Informix Error -23936: There are GK-indexes depending on a table in the statement.
Cause and resolution
There are GK-indexes depending on a table in the statement.
This statement cannot be executed because general-key indexes exist that depend on a table or tables involved in the statement. Drop the general-key index that depends on the table.
To determine which GK indexes need to be dropped to correct this error, run the following query on the system catalog table:
SELECT sysindexes.idxname, sysindexes.owner FROM systables, sysindexes, sysnewdepend WHERE systables.tabname = "<table_name>" AND systables.tabid = sysnewdepend.destid1 AND sysnewdepend.srcid2 = sysindexes.tabid AND sysnewdepend.srcid1 = sysindexes.idxname </table_name>
When a particular column of the GK index is involved (for example, in the case of a RENAME COLUMN statement), run the following query to determine which GK indexes need to be dropped:
SELECT sysindexes.idxname, sysindexes.owner FROM sysindexes, systables, syscolumns, sysnewdepend WHERE systables.tabname = "<table_name>" AND syscolumns.colname = "<column_name>" AND systables.tabid = sysnewdepend.destid1 AND syscolumns.colno = sysnewdepend.destid2 AND sysnewdepend.srcid2 = sysindexes.tabid AND sysnewdepend.srcid1 = sysindexes.idxname </column_name></table_name>
Oninit® Troubleshooting Guidance
Reasons / Common Causes
-23936 fires when a statement can't execute because general-key indexes depend on a table involved in it — per the official guidance.
- A general-key index depending on a table the statement affects, per the official guidance — the direct, only cause.
Solutions / Resolution
- Drop the dependent general-key index(es), per the official guidance. To find
which ones, query the system catalog:
For a specific column (e.g. forSELECT sysindexes.idxname, sysindexes.owner FROM systables, sysindexes, sysnewdepend WHERE systables.tabname = "<table_name>" AND systables.tabid = sysnewdepend.destid1 AND sysnewdepend.srcid2 = sysindexes.tabid AND sysnewdepend.srcid1 = sysindexes.idxnameRENAME COLUMN), add a join onsyscolumns.colname/sysnewdepend.destid2per the official guidance.
Examples
Finding dependent GK indexes
SELECT sysindexes.idxname, sysindexes.owner
FROM systables, sysindexes, sysnewdepend
WHERE systables.tabname = "orders"
AND systables.tabid = sysnewdepend.destid1
AND sysnewdepend.srcid2 = sysindexes.tabid
AND sysnewdepend.srcid1 = sysindexes.idxname;
Diagnostic Checks
- Run the catalog query against
sysnewdepend/sysindexes/systablesto identify the dependent GK index(es), then drop them.
Related Errors / Related Topics
No closely related error codes are cross-referenced for -23936 in this set yet.
A statement was blocked by a dependent general-key index — find and drop it via the
sysnewdepend catalog query.