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]

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**********@mi kropis.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