This chapter provides information and lists the SQL clauses that are supported by Oracle Database Provider for DRDA.
This chapter contains the following topics:
Oracle Database Provider for DRDA transforms parts of the third-party native SQL statements before sending them for processing on the Oracle Database. In this release, Oracle Database Provider for DRDA is made compatible with Oracle Database Release 11g, which does not have a native understanding of many clauses not supported by Oracle's version of SQL because it does not support SQL Translation. When using SQL Translation, this affects the data and content of SQL requests received by the translator.
Example 14-1 Removing Clauses from SQL Statements
If a user enters the following SQL line on the client:
SELECT * FROM EMPLOYEES WITH UR
Oracle Database Provider for DRDA strips out the clause WITH UR, so if the user is using a Translator, it receives the following line of SQL:
SELECT * FROM EMPLOYEEES
Because the Translator never gets the WITH UR clause, the translation of the statement and the subsequent result set may not be what the user expects.
For this reason, this release of Oracle Database Provider for DRDA encompasses some translations functions.
This section describes restrictions on SQL statements that are supported for conversion by Oracle Database Provider for DRDA.
When describing SQL statements, keep in mind the following notation.
Use of (..), parantheses, enclose the applicable SQL statement context. For example, (SELECT) means that the clause applies to a SELECT statement.
Use of {..}, curly brackets, indicates an optional constant.
Use of <..> indicates an optional variable.
SQL language restrictions are arranged in following groups.
The following clauses are processed internally.
GRANT {ALL, BIND, COPY, EXECUTE, DROP, SET} ON PACKAGE <collid>.<pkgnam> TO <authid>
GRANT {ALL, BIND, COPY, EXECUTE, DROP, SET} ON PACKAGE <collid>.<pkgnam> TO <authid> WITH GRANT OPTION
GRANT {ALL, BIND, COPY, EXECUTE, DROP, SET} ON PROGRAM <collid>.<pkgnam> TO <authid>
GRANT {ALL, BIND, COPY, EXECUTE, DROP, SET} ON PROGRAM <collid>.<pkgnam> TO <authid> WITH GRANT OPTION
REVOKE {ALL, BIND, COPY, EXECUTE, DROP, SET} ON PACKAGE <collid>.<pkgnam> FROM <authid>
REVOKE {ALL, BIND, COPY, EXECUTE, DROP, SET} ON PROGRAM <collid>.<pkgnam> FROM <authid>
DROP PACKAGE <collid>.<pkgnam> VERSION <vsn>
DROP PACKAGE <collid>.<pkgnam>
FREE LOCATOR :H
HOLD LOCATOR :H
The following SQL clauses are removed from SQL statements. This should be noted because they have a semantic effect.
(SELECT) FOR READ ONLY
(SELECT) FOR FETCH ONLY
(DECLARE) WITH HOLD
(DECLARE) WITHOUT HOLD
(DECLARE) WITH ROWSET POSITIONING
(DECLARE) WITHOUT ROWSET POSITIONING
(DECLARE) NO SCROLL
(DECLARE) SCROLL
(CALL) WITH RETURN CLIENT
(CALL) WITH RETURN CALLER
(CALL) <:hostvar> USING DESCRIPTOR <:hostvar>
(SAVEPOINT) {UNIQUE} ON ROLLBACK RETAIN CURSORS
(SAVEPOINT) ON ROLLBACK RETAIN LOCKS
(INSERT) FOR <literal>|<bind-variable> ROWS
(INSERT) FOR MULTIPLE ROWS
(INSERT) NOT ATOMIC CONTINUE ON SQLEXCEPTION
These clauses are removed and ignored during translation.
WITH RR
WITH RR USE AND KEEP {EXCLUSIVE|UPDATE|SHARE} LOCKS
WITH RS
WITH RS USE AND KEEP {EXCLUSIVE|UPDATE|SHARE} LOCKS
WITH CS
WITH CS KEEP LOCKS
WITH UR
WITH NC
The following SQL clauses are translated into an alternative syntax; this may have a semantic effect.
The original clause WHERE CURRENT OF <cursorname> becomes WHERE ROWID = :N. N is a number.
The original = becomes '' IS NULL.
The original != becomes '' IS NOT NULL.
DB2 uses a concept known as special registers; they may be thought of as environment variables within a SQL context. Oracle Database provides limited native support for special registers.
Example 14-2 Retrieving values from special registers
When a user enters the following statement on the client:
SELECT CURRENT CLIENT_ACCTNG FROM SYSIBM.SYSDUMMY1;
Oracle Database Provider for DRDA parses the preceding statement, and replaces the clause CURRENT CLIENT_ACCTNG by the clause SYS_CONTEXT('DRDAAS_CTX_NAME','CLIENT_ACCTNG'), as follows:
SELECT SYS_CONTEXT('DRDAAS_CTX_NAME','CLIENT_ACCTNG') FROM SYSIBM.SYSDUMMY1;
Therefore, if a translator expects a CURRENT CLIENT_ACCTNG clause, it will receive an altered query.
Example 14-3 Setting special registers
When a user enters the following statement on the client:
SET CLIENT_ACCTNG = 'abc';
Oracle Database Provider for DRDA sets the value of the CLIENT_ACCTNG register to the string abc by replacing the clause CLIENT_ACCTNG = 'abc' by clause SYS_CONTEXT('DRDAAS_CTX_NAME','CLIENT_ACCTNG'), as follows:
SET SYS_CONTEXT('DRDAAS_CTX_NAME','CLIENT_ACCTNG') = 'abc';
Therefore, if a translator expects a CURRENT CLIENT_ACCTNG clause, it will receive an altered statement.
Oracle Database Provider for DRDA supports the following registers.
CURRENT APPLICATION ENCODING SCHEME
SYS_CONTEXT('DRDAAS','APPLICATION_ENCODING_SCHEME')
Updates SYS_CONTEXT
No effect
CURRENT CLIENT_ACCTNG CLIENT ACCTNG
SYS_CONTEXT('DRDAAS','CLIENT_ACCTNG')
Updates SYS_CONTEXT and CLIENT_INFO
Updates CLIENT_INFO in V$SESSION
See MVS and DDF Accounting Information, as defined by IBM and DB2, documented in the DSNDQMDA macro.
CURRENT CLIENT_APPLNAME CLIENT APPLNAME
SYS_CONTEXT('DRDAAS','CLIENT_APPLNAME')
Updates SYS_CONTEXT and CLIENT_IDENTIFIER
Updates CLIENT_IDENTIFIER in V$SESSION
CURRENT CLIENT_PROGRAMID
SYS_CONTEXT('DRDAAS','CLIENT_PROGRAMID')
Updates SYS_CONTEXT
No effect
CURRENT CLIENT_USERID CLIENT USERID
SYS_CONTEXT('DRDAAS','CLIENT_USERID')
Cannot be set
Cannot be set
CURRENT CLIENT_WRKSTNNAME CLIENT WRKSTNNAME
SYS_CONTEXT('DRDAAS','CLIENT_WRKSTNNAME')
Updates SYS_CONTEXT
No effect
CURRENT DBPARTITIONNUM
SYS_CONTEXT('DRDAAS','DBPARTITIONNUM')
Cannot be set
Cannot be set
CURRENT DECFLOAT ROUNDING MODE
SYS_CONTEXT('DRDAAS','DECFLOAT_ROUNDING_MODE')
Updates SYS_CONTEXT
No effect
CURRENT DEFAULT TRANSFORM GROUP
SYS_CONTEXT('DRDAAS','DEFAULT_TRANSFORM_GROUP')
Updates SYS_CONTEXT
No effect
CURRENT EXPLAIN MODE
SYS_CONTEXT('DRDAAS','EXPLAIN_MODE')
Updates SYS_CONTEXT
No effect
CURRENT EXPLAIN SNAPSHOT
SYS_CONTEXT('DRDAAS','EXPLAIN_SNAPSHOT')
Updates SYS_CONTEXT
No effect
CURRENT FEDERATED ASYNCHRONY
SYS_CONTEXT('DRDAAS','FEDERATED_ASYNCHRONY')
Updates SYS_CONTEXT
No effect
CURRENT IMPLICIT XMLPARSE OPTION
SYS_CONTEXT('DRDAAS','IMPLICIT_XMLPARSE_OPTION')
Updates SYS_CONTEXT
No effect
CURRENT LOCK TIMEOUT
SYS_CONTEXT('DRDAAS','LOCK_TIMEOUT')
Updates SYS_CONTEXT
No effect
CURRENT LOCALE LC_TYPE CURRENT_LC_TYPE
SYS_CONTEXT('DRDAAS','LC_TYPE')
Updates SYS_CONTEXT
No effect
CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION
SYS_CONTEXT('DRDAAS','MAINTAINED_TYPES')
Updates SYS_CONTEXT
No effect
CURRENT OPTIMIZATION HINT
SYS_CONTEXT('DRDAAS','OPTIMIZATION_HINT')
Updates SYS_CONTEXT
No effect
CURRENT PACKAGE PATH
SYS_CONTEXT('DRDAAS','PACKAGE_PATH')
Updates SYS_CONTEXT
No effect
CURRENT PATH CURRENT_PATH CURRENT FUNCTION PATH
SYS_CONTEXT('DRDAAS','PATH')
Updates SYS_CONTEXT
No effect
CURRENT QUERY ACCELERATION
SYS_CONTEXT('DRDAAS','QUERY_ACCELERATION')
Updates SYS_CONTEXT
No effect
CURRENT QUERY OPTIMIZATION
SYS_CONTEXT('DRDAAS','QUERY_OPTIMIZATION')
Updates SYS_CONTEXT
No effect
CURRENT ROUTINE VERSION
SYS_CONTEXT('DRDAAS','ROUTINE_VERSION')
Updates SYS_CONTEXT
No effect
CURRENT SCHEMA CURRENT_SCHEMA
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
Updates SYS_CONTEXT
No effect