Stroed Procedure:Executing succesfully without errors while calling but the output is not being affected

Database
KVB
Enthusiast

Stroed Procedure:Executing succesfully without errors while calling but the output is not being affected

Hi,

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

BEGIN

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

         END IF;

END;

CALL db1.DROP_JI('DB1','INDEXNAME');

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

3 REPLIES
Junior Contributor

Re: Stroed Procedure:Executing succesfully without errors while calling but the output is not being affected

The SP is running in ANSI mode and there's no COMMIT within the SP. 

Do you COMMIT after the CALL?

CALL db1.DROP_JI('DB1','INDEXNAME');
COMMIT;

And why don't you check in dbc.TablesV for the JI name and TableKind = 'I' instead of using dbc.TableSize?

KVB
Enthusiast

Re: Stroed Procedure:Executing succesfully without errors while calling but the output is not being affected

Yes.It worked.Thanks Dieter.But one doubt is Why it does not work for dbc.tablesize?

Junior Contributor

Re: Stroed Procedure:Executing succesfully without errors while calling but the output is not being affected

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