Adding new column to a table

[annecarterfredi]

I'd like to add a new column to an existing table at a specific column
location...

Existing table definition:
MyTable(Col1, Col2, Col3)

I want to add a new column in MyTable and the new column's position is
in between Col2 and Col3:
MyTable(Col1, Col2, New_Column, Col3)

"Alter Table MyTable Add Column ..." statement adds the column to the
end (i.e. MyTable(Col1, Col2, Col3, New_Column) ).
I can drop the table and recreate it, but I would like to know whether
it can be done in any other way. Thanks for your help.
A Carter

[Serge Rielau]

an*************@gmail.com wrote:

I'd like to add a new column to an existing table at a specific column
location...

Existing table definition:
MyTable(Col1, Col2, Col3)

I want to add a new column in MyTable and the new column's position is
in between Col2 and Col3:
MyTable(Col1, Col2, New_Column, Col3)

"Alter Table MyTable Add Column ..." statement adds the column to the
end (i.e. MyTable(Col1, Col2, Col3, New_Column) ).
I can drop the table and recreate it, but I would like to know whether
it can be done in any other way. Thanks for your help.
A Carter

Why would you want any other way than ALTER ADD?
It's instantanious and online....

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Knut Stolze]

an*************@gmail.com wrote:

I'd like to add a new column to an existing table at a specific column
location...

Existing table definition:
MyTable(Col1, Col2, Col3)

I want to add a new column in MyTable and the new column's position is
in between Col2 and Col3:
MyTable(Col1, Col2, New_Column, Col3)

"Alter Table MyTable Add Column ..." statement adds the column to the
end (i.e. MyTable(Col1, Col2, Col3, New_Column) ).
I can drop the table and recreate it, but I would like to know whether
it can be done in any other way. Thanks for your help.

A table is comprised of a set of columns. Therefore, the column order is
completely irrelevant conceptually. You can choose any order of the
columns in your query.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[annecarterfredi]

Thanks for replies. Here is the situation...

I have audit column (for example, update_user_id) in every table in the
database. I'd like to keep this audit column as the last column when I
execute "describe table TableName" or "select * from TableName".

But, when I add a new column to a table, it is added after the last
column...is it possible to 'insert' the new column in between?

Thanks
A Carter

[Antonio Cangiano]

Knut Stolze wrote:

A table is comprised of a set of columns. Therefore, the column order is
completely irrelevant conceptually. You can choose any order of the
columns in your query.

For example, if you have MyTable(Col1, Col2, Col3, New_Column) you can
query it through 'SELECT Col1, Col2, New_Column, Col3 FROM MyTable'
obtaining the resultset with the columns ordered in the way you
requested. And you can choose any order as Knut suggested.

Regards,
--
Antonio Cangiano
IBM Toronto Lab

http://antoniocangiano.com
Zen and the Art of Ruby Programming

[John Bailo]

Knut Stolze wrote:

A table is comprised of a set of columns. Therefore, the column order is
completely irrelevant conceptually. You can choose any order of the
columns in your query.

Does it make a difference for the primary key?
--
Texeme Construct

[Knut Stolze]

John Bailo wrote:

Knut Stolze wrote:

>A table is comprised of a set of columns. Therefore, the column order is
completely irrelevant conceptually. You can choose any order of the
columns in your query.

Does it make a difference for the primary key?

No. Why should it?

If I remember correctly, there was the suggestion to have all fixed-length
columns at the beginning and all variable ones at the end. The advantage
is that the access to the fixed length data is faster because the position
of the data is known right away and you don't have to traverse over the
variable length stuff. If this still holds true with V8 and V9, I do not
know.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[John Bailo]

Knut Stolze wrote:

>>Does it make a difference for the primary key?

No. Why should it?

If I remember correctly, there was the suggestion to have all fixed-length
columns at the beginning and all variable ones at the end. The advantage
is that the access to the fixed length data is faster because the position
of the data is known right away and you don't have to traverse over the
variable length stuff. If this still holds true with V8 and V9, I do not
know.

Well, say there are three columns, FieldA, FieldB, FieldC, physically
laid out in that order.
If I choose FieldA and FieldC, then to create the index it will have to
scan across FieldB each time to get to FieldC.

Whereas if I choose FieldA and FieldB, it will be a best case scan --
OR, if I layout the table as FieldA, FieldC, FieldB in the column order.
--
Texeme Construct

[Serge Rielau]

John Bailo wrote:

Knut Stolze wrote:

>>Does it make a difference for the primary key?

No. Why should it?

If I remember correctly, there was the suggestion to have all
fixed-length
columns at the beginning and all variable ones at the end. The advantage
is that the access to the fixed length data is faster because the
position
of the data is known right away and you don't have to traverse over the
variable length stuff. If this still holds true with V8 and V9, I do not
know.

Well, say there are three columns, FieldA, FieldB, FieldC, physically
laid out in that order.
If I choose FieldA and FieldC, then to create the index it will have to
scan across FieldB each time to get to FieldC.

Whereas if I choose FieldA and FieldB, it will be a best case scan --
OR, if I layout the table as FieldA, FieldC, FieldB in the column order.

There is no such thing as scanning within a row.
DB2 does I/O at least in pages which can each contain up to 2000 rows
(in DB2 9).
There is exactly one good reason to worry about teh order of column in a
table: logging. When a row gets updated DB2 only logs from the start of
the first change to the end of the last. So, if you often update two
columns in a table and they happen to be the last and the first column
in the table definition, that would be increase the size of the log file.
In reality customers do NOT worry about that sort of thing. In TPC-C
where 0.5% difference mean a lot, yes we do worry.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Knut Stolze]

Serge Rielau wrote:

There is no such thing as scanning within a row.
DB2 does I/O at least in pages which can each contain up to 2000 rows
(in DB2 9).

Question: assuming you have this table structure:

col1 INT
col2 VARCHAR
col3 INT

Now you have "SELECT col3 FROM t". DB2 will have to skip "col1" and "col2"
to find the value of "col3", won't it? So that means there is some
scanning, although it is in memory and I really doubt that it makes a
noticeable difference.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany

[Serge Rielau]

Knut Stolze wrote:

Serge Rielau wrote:

>There is no such thing as scanning within a row.
DB2 does I/O at least in pages which can each contain up to 2000 rows
(in DB2 9).

Question: assuming you have this table structure:

col1 INT
col2 VARCHAR
col3 INT

Now you have "SELECT col3 FROM t". DB2 will have to skip "col1" and "col2"
to find the value of "col3", won't it? So that means there is some
scanning, although it is in memory and I really doubt that it makes a
noticeable difference.

"Will have to"? There are other strategies than linked lists...
Now, unless anyone reading this thread is planning to write their own
LOAD utility an answer to this really yields no practical value.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/

[Gregor Kova�]

Knut Stolze wrote:

an*************@gmail.com wrote:

>I'd like to add a new column to an existing table at a specific column
location...

Existing table definition:
MyTable(Col1, Col2, Col3)

I want to add a new column in MyTable and the new column's position is
in between Col2 and Col3:
MyTable(Col1, Col2, New_Column, Col3)

"Alter Table MyTable Add Column ..." statement adds the column to the
end (i.e. MyTable(Col1, Col2, Col3, New_Column) ).
I can drop the table and recreate it, but I would like to know whether
it can be done in any other way. Thanks for your help.

A table is comprised of a set of columns. Therefore, the column order is
completely irrelevant conceptually. You can choose any order of the
columns in your query.

Order of columns is very important when you do EXPORT/IMPORT. Yes, you can
remap them, but still.

Best regards,
Kovi
--
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-
| Gregor Kovac | Gr**********@mikropis.si |
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
| In A World Without Fences Who Needs Gates? |
| Experience Linux. |
-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

[P. Adhia]

Serge Rielau wrote:

When a row gets updated DB2 only logs from the start of
the first change to the end of the last.

Is this an improvement in DB2 V9, or DB2 has had this feature for a
while?

Also would you please explain on what gets logged for changes done to
indexes? If index changes do not get logged, then is it correct to
assume DB2 effectively inserts/updates/deletes index entries during a
roll-forward (and roll-back) operation, just as if changes to table
were done using SQL DML?

TIA

P Adhia

[Knut Stolze]

Gregor Kova� wrote:

Order of columns is very important when you do EXPORT/IMPORT. Yes, you can
remap them, but still.

No, not really because you can specify into which columns you want to import
and also the order of those columns. Only if you rely on implicit things,
then the order is relevant.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany