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
midrange.com
