ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Results Differ between green-screen and Ops Nav

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Results Differ between green-screen and Ops Nav

    Help!

    The following query returns two different result sets based on where it's run!

    Code:
    Select ClsDte, SubNam, Job#, Addrs, Street, Purch, cssls#, csbld#, csccr#, 
              csdcc#, csq1a, csq1b, csq1c, csq1d, csq1e, csq1f, csq1g, csq1h, csq1i, 
              csq1j, csq2a, csq2b, csq2c, csq2d, csq2e, csq2f, csq3, csq4, csq5                                  
       From TST#FILE.JOBMST a 
          inner join tst#file.subdvn b 
             on a.subdiv=b.subdiv 
          inner join tst#file.CSSRVY c 
             on a.subdiv=c.cssub and a.job#=c.csjob# 
       Where char(date(csupstmp)) between '2007-06-01' and '2007-09-30' 
       Order by ClsDte, SubNam, Street, Addrs
    When I run this query from the green screen STRSQL program, I get no records. When I run it from the Run SQL Scripts function from iSeries Navigator, I get the 116 records I would expect to see.

    (F.R.O.G. returns no records either. I guess that MUST be the right answer! )

    WHY?!?!?!?!

    Michael
    Last edited by littlepd; September 12, 2007, 05:28 PM.
    "Time passes, but sometimes it beats the <crap> out of you as it goes."

  • #2
    Re: Results Differ between green-screen and Ops Nav

    Well, merde! (Sorry to those of you who speak French!)

    It turns out that the SQL function DATE() returns the date value in the format of the job where the script is run. For all of my jobs except the one to which Run SQL Scripts is connected, the Date format for the job is *MDY.

    So if I change the script to

    Code:
    Select ClsDte, SubNam, Job#, Addrs, Street, Purch, cssls#, csbld#, csccr#, 
              csdcc#, csq1a, csq1b, csq1c, csq1d, csq1e, csq1f, csq1g, csq1h, csq1i, 
              csq1j, csq2a, csq2b, csq2c, csq2d, csq2e, csq2f, csq3, csq4, csq5                                  
       From TST#FILE.JOBMST a 
          inner join tst#file.subdvn b 
             on a.subdiv=b.subdiv 
          inner join tst#file.CSSRVY c 
             on a.subdiv=c.cssub and a.job#=c.csjob# 
       Where char(date(csupstmp)) between '06/01/07' and '09/30/07' 
       Order by ClsDte, SubNam, Street, Addrs
    everywhere by iNav, the $#!#@$ thing works!!!!! Aaaaaaaaah.

    "Time passes, but sometimes it beats the <crap> out of you as it goes."

    Comment


    • #3
      Re: Results Differ between green-screen and Ops Nav

      Hi,

      you dont need to convert your date into a character representation.
      SQL is smart enough to recognize a character string as date in one of the following formats :
      YYYY-MM-DD, MM/DD/YYYY, DD.MM.YYYY

      This works independent of the date format used in your session.

      PHP Code:
      Select ClsDteSubNamJob#, Addrs, Street, Purch, cssls#, csbld#, csccr#, 
                
      csdcc#, csq1a, csq1b, csq1c, csq1d, csq1e, csq1f, csq1g, csq1h, csq1i, 
                
      csq1jcsq2acsq2bcsq2ccsq2dcsq2ecsq2fcsq3csq4csq5                                  
         From TST
      #FILE.JOBMST a 
            
      inner join tst#file.subdvn b 
               
      on a.subdiv=b.subdiv 
            inner join tst
      #file.CSSRVY c 
               
      on a.subdiv=c.cssub and a.job#=c.csjob# 
         
      Where date(csupstmpbetween '2007-06-01' and '2007-09-30' 
         
      Order by ClsDteSubNamStreetAddrs 
      If CSUPSTMP is already a date and what I expect, just remove the scalar function DATE.
      In this way you'll never get a problem when comparing dates.

      BTW:
      Using a scalar function lefthand of the comparation operator (=, <, >, <=, >=, like, between, in) will prevent the optimitzer to use an index/keyed logical file over this column, which may result in a bad performance.

      Birgitta
      Last edited by B.Hauser; September 12, 2007, 11:14 PM.

      Comment


      • #4
        Re: Results Differ between green-screen and Ops Nav

        Originally posted by B.Hauser View Post

        BTW:
        Using a scalar function lefthand of the comparation operator (=, <, >, <=, >=, like, between, in) will prevent the optimitzer to use an index/keyed logical file over this column, which may result in a bad performance.
        if use scalar function will be bad perfomance, what should we use to get the best perfomance ?

        coz there no choice ??

        Comment


        • #5
          Re: Results Differ between green-screen and Ops Nav

          Hi,

          believe me, in most cases there is an other choice:

          For example:
          • Instead of: Substr(Fld, 1, 3) = 'ABC' use Like 'ABC%'
          • Instead of: Year(MyDate) = 2007 use MyDate between '2007-01-01' and '2007-12-31'
          • Instead of: Upper(MyField) use an other sort sequence (LangShrI
            d or LandIdUnq) and create the appropriate indexes with this sort sequence


          In either way, it is only possible to built indexes over "original" columns. Keyed DDS described logical files where fields are concatenated or substringed and keys defined over these new fields are ignored by the query optimizer.

          ... and the query optimizer can only use access path built over the original columns. If you are using scalar functions lefthand of the comparative operator, you "changed" the original column and the optimizer cannot use anymore an index built over this column. If there are no other where clauses or joins, the optimizer may use a table scan (instead of an index access).

          Birgitta
          Last edited by B.Hauser; September 13, 2007, 12:35 AM.

          Comment


          • #6
            Re: Results Differ between green-screen and Ops Nav

            Originally posted by B.Hauser View Post
            Hi,

            you dont need to convert your date into a character representation.
            SQL is smart enough to recognize a character string as date in one of the following formats :
            YYYY-MM-DD, MM/DD/YYYY, DD.MM.YYYY

            This works independent of the date format used in your session.

            If CSUPSTMP is already a date and what I expect, just remove the scalar function DATE.
            In this way you'll never get a problem when comparing dates.

            BTW:
            Using a scalar function lefthand of the comparation operator (=, <, >, <=, >=, like, between, in) will prevent the optimitzer to use an index/keyed logical file over this column, which may result in a bad performance.

            Birgitta
            I knew I could count on you for that last little bit of necessary refinement, B. Thanks.

            CSUPSTMP is a timestamp field. So, as i see it, the only way to avoid the DATE scalar function would be something like this.

            Code:
            Select ClsDte, SubNam, Job#, Addrs, Street, Purch, cssls#, csbld#, csccr#,
                      csdcc#, csq1a, csq1b, csq1c, csq1d, csq1e, csq1f, csq1g, csq1h, csq1i,
                      csq1j, csq2a, csq2b, csq2c, csq2d, csq2e, csq2f, csq3, csq4, csq5                                  
               From TST#FILE.JOBMST a
                  inner join tst#file.subdvn b
                     on a.subdiv=b.subdiv
                  inner join tst#file.CSSRVY c
                     on a.subdiv=c.cssub and a.job#=c.csjob#
               Where csupstmp between '2007-06-01-00.00.00.000000' and '2007-09-30-24.00.00.000000'
               Order by ClsDte, SubNam, Street, Addrs
            Yes? No?
            "Time passes, but sometimes it beats the <crap> out of you as it goes."

            Comment


            • #7
              Re: Results Differ between green-screen and Ops Nav

              Originally posted by B.Hauser View Post

              For example:
              • Instead of: Substr(Fld, 1, 3) = 'ABC' use Like 'ABC%'
              • Instead of: Year(MyDate) = 2007 use MyDate between '2007-01-01' and '2007-12-31'
              • Instead of: Upper(MyField) use an other sort sequence (LangShrI
                d or LandIdUnq) and create the appropriate indexes with this sort sequence


              Birgitta
              i think we must have a trick to query something to get the best permofance.
              Birgitta, how to learn the trick of query ?

              thx u

              Comment


              • #8
                Re: Results Differ between green-screen and Ops Nav

                Hi,

                Where csupstmp between '2007-06-01-00.00.00.000000' and '2007-09-30-24.00.00.000000'
                Yes, that's the way to compare timestamps without using skalar functions lefthand the comparative operator.

                Birgitta, how to learn the trick of query?
                Because you are from Jakarta I cannot say, come to one of my workshops or invite me and I'll show you how to analyse your SQL statements and where are the pitfalls.

                IBM offers a very good workshop about SQL Performance, but it's only hold by Kent Milligan and Mike Cain, both are very bussy and therefore only once or twice a year in Europe, Asia, USA and South America. You may check if you can get the chance to participate in this workshop.

                There are also a couple of tricks in the following redbooks or books in the online library:
                OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS in V5R4

                SQL Performance Diagnosis on IBM DB2 Universal Database for iSeries
                Database performance and query optimization

                Birgitta

                Comment


                • #9
                  Re: Results Differ between green-screen and Ops Nav

                  Thanks for the links Birgitta !!!

                  GC
                  Greg Craill: "Life's hard - Get a helmet !!"

                  Comment


                  • #10
                    Re: Results Differ between green-screen and Ops Nav

                    Originally posted by B.Hauser View Post

                    Because you are from Jakarta I cannot say, come to one of my workshops or invite me and I'll show you how to analyse your SQL statements and where are the pitfalls.
                    i must steal money from a bank to invite birgitta :> too expensive

                    may be i can learn from u in this forum n all of member in this forum

                    thx u for your link
                    :>

                    Comment

                    Working...
                    X