This document discusses configuration issues that apply to the use of IBM iSeries Access for Windows ODBC and OLEDB with Web server applications.
Resolving The Problem
IBM iSeries Access for Windows and Client Access Express (V5R1) are supported for use in Web server and n-tier environments. Earlier versions are no longer supported.
Basic Information on Supported Environments, Setup, Configuration, and Samples
Samples can be found on the Rochester Support Center FTP site at the following Web site:
The sample X3TierSamp.zip includes detailed installation and configuration instructions and an ASP page that verifies the Client Access and ODBC configuration.
Other Sources for Samples and Information
Information on Native IBM AS/400 Web Serving Including Java Servlets and Java Server Pages can be found at the following Web site:
http://www.ibm.com/developerworks/java/ (IBM Java Development)
ODBC Datasource Settings
Applications that use the ODBC SQLConnect API with incorrect signon information require that the Signon dialog prompting setting be set to Never prompt for SQLConnect. This option is located in the datasource setup on the General tab under Connection Options.... Applications that require this setting include the server version of Seagate Crystal Reports.
If an ODBC Data source is used, it must be a System DSN and not a User DSN. Select Control Panel, ODBC Data Sources to verify the data source type. Verify that the system name used in the DSN is exactly the same as the name stored in the DNS server or, if you used cwbcfg /s, the same name you configured. If you supplied a TCP/IP address using cwbcfg /s, PING the TCP/IP address and verify that it is correct.
Verify the Client Access Express or iSeries Access for Windows and IBM OS/400 or IBM i5/OS configuration by connecting in an interactive environment, for example, from a desktop application.
With ODBC you should set the ODBC API prompt behavior to never prompt. If you are using ADO, the connection.open method will default to noprompt (SQLDriverConnect with the SQL_DRIVER_NOPROMPT option). For additional information, refer to the following Rochester Support Center knowledgebase document:
N1017951, ODBC Prompt Mode Behavior:
If you are using Seagate Crystal Reports or any other application that uses SQLConnect (or if you are not sure) then set the ODBC driver option to not prompt on SQLConnect. In R510 this option is found under the ODBC datasource setup, General tab, Connection Options button. Set Signon dialog prompting to Never prompt for SQLConnect.
If you are using the iSeries Access for Windows system objects, verify that the PromptMode property is set to cwbcoPromptNever (this is not the default value).
You must specify both user profile and password on the connect string to get a prompt mode of never with the Client Access OLE DB provider.
|o||Disable IP Lookup and Port Mapper Lookup|
The default settings for the client access system object used by a Microsoft Windows NT service is to look up the TCP/IP address of the iSeries family of servers by using DNS and look up the OS/400 or i5/OS database server port via the port mapper for each connection attempt. This can be significant overhead if connection pooling is not configured.
To change these settings:
o Sign on Microsoft Windows as Administrator. At a Windows NT command prompt, run the command cwbcfg /host MYSYSTEM /ipaddr 126.96.36.199 /s, where MYSYSTEM is your system name and 188.8.131.52 is the iSeries family server's TCP/IP address. This will create a default registry entry for your system. Adjust the system name and TCP/IP address for your iSeries family system. Note that the system name used in your ODBC system DSN must be identical to the system name used here.
o Run regedt32. Navigate to the tree: HKEY_USERS\.DEFAULT\Software\IBM\Client Access Express\CurrentVersion\Environments\My AS/400 Connections\MYSYSTEM\Communication
Change the following values (they will have a default value of 0):
Port lookup mode = 2
IP address lookup mode = 4
|o||Use Connection Pooling|
For maximum scalability, most ASP applications perform a connect, request, and disconnect for each HTTP request. For a well-tuned application, most of the OS/400 or i5/OS processing time will be spent on the connect/disconnect and the very first full file open of an SQL statement with very little time spent on subsequent executions of the same SQL statement. To improve performance, connection pooling must be used.
iSeries Access for Windows ODBC does not implement any native connection pooling. Connection pooling can be enabled at the OLE DB level for the Microsoft OLE DB provider for ODBC (MSDASQL) and at the Microsoft ODBC driver manager level. We recommend that you use MDAC 2.1 or later and ODBC connection pooling rather than OLEDB connection pooling. Use Windows NT Performance Monitor to verify that the pooling is working correctly. For further information on ODBC connection pooling, OLE DB resource pooling, and MDAC downloads, refer to the following Microsoft data access Web site:
|o||MSDASQL vs IBMDA400|
ADO can use the OLEDB provider for ODBC (MSDASQL) with the iSeries Access for Windows ODBC driver or the "native" iSeries Access for Windows OLEDB provider (IBMDA400). iSeries Access for Windows ODBC offers more functionality for SQL based applications. IBMDA400 is optimized for applications using record level access.
|o||Open Port 8475 (as-rmtcmd)|
iSeries Access for Windows runtime might attempt to retrieve application administration policies using port 8475. If there is a registered application administration policy on the target system a flag is set in the Signon server processing during authentication. This flag indicates to the client that a check must be done to see if the client has a cached version of the policy and if it is current with the target system policy. This will result in the requirement for the port 8475 to be available. If this port is blocked (by a firewall), the first ODBC or OLEDB connection will hang until the 8475 connection attempt times out. This typically takes 50 seconds, which results in very slow connection times. The cwbcopwr utility shipped with iSeries Access for windows can be used to set the timeout value for the remote command connections down to 1 second. The command to do this is CWBCOPWR /SVC:RMTCMD /RT:1 /ST:1.
Thread Safety Issues
Client Access Express thread safety is described in Information APAR II11851. Click to link to II11851
iSeries Access for Windows ODBC, OLEDB and .Net data providers are thread safe. All other components support thread affinity. Refer to the Information APAR for updates.
The thread affinity statement implies that applications using the iSeries Access for Windows automation objects should not save these objects in an Application variable. Besides the performance impact of serializing all http requests through one connection object, this violates the thread affinity restriction.
If the application allows users to enter user ID/password information, administrators should consider the following:
|1.||Applications should reject zero length passwords and user IDs.|
If a user ID but no password is specified on a signon attempt, Client Access will attempt to use a cached password for the user. If both the user and password are zero length, Client Access might attempt to use the default user (see below).
|2.||Do not define a default user for the system.|
When both user ID and password are missing (zero length), Client Access determines if a default user is defined for the target system and if there is a cached password for that ID. By default, no user is defined as a default user for a new system; however, the CWBCFG command can be used to define one (the "/uid" parameter). To determine if a default user has been defined or to delete an existing default user, use regedt32 and navigate to the tree:
HKEY_USERS\.DEFAULT\Software\IBM\Client Access Express\CurrentVersion\Environments\My AS/400 Connections\MYSYSTEM\Communication
Verify that the User ID field is blank, or edit if needed. Another option is to set the Datasource Default User ID Connection option property to None.
|3.||Application Administration settings might not apply.|
If RMTCMD host server is not started or its port (8475) is blocked by a firewall, no policies are applied. The connection is allowed.
Internal Use Only
IBM ISERIES CLIENT ACCESS EXP (5722XE100)
17 June 2018