Experiences, advice on DSN_STATEMNT_TABLE

Tonmoy Dasgupta

Experiences, advice on DSN_STATEMNT_TABLE
DB2 V7.1 on z/OS 1.04

I am seeking advice, user experiences or technical references (not
manuals) on the usage of the DSN_STATEMNT_TABLE, particularly in
measuring SQL cost differences across rebinds.

I have experimented with a few package rebinds and some SQLs came with
the following values in the table

COST_CATEGORY = A
PROCMS = 2147483647
PROCSU = 2147483647

The PROCMS value, estimated processor cost in milliseconds, was the
highest allowable and translates to 24.8 days. Yet a SQL with those
figures took only 0MIN 55.00SEC SRB. Am I interpreting PROCMS
incorrectly? Or are those figures aberrations? How widespread are
aberrations (ballpark 1%, 10%, ...)? If a lot of expensive SQLs, the
ones you more want to keep a tab on, come with the highest allowable
values in this column, then you cannot measure any differences across
rebinds.

Any perspective would be appreciated.

Tonmoy Dasgupta,
DBA, DIS,
State of Arkansas.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

K T

Re: Experiences, advice on DSN_STATEMNT_TABLE
(in response to Tonmoy Dasgupta)
Tonmoy,
If you get a chance look thru the archive for this list and I think
DSN_STATMNT_TABLE numbers are not to be trusted, as sometimes queries
showing a relatively low number would run into many minutes and vice
versa like in this case of your query too. I would rather look at
explain and try to figure out things like access path index usage and
such. I have had some not too great experiences in past were in you see
low numbers and think looks great but the query would end up running for
practically ever, I would like to wait and see what Terry Purcell has to
say though.

HTH

-KT

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
Sent: Wednesday, December 21, 2005 10:35 AM
To: [login to unmask email]
Subject: [DB2-L] Experiences, advice on DSN_STATEMNT_TABLE

DB2 V7.1 on z/OS 1.04

I am seeking advice, user experiences or technical references (not
manuals) on the usage of the DSN_STATEMNT_TABLE, particularly in
measuring SQL cost differences across rebinds.

I have experimented with a few package rebinds and some SQLs came with
the following values in the table

COST_CATEGORY = A
PROCMS = 2147483647
PROCSU = 2147483647

The PROCMS value, estimated processor cost in milliseconds, was the
highest allowable and translates to 24.8 days. Yet a SQL with those
figures took only 0MIN 55.00SEC SRB. Am I interpreting PROCMS
incorrectly? Or are those figures aberrations? How widespread are
aberrations (ballpark 1%, 10%, ...)? If a lot of expensive SQLs, the
ones you more want to keep a tab on, come with the highest allowable
values in this column, then you cannot measure any differences across
rebinds.

Any perspective would be appreciated.

Tonmoy Dasgupta,
DBA, DIS,
State of Arkansas.

------------------------------------------------------------------------
---------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that
page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

-----------------------------------------
Use of email is inherently insecure. Confidential information,
including account information, and personally identifiable information,
should not be transmitted via email, or email attachment. In no event
shall Citizens or any of its affiliates accept any responsibility for
the loss, use or misuse of any information including confidential
information, which is sent to Citizens or its affiliates via email, or
email attachment. Citizens does not guarantee the accuracy of any email
or email attachment, that an email will be received by Citizens or that
Citizens will respond to any email.

This email message is confidential and/or privileged. It is to be used
by the intended recipient only. Use of the information contained in
this email by anyone other than the intended recipient is strictly
prohibited. If you have received this message in error, please notify
the sender immediately and promptly destroy any record of this email.


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Gerald Hodge

Re: Experiences, advice on DSN_STATEMNT_TABLE
(in response to K T)
The DSN_STATEMNT_TABLE values ten to be consistent with themselves. Across
various release of DB2 they have had problems predicting the "actual" cost
of running an SQL statement. However, they are consistent from run to run
where you have not altered the Version of DB2 or the hardware. This means
they have predictive value in the sense they can predict a cost going up or
down.

If you go to our website www.hlstechnologies.com and to the download page
you will find a white paper contrasting V6, V7 and V8 Optimizer choices for
simple SQL changes. We also provide the data, structures, DBRM, etc. to
allow you to recreate the tests on your machine.

If we can be of assistance please call us at 888-494-9019.

Gerald Hodge
HLS Technologies, Inc.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Tonmoy Dasgupta
Sent: Wednesday, December 21, 2005 7:35 AM
To: [login to unmask email]
Subject: [DB2-L] Experiences, advice on DSN_STATEMNT_TABLE

DB2 V7.1 on z/OS 1.04

I am seeking advice, user experiences or technical references (not
manuals) on the usage of the DSN_STATEMNT_TABLE, particularly in
measuring SQL cost differences across rebinds.

I have experimented with a few package rebinds and some SQLs came with
the following values in the table

COST_CATEGORY = A
PROCMS = 2147483647
PROCSU = 2147483647

The PROCMS value, estimated processor cost in milliseconds, was the
highest allowable and translates to 24.8 days. Yet a SQL with those
figures took only 0MIN 55.00SEC SRB. Am I interpreting PROCMS
incorrectly? Or are those figures aberrations? How widespread are
aberrations (ballpark 1%, 10%, ...)? If a lot of expensive SQLs, the
ones you more want to keep a tab on, come with the highest allowable
values in this column, then you cannot measure any differences across
rebinds.

Any perspective would be appreciated.

Tonmoy Dasgupta,
DBA, DIS,
State of Arkansas.

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm