Solved

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

Posted on 2012-04-03
7
1,013 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 45

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 250 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 45

Assisted Solution

by:Kent Olsen
Kent Olsen earned 250 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 45

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction Regular patching is part of a system administrator's tasks. However, many patches require that the system be in single-user mode before they can be installed. A cluster patch in particular can take quite a while to apply if the machine…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

713 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