Stelo Technical Documents

Network Troubleshooting Tips

Last Update: 2023/10/17
Product: SQDR Plus and SQDR
Version: All
Article ID: SQV00PL081

Objective

This technical document describes various techniques to troubleshoot network connectivity issues, and is applicable to all products.

The discussion in this topic is geared towards connecting to Db2 databases from SQDR, but the same tactics can be used when connecting to other databases and streaming services.

Solution

Gather some basic information:

• Is this a new deployment or something that has been working but suddenly stops functioning?
• Verify that you have the correct hostname, port and RDB (Database name). If you are using a hostname rather than an IP address, verify that the name resolves.
• Verify that Db2 is running.

Tools

• ping
• Test-NetConnection (Windows) or ncat (Linux)
• Telnet
• ODBC Administrator (Windows)
• odbctest (Windows)
• isql
• simpleconn (ODBC)
• simpleconn (JDBC)
• jping (IBM Db2 for i only)
• Tracing
• Using ping to test for a Black Hole Router (uncommon)
  

ping

ping is often not useful, as it may be blocked by firewalls and does not prove connectivity to the database; it is common for pings to fail while access to the database succeeds. It is better to test the actual port being used by Db2 (or other database or service).

Test-NetConnection and ncat

Windows: Use the PowerShell command:

PS> Test-NetConnection -ComputerName <host> -Port <port>

Linux: Test-NetConnection is not available for PowerShell on Linux; however, ncat (part of nmap package) provides a similar function:

$ ncat -vz host port

ncat also includes parameters for SSL connections.

Telnet

Using telnet is useful, since you can specify the port. You can expect the connection to persist, even though no communication occurs.

Windows: You can use any the following

• PuTTY. Specify the host & post, and choose Connection type: telnet or raw.
  
• telnet cmdlet of PowerShell
• Windows-supplied telnet

To install the Windows-supplied telnet:

Windows 10:

• Open the Programs and Features control panel.
  
• In the left column, select Turn Windows features on or off.
  
• Select Telnet Client

Windows Server:

• Open the Programs and Features control panel.
• In the left column, select Turn Windows features on or off.
  or
  In Server Manager, select Add Roles and Features from the Manage menu
  
• Continue through the Wizard until you reach Features
  
• Select Telnet Client
  

Linux:

You can use any the following

• telnet cmdlet of PowerShell:

Enter pwsh to enter the PowerShell environment, or supply the telnet command as follows:
$ pwsh -Command "telnet host port"

• telnet application:

If telnet is not installed, you may install it with yum install telnet. If yum install fails because of a repo that can't be contacted, you can instruct yum to skip that repo:

e.g.
# yum install telnet --disablerepo=pgdg10

Usage:

$ telnet host (or IP) port

A successful connection will look something like this:
PS /home/myuser> telnet myhost 446
Trying 179.19.36.65...
Connected to myhost.
Escape character is '^]'.

ODBC Administrator (Windows)

If you are using an ODBC data source (DSN), the setup dialog for many ODBC drivers include a Test button. Stelo recommends using a connection string rather than a DSN for use with SQDR, but you can also create a DSN if necessary for testing.

odbctest (Windows)

odbctest is a Microsoft test application that was part of the original ODBC SDK.

Using a DSN:

• Select Conn/Full Connect...
  
• Select the DSN
  
• Enter credentials

To use a connection string:

The connection string will be similar to what you use in SQDR, with the addition of the driver name.

1. Allocate an environment handle:
   Env/SQLAllocHandle (i.e. select SQLAllocHandle from the Env menu)
   Handle type SQL_HANDLE_ENV
2. Set an attribute (ODBC_V3) on that handle:
   Attr/SQLSetEnvAttr
   select the Env handle from the dropdown
3. Create a connection handle (using the environment handle):
   Env/SQLAllocHandle
   Handle type SQL_HANDLE_DBC
4. Call Conn/SQLDriverConnect using connection handle and supplying the connection string e.g.

DRIVER=StarSQL (64-bit);Server=MYRDB;HostName=myhost;port=myport;UID=myuser;PWD=mypwd;PkgColID=STARSQL

If the goal is to verify connectivity to the database, you can use bogus values for user & password, since receiving an invalid user or password error is sufficient to show connectivity.

If you get an error, select the Err button (or Diag/Errors All) to display the error message.

5. After that succeeds, create a statement handle using the connection handle:
   Env/SQLAllocHandle
   Handle type SQL_HANDLE_STMT

You are now in the state you would be after doing a “Conn/Full Connect“ with a DSN and can enter SQL statements.

isql

isql is a command line ODBC query tool and a standard part of the unixODBC package. It is also available for Windows from Stelo support. You can use either an ODBC data source or a connection string.

See Testing with isql and iusql for details.

Example:

$ isql -v -k "DRIVER=StarSQL (64-bit);Server=MYRDB;HostName=myhost;port=myport;UID=myuid;PWD=mypwd;PkgColID=STARSQL"

If the goal is to verify connectivity to the database, you can use bogus values for user & password, since receiving an invalid user or password error is sufficient to show connectivity.

Using isql with other ODBC drivers:

You can use a connection string as documented in the Data Replicator Manager help file. You will need to know the name of the ODBC driver; this information can be found in or the registry key HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBCINST.INI (Windows).or the file /etc/odbcinst.ini (Linux).

Here is an example using the IBM DB2 ODBC driver:

$ isql -v -k "DRIVER=IBM DB2 ODBC DRIVER;Database=MYRDB;HostName=myhost;port=myport;UID=xxx;PWD=xxx;Authentication=SERVER"

simpleconn (ODBC)

Installed with StarSQL/ODBC (Windows or Linux). It uses an ODBC DSN.

simpleconn (JDBC)

Installed with StarSQL for Java. It uses a JDBC URL; this information can often be obtained from the configuration for a Stelo Capture (SQDR Plus) agent.

jping (Db2 for i host)

Stelo Capture (SQDR Plus) includes the jping utility for verifying that the host servers on an IBM Db2 for i system are running and reachable. The host servers are used by used by SQDR Capture via Java Toolbox aka JTOpen, and optionally by SQDR Apply when using the i Access ODBC driver.

See Verifying Connectivity to Host Servers on Db2 for i Source System.

Tracing

The following is a list of tracing tools that might be of assistance; details are beyond the scope of this document:

• traceroute (Windows: tracert)
• Network traffic capture tools such as Wireshark
• DRDA tracing (StarSQL)
  
• DRDA & Debug tracing using StarPipes
  
• SQDR bundled drivers include an option to enable tracing, using the Tracing tab in ODBC Administrator (Windows) or by modifying the [ODBC] section in odbc.ini (Linux), adding parameters for Trace=1; TraceFile; TraceDll, TraceOptions, etc. These drivers can also use Progress Data Direct's Snoop, a Java-based utility used to log network packets between a database and driver.
• ddcstrc & db2trc (IBM DB2 ODBC driver)
  
• CWBCOTRC (IBM i Access)
• TRCCNN (IBM i)
  

Using ping to test for a Black Hole Router

If you can successfully connect to the host, but the connection times out when fetching or sending large amount of data, you may be encountering a black hole router:

See Network problems due to Black Hole Router.