DB2 Server for VSE & VM: SQL Reference

Appendix G. DRDA Considerations

Users who are planning to design applications that:

• run on non-VM platforms and use the Distributed Relation Database Architecture (DRDA) protocol to connect to DB2 Server for VSE & VM servers, or
• run on VM/ESA and use the Distributed Relation Database Architecture (DRDA) protocol to connect to servers other than DB2 Server for VSE & VM

need to be aware that DB2 Server for VSE & VM's support of SQL does not exactly match the IBM SQL standard ⁴ or the SQL Entry Level standard. ⁵ This appendix attempts to provide some guidance in discrepancies to these standards.

Omissions from the Standards

For a list of where DB2 Server for VSE & VM does not support the IBM SQL or |SQL92 entry level standard, please consult the DB2 Server for VSE & VM SQL Reference manual.

Extensions to the Standards

1. VM applications may not use the "userid IDENTIFIED BY password" clause on the CONNECT statement when using the DRDA protocol.
2. Packages that were created in SQLDS protocol by using extended dynamic statements ⁶ cannot be processed in DRDA protocol, or the other way around.
3. There is no support for modifiable packages created by using extended dynamic statements. If you request such support by specifying the MODIFY option on the CREATE PACKAGE statement, the system will override this option with NOMODIFY.
4. Nonmodifiable packages created by using extended dynamic statements are supported with the following restrictions:
   1. There is no support for the positioned UPDATE and positioned DELETE statements.
   2. If the Basic Extended PREPARE form of the extended PREPARE statement prepares a statement that contains parameter markers, the USING DESCRIPTOR clause must be used to identify an input SQLDA structure.
   3. There is no support for the Single Row Extended PREPARE form of the extended PREPARE statement.
   4. There is no support for the NODESCRIBE option of the CREATE PACKAGE statement. If specified, it will be ignored.
   5. There is no support for "USER" in the ISOLATION option of the CREATE PACKAGE statement. The system will override USER with CS.
   6. There is no support for "LOCAL" in the DATE or TIME option of the CREATE PACKAGE statement. If specified, SQLCODE -168 (SQLSTATE 42615) will be generated, indicating an incorrect parameter.
   7. DB2 Server for VSE & VM servers do not support cursors declared with the "WITH HOLD" clause. However, applications may use the "WITH HOLD" clause against other DRDA servers if they support it, except when extended dynamic statements are involved.
5. There is no support for the semantics checking of the Flagger, but the syntax checking of static SQL against the SAA and SQL-89 standards will still be carried out.

DB2 Server for VSE & VM Facility Restrictions

1. There is no support for the USERID option of the SQLPREP EXEC.
2. There is no support for "USER" in the preprocessing parameter ISOLATION. The system will override USER with CS.
3. There is no support for "LOCAL" in the preprocessor parameters DATE and TIME. If specified, SQLCODE -168 (SQLSTATE 42615) will be generated, indicating an incorrect parameter.
4. There is no support for the blocking of PUTs. However, the PUT operation will still be supported one row at a time as unblocked inserts.
5. The following ISQL commands are not supported when using the DRDA protocol, because they request functions specific to DB2 Server for VM:
   • SET ISOLATION
   • COUNTER
   • SHOW
6. The following DBSU commands are not supported when using the DRDA protocol, because they request functions specific to DB2 Server for VM:
   • UNLOAD DBSPACE
   • UNLOAD TABLE
   • UNLOAD PACKAGE
   • RELOAD DBSPACE
   • RELOAD TABLE
   • SET ISOLATION
   • SET UPDATE STATISTICS
   • REBIND PACKAGE
   • REORGANIZE INDEX
7. FORTRAN packages and any other packages created by using extended dynamic statements that were created in SQLDS protocol cannot be RELOADed by the DBS Utility in DRDA protocol, or the other way around.
8. Portable packages created under SQL/DS Version 2 Release 2 cannot be RELOADed by the DBS Utility in DRDA protocol.
9. If accounting data is sent from a DRDA application requester to a DB2 for VSE & VM server, only the first 16 bytes of user-defined data ⁷ is captured by the server and put into accounting records.

⁴

IBM SQL is a superset of the |SQL99 Entry Level standard

⁵

Entry Level of the International Organization for Standardization (ISO) 9075-1992 Database Language SQL specification

⁶

Since DB2 RXSQL uses extended dynamic statements, any restrictions on the use of extended dynamics apply to DB2 RXSQL as well.

⁷

For example, from DDCS for OS/2 user-defined data can be set by the DFT_ACCOUNT_STR configuration parameter.