Accessing remote databases with interactive SQL
In interactive SQL, you can communicate with a remote relational database by using the SQL CONNECT statement. Interactive SQL uses the CONNECT (Type 2) semantics (distributed unit of work) for CONNECT statements.
Interactive SQL establishes an implicit connection to the local relational database when starting an SQL session. When the CONNECT statement is completed, a message shows the relational database connection that was established. If interactive SQL is starting a new session and COMMIT(*NONE) was not specified, or if interactive SQL is restoring a saved session and the commitment control level saved with the session was not *NONE, the connection will be registered with commitment control. This implicit connection and possible commitment control registration might influence subsequent connections to remote databases. It is suggested that you perform one of the following tasks before connecting to the remote database:
- When you are connecting to an application server that does not support distributed unit of work, issue a RELEASE ALL statement followed by a COMMIT statement to end previous connections, including the implicit connection to the local database.
- When you are connecting to an application server other than Db2 for i, issue a RELEASE ALL statement followed by a COMMIT statement to end previous connections, including the implicit connection to the local database, and change the commitment control level to at least *CHG.
When you are connecting to an application server other than Db2 for i, some session attributes are changed to attributes that are supported by that application server. The following table shows the attributes that change.
| Session attribute | Original value | New value |
|---|---|---|
| Date format | *YMD
*DMY
*MDY
*JUL
|
*ISO
*EUR
*USA
*USA
|
| Time format | *HMS with a : separator *HMS with any other separator | *JIS
*EUR
|
| Commitment control | *CHG,
*NONE
*ALL
|
*CS Repeatable Read |
| Naming convention | *SYS | *SQL |
| Allow copy data | *NO, *YES | *OPTIMIZE |
| Data refresh | *ALWAYS | *FORWARD |
| Decimal point | *SYSVAL | *PERIOD |
| Sort sequence | Any value other than *HEX | *HEX |
After the connection is completed, a message is sent stating that the session attributes have been changed. The changed session attributes can be displayed by using the session services display. While interactive SQL is running, no other connection can be established for the default activation group.
When connected to a remote system with interactive SQL, a statement processing mode of syntax-only checks the syntax of the statement against the syntax supported by the local system instead of the remote system. Similarly, the SQL prompter and list support use the statement syntax and naming conventions supported by the local system. The statement is run, however, on the remote system. Because of differences in the level of SQL support between the two systems, syntax errors may be found in the statement on the remote system at run time.
Lists of schemas and tables are available when you are connected to the local relational database. Lists of columns are available only when you are connected to a relational database manager that supports the DESCRIBE TABLE statement.
When you exit interactive SQL with connections that have pending changes or connections that use protected conversations, the connections remain. If you do not perform additional work over the connections, the connections are ended during the next COMMIT or ROLLBACK operation. You can also end the connections by doing a RELEASE ALL and a COMMIT before exiting interactive SQL.
Using interactive SQL for remote access to application servers other than Db2 for i might require some setup.