iDatamining.org

I am looking for projects to work on
Please contact with me at yiyu.jia@BostonInfoPro.com!

Monday, October 25, 2010

failure on passing SQL statement as parameters into stored procedure and the way to walk around

Recently, I got code 3 error message when I tried to call a stored procedure on IBM i DB2 through System i Navigator. The purpose of this stored procedure is to perform a series queries that are passed in as the input parameter under a predefined schema.  Below is the way to call the stored procedure,
call myStoredProcedure('update dummyTbl set columnA = 1 where columnB = ''Jia''  ')

The error message says that

Message: [SQL7008] dummyTbl in schemaA not valid for 
operation. Cause . . . . . :   The reason code is 3.  
Reason codes are: 
1 -- dummyTbl has no members. 
2 -- dummyTbl has been saved with storage free. 
3 -- dummyTbl 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. 
The interesting thing is that I get error free when I run the same SQL query in System i Navigator. 

After doing research, I found that, on IBM i DB2, a table is journaled by default if it is created with SQL statement in i Navigator. On the other hand, the table is not journaled by default when it is created under green screen. And the walking around is adding the following statement in the stored procedure before executing the input string that is actually sql statement,
SET TRANSACTION ISOLATION LEVEL NO COMMIT

This could only be temporary walking around solution. To be serious, it is better to review tables setting about journaling and isolation level. However, for an existing complex database system, this walking around might be a good choice too.

No comments:

Post a Comment