• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1037
  • Last Modified:

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

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
Enyinnaya
Asked:
Enyinnaya
  • 3
  • 2
  • 2
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
sathyaram_sCommented:
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
 
EnyinnayaAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
sathyaram_sCommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
EnyinnayaAuthor Commented:
Good choices
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now