FEDERAL INFORMATION PROCESSING STANDARDS PUBLICATION 127-2 (Final Draft 1/25/93) Announcing the Standard for Database Language SQL Federal Information Processing Standards Publications (FIPS PUBS) are issued by the National Institute of Standards and Technology after approval by the Secretary of Commerce pursuant to Section 111(d) of the Federal Property and Administrative Services Act of 1949 as amended by the Computer Security Act of 1987, Public Law 100-235. 1. Name of Standard. Database Language SQL (FIPS PUB 127-2). 2. Category of Standard. Software Standard, Database. 3. Explanation. This publication is a revision of FIPS PUB 127-1 and supersedes that document in its entirety. It provides a substantial, upward- compatible enhancement of Database Language SQL. It includes four levels of conformance: Entry SQL, Transitional SQL, Intermediate SQL, and Full SQL. Entry SQL is a minor enhancement over the minimum requirements of FIPS PUB 127-1, Intermediate SQL is a major enhancement over Entry SQL, and Full SQL is a major enhancement over Intermediate SQL. Transitional SQL is a temporary FIPS specification that falls approximately half way between Entry SQL and Intermediate SQL. Conformance to Entry SQL is required in all Federal procurements of SQL products. Conformance to Transitional SQL, Intermediate SQL, or Full SQL are options that may be specified, explicitly, as requirements in a Federal procurement. Section 13 identifies the minimum requirements for conformance to Entry SQL in FIPS PUB 127-2 that differ from the minimum requirements for conformance to FIPS PUB 127-1, and Section 14 defines requirements for the three additional levels of conformance. This publication announces adoption of American National Standard Database Language SQL, ANSI X3.135-1992, as the Federal Information Processing Standard for Database Language SQL (FIPS SQL). The exact specification is in Section 10 of this standard. ANSI SQL is a revision and replacement of two previous American National Standards, ANSI X3.135-1989 and ANSI X3.168-1989. It specifies the syntax and semantics of SQL language facilities for defining and accessing SQL databases. These facilities include: -- Schema definition, to declare the structures, integrity constraints, and access privileges of a database. -- Schema manipulation, to alter a schema definition. -- Data manipulation, to populate a database and access SQL-data. -- Transaction management, to define and manage SQL-transactions. -- Connection management, to establish and manage SQL-connections. -- Session management, to set the attributes of an SQL-session. -- Dynamic SQL, to provide facilities for dynamic construction and execution of SQL statements. -- Diagnostics management, to communicate constraint violations and warnings to applications. -- Information schema tables, to provide an SQL description of schema definitions. -- Programming language bindings, to declare database procedures that may be called from various programming languages. -- Embedded SQL, to define how SQL statements may be syntactically embedded into one of the following programming languages: Ada, C, COBOL, FORTRAN, MUMPS, Pascal, or PL/I. Embedded SQL was formerly defined in ANSI X3.168-1989. ANSI SQL is specified in three levels: Entry SQL, Intermediate SQL, and Full SQL. Entry SQL is a minor enhancement of ANSI X3.135-1989 (see Section 13). Intermediate SQL adds provisions for schema manipulation, dynamic SQL, diagnostics management, long identifiers, multiple module support, cascade delete for referential integrity, multiple schemas per authorization identifier, DATE and TIME data types, domains, variable length character strings, support for national character sets, and substantial enhancements for data manipulation. The data manipulation enhancements in Intermediate SQL include: a CASE expression, CAST functions between data types, string operations, natural join, outer join, union join, row value expressions, and subqueries in value expressions, as well as table operations for union, intersection, and complement. Full SQL adds provisions for connection management, session management, pre-defined character translations and form-of-use conversions, a BIT string data type, deferrable integrity constraints, derived tables in the FROM clause, subqueries in CHECK clauses, insensitive cursors, self-referencing data operations, assertions, and temporary tables. A list of optional FIPS SQL features, comprising all of the additional facilities in ANSI Intermediate SQL and Full SQL, is defined in Section 14 of this standard. The purpose of FIPS SQL is to promote portability and interoperability of database application programs, to facilitate maintenance of database systems among heterogeneous data processing environments, and to allow for the efficient exchange of programmers among different data management projects. The standard is used by implementors as the reference authority in developing a FIPS conforming relational model database management system, with standard programming language interfaces to that database management system. The standard is used by application programmers to help write SQL conforming applications and by other computer professionals who need to know the precise syntactic and semantic rules of Database Language SQL. 4. Approving Authority. Secretary of Commerce. 5. Maintenance Agency. Department of Commerce, National Institute of Standards and Technology (Computer Systems Laboratory) 6. Cross Index a. American National Standard Database Language SQL, ANSI X3.135-1992 (revision of ANSI X3.135-1989 and replacement of ANSI X3.168-1989). b. ISO/IEC 9075:1992, Database Language SQL (revision of ISO/IEC 9075:1989). Note: Except for a different Foreword, Introduction, and Normative references, ANSI X3.135-1992 and ISO/IEC 9075:1992 are identical documents. 7. Related Documents a. Federal Information Resources Management Regulations (FIRMR) subpart 201.20.303, Standards, and subpart 201.39.1002, Federal Standards, April 1992. b. Federal ADP and Telecommunication Standards Index, U.S. General Services Administration, Information Resources Management Service, October 1992 (updated periodically). c. NIST, Validated Products List: Programming Languages, Database Language SQL, Graphics, GOSIP, POSIX, Security; Judy B. Kailey, Editor, NISTIR 5103, issue No. 1, January 1993 (republished quarterly). Available by subscription from the National Technical Information Service (NTIS). d. FIPS PUB 21-3, Programming Language COBOL, 1990. e. FIPS PUB 69-1, Programming Language FORTRAN, 1985. f. FIPS PUB 109, Programming Language Pascal, 1985. g. FIPS PUB 119, Programming Language Ada, 1985. h. FIPS PUB 125, Programming Language MUMPS, 1986 (Revision expected in 1993). i. FIPS PUB 160, Programming Language C, 1991. j. FIPS PUB 146, Government Open Systems Interconnection Profile (GOSIP). A revision to FIPS PUB 146-1, including Remote Database Access (RDA) specifications, is planned for mid-1993. To be issued in conjunction with IGOSS. k. IGOSS, Industry/Government Open Systems Specification, publication planned mid-1993. This specification will reference "stable agreements" from the NIST OSI Implementor's Workshop as of December 1992. l. NIST SP 500-206, Stable Implementation Agreements for Open Systems Interconnection Protocols, Version 6, Edition 1, NIST Workshop for Implementors of Open Systems Interconnection, December 1992. m. ISO/IEC DIS 9579-1, Information Technology - Open Systems Interconnection - Remote Database Access - Part 1: Generic model, service, and protocol, document ISO/IEC JTC1/SC21 N6375, August 1991. n. ISO/IEC DIS 9579-2, Information Technology - Open Systems Interconnection - Remote Database Access - Part 2: SQL specialization, document ISO/IEC JTC1/SC21 N6376, August 1991. o. ISO/IEC 10026, Information Technology - Open Systems Interconnection - Distributed Transaction Processing - Part 1: OSI TP Model, Part 2: OSI TP Service, Part 3: OSI TP Protocol Specification, International Standard, December 1992. p. SQL Information Bulletin, Number 1, SQLIB-1, Interpretations of ANSI X3.135-1989, available from Global Engineering Documents, April 1991. q. FIPS PUB 29-2, Interpretation Procedures for FIPS Software, 14 September 1987. r. ISO 646, Information Processing - ISO 7-bit coded character set for information interchange, 2nd edition, Third Edition, December 1991. s. ISO 4873, Information Processing - ISO 8-bit code for information interchange - Structure and rules for implementation, Third Edition, 1991. Replaces ANSI X3.134.1, 8-bit ASCII. t. ISO 8859-1, Information processing - 8-bit single-byte coded graphic character sets - Part 1: Latin alphabet No. 1, February 1987. Replaces ANSI X3.134.2. u. ISO/IEC CD 11404, Information Technology - Programming Languages - Language Independent Data Types (CLID), document JTC1/SC22/WG11 N345, December 1992. 8. Objectives. The FIPS for Database Language SQL permits Federal departments and agencies to exercise more effective control over the production, management, and use of the Government's information resources. The primary objectives are: -- to encourage more effective utilization and management of database application programmers by ensuring that skills acquired on one project are transportable to other projects, thereby reducing the cost of database programmer retraining. -- to reduce overall software costs by making it easier and less expensive to maintain database definitions and database application programs and to transfer those definitions and programs among different computers and database management systems, including replacement database management systems. -- to promote communication and interoperability among data installations conforming to FIPS SQL and related GOSIP communications standards. -- to reduce the cost of software development by achieving increased database application programmer productivity through the understanding and use of database methods employing standard structures and operations, standard data types, standard constraints, and standard interfaces to programming languages. -- to protect the software assets of the Federal government by ensuring to the maximal feasible extent that Federal database management system standards are technically sound and that subsequent revisions are compatible with the installed base. Government-wide attainment of the above objectives depends upon the widespread availability and use of comprehensive and precise standard database management system specifications. 9. Applicability 9.1 Database Language SQL is one of the database language standards provided for use by all Federal departments and agencies. These database language standards should be used for all computer database applications and programs that are either developed or acquired for government use. FIPS SQL is particularly well suited for use in database applications that employ the relational data model. The relational data model is appropriate for applications requiring flexibility in the data structures and access paths of the database. The relational data model is desirable where there is a substantial need for ad hoc data manipulation, and data restructuring, in addition to the need for access by static applications under production control. 9.2 FIPS SQL shall be used for relational database applications and programs when one or more of the following situations exist: -- It is anticipated that the life of the database application will be longer than the life of the presently utilized equipment or database management system, if any. -- The database application is under constant review for updating of the specifications, and changes may result frequently. -- The database application is being designed and developed centrally for a decentralized system that employs computers of different makes and models or database software acquired from a different vendor. -- The database application will or might be run under a database management system other than that for which the database application is initially written. -- The database application is to be understood and maintained by programmers other than the original ones. -- The database application is one part of a distributed application that requires exchange of data or interoperation of the various parts. -- The database application is or is likely to be used by organizations outside the Federal government (e.g., Federal government contractors, State and local governments, and others). 9.3 Nonstandard language features shall be used only when the needed operation or function cannot reasonably be implemented with the standard features alone. A needed language feature not provided by the FIPS database languages should, to the extent possible, be acquired as part of an otherwise FIPS conforming database management system. Although nonstandard language features can be very useful, it should be recognized that their use may make the interchange of programs and future conversion to a revised standard or replacement database management system more difficult and costly. 9.4 Although this standard does not specifically address interactive database access through graphical user interfaces (GUI), the SQL statements specified by this standard are appropriate for such use. In a Client/Server environment, a GUI client may use SQL statements to access SQL conformant server databases. 9.5 Although this standard does not specifically address distributed database management systems or distributed database applications, the connection management statements defined in this standard may be used, along with facilities for remote database access (ISO/IEC 9579) and distributed transaction processing (ISO/IEC 10026), to access SQL-data at remote nodes in a distributed system and to present a global view to application programs. 9.6 Although this standard does not specifically address user-defined data types, class hierarchies, inheritance, polymorphism, or other features of object database management systems, such capabilities are upward compatible extensions of this standard and may be specified in a future revision of FIPS SQL (see Section 16.8). 9.7 It is recognized that some programmatic requirements may be more economically and efficiently satisfied through the use of a database management system employing a different data model than those provided by the FIPS database languages or the use of a database management system that functionally conforms to a FIPS database language but does not conform to all other aspects of the FIPS. The use of any facility should be considered in the context of system life, system cost, data integrity, and the potential for data sharing. 9.8 Some programmatic requirements may be more economically and efficiently satisfied by the use of automatic program generators or by database access through other high-level language information processing systems. However, if the final output of a program generator or high-level language system is language that accesses a relational database, then that language shall conform to the conditions and specifications of SQL. 10. Specifications. FIPS SQL adopts all provisions of ANSI X3.135- 1992, Database Language SQL, with the exceptions listed below: a. FIPS SQL requires conformance to Entry SQL. Conformance to Transitional SQL, Intermediate SQL, or Full SQL are options that may be specified explicitly in SQL procurements (see Section 14). b. FIPS SQL does not include PL/I language bindings, since PL/I is not a FIPS programming language. c. FIPS SQL does not recognize conformance solely by "direct invocation and processing of SQL language" as specified in Subclause 23.2 of ANSI X3.135-1992, because direct invocation does not mandate all of the facilities desired in a FIPS SQL conforming product. Conformance to FIPS SQL requires a Module or Embedded SQL binding style to one or more FIPS programming languages. d. FIPS SQL requires that the "SQL Flagger" be implemented in Entry SQL in addition to Intermediate SQL and Full SQL. This is because FIPS SQL has always included a flagger requirement, even from its first specification in 1987. For conformance to Entry SQL, FIPS SQL requires "Entry SQL Flagging" with the "Syntax Only" extent of checking option as defined in Subclause 4.33 of ANSI X3.135-1992. The SQL Flagger is required for each language binding style, including "Interactive Direct SQL" (see Section 16.5). e. For conformance to Intermediate SQL or to Full SQL, FIPS SQL requires implementation of the following named character sets: SQL_CHARACTER, ASCII_GRAPHIC, LATIN1, ASCII_FULL, and SQL_TEXT. The form-of-use and default collation requirements for these character sets are defined in Section 16.7 of this standard. f. For conformance to Intermediate SQL or to Full SQL, FIPS SQL requires implementation of the FIPS_DOCUMENTATION schema, as specified in Section 15 of this standard. 11. Implementation. Implementation of this standard involves four areas of consideration: the effective date, acquisition of FIPS SQL implementations, interpretation of FIPS SQL, and validation of FIPS SQL implementations. 11.1 Effective Date. This publication is effective six (6) months after the date of publication of a final document in the Federal Register announcing its approval by the Secretary of Commerce. Prior to that date the requirements of FIPS PUB 127-1 apply to Federal SQL procurements. This delayed effective date is intended to give implementations that conform to FIPS PUB 127-1 time to make the enhancements necessary to enable conformance to Entry SQL (see Section 13). No further transitional period is necessary. 11.2 Acquisition of SQL Implementations. Relational model database management systems acquired for Federal use shall implement FIPS SQL. Conformance to FIPS SQL is required whether SQL implementations are developed internally, acquired as part of an ADP system procurement, acquired by separate procurement, used under an ADP leasing arrangement, or specified for use in contracts for programming services. Recommended terminology for procurement of FIPS SQL is contained in the U.S. General Services Administration publication Federal ADP & Telecommunications Standards Index, Chapter 4 Part 1. 11.3 Interpretation of FIPS SQL. NIST provides for the resolution of questions regarding FIPS SQL specifications and requirements, and issues official interpretations as needed. Procedures for interpretations are specified in FIPS PUB 29-2. All questions about the interpretation of FIPS SQL should be addressed to: Director Computer Systems Laboratory ATTN: Database Language SQL Interpretation National Institute of Standards and Technology Gaithersburg, MD 20899 Telephone: (301) 975-2833 11.4 Validation of SQL Implementations. Implementations of FIPS SQL shall be validated in accordance with NIST Computer Systems Laboratory (CSL) validation procedures for FIPS SQL. Recommended procurement terminology for validation of FIPS SQL is contained in the U.S. General Services Administration publication Federal ADP & Telecommunications Standards Index, Chapter 4 Part 2. This GSA publication provides terminology for three validation options: Delayed Validation, Prior Validation Testing, and Prior Validation. The agency shall select the appropriate validation option and shall specify whether a Validation Summary Report or Certificate of Validation is required. The agency shall specify appropriate time frames for validation and correction of nonconformities. The agency is advised to refer to the NIST publication Validated Products List for information about the validation status of SQL products. This information may be used to specify validation time frames that are not unduly restrictive of competition. The agency shall specify the criteria used to determine whether a Validation Summary Report (VSR) or Certificate is applicable to the hardware/software environment of the SQL implementation offered. The criteria for applicability of a VSR or Certificate should be appropriate to the size and timing of the procurement. A large procurement may require that the offered version/release of the SQL implementation shall be validated in a specified hardware/software environment and that the validation shall be conducted with specified hardware/software features or parameter settings; e.g. the same parameter settings to be used in a performance benchmark. An agency with a single-license procurement may review the Validated Products List to determine the applicability of existing VSRs or Certificates to the agency's hardware/software environment. Implementations shall be evaluated using the NIST SQL Test Suite, a suite of automated validation tests for SQL implementations. The NIST SQL Test Suite was first released in August 1988 to help users and vendors determine compliance with FIPS SQL. Version 3.0 of the test suite was released in January 1992, to be used for validating conformance to FIPS PUB 127-1 after July 1, 1992. It is expected that Version 4.0 of the test suite will be available in mid-1993, to be used for testing conformance to Entry SQL of FIPS PUB 127-2 after the effective date. The results of validation testing by the SQL Testing Service are published on a quarterly basis in the Validated Products List, available from the National Technical Information Service (NTIS). Each release of the test suite has provided additional interfaces and test cases to increase the test suite's coverage of the SQL language. Version 3.0 of the NIST SQL Test Suite provides 11 test suite types (interfaces): Embedded (pre-processor) Ada, Embedded C, Embedded COBOL, Embedded FORTRAN, Embedded Pascal, module language Ada, module language C, module language COBOL, module language FORTRAN, module language Pascal, and Interactive Direct SQL. Version 3.0 does not include tests for Embedded MUMPS or module language MUMPS because the MUMPS programming language interface is not defined in FIPS 127-1; such tests may be available in Version 4.0 for testing of FIPS 127-2. There are additional tests in Version 3.0 for the Integrity Enhancement Feature, default database sizing constructs, and the FIPS Flagger requirement of FIPS 127-1. An SQL Test Suite license includes all of the tests described above, documentation, and automatic notifications of approved changes to the SQL Test Suite for a six month period. A license for SQL Test Suite Version 3.0 is a necessary requirement for an organization that wishes to be tested by the NIST SQL Testing Service between July 1, 1992 and the effective date of FIPS 127-2. Current information about the NIST SQL Validation Service and validation procedures for FIPS SQL is available from: National Institute of Standards and Technology Computer Systems Laboratory Software Standards Validation Group Building 225, Room A266 Gaithersburg, Maryland 20899 (301) 975-2490 12. Waivers. Under certain exceptional circumstances, the heads of Federal departments and agencies may approve waivers to Federal Information Processing Standards (FIPS). The head of such agency may redelegate such authority only to a senior official designated pursuant to section 3506(b) of Title 44, U.S. Code. Waivers shall be granted only when: a. Compliance with a standard would adversely affect the accomplishment of the mission of an operator of a Federal computer system, or b. Cause a major adverse financial impact on the operator which is not offset by Government-wide savings. Agency heads may act upon a written waiver request containing the information detailed above. Agency heads may also act without a written waiver request when they determine that conditions for meeting the standard cannot be met. Agency heads may approve waivers only by a written decision which explains the basis on which the agency head made the required finding(s). A copy of each such decision, with procurement sensitive or classified portions clearly identified, shall be sent to: National Institute of Standards and Technology; ATTN: FIPS Waiver Decisions, Technology Building, Room B- 154; Gaithersburg, MD 20899. In addition, notice of each waiver granted and each delegation of authority to approve waivers shall be sent promptly to the Committee on Government Operations of the House of Representatives and the Committee on Governmental Affairs of the Senate and shall be published promptly in the Federal Register. When the determination on a waiver applies to the procurement of equipment and/or services, a notice of the waiver determination must be published in the Commerce Business Daily as a part of the notice of solicitation for offers of an acquisition or, if the waiver determination is made after that notice is published, by amendment to such notice. A copy of the waiver, any supporting documents, the document approving the waiver and any supporting and accompanying documents, with such deletions as the agency is authorized and decides to make under 5 U.S.C. Sec. 552(b), shall be part of the procurement documentation and retained by the agency. 13. New FIPS SQL Requirements. Conformance to Entry SQL requires additional capabilities from an SQL implementation beyond those required for minimal conformance to FIPS PUB 127-1. The following list identifies the additional capabilities required. All terms delimited by angle brackets (i.e. <...>) refer to syntactic productions specified in ANSI X3.135-1992. 1. Integrity enhancement. The Integrity Enhancement Feature was an option in FIPS PUB 127-1. Many implementations of the existing FIPS SQL support this feature and both ISO and ANSI standardization groups have made it required for all conforming Entry SQL implementations. This feature is now required in FIPS SQL. It includes any explicit or implicit , , , or the , each with specified restrictions for Entry SQL. 2. SQLSTATE status codes. This mechanism for returning exceptions to SQL statements augments the SQLCODE status codes originally specified. SQLCODE is now a deprecated feature. SQLSTATE specifies more than seventy-five (75) exception, completion, and warning codes, whereas SQLCODE specifies only three. SQLSTATE and SQLCODE are defined in Clause 22, "Status codes", of ANSI X3.135-1992. 3. Delimited identifiers. In the previous ANSI SQL specification, it was not possible for an application to specify identifiers with spaces or other special symbols. Also, it was not possible to protect against future assaults on the name space for s by additions to the list. The new facility for allows a user to enclose all identifiers in double-quotation marks, thereby ensuring that the name defined or referenced may contain spaces or other special symbols and will not be impacted by future additions to the list. 4. Renaming columns. In the SQL language it is possible to reference columns in a by position number instead of by column name. This is because it was not possible in the previous standard to name derived columns resulting from s. Reference by position number is now a deprecated feature; as an alternative, it is required to be able to name, or rename, any with an . 5. Commas in parameter lists. In the previous ANSI SQL specification, items in a were separated by spaces or other token separators. It is now possible to also separate such items by commas. This makes parameter lists compatible in style with all other lists in the SQL language. 6. SQL Errata. Several errors in the previous ANSI SQL specification have been corrected via announcement in the SQL Information Bulletin, SQLIB-1, including: addition of a leading colon for , corrections to the specification of WITH CHECK OPTION in a , clarification of the argument mode for Pascal parameters, and clarification of statement termination requirements for Embedded SQL in Pascal programs. Other errors in ANSI X3.135-1989 have also been corrected in ANSI X3.135-1992, including: additional overflow exceptions on data assignment, exceptions generated during evaluation of a , correction of the data type for the SQLCODE variable in an , removal of an ambiguity in the definition of WITH CHECK OPTION in nested view definitions, addition of a rule to prevent defining two s with identical unique column lists in the same table, additional requirements to enforce SELECT privileges, restrictions on circular view definitions, correction to the syntax of a in an , corrections for data type declarations in s for COBOL and PL/I, correction to the specification of , and correction to the specification of PL/I support for SQLCODE. Most of these corrections are listed in Annex E, "Incompatibilities", and Annex F, "Maintenance and interpretation of SQL" of ANSI X3.135-1992. All such corrections that pertain to Entry SQL are now required elements of FIPS SQL. Note: The term "deprecated", as used in ANSI SQL, means that a feature so labeled may not be supported in some future version of the standard, but it is still a fully supported and required feature of the existing standard. 14. Optional FIPS SQL features. FIPS SQL requires implementation of Entry SQL (see Section 10). Optionally, an SQL procurement may require conformance to Intermediate SQL, Full SQL, or Transitional SQL. Since implementations conforming to Intermediate SQL may not be available immediately, FIPS SQL specifies a temporary FIPS "Transitional SQL" approximately half way between Entry SQL and Intermediate SQL. FIPS Transitional SQL is intended to provide a common, near-term goal for SQL implementations that already have a number of features beyond Entry SQL. Federal procurements may wish to specify Transitional SQL as a requirement during the interim period before Intermediate SQL implementations are widely available. It is expected that a future version of the NIST SQL Test Suite will provide conformance testing for both Entry SQL and Transitional SQL before tests are completed for testing Intermediate SQL or Full SQL. There is no requirement for the SQL Flagger to flag Transitional SQL features separately from Entry SQL Flagging or Intermediate SQL Flagging. The following subsections partition FIPS SQL features into four subgroups: 1) required in Transitional SQL, 2) required in Intermediate SQL, 3) required in Full SQL, and 4) suitable in combination with Remote Database Access (RDA). The list of features in each subsection determines the required features for conformance to that level of FIPS SQL. In most cases, an SQL procurement will specify a mandatory base level of conformance along with a list of desirable features. Desirable features may be used to support the evaluation of a product offered in response to a procurement. In the following subsections, all Clause and Subclause references, and all syntactic terms delimited by angle brackets (i.e. <...>) are from ANSI X3.135- 1992. 14.1 Transitional SQL. The following FIPS SQL features should start becoming widely available in SQL conforming products in the near future. A conservative SQL procurement in an open systems environment could list these items as mandatory requirements with some degree of confidence that a competitive procurement would follow, at least by the end of the initial effective year of this standard. 1. Dynamic SQL. All provisions of Clause 17, "Dynamic SQL", with restrictions identified in the Leveling Rules for Intermediate SQL; removal of all Entry SQL Leveling Rules in Clause 17; removal of the Entry SQL restriction requiring that a not be a , as specified in Leveling Rule 2b of Subclause 12.1, ""; removal of Leveling Rule 2a of Subclause 6.2 that prohibits reference to a dynamic parameter in a . A shall include all s that are otherwise supported for Entry SQL, as well as any other for which non-preparable support is claimed by that implementation. 2. Basic information schema. Requires existence of an accessible INFORMATION_SCHEMA consisting of the following views defined in Subclause 21.2, "Information Schema": TABLES, VIEWS, and COLUMNS, all with any restrictions identified in the Leveling Rules for Intermediate SQL. 3. Basic schema manipulation. Support for the following schema definition and schema manipulation statements as s in an explicit or implicit : Subclauses 11.3 through 11.9, ""; Subclause 11.18, ""; Subclause 11.19, ""; Subclause 11.20, ""; Subclause 11.10, "", containing Subclause 11.11, ""; Subclause 11.10, "", containing Subclause 11.15, ""; Subclause 11.36, ""; and Subclause 11.37, ""; all with any other restrictions identified in the Leveling Rules for Entry SQL, and all with any enhancements derived from other features claimed to be supported by the implementation. Removal of Leveling Rules 2a in Subclauses 11.11, 11.15, 11.18, 11.20, and 11.37. 4. Joined table. All provisions for NATURAL JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, , and from Subclause 7.5, "", with restrictions identified in the Leveling Rules for Intermediate SQL. Removal of Entry SQL Leveling Rule 2a of Subclause 6.3, "
", that prohibits in a table reference. Removal of Entry SQL Leveling Rule 2c of Subclause 7.10, "", that prohibits in a . This feature does not include support for CROSS JOIN, UNION JOIN, or FULL OUTER JOIN. 5. DATETIME data types. Support for DATE, TIME, TIMESTAMP, and INTERVAL data types as defined in Subclause 6.1, "", with the exception of support for time zones and time zone management; support for and as defined in Subclause 5.3, ""; support for the following datetime operations: in an as defined in Subclause 6.6, "", defined in Subclause 6.8, defined in Subclause 6.14, defined in Subclause 6.15, and defined in Subclause 8.11 (Note: With support for s here); support for datetime comparison defined in Subclause 8.2, ""; support for as specified in Subclause 10.1; support for in a as specified in Subclause 11.5, ""; all with restrictions identified in the Leveling Rules for Intermediate SQL. The Syntax Rules require, by default from Syntax Rule 26 of Subclause 6.1, "", a greater than or equal to 6 decimal digits. This feature does not include support for time zones, including:
"; Leveling Rule 2a of Subclause 7.4, ""; Leveling Rule 2c of Subclause 7.9, ""; Leveling Rule 2a of Subclause 7.11, ", , and
", and Leveling Rule 2b of Subclause 13.5, ", as specified in Leveling Rule 2b of Subclause 7.9, "". 15. Lowercase identifiers. Removal of the Entry SQL restriction requiring that identifiers not contain any lowercase letters, as specified in Leveling Rule 2b of Subclause 5.2, " and "; with restrictions identified in the Leveling Rules for Intermediate SQL. 16. PRIMARY KEY enhancement. Removal of the Entry SQL restriction requiring that NOT NULL always be declared with any UNIQUE or PRIMARY KEY, as specified in Leveling Rule 2a of Subclause 11.7, "". 17. Multiple schemas per user. Support for separation of and in a . The itself need be processed only as required for Entry SQL (i.e. it need not be supported as an SQL statement in an explicit or implicit ), and a need only be as required by Entry SQL. See below feature #31, "Schema definition statement", for more restrictive syntactic requirements. If feature #2, "Basic information schema", is supported, then implementation of Subclause 21.2.4, "SCHEMATA view", in the INFORMATION_SCHEMA. If the Module language binding style is supported, as specified in Subclause 12.1, "", and Subclause 23.2, "Claims of conformance", then removal of Leveling Rule 2c of Subclause 12.1, "", that prohibits reference to a in a definition. 18. Multiple module support. Removal of the Entry SQL restriction that a be associated with an SQL-agent during its execution, and that an SQL-agent be associated with at most one , as specified in Leveling Rule 2a of Subclause 12.1, "". With removal of this restriction, it will be possible to compile s or s separately and rely on the implementation to link them together properly at execution time. To ensure universal portability, applications should adhere to the following self-imposed restrictions: 1) avoid linking modules having cursors with the same , 2) avoid linking modules that prepare statements with the same , 3) avoid linking modules that allocate descriptors with the same , 4) the scope of an is a single compilation unit, 5) an can be referenced only in the same compilation unit in which it is declared. 19. Referential delete actions. Remove Leveling Rule 2a of Subclause 11.8, "", thereby providing support for a that contains a , with restrictions identified in the Leveling Rules for Intermediate SQL. Remove Leveling Rule 2b of Subclause 11.4, "", thereby allowing an ON DELETE trigger. 20. CAST functions. All provisions of Subclause 6.10, "", with restrictions identified in the Leveling Rules for Intermediate SQL. The resulting data type of the and of the of a shall include all data types required for Entry SQL, as well as any other SQL standard data type whose support is claimed by the implementation. In particular, if feature #5, "Datetime data type", is supported, then s for casting DATE, TIME, TIMESTAMP, and INTERVAL to and from character strings. Removal of the Entry SQL restrictions against use of CAST, as specified in Leveling Rule 2a of Subclause 6.10, "", and Leveling Rule 2d of Subclause 6.11, "". 21. INSERT expressions. Removal of the Entry SQL restriction against specifying a in an , as specified in the second part of Leveling Rule 2a of Subclause 13.8, "". 22. Explicit defaults. Removal of the Entry SQL restriction against use of DEFAULT VALUES in an , as specified in Leveling Rule 2d of Subclause 13.8, "". Removal of the Entry SQL restriction against use of DEFAULT in a , as specified in Leveling Rule 2a of Subclause 7.1, "". Removal of the Entry SQL restriction against use of datetime and certain USER defaults, as specified in Leveling Rule 2a of Subclause 11.5, "". Removal of the Entry SQL restriction against use of DEFAULT in an of an or an , as specified in Leveling Rule 2c of Subclause 13.9, "". 23. Privilege tables. Support for feature #2, "Basic information schema", defined above, plus inclusion of the following views as defined in Subclause 21.2, "Information Schema": TABLE_PRIVILEGES, COLUMN_PRIVILEGES, and USAGE_PRIVILEGES, all with any restrictions identified in the Leveling Rules for Intermediate SQL. 24. Keyword relaxations. Removal of the Entry SQL restriction against using AS before a , as specified in Leveling Rule 2b of Subclause 6.3, "
". Removal of the Entry SQL restriction against using the optional keyword TABLE in a GRANT statement, as specified in Leveling Rule 2a of Subclause 11.36, "". Removal of the Entry SQL restriction for specifying FROM in a FETCH statement, as specified in Leveling Rule 2b of Subclause 13.3, "". Removal of the Entry SQL requirement to include the keyword WORK in COMMIT and ROLLBACK statements, as specified in Leveling Rules 2a of Subclause 14.3, "" and Subclause 14.4, "". 14.2 Intermediate SQL. The following FIPS SQL features are required for conformance to Intermediate SQL. They are the focus of current implementation attention and should start becoming widely available in the second or third effective year of this standard. 25. Domain definition. Support for Subclause 11.21, "", and Subclause 11.27, ""; support for VALUE in as specified in Syntax Rule 5 of Subclause 6.2, ""; reference to in a as specified in Subclause 11.4, ""; reference to in GRANT and REVOKE statements as specified in Subclause 11.36, "", and Subclause 11.37, ""; if feature #2, "Basic information schema", is supported, then implementation of Subclause 21.2.5, "DOMAINS view", in the INFORMATION_SCHEMA; if feature #3, "Basic schema manipulation", is supported, then support for and as s in an explicit or implicit ; if feature #20, "CAST functions", is supported, then support for as a ; if feature #33, "Constraint tables", is supported then implementation of Subclause 21.2.6, "DOMAIN_CONSTRAINTS view"; all with any restrictions identified in the Leveling Rules for Intermediate SQL. Removal of Leveling Rules 2a of Subclause 11.1, "", and Subclause 11.21, "", that prohibit domain definitions in Entry SQL; removal of Leveling Rule 2b of Subclause 6.2 that prohibits use of VALUE in a domain check constraint; removal of Leveling Rule 2a of Subclause 11.27, "", that prohibits dropping a domain from a schema; removal of Leveling Rule 2b of Subclause 11.36, "", that prohibits privilege definition on domains; removal of Leveling Rule 2a of Subclause 11.4, "", that prohibits column data type references to a domain. 26. CASE expression. All provisions of Subclause 6.9, "", with restrictions identified in the Leveling Rules for Intermediate SQL. Removal of all Entry SQL restrictions against use of CASE expressions, including Leveling Rule 2a of Subclause 6.9, "", and Leveling Rule 2c of Subclause 6.11, "". 27. Compound character literals. Removal of the Entry SQL restriction against specifying multiple repetitions of strings in a , as specified in Leveling Rule 2d of Subclause 5.3, "". This feature allows very long character strings to be subdivided into components prior to concatenation into a result string, thereby avoiding problems with line length or line termination in some programming languages. 28. LIKE enhancements. Removal of all Entry SQL restrictions in the LIKE predicate, including: Leveling Rules 2a, 2b, and 2c of Subclause 8.5, "", thereby allowing the to be a general instead of just a , and allowing the and to be general s instead of just simple s. 29. UNIQUE predicate. Support for the UNIQUE predicate, as specified in Subclause 8.9, ""; with any restrictions identified in the Leveling Rules for Intermediate SQL. Removal of the Entry SQL restrictions against use of the as specified by Leveling Rule 2b of Subclause 8.1 and Leveling Rule 2a of Subclause 8.9. 30. Table operations. Removal of Entry SQL restrictions in Subclause 7.10, "", pertaining to the UNION, EXCEPT and INTERSECT operations, and the CORRESPONDING option, including Leveling Rules 2a, 2b, 2d, and 2e; removal of the Entry SQL restriction requiring that a in an not include any table operations, as specified in part 1 of Leveling Rule 2a of Subclause 13.8, ""; removal of the Entry SQL restriction against using a in a
, as specified in Leveling Rule 2c of Subclause 6.3, "
"; removal of the Entry SQL restriction requiring that a in a not include any table operations, as specified in Leveling Rule 2b of Subclause 7.11, ""; removal of the Entry SQL restriction requiring that a in a not include any table operations, as specified in Leveling Rule 2a of Subclause 11.19, ""; all with any other restrictions identified in the Leveling Rules for Intermediate SQL. 31. Schema definition statement. Support for , Subclause 11.1, as an in an explicit or implicit , by removal of Leveling Rule 2a of Subclause 12.5, "", with any restrictions identified in the Leveling Rules for Intermediate SQL. A shall be an element required by Entry SQL, or an element defined in another feature whose support is claimed by the implementation. Support for feature #17, "Multiple schemas per user", defined above and removal of the Entry SQL restriction that prohibits definition of a in a , as specified in Leveling Rule 2b of Subclause 11.1. A may contain any circular references that are permitted for Intermediate SQL; in particular, s in two different
s that reference columns in the other table. 32. User authorization. All provisions of Subclause 16.4, "", with any restrictions identified in the Leveling Rules for Intermediate SQL. Removal of Leveling Rule 2a of Subclause 16.4. Support for CURRENT_USER, SESSION_USER, and SYSTEM_USER in by removal of Leveling Rule 2c of Subclause 6.2, "". 33. Constraint tables. Support for feature #2, "Basic information schema", defined above, plus inclusion of the following views as defined in Subclause 21.2, "Information Schema": TABLE_CONSTRAINTS, REFERENTIAL_CONSTRAINTS, and CHECK_CONSTRAINTS, all with any restrictions identified in the Leveling Rules for Intermediate SQL. 34. Usage tables. Support for feature #2, "Basic information schema", defined above, plus inclusion of the following views as defined in Subclause 21.2, "Information Schema": KEY_COLUMN_USAGE, VIEW_TABLE_USAGE, VIEW_COLUMN_USAGE, CONSTRAINT_TABLE_USAGE, CONSTRAINT_COLUMN_USAGE, and COLUMN_DOMAIN_USAGE, all with any restrictions identified in the Leveling Rules for Intermediate SQL. 35. Intermediate information schema. All provisions of Subclause 21.2, "Information schema", with restrictions identified in the Leveling Rules for Intermediate SQL. This feature includes feature #2, "Basic information schema", feature #23, "Privilege tables", feature #33, "Constraint tables", and feature #34, "Usage tables", defined above, the SCHEMATA view from feature #17, the DOMAINS and DOMAIN_CONSTRAINTS views from feature #25, and the CHARACTER_SETS view from feature #45, as well as all remaining tables required for Intermediate SQL, including: Subclause 21.2.2, "INFORMATION_SCHEMA_CATALOG_NAME base table", Subclause 21.2.17, "ASSERTIONS view", and Subclause 21.2.26, "SQL_LANGUAGES view". Removal of all Leveling Rules 2a in Subclause 21.2, "Information Schema". In Entry SQL or Intermediate SQL, some of these tables may have empty or trivial contents, but an implementation supporting this feature is required to process statements that properly reference any of these tables. These tables are all self-describing in the sense that they appear in the Definition Schema as PUBLIC tables and thus are visible in the INFORMATION_SCHEMA for every user. 36. Subprogram support. Removal of the Entry SQL restriction that prohibits full use of local variable name scoping in subprograms, as specified in Leveling Rule 2b of Subclause 19.1, "". With removal of this restriction, compilation units may include subprograms with SQL statements that reference local variables defined in the subprogram even if some other identically named variable, having different scope, happens to be defined in an SQL in the main program or in a different subprogram of the same compilation unit. This feature applies only to the Embedded SQL language interface specified in Clause 19, "Embedded SQL"; it does not apply to any Module or Direct Invocation interfaces. 37. Intermediate SQL Flagging. Support for both "Entry SQL Flagging" and "Intermediate SQL Flagging" with the "Syntax Only" extent of checking option as specified for conforming Intermediate SQL implementations in Subclause 23.4, "Flagger requirements", of ANSI X3.135-1992. This facility would allow an application to distinguish between vendor extensions beyond Entry SQL that are supported in Intermediate SQL and those that are beyond Intermediate SQL or are non-standard. 38. Schema manipulation. Support for feature #3, "Basic schema manipulation". In addition, support for the following schema definition and schema manipulation statements as s in an explicit or implicit : Subclause 11.2, ""; Subclauses 11.10 through 11.17, ""; all with any restrictions identified in the Leveling Rules for Intermediate SQL. Removal of Leveling Rules 2a in Subclause 11.2 and Subclauses 11.10 through 11.17. If Direct invocation and processing of SQL language is supported, as specified in Subclause 23.2, "Claims of conformance", then removal of Leveling Rule 2a of Subclause 20.1, "". 39. Long identifiers. Support for s or s that have lengths of up to 128 characters; that is, remove the restrictions of Leveling Rule 2a of Subclause 5.2, " and ". 40. Full outer join. Support for feature #4, "Joined table". In addition, support for FULL OUTER JOIN by removal of Leveling Rule 2a from Subclause 7.5, "". 41. Time zone specification. Support for feature #5, "DATETIME data types". In addition, full implementation of time zones and time zone management, including: support for
". Support for feature #33, "Constraint tables". If feature #25, "Domain definition", is supported, then removal of all restrictions against explicit s in Subclause 11.21, "". If feature #12, "Get diagnostics", is supported, then support for reference to user-defined, explicit s in the . 50. Documentation schema. Implementation of the documentation schema tables, SQL_FEATURES and SQL_SIZING, as specified in Section 15 of this standard. 14.3 Full SQL. The following FIPS SQL features are required for conformance to Full SQL. They will start becoming available in some products during the first through third effective years of FIPS 127-2. Some care should be taken before specifying Full SQL as a mandatory requirement in an SQL procurement because it is not yet certain if these features will be completely or competitively available in all operating environments. 51. BIT data type. Support for BIT and BIT VARYING data types, as defined in Subclause 6.1, ""; support for and as defined in Subclause 5.3, ""; support for the following bit string operations: defined in Subclause 6.6, "", defined in Subclause 6.7, "", and defined in Subclause 6.13, ""; support for comparison of fixed and variable length bit strings as defined in Subclause 8.2, ""; support for BIT VARYING in any supported by the implementation. If feature #20, "CAST functions", is supported, then s for casting bit strings to and from character strings. Removal of Leveling Rule 1b of Subclause 5.3, "". Removal of Leveling Rule 1b of Subclause 6.1, "". Removal of the Intermediate SQL requirement that a not be a , as specified in Leveling Rule 1b of Subclause 6.6, "". Removal of the Intermediate SQL restriction against using a , as specified in Leveling Rule 1d of Subclause 6.7, "". Removal of Leveling Rule 1b of Subclause 6.13, "", that prohibits use of a in Intermediate SQL. Removal of Leveling Rule 1a of Subclause 12.3, "", that prohibits specification of BIT or BIT VARYING in an Intermediate SQL . Removal of Leveling Rule 1a of Subclause 19.3, "", Leveling Rule 1a of Subclause 19.4, "", Leveling Rule 1a of Subclause 19.5, "", Leveling Rule 1a of Subclause 19.6, "", Leveling Rule 1a of Subclause 19.8, "", and Leveling Rule 1a of Subclause 19.9, "". 52. Assertion constraints. Support for and as s in an explicit or implicit , as specified in Subclauses 11.34 and 11.35, respectively. If feature #2, "Basic information schema" is supported, then implementation of Subclause 21.2.17, "ASSERTIONS view", in the INFORMATION_SCHEMA. Removal of Leveling Rules 1a in Subclause 11.1, Subclause 11.34, and Subclause 11.35. 53. Temporary tables. Support for GLOBAL TEMPORARY or LOCAL TEMPORARY table definitions and ON COMMIT DELETE ROWS or ON COMMIT PRESERVE ROWS options, as specified in Subclause 11.3, "
"; all provisions of Subclause 13.11, ""; all with any other restrictions identified in the Leveling Rules for Intermediate SQL. Removal of Leveling Rules 1a from Subclause 11.3, Subclause 12.1, and Subclause 13.11. 54. Full dynamic SQL. All provisions of Clause 17, "Dynamic SQL", including: host variables for descriptor names, statement names, and cursor names; DEALLOCATE PREPARE; DESCRIBE INPUT; dynamic single row select; and preparable positioned dynamic update/delete. Removal of the Intermediate SQL restriction against reference to system-supplied names, as specified in Leveling Rule 1a of Subclause 5.4, "Names and identifiers". Removal of Leveling Rules 1a of Subclauses 17.2, 17.3, 17.4, and 17.9 that restrict and to be s. Support for Subclause 17.7, "", Subclause 17.8, "", Subclause 17.13, "", Subclause 17.19, "", and Subclause 17.20, "", by removal Intermediate SQL Leveling Rule 1a from each of these clauses. Support for a in an EXECUTE statement, by removal of Leveling Rule 1a of Subclause 17.10, "". 55. Full DATETIME. Support for feature #5, "DATETIME data type", and feature #41, "Time zone specification". In addition, removal of the Intermediate SQL restrictions against specification of precision in TIME and TIMESTAMP data types, as specified in Leveling Rule 1a of Subclause 6.1, "", Leveling Rule 1a of Subclause 5.3, "", and Leveling Rule 1a of Subclause 6.8, "". 56. Full value expressions. Support for references to s in all places where they are restricted in Entry SQL or Intermediate SQL, including: removal of the Intermediate SQL restriction against using a in a with DISTINCT, as specified in Leveling Rule 1a of Subclause 6.5, ""; removal of the Intermediate SQL restriction against using a in an , as specified in Leveling Rule 1a of Subclause 8.4, "". 57. Truth value tests. Support for tests of TRUE, FALSE, or UNKNOWN, or their negations, applied to a as specified in Subclause 8.12, "". Removal of Leveling Rule 1a of Subclause 8.12. 58. Full character functions. Removal of the Intermediate SQL restriction against use of a POSITION expression, as specified in Leveling Rule 1a of Subclause 6.6, "". Removal of the Intermediate SQL restrictions against use of UPPER and LOWER functions, as specified in Leveling Rule 1a of Subclause 6.7, "". 59. Derived tables in FROM. Removal of the Intermediate SQL Leveling Rule 1a of Subclause 6.3, "
", that prohibits a
from being a . The effect is that a , possibly with a , may be specified in a FROM clause. 60. Trailing underscore. Removal of the Intermediate SQL restriction against using an as the last character of an identifier, as specified in Leveling Rule 1a of Subclause 5.2, " and ". 61. Indicator data types. Removal of the Intermediate SQL restrictions on the data types of indicator parameters and variables, as specified in Leveling Rule 1a of Subclause 6.2, " and ". 62. Referential name order. Removal of the Intermediate SQL restriction on the order of column names in a referential constraint definition, as specified in Leveling Rule 1c of Subclause 11.8, "". 63. Full SQL Flagging. Support for "Entry SQL Flagging", "Intermediate SQL Flagging", and "Full SQL Flagging", each with the "Syntax Only" extent of checking option as defined in Subclause 4.33 of ANSI X3.135-1992. This facility would allow an application to distinguish among vendor extensions beyond Entry SQL that are supported in Intermediate SQL, those beyond Intermediate SQL that are supported in Full SQL, and those that are non- standard. This feature does not include support for the "Catalog Lookup" option of the SQL Flagger (see feature #81). 64. Row and table constructors. All provisions of and
as specified in Subclause 7.1, "", and Subclause 7.2, "
", thereby providing support for multiple column row and table constructors. Removal of all Intermediate SQL Leveling Rules in these two clauses, allowing full use of s and
s in Clause 8, "Predicates", and in Subclause 13.8, "". 65. Catalog name qualifiers. Removal of the Intermediate SQL restriction against reference to catalog names, as specified in part of Leveling Rule 1b of Subclause 5.4, "Names and identifiers". 66. Simple tables. Support for and removal of the Intermediate SQL restriction against simple or explicit table references in a , as specified in Leveling Rules 1a and 1b of Subclause 7.10, "". 67. Subqueries in CHECK. Removal of the Intermediate SQL Leveling Rule 1a of Subclause 11.9, "", that prohibits specification of a in a CHECK constraint, but retaining any other restrictions on as required for Intermediate SQL. Support for granting and revoking REFERENCES privileges on all tables, including views, as specified in Subclause 10.3, "", so that the can reference base tables and views external to the table containing the check constraint. Removal of Intermediate SQL Leveling Rule 1b of Subclause 11.9, "", that allows implicit assumption of REFERENCES privileges on tables in Intermediate SQL. 68. Union and Cross join. Support for feature #4, "Joined table". In addition, support for UNION JOIN and CROSS JOIN by removal of Leveling Rule 1a and Leveling Rule 1b of Subclause 7.5, "". 69. Collation and translation. Support for Subclause 11.30, "", Subclause 11.31, "", Subclause 11.32, "", and Subclause 11.33, "", as s in an explicit or implicit . Removal of Leveling Rules 1a in Subclauses 11.30 through 11.33. Support for granting and revoking USAGE privileges on any defined collations or translations, as specified in Subclause 10.3, "", by removal of Leveling Rule 1a in Subclause 11.36, "". If feature #2, "Basic information schema" is supported, then implementation of Subclause 21.2.19, "COLLATIONS view", and Subclause 21.2.20, "TRANSLATIONS view", in the INFORMATION_SCHEMA. Removal of the Intermediate SQL restriction against reference to collation, translation, or conversion names, as specified in Leveling Rule 1b of Subclause 5.4, "Names and identifiers", in Leveling Rule 1a of Subclause 6.13, "", and in Leveling Rules 1b and 1c of Subclause 11.1, "". Removal of the Intermediate SQL restriction against use of the , as specified in Leveling Rule 1a of Subclause 10.5, "", Leveling Rule 1a of Subclause 11.4, "", Leveling Rule 1a of Subclause 7.7, "", Leveling Rule 1a of Subclause 11.21, "", and Leveling Rule 1a of Subclause 11.28, "". Removal of the Intermediate SQL restrictions against use of character translations or form-of-use conversions, as specified in Leveling Rules 1b and 1c of Subclause 6.7, "". 70. Referential update actions. Support for a that contains an , as defined in Subclause 11.8, "". Removal of Leveling Rule 1b of Subclause 11.8, "", that prohibits specification of an in a , thereby allowing an ON UPDATE trigger. 71. ALTER domain. Support for , , , , and , all specified in Subclauses 11.22 through 11.26, as s in an explicit or implicit . Remove all Intermediate SQL Leveling Rules in Subclauses 11.22 through 11.26. 72. Deferrable constraints. Support for the , specified in Subclause 14.2, as an in an explicit or implicit . Removal of Leveling Rule 1a of Subclause 14.2. Removal of Intermediate SQL Leveling Rule 1a of Subclause 10.6, " and ", that prohibits user specification of DEFERRABLE constraints and as DEFERRED or IMMEDIATE. 73. INSERT column privileges. Removal of the Intermediate SQL Leveling Rule 1a of Subclause 10.3, "", that prohibits the granting of INSERT privileges on individual columns. 74. Referential MATCH types. Support for MATCH FULL and MATCH PARTIAL, as defined in Subclause 11.8, "", by removal of Intermediate SQL Leveling Rule 1a. Support for the MATCH predicate, as specified in Subclause 8.10, "", by removal of Intermediate SQL Leveling Rules 1a of Subclause 8.1, "", and Subclause 8.10, "". 75. View CHECK enhancements. Support for CASCADED and LOCAL options in the WITH CHECK OPTION clause of a . Removal of the Intermediate SQL restrictions against explicit declaration of these options, as specified in Leveling Rule 1a of Subclause 11.19, "". 76. Session management. Support for "Session management", as specified in Subclause 16.1, ", Subclause 16.2, "", and Subclause 16.3, ". Removal of all Intermediate SQL Leveling Rules in Subclauses 16.1, 16.2, and 16.3. 77. Connection management. Support for all provisions of Clause 15, "Connection management", including: CONNECT, SET CONNECTION, and DISCONNECT. Those s that are valid s and s are implementation-defined, so long as Syntax Rule 1 of Subclause 15.1, "", is satisfied. The communication protocols used to implement the connection management statements are implementation-defined. Removal of all Intermediate SQL Leveling Rules in Subclauses 15.1, 15.2, and 15.3. Removal of the Intermediate SQL restriction against reference to , as specified in Leveling Rule 1b of Subclause 5.4, "Names and identifiers". 78. Self-referencing operations. Removal of the Intermediate SQL restrictions against self-referencing DELETE, INSERT, and UPDATE statements, as specified in Leveling Rules 1a of Subclause 13.7, "", Subclause 13.8, "", and Subclause 13.10, "". 79. Insensitive cursors. Support for the INSENSITIVE option on a cursor declaration, by removal of the Intermediate SQL Leveling Rule 1a of Subclause 13.1, "". If feature #54, "Full dynamic SQL", is supported, then removal of Leveling Rule 1a in Subclause 17.12, "". 80. Full set function. Support for feature #44, "Intermediate set function". In addition, removal of the Intermediate SQL restrictions against use of DISTINCT in a , as specified in Leveling Rule 1a of Subclause 6.5, "", and removal of the Intermediate SQL restriction against multiple use of DISTINCT in a , as specified in Leveling Rule 1a of Subclause 7.9, "". 81. Catalog flagging. Support for both the "Syntax Only" and "Catalog Lookup" extent of checking options of the SQL Flagger feature, as defined in Subclause 4.34 of ANSI X3.135-1992. This facility would allow the SQL Flagger to catch a syntactic extension that violates a Syntax Rule dependent upon information stored in the INFORMATION_SCHEMA. 82. Local table references. Support for qualified local table references of the form MODULE.T, by removal of the Intermediate SQL restriction against reference to a , as specified in Leveling Rule 1b of Subclause 5.4, "Names and identifiers". 83. Full cursor update. Support for the updatability of SCROLL or ORDER BY cursors, by removal of Leveling Rule 1b of Subclause 13.1, "" and Leveling Rule 1a of Subclause 13.9, "". If feature #54, "Full dynamic SQL", is supported, then removal of Leveling Rule 1b in Subclause 17.12, "". 14.4 Integration with RDA. The following FIPS SQL optional features require conformance to ISO/IEC 9579, the International Standard for Remote Database Access (RDA), with the implementation agreements specified in FIPS 146 (GOSIP) from the NIST OSI Implementors Workshop. SQL implementations that also conform to the RDA portion of GOSIP should start becoming available near the end of the first or at the beginning of the second effective year of FIPS 127-2. 84. RDA/SQL-Client. Conformance to the Remote Database Access (RDA) component of FIPS PUB 146, "Government Open Systems Interconnection Profile (GOSIP)", by satisfying all of the requirements for the "Immediate Execution" profile as an "RDA Client" and abiding by the NIST OSI Implementor's Workshop implementation agreements. The Immediate Execution profile requires the following RDA functional units: Dialogue Initialization, Dialogue Termination, Transaction Management (Basic Application Context), Resource Handling, and Immediate Execution DBL. Conformance to FIPS PUB 127-2, "FIPS SQL", at the Entry SQL level or above, and support for FIPS feature #1, "Dynamic SQL", and FIPS feature #2, "Basic information schema". Support for the FIPS feature #77, "Connection management", defined above, with the following additional requirements: the CONNECT statement triggers Dialogue Initialization (R-Initialize Service) and Resource Handling (R-Open Service); the DISCONNECT statement triggers Resource Handling (R-Close Service) and Dialogue Termination (R-Terminate Service); the SET CONNECTION statement re-establishes active Resource Handling and, if necessary may trigger Dialogue Termination (R-Close Service) to make a current SQL- connection dormant; Transaction Management supports SQL-COMMIT and SQL- ROLLBACK, and satisfies the timing and semantics of an SQL-Transaction; other SQL-Statements, in the host language binding style supported by the SQL-implementation, are mapped to Immediate Execution DBL (R-ExecuteDBL Service) protocols. 85. RDA/SQL-Server. Conformance to Remote Database Access (RDA) component of FIPS PUB 146, "Government Open Systems Interconnection Profile (GOSIP)", by satisfying all of the requirements for the "Immediate Execution" profile as an "RDA Server" and abiding by the NIST OSI Implementor's Workshop implementation agreements. The Immediate Execution profile includes: Dialogue Initialization, Dialogue Termination, Transaction Management (Basic Application Context), Resource Handling, and Immediate Execution DBL. Conformance to FIPS PUB 127-2, "FIPS SQL", at the Entry SQL level or above, and support for FIPS feature #1, "Dynamic SQL", and FIPS feature #2, "Basic information schema". 86. RDA Stored Execution. Conformance to feature #84, "RDA/SQL-Client", or feature #85, "RDA/SQL-Server", defined above and, in addition, support for the RDA Stored Execution Functional Unit as specified in ISO/IEC 9579- 2 (RDA SQL Specialization), and with implementor agreements specified by the NIST OSI Implementor's Workshop. 87. RDA Cancel. Conformance to feature #84, "RDA/SQL-Client", or feature #85, "RDA/SQL-Server", defined above and, in addition, support for the RDA Cancel Functional Unit as specified in ISO/IEC 9579-2 (RDA SQL Specialization), and with implementor agreements specified by the NIST OSI Implementor's Workshop. 88. RDA Status. Conformance to feature #84, "RDA/SQL-Client", or feature #85, "RDA/SQL-Server", defined above and, in addition, support for the RDA Status Functional Unit as specified in ISO/IEC 9579-2 (RDA SQL Specialization), and with implementor agreements specified by the NIST OSI Implementor's Workshop. 89. RDA TP Application Context. Conformance to feature #84, "RDA/SQL- Client", or feature #85, "RDA/SQL-Server", defined above and, in addition, support for the RDA SQL TP Application Context as specified in ISO/IEC 9579-2 (RDA SQL Specialization), and dependent upon ISO/IEC DIS 10026 (Distributed Transaction Processing), and with Distributed Transaction Processing implementor agreements specified by the NIST OSI Implementor's Workshop. 15. FIPS documentation schema. For conformance to Intermediate SQL or to Full SQL, FIPS SQL requires that the implementation provide a special schema, the FIPS_DOCUMENTATION schema, as a system-owned schema in every catalog supported by that implementation (see Section 10.f). The FIPS_DOCUMENTATION schema has, effectively, the following schema definition: CREATE SCHEMA FIPS_DOCUMENTATION AUTHORIZATION "_SYSTEM" DEFAULT CHARACTER SET SQL_CHARACTER CREATE TABLE SQL_FEATURES ( FEATURE_ID SMALLINT PRIMARY KEY CHECK (FEATURE_ID > 0), FEATURE_NAME CHARACTER (50) NOT NULL, CLASSIFICATION CHARACTER (12) NOT NULL CHECK (CLASSIFICATION IN ('TRANSITIONAL', 'INTERMEDIATE', 'FULL', 'RDA')), IS_SUPPORTED CHARACTER (3) NOT NULL CHECK (IS_SUPPORTED IN ('YES', 'NO')), IS_VERIFIED CHARACTER (3) NOT NULL CHECK (IS_VERIFIED IN ('YES', 'NO')), FEATURE_COMMENTS VARCHAR (500) CHARACTER SET SQL_TEXT, CHECK (IS_SUPPORTED='YES' OR IS_VERIFIED='NO') ) CREATE TABLE SQL_SIZING ( SIZING_ID SMALLINT PRIMARY KEY CHECK (SIZING_ID > 0), DESCRIPTION CHARACTER (50) NOT NULL, ENTRY_VALUE INTEGER, INTERMEDIATE_VALUE INTEGER, VALUE_SUPPORTED INTEGER, SIZING_COMMENTS VARCHAR (500) CHARACTER SET SQL_TEXT ) GRANT SELECT, REFERENCES ON SQL_FEATURES TO PUBLIC WITH GRANT OPTION GRANT SELECT, REFERENCES ON SQL_SIZING TO PUBLIC WITH GRANT OPTION 15.1 SQL_Features table. The SQL_FEATURES table shall consist of exactly one row for each FIPS SQL feature defined in Section 14 of this standard. The FEATURE_ID and FEATURE_NAME columns identify the feature by the integer and name assigned to it in Section 14. The CLASSIFICATION column identifies the FIPS conformance level in which the feature first becomes required; all features in Subsection 14.1 are classified as TRANSITIONAL, all features in Subsection 14.2 are classified as INTERMEDIATE, all features in Subsection 14.3 are classified as FULL, and all features in Subsection 14.4 are classified as RDA. The IS_SUPPORTED column is 'YES' if an implementation fully supports that feature when data in the identified catalog is accessed through that implementation, and is 'NO' if the implementation does not fully support the feature when accessing that catalog. If full support for the feature has been verified by testing with the NIST SQL Test Suite, then the IS_VERIFIED column is 'YES'; otherwise, the IS_VERIFIED column is 'NO'. If the IS_VERIFIED column is 'YES', then the vendor of the implementation shall have passed, either by self testing or by witnessed testing, all tests in the then current version of the NIST SQL Test Suite that apply to that feature. The FEATURE_COMMENTS column is intended for any vendor comments pertinent to the identified FIPS SQL feature. 15.2 SQL_Sizing table. The SQL_SIZING table shall consist of exactly one row for each FIPS SQL database construct defined in Section 16.6 of this standard. The SIZING_ID and DESCRIPTION columns identify the database construct by the integer and description assigned to it in Section 16.6. The ENTRY_VALUE column is equal to the default Entry SQL value defined for that construct by FIPS SQL in Section 16.6, with "*" converted to a Null value. The INTERMEDIATE_VALUE column is equal to the default Intermediate SQL value defined for that construct by FIPS SQL in Section 16.6, with "*" converted to a Null value. The VALUE_SUPPORTED column indicates a value for the construct that is supported by an implementation when data in the identified catalog is accessed through that implementation; if this value is Null, then there is no explicit restriction on the size of that construct. A user must be able to depend upon these values when executing SQL-statements against data in the catalog. If a given catalog spans multiple SQL-Server implementations, then the VALUE_SUPPORTED shall be valid in all of them. It is important to recognize that FIPS sizing defaults are not requirements for conformance to FIPS SQL; instead, they identify a default value that is assumed to be specified if a Federal SQL procurement is silent on that topic. For this reason, the VALUE_SUPPORTED may sometimes be less than the FIPS default for the ENTRY_VALUE or the INTERMEDIATE_VALUE, even for a FIPS SQL conforming implementation. The SIZING_COMMENTS column is intended for any vendor comments pertinent to the identified FIPS SQL database construct. 16. Special Procurement Considerations. FIPS SQL includes various alternatives for interfacing to programming languages, allows the additional specification of optional FIPS SQL features, and does not specify any minimum requirements for the size or number of occurrences of database constructs. Any invocation of this standard in a procurement should indicate the programming languages to which it interfaces, whether Modules, Embedded SQL, or both are required for each language, which base level of FIPS SQL conformance is a mandatory requirement, which optional features are desirable, and what the sizing and occurrence requirements are. Any use of this standard in a broader database management system (DBMS) procurement should be accompanied with functional requirements for other DBMS components and facilities. 16.1 Procurement wording. References to this standard in a procurement should be accompanied with appropriate solicitation wording. Information on Acquisition wording is in Section 11.2 of this standard, and information on Validation wording is in Section 11.4. 16.2 Programming language interfaces. References to this standard in a procurement should indicate which FIPS programming languages (e.g. Ada, C, COBOL, FORTRAN, MUMPS, or Pascal) are to be supported for language interface. Failure to make this indication means that support for any one of these languages satisfies the FIPS SQL requirement. 16.3 Style of language interface. References to this standard in a procurement should indicate, for each programming language identified above, whether the language interface is to support Modules, Embedded SQL, or both. Failure to make this indication means that support for any one interface style satisfies the FIPS SQL requirement. 16.4 Optional Features. References to this standard in a procurement should indicate which FIPS SQL conformance level is a mandatory requirement. Valid base level conformance alternatives are: Entry SQL, Transitional SQL, Intermediate SQL, or Full SQL. In addition, procurements may specify desirable features beyond that level (see Section 14). Implementations that support the identified desirable features may be rated higher in the procurement evaluation process. Failure to specify a mandatory base level of conformance means that only Entry SQL is required. Under certain circumstances, one or more FIPS SQL features above the base level of conformance may be specified as mandatory requirements in a Federal procurement. Usually such features will only be specified as desirable. Procurements that seek effective competition from a number of different vendors should be very careful in distinguishing between mandatory and desirable features. Depending upon agency requirements and vendor cooperation, the NIST SQL Test Suite may identify and test various SQL "profiles" for specific purposes. For example, an "interoperability profile" might include some features from Transitional SQL as well as Connection management (feature #77) from Full SQL and Client/Server capabilities (features #84 and #85) from RDA, or a "read only profile" might include only selected data manipulation statements without any schema definition or schema manipulation. See the most recent version of the NIST SQL Test Suite for possible specification of such profiles. As always, all syntactic extensions beyond Entry SQL, Intermediate SQL, or Full SQL shall be appropriately flagged by the SQL Flagger. 16.5 Interactive Direct SQL. References to this standard in a procurement should indicate whether or not "Interactive Direct SQL" is required. If it is required, then in order to satisfy the requirement, an implementation shall provide interactive access to the database, using any , as specified in Clause 20 of ANSI X3.135-1992, and subject to any leveling or FIPS SQL feature requirements specified in Section 16.4 above. Failure to indicate an explicit requirement for "Interactive Direct SQL" in a procurement means that this interface alternative is not required. Additional FIPS requirements for Interactive Direct SQL are as follows: if a statement raises an exception condition, then the system shall display a message indicating that the statement failed, giving a textual description of the failure; if a statement raises a completion condition that is a "warning" or "no data", then the system shall display a message indicating that the statement completed, giving a textual description of the "warning" or "no data"; an implementation shall provide some implementation-defined symbol for displaying null values and, for character string values, this symbol must be distinguishable from a value of all s. 16.6 Sizing for database constructs. References to this standard in a procurement should indicate minimum requirements for the precision, size, or number of occurrences of database constructs. Failure to make this indication means that the Entry Values detailed below are by default the minimum requirements for Entry SQL or Transitional SQL procurements and the Intermediate Values detailed below are by default the minimum requirements for Intermediate SQL or Full SQL procurements. Sizing Entry Intermediate Id Description Value Value 1. Length of an identifier 18 128 2 CHARACTER max length 240 1000 3. CHARACTER VARYING max length 254 1000 4. BIT max length in bits * 8000 5. BIT VARYING max length in bits * 8000 6. NATIONAL CHARACTER max length * 500 7. NATIONAL CHAR VARYING max length * 500 8. NUMERIC decimal precision 15 15 9. DECIMAL decimal precision 15 15 10. INTEGER decimal precision 9 * 11. INTEGER binary precision * 31 12. SMALLINT decimal precision 4 * 13. SMALLINT binary precision * 15 14. FLOAT binary mantissa precision 20 47 15. FLOAT binary exponent precision * 9 16. REAL binary mantissa precision 20 23 17. REAL binary exponent precision * 7 18. DOUBLE PRECISION binary mantissa precision 30 47 19. DOUBLE PRECISION binary exponent precision * 9 20. TIME decimal fractional second precision * 0 21. TIMESTAMP decimal fractional second precision * 6 22. INTERVAL decimal fractional second precision * 6 23. INTERVAL decimal leading field precision * 7 24. Columns in a table 100 250 25. Values in an INSERT statement 100 250 26. Set clauses in UPDATE statement 20 250 27. Length of a row (see Note 1) 2000 8000 28. Columns in UNIQUE constraint 6 15 29. Length of UNIQUE columns (Note 1) 120 750 30. Columns in GROUP BY column list 6 15 31. Length of GROUP BY column list (Note 1) 120 750 32. Sort items in ORDER BY clause 6 15 33. Length of ORDER BY column list (Note 1) 120 750 34. Referencing columns in FOREIGN KEY 6 15 35. Length of FOREIGN KEY column list (Note 1) 120 750 36. Table references in an SQL statement (Note 3) 15 50 37. Cursors simultaneously open 10 100 38. WHEN clauses in a CASE expression * 50 39. Columns in a named columns JOIN * 15 40. Length of JOIN column list (Note 1) * 750 41. Items in a SELECT list 100 250 42. Length of SQL (Note 2) * 30000 43. Length of (Note 2) * 4000 44. Length of (Note 2) * 4000 45. Occurrences in an ALLOCATE DESCRIPTOR * 100 46. Default occurrences in ALLOCATE DESCRIPTOR * 100 Note 1: The length of a collection of columns is conservatively estimated to be no larger than the sum of: twice the number of columns, OCTET_LENGTH of each character or bit column (see Subclause 6.6, "", of X3.135-1992), decimal precision plus 1 of each exact numeric column, binary precision divided by 4 plus 1 of each approximate numeric column, 10 for each DATE column, 8 for each TIME column, 14 for each TIME WITH TIME ZONE column, 19 for each TIMESTAMP column, 25 for each TIMESTAMP WITH TIME ZONE column, and 20 for each INTERVAL column. In addition, if any DATE, TIME, TIMESTAMP, or INTERVAL column has a non-zero fractional seconds precision, then add that precision plus 1 to the length of the column. Note 2: The length of an SQL statement is defined to be the result of applying the OCTET_LENGTH function (see Subclause 6.6, "", of X3.135-1992) to the SQL statement with the SQL statement considered to be an instance of a CHARACTER VARYING data type. Note 3: The number of table references in an SQL statement is the sum of: the number of views and base tables named in the statement, the number of underlying views and tables (see Subclause 4.9, "Tables", of X3.135-1992) for each derived table or cursor, and the number of s (either given in the SQL statement or contained in some view named in the SQL statement) not directly associated with a named table or view. Some applications may have requirements for CHARACTER VARYING or BIT VARYING data types with lengths much longer than the Entry SQL or Intermediate SQL values specified above. This is particularly true for applications that need to manage large Audio, Graphics, Text, or Video objects. Some applications have requirements for Audio, Text, or Graphics objects in excess of 2-3 million bytes, or Video objects in excess of multiple gigabytes. Implementations that provide such data types often impose severe restrictions in how these very large objects can be referenced in SQL definitions and statements. For example, a very long CHARACTER VARYING data type may not be allowed to participate in a PRIMARY KEY, a UNIQUE constraint, a REFERENTIAL constraint, a , a GROUP BY or HAVING clause, or an ORDER BY in a cursor definition. Applications that stay within the limits specified above should not encounter any unexpected restrictions in how these constructs can be used or referenced in SQL language. Some implementations address user requirements for very large objects, with a minimum number of restrictions, by allowing arbitrarily large maximum length declarations for CHARACTER VARYING or BIT VARYING, with an internal representation using some sort of indirect addressing mechanism. In this way they can keep the physical length of the row in which the object is represented less than the physical page size of the operating system environment, often necessary for lock management, while at the same time meet user requirements for storing, retrieving, and managing large objects. SQL procurements that anticipate requirements for very long CHARACTER VARYING or BIT VARYING data types should be very explicit in procurement specifications about additional requirements for how these large data types interface to external processors or how they need to be processed by SQL language. 16.7 Character set support. In ANSI Entry SQL, the set of character values for the character data types and the collation of characters in those data types are both implementation-defined. References to the Entry SQL level of this standard in a procurement should indicate any additional character data requirements. Failure to indicate specific character set requirements for the Entry SQL or Transitional SQL options of FIPS SQL means that support for representation of the 95-character graphic subset of ASCII (FIPS PUB 1-2), in an implementation-defined collating sequence, is by default the minimum requirement. In Intermediate SQL, various SQL statements may use a to identify, by name, one or more different implementation- defined character sets. In addition, users may define new character sets with a . For conformance to the Intermediate SQL or Full SQL options of FIPS SQL, it is required (see Section 10.e) that any be able to specify any one of the following character set names: SQL_CHARACTER, ASCII_GRAPHIC, LATIN1, ASCII_FULL, or SQL_TEXT. -- If SQL_CHARACTER is specified, then the resulting character set consists of the 83 s as specified in Subclause 5.1 of X3.135-1992. It consists of the 52 uppercase and lowercase simple latin characters, 10 digits, and 21 s, including: , , , , , , , , , , , , , , , , , , , , and . The 83 characters specified as s are all included in the ISO International Reference Version (IRV) characters specified in ISO 646:1991. The characters in IRV are included in many other international character set definitions. In addition, 82 of these characters (all except ) are in the most stable subset of IRV that, by ISO convention, is included in every latin-based ISO standard set of characters. As far as can be determined, is included in every character set that enjoys wide use in either the United States or Western Europe. Thus the SQL_CHARACTER repertoire is the most universal of the character sets named in this FIPS. The collation and form-of-use of SQL_CHARACTER is implementation-defined. -- If ASCII_GRAPHIC is specified, then the resulting character set consists of the 95-character graphic subset of ASCII as specified in FIPS PUB 1-2. The form-of-use is that corresponding to the coded representation of each character by a single byte (possibly 7-bit, 8-bit, or other), with no designation escape sequences for other character sets. The default collating sequence is that corresponding to the bit combinations defined by FIPS PUB 1-2. The ASCII_GRAPHIC character set is a superset of the s. The 12 characters included in ASCII_GRAPHIC that are not s are, in collation order: Exclamation mark !, Number sign #, Dollar sign $, Commercial at @, Left square bracket [, Reverse solidus \, Right square bracket ], Circumflex accent ^, Grave accent `, Left curly bracket {, Right curly bracket }, and Tilde ~. Of these 12 characters, only "!" is in the most stable subset of ISO 646, whereas "#" competes with the British pound sign, "$" competes with the international currency symbol, and the others occupy positions in ISO 646 that are reserved for national or application-oriented use. However, all are the default IRV values specified in ISO 646 when no national or application-specific version is explicitly specified. -- If LATIN1 is specified, then the resulting character set consists of the 191 graphic characters defined in ISO 8859-1. The form-of-use is that corresponding to the coded representation of each character by a single 8- bit byte, with no designation escape sequences for other character sets. The default collating sequence is that corresponding to the bit combinations defined by ISO 8859-1. The LATIN1 character set is a superset of ASCII_GRAPHIC and, when restricted to the ASCII_GRAPHIC characters, produces the same collation as ASCII_GRAPHIC. LATIN1 consists of all characters commonly used in the following languages: Danish, Dutch, English, Faeroese, Finnish, French, German, Icelandic, Irish, Italian, Norwegian, Portuguese, Spanish, and Swedish. It also includes the following special symbols, in collation order: No-break space, Inverted exclamation mark, Cent sign, Pound sign, Currency sign, Yen sign, Broken bar, Paragraph sign, Diaeresis, Copyright sign, Feminine ordinal indicator, Left angle quotation mark, Not sign, Soft hyphen, Registered trade mark sign, Macron, Degree sign, Plus-minus sign, Superscript two, Superscript three, Acute accent, Micro sign, Pilcrow sign, Middle dot, Cedilla, Superscript one, Masculine ordinal indicator, Right angle quotation mark, Fraction one quarter, Fraction one half, Fraction three quarters, and Inverted question mark. Other characters include the Multiplication sign and the Division sign. In LATIN1, all ASCII_GRAPHIC characters precede the non-ASCII_GRAPHIC characters in the default collation, followed by the special symbols, followed by the accented capital letters, followed by the accented small letters. The Multiplication sign is in the middle of the accented capital letters and the Division sign is in the middle of the accented small letters. LATIN1 is subject to the following conformance requirements, as specified in Clause 3, "Conformance", of ISO 8859-1: a. A set of graphic characters is in conformance with Part 1 of ISO 8859 if it comprises all graphic characters specified therein to the exclusion of any other and if their coded representations are those specified by Part 1 of ISO 8859. b. Equipment claimed to implement Part 1 of ISO 8859 shall implement all 191 characters. -- If ASCII_FULL is specified, then the resulting character set consists of all 256 characters of 8-bit ASCII, as specified in ANSI/ISO 4873 and ANSI/ISO 8859-1, including all control characters and all graphic characters. The form-of-use is that corresponding to the coded representation of each character by a single 8-bit byte, with no designation escape sequences for other character sets. The default collating sequence is that corresponding to the bit combinations defined by 8-bit ASCII. The ASCII_FULL character set is a superset of LATIN1 and, when restricted to the LATIN1 characters, produces the same collation and form-of-use. -- If SQL_TEXT is specified, then the resulting character set consists of the s and all characters that are in other character sets supported by the implementation, as specified in Syntax Rule 11 of Subclause 6.1, "", of X3.135-1992. Thus, in FIPS SQL, the SQL_TEXT character set must be a superset of ASCII_FULL. The collation and form-of-use of SQL_TEXT is implementation-defined. The character sets SQL_CHARACTER, ASCII_GRAPHIC, LATIN1, and ASCII_FULL have both a "floor" and "ceiling" requirement to consist of exactly the characters specified. Any character data type associated with one of these character sets has an implied integrity constraint limiting a value of the data type to be a character string consisting only of characters from the specified character set. The SQL_TEXT character set has a similar "floor" requirement in that it must contain all of the ASCII_FULL characters; however, SQL_TEXT does not have a "ceiling" requirement. Requirements for FIPS Intermediate SQL or FIPS Full SQL in an SQL procurement should indicate any additional character data requirements. Failure to indicate specific character set, collation, conversion, or translation requirements means that support for SQL_CHARACTER, ASCII_GRAPHIC, LATIN1, ASCII_FULL, and SQL_TEXT, as indicated above, are the only requirements. 16.8 DBMS procurement. Database software is normally purchased as a complete package called a database management system (DBMS). A DBMS is an implementation of one or more data models (e.g. the relational model, or an object model), together with other components, features, or data interfaces for efficient data administration. These additional facilities are not specified by this standard, so each procurement should itself specify the functional requirements of each additional feature desired. Additional facilities most often contained in a DBMS package include: special- purpose data types (e.g. multimedia types or spatial data types), user- defined data types, object management, database import and export tools, backup and recovery tools, performance optimization tools, audit trails, networking, data dictionary, data storage specification, natural language query, report writer, graphical user interface, query by forms, CASE tools, or application development tools. Emerging specifications for an expanded SQL database language in ANSI and ISO standardization bodies may result in future standardization for some of these facilities; others may always remain implementation-defined. The following features have "preliminary" syntax and semantics available in Working Draft form as part of an on-going ANSI and ISO/IEC standardization effort for further development of the SQL language. Generic support for some of these features may be specified as functional requirements, or as desirable elements, in a DBMS procurement, but any such procurement specification should be written knowing that the preliminary ANSI and ISO specifications are subject to substantial evolution or reconsideration before adoption in any future SQL standard. As these facilities evolve over the next several years and work their way through the various levels of the ANSI and ISO/IEC standardization process (i.e. CD and DIS), then more confidence can be put into referencing them in DBMS procurements. Features specified in preliminary form include: 1. SQL Call Level Interface (SQL/CLI). A new application program interface to SQL, initially specified for COBOL and C, that allows system calls to SQL services without the need for Embedded SQL preprocessing or Module language compilation. This interface would allow development of database client applications that could be linked to different SQL server implementations at execution time. This interface is a requirement for third-party software developers who wish only to distribute binary code to their customers. An ISO/IEC Working Draft specification was available in early 1993, with final standardization projected for 1994-1995. 2. Persistent SQL Modules. The ability to define packages of SQL procedures that "live" in the schema just like any other defined SQL object. Modules may be stored at remote nodes in a conforming communications network with only a remote procedure call needed to invoke a desired action. Persistent SQL Modules allow optimization of stored procedures at multiple sites in a communications network, thereby reducing both processing time and communications volume. An ISO/IEC Working Draft specification was available in early 1993, with final standardization projected for 1994-1995. 3. Abstract data type (ADT). A facility for user-defined data types, both structures and operations, using previously defined abstract data types and the standardized base types as primitives. 4. Dynamic assertions. Support for integrity constraints that are triggered by specific database actions, such as: after update, before insertion, or constraints based on comparing old and new values of a given attribute. Assertions are "dynamic" in that they may reference before and after images of the database or may depend upon temporary data values that only exist at the time the invoking statement is executed. 5. Dynamic triggers. Support for triggering a sequence of database actions based on a specific database action, such as after delete, thereby supporting the object notion of encapsulation. Assertions and Triggers make it possible for object self-management to be fully specified in a database schema, with increased opportunity for performance optimization by the underlying database management system. 6. Object identity. A persistent object-identifier created for each instance of an object data type that is independent of the object's name, structure, or location, and that persists over time to forever avoid confusion with the identity of another object. 7. SQL functions. A function, defined completely in SQL, that helps to define the behavior of abstract data types. Constructor and destructor functions create or destroy new ADT instances, and actor functions read or modify ADT attributes. 8. External function call. A function whose interface specification is defined in an SQL schema, but with the content of the function written in some other programming language (e.g Fortran, Ada, or C++). USAGE privileges on external functions are managed by SQL GRANT and REVOKE statements. 9. Subtypes and inheritance. An abstraction mechanism that allows classes of abstract data types to be related hierarchically. Inheritance allows ADT classes to share properties and operations with other classes to allow more accurate and succinct modeling of applications. 10. Polymorphic functions. The ability to invoke an operation on any of several different objects and have each object determine how to respond, during execution, by applying rules for disambiguating names and data types. 11. Program control structures. Support for defining computationally complete SQL procedures by allowing sequences of SQL statements, looping, branching, and other flow of control statements, and dynamic exception handling on a per-procedure basis. 12. Parameterized types. The ability to define "type families", with a new data type for each value of an input parameter. Such type templates may also be nested, thereby greatly simplifying the definition of some complex nested structures. 1 Generator types. Support for type generators such as LIST, ARRAY, and SET, with SQL syntax, but harmonized with emerging specifications for generator and parameterized types in ISO/IEC programming language committees, i.e. ISO/IEC CD 11404, "Common language independent data types" (CLID). 14. Recursive expressions. Support for SQL expressions of indefinite, recursive depth, such as those arising out of "bill-of-materials" part's hierarchies. 15. Existential and universal quantifiers. Support for more mathematically based SQL expressions involving the existential and universal quantifiers prevalent in 3-valued predicate logic. 16. SIMILAR predicate. A facility for pattern matching in bit strings and character strings that allows construction of regular expressions equivalent to regular expressions in the POSIX standard, ISO/IEC 9945. 17. Multiple null states. A facility that allows user definitions for an arbitrary number of application specific Null values, such as "Unknown", "Missing", "Not Applicable", "Pending", etc. Each such Null value would have a different representation in the database so that they could be distinguished by s during retrieval or update. 18. Roles and data security. An enhanced facility for database security management that builds upon the existing Grant and Revoke definitions. It extends the security model to include named "roles" in addition to schema objects, actions, and users. With roles defined as a nested collection of authorized actions on schema objects, security administration becomes more efficient and manageable. 19. Savepoints and subtransactions. A subtransaction is a portion of a transaction that is marked for potential rollback without affecting the other parts of the transaction. By setting and releasing savepoints, an application programmer is able to recover more easily from failed subtransactions, thereby leading to more efficient code. 20. Distributed database management. Distributed database management implies totally integrated, distributed data, under the coordinated control of multiple heterogeneous database management systems. Because it requires cooperating concurrency control managers, "standardized" distributed database management may be some time away; however, emerging standards for one-phase and two-phase commit protocols (see ISO/IEC 10026) allow individual implementations to access remote data and present a distributed view to their application programs. 21. Database export and import. Database export provides utilities for unloading a database definition and the data contents of a database into an external form, representable on various media, for the purpose of later automated re-generation. Database import provides utilities for loading a database definition and contents from an external source. Evolving specifications hope to make a database exported from any conforming SQL implementation importable into any other conforming SQL implementation. 22. Cursor sensitivity. The ability to specify a SENSITIVE option on a cursor definition, so that the cursor will always "see" concurrent modifications, in the same transaction, made to the underlying tables of the cursor definition. This provides a measure of predictability, but possibly with performance implications. 23. Asynchronous DML. Support for being able to name SQL statements so that other work can be done while they are executing. The names provide a mechanism for querying the status of outstanding statements. A NIST special publication is under development that will discuss potential future directions for Database Language SQL. All of the above features are discussed further in that document. 16.9 DBMS performance. DBMS performance is often a critical factor in a DBMS procurement. This standard is silent on the topic of performance. The NIST SQL Test Suite (see Section 11.4) also makes no attempt to test the performance aspects of a conforming system. Whenever performance requirements are known in advance, they may be included as an integral part of the procurement specification. A DBMS may also provide additional data structures, such as indices, or software, such as query optimizers, to enhance performance. User requirements for monitoring database activity or tools for tuning database performance should be specified explicitly. Sometimes a procurement will specify a performance benchmark with agency supplied, or publicly available, data and applications. If a procurement specifies benchmarking, then the agency should consider that results of a benchmark for conforming interfaces may not be comparable to results of a benchmark for nonconforming interfaces. Consequently, procurement terminology should specify that performance benchmarks shall be performed with the actual interfaces to be used with agency applications. When modification of the actual interface is required after a benchmark, for example in the case of a delayed validation where correction of nonconformities within a year is specified, procurement terminology should specify that the performance benchmark be achieved with the corrected interface. 16.10 Database security. Some database management systems must operate in a highly secure environment that requires "trusted" database access control beyond the GRANT and REVOKE privilege facilities and the VIEW definition capabilities specified in this standard. Procurements for systems that operate in these environments should include explicit additional requirements that shall be supported. FIPS SQL contains specifications for some Discretionary Access Control (DAC) mechanisms, but not Mandatory Access Control (MAC) nor the associated security labels. For a definition of DAC or MAC, refer to "Trusted Database Management System Interpretation of the Trusted Computer System Evaluation Criteria" (NCSC-TG-021 Version 1, "Lavender Book"), National Computer Security Center, April 1991. 16.11 System integration. In many cases a database or a database management system must be integrated with other information processing systems operating in the same environment. Examples of other systems might include: the operating system, document processing systems, engineering CAD/CAM systems, graphics systems, an information resource dictionary system, statistical analysis systems, a transaction processing system, or an artificial intelligence system. In addition, distributed data under the control of different vendor's database management systems may require integration into a coordinated global view through remote database access or open distributed processing. Except for bindings to programming languages (see Clause 12 and Clause 19 of ANSI X3.135-1992) and the Connection management statements (see Clause 15 of ANSI X3.135-1992), such integration is beyond the scope of this standard and, if desired, must be specified explicitly as part of procurement requirements. 17. Where to Obtain Copies. Copies of this publication are for sale by the National Technical Information Service, U.S. Department of Commerce, Springfield, VA 22161, telephone 703-487-4650. (Sale of the included specification document, ANSI X3.135-1992, is by arrangement with the American National Standards Institute.) When ordering, refer to Federal Information Processing Standards Publication 127-2 (FIPS PUB 127-2), Database Language SQL. Payment may be made by check, money order, or deposit account.