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
 

 

Validating Timestamp with DB2

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

New User


Joined: 03 Mar 2008
Posts: 12
Location: Lisboa, Portugal

PostPosted: Wed Apr 15, 2009 11:13 pm    Post subject: Validating Timestamp with DB2
Reply with quote

Hi there,

I think this is possible: use DB2 to validate if a timestamp variable e correct. Meaning validate date and time format.

Can anyone tell me how to do it?

I'm need to do it in a cobol program.
Back to top
View user's profile Send private message

Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Wed Apr 15, 2009 11:41 pm    Post subject:
Reply with quote

Code:

EXEC SQL
SET :WS-TS2 = :WS-TS1 + 1 MICROSECOND
END-EXEC.
IF SQLCODE NOT = 0
you have a problem
END-IF.
Back to top
View user's profile Send private message
ntmartins

New User


Joined: 03 Mar 2008
Posts: 12
Location: Lisboa, Portugal

PostPosted: Wed Apr 15, 2009 11:49 pm    Post subject: Thank you
Reply with quote

In truth it was a basic question. I should have tried to think a bit more.

Thank you for your answer.
Back to top
View user's profile Send private message
ntmartins

New User


Joined: 03 Mar 2008
Posts: 12
Location: Lisboa, Portugal

PostPosted: Thu Apr 16, 2009 7:57 pm    Post subject: Variables type invalid (SQL -171)
Reply with quote

I've tried this solution but i'm having an bind error.

Both variables were defined as Char 26.




BIND PACKAGE(DGB0.DCCBAU) MEMBER(MAUQ123A) OWNER(DCOWNDB) QUALIFIER(DCOWNDB

DEGREE(ANY) EXPLAIN(YES) VALIDATE(BIND) OPTHINT('MAUQ123A')

DSNX200I -DGB2 BIND SQL ERROR

USING DCOWNDB AUTHORITY

PLAN=(NOT APPLICABLE)

DBRM=MAUQ123A

STATEMENT=499

SQLCODE=-171

SQLSTATE=42815

TOKENS=1 +

CSECT NAME=DSNXOBFA

RDS CODE=30

DSNT233I -DGB2 UNSUCCESSFUL BIND FOR

PACKAGE = DGB0.DCCBAU.MAUQ123A.()
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Apr 16, 2009 9:18 pm    Post subject:
Reply with quote

What is the actual definition of the fields and what is the actual sql statement.
Back to top
View user's profile Send private message
ntmartins

New User


Joined: 03 Mar 2008
Posts: 12
Location: Lisboa, Portugal

PostPosted: Thu Apr 16, 2009 10:12 pm    Post subject:
Reply with quote

Problem solved. icon_smile.gif

But here's the explanation for future references... icon_cool.gif

icon_arrow.gif
fields definition:

WS-TS1 PIC X(26).
WS-TS2 PIC (X26).

SQL Statement

EXEC SQL
SET :WS-TS1 = :WS-TS2 + 1 MICROSECOND
END EXEC.



icon_exclaim.gif Anyway, I've been looking into DB2 manual and the function MICROSECOND returns the microseconds from a timestamp!


When we try the statement on Spufi we get:

---------+---------+---------+---------+---------+---------+---------+---------+
SELECT '2009-03-10-15.55.00.846647' + 1 MICROSECOND
FROM SYSIBM.SYSDUMMY1
;
---------+---------+---------+---------+---------+---------+---------+---------+
DSNT408I SQLCODE = -171, ERROR: THE DATA TYPE, LENGTH, OR VALUE OF ARGUMENT 1
OF + IS INVALID



icon_exclaim.gif Because the correct sentence would be this:

---------+---------+---------+---------+---------+---------
SELECT MICROSECOND('2009-03-10-15.55.00.846647')
FROM SYSIBM.SYSDUMMY1
;
---------+---------+---------+---------+---------+---------

---------+---------+---------+---------+---------+---------
846647


icon_cool.gif
So, the solution was:

Fields

WS-TS1 PIC X(26).
WS-DATEDUMMY PIC X(10).

SQL Statement

EXEC SQL
SET :WS-DATEDUMMY = DATE(:WS-TS1)
END-EXEC.

IF SQLCODE NOT EQUAL 0
ERROR
END-IF.



Thank you anyway.
Back to top
View user's profile Send private message
Craq Giegerich

Senior Member


Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA

PostPosted: Thu Apr 16, 2009 11:06 pm    Post subject:
Reply with quote

Microsecond is a named duration which can be added or subtacted from a timestamp.
Code:
SELECT '2009-03-10-15.55.00.846647' + 1 MICROSECOND
FROM SYSIBM.SYSDUMMY1

should be

SELECT TIMESTAMP('2009-03-10-15.55.00.846647') + 1 MICROSECOND
FROM SYSIBM.SYSDUMMY1


MICROSECOND( ) is a function.
Back to top
View user's profile Send private message
ntmartins

New User


Joined: 03 Mar 2008
Posts: 12
Location: Lisboa, Portugal

PostPosted: Thu Apr 16, 2009 11:24 pm    Post subject:
Reply with quote

Ok, got that.

Thank you for your time and explanation.
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 Need help in reading and validating a... ABINAYATHULASI COBOL Programming 8 Tue Apr 18, 2017 11:42 am
No new posts Validating file with multiple header/... mohitsaini DFSORT/ICETOOL 6 Thu Apr 13, 2017 1:53 am
No new posts Converting EBCDIC to Unicode (TimeStamp) muralikrishnan_new COBOL Programming 2 Mon Jul 25, 2016 10:42 pm
No new posts Convert Timestamp Value mrdinesh DFSORT/ICETOOL 7 Thu May 05, 2016 6:01 pm
No new posts Validating TSO node id in Job venkaraj JCL & VSAM 16 Thu May 05, 2016 4:41 pm


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