-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

book

Article ID: 237355

calendar_today

Updated On:

Products

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

Issue/Introduction

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:

 DSNT408I SQLCODE = -30082, ERROR:  CONNECTION FAILED FOR SECURITY              
          REASON 17 (UNSUPPORTED FUNCTION) 
 DSNT418I SQLSTATE   = 08001 SQLSTATE RETURN CODE                               
 DSNT415I SQLERRP    = DSNLTAS1 SQL PROCEDURE DETECTING ERROR                   
 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                  
          INFORMATION     

After changing TCPALVER back to YES these functions worked again.

Resolution

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.