14 SQL Statement Support in Oracle Database Provider for DRDA

Oracle Database Provider for DRDA supports the use of native DB2 SQL clauses.

Overview of SQL Statement Support

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.

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.

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

SQL Clause Restrictions

There are some 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.

  • Internally Processed SQL Statements

  • Removed SQL Clauses that Retain Semantic Effect

  • Ignored SQL Clauses

  • Translated SQL Clauses

Internally Processed SQL Statements

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

Removed SQL Clauses that Retain Semantic Effect

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

Ignored SQL Clauses

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

Translated SQL Clauses

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.

Support for Special Registers

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.

Retrieving Values from 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.

Setting Special Registers

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.

Special Registers Supported by Oracle Database Provider for DRDA

Oracle Database Provider for DRDA supports the following registers.

APPLICATION ENCODING SCHEME
CURRENT APPLICATION ENCODING SCHEME

Query

SYS_CONTEXT('DRDAAS','APPLICATION_ENCODING_SCHEME')

Set

Updates SYS_CONTEXT

Semantics

No effect

CLIENT_ACCTNG
CURRENT CLIENT_ACCTNG CLIENT ACCTNG

Query

SYS_CONTEXT('DRDAAS','CLIENT_ACCTNG')

Set

Updates SYS_CONTEXT and CLIENT_INFO

Semantics

Updates CLIENT_INFO in V$SESSION

Notes

See MVS and DDF Accounting Information, as defined by IBM and DB2, documented in the DSNDQMDA macro.

CLIENT_APPLNAME
CURRENT CLIENT_APPLNAME CLIENT APPLNAME

Query

SYS_CONTEXT('DRDAAS','CLIENT_APPLNAME')

Set

Updates SYS_CONTEXT and CLIENT_IDENTIFIER

Semantics

Updates CLIENT_IDENTIFIER in V$SESSION

CLIENT_PROGRAMID
CURRENT CLIENT_PROGRAMID

Query

SYS_CONTEXT('DRDAAS','CLIENT_PROGRAMID')

Set

Updates SYS_CONTEXT

Semantics

No effect

CLIENT_USERID
CURRENT CLIENT_USERID CLIENT USERID

Query

SYS_CONTEXT('DRDAAS','CLIENT_USERID')

Set

Cannot be set

Semantics

Cannot be set

CLIENT_WRKSTNNAME
CURRENT CLIENT_WRKSTNNAME CLIENT WRKSTNNAME

Query

SYS_CONTEXT('DRDAAS','CLIENT_WRKSTNNAME')

Set

Updates SYS_CONTEXT

Semantics

No effect

DATE
CURRENT DATE CURRENT_DATE

Query

CURRENT DATE

Set

Cannot be set

Semantics

Cannot be set

DBPARTITIONNUM
CURRENT DBPARTITIONNUM

Query

SYS_CONTEXT('DRDAAS','DBPARTITIONNUM')

Set

Cannot be set

Semantics

Cannot be set

DEBUG MODE
CURRENT DEBUG MODE

Query

SYS_CONTEXT('DRDAAS','DEBUG_MODE')

Set

Updates SYS_CONTEXT

Semantics

No effect

DECFLOAT ROUNDING MODE
CURRENT DECFLOAT ROUNDING MODE

Query

SYS_CONTEXT('DRDAAS','DECFLOAT_ROUNDING_MODE')

Set

Updates SYS_CONTEXT

Semantics

No effect

DEFAULT TRANSFORM GROUP
CURRENT DEFAULT TRANSFORM GROUP

Query

SYS_CONTEXT('DRDAAS','DEFAULT_TRANSFORM_GROUP')

Set

Updates SYS_CONTEXT

Semantics

No effect

DEGREE
CURRENT 	DEGREE

Query

SYS_CONTEXT('DRDAAS','DEGREE')

Set

Updates SYS_CONTEXT

Semantics

No effect

EXPLAIN MODE
CURRENT EXPLAIN MODE

Query

SYS_CONTEXT('DRDAAS','EXPLAIN_MODE')

Set

Updates SYS_CONTEXT

Semantics

No effect

EXPLAIN SNAPSHOT
CURRENT EXPLAIN SNAPSHOT

Query

SYS_CONTEXT('DRDAAS','EXPLAIN_SNAPSHOT')

Set

Updates SYS_CONTEXT

Semantics

No effect

FEDERATED ASYNCHRONY
CURRENT FEDERATED ASYNCHRONY

Query

SYS_CONTEXT('DRDAAS','FEDERATED_ASYNCHRONY')

Set

Updates SYS_CONTEXT

Semantics

No effect

IMPLICIT XMLPARSE OPTION
CURRENT IMPLICIT XMLPARSE OPTION

Query

SYS_CONTEXT('DRDAAS','IMPLICIT_XMLPARSE_OPTION')

Set

Updates SYS_CONTEXT

Semantics

No effect

ISOLATION
CURRENT ISOLATION

Query

SYS_CONTEXT('DRDAAS','ISOLATION')

Set

Updates SYS_CONTEXT

Semantics

No effect

LOCK TIMEOUT
CURRENT LOCK TIMEOUT

Query

SYS_CONTEXT('DRDAAS','LOCK_TIMEOUT')

Set

Updates SYS_CONTEXT

Semantics

No effect

LOCALE LC_TYPE
CURRENT LOCALE LC_TYPE CURRENT_LC_TYPE

Query

SYS_CONTEXT('DRDAAS','LC_TYPE')

Set

Updates SYS_CONTEXT

Semantics

No effect

MAINTAINED TABLE TYPES FOR OPTIMIZATION
CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION

Query

SYS_CONTEXT('DRDAAS','MAINTAINED_TYPES')

Set

Updates SYS_CONTEXT

Semantics

No effect

MEMBER
CURRENT MEMBER

Query

SYS_CONTEXT('DRDAAS','MEMBER')

Set

Cannot be set

Semantics

Cannot be set

OPTIMIZATION HINT
CURRENT OPTIMIZATION HINT

Query

SYS_CONTEXT('DRDAAS','OPTIMIZATION_HINT')

Set

Updates SYS_CONTEXT

Semantics

No effect

PACKAGE PATH
CURRENT PACKAGE PATH

Query

SYS_CONTEXT('DRDAAS','PACKAGE_PATH')

Set

Updates SYS_CONTEXT

Semantics

No effect

PACKAGESET
CURRENT PACKAGESET

Query

SYS_CONTEXT('DRDAAS','PACKAGESET')

Set

Updates SYS_CONTEXT

Semantics

No effect

PATH
CURRENT PATH CURRENT_PATH CURRENT FUNCTION PATH

Query

SYS_CONTEXT('DRDAAS','PATH')

Set

Updates SYS_CONTEXT

Semantics

No effect

PRECISION
CURRENT PRECISION

Query

SYS_CONTEXT('DRDAAS','PRECISION')

Set

Updates SYS_CONTEXT

Semantics

No effect

QUERY ACCELERATION
CURRENT QUERY ACCELERATION

Query

SYS_CONTEXT('DRDAAS','QUERY_ACCELERATION')

Set

Updates SYS_CONTEXT

Semantics

No effect

QUERY OPTIMIZATION
CURRENT QUERY OPTIMIZATION

Query

SYS_CONTEXT('DRDAAS','QUERY_OPTIMIZATION')

Set

Updates SYS_CONTEXT

Semantics

No effect

REFRESH AGE
CURRENT REFRESH AGE

Query

SYS_CONTEXT('DRDAAS','REFRESH_AGE')

Set

Updates SYS_CONTEXT

Semantics

No effect

ROUTINE VERSION
CURRENT ROUTINE VERSION

Query

SYS_CONTEXT('DRDAAS','ROUTINE_VERSION')

Set

Updates SYS_CONTEXT

Semantics

No effect

RULES
CURRENT RULES

Query

SYS_CONTEXT('DRDAAS','RULES')

Set

Updates SYS_CONTEXT

Semantics

No effect

SCHEMA
CURRENT SCHEMA CURRENT_SCHEMA

Query

SYS_CONTEXT('USERENV','CURRENT_SCHEMA')

Set

Updates SYS_CONTEXT

Semantics

No effect

SERVER
CURRENT SERVER CURRENT_SERVER

Query

SYS_CONTEXT('DRDAAS','SERVER')

Set

Cannot be set

Semantics

Cannot be set

SQL_CCFLAGS
CURRENT SQL_CCFLAGS

Query

SYS_CONTEXT('DRDAAS','SQL_CCFLAGS')

Set

Updates SYS_CONTEXT

Semantics

No effect

SQLID
CURRENT SQLID USER

Query

USER

Set

Updates SYS_CONTEXT('DRDAAS', 'CURRENT_SQLID')

Semantics

No effect

TIMESTAMP
CURRENT TIMESTAMP CURRENT_TIMESTAMP

Query

CURRENT TIMESTAMP

Set

Cannot be set

Semantics

Cannot be set

USER
CURRENT USER CURRENT_USER

Query

USER

Set

Cannot be set

Semantics

Cannot be set

SESSION_USER
SESSION_USER

Query

USER

Set

Cannot be set

Semantics

Cannot be set

SYSTEM_USER
SYSTE_USER

Query

USER

Set

Cannot be set

Semantics

Cannot be set

ENCRYPTION PASSWORD
ENCRYPTION PASSWORD

Query

Cannot be queried

Set

Updates SYS_CONTEXT('DRDAAS','ENCRYPTION_PASSWORD')

Semantics

No effect