IBM i DB2
Drivers
For applications running on an IBM i DB2 database, the correct drivers must be installed to be able to use the Windows GUI, Web GUI and Indicium application tier. This concerns the IBM i Access for Windows - .NET Data Provider. The required version depends on the IBM i version used on the server, for example, 6.1 or 7.3.
Make sure the latest service packs are installed. Service Packs can be found here.
Installing the IBM i Access .NET Data Provider
Ports
The database provider uses various network ports to connect with the database server. Ports 8470, 8471, 8475 and 8476 must be available from the client. If these ports are not open, setting up a connection can take a very long time.
Additional information is available on the following website. An overview of all port numbers that are used by this driver and what each port is used for can also be found here: https://www-304.ibm.com/support/docview.wss?uid=nas8N1019667.
Troubleshooting instructions are available here: http://www-01.ibm.com/support/docview.wss?uid=nas8N1019611
Expired passwords
In order to be able to use the expired password functionality for IBM i DB2, it is necessary that service pack SI44594_64a
or higher is
installed. You can download the latest service pack here.
Logging and tracing
Logging and tracing DB2 connections can be enabled in the registry using the following keys (32/64 bit):
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\IBM\Client Access\CurrentVersion\Common\ManagedProvider\Service
HKEY_LOCAL_MACHINE\SOFTWARE \IBM\Client Access\CurrentVersion\Common\ManagedProvider\Service
Performance optimizations
The performance of database connections and SQL on DB2 for IBM i can be improved with the following settings:
- Ports - Make sure the required ports are open.
- Prestarted Jobs - Creating connections (jobs) takes a relatively long time on an IBM i. By using Prestarted jobs these connections are prepared in advance. Set up the IBM i so that enough prestarted jobs are always available. See .
- Separate sub-system - Query plans remain valid as long as the environment variables (load, disk space, free memory) remain stable. Therefore, it is wise to place the QZDASOINIT tasks into a separate sub-system on the basis of, for example, an IP address.
- Plan cache - Increase the plan cache with the
QQQOOOCACH
command so that query plans remain available sufficiently long enough in the cache.
More information on prestarted jobs, subsystems and the plan cache can be found here.