472,791 Members | 1,567 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,791 software developers and data experts.

tablespace move from 32K to 4K page size on same instance

How to accomplish a tablespace move from 32K to 4K page size on AIX
5.2, DB2 UDB 8.2?

Enable forward recovery, take 32K tablespace backup, drop 32K
tablespace, create 4K tablespace, restore to 4K tablespace from the
backup, reset back to circular logging. Would this work?

Nov 12 '05 #1
6 3643
Ian
hi****@gmail.com wrote:
How to accomplish a tablespace move from 32K to 4K page size on AIX
5.2, DB2 UDB 8.2?

Enable forward recovery, take 32K tablespace backup, drop 32K
tablespace, create 4K tablespace, restore to 4K tablespace from the
backup, reset back to circular logging. Would this work?


No, you can't change page size. You have to export data, save DDL,
drop/create tablespaces, recreate objects, load data.

Nov 12 '05 #2
Why would you "reset back to circular logging"? Don't you need recovery? If
not, why not just drop everything and start from scratch?

Export, drop, create, load. Implement proper logging and log archiving.

<hi****@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
How to accomplish a tablespace move from 32K to 4K page size on AIX
5.2, DB2 UDB 8.2?

Enable forward recovery, take 32K tablespace backup, drop 32K
tablespace, create 4K tablespace, restore to 4K tablespace from the
backup, reset back to circular logging. Would this work?

Nov 12 '05 #3
hi****@gmail.com wrote:
How to accomplish a tablespace move from 32K to 4K page size on AIX
5.2, DB2 UDB 8.2?


Create a new table with the same structure in the 4K tablespace, copy the
data, drop the old table and rename the new one.

Assuming you want to change table T:

CREATE TABLE T_new LIKE T
INSERT INTO T_new SELECT * FROM T
DROP TABLE T
RENAME TABLE T_new TO T

You will also have to take care of indexes and constraints like
primary/foreign keys.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #4
We do a lot of full replace loads in batch, recovery logging forces the
tables to load or backup pending(forgot which one). I think I can
specify "COPY NO" or "NON RECOVERABLE" to avoid this, but we have'nt
taken that route yet. Please confirm that these will get rid of that
"load or backup pending" issue.

Thanks for your tips!!

Nov 12 '05 #5
Ian
hi****@gmail.com wrote:
We do a lot of full replace loads in batch, recovery logging forces the
tables to load or backup pending(forgot which one). I think I can
specify "COPY NO" or "NON RECOVERABLE" to avoid this, but we have'nt
taken that route yet. Please confirm that these will get rid of that
"load or backup pending" issue.


NONRECOVERABLE will prevent DB2 from putting the tablespace in backup
pending, but of course you won't be able to roll forward through the
load operation.

Nov 12 '05 #6
no more since Version 8. (online load)
<hi****@gmail.com> schrieb im Newsbeitrag
news:11**********************@o13g2000cwo.googlegr oups.com...
We do a lot of full replace loads in batch, recovery logging forces the
tables to load or backup pending(forgot which one). I think I can
specify "COPY NO" or "NON RECOVERABLE" to avoid this, but we have'nt
taken that route yet. Please confirm that these will get rid of that
"load or backup pending" issue.

Thanks for your tips!!

Nov 12 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: sandeep G | last post by:
I've a table which has a number & a blob column, both of which are NOT NULL type. This table is composite partitioned using range & hash on the same column. Each partition is sub partitioned into...
1
by: xixi | last post by:
There is one thing I don't understand, I have default tablespace userspace1 is database managed type, with total freepages 506048. another one called tmpxx is DMS type too, with 525120 free pages....
2
by: Patrick Tan | last post by:
I have a tablespace which is approaching 64 GB (for a single table), i understand that for 4k page sized tablespaces, this is sized tablespace, what's the best way / fastest way to do it ? can...
4
by: mairhtin o'feannag | last post by:
Hello, I have a tablespace striped across three drives, call them 1,2,3, just to be clever. :) I allocated a lot more space (DMS) than I should have, since I didn't know a way to estimate the...
2
by: Kums | last post by:
What is the maximum permissible size of a database? Is there any limitation. What is the maximum # of tablespace's allowed in a database? Thanks for your response.
1
by: Bruce Pullen | last post by:
db27.2 (fp13) on AIX 5.2. The following error is generated when attempting a reorg or, more precisely, when the file associated with the reorg tablespace reaches 1Gb. SQL2216N SQL error...
2
by: Pablo | last post by:
I have observed a significant discrepancy between the amount of space used in a long tablespace when using import as compared to load. Can anyone explain the following? In an attempt to move...
2
by: db2dude | last post by:
Hello all, I have a database around 600GB and all tables were self-created by the application. Hence, we have only one huge SMS tablespace where all the tables are stored. To get better...
4
by: Alvin SIU | last post by:
Hi all, I am now doing development using DB2. Q1. There is one tablespace to store 5 tables. But, when in production, there will be one tablespace for EACH of the tables.
0
by: Rina0 | last post by:
Cybersecurity engineering is a specialized field that focuses on the design, development, and implementation of systems, processes, and technologies that protect against cyber threats and...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 2 August 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: erikbower65 | last post by:
Using CodiumAI's pr-agent is simple and powerful. Follow these steps: 1. Install CodiumAI CLI: Ensure Node.js is installed, then run 'npm install -g codiumai' in the terminal. 2. Connect to...
0
linyimin
by: linyimin | last post by:
Spring Startup Analyzer generates an interactive Spring application startup report that lets you understand what contributes to the application startup time and helps to optimize it. Support for...
0
by: kcodez | last post by:
As a H5 game development enthusiast, I recently wrote a very interesting little game - Toy Claw ((http://claw.kjeek.com/))。Here I will summarize and share the development experience here, and hope it...
14
DJRhino1175
by: DJRhino1175 | last post by:
When I run this code I get an error, its Run-time error# 424 Object required...This is my first attempt at doing something like this. I test the entire code and it worked until I added this - If...
5
by: DJRhino | last post by:
Private Sub CboDrawingID_BeforeUpdate(Cancel As Integer) If = 310029923 Or 310030138 Or 310030152 Or 310030346 Or 310030348 Or _ 310030356 Or 310030359 Or 310030362 Or...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: lllomh | last post by:
How does React native implement an English player?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.