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!

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

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!

Try using the ON clause of the RESTORE command to specify new location(s) for automatic storage containers:
and continue to set non-automatic storage containers as you've done before.

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>

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.

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?

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.

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!

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.

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?

Let's try another thing: "db2set DB2NTNOCACHE=ON" and restart the instance, then do another restore.

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!

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.

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.