Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DB2 backup at the tablespace (4K) level - Can it be restored to a 32k tablespace?

Posted on 2012-04-03
7
Medium Priority
?
1,033 Views
Last Modified: 2012-06-27
Hi all,

Just a quick quetion. I have tablespace 4K, 16K which I am going to backup individually at the tablespace level. My requirement are that the tablespace and all the tables that it contains be restored to a 32K tablespace.
How can I restore the individual tablespaces to a same name 32k tablespace? what command will restore the tablespaces individually to the desired 32k tablespaces.

Thanks
0
Comment
Question by:Enyinnaya
  • 3
  • 2
  • 2
7 Comments
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 37803805
Hi Enyinnaya,

I'm not aware of a backup/restore procedure that will allow you to change the page size this way.

But there are several ways that you can recreate the database with a larger page size.  You can always create a new tablespace and tables, then insert the rows into it.  DB2MOVE can also be used to change the page size.


Kent
0
 
LVL 4

Accepted Solution

by:
sathyaram_s earned 1000 total points
ID: 37803830
If you are on db2 9.7, you can conisder using tools like admin_move_table, admin_copy_schema etc. to move your current tables to a new tablespace of desired  pagesize



==
0
 

Author Comment

by:Enyinnaya
ID: 37804461
sathyaram_s, Thanks for the response...yes admin_move_tablecan do the job but I have 1800 plus tables to move and I am afraid the stor proc does move one table at a time, right? I have read the command description and I don't get the understanding that admin_move_table can do a mass move or can it? copy_schema can work but does require lots of storage space which I don't have...

Any suggestion?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 4

Expert Comment

by:sathyaram_s
ID: 37804934
Unfortunately, you do not have a choice. You cannot "convert" 4K to 32K pages using any inbuilt commnads/utilities. The only option is to do one table at a time.

If you have the "luxury" of downtime, then db2move or export/db2look/load will be a good option as Kent suggested. Otherwise, you can prioratize the tables and use admin_move_tables to move them to the tablespace of your choice one at a time over a few months. Out of the 1800 tables, I assume most of them will be tiny ..  So, should be quick on them ..

HTH
0
 
LVL 46

Assisted Solution

by:Kent Olsen
Kent Olsen earned 1000 total points
ID: 37806239
Hi Enyinnaya,


Do you have enough free disk space to hold the current tablespace as well as a 32K version of it?  The 32K tablespace may require less storage than the tablespace with the smaller page size, depending on how the rows pack in the page, but the worst-case scenario is that both tablespaces will require the same amount of storage.  Unless you're going to export data to offline storage (i.e. tape) you're going to need enough storage to do the conversion.

You can use the admin* tools that sathyaram mentions to perform the conversion, or you can very quickly generate a script to do the work for you.

Since you can't convert the existing tablespace "in place" you'll need a new tablespace to hold the data while the migration process runs.

First, check to see which tables will need to be converted.

SELECT name, creator, type, tbspace, card, npages, fpages, overflow 
FROM sysibm.systables
WHERE type = 'T'
  AND creator = {schemaname}
  AND tbspace = {tablespacename}

Open in new window


That will show the items that need converting (and some additional information).

Then create the new tablespace

CREATE TABLESPACE Userspace32K pagesize 32K

Open in new window


Create a Schema to hold the migrated items.

CREATE SCHEMA migrate32

Open in new window


Now generate the meta-SQL to create all of the tables.

SELECT 
  'CREATE TABLE migrate32.' || name || ' as (select * from {oldschema}.' || name || ') definition only in Userspace32'

Open in new window


Run the SQL that is generated from that statement to create all of the tables in the 32K tablespace.

Then generate the meta-SQL to copy all of the data.

SELECT 
  'ALTER TABLE migrate32.' || name || ' ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE;' ||
  'INSERT INTO migrate32.' || name || ' SELECT * FROM {oldschema}.' || name || ';' ||
  'COMMIT;'
FROM sysibm.systables
WHERE type = 'T'
  AND creator = {schemaname}
  AND tbspace = {tablespacename}
ORDER BY NPAGES ASCENDING

Open in new window


Loading the small (probably more static) tables first puts them together near the top of the tablespace and reduces fragmenting of the larger tables.  That may (or may not) have a small performance boost as the data changes.

You can then run the SQL that is generated to load the data into the new tablespace.  If there is any LOB/BLOB data, a bit of additional definitions/handling will be required.

You'll want to restore the IDENTITY definitions.  Annoyingly, they aren't carried over when the table is created by copying the definition of another table.  So we generate the meta-SQL to do this.

SELECT
  'ALTER TABLE Migrate32.' || tbname || ' ALTER COLUMN ' || name || ' GENERATED ' ||
  case when generated = 'D' then 'BY DEFAULT' else 'ALWAYS' end || ' AS IDENTITY '
FROM sysibm.syscolumns
WHERE identity = 'Y'
  AND TBCREATOR = {schemaname}

Open in new window


Run that to establish the correct columns as IDENTITY columns.

The IDENTITY columns will need to be set to the correct "next" value.  That could be done as part of the query above, but it's just as easy to do it as a separate query.

SELECT 
  'ALTER TABLE Migrate32.' || tbname || ' ALTER COLUMN ' || name || ' RESTART WITH ' ||
  (SELECT cast (max(' || name || ')+1) as varchar(10)) from {schemaname}.' || tbname || ')'
FROM sysibm.syscolumns
WHERE identity = 'Y'
  AND TBCREATOR = {schemaname}

Open in new window

0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 37806255
Oops.  Hit "Submit" instead of "Preview".

Anyway, converting the page size isn't trivial.  LOB/BLOBs must be taken into account, as well as constraints.

But once the data is created in the new tablespace, you can back up the tablespace and restore it (or the contained objects) over the original tablespace/objects.

You may have to rebind objects that referenced items in the original tablespace, too.



Kent
0
 

Author Closing Comment

by:Enyinnaya
ID: 37858994
Good choices
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Using libpcap/Jpcap to capture and send packets on Solaris version (10/11) Library used: 1.      Libpcap (http://www.tcpdump.org) Version 1.2 2.      Jpcap(http://netresearch.ics.uci.edu/kfujii/Jpcap/doc/index.html) Version 0.6 Prerequisite: 1.      GCC …
Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Suggested Courses

963 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question