SQL Toolkit configuration

Introduction Copied

The SQL Toolkit plugin executes database queries that can run and display the output of the query in the dataview.

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

For more information, see SQL Toolkit.

This is a sample query that is used in SQL Toolkit plugin:

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

Note

There is no need for a semicolon at the end of the query.

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.

Note

The order of the rows published in the dataview may not always adhere to the order returned by the query (for example, if “ORDER BY” is used in the query). This is due to performance considerations.

Plugin Configuration Copied

queries Copied

Holds a set of queries which user wants the plug-in to use to query the database.

To view the list of data types that SQL Toolkit plugin does not support, see Manage unsupported data types in SQL Toolkit.

Mandatory: Yes

queries > query Copied

Holds a query name and query sql code for a specific query

Mandatory: Yes

queries > query > name Copied

Describes the name by which this query is known.

For example, Query1

Mandatory: Yes

queries > query > sql Copied

Holds the SQL used to run the query.

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).

For example: select * from my_table

Mandatory: Yes

queries > query > rowLimit Copied

Limits the amount of rows to be returned by a query.

Note

Netprobe is capable of handling multiple rows. However, this can cause system failure for the Netprobe to provide correct sample.

Mandatory: No

Default: 200 rows

connection Copied

Connection defines all of the settings that need to be passed to the database.

Note

The Netprobe has a default of 10
maximum database connections. This number is configurable in the GSE under the Advanced tab of Probes with the field Max Database Connections.

Connect to a SQL Server database Copied

connection > database Copied

The specific database that should be connected to. The supported databases are currently:

connection > database > mysql > var-serverName Copied

Connect to a Sybase database Copied

The name of the MySQL server.

Mandatory: Yes

connection > database > mysql > var-port Copied

The port number that MySQL is listening on.

Mandatory: No

Default: 3306

connection > database > mysql > var-databaseName Copied

The name of the database to use when running the query.

Mandatory: Yes

connection > database > oracle > var-databaseName Copied

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:

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

connection > database > oracle > var-applicationName Copied

The application name to be set to the connection created from the sampler to the Database Server.

Mandatory: No

Default: NetProbe (listen-port <Netprobe's port>)

connection > database > sybase > var-interfaceEntry Copied

The name of the Sybase interface. This is specified in the sql.ini or interfaces file.

Mandatory: Yes

connection > database > sybase > var-databaseName Copied

The name of the database to use when running the query.

Mandatory: Yes

connection > database > sybase > var-applicationName Copied

The application name to be set to the connection created from the sampler to the Database Server.

Mandatory: No

Default: NetProbe (listen-port <Netprobe's port>)

connection > database > sqlServer > var-serverName Copied

The name of the SQL Server.

Note

When connecting to named instances the format should be “ServerName\InstanceName”. On connection Geneos will verify the named instance actually matches the one listening on the specified port.

Mandatory: Yes

connection > database > sqlServer > var-port Copied

The port number that MySQL is listening on.

Mandatory: No

Default: 3306

connection > database > sqlServer > var-applicationName Copied

The application name to be set to the connection created from the sampler to the Database Server.

Mandatory: No

Default: NetProbe (listen-port <Netprobe's port>)

Note

This option is only valid for Windows platforms.

connection > database > sqlServer > var-databaseName Copied

The name of the database to use when running the query if the database name is not specified in the query. If the query contains a valid database name, this value will be ignored.

Mandatory: No

connection > database > sqlServer > useWindowsAuthentication Copied

Note: Beginning Geneos 5.6.x, this plugin supports Windows authentication for Linux platforms. For more information, see Active Directory: Using Kerberos Keytabs to integrate non-Windows systems.

Note: Beginning Geneos 5.6.x, this plugin supports Windows authentication for Linux platforms. For more information, see Active Directory: Using Kerberos Keytabs to integrate non-Windows systems.

Login using Windows authentication instead of SQL authentication.

The credentials used for Windows authentication are the username and password provided in the fields. If the username and password fields are left blank, then the currently logged-on user executing the probe will be used.

You can specify a domain by entering the username using the UPN format or the down-level logon format. If invalid Windows credentials are supplied, then the sampler will fall back to SQL authentication.

Mandatory: No

Default: false

connection > database > db2 > var-databaseName Copied

The alias of the database to use when running the query.

Mandatory: Yes

connection > database > PostgreSQL > var-serverName Copied

The name of the PostgreSQL server.

Mandatory: Yes

connection > database > PostgreSQL > var-databaseName Copied

The name of the PostgreSQL database to use when running the query.

Mandatory: Yes

connection > database > PostgreSQL > var-port Copied

The port number of the PostgreSQL server.

Mandatory: Yes

connection > database > PostgreSQL > var-applicationName Copied

The application name to be set to the connection created from the sampler to the Database Server.

Mandatory: No

Default: NetProbe (listen-port <Netprobe's port>)

connection > var-userName Copied

The name of the user with which to connect to the database.

Mandatory: No

connection > password Copied

The password with which to connect to the database. To set an encrypted password, click on the “Set Password” button, then enter and confirm the password to be used.

Mandatory: No

connection > closeConnectionAfterQuery Copied

If set to true the sampler will close the connection to the database after each query. This frees resources for other processes that may connect to the database but makes the sampler a little less efficient. If false the connection remains open but is returned to a pool of connections for reuse within the netprobe.

When using a large sample interval (e.g. running queries every hour), setting this parameter to true will reduce database resources without significantly impacting Netprobe performance.

Mandatory: No

Default: false

userDetails Copied

This setting has been replaced by Technical Reference: connection.

Deprecated: Yes

Sample dataview Copied

Sample dataview

Advanced tab Copied

Advanced tab

QUERY DB2 Netprobe
Simple SELECT query
SELECT * FROM tbl_name
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name
FROM SYSIBM.SYSDUMMY1
SELECT func_name
FROM SYSIBM.SYSDUMMY1
SELECT *
FROM TABLE(func_name)
SELECT *
FROM TABLE(func_name)
Function call with input parameters
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))
Procedure call without parameters
CALL proc_name
CALL proc_name
Procedure call with input parameters
CALL proc_name('str',1)
CALL proc_name('str',1)
Procedure call with output parameter
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

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 Copied

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

Mandatory: No

Default: false

Kerberos Copied

Note: Beginning Geneos 5.6.x, this plugin supports Windows authentication for Linux platforms. For more information, see Active Directory: Using Kerberos Keytabs to integrate non-Windows systems.

Additional steps for Windows Netprobe service Copied

If you are running the Netprobe as a service on Windows, then you need to perform some additional configuration on the system for Kerberos authentication to work:

  1. Open the Services app. To find it, press ⊞ Win and type services.
  2. In the Services app, look for NetprobeNT_64, or the service name you assigned to the Netprobe.
  3. Right-click the Netprobe service and select Properties.
  4. In the Properties window, select the Log On tab.
  5. Under This account: enter the Kerberos credentials, then click OK:

Windows Netprobe service log on properties

  1. Restart the Netprobe service by right-clicking the service and selecting Restart.

Enabling diagnostics Copied

To help track down problems, the printing of debug statements can be enabled by adding a debug setting to the sampler set-up called SQL-TOOLKIT. This results in the plugin printing statements each time it attempts something of significance e.g. attempting to open a connection to a database, being unable to connect to a database, etc.

Configuring the Netprobe environment 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 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 Copied

Note

For MySQL 8.0, only mysql_native_password authentication is supported. The caching_sha2_password option is not supported.

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%

IBM AIX


setenv LD_LIBRARY_PATH /usr/local/mysql

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 Copied

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 Copied

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 Copied

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 Copied

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

Others Copied

Microsoft.VC80.CRT redistributables are required when using AES-256 passwords in Windows machines.

Appendix A - MS SQL Server Database Setup Copied

Required Client Libraries Copied

The Linux netprobe requires an Open Database Connectivity (ODBC) driver for accessing an SQL Server database. This driver serves as a translation layer between the application and the database management system (DBMS) that allows the netprobe to access data via queries sent by the driver to the database.

Below is a list of the libraries that can be used by the netprobe to connect to an SQL Server database:

Library Filename Description Source
Microsoft ODBC Driver for SQL Server on Linux Microsoft ODBC Driver 13 for SQL Server Native library used for 64-bit Linux probes https://docs.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server

Note

Microsoft ODBC Driver 17 for SQL Server is also supported.

Installation of ODBC Drivers Copied

The Linux Netprobe uses the MS ODBC driver to connect to an SQL Server database.

Installing Microsoft ODBC Driver for SQL Server Copied

The MS ODBC driver allows native applications running on Linux to connect to SQL Server databases.

Currently, the 64-bit Linux Netprobe supports MS SQL Server via the MS ODBC driver.

  1. Install MS SQL Server from the Microsoft site on Windows or Linux. For fresh installations, select MS SQL Server as the default installation (i.e. do not give it any instance name). In case you are using MS SQL Server 2008 Express, there is a bug in it where selecting the Default Instance box in the installation does not work. By entering MSSQLSERVER as the instance name the installer will install SQL Server with the default (i.e. none) instance name.
  2. Download and install the latest version of Gateway on your Linux box from the ITRS Download website as usual.
  3. For MS SQL Server database connection from a Linux host, you need a separate database library. You need the unixODBC driver manager and the MS SQL ODBC Driver for SQL Server. At the time of writing the SQL Server driver is “MS SQL ODBC Driver 13 for SQL Server”.

Installing the Microsoft ODBC DRIVER For SQL SERVER Copied

With the release of Microsoft’s ODBC Driver 13.0 for Linux, Microsoft have made the installation and configuration of their driver much easier by supporting Linux package management tools.

  1. First follow the instructions for your platform from Microsoft’s documentation.
  2. To verify that the ODBC Driver on Linux was registered successfully, execute the following command:
odbcinst -q -d -n "ODBC Driver 13 for SQL Server"

Change the quoted string to match your driver version. It should show you output such as:

[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
  1. Copy or symlink /etc/odbcinst.ini (having above contents) to /opt/microsoft/msodbcsql/lib64 folder.
sudo ln -s /etc/odbcinst.ini /opt/microsoft/msodbcsql/lib64/odbcinst.ini

Setting up the environment before starting Gateway/Netprobe Copied

  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.
  1. 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”.

Note

When running SQL-Toolkit on Windows
and connecting to a SQL Server instance using the Windows Authentication mode, you should not need to provide any explicit credentials in the database setup. If run as a service the netprobe must be either running as a user with permission to connect to the SQL Server instance, or credentials are provided in the sql-toolkit sampler set-up.

Note

Connecting to a dynamic MS SQL instance is not supported by the Microsoft ODBC driver. A named instance must be referenced by a port number. See the Microsoft documentation for more information.

Sample Queries Copied

Oracle Copied

QUERY ISQL Netprobe
Simple SELECT query
SELECT * FROM tbl_name;
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name;
SELECT func_name
SELECT *
FROM func_name;
SELECT *
FROM func_name
Function call with input parameters
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
Procedure call without parameters
EXEC proc_name;
EXEC proc_name
Procedure call with input parameters
EXEC proc_name 'str',1;
EXEC proc_name 'str',1
Procedure call with output parameter
DECLARE @lastRw TINYINT;
EXEC get_max_ @lastRw OUTPUT;
EXEC insertNextRow @lastRw;
DECLARE @lastRw TINYINT
EXEC get_max_ @lastRw OUTPUT
EXEC insertNextRow @lastRw

Note

Cursor return type is only supported in Oracle.

Sybase Copied

QUERY ISQL Netprobe
Simple SELECT query
SELECT * FROM tbl_name;
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name();
SELECT func_name()
SELECT *
FROM func_name();
SELECT *
FROM func_name()
Function call with input parameters
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
Function call with output parameter

Note: Output parameters are not included in the function call.

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

MySQL Copied

QUERY MYSQL Netprobe
Simple SELECT query
SELECT * FROM tbl_name;
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name FROM dual;
SELECT func_name FROM dual
SELECT func_name;
SELECT func_name

Note

Function calls without table reference can be called with or without 'FROM dual'

Function call with input parameters
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
Procedure call without parameters
CALL proc_name;
CALL proc_name

Note

If a procudure is returning multiple resultsets, only the first resultset will be displayed in the dataview.

Procedure call with input parameters
CALL proc_name('str',1);
CALL proc_name('str',1)
Procedure call with output parameter
EXECUTE proc1(@num);  --as output
EXECUTE proc2(@num);  --as input
EXECUTE proc1(@num);
EXECUTE proc2(@num)

DB2 Copied

QUERY DB2 Netprobe
Simple SELECT query
SELECT * FROM tbl_name
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name
FROM SYSIBM.SYSDUMMY1
SELECT func_name
FROM SYSIBM.SYSDUMMY1
SELECT *
FROM TABLE(func_name)
SELECT *
FROM TABLE(func_name)
Function call with input parameters
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))
Procedure call without parameters
CALL proc_name
CALL proc_name
Procedure call with input parameters
CALL proc_name('str',1)
CALL proc_name('str',1)
Procedure call with output parameter
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

MS SQL Server Copied

QUERY ISQL Netprobe
Simple SELECT query
SELECT * FROM tbl_name;
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name;
SELECT func_name
SELECT *
FROM func_name;
SELECT *
FROM func_name
Function call with input parameters
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
Procedure call without parameters
EXEC proc_name;
EXEC proc_name
Procedure call with input parameters
EXEC proc_name 'str',1;
EXEC proc_name 'str',1
Procedure call with output parameter
DECLARE @lastRw TINYINT;
EXEC get_max_ @lastRw OUTPUT;
EXEC insertNextRow @lastRw;
DECLARE @lastRw TINYINT
EXEC get_max_ @lastRw OUTPUT
EXEC insertNextRow @lastRw

Note

When calling functions/procedures in MS SQL Server, [EXEC ] can be omitted or replaced with [EXECECUTE ].

PostgreSQL Copied

QUERY ISQL Netprobe
Simple SELECT query
SELECT * FROM tbl_name;
SELECT * FROM tbl_name
Function call without parameters
SELECT func_name();
SELECT func_name()
SELECT *
FROM func_name();
SELECT *
FROM func_name()
Function call with input parameters
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
Function call with output parameter

Note: Output parameters are not included in the function call.

SELECT func_name('str', 1);
SELECT func_name('str', 1)
SELECT *
FROM func_name('str', 1);
SELECT *
FROM func_name('str', 1)
["Geneos"] ["Geneos > Netprobe"] ["User Guide"]

Was this topic helpful?