Solved

Modification of procedure to create unique index too

Posted on 2010-09-10
4
437 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Change the sort Order 5 254
Wrong record extracting - DB2 SQL 10 155
DB2 Integer to Decimal 1 118
iSeries DB2 - Query with Sub Query? 7 98
November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
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…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now