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
 

 

Performance problem

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

New User


Joined: 29 Nov 2005
Posts: 49

PostPosted: Thu Oct 29, 2009 12:57 pm    Post subject: Performance problem
Reply with quote

Hello,

I have the following requirement. Could anyone please help me on this.

I have an sequential input file with 100 records. I should read the first record from the input file.
With that record, I have to read the db2 database by passing key as one of the input field.
In database it will have multiple entries for that record (but at max 5 entries).
I should read all those entries from the database and store it an array and have to do some processing.

After that I have to read the second record from input file and with that record I have to read the databse again.
This process has to be continued until eof.

ex: input file
100
200
300

database
100 vvvv
100 cccc
100 dddd

200 yyyy
200 cccc

300 dddd
300 ssss
300 fghh
300 kkkk

The question what I have here is, in this case for each and every record from input file i should open & close the cursor multiple
times which will cause to performance issue.

Is there any alternative to do this? Please help me on this.


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

Escapa

Senior Member


Joined: 16 Feb 2007
Posts: 1399
Location: IL, USA

PostPosted: Thu Oct 29, 2009 1:06 pm    Post subject:
Reply with quote

Dump the first input file records in temporary table and join this with other table..
Back to top
View user's profile Send private message
Steve Davies

New User


Joined: 15 Oct 2009
Posts: 32
Location: UK

PostPosted: Thu Oct 29, 2009 1:16 pm    Post subject:
Reply with quote

Do you really think you'll have a performance problem reading 100 records and opening a cursor for each one??????? !!!!!!!!!!! Assuming your access to the table is via a suitable index (i.e. the key from the input file 100, 200, 300, what ever that field is), then it hardly seems like it's going to cause trouble!
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Oct 29, 2009 1:22 pm    Post subject:
Reply with quote

it depends,
if you have a 100 records in the file
but 10.000.000 rows in the table, with an index on col1
=> it is probably best to re-open the query.

if you have 5000 rows in the table
you might consider opening the cursor once in the beginning of the program without condition on col1 but ordered by col1
and code some kind of match-merge program.
Back to top
View user's profile Send private message
sunnybunny

New User


Joined: 29 Nov 2005
Posts: 49

PostPosted: Thu Oct 29, 2009 1:26 pm    Post subject: Reply to: Performance problem
Reply with quote

Thanks for your replies.

Sorry to say that, example purpose only I mentioned that input file will contain 100 records. Actually input file will have around 10 lakh records.

But for each record in input file, databse will contain at max 5 rows.

Thanks
Sunny
Back to top
View user's profile Send private message
Steve Davies

New User


Joined: 15 Oct 2009
Posts: 32
Location: UK

PostPosted: Thu Oct 29, 2009 1:42 pm    Post subject:
Reply with quote

OK, lakh = 100,000 correct? well there are a few things you could do

1) Give your program a go as you describe it in your post, as you don't actually know if there is a performance problem with it.

If there is a problem, you could,

2) Stream your batch job so you have x number of occurances of the job running. You'll need to split your input file in to x number of files. They can all run together then. Watch out for contention between streams though.

3) Unload the table and ensure it's in the same sort order as the input file and read both sequentially doing a match on the table unload file. This assumes you wont need to update the table.

No doubt there are many more things you can do, but it's a bit difficult not knowing the processing the program is doing.

No doubt other people will also post other ideas.
Back to top
View user's profile Send private message
sunnybunny

New User


Joined: 29 Nov 2005
Posts: 49

PostPosted: Thu Oct 29, 2009 2:37 pm    Post subject: Reply to: Performance problem
Reply with quote

Thanks Steve.
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 Strings with double quotes having pro... raja Arumugam All Other Mainframe Topics 11 Thu Mar 30, 2017 10:34 am
No new posts Problem reading GTF trace output danik56 ABENDS & Debugging 7 Thu Mar 16, 2017 1:02 pm
No new posts ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm
No new posts BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts SYMNAMES problem jacobdng DFSORT/ICETOOL 7 Thu Dec 22, 2016 7:47 am


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