How to Check If a Virtual DataPort Server Is Alive

When you connect to Denodo through a load balancer or an application that creates a pool of connections to Virtual DataPort, the load balancer or the application need a way to check that a connection is still valid. The following sections explain different alternatives to check the health of a connection.

Connecting from a JDBC Client Through a Load Balancer

Follow these steps when connecting from a JDBC client to Virtual DataPort through a load balancer or another intermediate resource that holds a pool of connections to Virtual DataPort:

  1. Add the parameter reuseRegistrySocket=false to the URL of the JDBC client.

  2. If the load balancer or the client will execute a ping query without support to set a timeout for that query, add the parameters pingQuery and pingQueryTimeout to the URL of the JDBC client.

The section Connecting to Virtual DataPort Through a Load Balancer of the Developer Guide explains how to use these parameters.

Using the Ping Script

The script ping checks that a Virtual DataPort server is “alive”. It is located in the directory <DENODO_HOME>/bin.

Syntax of the ping script
ping [ -t timeout ] [ -q <query> -l <login> [ -p <password> | -cf <configuration file> ] ] [ -v ]
     <host>[:<port>][/<database name>
Parameters of the ping script

Parameter

Description

-t

Optional. Time to wait for a response, in milliseconds. If after this period the script does not receive a response, it returns an error.

-q <query>

Optional. If present, the script, besides checking if the Virtual DataPort server is up, it executes a query. If you indicate this parameter, you also have to pass the login and password of the user. In many operating systems, you have to surround the query with double quotes so the query is considered a single parameter.

-l

Mandatory when using -q. Login.

-p

Password. Incompatible with -cf. You can provide the password encrypted by prefixing it with encrypted: (use the script encrypt_password to encrypt the password). E.g. -p "encrypted:UjOsIu8972jviqGpcLP3Mg=="

If you provide the login (-l) but not the password (-p) nor the configuration file (-cf), the script will prompt for your password.

If you are going to invoke this from another script of application, we recommend encrypting the password; to to avoid putting in plain text.

-cf <file>

Incompatible with -p. File with one line that contains this:

password=<password>

or this:

password=encrypted:<encrypted_password> (Use the script encrypt_password to encrypt the password)

-v

Optional. If present, the script displays the status the time taken to receive a response from the Server. Otherwise, it only displays error messages.

<host>

Host of Virtual DataPort.

<port>

Optional. If not present, the script sends the request to the default Virtual DataPort port: 9999.

<database>

Mandatory when using -q. The database that the query will be executed.

This script returns 1 if there is any error (e.g. the server cannot be reached or the query fails or the timeout is reached…). If the script finishes correctly, it returns 0.

Examples

Example 1

For Windows:

<DENODO_HOME>\bin\ping.bat -v -t 30000 -q "SELECT 1" -l monitor_user -p "encrypted:UjOsIu8972jviqGpcLP3Mg==" //denodo-dv1.acme.com/admin

For Linux:

<DENODO_HOME>/bin/ping.sh -v -t 30000 -q "SELECT 1" -l monitor_user -p "encrypted:UjOsIu8972jviqGpcLP3Mg==" //denodo-dv1.acme.com/admin

This sends a ping request to “denodo-dv1.acme.com”:

  • If the server responds, it executes the query “SELECT 1” on the database “admin”. Note that the query (-q parameter) needs to be surrounded by double quotes so the query is considered a single parameter.

  • If connecting to Virtual DataPort and running the query takes more than 30 seconds, the script fails.

  • Note that the password (parameter -p) starts with encrypted:. That is because what follows is the password encrypted.

To encrypt the password of a user account, execute this:

On Windows:

<DENODO_HOME>\bin\encrypt_password.bat

On Linux:

<DENODO_HOME>/bin/encrypt_password.sh

Enter your password and press Enter; the script will return your password encrypted. You can also pass your password as a parameter of this script.

Example 2

ping -t 30000 -v //denodo-dv1.acme.com:19999

Sends a ping request to a Virtual DataPort that listens on the port 19999, with a timeout of 30 seconds. Unlike in the previous example, this one does not execute any query. That is why you do not need to specify the user and password.

Alternatives to the Ping Script

In an environment where the load balancer cannot run a ping query, nor use the ping script, you can develop a script that sends an HTTP request to Virtual DataPort through the RESTful Web service.

It is important for the queried view to be very lightweight. We suggest creating this view with this statement:

CREATE OR REPLACE VIEW ping_query_view AS
SELECT 1
FROM Dual();

When this view is queried, it only invokes the internal stored procedure Dual and does not involve any data source.

Then, develop a script that access the URL http://denodo-dv1.acme.com:9090/denodo-restfulws/admin/views/ping_query_view

This URL will return the HTTP code 200 if Virtual DataPort server is alive.