Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2012-04-03
7
1,004 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
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
A while back when OPSMGR 2012 was released we were very excited about getting it into our environment and upgrading our 2007 implementation,  we started our planning and we then proceeded with our implementation. All went as planned & our system …
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 navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…

828 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