467,185 Members | 1,207 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,185 developers. It's quick & easy.

DB2 - Decode Compiled SQL Statements

wfs
Hi All,

does anyone know how to decode a compiled SQL statement (db2 z/os 7.1 -
skeleton cursor table)

I'm trying to find all bound sql statements where the columns in a
'where' clause are of different sizes

where a = b

a = char(10)

b = char(20)

or where a or b are not indexed etc etc.

Somewhere in there must be a table & column identifier....

TIA

Bill
Mar 7 '06 #1
  • viewed: 4401
Share:
2 Replies
I used to track this down using the EXPLAIN facility. I'd look for
predicates that should use indexes and investigate why they didn't.

Locating non length-matching predicates may or may not be of value
because changes to the optimizer over a period of many years have
improved its ability to handle these differences.

To fully automate what you state you want to do will require parsing the
actual SQL statements, analyzing their column use and attributes in the
predicates, and determining if an index would be appropriate to use. Of
course, you need to determine if it's better to use the index or perform
a scan. You'll also need to determine how to handle multiple column
indexes. By the time you get finished, you will have written a fair
portion of the optimizer.

Many years ago, I did this by hand for a single SQL statement that
wouldn't use an index that should have been used. After many, many hours
of investigation, we discovered that the performance analysis tool that
was being used couldn't spot that the predicate's data area definition
in the program didn't match the length as defined in the table and index.
Phil Sherman

wfs wrote:
Hi All,

does anyone know how to decode a compiled SQL statement (db2 z/os 7.1 -
skeleton cursor table)

I'm trying to find all bound sql statements where the columns in a
'where' clause are of different sizes

where a = b

a = char(10)

b = char(20)

or where a or b are not indexed etc etc.

Somewhere in there must be a table & column identifier....

TIA

Bill

Mar 7 '06 #2
wfs
Thanks

Bill

"Phil Sherman" <ps******@ameritech.net> wrote in message
news:XO******************@newssvr24.news.prodigy.n et...
I used to track this down using the EXPLAIN facility. I'd look for
predicates that should use indexes and investigate why they didn't.

Locating non length-matching predicates may or may not be of value because
changes to the optimizer over a period of many years have improved its
ability to handle these differences.

To fully automate what you state you want to do will require parsing the
actual SQL statements, analyzing their column use and attributes in the
predicates, and determining if an index would be appropriate to use. Of
course, you need to determine if it's better to use the index or perform a
scan. You'll also need to determine how to handle multiple column indexes.
By the time you get finished, you will have written a fair portion of the
optimizer.

Many years ago, I did this by hand for a single SQL statement that
wouldn't use an index that should have been used. After many, many hours
of investigation, we discovered that the performance analysis tool that
was being used couldn't spot that the predicate's data area definition in
the program didn't match the length as defined in the table and index.
Phil Sherman

wfs wrote:
Hi All,

does anyone know how to decode a compiled SQL statement (db2 z/os
7.1 - skeleton cursor table)

I'm trying to find all bound sql statements where the columns in a
'where' clause are of different sizes

where a = b

a = char(10)

b = char(20)

or where a or b are not indexed etc etc.

Somewhere in there must be a table & column identifier....

TIA

Bill

Mar 9 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by N | last post: by
reply views Thread by richasaraf | last post: by
1 post views Thread by anonymous | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.