CODEWITHSUNDEEP
db2user-defined-functionsdb2-luwfederationfederated
Manoj
Manoj

Reputation: 31

Getting error while trying to execute a DB2 UDF which contains a table name connected through Federation

I have created a DB2 UDF in Database1, which is referring to a table (tablename2) in Database2 through Federation setup. The function is getting complied properly in Database1. But when the UDF is getting run by running the following select query, error message is obtained

select Database1schema.UDFName() from sysibm.sysdummy1;

Error Message -

An error occurred during implicit system action type "5". Information returned for the error includes SQLCODE "-204", SQLSTATE "42704" and message tokens "schemaname.tablename2".. SQLCODE=-727, SQLSTATE=56098, DRIVER=3.64.114

-- UDF Code

CREATE or replace function Database1schema.UDFName()
returns varchar(1000)

LANGUAGE SQL 


P1: BEGIN 


    DECLARE v_outmessage varchar(1000) default '0';


        BEGIN

            select a.status into v_outmessage
            from database2.database2schema.tablename2 a,database1schema.tablename1 b
            where a.appno = b.app_no;

        END;


    return v_outmessage;
END P1

PS:- Output is obtained when the select query is executed for fetching the tablename2 data from Database1, through Federation. But through function, it is not working properly. I will appreciate any help on this.

Upvotes: 0

Views: 1143

Answers (2)

Mark Barinstein
Mark Barinstein

Reputation: 12339

Restrictions on SQL functions:

The following restrictions apply to SQL functions:

  • SQL table functions cannot contain compiled compound statements.
  • A compiled SQL scalar function that is defined as READS SQL can be invoked in a partitioned database environment, but only if it is certain to be executed in the coordinator agent.
  • A compiled SQL scalar function that is defined as CONTAINS SQL and is invoked in a partitioned database environment cannot prepare SQL statements, cannot execute CALL statement, and cannot use any construct that the SQL compiler translates to a full SQL statement before processing.
  • By definition, SQL functions cannot contain cursors defined with the WITH RETURN clause.
  • The following data types are not supported within compiled SQL functions: structured data types, LONG VARCHAR data type, and LONG VARGRAPHIC data type. The XML data type is not supported in Version 10.1. The support for XML data type starts in Version 10.1 Fix Pack 1.
  • In this version, use of the DECLARE TYPE statement within compiled SQL functions is not supported.
  • Compiled SQL functions (including PL/SQL functions) must not contain references to federated objects.
  • Compiled SQL functions (including PL/SQL functions) that modify SQL data can only be used as the only element on the right side of an assignment statement that is within a compound SQL (compiled) statement.
  • If a table contains a generated column expression in which the user-defined function is a compiled compound SQL, then you cannot use the LOAD utility to insert data into the table.

Try an inlined function (P1: BEGIN ATOMIC) instead.

Upvotes: 1

Paul Vernon
Paul Vernon

Reputation: 3901

-204 is 

SQL0204N  "<name>" is an undefined name.

Explanation:

This error is caused by one of the following:

*  The object identified by "<name>" is not defined in the
   database.

while -727 reason code 5 is

SQL0727N  An error occurred during implicit system action type
      "<action-type>". Information returned for the error includes
      SQLCODE "<sqlcode>", SQLSTATE "<sqlstate>" and message tokens
      "<token-list>".


5

         incremental bind of a static SQL statement, not bound during
         package bind time

with notes

Federated system users: You may have received this message because you
dynamically prepared an SQL statement in a pass-through session and then
tried to execute the statement after the session was closed.

and

Federated system users: If the statement that failed was dynamically
prepared in a pass-through session, open another pass-through session,
write and prepare the statement again, and execute it while the session
is still open.

If the above does not help, update yor question with the code for your UDFName() UDF

Upvotes: 0

Related Questions