unixindexingdb2

db2 create index but hit SQLSTATE=42703


I have a table created successfully.

1 of the column name is code and another 1 is "deleted".

Thus, I plan to use this 2 field to create its index. I am doing something like follow:

CREATE INDEX SADM.IDX_SC_IDX1 on SADM.SC ("code" ASC, "DELETED") ALLOW REVERSE SCANS;

This is working fine in my local. However, I hit this error in UAT:

DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0205N  Column, attribute, or period "code" is not defined in
"SADM.SC".  SQLSTATE=42703

I double check the table and confirm the "code" column or "deleted" is exist and same with my local.

I believe something wrong is inside but I cant find the root cause.

Kindly advise.


Solution

  • As per my comment. You are using double-quotes around the column names the column case (uppercase, lowercase) must match between the table-definition and the index definition.