|
View previous topic :: View next topic
|
| Author |
Message |
tom wright
New User
Joined: 25 May 2011
Posts: 3
Location: usa
|
|
|
|
Hello all, I am receiving a gmt timestamp value and a gmt offset in a sequential file. I need to calculate the local timestamp value from that. But it is defeating me in DB2 COBOL.
precompiler is:
DB2 SQL PRECOMPILER VERSION 8 REL. 1.0
Cobol compiler is:
PP 5655-G53 IBM ENTERPRISE COBOL FOR Z/OS 3.3.1
The following works in spufi:
| Code: |
---------+---------+---------+---------+---------+---------
SELECT TIMESTAMP('2011-05-24-07.30.20')
+ -5 HOURS
FROM SYSIBM.SYSDUMMY1
;
---------+---------+---------+---------+---------+---------
---------+---------+---------+---------+---------+---------
2011-05-24-02.30.20.000000
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
|
However, when using embedded sql in COBOL, it does not work. I am getting -180 errors, yet all my variables SEEM to be orrect format and value.
Here are the relevant working storage fields:
| Code: |
05 WK-CXN-NSC-LCL-UDT-TS PIC X(26)
VALUE '2010-01-01-01.01.01.000000'.
05 WK-UTC-TCF-TM-QY PIC S9(6)V USAGE COMP-3.
05 WK-UPD-TS PIC X(26)
VALUE '2010-01-01-01.01.01.000000'.
05 :TAG:-UTC-TCF-TM-QY PIC S99V99 COMP-3.
05 :TAG:-UPD-TS PIC X(19).
|
and the relevant code:
| Code: |
INITIALIZE WK-CXN-NSC-LCL-UDT-TS
WK-UTC-TCF-TM-QY
WK-UPD-TS
COMPUTE WK-UTC-TCF-TM-QY = ( I-UTC-TCF-TM-QY * 10000 )
MOVE I-UPD-TS TO WK-UPD-TS(1:19)
MOVE '.000000' TO WK-UPD-TS(20:7)
EXEC SQL
SELECT TIMESTAMP(':WK-UPD-TS')
+ :WK-UTC-TCF-TM-QY HOURS
INTO :WK-CXN-NSC-LCL-UDT-TS'
FROM SYSIBM.SYSDUMMY1
END-EXEC
EVALUATE SQLCODE
WHEN ZEROES
CONTINUE
WHEN OTHER
DISPLAY '*****************************************'
DISPLAY ' DB2 DATE MATH ERROR FOR WK-CXN-NSC-LCL-UDT-TS'
DISPLAY 'SELECT ERROR ON SYSIBM.SYSDUMMY1, SQLCODE: '
SQLCODE
DISPLAY 'STATEMENT IS AS FOLLOWS:'
DISPLAY 'SELECT TIMESTAMP(:WK-UPD-TS)'
DISPLAY ' + :WK-UTC-TCF-TM-QY HOURS'
DISPLAY ' INTO :WK-CXN-NSC-LCL-UDT-TS '
DISPLAY ' FROM SYSIBM.SYSDUMMY1 '
DISPLAY ' WK-UPD-TS = ' WK-UPD-TS
DISPLAY ' WK-UTC-TCF-TM-QY = ' WK-UTC-TCF-TM-QY
DISPLAY ' WK-CXN-NSC-LCL-UDT-TS = '
WK-CXN-NSC-LCL-UDT-TS
DISPLAY ' I-UPD-TS = ' I-UPD-TS
DISPLAY ' I-UTC-TCF-TM-QY = ' I-UTC-TCF-TM-QY
DISPLAY '*****************************************'
DISPLAY ' RECORD NUMBER IN = ' MYCH-READ-CTR
DISPLAY '*****************************************'
MOVE +16 TO RETURN-CODE
ABEND-CODE
PERFORM 8900-DB2-ERROR
END-EVALUATE
.
|
the error messages:
| Code: |
DB2 DATE MATH ERROR FOR WK-CXN-NSC-LCL-UDT-TS
SELECT ERROR ON SYSIBM.SYSDUMMY1, SQLCODE: 000000018}
STATEMENT IS AS FOLLOWS:
SELECT TIMESTAMP(:WK-UPD-TS)
+ :WK-UTC-TCF-TM-QY HOURS
INTO :WK-CXN-NSC-LCL-UDT-TS
FROM SYSIBM.SYSDUMMY1
WK-UPD-TS = 2011-05-25-06.07.23.000000
WK-UTC-TCF-TM-QY = 05000}
WK-CXN-NSC-LCL-UDT-TS =
I-UPD-TS = 2011-05-25-06.07.23
I-UTC-TCF-TM-QY = 050}
*****************************************
RECORD NUMBER IN = 000000001
*****************************************
SQLCODE : 000000018}
SQLSTATE : 22007
|
At this point, after 3 days of banging my head on this, with a deadline looming, I am giving up and shipping this data over to Unix/Oracle where the date math functions are well documented and easy to use. At that, I may be working over the upcoming holiday weekend.
But this would be a very good thing to know for the future. My company is global and I may not always have access to an Oracle DB to use as a tool.
Any clues as to the obvious, or not so obvious things I am missing?  |
|
| Back to top |
|
 |
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| Code: |
SELECT TIMESTAMP(':WK-UPD-TS')
+ :WK-UTC-TCF-TM-QY HOURS
INTO :WK-CXN-NSC-LCL-UDT-TS'
Try this instead
SELECT TIMESTAMP(:WK-UPD-TS)
+ :WK-UTC-TCF-TM-QY HOURS
INTO :WK-CXN-NSC-LCL-UDT-TS |
You are trying to use the literal ':WK-UPDT-TS' as a value and you have and extra quote at the end of your statement. |
|
| Back to top |
|
|
tom wright
New User
Joined: 25 May 2011
Posts: 3
Location: usa
|
|
|
|
well son-of-a...
With all the other eyes I had look at that, no one saw that.
Never mind the extra "'", that was a typo when I pasted that line.
But the quotes around the field name were invisible to every one.
Too bad I do do not have access to Xpediter currently, or I might have seen that myself. Just doing displays did not show me that. |
|
| Back to top |
|
|
daveporcelan
Active Member
Joined: 01 Dec 2006
Posts: 792
Location: Pennsylvania
|
|
|
|
Tom,
FYI,
There is a difference between GMT (Greenwich Mean Time), and UTC (Cooridnated Universal Time).
The Offset usually refers to the later (UTC Offset).
This value varies depending on daylight savings time.
The current offset is 4 for the usa eastern time zone.
You are showing 5, so I assume you are in the centeral time zone.
Keep in mind this will change (up by one) in the fall.
I know this because we went through the same issues. |
|
| Back to top |
|
|
tom wright
New User
Joined: 25 May 2011
Posts: 3
Location: usa
|
|
|
|
Thanks Dave.
In this case I am receiving the gmt offset and the gmt in the same record, so it is already matched to the time the event described in the record occurred.
That would be tough: Getting a timestamp and then having to retrieve the location and the date DS occurrs in that location, and even IF DS applies to that location.
Oy.
Glad it is already in this data. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Quote: |
| Code: |
SELECT TIMESTAMP(':WK-UPD-TS')
+ :WK-UTC-TCF-TM-QY HOURS
INTO :WK-CXN-NSC-LCL-UDT-TS'
|
|
I can only see three quote marks. Why didn't that give an error of some sort? |
|
| Back to top |
|
|
Craq Giegerich
Senior Member
Joined: 19 May 2007
Posts: 1512
Location: Virginia, USA
|
|
|
|
| Bill Woodger wrote: |
| Quote: |
| Code: |
SELECT TIMESTAMP(':WK-UPD-TS')
+ :WK-UTC-TCF-TM-QY HOURS
INTO :WK-CXN-NSC-LCL-UDT-TS'
|
|
I can only see three quote marks. Why didn't that give an error of some sort? |
| Quote: |
tom wright
Never mind the extra "'", that was a typo when I pasted that line. |
|
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Thanks Craq, I could only see trees. |
|
| Back to top |
|
|
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
| Quote: |
[...]
| Code: |
INITIALIZE WK-CXN-NSC-LCL-UDT-TS
WK-UTC-TCF-TM-QY
WK-UPD-TS
COMPUTE WK-UTC-TCF-TM-QY = ( I-UTC-TCF-TM-QY * 10000 )
MOVE I-UPD-TS TO WK-UPD-TS(1:19)
MOVE '.000000' TO WK-UPD-TS(20:7)
EXEC SQL
SELECT TIMESTAMP(':WK-UPD-TS')
+ :WK-UTC-TCF-TM-QY HOURS
INTO :WK-CXN-NSC-LCL-UDT-TS'
FROM SYSIBM.SYSDUMMY1
END-EXEC
EVALUATE SQLCODE
WHEN ZEROES
CONTINUE
WHEN OTHER
DISPLAY '*****************************************'
DISPLAY ' DB2 DATE MATH ERROR FOR WK-CXN-NSC-LCL-UDT-TS'
DISPLAY 'SELECT ERROR ON SYSIBM.SYSDUMMY1, SQLCODE: '
SQLCODE
DISPLAY 'STATEMENT IS AS FOLLOWS:'
DISPLAY 'SELECT TIMESTAMP(:WK-UPD-TS)'
DISPLAY ' + :WK-UTC-TCF-TM-QY HOURS'
DISPLAY ' INTO :WK-CXN-NSC-LCL-UDT-TS '
DISPLAY ' FROM SYSIBM.SYSDUMMY1 '
DISPLAY ' WK-UPD-TS = ' WK-UPD-TS
DISPLAY ' WK-UTC-TCF-TM-QY = ' WK-UTC-TCF-TM-QY
DISPLAY ' WK-CXN-NSC-LCL-UDT-TS = '
WK-CXN-NSC-LCL-UDT-TS
DISPLAY ' I-UPD-TS = ' I-UPD-TS
DISPLAY ' I-UTC-TCF-TM-QY = ' I-UTC-TCF-TM-QY
DISPLAY '*****************************************'
DISPLAY ' RECORD NUMBER IN = ' MYCH-READ-CTR
DISPLAY '*****************************************'
MOVE +16 TO RETURN-CODE
ABEND-CODE
PERFORM 8900-DB2-ERROR
END-EVALUATE
.
|
[...]
|
I don't want to seem over-picky, but that's tough, I'd have to press the back-arrow now, but this is a good example of the problem of trying to display the Cobol code which is causing the problem (in the cases where you identify a problem programatically, so can tell which lines are causing the problem).
In general, it is a worthwhile idea. In practice it, usually, has the problem exhibited here. The code being displayed is not the code being compiled. And because it has the values displayed as well on the output, there is a tendency to stare for ever at the sysout wondering what went wrong.
Even if the code is identical to start with (which in this case would have given a compile error, unless Cobol now handles imbedded quotes directly), you only need someone lazy to do the next mod and, like copious comments, it can start to get in the way.
So, unless you go "the whole hog" (whatever that means) and do a pre-processor or a compiler-exit, someone, somewhere along the way, will screw up the plan.
The current compiler listing is the only reliable source of the source of the program that has failed. When displaying error messages, I always display WHEN-COMPILED so that I can be happy I have the current listing. When people came to me with problems, I ask for the current listing. When they say, this is it, I check the dump and usually it isn't, so off they go to the print room to get the real one. |
|
| Back to top |
|
|
|
|
