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

Views: 126

Add a Comment

You need to be a member of The World of DB2 to add comments!

Join The World of DB2

Events

Latest Activity

2 events by Surekha Parekh were featured
Jan 12
Surekha Parekh updated an event

Modernize IBM Z Apps for Hybrid Cloud using APIs at webcast

January 11, 2021 all day
Available on-demand now, this webinar will show you how to provide access to curated, proven and high-performance applications by turning your existing IBM Z® assets into security-rich, consumable and managed APIs. You will also learn some key considerations on how to choose among the numerous API and integration solutions available. Click the link to watch:…See More
Jan 11
francisco venegas liked Surekha Parekh's event Db2 12 Exploiting new functions and Migration Planning PART 1
Jan 11
Mark liked Surekha Parekh's video
Dec 17, 2020
Judith Talavera posted an event

Modernize IBM Z Apps for Hybrid Cloud using APIs at webcast

December 16, 2020 to February 16, 2021
Available on-demand now, this webinar will show you how to provide access to curated, proven and high-performance applications by turning your existing IBM Z® assets into security-rich, consumable and managed APIs. You will also learn some key considerations on how to choose among the numerous API and integration solutions available. Click the link to watch:…See More
Dec 17, 2020
An event by Janttu Lindroos was featured

Finland Db2 User Group (FiDUG) Seminar at IBM Finland

December 10, 2020 from 9am to 5pm
More info will be available later on. Stay tuned.See More
Dec 4, 2020
An event by Michael Strelczuk was featured
Thumbnail

MDUG - Michigan Db2 User Group Seminar at Four Points by Sheraton, Novi

December 9, 2020 from 8:15am to 4pm
Check out our MDUG web site for upcoming Seminar details and past Seminar presentations, etc.:http://www.mdug.org/New to MDUG?Please see content underHome About MDUGFor Seminar Reservations, please review the MDUG site content under…See More
Dec 4, 2020
An event by Surekha was featured
Thumbnail

Surprise in store! Join the Db2 for z/OS Spotlight session for REALLY new news from the lab at http://ibm.biz/MoTownsend_IBM2020

December 7, 2020 from 11am to 12pm
Surprise in store! Join the Db2 for z/OS Spotlight session for REALLY new news from the labIDUG EMEA 2020 Spotlight session Available today on replay  http://ibm.biz/MoTownsend_IBM2020 Db2 12 for z/OS® took Db2 to a new level, by extending the core capabilities and empowering the future. IBM extended the core with new…See More
Dec 4, 2020
Jonathan Sloan posted an event

IBM Z and IBM Cloud Pak for Data - Better Together at Webcast - East Coast U.S. time zone

December 3, 2020 from 11am to 12pm
Register at http://ibm.biz/IBMZandBetterTogetherWoD.IBM Cloud Pak for Data is a fully integrated data and AI platform that modernizes how businesses collect, organize and analyze data and infuse AI throughout their organization. And now IBM has introduced IBM Cloud Pak for Data on IBM Z, a highly secure private cloud for your enterprise data that allows you to adapt, transform and compete more effectively. Join this webcast to learn how IBM…See More
Dec 4, 2020
Surekha posted an event
Thumbnail

Surprise in store! Join the Db2 for z/OS Spotlight session for REALLY new news from the lab at http://ibm.biz/MoTownsend_IBM2020

December 7, 2020 from 11am to 12pm
Surprise in store! Join the Db2 for z/OS Spotlight session for REALLY new news from the labIDUG EMEA 2020 Spotlight session Available today on replay  http://ibm.biz/MoTownsend_IBM2020 Db2 12 for z/OS® took Db2 to a new level, by extending the core capabilities and empowering the future. IBM extended the core with new…See More
Dec 4, 2020
Dragica Smintic liked Mateja Jankovič's event SQLAdria Virtual Event
Nov 25, 2020
Jonathan Sloan posted an event

IBM Z and IBM Cloud Pak for Data - Better Together at Webcast - East Coast U.S. time zone

December 3, 2020 from 11am to 12pm
Register at http://ibm.biz/IBMZandBetterTogetherWoD.IBM Cloud Pak for Data is a fully integrated data and AI platform that modernizes how businesses collect, organize and analyze data and infuse AI throughout their organization. And now IBM has introduced IBM Cloud Pak for Data on IBM Z, a highly secure private cloud for your enterprise data that allows you to adapt, transform and compete more effectively. Join this webcast to learn how IBM…See More
Nov 23, 2020
Massimo Verzì liked Adrian Collett's group DUGI - Db2 User Group Italia
Nov 20, 2020
Profile Icon via Twitter
Enjoy complimentary visit #IBM Expo at #IDUGDb2 EMEA 2020. Virtual Expo is available 24x7 - check out our compli… https://t.co/pAqtzMLvfm
TwitterNov 19, 2020 · Reply · Retweet
Carol Davis-Mann posted a blog post

DB2 v11.5.4 REST API – Part One Setup and Configuration

By James CockayneEnabling clients to interact via HTTP GET/POST requests the REST API functionality provides clients a lightweight, modern interface to data stored in DB2 databases.  In this series we look at how to get started with the REST API from the DBA’s perspective, starting in part one with how to get the service up and running.…See More
Nov 18, 2020
Roberto S Chirinos liked Surekha's blog post What year was IBM Db2 invented?
Nov 17, 2020

Bringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.

Forum

Replaying past database activities

Started by Shaun Hide in Security and DB2. Last reply by Alexander Dashevsky Nov 17, 2020. 1 Reply

I would need to know who modified table data on a certain day.I do have my backups and database logs available.DB2audit was however not activated at this point.How would i  be able to access this information?Continue

Tags: #DataAccess

Db2 is developer inclusive and preview of Db2 11.5.5

Started by Kip in What's hot ? Nov 4, 2020. 0 Replies

This digital event examines how enterprises can become smarter with a data management system that is developer-inclusive. During this session, participants will get to learn how Db2 integrates with a broad array of programming languages, employs…Continue

© 2021   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service