473,725 Members | 2,006 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

DB2 v8 performance improvement for Stored Proc resolution

Hi all

I while ago I posted a suggestion to the DB2 newsgroup:

http://groups.google.com/groups?hl=e...com%26rnum%3D2
(or search Google Groups for "Arrenbrech t DB2")

where I proposed a change to the internal query DB2 uses when preparing
a dynamic CALL statement. The response then was:

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

However, since I saw the statement reappear in our v8 performance tests,
I took the trouble to db2batch the two variants in DB2 UDB v8.1 SP 5.
Here's the results:

Non-optimized:

Buffer pool data logical reads = 59
Buffer pool index logical reads = 2
Elapsed time = 0.188

Optimized:

Buffer pool data logical reads = 2
Buffer pool index logical reads = 4
Elapsed time = 0.078
Below you find the db2batch input file which shows the changes:

--#SET PERF_DETAIL 3 ROWS_OUT 5
SELECT A.PROCSCHEMA
, A.PROCNAME
, A.PARMNAME
, A.TYPESCHEMA
, A.TYPENAME
, A.LENGTH
, A.SCALE
, A.PARM_MODE
, A.ORDINAL
, A.CODEPAGE
FROM "SYSIBM".SYSPRO CPARMS A
, "SYSIBM".SYSPRO CEDURES B
WHERE A.PROCSCHEMA = 'UMBNT'
AND A.PROCNAME = 'GETBYID_NUMBER CYCLE'
AND A.SPECIFICNAME = B.SPECIFICNAME
AND A.PROCSCHEMA = B.PROCSCHEMA
AND B.PARM_COUNT = 1
ORDER BY 1, 2, 9;

SELECT B.PROCSCHEMA
, B.PROCNAME
, A.PARMNAME
, A.TYPESCHEMA
, A.TYPENAME
, A.LENGTH
, A.SCALE
, A.PARM_MODE
, A.ORDINAL
, A.CODEPAGE
FROM "SYSIBM".SYSPRO CPARMS A
, "SYSIBM".SYSPRO CEDURES B
WHERE B.PROCSCHEMA = 'UMBNT'
AND B.PROCNAME = 'GETBYID_NUMBER CYCLE'
AND A.SPECIFICNAME = B.SPECIFICNAME
AND A.PROCSCHEMA = B.PROCSCHEMA
AND B.PARM_COUNT = 1
ORDER BY 1, 2, 9;
So, I again strongly suggest IBM make this change. In our production
system we see values for "Rows read" and "CPU" for this statement that
are way out of line.

--
Peter Arrenbrecht <ar*********@NO XXX.opus.ch>
Opus Software AG <http://www.opus.ch/>
Nov 12 '05 #1
3 2598
Peter,

I will pass your suggestion along. Also if this is a serious issue for
you there are ways for support to refresh the view as you indicate on
your system.

I do have one question though (which gears to what Sean stated).
A normal CALL statement doesn't browse this view. SYSPROCPARMS is never
used by the DB2 engine at all for procedure resolution, only for
dependency checking (such as when you drop a distinct type).
Seemingly there must be some client-interface (CLI, JDBC, ???) which
does some extra work. If you could post the repro scenario that would be
valuable (or pass it along when you open a PMR to get your instance
improved)

Cheers
Serge
Nov 12 '05 #2
Serge

It's the following stored procedure which we call very frequently using
a dynamic CALL statement in an ODBC connection (prepare, then execute).
We do not, however, keep the statement prepared across calls at this
point. Here's the call:

call umbnt.getbyid_n umbercycle( ? )

And here's the procedure:

CREATE PROCEDURE GETBYID_NUMBERC YCLE( in pID int )
LANGUAGE SQL
MODIFIES SQL DATA
DYNAMIC RESULT SETS 1
NOT DETERMINISTIC
BEGIN
DECLARE vLast INT;
DECLARE vStep INT;
DECLARE vInit INT;
DECLARE vNew INT;

DECLARE res CURSOR WITH RETURN FOR
SELECT vNew as NUMCYC_LASTVALU E FROM SYSIBM.SYSDUMMY 1;

UPDATE UMBNT.NUMBERCYC LE
SET NUMCYC_GUIDSTAM P = VARCHAR(CURRENT TIMESTAMP)
WHERE NUMCYC_ID = pID;

SELECT NUMCYC_LASTVALU E, NUMCYC_STEPPING , NUMCYC_STARTVAL UE
INTO vLast, vStep, vInit
FROM UMBNT.NUMBERCYC LE
WHERE NUMCYC_ID = pID;

IF vLast IS NULL THEN
SET vNew = VALUE(vInit,1);
ELSE
SET vNew = VALUE(vLast,vIn it) + VALUE(vStep,1);
END IF;

UPDATE UMBNT.NUMBERCYC LE
SET NUMCYC_LASTVALU E = vNew
WHERE NUMCYC_ID = pID;

COMMIT WORK;

OPEN res;
END

The client is a v7 client. The database is a v8 db.

Regards,
peo
Serge Rielau wrote:
Peter,

I will pass your suggestion along. Also if this is a serious issue for
you there are ways for support to refresh the view as you indicate on
your system.

I do have one question though (which gears to what Sean stated).
A normal CALL statement doesn't browse this view. SYSPROCPARMS is never
used by the DB2 engine at all for procedure resolution, only for
dependency checking (such as when you drop a distinct type).
Seemingly there must be some client-interface (CLI, JDBC, ???) which
does some extra work. If you could post the repro scenario that would be
valuable (or pass it along when you open a PMR to get your instance
improved)

Cheers
Serge

Nov 12 '05 #3
> The client is a v7 client
Aha!
Nov 12 '05 #4

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

Similar topics

1
3163
by: Bruce Hendry | last post by:
In a nutshell, I have a stored proc that takes appx 15 minutes when it uses a read-only database, and about 2 minutes when the database is not read-only. Details: SQL Server 7 The stored proc exists on a writable database on the same server. It stuffs a subset of about 20000 rows into a temp table and repeatedly updates the
8
3698
by: Jack | last post by:
I have a test database that I have built in a 3 partition (and 3 node) environment. I have defined all the tables so they have the same partition key. The tables (7 of them) form a hierarchical arrangement. The data is all bogus, so I using the generate_unique function to come up with a partitioning key for each record. This is all done through a stored proc with will insert about 90 records in the heirarchy. The behavior that I am...
6
2166
by: AC Slater | last post by:
Hi All, Out of nowhere my udb system (v8) performance has went terrible. Its gotten about 10x worse, (some tests that used to take 2 seconds to run now take 20)... I'm not sure what happened. I did reorg/runstat/rebind on everything, no luck... I'm not sure what to do next...? Any recommendations on something to try to start narrowing down the possible problem. Things I've tried:
1
1894
by: Peter Arrenbrecht Opus | last post by:
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 =
9
1592
by: wdwedw | last post by:
I have included all the source codes in the attached MyTest.zip (http://www.codeguru.com/forum/attachment.php?attachmentid=11218) There are three projects: VBTestCOM project is a apartment threaded DLL, it has one function doing a stored procedure call. This DLL will be called from C++ multithread. C++ test project is a ATL multithreaded DLL, it just simple created multithread, and call VBTestCom's doSPCall function in each thread.
10
1932
by: db2udbgirl | last post by:
I have coded a stored procedure which is planned to run only once in production to process historic data from DB2 8.2 database. It accesses data from a partitioned table and based on some condition it would insert the result to another table and update the same row (using updatable cursor). Right now it takes around 2 minutes to process 10000 rows of data so when I did a test run against 3 Million row it took around 30 hours to complete....
6
2939
by: teddysnips | last post by:
In a system I'm maintaining there is a Stored Procedure called dbo.MyStoredProcedure. I didn't create this - it was created by a developer who has now left. I don't know how the object came by its "dbo." prefix, but I think he created it in QA. Anyway, there were some performance issues (it was taking between 4 and 10 seconds to complete) so I copied the SQL into a QA window and it consistently ran in under 1 second. So I created a...
0
2559
by: ravindrag | last post by:
Hi, I am getting error SQL1131N during sqlj.install_jar(...). There is no useful message in the diag.log (even with diag level 4). I am giving the diag.log entries at the end of this posting (would have been ideal if there was an option to attach the file). command: db2 call sqlj.install_jar('file:/home/xyz/abc.jar','def.abc') response: SQL1131N DARI (Stored Procedure) process has been terminated abnormally. SQLSTATE=38503
0
1987
by: mirandacascade | last post by:
Questions toward the bottom of the post. Situation is this: 1) Access 97 2) SQL Server 2000 3) The Access app: a) sets up pass-thru query b) .SQL property of querydef is a string, the contents of which comprise the call to a stored proc
0
8886
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9401
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9255
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9168
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
6701
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6010
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4509
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4780
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2632
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.