On 03-Dec-2013 12:52 -0800, Booth Martin wrote:
The file has about 2.5 million records. Some of those records, I
have no idea how many, give an error and my RPG program fails.
<ed: question moved later>
Cause . . . . . : The condition was caused by the program output
data containing below hex 40 ...
Sometimes errors with that implication may arise from something other
than the database row data, so be sure to verify the origin. Not
knowing the specific message ID and the context of the failure, I can
not even guess if that might be the case here; heck, I can only presume
that it is a Display File (DSPF) that is the output device file.
One option is to monitor for the error, translate the data such that
all data below 0x40 [or is 0xFF?] is converted to a /displayable/
character, then perform the output operation again; i.e. reactive to the
condition, assuming it would be rare. Of course that translate function
could always be performed, for that cost, as a proactive measure. AFaIK
the DDS never added a keyword to effect translation to an output field.
AFaIK the UIM implicitly translates the data to its output fields
[thus colorizing 5250 datastream bytes are lost].
Another option is to get the database to do that translation before
ever passing the data to the program, which although a proactive
request, likely would be faster than performing the request on the
row-data retrieved into the record buffer of the program. The SQL has a
TRANSLATE() function and the DDS has a TRNTBL function.
Is there a way to process the file and delete those troublesome
records?
Typically data would be /corrected/ rather than deleted; by first
analyzing the data to determine if the corrective actions might possibly
require manual intervention, or the review may determine that an
automated correction might be possible.
If analysis of the unexpected data proves to be something that can be
updated automatically with a common request for all rows, then an SQL
statement such as the following might be used to /modify/ the data in
the desired manner; e.g. replace all characters that have a hex code
point less than 0x40 with the empty string which in-effect removes the
character from the character string:
update the_file
set the_bad_column = replace(
translate(the_bad_column, x'3F'
, /* next two lines must be a contiguous hex constant */
x'000102030405060708090A0B0C0D0E0F101112131415161718191A1B1C1D1E1F
202122232425262728292A2B2C2D2E2F303132333435363738393A3B3C3D3E3F'
, x'3F')
, x'3F', '')
And yes... I understand that the 0x3F as the last byte of the
from-string in the TRANSLATE scalar is useless [redundant actually] in
the above expression, but it is coded in case the to-string might be
modified and optionally the REPLACE scalar removed.
midrange.com
