More detailed guides for developing C++ routine code for db2

[pwharned]

I have been trying to develop and deploy some simple C++ routines and deploy them to db2. I used the udtfsampl.cpp file and followed these steps.

db2 prep udtfsampl.sqx bindfile

db2 bind udtfsampl.bnd

g++ -m64 -fPIC -c udtfsampl.C -std=c++14 -I/opt/ibm/db2/V11.5/include/ -D_REENTRANT

g++ -m64 -shared -o udtfsaml udtfsampl.o -L$DB2PATH -ldb2 -Wl,-rpath,$DB2PATH/$LIB -lpthread

cp udtfsampl $HOME/sqllib/function/

chmod 777 /database/config/db2inst1/sqllib/function/udtfsampl

db2stop force

db2start

CREATE OR REPLACE FUNCTION parsenames(VARCHAR(1024))
    RETURNS TABLE(product_id VARCHAR(200)) LANGUAGE  CPP
    PARAMETER STYLE  NPSGENERIC
    FENCED NOT THREADSAFE   NO FINAL CALL   DISALLOW PARALLEL   NO DBINFO
    NOT DETERMINISTIC   RETURNS NULL ON NULL INPUT   NO SQL
    EXTERNAL NAME '/database/config/db2inst1/sqllib/function/udtfsampl!parseNames';

db2 "select t.cust_id from orders t, table(parsenames(prod_codes)) as fi"

Result is an error code.

The documentation around this subject is sparse, confusing and incomplete - it would be great to have a 'Hello world` sample for something straightforward like a simple UDTF that returns some random numbers for example

[pwharned]

after much head banging, i did finally get it to work. EXTRA_LFLAG="-Wl,-rpath,$DB2PATH/$LIB"

Db2 ships with the bldrtn shell script but it does not intelligently detect extensions so it doesnt work for cpp extensions

DB2PATH=$HOME/sqllib

HARDWAREPLAT=$(uname -m)

BITWIDTH=64

LIB="lib64"

EXTRA_C_FLAGS="-m64"

EXTRA_LFLAG="-Wl,-rpath,$DB2PATH/$LIB"

g++ $EXTRA_C_FLAGS -fpic -I$DB2PATH/include -c udtfsampl.cpp -D_REENTRANT

g++ $EXTRA_C_FLAGS -shared -o udtfsampl udtfsampl.o $EXTRA_LFLAG -L$DB2PATH/$LIB -ldb2 -lpthread

cp udtfsampl $DB2PATH/function/

db2stop force

db2start

db2 connect to bludb

db2 "CREATE OR REPLACE FUNCTION parsenames(VARCHAR(1024)) RETURNS TABLE(product_id VARCHAR(200)) LANGUAGE CPP PARAMETER STYLE NPSGENERIC FENCED NOT THREADSAFE NO FINAL CALL DISALLOW PARALLEL NO DBINFO NOT DETERMINISTIC RETURNS NULL ON NULL INPUT NO SQL EXTERNAL NAME '/database/config/db2inst1/sqllib/function/udtfsampl!parseNames';"

`db2 "CREATE TABLE orders(order_id INTEGER, cust_id VARCHAR(200),prod_codes VARCHAR(1000));"`

`db2 "INSERT INTO orders(order_ID, cust_ID,  PROD_CODES) VALUES (124, 'AB123456', '124,6,12,121');"`

`SELECT t.cust_id, fi.* FROM orders AS t,TABLE ( parsenames(prod_codes) ) AS fi`