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

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:[color=blue]
> 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
>
>[/color]

Thanks

Bill

"Phil Sherman" <psherman@ameritech.net> wrote in message
news:XOgPf.20752$tb3.9823@newssvr24.news.prodigy.n et...[color=blue]
>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:[color=green]
>> 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[/color][/color]