Maintaining ERwin Data Types for Element
Article ID: 54843
Updated On:
Products
Repository
Issue/Introduction
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:
- 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 - Translate and validate Element Data type using the ERwin control table '<repowner>.DBX_ERW4_CNTL'.
- 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')
- Format of ERwin Control Table.
- 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; //
Environment
Release:
Component: XTR
Component: XTR
Feedback
Yes
No
Powered by
