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: 180

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

Jim Reed updated an event
Thumbnail

Build simple to multifaceted dashboards in QMF for Workstation at Online

July 22, 2021 from 8am to 9am
Today businesses need real time visual dashboards and reports across all their data, including hard to reach data across IBM Z non-relational data sources. QMF for Workstation makes visualizing virtually any data a reality, including IMS, VSAM, Adabas, IDMS and DB2 for z/OS data sources.This session will showcase and demo QMF’s dashboard/application building interface in QMF for Workstation from simple to multifaceted.See More
Wednesday
Carol Davis-Mann posted a blog post

Management Overview: DB2 Continuous Delivery Function Level Upgrades

By Julian StuhlerBackgroundWith the advent of DB2 12 for z/OS, IBM has moved to a more agile approach for delivering new function to DB2 customers, known as “continuous delivery”. Major new releases of DB2 will now happen more rarely, with smaller packets of new functionality being delivered via the routine product maintenance process. This allows IBM to develop and release new features much more frequently, thereby reducing “time to value” – a familiar DevOps message.To allow DB2 customers to absorb this new function in a flexible and efficient way, IBM has also delivered a comprehensive set of capabilities that allow the “function level” of the overall DB2 system to be easily progressed while insulating individual applications from the impact of any changes via the “application compatibility level” set for each program.Read Julian Stuhler's article…See More
Jul 14
Jim Reed posted an event
Thumbnail

Build simple to multifaceted dashboards in QMF for Workstation at Online

July 20, 2021 from 8am to 9am
Today businesses need real time visual dashboards and reports across all their data, including hard to reach data across IBM Z non-relational data sources. QMF for Workstation makes visualizing virtually any data a reality, including IMS, VSAM, Adabas, IDMS and DB2 for z/OS data sources.This session will showcase and demo QMF’s dashboard/application building interface in QMF for Workstation from simple to multifaceted.See More
Jul 14
A video by Surekha Parekh was featured

Db2 AI for z/OS Version 1.4

IBM Db2 AI for z/OS version 1.4 is a powerhouse of enhancements! Db2 AI for z/OS infuses more AI into your Db2 environment, making it operate smarter and mor...
Jul 5
A blog post by Daniel Luksetich was featured

Db2 for z/OS and JSON

I wrote a very short story about using the JSON/SQL support in Db2 for z/OS. I also wrote an introduction to JSON support in Db2 for z/OS article for IDUG.The story and link to the IDUG article is here:https://www.db2expert.com/db2expert/introduction-to-db2-for-z-os-json-sql-support/This is a direct link to the IDUG article, but you need an IDUG login account (which is free).https://www.db2expert.com/db2expert/introduction-to-db2-for-z-os-json-sql-support/See More
Jun 30
Daniel Luksetich posted a blog post

Db2 for z/OS and JSON

I wrote a very short story about using the JSON/SQL support in Db2 for z/OS. I also wrote an introduction to JSON support in Db2 for z/OS article for IDUG.The story and link to the IDUG article is here:https://www.db2expert.com/db2expert/introduction-to-db2-for-z-os-json-sql-support/This is a direct link to the IDUG article, but you need an IDUG login account (which is free).https://www.db2expert.com/db2expert/introduction-to-db2-for-z-os-json-sql-support/See More
Jun 30
William Shipley liked Surekha Parekh's blog post Live Db2 12 Client Panel 30th June 2021 - Sign Up today!
Jun 23
A blog post by Surekha Parekh was featured

Live Db2 12 Client Panel 30th June 2021 - Sign Up today!

Live Db2 12 Client Panel 30th June 2021 - Sign Up today!HURRY SIGN UP TIME IS HURRYING OUTSummaryDb2 for z/OS is the strategic enterprise database that has evolved and adapted to meet client needs for over 35 years. John Campbell IBM Distinguished Engineer has been one of key developers from IBM Silicon Valley Lab that has been extremely influential in driving some of the innovation around the core strengths and business value of Db2 for z/OS.We are bringing together some of our Db2 for z/OS clients so that they can share some of their "use cases" and experiences around Db2 12 upgrade journey and discuss how they are exploiting, planning to exploit the new technology to leverage  their Db2 for z/OS data to…See More
Jun 22
Daniel Luksetich posted a blog post

Traditional Db2 Development and Modern Database Development

This article is the first of a four-part series addressing Db2 for z/OS and modern development utilizing a Agile methodology and DevOps processes. In this article we define traditional versus modern development and how Db2 for z/OS, while remaining the premier database for high volume system of record applications, might be left behind when it comes to best practices involving innovation. Did you notice that “Db2” follows “Traditional” in the title of this article, but “Database” follows “Modern”? It is my opinion that Db2 for z/OS is not the first database that comes to mind when considering a DevOps development methodology. However, there is no reason why it shouldn’t be!The first thing we need to do is to identify what we mean by traditional versus modern development. In this series of articles, we will be speaking specifically of the waterfall methodology versus an Agile methodology and DevOps practices.To read the entire article please follow this link:…See More
Jun 21
Gulfem Ogutgen liked Annette Zawacki's event Garanti-BBVA shares Db2 Analytics Accelerator migration and operational experiences
Jun 21
Gulfem Ogutgen liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Jun 21
edmundo nascimento liked Surekha Parekh's event Interactive Db2 12 Client Panel with John Campbell IBM DE
Jun 21

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

Forum

ADM10501W - Health indicator "Database Automatic Storage Utilization"

Started by ONLO in Application Development and DB2 Jun 16. 0 Replies

AIX Version 6.1DB2 10.5.6Hi every body,I got the above error:MESSAGE : ADM10501W Health indicator "Database Automatic Storage Utilization" ("db.auto_storage_util") breached the "upper" warning threshold of "80 %" with value "88 %" on "database"…Continue

DB2 Future

Started by Peter E Saxe in Application Development and DB2 May 20. 0 Replies

Has anyone ever submitted ideas of new functions in DBMS software like DB2?   have an idea for a future enhancement to DB2 that I would like to see.  Is this discussion area a good place to discuss this ?  Thanks.Continue

Tags: Enhancements, Future, DB2

© 2021   Created by Surekha Parekh.   Powered by

Badges  |  Report an Issue  |  Terms of Service