Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

DB2 date fetch error.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: DB2 date fetch error.
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Wed Oct 07, 2009 6:48 pm    Post subject: Reply to: DB2 date fetch error.
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    Post subject:
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

Global Moderator


Joined: 14 Mar 2007
Posts: 10201
Location: italy

PostPosted: Wed Oct 07, 2009 7:15 pm    Post subject: Reply to: DB2 date fetch error.
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: 1278
Location: Belgium

PostPosted: Wed Oct 07, 2009 7:55 pm    Post subject:
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    Post subject:
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: 1238
Location: Richfield, MN, USA

PostPosted: Thu Oct 08, 2009 9:04 pm    Post subject:
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    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm
No new posts TOT & DATE parameter in ICEMAN hemanthj642 DFSORT/ICETOOL 4 Mon Nov 14, 2016 5:19 am
This topic is locked: you cannot edit posts or make replies. How to pass the previous month date i... Suganya87 DFSORT/ICETOOL 5 Mon Oct 31, 2016 4:13 pm
This topic is locked: you cannot edit posts or make replies. Get correct date and time when curren... balaji81_k DB2 24 Fri Oct 14, 2016 10:40 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us