At 11:15 AM 26/10/2005 +1000, I wrote:

>There *is* another issue arising from this that concerns me. At some
>point, someone has caused the engine to stop flagging primary, foreign and
>unique key indexes as "system-defined". This is an undocumented change
>which it behoves me to follow up and document, if it is intentional; or to
>report as a bug if it is an unintentional side-effect of other internal
>changes to indexing in the Firebird era.

OK, I retract this conjecture. I've checked some ODS9 databases and, sure
enough, constraint indexes didn't previously get a non-null system flag, so
nothing has changed in this regard.

You're getting hits on constraint indexes as designed. The script as
written won't work with Firebird because Firebird (at some point) closed
the loophole that allowed constraint indexes to be disabled.

In your modified script (with the amended objective of creating an output
script for disabling all user-defined indexes) you'll need to alter your
search criteria to exclude constraint indexes. Possibly the most
straighforward way to do this will be to access the column RDB$INDEX_NAME
in the table RDB$RELATION_CONSTRAINTS. Here's how to use ISQL to get your
script (assuming you're on Windows):

Open a command shell.

Get to Firebird's bin directory:

C:\Documents and Settings\Administrator.DEV>CD \PROGRAM
FILES\FIREBIRD\FIREBIRD_1_5\BIN

Connect to the database (I have the employee database aliased as EMP1)

C:\Program Files\Firebird\Firebird_1_5\bin>ISQL EMP1
Database: EMP1
SQL> SET ECHO OFF;
SQL> OUTPUT L:\DATA\Rebuild_Indexes.SQL;
SQL> SELECT 'ALTER INDEX ' || i.RDB$INDEX_NAME || ' INACTIVE;' AS S
CON> FROM RDB$INDICES i
CON> WHERE
CON> (i.RDB$SYSTEM_FLAG = 0 OR i.RDB$SYSTEM_FLAG IS NULL)
CON> AND NOT EXISTS (
CON> SELECT 1 FROM RDB$RELATION_CONSTRAINTS rc
CON> WHERE i.RDB$INDEX_NAME = rc.RDB$INDEX_NAME) ;
CON> COMMIT;
SQL> SELECT 'ALTER INDEX ' || i.RDB$INDEX_NAME || ' ACTIVE;' AS S
CON> FROM RDB$INDICES i
CON> WHERE
CON> (i.RDB$SYSTEM_FLAG = 0 OR i.RDB$SYSTEM_FLAG IS NULL)
CON> AND NOT EXISTS (
CON> SELECT 1 FROM RDB$RELATION_CONSTRAINTS rc
CON> WHERE i.RDB$INDEX_NAME = rc.RDB$INDEX_NAME);
SQL> COMMIT;
SQL> OUTPUT; /* Closes the file and brings output back to shell */
SQL>

You'll need to open your output file in a text editor, comment out the
superfluous output and insert a couple of COMMIT statements:

/*
S
=====================================================
*/
ALTER INDEX MINSALX INACTIVE;
ALTER INDEX MAXSALX INACTIVE;
ALTER INDEX BUDGETX INACTIVE;
ALTER INDEX NAMEX INACTIVE;
ALTER INDEX PRODTYPEX INACTIVE;
ALTER INDEX UPDATERX INACTIVE;
ALTER INDEX CHANGEX INACTIVE;
ALTER INDEX CUSTNAMEX INACTIVE;
ALTER INDEX CUSTREGION INACTIVE;
ALTER INDEX NEEDX INACTIVE;
ALTER INDEX SALESTATX INACTIVE;
ALTER INDEX QTYX INACTIVE;

COMMIT;
/*
S
===================================================
*/
ALTER INDEX MINSALX ACTIVE;
ALTER INDEX MAXSALX ACTIVE;
ALTER INDEX BUDGETX ACTIVE;
ALTER INDEX NAMEX ACTIVE;
ALTER INDEX PRODTYPEX ACTIVE;
ALTER INDEX UPDATERX ACTIVE;
ALTER INDEX CHANGEX ACTIVE;
ALTER INDEX CUSTNAMEX ACTIVE;
ALTER INDEX CUSTREGION ACTIVE;
ALTER INDEX NEEDX ACTIVE;
ALTER INDEX SALESTATX ACTIVE;
ALTER INDEX QTYX ACTIVE;

COMMIT;

And make sure there is at least one carriage return after the last
statement! and save the changes.

Then, if you like, you can proceed to run the script:

SQL> INPUT L:\DATA\Rebuild_Indexes.SQL;

./heLen