[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Modification of procedure to create unique index too

Posted on 2010-09-10
4
Medium Priority
?
470 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

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 (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…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

650 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