Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Obtaining the LOAD utility control statements

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Marso

REXX Moderator


Joined: 13 Mar 2006
Posts: 1228
Location: Israel

PostPosted: Wed Jun 18, 2008 5:58 pm    Post subject: Obtaining the LOAD utility control statements
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    Post subject:
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    Post subject: Reply to: Obtaining the LOAD utility control statements
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    Post subject: Reply to: Obtaining the LOAD utility control statements
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: 1228
Location: Israel

PostPosted: Wed Jun 18, 2008 7:57 pm    Post subject: Reply to: Obtaining the LOAD utility control statements
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    Post subject: Re: Reply to: Obtaining the LOAD utility control statements
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    Post subject: Reply to: Obtaining the LOAD utility control statements
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: 1228
Location: Israel

PostPosted: Mon Jun 23, 2008 2:06 pm    Post subject: Reply to: Obtaining the LOAD utility control statements
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts DB2 - TERMINATE utility vasanthz DB2 5 Mon May 15, 2017 11:49 pm
No new posts Cobol upgrade - source code missing f... gthmrj IBM Tools 1 Wed Apr 26, 2017 6:04 pm
No new posts Copy load modules and alias vasanthz IBM Tools 5 Thu Apr 20, 2017 6:31 am
No new posts HLIST Utility In Batch Virendra Shambharkar TSO/ISPF 4 Fri Apr 07, 2017 3:38 pm
No new posts PA02 - Programmtically control attent... dodithegreat IMS DB/DC 1 Thu Apr 06, 2017 8:33 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us