Order by not working on select into GTT

[ebsch94]

I am executing the following code below. When we execute this on our
TEST system (Redhat 4.0, DB2 V8.2 FP11) the rows are returned in order
of dollar_range_from. We just moved our Production system from V7.2
(Redhat 2.1) to a
RedHat 4.0 64-bit/Db2 V8.2 64-bit FP14 enviromnment and now the below
code returns the rows out of order from the temp table even though
they were inserted with an order by.

DECLARE GLOBAL TEMPORARY TABLE session.TEMPTABLE ( dollar_range_from
DECIMAL(15,2), dollar_range_to DECIMAL(15,2) )
ON COMMIT PRESERVE ROWS NOT LOGGED ;

insert into session.TEMPTABLE
SELECT from_amount as dollar_range_from, to_amount as
dollar_range_to FROM prodsys.dollar_range_bands WHERE
dollar_range_type = 'T' AND report_ids = 'REPORT3' AND coverage_type =
'99' OrDER BY by dollar_range_from;
select * from session.temptable;

[Lennart]

ebsch94 wrote:

I am executing the following code below. When we execute this on our
TEST system (Redhat 4.0, DB2 V8.2 FP11) the rows are returned in order
of dollar_range_from. We just moved our Production system from V7.2
(Redhat 2.1) to a
RedHat 4.0 64-bit/Db2 V8.2 64-bit FP14 enviromnment and now the below
code returns the rows out of order from the temp table even though
they were inserted with an order by.

The DBMS (in this case db2) will retrive rows in what order it finds
most appropiate for the moment, unless you explicitly specify an order.
This is how it is supposed to work. I'm a bit surprised that you can do
insert with an order by, since it is has no meaning. You should move
the "order by" so that it becomes part of the select instead of the insert

/Lennart

>

DECLARE GLOBAL TEMPORARY TABLE session.TEMPTABLE ( dollar_range_from
DECIMAL(15,2), dollar_range_to DECIMAL(15,2) )
ON COMMIT PRESERVE ROWS NOT LOGGED ;

insert into session.TEMPTABLE
SELECT from_amount as dollar_range_from, to_amount as
dollar_range_to FROM prodsys.dollar_range_bands WHERE
dollar_range_type = 'T' AND report_ids = 'REPORT3' AND coverage_type =
'99' OrDER BY by dollar_range_from;
select * from session.temptable;

[ebsch94]

On Mar 9, 3:10 pm, Lennart <[e-mail]:

ebsch94 wrote:

I am executing the following code below. When we execute this on our
TEST system (Redhat 4.0, DB2 V8.2 FP11) the rows are returned in order
of dollar_range_from. We just moved our Production system from V7.2
(Redhat 2.1) to a
RedHat 4.0 64-bit/Db2 V8.2 64-bit FP14 enviromnment and now the below
code returns the rows out of order from the temp table even though
they were inserted with an order by.

The DBMS (in this case db2) will retrive rows in what order it finds
most appropiate for the moment, unless you explicitly specify an order.
This is how it is supposed to work. I'm a bit surprised that you can do
insert with an order by, since it is has no meaning. You should move
the "order by" so that it becomes part of the select instead of the insert

/Lennart

DECLARE GLOBAL TEMPORARY TABLE session.TEMPTABLE ( dollar_range_from
DECIMAL(15,2), dollar_range_to DECIMAL(15,2) )
ON COMMIT PRESERVE ROWS NOT LOGGED ;

insert into session.TEMPTABLE
SELECT from_amount as dollar_range_from, to_amount as
dollar_range_to FROM prodsys.dollar_range_bands WHERE
dollar_range_type = 'T' AND report_ids = 'REPORT3' AND coverage_type =
'99' OrDER BY by dollar_range_from;

select * from session.temptable;- Hide quoted text -

- Show quoted text -

It seems that in our QA system, and prior production system these rows
were returned in order of dollar_range_from, and unfortunately our
developer does other operations before the final select that depends
on those rows being in order. He is convinced something is wrong
because it behaves differently in our new production system.

[Lennart]

ebsch94 wrote:
[...]

>
It seems that in our QA system, and prior production system these rows
were returned in order of dollar_range_from, and unfortunately our
developer does other operations before the final select that depends
on those rows being in order.

You've lost me here. How are the rows suposed to be in any order before
you retrieved them?

He is convinced something is wrong
because it behaves differently in our new production system.

A table is a set (actually a bag in sql), Therefor unordered by
definition. If rows where retrieved in a particular order before, it
could depend of a lot of things (luck, internal implementation, etc).
However, you should never ever expect them to do so (as you have noticed
by now).

If it where any other way you should be able to find a reference or two
that says that it is safe to assume that the rows are returned in a
certain order without using an order by. I dont think you will find one.

/Lennart

[Lennart]

ebsch94 wrote:
[...]

>
It seems that in our QA system, and prior production system these rows
were returned in order of dollar_range_from, and unfortunately our
developer does other operations before the final select that depends
on those rows being in order.

You've lost me here. How are the rows suposed to be in any order before
you retrieved them?

He is convinced something is wrong
because it behaves differently in our new production system.

A table is a set (actually a bag in sql), Therefor unordered by
definition. If rows where retrieved in a particular order before, it
could depend of a lot of things (luck, internal implementation, etc).
However, you should never ever expect them to do so (as you have noticed
by now).

If it where any other way you should be able to find a reference or two
that says that it is safe to assume that the rows are returned in a
certain order without using an order by. I dont think you will find one.

/Lennart

[Knut Stolze]

Lennart wrote:

If it where any other way you should be able to find a reference or two
that says that it is safe to assume that the rows are returned in a
certain order without using an order by. I dont think you will find one.

If you do, please report it because that's a bug.

--
Knut Stolze
DB2 z/OS Admin Enablement
IBM Germany