IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

DB2 date fetch error.


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
Mukesh Pandey

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Wed Oct 07, 2009 11:12 am
Reply with quote

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.
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Wed Oct 07, 2009 12:25 pm
Reply with quote

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).
Back to top
View user's profile Send private message
Mukesh Pandey

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Wed Oct 07, 2009 1:02 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Mukesh Pandey

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Wed Oct 07, 2009 1:57 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Mukesh Pandey

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Wed Oct 07, 2009 2:10 pm
Reply with quote

please provide me the table name to query CHAR(YEAR(CURRENT DATE)) in qmf.
Back to top
View user's profile Send private message
Srihari Gonugunta

Active User


Joined: 14 Sep 2007
Posts: 295
Location: Singapore

PostPosted: Wed Oct 07, 2009 2:23 pm
Reply with quote

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

Code:
SELECT CHAR(YEAR(CURRENT DATE)) FROM SYSIBM.SYSDUMMY1;
Back to top
View user's profile Send private message
Mukesh Pandey

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Wed Oct 07, 2009 3:56 pm
Reply with quote

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,
Back to top
View user's profile Send private message
bhairon singh rathore

New User


Joined: 19 Jun 2008
Posts: 91
Location: banglore

PostPosted: Wed Oct 07, 2009 4:36 pm
Reply with quote

Can you Paste your code
Back to top
View user's profile Send private message
Mukesh Pandey

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Wed Oct 07, 2009 4:50 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Ketan Varhade

Active User


Joined: 29 Jun 2009
Posts: 197
Location: Mumbai

PostPosted: Wed Oct 07, 2009 5:45 pm
Reply with quote

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.
Back to top
View user's profile Send private message
Mukesh Pandey

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Wed Oct 07, 2009 6:06 pm
Reply with quote

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..
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10833
Location: italy

PostPosted: Wed Oct 07, 2009 6:48 pm
Reply with quote

Quote:
cobol function is not allowed in my project..


looks like the IQ of people setting the requirements id getting lower day by day
Back to top
View user's profile Send private message
Mukesh Pandey

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Wed Oct 07, 2009 6:56 pm
Reply with quote

welll... enrico.... cud u please suggest whts the error in my code.
Back to top
View user's profile Send private message
enrico-sorichetti

Superior Member


Joined: 14 Mar 2007
Posts: 10833
Location: italy

PostPosted: Wed Oct 07, 2009 7:15 pm
Reply with quote

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 ???
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed Oct 07, 2009 7:55 pm
Reply with quote

integer is not pic 9(4)
Back to top
View user's profile Send private message
Mukesh Pandey

Active User


Joined: 11 Nov 2008
Posts: 143
Location: India

PostPosted: Thu Oct 08, 2009 11:09 am
Reply with quote

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
Back to top
View user's profile Send private message
Terry Heinze

JCL Moderator


Joined: 14 Jul 2008
Posts: 1249
Location: Richfield, MN, USA

PostPosted: Thu Oct 08, 2009 9:04 pm
Reply with quote

Did you read the post by GuyC?
Back to top
View user's profile Send private message
lalitsharma_77

New User


Joined: 29 Nov 2007
Posts: 10
Location: Pune

PostPosted: Fri Oct 09, 2009 2:28 am
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts SMF Record Date conversion failing CLIST & REXX 1
No new posts Removal of date value records DFSORT/ICETOOL 12
No new posts dsnrexx fails without error message CLIST & REXX 9
No new posts writing into VSAM indexed tabl in PL1... PL/I & Assembler 8
No new posts SORT ERROR PARAMETER VALUE EXCEEDS M... DFSORT/ICETOOL 12
Search our Forums:

Back to Top