Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Mass Table Structure Modification

    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.
    Last edited by DBFinder; 06-04-09 at 08:37.

  2. #2
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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.

    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
    Last edited by DBFinder; 06-04-09 at 13:15.

  4. #4
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  9. #9
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  10. #10
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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.

  11. #11
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  12. #12
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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.
    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

  13. #13
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    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?
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  14. #14
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  15. #15
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •