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

In DB2 how to divide a table in to exactly half


IBM Mainframe Forums -> DB2
Post new topic   This topic is locked: you cannot edit posts or make replies.
View previous topic :: View next topic  
Author Message
mighty

New User


Joined: 21 May 2008
Posts: 26
Location: chennai

PostPosted: Thu May 22, 2008 4:17 pm
Reply with quote

In DB2 how to divide a table in to exactly half.
Back to top
View user's profile Send private message
mighty

New User


Joined: 21 May 2008
Posts: 26
Location: chennai

PostPosted: Thu May 22, 2008 4:30 pm
Reply with quote

Sorry,the exact question is In DB2 how can u divide a table in to exactly half?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu May 22, 2008 5:00 pm
Reply with quote

what do you mean 'in half?'.
Back to top
View user's profile Send private message
mighty

New User


Joined: 21 May 2008
Posts: 26
Location: chennai

PostPosted: Thu May 22, 2008 5:13 pm
Reply with quote

A table is having some n number of records,so i need exactly n/2 records in my output file.How can we achieve this.
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Thu May 22, 2008 5:37 pm
Reply with quote

Hi,

Code:

SELECT COUNT *
   FROM TABLE
   INTO  VAR1

SELECT *
  FROM TABLE
 FETCH FIRST VAR1 ROWS ONLY

Hope this would help you.
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India

PostPosted: Thu May 22, 2008 5:58 pm
Reply with quote

bharath,

Quote:
SELECT COUNT *
FROM TABLE
INTO VAR1

SELECT *
FROM TABLE
FETCH FIRST VAR1 ROWS ONLY


Did you test this SQL?
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Thu May 22, 2008 6:13 pm
Reply with quote

Sorry Aaru, I became a bit complacent.


Code:

SELECT COUNT *
  FROM TABLE
  INTO VAR1

COMPUTE VAR2 = VAR1 / 2.

SELECT COUNT *
  FROM TABLE
 FETCH FIRST VAR2 ROWS ONLY

Would this solve mighty's problem?
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Thu May 22, 2008 8:36 pm
Reply with quote

So much for a solution, but why would you need to "divide a table in to exactly half"?
What if there is an odd number of rows?
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10886
Location: italy

PostPosted: Thu May 22, 2008 8:45 pm
Reply with quote

Quote:
.... but why would you need to "divide a table in to exactly half"?

/standard answer on
that' s the requirement
/standard answer off icon_biggrin.gif

Quote:
What if there is an odd number of rows?

for that we appointed a committee to build a business case to implement it in the next release
Back to top
View user's profile Send private message
Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India

PostPosted: Thu May 22, 2008 8:45 pm
Reply with quote

bharath,

Quote:
Sorry Aaru, I became a bit complacent.


Not a Problem. But did u test this before posting? were you able to divide the table?

As far as i know COUNT should be coded as COUNT(*) and not as shown in your SQL.


I also doubt whether the INTO keyword is acceptable.

Cics Guy,

Good Question.
Back to top
View user's profile Send private message
CICS Guy

Senior Member


Joined: 18 Jul 2007
Posts: 2146
Location: At my coffee table

PostPosted: Thu May 22, 2008 9:26 pm
Reply with quote

enrico-sorichetti wrote:
Quote:
.... but why would you need to "divide a table in to exactly half"?

/standard answer on
that' s the requirement
/standard answer off icon_biggrin.gif

Quote:
What if there is an odd number of rows?

for that we appointed a committee to build a business case to implement it in the next release
ROTFL.... icon_lol.gif
Back to top
View user's profile Send private message
Phrzby Phil

Senior Member


Joined: 31 Oct 2006
Posts: 1049
Location: Richmond, Virginia

PostPosted: Fri May 23, 2008 1:03 am
Reply with quote

The commitee must await the next budget cycle.
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Fri May 23, 2008 4:51 am
Reply with quote

icon_smile.gif

All of this and not a table divide in sight. . .

Quote:
so i need exactly n/2 records in my output file.
Please show an example of some table with 10 rows in it and how you want 1/2 of them in an output file.

If you were to run this on 5 consecutive days should the same 1/2 always be selected? Said differently, do inserts & deletes matter?

What are you trying to accomplish?
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Fri May 23, 2008 7:42 am
Reply with quote

Unload
use sort to create two new files.
load (twice)
Back to top
View user's profile Send private message
mighty

New User


Joined: 21 May 2008
Posts: 26
Location: chennai

PostPosted: Fri May 23, 2008 9:18 am
Reply with quote

We will be doing inserts and updates after that and we have so many criteria after that like in one half we need to sort and find out some data and so on.......

So i just wanted a query to find out exactly 1/2 records of the table.

Thanks for all ur updates......
Back to top
View user's profile Send private message
the_gautam

Active User


Joined: 05 Jun 2005
Posts: 165
Location: Bangalore

PostPosted: Fri May 23, 2008 9:22 am
Reply with quote

I ve not checked it on mainframes. However, it is working for me on plain SQL. My table was having an ascending TIMESTAMP column.

Code:

SELECT * FROM MYTABLE A WHERE (SELECT COUNT(*) / 2 FROM MYTABLE B) > (SELECT COUNT(*) FROM MYTABLE B WHERE A.MYTIMESTAMP>B.MYTIMESTAMP)
Back to top
View user's profile Send private message
mighty

New User


Joined: 21 May 2008
Posts: 26
Location: chennai

PostPosted: Fri May 23, 2008 10:09 am
Reply with quote

Gautam i dont think it will work for me.Beacause none of my coloumns is sorted in any order.
_____________
Thanks,
Mighty
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


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

PostPosted: Fri May 23, 2008 9:56 pm
Reply with quote

Hello,

Quote:
We will be doing inserts and updates after that and we have so many criteria after that like in one half we need to sort and find out some data and so on.......

So i just wanted a query to find out exactly 1/2 records of the table.
If you continue to refuse to provide your actual requirement, we can't help you. You may believe you have provided proper info for us to make suggestions - you have not done so.

You have not defined what 1/2 means and you have not defined what you want to do with the 1/2.

If you can't or won't post some example data and how you want the process to work we will need to close this topic.
Back to top
View user's profile Send private message
mighty

New User


Joined: 21 May 2008
Posts: 26
Location: chennai

PostPosted: Mon May 26, 2008 11:20 am
Reply with quote

Thanks all for your help.
Thank you Goutam, its working for me with the query which you have provided...
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   This topic is locked: you cannot edit posts or make replies. View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Unload and Load ISPF Table TSO/ISPF 4
No new posts ISPF Table to add a new column TSO/ISPF 1
No new posts Inserting into table while open selec... DB2 1
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
Search our Forums:

Back to Top