Home   |   Register   |   Rules   |   Calendar   |   Get Daily   |     |  
 


If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > DB2 V9 on Windows - Changing Unique Index

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 03-17-09, 10:46
itsonlyme44 itsonlyme44 is offline
Registered User
  
Join Date: Dec 2007
Posts: 261
DB2 V9 on Windows - Changing Unique Index
I need to change PCTFREE 20 to PCTFREE 10 on a UNIQUE INDEX (DDL Below)

How would I go about doing this?



Code:
CREATE TABLE "KCDWHDUT"."TZAPP"  (
		  "CO_ID" CHAR(2) NOT NULL , 
		  "AGT_CTL_EFF_DT" DATE NOT NULL , 
		  "PREV_UPDT_USER_ID" CHAR(8) NOT NULL , 
		  "PREV_UPDT_TS" TIMESTAMP , 
		  "SS_EARN_PCT" DECIMAL(8,5) NOT NULL , 
		  "SS_EARN_MAX_AMT" DECIMAL(15,2) NOT NULL , 
		  "MEDCR_EARN_PCT" DECIMAL(8,5) NOT NULL , 
		  "MEDCR_EARN_MAX_AMT" DECIMAL(15,2) NOT NULL , 
		  "A401K_MAX_AMT" DECIMAL(15,2) NOT NULL , 
		  "A401K_CUP_MAX_AMT" DECIMAL(15,2) NOT NULL , 
		  "PERI_DRW_MAX_AMT" DECIMAL(15,2) NOT NULL , 
		  "GR_LIFE_CLI_ID" CHAR(10) NOT NULL , 
		  "GR_MEDIC_CLI_ID" CHAR(10) NOT NULL , 
		  "GR_ACCDNT_CLI_ID" CHAR(10) NOT NULL , 
		  "GR_LIFE_MAX_AMT" DECIMAL(15,2) NOT NULL , 
		  "GR_ACCDNT_MAX_AMT" DECIMAL(15,2) NOT NULL , 
		  "PAYRL_MOM_DY" DECIMAL(2,0) NOT NULL , 
		  "PAYRL_EOM_DY" DECIMAL(2,0) NOT NULL , 
		  "TRN_ALLOW_TOT_AMT" DECIMAL(15,2) NOT NULL , 
		  "TRN_ALLOW_INIT_AMT" DECIMAL(15,2) NOT NULL , 
		  "TRN_ALLOW_PMT1_AMT" DECIMAL(15,2) NOT NULL , 
		  "TRN_ALLOW_PMT1_QTY" SMALLINT NOT NULL , 
		  "TRN_ALLOW_PMT2_AMT" DECIMAL(15,2) NOT NULL , 
		  "TRN_ALLOW_PMT2_QTY" SMALLINT NOT NULL , 
		  "QUOT_GA_AMT" DECIMAL(15,2) NOT NULL , 
		  "QUOT_GA_MNMBR_QTY" SMALLINT NOT NULL , 
		  "QUOT_FA_AMT" DECIMAL(15,2) NOT NULL , 
		  "QUOT_FA_MNMBR_QTY" SMALLINT NOT NULL , 
		  "QUOT_AGA_AMT" DECIMAL(15,2) NOT NULL , 
		  "QUOT_AGA_MNMBR_QTY" SMALLINT NOT NULL , 
		  "QUOT_DA_AMT" DECIMAL(15,2) NOT NULL , 
		  "QUOT_DA_MNMBR_QTY" SMALLINT NOT NULL , 
		  "IA_QUOT_GA_PCT" DECIMAL(8,5) NOT NULL , 
		  "IA_GA_MNMBR_QTY" SMALLINT NOT NULL , 
		  "IA_QUOT_FA_PCT" DECIMAL(8,5) NOT NULL , 
		  "IA_FA_MNMBR_QTY" SMALLINT NOT NULL , 
		  "IA_QUOT_AGA_PCT" DECIMAL(8,5) NOT NULL , 
		  "IA_AGA_MNMBR_QTY" SMALLINT NOT NULL , 
		  "IA_QUOT_DA_PCT" DECIMAL(8,5) NOT NULL , 
		  "IA_DA_MNMBR_QTY" SMALLINT NOT NULL , 
		  "PRST_AGA_MIN_PCT" DECIMAL(8,5) NOT NULL , 
		  "PRST_MAX_PCT" DECIMAL(8,5) NOT NULL , 
		  "PRST_MIN_MO_DUR" SMALLINT NOT NULL , 
		  "LNCR_FRST_MO_DUR" SMALLINT NOT NULL , 
		  "LNCR_FRST_MIN_AMT" DECIMAL(15,2) NOT NULL , 
		  "LNCR_FRST_PD_AMT" DECIMAL(15,2) NOT NULL , 
		  "LNCR_2ND_MO_DUR" SMALLINT NOT NULL , 
		  "LNCR_2ND_MIN_AMT" DECIMAL(15,2) NOT NULL , 
		  "LNCR_2ND_PD_AMT" DECIMAL(15,2) NOT NULL , 
		  "PRST_CVG_MO_DUR" SMALLINT NOT NULL , 
		  "GA_VALID_FRST_AMT" DECIMAL(13,2) NOT NULL , 
		  "GA_VALID_2ND_AMT" DECIMAL(13,2) NOT NULL , 
		  "GR_DENTL_CLI_ID" CHAR(10) NOT NULL , 
		  "GR_LTB_CLI_ID" CHAR(10) NOT NULL , 
		  "GR_LTB_MIN_AMT" DECIMAL(15,2) NOT NULL , 
		  "GR_LTB_MAX_AMT" DECIMAL(15,2) NOT NULL , 
		  "GR_LTB_PYR_MAX_PCT" DECIMAL(8,5) NOT NULL , 
		  "GR_LTB_MIN_MO_DUR" DECIMAL(3,0) NOT NULL , 
		  "GR_LTB_EVAL_MO" DECIMAL(3,0) NOT NULL , 
		  "GR_LTB_EVAL_DY" DECIMAL(3,0) NOT NULL , 
		  "GR_LTB_NO_COST_DUR" DECIMAL(3,0) NOT NULL , 
		  "PRMH_OCOMM_MO_DUR" DECIMAL(3,0) NOT NULL , 
		  "PRM_OCOMM_YR_QTY" DECIMAL(3,0) NOT NULL , 
		  "GR_LTB_MIN_NEW_AMT" DECIMAL(15,2) NOT NULL )   
		 IN "TBLSP4K" INDEX IN "INDSP4K" ; 

ALTER TABLE "KCDWHDUT"."TZAPP" PCTFREE 10;

ALTER TABLE "KCDWHDUT"."TZAPP" APPEND ON;




CREATE UNIQUE INDEX "KCDWHDUT"."X01ZAPP" ON "KCDWHDUT"."TZAPP" 
		("CO_ID" ASC,
		 "AGT_CTL_EFF_DT" ASC)
		PCTFREE 20 ALLOW REVERSE SCANS;
-- DDL Statements for primary key on Table "KCDWHDUT"."TZAPP"

ALTER TABLE "KCDWHDUT"."TZAPP" 
	ADD CONSTRAINT "X01ZAPP" PRIMARY KEY
		("CO_ID",
		 "AGT_CTL_EFF_DT");
Reply With Quote
  #2 (permalink)  
Old 03-17-09, 10:50
aflorin27 aflorin27 is offline
Registered User
  
Join Date: Apr 2008
Location: Iasi, Romania
Posts: 317
Because DB2 LUW does not have an ALTER INDEX statement, drop the index and re-create it.
Reply With Quote
  #3 (permalink)  
Old 03-17-09, 10:57
itsonlyme44 itsonlyme44 is offline
Registered User
  
Join Date: Dec 2007
Posts: 261
Actually, I did try dropping it and I get this:

Code:
DROP INDEX KCDWHDUT.X01ZAPP
DB21034E  The command was processed as an SQL statement because it was not a 
valid Command Line Processor command.  During SQL processing it returned:
SQL0669N  A system required index cannot be dropped explicitly.  
SQLSTATE=42917
I also tried the UPDATE INDEX command
DB2 Database for Linux, UNIX, and Windows

and the ALTER INDEX command
DB2 Database for Linux, UNIX, and Windows

but I just can't seem to get it right!!!!!???
Reply With Quote
  #4 (permalink)  
Old 03-17-09, 11:32
ARWinner ARWinner is offline
Registered User
  
Join Date: Jan 2003
Posts: 3,575
Look at the explanation for the error message:

SQL0669N A system required index cannot be dropped explicitly.

Explanation:

The DROP INDEX statement attempted to drop an index required to:

o enforce the primary key constraint on a table

o enforce a unique constraint on a table

o enforce the uniqueness of the object identifier (OID) column
of a typed table hierarchy

o maintain a replicated materialized query table.



A system required index cannot be dropped using the DROP INDEX
statement.

The statement cannot be processed. The specified index is not
dropped.

User Response:

If you do not want to keep the primary or unique constraint, use
the DROP PRIMARY KEY clause or the DROP CONSTRAINT clause of the
ALTER TABLE statement to remove the primary key or unique
constraint. If the index was created only for enforcing the
primary or unique key, then the index will be dropped. If not,
the DROP INDEX statement could then be processed.

The index for an OID column can only be dropped by dropping the
table.

The index required to maintain a replicated materialized query
table can only be dropped by first dropping the replicated
materialized query table.

sqlcode : -669

sqlstate : 42917


Andy
Reply With Quote
  #5 (permalink)  
Old 03-17-09, 13:07
itsonlyme44 itsonlyme44 is offline
Registered User
  
Join Date: Dec 2007
Posts: 261
Thanks! This is what I had to do:

Code:
ALTER TABLE "KCDWHDUT"."TZAPP" drop PRIMARY KEY
DB20000I  The SQL command completed successfully.


DROP INDEX "KCDWHDUT"."X01ZAPP"
DB20000I  The SQL command completed successfully.


CREATE UNIQUE INDEX "KCDWHDUT"."X01ZAPP" ON "KCDWHDUT"."TZAPP" ("CO_ID" ASC, "AGT_CTL_EFF_DT" ASC) PCTFREE 10 ALLOW REVERSE SCANS
DB20000I  The SQL command completed successfully.


ALTER TABLE "KCDWHDUT"."TZAPP" ADD CONSTRAINT "X01ZAPP" PRIMARY KEY ("CO_ID", "AGT_CTL_EFF_DT")
SQL0598W  Existing index "KCDWHDUT.X01ZAPP" is used as the index for the 
primary key or a unique key.  SQLSTATE=01550
Might seem easy to some.. but I'm pretty new to this. Thank you for your help.
Reply With Quote
Reply

« Previous Thread | Next Thread »
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


 
 
 
Home  |   Register  |   Get New  |   FAQ's  |   Sitemap

Powered by vBulletin
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.