× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



Hi Everyone,

On a v7r3 box, I was using timestampdiff to determine the duration of a job in microseconds.  I'd never used it before, but it seemed pretty straightforward, but the result was  ++++++++++++++

SELECT JOB_NAME
     , JOB_ACTIVE_TIME
     , JOB_END_TIME
     , TIMESTAMPDIFF(1, char(JOB_END_TIME - JOB_ACTIVE_TIME)) uSeconds
   FROM TABLE(QSYS2/JOB_INFO( JOB_USER_FILTER => 'myusrid')) JI
  WHERE JOB_NAME = '123456/myusrid/myjob'

 JOB_NAME                    JOB_ACTIVE_TIME JOB_END_TIME               USECONDS
 123456/myusrid/myjob    2021-05-08-06.20.48 2021-05-08-07.46.29         ++++++++++++++


The problem is that for some reason, TIMESTAMPDIFF() returns INTEGER.  INTEGER is of course 4 bytes or 2^31 = 2,147,483,648 (signed).  That many microseconds is about 35 minutes and 47 seconds. Any job whose duration is longer gets SQLSTATE 22001 or diagnostic message CPD5036 "Data mapping error on member QSQPTABL." reason 27, which is "27 -- For the CAST operation, the character destination field is too short to hold all the significant digits of the source."

And when there is no explicit CAST operation in the SQL statement, and several possible implicit CAST operations, time is wasted figuring out where the problem actually is.

It seems like BIGINT would have been a better choice. 8 bytes gives 2^63 or 9,223,372,036,854,775,808 microseconds (signed), or over 106,751,991 days. The consumer of the result can choose to put their result into a smaller variable.

These days, microseconds is overkill, so I dropped back to just seconds to resolve my problem, but it still seems like a poor choice of result size for TIMESTAMPDIFF.


--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx> /

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.