Invalid Select Procedure
Paul Packham
We are getting a lot of exceptions generated from our Online DB2 Monitor
(Insight for DB2). The exception indicates that a rebind is needed due to
invalid select procedure.
The help on this exception states
"This exception identifies the plan containing columns (actually, rows
times columns) for which an invalid select procedure was encountered.
Invalid select
procedures are bypassed by DB2, and this can cause some performance
degradation but not any runtime failures.
A select procedure can be made invalid when DB2 maintenance is applied that
directly affects the select procedure. Plans and packages with invalid
select procedures continue to function correctly but with a small
performance degradation.
To eliminate this degradation, rebind any plan or package that causes a
nonzero value to appear. Rebinding a plan or package reenables its select
procedures. We recommend that the plans with the highest column count be
rebound first. Note that only the plan is identified, so all packages in a
plan should rebound as well as the plan itself."
I have asked IBM about this and they have relpied that a select procedure
is an internal procedure that DB2 creates for plans & packages. They also
confirm that this is not documentated.
My question is a) does anybody reading this know any more about select
procedures & b) is there a way to identify plans/packages which might be
canditates for invoking an invalid select procedure prior to applying
maintenance so we can issue the rebind and therefore avoid getting into the
situation we now find ourselves.
Thank you,
Paul Packham
The Post Office
Richard A Yevich
Do not worry about it. There are these internal procedure called S-procs,
I-procs and U-procs that are basically little modules that speed up
repetitive processing, like an INSERT or UPDATE in a loop for the same
columns. There are certain situations where an S-proc can be created during
the bind process and ready for use. IF invalidated, a bind should correct.
But if not, things still work. Think of it as if DB2, either ahead of time
or on-the-fly builds a small little module to move the columns from DB2 to
your program (vice-versa) instead of using the normal stage-2 logic. If
that little module fails, you get the normal method of movement of those
columns cross memory.
Richard Yevich
+=====+======+
Information Technology Consulting, Data Modeling, Advanced Education
RYC Inc. USA: 1-800-664-2421 Int'l: 1-305-361-8585 Fax: 1-512-476-3930
Web: <http://www.ryci.com> Email: in...@ryci.com Offices: USA and Europe
DB2 Family and Oracle Specialists - Parallel Technologies
VLDB and Data Sharing Technologies (specialties SAP, Peoplesoft)
Authors of "DB2 Answers" by Osborne-McGraw Hill, May, 1999 release