Ultrak The DBA wrote:
>
UDB Version 8.2 FP7 (r"DB2 v8.1.1.128", "s061108", "U810098", and
FixPak
"14".) 64 bit running on AIX 5.3.
We were getting the SQL0668 error below so we put the status sql into
the script to see the table state right before the cursor/load ran.
The table did not seem to be in a bad state. We switched this cursor/
load back to a regular export/load and the issue went away. We are
doing several cursor loads on some 52Million row tables after this
with no issues.
Does anyone have a clue as to why this was occurring?
select tabname,status,access_mode from syscat.tables where status !=
'N'
TABNAME
STATUS ACCESS_MODE
-----------------------------------------------------------------------------------------------------------------------------
--- ------ -----------
BNKACCTPRGVW
X F
BNKCUSTRECPRGVW
X F
2 record(s) selected.
values current timestamp
1
--------------------------
2007-10-16-19.29.51.268018
1 record(s) selected.
declare corprodinst cursor for select ((select
coalesce(max(CORProdInstanceKy), 0) from CORProdInstance) +
t.CORProdInstanceK
y), CORProductKy, productCode, updateDttm, updateUser, updateSystem,
versionStamp, COROrgKyFI from tmp_CORProdInstance t
DB20000I The SQL command completed successfully.
values current timestamp
1
--------------------------
2007-10-16-19.29.51.271669
1 record(s) selected.
load from corprodinst of cursor INSERT INTO CORProdInstance
(CORProdInstanceKy, CORProductKy, productHeldId, updateDttm, upda
teUser, updateSystem, versionStamp, COROrgKyFI)
SQL3501W The table space(s) in which the table resides will not be
placed in
backup pending state since forward recovery is disabled for the
database.
SQL0668N Operation not allowed for reason code "1" on table
"SDB3036.CORPRODINSTANCE". SQLSTATE=57016
I guess you run the LOAD twice without the necessary SET INTEGRITY statement
in between. The explanation for SQL0668 tells you what's wrong and how to
fix this:
$ db2 "? sql668"
SQL0668N Operation not allowed for reason code
"<reason-code>" on table "<table-name>".
Explanation:
Access to table "<table-name>" is restricted. The cause is based
on the following reason codes "<reason-code>":
1 The table is in the Set Integrity Pending No Access state.
The integrity of the table is not enforced and the content of the
table may be invalid. An operation on a parent table or an
underlying table that is not in the Set Integrity Pending No
Access state may also receive this error if a dependent table is
in the Set Integrity Pending No Access state.
[...]
User Response:
1 Execute the SET INTEGRITY statement with the IMMEDIATE CHECKED
option on table "<table-name>" to bring the table out of the
Set Integrity Pending No Access state. For a user
maintained materialized query table, execute the statement
with the IMMEDIATE UNCHECKED option instead of the
IMMEDIATE CHECKED option.
[...]
--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany
