Hi Guys,

This is first time that I need to Extend a column size such that it has to be moved to bigger page size tablespace.

The table is quite big 28GB.

If I export and import it will require long time. But database cannot be scheduled such a big offline time.

Estimated offline time will be 45 minutes to 65 minutes. This is not approvable in production.

What are my other options.

1. Load from cursor. or what ? 2. Can RENAME TABLE be used somehow ??


Thanks

DBFinder

DBFinder, Not sure if this will help much but a Load is faster than Import. I can't tell you how much faster but it would make you downtime less.

What type of activity is going on with the table? Select only? Inserts? Updates?

If it is Inserts or Updates, can you identify the rows changed (changed timestamp column maybe?)?

Rename table may be able to help you but there are quite a few rules (restrictions) associate with it.

create another table in a new TS (with bigger page size) and copy data. Your source table will be available all the time - no outage.

You would have to have an outage one way or another, even if it is a brief one to rename this table.

If you can find a time when this table is a read only, you can create a new copy; use LOAD FROM CURSOR; Rename old; rename new and you are done.

If not. you would have to find a way to identify those new records.

Well, the rename is done in a transaction (like all DDL). So you could do the renaming and DB2 will take care of the locking - no explicit outage is required.

(Note that other DBMS do not have transaction concepts for DDL statements.)

The problem is once the data is copied from the original table to the new/larger page size table (or even while the data is being copied if it is Read/Write), any changes to the original table will either be lost (if you just rename the tables) or have to be dealt with (applied to the new table). The only two choices I can see are:

A) Put the original table in Read Only so NO changes are made from the point the copy starts until the tables are renamed.

B) Leave the original table in Read/Write while the copy is processing, but you have to have a way to identify any of the changes that are being made and are NOT in the new table. This could be Inserts (missing rows), Deleted (extra rows) or Updates (changed rows). These would have to be identified and, prior to applying them to the new table, the original table would need to be in Read Only. (this is basically how the on-line reorgs work).

B has the least amount of down time as it shouldn't take too long to get the changes applied to the new table and rename the tables.

One way to keep track of the insert/update/delete activity is to set up triggers to put those affected rows into other table(s), then import that data to your new version of the table for the catch up. I've done similar on projects on both LUW and Z/OS. We used 3 shadow copies one for each type of activity. When we had 10 minute down time, renamed tables, inserted data from the insert shadow, updated data from the update shadow and, finally, deleted data from the delete shadow. Actually, only took about 3.5 minutes to finish up.
Dave

Excellent suggestion, Dave.

Dave,

This was what I was waiting for, But your response was little late. we did it with 1.4 hrs down time. Table was 28 GB and database took 1 hr 05 minutes to LOAD FROM CURSOR,

Your experience was great and this was exactly I thought, but having no exp. doing it myself, I avoided to take risk.

But your input did not get waste. I will try this in Staging for a hands on try.

I appreciate you input.

Thanks

DBFinder