DB2 v8 performance improvement for Stored Proc resolution

[Peter Arrenbrecht]

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/>

[Serge Rielau]

Peter,

I will pass your suggestion along. Also if this is a serious issue for
you there are ways for support to refresh the view as you indicate on
your system.

I do have one question though (which gears to what Sean stated).
A normal CALL statement doesn't browse this view. SYSPROCPARMS is never
used by the DB2 engine at all for procedure resolution, only for
dependency checking (such as when you drop a distinct type).
Seemingly there must be some client-interface (CLI, JDBC, ???) which
does some extra work. If you could post the repro scenario that would be
valuable (or pass it along when you open a PMR to get your instance
improved)

Cheers
Serge

[Peter Arrenbrecht]

Serge

It's the following stored procedure which we call very frequently using
a dynamic CALL statement in an ODBC connection (prepare, then execute).
We do not, however, keep the statement prepared across calls at this
point. Here's the call:

call umbnt.getbyid_numbercycle( ? )

And here's the procedure:

CREATE PROCEDURE GETBYID_NUMBERCYCLE( in pID int )
LANGUAGE SQL
MODIFIES SQL DATA
DYNAMIC RESULT SETS 1
NOT DETERMINISTIC
BEGIN
DECLARE vLast INT;
DECLARE vStep INT;
DECLARE vInit INT;
DECLARE vNew INT;

DECLARE res CURSOR WITH RETURN FOR
SELECT vNew as NUMCYC_LASTVALUE FROM SYSIBM.SYSDUMMY1;

UPDATE UMBNT.NUMBERCYCLE
SET NUMCYC_GUIDSTAMP = VARCHAR(CURRENT TIMESTAMP)
WHERE NUMCYC_ID = pID;

SELECT NUMCYC_LASTVALUE, NUMCYC_STEPPING, NUMCYC_STARTVALUE
INTO vLast, vStep, vInit
FROM UMBNT.NUMBERCYCLE
WHERE NUMCYC_ID = pID;

IF vLast IS NULL THEN
SET vNew = VALUE(vInit,1);
ELSE
SET vNew = VALUE(vLast,vInit) + VALUE(vStep,1);
END IF;

UPDATE UMBNT.NUMBERCYCLE
SET NUMCYC_LASTVALUE = vNew
WHERE NUMCYC_ID = pID;

COMMIT WORK;

OPEN res;
END

The client is a v7 client. The database is a v8 db.

Regards,
peo
Serge Rielau wrote:

Peter,

I will pass your suggestion along. Also if this is a serious issue for
you there are ways for support to refresh the view as you indicate on
your system.

I do have one question though (which gears to what Sean stated).
A normal CALL statement doesn't browse this view. SYSPROCPARMS is never
used by the DB2 engine at all for procedure resolution, only for
dependency checking (such as when you drop a distinct type).
Seemingly there must be some client-interface (CLI, JDBC, ???) which
does some extra work. If you could post the repro scenario that would be
valuable (or pass it along when you open a PMR to get your instance
improved)

Cheers
Serge

[Serge Rielau]

> The client is a v7 client
Aha!