IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Obtaining the LOAD utility control statements


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1353
Location: Israel

PostPosted: Wed Jun 18, 2008 5:58 pm
Reply with quote

The IBM documentation says:
Quote:
If you want to obtain the LOAD utility control statements for loading rows into a table, but you do not want to
unload the rows, you can set the data set names for the SYSRECnn data sets to DUMMY. For example, to
obtain the utility control statements for loading rows into the department table, you invoke DSNTIAUL like
this:


//UNLOAD EXEC PGM=IKJEFT01,DYNAMNBR=20
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DSN)
RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB71) -
LIB('DSN710.RUNLIB.LOAD')
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSREC00 DD DUMMY
//SYSPUNCH DD DSN=DSN8UNLD.SYSPUNCH,
// UNIT=SYSDA,SPACE=(800,(15,15)),DISP=(,CATLG),
// VOL=SER=SCR03,RECFM=FB,LRECL=120,BLKSIZE=1200
//SYSIN DD *
DSN8710.DEPT


Figure 252. DSNTIAUL Invocation to obtain LOAD control statements

This is exactly what I need, but I found that it unloads the rows, it just don't write them (or rather write them in the DUMMY file)...
If I have a table with 25 million rows, the price is very high just to get the PUNCH data.

Is there a way to really obtain that info without that "little" overhead ?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Jun 18, 2008 6:04 pm
Reply with quote

How about something like this.

UNLOAD DATA FROM TABLE XXXXXXXX.XXXXBWRK
WHEN (NOT FISCAL_YEAR = FISCAL_YEAR)
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Wed Jun 18, 2008 6:23 pm
Reply with quote

Since you are using the DSNTIAUL utility, you can give an SQL that doesnot retrieve any records by givin it in your SYSIN,

Code:

//SYSIN    DD *
SELECT * FROM SCHEMA1.TABLE1
WHERE COL1<>COL1;


regards,
asci(i)
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Wed Jun 18, 2008 6:27 pm
Reply with quote

oops please add parm(sql) in your SYSTSIN for the utility to pickup SQLs...

Code:

//SYSTSIN  DD  *
  DSN SYSTEM(DSN)
  RUN  PROGRAM(DSNTIAUL) PLAN(DSNTIAUL) PARM('SQL')
//SYSPRINT DD SYSOUT=*
...


thanks,
asci(i)
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1353
Location: Israel

PostPosted: Wed Jun 18, 2008 7:57 pm
Reply with quote

Code:
//SYSIN    DD *
  SELECT * FROM SCHEMA1.TABLE1
  WHERE COL1<>COL1;


According to our DBA, if COL1 is not part of an index, we still have a tablespace scan...
There should be an improvement if COL1 is in an index.

If there is no easy/fast way to get the info "on the fly", we certainly will have to start saving the PUNCH info for each table...
(and that's what we tried to avoid. Oh well, it's not that bad after all).

Thanks anyway guys!
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Jun 18, 2008 8:02 pm
Reply with quote

Marso wrote:
Code:
//SYSIN    DD *
  SELECT * FROM SCHEMA1.TABLE1
  WHERE COL1<>COL1;


According to our DBA, if COL1 is not part of an index, we still have a tablespace scan...
There should be an improvement if COL1 is in an index.

If there is no easy/fast way to get the info "on the fly", we certainly will have to start saving the PUNCH info for each table...
(and that's what we tried to avoid. Oh well, it's not that bad after all).

Thanks anyway guys!


Use a column in the when that is indexed and a condition that is always false.
Back to top
View user's profile Send private message
ascelepius

New User


Joined: 16 Jun 2008
Posts: 35
Location: bangalore

PostPosted: Thu Jun 19, 2008 12:12 pm
Reply with quote

Marso,

I found something interesting today morning,

I ran an explain on 2 queries that have a false condition,

a) the original SQL with
Code:

  SELECT * FROM SCHEMA1.TABLE1
  WHERE COL1<>COL1;


Even though COL1 was indexed it went for a tablespace scan and the PROCMS in the DSN_STATEMNT_TABLE showed some significant time.

b) I modified the predicates to
Code:

  SELECT * FROM SCHEMA1.TABLE1
  WHERE 1=0;


Interestingly, DB2 didnot find an accesstype for this and PROCMS in the DSN_STATEMNT_TABLE showed 0ms!

I guess this should be your solution. Though you would still need to consult your DBA.

regards,
asci(i)
Back to top
View user's profile Send private message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1353
Location: Israel

PostPosted: Mon Jun 23, 2008 2:06 pm
Reply with quote

The DBA just pointed out that running this step before loading the table in the test environment may be problematic: the PUNCH generated will fit the TEST database, not necessarily the PROD database (where the data come from).

It seems that we must stock the PUNCH files, which is, after all, the easiest solution.

Thanks guys for your time! icon_smile.gif
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts How to load to DB2 with column level ... DB2 6
No new posts Calling DFSORT from Cobol, using OUTF... DFSORT/ICETOOL 5
No new posts Using Dynamic file handler in the Fil... COBOL Programming 2
No new posts REASON 00D70014 in load utility DB2 6
Search our Forums:

Back to Top