Troubleshooting DB2z connection problems.

[David_Fergen]

Replicate is unable to connect to the DB2 database on z/OS. Testing the DB2z Endpoint is failing with an error similar to the followings

SYS-E-HTTPFAIL, Cannot connect to DB2 zOS Server.
SYS,GENERAL_EXCEPTION,Cannot connect to DB2 zOS Server,RetCode: SQL_ERROR SqlState: 08001 NativeError: -30081 Message: [IBM][CLI Driver] SQL30081N ...

Testing DB2z connection on Windows:

• Locate the IBM\SQLLIB\BIN folder, this should contain the db2cli.exe utility.

By default the installation directory is:
C:\Program Files\IBM\SQLLIB\BIN

• Issue the following command replacing the parameters with the actual values:

> db2cli validate -database <location>:<host>:<port> -connect -user <username> -passwd <pwd> -displaylic​

For example:

A machine with the IBM Data Server Driver ODBC/CLI
Location of the client is C:\Clients\DB2zODBC
> cd C:\Clients\DB2zODBC\bin
> db2cli validate -database myloc:myzos:5035 -connect -user user1 -passwd pwd1 -displaylic

Sample output:

Client Package Type       : IBM Data Server Driver For ODBC and CLI
Client Version (level/bit): DB2 v11.1.0.1527 (s1606081100/64-bit)
Client Platform           : NT 64BIT
Install/Instance Path     : C:\Clients\DB2zODBC

Common App Data Path      : C:\ProgramData\IBM\DB2\C_Clients_DB2zODBC
DB2DSDRIVER_CFG_PATH value: <not-set>
db2dsdriver.cfg Path      : <Common App Data Path>\cfg\db2dsdriver.cfg
DB2CLIINIPATH value       : <not-set>
db2cli.ini Path           : <Common App Data Path>\cfg\db2cli.ini
db2diag.log Path          : <Common App Data Path>\db2diag.log

...
===============================================================================
Connection attempt for database "DALLASB:zos5:5035":
===============================================================================
[SUCCESS]
===============================================================================
Valid License Found            : Yes
License location               : Server
Server routine type attempted  : SP
DB2 connect license version    : 1101
===============================================================================
The validation is completed.
===============================================================================

Note that this validation will also list two important configuration files for the DB2 Client. The location of the db2cli.ini and db2dsdriver.cfg, these files should be investigated for any parameters that could affect the connection. This is especially important when using a DSN and not a connection string via Replicate.

• Simulating an ODBC connection similar to the Replicate's Test Connection. This is accomplished using the native db2cli connection executed from the same location in the db2 driver bin folder.

First create a text file containing the following query:

> echo select 1 from sysibm.sysdummy1 >>insql.sql​

Run the db2cli as follows:

> db2cli execsql -connstring "HOSTNAME=myzos;SERVICENAME=5035;DATABASE=myloc;CURSORHOLD=1;DESCRIBEPARAM=1;CLIPatch2=15;CONCURRENTACCESSRESOLUTION=1;PROGRAMNAME=R4ZSCDC174FB;UID=user1;PWD=pw1" -inputsql insql.sql​

Sample output:

IBM DATABASE 2 Interactive CLI Sample Program
(C) COPYRIGHT International Business Machines Corp. 1993,1996
All Rights Reserved
Licensed Materials - Property of IBM
US Government Users Restricted Rights - Use, duplication or
disclosure restricted by GSA ADP Schedule Contract with IBM Corp.
> select 1 from sysibm.sysdummy1
FetchAll:  Columns: 1
  1
  1
FetchAll: 1 rows fetched.
>
 

Testing the connection using DB2CLI on Linux:

The steps on Linux are very similar to the Windows, the differences may be:

• In the shell maybe be able to execute db2cli, if not look for the the installation folder.

At some systems it's possible to find it by checking the value of an environment variable:

$ echo $DB2_CLI_DRIVER_INSTALL_PATH
/opt/db2_odbc/clidriver
Create the insql.sql as follows:
$ cd /opt/db2_odbc/clidriver
$ cat select 1 from sysibm.sysdummy1 >insql.sql