SQL Toolkit

Overview Copied

The SQL Toolkit plugin executes database queries that can display the output of the query in the dataview and run custom queries to monitor database data. For more information about the SQL Toolkit plugin and configuration, see SQL Toolkit configuration.

This plugin can connect to and extract data from the following supported databases:

For the complete list of supported database server versions, go to Geneos Compatibility Matrix.

A typical view constructed using the SQL Toolkit plugin consists of a dataview whose columns and rows are entirely determined by the query.

This works well for use cases that require periodical queries on databases, such as:

In addition, below is an example of SQL Toolkit dataview where you can see the headline variables:

SQL Toolkit dataview

To connect to the database, the Netprobe requires third-party database client libraries to run the SQL Toolkit plugin. These libraries are usually included in the database installation.

Headline Description
queryStatus

displays the status of the query. The following are the possible values:

  • Initializing - displays when the plugin starts up.

  • Running - indicates that a query is currently executing.

  • Idle - waiting for the query to execute.

queryDuration Displays the time in seconds that the current query took on its last run. The time is set to 0 when the status is Idle.
rowsReturned Shows the number of rows returned in the dataview.

Intended audience Copied

This guide is intended for experienced Geneos users who want to configure the SQL Toolkit plugin to execute database queries.

As a user, you should be familiar with SQL or other database, and with the administration of Netprobe and DB libraries that are required to run the SQL Toolkit sampler.

Prerequisites Copied

The following requirements must be met prior to the configuration and setup of the SQL Toolkit plugin:

Configure the Netprobe and database client libraries Copied

Depending on what database your Netprobe needs to connect to, you may have to amend your environment to expose the appropriate libraries to the Netprobe. The Netprobe indirectly loads these libraries depending on what database the plugin has been configured to connect to. For more information on supported libraries for each platform, see Required client libraries in Databases.

You must first configure your selected database and define its required variables in the Netprobe start script before you can set up your SQL Toolkit sampler in Gateway Setup Editor. The configuration steps for each database changes depending on the operating system you are using.

Note

The Netprobe start script is found in netprobe/templates directory of your Netprobe package.

Linux Copied

Windows Copied

AIX Copied

Caution

Some IBM AIX hosts still use the LIBPATH variable when loading shared libraries. For backward compatibility, the path to the required libraries must be added to the LIBPATH variable.

MySQL database Copied

For more information on supported MySQL libraries for each platform, see Required client libraries in Databases.

Note

The following authentication plugins are supported:

  • mysql_native_password
  • caching_sha2_password

The caching_sha2_passwordauthentication plugin can only be used with secure connections. To do this, the MySQL server should be configured to use secure connections, and the SSL mode in the SQL Toolkit configuration should be set to Preferred, Required, Verify_ca, or Verify_identity.

Linux Copied

When MySQL is installed via packaged installation, it automatically adds /usr/lib64/mysql to the list of directories (/etc/ld.so.conf.d) where the dynamic linker searches for libraries. This way, even if you do not set LD_LIBRARY_PATH, the Netprobe can find the library in /usr/lib64/mysql.

If you need to set LD_LIBRARY_PATH, follow these steps:

  1. Locate the MySQL client library file.

  2. Add these variables to the Netprobe start script using a text editor.

    Variable Value
    MYSQL_LIB export MYSQL_LIB=/usr/lib64/mysql
    LD_LIBRARY_PATH export LD_LIBRARY_PATH=${NP_LIB}:${MYSQL_LIB}

  3. Add the MYSQL_LIB variable.

  4. Enter the path of the library file in the MYSQL_LIB variable:

MYSQL_LIB=/usr/lib64/mysql
  1. Enter the MYSQL_LIB in the LD_LIBRARY_PATH variable:
export LD_LIBRARY_PATH=${NP_LIB}:${MYSQL_LIB}
  1. Save the changes to the Netprobe start script.
  2. Exit the editor.
  3. Restart the Netprobe.

Windows Copied

  1. Modify the Path variable of the Windows host.

  2. Locate the MySQL client library file – libmysql.dll. Below is an example that shows you where to locate the MySQL file:

    Variable Value
    Path <existing path>;C:\database_libs\mysql\mysql-connector-c-6.1.11-winx64\lib 
C:\database_libs\mysql\mysql-connector-c-6.1.11-winx64\lib>dirVolume in drive C has no label.Volume
Serial Number is 1846-3FFADirectory of C:\database_libs\mysql\mysql-connector-c-6.1.11-winx64\lib10/25/2017
04:59 PM    <DIR>          ..07/18/2017  12:07 AM         4,879,360 libmysql.dll07/18/2017  12:07 AM
26,956 libmysql.libC:\database_libs\mysql\mysql-connector-c-6.1.11-winx64\lib>

Caution

If you are also using a Maria in your machine and added its path in the Path variable, then you must move the MySQL to a higher directory to avoid conflict.
  1. Click Start on your computer.
  2. Right-click Computer, and click Properties.
  3. Click Advanced system settings.
  4. Click Environment Variables.
  5. Click the Path system variable. Path system variable
  6. Click Edit… to append the path of the libmysql.dll file system variable
  7. Click OK to save the changes in the Edit System Variable screen.
  8. Click OK to save the changes in the Environment Variables screen.
  9. Restart the Netprobe.

Success

You have successfully defined the path variables of the Windows host.
Go to the Gateway Setup Editor to Connect to a MySQL database in SQL Toolkit configuration.

IBM AIX Copied

Run the following export command in your machine before running the Netprobe:


export LIBPATH=/opt/freeware/lib/gcc/powerpc-ibm-aix7.2.0.0/8/pthread:/opt/freeware/lib64

If you are using Netprobe version 6.2.x. or older, then you must run the following additional export command:


export GENEOS_MYSQL_CLIENT_LIBRARY=libmysqlclient.a\(libmysqlclient.so.21\)

Caution

Some IBM AIX hosts still use the LIBPATH variable when loading shared libraries. For backward compatibility, the path to the required libraries must be added to the LIBPATH variable.

Success

You have successfully defined the database library paths in the Netprobe start script. Go to the Gateway Setup Editor to Connect to a MySQL database in SQL Toolkit configuration.

Microsoft SQL Server database Copied

Note

MS SQL is not supported in an ARM64 environment. Instead, MS SQL is designed to run on AMD64 or x64 processors.

Linux Copied

  1. Install the MS SQL Server ODBC 18 driver by following the instructions in the Microsoft website. Select the instruction that matches your Linux version.
  2. Execute this command to verify whether the installation is successful:
odbcinst -q -d -n "ODBC Driver 18 for SQL Server"

The command displays the message below once the installation is successful:

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver==/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1
UsageCount=1
  1. Copy or symlink the /etc/odbcinst.ini to /opt/microsoft/msodbcsql18/lib64 folder:
sudo ln -s /etc/odbcinst.ini /opt/microsoft/msodbcsql18/lib64/odbcinst.ini

Note

Symlink or symbolic link is a reference to another file or directory.

  1. Add these variables to the Netprobe start script using a text editor. Follow the variables and their values in the table:

    Variable Value
    SQLSERVER_LIB /opt/microsoft/msodbcsql18/lib64
    ODBCHOME ${SQLSERVER_LIB}
    ODBCSYSINI ${SQLSERVER_LIB}
    ODBCINSTINI odbcinst.ini
    GENEOS_MSSQL_DRIVER 'ODBC Driver 18 for SQL Server'
    LD_LIBRARY_PATH <Existing path>:${SQLSERVER_LIB}

  2. Add the variables and their corresponding values:


export SQLSERVER_LIB=/opt/microsoft/msodbcsql18/lib64
export ODBCHOME=${SQLSERVER_LIB}
export ODBCSYSINI=${SQLSERVER_LIB}
export ODBCINSTINI=odbcinst.ini
export GENEOS_MSSQL_DRIVER='ODBC Driver 18 for SQL Server'

The SQLSERVERLIB contains the path of the MS SQL Server client library file – libmsodbcsql-18.2.so.1.1.

  1. Add the SQLSERVER_LIB in the LD_LIBRARY_PATH variable:
export LD_LIBRARY_PATH=${NP_LIB}:${SQLSERVER_LIB}
  1. Save the changes to the start script.
  2. Exit the editor.
  3. Restart the Netprobe.

Success

You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor to Connect to an SQL Server database in SQL Toolkit configuration.

Windows Copied

Windows Server 2019 Copied
  1. Verify if the Microsoft OLE DB Driver is installed in your system. To check, the msoledbsql.dll or msoledbsql19.dll, and msoledbsqlr.rll should be in %SYSTEMROOT%\system32\.
  2. If the OLD DB Driver does not exist, then download and install the OLE DB Driver from Microsoft’s website.
  3. After installing the driver, restart the Netprobe.

Success

You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor to Connect to an SQL Server database in SQL Toolkit configuration.
Windows Server 2018 and older Copied

Note

You may use either the OLE DB Driver or the Microsoft SQL Server Native Client for Windows Server 2018 and older.
  1. Verify if the Microsoft SQL Server Native Client is installed in your system. To check, the sqlncli*.dllshould be in %SYSTEMROOT%\System32 or %SYSTEMROOT%\SysWOW64.
  2. If the Microsoft SQL Server Native Client does not exist, then download and install the Microsoft SQL Server Native Client from Microsoft’s website.
  3. After installing the client, restart the Netprobe.

Success

You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor to Connect to an SQL Server database in SQL Toolkit configuration.

AIX Copied

Microsoft does not currently provide AIX drivers which allow connecting to MS SQL Server running on Windows.

Solaris Copied

Microsoft does not currently provide Solaris drivers which allow connecting to MS SQL Server running on Windows.

Oracle database Copied

Linux Copied

  1. Locate the Oracle library file - libclntsh.so. Below is an example that shows you where to locate the Oracle library file:

[root@pabo lib]# pwd
/usr/lib/oracle/21/client64/lib
[root@pabo lib]# ls -l libclntsh*
lrwxrwxrwx. 1 oracle oinstall       12 Aug  9 22:27 libclntsh.so.10.1 -> libclntsh.so
lrwxrwxrwx. 1 oracle oinstall       12 Aug  9 22:27 libclntsh.so.11.1 -> libclntsh.so
-rwxr-xr-x. 1 oracle oinstall 58804016 Aug  9 22:28 libclntsh.so.12.1
[root@pabo lib]#
  1. Locate the tnsnames.ora file. Below is an example of script that shows you the tnsnames.ora file:

[root@pabo admin]# pwd
/usr/lib/oracle/21/client64/network/admin
[root@pabo admin]# ls -l
total 20
-rw-r--r--. 1 oracle oinstall  350 Aug  9 22:29 listener.ora
drwxr-xr-x. 2 oracle oinstall 4096 Aug  9 22:24 samples
-rw-r--r--. 1 oracle oinstall  373 Oct 31  2013 shrept.lst
-rw-r--r--. 1 oracle oinstall  195 Aug  9 22:29 sqlnet.ora
-rw-r--r--. 1 oracle oinstall  359 Aug  9 22:34 tnsnames.ora

  1. Add these variables to the Netprobe start script using a text editor. Refer to the table below:

    Variable Value
    ORACLE_HOME /usr/lib/oracle/21/client64
    TNS_ADMIN /usr/lib/oracle/21/client64/network/admin
    ORACLE_LIB /usr/lib/oracle/21/client64/lib
    LD_LIBRARY_PATH <Existing path>:${ORACLE_LIB}

  2. Add the ORACLE_HOME variable.

  3. Enter the path of the lib folder that contains the libclntsh.so file in the ORACLE_HOME variable.

  4. Export the ORACLE_HOME variable:

export ORACLE_HOME=/usr/lib/oracle/21/client64
  1. Add the TNS_ADMIN variable.
  2. Export the TNS_ADMIN variable. For example:
export TNS_ADMIN=/usr/lib/oracle/21/client64/network/admin
  1. Add the ORACLE_LIB variable.
  2. Enter the path of the folder which contains the libclntsh.so file in the ORACLE_LIB variable.
  3. Export the ORACLE_LIB variable:
export ORACLE_LIB=/usr/lib/oracle/21/client64/lib
  1. Add the variables ORACLE_LIB in the LD_LIBRARY_PATH variable:
export LD_LIBRARY_PATH=${NP_LIB}:${ORACLE_LIB}
  1. Exit the editor.
  2. Restart the Netprobe.

Success

You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor to Connect to an Oracle database in SQL Toolkit configuration.

Windows Copied

  1. Locate the Oracle client library file - oci.dll.

  2. Locate the tnsnames.ora file.

  3. Add the Path system variable of the Windows host:

    Variable Value
    Path <Existing path>;C:\database_libs\oracle\lib
    ORACLE_HOME

    C:\database_libs\oracle

    Note: The ORACLE_HOME variable should be set one directory higher where theoci.dll library file is located.
    TNS_ADMIN C:\database_libs\oracle

  4. Click Start on your computer.

  5. Right-click Computer, and click Properties.

  6. Click Advanced system settings.

  7. Click Environment Variables.

  8. Click the Path system variable. Path system variable

  9. Click Edit… to append the path to oci.dll file. System variable

  10. Click OK to save the changes in the Edit System Variable screen.

  11. Add the ORACLE_HOME system variable in the Windows host.

    • Click New… in the System variables group.
    • Enter the ORACLE_HOME in the Variable name field.
    • Enter the path of the oci.dll file in the Variable value field.
    • Click OK to save the changes in the New System Variable screen.
    • Click OK to save the changes in the Environment Variables screen.

  12. Add the TNS_ADMIN system variable in the Windows host.

    • Click New… in the System variables group.
    • Enter the TNS_ADMIN in the Variable name field.
    • Enter the path of the oci.dll file in the Variable value field.
    • Click OK to save the changes in the New System Variable screen.
    • Click OK to save the changes in the Environment Variables screen.

Note

You have successfully defined the path variables of the Windows host. Go to the Gateway Setup Editor to Connect to an Oracle database in SQL Toolkit configuration.

AIX Copied

Export the following variables:

export ORACLE_HOME=/usr/local/oracle/instantclient_19_3_ppc64
export TNS_ADMIN=/usr/local/oracle/instantclient_19_3_ppc64
export LIBPATH=$ORACLE_HOME:$LIBPATH

Setting up Oracle connection with Kerberos authentication Copied

This section provides a configuration guide for setting up the Oracle connection with Kerberos authentication, enabling connectivity between SQL Toolkit and Gateway Database Logging.

Preparing the Active Directory Server Copied

To prepare the Active Directory server, do the following:

  1. Create a service account in the Active Directory for the database server to validate the Kerberos tickets. This user does not need any specific rights but enable password never expires.
  2. Ensure that you deselect the account option Use Kerberos DES Encryption types for this account and select the option Do not require Kerberos preauthentication for this user.
  3. Make sure that the SPN is set to the correct realm. For example: setspn -A <oracle_service_name>/<key_distribution_center>@<domain> <user>
  4. Extract the keytab file for this designated user, so that we can create tickets without inputting a password. To do this, use the following command:
    ktpass -princ <oracle_service_name>/<key_distribution_center>@<domain> -crypto all -pass <password> -mapuser <user>@<domain> -out v5srvtab
  5. Copy the file in the database server and store it in the /etc/v5srvtab directory.
Setting up the Oracle Server Copied
  1. Generate a Kerberos ticket, which will be used to connect to the Kerberos server for ticket validation. To do this: $ORACLE_HOME/bin/okinit -k -t /etc/v5srvtab <oracle_service_name>/<key_distribution_center>
  2. Update the following files:
# sqlnet.ora Network Configuration File: /opt/oracle/homes/OraDBHome21cEE/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
# Debug/Tace logs can be removed

SQLNET.AUTHENTICATION_SERVICES= (BEQ, KERBEROS5)
TRACE_LEVEL_SERVER = ADMIN
SQLNET.KERBEROS5_CLOCKSKEW = 6000
SQLNET.KERBEROS5_CONF = /etc/krb5.conf
SQLNET.KERBEROS5_CONF_MIT = true
LOG_DIRECTORY_SERVER = /opt/oracle/logging
DIAG_ADR_ENABLED = OFF
SQLNET.KERBEROS5_KEYTAB=/etc/v5srvtab
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle

# tnsnames.ora Network Configuration File: /opt/oracle/homes/OraDBHome21cEE/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCLPDB1=
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = kskdc.qamnl.com)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ORCLPDB1)
  )
)

LISTENER_ORCLPDB1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = kskdc.qamnl.com)(PORT = 1521))

# Configuration snippets may be placed in this directory as well
includedir /etc/krb5.conf.d/
includedir /var/lib/sss/pubconf/krb5.include.d/

[logging]
 default = FILE:/var/log/krb5libs.log
 kdc = FILE:/var/log/krb5kdc.log
 admin_server = FILE:/var/log/kadmind.log

[libdefaults]
 default_realm = QAMNL.COM

[realms]
 QAMNL.COM = {
        kdc = ksad2.qamnl.com
        admin_server = ksad2.qamnl.com
        default_domain = QAMNL.COM
 }

[domain_realm]
 qamnl.com = QAMNL.COM
 .qamnl.com = QAMNL.COM
  1. Additional configurations can be included within the libdefaults section, such as:
Setting up the Oracle Client Copied
  1. Update the following files:
SQLNET.KERBEROS5_CONF = /etc/krb5.conf
SQLNET.KERBEROS5_CONF_MIT = true
ADR_BASE = /opt/oracle
SQLNET.AUTHENTICATION_SERVICES= (BEQ, TCPS, KERBEROS5)
TRACE_LEVEL_CLIENT = ADMIN
SQLNET.KERBEROS5_CLOCKSKEW = 6000
LOG_DIRECTORY_SERVER = /opt/oracle/logging
DIAG_ADR_ENABLED = OFF
SQLNET.AUTHENTICATION_KERBEROS5_SERVICE = oracle

ORCLPDB1=
(DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = kskdc.qamnl.com)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = ORCLPDB1)
  )
)

LISTENER_ORCLPDB1 =
  (ADDRESS = (PROTOCOL = TCP)(HOST = kskdc.qamnl.com)(PORT = 1521))

includedir /etc/krb5.conf.d/
includedir /var/lib/sss/pubconf/krb5.include.d/

[logging]
 default = FILE:/var/log/krb5libs.log
 kdc = FILE:/var/log/krb5kdc.log
 admin_server = FILE:/var/log/kadmind.log

[libdefaults]
 default_realm = QAMNL.COM

[realms]
 QAMNL.COM = {
        kdc = ksad2.qamnl.com
        admin_server = ksad2.qamnl.com
        default_domain = QAMNL.COM
 }

[domain_realm]
 qamnl.com = QAMNL.COM
 .qamnl.com = QAMNL.COM
  1. Additional configurations can be included within the libdefaults section, such as:

Troubleshooting Oracle with Kerberos authentication errors Copied

  1. ERROR: SQL-TOOLKIT[sql] Fail - ORA-12638: Credential retrieval failed
  1. ERROR: SQL-TOOLKIT[sql] Fail - ORA-12641: Authentication service failed to initialize
  1. ERROR: SQL-TOOLKIT[sql] Fail - OCIEnvNlsCreate != OCI_SUCCESS(6)

PostgreSQL database Copied

Netprobe requires the PostgreSQL client libraries to communicate with the PostgreSQL database server. The path to these client libraries should be included in the machine’s path environment variable.

Note

  • PostgreSQL is not supported in IBM AIX platforms.
  • PostgreSQL connections configured with SSL or TLS are not supported in SUSE 12 platforms.

Linux Copied

  1. Locate the PostgreSQL client library file - libpq.so. Below is an example that shows you where to locate the PostgreSQL client library file:
[root@pabo admin]#  pwd
/usr/lib/postgresql/14/lib
[root@pabo admin]#  ls -l
total 10928
-rw-r--r-- 1 500 sandbox   17120 Mar 23  2015 libcom_err.so.3
-r-xr-xr-x 1 500 sandbox 2135635 Mar 23  2015 libcrypto.so.1.0.0
-rw-r--r-- 1 500 sandbox  152714 Mar 23  2015 libecpg.a
-rw-r--r-- 1 500 sandbox   18852 Mar 23  2015 libecpg_compat.a
-rwxr-xr-x 1 500 sandbox   23327 Mar 23  2015 libecpg_compat.so.3.6
lrwxrwxrwx 1 500 sandbox      14 Apr 11  2017 libecpg.so -> libecpg.so.6.6
-rwxr-xr-x 1 500 sandbox  254702 Mar 23  2015 libedit.so.0
-rw-r--r-- 1 500 sandbox  415515 Mar 23  2015 libgssapi_krb5.so.
drwxrwxr-x 2 500 sandbox    4096 Mar 23  2015 pkgconfig
drwxrwxr-x 3 500 sandbox    4096 Mar 23  2015 postgresql

  1. Add and export the following variables in the Netprobe start script:

    Variable Value
    POSTGRES_LIB /usr/lib/postgresql/14/lib
    LD_LIBRARY_PATH <Existing path>:${POSTGRES_LIB}

  2. Save the changes done to the start script.

  3. Exit the text editor.

  4. Restart the Netprobe.

Success

You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor to Connect to a PostgreSQL database in SQL Toolkit configuration.

Windows Copied

  1. Modify the Path system variable of the Windows host.
 existing path; C:\Program Files\PostgreSQL\psqlODBC\bin
  1. Locate the PostgreSQL client library file - libpq.dll. Below is an example of script that shows you the PostgreSQL file:

C:\Program Files\PostgreSQL\psqlODBC\bin>dir
Volume in drive C has no label.
Volume Serial Number is 1846-3FFA

Directory of C:\Program Files\PostgreSQL\psqlODBC\bin
04/11/2017  06:43 PM              DIR. .
04/11/2017  06:43 PM              DIR..
03/17/2017  02:15 PM            31,232 capi.dll
03/17/2017  02:15 PM         1,659,904 libeay32.dll
03/17/2017  02:15 PM         1,690,490 libiconv-2.dll
11 File(s)      5,591,085 bytes
2 Dir(s)   3,842,527,232 bytes free
C:\Program Files\PostgreSQL\psqlODBC\bin
  1. Click Start on your computer.
  2. Right-click Computer, and click Properties.
  3. Click Advanced system settings.
  4. Click Environment Variables.
  5. Click the Path system variable. Path system variable
  6. Click Edit… to append the path of the libmysql.dll file. System variable
  7. Click OK to save the changes in the Edit System Variable screen.
  8. Click OK to save the changes in the Environment Variables screen.

Note

You have successfully defined the path variables of the Windows host. Go to the Gateway Setup Editor to Connect to a PostgreSQL database in SQL Toolkit configuration.

Solaris Copied

  1. Locate the PostgreSQL client library file - libpq.so.

  2. Add and export the following variables in the start script:

    Variable Value
    POSTGRES_LIB /usr/postgres/9.4-pgdg/lib
    LD_LIBRARY_PATH <Existing path>:${POSTGRES_LIB}

  3. Save the changes done to the start script.

  4. Exit the text editor.

  5. Restart the Netprobe.

Note

You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor.

Sybase ASE (SAP ASE) database Copied

The plugin behaviour depends on the Sybase client library referenced in these environment variables. If wide table and columns are used, Sybase client library must met the version supported by Geneos.

Linux Copied

  1. Locate the Sybase client library file – libsybintl_r64.so. Below is an example that shows you where to locate the Sybase client library file:

[root@pabo]# pwd
/opt/database_clients/sybase/OCS-15_0/lib
[root@pabo]# ls -l
total 25720
-rwxr-xr-x 1 root root  219202 Dec  8  2011 libsybblk64.a
-rwxr-xr-x 1 root root  136044 Dec  8  2011 libsybblk64.so
-rwxr-xr-x 1 root root  219564 Dec  8  2011 libsybblk_r64.a
-rwxr-xr-x 1 root root   83511 Dec  8  2011 libsybcs.so
-rwxr-xr-x 1 root root 1220366 Dec  8  2011 libsybct64.a
-rwxr-xr-x 1 root root   56235 Dec  8  2011 libsybdldap64.so.15.7.0.1
-rwxr-xr-x 1 root root   50541 Dec  8  2011 libsybdldap.so.15.7.0.1
-rwxr-xr-x 1 root root  865832 Dec  8  2011 libsybfssl64.so.15.7.0.1
  1. Locate the Sybase lib3p64 folder. For example:

[root@pabo OCS-15_0]# pwd
/opt/database_clients/sybase/OCS-15_0
[root@pabo OCS-15_0]# ls -l
total 2900
drwxr-xr-x 2 root root    4096 Jun  3  2014 bin
drwxr-xr-x 2 root root    4096 Nov  4  2016 config
drwxr-xr-x 2 root root    4096 Sep  4  2013 devlib
drwxr-xr-x 3 root root    4096 Jul  3 16:29 lib3p64
drwxr-xr-x 5 root root    4096 Sep  4  2013 sample
drwxr-xr-x 2 root root    4096 Sep  4  2013 scripts
drwxr-xr-x 3 root root    4096 May 28  2014 sybhelp
drwxr-xr-x 2 root root    4096 Sep  4  2013 xappdefaults
  1. Locate the Sybase interfaces file. For example:

[root@pabo sybase]# pwd
/opt/database_clients/sybase
[root@pabo sybase]# ls -l
total 68
drwxr-xr-x  8 root root 4096 Jun  9 00:30 backup
drwxr-xr-x 59 root root 4096 Sep  4  2013 charsets
-rwxr-xr-x  1 root root  567 Sep  4  2013 SYBASE.env
drwxr-xr-x  2 root root 4096 Sep  4  2013 Sybase_Install_Registry
-rwxr-xr-x  1 root root  841 Sep  4  2013 SYBASE.sh
-rwxr-xr-x  1  508  515 1212 Jul 23  2014 sybinit.err
drwxr-xr-x  4 root root 4096 Sep  4  2013 sybuninstall

This database uses the libraries that comes with the SAP ASE database. Add the following variables in the Netprobe start script:

Variable Value
SYBASE /path/to/sybase/home
SYBASE_LIB /path/to/sybase/lib
SYBASE_LIB3P64 /path/to/sybase/lib3p64
LD_LIBRARY_PATH <Existing path>:${SYBASE_LIB}:${SYBASE_LIB3P64}
  1. Add the SYBASE variable.
  2. Enter the path of the folder which contains the interfaces file in the SYBASE variable.
  3. Export the SYBASE variable:
export SYBASE=/opt/database_clients/Sybase
  1. Add the SYBASE_LIB variable.
  2. Enter the path of the folder which contains the libsybintl_r64.so file in the SYBASE_LIB variable.
  3. Export the SYBASE_LIB variable:
export SYBASE_LIB=/opt/database_clients/sybase/OCS-15_0/lib
  1. Add the SYBASE_LIB3P64 variable.
  2. Enter the path of the lib3p64 folder in the SYBASE_LIB3P64 variable:
export LD_LIBRARY_PATH=${NP_LIB}:${SYBASE_LIB}:${SYBASE_LIB3P64}
  1. Add the SYBASE_LIB and SYBASE_LIB3P64 variables into the LD_LIBRARY_PATH variable:
  2. Save the changes done to the start script.
  3. Exit the text editor.
  4. Add the following to /opt/sap/locale/locales.dat.
locale = en_UK, us_english, iso_1
  1. Add the following to the interface file.
GENEOSDB_AWS
        master tcp ether ec2-52-62-125-137.ap-southeast-2.compute.amazonaws.com 5000
  query tcp ether ec2-52-62-125-137.ap-southeast-2.compute.amazonaws.com 5000
  1. Run the following command:
source /opt/sap/./SYBASE.sh
  1. Restart the Netprobe.

Success

You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor to Connect to a Sybase database in SQL Toolkit configuration.

Windows Copied

  1. Configure the Windows environment and Sybase client.

  2. Locate the Sybase lib3p64 folder.

  3. Locate the Sybase interfaces file, sql.ini.

  4. Modify the Path system variable of the Windows host.

  5. Click Start on your computer.

  6. Right-click Computer, and click Properties.

  7. Click Advanced system settings.

  8. Click Environment Variables.

  9. Click the Path system variable. Environment Variables

  10. Click Edit… to append the path to libsybct64.dll and lib3p64 file. System variable

  11. Click OK to save the changes in the Edit System Variable screen.

  12. Click OK to save the changes in the Environment Variables screen.

  13. Add the SYBASE system variable in the Windowshost.

    • Click New… in the System variables group.
    • Enter the SYBASE name in the Variable name field.
    • Enter the path of the folder where the ini folder is located in the Variable value field.
    • Click OK to save the changes in the New System Variable screen.
    • Click OK to save the changes in the Environment Variables screen.

  14. Restart the Netprobe.

Success

You have successfully defined the path variables of the Windows host. Go to the Gateway Setup Editor to Connect to a Sybase database in SQL Toolkit configuration.

AIX Copied 

export SYBASE=/opt/sybase
export SYBASE_LIB=/opt/sybase/OCS-15_0/lib
export SYBASE_LIB3P64=/opt/sybase/OCS-15_0/lib3p64
export LIBPPATH=$SYBASE_LIB:$SYBASE_LIB3P64:$LIBPATH

Configure Sybase using OCS Copied

Linux Copied
  1. In the Netprobe start script, add the variable SYBASE_OCS. The value must be OCS_<ocs version>.
export SYBASE_OCS=OCS-15_0
  1. Export the SYBASE_OCS variable.
  2. Save the script.
  3. Add the following to /opt/sap/locale/locales.dat.
locale = en_UK, us_english, iso_1
  1. Add the following to the interface file.
GENEOSDB_AWS
        master tcp ether ec2-52-62-125-137.ap-southeast-2.compute.amazonaws.com 5000
  query tcp ether ec2-52-62-125-137.ap-southeast-2.compute.amazonaws.com 5000
  1. Run the following command:
source /opt/sap/./SYBASE.sh
  1. Restart the Netprobe.
Windows Copied

  1. Configure the Windows environment.

  2. Add the SYBASE system variable in the Windows host.

  3. Click Start on your computer.

  4. Right-click Computer, and then click Properties.

  5. Click Advanced system settings.

  6. Click Environment Variables.

  7. Click New… in the System variables group.

  8. Enter the SYBASE_OCS in the Variable name field.

  9. Enter the OCS version in the Variable value field.

  10. Click OK to save the changes in the New System Variable screen.

  11. Click OK to save the changes in the Environment Variables screen.

  12. Restart the Netprobe.

  13. Modify the Application Name setting in your SQL Toolkit sampler.

    Field Description
    SYBASE_OCS Containing the OCS_* folder name.
    Example: SYBASE_OCS=OCS-15_0
    ocs.cfg Includes the desired Netprobe application name in the file.
    Example: [Netprobe] CS_SEC_ENCRYPTION = CS_TRUE
    Application Name Same value defined in the ocs.cfg file.

The runtime configuration file ocs.cfg is used by Client-Library applications to set the following:

By using ocs.cfg, applications eliminate the need to call the routines to set the values.

This allows you to change the application settings without recompiling the code.

Note

By default, Client-Library does not read the ocs.cfg. However, all Client-Library based applications attempt to read the file if the file name exists in $SYBASE/$SYBASE_OCS/config.

The application must set the properties to enable Client-Library to use this file.

IBM Db2 database Copied

Linux Copied

  1. Locate the client library file - libdb2.so. Below is an example of an IBM Db2 Data server file:

$ cd DB2LIB is located at /opt/ibm/db2/V11.5/lib64
$ ls -l libdb2.so*
lrwxrwxrwx. 1 root root       12 Jun 11  2019 libdb2.so -> libdb2.so.1
-r-xr-xr-x. 1 bin  bin  42566992 Jun 11 2019 libdb2.so.1
  1. Locate the home directory of the Db2 Data Server Client instance created to connect to the target Db2 database:
$ cd /home/db2inst1/sqllib/
$ ls -l
total 120
drwxr-sr-x. 2 db2inst1 db2iadm1  4096 Apr 25 21:40 adm
drwxr-s---. 2 db2inst1 db2iadm1  4096 Apr 25 21:40 backup
lrwxrwxrwx. 1 root     db2iadm1    31 Apr 25 21:40 bin -> /opt/lib/database/db2/V10.5/bin
/opt/lib/database/db2/V10.5/conv
drwxrwsr-t. 2 db2inst1 db2iadm1  4096 Apr 25 21:40 ctrl
drwxrwsr-t. 2 db2inst1 db2iadm1  4096 Apr 25 21:40 ctrlha
-rwxr-xr-x. 1 db2inst1 db2iadm1 12147 Apr 25 21:40 db2cshrc
drwxrwsrwt. 2 db2inst1 db2iadm1  4096 Apr 25 21:40 db2dump
-rwxr-xr-x. 1 db2inst1 db2iadm1  7962 Apr 26 00:50 db2profile

Note

The instance name will be the value of the variable DB2INSTANCE. The path will be the value of the variable DB2DIR.

  1. Add and export the following variables in the Netprobe start script:

    Variable Value
    DB2Instance export DB2INSTANCE=db2inst1
    DB2DIR export DB2DIR=/home/${DB2INSTANCE}/sqllib
    DB2LIB export DB2LIB=${DB_CLIENT_HOME}/db2/V10.5/lib64
    LD_LIBRARY_PATH export LD_LIBRARY_PATH=${DB2LIB}

  2. Save the changes done to the start script.

  3. Exit the text editor.

  4. Run the following command:

source /home/db2inst1/sqllib/db2profile
  1. Restart the Netprobe.

Note

You have successfully defined the database variables and their values in the Netprobe start script. Go to the Gateway Setup Editor toConnect to a Db2 database in SQL Toolkit configuration.

Windows Copied

  1. Locate the client library file - db2cli64.dll:

C: >cd C:\database_libs\db2\SQLLIB\lib
C:>dir
Volume in drive C has no label.
Volume Serial Number is 1846-3FFA
Directory of C:\database_libs\db2\SQLLIB\lib
04/26/2018  05:31 PM    DIR          .
04/26/2018  05:31 PM    DIR          ..
05/30/2013  12:13 AM           634,362 db2api.lib
05/30/2013  12:13 AM             2,546 db2apie.lib
05/30/2013  12:13 AM             4,964 db2psmd.lib
04/26/2018  05:31 PM    DIR          Win32
6 File(s)        835,144 bytes
3 Dir(s)   5,418,590,208 bytes free
  1. Modify the Path variable of the Windows host.
  2. Edit the Path system variable in the Environment Variables.
  3. Click Edit... to append the path of the db2cli64.dll file.
  4. Click OK to save the changes in the New System Variable screen.
  5. Click OK to save the changes in the Environment Variables screen.

Note

 If your database is already attached to a node, then you need to uncatalog them in order to successfully connect to the DB server by running the following:


db2 uncatalog database <database name>
db2 uncatalog database <node name>

For example:


db2 uncatalog database SAMPLE
db2 uncatalog database DB2INST1

Success

You have successfully defined the path variables of the Windows host. Go to the Gateway Setup Editor to Connect to a Db2 database in SQL Toolkit configuration.

AIX Copied

If you wish to run the Netprobe on a different machine to that on which the Db2 server is running, then you would need to download and install the Db2 Client package from IBM’s website. You should then follow the instructions in the “Quick Beginnings for DB2 Clients” document to set-up communications between the client and the server. This document can be found on IBM’s website. Finally, you would need to configure the Netprobe environment, so that the client libraries are available to it.

export DB2LIB=/opt/db2/V11.5/lib64
export LIBPATH=$DB2LIB:$LIBPATH

Solaris Copied

If you wish to run the Netprobe on a different machine to that on which the Db2 server is running, then you would need to download and install the Db2 Client package from IBM’s website. You should then follow the instructions in the “Quick Beginnings for DB2 Clients” document to set-up communications between the client and the server. This document can be found on IBM’s website. Finally, you would need to configure the Netprobe environment, so that the client libraries are available to it.

setenv LD_LIBRARY_PATH /opt/IBM/db2/V9.7/lib32

Configure the SQL Toolkit sampler Copied

For more information on plugin configuration, see SQL Toolkit configuration.

  1. Open your Gateway Setup Editor
  2. Go to Samplers > New Sampler to create a new sampler.
  3. Input a name for your new sampler.
  4. Select SQL Toolkit in the Plugin dropdown list.

SQL Toolkit sampler

  1. Select the database where you will connect the plugin to on the Database menu. For more information on the database configuration in your SQL toolkit sampler, see the following:

Note

The Database dropdown list displays all the supported databases regardless of their availability on the underlying system the Netprobe is running on. The Administrator must ensure that the appropriate database client libraries are correctly installed and configured before expecting the selected one to work. For more information, see Configure the Netprobe and database client libraries.

Adjust the maximum database connections Copied

The probe has a default value of 10, which means that no more than ten simultaneous open connections can be made to all databases configured on the Netprobe.

Before the sampler configuration starts, you can limit the total number of database for each Netprobe. This limit is set to ensure the configured database plugins do not consume all available database sessions.

To configure the maxDatabaseConnections in Netprobe:

  1. In Gateway Setup Editor, open the host Netprobe where the SQL Toolkit is running.

  2. Go to the Managed entities to verify the host Netprobe. This will resolve the following error: Error: SQL-Toolkit Fail maximum connections have been exceeded (0).

  3. Click the Advanced tab in the Probe section.

  4. Enter the desired number of connections in the Max database connections field.

This sets the maximum amount of connections that a Netprobe can make to any database. For example, if:

This setup uses ten database connections in total. If the Netprobe has the default value of 10, then there is no additional database connections can be made.

Set SQL Toolkit DB authentication Copied

To require authentication when configuring the database connection, set your username and password:

  1. Enter your credentials in the Connection > User Name and Connection > Password fields.
  2. Select AES 256 bit Encryption on the Password menu to set an encrypted password.
  3. Confirm the password, and click OK.

Manage unsupported data types Copied

SQL Toolkit plugin does not support the following data types:

If you use any of these data types to execute database queries, Active Console will unable to display the queries correctly:

In the example above, the COL2 column contains a clob data type in Oracle database. To manage queries that return unsupported data types in SQL Toolkit, you may rewrite the query with SQL functions that convert the data to readable formats. For example:

  1. You can use the cast and substr functions for conversion. The substr function is use to reduce the output to 1000 characters if the source data is huge.

  2. Configure the SQL query in the sampler:

SELECT col1, cast(substr(col2,1,1000) as varchar(1000)) AS col2 FROM clob_table

Success

Active Console now displays the dataview with the readable formats.

Interval data type Copied

SQL Toolkit plugin does not support the Interval data type specific to MySQL and Oracle databases. Active Console might display the following error messages:

["Geneos"] ["Geneos > Netprobe"] ["User Guide"]

Was this topic helpful?