Installation and Configuration Supplement

Using the DB2 Universal Database Server from Host or AS/400 Clients

Connections from host or AS/400 database clients are treated like any other connection to the DB2 Universal Database server to ensure consistency in the way the maximum number of concurrent connections to a server is measured, from host, AS/400, and Universal Database clients.

The Administration Guide summarizes the CCSIDs that a host or AS/400 database client must use to connect to a DB2 Universal Database server.

When using APPC, a host or AS/400 database client connects to the DB2 Universal Database server by specifying the appropriate Transaction Program Name (TPN) defined at the DB2 Universal Database server. The TPN can correspond to the tpname parameter value in the instance's database manager configuration file. The TPN used by the host or AS/400 database client can also be the service transaction program x'07'6DB. When using this TPN and if multiple DB2 Universal Database instances exist at the server, the instance which will process the x'07'6DB transaction program is specified by the DB2 registry value DB2SERVICETPINSTANCE. If only a single DB2 instance will be accessed by the host or AS/400 client there is no need to specify the DB2SERVICETPINSTANCE value.

Authentication

If you choose APPC as your communication protocol, the communications subsystem may restrict the types of database manager configuration authentication settings you can use at the DB2 Universal Database server. Not all communications subsystems expose the client's password to the DB2 Universal Database server when security (program) is used. When this is the case, the database manager configuration authentication must not be set to SERVER.

If you choose APPC as your communications protocol, you may be restricted in the types of database manager configuration authentication setting you can use at the DB2 Universal Database Server. If you have the SNA Syncpoint Manager configured, you can use any available authentication (SERVER, CLIENT, DCS).

To overcome the restriction that prevents you from using authentication SERVER, you can set the database manager authentication to DCS. This allows host or AS/400 database client connections that have been authenticated by the communication subsystem to succeed. However, with this setting the DB2 Universal Database will act as if authentication SERVER is being used for remote DB2 Universal Database client connections.

Troubleshooting

The DB2 DRDA Trace utility (db2drdat) is provided for tracing the data flow between a host or AS/400 database client and the DB2 Universal Database Server. For more information on setting up this trace, refer to the Troubleshooting Guide.

DRDA Functions Supported

DRDA functions are categorized as required or optional. Table 28 identifies which functions are implemented in the AS on the DB2 Universal Database server. The subsequent table lists the supported bind options.

Table 28. Supported DRDA Functions

Description	Required (R) Optional (O)	Supported
DRDA level 1 required function	R	Yes*
Rebind	O	Yes
Describe user privileges	O	No
Describe RDB table	O	No
Interrupt RDB request	O	No
Stored procedures return multipe row result sets	O	Yes

Note:

* Certain required functions are not supported.

Bind Options Supported by the DB2 DRDA Application Server

Table 29. Bind Options Supported by the DB2 DRDA Application Server

Bind Option	Value	Sup- ported	DB2 for MVS/ESA Precompile Option (Note 1)	DB2/VM Preprocessing Option	OS/400 Precompile Option	DB2 Prep or Bind Option
Package Version Name	Null	Yes	VERSION			VERSION
	Any other value	No
Bind Existence Checking	Object existence optional	No	VALIDATE (RUN)b	NOEXIST	GENLVL(10, 11-40)	VALIDATE RUN
	Object existence required	Yes	VALIDATE (BIND)b	EXIST	GENLVL(00-09)	VALIDATE BIND
Package Replacement Option	Replacement allowed	Yes	ACTION (REPLACE)	REPLACE	REPLACE(*YES)	ACTION REPLACE
	Replacement not allowed	No	ACTION(ADD)	NEW	REPLACE(*NO)	ACTION ADD
Package Authorization Option	Keep authorizations	Yes		KEEP		RETAIN YES
	Revoke authorizations	No		REVOKE		RETAIN NO
Statement String Delimiter (Note 2)	Apostrophe	Yes	APOSTSQL	SQLAPOST	OPTION([...] *APOSTSQL) (Note 3)	STRDEL APOSTROPHE
	Double quote	No	QUOTESQL	SQLQUOTE	OPTION([...] *QUOTESQL) (Note 4)	STRDEL QUOTE
Statement Decimal Delimiter (Note 5)	Period	Yes	PERIOD	PERIOD	OPTION([...] *PERIOD) or OPTION([...] *SYSVAL) (Note 6)	DECDEL PERIOD
	Comma	No	COMMA	COMMA	OPTION([...] *COMMA) or OPTION([...] *SYSVAL) (Note 6)	DECDEL COMMA
Date Format (Note 7)	ISO	Yes	DATE(ISO) (Note 8)	DATE(ISO)	DATFMT(*ISO) (Note 8)	DATETIME ISO (Note 9)
	USA	Yes	DATE(USA)	DATE(USA)	DATFMT(*USA)	DATETIME USA
	EUR	Yes	DATE(EUR)	DATE(EUR)	DATFMT(*EUR)	DATETIME EUR
	JIS	Yes	DATE(JIS)	DATE(JIS)	DATFMT(*JIS)	DATETIME JIS
Time Format (Note 7)	ISO	Yes	TIME(ISO) (Note 8)	TIME(ISO)	TIMFMT(*ISO) (Note 8)	DATETIME ISO (Note 9)
	USA	Yes	TIME(USA)	TIME(USA)	TIMFMT(*USA)	DATETIME USA
	EUR	Yes	TIME(EUR)	TIME(EUR)	TIMFMT(*EUR)	DATETIME EUR
	JIS	Yes	TIME(JIS)	TIME(JIS)	TIMFMT(*JIS)	DATETIME JIS
Package Isolation Level (Note 10)	Repeatable read	Yes	ISOLATION(RR)b	ISOLATION(RR)		ISOLATION RR
	Read Stability (All)	Yes		ISOLATION(RS)	COMMIT(*ALL)	ISOLATION RS
	Cursor stability	Yes	ISOLATION(CS)b	ISOLATION(CS)	COMMIT(*CS)	ISOLATION CS
	Uncommitted Read (Change)	Yes		ISOLATION(UR)	COMMIT(*CHG)	ISOLATION UR
	No commit	No (Note 11)			COMMIT(*NONE)	ISOLATION NC
Bind Creation Control	No errors allowed	Yes	SQLERROR (NOPACKAGE)b	NOCHECK	OPTION([...] *GEN) GENLVL(00-09, 10, 11-20)	SQLERROR NOPACKAGE
	Check only	Yes		CHECK	OPTION([...] *NOGEN)	SQLERROR CHECK
	Errors allowed	No	SQLERROR (CONTINUE)b	ERROR	OPTION([...] *GEN) GENLVL(21-40)	SQLERROR CONTINUE
Bind Explain Option	No SQL statements	Yes	EXPLAIN(NO)b	EXPLAIN(NO)		EXPLAIN NO
	All explainable SQL statements	No	EXPLAIN(YES)b	EXPLAIN(YES)		EXPLAIN YES
Package Owner Identifier	<Authorization ID>	Yes	OWNERb	OWNER		OWNER
	Any other value	No
RDB Release Option	Release at commit	Yes	RELEASE (COMMIT)b	RELEASE (COMMIT)		RELEASE COMMIT
	Release at conversation deallocation	No	RELEASE (DEALLOCATE)b	RELEASE (DEALLOCATE)		RELEASE DEALLOCATE
Default RDB Collection ID	<Authorization ID>	Yes	QUALIFIERb	QUALIFIER	DFTRDBCOL	QUALIFIER
	Any other value	No
Title (Package Description)	Any value (ignored by DB2)	Yes		LABEL	TEXT	TEXT
Query Block Protocol Control	Fixed row	Yes	CURRENTDATA (YES)b	SBLOCK	ALWBLK(*READ)	BLOCKING UNAMBIG
	Limited block	Yes	CURRENTDATA (NO)b	BLOCK	ALWBLK (*ALLREAD)	BLOCKING ALL
	Forced fixed row	Yes		NOBLOCK	ALWBLK(*NONE)	BLOCKING NO
Package Default Char. Subtype
	Use system default	Yes				CHARSUB DEFAULT
If Default CCSID is SBCS	BIT	No		CHARSUB(BIT)		CHARSUB BIT
If Default CCSID is SBCS	SBCS	Yes		CHARSUB(SBCS)		CHARSUB SBCS
If Default CCSID is SBCS	MBCS	No		CHARSUB(MBCS)		CHARSUB MBCS
If Default CCSID is MBCS	BIT	No		CHARSUB(BIT)		CHARSUB BIT
If Default CCSID is MBCS	SBCS	No		CHARSUB(SBCS)		CHARSUB SBCS
If Default CCSID is MBCS	MBCS	Yes		CHARSUB(MBCS)		CHARSUB MBCS
	Any other value	No
Package Default CCSID	Value specified when DB2 database was created	Yes		CCSIDSBCS() CCSIDGRAPHIC() CCSIDMIXED()		CCSIDS CCSIDG CCSIDM
	Any other value	No
Decimal Precision (Note 12)	31	Yes	DEC(31)			DEC 31
	Any other value	No	DEC(15)			DEC 15
Replaced Package Version Name	Null	Yes	REPLVERb			REPLVER
	Any other value	No
Generic Bind Option	Null	No				GENERIC
	Any other value	No
Package Authorization Rule	Requester	Yes				DYNAMICRULES RUN
	Owner	No				DYNAMICRULES BIND
	Creator of the user-defined function and stored procedure	No				DYNAMICRULES DEFINE
	Invoker of the user-defined function and stored procedure	No				DYNAMICRULES INVOKE
Degree of Parallelism	1	No				DEGREE 1
	n	No				DEGREE n
	ANY	No				DEGREE ANY
Note: (*) Default values are in bold. (1) Most are precompile options. Bind options are indicated by b. (2) Defaults to what the target database supports. For DB2 the default is apostrophe. (3) Default for non-COBOL applications. (4) Default for COBOL applications. (5) Defaults to what the target database supports. For DB2 the default is period. (6) Depending on the installation, *SYSVAL is equivalent to *PERIOD or *COMMA. (7) Date and time formats must be the same for the DB2 DRDA AS. (8) Default is dependent on the installation. (9) Format applies to both date and time. If not specified, it defaults based on the country code. This default is mapped to ISO in DRDA flow. (10) Package isolation level has no default because an explicit value is always present in the DRDA datastream. (11) The isolation level will be escalated to Uncommitted Read (Change). (12) Defaults to what the target database supports. For DB2 the default is 31. (13) All variables will default to 1.

Special Considerations for DB2 for VM (SQL/DS)

Additional steps are needed to ensure the following DB2 for VM utilities work properly when accessing a DB2 Universal Database server.

• SQLDBSU

  1. Make sure a PTF for DB2 for VM APAR PN69073 is installed on your DB2 for VM system, either PTF UN91171 or PTF UN91172. (At the time of publication of this manual, there were no PTFs for DB2 for VM V4 or V5.)

  2. Set up dummy tables in your DB2 database by executing the sqldbsu utility provided with DB2 as sqldbsu database_name.

  3. Bind SQLDBSU from DB2 for VM. Refer to the "Using a DRDA Environment" section in the SQL/DS System Administration for IBM VM Systems manual for details. (You can skip the step on creating and populating the SQLDBA.DBSOPTIONS table because this is done by the sqldbsu utility in the previous step.)

• ISQL

  1. Complete the steps described above for SQLDBSU.

  2. Set up dummy tables in your DB2 database by executing the isql utility provided with DB2 as isql database_name.

  3. Bind ISQL from DB2 for VM. Refer to the "Using a DRDA Environment" in the SQL/DS System Administration for IBM VM Systems manual for details.

Notes:

1. On UNIX workstations, the sqldbsu and isql utilities are in INSTHOME/sqllib/misc where INSTHOME represent the home directory of the instance owner.

2. On OS/2 and Windows, the sqldbsu and isql utilities are in DB2PATH\misc, for example:

      c:\SQLLIB\misc\
   

If you installed DB2 on drive C, using the default directory sqllib, then no special setup is required for RXSQL; refer to the SQL/DS Procedures Language Interface Installation manual for details.

Security and Auditability

Under APPC, DB2 Universal Database system security (authentication CLIENT, SERVER, or DCS) must be used with a APPC security SAME or PROGRAM. When these combinations are used, the user ID and password sent by the host or AS/400 is used to CONNECT to the requested database. APPC security level NONE is allowed only with DCE authentication. In this case, the encrypted DCE ticket is flown as part of the CONNECT attempt.

Under TCP/IP, all security information is flown in the CONNECT attempt.

User ID translation is not supported by DB2 Universal Database.

Configuration Considerations

Data access by host and AS/400 applications are tuned at the DB2 Universal Database server using the DB2 database manager configuration parameters. One parameter, DRDA Heap Size, is specifically for host and AS/400 database client connections. You may need to change the setting for some parameters because of the additional resources required for the DB2 Universal Database Server.

DRDA Heap Size (drda_heap_sz)

On UNIX workstations, DRDA heap size specifies the amount of memory, in pages, that is allocated for use by the DB2 Universal Database server for host and AS/400 connections.

ON OS/2 or Windows, DRDA heap size specifies the amount of memory, in segments, that is allocated for use by the DB2 Universal Database server for host and AS/400 connections.

Refer to the Administration Guide for more information about database manager configuration.

[ DB2 List of Books | Search the DB2 Books ]