• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

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

0
sam2929
Asked:
sam2929
  • 2
  • 2
1 Solution
 
momi_sabagCommented:
replace this:
 SET create_cmd = 'CREATE INDEX '||Sch_name||'.' || iname || ' ON ' ||Sch_name||'.'|| tname || ' ' || cmd ;


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



this will support up to 10 unique indexes per table from UN0 to UN9
0
 
sam2929Author Commented:
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>                                ".
0
 
sam2929Author Commented:
;(
0
 
momi_sabagCommented:
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 ;

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now