Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Location
    India
    Posts
    4

    Unanswered: Referential Itegrity

    Hello All,

    Actually in our shop...we are using DB2 V7 on Z/OS on mainframe.

    I have a question regardin Add a coulumn in the middle of the table
    which has Referential Integrity.

    I have Parent table and child table. As per the request I have to add a coulun in the middle of the table......could you please let me know the steps to add the column.

    I am just following the steps bellow.

    To add a column in the middle of the table. the only way is to drop and recreate the table. but before droping I have to take care of effects.

    also I have a question that if I drop the parent table I hope RI will be droped becasue there will not be any primary key to build a relation ship with foriegn key. is true.??

    please let me know the steps.

    Thanks
    Kumar

  2. #2
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    15
    Hi,

    of ocurse you can add a column to an existing table you don't need to drop it first. for this task use the sql command ALTER TABLE. with the same command you can add or drop a RI.

    kirna

  3. #3
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    Re: Referential Itegrity

    Hi!

    Hmmh. ALTER TABLE ADD column is the right way to it....
    But why in earth do you want to ...To add a column in the middle of the table. "! If there are any real PHYSICAL reason, then dropping the table is mandatory.

    One way to add columns middle in the table is to use VIEWS:

    ALTER TABLE ADD new_column...

    CREATE VIEW tablename_v2 AS
    SELECT old_col1, new_column, old_col2
    FROM tablename


    Cheers, Bill

  4. #4
    Join Date
    Mar 2004
    Location
    India
    Posts
    4

    Re: Referential Itegrity

    Originally posted by hurmavi
    Hi!

    Hmmh. ALTER TABLE ADD column is the right way to it....
    But why in earth do you want to ...To add a column in the middle of the table. "! If there are any real PHYSICAL reason, then dropping the table is mandatory.

    One way to add columns middle in the table is to use VIEWS:

    ALTER TABLE ADD new_column...

    CREATE VIEW tablename_v2 AS
    SELECT old_col1, new_column, old_col2
    FROM tablename


    Cheers, Bill


    Hi Bill,

    Thank you.......

    But request is they want in the middle of the table to use in the application...so I have to add a column in the middle of the table.

    offcourse View is one way but since I have to add in the table itself.

    I think I have to drop a table. could you please let me know what are steps do it. ???

    Thanks
    Ravi

  5. #5
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    Re: Referential Itegrity

    Originally posted by ravibh
    Hi Bill,

    Thank you.......

    But request is they want in the middle of the table to use in the application...so I have to add a column in the middle of the table.

    offcourse View is one way but since I have to add in the table itself.

    I think I have to drop a table. could you please let me know what are steps do it. ???

    Thanks
    Ravi
    Right!

    I still think, that it's a silly request. But here's how to do it:

    -- create a work table where to put your data
    CREATE TABLE work_table LIKE table;
    COMMIT;

    -- copy all data to work table
    INSERT INTO work_table
    SELECT * FROM table;
    COMMIT;

    -- drop and re-create table
    DROP TABLE table;
    COMMIT;
    CREATE TABLE table (
    old_col_01 char(1),
    new_col char(1),
    old_col_02 char(1) );
    COMMIT;

    -- copy all data from work table to table
    INSERT INTO table
    SELECT old_col_01, 'your default', old_col_02
    FROM work_table;

    -- get rid of work table
    DROP TABLE work_table;

    SELECT * FROM table;

    There are better ways to the copying, but this is a 'lazy man version'.

    Cheers, Bill

  6. #6
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64

    Angry Re: Referential Itegrity

    Ouch!

    You must include the child table (all child tables?) to the DDL, too.

    Bill

  7. #7
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: Referential Itegrity

    Why does the application developer think he wants a column in the middle of the table ...

    Are they doing select * ????

    Cheers
    Sathyaram


    Originally posted by ravibh
    Hi Bill,

    Thank you.......

    But request is they want in the middle of the table to use in the application...so I have to add a column in the middle of the table.

    offcourse View is one way but since I have to add in the table itself.

    I think I have to drop a table. could you please let me know what are steps do it. ???

    Thanks
    Ravi
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  8. #8
    Join Date
    Feb 2004
    Posts
    25

    Re: Referential Itegrity

    I agree the column probably does not need to be added in the middle of the table, it's just so the programmer can get his brain around it better. I have had this battle with my programmers also.

    If you do drop the table, you will also need to redefine indexes, primary and foreign keys, and GRANTs done for any user IDs, etc.... You will also have to run the check utility after you recreate the foreign keys.

  9. #9
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525

    Re: Referential Itegrity

    I agree the column probably does not need to be added in the middle of the table, it's just so the programmer can get his brain around it better. I have had this battle with my programmers also.

    If you do drop the table, you will also need to redefine indexes, primary and foreign keys, and GRANTs done for any user IDs, etc.... You will also have to run the check utility after you recreate the foreign keys.
    I remember being told a while ago that the order of columns can be very important in terms of peformance, specifically updates. From memory the logic behind it was that highly mutable fields (i.e. those regularly updated) should be at the beginning of the row because presumably they are more easily accessed there (?). Also varchars should be positioned at the end of the row as due to their variable length, if they are updated to a different length this would involve the remainder of the record also being modified.

    Not sure which version(s) this applied to or even if it still applies to any version.

    Can anyone clear this up?

    Damian

  10. #10
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525

    Re: Referential Itegrity

    I extracted this from a document I found online...
    Guidelines for DB2 Column Sequence in the Row
    Place the primary key first.
    Place frequently read columns next.
    Place infrequently read and infrequently updated columns next
    Place VARCHAR and VARGRAPHIC columns next
    Place very frequently updated columns after variable columns
    For varying length rows, DB2 logs updates from the point of the change to the end of the row.
    For fixed length rows (that is, no VARCHAR or VARGRAPHIC columns), frequently updated columns can be placed anywhere in the table because DB2 will log updates from the begin point of the change to the end point.
    Given these guidelines, try to sequence the columns in an order that makes sense to the users of the table.
    It seems to reference several versions of DB2 but I'm sure the guidelines above are not generic.

    http://www.oti.fsu.edu/dba/2003_Data...ordDocAug7.doc
    Last edited by Damian Ibbotson; 04-01-04 at 14:47.

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    There is probably some difference between DB2 for OS/390 and DB2 for UNIX, Linux, and Windows in how data is physically stored.

    DB2 for OS/390 logs row changes (updates) from the point where the change occurred to the end of the row. So frequently updated columns should be at the end of the row, not the beginning.

    DB2 for UNIX, Linux and Windows automatically places varchar columns physically at the end of the row when a table is created. I don't believe that DB2 for OS/390 automatically does that (unless they have changed it in last 5 years).

    The main issue is logging, so if the amount of updates is relatively low, it is not a big factor. DB2 will log the entire row for inserts and deletes.

    Also important is when updating a varchar column with a larger value. In that case, the row may not fit back in the same place, or even on the same page, so it needs to be relocated to a new page. This can cause havoc it happens with a mass update.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    DB2 for OS/390 logs row changes (updates) from the point where the change occurred to the end of the row. So frequently updated columns should be at the end of the row, not the beginning.
    Damn that memory!

Posting Permissions

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