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 does an implicit connect to the local RDB when starting an SQL session. When the CONNECT statement is completed, a message shows the relational database connection that was established. If starting a new session and COMMIT(*NONE) was not specified, or if restoring a saved session and the commit level saved with the session was not *NONE, the connection will be registered with commitment control. This implicit connect and possible commitment control registration may influence subsequent connections to remote databases. IBM recommends that prior to connecting to the remote system:

When you are connecting to a non-DB2 UDB for iSeries application server, some session attributes are changed to attributes that are supported by that application server. The following table shows the attributes that change.

Table 1. Values table
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
Notes:

  1. If you are connected to an application server that is running a release prior to Version 2 Release 3, the sort sequence value changes to *HEX.

  2. When you are connected to a DB2/2 or DB2/6000 application server, the date and time formats specified must be the same.

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 non-DB2 UDB for iSeries application servers might require some setup.

In the output of a communications trace, there may be a reference to a 'CREATE TABLE XXX' statement. This is used to determine package existence; it is part of normal processing, and can be ignored.

 

Parent topic:

Using interactive SQL

 

Related concepts


Distributed database programming

 

Related reference


Determining the connection type