compare db2 year function with host variable



IBM's flagship relational database management system

compare db2 year function with host variable

Postby chig » Wed Jan 16, 2013 9:12 pm

I have to compare a db2 date with a host variable. The date from db2 table could be any date of the month. The host variable can be defined easily as first of the month. How to compare them ? Plus, I have to compare them in select statment rather than as a predicate.

e.g.
SELECT CASE                                 
          WHEN YEAR(T1.AC_START_DT) =     
               :WS-AC-START-DT-YY         
           AND MONTH(T1.AC_START_DT) =     
               :WS-AC-START-DT-MM         
          THEN DATE(T1.AC_START_DT) - 1 DAY
          ELSE :WS-SOME-OTHER-DT         
       END AS FROMDT                           
FROM Table1 T1                           
WHERE ACC_NO = :WS-AC-NO

I am getting errors such as the host variables WS-AC-START-DT-YY and WS-AC-START-DT-MM are not defined correctly. So, I don't know what is the correct way of defining such host variables. I am defining them as 9(04) comp.

Also is there any other way to do this ? Once this query goes through my DBAs, I know they will reject it as they do not like me using YEAR and MONTH (or even LAST_DAY) functions as it slows down the performance.

P.S. the query given above is a small part of a bigger query, so it cannot be done in COBOL also. I have to do it somehow in db2.

Any suggestion would be really grateful.

thanks a lot.
chig
 
Posts: 11
Joined: Tue Oct 23, 2012 8:41 pm
Has thanked: 1 time
Been thanked: 0 time

Re: compare db2 year function with host variable

Postby Akatsukami » Wed Jan 16, 2013 9:21 pm

Date/time host variables ought, IIRC, to be defined in COBOL as PIC X(n).
"You have sat too long for any good you have been doing lately ... Depart, I say; and let us have done with you. In the name of God, go!" -- what I say to a junior programmer at least once a day
User avatar
Akatsukami
Global moderator
 
Posts: 1058
Joined: Sat Oct 16, 2010 2:31 am
Location: Bloomington, IL
Has thanked: 6 times
Been thanked: 51 times

Re: compare db2 year function with host variable

Postby chig » Wed Jan 16, 2013 9:28 pm

Thank you Akatsukami.

defining host variables as PIC X(4) and PIC X(2) gives -401.
chig
 
Posts: 11
Joined: Tue Oct 23, 2012 8:41 pm
Has thanked: 1 time
Been thanked: 0 time

Re: compare db2 year function with host variable

Postby chig » Wed Jan 16, 2013 9:50 pm

ok. defining these variables are S9(4) binary works. Now, my task is to somehow tune the query so as not to use functions.
chig
 
Posts: 11
Joined: Tue Oct 23, 2012 8:41 pm
Has thanked: 1 time
Been thanked: 0 time

Re: compare db2 year function with host variable

Postby dick scherrer » Wed Jan 16, 2013 10:32 pm

Hello,

Now, my task is to somehow tune the query so as not to use functions.
Why is this a "requirement"?

It is usually not a good idea to start tuning until the process works correctly and it is determined that there is some kind of performance issue.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: compare db2 year function with host variable

Postby chig » Thu Jan 17, 2013 1:23 am

hi d.sch.

this will be a new query which will have to go through my DBAs consent in our shop. and this being a new one, he would want me to do it some other way by not using functions.
chig
 
Posts: 11
Joined: Tue Oct 23, 2012 8:41 pm
Has thanked: 1 time
Been thanked: 0 time

Re: compare db2 year function with host variable

Postby GuyC » Thu Jan 17, 2013 1:59 pm

I think this might work, although not sure if you are allowed to use Last_day() on a Host-variable:
when T1.AC_START_DT between date(:WS-AC-START-DT) and last_day(date(:WS-AC-START-DT))

else you need to define 2 hostvariable :beginning-of-month and :end-of-month

It is always good to try to code Stage1 Predicates.
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: compare db2 year function with host variable

Postby chig » Sat Jan 19, 2013 2:13 am

GuyC thanks for your suggestion. I think we can use last_day for a host variable when converted to a date. Also you gave me a good idea. to use BETWEEN. I can before using the query itself put both the host variables as start and end of the month. in that case i can prevent using LAST_DAY function also...
chig
 
Posts: 11
Joined: Tue Oct 23, 2012 8:41 pm
Has thanked: 1 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post