Performance improvement for Stored Proc resolution

[Peter Arrenbrecht Opus]

Hello IBM

I think that one could improve the performance of DB2 UDB v7.2's
stored procedure resolution. Here's what DB2 normally does:

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 =
'GETBYNAME_NUMBERCYCLE' AND A.SPECIFICNAME = B.SPECIFICNAME AND
A.PROCSCHEMA =
B.PROCSCHEMA AND B.PARM_COUNT = 2 ORDER BY 1, 2, 9

If you explain this statement, however, you will see that it results
in a scan of the table SYSPROCPARMS. Simply rephrasing the query as
follows will eliminate the scan (note how the name now restricts
SYSPROCEDURES so the index is used):

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 = 'GETBYNAME_NUMBERCYCLE'
AND A.SPECIFICNAME = B.SPECIFICNAME
AND A.PROCSCHEMA = B.PROCSCHEMA
AND B.PARM_COUNT = 2
ORDER BY 1, 2, 9

While on most systems, the actual performance improvement will be
negligible, it does matter on systems with lots of procedures.

It also does not artificially degrade the ratio of rows read vs rows
selected, which improves this value as a health indicator.

Regards,
Peter Arrenbrecht
Opus Software AG

[Sean McKeough]

In v8 CALL is a compiled statement, and we no longer require a table
scan per call.

Peter Arrenbrecht Opus wrote:

Hello IBM

I think that one could improve the performance of DB2 UDB v7.2's
stored procedure resolution. Here's what DB2 normally does:

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 =
'GETBYNAME_NUMBERCYCLE' AND A.SPECIFICNAME = B.SPECIFICNAME AND
A.PROCSCHEMA =
B.PROCSCHEMA AND B.PARM_COUNT = 2 ORDER BY 1, 2, 9

If you explain this statement, however, you will see that it results
in a scan of the table SYSPROCPARMS. Simply rephrasing the query as
follows will eliminate the scan (note how the name now restricts
SYSPROCEDURES so the index is used):

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 = 'GETBYNAME_NUMBERCYCLE'
AND A.SPECIFICNAME = B.SPECIFICNAME
AND A.PROCSCHEMA = B.PROCSCHEMA
AND B.PARM_COUNT = 2
ORDER BY 1, 2, 9

While on most systems, the actual performance improvement will be
negligible, it does matter on systems with lots of procedures.

It also does not artificially degrade the ratio of rows read vs rows
selected, which improves this value as a health indicator.

Regards,
Peter Arrenbrecht
Opus Software AG