FETCH +100 TAKES 35.84 SECONDS

[Member 310024]

Environment: IBM mainframe, COBOL/DB2, MVS
I have a program which reads a chain of DB2 records.
I firstly define the cursor, then open it.
I then FETCH each record within a loop until SQLCODE +100.
I have done various timing points and what I have found
is that the FETCH which results in the SQLCODE +100
takes 35.84 seconds(elapsed). The previous fetchs took
0.12 and 0.00 seconds (closest hundredths timing).
This is fairly consistent timing as I have done the timings
multiple times. Therefore I discount swapping issues.
My question is, does anyone have prior experience with
a problem like this or have any idea as to what might be
causing this most unusual phenomenon?

 

[MarcLodge]

Are the tables you are enquiring upon quite large? Does the fetch have an ORDER by clause?
Marc

 

[Member 310024]

The tables are quite large and I do have an ORDER BY,
although the subsets I am defining are quite small eg 3-10 records.
I have experimented with my code and found out what the
problem was.
In an attempt to modularize my code and save labour costs,
I created a generic sql for the DECLARE CURSOR.
The problem was in the WHERE clause.
I wanted to have a bgn & end value for each field in the
key, and then the programmer would not have to start writing SQL. All he would have to do is enter the bgn & end values and then perform the section which contained the DECLARE. For example :

X-DECLARE-CURSOR-XYZ SECTION.
CONTROL-PARAGRAPH.
EXEC SQL
DECLARE blah blah blah
WHERE
(
(
KEY_FLD_1 >= :WW-KEY-FLD-1-BGN AND
KEY_FLD_1 <= :WW-KEY-FLD-1-END
)
AND
(
KEY_FLD_2 >= :WW-KEY-FLD-2-BGN AND
KEY_FLD_2 <= :WW-KEY-FLD-2-END
)
)
ORDER BY blah blah blah
END-EXEC.
Test SQLCODE here ...

It was an attempt to save labour costs, but it back-fired.
It peformed like a snail on valium.

I modified the WHERE clause, for example :
WHERE
KEY_FLD_1 = :WW-KEY-FLD-1
AND
KEY_FLD_2 = :WW-KEY-FLD-2

The program was quite complex with quite a few lookups which invloved chain reads within chain reads.
However, the performance went from as low as 3 records per minute (rpm) to averaging 6,000 rpm.

 

[MarcLodge]

Blimey, that's some performance enhancement! You should ask for a raise!!!

Marc

 

[gregsimpson]

Terminate,

probably too late now, but you could try the same query using &quot;between&quot; and I suspect you may get substantailly differing results to that of your first query.

Cheers
Greg