|
View previous topic :: View next topic
|
| Author |
Message |
boo_sri
New User
Joined: 26 Jul 2005
Posts: 36
Location: charlotte
|
|
|
|
Hi -
I have a query which involves multiple tables and will 200,000 thousand rows. I want the result set in the following manner:
Set 1 - 50,000 Records
Set 2 - 50,001 - 100,000
Set 3 - 100,101 - 150,000
The DB is DB2 V8 for ZOS.
can anybody suggest a suitable method to accomplish this requirement.
Thanks,
Boopathy |
|
| Back to top |
|
 |
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
Unload the data and split the file using your sort product. |
|
| Back to top |
|
|
boo_sri
New User
Joined: 26 Jul 2005
Posts: 36
Location: charlotte
|
|
|
|
| Query is being executed QTP tool which will in turn populate the excel directly.So we dont want to use UNLOAD utility. Any other suggestion in query level? |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| Any other suggestion in query level? |
Not if it is important that a complete set of data be created. . . |
|
| Back to top |
|
|
sachin_star3
Warnings : 1
New User
Joined: 30 Sep 2006
Posts: 78
Location: pune
|
|
|
|
Use the scrollable cursor logic with temp table here.
Find the psesudo code below.
ID DIVISION.
DATA DIVISION.
W-S SECTION.
1.
declare cursor cur1 insensitive cursor
for select * from a,b,c where <condition> fetch first 50000 rows only.
2.
declare cursor cur2 insensitive cursor
for select * from a,b,c where <condition> fetch first 100000 rows only.
3.DECLARE TEMP TABLE
(COLUMMN A,
COLUMNB,
COLUMN C)
DECLARE TABLE SESSION.RESULT .
PROCEDURE DIVISION.
EXEC-SQL
OPEN CUR1
END-EXEC.
EXEC-SQL
OPEN CUR2
END-EXEC.
*SCROLLABLE CURSOR LOGIC.
FETCH 1 USING CUR1
INTO
WS-COLUMN A,
WS-COLUMN B,
WS-COLUMN C
INSERT INTO TEMP TABLE. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
there's no easy solution for this in V8, especially not when some order is involved.
in V9 you might use quantile runstats to determine the cut off points |
|
| Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
It's easy....
Lets keyid some key from table1.
Idea is enumeration of rows in the table and use rowno for get exact number of rows:
| Code: |
select t1.*
From
(select t2.*, rowno
from table1 t2
, table
(select count(*) + 1 rowno from table1 t3
where t2.keyid > t3.keyid ) t4
) t1
Where t1.rowno <= 50000
|
... ETC... 2 times ...
But I don't like this solution because I have to repeat the enumeration 3 times....
Better solution to use enumeration implicity:
1st result table:
| Code: |
select t1.*
from table1 t1
, table
(select count(*) + 1 from table1 t2
where t1.keyid > t2.keyid
having count(*) + 1 <= 50000 ) t2 |
2nd result table:
| Code: |
select t1.*
from table1 t1
, table
(select count(*) + 1 from table1 t2
where t1.keyid > t2.keyid
having count(*) + 1 between 50001 and 100000) t2 |
2rd result table:
| Code: |
select t1.*
from table1 t1
, table
(select count(*) + 1 from table1 t2
where t1.keyid > t2.keyid
having count(*) + 1 >= 100001) t2 |
Has to work, if not, I did not make test, use the first solution.
Lenny  |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
easy, sure if you don't mind 200.000 Tablescans (or indexscans)
If you really want to number the rows : a faster way (or better : a not so slow way):
| Code: |
With enum (lvl, col1, col2,....,) as (
select 1 , t1.* from table1
where T1.keyid = (select min(t2.keyid) from table1 t2)
union all
select e.lvl + 1,t1.* from enum e , table1 t1
where T1.keyid = (select min(t2.keyid) from table1 t2 where t2.keyid > e.keyid)
and e.lvl < 150000
)
select * from enum where lvl > 50000 |
for the middle one |
|
| Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
With this small change we are guaranty to use index. 
1st result table:
| Code: |
select t1.*
from table1 t1
, table
(select count(*) + 1, t1.keyid
from table1 t2
where t1.keyid > t2.keyid
having count(*) + 1 <=50000) t2
where t1.keyid = t2.keyid |
2nd result table:
| Code: |
select t1.*
from table1 t1
, table
(select count(*) + 1, t1.keyid
from table1 t2
where t1.keyid > t2.keyid
having count(*) + 1 between 50001 and 150000) t2
where t1.keyid = t2.keyid |
2rd result table:
| Code: |
select t1.*
from table1 t1
, table
(select count(*) + 1, t1.keyid
from table1 t2
where t1.keyid > t2.keyid
having count(*) + 1 >= 150001) t2
where t1.keyid = t2.keyid |
Why we don't use group by 
Lenny |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
Lenny,
lets take the third query :
& the fact that table1 has 200.000 rows :
Questions :
How many times do you execute the subselect ?
How many rows (=indexentries) have you touched at the end of the query ?
Answer :
1) 200.000 times select count(*) (using an index)
2) 20.000.100.000 (20 billion) index entries have been touched. |
|
| Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
| GuyC wrote: |
Lenny,
lets take the third query :
& the fact that table1 has 200.000 rows :
Questions :
How many times do you execute the subselect ?
How many rows (=indexentries) have you touched at the end of the query ?
Answer :
1) 200.000 times select count(*) (using an index)
2) 20.000.100.000 (20 billion) index entries have been touched. |
This is kind of humor ! 
But take it easy.... You are the best.
Lenny |
|
| Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
1st result table:
| Code: |
select * from table1
order by keyid
Fetch first 50000 rows only |
Then from RS we get some value of keyid in last row and use it get
2nd result table:
| Code: |
select * from table1
where keyid > last1keyid
order by keyid
Fetch first 100000 rows only |
Again, from RS we get some value of keyid in last row and use it get
3rd result table:
| Code: |
select * from table1
where keyid > last2keyid
order by keyid
Fetch first 50000 rows only |
Lenny |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
I wasn't going to respond to your posts anymore, but if you give wrong/incomplete information I feel obligated to the TS.
if you want to fetch first and then use a value of the lastrow,
you must use order by. (which doesn't have to be an actual sort, it probably can be solved through an index)
You can not assume that a select without "order by" will return rows in any specific order (well, you can, but you'd be wrong) |
|
| Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
| GuyC wrote: |
I wasn't going to respond to your posts anymore, but if you give wrong/incomplete information I feel obligated to the TS.
if you want to fetch first and then use a value of the lastrow,
you must use order by. (which doesn't have to be an actual sort, it probably can be solved through an index)
You can not assume that a select without "order by" will return rows in any specific order (well, you can, but you'd be wrong) |
You have to watch before answer.
But anyway, even if you don't use order by for keyid it will come in sorted by keyid order, if keyid is primary key of the table.
Sincerely, Lenny |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| lkhiger wrote: |
But anyway, even if you don't use order by for keyid it will come in sorted by keyid order, if keyid is primary key of the table.
|
Wrong again |
|
| Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
| GuyC wrote: |
| lkhiger wrote: |
But anyway, even if you don't use order by for keyid it will come in sorted by keyid order, if keyid is primary key of the table.
|
Wrong again |
I am always wrong but my queries are working good ! (From practice). 
Lenny |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
try
select * from sysibm.systables
and see in what order that comes.
please enlighten me :If DB2 uses a Tablescan (usually when Select with no where or order by) in what order do your rows get returned :
A) Primary key
B) Clustering index
C) physical order of the pages which resembles clustering index but not necessary so. |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| But anyway, even if you don't use order by for keyid it will come in sorted by keyid order, if keyid is primary key of the table. |
Why would you believe this - it is completely wrong. . .
| Quote: |
| but my queries are working good |
That would be depend on how "good" is being measured. Just because something happens to run with no abend does not qualify it as "good".
You really should be aware that mis-information is worse than no information.
| Quote: |
| but if you give wrong/incomplete information I feel obligated to the TS. |
Hopefully, you will continue  |
|
| Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
1st result table:
| Code: |
select * from table1
order by keyid
Fetch first 50000 rows only |
Then from RS we get some value of keyid in last row and use it get
2nd result table:
| Code: |
select * from table1
where keyid > last1keyid
order by keyid
Fetch first 100000 rows only |
Again, from RS we get some value of keyid in last row and use it get
3rd result table:
| Code: |
select * from table1
where keyid > last2keyid
order by keyid
Fetch first 50000 rows only |
I'd like to show these 3 simple queries again.
See the previous page.
Can you see ORDER BY now
Lenny |
|
| Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
You can get the 3rd result table in a different way:
| Code: |
select * from table1
order by keyid desc
Fetch first 50000 rows only |
In this query we didn't use last keyid from 2nd table, but we got rows in descending order of keyid.
Lenny |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
no need to be rude.
At the time of my post about missing order by there was none. It's not my fault that you edit it afterwards and then play innocent.
you have now flooded this thread with a lot of BS and finally came to an answer that was already given in post 5 (which is also missing order by) |
|
| Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
| GuyC wrote: |
no need to be rude.
At the time of my post about missing order by there was none. It's not my fault that you edit it afterwards and then play innocent.
you have now flooded this thread with a lot of BS and finally came to an answer that was already given in post 5 (which is also missing order by) |
Forget about. It's not interesting for me anymore.
The problem was too easy, but you said ... it is too difficult in V8.  See your message.
That's why I shown to author the few ideas how to solve it.
Buy-buy ORDER BY !
Lenny
Lenny |
|
| Back to top |
|
|
lkhiger
New User
Joined: 28 Oct 2005
Posts: 89
|
|
|
|
| GuyC wrote: |
there's no easy solution for this in V8, especially not when some order is involved.
in V9 you might use quantile runstats to determine the cut off points |
 In V9 we can use the function
which is not required KEYID.
Lenny |
|
| Back to top |
|
|
|
|
