Troubleshooting DB2z connection problems.

David_Fergen — Fri, 28 Aug 2020 10:04:01 GMT

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

article Troubleshooting DB2z connection problems. in Official Support Articles

Troubleshooting DB2z connection problems.