SQL Toolkit Plug-In TECHNICAL REFERENCE

This is a sample query that is used in SQL Toolkit plug-in:

select distinct
        from_unixtime(timestamp) as 'Date',
        varname as 'Managed Variable',
        description
        from event_table
        where unix_timestamp(now()) - timestamp <= 3600
        and severity >= 2
        order by Date asc

The data returned by the query is converted into a set of regular Geneos Managed Variables. All the standard Geneos Enterprise Management Framework functionality (e.g. setting rules, logging to a database) applies.

Plug-in Configuration

Query1

select * from my_table

connection > database > oracle > var-databaseName

The name of the database to use when running the query. This is specified in the tnsnames.ora file.

Note: The full TNS definition in tnsnames.ora may be specified in place of the database name in any of these cases:

• tnsnames.ora is available
• tnsnames.ora is available
• tnsnames.ora is available

If tnsnames.ora is available, doing so will override the details of the file in your Oracle directory.

For example, if you have these configuration details:

MYALIAS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MYHOST)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = MYDATABASE)
      (SERVICE_NAME = MYDATABASE)
    )
  )

In the database name field, you may specify as follows:

(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=MYHOST)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SID=MYDATABASE)(SERVICE_NAME=MYDATABASE)))

Ensure that the alias is not included and the syntax does not have spaces. All necessary environment variables must also be set in the start-up environment of the Netprobe and adjusted for the operating system and start-up script in use. E.g. for UNIX/Linux components started from a bash script:

export ORACLE_HOME=/usr/local/oracle/instantclient_11_2
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib64:${LD_LIBRARY_PATH}

Remember to adjust the path to the Oracle installation directory and choose the correct library subdirectory based on the architecture of the Netprobe in use.

Mandatory: Yes

showHeadlines

Headlines showing the rows returned, query status and query time taken should be displayed

Mandatory: No

It is generally not useful to set the plug-in sample interval to a very small value, as most queries take some time to evaluate (if you are interested in real-time plots of data, the Active Chart feature of ActiveConsole may be more suitable). If the system attempts to sample before the current query has returned, the plug-in will ignore the sample request.

showRowline

Each row should be numbered to indicate its place relative to the other rows.

Mandatory: No

Default: false

Configuring the Netprobe environment

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 plug-in has been configured to connect to.

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

MySQL

Linux

When MySQL is installed via packaged installation, it automatically adds /user/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.

Windows

set PATH=C:\Program Files\MySQL\MySQL Server 5.0\bin;%PATH%

setenv LD_LIBRARY_PATH /export/share/dev/ThirdPartyRuntime/solaris

IBM AIX

setenv LD_LIBRARY_PATH /usr/local/mysql

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.

Sybase

The plug-in 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.

Please see Database Support section in Geneos Compatibility Matrix.

Windows

Needs an assortment of libraries usually found in the lib directory of the Sybase installation directory.

set PATH=C:\Sybase\OCS_0\dll;%PATH%

Solaris

Needs an assortment of libraries usually found in the lib directory of the Sybase installation directory.

setenv LD_LIBRARY_PATH /export/share/dev/SYBASE/OCS/lib

Linux

Needs an assortment of libraries usually found in the lib directory of the Sybase installation directory.

setenv LD_LIBRARY_PATH /export/share/dev/SYBASE/OCS/lib

AIX

Needs an assortment of libraries usually found in the lib directory of the Sybase installation directory.

Needs an assortment of libraries usually found in the lib directory of the Sybase installation directory.

setenv SYBASE /opt/sybase
setenv LD_LIBRARY_PATH /opt/sybase/OCS/lib

Oracle

Windows

Needs an assortment of libraries usually found in the bin directory of the Oracle installation directory.

set PATH=C:\oracle\product\client_1\bin;%PATH%

Solaris

Needs an assortment of libraries usually found in the bin directory of the Oracle installation directory.

setenv ORACLE_HOME /usr/oracle/oracle/product/client
setenv LD_LIBRARY_PATH /usr/oracle/oracle/product/client/lib32

Linux

Needs an assortment of libraries usually found in the bin directory of the Oracle installation directory.

setenv ORACLE_HOME /usr/oracle/oracle/product/client
setenv LD_LIBRARY_PATH /usr/oracle/oracle/product/client/lib32

AIX

Needs an assortment of libraries usually found in the bin directory of the Oracle installation directory.

setenv ORACLE_HOME /opt/oracle/product/
setenv LD_LIBRARY_PATH /opt/oracle/product/lib32

MS SQL Server

Windows

The machine running the netprobe needs to have the SQL Server OLEDB client installed on it. This is installed as part of the SQL Server Native Client which can be downloaded from Microsoft's website.

Solaris

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

Linux

For MS SQL Server database connection from a Linux host, you need to acquire separate packages for 32-bit or 64-bit based probes. Please refer to Appendix A.

AIX

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

IBM DB2

Windows

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, e.g.

set PATH=C:\Program Files\IBM\SQLLIB\BIN;%PATH%

Solaris

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, e.g.

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

Linux

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.

[32-bit netprobe]       setenv LD_LIBRARY_PATH /opt/ibm/db2/V9.7/lib32
[64-bit netprobe]       setenv LD_LIBRARY_PATH /opt/ibm/db2/V9.7/lib64

AIX

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, e.g.

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

PostgreSQL

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. Path shown below are just sample paths and may vary depending on the installation.

Windows

Needs the libpq.dll and intl.dll libraries.

set PATH=C:\Program Files (x86)\PostgreSQL\9.4\bin;%PATH%

Linux

Needs the libpq.so library.

setenv LD_LIBRARY_PATH /opt/PostgreSQL/9.4/lib

Solaris

Needs the libpq.so library.

setenv LD_LIBRARY_PATH /usr/postgres/9.4-pgdg/lib

AIX

Needs the libpq.so library.

setenv LD_LIBRARY_PATH /usr/local/pgsql/lib

Appendix A - MS SQL Server Database Setup

tar xvzf <package-name>.tgz

[FreeTDS]
Driver = /opt/geneos/SQL-DB/libtdsodbc.so

[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

Setting up the environment before starting Gateway/Netprobe

1. Create gateway startup scripts from the templates provided.
2. Set the environment variable values pertaining to MS SQL Server database in the gateway start up script (where /opt/microsoft/msodbcsql/lib64 is replaced by the path which contains the MS ODBC Driver 13 for SQL Server.

   setenv SQLSERVER_LIB /opt/microsoft/msodbcsql/lib64
   setenv LD_LIBRARY_PATH ${SQLSERVER_LIB}:.
   setenv ODBCHOME ${SQLSERVER_LIB}
   setenv ODBCSYSINI ${SQLSERVER_LIB}
   setenv ODBCINSTINI odbcinst.ini
   setenv GENEOS_MSSQL_DRIVER 'ODBC Driver 13 for SQL Server'

:: note::
   The environment variable $GENEOS_MSSQL_DRIVER is used to determine which entry in your odbcinst.ini refers to
   your MS SQL driver.  If this isn't set it defaults to driver 13 as above.

   When MS SQL server is configured Geneos will ensure that by default the library 'libodbc.so' is used.
   This will be found in the ${SQLSERVER_LIB} directory so you should ensure that this is first in your LD_LIBRARY_PATH.

   Older installations may have the $GENEOS_ODBCLIB environment variable set.  If this is the case it will be overriden
   and a warning written to the log file.  This is to prevent iODBC being used by default wheere it is installed.
   Pre (GA3.0.16) Gateways and Netproves have no concept of this.  The work-around is to place a symbolic link from libiodbc.so to libodbc.so in the
   same directory and ensure it comes first on the LD_LIBRARY_PATH.

3. Configure MS SQL Server database connection in the SQL-Toolkit Plug-In and run the Netprobe. You should be able to connect to an MS SQL Server database. You should see the following line in log file "Connecting to DB using Free TDS Driver" or "Connecting to DB using ODBC Driver 13 for SQL Server".

Sample Queries

Oracle

SELECT * FROM tbl_name;

SELECT * FROM tbl_name

SELECT func_name FROM dual;

SELECT func_name FROM dual

SELECT func_name('str', 1)
FROM dual;

SELECT func_name('str', 1) FROM dual

SELECT func_name(col1, col2)
FROM tbl_name;

SELECT func_name(col1, col2)
FROM tbl_name

exec proc_name;

proc_name

BEGIN proc_name; END;

exec proc_name('str', 1);

BEGIN proc_name ('str', 1); END;

BEGIN proc_name ('str', 1); END;

DECLARE num NUMBER;
BEGIN
proc1(num);  -- as output
proc2(num);  -- as input
END;

DECLARE num NUMBER;
BEGIN
proc1(num);
proc2(num);
END;

Note: Cursor return type is only supported in Oracle.

Sybase

SELECT * FROM tbl_name
GO

SELECT * FROM tbl_name

SELECT dbo.func_name()
GO

SELECT dbo.func_name()

SELECT dbo.func_name('str',1)
GO

SELECT dbo.func_name('str',1)

SELECT dbo.func_name(col1, col2)
FROM tbl_name
GO

SELECT dbo.func_name(col1, col2)
FROM tbl_name

EXECUTE proc_name
GO

EXECUTE proc_name 'str',1
GO

EXECUTE proc_name 'str',1

BEGIN proc_name 'str', 1 END
GO

BEGIN proc_name 'str', 1 END

DECLARE @num TINYINT
EXECUTE proc1 @num OUTPUT
EXECUTE proc2 @num IN

DECLARE @num TINYINT
EXECUTE proc1 @num OUTPUT
EXECUTE proc2 @num IN

SELECT * FROM tbl_name;

SELECT * FROM tbl_name

SELECT func_name FROM dual;

SELECT func_name FROM dual

SELECT func_name;

SELECT func_name

SELECT func_name('str', 1);

SELECT func_name('str', 1)

SELECT func_name(col1, col2)
FROM tbl_name;

SELECT func_name(col1, col2)
FROM tbl_name

CALL proc_name;

CALL proc_name('str',1);

CALL proc_name('str',1)

EXECUTE proc1(@num);  --as output
EXECUTE proc2(@num);  --as input

EXECUTE proc1(@num);
EXECUTE proc2(@num)

SELECT * FROM tbl_name

SELECT * FROM tbl_name

SELECT func_name
FROM SYSIBM.SYSDUMMY1

SELECT func_name
FROM SYSIBM.SYSDUMMY1

SELECT *
FROM TABLE(func_name)

SELECT *
FROM TABLE(func_name)

SELECT func_name('str', 1)
FROM SYSIBM.SYSDUMMY1

SELECT func_name('str', 1)
FROM SYSIBM.SYSDUMMY1

SELECT func_name(col1, col2)
FROM tbl_name

SELECT func_name(col1, col2)
FROM tbl_name

SELECT *
FROM TABLE(func_name('str', 1))

SELECT *
FROM TABLE(func_name('str', 1))

CALL proc_name

CALL proc_name

CALL proc_name('str',1)

CALL proc_name('str',1)

BEGIN
DECLARE num SMALLINT;
CALL proc1(num); -- as output
CALL proc2(num); -- as input
END

BEGIN
DECLARE num SMALLINT;
CALL proc1(num);
CALL proc2(num);
END

SELECT * FROM tbl_name;

SELECT * FROM tbl_name

SELECT func_name;

SELECT func_name

SELECT *
FROM func_name;

SELECT *
FROM func_name

SELECT func_name('str', 1);

SELECT func_name('str', 1)

SELECT *
FROM func_name('str', 1);

SELECT *
FROM func_name('str', 1)

SELECT func_name(col1, col2)
FROM tbl_name;

SELECT func_name(col1, col2)
FROM tbl_name

EXEC proc_name;

EXEC proc_name

EXEC proc_name 'str',1;

EXEC proc_name 'str',1

DECLARE @lastRw TINYINT;
EXEC get_max_ @lastRw OUTPUT;
EXEC insertNextRow @lastRw;

DECLARE @lastRw TINYINT
EXEC get_max_ @lastRw OUTPUT
EXEC insertNextRow @lastRw

SELECT * FROM tbl_name;

SELECT * FROM tbl_name

SELECT func_name();

SELECT func_name()

SELECT *
FROM func_name();

SELECT *
FROM func_name()

SELECT func_name('str', 1);

SELECT func_name('str', 1)

SELECT *
FROM func_name('str', 1);

SELECT *
FROM func_name('str', 1)

SELECT func_name(col1, col2)
FROM tbl_name;

SELECT func_name(col1, col2)
FROM tbl_name

SELECT func_name('str', 1);

SELECT func_name('str', 1)

SELECT *
FROM func_name('str', 1);

SELECT *
FROM func_name('str', 1)