Link to home
Start Free TrialLog in
Avatar of sam2929
sam2929

asked on

Modification of procedure to create unique index too

Below is create index script(Which Kent helped me) This script  creates index based upon the myindexes table, So we insert a row
and based upon that row if it sees new indexes it will create it else it if index is already there it skips it

We need modification in this script we also need this script to create unique index
Right now we are creating unique index with suffix _UN1 ,UN2 and nonunique index with suffix _N1,_N2

This works fine

INSERT INTO dmart_mkt1.myindexes (IndexName, TableName, Text,sch_name)
 VALUES ('EIW_CUSTOMER_N2','EIW_CUSTOMER', '(CUST_CITY ASC) PCTFREE 0 ALLOW  REVERSE SCANS','DMART_MKT0');

We need to modify it to create unique index too so if it see UN1 it creates unique index else just create nonunique index.

INSERT INTO dmart_mkt1.myindexes (IndexName, TableName, Text,sch_name)
 VALUES ('EIW_CUSTOMER_UN1','EIW_CUSTOMER', '(CUSTid ASC) PCTFREE 0 ALLOW  REVERSE SCANS','DMART_MKT0');
CREATE PROCEDURE dmart_mkt1.SPRebuildIndexes(IN Sch_name varchar(100),IN tname varchar (100))

LANGUAGE SQL

NOT DETERMINISTIC



P1: BEGIN

  DECLARE iname varchar (32);



  DECLARE cmd varchar (1000);

  DECLARE create_cmd varchar (1000);

  DECLARE sqlcode integer default 0;



  DECLARE c1 CURSOR WITH HOLD FOR

  SELECT m.IndexName, m.Text

  FROM dmart_mkt1.MYINDEXES m

  LEFT JOIN sysibm.sysindexes s

    ON upper(m.IndexName) = upper(s.name)

   AND upper(m.TableName) = upper(s.tbname)
    and upper(m.Sch_Name) =upper(s.tbcreator)

  WHERE s.name is NULL
  AND upper(m.TableName) = UPPER (tname)
    AND upper(m.Sch_Name) =upper(Sch_name);

  OPEN c1;

  FETCH c1 INTO iname, cmd;

  WHILE (SQLCODE = 0)

  DO

    SET create_cmd = 'CREATE INDEX '||Sch_name||'.' || iname || ' ON ' ||Sch_name||'.'|| tname || ' ' || cmd ;

    EXECUTE IMMEDIATE create_cmd;

    commit;

    FETCH c1 INTO iname, cmd;

  END WHILE;

END

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of sam2929
sam2929

ASKER

Error below when i made above change

CALL DMART_MKT1.SPRebuildIndexes('DMART_MKT0','EIW_CUSTOMER')
SQL0104N  An unexpected token "DMART_MKT0" was found following "CREATE INDEX
UNIQUE".  Expected tokens may include:  "<space>".  SQLSTATE=42601

SQL0104N  An unexpected token "DMART_MKT0" was found following "CREATE INDEX UNIQUE".  Expected tokens may include:  "<space>                                ".
Avatar of sam2929

ASKER

;(
my bad, try

with:
 SET create_cmd = 'CREATE '
 || case when iname like '%UN_' then ' UNIQUE ' ELSE '' end || ' INDEX '
 ||Sch_name||'.' || iname || ' ON ' ||Sch_name||'.'|| tname || ' ' || cmd ;