By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,227 Members | 1,026 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,227 IT Pros & Developers. It's quick & easy.

DB2 - Decode Compiled SQL Statements

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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.