473,320 Members | 1,719 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

SQLprocedures() API performance on UDB 8.1

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.SQLPROCEDURES(?,?,?,?) stored procedure. Stored procedure references
non-indexed schema views. Query performance degrades based on the number of
procedures in the catalog and the overall system activity. Have seen that it
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.sqlprocedures, and incorrectly posted in reference to
SQLprocedurecolumns() api)), but I'm kind of frustrated by this.
Nov 12 '05 #1
7 2025
Have you set the registry variable DB2_STPROC_LOOKUP_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" <an****@bpm.no-ip.net> wrote in message
news:bI********************@comcast.com...
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.SQLPROCEDURES(?,?,?,?) stored procedure. Stored procedure references non-indexed schema views. Query performance degrades based on the number of procedures in the catalog and the overall system activity. Have seen that it 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.sqlprocedures, and incorrectly posted in reference to
SQLprocedurecolumns() api)), but I'm kind of frustrated by this.

Nov 12 '05 #2
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:
Have you set the registry variable DB2_STPROC_LOOKUP_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" <an****@bpm.no-ip.net> wrote in message
news:bI********************@comcast.com...
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.SQLPROCEDURES(?,?,?,?) stored procedure. Stored procedure


references
non-indexed schema views. Query performance degrades based on the number


of
procedures in the catalog and the overall system activity. Have seen that


it
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.sqlprocedures, and incorrectly posted in reference to
SQLprocedurecolumns() api)), but I'm kind of frustrated by this.


Nov 12 '05 #3
I found API docs at

http://webdocs.caspur.it/ibm/web/udb....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(handle->sqlhstmt(),
dbNamePtr, dbNameLen, ownerNamePtr, ownerNameLen,
(SQLCHAR*)objectName.data(), SQL_NTS);

"Sean McKeough" <mc******@nospam.ca.ibm.com> wrote in message
news:bu**********@hanover.torolab.ibm.com...
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:
Have you set the registry variable DB2_STPROC_LOOKUP_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" <an****@bpm.no-ip.net> wrote in message
news:bI********************@comcast.com...
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.SQLPROCEDURES(?,?,?,?) stored procedure. Stored procedure


references
non-indexed schema views. Query performance degrades based on the number


of
procedures in the catalog and the overall system activity. Have seen that

it
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 secondsrunning against databases with similiar number of stored procedures in
catalog.

My apology for this repost (first posted specifically in reference to
sysibm.sqlprocedures, and incorrectly posted in reference to
SQLprocedurecolumns() api)), but I'm kind of frustrated by this.


Nov 12 '05 #4
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

Nov 12 '05 #6
Tried solutions suggested by IBM.

Still does not work.

Forcing CLI behavior to emulate v7 did indeed eliminate the call to
SYSIBM.SQLPROCEDURES 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.sqlprocedures 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)@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

Nov 12 '05 #7
Not sure but maybe the db2ocat tool. can help.

PM

"Andrew Werden" <an***********@pega.com> a écrit dans le message de
news:LZ********************@comcast.com...
Tried solutions suggested by IBM.

Still does not work.

Forcing CLI behavior to emulate v7 did indeed eliminate the call to
SYSIBM.SQLPROCEDURES 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.sqlprocedures 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)@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


Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

25
by: Brian Patterson | last post by:
I have noticed in the book of words that hasattr works by calling getattr and raising an exception if no such attribute exists. If I need the value in any case, am I better off using getattr...
12
by: serge | last post by:
I have an SP that is big, huge, 700-800 lines. I am not an expert but I need to figure out every possible way that I can improve the performance speed of this SP. In the next couple of weeks I...
6
by: teedilo | last post by:
We have an application with a SQL Server 2000 back end that is fairly database intensive -- lots of fairly frequent queries, inserts, updates -- the gamut. The application does not make use of...
5
by: Andrew Werden | last post by:
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?)...
5
by: Scott | last post by:
I have a customer that had developed an Access97 application to track their business information. The application grew significantly and they used the Upsizing Wizard to move the tables to SQL...
115
by: Mark Shelor | last post by:
I've encountered a troublesome inconsistency in the C-language Perl extension I've written for CPAN (Digest::SHA). The problem involves the use of a static array within a performance-critical...
13
by: bjarne | last post by:
Willy Denoyette wrote; > ... it > was not the intention of StrousTrup to the achieve the level of efficiency > of C when he invented C++, ... Ahmmm. It was my aim to match the performance...
13
by: Bern McCarty | last post by:
I have run an experiment to try to learn some things about floating point performance in managed C++. I am using Visual Studio 2003. I was hoping to get a feel for whether or not it would make...
1
by: jvn | last post by:
I am experiencing a particular problem with performance counters. I have created a set of classes, that uses System.Diagnostics.PerformanceCounter to increment custom performance counters (using...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.