-30082 SQL ERROR when ZParm TCPALVER set to NO for Database Management Solutions
search cancel

-30082 SQL ERROR when ZParm TCPALVER set to NO for Database Management Solutions


Article ID: 237355


Updated On:


RC/Migrator for DB2 for z/OS RC/Query for DB2 for z/OS RC/Update for DB2 for z/OS RC/Merger for DB2 for z/OS


After changing ZParm TCPALVER from YES to NO as recommended by IBM, users of the Database Management Admin tools were impacted when trying to access remote subsystems.
For example a Compare between two subsystems failed with:

 DSNT418I SQLSTATE   = 08001 SQLSTATE RETURN CODE                               
 DSNT416I SQLERRD    = 9  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION             
 DSNT416I SQLERRD    = X'00000009'  X'00000000'  X'00000000'                    
          X'FFFFFFFF'  X'00000000'  X'00000000' SQL DIAGNOSTIC                  

After changing TCPALVER back to YES these functions worked again.


The Db2 Packages bound during the install of the Broadcom Db2 Tools all utilize DBPROTOCOL(DRDA).  When SQL is utilized for remote location a 3-part name is generated in the form of
“location.schema.name” where the “location” is defined in the Db2 Communications Database (CDB) of the involved Db2 subsystems.  When remote SQL is processed Db2 establishes a connection via
DRDA to the remote subsystem utilizing the security requirements defined in the customer CDB for the remote and target subsystems.

When the ZParm TCPALVER is set to “YES” it indicates to Db2 that the application wanting to connect via TCP/IP is ALready VERified.  In that instance, Db2 will accept a connection request that
provides an ID but no authentication credential.  No password, PassTicket, or client certificate is required.

When TCPALVER is set to “NO” the connection request will fail unless the Db2 CDB is configured in both subsystems to provide authentication credentials.  This can be done in a number of ways as
defined in the Db2 documentation and is specific to the customer needs and environment.

The Broadcom Db2 Tools are dependent on the customer CDB’s being configured with credentials to operate properly when TCPALVER is set to “NO”.  The same applies to any other applications
running under Db2 that require remote access.

Additional Information

Below is a link to an IBM Redbook which further describes the use of TCPALVER and the types of authentication credentials that may be defined.

DB2 9 for z/OS: Distributed Functions

While this Redbook was initially published for Db2 v9 it has very good overview and description of the security requirements.  The most recent Db2 v12 SQL Reference has additional details in the
appendix for the system catalog with specific focus on the SECURITY_OUT column of the SYSIBM.IPNAMES table which defines the DRDA security option to be used for remote requests.