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
 
Advice on normal db2 prg to multi-row support conversion

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

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Nov 25, 2009 9:51 pm    Post subject: Advice on normal db2 prg to multi-row support conversion
Reply with quote

Hi,

I would like to know, whether is it worth an effort to convert current DB2 programs to support Multi-row fetch & insert.

I would also like to know, whether any other shop has done a process like this. Because huge amount of effort is required when number of programs to convert is high.

Thank You,
Sushanth
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Nov 25, 2009 10:02 pm    Post subject:
Reply with quote

well, we did and doing it gradually.
Only the big fetchers and inserters and it is definitely worth the effort.
up to 30% cpu gain.
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Nov 26, 2009 10:57 am    Post subject:
Reply with quote

Thank You GuyC,

In our shop currently they were looking for some performance benefits in our applications across all the domain(Client Server side as well as mainframe).

I suggested the Mult-row conversion for our currrent DB2 application programs. Funny thing is, from then on i didn't hear from them regarding this. So, i was thinking is this a really a weird conversion OR worth an effort to do something like this.

Again Thank You Very much GuyC!

Sushanth
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Nov 26, 2009 1:16 pm    Post subject:
Reply with quote

Quote:
This is in regarding the post (sub:Advice on normal db2 prg to multi-row support conversion).
i was thinking on the same lines for our shop for converting our COBOL DB2 insert programs.
Initially we evolved from 'Single record read from PS file to single insert into target table ' to 'Single record read from PS file to single insert into Global temp table and bulk select from to Global temp table and insert into target table' this conversion gave us few cpu gain.
I haven't coded or rather seen a multi row version of programs,so can u tell me what all complexities this conversion will have and issues u have faced.I have just gone thru the documents for multi row inserts , the changes i can understand, are the cursor usage which is a bit different here and the usage of arrays.Do u see anything else ?? and where is CPU gain coming from.
Thanks
Rakesh


Hi Rakesh,

In open, more people can join and share.

Created Temporary Table will require the usage of workfile(database/tablespaces), that is used for certain SQL operations.

You might have to change the your process like move your data from PS to an COBOL array and use the array while inserting.

Quote:
Where is CPU gain coming from.

CPU gain will be from number statements executed is reduced, i think.


Thank You,
Sushanth
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Nov 26, 2009 1:52 pm    Post subject:
Reply with quote

instead of one loop :

Code:
* code is more pseudocode than actual cobol
open
fetch
perform until sqlcode > 0
     process row
     fetch into row
end-perform
close

you need two :
Code:
* code is more pseudocode than actual cobol
open
fetch into row-table
move 1 to ix-row
perform until sqlcode > 0 and ix-row > sqlerrd(3)
     perform unitl ix-row > sqlerrd(3)
          process row (ix-row)
          ix-row +1
      end-perform
      fetch into row-table
end-perform
close


So it requires some effort.
It all depends on how expensive your CPU-seconds and how cheap your man-hours are.

The benefit is in the limited numbers of calls to DB2.
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 RFE: DB2 support for mixed case names. Pedro DB2 0 Tue Jul 04, 2017 1:32 am
No new posts Why TS/OP would Not contact site supp... RahulG31 All Other Mainframe Topics 4 Wed Mar 22, 2017 7:46 am
No new posts File Aid to File Manager conversion murali3955 IBM Tools 4 Thu Nov 24, 2016 3:41 pm
No new posts Conversion of a VB file to FB. isssal DFSORT/ICETOOL 1 Mon Oct 31, 2016 10:23 pm
No new posts EBCDIC to ASCII conversion using OCOPY mistah kurtz All Other Mainframe Topics 5 Tue Sep 06, 2016 2:26 pm


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