SCT02 - How can I detail the records ?

Luis Henrique Rosa

SCT02 - How can I detail the records ?
HI,

I'm on DB2 v8 NFM. z/OS.

I REORG my SCT02 and the utility said I have 500,000 records on my
61,695 SCT02 tracks on files:

DSNV.DSNDBD.DSNDB01.SCT02.I0001.A001 43695
DSNV.DSNDBD.DSNDB01.SCT02.I0001.A002 18000

I found 2496 plans using the command: SELECT COUNT(*) FROM
SYSIBM.SYSPLAN;

I already deleted all Invalid Plans.


Anybody can help me to understand why I have much more records on
SCT02 files than plans ?





Luís Henrique
=================

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Lance D Ziegler1

Re: SCT02 - How can I detail the records ?
(in response to Luis Henrique Rosa)

Luis,
These are user-defined pagesets and are not reallocated during a reorg.  In a LISTCAT of the two clusters, check your HI-USED-RBA versus your HI-ALLOC-RBA.  You are probably only using a small % of your A001 datasets and not using A002 at all.



American Express made the following annotations on Thu Jan 03 2008 17:02:42 ------------------------------------------------------------------------------ "This message and any attachments are solely for the intended recipient and may contain confidential or privileged information. If you are not the intended recipient, any disclosure, copying, use, or distribution of the information included in this message and any attachments is prohibited. If you have received this communication in error, please notify us by reply e-mail and immediately and permanently delete this message and any attachments. Thank you." American Express a ajouté le commentaire suivant le Thu Jan 03 2008 17:02:42 Ce courrier et toute pièce jointe qu'il contient sont réservés au seul destinataire indiqué et peuvent renfermer des renseignements confidentiels et privilégiés. Si vous n'êtes pas le destinataire prévu, toute divulgation, duplication, utilisation ou distribution du courrier ou de toute pièce jointe est interdite. Si vous avez reçu cette communication par erreur, veuillez nous en aviser par courrier et détruire immédiatement le courrier et les pièces jointes. Merci. ****************************************************************************** -------------------------------------------------------------------------------
The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services

Avram Friedman

Re: SCT02 - How can I detail the records ?
(in response to Lance D Ziegler1)
SCT02 is the Skeleton Cursor Table.
It contains access path information for every static SQL statment.
The access path information for a given statement can span several SCT02
records / rows.

Perhaps a better way to estimate the size of SCT02 is to count the rows in
SYSPACKSTMT + SYSSTMT. Note the word ESTIMATE.

Some information about the contents of SCT02 can be obtained from the
Liciensed publication DB2 Diagnostis and Reference. At the risk of repeating
some old postings this manual can be obtained by transfering hlq.SDSNIVPD
(DSNDR) to your PC as a binary file. The lowlevel qualifer must be pdf. The
publication can then be accessd with ADOBE reader or equivalent.

Regards
Avram Friedman

On Thu, 3 Jan 2008 21:47:38 -0200, [login to unmask email] wrote:

>HI,
>
> I'm on DB2 v8 NFM. z/OS.
>
> I REORG my SCT02 and the utility said I have 500,000 records on my
>61,695 SCT02 tracks on files:
>
>DSNV.DSNDBD.DSNDB01.SCT02.I0001.A001 43695
>DSNV.DSNDBD.DSNDB01.SCT02.I0001.A002 18000
>
> I found 2496 plans using the command: SELECT COUNT(*) FROM
>SYSIBM.SYSPLAN;
>
> I already deleted all Invalid Plans.
>
>
> Anybody can help me to understand why I have much more records on
>SCT02 files than plans ?
>
>
>
>
>
>Lu�Henrique
>=================
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

William Favero

Re: SCT02 - How can I detail the records ?
(in response to Avram Friedman)
A little off topic, but you can also get the DB2 Diagnosis Guide and
Reference in a PDF format from PTF UK21797 for Version 7 (LY37-3740-05)
and PTF UK21798 for Version 8 (LY37-3201-04)

Willie

Avram Friedman wrote:
> SCT02 is the Skeleton Cursor Table.
> It contains access path information for every static SQL statment.
> The access path information for a given statement can span several SCT02
> records / rows.
>
> Perhaps a better way to estimate the size of SCT02 is to count the rows in
> SYSPACKSTMT + SYSSTMT. Note the word ESTIMATE.
>
> Some information about the contents of SCT02 can be obtained from the
> Liciensed publication DB2 Diagnostis and Reference. At the risk of repeating
> some old postings this manual can be obtained by transfering hlq.SDSNIVPD
> (DSNDR) to your PC as a binary file. The lowlevel qualifer must be pdf. The
> publication can then be accessd with ADOBE reader or equivalent.
>
> Regards
> Avram Friedman
>
> On Thu, 3 Jan 2008 21:47:38 -0200, [login to unmask email] wrote:
>
>
>> HI,
>>
>> I'm on DB2 v8 NFM. z/OS.
>>
>> I REORG my SCT02 and the utility said I have 500,000 records on my
>> 61,695 SCT02 tracks on files:
>>
>> DSNV.DSNDBD.DSNDB01.SCT02.I0001.A001 43695
>> DSNV.DSNDBD.DSNDB01.SCT02.I0001.A002 18000
>>
>> I found 2496 plans using the command: SELECT COUNT(*) FROM
>> SYSIBM.SYSPLAN;
>>
>> I already deleted all Invalid Plans.
>>
>>
>> Anybody can help me to understand why I have much more records on
>> SCT02 files than plans ?
>>
>>
>>
>>
>>
>> Lu�Henrique
>> =================
>>
>> The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
>>
> archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug
> under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG
> conference information, and much more. If you have not yet signed up for
> Basic Membership in IDUG, available at no cost, click on Member Services at
> http://www.idug.org/lsms
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
>
>
>

--
Willie
My DB2 blog --> http://blogs.ittoolbox.com/database/db2zos
Houston, TX, USA

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Luis Henrique Rosa

Re: SCT02 - How can I detail the records ?
(in response to William Favero)
The second part of LISTCAT.


DATA ---------- DSNV.DSNDBD.DSNDB01.SCT02.I0001.A002
IN-CAT --- CATALOG.IIMS001
HISTORY
DATASET-OWNER-----(NULL) CREATION--------2007.361
RELEASE----------------2 EXPIRATION------0000.000
ACCOUNT-INFO-----------------------------------(NULL)
PROTECTION-PSWD-----(NULL) RACF----------------(NO)
ASSOCIATIONS
CLUSTER--DSNV.DSNDBC.DSNDB01.SCT02.I0001.A002
ATTRIBUTES
KEYLEN-----------------0 AVGLRECL---------------0
BUFSPACE------------8192 CISIZE--------------4096
RKP--------------------0 MAXLRECL---------------0
EXCPEXIT----------(NULL) CI/CA----------------180
SHROPTNS(3,3) RECOVERY UNIQUE NOERASE LINEAR
NOWRITECHK NOIMBED NOREPLICAT
UNORDERED REUSE NONSPANNED
STATISTICS
REC-TOTAL--------------0 SPLITS-CI--------------0
EXCPS------------------0
REC-DELETED------------0 SPLITS-CA--------------0
EXTENTS----------------1
REC-INSERTED-----------0 FREESPACE-%CI----------0
SYSTEM-TIMESTAMP:
REC-UPDATED------------0 FREESPACE-%CA----------0
X'0000000000000000'
REC-RETRIEVED----------0 FREESPC----------------0
ALLOCATION
SPACE-TYPE------CYLINDER HI-A-RBA-------884736000
SPACE-PRI-----------1200 HI-U-RBA---------------0

SPACE-SEC------------300
VOLUME
VOLSER------------DBR208 PHYREC-SIZE---------4096
HI-A-RBA-------884736000 EXTENT-NUMBER----------1
DEVTYPE------X'3010200F' PHYRECS/TRK-----------12
HI-U-RBA-------334290944 EXTENT-TYPE--------X'40'
VOLFLAG------------PRIME TRACKS/CA-------------15
EXTENTS:
LOW-CCHH-----X'1BE80000' LOW-RBA----------------0
TRACKS-------------18000
HIGH-CCHH----X'2097000E' HIGH-RBA-------884735999


Luís H





Lance D Ziegler1 <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
03/01/2008 22:05
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: SCT02 - How can I detail the records ?







Luis,
These are user-defined pagesets and are not reallocated during a reorg. In
a LISTCAT of the two clusters, check your HI-USED-RBA versus your
HI-ALLOC-RBA. You are probably only using a small % of your A001 datasets
and not using A002 at all.



American Express made the following annotations on Thu Jan 03 2008
17:02:42
------------------------------------------------------------------------------
"This message and any attachments are solely for the intended recipient
and may contain confidential or privileged information. If you are not the
intended recipient, any disclosure, copying, use, or distribution of the
information included in this message and any attachments is prohibited. If
you have received this communication in error, please notify us by reply
e-mail and immediately and permanently delete this message and any
attachments. Thank you." American Express a ajouté le commentaire suivant
le Thu Jan 03 2008 17:02:42 Ce courrier et toute pièce jointe qu'il
contient sont réservés au seul destinataire indiqué et peuvent renfermer
des renseignements confidentiels et privilégiés. Si vous n'êtes pas le
destinataire prévu, toute divulgation, duplication, utilisation ou
distribution du courrier ou de toute pièce jointe est interdite. Si vous
avez reçu cette communication par erreur, veuillez nous en aviser par
courrier et détruire immédiatement le courrier et les pièces jointes.
Merci.
******************************************************************************
-------------------------------------------------------------------------------

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org under
the Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Luis Henrique Rosa

Re: SCT02 - How can I detail the records ?
(in response to Luis Henrique Rosa)
Thank you every one for the help.

This is the listcat of my files. The RBA shows the file A001 almost
full and A002 I'm not sure how is the correct HI-U-RBA to see.

DATA ---------- DSNV.DSNDBD.DSNDB01.SCT02.I0001.A001
IN-CAT --- CATALOG.IIMS001
HISTORY
DATASET-OWNER-----(NULL) CREATION--------1992.176
RELEASE----------------2 EXPIRATION------0000.000
ACCOUNT-INFO-----------------------------------(NULL)
PROTECTION-PSWD-----(NULL) RACF----------------(NO)
ASSOCIATIONS
CLUSTER--DSNV.DSNDBC.DSNDB01.SCT02.I0001.A001
ATTRIBUTES
KEYLEN-----------------0 AVGLRECL---------------0
BUFSPACE------------8192 CISIZE--------------4096
RKP--------------------0 MAXLRECL---------------0
EXCPEXIT----------(NULL) CI/CA----------------180
SHROPTNS(3,3) RECOVERY UNIQUE NOERASE LINEAR
NOWRITECHK NOIMBED NOREPLICAT
UNORDERED REUSE NONSPANNED
STATISTICS
REC-TOTAL--------------0 SPLITS-CI--------------0
EXCPS--------------60266
REC-DELETED------------0 SPLITS-CA--------------0
EXTENTS---------------67
REC-INSERTED-----------0 FREESPACE-%CI----------0
SYSTEM-TIMESTAMP:
REC-UPDATED------------0 FREESPACE-%CA----------0
X'C0DC796A2AA72DCA'
REC-RETRIEVED----------0 FREESPC-------1032192000
ALLOCATION
SPACE-TYPE------CYLINDER HI-A-RBA------2147696640
SPACE-PRI-----------1350 HI-U-RBA------2146390016
SPACE-SEC-------------50
VOLUME
VOLSER------------DBR201 PHYREC-SIZE---------4096
HI-A-RBA------2147696640 EXTENT-NUMBER---------67
DEVTYPE------X'3010200F' PHYRECS/TRK-----------12
HI-U-RBA------2147483648 EXTENT-TYPE--------X'40'
VOLFLAG------------PRIME TRACKS/CA-------------15
EXTENTS:
LOW-CCHH-----X'15710000' LOW-RBA----------------0
TRACKS-------------21000
HIGH-CCHH----X'1AE8000E' HIGH-RBA------1032191999
LOW-CCHH-----X'1E310000' LOW-RBA-------1032192000
TRACKS--------------1500
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
DATA ---------- DSNV.DSNDBD.DSNDB01.SCT02.I0001.A002
IN-CAT --- CATALOG.IIMS001
HISTORY
DATASET-OWNER-----(NULL) CREATION--------2007.361
RELEASE----------------2 EXPIRATION------0000.000
ACCOUNT-INFO-----------------------------------(NULL)
PROTECTION-PSWD-----(NULL) RACF----------------(NO)
ASSOCIATIONS
CLUSTER--DSNV.DSNDBC.DSNDB01.SCT02.I0001.A002
ATTRIBUTES
KEYLEN-----------------0 AVGLRECL---------------0
BUFSPACE------------8192 CISIZE--------------4096
RKP--------------------0 MAXLRECL---------------0
EXCPEXIT----------(NULL) CI/CA----------------180
SHROPTNS(3,3) RECOVERY UNIQUE NOERASE LINEAR
NOWRITECHK NOIMBED NOREPLICAT
UNORDERED REUSE NONSPANNED
STATISTICS
REC-TOTAL--------------0 SPLITS-CI--------------0
EXCPS------------------0
REC-DELETED------------0 SPLITS-CA--------------0
EXTENTS----------------1
REC-INSERTED-----------0 FREESPACE-%CI----------0
SYSTEM-TIMESTAMP:
REC-UPDATED------------0 FREESPACE-%CA----------0
X'0000000000000000'
REC-RETRIEVED----------0 FREESPC----------------0
ALLOCATION
SPACE-TYPE------CYLINDER HI-A-RBA-------884736000
SPACE-PRI-----------1200 HI-U-RBA---------------0

SPACE-SEC------------300
VOLUME
VOLSER------------DBR208 PHYREC-SIZE---------4096
HI-A-RBA-------884736000 EXTENT-NUMBER----------1
DEVTYPE------X'3010200F' PHYRECS/TRK-----------12
HI-U-RBA-------334290944 EXTENT-TYPE--------X'40'
VOLFLAG------------PRIME TRACKS/CA-------------15
EXTENTS:
LOW-CCHH-----X'1BE80000' LOW-RBA----------------0
TRACKS-------------18000
HIGH-CCHH----X'2097000E' HIGH-RBA-------884735999

Luís H.
=================

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

James Campbell

Re: SCT02 - How can I detail the records ?
(in response to Luis Henrique Rosa)
I doubt SYSPACKSTMT is relevent - it would be for SPT01, but not for SCT02.

I think you'll find a count of distinct plname, sectno, seqno in sysibm.sysstmt is closer. But
that's just from reading the description of SCT02 and its index in the Diagnosis Guide.

To make it explicit for your understanding - one row in SCT02 for each statement. I'm not
sure how executable it has to be: I doubt a DESCRIBE TABLE, although recorded in
SYSSTMT, has an entry in SCT02; DECLARE CURSOR, possibly; OPEN, definitely.

James Campbell


On 4 Jan 2008 at 15:47, Avram Friedman wrote:

> SCT02 is the Skeleton Cursor Table.
> It contains access path information for every static SQL statment.
> The access path information for a given statement can span several SCT02
> records / rows.
>
> Perhaps a better way to estimate the size of SCT02 is to count the rows in
> SYSPACKSTMT + SYSSTMT. Note the word ESTIMATE.
>
> Some information about the contents of SCT02 can be obtained from the
> Liciensed publication DB2 Diagnostis and Reference. At the risk of repeating
> some old postings this manual can be obtained by transfering hlq.SDSNIVPD
> (DSNDR) to your PC as a binary file. The lowlevel qualifer must be pdf. The
> publication can then be accessd with ADOBE reader or equivalent.
>
> Regards
> Avram Friedman
>
> On Thu, 3 Jan 2008 21:47:38 -0200, [login to unmask email] wrote:
>
> >HI,
> >
> > I'm on DB2 v8 NFM. z/OS.
> >
> > I REORG my SCT02 and the utility said I have 500,000 records on my
> >61,695 SCT02 tracks on files:
> >
> >DSNV.DSNDBD.DSNDB01.SCT02.I0001.A001 43695
> >DSNV.DSNDBD.DSNDB01.SCT02.I0001.A002 18000
> >
> > I found 2496 plans using the command: SELECT COUNT(*) FROM
> >SYSIBM.SYSPLAN;
> >
> > I already deleted all Invalid Plans.
> >
> >
> > Anybody can help me to understand why I have much more records on
> >SCT02 files than plans ?
> >
> >
> >
> >
> >
> >LuHenrique

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms