RE: SYSTOOLS.VALIDATE_DATA issues?

Hi Birgitta,

Thanks again for your insights.

I'll reach out if we get stuck on this.

Regards,
Richard Schoen
Web: http://www.richardschoen.net
Email: richard@xxxxxxxxxxxxxxxxx

---------------------------------------------------------------------

message: 1
date: Wed, 16 Jul 2025 08:44:22 +0200
from: "Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>
subject: RE: SYSTOOLS.VALIDATE_DATA issues?

I have written my own UDTF which will detect invalid numeric values (and it already existed before the SYSTOOLS-Functions).
But I do it a little different.
In my function I use SQL to read the complete table. Invalid numeric values will cause a CPF5035 Error.
... using fully dynamic SQL (with Descriptor Areas) in (embedded) SQL is a little tricky.
After having read the complete file I search the job log for CPF5035 messages. These messages provide all necessary information.

Unfortunately, I cannot give out for free, but just in case you are interested contact me directly (Hauser@xxxxxxxxxxxx)

But you could do it manually, i.e. first read the complete table with ACS and then use the JOBLOG_INFO table function to find the CPF5035 messages.

The other option is, to write an UDTF also based on a dynamic SELECT-Statement that will return the Error Messages.
The WHERE conditions with TRY_CAST can be prepared dynamically based on the SYSCOLUMNS information.


Mit freundlichen Gr??en / Best regards

Birgitta Hauser
Modernization ? Education ? Consulting on IBM i Database and Software Architect IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown) "If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"
"Train people well enough so they can leave, treat them well enough so they don't want to. " (Richard Branson) "Learning is experience ? everything else is only information!" (Albert Einstein)