Search code examples
db2db2-luwsql-pl

SQL0526N when referencing a DGTT (Declared global temporary table) in a FUNCTION

I would like to return content from a pre-created DGTT as a result-set for a table function.

But when I reference it, I get SQL SQL0526N / SQLSTATE 42995 error.

I can't see why. Documentation says only 3 reasons for 42995 sqlstate error:

Restrictions on the use of declared temporary tables: Declared temporary tables cannot:
- Be specified in an ALTER, COMMENT, GRANT, LOCK, RENAME or REVOKE statement (SQLSTATE 42995).
- Be referenced in an AUDIT, CREATE ALIAS, or CREATE VIEW statement (SQLSTATE 42995).
- Be specified in referential constraints (SQLSTATE 42995).

But none of them are being used.. The function is pretty simple:

BEGIN  ATOMIC 

    CALL DBAREP.SP_ROW_CHECKSUM(sSchema, sTable, sColumnList, sWhere, iRows) ;     

    RETURN 
         (SELECT * FROM SESSION.DBAREP_ROW_CHECKSUM ) ;
END@

What rule am I violating this time?

Solution

  • It's really not explicitly stated in docs, but Declared GTTs can't be used in inlined compound statements (with BEGIN ATOMIC ... END).
    You may use Created GTT in an inlined compound statement instead.