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
 

 

[Solved]UNIQUE_TIMESTAMP() function

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

New User


Joined: 14 Sep 2005
Posts: 21

PostPosted: Mon Dec 04, 2006 10:38 pm    Post subject: UNIQUE_TIMESTAMP() function
Reply with quote

Hello -

I am trying to insert a row into a table. In that table, the first row is a Timestamp value. Here I am using the UNIQUE_TIMESTAMP() function to insert into the table value and I am getting a -180. Has anybody used this function in the DB2 before? If so, can anybody please tell me how to use this function.

Thanks for your time!
Back to top
View user's profile Send private message

MFRASHEED

Active User


Joined: 14 Jun 2005
Posts: 186
Location: USA

PostPosted: Mon Dec 04, 2006 11:46 pm    Post subject: Re: UNIQUE_TIMESTAMP() function
Reply with quote

I could not find find the function in DB2 manuals, is it a user defined function in your shop?

If requirement is to insert a time stamp value in a column to keep row unique you can use CURRENT TIMESTAMP and here are details:

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/handheld/Connected/BOOKS/dsnsqj10/2.15.23?DT=20040216135741

Example:
[/code]
Code:

INSERT                     
INTO TABLE                 
        (ENTRY_TS,         
         MAINT_TS)         
VALUES  (:HOST-ENTRY-TS,   
         CURRENT TIMESTAMP)
Back to top
View user's profile Send private message
hcl_ln

New User


Joined: 14 Sep 2005
Posts: 21

PostPosted: Tue Dec 05, 2006 12:50 am    Post subject: UNIQUE_TIMESTAMP() function
Reply with quote

Much thanks for the reply.
Yes, it is a user defined function, I beleive.
The idea behind using the UNIQUE_TIMESTAMP() function is: to capture the exact time in micro seconds.

From CURRENT TIMESTAMP function it is returning : 2006-12-04-12.17.14.724213
From UNIQUE_TIMESTAMP() function it is returning : 2006-12-04-12.17.14.394080

And the syntax which I am using is:

Code:
EXEC SQL                                     
     SET :HOST-TS-VARIABLE=UNIQUE_TIMESTAMP()
END-EXEC                                     


   SELECT TABLE_TS_VARIABLE             
          INTO :HOST-TS-VARIABLE
          FROM FINAL TABLE (             
   INSERT INTO TABLE_TS       
              (                           
                TABLE_TS_VARIABLE
              )
  VALUES
              (
                UNIQUE_TIMESTAMP()
              )
                                       )

Much thanks for your time!
Back to top
View user's profile Send private message
sgaid21

New User


Joined: 12 Sep 2006
Posts: 16
Location: Canada

PostPosted: Fri Dec 15, 2006 1:33 am    Post subject:
Reply with quote

If you are getting SQLCODE=-180 that means invalid data value.
Check if your column definition compatible with the UNIQUE_TIMESTAMP() return value.

Make sure :HOST-TS-VARIABLE PIC x(26).

I hope this answer would help you.[/img]
Back to top
View user's profile Send private message
hcl_ln

New User


Joined: 14 Sep 2005
Posts: 21

PostPosted: Thu Dec 28, 2006 3:29 am    Post subject: Re: UNIQUE_TIMESTAMP() function
Reply with quote

Hello All -

Forgot to post my results set.
I got the answer.

When using this UNIQUE_TIMESTAMP() function in DB2 V8, for some reason the date value in the time stamp is inserted as YYYY/MM/DD instead of the normal DB2 valid value YYYY-MM-DD. So I had to convert the date using some date conversion routine to get the correct results set. Then my -180 is gone and inserted into the DB2 table correctly.

Hope this helps in someone else's research.

Thanks
Back to top
View user's profile Send private message
ravi17s
Warnings : 1

New User


Joined: 15 Aug 2003
Posts: 57

PostPosted: Thu Dec 28, 2006 4:25 pm    Post subject: Re: UNIQUE_TIMESTAMP() function
Reply with quote

Cool.
You can also use the following conversion to convert the date into valid format
Name Layout Example
ISO yyyy-mm-dd 2002-10-22
USA mm/dd/yyyy 10/22/2002
EUR dd.mm.yyyy 22.10.2002
JIS yyyy-mm-dd 2002-10-22
LOCAL Locally defined layout N/A


CHAR(START_DATE,USA)
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
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts Strange EXEC function call in z/VM Willy Jensen CLIST & REXX 3 Wed Oct 05, 2016 2:07 pm
No new posts Replace function abdulrafi COBOL Programming 13 Mon Sep 12, 2016 10:12 am
No new posts Which function does the Ampersand (&a... Andi1982 PL/I & Assembler 3 Wed Aug 24, 2016 2:05 pm


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