3-Tier Development with iSeries Access for Windows and Internet Information Server (IIS)

Resolving The Problem

• Connection Problems
  
  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 the same as the name stored in the DNS server. If you used cwbcfg /s, the same as the host name you supplied. If you supplied a TCP/IP address by using cwbcfg /s, PING the TCP/IP address and verify that it is correct.
  
  Verify the configuration by connecting in an interactive environment, for example, from a desktop application.
• Hangs
  
  With ODBC, set the ODBC API prompt behavior to never prompt. If you are using ADO, the connection.open method defaults 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:
  
  When using Seagate Crystal Reports or any other application that uses SQLConnect (or if you are not sure), set the ODBC driver option to not prompt on SQLConnect. In R510 this option is found under the ODBC data source 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.

• Disable IP Lookup and Port Mapper Lookup
  
  The default settings for the IBM i system object used by a Microsoft Windows NT service are to look up the TCP/IP address of the IBM i system using DNS and look up the server ports via the port mapper for each connection attempt. These additional connections can cause a noticeable performance difference when connection pooling is not configured.
  
  To change these settings:
  
  1. Sign on Microsoft Windows as Administrator. At a Windows NT command prompt, run the command cwbcfg /host MYSYSTEM /ipaddr 1.1.1.1 /s /r, where MYSYSTEM is your system name and 1.1.1.1 is the IBM i TCP/IP address. This command creates a default registry entry for your system. Adjust the system name and TCP/IP address for your IBM i. The system name used in your ODBC system DSN must be identical to the system name used here.
  
  2. 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 have a default value of 0):
  Port lookup mode = 2
  IP address lookup mode = 4
   
• 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 IBM i processing time is spent on the connect and disconnect and the first full file open of an SQL statement with 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 website:
  //msdn.microsoft.com/en-us/library/cc307365.aspx
   
• 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 that use record level access.
   
• Open Port 8475 (as-rmtcmd)
  IBM i Access Client Solutions might attempt to retrieve application administration policies by 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 whether the client has a cached version of the policy and if it is current with the target system policy. So there is a requirement for the port 8475 to be available. If this port is blocked (by a firewall), the first ODBC or OLEDB connection hangs until the 8475 connection attempt times out. This time out typically takes 50 seconds, which results in slow connection times. The cwbcopwr utility shipped with IBM i Access Client Solutions Windows Application Package can be used to set the timeout value for the remote command connections down to 1 second:   CWBCOPWR /SVC:RMTCMD /RT:1 /ST:1.

1. Applications should reject zero length passwords and user IDs.
   
   If a user ID but no password is specified on a signon attempt, IBM i Access Client Solutions attempts to use a cached password for the user. If both the user and password are zero length, IBM i Access Client Solutions might attempt to use the default user (see next section).
    
2. Do not define a default user for the system.
   
   When both user ID and password are missing (zero length), IBM i Access Client Solutions determines whether a default user is defined for the target system and whether 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 whether a default user is 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 specify the correct user ID. Another option is to set the data source 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.