Quick Beginnings for DB2 Universal Database for UNIX

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 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.

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 program is specified by the different local LU name. The instance that handles the TPN x'07'6DB program is specified by the DB2 registry value DB2SERVICETPINSTANCE.

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 application requester's password to the application server when security (program) is used. When this is the case, the database manager configuration authentication must not be set to SERVER.

The following communications subsystems have this restriction:

AIX

Bull SNA

Solaris

all

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 32 identifies which functions are implemented in the AS on the DB2 Universal Database server. The subsequent table lists the supported bind options.

Table 32. Supported DRDA Functions

Description	Required (R) Optional (O)	Supported	Not Supported
DRDA level 1 required function	R	X*
Rebind	O	X
Describe user privileges	O		X
Describe RDB table	O		X
Interrupt RDB request	O		X

Note:

* Certain required functions are not supported.

Bind Options Supported by the DB2 DRDA Application Server

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

Bind Option	Value	Sup- ported	Not 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	X		VERSION			VERSION
	Any other value		X
Bind Existence Checking	Object existence optional		X	VALIDATE (RUN)b	NOEXIST	GENLVL(10, 11-40)	VALIDATE RUN
	Object existence required	X		VALIDATE (BIND)b	EXIST	GENLVL(00-09)	VALIDATE BIND
Package Replacement Option	Replacement allowed	X		ACTION (REPLACE)	REPLACE	REPLACE(*YES)	ACTION REPLACE
	Replacement not allowed		X	ACTION(ADD)	NEW	REPLACE(*NO)	ACTION ADD
Package Authorization Option	Keep authorizations	X			KEEP		RETAIN YES
	Revoke authorizations		X		REVOKE		RETAIN NO
Statement String Delimiter (Note 2)	Apostrophe	X		APOSTSQL	SQLAPOST	OPTION([...] *APOSTSQL) (Note 3)	STRDEL APOSTROPHE
	Double quote		X	QUOTESQL	SQLQUOTE	OPTION([...] *QUOTESQL) (Note 4)	STRDEL QUOTE
Statement Decimal Delimiter (Note 5)	Period	X		PERIOD	PERIOD	OPTION([...] *PERIOD) or OPTION([...] *SYSVAL) (Note 6)	DECDEL PERIOD
	Comma		X	COMMA	COMMA	OPTION([...] *COMMA) or OPTION([...] *SYSVAL) (Note 6)	DECDEL COMMA
Date Format (Note 7)	ISO	X		DATE(ISO) (Note 8)	DATE(ISO)	DATFMT(*ISO) (Note 8)	DATETIME ISO (Note 9)
	USA	X		DATE(USA)	DATE(USA)	DATFMT(*USA)	DATETIME USA
	EUR	X		DATE(EUR)	DATE(EUR)	DATFMT(*EUR)	DATETIME EUR
	JIS	X		DATE(JIS)	DATE(JIS)	DATFMT(*JIS)	DATETIME JIS
Time Format (Note 7)	ISO	X		TIME(ISO) (Note 8)	TIME(ISO)	TIMFMT(*ISO) (Note 8)	DATETIME ISO (Note 9)
	USA	X		TIME(USA)	TIME(USA)	TIMFMT(*USA)	DATETIME USA
	EUR	X		TIME(EUR)	TIME(EUR)	TIMFMT(*EUR)	DATETIME EUR
	JIS	X		TIME(JIS)	TIME(JIS)	TIMFMT(*JIS)	DATETIME JIS
Package Isolation Level (Note 10)	Repeatable read	X		ISOLATION(RR)b	ISOLATION(RR)		ISOLATION RR
	Read Stability (All)	X			ISOLATION(RS)	COMMIT(*ALL)	ISOLATION RS
	Cursor stability	X		ISOLATION(CS)b	ISOLATION(CS)	COMMIT(*CS)	ISOLATION CS
	Uncommitted Read (Change)	X			ISOLATION(UR)	COMMIT(*CHG)	ISOLATION UR
	No commit		X (Note 11)			COMMIT(*NONE)	ISOLATION NC
Bind Creation Control	No errors allowed	X		SQLERROR (NOPACKAGE)b	NOCHECK	OPTION([...] *GEN) GENLVL(00-09, 10, 11-20)	SQLERROR NOPACKAGE
	Check only	X			CHECK	OPTION([...] *NOGEN)	SQLERROR CHECK
	Errors allowed		X	SQLERROR (CONTINUE)b	ERROR	OPTION([...] *GEN) GENLVL(21-40)	SQLERROR CONTINUE
Bind Explain Option	No SQL statements	X		EXPLAIN(NO)b	EXPLAIN(NO)		EXPLAIN NO
	All explainable SQL statements		X	EXPLAIN(YES)b	EXPLAIN(YES)		EXPLAIN YES
Package Owner Identifier	<Authorization ID>	X		OWNERb	OWNER		OWNER
	Any other value		X
RDB Release Option	Release at commit	X		RELEASE (COMMIT)b	RELEASE (COMMIT)		RELEASE COMMIT
	Release at conversation deallocation		X	RELEASE (DEALLOCATE)b	RELEASE (DEALLOCATE)		RELEASE DEALLOCATE
Default RDB Collection ID	<Authorization ID>	X		QUALIFIERb	QUALIFIER	DFTRDBCOL	QUALIFIER
	Any other value		X
Title (Package Description)	Any value (ignored by DB2)	X			LABEL	TEXT	TEXT
Query Block Protocol Control	Fixed row	X		CURRENTDATA (YES)b	SBLOCK	ALWBLK(*READ)	BLOCKING UNAMBIG
	Limited block	X		CURRENTDATA (NO)b	BLOCK	ALWBLK (*ALLREAD)	BLOCKING ALL
	Forced fixed row	X			NOBLOCK	ALWBLK(*NONE)	BLOCKING NO
Package Default Char. Subtype
	Use system default	X					CHARSUB DEFAULT
If Default CCSID is SBCS	BIT		X		CHARSUB(BIT)		CHARSUB BIT
If Default CCSID is SBCS	SBCS	X			CHARSUB(SBCS)		CHARSUB SBCS
If Default CCSID is SBCS	MBCS		X		CHARSUB(MBCS)		CHARSUB MBCS
If Default CCSID is MBCS	BIT		X		CHARSUB(BIT)		CHARSUB BIT
If Default CCSID is MBCS	SBCS		X		CHARSUB(SBCS)		CHARSUB SBCS
If Default CCSID is MBCS	MBCS	X			CHARSUB(MBCS)		CHARSUB MBCS
	Any other value		X
Package Default CCSID	Value specified when DB2 database was created	X			CCSIDSBCS() CCSIDGRAPHIC() CCSIDMIXED()		CCSIDS CCSIDG CCSIDM
	Any other value		X
Decimal Precision (Note 12)	31	X		DEC(31)			DEC 31
	Any other value		X	DEC(15)			DEC 15
Replaced Package Version Name	Null	X		REPLVERb			REPLVER
	Any other value		X
Generic Bind Option	Null		X				GENERIC
	Any other value		X
Package Authorization Rule	Requester	X					DYNAMICRULES RUN
	Owner		X				DYNAMICRULES BIND
	Creator of the user-defined function and stored procedure		X				DYNAMICRULES DEFINE
	Invoker of the user-defined function and stored procedure		X				DYNAMICRULES INVOKE
Degree of Parallelism	1		X (Note 13)				DEGREE 1
	n		X				DEGREE n
	ANY		X				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 against a DB2 database through the Application 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.

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 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 Application Server.

DRDA Heap Size (drda_heap_sz)

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.

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

[ DB2 List of Books | Search the DB2 Books ]