0

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.

Manoj
  • 31
  • 1
  • 6
  • If you want to "fetch the tablename2 data from Database1" then just create a NICKNAME for tablename2 such as `CREATE NN_SCHEMANAME.TABLENAME2 FOR DATABASE1.SCHEMANAME.TABLENAME2` then just run `SELECT * FROM NN_SCHEMANAME.TABLENAME2` in Database2 – Paul Vernon Nov 29 '19 at 10:54
  • Does the SQL SELECT statement in your UDF work and return what you want if you run it as a stand-alone query (I.e. as a simple select without the SELECT INTO) ? – Paul Vernon Dec 02 '19 at 09:56
  • Yes. sql select is working from DB1 to DB2. Also,if i put this query inside a stored procedure, the expected output is obtained when the procedure is called. The issue is faced in UDF. – Manoj Dec 02 '19 at 11:19
  • As Mark has suggested below, try without the compiled SQL. E.g. just create the function like this `CREATE FUNCTION UDFName() returns varchar(1000) return select a.status from database2.database2schema.tablename2, a,database1schema.tablename1 b where a.appno = b.app_no` – Paul Vernon Dec 02 '19 at 14:41
  • Tried without the compiled SQL along with the nickname change and it worked. Thanks a lot :) – Manoj Dec 03 '19 at 07:52

2 Answers2

1

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.

Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16
0

-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

Paul Vernon
  • 3,818
  • 1
  • 10
  • 23