× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. MIDRANGE-L
  3. February 2010

Re: SQL Question


Thanks Charles
when you put it that way
"A binary search tree
isn't going to do you much good if you'd applied functions that change
the value in the key field."
it makes perfect sense


Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill



Charles Wilt
<charles.wilt@gma
il.com> To
Sent by: Midrange Systems Technical
midrange-l-bounce Discussion
s@xxxxxxxxxxxx <midrange-l@xxxxxxxxxxxx>
cc

02/17/2010 10:27 Subject
AM Re: SQL Question


Please respond to
Midrange Systems
Technical
Discussion
<midrange-l@midra
nge.com>






Alan,

I don't have a reference for you off the top of my head nor time to
find one right now...

However, if you've paid attention on this list, read any articles
about SQL performance, or attended SQL sessions you'd have seen the
same statement many times by people with more SQL expertise than
myself.

Ok, here's something, talking about how the new derived index support
in 6.1 helps:
http://www.ibm.com/developerworks/data/library/techarticle/dm-0806milligan/
"Performance problems could arise on past releases due to the fact
that the UPPER function prevented the DB2 for i query optimizer from
using an index to speed up this search. "

When you think about it it just makes sense. A binary search tree
isn't going to do you much good if you'd applied functions that change
the value in the key field.

HTH,
Charles


On Wed, Feb 17, 2010 at 9:53 AM, Alan Shore <AlanShore@xxxxxxxx> wrote:

Hi Charles
***NOTE** This first piece was added last as I read my original e-mail
and
realized that my question could come off as we being condescending
BUT this is a valid question.

nice answer, but I have a question
How do you know that "you will force the system to do at minimum a full
index scan and at maximum a full table scan"
will be done? Where can I find such documentation?
Thanks in advance


Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill



            Charles Wilt
            <charles.wilt@gma
            il.com>                                                    To
            Sent by:                  Midrange Systems Technical
            midrange-l-bounce         Discussion
            s@xxxxxxxxxxxx            <midrange-l@xxxxxxxxxxxx>
                                                                       cc

            02/17/2010 09:42                                      Subject
            AM                        Re: SQL Question


            Please respond to
            Midrange Systems
                Technical
               Discussion
            <midrange-l@midra
                nge.com>






Note however that by using scalar functions (substring and concat) on
FLD6 you will force the system to do at minimum a full index scan and
at maximum a full table scan unless you're at 6.1 and have a derived
index.

A better idea, since you're only looking for =, is to simply reformat
the just value you've got for SYSDATE.

SELECT FLD1, FLD2, FLD3, FLD4
FROM MYFILE
WHERE FLD6  = (SELECT dec(substr(SYSDATE,7,2)
                                               concat  substr
(SYSDATE,5,2)
                                               concat  substr
(SYSDATE,3,2)
                                              ,6,0)
                             FROM SYSPARM WHERE ENV= "PRD")

Now if you've got an index on FLD6, the system will be able to do an
index seek to find the rows.

HTH,
Charles Wilt



On Wed, Feb 17, 2010 at 9:15 AM, Alan Shore <AlanShore@xxxxxxxx> wrote:

Hi Dina
here is one suggestion
FLD6 is a decimal so you can convert that into character by the use of
DIGITS(FLD6)
This can then be parsed out (love that word)  - I am assuming the date
in
FLD6 is of the format ddmmyy
SUBSTR(DIGITS(FLD6), 5, 2) || SUBSTR(DIGITS(FLD6), 3, 2) || SUBSTR
(DIGITS
(FLD6), 1, 2)
where || is concatenation (I forget the word to use)

This can then be compared to the last six characters of SYSDATE
SELECT FLD1, FLD2, FLD3, FLD4

FROM MYFILE

WHERE
SUBSTR(DIGITS(FLD6), 5, 2) || SUBSTR(DIGITS(FLD6), 3, 2) || SUBSTR
(DIGITS
(FLD6), 1, 2)  =
SELECT SUBSTR(SYSDATE, 3, 6) FROM SYSPARM WHERE ENV= "PRD")



Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill



            dina ramzy
            <dina.ramzy@live.
            com>
To
            Sent by:                  <midrange-l@xxxxxxxxxxxx>
            midrange-l-bounce
cc
            s@xxxxxxxxxxxx

Subject
                                      SQL Question
            02/17/2010 09:08
            AM


            Please respond to
            Midrange Systems
                Technical
               Discussion
            <midrange-l@midra
                nge.com>








Hi All,



Could you please advise me with this query:



SELECT FLD1, FLD2, FLD3, FLD4

FROM MYFILE

WHERE FLD6  = (SELECT SYSDATE FROM SYSPARM WHERE ENV= "PRD")



The problem is FLD6 is decimal of 6 containing data like 160210

and SYSDTAE is char of 8 containting date like 20100216. and I can not
make
the comparison...







Thanks in advance for your help



Dina

_________________________________________________________________
Hotmail: Powerful Free email with security by Microsoft.
https://signup.live.com/signup.aspx?id=60969
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


Hi All,



Could you please advise me with this query:



SELECT FLD1, FLD2, FLD3, FLD4

FROM MYFILE

WHERE FLD6  = (SELECT SYSDATE FROM SYSPARM WHERE ENV= "PRD")



The problem is FLD6 is decimal of 6 containing data like 160210

and SYSDTAE is char of 8 containting date like 20100216. and I can not
make
the comparison...







Thanks in advance for your help



Dina
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.



--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.