Calling a UDX that uses a CPad

After you create the C++ programs that contain your CPad calls, you compile and register them on the Db2® system. These functions can easily be saved in one C++ program; after you compile the program, register each of the functions as a separate UDX.

After you compile and register a UDX, you can run it. For example, the following sequence of commands show how these functions process the data. The commands are processed within a BEGIN/COMMIT transaction block so that the CPad that is created by the string_pad_create function still exists when the string_pad_get function is called.

// If you are using CLPPlus, specify the +c option to initiate a transaction.
// If you terminate the transaction, it will be rolled back.

// Open an db2 interactive transaction session. 
db2 +c 
// Connect to the sample database. 
db2 => connect to sample
// Call the UDSF to create a CPad.
db2 => SELECT string_pad_create(10, 'dynamite') FROM tab1;
// Use the UDSF to retrieve a value from a CPad.
db2 => SELECT string_pad_get(4) FROM tab1;
// Commit the transaction. This frees the memory used by the CPad.
db2 => commit

If you run these functions as single select statements, the first function (string_pad_create) would run, create the CPad and the character array, and then exit. When the function returns, the Db2 database engine automatically cleans up the CPad and frees the memory. If you then run string_pad_get in a single select statement, the following error would be displayed because the CPad no longer exists:

db2 => select string_pad_get(0) from tab1;
ERROR: Pad does not exist