CODEWITHSUNDEEP
oracle-databasedb2
Ayusman
Ayusman

Reputation: 8719

Unique indexes in DB2

I am migrating some SQL queries from Oracle to DB2 UDB 8.

Here is the SQL query:

create unique index ACT_UNIQ_RU_BUS_KEY on ACT_RU_EXECUTION
(case when BUSINESS_KEY_ is null then null else PROC_DEF_ID_ end,
case when BUSINESS_KEY_ is null then null else BUSINESS_KEY_ end);

Does anybody know what could be a equivalent SQL in DB2 UDB 8.x?

FYI: This is an SQL query I am creating for Activiti BPM tool.

Upvotes: 1

Views: 590

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753525

That seems to be a complicated way to build an index, and the third line looks remarkably as if it is equivalent to writing just BUSINESS_KEY_. Assuming that PROC_DEF_ID_ and BUSINESS_KEY_ are column names, then the index seems to be on those two columns in that sequence, with the twist that if BUSINESS_KEY_ is null, then the first column in the index is also treated as null.

There isn't a way to do that in DB2, as far as I know - at least, not in the 8.x versions. The DB2 LUW 9.7 manual page for CREATE INDEX does not mention anything equivalent.

Upvotes: 1

Related Questions