MVSFORUMS.com

deepatred · Beginner Joined: 29 Nov 2005 Posts: 19 Topics: 6

Hi,

I have a program that selects daily data from a DB2 table using Cursor and formats some fields of data and writes them to an O/P file. In other words it extracts the data from DB to file. Daily data can go upto 32K.

CPU time for it's execution is 2 min 27 sec. What can be the reason for the CPU time to go in min? Is it because of the cursor?

Is there any other way so that i can reduce the CPU time for this Job.

Thanks,

kolusu · Posted: Wed Jul 05, 2006 7:04 am Post subject:

deepatred,

Check this explanation about CPU time

http://mvsforums.com/helpboards/viewtopic.php?p=948#948

In your case you need to check if your Cursor is using an index or not. Also another alternative is to use DSNTIAUL to unload and reformat the output to your needs.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

bauer · Posted: Thu Jul 06, 2006 1:17 am Post subject:

deepatred,

- check your cursor using db2 explain command
- use any monitoring tool (omeganmon ....)
- check your program logic (loops .....???)

regards
bauer

deepatred · Beginner Joined: 29 Nov 2005 Posts: 19 Topics: 6

Hi Kolusu,

Thanks for response.

kolusu · Posted: Thu Jul 06, 2006 5:06 am Post subject:

bauer · Posted: Thu Jul 06, 2006 5:33 am Post subject:

deepatred,

if you are talking about "Cursor is using indexes " the next questions are:

- Matchcolumns?
- Clusterindex ?
- Index Scan? Non Matching Index Scan ?
- Results of EXPLAIN?

regards Bauer

deepatred · Beginner Joined: 29 Nov 2005 Posts: 19 Topics: 6

Hi Kolusu,

Program is in Cobol. The processing that program does is somewhat like this.

for each record.
if field-1 not = 'HH'
increment counter count-1
if field-1 = 'CC'
Revert signs of 6 fields.
Feild-2 = field-2 * -1
Feild-3 = field-3 * -1
.
.
Feild-7 = field-7 * -1

split field-8 into 2 parts using a group variable. output file has it as
field-8A and field-8B
Write rest of the fields as it is to output file1.

file-2
1) write total record processed count.
2) write total count of field-1 not = 'HH' case: count1

this second file is a sort of report to be used latter for some processing by some other Job. It contains total records that were fetched and count1.

Thanks.

deepatred · Beginner Joined: 29 Nov 2005 Posts: 19 Topics: 6

Hi Bauer,

Thanks for the reply.
Actually i am somewhat new to mainframe and DB2, so not familiar to all those terms.
Could you please let me know some more details. Where to find those info and how these things affect
the performance of query.

Thanks.

bauer · Posted: Fri Jul 07, 2006 1:11 am Post subject:

deepatred,

the topic "how to use DB2 explain command" was dicussed several times in this forum. Pls use the search function to find the link.

But in general the influence on performance regarding the index structure, type on index, cluster / non cluster index, valid static data ..... require some skill and experience. So if you are not familiar with some general concepts the results of the DB2 explain Command wounn't help you. Because of the explain command (and in addition catlog information) will only tell you what for example "an cluster index with matchcolumns 2 is used".

So my proposel for the future: Look for an training / basic information about the general concepts of DB2 (and other database systems.......). Well it's hard, but witthout knowing your select statement, the databasestructure and the statistic data nowbody will be able to find out wether your select is the problem or the bad performance has any other reasons.

The background of my post was only, to give some ideas, not to explain general database concepts and the implementaion in ibms db2.

regards,
bauer

kolusu · Posted: Fri Jul 07, 2006 7:27 am Post subject:

deepatred,

I don't think you really need a cobol program for formatting the output. you can use an utility to format the input or even the unload utility can do that itself. Post your cobol layout of the table and also specify the fields that need to have the sign reverted and split fields. A sample input and desired output also would help.

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

deepatred · Beginner Joined: 29 Nov 2005 Posts: 19 Topics: 6

Hi Kolusu,

kolusu · Posted: Tue Jul 11, 2006 7:59 am Post subject:

Deepa,

If you don't have DFSORT then you may not be able to use IFTHEN and OVERLAY which would make job easier and all calculations can be done within a single step. With syncsort version you have you need two steps. try this job

deepatred · Beginner Joined: 29 Nov 2005 Posts: 19 Topics: 6

Hi Kolusu,

Thanks a lot for this solution it's working out and taking only .10 sec Smile

also got to learn some new features of syncsort that i had not used till now.

But there is one problem that i could not figure out, file REST is not getting any record? Those non 'HH' and 'CC' records are not getting written in REST file and somehow getting discarded and thus the final output file is not getting those records.

Also why 5 digit SEQNUM is being generated in file 'HH'? As far as i can
see we are not using that anywhere.

Thanks,

Regards,
Deepa

kolusu · Posted: Fri Jul 14, 2006 4:15 am Post subject:

deepatred · Beginner Joined: 29 Nov 2005 Posts: 19 Topics: 6

YEs i have written exactly similar to what you have given.