create db2 non clustered index

Question

Can you please tell me how to create a nonclustered index in DB2 ?I am not able to find any commands for that. I want to create one index on three columns. I tried:

CREATE INDEX IX_ACCOUNTNOTES_DATE_TYPE 
ON CPMTD.ACCOUNT_NS (ACCOUNT_ID,CREATED_DATE,NOTE_TYPE);"

Its giving me error as:

   DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;TED_DATE,NOTE_TYPE );END-OF-STATEMENT,       DRIVER=3.50.152   

    Message: An unexpected token "" was found following "".  Expected tokens may include:  "TED_DATE,NOTE_TYPE )".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.50.152 

ALso, create nonclustered index command is not working for db2.

any help will be greatly appreciated.

Thank you.

Answer 1

The normal CREATE INDEX (DB2 for z/OS) or CREATE INDEX (DB2 for LUW) command should be working for you,

CREATE INDEX IX_ACCOUNTNOTES_DATE_TYPE 
ON CPMTD.ACCOUNT_NS (
     ACCOUNT_ID
    ,CREATED_DATE
    ,NOTE_TYPE
)

If you copied and pasted your command exactly, then the extra " on the end there may have been messing things up.

Additionally, DB2 creates all indexes as non-clustered. The exceptions are that the first index defined will be the clustering index, unless you define a new index with the CLUSTER option:

CREATE INDEX IX_ACCOUNTNOTES_DATE_TYPE 
ON CPMTD.ACCOUNT_NS (
     ACCOUNT_ID
    ,CREATED_DATE
    ,NOTE_TYPE
)
CLUSTER