Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2008
    Posts
    12

    Unanswered: Restore DB2 9.1 backup problem

    Hello,

    I am trying to restore a DB2 9.1 fp2 backup to another DB2 9.1 fp2 machine, both are on Windows.

    C:\Program Files\IBM\SQLLIB\BIN>db2 -tvf C:\db2_data\test.db2
    UPDATE COMMAND OPTIONS USING S ON Z ON MYDB_NODE0000.out V ON
    DB20000I The UPDATE COMMAND OPTIONS command completed successfully.

    SET CLIENT ATTACH_DBPARTITIONNUM 0
    DB20000I The SET CLIENT command completed successfully.

    SET CLIENT CONNECT_DBPARTITIONNUM 0
    DB20000I The SET CLIENT command completed successfully.

    RESTORE DATABASE mydb FROM 'C:\' TAKEN AT 20080310154100 INTO mydb REDIRECT WITHOU
    T ROLLING FORWARD
    SQL1277W A redirected restore operation is being performed. Table space
    configuration can now be viewed and table spaces that do not use automatic
    storage can have their containers reconfigured.
    DB20000I The RESTORE DATABASE command completed successfully.

    SET TABLESPACE CONTAINERS FOR 2 USING ( FILE 'C:\db2_data\userspace1'
    256000 )
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    SET TABLESPACE CONTAINERS FOR 3 USING ( FILE 'C:\db2_data\regtbs'
    768000 )
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    SET TABLESPACE CONTAINERS FOR 4 USING ( FILE 'C:\db2_data\idxtbs'
    128000 )
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    SET TABLESPACE CONTAINERS FOR 5 USING ( FILE 'C:\db2_data\lobtbs'
    512000 )
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    SET TABLESPACE CONTAINERS FOR 6 USING ( PATH 'C:\db2_data\testspace' )
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    RESTORE DATABASE mydb CONTINUE
    SQL1277W A redirected restore operation is being performed. Table space
    configuration can now be viewed and table spaces that do not use automatic
    storage can have their containers reconfigured.
    DB20000I The RESTORE DATABASE command completed successfully.

    As you can see, although all the redirects finish successfully, it fails to run the restore db mydb continue

    1. The script is generated by db2

    2. I manually run the above commands too, got the same problem

    3. There are 0~7 tablespace containers, but 0, 1, and 7 are automatic storage tablespace,
    e.g.
    db2 => set tablespace containers for 7 using (file "C:\db2_data\C0000000.LRG" 32)
    SQL20319N The SET TABLESPACE CONTAINERS command is not allowed on an
    automatic storage table space. SQLSTATE=55061

    4. The only thing I changed is the file size in the SET TABLESPACE CONTAINERS command, since I don't have enough space for the tablespace allocated on the original machine, but I do have enough space to hold all the data (the backup file is about 28GB only).

    Any suggestion/help is appreciated!

  2. #2
    Join Date
    Jun 2006
    Posts
    471
    the restore command completed successfully and the SQL1277W is just a warning
    is command 3 executed in between or not because the previous output does not show this command and was finished ok
    Best Regards, Guy Przytula
    DB2 UDB LUW certified V6/7/8

  3. #3
    Join Date
    Apr 2008
    Posts
    12
    I tried to run the command

    db2 => set tablespace containers for 7 using (file "C:\db2_data\C0000000.LRG" 32)

    and since I got the error message "SQL20319N The SET TABLESPACE CONTAINERS command is not allowed on an
    automatic storage table space. SQLSTATE=55061"
    I removed them from the script.

    Seems to me for some reason, after the script finished, it is still in "A redirected restore operation is being performed" state. I should run the "restore db continue" to continue the restore process, is my understanding correct?

    Thanks a lot!

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Try using the ON clause of the RESTORE command to specify new location(s) for automatic storage containers:
    Code:
    RESTORE DATABASE mydb FROM 'C:\' TAKEN AT 20080310154100 on D: INTO mydb ...
    and continue to set non-automatic storage containers as you've done before.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Apr 2008
    Posts
    12
    Hello N_I,

    Thanks for the suggestion.

    I tried it, and still cannot continue the restore db


    C:\db2_data>db2 -tvf test.db2
    UPDATE COMMAND OPTIONS USING S ON Z ON GIS_NODE0000.out V ON
    DB20000I The UPDATE COMMAND OPTIONS command completed successfully.

    SET CLIENT ATTACH_DBPARTITIONNUM 0
    DB20000I The SET CLIENT command completed successfully.

    SET CLIENT CONNECT_DBPARTITIONNUM 0
    DB20000I The SET CLIENT command completed successfully.

    RESTORE DATABASE mydb FROM 'C:\' TAKEN AT 20080310154100 ON 'C:' INTO mydb REDIRECT WITHOUT ROLLING FORWARD
    SQL1277W A redirected restore operation is being performed. Table space
    configuration can now be viewed and table spaces that do not use automatic
    storage can have their containers reconfigured.
    DB20000I The RESTORE DATABASE command completed successfully.

    SET TABLESPACE CONTAINERS FOR 2 USING ( FILE 'C:\db2_data\userspace1' 256000 )
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    SET TABLESPACE CONTAINERS FOR 3 USING ( FILE 'C:\db2_data\regtbs' 768000 )
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    SET TABLESPACE CONTAINERS FOR 4 USING ( FILE 'C:\db2_data\idxtbs' 128000 )
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    SET TABLESPACE CONTAINERS FOR 5 USING ( FILE 'C:\db2_data\lobtbs' 512000 )
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    SET TABLESPACE CONTAINERS FOR 6 USING ( PATH 'C:\db2_data\testspace' )
    DB20000I The SET TABLESPACE CONTAINERS command completed successfully.

    RESTORE DATABASE GIS CONTINUE
    SQL1277W A redirected restore operation is being performed. Table space
    configuration can now be viewed and table spaces that do not use automatic
    storage can have their containers reconfigured.
    DB20000I The RESTORE DATABASE command completed successfully.


    C:\db2_data>db2 restore db GIS continue
    SQL1277W A redirected restore operation is being performed. Table space
    configuration can now be viewed and table spaces that do not use automatic
    storage can have their containers reconfigured.
    DB20000I The RESTORE DATABASE command completed successfully.

    C:\db2_data>

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The only other idea that I have is that your new containers are either invalid for some reason or have insufficient sizes. Verify that HWM values in the source database do not exceed target container sizes and that the new containers are created successfully.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Apr 2008
    Posts
    12
    Hello N_I,

    Thanks a lot! Hopefully this is my last question:

    The original tablespace container

    **************************************************

    -- ** Tablespace name = REGTBS

    -- ** Tablespace ID = 3

    -- ** Tablespace Type = Database managed space

    -- ** Tablespace Content Type = All permanent data. Regular table space.

    -- ** Tablespace Page size (bytes) = 4096

    -- ** Tablespace Extent size (pages) = 32

    -- ** Using automatic storage = No

    -- ** Auto-resize enabled = No

    -- ** Total number of pages = 7680000

    -- ** Number of usable pages = 7679968

    -- ** High water mark (pages) = 2459680

    -- ************************************************** ****

    I have about 130GB free disk space (one disk).

    The maximum space for this one:
    7680000 * 4K/page = 29.3GB

    But if I put:

    db2 => set tablespace containers for 2 using (FILE 'c:\db2_data\regtbs' 7680000)
    SQL10003C There are not enough system resources to process the request. The request cannot be processed. SQLSTATE=57011

    Since I am really new to DB2, I wonder if I missed something rather basic here?

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    There will be more detailed error message in db2diag.log, especially if DIAGLEVEL is set to 4. Windows file size restrictions set by the system administrator would be my first suspect in this case.
    ---
    "It does not work" is not a valid problem statement.

  9. #9
    Join Date
    Apr 2008
    Posts
    12
    2008-04-03-14.53.58.770000-420 I7894247H463 LEVEL: Error
    PID : 2956 TID : 5076 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : GIS
    APPHDL : 0-586 APPID: *LOCAL.DB2.080403214946
    AUTHID : TESTUSER
    FUNCTION: DB2 UDB, buffer pool services, sqlbSetPoolCont, probe:1360
    MESSAGE : ZRC=0x8502008B=-2063466357=SQLB_RC_INSSYS_KERNEL
    "Not enough kernel memory to open a file"

    Is this "kernal memory" referring DB2 or the OS?
    It is a Core 2 Duo 6700, 2GB RAM (1GB avaliable), Windows XP (32-bit)

    Thanks!

  10. #10
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Hm. Curiousier and curiousier. I believe it refers to the Windows kernel.

    May be your source system was 64-bit or had more memory? The bufferpool sizes are a part of the backup image so they get created with the same size as at the source.

    If you think this may be the case, try setting the DB2_OVERRIDE_BPF registry variable (e.g. db2set DB2_OVERRIDE_BPF=1000) and restarting the target instance, then doing the restore again.
    ---
    "It does not work" is not a valid problem statement.

  11. #11
    Join Date
    Apr 2008
    Posts
    12
    Hello n_i, thanks a lot!

    I tried db2set DB2_OVERRIDE_BPF=1000 and restart db2, still got the same errors (including the kernal memory error in db2diag)

    The source backup is generated from a db2 cluster (32-bit, on 32-bit windows 2003) and I am trying to restore it to a stand alone windows XP machine, maybe this is the problem?

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Let's try another thing: "db2set DB2NTNOCACHE=ON" and restart the instance, then do another restore.
    ---
    "It does not work" is not a valid problem statement.

  13. #13
    Join Date
    Apr 2008
    Posts
    12
    Hello n_i,

    I tried db2set DB2NTNOCACHE=ON and restart db2, this time, I am ABLE to set all the tablespace containers.

    When I run restore database continue, it run and stopped. So I checked the db2diag file, and found:

    --------------------------------------------------------------------
    2008-04-07-13.14.32.026000-420 I8113553H446 LEVEL: Error
    PID : 3772 TID : 2800 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : GIS
    APPHDL : 0-7 APPID: *LOCAL.DB2.080407200510
    AUTHID : TESTUSER
    FUNCTION: DB2 UDB, buffer pool services, sqlbSetPoolCont, probe:1360
    MESSAGE : ZRC=0x850F000C=-2062614516=SQLO_DISK "Disk full."
    DIA8312C Disk was full.

    2008-04-07-13.14.32.088000-420 E8114001H530 LEVEL: Error
    PID : 3772 TID : 2800 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000 DB : GIS
    APPHDL : 0-7 APPID: *LOCAL.DB2.080407200510
    AUTHID : TESTUSER
    FUNCTION: DB2 UDB, buffer pool services, sqlbStartPoolRestore, probe:3
    MESSAGE : ADM6025I The table space "LOBTBS" (ID "5") is in state 0x"2001100").
    "RESTORE" is not possible. Refer to the documentation for SQLCODE
    -290.
    --------------------------------------------------------------------

    Currently, I have 119GB free diskspace on C, and Windows quota is not enabled.

    It does create idxtbs (3GB), regtbs(10GB), and userspace1 (5.7GB).

    tbs HWM pagesize(KB) Size in GB
    cnter
    ---------------------------------------------
    2 1378528 4 5.258666992
    3 2459680 9.38293457
    4 756096 2.884277344
    5 2945504 11.23620605
    6 320 0.001220703
    -----------------------------------------------

    Thanks!

  14. #14
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Oh come on. This one should be easy. Tablespace 5 needs 2945504 pages, according to what you've posted, yet you define the container with only 512000 pages.
    ---
    "It does not work" is not a valid problem statement.

  15. #15
    Join Date
    Apr 2008
    Posts
    12
    Hello N_I,

    first of all, I really appreciate all your help!

    I was able to restore (kind of) the database. the commands are all finished successfully. I do find, sometimes, I will have to wait for a while and re-try several times to run some of the set tablespace containers for x using (file '' <size>) commands, only for large ones (e.g. 10GB and 12GB ones, others are fine).

    Now, I got the following errors in db2diag file:

    -----------------------------------------------------
    2008-04-10-08.30.00.120000-420 I10080425H1631 LEVEL: Severe
    PID : 5944 TID : 5520 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000
    APPHDL : 0-12 APPID: *LOCAL.DB2.080410152959
    AUTHID : TESTUSER
    FUNCTION: DB2 UDB, buffer pool services, sqlb_verify_page, probe:20
    MESSAGE : ZRC=0x8602000E=-2046689266=SQLB_DBCHK
    "The database dependent check failed. The masked version of dbseed does not match."
    DIA8415C An incorrect database signature was found.
    DATA #1 : String, 64 bytes
    Error encountered trying to read a page - information follows :
    DATA #2 : String, 23 bytes
    Page verification error
    ---------------------------

    And after several repeats of the above, another one:
    ----------------------------------------------------
    2008-04-10-08.30.11.526000-420 E10090191H514 LEVEL: Error
    PID : 5944 TID : 5520 PROC : db2syscs.exe
    INSTANCE: DB2 NODE : 000
    APPHDL : 0-12 APPID: *LOCAL.DB2.080410152959
    AUTHID : TESTUSER
    FUNCTION: DB2 UDB, buffer pool services, sqlbStartPools, probe:30
    MESSAGE : ADM6023I The table space "SYSCATSPACE" (ID "0") is in state 0x"0".
    The table space cannot be accessed. Refer to the documentation for
    SQLCODE -290.
    ----------------------------------------------------

    Command used:
    db2>restore database mydb from C:\ taken at 20080310154100 on C: into mydb redirect without rolling forward

    and using "ON C:" is to redirect the automatically allocated tablespaces.

    I did run the db2ckbkp and the image is good from the results.

Posting Permissions

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