Calling db2ReadLog from external UDF

Question

When trying to get transaction log information on a DB2 LUW database through an external UDF calling db2ReadLog a SQL0487N (attempted to execute on SQL statement) is returned. Using the same shared library as a stored procedure works without error.

The reason it needs to be a UDF is to be able to eventually create a table valued UDF which will return actual entries from the transaction log.

Here is the C source for the UDF to determine the initial LRI:

#include <sqlca.h>
#include <db2ApiDf.h>
#include <sqludf.h>
#include <string.h>
#include <stdio.h>
#include <inttypes.h>

#ifdef __plusplus
extern "C"
#endif
SQL_API_RC SQL_API_FN get_initial_lri(SQLUDF_CHAR *lri, SQLUDF_NULLIND 
    *lri_null_ind, SQLUDF_TRAIL_ARGS)
{
    struct sqlca sqlca;
    db2ReadLogStruct read_log_params;
    db2ReadLogInfoStruct info;
    SQL_API_RC rc;
    char state_msg[1024], error_msg[1024];

    memset(&sqlca, 0, sizeof sqlca);
    memset(&read_log_params, 0, sizeof read_log_params);
    memset(&info, 0, sizeof info);

    read_log_params.iCallerAction = DB2READLOG_QUERY;
    read_log_params.iFilterOption = DB2READLOG_FILTER_OFF;
    read_log_params.poReadLogInfo = &info;

    rc = db2ReadLog(db2Version1058, &read_log_params, &sqlca);
    if (rc < 0) {
            memcpy(SQLUDF_STATE, "38TA0", SQLUDF_SQLSTATE_LEN);
            strncpy(SQLUDF_MSGTX, "Could not query log for initial LRI", SQLUDF_MSGTEXT_LEN);

            goto error;
    } else if (sqlca.sqlcode < 0) {
            strncpy(SQLUDF_STATE, sqlca.sqlstate, SQLUDF_SQLSTATE_LEN);
            SQLUDF_MSGTX[0] = '\0';
            rc = sqlaintp(error_msg, sizeof error_msg, 80, &sqlca);
            if (rc > 0) {
                    strncpy(SQLUDF_MSGTX, error_msg, SQLUDF_MSGTEXT_LEN);
            }
            strncat(SQLUDF_MSGTX, "|", SQLUDF_MSGTEXT_LEN);
            rc = sqlogstt(state_msg, sizeof state_msg, 80, sqlca.sqlstate);
            if (rc > 0) {
                    strncat(SQLUDF_MSGTX, state_msg, SQLUDF_MSGTEXT_LEN);
            }

            goto error;
    } else {
            snprintf(lri, 101, "%" PRIx64 ":%" PRIx64 ":%" PRIx64, info.nextStartLRI.lriType, info.nextStartLRI.part1, info.nextStartLRI.part2);
    }

    return 0;

error:
    return SQLZ_DISCONNECT_PROC;
}

The following SQL is used for registering the C function as an external UDF:

create or replace function get_initial_lri()
    returns varchar(100)
    language c
    external name 'get_initial_lri_0!get_initial_lri'
    parameter style sql
    fenced not threadsafe
    reads sql data
    no external action
    no scratchpad
    no final call
    disallow parallel
    no dbinfo

And for registering the same code as a stored procedure the SQL is:

create or replace procedure get_initial_lri_sp(out lri varchar(100))
    language c
    dynamic result sets 0
    reads sql data
    not deterministic
    external name 'get_initial_lri_0!get_initial_lri'
    fenced not threadsafe
    no external action
    program type sub
    no dbinfo
    parameter style sql

The C source code was compiled with:

gcc -o get_initial_lri_0 get_initial_lri.c -L ~/sqllib/lib64 -ldb2 -shared -fpic -D_REENTRANT -I ~/sqllib/include

The version of DB2 is v10.5.0.8.

Is it at all possible to call the db2ReadLog API from a user defined function?

mao mao · Accepted Answer · 2017-10-17T09:09:50

The documentation states that "connection level APIs cannot be called from within external functions or external methods". And the db2ReadLog API requires a connection. The documentation link is here.

There is another API called db2ReadLogNoconn, maybe you should try that to compare its behaviour, although it may be subject to a different restriction. It is documented here.

Otherwise, a non-external UDF can call a stored procedure (subject to some restrictions) so you may be able to wrap the stored procedure, maybe you can investigate.

Calling db2ReadLog from external UDF

1 Answers