Installing the DB SQL Extension Toolkit

You can install the DB SQL Extension Toolkit in your Db2 environment to add a variety of functions that perform XML processing, compression, hashing, and other analytics that you used in your Netezza SQL queries.

About this task

DB SQL Extension Toolkit is supported on Db2® Warehouse, Db2 Warehouse on Cloud, and Integrated Analytics System. You can download the tool from IBM DeveloperWorks page.

For the list of functions that are implemented, see Release notes.

Refer to this link for details of available functions https://www.ibm.com/support/knowledgecenter/en/SSULQD_7.2.1/com.ibm.nz.sqltk.doc/c_sqltk_plg_overview.html

Prerequisites:
  • Db2 client driver must be installed on the system. CLPPLUS is required to run the setup.
  • Database user credentials which has the privileges to create schema/functions in database.
Usage:
./setup.py [-user USER -schema SCHEMA] {[-dsn DSN] | [-pw PASS -port PORT -db DATABASE -host HOST_IP]} [-cleanup]

  -h, --help      show this help message and exit
  -user USER      Username with which you want to register udx. Default is bluadmin.
  -pw PW          Password of -u user; prompts user if -u is provided.
  -host HOST      Host IP if you're using Db2WoC. Default is current host's IP.
  -port PORT      Port number in case of SSL connections. Default is 50000.
  -db DB          Database name. Default is BLUDB.
  -dsn DSN        Used in case of SSL connection mostly.
  -schema SCHEMA  Schema name in which user wants to register the UDXes. Default is $USER.
  -cleanup        Cleanup the sqltoolkit funtions created.

Procedure

  1. Download the package according to the architecture of your database server. For example, for x86_64 platform: 
    [bluadmin@host - Db2wh ga_1.2]$ uname -i
        x86_64
        [bluadmin@host - Db2wh ga_1.2]$ ls
        db_sqltoolkit_x86_64_1.2.tgz
  2. Extract the package to the location accessible to the user you logged in: 
    -- tar -xzf db_sqltoolkit_arch.tgz
    Example:
    
        [bluadmin@host - Db2wh ga_1.2]$ ls
        db_sqltoolkit_x86_64  db_sqltoolkit_x86_64_1.2.tgz
  3. Change the directory to db_sqltoolkit_arch.
    Example:
    [bluadmin@host - Db2wh ga_1.2]$ cd db_sqltoolkit_x86_64
        [bluadmin@host - Db2wh db_sqltoolkit_x86_64]$ ll
        total 4100
        -rw-r--r-- 1 bluadmin bluadmin   40011 Sep  3 12:39 create_sql_function.base
        -rw-r--r-- 1 bluadmin bluadmin     182 Sep  5 13:09 deploy.base
        -rw-r--r-- 1 bluadmin bluadmin     536 Aug 14 14:19 deploy_toolkit.cpp
        -rw-r--r-- 1 bluadmin bluadmin    8556 Sep  5 13:33 drop_sql_function.base
        -rw-r--r-- 1 bluadmin bluadmin      22 Sep  5 10:10 get_string_units.base
        -rw-r--r-- 1 bluadmin bluadmin      80 Aug 29 14:05 get_upload_path.sql
        -rw-r--r-- 1 bluadmin bluadmin    2069 Aug 14 14:19 README
        -rwxr-xr-x 1 bluadmin bluadmin   11932 Sep  5 14:31 setup.py
        -rw-r--r-- 1 bluadmin bluadmin 4106240 Aug 14 14:19 toolkit_libs.tar
        -rw-r--r-- 1 bluadmin bluadmin      36 Aug 29 14:04 upload.sql

    The directory contains a setup.sh file.

  4. Run ./setup.sh -h to display help for options.
  5. When DSN option is used, only the user, schema, and cleanup options are valid. Other connection options are ignored.
    Note: When using DSN option, add the DSN as follows:
    1. In catalog, using db_catalog command.
    2. Add DSN and database in db2dsdriver.cfg using db2cli writecfg. This enables WebAPIs required for CLPPlus.
  6. If the password is not provided in the command line argument, you will be prompted for it. In case of DSN, enter the password only when prompted. Even if the password is provided as command line argument, it will be ignored in case of DSN. 
    [bluadmin@host - Db2wh db_sqltoolkit_x86_64]$ ./setup.py -schema sqltk
        [INFO] : Checking required files ...
        [INFO] : Connecting user bluadmin with 172.16.176.74:50000/BLUDB
        Enter password for bluadmin :
        [INFO] : STRING_UNITS detected as : SYSTEM
        [INFO] : The files have been uploaded.
        [INFO] : Registering the functions ...
        [INFO] : Successfully operated ..
        [bluadmin@host - Db2wh db_sqltoolkit_x86_64]$
    Note: It is advised to use the function with SCHEMA_NAME in which you've registered the toolkit. Otherwise, it is possible that you may get unexpected results due to an already existing function with same name. This function may exist in schema with higher precedence in CURRENT_PATH.
    Example:
    db2 => values CURRENT_PATH

1
----------------------------------------------------------------------------------------------------
"SYSIBM","SYSFUN","SYSPROC","SYSIBMADM","BLUADMIN"

In the above path, SYSIBM schema has the highest precedence. If a function exists in both SYSIBM and BLUADMIN schemas, and you run that function without mentioning the schema, the version in SYSIBM will be executed.

db2 => values BLUADMIN.COMPRESS('some_string')
    Known issues:
    • For ARRAY_COMBINE function, more than one character delimiter does not work.

    • ENCRYPT and COMPRESS results may vary as compared to Netezza. If you encrypt a string using ENCRYPT function and use DECRYPT function on result of former returns original string. Similar for COMPRESS and DECOMPRESS functions. For example, VALUES(DECRYPT(ENCRYPT('string')) = 'string'

Best practices

  • NVARCHAR is internally handled in VARCHAR in Db2. Thus, registering the functions with VARCHAR variant only will internally handle NVARCHAR as well.
  • ARRAY objects are fits in VARCHAR column of the table. It is user's responsibility to allocate appropriate memory in VARCHAR.

    For example, VARCHAR(100) might give error while adding multiple BIGINT values. However, changing it to VARCHAR(10000) will solve the issue.

  • Since BYTEINT is converted to SMALLINT in db2, ARRAY(1) gives error for such query. User is advised to use ARRAY(2) , as BYTEINT values are handled in SMALLINT.
  • User needs to use functions with schema name under which they are registered. Schema will be the user name with which the package was installed.
  • Output representation is different for DOUBLE/BOOLEAN in Db2 compared to PDA. So, you might observe output difference in case you are using DOUBLE/BOOLEAN data types with the functions.
  • If your workload/query involves "NCHAR", "NVARCHAR" data then your system must be set with appropriate STRING_UNITS configurations.

Uninstalling the DB SQL Extension Toolkit

The cleanup of the SQL Extension Toolkit requires the schema which was provided while installing. If not provided, the user with which the script is run will be treated as schema name.

Procedure

Use the following command to uninstall the tool: 
[bluadmin@host - Db2wh db_sqltoolkit_x86_64]$ ./setup.py -schema sqltk -cleanup
        [INFO] : Checking required files ...
        [INFO] : Connecting user bluadmin with 172.16.176.74:50000/BLUDB
        Enter password for bluadmin :
        [INFO] : STRING_UNITS detected as : SYSTEM
        [INFO] : De-registering functions ...
        [INFO] : Successfully operated ..
        [bluadmin@host - Db2wh db_sqltoolkit_x86_64]$

Examples

Installing the toolkit:
  • using connection identifiers:
    ./setup.py -user bluadmin -host myhost_ip -schema db_toolkit
  • using DSN:
    ./setup.py -user bluadmin -dsn MY_DSN -schema db_toolkit
Uninstalling the toolkit:
  • using connection identifiers:
    ./setup.py -user bluadmin -host myhost_ip -schema db_toolkit -cleanup
  • using DSN:
    ./setup.py -user bluadmin -dsn MY_DSN -schema db_toolkit -cleanup