|
Problem IC75312
|
Status: Closed |
REDEFINING TEMP SMS TABLESPACE TO USE AUTOMATIC STORAGE WILL NOT SUCCEED UNTIL SECOND CONNECT |
| product: |
DB2 FOR LUW / DB2FORLUW / 970 - DB2 |
| Problem description: |
A) When doing a redirected restore if we redefine a TEMP SMS
tablespace to use Automatic storage, the first connection will
bring the tablespace offline if the old directory is not
available. The tablespace will only be redefined to use
automatic storage on the second connect to the database.
To reproduce:
1. database sample (db2sampl)
2. db2 "create user temporary tablespace test_tbsp managed by
system using ('/tmp/tbsp_cont')"
3. db2 "backup database sample to /tmp/BK"
4. db2 restore database sample from /tmp/BK redirect generate
script red.out
5.
The script contains the following:
--
****************************************************************
*************
-- ** Tablespace name = TEST_TBSP
-- ** Tablespace ID = 7
-- ** Tablespace Type = System
managed space
-- ** Tablespace Content Type = User
Temporary data
-- ** Tablespace Page size (bytes) = 8192
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = No
-- ** Total number of pages = 1
--
****************************************************************
*************
SET TABLESPACE CONTAINERS FOR 7
-- IGNORE ROLLFORWARD CONTAINER OPERATIONS
USING (
PATH '/tmp/tbsp_cont'
);
Changed it to
SET TABLESPACE CONTAINERS FOR 7 using Automatic storage;
6.
When the restore script is run we get
$ db2 "set tablespace containers for 7 using automatic storage"
DB20000I The SET TABLESPACE CONTAINERS command completed
successfully.
When we run the restore we do not fail, even if the path
/tmp/tbsp_cont does not exist in the system.
If the path /tmp/tbsp_cont exists in the system, then we will
get the tablespace ok but if the path does not exist then we
will get this tablespace offline when connecting to the database
for the first time after the restore:
db2 connect to sample
EDUID : 6006 EDUNAME: db2agent (SAMPLE)
FUNCTION: DB2 UDB, buffer pool services,
sqlbStartPoolsErrorHandling, probe:65
MESSAGE : ADM6081W The table space "TEST_TBSP" (ID "7") is in
the OFFLINE
state and is not accessible. The table space state is
0x"00004000".
Refer to the documentation for SQLCODE -293.
SQL0293N Error accessing a table space container.
db2 list tablespace containers for 7 show detail
Tablespace Containers for Tablespace 7
Container ID = 0
Name = /tmp/tbsp_cont
Type = Path
Total pages = 0
Useable pages = 0
Accessible = No
Tablespace ID = 7
Name = TEST_TBSP
Type = System managed space
Contents = User Temporary data
State = 0x4000
Detailed explanation:
Offline
We need to terminate the connection to the database and connect
back to get the Automatic path defined for this tablespace
B) When migrating a database using a restore operation,
redefining SMS temp space to use Automatic Storage, if the old
path for the SMS temp space is not available the migration will
fail with
After step 6) above (having redefined the containers):
RESTORE DATABASE SAMPLE FROM '/home/mariaj/TEMP/BK' TAKEN AT
20110328074610 INTO SAMPLE REDIRECT WITHOUT ROLLING FORWARD
SQL0902C A system error (reason code = "") occurred.
Subsequent SQL
statements cannot be processed. SQLSTATE=58005
Upgrade database command will also fail with :
$ db2 upgrade database sample
SQL0902C A system error (reason code = "") occurred.
Subsequent SQL
statements cannot be processed. SQLSTATE=58005
The migration will only work if we have the old TEMP SMS path
with its original SQLTAG.NAM file available in the system.
Once the upgrade has finished the TEMP SMS will be redefined as
Automatic Storage on the first connection.
It is important to note that if we are restoring into an already
existing database to proceed with the upgrade, then the error
-902 is expected behaviour:
In order to be able to restore into an existing database, we
must initiate an implicit connection to the existing database
first. This is well before the restore can take place.
If the existing database is from an older release, the first
connect will initiate an automatic database upgrade.
The database upgrade cannot proceed if one of the tablespace
containers is misssing, so the -902 is expected. The workaround
is to drop the existing database and initiate a fresh restore.
In this case, the implicit connect will not be needed. |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* ALL *
****************************************************************
* PROBLEM DESCRIPTION: *
* On first connection to the database we redefine all *
* Temporary SMS Automatic storage tablespace. *
* Redefinition does not take place just after a restore, we *
* will clear the restore flag on the second activation of the *
* database. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 Version 9.7 and Fix Pack 5 *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 9.7 Fix Pack 5 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 6 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 7 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 8 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
|
| Solution |
Problem was first fixed in DB2 Version 9.7 and Fix Pack 5 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 28.03.2011
09.12.2011
21.05.2012 |
| Problem solved at the following versions (IBM BugInfos) |
9.7.
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 9.7.0.5
|
 |
