create db2 non clustered index

database indexing db2 non-clustered-index
Snow white picture Snow white · Feb 8, 2013 · Viewed 7.2k times · Source

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

bhamby picture bhamby · Feb 8, 2013

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

Related questions

How does database indexing work?

Given that indexing is so important as your data set increases in size, can someone explain how indexing works at a database-agnostic level? For information on queries to index a field, check out How do I index a database column.

Read More
How to use index in select statement?

Lets say in the employee table, I have created an index(idx_name) on the emp_name column of the table. Do I need to explicitly specify the index name in select clause or it will automatically used to speed …

Read More
Multiple Indexes vs Multi-Column Indexes

I've just been adding an Index to a table in SQL Server 2005 and it got me thinking. What is the difference between creating 1 index and defining multiple columns over having 1 index per column you want to index. Are there certain …

Read More