Setting up SQL packages for interactive SQL

If either of the following items is true, then you need to ensure that SQL packages are created at the systems:

• If you have the IBM DB2® Query Manager and SQL Development Kit for i licensed program and plan to use the interactive SQL function of that product
• If you plan to connect to DRDA servers other than IBM i that use TCP/IP from a pre-V5R1 OS/400® client, or to ones that do not have two-phase commit capability

Interactive SQL does not require SQL packages for IBM i. Normally, SQL packages are created automatically for interactive SQL users at a server system that does not run IBM i. However, a problem can occur because the initial connection for interactive SQL is to the local system, and that connection is protected by two-phase commit protocols. If a subsequent connection is made to a system that is only one-phase commit capable, or if TCP/IP is used from a pre-V5R1 OS/400 client, then that connection is read-only. When an attempt is made to automatically create a package over such a connection, it fails because the creation of a package is considered an update, and cannot be done over a read-only connection.

The solution to this is to end the connection to the local database before connecting to the remote server system. This can be done by doing a RELEASE ALL command followed by a COMMIT. Then the connection to the remote system can be made and because it is the first connection, updates can be made over it.

When you start interactive SQL, you must specify a commitment control level other than *NONE. Also, the user ID that you use to connect with must have the proper authority to create an SQL package on the server. If you receive an SQLSTATE of 42501 on the connection attempt, you might not have package creation authority.