sam2929
asked on
stored procedure with multiple functionality for dropping and creating indexes
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.
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.
ASKER
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
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
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
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
ASKER
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
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
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
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;
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...
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...
ASKER
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.
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.
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
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
ASKER
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
("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
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.
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
("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
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.
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
("CUST_CITY" ASC)
PCTFREE 0 ALLOW REVERSE SCANS';
EXECUTE IMMEDIATE statement;
END IF;
END@
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
("CUST_CITY" ASC)
PCTFREE 0 ALLOW REVERSE SCANS';
EXECUTE IMMEDIATE statement;
END IF;
END@
ASKER
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
("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
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
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
("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
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
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
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
ASKER
Hi Kent,
Can i run procedure through command editor instead of command line processor?
Thanks,
Sam
Can i run procedure through command editor instead of command line processor?
Thanks,
Sam
ASKER
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
("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;
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
("CUST_CITY" ASC)
PCTFREE 0 ALLOW REVERSE SCANS';
EXECUTE IMMEDIATE statement;
END IF;
END@
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
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
ASKER
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
("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
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> ".
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
("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
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> ".
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
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
ASKER
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
("CUST_CITY" ASC)
PCTFREE 0 ALLOW REVERSE SCANS';
EXECUTE IMMEDIATE statement;
END IF;
END@
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
("CUST_CITY" ASC)
PCTFREE 0 ALLOW REVERSE SCANS';
EXECUTE IMMEDIATE statement;
END IF;
END@
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
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@
ASKER
Hi Kent,
Getting error below
call TSWGMKT.DMART_MKT1.REBUILD
The name "REBUILDINDEXES" has the wrong number of qualifiers.. SQLCODE=-108, SQLSTATE=42601, DRIVER=3.58.81
Getting error below
call TSWGMKT.DMART_MKT1.REBUILD
The name "REBUILDINDEXES" has the wrong number of qualifiers.. SQLCODE=-108, SQLSTATE=42601, DRIVER=3.58.81
I don't know your topography, but one of these may work:
call TSWGMKT.REBUILDINDEXES
call DMART_MKT1.REBUILDINDEXES
call TSWGMKT.REBUILDINDEXES
call DMART_MKT1.REBUILDINDEXES
ASKER
Hi Kent,
This ran but no index is created.
--------------------------
call DMART_MKT1.REBUILDINDEXES;
--------------------------
call DMART_MKT1.REBUILDINDEXES
Return Status = 0
This ran but no index is created.
--------------------------
call DMART_MKT1.REBUILDINDEXES;
--------------------------
call DMART_MKT1.REBUILDINDEXES
Return Status = 0
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
("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
("CUST_CITY" ASC)
PCTFREE 0 ALLOW REVERSE SCANS;
My guess is that we have a simple name mismatch.
Kent
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
("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
("CUST_CITY" ASC)
PCTFREE 0 ALLOW REVERSE SCANS;
My guess is that we have a simple name mismatch.
Kent
ASKER
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?
We have ic<>0 shuld this not be ic=0 then?
Yep. :)
ASKER
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
("CUST_CITY" ASC)
PCTFREE 0 ALLOW REVERSE SCANS';
EXECUTE IMMEDIATE statement;
END IF;
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
("CUST_CITY" ASC)
PCTFREE 0 ALLOW REVERSE SCANS';
EXECUTE IMMEDIATE statement;
END IF;
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
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
ASKER
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
("CUST_CITY" ASC)
PCTFREE 0 ALLOW REVERSE SCANS';
EXECUTE IMMEDIATE statement;
END IF;
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
("CUST_CITY" ASC)
PCTFREE 0 ALLOW REVERSE SCANS';
EXECUTE IMMEDIATE statement;
END IF;
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;
ASKER
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"
CALL RebuildIndexes("DMART_MKT0
CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER
CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER
PCTFREE 0 ALLOW REVERSE SCANS';
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"
CALL RebuildIndexes("DMART_MKT0
CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER
CREATE INDEX "DMART_MKT0"."EIW_CUSTOMER
PCTFREE 0 ALLOW REVERSE SCANS';
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
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
ASKER
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
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
End the previous line (16) with a semi-colon.
ASKER
still two errors
at
WHILE (SQLCODE = 0) appers to be misplaced
EXECUTE IMMEDIAT unexpected text
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
Hi Sam,
sqlcode must be declared. The procedure below compiles just fine.
Kent
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;
ENDASKER
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.
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.
Hi Sam,
What system are you running? It works fine in UDB/LUW 9.7
Kent
What system are you running? It works fine in UDB/LUW 9.7
Kent
ASKER
Database server = DB2/AIX64 9.5.4
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.....
ASKER
Hi Kent,
Can you please try on linux.
Thanks,
Sam
Can you please try on linux.
Thanks,
Sam
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
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
ASKER
Hi Kent,
Added code and screen shot of error.
Thanks
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
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
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
ASKER
After comment on EXECUTE IMMEDIATE statement in this procedure deplot went good.
Deploy DMART_MKT1.REBUILDINDEXES1
stored procedure - Create REBUILDINDEXES1 completed.
Deploy DMART_MKT1.REBUILDINDEXES1
stored procedure - Create REBUILDINDEXES1 completed.
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;
ENDASKER
Hi Kent,
This works, Now what will be my call statment.
call DMART_MKT1.REBUILDINDEXES(
Thanks
This works, Now what will be my call statment.
call DMART_MKT1.REBUILDINDEXES(
Thanks
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
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
ASKER
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(
Added (IN tname varchar(130))
I want to modify this procedure so that we can call just one table at one time lets say like
call DMART_MKT1.REBUILDINDEXES(
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
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
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
ASKER
Getting error below should the vairable be Tablename instead of tname
CALL DMART_MKT1.SPREBUILDINDEXE
SQL0206N "DMART_MKT0.EIW_CUSTOMER" is not valid in the context where it is
used. SQLSTATE=42703
SQL0206N "DMART_MKT0.EIW_CUSTOMER
CALL DMART_MKT1.SPREBUILDINDEXE
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
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.
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.
ASKER
kool this works
CALL DMART_MKT1.SPREBUILDINDEXE
One thing more i want to do commit after every index build will this procedure do it or we need addition steps?
Thanks,
Sam
CALL DMART_MKT1.SPREBUILDINDEXE
One thing more i want to do commit after every index build will this procedure do it or we need addition steps?
Thanks,
Sam
Hi Sam,
Don't worry about committing after building the index. You haven't changed user data so the commit isn't necessary.
Kent
Don't worry about committing after building the index. You haven't changed user data so the commit isn't necessary.
Kent
ASKER
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
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
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
Add a 'WITH HOLD' clause or the COMMIT will close the cursor and you'll only build 1 index per call.
Kent
ASKER
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;
Should adding commit after EXECUTE IMMEDIATE will do it?
SET create_cmd = 'CREATE INDEX ' || iname || ' ON ' || tname || ' ' || cmd ;
EXECUTE IMMEDIATE create_cmd;
COMMIT;
That won't hurt. As long as you add the 'WITH HOLD' clause to the DECLARE CURSOR.
Kent
ASKER
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
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
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
ASKER
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';
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';
ASKER
please look atacchment of screenshot from join above
Screenshot-of-indexes.doc
Screenshot-of-indexes.doc
ASKER
no suggestion :(
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
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
ASKER
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:
--------------------------
CALL dmart_mkt1.RebuildIndexes(
--------------------------
CALL dmart_mkt1.RebuildIndexes(
SQL0205N Column or attribute "CUST_CITY" is not defined in
"DMART_MKT2.DMT_OPP_DETAIL
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:
--------------------------
CALL dmart_mkt1.RebuildIndexes(
--------------------------
CALL dmart_mkt1.RebuildIndexes(
SQL0205N Column or attribute "CUST_CITY" is not defined in
"DMART_MKT2.DMT_OPP_DETAIL
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
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_DETAIL
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
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_DETAIL
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);ASKER
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.
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.
weird.
Let's copy it to a declared variable before applying the UPPER function.
Try this one.
Kent
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
ASKER
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(
Return Status = 0
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(
Return Status = 0
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(
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
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(
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
ASKER
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
ASKER
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
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
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
Open in new window