Need Help on a query

[shorti]

db2 v 8.2 on AIX 5.3

I will try to explain as brief as I can what it is I need. I am
building a function that will be called multiple times where I will
need to return x amount of records each time for the same basic
query. I will be basically get passed in where I had left off the
last call but it is two fields that make up this unique index.

The main rule is that I cannot hold open a cursor on the record I last
read because it will be a long operation. That is the reason I will
be given the two fields to identify where I left off.

The trick is there is not a single column in this table that makes a
unique index so I cannot just order it like that. The two fields
together are the unique index.

In saying that, I am having trouble finding the right SQL to get my
cursor to the place I want to start my next set of queries...until all
the records that meet the criteria are read.

Here is an example:

COL1 and COL2 together are the unique index. I order by COL1, COL2
each time.

TABLE1

COL1 COL2 COL3
200 0 1 <---Start of the range <----*
200 2 1 <----*
200 6 1 <----*
300 0 1
300 2 1
300 6 1
300 15 1
300 18 1
400 0 1
400 6 1
400 7 1 <---End of the range
500 0 1
500 2 1
600 0 1
600 2 1
200 0 2
So, let us say I can only fetch 3 records at a time. The first time
the function is called I am told to get all records that have a COL3 =
1. I am also told the range that is needed from COL1.

So my query is something like "select COL1, COL2, COL3 from TABLE1
where COL3 = 1 and COL1 between 200 and 400 sort by COL1, COL2"

I would return the records indicated with a <---* above.

The next time the function is called parameters passed in tell me this
is not the first call by giving me back the last value of COL1 (which
is 200) and the last value of COL2(which is 6). So I know where I
left off but how do I get myself at that point again so that I can
continue and return the next 3 records?

I basically want to start the next fetch on the record right after
COL1 = 200 and COL2 = 6, and continue...until I hit the end of the
sort which would be where COL1 = 400.

This would be easy if I had one unique index that I can order but I am
not sure what to do with two where I cannot say WHERE a certain #.

Thanks for helping!

[Tonkuma]

On Mar 8, 9:20 am, "shorti" <[e-mail]:

db2 v 8.2 on AIX 5.3

I will try to explain as brief as I can what it is I need. I am
building a function that will be called multiple times where I will
need to return x amount of records each time for the same basic
query. I will be basically get passed in where I had left off the
last call but it is two fields that make up this unique index.

The main rule is that I cannot hold open a cursor on the record I last
read because it will be a long operation. That is the reason I will
be given the two fields to identify where I left off.

The trick is there is not a single column in this table that makes a
unique index so I cannot just order it like that. The two fields
together are the unique index.

In saying that, I am having trouble finding the right SQL to get my
cursor to the place I want to start my next set of queries...until all
the records that meet the criteria are read.

Here is an example:

COL1 and COL2 together are the unique index. I order by COL1, COL2
each time.

TABLE1

COL1 COL2 COL3
200 0 1 <---Start of the range <----*
200 2 1 <----*
200 6 1 <----*
300 0 1
300 2 1
300 6 1
300 15 1
300 18 1
400 0 1
400 6 1
400 7 1 <---End of the range
500 0 1
500 2 1
600 0 1
600 2 1
200 0 2

So, let us say I can only fetch 3 records at a time. The first time
the function is called I am told to get all records that have a COL3 =
1. I am also told the range that is needed from COL1.

So my query is something like "select COL1, COL2, COL3 from TABLE1
where COL3 = 1 and COL1 between 200 and 400 sort by COL1, COL2"

I would return the records indicated with a <---* above.

The next time the function is called parameters passed in tell me this
is not the first call by giving me back the last value of COL1 (which
is 200) and the last value of COL2(which is 6). So I know where I
left off but how do I get myself at that point again so that I can
continue and return the next 3 records?

I basically want to start the next fetch on the record right after
COL1 = 200 and COL2 = 6, and continue...until I hit the end of the
sort which would be where COL1 = 400.

This would be easy if I had one unique index that I can order but I am
not sure what to do with two where I cannot say WHERE a certain #.

Thanks for helping!

WHERE COL3 = 1 and COL1 between 200 and 400
AND (COL1 last_col1
OR
COL1 = last_col1 AND COL2 last_col2
)
ORDER BY COL1, COL2
FETCH FIRST 3 ROWS ONLY

[Lennart]

On Mar 8, 1:20 am, "shorti" <[e-mail]:

db2 v 8.2 on AIX 5.3

I will try to explain as brief as I can what it is I need. I am
building a function that will be called multiple times where I will
need to return x amount of records each time for the same basic
query. I will be basically get passed in where I had left off the
last call but it is two fields that make up this unique index.

The main rule is that I cannot hold open a cursor on the record I last
read because it will be a long operation. That is the reason I will
be given the two fields to identify where I left off.

The trick is there is not a single column in this table that makes a
unique index so I cannot just order it like that. The two fields
together are the unique index.

In saying that, I am having trouble finding the right SQL to get my
cursor to the place I want to start my next set of queries...until all
the records that meet the criteria are read.

Here is an example:

COL1 and COL2 together are the unique index. I order by COL1, COL2
each time.

TABLE1

COL1 COL2 COL3
200 0 1 <---Start of the range <----*
200 2 1 <----*
200 6 1 <----*
300 0 1
300 2 1
300 6 1
300 15 1
300 18 1
400 0 1
400 6 1
400 7 1 <---End of the range
500 0 1
500 2 1
600 0 1
600 2 1
200 0 2

So, let us say I can only fetch 3 records at a time. The first time
the function is called I am told to get all records that have a COL3 =
1. I am also told the range that is needed from COL1.

So my query is something like "select COL1, COL2, COL3 from TABLE1
where COL3 = 1 and COL1 between 200 and 400 sort by COL1, COL2"

I would return the records indicated with a <---* above.

The next time the function is called parameters passed in tell me this
is not the first call by giving me back the last value of COL1 (which
is 200) and the last value of COL2(which is 6). So I know where I
left off but how do I get myself at that point again so that I can
continue and return the next 3 records?

I basically want to start the next fetch on the record right after
COL1 = 200 and COL2 = 6, and continue...until I hit the end of the
sort which would be where COL1 = 400.

This would be easy if I had one unique index that I can order but I am
not sure what to do with two where I cannot say WHERE a certain #.

Thanks for helping!

Not sure I fully understand but would something along the lines of:

select col1, col2, col3 from (
select COL1, COL2, COL3, row_number() over (
order by COL1, COL2 ) as rn
from TABLE1
where COL3 = 1 and COL1 between 200 and 400
) X where rn between 0 and 3

do? rn is the *cursor* position, so next time you run the query it
would be:

....
) X where rn between 4 and 7

etc
/Lennart

[shorti]

On Mar 7, 10:43 pm, "Tonkuma" <[e-mail]:

On Mar 8, 9:20 am, "shorti" <[e-mail]:

db2 v 8.2 on AIX 5.3

I will try to explain as brief as I can what it is I need. I am
building a function that will be called multiple times where I will
need to return x amount of records each time for the same basic
query. I will be basically get passed in where I had left off the
last call but it is two fields that make up this unique index.

The main rule is that I cannot hold open a cursor on the record I last
read because it will be a long operation. That is the reason I will
be given the two fields to identify where I left off.

The trick is there is not a single column in this table that makes a
unique index so I cannot just order it like that. The two fields
together are the unique index.

In saying that, I am having trouble finding the right SQL to get my
cursor to the place I want to start my next set of queries...until all
the records that meet the criteria are read.

Here is an example:

COL1 and COL2 together are the unique index. I order by COL1, COL2
each time.

TABLE1

COL1 COL2 COL3
200 0 1 <---Start of the range <----*
200 2 1 <----*
200 6 1 <----*
300 0 1
300 2 1
300 6 1
300 15 1
300 18 1
400 0 1
400 6 1
400 7 1 <---End of the range
500 0 1
500 2 1
600 0 1
600 2 1
200 0 2

So, let us say I can only fetch 3 records at a time. The first time
the function is called I am told to get all records that have a COL3 =
1. I am also told the range that is needed from COL1.

So my query is something like "select COL1, COL2, COL3 from TABLE1
where COL3 = 1 and COL1 between 200 and 400 sort by COL1, COL2"

I would return the records indicated with a <---* above.

The next time the function is called parameters passed in tell me this
is not the first call by giving me back the last value of COL1 (which
is 200) and the last value of COL2(which is 6). So I know where I
left off but how do I get myself at that point again so that I can
continue and return the next 3 records?

I basically want to start the next fetch on the record right after
COL1 = 200 and COL2 = 6, and continue...until I hit the end of the
sort which would be where COL1 = 400.

This would be easy if I had one unique index that I can order but I am
not sure what to do with two where I cannot say WHERE a certain #.

Thanks for helping!

WHERE COL3 = 1 and COL1 between 200 and 400
AND (COL1 last_col1
OR
COL1 = last_col1 AND COL2 last_col2
)
ORDER BY COL1, COL2
FETCH FIRST 3 ROWS ONLY- Hide quoted text -

- Show quoted text -

I think this will work. I tried some similar variations that I found
holes in but so far this one seems to work in all cases. I knew I was
just missing the right combo..thanks.

>Not sure I fully understand but would something along the lines of:

select col1, col2, col3 from (
select COL1, COL2, COL3, row_number() over (
order by COL1, COL2 ) as rn
from TABLE1
where COL3 = 1 and COL1 between 200 and 400
) X where rn between 0 and 3

do? rn is the *cursor* position, so next time you run the query it
would be:
...
) X where rn between 4 and 7

Lennart, Thanks for the info. I dont think this will work well in
this case since I would have to keep track of the cursor position in a
global and there would be multiple similar calls...each having it's
own cursor position. The initial caller stores the info in it's own
database so it can easily find and return the last information of COL1
and COL2. However, for them to return to me a the last cursor
position would require a lot of changes between the database and the
messaging components so I dont think they will go for it.