Administration and Programming Guide for OS/400

Managing Multiple Database Connections

You can connect to up to 50 local or remote databases at a time. The SQL language environment keeps the connections active for the life of the Web server process job that Net.Data is running under. Keeping the connections active provides fast database access after the initial connection to the database. You can prevent errors by taking the following issues into consideration:

• Net.Data does not allow concurrent connections to the same remote database. If a connection exists to a remote database using one user ID (the LOGIN SQL language environment parameter) and another request is made to connect to the same remote database using a second user ID, the SQL language environment must first disconnect the existing connection, do a commit (if commitment control is being used) and then reestablish the connection using the 'new' user ID and password. The commit is required because if the connection is broken, there is no way that a rollback can be accomplished in case of an error later in the macro.
• You can change login IDs after you've accessed a remote database, if TRANSACTION_SCOPE=SINGLE . The SQL language environment disconnects the existing connection, does a commit, and reestablishes the connection using the new user ID and password.
• Do not change login IDs after you have accessed a remote database if TRANSACTION_SCOPE=MULTIPLE, which is the default. The SQL language environment automatically rolls back and a SQL_CODE of -752 is returned, which indicates that the connection could not be changed.