Redirected restore problem

[Chris]

I have searched this group for answers and tried the responses.

I am trying to Use an full online backup from our production server and
apply it to our test server. The Tablespaces in the productions server
are on the d drive and my test server has no d drive.

The restore command i am using is:

RESTORE DATABASE CENTRAL FROM "C:\DB2Backups" TAKEN AT 20050620000000
INTO CENT0620 REDIRECT;

SQL1277N Restore has detected that one or more table space containers
are
inaccessible, or has set their state to 'storage must be defined'.
DB20000I The RESTORE DATABASE command completed successfully.

message as expected. I then go to issue SET TABLESPACE commands and I
get:

db2 => SET TABLESPACE CONTAINER FOR 4 USING (PATH "CMPDEFAULT.DATA")
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

Any help would be greatly appreciated!!

thanks,

Chris

[Chris]

I am sorry i forgot the basics:

Backup came from UDB worlgroup 8.1.7 on win32 (2003 server)

Restore is going on UDB worlgroup 8.1.7 on win32 (XP Pro)

I have also tried the restore on 8.1.9 on win32 (XP pro) with the same
results

thanks,

Chris

[Visu]

Try giving the full path in your in set tablespace command.

[Chris]

I did try that too. Same result.

[peteh]

Chris -

From the messages and codes doc, reason code 3:

------------------------------------------------------------------------------
03
A Restore request is in progress. A warning was received from the
initial utility call indicating that further requests are required
before Restore can complete.
-------------------------------------------------------------------------------

This would seem to indicate that you have missed at least 1 SET
TABLESPACE command because the util is still waiting. Good luck.

Pete H

[Chris]

I had looked the reason code 3 up. I posted in teh original post the
return from the restore statment the only warning it had was:
SQL1277N Restore has detected that one or more table space containers
are
inaccessible, or has set their state to 'storage must be defined'.
DB20000I The RESTORE DATABASE command completed successfully.

the following is the results from: LIST TABLESPACES SHOW DETAIL. Even
this returns with the same SQL1350N error reason code 3

Tablespaces for Current Database

Tablespace ID = 0
Name = SYSCATSPACE
Type = System managed space
Contents = Any data
State = 0x2000100
Detailed explanation:
Restore pending
Storage may be defined

Tablespace ID = 1
Name = TEMPSPACE1
Type = System managed space
Contents = System Temporary data
State = 0x2000100
Detailed explanation:
Restore pending
Storage may be defined

Tablespace ID = 2
Name = USERSPACE1
Type = System managed space
Contents = Any data
State = 0x2000100
Detailed explanation:
Restore pending
Storage may be defined

Tablespace ID = 3
Name = SYSTOOLSPACE
Type = System managed space
Contents = Any data
State = 0x2000100
Detailed explanation:
Restore pending
Storage may be defined

Tablespace ID = 4
Name = CMP_DEFAULT_DATA
Type = System managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 5
Name = CMP_DEFAULT_INDX
Type = System managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 6
Name = CMP_IVJ_DATA
Type = Database managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 7
Name = CMP_IVJ_INDX
Type = Database managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 8
Name = CMP_ORD_DATA
Type = Database managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 9
Name = CMP_ORD_INDX
Type = Database managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 10
Name = CMP_ORL_DATA
Type = Database managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 11
Name = CMP_ORL_INDX
Type = Database managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 12
Name = CMP_ORT_DATA
Type = Database managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 13
Name = CMP_ORT_INDX
Type = Database managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 14
Name = CMP_POI_DATA
Type = Database managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 15
Name = CMP_POI_INDX
Type = Database managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 16
Name = CMP_TRI_DATA
Type = Database managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 17
Name = CMP_TRI_INDX
Type = Database managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 18
Name = FLD_SLD_DATA
Type = Database managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 19
Name = FLD_SLD_INDX
Type = Database managed space
Contents = Any data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

Tablespace ID = 20
Name = SYSTOOLSTMPSPACE
Type = System managed space
Contents = User Temporary data
State = 0x2001100
Detailed explanation:
Restore pending
Storage must be defined
Storage may be defined

SQL1350N The application is not in the correct state to process this
request.
Reason code="3".
Is there something else that needs to be done between the redirected
restore command and the SET TABLESPACE command??

Thanks,

Chris

[Philip Nelson]

Chris wrote:

I have searched this group for answers and tried the responses.

I am trying to Use an full online backup from our production server and
apply it to our test server. The Tablespaces in the productions server
are on the d drive and my test server has no d drive.

The restore command i am using is:

RESTORE DATABASE CENTRAL FROM "C:\DB2Backups" TAKEN AT 20050620000000
INTO CENT0620 REDIRECT;

SQL1277N Restore has detected that one or more table space containers
are
inaccessible, or has set their state to 'storage must be defined'.
DB20000I The RESTORE DATABASE command completed successfully.

message as expected. I then go to issue SET TABLESPACE commands and I
get:

db2 => SET TABLESPACE CONTAINER FOR 4 USING (PATH "CMPDEFAULT.DATA")
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

Any help would be greatly appreciated!!

thanks,

Chris

Chris,

I suspect that this is because you are doing autocommit after each
statement. So DB2 is treating each statement as a totally separate unit
of work.

Suggest you put all your commands you need to do into a file (let's call it
fullback.cmds) and run it from the db2cmd C: prompt as -

db2 -tvf fullback.cmds

I've had problems running redirected restores in any other way than this.
I've also had issues when a failure occurs and all the commands weren't
completed.

I've got a Perl script I use to build the redirected restore file based on
the source database. It produces UNIX type paths but you can always
globally change the output. If you are interested I can email you a copy.
Just send me an email at teamdbaATNOSPAMscotdb.com !!!

Phil Nelson
ScotDB Ltd.

[peteh]

My apologies for not reading the OP more carefully. I know your pain -
I struggled for quite awhile with the redirected restore/set tablespace
syntax. I'll offer a few other (possibly lame) observations:
* you have a mix of DMS amd SMS tablespaces...
* not knowing the platform, does CMPDEFAULT.DATA really describe your
path? Given your orginal backup dir, I would have expected something
like C:\TBSP4
* I used single quotes around path, not doubles

Sincerely hope this helps,

Pete H

[Ian]

Chris wrote:

I have searched this group for answers and tried the responses.

I am trying to Use an full online backup from our production server and
apply it to our test server. The Tablespaces in the productions server
are on the d drive and my test server has no d drive.

The restore command i am using is:

RESTORE DATABASE CENTRAL FROM "C:\DB2Backups" TAKEN AT 20050620000000
INTO CENT0620 REDIRECT;

SQL1277N Restore has detected that one or more table space containers
are
inaccessible, or has set their state to 'storage must be defined'.
DB20000I The RESTORE DATABASE command completed successfully.

message as expected. I then go to issue SET TABLESPACE commands and I
get:

db2 => SET TABLESPACE CONTAINER FOR 4 USING (PATH "CMPDEFAULT.DATA")

This is a syntax error (yes, I agree that the error code is misleading).

set tablespace containers for 4 using (path "cmpdefault.data")

Notice "containers" (plural), not "container" (singular).

[Chris]

Well thank you all for your responces.

I did try the script and turning off auto commit.

I also changed from plural to singular.

Script used as follows with return values:

RESTORE DATABASE CENTRAL USER USERID USING FROM
"C:\DB2Backups" TAKEN A
T 20050620000000 INTO CENT0620 REDIRECT
SQL1277N Restore has detected that one or more table space containers
are
inaccessible, or has set their state to 'storage must be defined'.
DB20000I The RESTORE DATABASE command completed successfully.

SET TABLESPACE CONTAINER FOR 0 USING (PATH "SQLT0000.0")
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 1 USING (PATH "SQLT0001.0")
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 2 USING (PATH "SQLT0002.0")
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 3 USING (PATH "SYSTOOLSPACE")
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 4 USING (PATH "CMPDEFAULT.DATA")
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 5 USING (PATH "CMPDEFAULT.INDX")
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 6 USING (FILE "CMPIVJ_01.DATA" 250000)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 7 USING (FILE "CMPIVJ_01.INDX" 250000)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 8 USING (FILE "CMPORD_01.DATA" 50000)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 8 USING (FILE "CMPORD_01.INDX" 50000)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 10 USING (FILE "CMPORL_01.DATA" 50000)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 11 USING (FILE "CMPORL_01.INDX" 50000)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 12 USING (FILE "CMPORT_01.DATA" 12500)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 13 USING (FILE "CMPORT_01.INDX" 12500)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 14 USING (FILE "CMPPOI_01.DATA" 3750)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 15 USING (FILE "CMPPOI_01.INDX" 3750)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 16 USING (FILE "CMPTRI_01.DATA" 12500)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 17 USING (FILE "CMPTRI_01.INDX" 12500)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 18 USING (FILE "FLDSLD_01.DATA" 250000)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 19 USING (FILE "FLDSLD_01.INDX" 250000)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

SET TABLESPACE CONTAINER FOR 20 USING (PATH "SYSTOOLTMPSPACE")
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

RESTORE DATABASE CENTRAL CONTINUE
SQL1277N Restore has detected that one or more table space containers
are
inaccessible, or has set their state to 'storage must be defined'.
DB20000I The RESTORE DATABASE command completed successfully.
I am tring to put all containers into the defualt db directory. It is
interesting that the statment below even returns the same error:
db2 => SET
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

I am fully out of ideas.

thanks,

Chris

[hikums]

Why are you not providing your absolute path name in the PATH?

It should be something like this:
SET TABLESPACE CONTAINERS FOR 19 USING (FILE
"D:\DB2\NODE0000\SQL0002\FLDSLD_01.INDX" 250000)
I am hoping you have taken a list tablespaces show detail for the
original database, and run SET TABLESPACE CONTAINERS for all the
containers starting from container 0.

Also, you do not have to SET TABLESPACE CONTAINERS in a script.

THE MOST important thing about REDIRECTED RESTORE is all the commands
starting from RESTORE command to the final RESTORE CONTINUE should all
be in the same command session!! VERY IMPORTANT.

Else, you'll have to start from the beginning.

[Chris]

Thank you all for your help.

I still do not know why the backup file would not restore.

Last night I did an offline backup and it restored without error with
the above commands.

Is there a problem restoring redirected from an online backup??

thansk,

Chris