Thanks for the link.
Interesting how IBM has determined that root cause is the execution of
catalog sproc's in fenced mode.
Doesn't take into account that sysibm.sqlprocedures stored procedure uses
sysibm.sqlprocedures view, which is hardly an 'optimal performer'.
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
"PM (pm3iinc-nospam)" <PM(pm3iinc-nospam)@sympatico.ca> wrote in message
news:W%********************@news20.bellglobal.com. ..
You can also check this one.
http://www-1.ibm.com/support/docview...f-8&lang=en+en
PM