Edit physical file As400

[christia...@gmail.com]

Hallo to everyone,

Does anyone knows the command to edit data in a AS400 physical file that can't be opened by DFU or UPDDTA because there are wrong chars in a packed file?
I wouldn't like to creare an RPG program just to do that.
Thanks a lot!

Christian

[CRPence]

On 13-Jan-2016 03:19 -0700, christia...@gmail.com wrote:
>
> Does anyone knows the command to edit data in a AS400 physical file
> that can't be opened by DFU or UPDDTA because there are wrong chars
> in a packed file?

> I wouldn't like to create an RPG program just to do that.
>

I suppose that depends somewhat on what qualifies as "edit". If that
mandates the use of a hex editor, then Display/Alter/Dump of the Service
Tools is available, but not recommended; there may be 3rd party tooling
that offers that capability against column\row data. There is the
ability to get the raw data into a source physical file member that
could then be edited with a [e.g. client-based] hex editor, and that
data copied back using the inverse; the Copy File (CPYF) utility offers
a Format Option (FMTOPT) of Convert To\From Source (*CVTSRC) to effect that.

If the row(s) with the Packed Binary Coded Decimal (BCD) column(s)
can be selected without dependence on the column(s) with the bad data,
then the "Change Data in Query Manager Table" feature of the Start Query
Manager (STRQM) may suffice to "edit" the data; the only opportunity
however, being, to /set/ a value for the condition diagnosed as msg
QMR0341 "Not a valid number." because there is no value presented as its
assumption\interpretation of what that bad data might intend to represent.

The temporary DFU program generated for the Update Data (UPDDTA)
feature of the Data File Utility (DFU), nor a specifically-created DFU
program within Start DFU (STRDFU) seem to enable setting the value as is
allowed with the aforementioned QM feature. And because the DFU seems
also to disallow the use of shared opens, the potential ability to use
the Ignore Decimal Data Errors (IGNDECERR) feature of the Open Query
File (OPNQRYF) with the Open Options (OPTION) of *ALL can not be tested;
the lower-level possibilities consist of ignore-only and
ignore-and-modify. If perhaps the OP has DBU [i.e. meant DBU vs DFU],
then that feature likely will function against a shared Open Data Path
(ODP); i.e. the effect of IGNDECERR(*YES) on a shared ODP with matching
open options could be tested for whether ignore or ignore\fix.

Because the Query/400 uses the forced-conversion [i.e.
ignore-and-modify; I forget the /name/ of the feature, but I have
written about that in the past -- forced ¿Conversion Mapping?], the Run
Query (RUNQRY) feature specifying a Query Definition (QRYDFN) on the
Query Definition (QRY) prompt wherein that Ignore Decimal Data Errors
setting [of the Specify Processing Options] is explicitly Y=Yes will
effect a form of /correction/ to the invalid BCD data. Thus the
original data could be replaced in-full [if the amount of data and
exclusive access to the member are allowable]. The script to effect
that is to [assuming every object reference is properly library-qualified]:
• create a duplicate of the file.mbr with the bad data [e.g. as
CPY_BADDTA]
• create the Query Definition [with the ignore setting activated; e.g.
as THE_QRY] with the file specification naming CPY_BADDTA as input
• issue the following request to replace the original data [e.g. in
the file ORIGINALPF with member BADDTA]:

RUNQRY THE_QRY ((CPY_BADDTA)) *OUTFILE
OUTFILE(ORIGINALPF BADDTA *RPLMBR)

FWiW I would probably use one or a couple Quick&Dirty SQL scalar UDFs
to both locate and /correct/ the data; if the change to the data was
either to set the value to a literal\constant [e.g. the max negative] or
to the NULL value, then only finding the bad values is required.
Finding /bad/ Packed BCD data is a rather trivial task, and I have
written multiple times about doing so using the SQL. If not merely set
to a literal\NULL value, then what is the chosen algorithm to /correct/
the data becomes the question; a *presumption* by an algorithm about
what the meaning is of garbage-in, is of course, garbage-out, such that
the only *proper* correction is to *set* the value to what is
known\understood by a human to be the correct value. So for example,
given a file ORIGINALPF with invalid BCD data in columns P72 and P50,
the following statement might effect that:

UPDATE ORIGINALPF
SET P72 = FIXBCD(HEX(P72)) /* or?: P72=-99999.99 */
, P50 = FIXBCD(HEX(P50)) /* or?: P50=-99999 */
WHERE TSTBCD(HEX(P72))='*ERR'
OR TSTBCD(HEX(P50))='*ERR'

FWiW given uniquely keyed data, there is a Member Option (MBROPT)
option of Copy File (CPYF) from which the original file can be updated
from a copy of the file with the corresponding row data having been
corrected, and similarly there is the SQL MERGE statement for that purpose.

--
Regards, Chuck

[CRPence]

On 13-Jan-2016 09:20 -0700, CRPence wrote:
>
> <<SNIP>> The temporary DFU program generated for the Update Data

> (UPDDTA) feature of the Data File Utility (DFU), nor a
> specifically-created DFU program within Start DFU (STRDFU) seem to
> enable setting the value as is allowed with the aforementioned QM

> feature. <<SNIP>>

I was thinking the Ignore Decimal Data Errors feature of the DFU
might be limited to availability in only the System/36 Environment, but
per Roberto's reply, I see I overlooked the "Suppress Errors" capability
that can be set in a DFU program. I presume that in the S/36EE, that
Suppress capability is the default behaviour for a temporary DFU program
much like how the "Processing Options" of the Query Definition are
environment-specific when no explicit choice is made with regard to
ignoring decimal data errors.

--
Regards, Chuck