473,726 Members | 2,177 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

List tablespaces work, but not SQL

DB2 8.2.3

the task I have is to write a script to restore a database from a
backup on to another machine.
the backup can be as old as seven years. The container layout on the
machine to be restored
can be totally different from the machine which was backedup. So we are
going with redirect
restore.

I plan to write a script in perl to change all containers from the
original path to the new
path and then complete the redirect restore.

The first task is to get the information about the tablespaces and
container
as it existed on the backup image. It seems the only way is to do the
following:
db2 restore db dbname from /dev/rmt/0c taken at yyyymmdd into tmpdb
redirect without prompting

db2 list tablespaces

db2 list tablespace containers for 5 show detail
....

what it does is to create a new tmp db in an inconsistent state and
leaves my current db2 session connected to that. the next two lines
(list tablespaces) shows me the information
about tablespaces and containers as it existed on the backup image.
that information
can be saved and after that it is easy to reconstruct all required
information for the redirect restore.

Then we decided to not use list tablespaces command, but use perl DBI
as it makes the
code easier to maintain. The SQL for that is

select tbspaceid,tbspa ce
from syscat.tablespa ces ;
select
tablespace_id,c ontainer_id,tot al_pages,substr (container_name ,1,40)
from table (snapshot_conta iner('',-1)) as A
order by tablespace_id,c ontainer_id ;

Now here is the problem. Db2 does not allow any SELECT statment on the
tmpdb after it
got created by the db2 restore db command. It tells

SQL3022N An SQL error "-1350" occurred while processing the SELECT
string in
the Action String parameter.

SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

Why is DB2 allowing list tablespaces to work, but not the equivalent
SQL command?
IMO this is annoying even though I can understand why this is
happening. I think list tablespaces
directly goes to the NODE directory and reads it from the catalog files
created there, bypassing
the engine.

Anyhow, solutions from anyone?

TIA.

Oct 26 '06 #1
3 4849
Lew
use the db2bkckp command with -t option. you'll have to parse the
output but it shouldn't too bad

Lew

dc********@aim. com wrote:
DB2 8.2.3

the task I have is to write a script to restore a database from a
backup on to another machine.
the backup can be as old as seven years. The container layout on the
machine to be restored
can be totally different from the machine which was backedup. So we are
going with redirect
restore.

I plan to write a script in perl to change all containers from the
original path to the new
path and then complete the redirect restore.

The first task is to get the information about the tablespaces and
container
as it existed on the backup image. It seems the only way is to do the
following:
db2 restore db dbname from /dev/rmt/0c taken at yyyymmdd into tmpdb
redirect without prompting

db2 list tablespaces

db2 list tablespace containers for 5 show detail
...

what it does is to create a new tmp db in an inconsistent state and
leaves my current db2 session connected to that. the next two lines
(list tablespaces) shows me the information
about tablespaces and containers as it existed on the backup image.
that information
can be saved and after that it is easy to reconstruct all required
information for the redirect restore.

Then we decided to not use list tablespaces command, but use perl DBI
as it makes the
code easier to maintain. The SQL for that is

select tbspaceid,tbspa ce
from syscat.tablespa ces ;
select
tablespace_id,c ontainer_id,tot al_pages,substr (container_name ,1,40)
from table (snapshot_conta iner('',-1)) as A
order by tablespace_id,c ontainer_id ;

Now here is the problem. Db2 does not allow any SELECT statment on the
tmpdb after it
got created by the db2 restore db command. It tells

SQL3022N An SQL error "-1350" occurred while processing the SELECT
string in
the Action String parameter.

SQL1350N The application is not in the correct state to process this
request.
Reason code="3".

Why is DB2 allowing list tablespaces to work, but not the equivalent
SQL command?
IMO this is annoying even though I can understand why this is
happening. I think list tablespaces
directly goes to the NODE directory and reads it from the catalog files
created there, bypassing
the engine.

Anyhow, solutions from anyone?

TIA.
Oct 26 '06 #2

Lew wrote:
use the db2bkckp command with -t option. you'll have to parse the
output but it shouldn't too bad
db2bkckp does not work with Legato backups. IBM tech support told
us that. we backup small dbs on tapes and big ones on legato.

Oct 26 '06 #3
Ian
dc********@aim. com wrote:
Lew wrote:
>use the db2bkckp command with -t option. you'll have to parse the
output but it shouldn't too bad

db2bkckp does not work with Legato backups. IBM tech support told
us that. we backup small dbs on tapes and big ones on legato.
Then I would suggest that you capture a snapshot of your tablespace
layout prior to doing the backup. (i.e. get snapshot for tablespaces
on DB).

The DB2 module for NetWorker has the ability to run pre-backup scripts
as well as specify additional files/directories that should get saved
with the database backup, so you could leverage these two things to
collect/store this info with your DB2 backups.

Oct 26 '06 #4

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

Similar topics

1
9184
by: CK | last post by:
Need a piece of advice on allocation of tablespaces for partitioning We are using a day level range-based approach as our parititiong scheme given that we have data inflows running into 15 million rows each day averaging around 2GB. The table has 31 partitions (one for each day) as we do not plan on retaining the data beyond 2/3 weeks. The partitions are purged at the end of 2/3rd week depending upon the when it is scheduled to run and...
3
2713
by: Vissu | last post by:
Hi , We will have about 50 tables. Some tables will be huge (severel million rows) and some are small but most of them will have about a million rows. For best performance of the database, how many tablespace should I have. I was thinking of using Locally Managed tablespaces (uniform extents) for both data and indexes. Can anyone share some experiences? Any other tips on layout of the database will be appreciated. Thanks
1
4108
by: Odd Bjørn Andersen | last post by:
Environment: AIX 4.3.3 DB2 UDB WSE version 8.1 fixpack 9a When I access a database residing on this server from my workstation (Windows xp, servicepack2 - db2 udb ESE fixpack 10) and in Control Center try to list the tablespaces (click on Tablespaces in the left part of the GUI) I get this error message: "SQL0444N Routine "*GET_TBSP" (specific name "SNAP_GET_TBSP") is implemented with code in library or path...
6
3804
by: mike_dba | last post by:
Can anyone tell me why a EEE system might be created to have two separate tablespaces with the same 4k pagesize ? They both appear to be in use as I can see the underlying files being modified. These were created with SMS and reside on the same disk and filesystem (separate directory on filesystem). One is called temp_4k and the other temp_sys. I would assume that the temp_sys was intended for use by the db2 catalog. I know of no way...
10
13413
by: rAinDeEr | last post by:
Hi, I am trying to create around 70 tablespaces for around 100 tables.. Am using DB2 UDB 8.2 in Linux environment... This is one i generated through Control centre.... CREATE REGULAR TABLESPACE SNCI001 IN DATABASE PARTITION GROUP IBMDEFAULTGROUP PAGESIZE 4096 MANAGED BY SYSTEM USING ('/db2home/db2inst1/dnci1d/user_tblspace')
4
4827
by: beena | last post by:
All, I'm new to the concept of automatic storage... I'm looking at the database setup by a vendor. I see few tablespaces showing up with automatic storage - Yes. Tablespace ID = 0 Name = SYSCATSPACE Type = Database managed space Tablespace ID = 1 Name = TEMPSPACE1
8
2110
by: alexhguerra | last post by:
Hello If im not missing something, when looking into directories that are SMS containers i can see multiple files, one for table data, one for indexes and other for lobs. Is there any special reason for not having different files/containers/ tablespace for each index? if there's a recommendation by IBM to have one table per tablespace, why not having one file/tablespace for each index?
1
1749
by: Justin | last post by:
I am restoring a database (approximately 1 Terabyte). After the restore started, I noticed that the "list utilities" completed work was not being updated. I checked the location of the restore and watch to see that the size of the table spaces was increasing. Once the table spaces were about done then I saw list utilities "completed work" began increasing. What exactly is the restore doing? Is this expected? It seems that it would...
6
3310
by: Troels Arvin | last post by:
Hello, I have recently run a rather large data import where the imported data i pumped through some updatable views equipped with INSTEAD OF triggers. For various reasons, the exact same data where imported into the exact same table/view/trigger structures (but with slightly different tablespace configuration, see later). The involved hardware was different (one running on x86_64, one running on PPC), but with comparable CPU, memory and...
0
8752
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9259
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8101
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
6702
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
6011
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4521
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...
0
4785
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2639
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2157
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.