1.1 General Compliance Category

1.1.1 Host Languages

X/Open SQL specifies embedded bindings for SQL in the C and COBOL host languages.
Clearly, the host language supported is a major factor in applications
portability.
A compliant implementation is not required to support both bindings,
but each compliant implementation must publish which language(s) it
supports.

1.1.2 Transaction Processing XA Interface

The X/Open XA Specification describes a system-level interface by
which a "resource manager (RM)", in this case an SQL product,
coordinates with a "transaction manager (TM)".
SQL work can be associated with a global transaction identifier
that the TM provides, and SQL work can be committed atomically with
work done by other SQL and non-SQL products.

For an XA-compliant implementation, complete Section 1.3 fully.

Questions in Section 1.2 that relate to transactions must be answered
for all implementations, whether or not they claim XA compliance.

The form GRANT ALL instead of GRANT ALL PRIVILEGES, and the form REVOKE ALL
instead of REVOKE ALL PRIVILEGES.

Rationale

As defined in X/Open SQL, Section 1.2.2, Compliance Terminology,
an "optional" feature is a feature that implementations are not
required to support and that portable applications should not use.
Currently, the only optional features in X/Open SQL are the Integrity
Features (which specifies table-constraint-definitions)
and the deprecated features listed above.

Reference

The meaning of an optional SQL feature is described in the CAE
Specification, Structured Query Language (SQL), Section
1.2.2, Compliance Terminology.
The IEF is defined in Sections 5.3.4, CREATE TABLE, 5.3.9, GRANT and
5.3.10, REVOKE.
The deprecated features, and recommended alternatives, are described
in Chapter 8, Deprecated Features.

1.1.4 Character Set Support

Question 4: Does your implementation support multiple character
sets?

Response

No.
Yes.

If yes, please specify the following:

How does a program specify the desired character set?

Which character set is assumed?

Are values using different character sets comparable? If so, what is the
comparison algorithm?

Rationale

X/Open SQL defines as the SQL character set a character set consisting of
upper-case and lower-case letters, numerals and those symbols actually
used in X/Open SQL.
X/Open SQL does not specify the internal coding of these characters;
various X/Open RDA documents discuss their transfer format.
Implementations are allowed to provide other character sets.

Can a transaction span multiple SQL servers? If so, how does your
implementation define the range of work that a COMMIT or ROLLBACK statement
completes?

How many concurrent (current and dormant) connections can there be?

How does the implementation determine which server is the default
server that is identified by the keyword DEFAULT in the connection
statements and to which an application is connected when it executes
an SQL statement without first executing CONNECT?

Does the implementation use the contents of the USER clause, at the time of
the CONNECT statement, to determine whether to accept or reject the
connection? What other criteria, if any, does the implementation use to make
this determination?

List all implementation-defined criteria under which the CONNECT statement
can fail.

By what criteria does a server select a default schema?

What context, in addition to that listed explicitly in X/Open SQL, Section 5.7.4,
Connection Context, does the implementation save when a connection
to a server becomes dormant?

Rationale

X/Open SQL and the International Standard both conceptualise databases in
terms of clients and servers.

X/Open SQL envisages applications dealing with an SQL client and one
or more SQL servers, without regard to whether any server is local
or remote.
However, this model is not yet universal and is not mandatory in
X/Open SQL.
X/Open SQL specifies syntax and semantics for connection statements, and
requires that any use of the X/Open connection statements have the syntax
and semantics that X/Open specifies, for the sake of future application
portability.

1.2.3 Arithmetic Results

Question 7: What attributes does your implementation use for the
following?

Response

What is the precision of the result when approximate-numeric operands are
combined in an arithmetic operation?

What are the precision and range of the result when exact-numeric operands
are combined in an arithmetic operation?

What is the scale of the result when exact-numeric operands are combined by
division?

What is the range, precision and scale (as applicable) of the result of the
AVG set function?

What is the precision and range of the result of the COUNT set function?

What is the precision and range of the result of the SUM set function (both
for exact numeric and for approximate numeric results)?

Are warning flags or indicator variables set when assignment of a non-null
numeric value to an exact-numeric table column or host variable truncates
the fractional part of the number?
Yes
No

Rationale

In some cases, the attributes of a numeric result of an arithmetic operation
depend on the attributes of one or both operands.
The above questions enumerate the cases where the result attributes
are implementation-defined.

1.2.4 Scope of Host Variable

Question 9: What is the scope of a host variable?

Response

The scope is dictated by the scope rules of the host language.

The scope follows the textual order of embedded SQL constructs.

Rationale

X/Open SQL provides the two implementor options above for the
scope of a host variable.
(For host variables referenced by a cursor specification, the
scope rule applies to the OPEN statement rather than the DECLARE CURSOR
statement.)

1.2.6 Changes to Schema

Question 11: How does the implementation deal with the
situation where the schema changes after a program has been compiled?

Response

Indicate either that "Changes to a schema that would invalidate a compiled
program are prohibited" or "Changes to a schema that would invalidate a
compiled program are allowed but execution of the affected SQL statement fails
and an exception is raised with an SQLSTATE value in class 42".

If neither of these are true, then specify the behavior of the implementation.

Rationale

X/Open SQL requires any tables referenced by a SQL data manipulation
statement to exist when the program is compiled, but leaves
implementation-defined the effect if the definition of the table
changes after the program is compiled.

Question 12: How does the implementation resolve the situation
where a schema and an SQL statement are inconsistent at compilation
time or at execution time?

Response

Enter the behavior of the implementation in the area below. Possible behaviors
include:

Program is rejected at compilation if it is incompatible with a schema.

Program that is incompatible with a schema is accepted at compilation
time, but warning diagnostics may be issued. The statement is checked
again at execution time and an exception in class '42' is raised if the
statement is incompatible with a schema.

Consistency of SQL statements and schemas is checked only on execution,
and an exception in class '42' is raised if the statement is
incompatible with a schema.

Or any other legal behavior.

1.2.7 Implementation Diagnostics

Question 13: What run-time diagnostics are defined by your implementation?

Response

For every SQLSTATE value that is not defined for X/Open SQL, and where either
the class or the subclass or both is in the implementation-defined space,
specify:

1.2.8 Limits and Defaults

Question 14: What limits, if any, does your implementation place
on the following?

Response

For each entry in the two tables below, enter the appropriate limit
or default for your implementation. Where this does not apply, enter
"No limit" instead.

Note that a limit does not have to be a constant, but instead can be an
algorithm by which an application writer could determine the applicable
limit for the situation.

Attribute

X/Open Guaranteed Minimum

Implementation Limit

The length of a character string.

254

The maximum length of a variable-length character string.

254

The number of occurrences in an ALLOCATE DESCRIPTOR statement.

100

The precision of any DECIMAL or NUMERIC data type, in digits.

15 (decimal) digits

The precision of a FLOAT number, in bits.

47 bits

The precision of a SMALLINT number, in digits.

5 digits

The precision of an INTEGER number, in digits.

10 digits

The precision of a REAL number, in bits.

21 bits

The precision of a DOUBLE PRECISION number, in bits.

47 bits

The number of columns in a table.

100

The number of columns constituting an index.

6

The number of tables directly or indirectly referenced in a statement.

10

The number of cursors simultaneously open.

10

The number of columns that a single UPDATE statement can update.

20

The number of nested sub-queries.

9

The length of a text string containing an SQL statement.

4000 characters

The total length of a row.

2000 units*

The total length of an index key.

120 units*

The total length of columns specified in a GROUP BY clause.

120 units*

The total length of columns specified in an ORDER BY clause.

120 units*

*&nbsp&nbsp
X/Open SQL defines a storage model for the purpose of measuring
implementation capacity.
This definition, and the definition of the term storage "unit", is
in X/Open SQL, Section 7.1.3, Storage Capacity.

Enter below the defaults that apply to your implementation:

Attribute

X/Open Guaranteed Minimum

Default Value

The number of occurrences in an ALLOCATE DESCRIPTOR statement.

100

The precision of any DECIMAL or NUMERIC data type, in digits.

No minimum

The precision of a FLOAT number, in bits.

No minimum

Question 15: Are there any other limits your implementation
imposes on applications because of storage limitations specific to
your implementation?

Response

Rationale

Implementation techniques commonly impose limitations on the size and
complexity of SQL constructs.
X/Open SQL specifies minimum limits that an application can safely
assume all conforming implementations will support.
The responses to this section must be equal to or greater than the minima
specified in X/Open SQL.

1.2.9 Restrictions on Names

Question 16: What reserved words, if any, does your implementation reserve in addition to those defined as reserved words in X/Open SQL?

Response

Question 17: Does the implementation reserve any part of the
name space for procedure, function or variable names?

Response

If there are no restrictions, enter "No. Applications may use any such name."
If there are name space reservations, enter "Yes. The implementation reserves
the following part of the name space:". Follow this with the actual names
reserved or the critera for determining whether a name is reserved.

Question 18: What is the scope of a base-table-identifier and
viewed-table-identifier?

Response

The scope of these identifiers is a database; each such identifier must be unique within the database.

The scope of these identifiers is the current user.

Question 19: What is the relationship of the namespace of
index-identifier and the namespace of table-identifier?

Each of index-identifier and table-identifier has a separate namespace.
The index-identifier and table-identifier have the same namespace.

Question 20: Are keywords available for use as embedded host
variables?

Response

Yes.

No. The application must not use the keywords specified in X/Open SQL, or any additional keywords reserved by this implementation (see above) as embedded host variables. (This may be the case if the implementation uses a keyword-oriented parser.)

Question 21: Does your implementation allow qualification by
user name within table and index names in CREATE statements?

Response

Question 22: What is the maximum length of a user name that
may be retrieved into a host variable?

Response

Rationale

These questions identify implementation-defined information that
affects the application's choice of user-defined identifiers.

1.2.10 Data Definition Statements in Transactions

Question 23: What are the transaction semantics of data
definition statements and data manipulation statements?

Response

Select a response using one or more of the following:

Data definition and data manipulation statements may be mixed
arbitrarily within a transaction.

If a statement of one category has been executed in a transaction,
an attempt to execute a statement of the other category fails with
an exception condition, setting SQLSTATE to '25000'.

Execution of a data definition statement does not terminate the
current transaction.

Execution of a data definition statement terminates the current transaction.

A data definition statement may be executed successfully in a transaction
where a data manipulation statement has already been executed, and
commits its own action and that of all previously executed data
manipulation statements in the same transaction.

If none of the above are the case, enter a description of your own.

Question 24: How are transactions demarcated in your implementation?

Response

A transaction begins on the execution of the first SQL statement that operates on schema or data, and ends when either COMMIT or ROLLBACK is executed.
A transaction begins when the application executes tx_begin() and ends when the application executes either tx_commit() or tx_rollback().
A transaction can begin in either way, but must end in a way consistent with the way it began.

Rationale

This information encompasses current variability in the processing of data
definition statements.
X/Open SQL advises application writers to carefully structure
transactions that contain these statements, in order to be more widely
portable.

1.2.11 Commitment of Transactions

Question 25: What happens when communication errors occur during
execution of a COMMIT statement?

Response

The transaction is still active.
The transaction will either commit successfully or roll back successfully.
The transaction may be partly committed and partly rolled back.

Rationale

This information encompasses current variability in the level of guarantees
that SQL implementations provide regarding the atomicity of transactions in
the case of communication errors.
X/Open SQL advises application writers to carefully check the status
of the database after such a communication error before doing any
further work.

1.2.12 Assignments

Question 27: What semantics for character string assignment does
your implementation support?

Response

When the application tries to assign to a host variable a value that does not fit in the variable, trailing characters are truncated and SQLSTATE is set to '01004'.
When the application tries to assign to a host variable a value that does not fit in the variable, another outcome occurs:

Outcome:

Is an indicator value set to the original length of the source string if
it is assignable without truncation?
Yes
No

Affirm that SQLSTATE is set to '01004' to report any truncation.
Yes
No

Rationale

This question encompasses current variability in the assignment of character
strings to embedded host variables.
X/Open SQL advises applications to check SQLSTATE, not the indicator
variable, to determine whether truncation occurred.

1.2.13 DECLARE CURSOR

Question 28: When is a DECLARE CURSOR statement executed?

Response

No code is generated for a DECLARE CURSOR statement.
Instead, the application effectively executes the statement each time
it opens the defined cursor.
This is also the point at which any referenced host variables are evaluated.
DECLARE CURSOR must textually precede any statement that references the
defined cursor.

Code is generated for a DECLARE CURSOR statement.
Thus, DECLARE CURSOR must not only textually precede any statement
that references the defined cursor, but must be executed prior to the
referencing statement.

Rationale

The above two options, regarding the timing of execution of code for
DECLARE CURSOR, are both common in the marketplace.
X/Open SQL specifies the former, but acknowledges that in some
situations, notably operations with remote SQL servers, execution of
some code must occur before a statement is reached that references a cursor.

1.2.14 Integrity Constraints

Checking of uniqueness constraints is done following each update.
An UPDATE statement might fail if it violated a UNIQUE constraint
partway through its execution, even if it restored uniqueness before
it finished executing.
Checking of uniqueness constraints is done at the end of a statement.

Rationale

The above two implementations are both common in the marketplace.
X/Open SQL advises application writers not to perform multi-row
updates on columns defined as UNIQUE.

1.2.16 SELECT

Question 32: When is the * expression in a query-specification
evaluated?

Response

The * expression in a query-specification is evaluated at
compile-time.
The * expression in a query-specification is evaluated at the
time the surrounding statement is executed.

Rationale

Both above implementations of * in query-specifications
are common.
The effect on applications could vary, such as in the number of
columns a SELECT statement retrieves.
X/Open SQL advises application writers to avoid the * expression in
query-specifications, except in CREATE VIEW, in which * is
expanded when the view definition is originally executed.

1.2.17 Variable-Length String Comparison

It conceptually pads the shorter string with blanks to be the same
length as the longer string, and compares the entire length of the
longer string or until it finds a character position with a mismatch.
It compares only to the length of the shorter string; if the two strings
are identical for that length, it declares the longer string to be the
greater value.

Rationale

Both of the above implementations of comparison of variable-length
string comparison are found in the industry.
The effect on applications is that, in strings that include control
codes (which compare less than the blank character), the result of a
comparison could be reversed.
X/Open SQL advises application writers to exclude trailing blanks
from variable-length strings, or to explicitly blank-pad strings
before comparing them.

1.3 Transaction Processing (XA) Interface

The remainder of this CSQ must be completed if, and only if, the
answer to Question 2 is "Yes".
The SQL product is the resource manager (RM) in the questions below;
TM means the X/Open-compliant transaction manager.

1.3.1 Support for Dynamic Registration

Question 34: Does your implementation support dynamic registration?

Response

No. The RM does not dynamically register with the TM.
The TM must call the RM with the RM's xa_start(), xa_end(),
and xa_prepare() routines for each global transaction,
regardless of whether the AP accesses the RM in that global transaction.

Note that the X/Open XA Specification does not require that the
routines have these names; the Transaction Manager gains access to
these routines using a switch data structure, not by name.
The XA Specification defines the function of each routine; use of the
names in this questionnaire describes the routine with the specified
function.

Yes. The RM dynamically registers with the TM.
The TM does not call the RM's xa_start() routine at the start
of a global transaction.
However, whenever the AP accesses the RM and the RM is not already
within a global transaction, the RM calls ax_reg() before
it does application work, to see whether the work is part of a global
transaction.

Rationale

Dynamic registration is an RM-optional feature of the XA interface,
which may lead to efficiencies, especially on RMs that are involved
in relatively few global transactions.

An RM indicates whether it supports dynamic registration in its switch.

1.3.2 Switch Structure

Question 35: What is the name of the structure the RM defines of
type xa_switch_t?

Response

Question 36: What are the contents of this structure?

Response

Name of the RM

Applicable Flags

Version Number

0

Question 37: What are the forms of the information strings?

Response

Open string
Specify the form of the string that the TM provides for the RM's
xa_open() routine:

Close string
Specify the form of the string that the TM provides for the RM's
xa_close() routine:

Question 38: May the TM supply a null string in place of either
information strings?

Response

Open string
The TM must supply an open string, as specified in the response to the
previous question.
The TM may supply a null string as the string argument to the RM's
xa_open() routine.

Close string
The TM must supply a close string, as specified in the response to the
previous question.
The TM may supply a null string as the string argument to the RM's
xa_close() routine.

Question 39: May several instances of the RM implementation
operate from the same switch structure?

Response

No. Each instance of the RM implementation uses its own switch structure.
Yes. Several instances of the RM implementation may use the same switch
structure.

If you specified "Yes" above, indicate the syntax the TM uses in the
string argument to the RM's xa_open() routine to specify
different resource domains for different RM instances:

Rationale

The xa_switch_t structure is the mechanism the TM uses to
gain access to the RM.
It specifies the options of the XA interface that the RM supports and
gives entry points to the routines the RM provides.
In the above table, the flags may include:

1.3.3 Linking Information

Question 40: What linking information is necessary for your
implementation in the DTP environment?

Response

Specify the names of libraries or object files, in the correct
sequence, that the administrator must use when linking APs with the RM.
(If this information varies for different platforms - for example, if
the implementation uses standard archives on some platforms and
shared libraries on others - please provide the applicable
information for every relevant platform.)

Question 41: What are the changes in your implementation's API
in the DTP environment?

1.3.4 Read-only Optimisation

Question 42: Does your implementation use the read-only
optimisation?

Response

Yes. The RM may respond to the xa_prepare() call by returning
[XA_RDONLY] if the AP did not update shared resources at that RM.
This return concludes the RM's involvement in the transaction branch.
No. The RM never responds to the xa_prepare() call by
returning [XA_RDONLY], even if the AP has not modified any shared
resources.
Each call to xa_prepare() must be followed by a call to either
xa_commit() or xa_rollback().

Rationale

The read-only optimisation is an option that may simplify the transaction
commitment protocol in cases where certain RMs are involved only to read
shared resources.
It is not necessary to perform full two-phase commit at RMs where no
change to shared resources is contemplated.

1.3.5 Association Migration

Question 43: Does your implementation support association migration?

Response

Yes. If a TM suspends an association with the RM by calling
xa_end(TMSUSPEND), then xa_start(TMRESUME) may be called
in the same or in some other thread of control.
The TMNOMIGRATE bit is not set in the RM's switch structure.
No. If a TM suspends an association with the RM by calling
xa_end(TMSUSPEND), the subsequent call to xa_start(TMRESUME)
must come from the same thread of control.
The TMNOMIGRATE bit is set in the RM's switch structure.

Rationale

Because the RM associates context with a thread of control, or for other
reasons, some RMs may require that a suspended association be resumed
by the same thread that suspended it.

The TM declares when suspending an association that it reserves the
right to resume that association from a different thread of control.

1.3.6 Branch Identification

Question 44: How does your implementation use the bqual
component of the XID?

Response

The RM treats any requests as part of a single recoverable unit of work,
if the requests are associated with XIDs with the same gtrid
component.
The RM treats as a single recoverable unit of work only requests that are
associated with an XID identical in the gtrid and bqual
components.

Rationale

An RM that supports transaction branches, using the bqual
component of the XID, allows multiple recoverable units of work
that share a single use of the commitment protocol.

1.3.7 Asynchronous Calling Mode

Question 45: Does your implementation support the asynchronous
calling mode?

Response

Yes. Wherever the XA Specification allows it, the TM can call the RM using
the TMASYNC flag to specify asynchronous operation.
The RM returns immediately to the TM, providing a handle in place of
the status code.
The RM performs the requested operation asynchronously to the TM.
The TM may call xa_complete() to determine the status of the
requested operation.
The TMUSEASYNC flag is set in the RM's switch structure.

If you selected "Yes", specify the maximum number of asynchronous
operations the RM allows to be outstanding at any time:

No. If the TM calls the RM using the TMASYNC flag, the RM returns
[TMER_ASYNC].
The TMUSEASYNC flag is not set in the RM's switch structure.

Rationale

The asynchronous calling mode lets a TM efficiently schedule the
operations of multiple RMs.
For example, it may request at the same time that several RMs prepare
their work.

X/Open does not require RMs to support the asynchronous calling mode.
However, since support for this mode has a large impact on the
structure of the TM's calling sequence, RMs are required to publish
their level of support for this feature, using their switch structure.

1.3.8 Heuristics

Yes. The RM may respond to a call to xa_commit() or
xa_rollback() by returning [XA_HEURCOM], [XA_HEURRB],
[XA_HEURMIX] or [XA_HEURHAZ].
No. The RM never returns the above four heuristic codes.

Rationale

Based on elapsed time and other factors, an RM that has prepared
to commit a transaction branch may decide (or may allow the
administrator to decide) to commit or roll back its work independently
of the TM.
It could then unlock shared resources.
This may leave them in an inconsistent state.
The above four heuristic return codes inform the TM that the RM took
a heuristic decision, even if the result matched the decision the
TM requested.
Administrative action may be necessary after a heuristic outcome to
ensure the integrity of shared resources.

1.4 Other Topics

Although X/Open SQL lists the following topic as implementation-defined,
X/Open does not require further information in response to this
questionnaire:

Whether a pre-compiler is used and how the task of processing SQL
syntax is divided between the compiler and any pre-compiler.
(Reference CAE Specification, Structured Query Language (SQL),
Section 2.1.1, General Terms.)