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
 
GMT offset calcs, in cobol with host variables not working

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

New User


Joined: 25 May 2011
Posts: 3
Location: usa

PostPosted: Thu May 26, 2011 8:50 pm    Post subject: GMT offset calcs, in cobol with host variables not working
Reply with quote

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? icon_confused.gif
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 May 26, 2011 9:52 pm    Post subject:
Reply with quote

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
View user's profile Send private message
tom wright

New User


Joined: 25 May 2011
Posts: 3
Location: usa

PostPosted: Thu May 26, 2011 10:15 pm    Post subject:
Reply with quote

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.

icon_lol.gif

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
View user's profile Send private message
daveporcelan

Active Member


Joined: 01 Dec 2006
Posts: 683
Location: Pennsylvania

PostPosted: Thu May 26, 2011 10:16 pm    Post subject:
Reply with quote

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
View user's profile Send private message
tom wright

New User


Joined: 25 May 2011
Posts: 3
Location: usa

PostPosted: Fri May 27, 2011 12:25 am    Post subject:
Reply with quote

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
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Fri May 27, 2011 2:24 am    Post subject:
Reply with quote

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
View user's profile Send private message
Craq Giegerich

Senior Member


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

PostPosted: Fri May 27, 2011 2:49 am    Post subject:
Reply with quote

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
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Fri May 27, 2011 11:58 am    Post subject:
Reply with quote

Thanks Craq, I could only see trees.
Back to top
View user's profile Send private message
Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7315

PostPosted: Fri May 27, 2011 1:48 pm    Post subject: Reply to: GMT offset calcs, in cobol with host variables not
Reply with quote

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
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 Assembler Macro Copybook converted to... Ziquilix PL/I & Assembler 4 Tue Sep 26, 2017 3:07 am
No new posts LIBDEF not working in called proc packerm CLIST & REXX 0 Fri Sep 15, 2017 7:22 pm
No new posts Working with hex values in Filemanage... Emile Straker IBM Tools 0 Tue Sep 12, 2017 1:42 am
This topic is locked: you cannot edit posts or make replies. Extract all "IF" Statements... Adarsh Damodaran CLIST & REXX 1 Wed Sep 06, 2017 9:28 am
No new posts ALPHABETIC check in COBOL vidyaa COBOL Programming 8 Thu Aug 17, 2017 7:13 pm

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