Wrapping a UDF around it is probably a good idea
Select ..., cleanThisColumn(CobolColumn)...
Instead of
Select ..., CobolColumn...
Would be a good idea. For some good examples look at Alan Campin's idate routines.
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jay Vaughn
Sent: Wednesday, February 20, 2019 3:55 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL and invalid data in field
So occasionally we come across data in our files that SQL does not like to play with.
RLA handles it fine. But as we move more towards SQL it is becoming a problem.
Example
Embedded sql...
select * from table into :ds_table
when the fetch occurs to move the row into the host ds, we get the following error if "There is data in a decimal field that is not valid.
Message ID . . . . . . : CPF5035 Severity . . . . . . . : 10
Message type . . . . . : Diagnostic
Date sent . . . . . . : 02/20/19 Time sent . . . . . . :
15:26:00
Message . . . . : Data mapping error on member SRVCOLI.
Cause . . . . . : A data mapping error occurred on field
SRVCOLI_1.COLI_CHANGE_DATE in record number 0, record format *FIRST, member
number 1, in member SRVCOLI file SRVCOLI in library LDATASV3, because of
error code 1. The error code meanings follow:
1 -- There is data in a decimal field that is not valid.
2 -- A significant digit was truncated.
3 -- A floating point value exceeded the maximum representable value.
4 -- A floating point value was less than the minimum representable value.
5 -- A binary floating point value could not be used for the attempted
operation because it is not a number, or is not valid.
6 -- A floating point value could not be mapped to packed decimal, zoned
More...
I know the very first plan of action should be figure out where this invalid data is making its way into the file and my guess is a COBOL pgm, but at risk of major judgement, we just don't have time to go find and fix that. We have however looked into the issue and determined they are fields that are simply initialized incorrectly and then written out to the file.
Our suspicions are that a COBOL pgm does it.
Now, we have a simple fix pgm we can run over the file when we encounter this issue and it cleans the entire file. The fields essentially are initialized correctly back to 0. The pgm is short and sweet and looks like...
halwnull(*inputonly) fixnbr(*zoned)
fsrvcoli up e k disk
c update rcolst
That's all there is to it.
BUT I'm wanting to try and have SQL detect and handle the data during processing so that processing is not interrupted.
If anyone has any experience with this issue and wish to share the resolution I'd appreciate it.
Jay
--
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@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
