On 25-Dec-2014 20:14 -0600, Gary Kuznitz wrote:
Thank you very much for the reply...
Comments below.
On 25 Dec 2014 at 16:52, CRPence wrote:
On 25-Dec-2014 12:18 -0600, Gary Kuznitz wrote:
I'm trying to copy a file from the IFS to a DB file. I'm getting
errors on every record. The error is CPF2973
The from-program and to-program were not included in the
information below. The partial symptom string is msgCPF2973
F/QCPIMPRT FM/QCPIMPRT FP/Send_type_msg stmt/15 ... TM/QDBCTHTWRK
I am only working with one program that doesn't call any other
programs. I don't know why the from-program and to-program were not
included in the information.
The implication is that the information that was presented, as taken
from the joblog, was missing information. The layout of the data is
such that the Message Identifier (MSGID), the Message Type (MSGTYPE),
Severity (SEV), and a number of other details appear across one line,
and then several lines follow showing more details; i.e. the snippet of
lines left included\quoted just below:
From module . . . . . . . . : QCPIMPRT
From procedure . . . . . . : Send_type_msg
Statement . . . . . . . . . : 15
<<SNIP>>
Thus I was meaning to suggest, that what was included\given as
supporting information from the joblog, was effectively truncated;
headless really. The above should have appeared in the joblog instead
more like the following lines wherein I included the heading line and
the missing line both as wrapped lines; alternate formatting could be
used to include all of that information, yet I can understand the desire
to convey the necessary information while also desiring to avoid
spending possibly minutes of editing to further /beautify/ the information:
MSGID TYPE SEV DATE TIME
FROM PGM LIBRARY INST TO PGM LIBRARY INST
CPF2973 Completion 00 12/26/14 17:54:52.072704
QCPIMPRT QSYS *STMT UsrPgm UsrLib ????
From module . . . . . . . . : QCPIMPRT
From procedure . . . . . . : Send_type_msg
Statement . . . . . . . . . : 15
An APAR for v5r2 described a similar issue; at least having noted
the missing replacement text for that message.
The specific issue with the missing from-file replacement text for
the truncation msg CPF2973 issued for Copy From Import File (CPYFRMIMPF)
was not corrected until v6r1. The origin for that specific issue is per
use of the FROMSTMF vs FROMFILE in the given scenario. The problem
being, that the messaging used for the copy utility's truncation error
CPF2973 did not properly account for the IFS naming [only 10-byte naming
was accommodated], until the correction with the IBM i 6.1 changes,
since which a different message is sent.
In the CLP I have:
CHGJOB CCSID(37)
Message . . . . : 5700 - CPYFRMIMPF
FROMSTMF('/home/Payroll/ATU_PAYROLL_DATA_FY14_372_398.txt')
TOFILE(GARY2/MBRPAYATU) MBROPT(*ADD) RCDDLM(*CRLF) STRDLM('"')
FLDDLM('|')
FROMRCD(2 *END) ERRRCDFILE(GARY2/MBRPAY1ERS) ERRRCDOPT(*ADD)
RPLNULLVAL(*FLDDFT)
I am getting no records in the error file.
I don't have any double quotes in the file past the first record.
This is one record of the input file from the IFS:
372|7/12/2013|000027857|Last Name, Brian
J|3926|00001|A3|6/22/2013|000764|SIGNAL
INSPECTOR|A|AWD|AWARD|1|1|20|20
The above is all on one line. = One record.
Does anyone have any idea why I would be getting the error?
PS: V5R3
Is the Copy From Import File (CPYFRMIMPF) running using the v5r2
support; i.e. is there a Data Area (DTAARA) named QCPFRMIMPF in
QSYS with the string value 'CPV5R2'? If so, then allowing the
feature to use the actual V5R3 support might assist; i.e. delete
the data area, or modify the value of the string data, because the
v5r2 code path had an almost identical symptom.
I don't have that DTAARA on the system.
Note that the date data values are in *USA format, but the default
for the command tells the import feature that the date data values
should be *ISO.
Thank you for catching that. I'm not sure the date values are going
to work anyway.
The feature understands all of the /standard/ formats irrespective
the specification, because that is what the SQL does\understands. Thus
if the date values are any of *EUR, *ISO, *USA, *JIS then they will be
understood. Only _one_ of the non-standard dates can be understood by
the SQL, and that choice would have to be explicitly specified on the
Date Format (DATFMT) and Date Separator (DATSEP) parameters; i.e. if for
example some of the date values in the Separated Values records are *MDY
and others are *DMY, then only those values consistent with the command
specification [DATFMT+DATSEP] will be understood.
The dates in the input file may have different formats.
One may be: 7/12/2013
Another may be: 12/12/2013
I have changed the date data values to *USA.
Those two examples are not considered "different formats" in the Date
Format (DATFMT) sense; though formatted slightly differently as
'M/DD/YYYY' vs 'MM/DD/YYYY', those are both valid *USA literal\constant
[character string] date values. The implication was to explicitly
specify DATFMT(*USA), though I suspect [per my prior comments; and even
with a test on v5r3], the chosen specification should be moot if indeed
all records use only *USA date literals; should not matter, between use
of DATFMT(*USA) or [defaulted to] DATFMT(*ISO).
In the DDS I didn't specify the Date fields as dates.
A TRANSDATE 10 TEXT('Transaction Date')
A ten-byte alphanumeric should be valid to support any valid date
literal. I would not expect changing that to a Date data type would assist.
The command suggests the strings are delimited with the
double quote, but the items in the given record clearly are not.
I wasn't sure what that meant. Does that mean I should use *None?
Not "should use", but "might consider using, according to what is
desirable". The command defaults to STRDLM('"'), but the string data
appears consistently [though only inferred with the one sample row as a
given] to appear _without delimiters_. However if some rows do include
the double-quote as delimiter, for example to ensure that strings can
contain embedded pipe ['|'] characters, thus to avoid those being
mistaken as Separator characters, then the STRDLM(*NONE) specification
would *not* be desirable. The specified string delimiter designation is
to suggest to the utility, that _if_ there is a delimiter for the data
for a character string column, then the specified delimiter should be
removed from the datum before becoming column data.
Performing the request from the command-line in a new job rather
than via the CLP and in a job that has performed other work might
exhibit a different effect; may be worth a test attempt.
That same request made with the given record on the PUB1.DE public
v5r3 system functioned without any errors.
If none of that is helpful, then ...
That was very helpful. It's fine running now.
Thank you tremendously. You are a life saver.
Neither the change to a Date data type nor the string delimiter seem
likely to have been something that would have assisted. Not sure if
perhaps running the request distinct from any other activity performed
prior within the job or outside the CLP, or even if perhaps a changed
query [from a problematic cached query] due to the change in the target
output database file might have been necessary to progress; i.e. the
positive effect may be more chance than anything else.
The means to create the target of the import and the error record
file were not included, but those could be helpful to identify the
issue. Also helpful would be the first few stream records of the
STMF, regardless the /first/ record was asked to be skipped; much
more relevant, given "every record" gets the error. A sample of
just a few redacted records that still exhibit the errors would be
ideal, along with the hexadecimal code points of the data; plus the
CCSID of the STMF and the CCSID of the fields of the DBF.
If the problem returns and\or persists in another environment, then
the additional information [e.g. the entire DDS, not just for the Date
field] describing the full scenario is most beneficial to assist others
to be able to assist with [understanding and recovering from] the
failing scenario.
midrange.com
