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
 

 

bind error while getting difference of timestamp in minutes

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Arun bv

New User


Joined: 29 Dec 2010
Posts: 41
Location: Mumbai

PostPosted: Wed Jun 15, 2011 12:56 pm    Post subject: bind error while getting difference of timestamp in minutes
Reply with quote

I tried below snippet to get the time difference in minutes from timestamp.

05 WS-TIME-DIFF PIC X(04).
05 BDR-BKT-REQ-TIMESTMP PIC X(26).

EXEC SQL
SET :WS-TIME-DIFF = MINUTE(CURRENT TIME)
- MINUTE(:BDR-BKT-REQ-TIMESTMP)
END-EXEC


it is getting compiled successfully,but giving follwowing bind error

DSNX200I -FDSN BIND SQL ERROR
SQLCODE=-408
SQLSTATE=42821
TOKENS=*N
CSECT NAME=DSNXOCAS
RDS CODE=-100


(sqlcode =-408 description - The data type that is to be assigned is incompatible with the declared data
type of the assignment target. )

logically differnce will be of numeric ,but if i change WS-TIME-DIFF datatype to numeric ,it is giving compilation error.

so how can we handle this scenario to get the time diff,,,pls tell me if anyone know alternate way to get time diff in minutes.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Wed Jun 15, 2011 1:53 pm    Post subject:
Reply with quote

suggest you look at a db2 manual,
time arithmetic returns a DECIMAL(6,0) data type (which is comp-3 cobol data type)
Back to top
View user's profile Send private message
Arun bv

New User


Joined: 29 Dec 2010
Posts: 41
Location: Mumbai

PostPosted: Wed Jun 15, 2011 2:08 pm    Post subject:
Reply with quote

Thanks
Back to top
View user's profile Send private message
Arun bv

New User


Joined: 29 Dec 2010
Posts: 41
Location: Mumbai

PostPosted: Thu Jun 16, 2011 11:26 am    Post subject: ERROR WHILE DEBUGGING
Reply with quote

NOW I CHANGED THE VARIABLE DECLARATION TO

05 WS-TIME-DIFF PIC S9(6)V USAGE COMP-3.


BUT WHILE IM DEBUGGING I FOUND SQLCODE -180.


(sqlcode =-180 description The length or string representation of a DATE, TIME, or TIMESTAMP value does
ot conform to any valid format.

the value can contain one of the following:
For a host variable, the position number of the input host variable. If
the position number cannot be determined, a blank is displayed.
For a character string constant, the character string constant. The
maximum length that is displayed is the length of SQLERRM.
For a character column, the column name. If the column is a VIEW column
and it has a corresponding base column, the VIEW column name is
displayed. If the column is a VIEW column but it does not have a
corresponding base column, a string of '*N' is displayed. )




SO HOW DO I CONTINUE NOW?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 16, 2011 11:46 am    Post subject:
Reply with quote

Maybe :BDR-BKT-REQ-TIMESTMP is not properly populated.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 16, 2011 11:47 am    Post subject:
Reply with quote

The first error you had was a syntax error.
the current error you are encountering is a run-time error.
Back to top
View user's profile Send private message
Arun bv

New User


Joined: 29 Dec 2010
Posts: 41
Location: Mumbai

PostPosted: Thu Jun 16, 2011 11:49 am    Post subject:
Reply with quote

THAT I AM POPULATING WHILE DEBUGGING

SAMPLE DATA IS '2011-06-15-02.01.56.178837'
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 16, 2011 11:53 am    Post subject:
Reply with quote

ok, db2 is wrong, has a bug, call IBM
Back to top
View user's profile Send private message
Arun bv

New User


Joined: 29 Dec 2010
Posts: 41
Location: Mumbai

PostPosted: Thu Jun 16, 2011 12:05 pm    Post subject:
Reply with quote

icon_rolleyes.gif

oh dbz i dint expect this from you....could you tell me any alternate way to get that time ,if any...
Back to top
View user's profile Send private message
Arun bv

New User


Joined: 29 Dec 2010
Posts: 41
Location: Mumbai

PostPosted: Thu Jun 16, 2011 12:08 pm    Post subject:
Reply with quote

i found this but useless


SELECT TIMESTAMPDIFF (4, CHAR(
TIMESTAMP('2011-06-14-11.58.00')-
TIMESTAMP('2011-06-14-11.55.00'))) FROM SYSIBM.SYSDUMMY1



i cant use SYSIBM.SYSDUMMY1 in online program(i.e., within EXEC CICS... END-EXEC)
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7225

PostPosted: Thu Jun 16, 2011 1:10 pm    Post subject: Reply to: bind error while getting difference of timestamp i
Reply with quote

Just wondering, do you think you are getting a timestamp as a number of minutes, or do you think you are getting the minutes part of the timestamp?
Back to top
View user's profile Send private message
Arun bv

New User


Joined: 29 Dec 2010
Posts: 41
Location: Mumbai

PostPosted: Thu Jun 16, 2011 1:15 pm    Post subject:
Reply with quote

want to get the minutes part of the timestamp...

do u have any idea?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Jun 16, 2011 1:27 pm    Post subject:
Reply with quote

Quote:
i cant use SYSIBM.SYSDUMMY1 in online program(i.e., within EXEC CICS... END-EXEC)

1. the comment about sysdummy1 is false.
2. no wonder you are having problems with your sql. you need to enclose your sql with EXEC SQL END-EXEC



ok, I have compiled the following in both COBOL 2 and Enterprise-COBOL.
Cobol2 is db2 vsn 7
e-cobol is db2 vsn 9

working-storage:
Code:

03  WS-TIME-DIFF            PIC S9(6) COMP-3.
03  WS-TIMESTAMP            PIC X(26) VALUE
                            '2011-06-15-02.01.56.178837'.

procedure division:
Code:

EXEC SQL
    SET :WS-TIME-DIFF = MINUTE(CURRENT TIME)
    - MINUTE(TIMESTAMP(:WS-TIMESTAMP))
END-EXEC

DISPLAY WS-TIME-DIFF

EXEC SQL
    SET :WS-TIME-DIFF = MINUTE(CURRENT TIME)
    - MINUTE(:WS-TIMESTAMP)
END-EXEC

DISPLAY WS-TIME-DIFF


both sqls for both program provided the same result.

i created the first with the double CAST - min(timestamp())
in case db2 was having trouble deciding exactly what was in the host variable.

but that was not the case for either db2 version.

so back to the -180.
it is either due to CURRENT_TIME
or
your host variable.
you choose.

SO,
you are either not truthful
  • with your code
  • the contents of your host variable


either way,

the above is tested code.

suggest you RTFM and be more exact with
your code
and your debugging
than you have been posting in this thread.
Back to top
View user's profile Send private message
Arun bv

New User


Joined: 29 Dec 2010
Posts: 41
Location: Mumbai

PostPosted: Thu Jun 16, 2011 1:46 pm    Post subject:
Reply with quote

Thanks for trying out in MF

about the comment EXEC CICS ,,,,,i knew it is EXEC SQL ,,here its a typo error ,,,

and abt the CURRENT TIME, below sql query works

SELECT MINUTE(CURRENT TIME)
FROM SYSIBM.SYSDUMMY1


another working query

05 WS-TEMP-DATE PIC X(10).

EXEC SQL
SET :WS-TEMP-DATE = DATE(CURRENT DATE)
- 6 MONTHS
END-EXEC


if so why not in the above mentioned query(i.e the query which i posted here to get the time diff).
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 Sticky: difference between ... DUMMY ... and ... enrico-sorichetti JCL & VSAM 0 Mon Oct 17, 2016 4:31 pm
No new posts Error during restore rename archanamuthukrishnan All Other Mainframe Topics 2 Fri Oct 14, 2016 3:30 pm
No new posts Error IEC161I 052(009,XXXRS00)-084 wh... amitc23 JCL & VSAM 10 Wed Oct 12, 2016 5:00 pm


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