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 SELECT query to be replaced by a COBOL search for online

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

New User


Joined: 08 Sep 2009
Posts: 27
Location: Charlotte

PostPosted: Wed Sep 16, 2009 8:34 am    Post subject: DB2 SELECT query to be replaced by a COBOL search for online
Reply with quote

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
View user's profile Send private message

chanti

New User


Joined: 22 Nov 2008
Posts: 29
Location: hyderabad

PostPosted: Wed Sep 16, 2009 10:09 am    Post subject: Reply to: DB2 SELECT query to be replaced by a COBOL search
Reply with quote

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
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Wed Sep 16, 2009 10:35 am    Post subject:
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed Sep 16, 2009 11:23 am    Post subject:
Reply with quote

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
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Wed Sep 16, 2009 12:38 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Sep 16, 2009 7:50 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Sep 18, 2009 3:22 pm    Post subject:
Reply with quote

+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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Sep 18, 2009 3:28 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Fri Sep 18, 2009 4:01 pm    Post subject:
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Fri Sep 18, 2009 4:32 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Fri Sep 18, 2009 5:10 pm    Post subject:
Reply with quote

This is the strobe report before changing the query,
We have reduced to the total CPU mins by 85%
Back to top
View user's profile Send private message
jdeeponline

New User


Joined: 08 Sep 2009
Posts: 27
Location: Charlotte

PostPosted: Fri Sep 18, 2009 10:02 pm    Post subject:
Reply with quote

Ketan,

Appreciate the statistics. Even my goal is to be sure that I will indeed be making some CPU saving with this move. icon_smile.gif

Ashimer,
Yeah. The CPU consumption is a big headache for us too which is why we are attempting to change this.
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
This topic is locked: you cannot edit posts or make replies. Cobol GUI (like Editor) Kala Mainframe COBOL Programming 14 Wed May 10, 2017 12:30 pm
No new posts IMS T-Pipe queue counts in a COBOL Pr... Siva NKK Kothamasu IMS DB/DC 0 Tue May 09, 2017 6:31 pm
No new posts COBOL Code Parsers and Lineage Establ... balimanja COBOL Programming 2 Tue May 02, 2017 3:30 am
No new posts Cobol upgrade - source code missing f... gthmrj IBM Tools 1 Wed Apr 26, 2017 6:04 pm
No new posts COBOL Programming Sandpit? jodrisco COBOL Programming 6 Wed Apr 12, 2017 3:47 am


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