Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2009
    Posts
    5

    Unanswered: Rollforward recovery and Load Copy Yes

    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".

  2. #2
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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>

  3. #3
    Join Date
    Dec 2009
    Posts
    5

    Info Part 1

    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

  4. #4
    Join Date
    Dec 2009
    Posts
    5

    Info Part2 - hope no mistake in cut-and-paste

    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

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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"

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  7. #7
    Join Date
    Dec 2009
    Posts
    5
    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

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    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.

  9. #9
    Join Date
    Dec 2009
    Posts
    5

    It Works !

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •