In this example, you create a mixed target database view by loading both VSAM source and SQL DDL source into the same database view.
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.