Help with understanding DB2 and CPU TIME Usage

Lizette Koehler

Help with understanding DB2 and CPU TIME Usage
List -

I am just a simple z/OS Sysprog. Running DB2 V10 and z/OS V2.1

I am looking for a general understanding of why or when a DB2 SQL (Batch for
instance) will time out with a S322 abend. The JES2 class they are running in
has a maximum of 60 mins. The job clock time is 69 mins, but it used a solid 60
CPU Minutes.

I am trying to understand why a query might do this. Yes I know this is more
like a pie-in-the-sky question.

The DBAs have reviewed the SQL with DB2 TOOLS and have told me the SQL
statements are running really fast. It is just the amount of data causing the
issue.

What can I do other than raising the jobclass time to 2-4 hours, can I look at
or review or change anything to help this query run?

Should I just give it an unlimited time class? Is this a normal function for
DB2 SQL to just use a lot of CPU?

Do you have Queries running using a huge amount of CPU time?


Thanks

Lizette Koehler
statistics: A precise and logical method for stating a half-truth inaccurately

Philip Sevetson

Help with understanding DB2 and CPU TIME Usage
(in response to Lizette Koehler)
**please note my email address change**

Lizette,

1) DB2 SQL is generally optimized based on the statistics provided, using a lot of different estimators to fill in the gaps. Some of it runs very very fast and comes back very quickly. However, that depends on the target(s) being indexed in a useful way, or small, and no really complicated SQL causing a lot of looping (joins and correlated subqueries and the like) or I/O.
2) If you're joining multiple tables with large amounts of data (and "large amounts" is one of those "it depends" terms), I/O can quickly scale up. CPU, too, but to a lesser extent. ("lesser" is one of those "it depends" terms, too.)
3) Bad or just complex SQL code can scale up just as quickly.
4) The worst example of this that I recall was from the early 90's, at Aetna, and this was told to me a couple of years later by a couple of the DBAs from that time. They had a query which would never end, no matter how long they ran it (up to several days). They wound up putting the data in its own subsystem (and maybe on its own hardware?), and letting it sit and run as long as it needed, to get an answer. No, I don't know how long it took.
5) It's probably worth trying the query on a small subset of the data in a test environment, and see how long it takes to come back. Then scale up a bit, run the test again, and calculate the difference in the run times.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f



-----Original Message-----
From: Lizette Koehler [mailto:[login to unmask email]
Sent: Tuesday, February 28, 2017 2:25 PM
To: [login to unmask email]
Subject: [DB2-L] - Help with understanding DB2 and CPU TIME Usage

List -

I am just a simple z/OS Sysprog. Running DB2 V10 and z/OS V2.1

I am looking for a general understanding of why or when a DB2 SQL (Batch for
instance) will time out with a S322 abend. The JES2 class they are running in has a maximum of 60 mins. The job clock time is 69 mins, but it used a solid 60 CPU Minutes.

I am trying to understand why a query might do this. Yes I know this is more like a pie-in-the-sky question.

The DBAs have reviewed the SQL with DB2 TOOLS and have told me the SQL statements are running really fast. It is just the amount of data causing the issue.

What can I do other than raising the jobclass time to 2-4 hours, can I look at or review or change anything to help this query run?

Should I just give it an unlimited time class? Is this a normal function for
DB2 SQL to just use a lot of CPU?

Do you have Queries running using a huge amount of CPU time?


Thanks

Lizette Koehler
statistics: A precise and logical method for stating a half-truth inaccurately


-----End Original Message-----

**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Daniel Luksetich

Help with understanding DB2 and CPU TIME Usage
(in response to Lizette Koehler)
Lizette,
When you have a query that is using a lot of CPU relative to elapse time, in
your case 60 of 69 minutes, and the DBAs I am assuming are suggesting that
the access path is acceptable. Then I would look at the query for things
like nested table expressions and embedded functions and expressions. If
they have coded these within the statement then that could be an issue. This
won't show up in a DB2 explain. Take a look at page 3 of this article.

http://www.enterprisesystemsmedia.com/it-management/db2-for-z-os-sql-perform
ance-choices

Given the limited in formation this is only a guess but that's a lot of CPU.

Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Chairman
IBM Certified Database Adminstrator - DB2 11 DBA for z/OS
IBM Certified System Administrator - DB2 11 for z/OS
IBM Certified Application Developer - DB2 11 for z/OS
IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and
Windows

-----Original Message-----
From: Lizette Koehler [mailto:[login to unmask email]
Sent: Tuesday, February 28, 2017 1:25 PM
To: [login to unmask email]
Subject: [DB2-L] - Help with understanding DB2 and CPU TIME Usage

List -

I am just a simple z/OS Sysprog. Running DB2 V10 and z/OS V2.1

I am looking for a general understanding of why or when a DB2 SQL (Batch for
instance) will time out with a S322 abend. The JES2 class they are running
in has a maximum of 60 mins. The job clock time is 69 mins, but it used a
solid 60 CPU Minutes.

I am trying to understand why a query might do this. Yes I know this is
more like a pie-in-the-sky question.

The DBAs have reviewed the SQL with DB2 TOOLS and have told me the SQL
statements are running really fast. It is just the amount of data causing
the issue.

What can I do other than raising the jobclass time to 2-4 hours, can I look
at or review or change anything to help this query run?

Should I just give it an unlimited time class? Is this a normal function
for
DB2 SQL to just use a lot of CPU?

Do you have Queries running using a huge amount of CPU time?


Thanks

Lizette Koehler
statistics: A precise and logical method for stating a half-truth
inaccurately


-----End Original Message-----

Isaac Yassin

Help with understanding DB2 and CPU TIME Usage
(in response to Lizette Koehler)
Hi,
Can you, using db2 monitor how the cpu is split between db2 and the program
code?
The time consumed may have more to do with the batch program code than the
db2 part.

Isaac Yassin
Sent from my Galaxy Note 5

בתאריך 28 בפבר׳ 2017 21:25,‏ "Lizette Koehler" <[login to unmask email]> כתב:

> List -
>
> I am just a simple z/OS Sysprog. Running DB2 V10 and z/OS V2.1
>
> I am looking for a general understanding of why or when a DB2 SQL (Batch
> for
> instance) will time out with a S322 abend. The JES2 class they are
> running in
> has a maximum of 60 mins. The job clock time is 69 mins, but it used a
> solid 60
> CPU Minutes.
>
> I am trying to understand why a query might do this. Yes I know this is
> more
> like a pie-in-the-sky question.
>
> The DBAs have reviewed the SQL with DB2 TOOLS and have told me the SQL
> statements are running really fast. It is just the amount of data causing
> the
> issue.
>
> What can I do other than raising the jobclass time to 2-4 hours, can I
> look at
> or review or change anything to help this query run?
>
> Should I just give it an unlimited time class? Is this a normal function
> for
> DB2 SQL to just use a lot of CPU?
>
> Do you have Queries running using a huge amount of CPU time?
>
>
> Thanks
>
> Lizette Koehler
> statistics: A precise and logical method for stating a half-truth
> inaccurately
>
>
> -----End Original Message-----
>
>

Joe Geller

RE: Help with understanding DB2 and CPU TIME Usage
(in response to Daniel Luksetich)

Lizette,

A sql statement can use a lot of CPU (60 minutes out of 69) if it is doing a lot of work but with no I/O.  Multiplication adds up very fast.  An example - If you have two 1 million row tables and you join them and DB2 does a nested loop join without an index, DB2 has to examine 1 million * 1 million rows and 1 million table scans of the inner table of the join.  If the bufferpool is big enough for the 2 tables to fit, there will be very little I/O but lots of CPU.

In this case, the DBAs said that the statements are very quick but there is a high volume of data.  I assume they mean that the statement is executed many times (perhaps once for each row in a staging table).  So, if the volume is 1 million executions the next question (for the DBAs) is how fast is fast?  If each execution took 1 second, you have 1 million seconds, which is 300 hours.  Again, if there is no I/O this will all be CPU time.  If each execution only took 10 milliseconds, that would be 3 hours.  10 ms is very fast, but if it is all CPU, then you have exceeded your limit.

What the DBA needs to look at is the access path and the performance monitor statistics to see the number of getpages, rows examined, etc. per execution and the number of executions.

Is there anything that can be done to speed it up?  That depends.  If the access path is not good (scans too many rows) it's possible it could be improved.  Another possibility depending on the nature of the application (selects, updates etc.) is for the program to be rewritten so that instead of individual statements being executed, set processing could be used (joining the input set of records with the tables being accessed).

Joe

In Reply to Daniel Luksetich:

Lizette,
When you have a query that is using a lot of CPU relative to elapse time, in
your case 60 of 69 minutes, and the DBAs I am assuming are suggesting that
the access path is acceptable. Then I would look at the query for things
like nested table expressions and embedded functions and expressions. If
they have coded these within the statement then that could be an issue. This
won't show up in a DB2 explain. Take a look at page 3 of this article.

http://www.enterprisesystemsmedia.com/it-management/db2-for-z-os-sql-perform
ance-choices

Given the limited in formation this is only a guess but that's a lot of CPU.

Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Chairman
IBM Certified Database Adminstrator - DB2 11 DBA for z/OS
IBM Certified System Administrator - DB2 11 for z/OS
IBM Certified Application Developer - DB2 11 for z/OS
IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and
Windows

-----Original Message-----
From: Lizette Koehler [mailto:[login to unmask email]
Sent: Tuesday, February 28, 2017 1:25 PM
To: [login to unmask email]
Subject: [DB2-L] - Help with understanding DB2 and CPU TIME Usage

List -

I am just a simple z/OS Sysprog. Running DB2 V10 and z/OS V2.1

I am looking for a general understanding of why or when a DB2 SQL (Batch for
instance) will time out with a S322 abend. The JES2 class they are running
in has a maximum of 60 mins. The job clock time is 69 mins, but it used a
solid 60 CPU Minutes.

I am trying to understand why a query might do this. Yes I know this is
more like a pie-in-the-sky question.

The DBAs have reviewed the SQL with DB2 TOOLS and have told me the SQL
statements are running really fast. It is just the amount of data causing
the issue.

What can I do other than raising the jobclass time to 2-4 hours, can I look
at or review or change anything to help this query run?

Should I just give it an unlimited time class? Is this a normal function
for
DB2 SQL to just use a lot of CPU?

Do you have Queries running using a huge amount of CPU time?


Thanks

Lizette Koehler
statistics: A precise and logical method for stating a half-truth
inaccurately


-----End Original Message-----

Lockwood Lyon

Help with understanding DB2 and CPU TIME Usage
(in response to Lizette Koehler)
Lizette,

Right, just a simple z/OS Sysprog ... :)

I understand that this pertains to a single batch application (3GL
or or DSNTIAUL?) that executes multiple SQL statements, and your
question(s) relates to the high absolute amount of CPU time.

You note that the DBAs say "the SQL statements are running really
fast". What does that mean? Are they completing execution quickly,
or are they doing a "lot" of work, or what? Are they executing
multiple (thousands?) of times? Does the "amount of data" mean that
many rows of many tables are beinga accessed, or that many, many
rows are being returned?

A few ideas:

(1) If you have "small" SQL statements that are being executed
(tens of) thousands of times per batch execution, then much of the
CPU time may be buried in the overhead of invoking DB2, opening
cursors, etc. See SMF data for this.

(2) If the SQL statements access a large number oif tables/indexes,
a lot of CPU may be spent on executing I/Os. A simple SMF report
will tell you if so.

(3) Do the SQL statements invoke (a lot of) built-in scalar or row
functions like SUBSTR, CAST, SUM, TRANSLATE, etc.? There's CPU
spent invoking functions to either qualify rows or create results.

(4) You are abending with a S322. May I assume that it's the Batch
Job that is abending? Well, if this is an application program the
CPU spent in the 3GL code counts towards your Job/Step limit. Got
any bad code or quasi-infinite loops?!

(5) Sorts in the SQL? Perhaps an EXPLAIN will show that DB2 is
using CPU to do Sorting, which takes CPU (for in-memory sort) or CPU
+ I/O (for Workfile sort).

Lots more ideas out there, but a Statistics Report may give you more
direction towards the cause.

- Lock Lyon


-----Original Message-----
From: Lizette Koehler [mailto:[login to unmask email]
Sent: Tuesday, February 28, 2017 2:25 PM
To: [login to unmask email]
Subject: [DB2-L] - Help with understanding DB2 and CPU TIME Usage

List -

I am just a simple z/OS Sysprog. Running DB2 V10 and z/OS V2.1

I am looking for a general understanding of why or when a DB2 SQL
(Batch for
instance) will time out with a S322 abend. The JES2 class they are
running in
has a maximum of 60 mins. The job clock time is 69 mins, but it
used a solid 60
CPU Minutes.

I am trying to understand why a query might do this. Yes I know
this is more
like a pie-in-the-sky question.

The DBAs have reviewed the SQL with DB2 TOOLS and have told me the
SQL
statements are running really fast. It is just the amount of data
causing the
issue.

What can I do other than raising the jobclass time to 2-4 hours, can
I look at
or review or change anything to help this query run?

Should I just give it an unlimited time class? Is this a normal
function for
DB2 SQL to just use a lot of CPU?

Do you have Queries running using a huge amount of CPU time?


Thanks

Lizette Koehler
statistics: A precise and logical method for stating a half-truth
inaccurately


Lizette Koehler

Help with understanding DB2 and CPU TIME Usage
(in response to Joe Geller)
Thanks everyone so far. We have STROBE so that will be my next task. I will also ask the DBAs about the other questions.



I cannot post the query, but I can generalize it



It starts with a DECLARE LINK x CURSOR FOR

Then SELECT DISTINT for 10 fields from 3 different tables and then, the fun

The WHERE uses X = Y

Followed by 30 AND statements from the 4 tables.



Some of the ANDs are for host variables and some for specific fields.



There are no UNIONs or sub-selection criteria. Just straight

WHERE

AND

AND … till the end.





So if each table has 1,000,000 rows, I can see a lot of cpu being consumed based on the discussion so far.



Lizette







From: Joe Geller [mailto:[login to unmask email]
Sent: Tuesday, February 28, 2017 1:30 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Help with understanding DB2 and CPU TIME Usage



Lizette,

A sql statement can use a lot of CPU (60 minutes out of 69) if it is doing a lot of work but with no I/O. Multiplication adds up very fast. An example - If you have two 1 million row tables and you join them and DB2 does a nested loop join without an index, DB2 has to examine 1 million * 1 million rows and 1 million table scans of the inner table of the join. If the bufferpool is big enough for the 2 tables to fit, there will be very little I/O but lots of CPU.

In this case, the DBAs said that the statements are very quick but there is a high volume of data. I assume they mean that the statement is executed many times (perhaps once for each row in a staging table). So, if the volume is 1 million executions the next question (for the DBAs) is how fast is fast? If each execution took 1 second, you have 1 million seconds, which is 300 hours. Again, if there is no I/O this will all be CPU time. If each execution only took 10 milliseconds, that would be 3 hours. 10 ms is very fast, but if it is all CPU, then you have exceeded your limit.

What the DBA needs to look at is the access path and the performance monitor statistics to see the number of getpages, rows examined, etc. per execution and the number of executions.

Is there anything that can be done to speed it up? That depends. If the access path is not good (scans too many rows) it's possible it could be improved. Another possibility depending on the nature of the application (selects, updates etc.) is for the program to be rewritten so that instead of individual statements being executed, set processing could be used (joining the input set of records with the tables being accessed).

Joe

In Reply to Daniel Luksetich:

Lizette,
When you have a query that is using a lot of CPU relative to elapse time, in
your case 60 of 69 minutes, and the DBAs I am assuming are suggesting that
the access path is acceptable. Then I would look at the query for things
like nested table expressions and embedded functions and expressions. If
they have coded these within the statement then that could be an issue. This
won't show up in a DB2 explain. Take a look at page 3 of this article.

http://www.enterprisesystemsmedia.com/it-management/db2-for-z-os-sql-perform
ance-choices

Given the limited in formation this is only a guess but that's a lot of CPU.

Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Chairman
IBM Certified Database Adminstrator - DB2 11 DBA for z/OS
IBM Certified System Administrator - DB2 11 for z/OS
IBM Certified Application Developer - DB2 11 for z/OS
IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and
Windows

-----Original Message-----
From: Lizette Koehler [mailto:[login to unmask email]
Sent: Tuesday, February 28, 2017 1:25 PM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - Help with understanding DB2 and CPU TIME Usage

List -

I am just a simple z/OS Sysprog. Running DB2 V10 and z/OS V2.1

I am looking for a general understanding of why or when a DB2 SQL (Batch for
instance) will time out with a S322 abend. The JES2 class they are running
in has a maximum of 60 mins. The job clock time is 69 mins, but it used a
solid 60 CPU Minutes.

I am trying to understand why a query might do this. Yes I know this is
more like a pie-in-the-sky question.

The DBAs have reviewed the SQL with DB2 TOOLS and have told me the SQL
statements are running really fast. It is just the amount of data causing
the issue.

What can I do other than raising the jobclass time to 2-4 hours, can I look
at or review or change anything to help this query run?

Should I just give it an unlimited time class? Is this a normal function
for
DB2 SQL to just use a lot of CPU?

Do you have Queries running using a huge amount of CPU time?


Thanks

Lizette Koehler
statistics: A precise and logical method for stating a half-truth
inaccurately


-----End Original Message-----



-----End Original Message-----

Venkat Srinivasan

RE: Help with understanding DB2 and CPU TIME Usage
(in response to Lizette Koehler)

Accounting report should be the basis for first line of review. you look for class-1 vs class-2 / high getpages/ latch contention number of sqls  / entry exits lock / latch counts etc. More than likely a high cpu consumer would be doing prefetch. 

Have you ruled out a looping problem? it can be outside of db2 or executing same sql over and over again.

Is it one sql consuming 60 cpu minutes or 60 million executions ?

if it is class-2 cpu time, access path is likely the issue.

When everything else fails, the question of whether there is any need of executing such a program is also valid.

There is probably no potential remedy for "select everything from everywhere, hey I have got a with ur in it" type of problem. 

Changing the jobclass may be delaying the action. How do we know it will complete in 180 cpu minutes if we are willing to let it consume that much?

Venkat
 
In Reply to Lizette Koehler:

List -

I am just a simple z/OS Sysprog. Running DB2 V10 and z/OS V2.1

I am looking for a general understanding of why or when a DB2 SQL (Batch for
instance) will time out with a S322 abend. The JES2 class they are running in
has a maximum of 60 mins. The job clock time is 69 mins, but it used a solid 60
CPU Minutes.

I am trying to understand why a query might do this. Yes I know this is more
like a pie-in-the-sky question.

The DBAs have reviewed the SQL with DB2 TOOLS and have told me the SQL
statements are running really fast. It is just the amount of data causing the
issue.

What can I do other than raising the jobclass time to 2-4 hours, can I look at
or review or change anything to help this query run?

Should I just give it an unlimited time class? Is this a normal function for
DB2 SQL to just use a lot of CPU?

Do you have Queries running using a huge amount of CPU time?


Thanks

Lizette Koehler
statistics: A precise and logical method for stating a half-truth inaccurately

Patrick Bossman

RE: Help with understanding DB2 and CPU TIME Usage
(in response to Venkat Srinivasan)

I'm with Venkat.  Start with an accounting report.

- Is it one query executing once?  Or in a loop?

- Can you tell where all the getpages are occurring?  Do you have query monitor or something so you know which pageset(s) all the activity is occurring against?

Sometimes, join sequence, join method, index scan is not enough.  I've seen cases where there are "cross over" joins - where there are millions of rows qualified from outer tables, and then there is a join where the inner table is in a different clustering sequence then the rows from the outer table(s).

This results in millions of random getpages to the inner table.  If the inner is in memory, it's still a CPU burner.

In that sort of scenario, you'd want DB2 to sort the composite before the join.  You'd see the execution change because the number of getpages would drop (due to index lookaside), and you'd see dynamic prefetch kick in on the inner (because it's being accessed in sequence now)...

If you want to see how to break a query down, this is a presentation Jase Alpers published based on techniques we use at the lab.  It focuses on figuring out where DB2 thinks filtering is, where filtering actually is, and going from there.

https://www.ibm.com/developerworks/community/files/form/anonymous/api/library/89c92396-429f-440a-a682-b0139921a14f/document/075eeeb2-2733-4339-8278-deac34db2831/media

Best regards,

Pat Bossman

Myron Miller

Help with understanding DB2 and CPU TIME Usage
(in response to Lizette Koehler)
Strobe is definitely the thing to do. It will tell you a lot about where the actual time is being spent. After identifying the problem SQL according to Strobe, then you can use Data Studio to explain and look at those queries from an access point of view. And this will tell you a lot about what is happening on the access paths.


Strobe will also address whether it is really the complex query shown below causing all the problems or whether it is other queries using up the CPU Unnecessarily. For example, I have one program that has an SQL statement that accesses a 7,000 row parm table. Unfortunately in one run, it accesses that table 300 million times. Poor design.


Thanks Myron W. Miller


________________________________
From: Lizette Koehler <[login to unmask email]>
Sent: Tuesday, February 28, 2017 4:00 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Help with understanding DB2 and CPU TIME Usage


Thanks everyone so far. We have STROBE so that will be my next task. I will also ask the DBAs about the other questions.



I cannot post the query, but I can generalize it



It starts with a DECLARE LINK x CURSOR FOR

Then SELECT DISTINT for 10 fields from 3 different tables and then, the fun

The WHERE uses X = Y

Followed by 30 AND statements from the 4 tables.



Some of the ANDs are for host variables and some for specific fields.



There are no UNIONs or sub-selection criteria. Just straight

WHERE

AND

AND … till the end.





So if each table has 1,000,000 rows, I can see a lot of cpu being consumed based on the discussion so far.



Lizette







From: Joe Geller [mailto:[login to unmask email]
Sent: Tuesday, February 28, 2017 1:30 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Help with understanding DB2 and CPU TIME Usage



Lizette,

A sql statement can use a lot of CPU (60 minutes out of 69) if it is doing a lot of work but with no I/O. Multiplication adds up very fast. An example - If you have two 1 million row tables and you join them and DB2 does a nested loop join without an index, DB2 has to examine 1 million * 1 million rows and 1 million table scans of the inner table of the join. If the bufferpool is big enough for the 2 tables to fit, there will be very little I/O but lots of CPU.

In this case, the DBAs said that the statements are very quick but there is a high volume of data. I assume they mean that the statement is executed many times (perhaps once for each row in a staging table). So, if the volume is 1 million executions the next question (for the DBAs) is how fast is fast? If each execution took 1 second, you have 1 million seconds, which is 300 hours. Again, if there is no I/O this will all be CPU time. If each execution only took 10 milliseconds, that would be 3 hours. 10 ms is very fast, but if it is all CPU, then you have exceeded your limit.

What the DBA needs to look at is the access path and the performance monitor statistics to see the number of getpages, rows examined, etc. per execution and the number of executions.

Is there anything that can be done to speed it up? That depends. If the access path is not good (scans too many rows) it's possible it could be improved. Another possibility depending on the nature of the application (selects, updates etc.) is for the program to be rewritten so that instead of individual statements being executed, set processing could be used (joining the input set of records with the tables being accessed).

Joe

In Reply to Daniel Luksetich:

Lizette,
When you have a query that is using a lot of CPU relative to elapse time, in
your case 60 of 69 minutes, and the DBAs I am assuming are suggesting that
the access path is acceptable. Then I would look at the query for things
like nested table expressions and embedded functions and expressions. If
they have coded these within the statement then that could be an issue. This
won't show up in a DB2 explain. Take a look at page 3 of this article.

http://www.enterprisesystemsmedia.com/it-management/db2-for-z-os-sql-perform

[http://enterprisesystemsmedia.com/images/ui/esm_facebook.jpg]<http://www.enterprisesystemsmedia.com/it-management/db2-for-z-os-sql-perform>

404 Error<http://www.enterprisesystemsmedia.com/it-management/db2-for-z-os-sql-perform>
www.enterprisesystemsmedia.com
Enterprise Systems Media publishes Enterprise Executive and Enterprise Tech Journal (formerly z/Journal) for IT managers and technicians in the world’s largest multi-platform enterprises.



ance-choices

Given the limited in formation this is only a guess but that's a lot of CPU.

Dan

Daniel L Luksetich
DanL Database Consulting

IBM GOLD Consultant
IBM Champion for Analytics
IDUG Content Committee Chairman
IBM Certified Database Adminstrator - DB2 11 DBA for z/OS
IBM Certified System Administrator - DB2 11 for z/OS
IBM Certified Application Developer - DB2 11 for z/OS
IBM Certified Advanced Database Administrator - DB2 10.1 for Linux UNIX and
Windows

-----Original Message-----
From: Lizette Koehler [mailto:[login to unmask email]
Sent: Tuesday, February 28, 2017 1:25 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Help with understanding DB2 and CPU TIME Usage

List -

I am just a simple z/OS Sysprog. Running DB2 V10 and z/OS V2.1

I am looking for a general understanding of why or when a DB2 SQL (Batch for
instance) will time out with a S322 abend. The JES2 class they are running
in has a maximum of 60 mins. The job clock time is 69 mins, but it used a
solid 60 CPU Minutes.

I am trying to understand why a query might do this. Yes I know this is
more like a pie-in-the-sky question.

The DBAs have reviewed the SQL with DB2 TOOLS and have told me the SQL
statements are running really fast. It is just the amount of data causing
the issue.

What can I do other than raising the jobclass time to 2-4 hours, can I look
at or review or change anything to help this query run?

Should I just give it an unlimited time class? Is this a normal function
for
DB2 SQL to just use a lot of CPU?

Do you have Queries running using a huge amount of CPU time?


Thanks

Lizette Koehler
statistics: A precise and logical method for stating a half-truth
inaccurately


-----End Original Message-----



-----End Original Message-----

-----End Original Message-----

Russell Peters

RE: Help with understanding DB2 and CPU TIME Usage
(in response to Lizette Koehler)

Assuming the sql is executing efficiently as you have been told I have some other suggestions.

Does the batch job have multiple steps? We have batch jobs that may run very long but these jobs have a lot of steps and execute several programs. If that is the case in your situation then you could possibly split the work into two batch jobs to allow it to run.

Maybe the sql is performing efficiently but there is just a lot of work being done. In that case then maybe they could split the work out by date or some other filter to break the work out into more batch jobs. 

Also, the long-running high-cpu work may be in the program that is processing the data, not in db2. We have a few situations like this. There's nothing we can do about it; the cobol program is processing a lot of data and doing a lot of work with the data. The majority of the cpu is attributed to the program, not db2.

Lizette Koehler

Help with understanding DB2 and CPU TIME Usage
(in response to Venkat Srinivasan)
Thank you everyone for your assistance in educating me on the topic.



Basically it looks like the SQL is trying to take in a mountain (do everything in one SQL) rather than breaking it down into smaller – easier to manage – subsets



My recommendation to my user was the subset the data, work on smaller groups rather than do the whole thing at once.



I am grateful to the help in understanding that – yes there can be massive running SQLs. But with evaluation and review of the process, they may not always be the right technique to use.



Lizette



Dave Nance

Help with understanding DB2 and CPU TIME Usage
(in response to Lizette Koehler)
I'd be curious how much longer it takes to do the process that way. In my past experience I have seen very few cases where doing such would result in better performance.
Dave Nance 

From: Lizette Koehler <[login to unmask email]>
To: [login to unmask email]
Sent: Friday, March 3, 2017 9:05 AM
Subject: [DB2-L] - RE: Help with understanding DB2 and CPU TIME Usage

<!--#yiv1026504432 _filtered #yiv1026504432 {font-family:"Cambria Math";panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv1026504432 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv1026504432 {font-family:"Comic Sans MS";panose-1:3 15 7 2 3 3 2 2 2 4;}#yiv1026504432 #yiv1026504432 p.yiv1026504432MsoNormal, #yiv1026504432 li.yiv1026504432MsoNormal, #yiv1026504432 div.yiv1026504432MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;font-family:"Times New Roman", serif;}#yiv1026504432 a:link, #yiv1026504432 span.yiv1026504432MsoHyperlink {color:blue;text-decoration:underline;}#yiv1026504432 a:visited, #yiv1026504432 span.yiv1026504432MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv1026504432 p.yiv1026504432MsoPlainText, #yiv1026504432 li.yiv1026504432MsoPlainText, #yiv1026504432 div.yiv1026504432MsoPlainText {margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:"Courier New";}#yiv1026504432 p.yiv1026504432msonormal0, #yiv1026504432 li.yiv1026504432msonormal0, #yiv1026504432 div.yiv1026504432msonormal0 {margin-right:0in;margin-left:0in;font-size:12.0pt;font-family:"Times New Roman", serif;}#yiv1026504432 span.yiv1026504432EmailStyle19 {font-family:"Arial", sans-serif;color:#1F497D;}#yiv1026504432 span.yiv1026504432EmailStyle20 {font-family:"Comic Sans MS";color:windowtext;}#yiv1026504432 span.yiv1026504432PlainTextChar {font-family:"Courier New";}#yiv1026504432 .yiv1026504432MsoChpDefault {font-family:"Calibri", sans-serif;} _filtered #yiv1026504432 {margin:1.0in 1.0in 1.0in 1.0in;}#yiv1026504432 div.yiv1026504432WordSection1 {}-->Thank you everyone for your assistance in educating me on the topic.  Basically it looks like the SQL is trying to take in a mountain (do everything in one SQL) rather than breaking it down into smaller – easier to manage – subsets  My recommendation to my user was the subset the data, work on smaller groups rather than do the whole thing at once.  I am grateful to the help in understanding that – yes there can be massive running SQLs.  But with evaluation and review of the process, they may not always be the right technique to use.  Lizette  
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]** ** ** Attend the 2017 IDUG Data Tech Summit ** ** **
---> Anaheim, California, May 1 - May 2, 2017 <---
http://www.idug.org/page/dts2017

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Lizette Koehler

Help with understanding DB2 and CPU TIME Usage
(in response to Dave Nance)
I know that everything “depends” on the data and what is required in the process.



Where I am now concerned is that this SQL goes into an environment where there is a lot of access going on these tables and a potential Lock Escalation occur with this job holding the locks for 1 hour of cpu time or several hours of clock time. While in a development stage for this code, there is little access other than this SQL. In another environment, there could be much higher utilization by other work (TSO, QMF, Batch, Online IMS/CICS/etc)



I will see what my user gets back to me with – can they break it up or not.



Lizette





From: Dave Nance [mailto:[login to unmask email]
Sent: Friday, March 03, 2017 9:20 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Help with understanding DB2 and CPU TIME Usage



I'd be curious how much longer it takes to do the process that way. In my past experience I have seen very few cases where doing such would result in better performance.



Dave Nance





_____

From: Lizette Koehler <[login to unmask email] <mailto:[login to unmask email]> >
To: [login to unmask email] <mailto:[login to unmask email]>
Sent: Friday, March 3, 2017 9:05 AM
Subject: [DB2-L] - RE: Help with understanding DB2 and CPU TIME Usage



Thank you everyone for your assistance in educating me on the topic.



Basically it looks like the SQL is trying to take in a mountain (do everything in one SQL) rather than breaking it down into smaller – easier to manage – subsets



My recommendation to my user was the subset the data, work on smaller groups rather than do the whole thing at once.



I am grateful to the help in understanding that – yes there can be massive running SQLs. But with evaluation and review of the process, they may not always be the right technique to use.



Lizette





-----End Original Message-----





-----End Original Message-----

Joe Geller

RE: Help with understanding DB2 and CPU TIME Usage
(in response to Lizette Koehler)

Lizette,

In your first email you said that the DBAs said the SQL was running very fast, but in this email you say that it is one SQL trying to do everything.  How can one SQL statement be both very fast but also run for several hours?

Joe

In Reply to Lizette Koehler:

I know that everything “depends” on the data and what is required in the process.



Where I am now concerned is that this SQL goes into an environment where there is a lot of access going on these tables and a potential Lock Escalation occur with this job holding the locks for 1 hour of cpu time or several hours of clock time. While in a development stage for this code, there is little access other than this SQL. In another environment, there could be much higher utilization by other work (TSO, QMF, Batch, Online IMS/CICS/etc)



I will see what my user gets back to me with – can they break it up or not.



Lizette





From: Dave Nance [mailto:[login to unmask email]
Sent: Friday, March 03, 2017 9:20 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Help with understanding DB2 and CPU TIME Usage



I'd be curious how much longer it takes to do the process that way. In my past experience I have seen very few cases where doing such would result in better performance.



Dave Nance





_____

From: Lizette Koehler <[login to unmask email] <mailto:[login to unmask email]> >
To: [login to unmask email] <mailto:[login to unmask email]>
Sent: Friday, March 3, 2017 9:05 AM
Subject: [DB2-L] - RE: Help with understanding DB2 and CPU TIME Usage



Thank you everyone for your assistance in educating me on the topic.



Basically it looks like the SQL is trying to take in a mountain (do everything in one SQL) rather than breaking it down into smaller – easier to manage – subsets



My recommendation to my user was the subset the data, work on smaller groups rather than do the whole thing at once.



I am grateful to the help in understanding that – yes there can be massive running SQLs. But with evaluation and review of the process, they may not always be the right technique to use.



Lizette





-----End Original Message-----





-----End Original Message-----

Tim Hare

RE: Help with understanding DB2 and CPU TIME Usage
(in response to Joe Geller)

In our environment, they often use a code generator, which because of its ability to generate code for various platforms, often uses nested COBOL PERFORMs, with each paragraph having its own SELECTs, rather than generating complex SQL with nested JOINs.  We have found sometimes that coding a view, resulting in one "table" that the application needs, can drastically improve CPU time because of the DB2 optimizer.  Look for such a situation.