0

After I run a query I get the following error:

OLE DB provider "IBMDASQL" for linked server "DB2400OLEDB" returned message "SQL7008: TABLE1 in STAGING not valid for operation.
Cause . . . . . :   **The reason code is 3.** 
 Reason codes are: 
1 -- TABLE1  has no members. 
2 -- TABLE1  has been saved with storage free. ***
3 -- TABLE1  not journaled, no authority to the journal, or the journal state is *STANDBY.  Files with an RI constraint action of CASCADE, SET NULL, or SET DEFAULT must be journaled to the same journal.***
4 and 5 -- TABLE1  is in or being created into production library but the user has debug mode UPDPROD(*NO). 
6 -- Schema being created, but user in debug mode with UPDPROD(*NO).
7 -- A based-on table used in creation of a view is not valid. Either the table is program described table or it is in a temporary schema. 
8 -- Based-on table resides in a different ASP than ASP of object being created. 
9 -- Index is currently held or is not valid. 
10 -- A constraint or trigger is being added to an invalid type of table, or the maximum number of triggers has been reached, or all nodes of the distributed table are not at the same release level. 
11 -- Distributed table is being created in schema QTEMP, or a view is being created over more than one distributed table.
12 -- Table could not be created in QTEMP, QSYS, QSYS2, or SYSIBM because it contains a column of type DATALINK having the FILE LINK CONTROL option.
13 -- The table contains a DATALINK column or a LOB column that conflicts with the data dictionary.
14 -- A DATALINK, LOB, or IDENTITY column cannot be added to a non SQL table.
15 -- Attempted to create or change an object using a commitment definition in a different ASP.
16 -- Sequence TABLE1  in STAGING was incorrectly modified with a CL command.
17 -- The table is not usable because it contains partial transactions. Recovery  . . . :
   Do one of the following based on the reason code:
   1 -- Add a member to TABLE1  (ADDPFM).
   2 -- Restore TABLE1  (RSTOBJ). 3 -- Start journaling on TABLE1 (STRJRNPF), get access to the journal, or change the ...
jwheron
  • 2,553
  • 2
  • 30
  • 40
Sunil Chavan
  • 524
  • 5
  • 15
  • 1
    Does http://stackoverflow.com/q/9040884/803367 answer the question? – Buck Calabro Jul 21 '13 at 11:32
  • Hi Buck, thanks but this is not solution i am looking for. I am using IBMDASQL provider. – Sunil Chavan Jul 22 '13 at 05:42
  • IBM's reference says that IBMDASQL does not support commitment control. http://www-01.ibm.com/support/docview.wss?uid=nas1d7cd7e4509cc894c86257280005c65dc It looks like you will need to use a different method or get the IBM system admin to start journaling the table (which is easy). – Buck Calabro Jul 22 '13 at 13:41
  • journaling is not good option according to as400 team because it degrades the performance badly. – Sunil Chavan Jul 28 '13 at 09:23
  • 1
    I cannot speak for every IBM i in existence, but I personally have not seen a performance problem since the late 1980s. I advise them to try it before they dismiss it out of hand. – Buck Calabro Jul 29 '13 at 13:53

1 Answers1

2

IBMDASQL does support commitment control; it's IBMDA400 that does not (according to the http://www-01.ibm.com/support/docview.wss?uid=nas8N1014514 link). If the table is not journaled, then the transactions must run with commitment control disabled. That's a bad practice. The table should be journaled. If it causes significant performance issues, it's almost certainly because the system is too small for its workload or it's configured poorly.

user2338816
  • 2,163
  • 11
  • 11