|
View previous topic :: View next topic
|
| Author |
Message |
jdeeponline
New User
Joined: 08 Sep 2009
Posts: 27
Location: Charlotte
|
|
|
|
I have a requirement. In order to reduce the DB2 access in online transactions, we intend to have a cursor to fetch the rows of the table for the first first transaction of a thread, load it to a COBOL array and then access the COBOL array for the remaining transactions instead of the table.
I wanted to know if
a) I will make any savings with the reduction in the DB2 access.
b) Will the Response time take a heavy beating. I am expecting a slight rise. |
|
| Back to top |
|
 |
chanti
New User
.jpg)
Joined: 22 Nov 2008
Posts: 30
Location: hyderabad
|
|
|
|
a) I will make any savings with the reduction in the DB2 access.
--- Defnitely this will result in cost savings, as there will be less DB2 calls
b) Will the Response time take a heavy beating
-- I think it will not make much diferrence
Note:
Loading Db2 Data into cobol array is advisable
(i) If the DB2 table size is very less
(ii) If there are millions of records accessing the DB2 the table
Thanks,
Chanti |
|
| Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
Hi,
By dumping the cursor in the cobol table will reduce the number of resources used by the program, this will definately save the CPU. |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
This
| Quote: |
| we intend to have a cursor to fetch the rows of the table for the first first transaction of a thread |
needs more clrification. What is "thread" here? Are you talking about "multi-row-fetch" concept, by any chance?
And, CPU utilization is one of the last things to worry about these days when machines are executing tens of millions of instructions in one second of CPU time. If you think the job is using too much time, then you look at it -- otherwise, you don't really need to think much about it. |
|
| Back to top |
|
|
Srihari Gonugunta
Active User
Joined: 14 Sep 2007
Posts: 295
Location: Singapore
|
|
|
|
| Quote: |
| first transaction of a thread, load it to a COBOL array and then access the COBOL array for the remaining transactions instead of the table. |
Instead load the rows to a TSQ initially and access this TSQ there on. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| By dumping the cursor in the cobol table will reduce the number of resources used by the program, this will definately save the CPU. |
Very possibly not. . .
If this is a heavily used transaction and there are many users who are "in" this set of transactions concurrently, the memory usage will be much higher. If there are typically 2000 rows to be processed for a function, all 2000 must be read into the array for every user - lots of overhead.
I'd suggest looking at improving the processing of the transaction(s). Better transaction design, better indexes to support the transaction(s), tsq, etc. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| +1 to dick's post. COBOL array processing can sometimes be extremely expensive if volume of data and number of executions are high. |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
| Quote: |
And, CPU utilization is one of the last things to worry about these days when machines are executing tens of millions of instructions in one second of CPU time.
|
For institutions using mainframe with support directly from IBM this CPU thing is a major headache. They shell out millions an year for CPU time. For almost an year i worked on a CPU consumption reduction project all alone. You would really be surprised to see the monthly bill my client used to get before the start of this project. IBM was charging around 30 euros for a single minute of CPU ( between 9 am and 5 pm ) !!!! |
|
| Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
Hi,
I have small query regarding this point only
| Code: |
2723 SELECT MBR_ID INTO :H FROM TBUAMBR WHERE AUTH_RNG_STRT_NUM<=:H AND AUTH_RNG_END_NUM>=:H FETCH FIRST
1 ROW ONLY
0 STMT STATEMENT STMT EXECUTION % CPU TIME CPU TIME HISTOGRAM MARGIN OF ERROR: .76%
NUMBER TEXT CNT AVG-TIME SOLO TOTAL .00 21.50 43.00 64.50 86.00
2284 SELECT 2639810 .0001 1.09 1.09 .
2326 SELECT 2100952 .0012 .89 .89 .
2723 SELECT 1636196 .0014 84.39 84.39 .***************************************
2753 SELECT 478528 .0001 .16 .16 .
----- ------ ----- -----
DBRM - XXXXXXX TOTALS 6,855,486 .0007 86.52 86.52
|
| Code: |
FETCH TYPE 2 IDX ENTRY 91592 74.30 74.30 .
|
This the my strobe report for one of the job at our shop.
Now looking at the strobe report what I have done is like dumping the DB2 table to a cobol and then processing the COBOL table, and have achived to reduce the timing completly. the volume of the data in around 40K, so i did this thing and then only I have given the above recomendation, Can any one explaing me what else I can have done to reduce the CPU time |
|
| Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004
Posts: 551
Location: Bangalore
|
|
|
|
Ketan, you had given a valid suggestion. .. i was just referring to worst case scenario ..
Your report does not show COBOL stmts timings ... What was the % of reduction ? |
|
| Back to top |
|
|
Ketan Varhade
Active User
Joined: 29 Jun 2009
Posts: 197
Location: Mumbai
|
|
|
|
This is the strobe report before changing the query,
We have reduced to the total CPU mins by 85% |
|
| Back to top |
|
|
jdeeponline
New User
Joined: 08 Sep 2009
Posts: 27
Location: Charlotte
|
|
|
|
Ketan,
Appreciate the statistics. Even my goal is to be sure that I will indeed be making some CPU saving with this move. 
Ashimer,
Yeah. The CPU consumption is a big headache for us too which is why we are attempting to change this. |
|
| Back to top |
|
|
|
|
