DB2 date fetch error.

Mukesh Pandey · Active User Joined: 11 Nov 2008 Posts: 143 Location: India

hi all,

please let me know if the below code is correct.

01 WS-VALID-HOLIDAY-YEAR-1 PIC X(04).
01 WS-VALID-HOLIDAY-YEAR-2 PIC X(04).

.....

EXEC SQL
SET :WS-VALID-HOLIDAY-YEAR-1 = CHAR(YEAR(CURRENT DATE))
,:WS-VALID-HOLIDAY-YEAR-2 = CHAR(YEAR(CURRENT DATE) - 1)
END-EXEC

In case if there is any short cut of doing it without further working storage variable declaration then ple let me know.

Thanks in advance.

Srihari Gonugunta · Posted: Wed Oct 07, 2009 12:25 pm

Mukesh,
Subject says there is some error. But I don't see any problem with your approach. Please provide more information of the problem (if any).

Mukesh Pandey · Active User Joined: 11 Nov 2008 Posts: 143 Location: India

i have done some enhancement where above code is introduced. during testing i am getting S0C7 hence i am trying to eped the code.

While control encou8nters the code EXEC SQL
SET :WS-VALID-HOLIDAY-YEAR-1 = CHAR(YEAR(CURRENT DATE))
,:WS-VALID-HOLIDAY-YEAR-2 = CHAR(YEAR(CURRENT DATE) - 1)
END-EXEC

IF SQLCODE NOT = 0
MOVE FALSX TO RECORD-FOUND-FLG
GO TO 6600-DB2-SEARCH-HOLIDAY-EXIT
END-IF

and the control goes to SQL-INITIAL. para which is no where coded in the program.

Hence i suspected that there might be some error in my code shown above.

Please suggest on this issue.

Thanks in advance.

Mukesh Pandey · Active User Joined: 11 Nov 2008 Posts: 143 Location: India

EXEC SQL WHENEVER SQLERROR GOTO 9999-DISPLAY-SQLCA
END-EXEC
EXEC SQL WHENEVER SQLWARNING GOTO 9999-DISPLAY-SQLCA
END-EXEC

The above code is valid in my pgm

Can any one pls suggest me any correct way to code :

EXEC SQL
SET :WS-VALID-HOLIDAY-YEAR-1 = CHAR(YEAR(CURRENT DATE))
,:WS-VALID-HOLIDAY-YEAR-2 = CHAR(YEAR(CURRENT DATE) - 1)
END-EXEC

Thanks in advance,

Thanks in advance.

Mukesh Pandey · Active User Joined: 11 Nov 2008 Posts: 143 Location: India

please provide me the table name to query CHAR(YEAR(CURRENT DATE)) in qmf.

Srihari Gonugunta · Posted: Wed Oct 07, 2009 2:23 pm

Mukesh,
Your query is perfectly fine. Just try to put some displays and debug. SQL-INITIAL. para is part of your modified source code.

Mukesh Pandey · Active User Joined: 11 Nov 2008 Posts: 143 Location: India

01 WS-VALID-HOLIDAY-YEAR-1 PIC 9(04).
01 WS-VALID-HOLIDAY-YEAR-2 PIC 9(04).

05 LK-HOLID-KEY.
10 LK-HOLID-YEAR PIC 9(04).

EXEC SQL
SET :WS-VALID-HOLIDAY-YEAR-1 = INT(YEAR(CURRENT DATE))
,:WS-VALID-HOLIDAY-YEAR-2 = INT(YEAR(CURRENT DATE) - 1)
END-EXEC

IF (WS-VALID-HOLIDAY-YEAR-1 = LK-HOLID-YEAR)
.........

Error

UNDEFINED OR UNUSABLE HOST VARIABLE "WS-VALID-HOLIDAY-YEAR-1"
UNDEFINED OR UNUSABLE HOST VARIABLE "WS-VALID-HOLIDAY-YEAR-2"
SET : WS-VALID-HOLIDAY-YEAR-1 = INT(YEAR(CURRENT DATE)),: WS-VALID-HOLIDAY-YEAR2 = INT(YEAR(CURRENT DATE)-1)

Please suggest where the error is..

Thanks in advance,

bhairon singh rathore · New User Joined: 19 Jun 2008 Posts: 91 Location: banglore

Can you Paste your code

Mukesh Pandey · Active User Joined: 11 Nov 2008 Posts: 143 Location: India

01 WS-HOLID-YEAR PIC X(04).
01 WS-VALID-HOLIDAY-YEAR-1 PIC X(04) VALUE SPACES.
01 WS-VALID-HOLIDAY-YEAR-2 PIC X(04) VALUE SPACES.

05 LK-HOLID-KEY.
10 LK-HOLID-YEAR PIC 9(04).
10 LK-HOLID-REST-OF-KEY PIC X(91).

MOVE LK-HOLID-YEAR TO WS-HOLID-YEAR

EXEC SQL
SET :WS-VALID-HOLIDAY-YEAR-1 = CHAR(YEAR(CURRENT DATE))
,:WS-VALID-HOLIDAY-YEAR-2 = CHAR(YEAR(CURRENT DATE) - 1)
END-EXEC

IF (WS-VALID-HOLIDAY-YEAR-1 = WS-HOLID-YEAR)

... ..
.....
IF SQLCODE NOT = 0
MOVE FALSX TO RECORD-FOUND-FLG
GO TO 6600-DB2-SEARCH-HOLIDAY-EXIT
END-IF

While exped sql code is 0 but the program goes into error..... means the flow does not go to the next statement.

Ketan Varhade · Posted: Wed Oct 07, 2009 5:45 pm

Hi,
You can use cobol date fetch logic and then save it to a variable and then add or subtract number by using INTEGER OF DATE AND DATE OF INTEGER.
this will save the CPU used.

Mukesh Pandey · Active User Joined: 11 Nov 2008 Posts: 143 Location: India

cobol function is not allowed in my project.. need to implement using db2 function. please point out if there is any error in my code above..

enrico-sorichetti · Posted: Wed Oct 07, 2009 6:48 pm

Mukesh Pandey · Active User Joined: 11 Nov 2008 Posts: 143 Location: India

welll... enrico.... cud u please suggest whts the error in my code.

enrico-sorichetti · Posted: Wed Oct 07, 2009 7:15 pm

the main issue is with Your post...

the "UNDEFINED OR UNUSABLE HOST VARIABLE" is a message issued by the preprocessor otr cobol coprocessor

the s0c7 is a run time abend...

how did You get a running program with the pre/coprocesor error ???

GuyC · Posted: Wed Oct 07, 2009 7:55 pm

integer is not pic 9(4)

Mukesh Pandey · Active User Joined: 11 Nov 2008 Posts: 143 Location: India

i tried to change the code to eliminate soc7 hence was getting undefined or unusable host variable...

seems the error is in the below code. can any one suggest.

EXEC SQL
SET :WS-VALID-HOLIDAY-YEAR-1 = CHAR(YEAR(CURRENT DATE))
,:WS-VALID-HOLIDAY-YEAR-2 = CHAR(YEAR(CURRENT DATE) - 1)
END-EXEC

Terry Heinze · Posted: Thu Oct 08, 2009 9:04 pm

Did you read the post by GuyC?

lalitsharma_77 · New User Joined: 29 Nov 2007 Posts: 10 Location: Pune

Hi Mukesh,

I guess that the error you are getting 'undefined or unusable host variable' is while compilation/Bind process. I faced the same issue and the reason was the the data definition of WS variable is not same to data definition of extracted column (DCLGEN). I guess you need to check the data definition of WS variables.