473,320 Members | 2,029 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.

DB2 V8 Performance

Db2 gurus...

I am fairly knew to DB2. We just migrated to it from Oracle (not
sure why other than money). Anyways, we have a situation where one of
our test databases is performing very badly. It is setup just like
production and has all the same config settings, data and objects as
development. The application has the ability to update multiple
records at once. (Example: Update 5 employee records to all include
"Mr." on their salutation.) On Production and Development this runs
in seconds. But on our test instance/database it takes up to 15
minutes. The user session stays in a status of "compiling" and says
it is building dynamic SQL. The user process on unix (AIX) hits its
maximum CPU usage, but there is very little I/O. Once the status
switches to "executing", it runs fairly quick (seconds). On a very
few occaisions it will execute for a minute then error out with query
too complex.

We turned on logging on the application side...but it just said it
was waiting on the server. We tried increasing the SQL Statement
Heap, Application Heap and Pacakage Cache to no avail. I tried
tracing the session...but I don't understand the formatted or flow
output. The only other thing I have noticed is that it always says
the current package is SYSSH200 statement section # 5. I have no idea
what that means. I am at a loss for what to do. (I could figure this
out in Oracle...)

Your Help appreciated.
Nov 12 '05 #1
11 5552
ChetWest schrieb:
Db2 gurus...

I am fairly knew to DB2. We just migrated to it from Oracle (not
sure why other than money). Anyways, we have a situation where one of
our test databases is performing very badly. It is setup just like
production and has all the same config settings, data and objects as
development. The application has the ability to update multiple
records at once. (Example: Update 5 employee records to all include
"Mr." on their salutation.) On Production and Development this runs
in seconds. But on our test instance/database it takes up to 15
minutes. The user session stays in a status of "compiling" and says
it is building dynamic SQL. The user process on unix (AIX) hits its
maximum CPU usage, but there is very little I/O. Once the status
switches to "executing", it runs fairly quick (seconds). On a very
few occaisions it will execute for a minute then error out with query
too complex.


Did you reorganize the table? Are indexes the same?

Regards,
--
Burkhard Schultheis
Tele Data Electronic, Wagnerstr. 10, D-76448 Durmersheim
Email: sc********@tde-online.de
Phone: +49-7245-9287-21, Fax: +49-7245-9287-30
Nov 12 '05 #2
What's the optimization level? If it's higher than on the production adn
dev server that may explain why compile time is higher.
(although from seconds to 15 minutes is a bit extreme).
Another thought maybe locking.
Are other connections doing DDL against the same database.
Unliek Oracle, DB2 DDL is under transaction control. This means if
someone does DDL and doesn't commit teh compiler might be twiddling it's
thumbs waiting on soem catalog lock in SYSTABLES or so....

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #3
Burkhard Schultheis <sc********@tde-online.de> wrote in message news:<2i************@uni-berlin.de>...
ChetWest schrieb:
Db2 gurus...

I am fairly knew to DB2. We just migrated to it from Oracle (not
sure why other than money). Anyways, we have a situation where one of
our test databases is performing very badly. It is setup just like
production and has all the same config settings, data and objects as
development. The application has the ability to update multiple
records at once. (Example: Update 5 employee records to all include
"Mr." on their salutation.) On Production and Development this runs
in seconds. But on our test instance/database it takes up to 15
minutes. The user session stays in a status of "compiling" and says
it is building dynamic SQL. The user process on unix (AIX) hits its
maximum CPU usage, but there is very little I/O. Once the status
switches to "executing", it runs fairly quick (seconds). On a very
few occaisions it will execute for a minute then error out with query
too complex.


Did you reorganize the table? Are indexes the same?

Regards,


Yes...we did reorgs on the tables and indexes...

Chet
Nov 12 '05 #4
"ChetWest" <ch******@yahoo.com> wrote in message
news:47**************************@posting.google.c om...
Db2 gurus...

I am fairly knew to DB2. We just migrated to it from Oracle (not
sure why other than money). Anyways, we have a situation where one of
our test databases is performing very badly. It is setup just like
production and has all the same config settings, data and objects as
development. The application has the ability to update multiple
records at once. (Example: Update 5 employee records to all include
"Mr." on their salutation.) On Production and Development this runs
in seconds. But on our test instance/database it takes up to 15
minutes. The user session stays in a status of "compiling" and says
it is building dynamic SQL. The user process on unix (AIX) hits its
maximum CPU usage, but there is very little I/O. Once the status
switches to "executing", it runs fairly quick (seconds). On a very
few occaisions it will execute for a minute then error out with query
too complex.

We turned on logging on the application side...but it just said it
was waiting on the server. We tried increasing the SQL Statement
Heap, Application Heap and Pacakage Cache to no avail. I tried
tracing the session...but I don't understand the formatted or flow
output. The only other thing I have noticed is that it always says
the current package is SYSSH200 statement section # 5. I have no idea
what that means. I am at a loss for what to do. (I could figure this
out in Oracle...)

Your Help appreciated.


Check to see if you have a lock contention problem on the catalog. Is your
LOCKTIMEOUT set to -1 (which means that the application will wait forever
for locks to be released). If you set it to 30, it will wait 30 seconds and
abend with a -911 if it is still waiting for lock.

All do a snapshot for locks.

Lock contention on the catalog can occur if someone else is doing DDL or
certain utilities are being run.
Nov 12 '05 #5
Is it a large table?
Have you done RUNSTATS?

ChetWest wrote:
Db2 gurus...

I am fairly knew to DB2. We just migrated to it from Oracle (not
sure why other than money). Anyways, we have a situation where one of
our test databases is performing very badly. It is setup just like
production and has all the same config settings, data and objects as
development. The application has the ability to update multiple
records at once. (Example: Update 5 employee records to all include
"Mr." on their salutation.) On Production and Development this runs
in seconds. But on our test instance/database it takes up to 15
minutes. The user session stays in a status of "compiling" and says
it is building dynamic SQL. The user process on unix (AIX) hits its
maximum CPU usage, but there is very little I/O. Once the status
switches to "executing", it runs fairly quick (seconds). On a very
few occaisions it will execute for a minute then error out with query
too complex.

We turned on logging on the application side...but it just said it
was waiting on the server. We tried increasing the SQL Statement
Heap, Application Heap and Pacakage Cache to no avail. I tried
tracing the session...but I don't understand the formatted or flow
output. The only other thing I have noticed is that it always says
the current package is SYSSH200 statement section # 5. I have no idea
what that means. I am at a loss for what to do. (I could figure this
out in Oracle...)

Your Help appreciated.


--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

Nov 12 '05 #6
Anton Versteeg <an************@nnll.ibm.com> wrote in message news:<ca**********@sp15en20.hursley.ibm.com>...
Is it a large table?
Have you done RUNSTATS?

ChetWest wrote:
Db2 gurus...

I am fairly knew to DB2. We just migrated to it from Oracle (not
sure why other than money). Anyways, we have a situation where one of
our test databases is performing very badly. It is setup just like
production and has all the same config settings, data and objects as
development. The application has the ability to update multiple
records at once. (Example: Update 5 employee records to all include
"Mr." on their salutation.) On Production and Development this runs
in seconds. But on our test instance/database it takes up to 15
minutes. The user session stays in a status of "compiling" and says
it is building dynamic SQL. The user process on unix (AIX) hits its
maximum CPU usage, but there is very little I/O. Once the status
switches to "executing", it runs fairly quick (seconds). On a very
few occaisions it will execute for a minute then error out with query
too complex.

We turned on logging on the application side...but it just said it
was waiting on the server. We tried increasing the SQL Statement
Heap, Application Heap and Pacakage Cache to no avail. I tried
tracing the session...but I don't understand the formatted or flow
output. The only other thing I have noticed is that it always says
the current package is SYSSH200 statement section # 5. I have no idea
what that means. I am at a loss for what to do. (I could figure this
out in Oracle...)

Your Help appreciated.


Almost 500,000 records. And we have done runstats.
Nov 12 '05 #7
Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<ca**********@hanover.torolab.ibm.com>...
What's the optimization level? If it's higher than on the production adn
dev server that may explain why compile time is higher.
(although from seconds to 15 minutes is a bit extreme).
Another thought maybe locking.
Are other connections doing DDL against the same database.
Unliek Oracle, DB2 DDL is under transaction control. This means if
someone does DDL and doesn't commit teh compiler might be twiddling it's
thumbs waiting on soem catalog lock in SYSTABLES or so....

Cheers
Serge


Where can I find the optimization level?

Chet
Nov 12 '05 #8
"Mark A" <ma@switchboard.net> wrote in message news:<ol*****************@news.uswest.net>...
"ChetWest" <ch******@yahoo.com> wrote in message
news:47**************************@posting.google.c om...
Db2 gurus...

I am fairly knew to DB2. We just migrated to it from Oracle (not
sure why other than money). Anyways, we have a situation where one of
our test databases is performing very badly. It is setup just like
production and has all the same config settings, data and objects as
development. The application has the ability to update multiple
records at once. (Example: Update 5 employee records to all include
"Mr." on their salutation.) On Production and Development this runs
in seconds. But on our test instance/database it takes up to 15
minutes. The user session stays in a status of "compiling" and says
it is building dynamic SQL. The user process on unix (AIX) hits its
maximum CPU usage, but there is very little I/O. Once the status
switches to "executing", it runs fairly quick (seconds). On a very
few occaisions it will execute for a minute then error out with query
too complex.

We turned on logging on the application side...but it just said it
was waiting on the server. We tried increasing the SQL Statement
Heap, Application Heap and Pacakage Cache to no avail. I tried
tracing the session...but I don't understand the formatted or flow
output. The only other thing I have noticed is that it always says
the current package is SYSSH200 statement section # 5. I have no idea
what that means. I am at a loss for what to do. (I could figure this
out in Oracle...)

Your Help appreciated.


Check to see if you have a lock contention problem on the catalog. Is your
LOCKTIMEOUT set to -1 (which means that the application will wait forever
for locks to be released). If you set it to 30, it will wait 30 seconds and
abend with a -911 if it is still waiting for lock.

All do a snapshot for locks.

Lock contention on the catalog can occur if someone else is doing DDL or
certain utilities are being run.

Good thought...but we have our LOCKTIMEOUT set to 60.

Chet
Nov 12 '05 #9
These database manager config parms:

Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = NO

These database config parms:

Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1

And these registers and parameters from the SQL Reference manual:

CURRENT QUERY OPTIMIZATION (i.e. db2 set current query optimization=5 )
CURRENT DEGREE

ChetWest wrote:
Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<ca**********@hanover.torolab.ibm.com>...
What's the optimization level? If it's higher than on the production adn
dev server that may explain why compile time is higher.
(although from seconds to 15 minutes is a bit extreme).
Another thought maybe locking.
Are other connections doing DDL against the same database. Unliek Oracle, DB2 DDL is under transaction control. This means if
someone does DDL and doesn't commit teh compiler might be twiddling it's
thumbs waiting on soem catalog lock in SYSTABLES or so....

Cheers
Serge

Where can I find the optimization level?

Chet


Nov 12 '05 #10
My Parms look like this

Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1

Maximum query degree of parallelism (MAX_QUERYDEGREE) = -1
Enable intra-partition parallelism (INTRA_PARALLEL) = YES

Chet

Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<ca**********@hanover.torolab.ibm.com>...
These database manager config parms:

Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = NO

These database config parms:

Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1

And these registers and parameters from the SQL Reference manual:

CURRENT QUERY OPTIMIZATION (i.e. db2 set current query optimization=5 )
CURRENT DEGREE

ChetWest wrote:
Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<ca**********@hanover.torolab.ibm.com>...
What's the optimization level? If it's higher than on the production adn
dev server that may explain why compile time is higher.
(although from seconds to 15 minutes is a bit extreme).
Another thought maybe locking.
Are other connections doing DDL against the same database.Unliek Oracle, DB2 DDL is under transaction control. This means if
someone does DDL and doesn't commit teh compiler might be twiddling it's
thumbs waiting on soem catalog lock in SYSTABLES or so....

Cheers
Serge

Where can I find the optimization level?

Chet

Nov 12 '05 #11
Looks like you're running more or less with defaults.

ChetWest wrote:
My Parms look like this

Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1

Maximum query degree of parallelism (MAX_QUERYDEGREE) = -1
Enable intra-partition parallelism (INTRA_PARALLEL) = YES

Chet

Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<ca**********@hanover.torolab.ibm.com>...
These database manager config parms:

Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = NO

These database config parms:

Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1

And these registers and parameters from the SQL Reference manual:

CURRENT QUERY OPTIMIZATION (i.e. db2 set current query optimization=5 )
CURRENT DEGREE

ChetWest wrote:
Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<ca**********@hanover.torolab.ibm.com>...
What's the optimization level? If it's higher than on the production adn
dev server that may explain why compile time is higher.
(although from seconds to 15 minutes is a bit extreme).
Another thought maybe locking.
Are other connections doing DDL against the same database.

Unliek Oracle, DB2 DDL is under transaction control. This means if
someone does DDL and doesn't commit teh compiler might be twiddling it's
thumbs waiting on soem catalog lock in SYSTABLES or so....

Cheers
Serge
Where can I find the optimization level?

Chet


Nov 12 '05 #12

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: Fred | last post by:
Has anyone a link or any information comparing c and c++ as far as execution speed is concerned? Signal Processing algorithms would be welcome... Thanks Fred
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: 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...
7
by: Michael D. Ober | last post by:
When calling Enqueue, the internal array may need to be reallocated. My question is by how much? In the old MFC array classes, you could tell MFC how many additional elements to add to the array...
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...
1
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
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
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.