I have a stored procedure to drop the join indexes when databasename and indexname are passed as parameters.
REPLACE PROCEDURE db1.DROP_JI( IN databasename VARCHAR(130), IN jiname VARCHAR(130))
DECLARE db_name varchar(130);
DECLARE ji_name varchar(130);
SET db_name = TRIM(databasename);
SET ji_name = TRIM(jiname);
IF EXISTS(SELECT 1 FROM dbc.tablesize WHERE databasename = db_name AND tablename = ji_name) THEN
CALL DBC.SysExecSQL('DROP JOIN INDEX ' || db_name ||'.'|| ji_name);
Showing as 0 rows processed.But the index is not getting dropped.I have checked the privileges and modes.everything is fine.What might be the reason.
help procedure DB1.DROP_JI attributes
Transaction Semantics Print Mode Platform Character Set Default Character DataType Collation SPL Text Version Number Default Database Warning Option
1 ANSI N LINUX ASCII LATIN ASCII Y 08 DB Y
The SP is running in ANSI mode and there's no COMMIT within the SP.
Do you COMMIT after the CALL?
And why don't you check in dbc.TablesV for the JI name and TableKind = 'I' instead of using dbc.TableSize?
Yes.It worked.Thanks Dieter.But one doubt is Why it does not work for dbc.tablesize?
It's working using dbc.TableSize, but it's not the best way to get that info. TableSize returns multiple rows per table while it's only a single row in TablesV