Why below SQL statement fails to run on DB2 for z/OS?

Question

Below SQL statement works fine on DB2 LUW 9.7 but fails to run on DB2 for z/OS 9.1, why and how to make it work?

CREATE TABLE HIERARCHY_TEST ( 
  HIER_URI VARCHAR(255) NOT NULL,
  PARENT_URI VARCHAR(255),
  TITLE VARCHAR(1000) NOT NULL,
  MIMETYPE VARCHAR(100),
  DESCRIPTION VARCHAR(1000),
  MODIFIED_BY VARCHAR(1000),    
  CONSTRAINT PK_SFIND_HIERARCHY PRIMARY KEY (HIER_URI ),
  CONSTRAINT FK_ND_HIERARCHY_1 FOREIGN KEY (PARENT_URI) REFERENCES HIERARCHY_TEST ( HIER_URI) 
)

The error message I got is "DEV.FIND_HIERARCHY_TEST IS AN UNDEFINED NAME. SQLCODE=-204, SQLSTATE=42704, DRIVER=3.53.71"

Thanks.

I'm not sure how DB2 works with self referencing foreign keys; have you tried defining the table without the foreign key and then using ALTER TABLE to add the constraint afterwards? — El Yobo
It's interesting that DEV.FIND_HIERARCHY_TEST isn't the name of an object in your script - is it definitely the above snippet that is provoking this response from the server? — Will A
Thanks for editing my post El. I just tried to define the PK and FK in a separate ALTER TABLE statement and it works. But still I don't get why the original statement not work on all DB2 databases. What's the difference between DB2 for z/OS and other DB2 databases? — Bruce Li
@Will A, DEV is the default schema of current user, so it's added to the message returned from server automatically. — Bruce Li
OK, great; I thought that might be the problem, as I've had similar problems in other DBMSs before. — El Yobo

Ian Bjorhovde Ian Bjorhovde · Accepted Answer · 2010-12-16T10:04:22

The reason this statement works in DB2 9.7 for Linux/UNIX/Windows because DB2 9.7 added a feature called Automatic Revalidation. This feature does not exist in DB2 9.1 for z/OS.

This feature allows you to create an object that depends on another object, even if the parent object does not yet exist. (i.e. you can create a view on a table that does not yet exist, or in your case you can define a foreign key on a table that does not yet exist).

The newly-created object will be invalid, and DB2 will automatically revalidate it the next time it's accessed. This behavior is controlled by the auto_reval database configuration parameter.

As El Yobo noted in the comments above, if you need to have the exact same statement(s) run in both environments you need to remove the foreign key constraint from the CREATE TABLE and execute it as a separate ALTER TABLE statement.

Why below SQL statement fails to run on DB2 for z/OS?

1 Answers