473,880 Members | 1,975 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Redirected restore problem

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:\DB2Back ups" 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.DAT A")
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

Nov 12 '05 #1
11 13612
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

Nov 12 '05 #2

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

Nov 12 '05 #3
I did try that too. Same result.

Nov 12 '05 #4
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

Nov 12 '05 #5
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_DAT A
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_IND X
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 = SYSTOOLSTMPSPAC E
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

Nov 12 '05 #6
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:\DB2Back ups" 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.DAT A")
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 teamdbaATNOSPAM scotdb.com !!!

Phil Nelson
ScotDB Ltd.
Nov 12 '05 #7
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

Nov 12 '05 #8
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:\DB2Back ups" 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.DAT A")


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

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

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

Nov 12 '05 #9
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:\DB2Back ups" 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 "SYSTOOLSPA CE")
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.DAT A")
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.IND X")
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 "SYSTOOLTMPSPAC E")
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

Nov 12 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2971
by: Philip Nelson | last post by:
Ran into a problem doing a redirected restore of a database with SQL stored procedures. On our production server database DPXXX001 has a number of SQL stored procedures defined. After installing a new release into production I want to create another copy of this onto our test box using name DTXXX001. I do a redirected restore, which completes successfully. However when I try to connect to the restored database I get -
3
3512
by: Vikrant | last post by:
Friends, Due an application (old) install program problem, Under AIX I could only create DB2 instance, I could also catalog it. Its complex application and I am not expert in creating full database for this application, but I have desired DB2 backup (by db2 backup db command). Can I do redirected restore (SET TABLESPACE CONTAINERS ....) only when instance is created & cataloged , I am under the impression that redirected restore is...
1
10368
by: Vikrant | last post by:
Hi Kelly This is from old response by you. What is 'callerac' parameter in REDIRECTED restore, how can I provide 'callerac' value ? Thanks, VKG =====
9
14041
by: GL | last post by:
I am running DB2 8.1.1 on AIX 5.1 Having a problem with a redirected restore. Once into the restore continue phase, I immediately get the following “SQL2059W A device full warning was encountered on device "TBS_IDX". Do you want to continue(c), terminate this device only(d), abort the utility(t) ? (c/d/t) t
1
2793
by: Jack | last post by:
Is it possible to find out what the tablespace definitions are of database you are about to build using a redirected restore? What I am wanting to do is build a script that can take only a backup file of a database and do a redirected restore into another database. This means I may not know or have access to the database where the backup originated at. It seems like once you start the redirected restore there is no way to do a list...
7
6278
by: CCC via DBMonster.com | last post by:
We use redirected restore to refresh test from prod on different AIX 5.2 servers. The databases have the same number, size and names of cooked containers. The only difference is the fully qualified path contains a different "SID": eg. "/db2/PRD/data1/..." versus "/db2/TST/data1/...". (Please note as an SAP installation I cannot change this layout). We use "set tablespace container" statements to redefine the fully qualified container...
1
2214
by: Kent.Brooke | last post by:
When a backup is taken from server a and then redirected restore is done on server b, the tables and data is fine however db2advis says "explain & advise tables not created for user x properly". However user x has access read, insert, delete rights to the advise & explain tables as well the tablespace where tables exist. I am perplexed and for some reason think it has something to do with the redirected restore am I correct. Any replys...
4
5604
by: Laurence | last post by:
Hi there, Command Reference book stated for db2relocatedb command: Copy the files/devices belonging to the databases being copied onto the system where the new instance resides. The path names must be changed as necessary. However, if there are already databases in the directory where the database files are moved to, you can mistakenly overwrite the existing sqldbdir file, thereby removing the references to the existing databases. In...
1
2534
by: okonita | last post by:
Hi all, This is a very urgent issue for my team. We are trying a Redirected restore the most current online DB2 UDBv8.2 backup copy from prod disk to disk on another/test instance. Our environment is RedHat Linux. We have tried the following with failure: (1) $ db2 restore db AANPDM01 from "/stuff/ftp/bkp" taken at 20070525164103 to "/dmd/data" into AANNDM01 NEWLOGPATH "/dmt/log" redirect without rolling forward; SQL1052N The...
0
9925
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11089
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10809
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9550
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7948
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5777
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4595
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4192
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3219
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.