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

bind error while getting difference of timestamp in minutes


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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
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
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
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
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
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
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
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
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
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

Moderator Emeritus


Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix

PostPosted: Thu Jun 16, 2011 1:10 pm
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
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
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts IEW2315E 1369 Link error All Other Mainframe Topics 3
No new posts Help on IMS -API URL migrating from P... IMS DB/DC 1
No new posts Difference between joblib and steplib JCL & VSAM 2
No new posts JES datasets IO Error ABENDS & Debugging 3
No new posts Error to invoke MPP program through B... IMS DB/DC 3
Search our Forums:

Back to Top