SEO
vBulletin Search Engine Optimization
| |||||||
| Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
03-17-2008, 06:12 AM
| ||||
| ||||
 Questions about DB2 range partition Hi, I have some questions about DB2 range parition and I cannot find the answer from the document. 1. Can I name the partition name for every partition in a range partition? For example, p2008jan for data of Jan, 2008? 2. Can I select data from a particular partition? Such as, select count(*) from <owner>.<table_name>.<partition1> 3. Which catalog tables provide information about range partition tables? Thanks.      |
|
03-17-2008, 06:12 AM
| |||
| |||
| Re: Questions about DB2 range partition Challenge wrote: > Hi, > > I have some questions about DB2 range parition and I cannot find the > answer from the document. > > 1. Can I name the partition name for every partition in a range > partition? For example, p2008jan for data of Jan, 2008? Yes. The clause is PARTITION <partitionname> see CREATE TABLE. > > 2. Can I select data from a particular partition? Such as, select > count(*) from <owner>.<table_name>.<partition1> Absolutely not. And that is by design. Partitioning is part of physical design it does not belong into SQL. Use a range query to get the data you want. The effect shoule be the same. > 3. Which catalog tables provide information about range partition > tables? SYSCAT.DATAPARTITIONS, SYSCAT.DATAPARTITIONEXPRESSION Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab |
|
03-17-2008, 07:35 AM
| |||
| |||
| Re: Questions about DB2 range partition Challenge wrote: > Hi, > > I have some questions about DB2 range parition and I cannot find the > answer from the document. > > 1. Can I name the partition name for every partition in a range > partition? For example, p2008jan for data of Jan, 2008? Different question back: Is this an existing, home-grown application? If not, is there a particular reason for not applying proper data modeling and denormalization of the data? -- Knut Stolze DB2 z/OS Utilities Development IBM Germany |
|
03-20-2008, 12:46 PM
| |||
| |||
| Re: Questions about DB2 range partition On Mar 14, 11:39*am, Serge Rielau <srie...@ca.ibm.com> wrote: > Challenge wrote: > > Hi, > > > I have some questions about DB2 range parition and I cannot find the > > answer from the document. > > > 1. Can I name the partition name for every partition in a range > > partition? For example, p2008jan for data of Jan, 2008? > > Yes. The clause is PARTITION <partitionname> see CREATE TABLE. > > > 2. Can I select data from a particular partition? Such as, select > > count(*) from <owner>.<table_name>.<partition1> > > Absolutely not. And that is by design. > Partitioning is part of physical design it does not belong into SQL. > Use a range query to get the data you want. > The effect shoule be the same. > > > 3. Which catalog tables provide information about range partition > > tables? > > SYSCAT.DATAPARTITIONS, SYSCAT.DATAPARTITIONEXPRESSION > > Cheers > Serge > -- > Serge Rielau > DB2 Solutions Development > IBM Toronto Lab Thanks, Serge. For the Q no. 1, I finially created a table with partition names. I made a syntax error before. For Q no. 2, I can select data from a partition in Oracle. So I tried to find a similar thing in DB2. Yes, I can select data by a range query. But how can I know that data are distributed in the right partition? For Q no. 3, it seems that I couldn't find the some information I want to know from the catalog tables. Such as, data distribution in different partitions, etc. |
|
03-20-2008, 12:46 PM
| |||
| |||
| Re: Questions about DB2 range partition On Mar 17, 3:42*am, Knut Stolze <sto...@de.ibm.com> wrote: > Challenge wrote: > > Hi, > > > I have some questions about DB2 range parition and I cannot find the > > answer from the document. > > > 1. Can I name the partition name for every partition in a range > > partition? For example, p2008jan for data of Jan, 2008? > > Different question back: Is this an existing, home-grown application? *If > not, is there a particular reason for not applying proper data modeling and > denormalization of the data? > > -- > Knut Stolze > DB2 z/OS Utilities Development > IBM Germany We are designing data model for a new project in distributed environment. We are going to use range partition. And range partition in DB2 is new for us. |
|
03-20-2008, 12:46 PM
| |||
| |||
| Re: Questions about DB2 range partition Challenge wrote: > For Q no. 2, I can select data from a partition in Oracle. So I tried > to find a similar thing in DB2. Yes, I can select data by a range > query. But how can I know that data are distributed in the right > partition? > > For Q no. 3, it seems that I couldn't find the some information I want > to know from the catalog tables. Such as, data distribution in > different partitions, etc. Did you take a look at the views I posted? SYSCAT.DATAPARTITIONS.LOWVALUE and SYSCAT.DATAPARTITIONS.HIGHVALUE tell you the range of the partition. Cheers Serge -- Serge Rielau DB2 Solutions Development IBM Toronto Lab |
|
03-20-2008, 12:46 PM
| |||
| |||
| Re: Questions about DB2 range partition Challenge wrote: > On Mar 17, 3:42Â*am, Knut Stolze <sto...@de.ibm.com> wrote: >> Challenge wrote: >> > Hi, >> >> > I have some questions about DB2 range parition and I cannot find the >> > answer from the document. >> >> > 1. Can I name the partition name for every partition in a range >> > partition? For example, p2008jan for data of Jan, 2008? >> >> Different question back: Is this an existing, home-grown application? Â*If >> not, is there a particular reason for not applying proper data modeling >> and denormalization of the data? > > We are designing data model for a new project in distributed > environment. We are going to use range partition. And range partition > in DB2 is new for us. As Serge mentioned, range partitioning is a feature that you can use to physically organize your data. It has nothing to do with the conceptual data model. I was mostly asking my question because you obviously mix different things in a value like "p2008jan". If you have DATE values, then it makes sense to use the DATA data type. If you only have month and year, then I would model that as separate columns so that you can efficiently query it w/o string operations. -- Knut Stolze DB2 z/OS Utilities Development IBM Germany |
|
03-20-2008, 12:46 PM
| |||
| |||
| Re: Questions about DB2 range partition On Mar 17, 10:11*am, Serge Rielau <srie...@ca.ibm.com> wrote: > Challenge wrote: > > For Q no. 2, I can select data from a partition in Oracle. So I tried > > to find a similar thing in DB2. Yes, I can select data by a range > > query. But how can I know that data are distributed in the right > > partition? > > > For Q no. 3, it seems that I couldn't find the some information I want > > to know from the catalog tables. Such as, data distribution in > > different partitions, etc. > > Did you take a look at the views I posted? > SYSCAT.DATAPARTITIONS.LOWVALUE and > SYSCAT.DATAPARTITIONS.HIGHVALUE > tell you the range of the partition. > > Cheers > Serge > > -- > Serge Rielau > DB2 Solutions Development > IBM Toronto Lab Yes. I see the two views. What I would like to know is the real number of rows in a partition or the status of a partition, somthing like SYSCAT.DATAPARTITIONS.<NO_OF_ROWS>, SYSCAT.DATAPARTITIONS.<NO_OF_PAGE_USED>, SYSCAT.DATAPARTITIONS.<ADD_TIME>, etc.. The current info. in SYSCAT.DATAPARTITIONS is only the definition of the partitions. |
|
03-20-2008, 12:46 PM
| ||||
| ||||
| Re: Questions about DB2 range partition Challenge wrote: > On Mar 17, 10:11 am, Serge Rielau <srie...@ca.ibm.com> wrote: >> Challenge wrote: >>> For Q no. 2, I can select data from a partition in Oracle. So I tried >>> to find a similar thing in DB2. Yes, I can select data by a range >>> query. But how can I know that data are distributed in the right >>> partition? >>> For Q no. 3, it seems that I couldn't find the some information I want >>> to know from the catalog tables. Such as, data distribution in >>> different partitions, etc. >> Did you take a look at the views I posted? >> SYSCAT.DATAPARTITIONS.LOWVALUE and >> SYSCAT.DATAPARTITIONS.HIGHVALUE >> tell you the range of the partition. >> >> Cheers >> Serge >> >> -- >> Serge Rielau >> DB2 Solutions Development >> IBM Toronto Lab > > Yes. I see the two views. What I would like to know is the real number > of rows in a partition or the status of a partition, somthing like > SYSCAT.DATAPARTITIONS.<NO_OF_ROWS>, > SYSCAT.DATAPARTITIONS.<NO_OF_PAGE_USED>, > SYSCAT.DATAPARTITIONS.<ADD_TIME>, etc.. The current info. in > SYSCAT.DATAPARTITIONS is only the definition of the partitions. What you are looking for is not stored in the catalog. See the DATAPARTITIONNUM scalar function. With this and the syscat.datapartitions view you can write a query to give you row distributions for your various range partitions. You can also get some of the information about the various table partitions from db2pd -tcbstats. |
Linear Mode
