View previous topic :: View next topic
|
Author |
Message |
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
How about something like this.
UNLOAD DATA FROM TABLE XXXXXXXX.XXXXBWRK
WHEN (NOT FISCAL_YEAR = FISCAL_YEAR) |
|
Back to top |
|
|
ascelepius
New User
Joined: 16 Jun 2008 Posts: 35 Location: bangalore
|
|
|
|
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 |
|
|
ascelepius
New User
Joined: 16 Jun 2008 Posts: 35 Location: bangalore
|
|
|
|
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 |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
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 |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007 Posts: 1512 Location: Virginia, USA
|
|
|
|
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 |
|
|
ascelepius
New User
Joined: 16 Jun 2008 Posts: 35 Location: bangalore
|
|
|
|
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 |
|
|
Marso
REXX Moderator
Joined: 13 Mar 2006 Posts: 1353 Location: Israel
|
|
|
|
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! |
|
Back to top |
|
|
|