Getting a -873 error when altering table and adding a FOREIGN KEY constraint between EBCDIC and UNICODE tables

Dean Parkinson

Getting a -873 error when altering table and adding a FOREIGN KEY constraint between EBCDIC and UNICODE tables

I have an EBCDIC table with a child table that is UNICODE.  When I try and add a FOREIGN KEY constraint for a TIMESTAMP field I get the error;

SQLCODE = -873, ERROR:  THE STATEMENT REFERENCED DATA ENCODED WITH DIFFERENT ENCODING SCHEMES OR CCSIDS IN AN INVALID CONTEXT

Here is the ALTER;

ALTER TABLE TABLE002

ADD
CONSTRAINT RTABLE002
FOREIGN KEY
(
DEPART_DTTM
)
REFERENCES TABLE001
(
DEPART_DTTM
) ON DELETE RESTRICT
ENFORCED;

Can I add something to the ALTER that would make this work?

Michael Hannan

RE: Getting a -873 error when altering table and adding a FOREIGN KEY constraint between EBCDIC and UNICODE tables
(in response to Dean Parkinson)

Dean,

 I am not in a position to test this. However firstly it is strange to have tables in a mixture of Unicode and EBCDIC.

These days, Unicode can be utilised on a column basis without making entire table Unicode (at least on DB2 zOS).

It strikes me as strange that there is a problem, since the column in question, is a Timestamp, which does not really have a CCSID encoding. It is neither EBCDIC nor UNICODE (which apply to CHAR columns). It is just a TIMESTAMP. Of course if a site stored a Timestamp in CHAR, then that is asking for problems. I have seem tables with dates and times stored in Decimal columns which is also an extremely bad idea.

Finally, Referential Integrity constraints are a nice to have feature, sometimes, but not essential. You might be able to live without it. You might also be able to code a BEFORE Insert type trigger, and Before Update of Prime Key type trigger, to check that related parent exists, if DB2 is determined not to allow the R.I. In a Trigger, use SIGNAL to indicate situations that violate the rules.

Triggers can allow Integrity checking a bit more advanced than R.I. at possibly higher performance cost.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd