473,598 Members | 3,132 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

partioned table or partioned index

Hi,

I have a table that I would like to partition. It has a column c1 which has
100 distinct values.
I was planning to partition the table on column c1 using a partioned index,
and then apply data partitioned secondary indexes on the table.

I then read about partioned table spaces, How do I get the same behaviour as
above by creating
a partioned table space?Do I create the partion table space, create the
table and then create
partioned indexes?

I like the properties of the partioned table space,because the size of the
tables I am dealing with
are really huge?

Any thoughts and pointers are appreaciate.

Thanks,
Sumanth
Apr 13 '06 #1
10 2376
Sumanth wrote:
Hi,

I have a table that I would like to partition. It has a column c1 which has
100 distinct values.
I was planning to partition the table on column c1 using a partioned index,
and then apply data partitioned secondary indexes on the table.

I then read about partioned table spaces, How do I get the same behaviour as
above by creating
a partioned table space?Do I create the partion table space, create the
table and then create
partioned indexes?

I like the properties of the partioned table space,because the size of the
tables I am dealing with
are really huge?

Sumath, what platform are you on?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 13 '06 #2

read thru some docs and I believe that going with a partioned table space
with a partition by clause on C1 would
work. I then can define a data partioned secondary index that would be valid
on each of the partitions.

What is an index that is created as a partioned cluster?

Thanks,
Sumanth

"Sumanth" <Su************ @sas.com> wrote in message
news:e1******** **@foggy.unx.sa s.com...
Hi,

I have a table that I would like to partition. It has a column c1 which
has 100 distinct values.
I was planning to partition the table on column c1 using a partioned
index,
and then apply data partitioned secondary indexes on the table.

I then read about partioned table spaces, How do I get the same behaviour
as above by creating
a partioned table space?Do I create the partion table space, create the
table and then create
partioned indexes?

I like the properties of the partioned table space,because the size of the
tables I am dealing with
are really huge?

Any thoughts and pointers are appreaciate.

Thanks,
Sumanth

Apr 13 '06 #3
"Sumanth" <Su************ @sas.com> wrote in message
news:e1******** **@foggy.unx.sa s.com...
Hi,

I have a table that I would like to partition. It has a column c1 which
has 100 distinct values.
I was planning to partition the table on column c1 using a partioned
index,
and then apply data partitioned secondary indexes on the table.

I then read about partioned table spaces, How do I get the same behaviour
as above by creating
a partioned table space?Do I create the partion table space, create the
table and then create
partioned indexes?

I like the properties of the partioned table space,because the size of the
tables I am dealing with
are really huge?

Any thoughts and pointers are appreaciate.

Thanks,
Sumanth


Partitioned tablespaces are only for DB2 for z/OS and OS/390. If you are
using that platform, please state that in your response.
Apr 13 '06 #4

I am on AIX.

-Sumanth

"Mark A" <no****@nowhere .com> wrote in message
news:c9******** ************@co mcast.com...
"Sumanth" <Su************ @sas.com> wrote in message
news:e1******** **@foggy.unx.sa s.com...
Hi,

I have a table that I would like to partition. It has a column c1 which
has 100 distinct values.
I was planning to partition the table on column c1 using a partioned
index,
and then apply data partitioned secondary indexes on the table.

I then read about partioned table spaces, How do I get the same behaviour
as above by creating
a partioned table space?Do I create the partion table space, create the
table and then create
partioned indexes?

I like the properties of the partioned table space,because the size of
the tables I am dealing with
are really huge?

Any thoughts and pointers are appreaciate.

Thanks,
Sumanth


Partitioned tablespaces are only for DB2 for z/OS and OS/390. If you are
using that platform, please state that in your response.

Apr 14 '06 #5

So on AIX what are my options. If I have a table that is big (close to say
200 GB) what are my options in partitioning the table.

Thanks,
Sumanth

"Serge Rielau" <sr*****@ca.ibm .com> wrote in message
news:4a******** ****@individual .net...
Sumanth wrote:
Hi,

I have a table that I would like to partition. It has a column c1 which
has 100 distinct values.
I was planning to partition the table on column c1 using a partioned
index,
and then apply data partitioned secondary indexes on the table.

I then read about partioned table spaces, How do I get the same behaviour
as above by creating
a partioned table space?Do I create the partion table space, create the
table and then create
partioned indexes?

I like the properties of the partioned table space,because the size of
the tables I am dealing with
are really huge?

Sumath, what platform are you on?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 14 '06 #6

Can i create separate tables for each value of c1 and use a check constraint
on each of these tables.
And create a view that runs across all these tables (100 of them)
Will the access path of the queries be efficient.?

Any other options?

Thanks,
Sumanth
"Sumanth" <Su************ @sas.com> wrote in message
news:e1******** **@foggy.unx.sa s.com...
Hi,

I have a table that I would like to partition. It has a column c1 which
has 100 distinct values.
I was planning to partition the table on column c1 using a partioned
index,
and then apply data partitioned secondary indexes on the table.

I then read about partioned table spaces, How do I get the same behaviour
as above by creating
a partioned table space?Do I create the partion table space, create the
table and then create
partioned indexes?

I like the properties of the partioned table space,because the size of the
tables I am dealing with
are really huge?

Any thoughts and pointers are appreaciate.

Thanks,
Sumanth

Apr 14 '06 #7
Sumanth wrote:
So on AIX what are my options. If I have a table that is big (close to say
200 GB) what are my options in partitioning the table.

On DB2 V8.2 for AIX you can use
* nothing special and a 16KB or 32KB pagesize.
* Multi-dimensional clustering (MDC) with at 16KB or 32KB pagesize
The advantage of MDC is that it's one table. You get benefits on rollout
(faster deletes of a "partition" ) and you get fast scans.
MDC is avlaible without extra cost on licence.
* Hash partition the table across multiple database partitions
The advantage of this is scale out across multiple physical machines (if
you choose so).
It requires the Database partitioning feature (significant extra $$/CPU)
and you need to think about the design of your database beyond just this
one table (fnding partitioning keys, etc...).
I would not recommend DPF in an OLTP system just because a single table
is big. DPF works, and works well in OLTP, but it requires careful
layout of your schema.

* UNION ALL views
The advantage can be fast roll-in/roll-out and partition elimination at
runtime as well as compile-ttime.
DB2 is reasonably good about keeping the optimizer plans in check.
Today DB2 supports in access of 100 branches of UNION ALL. I think (!)
you need to run with optimization level 7 to get all the goodies.
In DB2 Viper (ETA pre-announced for "this summer")
You have two more options:
1. DB2 supports more rows per page and more pages per tablespace.
This means you can have a single table of, uh, 16TB and 1.2 trillion
rows if you so desire.
I.e. a simple 4KB pagesize table will do just fine
2. range partitioning
This is the replacement for UNION ALL views. Thousands of partitions are
supported along with special ATTACH and DETACH DDL statements for fast
roll-in, roll-out.

In DB2 Viper we do not recommend UNION ALL views anymore for partitioning.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 14 '06 #8

Thanks Serge.
"Serge Rielau" <sr*****@ca.ibm .com> wrote in message
news:4a******** ****@individual .net...
Sumanth wrote:
So on AIX what are my options. If I have a table that is big (close to
say 200 GB) what are my options in partitioning the table.

On DB2 V8.2 for AIX you can use
* nothing special and a 16KB or 32KB pagesize.
* Multi-dimensional clustering (MDC) with at 16KB or 32KB pagesize
The advantage of MDC is that it's one table. You get benefits on rollout
(faster deletes of a "partition" ) and you get fast scans.
MDC is avlaible without extra cost on licence.
* Hash partition the table across multiple database partitions
The advantage of this is scale out across multiple physical machines (if
you choose so).
It requires the Database partitioning feature (significant extra $$/CPU)
and you need to think about the design of your database beyond just this
one table (fnding partitioning keys, etc...).
I would not recommend DPF in an OLTP system just because a single table is
big. DPF works, and works well in OLTP, but it requires careful layout of
your schema.

* UNION ALL views
The advantage can be fast roll-in/roll-out and partition elimination at
runtime as well as compile-ttime.
DB2 is reasonably good about keeping the optimizer plans in check.
Today DB2 supports in access of 100 branches of UNION ALL. I think (!) you
need to run with optimization level 7 to get all the goodies.
In DB2 Viper (ETA pre-announced for "this summer")
You have two more options:
1. DB2 supports more rows per page and more pages per tablespace.
This means you can have a single table of, uh, 16TB and 1.2 trillion rows
if you so desire.
I.e. a simple 4KB pagesize table will do just fine
2. range partitioning
This is the replacement for UNION ALL views. Thousands of partitions are
supported along with special ATTACH and DETACH DDL statements for fast
roll-in, roll-out.

In DB2 Viper we do not recommend UNION ALL views anymore for partitioning.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Apr 14 '06 #9
Ian
Serge Rielau wrote:
In DB2 Viper we do not recommend UNION ALL views anymore for partitioning.


That's funny - aren't there still people on the mainframe recommending
UNION ALL in certain situations? (but that may be related to issues
with rebuild of global indexes).

And does this mean that the optimizer's "union all" smarts will be
removed?

Thanks -

Ian
Apr 14 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
7838
by: John | last post by:
I have a table with two rows. On the first row is a text box and in the second row is an image. I have set the table cellpadding to 0 and cellspacing to 0. The table is leaving extra spaces in the rows on the top and bottom of the picture and image. I need to make the height of the rows to be the same as the textbox and image. How do I do this? I have tried even setting the height of the table and all the <td> and <tr> tags to 1 but have...
16
21298
by: mamo74 | last post by:
Hello. I am administering a SQL Server (Enterprise Edition on Windows 2003) from some month and can't understand what is going on in the latest week (when the db grow a lot). The DB is around 250G, and has one table with 1 billion rows. It is performing in a decent way, but can't understand why a particolar table has strong performance problem.
9
2756
by: Ed_No_Spam_Please_Weber | last post by:
Hello All & Thanks in advance for your help! Background: 1) tblT_Documents is the primary parent transaction table that has 10 fields and about 250,000 rows 2) There are 9 child tables with each having 3 fields each, their own PK; the FK back to the parent table; and the unique data for that table. There is a one to many relation between the parent and each of the 9 child rows. Each child table has between 100,000 and 300,000
4
10550
by: serge | last post by:
I ran into a table that is used a lot. Well less than 100,000 records. Maybe not a lot of records but i believe this table is used often. The table has 26 fields, 9 indexes but no Primary Key at all! There are no table relationships defined in this database, no Natural keys, only Surrogate keys in the database. 1- Maybe an odd question but is it normal to have 1/3 of the table's fields as indexes? Is this a valid question or it really
7
10795
by: Bing Wu | last post by:
Hi Folks, I have a very large table containing 170 million rows of coordinats: CREATE TABLE "DB2ADMIN"."COORDINATE" ( "FID" INTEGER NOT NULL , "AID" INTEGER NOT NULL , "X" REAL NOT NULL , "Y" REAL NOT NULL , "Z" REAL NOT NULL )
1
1797
by: sumanroyc | last post by:
Hi, We want to create a table such that it will be partitioned based on the day. That is all the records which are inserted into the table on a particular day should go into the partition for that day only. At the end of the day at 00:00 hours, we want to copy all the data from the partition for that particular day to the archive table and then truncate that partition. So it will be a daily proccess of creating and truncating partitions. ...
4
3708
by: Hemant Shah | last post by:
Folks, Our client has a program that browses whole table from begining to end. The table has 1 million rows in it. REORGCHK does not show any problems. It has unique index defined on KEY0 column. If I use SELECT statement without OPTIMIZE FOR clause, then it uses temporary table to sort the data, but if I use OPTIMIZE clause then it uses index access without temporary table. If I use OPTIMIZE FOR more than 700 rows then it uses...
0
1459
by: Dan van Ginhoven | last post by:
Hi! I have DB2 9.1 running on a Linux-server. The database is partioned into two nodes on this server. On another Linux-server, acting as application server, I want to initiate the backup. When I issue the backup command from the client server, I get a backup of the controlling node, NODE0000.
5
6049
by: danfan46 | last post by:
Hi. I'm on DB2 9.5.0.1 on Linux AMD 64 and the database has four partitions where catalog is on partion 0 and user data on 1 thru 3. After a load failure (path to load file was wrong) I cannot access the tables, even though the loads have been rerun with success. set integrity for ..table..
0
7985
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
8393
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8048
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8263
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
5438
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
3896
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
2411
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1503
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1246
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.