RE: Problem with view that uses an SQL function created from a procedure within a service program

That's a great addition... Thanks to the team!


Roger Harman
COMMON Certified Application Developer - ILE RPG on IBM i on Power




-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf Of Sue Romano
Sent: Saturday, October 31, 2020 5:46 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: RE: Problem with view that uses an SQL function created from a procedure within a service program



Sorry I'm late to respond, but I thought this group would be interested in
another option. A new SYSTOOLS function was part of the May 2020 database
PTF groups for 7.3 and 7.4: VALIDATE_DATA. It will touch all the data in a
file and tell you which rows, by RRN and column name, have bad numeric
data.

https://www.ibm.com/support/pages/node/6174171
https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_74/rzajq/rzajqudfvalidate.htm

THAT did it
By placing the results into a work file, I was able to capture the

relative record numbers as well as the data

SELECT rrn(a), a.*
FROM VIEW a
I could then look at the results ? see the highest rrn and using STRDBG

against the procedure in the service program, use the >following query

SELECT rrn(a), a.*
FROM VIEW a where rrn(a) > 180944

I was able to step through the procedure and look at the individual fields
It was one of the numeric fields that had spaces in it
I just manually changed the value and now everything is working fine

Sue Romano
Db2 for IBM i Development