Re: SQL's TIMESTAMP_FORMAT causes MCH3601

Hi Birgitta,

Interesting. Since we're in January, all of the dates have leading zeros,
and the query is working fine. But, I'd rather be safe than to get hit
with an IBM "fix" that changes the behavior. Using DIGITS eliminates the
guesswork.

Thanks,
- Dan

On Fri, Jan 27, 2017 at 1:00 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

timestamp_format( joDate || digits(joTime ), ‘MMDDYYHH24MISS’),

I'd also convert JODATE with digits. Without explicit conversion it is
internally converted into VarChar which is left adjusted and suppresses
leading zeros. Since your year format is MMDDYY, the month might be a
single digit month.
Also replace || with concat, since the || is not international, i.d. may
cause problems with different CCSIDs

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 [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Dan
Sent: Donnerstag, 26. Januar 2017 21:51
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: SQL's TIMESTAMP_FORMAT causes MCH3601

This problem is different from the one I posted here a few days ago (Data
mapping error on timestamp in SQL insert). Arco correctly diagnosed that
issue with the CHAR function dropping the leading zeros; I fixed the
problem by replacing CHAR with DIGITS. More background: The file being
queried in the query is the outfile from Carsten Flensburg's EXTJRNDTA
utility; that outfile is basically the outfile from DSPJRN with the space
consumed by JOESD redefined for the journaled file's record format. So,
the joDate and joTime fields used in the timestamp_format come from DSPJRN.

I am now sporadically getting MCH3601 (Pointer not set for location
referenced), followed by CPF503E (User-defined function error on member
EJD$CHARGE):
CAUSE: An error occurred while invoking user-defined function
TIMESTAMP_FORMAT in library QSYS2. The error occurred while invoking the
associated external program or service program QQQSVUSR in library QSYS,
program entry point or external name QQQTimestamp_Format, specific name *N.
The error occurred on member EJD$CHARGE file EJD$CHARGE in library
DBALEIQX. The error code is 2. The error codes and their meanings follow:
2 -- The external program failed before it completed.
RECOVERY: For error codes 1 and 2, determine the cause of the error from
either the SQLSTATE or a previously listed message.

Yesterday, when the query (below) ran from a RUNSQL command in batch, it
issued MCH3601 and CPF503E. When I ran the exact same query in interactive
SQL, it ran fine. Today, as I was preparing this post, I ran the same
query in interactive SQL, and got MCH3601 and CPF503E. When I ran it a
second time, it ran fine, and inserted all of the expected records in the
target file. Given that the MCH3601/CPF503E seems to be associated with
the timestamp_format function, would a call to IBM be in order?

Insert into IQX003H
select case
when (JOCODE, JOENTT) = (‘R’, ‘PT’ ) then ‘I’
when (JOCODE, JOENTT) = (‘R’, ‘UB’ ) then ‘B’
when (JOCODE, JOENTT) = (‘R’, ‘UP’ ) then ‘A’
when (JOCODE, JOENTT) = (‘R’, ‘DL’ ) then ‘D’
end,
timestamp_format( joDate || digits(joTime ), ‘MMDDYYHH24MISS’),
next value for IQX003Hseq,
SU_CRGID, SU_PATID, SU_ACCT, SU_OWNID, SU_DR, SU_RDR, SU_PVDID, SU_ORGID,
SU_SUBORG, SU_AGREEID, SU_ID1, SU_ID2, SU_ID3, SU_ORGINID, SU_DOS, SU_CHRG,
SU_PAID, SU_BAL, SU_DIAG, SU_CPT, SU_DESC, SU_DWNPMT, SU_WHOPVD, SU_LOCNAM,
SU_BALSRC, SU_BALTYP, SU_INSID1, SU_INSUPY1, SU_IREJDE1, SU_INSID2,
SU_INSUPY2, SU_IREJDE2, SU_INSID3, SU_INSUPY3, SU_IREJDE3, SU_STATUS,
SU_PIFDAT, SU_PIFTIM, SU_PIFUSR, SU_PIFPGM, SU_DELDAT, SU_DELTIM,
SU_DELUSR, SU_DELPGM, SU_PVDPTD, SU_LSTPDP, SU_LSTSTMD, SU_LSTSTM#,
SU_STMCNT, SU_CODE1, SU_C1RDTE, SU_CODE1DT, SU_CODE2, SU_C2RDTE,
SU_CODE2DT, SU_CODE3, SU_C3RDTE, SU_CODE3DT, SU_CODE4, SU_C4RDTE,
SU_CODE4DT, SU_CODE5, SU_C5RDTE, SU_CODE5DT, SU_HDATE, SU_FEEPLAN,
SU_RREQUST, SU_RREQDTE, SU_RREQWHO, SU_RREQPDT, SU_QCOUNT, SU_QDATE,
SU_QWHO, SU_N1, SU_N2, SU_N3, SU_N4, SU_N5, SU_N6, SU_N7, SU_N8, SU_N9,
SU_N10, SU_A1, SU_A2, SU_A3, SU_A4, SU_A5, SU_A6, SU_A7, SU_A8, SU_A9,
SU_A10, SU_D1, SU_D2, SU_D3, SU_D4, SU_D5, SU_D6, SU_D7, SU_D8, SU_D9,
SU_D10, SU_ENTDAT, SU_ENTTIM, SU_ENTUSR, SU_ENTPGM, SU_UPDDAT, SU_UPDTIM,
SU_UPDUSR, SU_UPDPGM from QTEMP/EJD$CHARGE

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD