Hi,

I am trying to set my DB for Rollforward Recovery, and have not been able to do so. My goal is to set up the database for Roll forward recovery, and test it with Load Copy Yes.

This is what I did:
update db cfg for <mydb> using logretain recovery
backup db <mydb> to <db_backup_dir>
connect to <mydb>
load from <load_file> of del insert into <my table> copy yes to <copy dir>

I get:
"SQL1116N A connection to or activation of database <mydb> cannot be made because of BACKUP PENDING. SQLSTATE=57019"
even though just just did a backup.

I'be been going a bit insane trying to make it work;
Please save me from jumping out the window !

Thanks !

- MDB


Db2 version:
Product name: "DB2 Enterprise Server Edition"
Product identifier: "db2ese"
Version information: "9.5"
DB21085I Instance "db2inst2" uses "64" bits and DB2 code release "SQL09051" with level identifier "03020107".
Informational tokens are "DB2 v9.5.0.1", "s080328", "U814639", and Fix Pack "1".

This should work. Please post the output from:

db2 get db cfg for <db name> | grep -i pending
db2 list history backup all for db <db name>

DBGirl, thanks for the reply.
Here's ths information:


db2 get db cfg for <db name> | grep -i pending

Backup pending = NO
Rollforward pending = NO
Restore pending = NO


================================================== ========
db2 list history backup all for db <db name>:

List History File for PLAY_DB

Number of matching file entries = 10


Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20091205181639000 F S0000000.LOG
----------------------------------------------------------------------------
Contains 19 tablespace(s):

00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 TS_CF
00005 TS_CF_IX
00006 TS_JB7_A
00007 TS_JB7_IX_A
00008 TS_CF2
00009 TS_CF2_IX
00010 TS_CF2_LG
00011 TS_MISC
00012 TS_MISC_IX
00013 TS_REP
00014 TS_REP_IX
00015 TS_QSTN2
00016 TS_QSTN2_IX
00017 TS_QSTN2_LG
00018 TS_ADMIN
00019 TS_ADMIN_IX
----------------------------------------------------------------------------
Comment: DB2 BACKUP PLAY_DB OFFLINE
Start Time: 20091205181639
End Time: 20091205181640
Status: A
----------------------------------------------------------------------------
EID: 50 Location:

SQLCA Information

sqlcaid : SQLCA sqlcabc: 136 sqlcode: -2061 sqlerrml: 21

sqlerrmc: /tmp/PLAY_DB/db2_backup
sqlerrp : sqlubMWR
sqlerrd : (1) 0 (2) 0 (3) 0
(4) 0 (5) 0 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20091205181737001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 19 tablespace(s):

00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 TS_CF
00005 TS_CF_IX
00006 TS_JB7_A
00007 TS_JB7_IX_A
00008 TS_CF2
00009 TS_CF2_IX
00010 TS_CF2_LG
00011 TS_MISC
00012 TS_MISC_IX
00013 TS_REP
00014 TS_REP_IX
00015 TS_QSTN2
00016 TS_QSTN2_IX
00017 TS_QSTN2_LG
00018 TS_ADMIN
00019 TS_ADMIN_IX
----------------------------------------------------------------------------
Comment: DB2 BACKUP PLAY_DB OFFLINE
Start Time: 20091205181737
End Time: 20091205181823
Status: A
----------------------------------------------------------------------------
EID: 51 Location: /tmp/db2_backup


Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20091205182716001 F D S0000003.LOG
----------------------------------------------------------------------------
Contains 19 tablespace(s):

00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 TS_CF
00005 TS_CF_IX
00006 TS_JB7_A
00007 TS_JB7_IX_A
00008 TS_CF2
00009 TS_CF2_IX
00010 TS_CF2_LG
00011 TS_MISC
00012 TS_MISC_IX
00013 TS_REP
00014 TS_REP_IX
00015 TS_QSTN2
00016 TS_QSTN2_IX
00017 TS_QSTN2_LG
00018 TS_ADMIN
00019 TS_ADMIN_IX
----------------------------------------------------------------------------
Comment: DB2 BACKUP PLAY_DB OFFLINE
Start Time: 20091205182716
End Time: 20091205182845
Status: A
----------------------------------------------------------------------------
EID: 55 Location: /tmp/db2_backup

SQLCA Information

sqlcaid : SQLCA sqlcabc: 136 sqlcode: -2001 sqlerrml: 0

sqlerrmc:
sqlerrp : sqlubcka
sqlerrd : (1) 0 (2) 0 (3) 0
(4) 0 (5) 0 (6) 0
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20091205182949001 F D S0000003.LOG S0000003.LOG
----------------------------------------------------------------------------
Contains 19 tablespace(s):

00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 TS_CF
00005 TS_CF_IX
00006 TS_JB7_A
00007 TS_JB7_IX_A
00008 TS_CF2
00009 TS_CF2_IX
00010 TS_CF2_LG
00011 TS_MISC
00012 TS_MISC_IX
00013 TS_REP
00014 TS_REP_IX
00015 TS_QSTN2
00016 TS_QSTN2_IX
00017 TS_QSTN2_LG
00018 TS_ADMIN
00019 TS_ADMIN_IX
----------------------------------------------------------------------------
Comment: DB2 BACKUP PLAY_DB OFFLINE
Start Time: 20091205182949
End Time: 20091205183033
Status: A
----------------------------------------------------------------------------
EID: 56 Location: /tmp/db2_backup

Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B P 20091205184857001 F D S0000004.LOG S0000004.LOG
----------------------------------------------------------------------------
Contains 1 tablespace(s):

00001 USERSPACE1
----------------------------------------------------------------------------
Comment: DB2 BACKUP PLAY_DB OFFLINE
Start Time: 20091205184857
End Time: 20091205184927
Status: A
----------------------------------------------------------------------------
EID: 58 Location: /tmp/db2_backup


Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20091205190158001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 19 tablespace(s):

00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 TS_CF
00005 TS_CF_IX
00006 TS_JB7_A
00007 TS_JB7_IX_A
00008 TS_CF2
00009 TS_CF2_IX
00010 TS_CF2_LG
00011 TS_MISC
00012 TS_MISC_IX
00013 TS_REP
00014 TS_REP_IX
00015 TS_QSTN2
00016 TS_QSTN2_IX
00017 TS_QSTN2_LG
00018 TS_ADMIN
00019 TS_ADMIN_IX
----------------------------------------------------------------------------
Comment: DB2 BACKUP PLAY_DB OFFLINE
Start Time: 20091205190158
End Time: 20091205190243
Status: A
----------------------------------------------------------------------------
EID: 60 Location: /tmp/db2_backup


Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20091205193307001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 19 tablespace(s):

00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 TS_CF
00005 TS_CF_IX
00006 TS_JB7_A
00007 TS_JB7_IX_A
00008 TS_CF2
00009 TS_CF2_IX
00010 TS_CF2_LG
00011 TS_MISC
00012 TS_MISC_IX
00013 TS_REP
00014 TS_REP_IX
00015 TS_QSTN2
00016 TS_QSTN2_IX
00017 TS_QSTN2_LG
00018 TS_ADMIN
00019 TS_ADMIN_IX
----------------------------------------------------------------------------
Comment: DB2 BACKUP PLAY_DB OFFLINE
Start Time: 20091205193307
End Time: 20091205193350
Status: A
----------------------------------------------------------------------------
EID: 62 Location: /tmp/db2_backup


Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20091205194231001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 19 tablespace(s):

00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 TS_CF
00005 TS_CF_IX
00006 TS_JB7_A
00007 TS_JB7_IX_A
00008 TS_CF2
00009 TS_CF2_IX
00010 TS_CF2_LG
00011 TS_MISC
00012 TS_MISC_IX
00013 TS_REP
00014 TS_REP_IX
00015 TS_QSTN2
00016 TS_QSTN2_IX
00017 TS_QSTN2_LG
00018 TS_ADMIN
00019 TS_ADMIN_IX
----------------------------------------------------------------------------
Comment: DB2 BACKUP PLAY_DB OFFLINE
Start Time: 20091205194231
End Time: 20091205194317
Status: A
----------------------------------------------------------------------------
EID: 63 Location: /tmp/db2_backup


Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20091205202532001 F D S0000001.LOG S0000001.LOG
----------------------------------------------------------------------------
Contains 19 tablespace(s):

00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 TS_CF
00005 TS_CF_IX
00006 TS_JB7_A
00007 TS_JB7_IX_A
00008 TS_CF2
00009 TS_CF2_IX
00010 TS_CF2_LG
00011 TS_MISC
00012 TS_MISC_IX
00013 TS_REP
00014 TS_REP_IX
00015 TS_QSTN2
00016 TS_QSTN2_IX
00017 TS_QSTN2_LG
00018 TS_ADMIN
00019 TS_ADMIN_IX
----------------------------------------------------------------------------
Comment: DB2 BACKUP PLAY_DB OFFLINE
Start Time: 20091205202532
End Time: 20091205202616
Status: A
----------------------------------------------------------------------------
EID: 71 Location: /tmp/db2_backup


Op Obj Timestamp+Sequence Type Dev Earliest Log Current Log Backup ID
-- --- ------------------ ---- --- ------------ ------------ --------------
B D 20091206003517001 F D S0000000.LOG S0000000.LOG
----------------------------------------------------------------------------
Contains 19 tablespace(s):

00001 SYSCATSPACE
00002 USERSPACE1
00003 SYSTOOLSPACE
00004 TS_CF
00005 TS_CF_IX
00006 TS_JB7_A
00007 TS_JB7_IX_A
00008 TS_CF2
00009 TS_CF2_IX
00010 TS_CF2_LG
00011 TS_MISC
00012 TS_MISC_IX
00013 TS_REP
00014 TS_REP_IX
00015 TS_QSTN2
00016 TS_QSTN2_IX
00017 TS_QSTN2_LG
00018 TS_ADMIN
00019 TS_ADMIN_IX
----------------------------------------------------------------------------
Comment: DB2 BACKUP PLAY_DB OFFLINE
Start Time: 20091206003517
End Time: 20091206003600
Status: A
----------------------------------------------------------------------------
EID: 73 Location: /tmp/db2_backup

You have at least one successful offline db backup, db is not in backup pending state as per db cfg and you're able to connect to it.


Questions:
- Is this a single-partitioned or multi-partitioned db?
- If you omit "copy yes ...", does the tablespace for table <my table> is placed in backup pending state?
- Can you perform an online db backup?




Please execute the following steps and post the output:

db2 create db test
db2 update db cfg for test using logretain recovery
db2 "backup db test to /dev/null"
db2 connect to test
db2 create table test like sysibm.systables
db2 "load from /dev/null of del insert into test copy yes to /dev/null"

I can reproduce this error when using a multi-partitioned db if:

on node X - logretain is updated and offline backup is performed and
on node Y - logretain is updated but offline backup is not performed

I can connect to the db from node X, but load into a table defined across nodes X and Y gives this error.

db2girl, I think you are on the right track.
My db2 was a multi-partitione dsetup; I switched to a single-partition environment and tried again; this time around, I didn't run into the BACKUP PENDING problem, but still isn't able to load successfully with "COPY YES":

I do have LOGRETAIN set to RECOVERY and USEREXIT enabled; see at the bottom.

Any suggestion on how to proceed ?

Thanks !

- MDB





>> db2 load from /tmp/BEAR.TEST_TABLE.dat of del insert into BEAR.TEST_TABLE copy yes to /tmp/db2_copy_dir
SQL3522N A copy target cannot be provided when both log retain and user exits
are disabled.


Agent Type Node SQL Code Result
__________________________________________________ ____________________________
LOAD 000 +00000000 Success.
__________________________________________________ ____________________________
LOAD 001 -00003522 Init error. Table unchanged.
__________________________________________________ ____________________________
RESULTS: 1 of 2 LOADs completed successfully.
__________________________________________________ ____________________________

Summary of LOAD Agents:
Number of rows read = 0
Number of rows skipped = 0
Number of rows loaded = 0
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 0



>> db2 get db cfg for test

Database Configuration for Database test

Database configuration release level = 0x0a00
Database release level = 0x0a00

Database territory = US
Database code page = 819
Database code set = ISO8859-1
Database country/region code = 1
Database collating sequence = UNIQUE
Alternate collating sequence (ALT_COLLATE) =

Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

Discovery support for this database (DISCOVER_DB) = ENABLE

Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) = 0
Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20

Backup pending = NO

Database is consistent = NO
Rollforward pending = NO
Restore pending = NO

Multi-page file allocation enabled = YES

Log retain for recovery status = RECOVERY
User exit for logging status = YES

Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0

Database heap (4KB) (DBHEAP) = 1200
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4)
Log buffer size (4KB) (LOGBUFSZ) = 8
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
Buffer pool size (pages) (BUFFPAGE) = 1000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 100

Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 40000
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 512

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 256
SQL statement heap (4KB) (STMTHEAP) = 2048
Default application heap (4KB) (APPLHEAPSZ) = 64
Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 10
Lock timeout (sec) (LOCKTIMEOUT) = -1

Changed pages threshold (CHNGPGS_THRESH) = 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1
Number of I/O servers (NUM_IOSERVERS) = 3
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC

Track modified pages (TRACKMOD) = OFF

Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

Max number of active applications (MAXAPPLS) = AUTOMATIC
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 64

Log file size (4KB) (LOGFILSIZ) = 1000
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 2
Changed path to log files (NEWLOGPATH) =
Path to log files = /home/db2inst2/db2inst2/NODE0000/SQL00005/SQLOGDIR/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file = S0000002.LOG
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Percent of max active log space by transaction(MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = ON

HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) =
HADR local service name (HADR_LOCAL_SVC) =
HADR remote host name (HADR_REMOTE_HOST) =
HADR remote service name (HADR_REMOTE_SVC) =
HADR instance name of remote server (HADR_REMOTE_INST) =
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC

First log archive method (LOGARCHMETH1) = USEREXIT
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =
Number of log archive retries on error (NUMARCHRETRY) = 5
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
Vendor options (VENDOROPT) =

Auto restart enabled (AUTORESTART) = ON
Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART)
Log pages during index build (LOGINDEXBUILD) = OFF
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366

TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =

Automatic maintenance (AUTO_MAINT) = OFF
Automatic database backup (AUTO_DB_BACKUP) = OFF
Automatic table maintenance (AUTO_TBL_MAINT) = OFF
Automatic runstats (AUTO_RUNSTATS) = OFF
Automatic statistics profiling (AUTO_STATS_PROF) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF
Automatic reorganization (AUTO_REORG) = OFF

Table BEAR.TEST_TABLE is defined across two nodes: node 0 and node 1

db is enabled for archival logging on node 0 (get db cfg is from node 0) and so load completed successfully, but it's not enabled on node 1 and therefore
load returned -3522.

a thousand thanks to db2girl !

After learning more about multi partition, how to use db2_all, DB2NODE, db2nodes.cfg, etc., I was able to get "COPY YES" to work. Now I can test rollforward recovery.

- MDB