IBM Mainframe Forum

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.

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.

Date/time host variables ought, IIRC, to be defined in COBOL as PIC X(n).

Thank you Akatsukami.

defining host variables as PIC X(4) and PIC X(2) gives -401.

ok. defining these variables are S9(4) binary works. Now, my task is to somehow tune the query so as not to use functions.

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.

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.

I think this might work, although not sure if you are allowed to use Last_day() on a Host-variable:

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

It is always good to try to code Stage1 Predicates.

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...