Order by not working on select into GTT

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • ebsch94
    #1

    Order by not working on select into GTT

    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_fr om. 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.TEMPTAB LE ( dollar_range_fr om
    DECIMAL(15,2), dollar_range_to DECIMAL(15,2) )
    ON COMMIT PRESERVE ROWS NOT LOGGED ;



    insert into session.TEMPTAB LE
    SELECT from_amount as dollar_range_fr om, to_amount as
    dollar_range_to FROM prodsys.dollar_ range_bands WHERE
    dollar_range_ty pe = 'T' AND report_ids = 'REPORT3' AND coverage_type =
    '99' OrDER BY by dollar_range_fr om;


    select * from session.temptab le;

    Tags: None
    • Lennart
      #2
      Re: Order by not working on select into GTT

      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_fr om. 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.TEMPTAB LE ( dollar_range_fr om
      DECIMAL(15,2), dollar_range_to DECIMAL(15,2) )
      ON COMMIT PRESERVE ROWS NOT LOGGED ;
      >
      >
      >
      insert into session.TEMPTAB LE
      SELECT from_amount as dollar_range_fr om, to_amount as
      dollar_range_to FROM prodsys.dollar_ range_bands WHERE
      dollar_range_ty pe = 'T' AND report_ids = 'REPORT3' AND coverage_type =
      '99' OrDER BY by dollar_range_fr om;
      >
      >
      select * from session.temptab le;
      >

        Comment

        • ebsch94
          #3
          Re: Order by not working on select into GTT

          On Mar 9, 3:10 pm, Lennart <erik.lennart.j ons...@gmail.co mwrote:
          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_fr om. 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.TEMPTAB LE ( dollar_range_fr om
          DECIMAL(15,2), dollar_range_to DECIMAL(15,2) )
          ON COMMIT PRESERVE ROWS NOT LOGGED ;
          >
          insert into session.TEMPTAB LE
          SELECT from_amount as dollar_range_fr om, to_amount as
          dollar_range_to FROM prodsys.dollar_ range_bands WHERE
          dollar_range_ty pe = 'T' AND report_ids = 'REPORT3' AND coverage_type =
          '99' OrDER BY by dollar_range_fr om;
          >
          select * from session.temptab le;- 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_fr om, 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.


            Comment

            • Lennart
              #4
              Re: Order by not working on select into GTT

              ebsch94 wrote:
              [...]
              >
              It seems that in our QA system, and prior production system these rows
              were returned in order of dollar_range_fr om, 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

                Comment

                • Lennart
                  #5
                  Re: Order by not working on select into GTT

                  ebsch94 wrote:
                  [...]
                  >
                  It seems that in our QA system, and prior production system these rows
                  were returned in order of dollar_range_fr om, 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

                    Comment

                    • Knut Stolze
                      #6
                      Re: Order by not working on select into GTT

                      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

                        Comment

                        Previous template Next
                        Working...