xxSJCUST

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_LINE TRUE 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_TYP CSV or FLAT Typ of IMPORT_FILE

• IGNORE_FIRST_LINE TRUE 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.