hey guys,

i created a partitioned table where i can name the partitions for each partition as well......

but when i try to query that table is there any means that i can query that named partition explicitly.

ex: i created a table like this

CREATE TABLE orders(id INT, shipdate DATE, …) PARTITION BY RANGE(shipdate) (
PARTITION p1 STARTING MINVALUE,
PARTITION p2 STARTING '4/1/2006',
PARTITION p3 STARTING '10/1/2006'
ENDING ‘12/31/2006' )

I know that when i query on the table, db2 itself will take care of which partition to choose, but i want to make that of my choice...

So how can i query that table on partition p1 or p2 or p3.... ?
if so how....?
can u any one suggest me with the query...

thx in advance
goutam

how about "select c1,c2... from orders where shipdate between '4/1/2006' and '9/20/2006' " to select p2 etc.

hi dbamota,

u'r sql looks obvious to me...
but u'r same sql can be run on non partitioned table as well....

but what i was looking for is...
can i query on a particular partition of which i specify...?

like
select c1,c2 from orders in partition p1.....

thx,
goutam

Why would you want to do that?

The "IN PARTITION ..." as you suggest is not available, fortunately.

You can have a look at the access plan to verify that DB2 excludes all other partitions. If you don't trust that, create separate base tables and query those directly. If you want to query all those base tables together, you can use a UNION ALL view over the base tables.

In Oracle, you can address the partitions; I don't think you can do that in db2.

hey stolz and dbamota

thx for u'r suggestions...

And wel can any one let me know

1. r there any other different ways that i can query the partitioned table
2. r there any new clauses that were designed, to specify while querying on a partitioned tables.

i cant find any material that discusess how to deal with queries for this specialized partition tables...
if possible can any one provide me with the good links....


thx guys,
goutam

I'm really curious: could you describe what you want to achieve and why you need to have access to the single partitions?

Hey Goutam,

I think we cant really run a select query on a single partition. the whole significance of the partition's is that it really improves te performance but i guess when you specify a range for a partition, what it does is it sorts the data accordingly in those ranges. For example say u are partitioning on ID which is unique and u have specified the ranges a sfollows.

par1 --- 1 to 10
par2 --- 11 to 40
par3 --- 121 to 201
par4 --- 41 to 120

what it does when you do a select * is it sorts 1 to 10 and then 11 to 40 and 121 to 201 and then 41 to 120. So by this you can know the respective partitions and their ranges. But you cant really run a query for a single partition.

ound this in a IBM material.

db2 “select datapartitionnum(l_shipdate) as PartitionId, l_shipdate from lineitem
where l_shipdate between ’01/06/1992’ and ‘31/07/1992’
order by l_shipdate”


source: https://www6.software.ibm.com/develo...dm/dm0612read/

Hope this helps

hey viper,

thx dude.... this was what i was looking for......

i guess sure this link wil help me and for others......