Geneos

SQL Toolkit PlugIn User Guide

Overview

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.

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

  • MySQL or MariaDB.
  • Microsoft SQL Server
  • Oracle
  • SAP ASE, formerly known as Sybase ASE.
  • PostgreSQL.
  • IBM DB2.

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

A typical view constructed using the SQL Toolkit plug-in 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:

  • Security administrators who check and review audit logs recorded in the database server.
  • Service Level Managers who get alerted on application incidents.
  • Administrators who view the quality metrics inserted in the database servers.

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

Headline Description
queryStatus

This has three possible values:

Initializing - displays when the plug-in 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 in Idle.
rowsReturned Shows the number of rows returned in the dataview.
   

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

Intended audience

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

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

Configure the Netprobe and database client libraries

Please take note of these important reminders and requirements before you start the configuration steps to the Netprobe and DB client libraries.

The configuration steps for each database changes depending on the operating system you are using:

Linux

  • Ensure the database libraries and paths are defined in the Netprobe start script. A Netprobe start script is where you define the required variables to run a Netprobe.
  • Log in to the Linux server where the Netprobe is running.
  • Use any Secure Shell or SSH client to access the server and to modify the Netprobe start script.

Windows

  • Ensure the required Windows environment variables and libraries are defined in the system properties.
  • Log in the Windows server where the Netprobe is running.
  • Access the actual Windows server.

AIX

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.

Note: 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.

MySQL database

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

Linux

  1. Locate the MySQL client library file (libmysqlclient_r.so).
  2. Below is an example that shows the required client libraries:

    [root@pabo lib]# cd lib/
    [@root@pabo lib]# ls -1
    total 12748
    -rw-r--r--. 1 sandbox sandbox 8199192 Aug  8  2009 libmysqlclient.a
    lrwxrwxrwx. 1 sandbox sandbox      18 Sep 21 18:52 libmysqlclient_r.so -> libmysql.so.16.0.0
    lrwxrwxrwx. 1 sandbox sandbox      11 Aug  8  2009 libmysqlclient.so -> libmysql.so
    lrwxrwxrwx. 1 sandbox sandbox      14 Aug  8  2009 libmysql.so -> libmysql.so.16
    lrwxrwxrwx. 1 sandbox sandbox      18 Aug  8  2009 libmysql.so.16 -> libmysql.so.16.0.0
    -rwxr-xr-x. 1 sandbox sandbox 4851331 Aug  8  2009 libmysql.so.16.0.0
    [root@pabo lib]#				

    Note: A symbolic link or symlink must be created between the connector C library and the file libmysqlclient_r.so.

  3. Add these variables to the Netprobe start script using a text editor.
  4. Variable Value
    MYSQL_LIB export MYSQL_LIB=/opt/lib/database/mysql/connector_c_6_0_2/lib
    LD_LIBRARY_PATH export LD_LIBRARY_PATH=${NP_LIB}:${MYSQL_LIB}
       
  5. Add the MYSQL_LIB variable.
  6. Enter the path of the libmysqlclient_r.so file in the MYSQL_LIB variable:
  7. MYSQL_LIB=/opt/lib/database/mysql/connector_c_6_0_2/lib
  8. Enter the MYSQL_LIB in the LD_LIBRARY_PATH variable:
  9. export LD_LIBRARY_PATH=${NP_LIB}:${MYSQL_LIB}
  10. Save the changes to the start script.
  11. Exit the editor.
  12. Restart the Netprobe.

You have successfully defined the database library paths in the Netprobe start script.
Go to the Gateway Setup Editor to configure your SQL Toolkit Sampler.

Windows

  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:
    VariableValue
    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>
  3. Click Start on your computer.
  4. Right-click Computer, and click Properties.
  5. Click Advanced system settings.
  6. Click Environment Variables.
  7. Click the Path system variable.
  8. Click Edit… to append the path of the libmysql.dll file
  9. Click OK to save the changes in the Edit System Variable screen.
  10. Click OK to save the changes in the Environment Variables screen.
  11. Restart the Netprobe.

Success: You have successfully defined the path variables of the Windows host.
Go to the Gateway Setup Editor to configure your SQL Toolkit Sampler.

Microsoft SQL server database

Linux

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

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

    [ODBC Driver 13 for SQL Server]
    Description=Microsoft ODBC Driver 13 for SQL Server
    Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.0.so.1.0
    UsageCount=1				
  4. Copy or symlink the /etc/odbcinst.ini to /opt/microsoft/msodbcsql/lib64 folder:
  5. sudo ln -s /etc/odbcinst.ini /opt/microsoft/msodbcsql/lib64/odbcinst.ini

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

  6. Add these variables to the Netprobe start script using a text editor. Follow the variables and their values in the table:
  7. Variable Value
    SQLSERVER_LIB /opt/microsoft/msodbcsql/lib64
    ODBCHOME ${SQLSERVER_LIB}
    ODBCSYSINI ${SQLSERVER_LIB}
    ODBCINSTINI odbcinst.ini
    GENEOS_MSSQL_DRIVER 'ODBC Driver 13 for SQL Server'
    LD_LIBRARY_PATH <Existing path>:${SQLSERVER_LIB}
       
  8. Add the variables and their corresponding values:
  9. export SQLSERVER_LIB=/opt/microsoft/msodbcsql/lib64
    export ODBCHOME=${SQLSERVER_LIB}
    export ODBCSYSINI=${SQLSERVER_LIB}
    export ODBCINSTINI=odbcinst.ini
    export GENEOS_MSSQL_DRIVER='ODBC Driver 13 for SQL Server'					

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

  10. Add the SQLSERVER_LIB in the LD_LIBRARY_PATH variable:
  11. export LD_LIBRARY_PATH=${NP_LIB}:${SQLSERVER_LIB}					
  12. Save the changes to the start script.
  13. Exit the editor.
  14. 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 configure your SQL Toolkit Sampler.

Windows

  1. Locate the Microsoft SQL Server Native Client – sqlncli*.dll. It is usually located in %SYSTEMROOT%\System32 and %SYSTEMROOT%\SysWOW64.
  2. Proceed to the configuration of your SQL Toolkit Sampler if the Native Client exists.
  3. Download the Microsoft SQL Server Native Client from Microsoft’s website if the Native Client does not exist.
    1. Run the installer.
    2. Restart the Netprobe.
  4. Proceed to the configuration of your SQL Toolkit Sampler.

Oracle database

Linux

  1. Locate the Oracle library file - libclntsh.so. Below is an example that shows you where to locate the Oracle library file:
  2. [root@pabo lib]# pwd
    /u01/app/oracle/product/12.1.0/dbhome_1/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]#
  3. Locate the tnsnames.ora file. Below is an example of script that shows you the tnsnames.ora file:
  4. [root@pabo admin]# pwd
    /u01/app/oracle/product/12.1.0/dbhome_1/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				
  5. Add these variables to the Netprobe start script using a text editor. Refer to the table below:
  6. Variable Value
    ORACLE_HOME /u01/app/oracle/product/12.1.0/dbhome_1/
    TNS_ADMIN /u01/app/oracle/product/12.1.0/dbhome_1/network/admin
    ORACLE_LIB /u01/app/oracle/product/12.1.0/dbhome_1/lib
    LD_LIBRARY_PATH <Existing path>:${ORACLE_LIB}
       
  7. Add the ORACLE_HOME variable.
  8. Enter the path of the lib folder that contains the libclntsh.so file in the ORACLE_HOME variable.
  9. Export the ORACLE_HOME variable:
  10. export ORACLE_HOME=/u01/app/oracle/product/12.1.0/dbhome_1/
  11. Add the TNS_ADMIN variable.
  12. Export the TNS_ADMIN variable. For example:
  13. export TNS_ADMIN=/u01/app/oracle/product/12.1.0/dbhome_1/network/admin
  14. Add the ORACLE_LIB variable.
  15. Enter the path of the folder which contains the libclntsh.so file in the ORACLE_LIB variable.
  16. Export the ORACLE_LIB variable:
  17. export ORACLE_LIB=/u01/app/oracle/product/12.1.0/dbhome_1/lib
  18. Add the variables ORACLE_LIB in the LD_LIBRARY_PATH variable:
  19. export LD_LIBRARY_PATH=${NP_LIB}:${ORACLE_LIB}
  20. Exit the editor.
  21. Restart the Netprobe.

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

Windows

  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:
  4. Variable Value
    Path <Existing path>;C:\database_libs\oracle\lib
    ORACLE_HOME C:\database_libs\oracle
    TNS_ADMIN C:\database_libs\oracle
       
  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.
  10. Click Edit… to append the path to oci.dll file.
  11. Click OK to save the changes in the Edit System Variable screen.
  12. Add the ORACLE_HOME system variable in the Windows host.
    1. Click New... in the System variables group.
    2. Enter the ORACLE_HOME in the Variable name field.
    3. Enter the path of the oci.dll file in the Variable value field.
    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.
  13. Add the TNS_ADMIN system variable in the Windows host.
    1. Click New... in the System variables group.
    2. Enter the TNS_ADMIN in the Variable name field.
    3. Enter the path of the oci.dll file in the Variable value field. :
    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.

You have successfully defined the path variables of the Windows host.
Go to the Gateway Setup Editor to configure your SQL Toolkit Sampler.

PostgreSQL database

Linux

  1. Locate the PostgreSQL client library file - libpq.so. Below is an example that shows you where to locate the PostgreSQL client library file:
  2. [root@pabo admin]#  pwd
    /opt/lib/database/postgres/64/9.4/pgsql/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					
  • Add and export the following variables in the Netprobe start script:
    1. Variable Value
      POSTGRES_LIB /opt/postgres/64/9.4/pgsql/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.

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

    Windows

    1. Modify the Path system variable of the Windows host.
    2.  existing path; C:\Program Files\PostgreSQL\psqlODBC\bin
    3. Locate the PostgreSQL client library file - libpq.dll. Below is an example of script that shows you the PostgreSQL file:
    4. 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					
    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.
    10. Click Edit… to append the path of the libmysql.dll file
    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.

    You have successfully defined the path variables of the Windows host.
    Go to the Gateway Setup Editor to configure your SQL Toolkit Sampler.

    Sybase ASE (SAP ASE) database

    Linux

    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:
    2. [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			
    3. Locate the Sybase lib3p64 folder. For example:
    4. [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					
    5. Locate the Sybase interfaces file. For example:
    6. [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}
         
    7. Add the SYBASE variable.
    8. Enter the path of the folder which contains the interfaces file in the SYBASE variable.
    9. Export the SYBASE variable:
    10. export SYBASE=/opt/database_clients/Sybase
    11. Add the SYBASE_LIB variable.
    12. Enter the path of the folder which contains the libsybintl_r64.so file in the SYBASE_LIB variable.
    13. Export the SYBASE_LIB variable:
    14. export SYBASE_LIB=/opt/database_clients/sybase/OCS-15_0/lib
    15. Add the SYBASE_LIB3P64 variable.
    16. Enter the path of the lib3p64 folder in the SYBASE_LIB3P64 variable:
    17. export LD_LIBRARY_PATH=${NP_LIB}:${SYBASE_LIB}:${SYBASE_LIB3P64}
    18. Add the SYBASE_LIB and SYBASE_LIB3P64 variables into the LD_LIBRARY_PATH variable:
    19. Save the changes done to the start script.
    20. Exit the text editor.
    21. Restart the Netprobe.

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

    Windows

    1. Configure the Windows environment and Sybase client.
    2. Locate the Sybase client library file - libsybintl_r64.so.
    3. Locate the Sybase lib3p64 folder.
    4. Locate the Sybase interfaces file.
    5. Modify the Path system variable of the Windows host.
    6. Click Start on your computer.
    7. Right-click Computer, and click Properties.
    8. Click Advanced system settings.
    9. Click Environment Variables.
    10. Click the Path system variable.
    11. Click Edit… to append the path to libsybct64.dll and lib3p64 file.
    12. Click OK to save the changes in the Edit System Variable screen.
    13. Click OK to save the changes in the Environment Variables screen.
    14. Add the SYBASE system variable in the Windows host.
      1. Click New... in the System variables group.
      2. Enter the SYBASE name in the Variable name field.
      3. Enter the path of the folder where the ini folder is located in the Variable value field.
      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.
    15. Restart the Netprobe.

    You have successfully defined the path variables of the Windows host.
    Go to the Gateway Setup Editor to configure your SQL Toolkit Sampler.

    IBM DB2 database

    Linux

    1. Locate the client library file - libdb2.so. Below is an example of an IBM DB2 Data server file:
    2. $ cd /opt/lib/database/db2/V10.5/lib64
      $ ls -l libdb2.so*
      lrwxrwxrwx. 1 root root       11 Aug 17  2013 libdb2.so -> libdb2.so.1
      -r-xr-xr-x. 1 bin  bin  42566992 Apr 25 21:40 libdb2.so.1				
    3. Locate the home directory of the DB2 Data Server Client instance created to connect to the target DB2 database:
    4. $ 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.

    5. Add and export the following variables in the Netprobe start script:
    6. 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}
         
    7. Save the changes done to the start script.
    8. Exit the text editor.
    9. Restart the Netprobe.

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

    Windows

    1. Locate the client library file - db2cli64.dll:
    2. 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	
    3. Modify the Path variable of the Windows host.
    4. Edit the Path system variable in the Environment Variables.
    5. Click Edit... to append the path of the db2cli64.dll file.
    6. Click OK to save the changes in the New System Variable screen.
    7. Click OK to save the changes in the Environment Variables screen. 

    You have successfully defined the path variables of the Windows host.
    Go to the Gateway Setup Editor to configure your SQL Toolkit Sampler.

    Configure the SQL Toolkit sampler

    For more information on plug-in configuration, see SQL Toolkit Plug-in Technical Reference.

    1. Go to Gateway > Probes > Managed entity > Samplers to create a SQL toolkit sampler.
    2. In the Samplers section, select sql-toolkit on the Plugin menu.
    3. Select the database where you will connect the plug-in to on the Database menu.

    Note: The Database menu drop-down 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, as above, before expecting the selected one to work.

    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 plug-ins do not consume all available database sessions.

    Adjust the maximum database connections

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

    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 entity section to verify the host Netprobe.
    3. This will resolve the "Error: SQL-Toolkit Fail maximum connections have been exceeded (0)" issue.

    4. Click the Advanced tab in the Probe section.
    5. 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:

    • One Managed entity belonging to one probe, uses three SQL Toolkit plug-ins.
    • One Sybase plug-in and one Oracle plug-in.
    • Two Managed entities belonging to one probe, uses the same.

    This set-up uses 10 database connections in total. If probe has the default value of 10, then there is no additional database connections can be made.

    Use a MySQL DB

    1. Select MySQL on the Database menu.
    2. In the Mysql group, enter the name of the MySQL server in Server Name field.
    3. Enter the name of the database to use when running the query in the Database Name field.
    4. Enter the port number where MySQL is connected to in the Port field.
    5. Field Description
      Server Name Hostname of the server where the MySQL database is running.
      Database Name Name of database you need to monitor or is connected to.
      Port Port used by the running MySQL database.
       
      Default: 3306
         

      Below is an example for SQL Toolkit sampler configuration using MySQL:

    6. Enter the name of the query in the Name field.
    7. Enter the SQL command to run the query in the Sql field.
    8. Name: event_table
      Sql: select * from event_table order by timestamp desc
      
      Name 2: MySQL version
      Sql 2: SELECT VERSION()
      
      Name 3: Geneos DB schema version
      Sql 3: select * from version_table			
    9. Click the Validate button to check your configuration.
    10. Click the Save button to save your changes.
    11. Go to the SQL Toolkit dataview in Active Console to check if your SQL query runs successfully.

    Use a SQL Server DB

    1. Select Sql server on the Database menu.
    2. In the Sql server group, enter the hostname and instance name of the SQL server in the Server Name field.
      • Follow this server name format: Servername\InstanceName
      • The server name can accept the hostname or ServerName if there is only once instance running on the database's host.

      Note: Dynamic MS SQL instances are not supported.

    3. Enter the name of the database to use when running the query in the Database Name field.
    4. Enter the port number where Sql server is connected to in the Port field.
    5. Field Description
      Server Name Hostname of the server where the SQL server database is running.
      Database Name Name of database you need to monitor or is connected to.
      Port Port used by the running SQL server database.
       
      Default: 1433
         
    6. Enter the name of the query in the Name field.
    7. Enter the SQL command to run the query in the Sql field.
    8. Name: Geneos DB schema version
      Sql: select * from event_table
      
      Name 2: SQL Server version
      Sql 2: select @@version	

      Caution: You should only enter SQL SELECT statements that return output in table format. Otherwise, if you try to use other types of SQL statements, this plugin may fail and display the error message: Fail - Failed to get rows as Query was probably INSERT, DELETE, UPDATE or procedure call(0).

    9. Click the Validate button to check your configuration.
    10. Click the Save button to save your changes.
    11. Go to the SQL Toolkit dataview in Active Console to check if your SQL query runs successfully.

    Use an Oracle DB

    1. Select Oracle on the Database menu.
    2. In the Oracle group, enter the name of the database to use when running the query in the Database Name field.

      Note: You can extract the DB name for this by using the tnsnames.ora file or execute the query using the "select name from v$database" in the database.

    3. Enter the name of the query in the Name field.
    4. Enter the SQL command to run the query in the Sql field.
    5. Name: query_processes_table
      Sql: select * from processes_table
      
    6. Click the Validate button to check your configuration.
    7. Click the Save button to save your changes.
    8. Go to the SQL Toolkit dataview in Active Console to check if your SQL query runs successfully.

    To configure an Oracle SQL Toolkit using connection string, copy and enter the sample below in the Database Name field:

    Sample connection string: (DESCRIPTION =   (
    ADDRESS = (PROTOCOL = TCP)
    HOST = 192.168.100.87)(PORT = 1521))
    (CONNECT_DATA =   (SERVER = DEDICATED)       
    (SERVICE_NAME = geneosdb.mnl.itrsgroup.com)     )   )				

    You can use the connection string instead of using the actual database name. This means that when the database name changes, you can still connect provided that the connection string remains intact.

    Note: If you encounter the SQL-TOOLKIT Fail - OCI_INVALID_HANDLE(0) error while configuring the Oracle database, ensure to define and export the ORACLE_HOME and LD_LIBRARY_PATH variables. See Oracle database.

    Use a PostgreSQL DB

    1. Select PostgreSQL on the Database menu.
    2. In the PostgreSQL server group, enter the name of the PostgreSQL server in the Server Name field.
    3. Enter the name of the database to use when running the query in the Database Name field.
    4. Enter the port number where Sql server is connected to in the Port field.
    5. Field Description
      Server Name Hostname of the server where the PostgreSQL server database is running.
      Database Name Name of database you need to monitor or is connected to.
      Port Port used by the running SQL server database.
         
    6. Enter the name of the query in the Name field.
    7. Enter the SQL command to run the query in the Sql field.
    8. Name: Postgres Version
      Sql: select version()
      
      Name 2: Products Table
      Sql 2: select * from products
      
      Name 3: Geneos DB schema version
      Sql 3: select * from version_table			
    9. Click the Validate button to check your configuration.
    10. Click the Save button to save your changes.
    11. Go to the SQL Toolkit dataview in Active Console to check if your SQL query runs successfully.

    Use a Sybase DB

    1. Select Sybase on the Database menu.
    2. In the Sybase group, enter the server name of the database to connect to that is located in the interfaces file.
    3. Note: The Sybase database connection details are specified in the interfaces file.
      This is a text file included in SAP ASE installation which contains the connection details of SAP ASE Database, including its host name and port.

    4. Enter the name of the database to use when running the query in the Database Name field.
    5. Enter the application name to be set to the connection created from the sampler of the Database Server in the Application Name field.
    6. Field Description
      Interface Entry Database entry located in the interfaces file.
      Database Name Name of database you need to monitor or is connected to.
      Application Name Same value defined in the ocs.cfg file.
         
    7. Enter the name of the query in the Name field.
    8. Enter the SQL command to run the query in the Sql field.
    9. Name: SAP ASE version
      Sql: select @@version
    10. Click the Validate button to check your configuration.
    11. Click the Save button to save your changes.
    12. Go to the SQL Toolkit dataview in Active Console to check if your SQL query runs successfully.

    Configure Sybase using OCS

    Linux

    1. In the Netprobe start script, add the variable SYBASE_OCS. The value must be OCS_<ocs version>.
    2. export SYBASE_OCS=OCS-15_0
    3. Export the SYBASE_OCS variable.
    4. Save the script.
    5. Restart the Netprobe.

    Windows

    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.
    14. 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:

    • Property values
    • Server option values
    • Server capabilities
    • Debugging options

    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.

    Set SQL Toolkit DB authentication

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

    1. Enter your credentials in the User Name and 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

    SQL Toolkit plugin does not support the following data types:

    • SA_dtUnknown — Unknown
    • SA_dtInterval — Interval (MySQL specific)
    • SA_dtLongBinary — Long binary data
    • SA_dtBLob — BLob data
    • SA_dtCLob — CLob data
    • SA_dtCursorOracle REF CURSOR
    • SA_dtSpecificToDBMS — server-specific and not interpreted by SQLAPI++

    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.
    2. The substr function is use to reduce the output to 1000 characters if the source data is huge.

    3. Configure the SQL query in the sampler:
    4. 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

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

    • MySQL database — unsupported::interval.
    • OracleORA-01406: fetched column value was truncated. If you encounter this problem, you may update to newer version of Netprobe.