SQL0462 with queries over IBM created SQL Views in QSYS2

Troubleshooting

Problem

You may see warning SQLCode +462 (SQL0462) with a SQLState of 01548 when running a query over the various IBM created SQL Views in QSYS2

Symptom

The SQLCode returns a +462 with a SQLState of 01548.

You may see this information on the execution of your SQL, either in the joblog showing a SQL0462 or the database monitor showing a SQLCode (QQI8) of +462 and a SQLState (QQC81) of 01548, etc.

Note that this is a positive SQLCode, which is a warning, and not a failure.

Cause

The IBM created SQL Views in QSYS2 are designed to simplify retrieving information in an easy to read format.

Some of these IBM created SQL Views have calls within their logic to IBM created user-defined table functions.

These IBM created user-defined table functions may return a SQLCode +462 with a SQLState of 01548 if that IBM created user-defined table function touches a schema (etc.) that the user associated with the execution of that query does not have access to.

In this case, no data is returned for that schema (etc.) - as it is not applicable - and the query continues on.

This SQLCode is not necessarily stating that the authority issue is directly related to the query.

To clarify with an example, refer to this query:

SELECT 'A' INTO :hv1
FROM QSYS2.SYSSCHEMAS
WHERE SYSTEM_SCHEMA_NAME = '<YOURLIB>'

This may return a SQLCode +462 but the CURRENT USER *does* have authority to schema <YOURLIB>.

So why the SQLCode of +462?

SQL View QSYS2.SYSSCHEMAS is calling user-defined table function OBJECT_STATISTICS and that is (attempting) to access more schemas that just '<YOURLIB>'.

The CURRENT USER does not have enough authority to one (or more) of those schemas.

Since this query does not need any information from those schemas, a (warning) SQLCode of +462 is returned and the query continues on.

You will usually only see this SQLCode when you are running with a CURRENT USER with authority to only a subset of the IBM i schemas (etc.)

The SQLState of 01548 is also referenced in this V7R2 URL

http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_75/rzajq/rzajqudfobjectstat.htm?lang=en

which describes the OBJECT_STATISTICS table function in more detail

Diagnosing The Problem

The SQL0462 joblog may show text like this:
Message . . . . : Procedure or user-defined function OBJECT_STATISTICS in QSYS2 returned a warning SQLSTATE.

Resolving The Problem

As this is a warning message and not an actual error, you can ignore this warning message.
Another option is to run that query with a CURRENT USER that has authority to all schemas (etc.) on they system.

[{"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001go6AAA","label":"IBM i Db2-\u003ESQL Services \/ table functions"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Version(s)","Line of Business":{"code":"LOB68","label":"Power HW"}}]

Historical Number

N1012655

Was this topic helpful?

Document Information

More support for:
IBM i

Component:
IBM i Db2->SQL Services / table functions

Software version:
All Version(s)

Operating system(s):
IBM i

Document number:
687139

Modified date:
28 November 2024

UID

nas8N1012655

IBM Support

Tips