Example - Mixed Target

Restriction: This topic applies only when the AppMaster Builder AddPack has been installed, and applies only to Windows platforms.

In this example, you create a mixed target database view by loading both VSAM source and SQL DDL source into the same database view.

Note: When creating your own data views, you can optionally load your SQL tables using an SQL schema instead of loading SQL DDL source directly.

From the AppMaster Builder Data View Manager, create a new VSAM source member named MIXEDSUB.

Enter the following code into your default editor. Start each *DDI statement in column 7. The line numbers shown here are for the purposes of annotating the code only so each line can be explained below. Do not put them into your code:

0010   *DDI VSM DDN=SAMPVSM1
0020   *DDI     TYPE=K,VSPREFIX=USER.CLASSX,TRK(10 10)
0030   *DDI     VOL(30E119),CSIZ(4096)
0040   *DDI REC NAME=SAMPLE-RECORD1,SHORT=SAMPVSM1
0050   *DDI     COPY=SAMPVSM1,SOURCE=P,MAXLEN=250
0060   *DDI IDX NAME=ISE-PRIMARY-KEY,TYPE=P,KEYLEN=40,
0070   *DDI     OFFSET=0,DDN=SAMPVSM1,TRK(1,1)
0080   *DDI IDX NAME=ISE-ALTERNATE1-KEY,TYPE=U,KEYLEN=45,
0090   *DDI     OFFSET=40,DDN=SAMPALT1,TRK(1,1)
0100   *DDI VSM DDN=SAMPVSM2
0110   *DDI     TYPE=K,VSPREFIX=USER.CLASSX,TRK(10 10)
0120   *DDI     VOL(30E119),CISZ(4096)
0130   *DDI REC NAME=SAMPLE-RECORD2,SHORT=SAMPVSM2
0140   *DDI     COPY=SAMPVSM2,SOURCE=P,MAXLEN=2500
0150   *DDI IDX NAME=MB-CCMS-600-COMPY-KEY,TYPE=P,KEYLEN=7,
0160   *DDI     OFFSET=0,DDN=SAMPVSM2,TRK(1,1)
0170   *DDI SUB NAME=MIXEDSUB
0180   *DDI       RECORD=SAMPLE-RECORD1,PROCOPT=A,
0190   *DDI       ACC=DYNAMIC
0200   *DDI AS=SAMPVSM1
0210   *DDI       RECORD=SAMPLE-RECORD2,PROCOPT=A
0220   *DDI       ACC=DYNAMIC
0230   *DDI AS=SAMPVSM2

Lines 0010 - 0030:

       *DDI VSM DDN=SAMPVSM1
       *DDI     TYPE=K,VSPREFIX=USER.CLASSX,TRK(10 10)
       *DDI     VOL(30E119),CSIZ(4096)

Use the VSM keyword to provide a DD name for a VSAM record.

Lines 0040 - 0050:

       *DDI REC NAME=SAMPLE-RECORD1,SHORT=SAMPVSM1
       *DDI     COPY=SAMPVSM1,SOURCE=P,MAXLEN=250

Use the REC keyword to define the VSAM record.

Lines 0060:

       *DDI IDX NAME=ISE-PRIMARY-KEY,TYPE=P,KEYLEN=40,
       *DDI     OFFSET=0,DDN=SAMPVSM1,TRK(1,1)
       *DDI IDX NAME=ISE-ALTERNATE1-KEY,TYPE=U,KEYLEN=45,
       *DDI     OFFSET=40,DDN=SAMPALT1,TRK(1,1)

Use the IDX keyword to define primary and alternate indexes.

Lines 0100 - 0160:

       *DDI VSM DDN=SAMPVSM2
       *DDI     TYPE=K,VSPREFIX=USER.CLASSX,TRK(10 10)
       *DDI     VOL(30E119),CISZ(4096)
       *DDI REC NAME=SAMPLE-RECORD2,SHORT=SAMPVSM2
       *DDI     COPY=SAMPVSM2,SOURCE=P,MAXLEN=2500
       *DDI IDX NAME=MB-CCMS-600-COMPY-KEY,TYPE=P,KEYLEN=7,
       *DDI     OFFSET=0,DDN=SAMPVSM2,TRK(1,1)

This code defines a second VSAM record.

Lines 0170 - 0230:

       *DDI SUB NAME=MIXEDSUB
       *DDI       RECORD=SAMPLE-RECORD1,PROCOPT=A,
       *DDI       ACC=DYNAMIC
       *DDI AS=SAMPVSM1
       *DDI       RECORD=SAMPLE-RECORD2,PROCOPT=A
       *DDI       ACC=DYNAMIC
       *DDI AS=SAMPVSM2

Use the SUB statement to specify the name of the database view that will contain the two identified records. In this case, the name of the database view is MIXEDSUB, and it identifies SAMPLE-RECORD1 and SAMPLE-RECORD2. The name specified for the SUB statement must match the name of the database view.

Save the code in your editor and then load the MIXEDSUB VSAM source file and build the MIXEDSUB database view.

You should see your new database view, MIXEDSUB appear in the AppMaster Builder Data View Manager tree structure.

When creating a mixed target database view that contains SQL records, the subschema name (specified in the CREATE SUBSCHEMA statement of your SQL DDL file) and the filename of your SQL DDL file must also match the name of the database view. This is the same name as is specified in the SUB statement of your VSAM DDISRC file.

Next, open the MIXEDSUB database view and create a new SQL DDL source member named MIXEDSUB.

Enter the following native SQL into your editor to define your tables. It does not matter what column you start in. Again, the line numbers are for annotation purposes only. Do not put them into your code:

0010 CREATE SUBSCHEMA MIXEDSUB
0020 (TB_CEDCO_BRK     COPYLIB 'TBXCEDCO',
0030 TB_GROUP_CODES    COPYLIB 'TBXGROUP',
0040 TB_INSTMT_DETLS   COPYLIB 'TBXINSTM');
0050 
0060 
0070 CREATE TABLE TB_CEDCO_BRK
0080    ( CEDCO_CODE         CHAR        (006)       NOT NULL,
0090      CEDCO_NAME         CHAR        (030)       NOT NULL WITH DEFAULT,
0100      ADDRESS_1          CHAR        (030)       NOT NULL WITH DEFAULT,
0110     ADDRESS_2          CHAR        (030)       NOT NULL WITH DEFAULT,
0120      ADDRESS_3          CHAR        (030)       NOT NULL WITH DEFAULT,
0130      ADDRESS_4          CHAR        (030)       NOT NULL WITH DEFAULT,
0140      GROUP_CODE         CHAR        (003)       NOT NULL WITH DEFAULT,
0150      UK_AUTHORITY       CHAR        (001)       NOT NULL WITH DEFAULT,
0160      SOLVENCY_IND       CHAR        (002)       NOT NULL WITH DEFAULT,
0170      SOLVENCY_UPD_DATE  DATE                   ,
0180      BRK_TERRITORY_CODE CHAR        (003)       NOT NULL WITH DEFAULT,
0190      UPDATE_DATE        DATE                    NOT NULL,
0200      UPDATE_TIME        TIME                    NOT NULL,
0210      TIME_STAMP         TIMESTAMP               NOT NULL WITH DEFAULT,
0220      PRIMARY KEY
0230        ( CEDCO_CODE
0240        )
0250    )
0260 IN UAPDB001.TSDE01;
0270 
0280 CREATE TABLE TB_GROUP_CODES
0290    ( GROUP_CODE         CHAR        (003)       NOT NULL,
0300      GROUP_NAME         CHAR        (030)       NOT NULL WITH DEFAULT,
0310      UPDATE_DATE        DATE                    NOT NULL,
0320      UPDATE_TIME        TIME                    NOT NULL,
0330      TIME_STAMP         TIMESTAMP               NOT NULL WITH DEFAULT,
0340      PRIMARY KEY
0350        ( GROUP_CODE
0360        )
0370    )
0380 IN UAPDB001.TSSG01;
0390 
0400 CREATE TABLE TB_INSTMT_DETLS
0410    ( COMPANY_CODE       CHAR        (002)       NOT NULL,
0420      CONTRACT_NUMBER    CHAR        (010)       NOT NULL,
0430      UW_YEAR            CHAR        (002)       NOT NULL,
0440      INSTAL_OCC_NO      SMALLINT                NOT NULL,
0450      PREMIUM_DUE_MTH    CHAR        (002)       NOT NULL WITH DEFAULT,
0460      PREMIUM_DUE_YEAR   CHAR        (002)       NOT NULL WITH DEFAULT,
0470      PREMIUM_DUE_PCT    DECIMAL (007,004)       NOT NULL WITH DEFAULT,
0480      TIME_STAMP         TIMESTAMP               NOT NULL WITH DEFAULT,
0490      PRIMARY KEY
0500        ( COMPANY_CODE,
0510          CONTRACT_NUMBER,
0520          UW_YEAR,
0530          INSTAL_OCC_NO
0540        )
0550    )
0560 IN UAPDB001.TSMA04;

Lines 0010 - 0040:

     CREATE SUBSCHEMA MIXEDSUB
     (TB_CEDCO_BRK     COPYLIB 'TBXCEDCO',
     TB_GROUP_CODES    COPYLIB 'TBXGROUP',
     TB_INSTMT_DETLS   COPYLIB 'TBXINSTM');

The CREATE SUBSCHEMA statement identifies the subschema name and defines the copybooks it uses.

Lines 0070 - 0560:

0070 CREATE TABLE TB_CEDCO_BRK
0080    ( CEDCO_CODE         CHAR        (006)       NOT NULL,
0090      CEDCO_NAME         CHAR        (030)       NOT NULL WITH DEFAULT,
0100      ADDRESS_1          CHAR        (030)       NOT NULL WITH DEFAULT,
0110     ADDRESS_2          CHAR        (030)       NOT NULL WITH DEFAULT,
0120      ADDRESS_3          CHAR        (030)       NOT NULL WITH DEFAULT,
0130      ADDRESS_4          CHAR        (030)       NOT NULL WITH DEFAULT,
0140      GROUP_CODE         CHAR        (003)       NOT NULL WITH DEFAULT,
0150      UK_AUTHORITY       CHAR        (001)       NOT NULL WITH DEFAULT,
0160      SOLVENCY_IND       CHAR        (002)       NOT NULL WITH DEFAULT,
0170      SOLVENCY_UPD_DATE  DATE                   ,
0180      BRK_TERRITORY_CODE CHAR        (003)       NOT NULL WITH DEFAULT,
0190      UPDATE_DATE        DATE                    NOT NULL,
0200      UPDATE_TIME        TIME                    NOT NULL,
0210      TIME_STAMP         TIMESTAMP               NOT NULL WITH DEFAULT,
0220      PRIMARY KEY
0230        ( CEDCO_CODE
0240        )
0250    )
0260 IN UAPDB001.TSDE01;
0270 
0280 CREATE TABLE TB_GROUP_CODES
0290    ( GROUP_CODE         CHAR        (003)       NOT NULL,
0300      GROUP_NAME         CHAR        (030)       NOT NULL WITH DEFAULT,
0310      UPDATE_DATE        DATE                    NOT NULL,
0320      UPDATE_TIME        TIME                    NOT NULL,
0330      TIME_STAMP         TIMESTAMP               NOT NULL WITH DEFAULT,
0340      PRIMARY KEY
0350        ( GROUP_CODE
0360        )
0370    )
0380 IN UAPDB001.TSSG01;
0390 
0400 CREATE TABLE TB_INSTMT_DETLS
0410    ( COMPANY_CODE       CHAR        (002)       NOT NULL,
0420      CONTRACT_NUMBER    CHAR        (010)       NOT NULL,
0430      UW_YEAR            CHAR        (002)       NOT NULL,
0440      INSTAL_OCC_NO      SMALLINT                NOT NULL,
0450      PREMIUM_DUE_MTH    CHAR        (002)       NOT NULL WITH DEFAULT,
0460      PREMIUM_DUE_YEAR   CHAR        (002)       NOT NULL WITH DEFAULT,
0470      PREMIUM_DUE_PCT    DECIMAL (007,004)       NOT NULL WITH DEFAULT,
0480      TIME_STAMP         TIMESTAMP               NOT NULL WITH DEFAULT,
0490      PRIMARY KEY
0500        ( COMPANY_CODE,
0510          CONTRACT_NUMBER,
0520          UW_YEAR,
0530          INSTAL_OCC_NO
0540        )
0550    )
0560 IN UAPDB001.TSMA04;

Save the code in your editor, then load your new MIXEDSUB source file into the MIXEDSUB database view and build it again.

From the AppMaster Builder Data View Manager, expand MIXEDSUB to see the VSAM and SQL members contained in the one database view.