sysibm.sqlprocedures performance on UDB8.1 (Solaris)

[Andrew Werden]

I've inherited some code that calls SYSIBM.SQLPROCEDURES to validate user
provided parameters and text prior to executing a stored procedure.

This code was written on an early UDB release (V6?) and presumably ran fine.

On some UDB v8.1 systems, calls to SYSIBM.SQLPROCEDURES take in excess of 58
seconds. My actual application sproc takes < 1 second to execute. Needless
to say, this is not quite acceptable performance.

On other UDB 8.1 systems, the same SYSIBM.SQLPROCEDURES calls run
sub-second. My 'slow' system is a 4cpu Sun 3800 (with 1.2Ghz chips). Same
calls ran fast on a two CPU Sun Ultra2 (with 300 mhz chips and very little
memory).

Anyone experience problems with SYSIBM.SQLPROCEDURES? Any suggestions on how
to fix this?
SQLProcedures( hStmt=2:1, pszCatalogName=Null Pointer,
sCatalogNameByteCount=0, pszSchemaName=Null Pointer, sSchemaNameByteCount=0,
pszProcName="CONTACT_PHONE_ADD", sProcNameByteCount=-3 )
---> Time elapsed - +3.320000E-004 seconds
( StmtOut="CALL SYSIBM.SQLPROCEDURES(?,?,?,?)" )
( Package="SYSSH200 ", Section=4 )
( pInputDD=" SQLDD Information
~~~~~~~~~~~~~~~~~
numAllocVals = 4
numVals = 4
numLobVals = 0
numNonNullLobVals = 0
Default Data Type Definition = 3
Max Row Size = 0
flags = 0
codepage = 819
singleByteCP = 819
doubleByteCP = 1200
DDvalue[0] DDtype = 87, DDlen = 128,
DDcodepage = 819, DDLobLenSize = 8224
DDvalue[1] DDtype = 87, DDlen = 128,
DDcodepage = 819, DDLobLenSize = 8224
DDvalue[2] DDtype = 87, DDlen = 128,
DDcodepage = 819, DDLobLenSize = 0
DDvalue[3] DDtype = 87, DDlen = 4000,
DDcodepage = 819, DDLobLenSize = 0
There is no extension for this dataDescriptor
" )
( Row=1, iPar=1, fCType=SQL_C_CHAR, rgbValue=Null data, pcbValue=-1,
piIndicatorPtr=-1 )
( Row=1, iPar=2, fCType=SQL_C_CHAR, rgbValue=Null data, pcbValue=-1,
piIndicatorPtr=-1 )
( Row=1, iPar=3, fCType=SQL_C_CHAR, rgbValue="CONTACT_PHONE_ADD",
x'434F4E544143545F50484F4E455F414444', pcbValue=17, piIndicatorPtr=17 )
( Row=1, iPar=4, fCType=SQL_C_CHAR, rgbValue="DATATYPE='ODBC';",
x'44415441545950453D274F444243273B', pcbValue=16, piIndicatorPtr=16 )
sqlccsend( ulBytes - 466 )
sqlccsend( Handle - 00638000 )
sqlccsend( ) - rc - 0, time elapsed - +1.790000E-004
sqlccrecv( )
sqlccrecv( ulBytes - 910 ) - rc - 0, time elapsed - +5.803996E+001
( return=0 )

SQLProcedures( )
<--- SQL_SUCCESS Time elapsed - +5.804572E+001 seconds

[Serge Rielau]

Andrew,

1. You are not supposed to read from any SYSIBM object with the
exception of SYSIBM.SYSDUMMY1. These objects are not documented for good
reason.
Please use SYSCAT.ROUTINES which is the documented view.
2. Please post your query. There is little that can be done without
knowing the query.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Andrew Werden]

Not much I can do about using SYSCAT rather than SYSIBM -- its code I
inherited, rather than wrote.

I believe the query I am running is something like

call SYSIBM.SQLPROCEDURES (null, null, 'CONTACT_PHONE_ADD', 'DATATYPE=ODBC')

where contact_phone_add is the name of the actual procedure that I want this
inherited code to actually run ...

when I run this manually , it runs in 63 seconds.

thanks for your help.

/Andrew

Result set 1
--------------

PROCEDURE_CAT
PROCEDURE_SCHEM
PROCEDURE_NAME
NUM_INPUT_PARAMS NUM_OUTPUT_PARAMS NUM_RESULT_SETS REMARKS
PROCEDURE_TYPE
--------------------------------------------------------------------------
------------------------------------------------------ ---------------------
----------------------------------------------------------------------------
------------------------------- --------------------------------------------
----------------------------------------------------------------------------
-------- ---------------- ----------------- --------------- ----------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
---------- --------------
-
DB2PERFA
CONTACT_PHONE_ADD
13 3 0 -
1

1 record(s) selected.

Return Status = 0

db2 =>
"Serge Rielau" <sr*****@ca.eye-bee-m.com> wrote in message
news:bt**********@hanover.torolab.ibm.com...

Andrew,

1. You are not supposed to read from any SYSIBM object with the
exception of SYSIBM.SYSDUMMY1. These objects are not documented for good
reason.
Please use SYSCAT.ROUTINES which is the documented view.
2. Please post your query. There is little that can be done without
knowing the query.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Uh.. sorry it took me so long. This is a DB2 provided procedure.
I seem to recall some performance issues (V7 or V8??) that revolved
around resolution path for external routines (OS path not SQL!).
If there is, say a DFS or AFS dierctory earlier in the resolutuon path
then that could chew up time. It's a bit hazy, but maybe soemone else's
brain gets tickled by teh hint.
It certainly woudl explain why you see it on soem machines and not others.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Andrew Werden]

Thanks for the info.

$PATH for the ID I started the instance from is

/usr/bin:/usr/ucb:/etc:/opt/SUNWspro/bin:.:/export/home/db2inst1/sqllib/bin:
/export/home/db2inst1/sqllib/adm:/export/home/db2inst1/sqllib/misc

My application stored procedures run reasonably well. I've heard third hand
suggestions to runstats on syscat tables and then db2rbind, but I'm really
hesitant to try such a shotgun approach.

Any way to find out what base tables sysibm.sqlprocedures queries?

/andrew

"Serge Rielau" <sr*****@ca.eye-bee-m.com> wrote in message
news:bt**********@hanover.torolab.ibm.com...

Uh.. sorry it took me so long. This is a DB2 provided procedure.
I seem to recall some performance issues (V7 or V8??) that revolved
around resolution path for external routines (OS path not SQL!).
If there is, say a DFS or AFS dierctory earlier in the resolutuon path
then that could chew up time. It's a bit hazy, but maybe soemone else's
brain gets tickled by teh hint.
It certainly woudl explain why you see it on soem machines and not others.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[subaga]

The procedure has a cursor proc_el which is taking all the time. Got
this query from the db2schema.bnd file

DECLARE PROC_EL CURSOR WITH HOLD FOR
SELECT PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME,
NUM_INPUT_PARAMS, NUM_OUTPUT_PARAMS,
NUM_RESULT_SETS, REMARKS, PROCEDURE_TYPE
FROM SYSIBM.SQLPROCEDURES
WHERE PROCEDURE_SCHEM = :H00058
AND PROCEDURE_NAME LIKE :H00059 ESCAPE '\'
ORDER BY 2,3

This looks simple until we take a look at the text of the view
sysibm.sysprocedrues.
CREATE VIEW SYSIBM.SQLPROCEDURES
( PROCEDURE_CAT, PROCEDURE_SCHEM, PROCEDURE_NAME,
NUM_INPUT_PARAMS, NUM_OUTPUT_PARAMS, NUM_RESULT_SETS,
REMARKS, PROCEDURE_TYPE, NUM_INOUT_PARAMS ) AS
WITH IN ( PROCSCHEMA, PROCNAME, INCOUNT )
AS
( SELECT PROCSCHEMA, PROCNAME, COUNT(*)
FROM SYSIBM.SYSPROCPARMS
WHERE PARM_MODE = 'IN'
GROUP BY PROCSCHEMA, PROCNAME
UNION ALL
SELECT PROCSCHEMA, PROCNAME, 0
FROM SYSIBM.SYSPROCEDURES
WHERE (PROCSCHEMA, PROCNAME) NOT IN ( SELECT PROCSCHEMA, PROCNAME
FROM SYSIBM.SYSPROCPARMS
WHERE PARM_MODE = 'IN' )
),
OUT ( PROCSCHEMA, PROCNAME, OUTCOUNT )
AS
( SELECT PROCSCHEMA, PROCNAME, COUNT(*)
FROM SYSIBM.SYSPROCPARMS
WHERE PARM_MODE = 'OUT'
GROUP BY PROCSCHEMA, PROCNAME
UNION ALL
SELECT PROCSCHEMA, PROCNAME, 0
FROM SYSIBM.SYSPROCEDURES
WHERE (PROCSCHEMA, PROCNAME) NOT IN
( SELECT PROCSCHEMA, PROCNAME
FROM SYSIBM.SYSPROCPARMS
WHERE PARM_MODE = 'OUT' )
),
INOUT ( PROCSCHEMA, PROCNAME, INOUTCOUNT ) AS
( SELECT PROCSCHEMA, PROCNAME, COUNT(*)
FROM SYSIBM.SYSPROCPARMS
WHERE PARM_MODE = 'INOUT'
GROUP BY PROCSCHEMA, PROCNAME
UNION ALL
SELECT PROCSCHEMA, PROCNAME, 0
FROM SYSIBM.SYSPROCEDURES
WHERE (PROCSCHEMA, PROCNAME) NOT IN
( SELECT PROCSCHEMA,PROCNAME
FROM SYSIBM.SYSPROCPARMS
WHERE PARM_MODE = 'INOUT' )
)
SELECT DISTINCT CAST( NULL AS VARCHAR(128) ),
RTRIM(P.PROCSCHEMA),
P.PROCNAME,
I.INCOUNT,
O.OUTCOUNT,
P.RESULT_SETS,
P.REMARKS,
SMALLINT(1),
IO.INOUTCOUNT
FROM SYSIBM.SYSPROCEDURES P,
IN I,
OUT O,
INOUT IO
WHERE P.PROCSCHEMA = I.PROCSCHEMA
AND P.PROCNAME = I.PROCNAME
AND P.PROCSCHEMA = O.PROCSCHEMA
AND P.PROCNAME = O.PROCNAME
AND P.PROCSCHEMA = IO.PROCSCHEMA
AND P.PROCNAME = IO.PROCNAME

It takes time just to do a select count(*) on the view sqlprocedures.
This view looks very bad. Maybe this will work fine when the number of
stored procedures are very less. but for a database where the number
of stored procedures are in thousands and the number of parameters in
100s of thousands, is this the best that could have been written by
IBM!!! I see that just by making the subqueries correlated, the
performance improves significantly.

"Andrew Werden" <an***********@pega.com> wrote in message news:<CM********************@comcast.com>...

Thanks for the info.

$PATH for the ID I started the instance from is

/usr/bin:/usr/ucb:/etc:/opt/SUNWspro/bin:.:/export/home/db2inst1/sqllib/bin:
/export/home/db2inst1/sqllib/adm:/export/home/db2inst1/sqllib/misc

My application stored procedures run reasonably well. I've heard third hand
suggestions to runstats on syscat tables and then db2rbind, but I'm really
hesitant to try such a shotgun approach.

Any way to find out what base tables sysibm.sqlprocedures queries?

/andrew

"Serge Rielau" <sr*****@ca.eye-bee-m.com> wrote in message
news:bt**********@hanover.torolab.ibm.com...

Uh.. sorry it took me so long. This is a DB2 provided procedure.
I seem to recall some performance issues (V7 or V8??) that revolved
around resolution path for external routines (OS path not SQL!).
If there is, say a DFS or AFS dierctory earlier in the resolutuon path
then that could chew up time. It's a bit hazy, but maybe soemone else's
brain gets tickled by teh hint.
It certainly woudl explain why you see it on soem machines and not others.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab