Subject | Rebuild indices stored procedure |
---|---|
Author | k_smith_000 |
Post date | 2005-10-25T20:45:22Z |
Hello,
At page 263 of Operation Guide I've found script to generate
statements that rebuild indices:
SELECT 'ALTER INDEX ' || RDB$INDEX_NAME || ' ACTIVE;'
FROM RDB$INDICES
WHERE RDB$SYSTEM_FLAG = 0 OR RDB$SYSTEM_FLAG IS NULL;
I've tried to create stored procedure
CREATE PROCEDURE SERVICE_INDEX_REBUILD
AS
DECLARE VARIABLE S VARCHAR(200);
DECLARE VARIABLE S2 VARCHAR(200);
BEGIN
FOR select RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$SYSTEM_FLAG = 0
OR RDB$SYSTEM_FLAG IS NULL INTO :S DO
BEGIN
S = 'ALTER INDEX ' || s || 'inactive;';
EXECUTE STATEMENT :S;
S2 = 'ALTER INDEX ' || s || 'active;';
EXECUTE STATEMENT :S2;
END
SUSPEND;
END
Unfortunatelly when I try to execute procedure I got following error:
EXECUTE PROCEDURE SERVICE_INDEX_REBUILD;
Statement failed, SQLCODE = -607
unsuccessful metadata update
-MODIFY RDB$INDICESS failed
-action cancelled by trigger (2) to preserve data integrity
-Cannot deactivate index used by an Integrity Constraint
Best regards,
Kevin Smith
At page 263 of Operation Guide I've found script to generate
statements that rebuild indices:
SELECT 'ALTER INDEX ' || RDB$INDEX_NAME || ' ACTIVE;'
FROM RDB$INDICES
WHERE RDB$SYSTEM_FLAG = 0 OR RDB$SYSTEM_FLAG IS NULL;
I've tried to create stored procedure
CREATE PROCEDURE SERVICE_INDEX_REBUILD
AS
DECLARE VARIABLE S VARCHAR(200);
DECLARE VARIABLE S2 VARCHAR(200);
BEGIN
FOR select RDB$INDEX_NAME FROM RDB$INDICES WHERE RDB$SYSTEM_FLAG = 0
OR RDB$SYSTEM_FLAG IS NULL INTO :S DO
BEGIN
S = 'ALTER INDEX ' || s || 'inactive;';
EXECUTE STATEMENT :S;
S2 = 'ALTER INDEX ' || s || 'active;';
EXECUTE STATEMENT :S2;
END
SUSPEND;
END
Unfortunatelly when I try to execute procedure I got following error:
EXECUTE PROCEDURE SERVICE_INDEX_REBUILD;
Statement failed, SQLCODE = -607
unsuccessful metadata update
-MODIFY RDB$INDICESS failed
-action cancelled by trigger (2) to preserve data integrity
-Cannot deactivate index used by an Integrity Constraint
Best regards,
Kevin Smith