Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: 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");

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Because DB2 LUW does not have an ALTER INDEX statement, drop the index and re-create it.

  3. #3
    Join Date
    Dec 2007
    Posts
    288
    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!!!!!???

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Dec 2007
    Posts
    288
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •