Question & Answer
Question
A SELECT statement from unqualified object (Table/View) might result in an SQL0204N error after HADR takeover if Primary and Standby servers are in different timezones.
Cause
The DB2® product uses conservative binding semantics to ensure consistent object resolution. This rule prohibits an object created after the binding of currently executed SQL statement from being seen/resolved to. For example, your database might have the primary server resides in a timezone (JST) and the standby server resides in a different timezone (UTC). If you create a the table TEST1 on the primary server and perform a HADR takeover using a duration of time less than that of the time difference between that of the primary and standby servers, then when you attempt to SELECT from table TEST1 you will receive the SQL0204N error because the bind time of the executed query SELECT * FROM TEST1 is earlier than the time the table was created.
The following steps might cause this problem to occur:
1. On the primary server:
$ db2 "create table test1(col1 int)"
DB20000I The SQL command completed successfully.
$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST1 DB2INST1 T 2013-11-28-12.04.34.221299
1 record(s) selected.
$ db2 "insert into test1 values(1)"
DB20000I The SQL command completed successfully.
$ db2 "insert into test1 values(2)"
DB20000I The SQL command completed successfully.
$ db2 "insert into test1 values(3)"
DB20000I The SQL command completed successfully.
$ db2 "select * from test1"
COL1
-----------
1
2
3
3 record(s) selected.
2. Takeover, the standby server takes over the primary server.
3. Run the following on the duplicated primary server:
$ db2 list tables
Table/View Schema Type Creation time
------------------------------- --------------- ----- --------------------------
TEST1 DB2INST1 T 2013-11-28-12.04.34.221299
1 record(s) selected.
Please run this on standby from your example so it shows correct TS.
$ db2 "values(current_timestamp)"
1
--------------------------
2014-11-28-03.14.34.221299
1 record(s) selected.
$ db2 "select * from DB2INST1.TEST1"
COL1
-----------
1
2
3
3 record(s) selected.
$ db2 "select * from TEST1"
SQL0204N "DB2INST1.TEST1" is an undefined name. SQLSTATE=42704
Answer
There are two work-arounds to this limitation:
1. Ensure that primary and standby are set to the same timezones.
2. When running the SELECT from the objects, qualify the objects with schema name.
For example:
SELECT * FROM DB2INST1.TEST1
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21661900