Db2 12 Exploiting new functions and Migration Planning PART 2
February 10, 2021 from 11am to 4pmDb2 12 Exploiting new functions and Migration Planning PART 1
February 9, 2021 from 11am to 4pm
IBM Db2 - The Ultimate Database for Cloud, Analytics & Mobile
Introduction:
Assume the following scenario:
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:
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:
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:
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:
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:
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:
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
February 9, 2021 from 8:30am to 4pm – "Fuller Conference Center " OLD STURBRIDGE VILLAGE
See the webpage for full details https://www.nedb2ug.com/meetings/september-26-2019 The next meeting of the New England Db2 User's Group will occur on Thursday, September 26 at the Fuller Conferenc…
Organized by Earl Dugan | Type: nedb2ug, meeting
0 Comments 0 LikesFebruary 9, 2021 from 11am to 4pm – Live webcast
Db2 12 Exploiting new functions and Migration Planning REGISTER NOW Db2 12 for z/OS® takes Db2 to a new level, both extending the core capabilities and empowering the future. Db2 12 extends the…
Organized by Surekha Parekh | Type: webcast
1 Comment 1 LikeFebruary 10, 2021 from 11am to 4pm – Live webcast
Db2 12 Exploiting new functions and Migration Planning PART 2 REGISTER NOW Db2 12 for z/OS® takes Db2 to a new level, both extending the core capabilities and empowering the future. Db2 12 extends…
Organized by Surekha Parekh | Type: webcast
0 Comments 0 LikesBringing Db2 enthusiasts together virtually. Expert or novice, distributed or mainframe, this is the place for everything DB2.
Started by Shaun Hide in Security and DB2. Last reply by Alexander Dashevsky Nov 17, 2020. 1 Reply 0 Likes
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
Started by Kip in What's hot ? Nov 4, 2020. 0 Replies 0 Likes
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
You need to be a member of The World of DB2 to add comments!
Join The World of DB2