icxwtab.odf is unable to create index ICX_TRANSACTIONS_U1

Sometime while patching, adpatch fails on script icxwtab.odf with the following errors:
As specified in Metalink Note 430673.1:
ERROR
The table is missing the index ICX_TRANSACTIONS_U1
or index ICX_TRANSACTIONS_U1 exists on another table.
Create it with the statement:

Start time for statement below is: Mon May 07 2007 14:23:44

CREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS
(TRANSACTION_ID) LOGGING STORAGE (INITIAL 4K NEXT 104K MINEXTENTS 1
MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE
10 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS TABLESPACE ICXX

Statement executed.
AD Worker error:
The index cannot be created as the table has duplicate keys.

Use the following SQL statement to identify the duplicate keys:

SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1

AD Worker error:
Unable to compare or correct tables or indexes or keys
because of the error above

SPECIFIC DATA
Ran the suggested query, and here is the output:

TRANSACTION_ID COUNT(*)
-------------------------- -------------
148341124             2
431640607             2
555224577             2
1202811809           2

Cause
These duplicate transactions are there because the concurrent program that deletes temporary session data (program that removes old entries in ICX_SESSIONS and ICX_TRANSACTIONS) is not executed on a regular basis. As a result, these tables grow in space and there is the possibility that the sequences cycle and restart, creating duplicate primary keys.

The following justifies how the issue is related to this specific customer:

SELECT TRANSACTION_ID, count(*)
FROM ICX.ICX_TRANSACTIONS
GROUP BY TRANSACTION_ID
HAVING count(*)>1

TRANSACTION_ID COUNT(*)
-------------------------- --------------
148341124                2
431640607                2
555224577                2
1202811809              2

This is explained in the following unpublished bug: Bug 5001287 PERFORMANCE PROBLEM WHEN APPROVING POS WITH ICX_TRANSACTIONS

Solution
To implement the solution, please execute the following steps:

1. Run the purge program:
     a. The name of the program is "Purge Inactive Sessions" located under the "Apps for the Web Manager" responsibility.
     b. The internal name is ICXDLTMP.
     c. Also you can find this SQL script under $ICX_TOP/sql (named ICXDLTMP.sql).

2. Rerun the failed worker (icxwtab.odf).

3. Migrate the solution as appropriate to other environments.

4. This program should be executed at least once a week to clean up ICX_TRANSACTIONS and ICX_SESSION tables, otherwise they will continue to grow.

Related Posts by Categories