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.

re: DB2 V8 Performance

ChetWest schrieb:
[color=blue]
> 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.
>[/color]

Did you reorganize the table? Are indexes the same?

Regards,
--
Burkhard Schultheis
Tele Data Electronic, Wagnerstr. 10, D-76448 Durmersheim
Email: schultheis@tde-online.de
Phone: +49-7245-9287-21, Fax: +49-7245-9287-30

re: DB2 V8 Performance

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

re: DB2 V8 Performance

Burkhard Schultheis <schultheis@tde-online.de> wrote in message news:<2ilv7oFo1039U1@uni-berlin.de>...[color=blue]
> ChetWest schrieb:
>[color=green]
> > 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.
> >[/color]
>
> Did you reorganize the table? Are indexes the same?
>
> Regards,[/color]

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

Chet

re: DB2 V8 Performance

"ChetWest" <chetwest@yahoo.com> wrote in message
news:47c93e56.0406080534.32239ae9@posting.google.c om...[color=blue]
> 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.[/color]

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.

re: DB2 V8 Performance

Is it a large table?
Have you done RUNSTATS?

ChetWest wrote:
[color=blue]
> 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.[/color]

--
Anton Versteeg
IBM Certified DB2 Specialist
IBM Netherlands

re: DB2 V8 Performance

Anton Versteeg <anton_versteeg@nnll.ibm.com> wrote in message news:<ca6ft0$vlu$1@sp15en20.hursley.ibm.com>...[color=blue]
> Is it a large table?
> Have you done RUNSTATS?
>
> ChetWest wrote:
>[color=green]
> > 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.[/color][/color]

Almost 500,000 records. And we have done runstats.

re: DB2 V8 Performance

Serge Rielau <srielau@ca.eye-be-em.com> wrote in message news:<ca4l4t$s47$1@hanover.torolab.ibm.com>...[color=blue]
> 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[/color]

Where can I find the optimization level?

Chet

re: DB2 V8 Performance

"Mark A" <ma@switchboard.net> wrote in message news:<olpxc.161$N07.74138@news.uswest.net>...[color=blue]
> "ChetWest" <chetwest@yahoo.com> wrote in message
> news:47c93e56.0406080534.32239ae9@posting.google.c om...[color=green]
> > 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.[/color]
>
> 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.[/color]


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

Chet

re: DB2 V8 Performance

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:[color=blue]
> Serge Rielau <srielau@ca.eye-be-em.com> wrote in message news:<ca4l4t$s47$1@hanover.torolab.ibm.com>...
>[color=green]
>>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.[/color][/color]
[color=blue][color=green]
>>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[/color]
>
>
> Where can I find the optimization level?
>
> Chet[/color]

re: DB2 V8 Performance

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 <badamache@2muchspam.yahoo.com> wrote in message news:<ca7e3s$a4o$1@hanover.torolab.ibm.com>...[color=blue]
> 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:[color=green]
> > Serge Rielau <srielau@ca.eye-be-em.com> wrote in message news:<ca4l4t$s47$1@hanover.torolab.ibm.com>...
> >[color=darkred]
> >>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.[/color][/color]
>[color=green][color=darkred]
> >>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[/color]
> >
> >
> > Where can I find the optimization level?
> >
> > Chet[/color][/color]

re: DB2 V8 Performance

Looks like you're running more or less with defaults.

ChetWest wrote:[color=blue]
> 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 <badamache@2muchspam.yahoo.com> wrote in message news:<ca7e3s$a4o$1@hanover.torolab.ibm.com>...
>[color=green]
>>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:
>>[color=darkred]
>>>Serge Rielau <srielau@ca.eye-be-em.com> wrote in message news:<ca4l4t$s47$1@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.[/color]
>>
>>
>>[color=darkred]
>>>>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[/color][/color][/color]