Hello,

i want to insert a new row at the end of a table, but if i delete a row before, the DBMS I think, will insert the new row at that position where the the row before was deleted.

is it possible to insert the row every time at the end

thanks

Stefan

sry for my english

You can always insert new rows at the end of the table by doing the following:

alter table <table-name> append;

However, when you select rows from the table without an “order by,” there is no guarantee the last row in the table (physically the last row) will be the last row returned in your answer set with an SQL select statement.

Relational theory requires that if you want rows returned in a particular order, you must use the "order by" clause.

The advantage of “append” is that DB2 will not spend a lot of time looking for free space within the table to insert new rows.

thank you, i read about append on IBM hp and i think it would solve the problem...
but i can't execute the statement with Zend Studio 5.5 (syntax error):

ALTER TABLE HSL.LOHNLISTE2 append on


is it possible to create a new table with apend on? (without alter table syntax)

Its very much possible. Just append the ALTER TABLE option immediately after the CREATE TAB statement. If your existing tables are not fully loaded, you can definitely choose the following option.


CREATE TABLE TEST.TB_NEW1 (
SOURCETYP CHARACTER(1) NOT NULL,
TRANTYP CHARACTER(1) NOT NULL DEFAULT ' '
)
IN TS_DATA1
INDEX IN TS_INDX1
;
ALTER TABLE TEST.TB_NEW1 APPEND ON


Thanks,
Jayanta

Maybe there is a misconception here. The rows in a table do not have any ordering. You cannot rely on anything, unless you specify an ORDER BY in your query.

The APPEND option mentioned here has only an impact on the physical storage of the rows. It does not guarantee a specific row order in queries.

I hope that is clear to everyone that while querying DB2 will always return the pages in a random way if you are not ordering them. But the question which is asked here on the top by Stefan is "is it possible to insert the row every time at the end? " May be for that APPEND ON seems to be perfect as Marcus A has suggested.

Sure. The question is whether the OP was really referring to the physical order of rows in the table space or the order in which rows are returned by queries. There are a lot of people who confuse both things.

Why are you concerned with the placement? It doesn't sound like this is due to any performance type issue, so why would it matter?
Dave