Support Article
DB2 error after fail over database swap
SA-46263
Summary
After fail over test, upon reverting back to primary database, an error is observed.
Error Messages
LEVEL: Warning
PID : 30015688 TID : 44555 PROC : db2sysc 0
INSTANCE: your_instance NODE : 000 DB : your_node
APPHDL : 0-655 APPID: your_ip.34778.171015045554
AUTHID : PRPC HOSTNAME: your_host
EDUID : 44555 EDUNAME: db2agent (your_instance) 0
FUNCTION: DB2 UDB, data management, sqldSetupSQLCA, probe:3
MESSAGE : ADM5570W Access was attempted on an unavailable object with id "4"
in tablespace "2" for table "DATA.PR_LOG_REPORTSTATS". If the object
is a table it will have to be dropped. If the object is a partition
it will have to be detached. If the object is a non-partitioned index
the index will have to be dropped.
Steps to Reproduce
Not Applicable
Root Cause
A defect or configuration issue in the operating environment:
The issue stems from the setting of this command property during purging and rebuilding of tables within the environment:
'activate not logged initially'.
Resolution
Perform the following local-change steps:
Make this local change to DATA.sppr_purge_table_no_logging
Make comment in A: as below
A: BEGIN
DECLARE NO_PRIVS CONDITION FOR SQLSTATE '42501';
DECLARE CONTINUE HANDLER FOR NO_PRIVS
BEGIN
-- do nothing
END;
-- set sqlstmtAlterTableForNoLog = 'ALTER TABLE ' || tablename || ' activate not logged initially';
-- prepare s1 from sqlstmtAlterTableForNoLog;
-- execute s1;
END A;
DECLARE NO_PRIVS CONDITION FOR SQLSTATE '42501';
DECLARE CONTINUE HANDLER FOR NO_PRIVS
BEGIN
-- do nothing
END;
-- set sqlstmtAlterTableForNoLog = 'ALTER TABLE ' || tablename || ' activate not logged initially';
-- prepare s1 from sqlstmtAlterTableForNoLog;
-- execute s1;
END A;
Make this local change to DATA.sppr_defragment_table
commit;
-- SET stmt = 'ALTER TABLE '|| origTableName || ' ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE';--'TRUNCATE TABLE ' || origTableName || ' IMMEDIATE';
-- PREPARE S1 FROM stmt;
-- EXECUTE S1;
SET stmt = 'TRUNCATE TABLE ' || origTableName || ' IMMEDIATE';
PREPARE S1 FROM stmt;
EXECUTE S1;
-- SET stmt = 'ALTER TABLE '|| origTableName || ' ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE';--'TRUNCATE TABLE ' || origTableName || ' IMMEDIATE';
-- PREPARE S1 FROM stmt;
-- EXECUTE S1;
SET stmt = 'TRUNCATE TABLE ' || origTableName || ' IMMEDIATE';
PREPARE S1 FROM stmt;
EXECUTE S1;
This will have an impact on the database log usage so the DBA may need to monitor the log usage and take measures to increase log space values accordingly.
Consider running with "BLOCKEDNONLOGGED set to YES" as a database property to be sure data replication is working properly.
Published March 26, 2018 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.