Technote

(FAQ)
WebSphere Commerce Analyzer throws 'Operation not allowed...' error due to a database table being in Load Pending state.
Problem
The following error shows in the log files during loading of data into the WebSphere Commerce Analyzer tables.

[IBM][CLI Driver][DB2/NT] SQL0668N Operation not allowed for reason code "3" on table "WCA.FACT_EVENT". SQLSTATE=57016
Cause
From a DB2 command prompt, run the following command to see a description of the problem and solution of the load pending state:

 db2 ? SQL0668

The table is in the load pending state. A previous LOAD attempt on this table resulted in failure. No access to the table is allowed until the LOAD operation is restarted or terminated.

Restart or terminate the previously failed LOAD operation on this table by issuing LOAD with the RESTART or TERMINATE option respectively.

Solution
To terminate the load on the table:
The table that is causing the error is determined based on the log files (for example, pop_fact_event.log)

From the error in the log files, the following load command failed:

db2 "load  from C:\PROGRA~1\IBM\WCA\tmp\wca_load.ixf of ixf  messages C:\PROGRA~1\IBM\WCA\tmp\wca_loadimp.msg insert into wca.fact_event (IMPRS_ID          , IMPRS_SUB_ID , MEMBER_ID       , HOUR_ID           , EVENT_TYPE_ID, STORE_ID          , PER_ID          , ENTRY_TIME , CATEGORY_ID, PRODUCT_ID    , CAMPREL_ID     , REFURL            , PREVURL          , REDIRURL        , NUM_IMPRS    , NUM_CLICKS , IP_ADDRESS   , RUN_NO        , LAST_UPDATED    , LAST_UPDATED_ID, report_currency, store_currency )  for exception wcaexcpt.fact_event" 

According to the command syntax (http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0008305.htm) , replace the word insert with terminate for this command to terminate the load.

db2 "load  from C:\PROGRA~1\IBM\WCA\tmp\wca_load.ixf of ixf  messages C:\PROGRA~1\IBM\WCA\tmp\wca_loadimp.msg terminate into wca.fact_event (IMPRS_ID          , IMPRS_SUB_ID , MEMBER_ID       , HOUR_ID           , EVENT_TYPE_ID, STORE_ID          , PER_ID          , ENTRY_TIME , CATEGORY_ID, PRODUCT_ID    , CAMPREL_ID     , REFURL            , PREVURL          , REDIRURL        , NUM_IMPRS    , NUM_CLICKS , IP_ADDRESS   , RUN_NO        , LAST_UPDATED    , LAST_UPDATED_ID, report_currency, store_currency )  for exception wcaexcpt.fact_event" 


To find which tables are in load pending state:

db2 select tabname, substr(const_checked,1,1) as FK_CHECKED, substr(const_checked,2,1) as CC_CHECKED from syscat.tables where status='C'   
The results of this command will show the tables that require a status update.

To update their status to normal, use the set integrity command and create a dummy table for exceptions:

> db2 "create table FACT_EVENTEXP like FACT_EVENT" 

> db2 "set integrity for FACT_EVENT immediate checked for exception in FACT_EVENT use FACT_EVENTEXP" 

To clean up the table that may have been partially updated:

delete * from WCAEXCPT.FACT_EVENT 

Run the failed step again.

 

Document Information

Current web document: http://www.ibm.com/support/docview.wss?uid=swg21239763