Hi
Notice that syscat.datapartitions contains a data tablespace and a long
tablespace for each range partition, but not an index tablespace. Further,
the index tablespace is only declared when the table is first defined, and
is not subsequently required or indeed allowed for ALTER table statements.
All of which seems to suggest that at V9.1 you're limited to a singular
index tablespace, but can have multiple data and long tablespaces.
But stop - there must be a reason behind such design logic from the labs:
thinking on it, perhaps you need a unified index under the bonnet to lead
you to the correct partition(s)? I've been very impressed in testing by how
range-partition-aware the optimizer is, certainly compared to the MDCs that
were introduced at V8. Also, why would you want separate index tablespaces
anyway? Table partitioning is purely for ease of storage and access. At a
philosophical level, does it make sense to partition an index? It's still
one index, same as the table is still one table, albeit in multiple storage
containers. At a practical level, there's no need to reorg the table itself,
but you'll want to runstats the table and indexes themselves after
significant data churn, or after every attach/detach operation; so wouldn't
it be simpler just to reorg the indexes themselves at the same time? it
should be very quick.
Hope this snippet of db2look output helps with the DDL:
Regards
Alex Levy
Sustainable Software Ltd.
------------------------------------------------
-- DDL Statements for table "#DBA "."AUDIT"
------------------------------------------------
CREATE TABLE "#DBA "."AUDIT" (
"AUDIT_ID" NOT NULL ,
"JVM_CODE" VARCHAR(20) NOT NULL ,
"MESG_TIME" TIMESTAMP NOT NULL ,
"MESG" VARCHAR(3000) NOT NULL ,
"CATEGORY" VARCHAR(40) ,
"USER_ID" CHAR(10) NOT NULL ,
"MESG_PARAMS" VARCHAR(150) NOT NULL )
INDEX IN "#DBA_TRNIDX4K" PARTITION BY RANGE("MESG_TIME")
(PART "PART0" STARTING('2007-10-04-00.00.00.000000') IN
"#DBA_TRNDTA4K" LONG IN "#DBA_TRNLNG32K",
PART "PART1" STARTING('2007-10-05-00.00.00.000000') IN
"#DBA_TRNDTA4K" LONG IN "#DBA_TRNLNG32K",
... etc. ...
PART "PART8" STARTING('2007-10-12-00.00.00.000000')
ENDING('2007-10-12-23.59.59.999999')
IN "#DBA_TRNDTA4K" LONG IN "#DBA_TRNLNG32K");
COMMENT ON TABLE "#DBA "."AUDIT" IS 'Range partitioned audit table with
one partition per day for easy rollin and rollout.';
-----Original Message-----
From: Jagdish Singh Rawat NCS
[mailto:db2udbdba-ezmlmshield-x92883074.[Email address protected]
Sent: 22 October 2007 10:40
To: LazyDBA Discussion
Subject: How to create Partitioning table including index tablespace
Hi
I am working on partitioning table.
Want to create table having separate table for data and indexes.
CREATE TABLE PMMU .TBL_PMM_RSN (
RSN_ID DECIMAL(19,0) NOT NULL GENERATED BY DEFAULT AS
IDENTITY (
START WITH +1
INCREMENT BY +1
MINVALUE +1
MAXVALUE +9999999999999999999
NO CYCLE
CACHE 20
NO ORDER ) ,
PMT_NO CHAR(11) NOT NULL ,
AMEND_REQ_NO SMALLINT NOT NULL WITH DEFAULT ,
SC_APPR_DTTM TIMESTAMP NOT NULL ,
TYPE CHAR(1) NOT NULL ,
RSN_CD CHAR(12) ,
RSN_TXT VARCHAR(280) ,
DW_CREATED_DTTM TIMESTAMP NOT NULL WITH DEFAULT ,
DW_UPDATED_DTTM TIMESTAMP NOT NULL WITH DEFAULT )
PARTITION BY RANGE(SC_APPR_DTTM)
(PART MINIMUM STARTING(MINVALUE) ENDING('2007-05-01-00.00.00.000000')
EXCLUSIVE IN TPMM0001_04P1_01,
PART MAY_07 STARTING('2007-05-01-00.00.00.000000')
ENDING('2007-06-01-00.00.00.000000') EXCLUSIVE IN TPMM0001_04P1_01,
PART JUN_07 STARTING('2007-06-01-00.00.00.000000')
ENDING('2007-07-01-00.00.00.000000') EXCLUSIVE IN TPMM0001_04P1_01,
PART JUL_07 STARTING('2007-07-01-00.00.00.000000')
ENDING('2007-08-01-00.00.00.000000') EXCLUSIVE IN TPMM0001_04P1_01
);
Where I can put "index in <tablespace>" I have tried a lot but no
success.
I need every partition have separate index tablespace.
Thanx in advance.
Thanks
Jagdish S. Rawat
IBM Certified Advance DB2DBA
NCS Pte. Ltd.
5 Ang Mo Kio St 62, NCS Hub
Singapore - 569141
Ph.# +65-65565870(Direct) +65-65568000 Ext. # 5870
HP.# +65-96425135
Fax# +65-64830495
-----Original Message-----
From: Mohammed Mohsin
[mailto:db2udbdba-ezmlmshield-x48448957.[Email address protected]
Sent: Monday, October 22, 2007 1:37 PM
To: LazyDBA Discussion
Subject: RE: SYSCATSPACE from SMS tablespace to DMS tablespace
Thanks All for the replies...
Rgds,
Mohsin
-----Original Message-----
From: Lavezzo.Craig
[mailto:db2udbdba-ezmlmshield-x26833840.[Email address protected]
Sent: Friday, October 19, 2007 7:53 PM
To: LazyDBA Discussion
Subject: RE: SYSCATSPACE from SMS tablespace to DMS tablespace
I wrote a script to move everything from one table space to another.
From there it is easy to move just one table across tablespaces is you
need to.
1. Rename the table space.
2. Rename the tables and indexes on that table space.
3. Create the new DMS space.
4. Create the old table/index names onto the new DMS table space.
5. Cursor load the renamed tables into the old table names that now
live on the new DMS table space.
Sometime I hate DB2
-----Original Message-----
From: Khan Nadeem
[mailto:db2udbdba-ezmlmshield-x31466112.[Email address protected]
Sent: Friday, October 19, 2007 2:14 AM
To: LazyDBA Discussion
Subject: RE: SYSCATSPACE from SMS tablespace to DMS tablespace
Well
I don't think so its possible
As SYSCATSPACE holds DDC, which can make Database useless if played
around
But you can always change state of SMS tablespace to DMS
Thru some extra effort
Thanks,
Nadeem Khan
-----Original Message-----
From: Mohammed Mohsin
[mailto:db2udbdba-ezmlmshield-x33338305.[Email address protected]
Sent: Friday, October 19, 2007 2:16 PM
To: LazyDBA Discussion
Subject: SYSCATSPACE from SMS tablespace to DMS tablespace
Hi,
I want to change the SYSCATSPACE of DB2 8.1 on Solaris 5.10 from SMS
tablespace to DMS tablespace without dropping the database and
recreating. Is it possible??
Rgds,
Mohsin
Phone : +91-80-22489390x8456
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
This message contains information that may be privileged or confidential
and is the property of the Capgemini Group. It is intended only for the
person to whom it is addressed. If you are not the intended recipient,
you are not authorized to read, print, retain, copy, disseminate,
distribute, or use this message or any part thereof. If you receive this
message in error, please notify the sender immediately and delete all
copies of this message.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
________________________________________________________________________
________________________________________
CONFIDENTIALITY NOTICE: This email from the State of California is for
the sole use of the intended recipient and may contain confidential and
privileged information. Any unauthorized review or use, including
disclosure or distribution, is prohibited. If you are not the intended
recipient, please contact the sender and destroy all copies of this
email.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
DB2 & UDB email list listserv db2-l LazyDBA home page