Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 Timestamp field weird behaviour.

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

New User


Joined: 02 Jan 2018
Posts: 11
Location: INDIA

PostPosted: Wed Dec 26, 2018 4:33 pm    Post subject: DB2 Timestamp field weird behaviour.
Reply with quote

Hi,

There is a table (X) and it has this column (XT) defined as timestamp.

At test environment, When I run below query then it gets executed successfully.

Update X
SET XT = '2017-12-26'
WHERE X_primary key = '123'
;



XT then updated as '2017-12-26-00.00.00.000000'.

Now issue at prod environment with same query. It gives sqlcode -180.

To avoid this error I needed to write query like below.

Update X
SET XT ='2017-12-26-00.00.00.000000' (whole timestamp format data given)
WHERE X_primary key = '123'
;


Anybody know why prod did not accept date in timestamp where as test does or why test has accepted date in place timestamp ?
[/b]
Back to top
View user's profile Send private message

Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2165
Location: NY,USA

PostPosted: Thu Dec 27, 2018 12:47 am    Post subject:
Reply with quote

Did you talk to DBAs yet, if not then talk to them first? Looks like you have two different DB2 versions and DB2 set up in prod and Test.
Back to top
View user's profile Send private message
rizwanaccy

New User


Joined: 02 Jan 2018
Posts: 11
Location: INDIA

PostPosted: Thu Dec 27, 2018 12:20 pm    Post subject: Reply to: DB2 Timestamp field weird behaviour.
Reply with quote

Hi,

I checked DB version at both test and prod are same. I will check with DBA but just wanted to know which DB version has which settings on timestamp?
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2180
Location: Hampshire, UK

PostPosted: Thu Dec 27, 2018 4:25 pm    Post subject: Reply to: DB2 Timestamp field weird behaviour.
Reply with quote

Quote:
just wanted to know which DB version has which settings on timestamp

Why ask us? We do not work where you work. We do not know how your systems are set up. Your DBA does. Your DBA is always your first point of contact, after your colleagues.
Back to top
View user's profile Send private message
rizwanaccy

New User


Joined: 02 Jan 2018
Posts: 11
Location: INDIA

PostPosted: Thu Dec 27, 2018 4:30 pm    Post subject: Reply to: DB2 Timestamp field weird behaviour.
Reply with quote

I am asking in terms of general knowledge on Db2. Are there any kind of settings on timestamp field which allows date?
Back to top
View user's profile Send private message
expat

Global Moderator


Joined: 14 Mar 2007
Posts: 8782
Location: Welsh Wales

PostPosted: Thu Dec 27, 2018 5:09 pm    Post subject:
Reply with quote

You MUST ask YOUR site support about these things.

They may restrict some things, or change them across environments, giving differeing results, as you have seen and posted.

We cannot possibly know what they know
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 2180
Location: Hampshire, UK

PostPosted: Thu Dec 27, 2018 10:11 pm    Post subject: Reply to: DB2 Timestamp field weird behaviour.
Reply with quote

Quote:
asking in terms of general knowledge on Db2


The relevant manual is probably the installation guide. Did you look there?
Back to top
View user's profile Send private message
Rohit Umarjikar

Senior Member


Joined: 21 Sep 2010
Posts: 2165
Location: NY,USA

PostPosted: Thu Dec 27, 2018 11:29 pm    Post subject:
Reply with quote

I ran Update query as below, so my test result is different than what you said.
Code:
update table a
set upd_ts = current date
where
a= 'xyz'
Before update value : 2018-12-23-12.02.10.330326
After update value  : 2018-12-27-12.02.10.330326

I have DB2 V12R1 installed for this test. So since we can have different results in different set ups so its best you provide full 26 bytes timestamp to be at safer side always. As suggested spend time looking at manuals to find what specific set up caused different results or get that info from DBA since they know it easily, when you know share us back so others can be benefited.
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 SECTION BASED ON FIELD speermb DFSORT/ICETOOL 7 Mon Jun 17, 2019 9:41 pm
No new posts Send text at cursor position on an IS... Domenic Ailati CLIST & REXX 10 Sun Jun 02, 2019 7:34 am
No new posts how to add date & timestamp to th... dharmaraok DFSORT/ICETOOL 5 Thu Apr 18, 2019 2:45 pm
No new posts Display Signed ZD field socker_dad DFSORT/ICETOOL 11 Thu Feb 28, 2019 1:17 am
No new posts Replace comp-3 field value to comp-3 ... dharmaraok DFSORT/ICETOOL 5 Fri Sep 14, 2018 3:46 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us