IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Positioning pointer at a specified record of a cursor result


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Sat Oct 27, 2007 9:05 pm
Reply with quote

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
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Sun Oct 28, 2007 3:48 am
Reply with quote

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.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Sun Oct 28, 2007 7:05 am
Reply with quote

Quote:
I could not give this particular field in the where clause as the column is not sorted (eventhough the field is in the index).


What does that have to do with using the column in the where clause?
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Tue Oct 30, 2007 4:48 am
Reply with quote

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?
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Tue Oct 30, 2007 4:52 am
Reply with quote

dick scherrer wrote:

when you reach the position you want, process as needed om that point.


Hi Dick,

I am searhing for a solution to reach the required position. Do you mean that I should have a loop in my cobol program until I reach that point?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Tue Oct 30, 2007 5:50 am
Reply with quote

nbalajibe wrote:
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?


My question is why can you not use the field in a where cluase?
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Tue Oct 30, 2007 6:07 am
Reply with quote

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.
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Tue Oct 30, 2007 6:13 am
Reply with quote

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.
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Tue Oct 30, 2007 6:29 am
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Oct 31, 2007 2:01 am
Reply with quote

Hello,

Quote:
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
Unless there is a typo or i still misunderstand, rows with a rank less than 5 are not supposed to be selected?

If you try something like
Code:

select DOJ, name, rank
  from tabl where DOJ  <= '2007-10-01'
              and rank => '5'
  order by DOJ desc, rank
what are the results?
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Wed Oct 31, 2007 3:31 am
Reply with quote

dick scherrer wrote:

select DOJ, name, rank
from tabl where DOJ <= '2007-10-01'
and rank => '5'
order by DOJ desc, rank[/code]what are the results?


Hi Dick,

I need all the records below that particular entry, hence i need a row even if the rank is less than 5 in the next rows. The query given does not retrieve the rank below 5 in the next date, because the fields are first ordered by date and then by rank.

Also the index is built as "date desc and rank asc" hence the order of rank within a date is known, but the ranks in the other date might have any value. Hence I am searching a logic to place the pointer at a particular row (the value of which is known) as I have mentioned earlier (a call similar to a GU in IMS to position the pointer).
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Oct 31, 2007 6:55 am
Reply with quote

Hi Shyam,

Yes, i misunderstood - again icon_confused.gif 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.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Wed Oct 31, 2007 1:38 pm
Reply with quote

Quote:
he DB is sorted


I mildly suggest that You review Your understanding of how a relational DB works...

a relational database/table is LOGICALLY NEVER sorted on anything...

all the INDEXes You might define are there mainly for performance reasons..
( forget for a moment about foreign keys and unique keys )

the most important relational DB programming paradigm is..
never assume that the rows retrieved are in any specific order...
unless in Your query You specify a clause 'ORDER BY'

sometimes INDEXES are built by the the DB support team,
without application programmer' s awareness,
because of application performance tuning
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Nov 01, 2007 2:18 am
Reply with quote

Quote:

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


Code:
select DOJ, name, rank
from tabl where DOJ <= '2007-10-01'
order by DOJ desc, rank


the above query will give exactly Your ouput,
but please explain why 9/9/2007 xxx 5
was not in the output ( maybe a typo )

using a cursor will let You scan the records selected and take any
action You like..
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Thu Nov 01, 2007 2:58 am
Reply with quote

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?
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Thu Nov 01, 2007 3:10 am
Reply with quote

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. icon_sad.gif
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Thu Nov 01, 2007 3:14 am
Reply with quote

nbalajibe wrote:
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 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. icon_sad.gif


I have corrected the typing error of the previous post.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Thu Nov 01, 2007 4:34 am
Reply with quote

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
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Fri Nov 02, 2007 2:28 am
Reply with quote

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).
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10872
Location: italy

PostPosted: Fri Nov 02, 2007 2:55 am
Reply with quote

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
Back to top
View user's profile Send private message
nbalajibe
Warnings : 1

New User


Joined: 28 Nov 2006
Posts: 75
Location: India

PostPosted: Fri Nov 02, 2007 5:49 am
Reply with quote

Hi E.S.,

Thanks for the query. I tried this and it works fine. icon_smile.gif
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Fri Nov 02, 2007 8:02 am
Reply with quote

Good to hear it is now working icon_smile.gif

Thank you for posting the good news icon_smile.gif

d
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts How to split large record length file... DFSORT/ICETOOL 10
No new posts SFTP Issue - destination file record ... All Other Mainframe Topics 2
No new posts FINDREP - Only first record from give... DFSORT/ICETOOL 3
No new posts To find whether record count are true... DFSORT/ICETOOL 6
No new posts Validating record count of a file is ... DFSORT/ICETOOL 13
Search our Forums:

Back to Top