Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 Streaming Batch Processing Problem

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Sat Sep 24, 2016 12:14 pm    Post subject: DB2 Streaming Batch Processing Problem
Reply with quote

Hi,

To decrease execution time of batch job we tried to use multi streaming and split the job as follows :

Env:
1- Parallel Sysplex 2 Member
2- DB2 Ver 10
3- Account Table with 60,000,000 records RecLen 2K
4- Number of partitions Table space 80 by reange
5- Buffer pool size in each Member of data sharing is 80000 32K - LRU
6- Random Access
7- Group Buffer pool 1Gig
8- Number of CP 20 for Each Member
9- Bind The Package with Degree(ANY)
10- GBPCache set to Change TS and Index both
11- 80 stream for Each part
12- commit cont 100
There is 2 table spaces with same specific and batch just read and update both
tables heavy update.

The problem is some of the stream will finished very fast but some taking unexpected time and this situation is random for each run.

Could you please lead me to solve this problem?

Thanks
Manshadi
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Mon Sep 26, 2016 2:55 pm    Post subject:
Reply with quote

Starting point:
DB2 10 for z/OS Performance Topics
DB2 10 for z/OS Performance Topics wrote:

6.3.1 Record range partitioning

To evaluate parallelism, DB2 chooses key ranges decided at bind time by the optimizer, based on statistics (low2key, high2key, and column cardinality) and the assumption of uniform data distribution within low2key and high2key. This makes DB2 dependent on the availability and accuracy of the statistics. If the statistics are inaccurate or there is data skew or data correlation, the key ranges chosen might not result in an even distribution of the workload among the parallel tasks. The uneven distribution of the workload can result in elongated elapsed times due to one or more of the parallel tasks processing considerably more data than the other tasks.

Record range partitioning differs from key range partitioning in that the partitioning is done at execution time instead of bind time and that partitioning is based on an equal number of records instead of based on keys.

The following key range related re-partitioning restrictions or inefficiencies no longer exist with record range partitioning:

Limited number of distinct values for leading columns
Data skew or data correlation
Lack of accurate statistics
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Mon Sep 26, 2016 9:42 pm    Post subject:
Reply with quote

Quote:
The problem is some of the stream will finished very fast but some taking unexpected time and this situation is random for each run.
What do you mean by unexpected time? do you have the exact same volume for each split and run? What is your investigation on knowing the differences of the faster and slower run ( Always try giving the real stats of CPU and elapsed time)? May be it has nothing to do with UPDATE query and something else may be query itself?, feed us more to get specific answers.
Back to top
View user's profile Send private message
Manshadi

New User


Joined: 31 Aug 2005
Posts: 82

PostPosted: Fri Sep 30, 2016 1:48 am    Post subject: Reply to: DB2 Streaming Batch Processing Problem
Reply with quote

Sorry to late reply

I was busy to test many senario and gathering information to find the cuse of problem.

and the result is as follow :

Senario 1- Whenever we restart the DB2 the result of execution time is same(7 min ) and each stream took same cpu%(3-4) and job will finished fine.
Please note that in this case related table space GBP not allocated and the job dived between to memeber via WLM.

For example : Member 1 ( Job1 - Job2 - job3 - job4) and Member2(job5-job6-job7-job8)

Scenario 2- In the second run without changing any thing with same setuation some of the jobs not taking CPU%( less than 1%) and some Taking 3-4 %. The jobs which taking CPU%(3-4%) will finished fine(7 Min )and the jobs which taking CPU% less than 1% taking time more than 30 Min.
Please note that in this senario WLM will divided defferent stream on the Members and related table space GBP will allocated.

Member1 ( job5 - job2 - job6 -job4)
Member2 (Job3 - job7 - job1 - job8)

Senario 3-Restart DB2 again and separate the strem between Members by SYSAFF. In this case everything will fine and jobs will taking expected CPU(3-4%) and execution time (7 Min) for each run.

We took accounting report but its to much complicate and to much data .

Please help me to which point of accounting report I should focus?

Senario 4- Bing up both DB2 in One Member again everything is fine.

Best regards
Manshadi
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Sep 30, 2016 9:30 pm    Post subject:
Reply with quote

7.2 Test iterations
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts How does a called pgm know if its cal... Graeme Westerman COBOL Programming 4 Tue Nov 29, 2016 9:25 pm
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
This topic is locked: you cannot edit posts or make replies. MIPS reduction for Batch job Virendra Shambharkar All Other Mainframe Topics 8 Mon Nov 07, 2016 4:02 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts Run stats processing on zIIP Engine Virendra Shambharkar DB2 9 Fri Oct 14, 2016 10:24 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us