Re: Foreign key causing Error:55019 Message:SQLSTATE=55019 SQLCODE=-7008

On 23-Oct-2015 11:47 -0500, Justin Dearing wrote:

I would think a simple foreign key would be simple. It's not apparently.

tl;dr: If I add a foreign key to a child table, I can insert into
the parent table, but not update it (I'm not updating the foreign key
column)

The long version

I have two tables ABBAPF and ABBACPF. They don't have journals
created according to the output of the create table statement. In the
later I have a foreign key setup like this:

AD_ID FOR COLUMN ABADROWID BIGINT NOT NULL
CONSTRAINT WEBLIB.FK_ABBACPF_ABBAPF
REFERENCES WEBLIB.ABBAPF(ROWID)

I can insert into the parent table with no problem. However, this
update fails

UPDATE WEBLIB.ABBAPF SET
XML_COL = XMLPARSE(DOCUMENT '<xmlRoot/>')
, ABMSGSTAMP = CURRENT TIMESTAMP
, STATUS = 0
, MESSAGE = 'Success'
WHERE ROWID = 1 WITH NC

[SQL7008] ABBAPF in WEBLIB not valid for operation. [SQL State=55019, DB
Errorcode=-7008]

Note I tried adding the WITH NC after the fact. removing it doesn't
help.

Dropping the child table makes the update work. therefore I'm pretty
sure it's not journaling like this article says

[http://www.ibm.com/support/docview.wss?uid=swg21380662]

While the error msg SQL7008 indeed can indicate that journaling is not active and thus isolation other than NC is unsupported, the Referential Integrity (RI) requires journaling *irrespective* of Commitment Control (CMTCTL or COMMIT or ISOLATION) level being used; i.e. the referenced article is not really applicable, specifically, but generally is correct at least with regard to the conclusion drawn, "it's not journaling", whereby the origin of the problem is that the tables are not being journaled.

The command to journal the PFs is the Start Journal Physical File (STRJRNPF). After both the parent TABLE and child TABLE are journaled [IIRC in the past they had to be journaled to the same Journal (*JRN) object, but that should no longer be the case since many releases ago], then the UPDATE should be able to occur without that error being issued to diagnose the restriction. FWiW the details of the second level message text of the SQL7008 probably even cover that scenario?; the joblog details for the failure were not included in the above quoted text.