Solved

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

Posted on 2012-04-03
7
1,023 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
[X]
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
  • 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
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 
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

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

Question has a verified solution.

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

I. Introduction There's an interesting discussion going on now in an Experts Exchange Group — Attachments with no extension (http://www.experts-exchange.com/discussions/210281/Attachments-with-no-extension.html). This reminded me of questions tha…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
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.:
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.

623 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