We help IT Professionals succeed at work.

stored procedure with multiple functionality for dropping and creating indexes

sam2929
sam2929 asked
on
1,799 Views
Last Modified: 2020-04-13
Hi there,
Can any let me know if it is possible to have procedure with below functionality?
The stored procedure should have the following functionality:
Should have an argument for the table schema name.
Should have an argument for the table name.
Should have an argument for creating or deleting the indexes for the specified table.
Should have an argument for  performing the action (creating or deleting) on all secondary indexes for the specified table. True by default. If set to false all indexes regardless of type (eg. unique , not unique) .
Should have an override to drop or create specific indexes.
Be dynamic to allow other table indexes to dropped and recreated in this fashion (investigate using the system catalogs).
Ignore (do not raise an error) when dropping indexes that are not found for the specified table in the database.
Ignore (do not raise an error) when creating indexes already exiting for the specified table in database.
Comment
Watch Question

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

Piece of cake.  :)

DB2 allows you to overload a procedure name, so if you want/need some flexibility you can create several procedures with the same name (e.g. "BuildAnIndex" or anything else) as long as the parameter lists are different.

Inside the procedure, just concatenate the parameters into the appropriate string and call EXECUTE IMMEDIATE.

  http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0000950.htm

All DB2 SQL statements can't be run with EXECUTE IMMEDIATE, but CREATE INDEX should be fine.

A simple example is below.  You can add additional parameters and error handling as necessary.


Good Luck,
Kent

CREATE PROCEDURE BUILDMYINDEX (
  IN SCHEMANAME varchar (35), 
  IN TABLENAME varchar (35),
  IN INDEXNAME varchar (35), 
  IN COLUMNLIST varchar (35))
AS
  DECLARE SQLSTATEMENT varchar (1000);

  SET SQLSTATEMENT = 'CREATE INDEX ' || SCHEMANAME || '.' || INDEXNAME || ' ON ' || TABLENAME ||
    ' (' || COLUMNLIST || ')';

  EXECUTE IMMEDIATE SQLSTATEMENT;

Open in new window

Author

Commented:
Hi Kent,
Not that good in stored procedure,
How can i modify this stored procedure lets say i have table aa.fact and it has like 3 indexes one is unquie other two are non unique.
1)First i want to drop just two non unqiue lets name them aaa_1 and aaa_2
2)once the data is loaded i want to recreate two nonunique indexes
3)create aaa_1 and aaa_2,

Can you please implement this logic in the stored procedure

Thanks
Sam

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

Instead of building a stored procedure to build the command from a set of parameters, can you live with building a stored procedure that just checks and builds all of the indexes?

What about a batch script that does the same?


Either of those will be a lot easier than what you first asked for.


Kent

Author

Commented:
Hi kent,
What will be better option,
Right now we load data using ETL tool with in that ETL tool we drop indexes before loading data and after we load the data we create indexes from with in the etl .

Now what happen is sumtime while creating indexes it fails due to log transcation full error , so we have
to manually create them we want to avoid it and create a procedure so that if this happens again we just run the procedure to fix it,Please let me know your thoughts about it, we want stored procedure
so that we can call that procedure via etl tool.

Thanks,
Sam
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

Cool.  That's actually pretty easy.  :)  And a lot easier to maintain than a "one size fits all" procedure with a lot of parameters.

Since you're going to run the procedure to rebuild the indexes, you should probably set it up to test for the existence of the indexes before trying to create it.  (You can always just issue the create and test the error code.  Either approach will work fine.)

In that case, something like the example below should work for you.

Note that you cannot execute a RUNSTATS from the stored procedure.  Once the procedure runs, you'll want to run a job to update the statistics.

 
Kent

CREATE PROCEDURE RebuildIndexes()

LANGUAGE SQL
NOT DETREMINISTIC
AS

BEGIN

  DECLARE ic integer;
  DECLARE statement VARCHAR (1000);

--  Repeat this code for every index that you need to check

  SELECT COUNT(*) INTO ic FROM SYSIBM.SYSINDEXES
  WHERE name = 'MYINDEX001'
    AND tbname = 'MYTABLE001'
    AND creator = 'TABLE_OWNER';   -- e.g. db2inst1

  IF (ic <> 0) THEN
    SET statement = 'CREATE INDEX MYINDEX001 ON MYTABLE001 (col1, col2, ..., coln)';
    EXECUTE IMMEDIATE statement;
  END IF;
--
END;

Open in new window

CERTIFIED EXPERT
Top Expert 2011

Commented:
what you want to do will depend on the version and environment of db2 you are running ...

what are they?

you may find it is better to script out the Indexes for your tables from you data model

 and then allow the DBAs to maintain these files with any additional parameters
 that they wish to include for performance reasons ... (that is likely to work best in the long run , otherwise performance tweaks will be potentially lost..., and responsibilty for physical definition... is firmly with the dba...)
l
into a series of batch files and execute them around your ETL code...

ie

Drop ....
ETL
Create...
Runstats...

similarly have batch files under DBA maintenance control to perform runstats and reorgs at the appropiate times within you etl suite, but letting the DBAs control
the choice of parameter settings...

Author

Commented:
Hi Kent,
Looks like select don't like  ic

SELECT COUNT(*) INTO ic FROM SYSIBM.SYSINDEXES WHERE name = 'EIW_CUSTOMER_UN1' AND tbname = 'EIW_CUSTOMER' AND tbcreator = 'DMART_MKT0'
SQL0206N  "IC" is not valid in the context where it is used.  SQLSTATE=42703

SQL0206N  "IC                                                                    " is not valid in the context where it is used.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

That should work fine in DB2.  SQL Server, et al, use a different syntax but that's correct for DB2.

Can you post your procedure?


Thanks,
Kent

Author

Commented:
Error
CREATE PROCEDURE RebuildIndexes()

LANGUAGE SQL
NOT DETREMINISTIC
AS

BEGIN

  DECLARE ic integer;
  DECLARE statement VARCHAR (1000);

--  Repeat this code for every index that you need to check

  SELECT COUNT(*) INTO ic FROM SYSIBM.SYSINDEXES
  WHERE name = 'EIW_CUSTOMER_N1'
    AND tbname = 'EIW_CUSTOMER'
    AND creator = 'DMART_MKT0';   -- e.g. db2inst1

  IF (ic <> 0) THEN
    SET statement = 'CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N1" ON "DMART_MKT0"."EIW_CUSTOMER"
                          ("CUST_CITY" ASC)
                          PCTFREE 0 ALLOW REVERSE SCANS';
    EXECUTE IMMEDIATE statement;
  END IF;
------------------------------------------------------------------------------
CREATE PROCEDURE RebuildIndexes()


LANGUAGE SQL
NOT DETREMINISTIC
AS


BEGIN


DECLARE ic integer
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "NOT" was found following "ES()   LANGUAGE SQL
".  Expected tokens may include:  "FOR".  LINE NUMBER=5.  SQLSTATE=42601

DECLARE statement VARCHAR (1000)
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "DECLARE statement VARCHAR" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  
SQLSTATE=42601

SELECT COUNT(*) INTO ic FROM SYSIBM.SYSINDEXES WHERE name = 'EIW_CUSTOMER_N1' AND tbname = 'EIW_CUSTOMER' AND creator = 'DMART_MKT0';   -- e.g. db2inst1 IF (ic <> 0) THEN SET statement = 'CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N1" ON "DMART_MKT0"."EIW_CUSTOMER" ("CUST_CITY" ASC) PCTFREE 0 ALLOW REVERSE SCANS'
SQL0104N  An unexpected token ";" was found following "eator = 'DMART_MKT0'".  
Expected tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601

EXECUTE IMMEDIATE statement
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "IMMEDIATE" was found following "EXECUTE ".  
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

END IF
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END IF".
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END IF".  Expected tokens may include:  "JOIN <joined_table>                           ".

Explanation:

A syntax error in the SQL statement was detected at the specified
token following the text "<text>".  The "<text>" field indicates
the 20 characters of the SQL statement that preceded the token
that is not valid.  

 As an aid to the programmer, a partial list of valid tokens is
provided in the SQLERRM field of the SQLCA as "<token-list>".  
This list assumes the statement is correct to that point.  
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Ahhh...

The old ';' gotcha.

When running the command line, DB2 normally uses the NEWLINE character as the logical end-of-line.  Your procedure has more than a dozen NEWLINE characters as every line ends with one.

All you need to do is alert DB2 to key on another character.  I don't see an '@' symbol in your source, so that will work.

It's a little irritating to keep the '@' sign as you have to remember to end every line with one, so once the procedure is created I'd suggest resetting the line terminator.  Quitting out of the CLI and restarting it with the defaults is the easiest.


Good Luck,
Kent



db2 -dt@
db2>  connect to test@
db2>
CREATE PROCEDURE RebuildIndexes()

LANGUAGE SQL
NOT DETREMINISTIC
AS

BEGIN

  DECLARE ic integer;
  DECLARE statement VARCHAR (1000);

--  Repeat this code for every index that you need to check

  SELECT COUNT(*) INTO ic FROM SYSIBM.SYSINDEXES
  WHERE name = 'EIW_CUSTOMER_N1'
    AND tbname = 'EIW_CUSTOMER'
    AND creator = 'DMART_MKT0';   -- e.g. db2inst1

  IF (ic <> 0) THEN
    SET statement = 'CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N1" ON "DMART_MKT0"."EIW_CUSTOMER"
                          ("CUST_CITY" ASC)
                          PCTFREE 0 ALLOW REVERSE SCANS';
    EXECUTE IMMEDIATE statement;
  END IF;
END@

Author

Commented:
Error
CREATE PROCEDURE RebuildIndexes()

LANGUAGE SQL
NOT DETREMINISTIC
AS

BEGIN

  DECLARE ic integer;
  DECLARE statement VARCHAR (1000);

--  Repeat this code for every index that you need to check

  SELECT COUNT(*) INTO ic FROM SYSIBM.SYSINDEXES
  WHERE name = 'EIW_CUSTOMER_N1'
    AND tbname = 'EIW_CUSTOMER'
    AND creator = 'DMART_MKT0';   -- e.g. db2inst1

  IF (ic <> 0) THEN
    SET statement = 'CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N1" ON "DMART_MKT0"."EIW_CUSTOMER"
                          ("CUST_CITY" ASC)
                          PCTFREE 0 ALLOW REVERSE SCANS';
    EXECUTE IMMEDIATE statement;
  END IF;
END@;
------------------------------------------------------------------------------
CREATE PROCEDURE RebuildIndexes()

LANGUAGE SQL
NOT DETREMINISTIC
AS

BEGIN

  DECLARE ic integer
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "NOT" was found following "XES()  LANGUAGE SQL
".  Expected tokens may include:  "FOR".  LINE NUMBER=4.  SQLSTATE=42601

DECLARE statement VARCHAR (1000)
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "DECLARE statement VARCHAR" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  
SQLSTATE=42601

SELECT COUNT(*) INTO ic FROM SYSIBM.SYSINDEXES WHERE name = 'EIW_CUSTOMER_N1' AND tbname = 'EIW_CUSTOMER' AND creator = 'DMART_MKT0';   -- e.g. db2inst1
 IF (ic <> 0) THEN SET statement = 'CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N1" ON "DMART_MKT0"."EIW_CUSTOMER" ("CUST_CITY" ASC) PCTFREE 0 ALLOW REVERSE SCANS'
SQL0104N  An unexpected token ")" was found following "  IF (ic <> 0".  
Expected tokens may include:  ")".  SQLSTATE=42601

EXECUTE IMMEDIATE statement
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "IMMEDIATE" was found following "EXECUTE ".  
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

END IF
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END IF".
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

END@
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END@".  
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END@".  Expected tokens may include:  "JOIN <joined_table>                             ".

Explanation:

A syntax error in the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure was detected at the specified token
following the text "<text>". The "<text>" field indicates the 20
characters of the SQL statement or the input command string for the
SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

As an aid, a partial list of valid tokens is provided in the SQLERRM
field of the SQLCA as "<token-list>". This list assumes the statement is
correct to that point.

The statement cannot be processed.

User response:

Examine and correct the statement in the area of the specified token.

 sqlcode: -104

 sqlstate: 42601
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
My bad.  The db2 CLI invokation should be:

  db2 -td@

(I has swapped the 't' and 'd' before.)

Any you don't need the ';' after the '@' that ends the procedure.  DB2 will try to create the procedure, then it will try to process the ';' as a command.


Kent

Author

Commented:
Hi Kent,
Can i run procedure through command editor instead of command line processor?

Thanks,
Sam

Author

Commented:
I am running this in command editor not putting ; after END@ but when i get error i see ; any idea why it is behaving like this.
CREATE PROCEDURE RebuildIndexes()

LANGUAGE SQL
NOT DETREMINISTIC
AS

BEGIN

  DECLARE ic integer;
  DECLARE statement VARCHAR (1000);

--  Repeat this code for every index that you need to check

  SELECT COUNT(*) INTO ic FROM SYSIBM.SYSINDEXES
  WHERE name = 'EIW_CUSTOMER_N1'
    AND tbname = 'EIW_CUSTOMER'
    AND creator = 'DMART_MKT0';   -- e.g. db2inst1

  IF (ic <> 0) THEN
    SET statement = 'CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N1" ON "DMART_MKT0"."EIW_CUSTOMER"
                          ("CUST_CITY" ASC)
                          PCTFREE 0 ALLOW REVERSE SCANS';
    EXECUTE IMMEDIATE statement;
  END IF;
END@
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

Probably not the command editor, though several tools do have procedure editors.  You should be able to run it anywhere, just not create it.


You might install the IBM Data Studio.  It's a very good tool.


Kent

Author

Commented:
Hi Kent,
Getting error below while running  in command editor i remove create to just test it and other funny thing is i am not putting ; after END@ but sumhow it does it automatic

LANGUAGE SQL
NOT DETREMINISTIC
AS

BEGIN

  DECLARE ic integer;
  DECLARE statement VARCHAR (1000);

--  Repeat this code for every index that you need to check

  SELECT COUNT(*) INTO ic FROM SYSIBM.SYSINDEXES
  WHERE name = 'EIW_CUSTOMER_N1'
    AND tbname = 'EIW_CUSTOMER'
    AND creator = 'DMART_MKT0';   -- e.g. db2inst1

  IF (ic <> 0) THEN
    SET statement = 'CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N1" ON "DMART_MKT0"."EIW_CUSTOMER"
                          ("CUST_CITY" ASC)
                          PCTFREE 0 ALLOW REVERSE SCANS';
    EXECUTE IMMEDIATE statement;
  END IF;
END@;
------------------------------------------------------------------------------
LANGUAGE SQL NOT DETREMINISTIC AS BEGIN DECLARE ic integer
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "NOT" was found following "LANGUAGE SQL ".  
Expected tokens may include:  "JOIN".  SQLSTATE=42601

DECLARE statement VARCHAR (1000)
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "DECLARE statement VARCHAR" was found following
"BEGIN-OF-STATEMENT".  Expected tokens may include:  "<values>".  
SQLSTATE=42601

SELECT COUNT(*) INTO ic FROM SYSIBM.SYSINDEXES WHERE name = 'EIW_CUSTOMER_N1' AND tbname = 'EIW_CUSTOMER' AND creator = 'DMART_MKT0';   -- e.g. db2inst1 IF (ic <> 0) THEN SET statement = 'CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N1" ON "DMART_MKT0"."EIW_CUSTOMER" ("CUST_CITY" ASC) PCTFREE 0 ALLOW REVERSE SCANS'
SQL0104N  An unexpected token ";" was found following "eator = 'DMART_MKT0'".  
Expected tokens may include:  "END-OF-STATEMENT".  SQLSTATE=42601

EXECUTE IMMEDIATE statement
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "IMMEDIATE" was found following "EXECUTE ".  
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

END IF
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END IF".
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

END@
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END@".  
Expected tokens may include:  "JOIN <joined_table>".  SQLSTATE=42601

SQL0104N  An unexpected token "END-OF-STATEMENT" was found following "END@".  Expected tokens may include:  "JOIN <joined_table>                             ".
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

You can't run the meat of a stored procedure in the command editor.  DECLARE, IF, and the other procedure language elements aren't supported at the command level.


Kent

Author

Commented:
Hi Kent,
When i paste this procedure in Data Sudio, i see three red cross which says
NOT appears to be misplaced

AS colon expected after this token

END@ end excepted instead of this input

But if i remove NOT DETREMINISTIC AS and @ against END it seems to give not red cross.

Procedure i pasted is:
CREATE PROCEDURE RebuildIndexes()

LANGUAGE SQL
NOT DETREMINISTIC
AS

BEGIN

  DECLARE ic integer;
  DECLARE statement VARCHAR (1000);

--  Repeat this code for every index that you need to check

  SELECT COUNT(*) INTO ic FROM SYSIBM.SYSINDEXES
  WHERE name = 'EIW_CUSTOMER_N1'
    AND tbname = 'EIW_CUSTOMER'
    AND creator = 'DMART_MKT0';   -- e.g. db2inst1

  IF (ic <> 0) THEN
    SET statement = 'CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N1" ON "DMART_MKT0"."EIW_CUSTOMER"
                          ("CUST_CITY" ASC)
                          PCTFREE 0 ALLOW REVERSE SCANS';
    EXECUTE IMMEDIATE statement;
  END IF;
END@
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

Just a couple of small syntax things.  'AS' isn't needed in the header and 'DETERMINISTIC' is misspelled.  Also, a beginning label is a good idea.

The code below compiles for me,
Kent

CREATE PROCEDURE RebuildIndexes()

LANGUAGE SQL
NOT DETERMINISTIC

P1: BEGIN

  DECLARE ic integer;
  DECLARE statement VARCHAR (1000);

--  Repeat this code for every index that you need to check

  SELECT COUNT(*) INTO ic FROM SYSIBM.SYSINDEXES
  WHERE name = 'EIW_CUSTOMER_N1'
    AND tbname = 'EIW_CUSTOMER'
    AND creator = 'DMART_MKT0';   -- e.g. db2inst1

  IF (ic <> 0) THEN
    SET statement = 'CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N1" ON "DMART_MKT0"."EIW_CUSTOMER"
                          ("CUST_CITY" ASC)
                          PCTFREE 0 ALLOW REVERSE SCANS';
    EXECUTE IMMEDIATE statement;
  END IF;
END@

Open in new window

Author

Commented:
Hi Kent,
Getting error below
call TSWGMKT.DMART_MKT1.REBUILDINDEXES

The name "REBUILDINDEXES" has the wrong number of qualifiers.. SQLCODE=-108, SQLSTATE=42601, DRIVER=3.58.81
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
I don't know your topography, but one of these may work:

  call TSWGMKT.REBUILDINDEXES

  call DMART_MKT1.REBUILDINDEXES

Author

Commented:
Hi Kent,
This ran but no index is created.
------------------------------ Commands Entered ------------------------------
call DMART_MKT1.REBUILDINDEXES;
------------------------------------------------------------------------------
call DMART_MKT1.REBUILDINDEXES

  Return Status = 0
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Ok.  Let's run the procedure's two key queries from a client:

  SELECT COUNT(*) INTO ic FROM SYSIBM.SYSINDEXES
  WHERE name = 'EIW_CUSTOMER_N1'
    AND tbname = 'EIW_CUSTOMER'
    AND creator = 'DMART_MKT0';   -- e.g. db2inst1

  IF (ic <> 0) THEN
    SET statement = 'CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N1" ON "DMART_MKT0"."EIW_CUSTOMER"
                          ("CUST_CITY" ASC)
                          PCTFREE 0 ALLOW REVERSE SCANS';


becomes:

  SELECT COUNT(*) FROM SYSIBM.SYSINDEXES
  WHERE name = 'EIW_CUSTOMER_N1'
    AND tbname = 'EIW_CUSTOMER'
    AND creator = 'DMART_MKT0';

and:

    CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N1" ON "DMART_MKT0"."EIW_CUSTOMER"
                          ("CUST_CITY" ASC)
                          PCTFREE 0 ALLOW REVERSE SCANS;



My guess is that we have a simple name mismatch.

Kent

Author

Commented:
There was no index so count was 0 for first sql and once i create the index count is 1 now.
We have ic<>0 shuld this not be ic=0 then?
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:

Yep.  :)

Author

Commented:
Hi Kent,
Repeat this code don't look very efficent as in some cases we create new indexes on tables.Any alternative way so that we just look at tbname if index is there don't create it else create it?

--  Repeat this code for every index that you need to check

  SELECT COUNT(*) INTO ic FROM SYSIBM.SYSINDEXES
  WHERE name = 'EIW_CUSTOMER_N1'
    AND tbname = 'EIW_CUSTOMER'
    AND creator = 'DMART_MKT0';   -- e.g. db2inst1

  IF (ic <> 0) THEN
    SET statement = 'CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N1" ON "DMART_MKT0"."EIW_CUSTOMER"
                          ("CUST_CITY" ASC)
                          PCTFREE 0 ALLOW REVERSE SCANS';
    EXECUTE IMMEDIATE statement;
  END IF;
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
You could always create another table that describes your desired index(es), then test them in a loop.


CREATE TABLE myindexes
(
  Id                     integer identity,
  IndexName      varchar (32),
  TableName      varchar (32),
  Text                 varchar (1000)
);

INSERT INTO myindexes (IndexName, TableName, Text) VALUES ('EIW_CUSTOMER_N1', 'EIW_CUSTOMER', '("CUST_CITY" ASC) PCTFREE 0 ALLOW REVERSE SCANS');

Then your stored procedure can test that the indexes described in your table exist.  If they don't, create them.


Kent

Author

Commented:
Hi Kent,
Then procedure sgould look like this right

SELECT COUNT(*) INTO ic FROM myindexes
  WHERE tbname = 'EIW_CUSTOMER'
    group by indexname

  IF (ic = 0) THEN
    SET statement = 'CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N1" ON "DMART_MKT0"."EIW_CUSTOMER"
                          ("CUST_CITY" ASC)
                          PCTFREE 0 ALLOW REVERSE SCANS';
    EXECUTE IMMEDIATE statement;
  END IF;
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:

I might do it in a single pass.  This gives you the flexibility to change, add, or delete indexes without having to change the stored procedure.  Just maintain the descriptions in this table.

Kent



DECLARE iname varchar (32);
DECLARE tname varchar (32);
DECALRE cmd varchar (1000);

DECLARE c1 CURSOR FOR
SELECT m.IndexName, m.TableName, m.Text
FROM myindexes m
LEFT JOIN sysibm.sysindexes s
  ON m.IndexName = s.name
 AND m.TableName = s.tbname
WHERE s.name is NULL
  AND s.creator = 'DMART_MKT0';

OPEN c1;

FETCH c1 INTO iname, tname, cmd;
WHILE (SQLCODE = 0)
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX ' || iname || ' ON ' || tname || ' ' || cmd;
  FETCH c1 INTO iname, tname, cmd;
END;


Author

Commented:
Hi Kent,
By doing
EXECUTE IMMEDIATE 'CREATE INDEX ' || iname || ' ON ' || tname || ' ' || cmd;
Basically we are creating the indexes on fly right
lets say we want to create two below indexes so we will call it 2 times right?

CALL RebuildIndexes(DMART_MKT0"."EIW_CUSTOMER_N2","DMART_MKT0"."EIW_CUSTOMER" ("CMR_ISU_CODE" ASC),PCTFREE 0 ALLOW REVERSE SCANS')

CALL RebuildIndexes("DMART_MKT0"."EIW_CUSTOMER_N1","DMART_MKT0"."EIW_CUSTOMER" ("CUST_CITY" ASC),PCTFREE 0 ALLOW REVERSE SCANS')



CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N2" ON "DMART_MKT0"."EIW_CUSTOMER" ("CMR_ISU_CODE" ASC) ;
CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER_N1" ON "DMART_MKT0"."EIW_CUSTOMER" ("CUST_CITY" ASC);
                          PCTFREE 0 ALLOW REVERSE SCANS';
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
You can certainly set up a procedure to do that, but that's not what my last suggestion does.

If you'll put the definition in the table (similar to the INSERT statement in my example) then all you need to do is call the procedure.  It will make sure that every index in the table is in place.

  CALL REBUILDINDEXES ();

You can add a lot of bullet-proofing, like making sure that the table exists before creating the index.


Kent

Author

Commented:
Hi Kent,
Errors on procedure in data studio on
open c1  having was expected instead of open
WHILE (SQLCODE = 0) appers to be misplaced
EXECUTE IMMEDIAT unexpected text

CREATE PROCEDURE RebuildIndexes1()
LANGUAGE SQL
NOT DETERMINISTIC

P1: BEGIN
DECLARE iname varchar (32);
DECLARE tname varchar (32);
DECLARE cmd varchar (1000);

DECLARE c1 CURSOR FOR
SELECT m.IndexName, m.TableName, 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)
WHERE s.name is NULL
OPEN c1;

FETCH c1 INTO iname, tname, cmd;
WHILE (SQLCODE = 0)
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX ' || iname || ' ON ' || tname || ' ' || cmd;
  FETCH c1 INTO iname, tname, cmd;
END IF;
END

Open in new window

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
End the previous line (16) with a semi-colon.

Author

Commented:
still two errors
at
WHILE (SQLCODE = 0) appers to be misplaced
EXECUTE IMMEDIAT unexpected text


CREATE PROCEDURE RebuildIndexes1()
LANGUAGE SQL
NOT DETERMINISTIC

P1: BEGIN
DECLARE iname varchar (32);
DECLARE tname varchar (32);
DECLARE cmd varchar (1000);

DECLARE c1 CURSOR FOR
SELECT m.IndexName, m.TableName, 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)
WHERE s.name is NULL;
OPEN c1;
FETCH c1 INTO iname, tname, cmd;
WHILE (SQLCODE = 0)
BEGIN
  EXECUTE IMMEDIATE 'CREATE INDEX ' || iname || ' ON ' || tname || ' ' || cmd;
  FETCH c1 INTO iname, tname, cmd;
END IF;
END

Open in new window

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

sqlcode must be declared.  The procedure below compiles just fine.


Kent


CREATE PROCEDURE RebuildIndexes1()
LANGUAGE SQL
NOT DETERMINISTIC

P1: BEGIN
DECLARE iname varchar (32);
DECLARE tname varchar (32);
DECLARE cmd varchar (1000);
DECLARE sqlcode integer default 0;

DECLARE c1 CURSOR FOR
SELECT m.IndexName, m.TableName, m.Text
FROM MYINDEXES m
LEFT JOIN sysibm.sysindexes s
  ON upper(m.IndexName) = upper(s.name)
 AND upper(m.TableName) = upper(s.tbname)
WHERE s.name is NULL;
OPEN c1;
FETCH c1 INTO iname, tname, cmd;
WHILE (SQLCODE = 0)
DO
  EXECUTE IMMEDIATE 'CREATE INDEX ' || iname || ' ON ' || tname || ' ' || cmd;
  FETCH c1 INTO iname, tname, cmd;
END WHILE;
END

Open in new window

Author

Commented:
Error will deploy
An unexpected token "EXECUTE IMMEDIATE" was found following "LE (SQLCODE = 0)
DO
".  Expected tokens may include:  "<psm_return>".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.58.81
---
SQLCODE: -104, SQLSTATE: 42601
***

stored procedure - Create REBUILDINDEXES1 failed.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

What system are you running?  It works fine in UDB/LUW 9.7


Kent

Author

Commented:
Database server        = DB2/AIX64 9.5.4
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:

That's weird.  You're running 64-bit UDB 9.5 on AIX, I'm running 64-bit UDB 9.7 on Windows.  For this purpose, they should be the same....

And my linux equivalent is down right now.....

Author

Commented:
Hi Kent,
Can you please try on linux.
Thanks,
Sam
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

I'd love to, but I need to reinstall DB2 on my linux system.  That won't happen for a while...


Can you post the exact SQL that you're trying to compile?  I suspect a typo or missing punctuation in your code.



Kent

Author

Commented:
Hi Kent,
Added code and screen shot of error.
Thanks
CREATE PROCEDURE dmart_mkt1.RebuildIndexes1()
LANGUAGE SQL
NOT DETERMINISTIC

P1: BEGIN
DECLARE iname varchar (32);
DECLARE tname varchar (32);
DECLARE cmd varchar (1000);
DECLARE sqlcode integer default 0;

DECLARE c1 CURSOR FOR
SELECT m.IndexName, m.TableName, m.Text
FROM MYINDEXES m
LEFT JOIN sysibm.sysindexes s
  ON upper(m.IndexName) = upper(s.name)
 AND upper(m.TableName) = upper(s.tbname)
WHERE s.name is NULL;
OPEN c1;
FETCH c1 INTO iname, tname, cmd;
WHILE (SQLCODE = 0)
DO
  EXECUTE IMMEDIATE 'CREATE INDEX ' || iname || ' ON ' || tname || ' ' || cmd ;
  FETCH c1 INTO iname, tname, cmd;
END WHILE;
END

Open in new window

error.doc
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

This is really bizarre.  After dropping the schema name the rest of the procedure compiles just fine for me.

Let's try two things.

-- Try to create the procedure using the default schema.  (remove 'dmart_mkt1.' from the procedure name.)

-- Comment out the EXECUTE IMMEDIATE statement in this procedure.

Let's see if this is a strange error brought about by a permissions issue.

Kent

Author

Commented:
After comment on EXECUTE IMMEDIATE statement in this procedure deplot went good.

Deploy DMART_MKT1.REBUILDINDEXES1

stored procedure - Create REBUILDINDEXES1 completed.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:

Ok.  Let's be really silly and follow the strictest interpretation of the statement definition.

The IBM documentation says:

  -EXECUTE IMMEDIATE--host variable-------


Let's try that.

Kent

CREATE PROCEDURE dmart_mkt1.RebuildIndexes1()
LANGUAGE SQL
NOT DETERMINISTIC

P1: BEGIN
  DECLARE iname varchar (32);
  DECLARE tname varchar (32);
  DECLARE cmd varchar (1000);
  DECLARE create_cmd varchar (1000);
  DECLARE sqlcode integer default 0;

  DECLARE c1 CURSOR FOR
  SELECT m.IndexName, m.TableName, m.Text
  FROM MYINDEXES m
  LEFT JOIN sysibm.sysindexes s
    ON upper(m.IndexName) = upper(s.name)
   AND upper(m.TableName) = upper(s.tbname)
  WHERE s.name is NULL;

  OPEN c1;
  FETCH c1 INTO iname, tname, cmd;
  WHILE (SQLCODE = 0)
  DO
    SET create_cmd = 'CREATE INDEX ' || iname || ' ON ' || tname || ' ' || cmd ;
    EXECUTE IMMEDIATE create_cmd;
    FETCH c1 INTO iname, tname, cmd;
  END WHILE;
END

Open in new window

Author

Commented:
Hi Kent,
This works, Now what will be my call statment.
call DMART_MKT1.REBUILDINDEXES()
Thanks




Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

If you're calling this from SQL, that should be fine.  If you've got a C, Java, or COBOL program there are a few more hoops to be jumped through.


Kent

Author

Commented:
Hi Kent,
I want to modify this procedure so that we can call just one table at one time lets say like
call DMART_MKT1.REBUILDINDEXES(dmart_mkt0.eiw_customer) , Should below work then?
Added (IN tname varchar(130))



CREATE PROCEDURE dmart_mkt1.SPREBUILDINDEXES(IN tname varchar(130))
LANGUAGE SQL
NOT DETERMINISTIC

P1: BEGIN
  DECLARE iname varchar (32);
  DECLARE tname varchar (32);
  DECLARE cmd varchar (1000);
  DECLARE create_cmd varchar (1000);
  DECLARE sqlcode integer default 0;

  DECLARE c1 CURSOR FOR
  SELECT m.IndexName, m.TableName, 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)
  WHERE s.name is NULL;
COMMIT;
  OPEN c1;
  FETCH c1 INTO iname, tname, cmd;
  WHILE (SQLCODE = 0)
  DO
    SET create_cmd = 'CREATE INDEX ' || iname || ' ON ' || tname || ' ' || cmd ;
    EXECUTE IMMEDIATE create_cmd;
    FETCH c1 INTO iname, tname, cmd;
  END WHILE;
END

Open in new window

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
That won't work, but the code below should.  :)

You should store all of your index declarations in your table and just select the ones that you want.  The procedure that you've shown will produce errors as it attempts to create all of the indexes in MYINDEXES on the table that you pass to the procedure.

Also, the COMMIT statement is misplaced.  At that point, you haven't changed any user data so there's no point in committing there.



Kent

CREATE PROCEDURE dmart_mkt1.RebuildIndexes1(IN tname varchar (32))
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 FOR
  SELECT m.IndexName, m.Text
  FROM MYINDEXES m
  LEFT JOIN sysibm.sysindexes s
    ON upper(m.IndexName) = upper(s.name)
   AND upper(m.TableName) = upper(s.tbname)
  WHERE s.name is NULL
   AND upper(m.TableName) = upper(tname);

  OPEN c1;
  FETCH c1 INTO iname, cmd;
  WHILE (SQLCODE = 0)
  DO
    SET create_cmd = 'CREATE INDEX ' || iname || ' ON ' || tname || ' ' || cmd ;
    EXECUTE IMMEDIATE create_cmd;
    FETCH c1 INTO iname, cmd;
  END WHILE;
END

Open in new window

Author

Commented:
Getting error below should the vairable be Tablename instead of tname
CALL DMART_MKT1.SPREBUILDINDEXES(DMART_MKT0.EIW_CUSTOMER)
SQL0206N  "DMART_MKT0.EIW_CUSTOMER" is not valid in the context where it is
used.  SQLSTATE=42703

SQL0206N  "DMART_MKT0.EIW_CUSTOMER  
CREATE PROCEDURE dmart_mkt1.SPREBUILDINDEXES(IN tname varchar(32))

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 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)

  WHERE s.name is NULL;

  OPEN c1;

  FETCH c1 INTO iname, cmd;

  WHILE (SQLCODE = 0)

  DO

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

    EXECUTE IMMEDIATE create_cmd;

    FETCH c1 INTO iname,cmd;

  END WHILE;

END

Open in new window

CERTIFIED EXPERT
Top Expert 2011

Commented:
1) YOU HAVEN'T GOT the parameter test in the procedure code...

2) you need to pass the parameter in quotes

3) you need 2 parameters 1 for table owner DMART_MKT0
   and the second for the table name EIW_CUSTOMER

4) you need to adjust the procedure to account for the owner/schema name  columns which represent a
    table and index.


Author

Commented:
kool this works
CALL DMART_MKT1.SPREBUILDINDEXES('DMART_MKT0.EIW_CUSTOMER')
One thing more i want to do commit after every index build will this procedure do it or we need addition steps?

Thanks,
Sam
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

Don't worry about committing after building the index.  You haven't changed user data so the commit isn't necessary.


Kent

Author

Commented:
Hi Kent,
We build indexes on 35 million rows and dba has recommended to do commits as we have seen transaction log full errors so he recommended to do commits more often .

Thanks
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Ok.  If you're going to execute a COMMIT after every index build, you'll want to change the DECLARE CURSOR statement.

Add a 'WITH HOLD' clause or the COMMIT will close the cursor and you'll only build 1 index per call.



Kent

Author

Commented:
Hi Kent,
Should adding commit after EXECUTE IMMEDIATE  will do it?

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

    EXECUTE IMMEDIATE create_cmd;
    COMMIT;
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:

That won't hurt.  As long as you add the 'WITH HOLD' clause to the DECLARE CURSOR.


Kent

Author

Commented:
This makes sense?
CREATE PROCEDURE dmart_mkt1.RebuildIndexes1(IN tname varchar (32))
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 MYINDEXES m
  LEFT JOIN sysibm.sysindexes s
    ON upper(m.IndexName) = upper(s.name)
   AND upper(m.TableName) = upper(s.tbname)
  WHERE s.name is NULL
   AND upper(m.TableName) = upper(tname);

  OPEN c1;
  FETCH c1 INTO iname, cmd;
  WHILE (SQLCODE = 0)
  DO
    SET create_cmd = 'CREATE INDEX ' || iname || ' ON ' || tname || ' ' || cmd ;
    EXECUTE IMMEDIATE create_cmd;
    commit;
    FETCH c1 INTO iname, cmd;
  END WHILE;
END

Open in new window

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Looks good.

The query below should generate the SQL that the procedure will execute.  Just replace 'mytable' with the correct name.



SELECT 'CREATE INDEX ' || iname || ' ON ' || tname || ' ' || cmd
  FROM MYINDEXES m
  LEFT JOIN sysibm.sysindexes s
    ON upper(m.IndexName) = upper(s.name)
   AND upper(m.TableName) = upper(s.tbname)
  WHERE s.name is NULL
   AND upper(m.TableName) = 'mytable';


Kent

Author

Commented:
Hi Kent,
iwe really don't want to hard code tablename in sql, i was thinking when we do call it should just call
that table indexes :( any work around
 
SELECT *
  FROM DMART_MKT1.MYINDEXES m
  LEFT JOIN sysibm.sysindexes s
    ON upper(m.IndexName) = upper(s.name)
   AND upper(m.TableName) = upper(s.tbname)
  WHERE s.name is NULL
   AND upper(m.TableName) = 'HIST_FT';

Author

Commented:
please look atacchment of screenshot from join above
Screenshot-of-indexes.doc

Author

Commented:
no suggestion :(
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

Sorry -- I've been away for a couple of days.

I wasn't suggesting that the table name be hard coded into the procedure.  That was just an example to generate the SQL that the procedure will execute for 1 table.



Kent

Author

Commented:
Hi KENT,
When i run the call i get error below and cust_city is index from other table it should ignore that.Please
check screenshot attachment and procedure:
------------------------------ Commands Entered ------------------------------
CALL dmart_mkt1.RebuildIndexes('DMART_MKT2.DMT_OPP_DETAILS_HIST_FT');
------------------------------------------------------------------------------
CALL dmart_mkt1.RebuildIndexes('DMART_MKT2.DMT_OPP_DETAILS_HIST_FT')
SQL0205N  Column or attribute "CUST_CITY" is not defined in
"DMART_MKT2.DMT_OPP_DETAILS_HIST_FT".  SQLSTATE=42703
CREATE PROCEDURE dmart_mkt1.RebuildIndexes(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)

  WHERE s.name is NULL;



  OPEN c1;

  FETCH c1 INTO iname, cmd;

  WHILE (SQLCODE = 0)

  DO

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

    EXECUTE IMMEDIATE create_cmd;

    commit;

    FETCH c1 INTO iname, cmd;

  END WHILE;

END

Open in new window

Screenshot-of-indexes.doc
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

That error is pretty self explanatory, but interpreting it based on what we're doing probably isn't obvious.

The procedure was called, passing the table name 'DMART_MKT2.DMT_OPP_DETAILS_HIST_FT'.  The procedure attempted to create an index on column CUST_CITY.  An error was thrown saying that that column isn't in that table.

An easy way to sort this out, would be replace the EXECUTE IMMEDIATE statement with an INSERT INTO mydebugtable command, then see what gets written into the table.

But I think that I see it.  :)

In the code below I've added one line to the cursor declaration.  As it is now, the procedure tries to build the indexes for all of the tables, but apply them to the table name passed on the procedure call.  (One of the tables has a column called CUST_CITY, but not DMT_OPP_DETAILS_HIST_FT.)

Change the filter to contain both items shown below and you should be fine.


Kent

-- Original Cursor Definition

  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)

  WHERE s.name is NULL;


-- Correct cursor definition

  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)

  WHERE s.name is NULL
    AND upper(s.tbname) = UPPER (tname);

Open in new window

Author

Commented:
it don't like tname
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)
  WHERE s.name is NULL
    AND upper(s.tbname) = UPPER (tname);
------------------------------------------------------------------------------
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) WHERE s.name is NULL AND upper(s.tbname) = UPPER (tname)
SQL0206N  "TNAME" is not valid in the context where it is used.  
SQLSTATE=42703

SQL0206N  "TNAME                                                                 " is not valid in the context where it is used.
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
weird.

Let's copy it to a declared variable before applying the UPPER function.


Try this one.


Kent

CREATE PROCEDURE dmart_mkt1.RebuildIndexes(IN tname varchar (100))

LANGUAGE SQL

NOT DETERMINISTIC



P1: BEGIN

  DECLARE iname varchar (32);

  DECLARE tblname varchar (100);


  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)

  WHERE s.name is NULL
    AND upper (m.TableName) = tblname;


  SET tblname = UPPER (tname);

  OPEN c1;

  FETCH c1 INTO iname, cmd;

  WHILE (SQLCODE = 0)

  DO

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

    EXECUTE IMMEDIATE create_cmd;

    commit;

    FETCH c1 INTO iname, cmd;

  END WHILE;

END

Open in new window

Author

Commented:
when i run query below i can see 4 indexes which need to be created which is good,but when i call procedure it return 0 vaules and finish with in 1 second and indexes are never created looks like procedure is just skipping indexes


SELECT *
  FROM dmart_mkt1.MYINDEXES m
  LEFT JOIN sysibm.sysindexes s
    ON upper(m.IndexName) = upper(s.name)
   AND upper(m.TableName) = upper(s.tbname)
  WHERE s.name is NULL
    AND upper (m.TableName) = 'DMT_OPP_DETAILS_HIST_FT';

CALL dmart_mkt1.RebuildIndexes('dmart_mkt2.DMT_OPP_DETAILS_HIST_FT')

  Return Status = 0
Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

Ever roll your eyes, slap yourself in the forehead, and say, "Geez"....  (This is one of those moments.)   :)

Note the two highlighted items below.  The SELECT statement filters by table name.  The call statement passes a qualified table name (schema.table) to the procedure.  If you substitute the qualified name into the filter of the SELECT statement it will return no rows unless you have table names stored as {schema.table}.


SELECT *
  FROM dmart_mkt1.MYINDEXES m
  LEFT JOIN sysibm.sysindexes s
    ON upper(m.IndexName) = upper(s.name)
   AND upper(m.TableName) = upper(s.tbname)
  WHERE s.name is NULL
    AND upper (m.TableName) = 'DMT_OPP_DETAILS_HIST_FT';

CALL dmart_mkt1.RebuildIndexes('dmart_mkt2.DMT_OPP_DETAILS_HIST_FT')


I suggest that you start with the requirement that the procedure works on tables and indexes in the current schema.  Once that works, we can add the structure to manage indexes in any schema.


Kent

Author

Commented:
Tried adding schema but not luck  :(
CREATE PROCEDURE dmart_mkt1.RebuildIndexes(IN tbcreator VARCHAR(130),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)

  WHERE s.name is NULL
    AND upper(s.tbname) = UPPER (TBNAME)
    AND upper(s.tbcreator) =upper(TBCREATOR);

  OPEN c1;

  FETCH c1 INTO iname, cmd;

  WHILE (SQLCODE = 0)

  DO

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

    EXECUTE IMMEDIATE create_cmd;

    commit;

    FETCH c1 INTO iname, cmd;

  END WHILE;

END

Open in new window

Data Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
HI Kent,
This code worked now need to modify it a bit this code should now delete any index which is not find
in myindexes table ,Lets say customer_1 is in sysindexes table but not in  myindexes file then it should delete customer_1 from sysindexes.

Thanks
CREATE PROCEDURE dmart_mkt1.RebuildIndexes(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.Schema) =upper(s.tbcreator)

  WHERE s.name is NULL
  AND upper(m.TableName) = UPPER (tname)
    AND upper(m.Schema) =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

Kent OlsenData Warehouse / Database Architect
CERTIFIED EXPERT

Commented:
Hi Sam,

A procedure to Drop the indexes is almost identical to the one above.  Just change the join/filter in the SELECT and the command that we build.

Copy/paste the SELECT portion of the code below into a client and run it.  It should generate the DROP INDEX commands for everything to be dropped.  If so, the procedure should be ready to go.


Kent

CREATE PROCEDURE dmart_mkt1.RebuildIndexes(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.Schema) =upper(s.tbcreator)

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

  OPEN c1;

  FETCH c1 INTO iname, cmd;

  WHILE (SQLCODE = 0)

  DO

    SET create_cmd = 'DROP INDEX '||Sch_name||'.' || iname;

    EXECUTE IMMEDIATE create_cmd;

    commit;

    FETCH c1 INTO iname, cmd;

  END WHILE;

END 

Open in new window

Author

Commented:
hi Kent,
I did that and i getting error below i opened a new question for it can you please look at new post
as the sql gives me result which i want and the procedure i am creating went good but when i call it
it gives error below
CALL dmart_mkt1.DropIndexes1()
SQL0104N  An unexpected token "DROP" was found following "BEGIN-OF-STATEMENT".
Expected tokens may include:  "EXPAND".  SQLSTATE=42601

SQL0104N  An unexpected token "DROP" was found following "BEGIN-OF-STATEMENT".  Expected tokens may include:  "EXPAND                                        

CREATE PROCEDURE dmart_mkt1.DropIndexes1()
LANGUAGE SQL
NOT DETERMINISTIC

P1: BEGIN
DECLARE iname varchar (10000);
DECLARE tname varchar (32);
DECLARE cmd varchar (1000);
DECLARE drop_cmd varchar (1000);
DECLARE sqlcode integer default 0;

DECLARE c1 CURSOR FOR


SELECT s.name,s.tbname,s.tbcreator
FROM dmart_mkt1.MYINDEXES m
 RIGHT OUTER JOIN sysibm.sysindexes s
ON upper(m.IndexName) = upper(s.name)
AND upper(m.TableName) = upper(s.tbname)
and upper(m.Schema) =upper(s.tbcreator)
WHERE  upper(s.tbname) = UPPER ('EIW_CUSTOMER')
AND upper(s.tbcreator) =upper('DMART_MKT0')
and upper(s.name) NOT LIKE '%UN%';

OPEN c1;

FETCH c1 INTO iname, tname, cmd;
WHILE (SQLCODE = 0)
DO
SET drop_cmd = 'DROP INDEX'||cmd||'.' || iname;
EXECUTE IMMEDIATE drop_cmd;
commit;
FETCH c1 INTO iname, tname, cmd;
END WHILE;
END

Open in new window

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.