The DB2 table CUSTOM_DATA can be filled using SQL statements.
These can be generated with the PRCICUS batch program.
Program can read data in CSV format or a FLAT file structure.
The input data is described with DD EDEFCMDS.
Furthermore, it must be described which data is stored in which column of the DB2 table.
DB2 Structure of CUSTOM_DATA
DATA_TYPE VARCHAR(128) NOT NULL,
DATA_INT_1 INTEGER DEFAULT 0 NOT NULL,
DATA_INT_2 INTEGER DEFAULT 0 NOT NULL,
DATA_STR_1 VARCHAR(128) DEFAULT ' ' NOT NULL,
DATA_STR_2 VARCHAR(128) DEFAULT ' ' NOT NULL,
DATA_STR_3 VARCHAR(256) DEFAULT ' ' NOT NULL,
DATA_STR_4 VARCHAR(256) DEFAULT ' ' NOT NULL,
DATA_STR_5 VARCHAR(1024) DEFAULT ' ' NOT NULL,
DATA_STR_6 VARCHAR(2048) DEFAULT ' ' NOT NULL
Job-Control
//S010 EXEC PGM=PRCICUS, PARM='DO=PM2930.CUSTOM_DATA,MC=500'
//* PROGRAM PARM 1: NAME OF DB2 TABLE
//* PROGRAM PARM 2: WRITE COMMIT AFTER EACH XXX SQL STATEMENT
//CEEOUT DD SYSOUT=*
//CEEDUMP DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSOUT DD SYSOUT=*
//HOSOLOAD DD DSN=&&PMFRMCO,DISP=(NEW,PASS,DELETE),
// RECFM=FB,LRECL=80,SPACE=(TRK,(100,100),RLSE)
//EDEFCMDS DD *
. . . . .
The EXEC parameter ‘DO’ is used to specify the target table and the parameter ‘MC’ is used to specify the COMMIT frequency.
EDEFCMDS Example CSV
//EDEFCMDS DD *
IMPORT_FILE=P391C.PROCMAN.V5R1.USER.SYSIN(HOSEMICL);
IMPORT_FILE_LRECL=80;
IMPORT_TYP=CSV; /* FLAT OR CSV */
IGNORE_FIRST_LINE=TRUE; /* TRUE OF FALSE */
SQL_TYP=INSERT; /* INSERT or DELETE */
- /* Default is INSERT */
-----------------------------------
-- PARAMETER FOR IMPORT_TYP CSV
-----------------------------------
CSV_DELIMITER=';';
CSV_ENCLOSURE='"';
CSV_COLUMNS_1=DATA_TYPE;
CSV_COLUMNS_2=DATA_INT_1;
CSV_COLUMNS_3=DATA_STR_1;
CSV_COLUMNS_4=DATA_STR_2;
CSV_KEY=CSV_COLUMNS_3;
CSV_FILTER=CSV_COLUMNS_1,'FIRE';
END1BLOCK;
IMPORT_FILE Name of Input file and Member name. Only PO datasets are supported.
IMPORT_FILE_LRECL Record length of Input file. Following LRECL are supported.
LRECL 80, 100, 120, 150, 200, 250, 300, 350, 400
IMPORT_TYP CSV or FLAT Typ of IMPORT_FILE
IGNORE_FIRST_LINETRUE or FALSE If the 1st line contains headings.
SQL_TYP INSERT or DELETE Which type of SQL should be written.
CSV_DELIMITER Each 1 digit character. E.g. ; or , or !
CSV_COLUMNS_n Column number of CSV source data and the target column in
CUSTOM_DATA
CSV_KEY Column number of CSV source data which must be unique.
In the case of duplicate values, only the 1 entry is used.
Note! Each definition block must be terminated with END1BLOCK.
EDEFCMDS Example FLAT
//EDEFCMDS DD *
----------------------------------
IMPORT_FILE=P391C.PROCMAN.V5R1.USER.SYSIN(HOFLATFL);
IMPORT_FILE_LRECL=80;
IMPORT_TYP=FLAT; /* FLAT OR CSV */
IGNORE_FIRST_LINE=TRUE; /* TRUE OF FALSE */
SQL_TYP=INSERT; /* INSERT or DELETE */
- /* Default is INSERT */
-----------------------------------
-- PARAMETER FOR IMPORT_TYP FLAT
-----------------------------------
FLAT_COLUMNS_1=DATA_TYPE,1:4;
FLAT_COLUMNS_2=DATA_INT_1,6:10;
FLAT_COLUMNS_3=DATA_STR_1,12:15;
FLAT_COLUMNS_4=DATA_STR_2,17:20;
FLAT_KEY=FLAT_COLUMNS_3;
FLAT_FILTER=FLAT_COLUMNS_1,'HIRE';
END1BLOCK;
IMPORT_FILE Name of Input file and Member name. Only PO datasets are supported.- IMPORT_FILE_LRECL Record length of Input file. Following LRECL are supported. LRECL 80,
100, 120, 150, 200, 250, 300, 350, 400
IMPORT_TYPCSV or FLAT Typ of IMPORT_FILE
IGNORE_FIRST_LINETRUE or FALSE If the 1st line contains headings
SQL_TYP INSERT or DELETE Which type of SQL should be written.
FLAT_COLUMNS_n Column number of FLAT source data and the target column in
CUSTOM_DATA
FLAT_KEY Column number of FLAT source data which must be unique. In the case
of nn duplicate values, only the 1 entry is used.
FLAT_FILTER Column number of FLAT source data and search value.