Invalid select procedure and maintenance

[login to unmask email]

Invalid select procedure and maintenance
Hi all

Can someone explain QISTCOLS. and or ifcid 224

Tom



[login to unmask email]

Re: Invalid select procedure and maintenance
(in response to ttaylor@CHUBB.COM)
I found this when we first went to Version 5

5.3.3.5 Considerations for Rebinding Certain Plans and Packages

SQL queries in applications that are bound in DB2 Version 3 or later
releases, automatically undergo an internal optimization that could improve
their performance,
depending on the number of rows fetched. The SQL statements most likely to
show a decrease in elapsed time are those that select a large number of
columns and
fetch hundreds of rows.

This optimization can be bypassed if service applied to DB2 has invalidated
it. In this case, you can rebind the appropriate plans to once again take
advantage of the
internal optimization. To determine if the optimization has been
invalidated
by DB2 maintenance, check the BYPASS COL field in the Miscellaneous section
of the DB2
PM statistics report (field QISTCOLS in IFCID 0002). If this field contains
anything but zero, get the names of the plans or packages from IFCID 0224.

To start a trace for IFCID 0224, you must use one of the
installation-defined trace classes (30-32):

-START TRACE (PERFM) CLASS(30) IFCID(224)

IFCID 0224 is written whenever the invalidated optimization is detected.
The
record contains the plan names or package names; rebind those plans or
packages to pick
up the optimization again.


Depending on number of packages/plans may be easier to re-bind all.

Good luck.



[login to unmask email]

Re: Invalid select procedure and maintenance
(in response to pjackson@SUMMITBANK.COM)
Thanks









[login to unmask email] on 12/16/99 03:29:44 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>








To: [login to unmask email]

cc: (bcc: Thomas G Taylor/ChubbMail)



Subject: Re: Invalid select procedure and maintenance








I found this when we first went to Version 5

5.3.3.5 Considerations for Rebinding Certain Plans and Packages

SQL queries in applications that are bound in DB2 Version 3 or later
releases, automatically undergo an internal optimization that could improve
their performance,
depending on the number of rows fetched. The SQL statements most likely to
show a decrease in elapsed time are those that select a large number of
columns and
fetch hundreds of rows.

This optimization can be bypassed if service applied to DB2 has invalidated
it. In this case, you can rebind the appropriate plans to once again take
advantage of the
internal optimization. To determine if the optimization has been
invalidated
by DB2 maintenance, check the BYPASS COL field in the Miscellaneous section
of the DB2
PM statistics report (field QISTCOLS in IFCID 0002). If this field contains
anything but zero, get the names of the plans or packages from IFCID 0224.

To start a trace for IFCID 0224, you must use one of the
installation-defined trace classes (30-32):

-START TRACE (PERFM) CLASS(30) IFCID(224)

IFCID 0224 is written whenever the invalidated optimization is detected.
The
record contains the plan names or package names; rebind those plans or
packages to pick
up the optimization again.


Depending on number of packages/plans may be easier to re-bind all.

Good luck.