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

On 23-Oct-2015 12:29 -0500, CRPence wrote:

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.

I believe the RC3 of the msg SQL7008 covers the situation for the [defaulted] NO ACTION clause on the FOREIGN KEY specification; and presumably only the parent file will _need_ to be journaled [per the doc snippet included below], though best to journal both the parent and child tables anyhow:

[http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/sqlp/rbafyjourg.htm]
_Journaling_
"The DB2® for i journal support provides an audit trail and forward and backward recovery.

Forward recovery can be used to take an older version of a table and apply the changes logged on the journal to the table. Backward recovery can be used to remove changes logged on the journal from the table.

When an SQL schema is created, a journal and journal receiver are created in the schema. When SQL creates the journal and journal receiver, they are only created on a user auxiliary storage pool (ASP) if the ASP clause is specified on the CREATE SCHEMA statement. However, because placing journal receivers on their own ASPs can improve performance, the person managing the journal might want to create all future journal receivers on a separate ASP.

When a table is created into the schema, it is automatically journaled to the journal that DB2 for i created in the schema (QSQJRN). A table created in a library also has journaling started if a journal named QSQJRN exists in that library. After this point, it is your responsibility to use the journal functions to manage the journal, the journal receivers, and the journaling of tables to the journal. For example, if a table is moved into a schema, no automatic change to the journaling status occurs. If a table is restored, the normal journal rules apply. That is, if the table was journaled at save time, it is journaled to the same journal at restore time. If the table was not journaled at save time, it is not journaled at restore time.

The journal created in the SQL schema is normally the journal used for logging all changes to SQL tables. You can, however, use the system journal functions to journal SQL tables to a different journal.

A user can stop journaling on any table using the journal functions, but doing so prevents an application from running under commitment control. If journaling is stopped on a parent table of a referential constraint with a delete rule of NO ACTION, CASCADE, SET NULL, or SET DEFAULT, all update and delete operations will be prevented. Otherwise, an application is still able to function if you have specified COMMIT(*NONE); however, this does not provide the same level of integrity that journaling and commitment control provide.

Parent topic: _Data integrity_ [http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/sqlp/rbafydataintex.htm]

Related concepts:
_Journal management_ [http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzaki/rzakikickoff.htm]

Related reference:
_Updating tables with referential constraints_ [http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/sqlp/rbafyrfupdating.htm]";