how to select the rows from a perticular partetion?

sumannaidu · New User Joined: 21 May 2005 Posts: 13 Location: Bangalore

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

ragshere · New User Joined: 20 Dec 2004 Posts: 70

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

sumannaidu · New User Joined: 21 May 2005 Posts: 13 Location: Bangalore

Hi rags

can you please explain the above with one example.

rgds
suman

MichaelKBS · New User Joined: 10 Jan 2006 Posts: 24 Location: Germany

ragshere · New User Joined: 20 Dec 2004 Posts: 70

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

sumannaidu · New User Joined: 21 May 2005 Posts: 13 Location: Bangalore

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

ragshere · New User Joined: 20 Dec 2004 Posts: 70

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

sumannaidu · New User Joined: 21 May 2005 Posts: 13 Location: Bangalore

Thanks rags