Positioning pointer at a specified record of a cursor result

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

Hi,

I am working on converting a IMSDB call to DB2 call for a new DB2 DB. Earlier the logic was to issue a GU call to position the pointer in IMSDB and then issue GN calls to fetch the records (The GU call is a qualified one). Now I am using a cursor to get the records and I filter all the records in COBOL until that particular record is fetched. I could not give this particular field in the where clause as the column is not sorted (eventhough the field is in the index).I went through Scrollabe cursors but I could not get a logic to position the pointer depending on a field value. How to replace the same logic in DB2 i.e. positioning the pointer at a particular position and retrieve the values from that point?

Thanks,
Bala

dick scherrer · Posted: Sun Oct 28, 2007 3:48 am

Hello,

You might consider using an ORDER BY in the select for your cursor. . .

This would allow you to see the rows in a guaranteed/predictable sequence and when you reach the position you want, process as needed from that point.

Craq Giegerich · Posted: Sun Oct 28, 2007 7:05 am

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

If I was able to give it in the where clause, I would have got the required row first. Now the required row may be after around 1000 records or more, so I want to position the pointer there so that I can start my processing from there instead of doing a cobol filter. So how to accomplish the positioning of pointer depending upon the value of a field?

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

Craq Giegerich · Posted: Tue Oct 30, 2007 5:50 am

dick scherrer · Posted: Tue Oct 30, 2007 6:07 am

Hello Shyam,

We do not yet understand your situation.

I would think that either using the WHERE or by using a cursor which will return rows in a predictable order (and allowing multiple rows with the cursor), you would be able to retrieve the row(s) you want and exclude others.

Maybe if you post sample data, we will better able to understand.

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

Hi,

Let me explain with a example. Consider the DB containing the Date of Joining of the students, their name and their ranks. The DB is sorted in the descending order of the DOJ of the students. If I were to retrieve the rows below a particular date and starting from a student's rank, I cannot give "where date <= '2007-10-01' and where rank => '5' ".
In this case if a rank at the next date is less than 5 that would not get retrieved. Also we need the output in the desc order of date, hence we cannot sort in the order of rank. Hence I am not having rank in the where clause and putting a loop in my program till I reach that particular students rank but I want to remove this unnecessary loop. Hope this explain my issue.

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

Hi Dick,

Let us consider the DB contents as follows,
DOJ Name Rank
10/1/2007 rrr 50
9/9/2007 xxx 5
9/9/2007 yyy 10
9/9/2007 ccc 15
8/7/2007 ddd 2
8/7/2007 eee 25
8/7/2007 zzz 35
7/6/2007 aaa 1
5/5/2007 bbb 5

and the expected result is,
DOJ Name Rank
9/9/2007 yyy 10
9/9/2007 ccc 15
8/7/2007 ddd 2
8/7/2007 eee 25
8/7/2007 zzz 35
7/6/2007 aaa 1
5/5/2007 bbb 5

If there are around 10000 students who joined on that particular date. I have retrieved the details of 9000 students earlier and now I want only the details of 1000 students also the identifier here is the rank which is in the sorted order inside a date, so I open the cursor here and do a loop until I reach that 9001 record (In real situation the record number is unknown). But I want to reach to that particular record instead of scanning all the 9000 records.

dick scherrer · Posted: Wed Oct 31, 2007 2:01 am

Hello,

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

dick scherrer · Posted: Wed Oct 31, 2007 6:55 am

Hi Shyam,

Yes, i misunderstood - again

Possibly, i better understand now. . .

I will look around and try to find something more suitable to what you need.

How do you feel about a temporary table or some sort of 2-phase approach (1 to determine the place to start and the other to return all rows below that point)? I'm not necessarily recommending these, just wondering what your thoughts are.

How often will this process be run? The frequency may impact the approach.

One other thing to consider is to unload the table and do what you want with a qsam file. . . Not always is database the better solution.

enrico-sorichetti · Posted: Wed Oct 31, 2007 1:38 pm

enrico-sorichetti · 9/9/2007 xxx 5

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

Hi Dick,

Would the idea of temp table or sort improve the performance of the system, I am looking for a new logic to eliminate cobol filter logic.

This process would be called from 60% of the jobs and onlines daily (hence performance should be high).

Would there be any logic in JOINS or Subqueries to satisfy the condition?

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

Hi E.S.,

Thanks for making things clear.

As per my requirement only the rows starting from the given record should be fetched, hence 9/9/2007 xxx 5 was not which is above the given record should not be retrieved (the starting record given here is 9/9/2007 yyy 10). In this case we are having only one record above the starting one, in the worst case there can be thousands of records for the same date, but i need to retrieve only the records below the given record. Currently I am using the query you sent but I have to filter all the records above the required record, I want to remove the filtering logic.

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

enrico-sorichetti · Posted: Thu Nov 01, 2007 4:34 am

we' ll have to elucubrate a little,
maybe the donkey approach of selecting first the good date

something like ( forget the syntax, it' s just to grasp the logic )

select date from ... where date < some_date
order by date desc fetch first row only

and You get the first_good_date to process

select all_you_need from ....
where date < first_good_date or
( date = first_good_date and rank > first_rank )
order by date desc, rank

and You get all You need ...
the logic seems good, I do not know about the performance

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

Hi,

I have no issues in positioning the pointer at the required date, my issues are only with positioning the pointer at the required rank within it. Also I need all the rows below that (this includes the rows with other dates below this too).

enrico-sorichetti · Posted: Fri Nov 02, 2007 2:55 am

Have You looked at my select sketches.. ?
the first one positions to the date,

the second retrieves exactly what You need.. OR NOT ??? :-)

the first query will retrieve 9/9/2007
( the first date lower then 10/1/2007)

the second one one splits the where clause in two

selecting date less then 9/9/2007 regardless of the rank

and for 9/9/2007 selects the rows with a rank greater then 5

I tend to believe that it should work

nbalajibe · New User Joined: 28 Nov 2006 Posts: 75 Location: India

Hi E.S.,

Thanks for the query. I tried this and it works fine.

dick scherrer · Posted: Fri Nov 02, 2007 8:02 am

Good to hear it is now working

Thank you for posting the good news

d