CODEWITHSUNDEEP
sqldb2
Bruce Li
Bruce Li

Reputation: 515

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

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.

Upvotes: 0

Views: 938

Answers (1)

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11052

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.

Upvotes: 4

Related Questions