I'm looking for a component that will set defaults and check data lengths for me and output a warning or error schema if any field fails the checks. This should be easy to make generic for any database, but the specific one I am working with is AS400 DB2.
The component should:
Apply schema defaults if a value is null and the column does not allow nulls
String processing
Convert from String to CHAR or VARCHAR and optionally trim on the right if the target is a varchar.
Test the length of the string and truncate automatically if necessary, optionally duplicating the record to the error schema and indicating which field was in error.
Numeric processing
Convert automatically between all numeric formats including BigDecimal and BigInt.
Test the number against the target precision and scale, if overflow occurs provide for a literal replacement value, and optionally duplicating the record to the error schema and indicating which field was in error.
On truncation of significant decimal digits, optionally duplicate the record to the error schema and indicate which field was in error.
Is there a component that does this, or would it have to be a custom component? It seems that it would be a useful component, and easy to make generic.
@mmurphy, In my view, a quick workaround for your ask is to go the XML/XSD route if possible. Convert your incoming data to an xml and run that against a pre-defined xsd that can enforce all kinds of validations that you are asking for. In addition, if you go the xsd route, capturing the error messages and exact discrepancies between expected and actual data will be seamless and your main stream of process would still move forward and push the errored data to go the rejected route.
I'm doing this for database migration. Some of the databases we migrate are massive, converting to XML seems like it would take way to much memory and processing time as XML is extremely inefficient when it comes to content vs. metadata.
Yes, I do all this in tMap components. Over and over again. Once I have done a thing enough times, I look for a way to automate it. All of the needed data is available in the schemas. I just want a nice component to drop in to do this.
If you think through and estimate the processing overhead, rather than trying to validate every column value at each record level, XML validation happens at a record level and would relatively put lesser processing overhead when compared to custom coding where you converse every incoming row to multiple rows before you can qualify a row as valid and take it to next level of processing.
Even if your data/databases are massive, your requirement of data/schema/metadata validation should happen at column level.
