DSNTIAUL access path question

Liao Wensheng

DSNTIAUL access path question
Esteemed Masters:

We have a DSNTIAUL job, had previously been running fine, but last night it ran more than 10 hours is not over, the job's IO and CPU activity is very low. This is a PARTITION TABLESPACE, it has a CLUSTERED PI index and an NPI index, a total of 30 million records, but later found that DB2 selected the NPI index incorrectly, after RUNSTATS the job run for 20 minutes, but my wonder is why DB2 optimizer select INDEX SCAN but not TABLE SCAN, the job similar to the following:


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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Roy Boxwell

Re: DSNTIAUL access path question
(in response to Liao Wensheng)
as DSNTIAUL can accept SQL for its "unload script" it is highly sensitive
to SQL predicates and host variables - There was an interesting thread a
while ago about rebinding your DSNTIAUL to REOPT(ALWAYS) to give it a
performance boost if using things like CURRENT DATE etc.....Bottom line is
we need the actual SQL input - not the DB2 sample job...

Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Wilson Liao <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
26.01.2011 23:44
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] DSNTIAUL access path question






Esteemed Masters:

We have a DSNTIAUL job, had previously been running fine, but last night
it ran more than 10 hours is not over, the job's IO and CPU activity is
very low. This is a PARTITION TABLESPACE, it has a CLUSTERED PI index and
an NPI index, a total of 30 million records, but later found that DB2
selected the NPI index incorrectly, after RUNSTATS the job run for 20
minutes, but my wonder is why DB2 optimizer select INDEX SCAN but not
TABLE SCAN, the job similar to the following:


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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2
information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's Listserv


_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Terry Purcell

Re: DSNTIAUL access path question
(in response to Roy Boxwell)
Wilson,

What you are saying is that RUNSTATS solved the problem, which seems to indicate that there was either an inconsistency between the index and tablespace statistics OR the statistics were stale.

So the question then needs to be asked whether you should be revisiting your statistics collection frequency, and using the DB2 supplied stored procedure DSNACCOR (or DSNACCOX in DB2 10) may be of interest.

My gut feeling is that any other type of deep dive to try to get to the root cause may be difficult now that you have overcome the problem - unless you have before/after explain information, statistics history etc.

Regards
Terry Purcell

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Liao Wensheng

Re: DSNTIAUL access path question
(in response to Terry Purcell)
Hello Roy
Thank you for your reply, in fact, in our SYSIN contains only OWNER.TABLE, does not contain any predicates and ORDER BY, etc.

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Liao Wensheng

Re: DSNTIAUL access path question
(in response to Liao Wensheng)
Hello Terry

Because the project is mainly used for system data migration, after the completion of migration, the project set aside, the operation of the entire project life cycle not more than 12 hours, subject to time window constraints, and basically had no time to do RUNSTATS.

Very regret not collecting statistics before.

Regards

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Dave Nance

Re: DSNTIAUL access path question
(in response to Liao Wensheng)
   Due to the stats at execution time. Running SQL via
DSNTIAUL/tep2/tep4/dsntiad/qmf/etc... is dynamic, so access path is decided
at run time. Since you state that you collected stats and it ran in an
acceptable amount of time, I would say that something in the catalog had changed
prior to your long running job that caused an inefficient path to be chosen.
   To ensure this doesnt happen again, I would suggest that you find what recent
change caused the optimizer to pick the access path it used. Then whenever you
do that type of function again, ensure that you collect stats afterwards.
 
David Nance




________________________________
From: Wilson Liao <[login to unmask email]>
To: [login to unmask email]
Sent: Wed, January 26, 2011 5:44:50 PM
Subject: [DB2-L] DSNTIAUL access path question

Esteemed Masters:

We have a DSNTIAUL job, had previously been running fine, but last night it ran
more than 10 hours is not over, the job's IO and CPU activity is very low. This
is a PARTITION TABLESPACE, it has a CLUSTERED PI index and an NPI index, a total
of 30 million records, but later found that DB2 selected the NPI index
incorrectly, after RUNSTATS  the job run for 20 minutes, but my wonder is why
DB2 optimizer select INDEX SCAN but not TABLE SCAN, the job similar to the
following:


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

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *  http://IDUG.ORG/NA
*
*  Your only source for independent, unbiased, and trusted DB2 information.  *
**  The most DB2 technical sessions of any conference
**  Access IBM experts and developers
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the
home of IDUG's Listserv





_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Koen Vannisselroy

Re: DSNTIAUL access path question
(in response to Dave Nance)
Wilson,

I don't know on what version of db2 you are, but if you unload the entire table you better use teh unload untitilty.
I think it was introduced in version 8. It is much faster than dsntiaul.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv