| Author |
Message
|
| hayderr |
 Posted: Tue Aug 07, 2007 5:55 pm Post subject: CURRENT_GMTTIMESTAMP |
 |
|
Novice
Joined: 14 May 2007
Posts: 22
|
Hello all,
I am using ESQL (Broker 6) to update a record in one of my DB2 tables. The code I am using below:
UPDATE TABLENAME M TIMESTMP = CURRENT_GMTTIMESTAMP WHERE M.STATUS = 'N';
The updated statement is done correctly but with LOCAL time instead of GMT time. My insert statement works correctly with GMT time. Only when I do any update to any record the time will be converted to local.
I am really stuck in this, any comment or help is much appreciated.
Thank you |
|
| Back to top |
|
 |
| hayderr |
| Posted: Tue Aug 07, 2007 5:56 pm Post subject: |
|
|
Novice
Joined: 14 May 2007
Posts: 22
|
Corrected ESQL:
UPDATE TABLENAME AS M SET TIMESTMP = CURRENT_GMTTIMESTAMP
WHERE M.STATUS = 'N';
Regards |
|
| Back to top |
|
|
| AkankshA |
| Posted: Tue Aug 07, 2007 7:46 pm Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
UPDATE Database.<DatabaseschemaName>.<tableName> AS M SET TIMESTMP = CURRENT_GMTTIMESTAMP WHERE M.STATUS = 'N';
_________________
Cheers |
|
| Back to top |
|
|
| hayderr |
| Posted: Tue Aug 07, 2007 7:54 pm Post subject: |
|
|
Novice
Joined: 14 May 2007
Posts: 22
|
Yes, this is what I am doing:
UPDATE Database.{dbSchema}.TABLENAME AS M SET STATUS = 'C',
TIMESTMP = CURRENT_GMTTIMESTAMP
WHERE M.STATUS = 'N'; |
|
| Back to top |
|
|
| AkankshA |
| Posted: Tue Aug 07, 2007 8:00 pm Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
hmmm.... that seems vague behaviour 
how about storing the current_gmttimestamp in a variable and using debugger to see the cause
else just addup LOCAL_TIMEZONE in current_timestamp .. this shall give u gmttimestamp
_________________
Cheers |
|
| Back to top |
|
|
| hayderr |
| Posted: Tue Aug 07, 2007 8:24 pm Post subject: |
|
|
Novice
Joined: 14 May 2007
Posts: 22
|
I tried using a variable to pass the value. I used the debugger, the value I am passing to the Update statement is the correct one. When i check the table, the time is LOCAL.
DECLARE myTime GMTTIMESTAMP CURRENT_GMTTIMESTAMP;
UPDATE Database.{dbSchema}.TABLENAME AS M
SET TIMESTMP = myTime
WHERE M.STATUS = status; |
|
| Back to top |
|
|
| AkankshA |
| Posted: Tue Aug 07, 2007 8:29 pm Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
seems like the problem wd ur db table
pls paste the table definition here.... do u have any constraints set on timestmp column???
_________________
Cheers |
|
| Back to top |
|
|
| hayderr |
| Posted: Tue Aug 07, 2007 9:09 pm Post subject: |
|
|
Novice
Joined: 14 May 2007
Posts: 22
|
CREATE TABLE SCHEMA.TABLENAME (
a CHAR( NOT NULL,
b CHAR(6) NOT NULL,
c INTEGER NOT NULL,
d CHAR(,
e CHAR(6),
f CHAR(2),
g CHAR(1),
h CHAR(,
i CHAR(6),
j CHAR(12),
k CHAR(1)
CONSTRAINT xyz
CHECK (UPPER(k) IN('A',
'N','S')),
TIMESTMP TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP
);
The problem is only with the UPDATE statement.
Thnak you in advance |
|
| Back to top |
|
|
| AkankshA |
| Posted: Tue Aug 07, 2007 9:22 pm Post subject: |
|
|
Grand Master
Joined: 12 Jan 2006
Posts: 1494
Location: Singapore
|
TIMESTMP column is populated with local timestamp which is default value as specified in the table...
that shows that ur DB is not getting any value in TIMESTMP column.... i.e. null is being transferred there
weird
hmm... can't really provide u any accurate reasoning for this.. things wud be guess only... i would say check up the flow using debug once again as working wd insert and not with update seems strange
however a trial can be done using passthru
_________________
Cheers |
|
| Back to top |
|
|
| hayderr |
| Posted: Wed Aug 08, 2007 3:51 pm Post subject: |
|
|
Novice
Joined: 14 May 2007
Posts: 22
|
Thank you,
I will try PASSTHRU statement and see the results
Thanks again |
|
| Back to top |
|
|
| hayderr |
| Posted: Mon Aug 13, 2007 8:14 pm Post subject: |
|
|
Novice
Joined: 14 May 2007
Posts: 22
|
Hello all,
I tried the PASSTHRU and the it is the same, with the UPDATE the time is LOCAL.
Is it worth opening a PMR for it?
Thanks |
|
| Back to top |
|
|
|
|
