Topic
  • 4 replies
  • Latest Post - ‏2012-10-11T09:04:25Z by SystemAdmin
SystemAdmin
SystemAdmin
849 Posts

Pinned topic Mass annotation of DB2 tables

‏2012-10-08T09:00:21Z |
Hi,

We are using RAA v6.0.0. One of the customizations that is being explored, is to tag all components with their respective owning teams.
RAA provides UI option to annotate the components, but this needs to be done 1-by-1 & is time consuming for an inventory size of 50-60 K.

Hence, we thought of annotating the parent applications and automatically cascade the same across the components of this particular application, by means of triggers on DMH database tables.

We have been able to relate DMH_APPL_CMPNT table & DMH_CHAR_PROPERTY to cascade this information for cmpnt_type = 9.

For DB2 tables, it seemed easier to annotate the Database & let the component Tables under that particular DB adopt the same annotain as DB - again, by use of triggers \ queries on DMH DB.

How can this be done?
We have the relation between application, Database and DB2 table name mapping ready.

When trying to use any of the DMH_DB2% or DMH_SQL% tables, we see that these tables have the table name\Database information. We have the table id (DB_TABLE_ID) though, but that doesnt help, as we need the table name \ database name references to associate to application owners.

If there is a different way to do this, do let us know.
Thank you!
Updated on 2012-10-11T09:04:25Z at 2012-10-11T09:04:25Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    849 Posts

    Re: Mass annotation of DB2 tables

    ‏2012-10-09T04:29:32Z  
    Wanted to correct the previous question

    When trying to use any of the DMH_DB2% or DMH_SQL% tables, these tables do not have the table name\Database name information. We have the table id (DB2_TABLE_ID) though, but that doesnt help, as we need the table name \ database name references to associate to application owners.

    Where are DB2 table names (used in applications) stored in DMH DB?
  • SystemAdmin
    SystemAdmin
    849 Posts

    Re: Mass annotation of DB2 tables

    ‏2012-10-09T14:27:54Z  
    Wanted to correct the previous question

    When trying to use any of the DMH_DB2% or DMH_SQL% tables, these tables do not have the table name\Database name information. We have the table id (DB2_TABLE_ID) though, but that doesnt help, as we need the table name \ database name references to associate to application owners.

    Where are DB2 table names (used in applications) stored in DMH DB?
    Columns FULLNAME_SYM_ID and NAME_SYM_ID are foreign keys to the DMH_SYMBOL table.
  • jcdelmo
    jcdelmo
    344 Posts

    Re: Mass annotation of DB2 tables

    ‏2012-10-09T15:21:02Z  
    Wanted to correct the previous question

    When trying to use any of the DMH_DB2% or DMH_SQL% tables, these tables do not have the table name\Database name information. We have the table id (DB2_TABLE_ID) though, but that doesnt help, as we need the table name \ database name references to associate to application owners.

    Where are DB2 table names (used in applications) stored in DMH DB?
    The DMH_DB2_TABLE (see below) stores four names associated with each TABLE and/or VIEW: The LOCATION name, the CREATOR (schema) name, the TABLE name and the FULLNAME

    For example, a table named ABC in schema XYZ in location DEF would be stored as:

    1) FULLNAME = DEF.XYZ.ABC
    2) NAME = ABC
    3) CREATOR = XYZ
    4) LOCATION = DEF

    The actual text of the name is stored in the DMH_SYMBOL table. Joining any of these xxxx_SYM_ID columns with the SYMBOL_ID column in that table will give you the name from the SYMBOL_TEXT column.
    
    CREATE TABLE DMH_DB2_TABLE ( DB2_TABLE_ID         INTEGER        NOT NULL, DB2_SUBSYSTEM_ID     INTEGER        NOT NULL, FULLNAME_SYM_ID      INTEGER        NOT NULL, NAME_SYM_ID          INTEGER        NOT NULL, CREATOR_SYM_ID       INTEGER        NOT NULL WITH DEFAULT, LOCATION_SYM_ID      INTEGER        NOT NULL WITH DEFAULT, CMPNT_TYPE_ID        SMALLINT       NOT NULL, PRIMARY KEY ( DB2_TABLE_ID ) )
    
  • SystemAdmin
    SystemAdmin
    849 Posts

    Re: Mass annotation of DB2 tables

    ‏2012-10-11T09:04:25Z  
    • jcdelmo
    • ‏2012-10-09T15:21:02Z
    The DMH_DB2_TABLE (see below) stores four names associated with each TABLE and/or VIEW: The LOCATION name, the CREATOR (schema) name, the TABLE name and the FULLNAME

    For example, a table named ABC in schema XYZ in location DEF would be stored as:

    1) FULLNAME = DEF.XYZ.ABC
    2) NAME = ABC
    3) CREATOR = XYZ
    4) LOCATION = DEF

    The actual text of the name is stored in the DMH_SYMBOL table. Joining any of these xxxx_SYM_ID columns with the SYMBOL_ID column in that table will give you the name from the SYMBOL_TEXT column.
    <pre class="jive-pre"> CREATE TABLE DMH_DB2_TABLE ( DB2_TABLE_ID INTEGER NOT NULL, DB2_SUBSYSTEM_ID INTEGER NOT NULL, FULLNAME_SYM_ID INTEGER NOT NULL, NAME_SYM_ID INTEGER NOT NULL, CREATOR_SYM_ID INTEGER NOT NULL WITH DEFAULT, LOCATION_SYM_ID INTEGER NOT NULL WITH DEFAULT, CMPNT_TYPE_ID SMALLINT NOT NULL, PRIMARY KEY ( DB2_TABLE_ID ) ) </pre>
    Joan & JCLDEMO - Thank you very much; that helped.