Peter it is a great idea - almost a design flaw of the timediff function.

If you are unfamiliar with the RFE system then I can post it for you - just
give me a shout . However I think you should give the RFE a try.



On Wed, May 12, 2021 at 9:09 AM Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

You can create your own private IBM id.

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so
they don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Peter Dow
Sent: Mittwoch, 12. Mai 2021 08:39
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: Re: TIMESTAMPDIFF() result should be BIGINT instead of INTEGER

Is it possible to do that without having an IBM customer ID?


On 5/11/2021 11:10 PM, Birgitta Hauser wrote:
If you think this would be a necessary enhancement, just open an RFE
(Request For Enhancements)

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown) "If you think education is expensive, try ignorance."
(Derek Bok) "What is worse than training your staff and losing them? Not
training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so
they don't want to.“ (Richard Branson)


-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Peter Dow
Sent: Dienstag, 11. Mai 2021 21:59
To: Midrange Systems Technical Discussion
<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: TIMESTAMPDIFF() result should be BIGINT instead of INTEGER

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> /
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com


--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

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

This mailing list archive is Copyright 1997-2021 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.