Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2014
    Posts
    4

    Unanswered: Need HELP with restore and empty tables

    Hey people

    I have been trying to make a copy of a database 3 days now and I just can't get it to work
    I'm a complete Newbie in db2 so please help me (anyway)

    DB2/LINUXX8664 9.7.5

    I made a backup of my database (no problem)
    db2 backup database lala to "/home/xxx/lala" compress without prompting
    That got me a nice 4 Gb file

    Ever since I try to restore the thing in another database with not exactly the same specifications.
    It's the same server but the tablespaces don't have the same size.
    db2 restore database lala from "/home/xxxx/lala" into lili REPLACE EXISTING without rolling forward
    It ends in
    DB20000I The RESTORE DATABASE command completed successfully.
    I can do LISTs
    db2 list tables for all
    And I can do SELECTs but not from tables that were empty in the original database
    db2 "select count(*) from schema.REJECTS"
    1
    -----------
    SQL0276N Connection to database "LILI" cannot be made because it is in the restore pending state. SQLSTATE=08004

    When I connect with a user different from administrator I get this message
    db2 connect to LILI user xxxx using ?????
    SQL20157N User with authorization ID "xxxx" failed to attach to a quiesced
    instance, or connect to a quiesced database or a database in a quiesced
    instance which is in the following quiesce mode: "QUIESCE DATABASE"
    SQLSTATE=08004

    So I try to unquiesce LILI
    db2 unquiesce database
    SQL0276N Connection to database "LILI" cannot be made because it is in the restore pending state. SQLSTATE=08004

    Do you have an idea?
    Thank you

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Code:
    SQL0276N  Connection to database "<name>" cannot be made because it is
          in the restore pending state.
    
    Explanation:
    
    The database must be restored before a connection can be done.
    
    No connection was made.
    
    User response:
    
    Restore the database, then issue the CONNECT statement again.
    
     sqlcode: -276
    
     sqlstate: 08004
    Where is the second database? Is it on the same server? If so you need to do a redirected restore. If not, is the second server exactly like the first? If not, then you need to do a redirected restore.

    Andy

  3. #3
    Join Date
    Dec 2014
    Posts
    4

    restore redirect

    Well, my databases are on the same server but they are not identical.
    So I need a redirect clause.
    With SET TABLESPACE CONTAINERS commands.

    That doesn't suit me (:
    I have turned it every way I could but there seems to be no way around it.
    The thing is that I don"t know how to do that.

    As far as I I understand I need to specify a sort of mapping between the existing table spaces and the files making up the table spaces of the destination database.

    By original db has 9 table spaces :
    0 SYSCATSPACE
    1 TEMPSPACE1
    2 USERSPACE1
    5 SC4_SDATA_TS_001
    6 SC4_SIX_TS_001
    9 SC4_SLOB_TS_001
    10 CCTOOLS_P_DATA_000
    11 DB2TMP
    12 SYSTOOLSPACE

    The destination database (which is bigger) has 12 table spaces
    0 SYSCATSPACE
    1 TEMPSPACE1
    2 USERSPACE1
    5 SYSTOOLSPACE
    6 SC4_SDATA_TS_001
    7 SC4_SIX_TS_001
    10 SC4_SLOB_TS_001
    12 SC4_SDATA_TS_002
    15 SC4_SLOB_TS_002
    16 SC4_SIX_TS_002
    17 CCTOOLS_P_DATA_000
    18 DB2TMP

    And these table spaces are distributed over many many physical files
    I really have to idea how to say in a db2-ish way to copy they data in each table space to corresponding destination table space

    I really appreciate your help but I need some more

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Use the GENERATE SCRIPT option of the restore command, then edit the generated script to specify the actual paths for the tablespaces:

    Code:
    restore db sample from /wherever redirect generate script /tmp/myscript.db2
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    you could consider changing to automatic storage... unless you have a real need for individual files. Also, DMS tablespaces are deprecated as of 10.1

  6. #6
    Join Date
    Dec 2014
    Posts
    4

    automatic storage

    I discovered that I have automatic storage.
    I don't really know what that means.

    If I use the script clause in the restore command I get a script that containts no
    set tablespace container
    commands at all. Does that mean that with automatic storage you don't have to specify anything else than
    restore database ... continue
    ?

    If that is so I still don't understand why on certain tables (mostly but not excursively empty tables) I get this SQL076N error
    db2 "select count(*) from SC4BAT.REJECTS"
    SQL0276N Connection to database "LILI" cannot be made because it is in the restore pending state. SQLSTATE=08004

    Anyone have more ideas? I'm desperate

  7. #7
    Join Date
    May 2012
    Location
    Canberra, Australia
    Posts
    176
    Provided Answers: 6
    an example
    "RESTORE DATABASE DMIGTSTB FROM '/db2/db2_data/backup/keep' taken at 20140414144133 ON '/db2/db2_data/db2tufi1/dcaptufa/data' DBPATH ON '/db2/db2_data/db2tufi1/dcaptufa/data' INTO DCAPTUFA"
    where the paths shown are the paths you want for the new db. have a look at the definitions of 'ON' and 'DBPATH' under 'restore database'
    check the links below... which you could have found by searching the Infocenter. By the way, you aren't running a production database are you?
    http://www-01.ibm.com/support/knowle...%2F3-0-0-2-4-1
    http://www-01.ibm.com/support/knowle...%2F3-6-2-4-109

  8. #8
    Join Date
    Dec 2014
    Posts
    2
    maybe you need copy all the archive logs to new location and roll forward the database

    ROLLFORWARD DATABASE lili TO END OF LOGS AND COMPLETE

Tags for this Thread

Posting Permissions

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