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

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

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

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

Ouch!

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

Bill

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

Are they doing select * ????

Cheers
Sathyaram

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

I extracted this from a document I found online...
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

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.

Damn that memory!