CODEWITHSUNDEEP
db2db2-luw
tsschulz
tsschulz

Reputation: 23

db2 external function ends with SQL0444N, Reason code 6, SQLSTATE 42724

I'll develop new (external) functions for DB2. My first test:

db2crypt.h

#ifndef DB2CRYPT_H
#define DB2CRYPT_H

#include <string.h>
#include <stdlib.h>

char *encryptAes(const char *source, const char *key);
#endif /* DB2CRYPT_H */

db2crypt.cpp

#include "db2crypt.h"

#include <string>

char *encryptAes(const char *source, const char *key) {
    std::string test("abc");
    return (char *)test.c_str();
}

is compiled without an error.

g++ -fPIC -c db2crypt.cpp  -std=c++14
g++ -shared -o db2crypt db2crypt.o -L$DB2PATH -ldb2

I also copied the new file into $DB2PATH/function and made a softlink in $DB2PATH/function/unfenced.

Then I created the function with

create function aes(VARCHAR(4096), VARCHAR(4096)) 
SPECIFIC encryptAes 
RETURNS VARCHAR(4069) 
NOT FENCED 
DETERMINISTIC 
NO SQL 
NO EXTERNAL ACTION 
LANGUAGE C 
RETURNS NULL ON NULL 
INPUT PARAMETER STYLE SQL 
EXTERNAL NAME "db2crypt!encryptAes"

which was also ok.

But when I do select db2inst1.aes('a', 'b') from SYSIBM.SYSDUMMY1 I get the error

SQL0444N  Die Routine "DB2INST1.AES" (spezifischer Name "ENCRYPTAES") ist
durch Code in Bibliothek oder Pfad ".../sqllib/function/db2crypt", Funktion
"encryptAes" implementiert, auf die kein Zugriff möglich ist.  Ursachencode:
"6".  SQLSTATE=42724

(sorry, I don't know how to change the error output into english)

What I made wrong?

Upvotes: 0

Views: 831

Answers (1)

tsschulz
tsschulz

Reputation: 23

Ok, I got the answer. Thank you at @mao, you did help me. But I also needed some other help. If someone searches for an answer:

First you have to compile with some important parameters:

g++ -m64 -fPIC -c <yourfile>.cpp -std=c++14 -I/opt/ibm/db2/V11.1/include/ -D_REENTRANT
g++ -m64 -shared -o <yourfile> <yourfile>.o -L$DB2PATH -ldb2 -Wl,-rpath,$DB2PATH/$LIB -lpthread

Second: The function declaration, you also have to add parameters for null values AND the return value can't be a function return, it has to be a parameter. Also you have to use the types which are defined in sqludf.h:

void SQL_API_FN encryptAes(SQLUDF_CHAR      *source,
                        SQLUDF_CHAR      *key,
                        SQLUDF_CHAR      out[4096],
                        SQLUDF_SMALLINT  *sourcenull,
                        SQLUDF_SMALLINT  *keynull,
                        SQLUDF_SMALLINT  *outnull,
                        SQLUDF_TRAIL_ARGS) {
...
}

Also, when you do C++ instead of C, you have to tell the script that it has to handle the function as C:

#ifdef __cplusplus
extern "C"
#endif
void SQL_API_FN ...

Upvotes: 1

Related Questions