Solved

Modification of procedure to create unique index too

Posted on 2010-09-10
4
448 Views
Last Modified: 2012-05-10
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
Comment
Question by:sam2929
  • 2
  • 2
4 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 500 total points
ID: 33647726
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
 

Author Comment

by:sam2929
ID: 33654072
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
 

Author Comment

by:sam2929
ID: 33667209
;(
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 33687805
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

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
db2 connect on mac 3 523
Guide to DB2 SQL pl programming 3 212
datediff of ssrs 3 189
IBM DB2  List Table Columns using a SQL SELECT STatement and ADO in a VB Script 5 86
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question