Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: DB2 TSM restore

  1. #1
    Join Date
    Apr 2003
    Posts
    20

    Unanswered: DB2 TSM restore

    Sorry to bother you again but I still haven't managed to get restore to work.

    If I have the following two lines in the db cfg, what am I missing??

    Rollforward pending = DATABASE
    Restore pending = YES

    Please help. A backup is kind of useless if I can't restore.

    Cheers
    ---
    Henrik Morsing
    Certified AIX 4.3 Systems Administration
    & p690 Technical Support

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: DB2 TSM restore

    Do a rollforward database command ...

    Cheers

    Sathyaram

    Originally posted by morsing
    Sorry to bother you again but I still haven't managed to get restore to work.

    If I have the following two lines in the db cfg, what am I missing??

    Rollforward pending = DATABASE
    Restore pending = YES

    Please help. A backup is kind of useless if I can't restore.

    Cheers

  3. #3
    Join Date
    Apr 2003
    Posts
    20
    I tried that already and it won't do that:

    [bob5](db2inst1:/home/db2inst1) $db2 rollforward db tsmtest to end of logs
    SQL1119N A connection to or activation of database "TSMTEST" cannot be made
    because a previous restore is incomplete. SQLSTATE=57019
    [bob5](db2inst1:/home/db2inst1) $

    Cheers
    ---
    Henrik Morsing
    Certified AIX 4.3 Systems Administration
    & p690 Technical Support

  4. #4
    Join Date
    Sep 2002
    Posts
    456
    try this:

    db2 rollforward db tsmtest to end of logs and complete

    if that does not work try cancelling the rollforward operation and retry the above command. You can cancel the rollforward by:

    db2 rollforward database tsmtest cancel

    Good luck

    dollar

    Originally posted by morsing
    I tried that already and it won't do that:

    [bob5](db2inst1:/home/db2inst1) $db2 rollforward db tsmtest to end of logs
    SQL1119N A connection to or activation of database "TSMTEST" cannot be made
    because a previous restore is incomplete. SQLSTATE=57019
    [bob5](db2inst1:/home/db2inst1) $

    Cheers

  5. #5
    Join Date
    Apr 2003
    Posts
    20
    Well, I have three test databases and I must have done something wrong but each time I have made a restore they give me the following error no matter what I do:

    SQL1119N A connection to or activation of database "TESTING" cannot be made
    because a previous restore is incomplete. SQLSTATE=57019

    A can't connect, restore or rollforward.

    Cheers
    ---
    Henrik Morsing
    Certified AIX 4.3 Systems Administration
    & p690 Technical Support

  6. #6
    Join Date
    Apr 2003
    Posts
    191
    Hi morsing,

    looks like the restore itself didn't complete. Can you come up with an explanation to this?

    Did you capture the output from your restore command?

    Also, you may want to check ~/sqllib/db2diag.log for error messages.

    Johann


    Originally posted by morsing
    Well, I have three test databases and I must have done something wrong but each time I have made a restore they give me the following error no matter what I do:

    SQL1119N A connection to or activation of database "TESTING" cannot be made
    because a previous restore is incomplete. SQLSTATE=57019

    A can't connect, restore or rollforward.

    Cheers

  7. #7
    Join Date
    Apr 2003
    Posts
    20
    Hi Johann,

    I can't really come up with an explanation since I have absolutely no idea what I'm doing.
    I've tried some things I found on the net but so far the only thing that has finished succesfully is a full backup restore. After that it's pretty unclear what it wants.

    Sometimes it says it's a part of an incremental restore and it needs an image time stamp <number> but when I try to restore number it says it doesn't have an image with that number.

    I did manage once to put on of the incremental images on but every locks after that.

    Thanks for any help

    Cheers
    ---
    Henrik Morsing
    Certified AIX 4.3 Systems Administration
    & p690 Technical Support

  8. #8
    Join Date
    Apr 2003
    Posts
    191
    Hi Henrik,

    so full offline backup restores work, don't they?

    My experience with online backups is sparse, none with incremental backups so far.

    My recommendation is:
    a) Make sure full offline backups are restorable.
    b) Do a new full online backup, catch stdout, do a restore, catch stdout

    If there are problems, stop and send over the stdouts. If not, continue as requested, eg.:
    c) Do a new full offline backup, then an incremental offline backup, then restore, catch stdouts again. Can you connect?
    d) Do a new full online backup, then an incremental online backup, then restore, same as above ...

    Johann

    Originally posted by morsing
    Hi Johann,

    I can't really come up with an explanation since I have absolutely no idea what I'm doing.
    I've tried some things I found on the net but so far the only thing that has finished succesfully is a full backup restore. After that it's pretty unclear what it wants.

    Sometimes it says it's a part of an incremental restore and it needs an image time stamp <number> but when I try to restore number it says it doesn't have an image with that number.

    I did manage once to put on of the incremental images on but every locks after that.

    Thanks for any help

    Cheers

  9. #9
    Join Date
    Apr 2003
    Posts
    20
    Hi Johann,
    thanks for giving a helping hand, I really really appriciate it.

    I just started over in small steps and I suddenly found out that the executable 'db2uext2' isn't on my system.

    Could this be it?? Do you have it??

    Cheers
    ---
    Henrik Morsing
    Certified AIX 4.3 Systems Administration
    & p690 Technical Support

  10. #10
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    has the userexit been setup to ON ...

    db2 get db cfg for <dbname> | grep USEREXIT

    Unless this is ON, you will not need the userexit program ...

    This userexit is used for archiving the log files and retrieving them during rollforward ....

    If the userexit parameter has been set to ON and you did not have the program, there will be dozens(or hundreds) of error messages in your db2diag.log


    HTH

    Cheers

    Sathyaram

    Originally posted by morsing
    Hi Johann,
    thanks for giving a helping hand, I really really appriciate it.

    I just started over in small steps and I suddenly found out that the executable 'db2uext2' isn't on my system.

    Could this be it?? Do you have it??

    Cheers

  11. #11
    Join Date
    Apr 2003
    Posts
    191
    Hi Henrik,

    as Sathyaram pointed out, you will need db2uext2 only if you want to enable roll forward recovery. Most of the time, though, you will want this for obvious reasons.

    You should not complain about a possibly missing db2uext2 program. You have to build it yourself. IBM provides some examples in the sample directory. I think there is a db2uext2.adsm file or something that may be of interest to you; this is going to take you some time ...

    Johann

    Originally posted by morsing
    Hi Johann,
    thanks for giving a helping hand, I really really appriciate it.

    I just started over in small steps and I suddenly found out that the executable 'db2uext2' isn't on my system.

    Could this be it?? Do you have it??

    Cheers

  12. #12
    Join Date
    Apr 2003
    Posts
    20
    But do I need userexit??

    I just managed to do the following:

    Create DB and table. -- OK
    Disconnect & Backup -- OK
    connect and insert data -- OK
    Disconnect & Backup -- OK
    connect and update data -- OK
    disconnect -- OK
    restore image (only one in the history list) -- OK
    connect --FAILED with the following error:

    SQL1119N A connection to or activation of database "TESTDB" cannot be made
    because a previous restore is incomplete. SQLSTATE=57019


    This is all I have done!!

    The db cfg looks like:

    Database Configuration for Database testdb

    Database configuration release level = 0x0900
    Database release level = 0x0900

    Database territory = GB
    Database code page = 819
    Database code set = ISO8859-1
    Database country code = 44

    Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

    Directory object name (DIR_OBJ_NAME) =
    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
    Number of frequent values retained (NUM_FREQVALUES) = 10
    Number of quantiles retained (NUM_QUANTILES) = 20

    Backup pending = NO

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

    Multi-page file allocation enabled = NO

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

    Data Links Token Expiry Interval (sec) (DL_EXPINT) = 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
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = 64
    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 appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128

    Sort list heap (4KB) (SORTHEAP) = 256
    SQL statement heap (4KB) (STMTHEAP) = 2048
    Default application heap (4KB) (APPLHEAPSZ) = 128
    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) = 32

    Track modified pages (TRACKMOD) = ON

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

    Max number of active applications (MAXAPPLS) = 40
    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/db2inst1/db2ins
    t1/NODE0000/SQL00004/SQLOGDIR/
    First active log file = S0000002.LOG

    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

    Auto restart enabled (AUTORESTART) = ON
    Index re-creation time (INDEXREC) = SYSTEM (RESTART)
    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) =


    Cheers
    ---
    Henrik Morsing
    Certified AIX 4.3 Systems Administration
    & p690 Technical Support

  13. #13
    Join Date
    Apr 2003
    Posts
    20
    Sorry Johann, we cross posted each other.

    So the missing db2uext2 binary seems to be my problem then??

    Cheers
    ---
    Henrik Morsing
    Certified AIX 4.3 Systems Administration
    & p690 Technical Support

  14. #14
    Join Date
    Apr 2003
    Posts
    191
    Hi Henrik,

    consider these db cfg parameters:

    Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00004/SQLOGDIR/

    Log retain for recovery enabled (LOGRETAIN) = RECOVERY
    User exit for logging enabled (USEREXIT) = ON

    The third means db2uext2 is needed, which is what you want for production databases. For testing purposes, you may switch this off.

    The second enables roll forward recovery. This is what you need for most production databases. It means log files will be kept and not recycled once full and archived.

    The first is the path you will fill up with archived log files unless you enable the userexit.

    To get started, you can proceed as follows:
    1) turn off the userexit, do your backup and restore testing
    2) once you are satisfied, turn it back on, find, modify, compile and deploy db2uext2.adsm
    3) test the userexit (through lots of big transactions)
    4) once you are satisfied, continue testing backup and restore

    Johann

  15. #15
    Join Date
    Apr 2003
    Posts
    20
    Sadly, turning off userexit didn't make any difference.

    The command

    $ db2 restore db testdb incremental use tsm taken at 2003050811305

    Finishes succesfully and in db2diag.log I get:


    2003-05-08-13.29.22.425527 Instance:db2inst1 Node:000
    PID:29172(db2agent (TESTDB)) Appid:*LOCAL.db2inst1.030508080908
    database_utilities sqludPrintStartingMsg Probe:1 Database:TESTDB

    Continuing an incremental restore.

    2003-05-08-13.30.02.233243 Instance:db2inst1 Node:000
    PID:46446(db2med.29172.0) Appid:none
    database_utilities sqluivdev Probe:34

    DiagData
    0000 0017 ....


    2003-05-08-13.30.02.477106 Instance:db2inst1 Node:000
    PID:46446(db2med.29172.0) Appid:none
    database_utilities sqluivdev Probe:35

    DiagData
    0000 7000 ..p.


    2003-05-08-13.30.13.930140 Instance:db2inst1 Node:000
    PID:29172(db2agent (TESTDB)) Appid:*LOCAL.db2inst1.030508080908
    database_utilities sqludrsa Probe:0 Database:TESTDB

    Restore Complete.


    It all looks fine to me but after that I keep getting that "can't conect because a restore didn't finish" error.


    Cheers
    ---
    Henrik Morsing
    Certified AIX 4.3 Systems Administration
    & p690 Technical Support

Posting Permissions

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