Maintaining ERwin Data Types for Element

Document ID : KB000054843
Last Modified Date : 14/02/2018
Show Technical Document Details

Description:

How do you add additional data types to support various systems? What Code Table does the ERwin Exchange validate against when loading models into CAR/zOS from the work tables?

Solution:

  1. Mapping ERwin Data types in the ERwin Control file.

    You can translate an ERwin Data type into a different type(s) by using the mapping feature of the ERwin Control file.
    In the Mapping part of Control file you need to include the following:

            <Mapping>        <FromErwin>            <Object>Attribute</Object>            <Attr>Datatype</Attr>        </FromErwin>        <ToRepository>            <Table>ELEMENT</Table>            <Column>DATA_TYPE</Column>        </ToRepository>        <Convert>            <FromValue>erw_datatype1</FromValue>            <ToValue>map_datatype1</ToValue>        </Convert>        <Convert>            <FromValue>erw_datatype2</FromValue>            <ToValue>map_datatype2</ToValue>        </Convert>            ............................................         </Mapping>

    Erw_datatype - datatype for Attribute from ERwin XML file
    Map_datatype - mapped datatype which will appear in the <repowner>.PRMXML_PI working table

  2. Translate and validate Element Data type using the ERwin control table '<repowner>.DBX_ERW4_CNTL'.
    1. Format of ERwin Control Table.

      ERwin control table has following structure:
                 TABLE_NAME                      CHAR (8) NOT NULL,            TYPE                            CHAR (80) NOT NULL,           VALUE1                          CHAR (80) NOT NULL,           VALUE2                          CHAR (18) NOT NULL,           VALUE3                          CHAR (18) NOT NULL,           VALUE4                          CHAR (18) NOT NULL,           ERW_RELEASE                     CHAR (3) NOT NULL

      Table name determines different entries of control tables.
      These entries are used for different purpose: validating, translating, default value, etc.
      Separate sets of Table names exist for every running release of AllFusion ERwin

      For translating and validating Element Data type we are using the
      Table name 'ELEMDATA'. We have following values for the fields:

      TABLE_NAME    -  'ELEMDATA' TYPE          -  ERwin data type or mapping data type of the Element  VALUE1        -  Repository data type for Element. We will translate 
      ERwin data type in this data type during step ER04
      of loading job VALUE2 - 'Y' - if Length is permitting for this data type. 'N' - if not VALUE3 - 'Y' - if Decimal is permitting for this data type. 'N' - if not VALUE4 - 'Y' - if Sign is permitting for this data type. 'N' - if not ERW_RELEASE - Release of AllFusion ERwin. (e.g. '4.1')


  3. Maintaining Data type in the ERwin Control Table.
    You can insert new, update or delete existing Data type.
    For all of these purposes you can use following SQL statements:

    Insert INSERT INTO owner.DBX_ERW4_CNTL     (TABLE_NAME,                       TYPE,                             VALUE1,                           VALUE2,                           VALUE3,                           VALUE4,                           ERW_RELEASE)                   VALUES(                                  'ELEMDATA',                       'erw_datatype',                   'repos_datatype',                 'lengthflag(Y/N)',                'decimalflag(Y/N)',               'signflag(Y/N)',                  'erw release')            ;     Update UPDATE owner.DBX_ERW4_CNTL            SET VALUE1='repos_datatype',            VALUE2='lengthflag(Y/N)',           VALUE3='decimalflag(Y/N)',          VALUE4='signflag(Y/N)'          WHERE TABLE_NAME = 'ELEMDATA' AND         TYPE = 'erw_datatype' AND           ERW_RELEASE = 'erw release';  Delete DELETE FROM DBXERW.DBX_ERW4_CNTL            WHERE TABLE_NAME = 'ELEMDATA' AND         TYPE = 'erw_datatype' AND           ERW_RELEASE = 'erw release';
    You can use online tools to execute these queries or you can use following step for batch job:
    //RUNTEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20//STEPLIB  DD  DISP=SHR,DSN=DB2.dsn.SDSNLOAD//SYSTSPRT DD SYSOUT=*//SYSTSIN  DD *   DSN SYSTEM(dsn)   RUN PROGRAM(DSNTIAD) PLAN(DSNTIA81) -       LIB('dsn.RUNLIB.LOAD')   END//SYSPRINT DD SYSOUT=*//SYSUDUMP DD SYSOUT=*//SYSIN    DD *                              Any or multiple SQL statements from above   COMMIT;//

    Sample1:

    We have loaded ERwin 4.0 data type 'LONG VARCHAR' into repository as 'VARCHAR' but we need to load such data type as 'LONGVAR'.
    //FINVA01$ JOB (10146),'',                                     //   NOTIFY=FINVA01,MSGCLASS=X,CLASS=B,REGION=6M,MSGLEVEL=(1,1)//RUNTEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20                        //STEPLIB  DD  DISP=SHR,DSN=DB2.DB2810.SDSNLOAD                   //SYSTSPRT DD SYSOUT=*                                         //SYSTSIN  DD *                                                   DSN SYSTEM(D81A)                                                RUN PROGRAM(DSNTIAD) PLAN(DSNTIA81) -                              LIB('D81A.RUNLIB.LOAD')                                     END                                                         //SYSPRINT DD SYSOUT=*                                         //SYSUDUMP DD SYSOUT=*                                         //SYSIN    DD *                                                  UPDATE ARZ7SP1.DBX_ERW4_CNTL                                      SET VALUE1='LONGVAR'                                           WHERE TABLE_NAME = 'ELEMDATA' AND                                    TYPE = 'LONG VARCHAR' AND                                      ERW_RELEASE = '4.0'                                   ;                                                              COMMIT;  //

    Sample2:

    Insert new ERwin 4.0 data type 'NUMERO' and load into Repository as NUMERIC, it could have length and sign but not decimal.
    //FINVA01$ JOB (10146),'',                                     //   NOTIFY=FINVA01,MSGCLASS=X,CLASS=B,REGION=6M,MSGLEVEL=(1,1)//RUNTEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20                        //STEPLIB  DD  DISP=SHR,DSN=DB2.DB2810.SDSNLOAD                   //SYSTSPRT DD SYSOUT=*                                         //SYSTSIN  DD *                                                   DSN SYSTEM(D81A)                                                RUN PROGRAM(DSNTIAD) PLAN(DSNTIA81) -                              LIB('D81A.RUNLIB.LOAD')              END //SYSPRINT DD SYSOUT=*                                         //SYSUDUMP DD SYSOUT=*                                         //SYSIN    DD *                                                  INSERT INTO ARZ7SP1.DBX_ERW4_CNTL   (TABLE_NAME,                     TYPE,                           VALUE1,                         VALUE2,                         VALUE3,                         VALUE4,                         ERW_RELEASE)                 VALUES(                                'ELEMDATA',                     'NUMERO',                 'NUMERIC',                'Y',                            'N',                            'Y',                            '4.0')                  ;                                 COMMIT;  //

    Sample3:

    Delete from ERwin 4.0 data type 'NUMERO'.
    //FINVA01$ JOB (10146),'',                                      //   NOTIFY=FINVA01,MSGCLASS=X,CLASS=B,REGION=6M,MSGLEVEL=(1,1) /*JOBPARM SYSAFF=CA31                                           //RUNTEP2 EXEC PGM=IKJEFT01,DYNAMNBR=20                         //STEPLIB  DD  DISP=SHR,DSN=DB2.DB2810.GA.SDSNLOAD              //SYSTSPRT DD SYSOUT=*                                          //SYSTSIN  DD *                                                    DSN SYSTEM(D81A)                                                RUN PROGRAM(DSNTIAD) PLAN(DSNTIA81) -                               LIB('D81A.RUNLIB.LOAD')                                     END                                                          //SYSPRINT DD SYSOUT=*                                          //SYSUDUMP DD SYSOUT=*                                          //SYSIN    DD *                                                                                                                     DELETE FROM ARZ7SP1.DBX_ERW4_CNTL                                WHERE TABLE_NAME = 'ELEMDATA' AND                                TYPE = 'NUMERO' AND                                             ERW_RELEASE = '4.0'                                         ;      COMMIT;  //