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
 

 

how to select the rows from a perticular partetion?

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sumannaidu

New User


Joined: 21 May 2005
Posts: 13
Location: Bangalore

PostPosted: Mon Feb 20, 2006 11:50 am    Post subject: how to select the rows from a perticular partetion?
Reply with quote

Hi,

can anyone tell me how to retrive some specific rows from a perticular partetion.

suppose if we have 20 partetions in a table and if we want to retrive the rows from 11th partetion how to do that?

rgds
suman
Back to top
View user's profile Send private message

ragshere

New User


Joined: 20 Dec 2004
Posts: 70

PostPosted: Mon Feb 20, 2006 4:04 pm    Post subject: Re: how to select the rows from a perticular partetion?
Reply with quote

Hi,
It is not possble to retrieve data from particular partition thru SQL query. I mean we can't give the part number in the query.

If we know the keys in paritioning index and values for those keys in part 11 ,then we can use those keys. so that DB2 will get the data from part 11.


Thanks
Rags
Back to top
View user's profile Send private message
sumannaidu

New User


Joined: 21 May 2005
Posts: 13
Location: Bangalore

PostPosted: Mon Feb 20, 2006 6:30 pm    Post subject: Re: how to select the rows from a perticular partetion?
Reply with quote

Hi rags

can you please explain the above with one example.


rgds
suman
Back to top
View user's profile Send private message
MichaelKBS

New User


Joined: 10 Jan 2006
Posts: 24
Location: Germany

PostPosted: Mon Feb 20, 2006 8:26 pm    Post subject: Re: how to select the rows from a perticular partetion?
Reply with quote

sumannaidu wrote:
Hi,
suppose if we have 20 partetions in a table and if we want to retrive the rows from 11th partetion how to do that?


Hi Suman,

if you're using DB2 Version 7 or higher you can do that with UNLOAD-Utility by specifying the PART option
see: http://www.redbooks.ibm.com/abstracts/SG246289.html?Open


regards,
Michael
Back to top
View user's profile Send private message
ragshere

New User


Joined: 20 Dec 2004
Posts: 70

PostPosted: Tue Feb 21, 2006 1:22 pm    Post subject: Re: how to select the rows from a perticular partetion?
Reply with quote

Hi,
we can use unload utility anyway.....you can get data from particular partition and with particular criteria...

In my previous mail, I talked about getting the data through SQL...

suman, see this example...

suppose a partition table YEAR_DATA has 12 parts and stores the turnovers of a firm for all its branches.

and each part will contain each month data.

If the tables contain below columns

month - integer
branch_id - char
turnover - decimal

table data is like below

month branch_id turnover
4 b0010 300000
3 b0101 10000
5 b0010 500000
1 b0001 20000
5 b0001 50000


partitioning index defined on month column (values 1 to 12).one part is for one month.

In this case we can get the data from required partition through sql by giving month value in where clause.

for the query

select turnover from year_data
where month = 5
and branch_id='b0010';


This query will get the data directly from partition 5 and it will get the turnover (500000) for branch b00010. It won't scan entire tablespace.

Hope you got me.

Thanks
Rags
Back to top
View user's profile Send private message
sumannaidu

New User


Joined: 21 May 2005
Posts: 13
Location: Bangalore

PostPosted: Wed Feb 22, 2006 12:25 pm    Post subject:
Reply with quote

hi rags

thanks i understood how to retrive rows from perticular partition.

but i dint understand how the rows will be entered in to that specific partion for example in we are inserting 10 rows with 2 of them from 3rd month and 5 of them from 6th month ...etc...


rgds
suman
Back to top
View user's profile Send private message
ragshere

New User


Joined: 20 Dec 2004
Posts: 70

PostPosted: Wed Feb 22, 2006 1:12 pm    Post subject: Re: how to select the rows from a perticular partetion?
Reply with quote

Hi suman,
That will be taken care by DB2 while inserting the data.

our duty is just to create the partitioning index with values.


Thanks
Rags
Back to top
View user's profile Send private message
sumannaidu

New User


Joined: 21 May 2005
Posts: 13
Location: Bangalore

PostPosted: Wed Feb 22, 2006 5:14 pm    Post subject:
Reply with quote

Thanks rags
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
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
This topic is locked: you cannot edit posts or make replies. DB2 query Using Recursion, Converting... smilewithashu2 DB2 3 Tue Jan 03, 2017 12:50 pm
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


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