insert/select question

[K.Fawcett]

I am doing a simple insert into a table and I would like the data to be
retrieved in the order it is inserted not by what appears to be
alphabetical order.

insert into mytbl values('c')
insert into mytbl values('b')
insert into mytbl values('a')

select * from mytbl yields

a
b
c

I would like the select to yield
c
b
a
(the order in which the data was inserted)

What is db2 doing with the ordering during insert?

[Mark A]

"K.Fawcett" <k.*******@mccsoftware.com> wrote in message
news:11*********************@l41g2000cwc.googlegro ups.com...

I am doing a simple insert into a table and I would like the data to be
retrieved in the order it is inserted not by what appears to be
alphabetical order.

insert into mytbl values('c')
insert into mytbl values('b')
insert into mytbl values('a')

select * from mytbl yields

a
b
c

I would like the select to yield
c
b
a
(the order in which the data was inserted)

What is db2 doing with the ordering during insert?

The only way would be to define a timestamp column on the table and let DB2
default to the "current timestamp" when the data is inserted. Then you could
use the ORDER BY clause to retrieve the rows by the timestamp column.

By definition, a relational database does not guarantee the rows will be
returned in any particular order unless you specify the order with ORDER BY.

[K.Fawcett]

Thanks for you reply. An 'ORDER BY' clause was never required by this
table until a customer wanted to do something new. I will probably
need to add a new field to support this. I guess what threw me is that
the results of a 'SELECT' query always appear alphabetized (at least
for the queries I performed). From what you are saying I should expect
to see random ordering in my results. I accept this, but is there a
reason that I would never experience it? Here is my query and my
result:
E:\src\univ\bmz>DB2 SELECT DISP_NAME,DISP_SYM FROM MCCTOOLS.DISP_TBL

allout.qry

E:\src\univ\bmz>DB2 SELECT PROG_ID,DISP_NAME,DISP_SYM FROM
MCCTOOLS.DISP_TBL WHERE PROG_ID='RX6099B3'

PROG_ID DISP_NAME
DISP_SYM
-------------------------------- --------------------------------
--------
RX6099B3 00_APASS .
RX6099B3 000_APASS .
RX6099B3 01_BUILD B
RX6099B3 02_LBOPA 1
RX6099B3 03_XARRYD 8
RX6099B3 04_LOGIC 7
RX6099B3 05_LBBYP 6
RX6099B3 06_EIDC 5
RX6099B3 07_IOW N
RX6099B3 08_XARRYA Z
RX6099B3 09_WOP1 2
RX6099B3 10_WOP2 3
RX6099B3 11_OLSSD W
RX6099B3 12_POST 9
RX6099B3 13_FAILSTEP K

15 record(s) selected.

[Mark A]

"K.Fawcett" <k.*******@mccsoftware.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Thanks for you reply. An 'ORDER BY' clause was never required by this
table until a customer wanted to do something new. I will probably
need to add a new field to support this. I guess what threw me is that
the results of a 'SELECT' query always appear alphabetized (at least
for the queries I performed). From what you are saying I should expect
to see random ordering in my results. I accept this, but is there a
reason that I would never experience it? Here is my query and my
result:
E:\src\univ\bmz>DB2 SELECT DISP_NAME,DISP_SYM FROM MCCTOOLS.DISP_TBL

allout.qry

E:\src\univ\bmz>DB2 SELECT PROG_ID,DISP_NAME,DISP_SYM FROM
MCCTOOLS.DISP_TBL WHERE PROG_ID='RX6099B3'

PROG_ID DISP_NAME
DISP_SYM
-------------------------------- --------------------------------
--------
RX6099B3 00_APASS .
RX6099B3 000_APASS .
RX6099B3 01_BUILD B
RX6099B3 02_LBOPA 1
RX6099B3 03_XARRYD 8
RX6099B3 04_LOGIC 7
RX6099B3 05_LBBYP 6
RX6099B3 06_EIDC 5
RX6099B3 07_IOW N
RX6099B3 08_XARRYA Z
RX6099B3 09_WOP1 2
RX6099B3 10_WOP2 3
RX6099B3 11_OLSSD W
RX6099B3 12_POST 9
RX6099B3 13_FAILSTEP K

15 record(s) selected.

I would not say that the results would be totally random, but there is no
guarantee of the order unless you use an ORDER BY. If the rows were
physically stored in a certain order, then DB2 "might" return them in that
order. But there is no guarantee.

Also consider that DB2 sometimes inserts new rows in the middle of a table,
and sometimes at the end, depending on whether there is a clustering index
defined and where space is available (although if append option is used, it
always inserts new rows at the end).

This definition of a relational database is that the user does not know or
care about how the data is physically stored. If the user wants the rows
returned in a particular order, then the user MUST request that order.

A database product is considered to be relational, to the extent to which it
isolates the users from the way the data is physically stored. No database
is completely isolated (and no database is completely relational), but the
physical ordering of rows in an answer set is one aspect where DB2 is fully
relational.

[ak_tiredofspam]

>> The only way would be to define a timestamp column on the table and
let DB2
default to the "current timestamp" when the data is inserted. Then you
could
...<<

might not work on a powerful server - many rows could have one and the
same timestamp. using a sequence or an identity column is another
alternative

[Serge Rielau]

ak************@yahoo.com wrote:

The only way would be to define a timestamp column on the table and

let DB2
default to the "current timestamp" when the data is inserted. Then you
could
..<<

might not work on a powerful server - many rows could have one and the
same timestamp. using a sequence or an identity column is another
alternative

Use TIMESTAMP(GENERATE_UNIQUE()) or use an IDENTITY column.
Also note that CURRENT TIMESTAMP is constant per statement.
That is dups will occur for multi-row inserts.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[efiryago]

Use SELECT ... FROM FINAL TABLE that (according to the DB2 doc, AFAIK),
returns row in the order the table was inserted.

db2 => CREATE TABLE mytbl(c CHAR(1))
DB20000I The SQL command completed successfully.
db2 => SELECT c FROM FINAL TABLE( INSERT INTO mytbl(c) VALUES
('c'),('b'),('a') )

C
-
c
b
a

3 record(s) selected.

-Eugene

[Serge Rielau]

ef******@gmail.com wrote:

Use SELECT ... FROM FINAL TABLE that (according to the DB2 doc, AFAIK),
returns row in the order the table was inserted.

db2 => CREATE TABLE mytbl(c CHAR(1))
DB20000I The SQL command completed successfully.
db2 => SELECT c FROM FINAL TABLE( INSERT INTO mytbl(c) VALUES
('c'),('b'),('a') )

C
-
c
b
a

3 record(s) selected.

-Eugene

Actually it doesn't.

SELECT c
FROM FINAL TABLE( INSERT INTO mytbl(c)
VALUES ('c'),('b'),('a') )
ORDER BY INPUT SEQUENCE

Returns the data in the order in which it was provided.
Without the ORDER BY clause all bets are of.
Either way DB2 could decide to use any form of physical or time order to
store the data.
E.g. if mytbl is partitioned the 'a' may be stored first because it will
live on the local node while teh node holding 'c' is on teh other end of
a 50 Baut string-telephone ;-)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Art S. Kagel]

K.Fawcett wrote:

Thanks for you reply. An 'ORDER BY' clause was never required by this
table until a customer wanted to do something new. I will probably
need to add a new field to support this. I guess what threw me is that
the results of a 'SELECT' query always appear alphabetized (at least
for the queries I performed). From what you are saying I should expect
to see random ordering in my results. I accept this, but is there a
reason that I would never experience it? Here is my query and my
result:

<SNIP>
The return order of rows from an RDBMS is not 'random' but 'undefined' and
'indeterminate'. That is because the RDBMS is free to return rows by
scanning the table and reading allrows, by scanning an index on a filter
column or any other column or set of columns which the optimizer determines
might improve efficiency, or be creating a hash or other temporary table as
an intermediary. In this case it looks like the optimizer selected to query
via an index that apparently includes the disp_sym and disp_name columns so
they seem to be returned ordered by disp_name. The engine likely did that
because the filter value of the index for reducing IOs to find rows with the
disp_sym value 'RX6099B3' was high enough that the index read cose was less
than the IO costs saved by not scanning every data page.

Art S. Kagel