Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Need a Query to split the results
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
boo_sri

New User


Joined: 26 Jul 2005
Posts: 36
Location: charlotte

PostPosted: Wed Sep 16, 2009 2:12 am    Post subject: Need a Query to split the results
Reply with quote

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
View user's profile Send private message

dick scherrer

Site Director


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

PostPosted: Wed Sep 16, 2009 2:19 am    Post subject:
Reply with quote

Hello,

Unload the data and split the file using your sort product.
Back to top
View user's profile Send private message
boo_sri

New User


Joined: 26 Jul 2005
Posts: 36
Location: charlotte

PostPosted: Wed Sep 16, 2009 3:07 am    Post subject:
Reply with quote

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

Site Director


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

PostPosted: Wed Sep 16, 2009 3:27 am    Post subject:
Reply with quote

Hello,

Quote:
Any other suggestion in query level?
Not if it is important that a complete set of data be created. . .
Back to top
View user's profile Send private message
sachin_star3
Warnings : 1

New User


Joined: 30 Sep 2006
Posts: 78
Location: pune

PostPosted: Sat Sep 19, 2009 1:28 am    Post subject: Reply to: Need a Query to split the results
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Sep 22, 2009 6:39 pm    Post subject:
Reply with quote

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
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Wed Sep 23, 2009 9:08 am    Post subject: Simply idea
Reply with quote

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

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Sep 23, 2009 12:27 pm    Post subject:
Reply with quote

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
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Wed Sep 23, 2009 4:12 pm    Post subject: using indexes
Reply with quote

With this small change we are guaranty to use index. icon_exclaim.gif

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 icon_question.gif

Lenny
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Sep 23, 2009 4:29 pm    Post subject:
Reply with quote

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
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Wed Sep 23, 2009 4:47 pm    Post subject:
Reply with quote

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 ! icon_lol.gif

But take it easy.... You are the best. icon_exclaim.gif

Lenny
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Wed Sep 23, 2009 5:20 pm    Post subject: the easiest way
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Sep 23, 2009 5:34 pm    Post subject:
Reply with quote

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
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Wed Sep 23, 2009 5:44 pm    Post subject:
Reply with quote

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
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Sep 23, 2009 5:51 pm    Post subject:
Reply with quote

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
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Wed Sep 23, 2009 5:58 pm    Post subject:
Reply with quote

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). icon_cool.gif

Lenny
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Wed Sep 23, 2009 6:04 pm    Post subject:
Reply with quote

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

Site Director


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

PostPosted: Wed Sep 23, 2009 9:20 pm    Post subject:
Reply with quote

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

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Thu Sep 24, 2009 5:42 am    Post subject: Comments
Reply with quote

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 icon_question.gif

Lenny
Back to top
View user's profile Send private message
lkhiger

New User


Joined: 28 Oct 2005
Posts: 89

PostPosted: Thu Sep 24, 2009 6:43 am    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm
No new posts Low values Results from VARCHAR FORMAT balaji81_k DB2 10 Thu Oct 20, 2016 1:18 am
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us