Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2008
    Posts
    2

    Unanswered: problem with deleting rows

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2008
    Posts
    2
    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)

  4. #4
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    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
    Last edited by JAYANTA_DATTA; 11-28-08 at 08:35.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    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.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

Posting Permissions

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