E9.2 SQL0462 error, but only when running a UBE

[Adam (DF)]

We have a database view that is represented as a table in JDE. There is a UBE that runs a Select/Fetch Next against this table (database view). When the UBE runs and the fetch happens, the UBE logs this error:

OS400QL010 - FetchSQLRow:Fetch failed. QSQLMSG *LIBL - SQL0462 - Procedure or user-defined function &1 in &2 returned a warning SQLSTATE

Apparently the JDE proxy user is missing some security to a database object used by our view, but the logged message doesn't say the name of the objects.

To troubleshoot, we have directly run the logged query using the proxy user, but then it runs just fine.

1. Have you run into the SQL0462 with a UBE?
2. Is there a way to make JDE log the actual error from the database (with the object names) instead of the error message template?
3. Do you have another idea for troubleshooting that I haven't thought of?

 

[JohnDanter2]

If you ran that ube with logging on, level 6 you can look at the jdedebuglog and see the SQL or view it tried to run before spitting out the error.

Bu tI think that errors means a security restriction. So it's either the MCU or the table/object you are running

 

[DSauve]

Does your database view use some functions or stored procedures (or their equivalent in db2 [which I'm not familiar with])? If so, it may be that you need to grant run access to the database proxy user being used within JDE when the UBE is run.

 

[Adam (DF)]

Hey guys, sorry for not responding. It seems my notifications aren't working.

If you ran that ube with logging on, level 6 you can look at the jdedebuglog and see the SQL or view it tried to run before spitting out the error.

Bu tI think that errors means a security restriction. So it's either the MCU or the table/object you are running

It is a security restriction, but I haven't figured out where. In any case, running with debug logging on level 6 only gives the same error message.

Does your database view use some functions or stored procedures (or their equivalent in db2 [which I'm not familiar with])? If so, it may be that you need to grant run access to the database proxy user being used within JDE when the UBE is run.

It does, and that is the problem. The error message should be telling me which object is failing the security check. So, I am stuck using the binary search method of troubleshooting.

 

[JohnDanter2]

Thats ok

It is a security restriction, so you will either a select to F00950 or F0006. As it could be object level or branch plant security, but in both cases F00950 should be referenced.
So check the log for any reference to F000950 and what it's looking for.
Or lines in the log just above the error you see

 

[Adam (DF)]

As it happens, this SQLSTATE warning is being thrown by the database to all clients (UBE, Form, SQL console). The warning seems to be caused, not by security as IBM suggests, but by possible data truncation during a comparison or coalesce operation. We are sure there is no truncation taking place, but data types make it look possible.

Therefore, because the UBE is checking File IO Status = Success, the UBE thinks the fetch failed, even though it succeeded. Since there is no way to ignore this warning in the UBE, we won't check file IO status, we will check the value of the variable we are fetching into. This is an overall fix.