|
View previous topic :: View next topic
|
| Author |
Message |
Manshadi
New User
.jpg)
Joined: 31 Aug 2005
Posts: 82
|
|
|
|
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 |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| 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 |
|
|
Manshadi
New User
Joined: 31 Aug 2005
Posts: 82
|
|
|
|
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 |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
| Back to top |
|
|
|
|
