SQLCODE -579: Routine not allowed to read data
SQLCODE -579 (SQLSTATE 38004) means a user-defined function or stored procedure tried to read data, but its definition, or the definition of a routine that called it, doesn't allow reading. The routine needs READS SQL DATA or MODIFIES SQL DATA.
Other names for this code
Also written as SQL -579, SQLCODE=-579, SQLCODE = -579, db2 -579, sqlstate 38004, SQL0579N, attempted to read data when the definition of the function or procedure did not specify this action.
What it means
Functions and stored procedures are defined with an SQL data access level. CONTAINS SQL lets a routine run SQL that doesn't touch table data, such as SET statements. NO SQL allows no SQL at all. To run a SELECT, or open a cursor against a table, a routine needs READS SQL DATA or MODIFIES SQL DATA.
Db2 enforces the level while the routine runs. When a routine without read access issues a statement that reads data, it gets -579.
As with -577, nested calls use the most restrictive level in the chain. A routine defined CONTAINS SQL or NO SQL that calls another defined READS SQL DATA also gets -579. The message gives the object type and the routine name. SQLSTATE 38004 is the standard SQL code for this condition.
Common causes
- A routine first written as pure logic, defined CONTAINS SQL or NO SQL, later gained a lookup against a table.
- A function defined CONTAINS SQL calls another function or procedure that reads data.
- The CREATE statement was copied from a template with the wrong data access level.
- An external routine calls a shared module that runs a SELECT nobody knew about.
How to find the problem
- Take the routine name from the message.
- Check its level with
SELECT SCHEMA, NAME, ROUTINETYPE, SQL_. C means CONTAINS SQL and N means NO SQL.DATA_ ACCESS FROM SYSIBM. SYSROUTINES WHERE NAME = 'GET_ RATE' - Check the levels of the routines that call it and the ones it calls.
- For an external routine, search its source, and the modules it calls, for SELECT, DECLARE CURSOR and FETCH.
How to fix it
- Change the routine to READS SQL DATA, or MODIFIES SQL DATA if it also changes data, with ALTER FUNCTION or ALTER PROCEDURE, or re-create it.
- Raise the level of any calling routine that is more restrictive.
- If the routine should stay free of table access, move the read to the caller and pass the value in as a parameter.
IBM documentation
IBM documentation: -579. Use the documentation for your installed release.
Related codes
Questions
What does CONTAINS SQL allow?
SQL that doesn't read or change table data, such as SET statements and simple expressions. Any SELECT against a table needs READS SQL DATA.
Can a NO SQL routine call one that reads data?
No. The caller's level limits everything it calls, so the call fails with -579.
Is -579 the same as -577?
They are the same kind of check. -579 is for reading data and -577 is for changing it.