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. 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
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
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
"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.
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
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.
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
"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
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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |