Hi all
I while ago I posted a suggestion to the DB2 newsgroup:
http://groups.google.com/groups?hl=e...com%26rnum%3D2
(or search Google Groups for "Arrenbrecht DB2")
where I proposed a change to the internal query DB2 uses when preparing
a dynamic CALL statement. The response then was:
<cite>
In v8 CALL is a compiled statement, and we no longer require a table
scan per call.
</cite>
However, since I saw the statement reappear in our v8 performance tests,
I took the trouble to db2batch the two variants in DB2 UDB v8.1 SP 5.
Here's the results:
Non-optimized:
Buffer pool data logical reads = 59
Buffer pool index logical reads = 2
Elapsed time = 0.188
Optimized:
Buffer pool data logical reads = 2
Buffer pool index logical reads = 4
Elapsed time = 0.078
Below you find the db2batch input file which shows the changes:
--#SET PERF_DETAIL 3 ROWS_OUT 5
SELECT A.PROCSCHEMA
, A.PROCNAME
, A.PARMNAME
, A.TYPESCHEMA
, A.TYPENAME
, A.LENGTH
, A.SCALE
, A.PARM_MODE
, A.ORDINAL
, A.CODEPAGE
FROM "SYSIBM".SYSPROCPARMS A
, "SYSIBM".SYSPROCEDURES B
WHERE A.PROCSCHEMA = 'UMBNT'
AND A.PROCNAME = 'GETBYID_NUMBERCYCLE'
AND A.SPECIFICNAME = B.SPECIFICNAME
AND A.PROCSCHEMA = B.PROCSCHEMA
AND B.PARM_COUNT = 1
ORDER BY 1, 2, 9;
SELECT B.PROCSCHEMA
, B.PROCNAME
, A.PARMNAME
, A.TYPESCHEMA
, A.TYPENAME
, A.LENGTH
, A.SCALE
, A.PARM_MODE
, A.ORDINAL
, A.CODEPAGE
FROM "SYSIBM".SYSPROCPARMS A
, "SYSIBM".SYSPROCEDURES B
WHERE B.PROCSCHEMA = 'UMBNT'
AND B.PROCNAME = 'GETBYID_NUMBERCYCLE'
AND A.SPECIFICNAME = B.SPECIFICNAME
AND A.PROCSCHEMA = B.PROCSCHEMA
AND B.PARM_COUNT = 1
ORDER BY 1, 2, 9;
So, I again strongly suggest IBM make this change. In our production
system we see values for "Rows read" and "CPU" for this statement that
are way out of line.
--
Peter Arrenbrecht <ar*********@NOXXX.opus.ch>
Opus Software AG <http://www.opus.ch/>