SQLprocedures() API performance on UDB 8.1

**Bill Pellett** · Nov 12 '05, 06:14 AM

Re: SQLprocedures() API performance on UDB 8.1

Have you set the registry variable DB2_STPROC_LOOK UP_FIRST=ON. This won't
fix the no index problem, but DB2 will look in the system catalog table
before looking in the fenced and non-fenced directories first for the stored
procedure.

"andrew" <andrew@bpm.n o-ip.net> wrote in message
news:bImdnW_lEb pv9Zjd4p2dnA@co mcast.com...[color=blue]
> Does anyone know if there are any plans to address SQLprocedures() API
> performance in UDB 8.1 client/server?
>
> 8.1 SQLprocedures() API call is implemented through
> SYSIBM.SQLPROCE DURES(?,?,?,?) stored procedure. Stored procedure[/color]
references[color=blue]
> non-indexed schema views. Query performance degrades based on the number[/color]
of[color=blue]
> procedures in the catalog and the overall system activity. Have seen that[/color]
it[color=blue]
> is 'sometimes' possible to partially remediate performance issues by
> reorging , runstats and db2rbind, but remediation does not always succeed,
> and at best, mediocre performance is achieved (.05 seconds).
>
> Currently seeing sqlprocedures() API calls taking betwen 3 and 58 seconds
> running against databases with similiar number of stored procedures in
> catalog.
>
> My apology for this repost (first posted specifically in reference to
> sysibm.sqlproce dures, and incorrectly posted in reference to
> SQLprocedurecol umns() api)), but I'm kind of frustrated by this.
>
>[/color]

**Sean McKeough** · Nov 12 '05, 06:14 AM

Re: SQLprocedures() API performance on UDB 8.1

That registry var is depricated in v8 (since call is always a compiled
statement).

Where is the SQLprocedure api documented (it is not in the api ref, and
I can't find it in our codebase)?

Bill Pellett wrote:[color=blue]
> Have you set the registry variable DB2_STPROC_LOOK UP_FIRST=ON. This won't
> fix the no index problem, but DB2 will look in the system catalog table
> before looking in the fenced and non-fenced directories first for the stored
> procedure.
>
> "andrew" <andrew@bpm.n o-ip.net> wrote in message
> news:bImdnW_lEb pv9Zjd4p2dnA@co mcast.com...
>[color=green]
>>Does anyone know if there are any plans to address SQLprocedures() API
>>performance in UDB 8.1 client/server?
>>
>>8.1 SQLprocedures() API call is implemented through
>>SYSIBM.SQLPRO CEDURES(?,?,?,? ) stored procedure. Stored procedure[/color]
>
> references
>[color=green]
>>non-indexed schema views. Query performance degrades based on the number[/color]
>
> of
>[color=green]
>>procedures in the catalog and the overall system activity. Have seen that[/color]
>
> it
>[color=green]
>>is 'sometimes' possible to partially remediate performance issues by
>>reorging , runstats and db2rbind, but remediation does not always succeed,
>>and at best, mediocre performance is achieved (.05 seconds).
>>
>>Currently seeing sqlprocedures() API calls taking betwen 3 and 58 seconds
>>running against databases with similiar number of stored procedures in
>>catalog.
>>
>>My apology for this repost (first posted specifically in reference to
>>sysibm.sqlpro cedures, and incorrectly posted in reference to
>>SQLprocedurec olumns() api)), but I'm kind of frustrated by this.
>>
>>[/color]
>
>
>[/color]

**andrew** · Nov 12 '05, 06:14 AM

Re: SQLprocedures() API performance on UDB 8.1

I found API docs at

http://webdocs.caspur.it/ibm/web/udb-6.1/db2l0/sqll1347.htm#HDRFNPRCS

Specification: DB2 CLI 2.1 ODBC 1.0

SQLProcedures() returns a list of procedure names that have been registered
at the server, and which match the specified search pattern.

The information is returned in an SQL result set, which can be retrieved
using the same functions that are used to process a result set generated by
a query.

Syntax

SQLRETURN SQLProcedures (
SQLHSTMT StatementHandle , /* hstmt */
SQLCHAR FAR *CatalogName, /* szProcCatalog */
SQLSMALLINT NameLength1, /* cbProcCatalog */
SQLCHAR FAR *SchemaName, /* szProcSchema */
SQLSMALLINT NameLength2, /* cbProcSchema */
SQLCHAR FAR *ProcName, /* szProcName */
SQLSMALLINT NameLength3); /* cbProcName */
Code that I've inherited allows run-time insertion of procedure name based
on user input, and uses sqlprocedures() call to validate the procedure
existence and determine if it is a procedure or function

SQLRETURN rc = SQLProcedures(h andle->sqlhstmt(),
dbNamePtr, dbNameLen, ownerNamePtr, ownerNameLen,
(SQLCHAR*)objec tName.data(), SQL_NTS);

"Sean McKeough" <mckeough@nospa m.ca.ibm.com> wrote in message
news:bu6tia$k9r $1@hanover.toro lab.ibm.com...[color=blue]
> That registry var is depricated in v8 (since call is always a compiled
> statement).
>
> Where is the SQLprocedure api documented (it is not in the api ref, and
> I can't find it in our codebase)?
>
> Bill Pellett wrote:[color=green]
> > Have you set the registry variable DB2_STPROC_LOOK UP_FIRST=ON. This[/color][/color]
won't[color=blue][color=green]
> > fix the no index problem, but DB2 will look in the system catalog table
> > before looking in the fenced and non-fenced directories first for the[/color][/color]
stored[color=blue][color=green]
> > procedure.
> >
> > "andrew" <andrew@bpm.n o-ip.net> wrote in message
> > news:bImdnW_lEb pv9Zjd4p2dnA@co mcast.com...
> >[color=darkred]
> >>Does anyone know if there are any plans to address SQLprocedures() API
> >>performance in UDB 8.1 client/server?
> >>
> >>8.1 SQLprocedures() API call is implemented through
> >>SYSIBM.SQLPRO CEDURES(?,?,?,? ) stored procedure. Stored procedure[/color]
> >
> > references
> >[color=darkred]
> >>non-indexed schema views. Query performance degrades based on the number[/color]
> >
> > of
> >[color=darkred]
> >>procedures in the catalog and the overall system activity. Have seen[/color][/color][/color]
that[color=blue][color=green]
> >
> > it
> >[color=darkred]
> >>is 'sometimes' possible to partially remediate performance issues by
> >>reorging , runstats and db2rbind, but remediation does not always[/color][/color][/color]
succeed,[color=blue][color=green][color=darkred]
> >>and at best, mediocre performance is achieved (.05 seconds).
> >>
> >>Currently seeing sqlprocedures() API calls taking betwen 3 and 58[/color][/color][/color]
seconds[color=blue][color=green][color=darkred]
> >>running against databases with similiar number of stored procedures in
> >>catalog.
> >>
> >>My apology for this repost (first posted specifically in reference to
> >>sysibm.sqlpro cedures, and incorrectly posted in reference to
> >>SQLprocedurec olumns() api)), but I'm kind of frustrated by this.
> >>
> >>[/color]
> >
> >
> >[/color][/color]

**PM \(pm3iinc-nospam\)** · Nov 12 '05, 06:15 AM

Re: SQLprocedures() API performance on UDB 8.1

You can also check this one.

http://www-1.ibm.com/support/docview.wss?rs=71&context=SSEPGG&uid=swg21153193&loc=en_US&cs=utf-8&lang=en+en

PM

**Andrew Werden** · Nov 12 '05, 06:15 AM

Re: SQLprocedures() API performance on UDB 8.1

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.sqlproce dures stored procedure uses
sysibm.sqlproce dures view, which is hardly an 'optimal performer'.

CREATE VIEW SYSIBM.SQLPROCE DURES

( PROCEDURE_CAT, PROCEDURE_SCHEM , PROCEDURE_NAME,

NUM_INPUT_PARAM S, NUM_OUTPUT_PARA MS, NUM_RESULT_SETS ,

REMARKS, PROCEDURE_TYPE, NUM_INOUT_PARAM S ) AS

WITH IN ( PROCSCHEMA, PROCNAME, INCOUNT )

AS

( SELECT PROCSCHEMA, PROCNAME, COUNT(*)

FROM SYSIBM.SYSPROCP ARMS

WHERE PARM_MODE = 'IN'

GROUP BY PROCSCHEMA, PROCNAME

UNION ALL

SELECT PROCSCHEMA, PROCNAME, 0

FROM SYSIBM.SYSPROCE DURES

WHERE (PROCSCHEMA, PROCNAME) NOT IN ( SELECT PROCSCHEMA, PROCNAME

FROM SYSIBM.SYSPROCP ARMS

WHERE PARM_MODE = 'IN' )

),

OUT ( PROCSCHEMA, PROCNAME, OUTCOUNT )

AS

( SELECT PROCSCHEMA, PROCNAME, COUNT(*)

FROM SYSIBM.SYSPROCP ARMS

WHERE PARM_MODE = 'OUT'

GROUP BY PROCSCHEMA, PROCNAME

UNION ALL

SELECT PROCSCHEMA, PROCNAME, 0

FROM SYSIBM.SYSPROCE DURES

WHERE (PROCSCHEMA, PROCNAME) NOT IN ( SELECT PROCSCHEMA, PROCNAME

FROM SYSIBM.SYSPROCP ARMS

WHERE PARM_MODE = 'OUT' ) ),

INOUT ( PROCSCHEMA, PROCNAME, INOUTCOUNT ) AS

( SELECT PROCSCHEMA, PROCNAME, COUNT(*)

FROM SYSIBM.SYSPROCP ARMS

WHERE PARM_MODE = 'INOUT'

GROUP BY PROCSCHEMA, PROCNAME

UNION ALL

SELECT PROCSCHEMA, PROCNAME, 0

FROM SYSIBM.SYSPROCE DURES

WHERE (PROCSCHEMA, PROCNAME) NOT IN ( SELECT PROCSCHEMA, PROCNAME

FROM SYSIBM.SYSPROCP ARMS

WHERE PARM_MODE = 'INOUT' )

)

SELECT DISTINCT CAST( NULL AS VARCHAR(128) ),

RTRIM(P.PROCSCH EMA),

P.PROCNAME,

I.INCOUNT,

O.OUTCOUNT,

P.RESULT_SETS,

P.REMARKS,

SMALLINT(1),

IO.INOUTCOUNT

FROM SYSIBM.SYSPROCE DURES 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)@sympati co.ca> wrote in message
news:W%3Ob.2456 0$1K1.399681@ne ws20.bellglobal .com...[color=blue]
> You can also check this one.
>
>[/color]
http://www-1.ibm.com/support/docview...f-8&lang=en+en[color=blue]
>
> PM
>
>[/color]

**Andrew Werden** · Nov 12 '05, 06:17 AM

Re: SQLprocedures() API performance on UDB 8.1

Tried solutions suggested by IBM.

Still does not work.

Forcing CLI behavior to emulate v7 did indeed eliminate the call to
SYSIBM.SQLPROCE DURES stored procedure, but replaced it with a direct query
to the SQLPROCEDURES view.

It seems that the fundamental problem in my case is not the actual cost of
running the procedure, but the poor implementation of the sqlprocedures view
in the UDB 8 schema catalog.

On a small database with few procedures, I imagine that the cost of running
a fenced stored procedure was quite high. I'm dealing with a database with
several thousand procedures. When I ran v8 sqlprocedures () api call
[sysibm.sqlproce dures proc] on an unloaded system against a database with
only 5 stored procedures, the cost is negligible. When I run either api
version of sqlprocedures against a database with thousands of stored
procedures, the cost of the view alone is a killer and the procedure's
overhead is negligible.

Any other suggestions?

"PM (pm3iinc-nospam)" <PM(pm3iinc-nospam)@sympati co.ca> wrote in message
news:W%3Ob.2456 0$1K1.399681@ne ws20.bellglobal .com...[color=blue]
> You can also check this one.
>
>[/color]
http://www-1.ibm.com/support/docview...f-8&lang=en+en[color=blue]
>
> PM
>
>[/color]

**PM \(pm3iinc-nospam\)** · Nov 12 '05, 06:17 AM

Re: SQLprocedures() API performance on UDB 8.1

Not sure but maybe the db2ocat tool. can help.

PM

"Andrew Werden" <andrew.werden@ pega.com> a écrit dans le message de
news:LZmdnVWW-ouC_ZDdRVn-hg@comcast.com. ..[color=blue]
> Tried solutions suggested by IBM.
>
> Still does not work.
>
> Forcing CLI behavior to emulate v7 did indeed eliminate the call to
> SYSIBM.SQLPROCE DURES stored procedure, but replaced it with a direct query
> to the SQLPROCEDURES view.
>
> It seems that the fundamental problem in my case is not the actual cost of
> running the procedure, but the poor implementation of the sqlprocedures[/color]
view[color=blue]
> in the UDB 8 schema catalog.
>
> On a small database with few procedures, I imagine that the cost of[/color]
running[color=blue]
> a fenced stored procedure was quite high. I'm dealing with a database with
> several thousand procedures. When I ran v8 sqlprocedures () api call
> [sysibm.sqlproce dures proc] on an unloaded system against a database with
> only 5 stored procedures, the cost is negligible. When I run either api
> version of sqlprocedures against a database with thousands of stored
> procedures, the cost of the view alone is a killer and the procedure's
> overhead is negligible.
>
> Any other suggestions?
>
>
>
>
>
> "PM (pm3iinc-nospam)" <PM(pm3iinc-nospam)@sympati co.ca> wrote in message
> news:W%3Ob.2456 0$1K1.399681@ne ws20.bellglobal .com...[color=green]
> > You can also check this one.
> >
> >[/color]
>[/color]
http://www-1.ibm.com/support/docview...f-8&lang=en+en[color=blue][color=green]
> >
> > PM
> >
> >[/color]
>
>[/color]

SQLprocedures() API performance on UDB 8.1

SQLprocedures() API performance on UDB 8.1

Comment

Comment

Comment

Comment

Comment

Comment

Comment