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
 

 

In DB2 how to divide a table in to exactly half

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

New User


Joined: 21 May 2008
Posts: 22
Location: chennai

PostPosted: Thu May 22, 2008 4:17 pm    Post subject: In DB2 how to divide a table in to exactly half
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: 22
Location: chennai

PostPosted: Thu May 22, 2008 4:30 pm    Post subject: Reply to: [color=green]In DB2 how can u divide a table in to
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    Post subject:
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: 22
Location: chennai

PostPosted: Thu May 22, 2008 5:13 pm    Post subject:
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    Post subject:
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: 1288
Location: Chennai, India

PostPosted: Thu May 22, 2008 5:58 pm    Post subject: Reply to: In DB2 how to divide a table in to exactly half
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    Post subject:
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: 2150
Location: At my coffee table

PostPosted: Thu May 22, 2008 8:36 pm    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Thu May 22, 2008 8:45 pm    Post subject: Reply to: In DB2 how to divide a table in to exactly half
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: 1288
Location: Chennai, India

PostPosted: Thu May 22, 2008 8:45 pm    Post subject: Reply to: In DB2 how to divide a table in to exactly half
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: 2150
Location: At my coffee table

PostPosted: Thu May 22, 2008 9:26 pm    Post subject: Re: Reply to: In DB2 how to divide a table in to exactly hal
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

Active Member


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

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

The commitee must await the next budget cycle.
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: Fri May 23, 2008 4:51 am    Post subject:
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    Post subject:
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: 22
Location: chennai

PostPosted: Fri May 23, 2008 9:18 am    Post subject:
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    Post subject:
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: 22
Location: chennai

PostPosted: Fri May 23, 2008 10:09 am    Post subject:
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

Site Director


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

PostPosted: Fri May 23, 2008 9:56 pm    Post subject:
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: 22
Location: chennai

PostPosted: Mon May 26, 2008 11:20 am    Post subject:
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.    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Command UNIQUE CONSTRAINT on any tabl... rohanthengal DB2 2 Thu Aug 18, 2016 3:48 pm
No new posts Updating online Db2 table kishpra DB2 3 Fri Aug 12, 2016 2:44 pm
No new posts Getting soc4 on internal table in cobol abdulrafi COBOL Programming 13 Fri May 06, 2016 3:39 pm
No new posts Difference space showed for TS and it... autobox DB2 1 Thu Apr 14, 2016 1:07 pm


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