Introduction:

Assume the following scenario:

1. Application delivers files as LOAD input for a partitioned table space which contains already data. The files have a naming convention, e.g. name.IN01, …IN02 etc., which means records from file IN01 go into partition 01 etc. Unfortunately, some records are not assignable by the application, so there is an extra file named IN99 with records for all partitions.
2. The application wants to have in a description column the value “Unassigned” if the record is loaded from input file IN99.
3. Only the records should be loaded with an update timestamp value of year 2020. For this purpose, the application has a column which was defined with a FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP column. The application wants to have their delivered update timestamp in this column and not the LOAD time. The format of the timestamp and an appropriate DATE column is yymmddhhmmss.
4. The application wants to get back a file which contains the records which could not be loaded. They should be marked with a flag which indicates the source file.
5. As the table contains already data, RESUME YES must be used. Due to performance also SHRLEVEL NONE and LOG NO. For recover ability an image copy must be taken.
6. A REORG after the LOAD has to be avoided, so the records to be loaded must be in clustering order.

 

The table definition is:

COL_EMPNO    DEC( 15 , 0) NOT NULL      ,                    

COL_LOCATION CHAR(20)               ,                        

COL_DATE     DATE                   ,                         

COL_TSTAMP   TIMESTAMP ,                                     

COL_TSTAMP   NOT NULL  GENERATED ALWAYS                      

             FOR EACH ROW ON UPDATE AS ROW CHANGE TIMESTAMP ,

COL_FLAG     CHAR(2)  IMPLICITLY HIDDEN ,                     

COL_DESC     CHAR(15) DEFAULT ' '  ,                         

PRIMARY KEY(COL_EMPNO))                                      

PARTITION BY RANGE                                           

(COL_EMPNO ASC)                                               

(PARTITION 1 ENDING ( 10 ) ,                                 

 PARTITION 2 ENDING ( 20 ) )                                 

What is to do:

1) Input file ...IN01 looks like:

000000000000001,    Berlin              200727120159

000000A00000003,    Oslo               200727120159

000000000A00005,    Rom               200727120159

 

First step is to bring the file qualifier into the input files: Use JCL and ICETOOL for this.

Define a JCL procedure:

//      EXPORT SYMLIST=(ISUF,OSUF)                           

//         SET  ISUF='99'                                    

//         SET  OSUF='99'                                    

//*******************************************************    

//ICETOOL  PROC ISUF='99',OSUF='99'                          

//RUN        EXEC PGM=SORT                                   

//SYSOUT     DD DUMMY                                        

//SORTIN     DD DISP=SHR,DSN=SYSADM.DB2.INPUT.IN&ISUF        

//SORTOUT    DD DISP=(NEW,PASS),UNIT=SYSDA,SPACE=(CYL,(1,1)),

//             DCB=(DSORG=PS),                               

//             DSN=DSNREC.#&OSUF                             

//*******************************                            

//SYSIN      DD *,SYMBOLS=EXECSYS                            

  OPTION COPY                                                

  OUTREC BUILD=(1,2,JFY=(SHIFT=LEFT,LEAD=C'&ISUF'),          

  3:C' ',1,200)                                              

//ICETOOL  PEND                                              

The input file has a LRECL of 200. Run the procedure for each input file, like this

//SORT01     EXEC ICETOOL,ISUF=01,OSUF=01

//SORT02     EXEC ICETOOL,ISUF=02,OSUF=02

//SORT91     EXEC ICETOOL,ISUF=99,OSUF=91

//SORT92     EXEC ICETOOL,ISUF=99,OSUF=92

Consider that you need 2 files from the IN99 file due to the syntax of LOAD.

The output file ...IN01 looks like:

01 000000000000001,    Berlin              200727120159

01 000000A00000003,    Oslo               200727120159

01 000000000A00005,    Rom               200727120159

…

and the file ...IN99 looks like:

99 000000000000002,    Amsterdam      200727120159

99 000000000000011,    Lyon                190831110254

 

Now the following LOAD INDDN statements can be used:

//IN01     DD DISP=SHR,DSN=DSNREC.#01

//IN02     DD DISP=SHR,DSN=DSNREC.#02

//IN91     DD DISP=SHR,DSN=DSNREC.#91

//IN92     DD DISP=SHR,DSN=DSNREC.#92

//SYSIN    DD *                     

LOAD DATA                                                         

   INTO TABLE … PART 1 INDDN(IN01,IN91)     

   …                                            

   INTO TABLE … PART 2 INDDN(IN02,IN92)     

Consider that IN91 and IN92 must be 2 separate files, although they are equal in this case.

 

As a reference see the following links:

SYMLIST

ICETOOL

PI96136

 

2) LOAD enhancement CONSTANTIF: The following code makes the application requirement to have "Unassigned" value in a description column:

LOAD DATA   

  INTO TABLE …

  ( COL_FLAG     POSITION  (1)    CHAR(2) ,

    …

    COL_DESC POSITION  (60:74)          

             CONSTANTIF(COL_FLAG = '99' )

             CONSTANT  ('Unassigned')   

 

As a reference see the following links:

PI69526

 

3) These requirements can be achieved by a few steps:

Use a WHEN condition like this

LOAD DATA             

  INTO TABLE …

  WHEN (44:45) = '20'

  …

to load only the records from year 2020.

 

Use OVERRIDE(ROWCHANGE) to load the application provided timestamp for GENERATED ALWAYS timestamps.

 

Use new date and timestamp formats in the field specification of the LOAD:

LOAD DATA                                                         

  OVERRIDE(ROWCHANGE)                                                                         

  INTO TABLE SZI10T                                                 

  (…

   COL_DATE     POSITION  (44)   DATE EXTERNAL(DATE_L) ,          

   COL_TSTAMP   POSITION  (44)   TIMESTAMP EXTERNAL(TIMESTAMP_D) , …

 

As a reference see the following links:

PH25572

PI69064

 

4) Code IGNORE(WHEN) condition in the LOAD to remove the records which are not loaded due to WHEN condition. The discard file for the application looks like:

01 000000000A00005,    Rom                200727120159

99 000000000A00004,    Paris               200727120159

02 000000B00000017,    Trondheim      200727120159

 

As a reference see the following links:

PI77159

 

5) LOAD RESUME YES can create inline image copies e.g.:

  TEMPLATE SCPY                                              

  DSN 'DSNC910.IC.&DB..&TS..A&PART(3,3)..&UNIQ.'              

  UNIT=SYSDA                                                 

                                                             

LOAD DATA                                                    

 RESUME YES SHRLEVEL NONE LOG NO                             

 …

  INTO TABLE … PART 1 INDDN(IN01,IN91)   COPYDDN(SCPY)

 …                                              

  INTO TABLE … PART 2 INDDN(IN02,IN92)  COPYDDN(SCPY)

 

As a reference see the following links:

PI81724

6) LOAD can sort the input in clustering order by using PRESORT and SORTDEVT parameter:

LOAD DATA                       

 RESUME YES SHRLEVEL NONE LOG NO

 IGNORE(WHEN) OVERRIDE(ROWCHANGE)

 PRESORT SORTDEVT SYSDA

 …     

 

As a reference see the following links:

PH23105

And finally, build all together and you have it.

The complete LOAD utility syntax is described here:

 

Summary:

This example should show that LOAD utility is a very powerful tool. If it is combined with JCL and ICETOOL, simple but useful application logic can be totally covered.

 

If you want the complete scenario send an email to peterhar@de.ibm.com