Mass Table Structure Modification

DBFinder · #1 (**permalink**) 06-04-09, 07:32

Hi,

DB2 WSE V 9.5 Fixpack 0 on Win 2k3 servers.

We need to modify PLYAER_ID from CHAR(14) to VARCHR (22)

There are 126 tables in POS db and 212 tables in GAMING db.

How should I do it. Write a script. or manually using CC.

Or any other utility that I may never have used ?

What about the down time ?

As I know modifying one field size in one table results in tablespace backup required.

Later We will change data in offline mode to rerformat the existing PLAYER_ID.

Please advise.

DBFinder.

Cougar8000 · #2 (**permalink**) 06-04-09, 09:44

write a script that will pick out all of your tables from syscat and pipe output to a file that will contain your ALTER statement. then run that statement.

Never heard of needed to do a backup after ALTER column. It is somewhat recommended to reorg it.

DBFinder · #3 (**permalink**) 06-04-09, 12:12

That's right.

The problem is that some times it recommend REORG and some times not.

And REORGs ome times cannot be done online. See following.

Quote:

db2 alter table trans_log alter column player_id set data type VARCHAR(20)
DB20000I The SQL command completed successfully.

db2 select count(*)db from trans_log

1
-----------
SQL0668N Operation not allowed for reason code "7" on table
"DB2ADMIN.TRANS_LOG". SQLSTATE=57016

db2 reorg table trans_log inplace allow write access
SQL2216N SQL error "-668" occurred while reorganizing a database table or its indexes.

Is there a way to know beforehand which tables will be require REORGS.

so that I can avoid doing reorgs for now and change tables only that won't require REORGs.

Thanks
DBFinder

Cougar8000 · #4 (**permalink**) 06-04-09, 12:28

what ver are you running?

what is your tab layout before?

I am on 9.1 aix and have no issue

Code:

db2 describe table  TYP_ID_LKP

Column                         Type      Type
name                           schema    name               Length   Scale Nulls
------------------------------ --------- ------------------ -------- ----- ------
TYP_ID                         SYSIBM    BIGINT                    8     0 No    
TYP_DESC                       SYSIBM    CHARACTER                30     0 Yes   

  2 record(s) selected.

db2 "alter table TYP_ID_LKP alter column TYP_DESC set data type varchar(40)"
DB20000I  The SQL command completed successfully.

db2 "select count(*) from TYP_ID_LKP"

1          
-----------
          3

  1 record(s) selected.

Cougar8000 · #5 (**permalink**) 06-04-09, 12:33

Thank you for making me read

SQL Ref 2

A REORG-recommended operation has occured when changes resulting from an ALTER TABLE statement affect the row format of the data. When this occurs, most subsequent operations on the table are restricted until a table reorganization operation completes successfully.

DBFinder · #6 (**permalink**) 06-04-09, 12:35

Probably you tried on small table.

It succeeded on two tables and on third table it required REORGS.

I indicated my versions already DB2V9.5 FP 0 on Windows 2003 server

DBFinder

DBFinder · #7 (**permalink**) 06-04-09, 12:38

Thanks for research for my problem,

Actually I have been through these kind of modifications once a while, but on 1 or 2 tables at a time.

Some do not require REORGs. And Some DO.

I am more concerned because changing over 100 tables may put me in situation where I might reorg 50 or more tables. An previous one tells me that these cannot be REORGed online.

Any Idea how to know that this table will need REORG after modification.

Thanks

ARWinner · #8 (**permalink**) 06-04-09, 13:08

Quote:

Originally Posted by DBFinder

Any Idea how to know that this table will need REORG after modification.

Thanks

Try looking here:

IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

Andy

Cougar8000 · #9 (**permalink**) 06-04-09, 13:21

the way I understand is if reorg forces your records to span data page that it is currently on you will have to reorg.

I would solve this problem with shell script(going back kind of what I was proposing earlier) in your script do a check if a reorg is needed on a table you just changed. If yes, then do an offline reorg, if not, go and change the next table.

And yes, I would do it during the down time.

DBFinder · #10 (**permalink**) 06-04-09, 13:32

Good, that makes sense,

In the script, I won't have chance to do while outage.
I am willing to know by some means so that I can seperate the list.
Few that won't need reorgs, and others that will need.

The management needs answer to these questions befor schedualling the outage.

Only thing that I can pay with is a Staging databae where I can try commands for test. And that database is much smaller having almost no activities.

ARWinner,
Thanks
I am trying it now.

Cougar8000 · #11 (**permalink**) 06-04-09, 13:41

like it or not you HAVE TO do it during the outage. If you do not and users will try using those few tables your phone will go nuts.

DBFinder · #12 (**permalink**) 06-04-09, 13:55

I understand, only point is to figure out some tables that can be done without reors reqd.

This will help reduce downtime.

ARWinner,

I saw and tried that table function.

Quote:

REORG_PENDING CHAR(1) -- A value of 'Y' indicates that a reorg recommended alter has been applied to the table and a classic (offline) reorg is required. Otherwise 'N' is returned.

This gives me STATE after ALTER.
Once this flag is set, I will have no way to escape.

I am trying to find, if possible, to know before applying ALTER .

Thanks anyway

DBFinder

Cougar8000 · #13 (**permalink**) 06-04-09, 14:01

Quote:

Originally Posted by DBFinder

This will help reduce downtime.

Scripting your shell script to check the status and ignore those that do not need to will reduce your down time.

Why bother trying to identify now something that shell script will take care of when you do the job? what am I missing?

dav1mo · #14 (**permalink**) 06-05-09, 08:56

DBFinder,
How about performing the alters on a redirected restore of your system ( a test machine) and see which ones need reorging that way. You could, also, then perform the reorgs offline to see how much downtime you will need.

Dave Nance

DBFinder · #15 (**permalink**) 06-05-09, 09:16

Dave,

You are right, I found same so far.

Actually we do have a machine , a Mirror to that subject server.

I hope I will try that today, provided I get hold of it today ( sometimes it is used for reports).

I will get back with results ( or findings ).

Thanks Dave,

DBFinder

Internet Services

Web Development

Digital Marketing

Consumer Tech