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:
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
Sizing Entry Interm.
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
FIPS PUB 127-2
FEDERAL INFORMATION
PROCESSING STANDARDS PUBLICATION
1993 June 2
U.S. DEPARTMENT OF COMMERCE/National Institute of
Standards and Technology
This office has a record of your interest in receiving changes to
the above FIPS PUB. The change(s) indicated below have been
provided by the Maintenance Agency for this publication and will
be included in the next published revision to this FIPS PUB.
Questions or requests for additional information should be
addressed to the Maintenance Agency:
Department Of Commerce
National Bureau Of Standards
Institute for Computer Sciences and Technology
Washington, D.C. 20234
Discussion
FIPS PUB 127-2 contains an inconsistency in the specification of FIPS Transitional SQL. The
inconsistency is that
This FIPS SQL problem was discussed at a recent meeting of the ANSI/X3 technical committee
responsible for standardization of Database Language SQL in the United States. That committee
recommended that FIPS Transitional SQL requirements be modified to require support for the
content of required Information Schema tables in a special FIPS schema different from the
INFORMATION_SCHEMA and having shortened column names. NIST accepts this
recommendation and will design the NIST SQL Test Suite for Transitional SQL to test for the
existence of appropriate views in a special INFO_SCHEM schema.
The ANSI SQL committee also recommended that the following name shortening algorithm be
used
for consistency and convenience in being able to remember the shortened names:
Applications that are designed to depend upon these shortened names may be ported to systems
that
support longer names or may outlive the 18 character name restriction on identifiers. Thus FIPS
SQL will require that these special views in the INFO_SCHEM schema be supported by all
implementations of FIPS Transitional SQL, including implementations that also support these
tables
in the INFORMATION_SCHEMA without difficulty. This additional FIPS Transitional SQL
requirement is the small penalty that we must all pay for letting NIST make such a mistake in the
first place!
This required feature of FIPS Transitional SQL is marked as a "deprecated" feature. The term
"deprecated" 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.
Application programs written to reference tables and columns in the INFO_SCHEM can be
modified
automatically to execute in any Intermediate SQL processing environment by substituting
"INFORMATION_SCHEMA" for "INFO_SCHEM" in any schema reference and by expanding
the
seven shortened words identified above in any column reference that is explicitly or implicitly
qualified by an INFO_SCHEM schema name.
A second alternative to support programs written to reference tables and columns in the
INFO_SCHEM in any Intermediate SQL processing environment is for the Database
Administrator
to define INFO_SCHEM directly as specified below. This alternative should add only a minimal
runtime performance cost, and will avoid making changes to any individual application program.
Add the following paragraphs to Section 10, "Specifications", of FIPS PUB 127-2:
For conformance to Transitional SQL, FIPS SQL requires that the implementation provide a
special
schema, the INFO_SCHEM schema, as a system-owned schema in every catalog supported by
that
implementation. This is a deprecated feature in FIPS 127-2. The INFO_SCHEM schema has,
effectively, the following schema definition:
NIST accepts this recommendation, and will apply it to all column names in an INFO_SCHEM
view
definition for all Information Schema tables required by Transitional SQL.
DEFAULT ---> DEF CHARACTER ---> CHAR MAXIMUM ---> MAX PRECISION ---> PREC CATALOG ---> CAT CHEMA ---> SCHEM NUMERIC ---> NUM
FIPS SQL Modification
CREATE SCHEMA INFO_SCHEM
AUTHORIZATION "_SYSTEM"
DEFAULT CHARACTER SET SQL_TEXT
CREATE VIEW SCHEMATA
( CAT_NAME,
SCHEM_NAME,
SCHEM_OWNER,
DEF_CHAR_SET_CAT,
DEF_CHAR_SET_SCHEM,
DEF_CHAR_SET_NAME
)
AS SELECT
CATALOG_NAME, SCHEMA_NAME, SCHEMA_OWNER,
DEFAULT_CHARACTER_SET_CATALOG,
DEFAULT_CHARACTER_SET_SCHEMA,
DEFAULT_CHARACTER_SET_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
CREATE VIEW TABLES
( TABLE_CAT,
TABLE_SCHEM,
TABLE_NAME,
TABLE_TYPE
)
AS SELECT
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM INFORMATION_SCHEMA.TABLES
CREATE VIEW VIEWS
( TABLE_CAT,
TABLE_SCHEM,
TABLE_NAME,
VIEW_DEFINITION,
CHECK_OPTION,
IS_UPDATABLE
)
AS SELECT
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, VIEW_DEFINITION,
CHECK_OPTION, IS_UPDATABLE
FROM INFORMATION_SCHEMA.VIEWS
CREATE VIEW COLUMNS
( TABLE_CAT,
TABLE_SCHEM,
TABLE_NAME,
COLUMN_NAME,
ORDINAL_POSITION,
COLUMN_DEF,
IS_NULLABLE,
DATA_TYPE,
CHAR_MAX_LENGTH,
CHAR_OCTET_LENGTH,
NUM_PREC,
NUM_PREC_RADIX,
NUM_SCALE,
DATETIME_PREC,
INTERVAL_CODE,
INTERVAL_PREC,
CHAR_SET_CAT,
CHAR_SET_SCHEM,
CHAR_SET_NAME,
COLLATION_CAT,
COLLATION_SCHEM,
COLLATION_NAME,
DOMAIN_CAT,
DOMAIN_SCHEM,
DOMAIN_NAME
)
AS SELECT
TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION, INTERVAL_CODE, INTERVAL_PRECISION,
CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA,
CHARACTER_SET_NAME,
COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME,
DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
CREATE VIEW TABLE_PRIVILEGES
( GRANTOR,
GRANTEE,
TABLE_CAT,
TABLE_SCHEM,
TABLE_NAME,
PRIVILEGE_TYPE,
IS_GRANTABLE
)
AS SELECT
GRANTOR, GRANTEE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
PRIVILEGE_TYPE, IS_GRANTABLE
FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES
CREATE VIEW COLUMN_PRIVILEGES
( GRANTOR,
GRANTEE,
TABLE_CAT,
TABLE_SCHEM,
TABLE_NAME,
COLUMN_NAME,
PRIVILEGE_TYPE,
IS_GRANTABLE
)
AS SELECT
GRANTOR, GRANTEE, TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME,
COLUMN_NAME, PRIVILEGE_TYPE, IS_GRANTABLE
FROM INFORMATION_SCHEMA.COLUMN_PRIVILEGES
CREATE VIEW USAGE_PRIVILEGES
( GRANTOR,
GRANTEE,
OBJECT_CAT,
OBJECT_SCHEM,
OBJECT_NAME,
OBJECT_TYPE,
PRIVILEGE_TYPE,
IS_GRANTABLE
)
AS SELECT
GRANTOR, GRANTEE, OBJECT_CATALOG, OBJECT_SCHEMA, OBJECT_NAME,
OBJECT_TYPE, PRIVILEGE_TYPE, IS_GRANTABLE
FROM INFORMATION_SCHEMA.USAGE_PRIVILEGES
GRANT SELECT, REFERENCES ON SCHEMATA TO PUBLIC WITH GRANT OPTION
GRANT SELECT, REFERENCES ON TABLES TO PUBLIC WITH GRANT OPTION
GRANT SELECT, REFERENCES ON VIEWS TO PUBLIC WITH GRANT OPTION
GRANT SELECT, REFERENCES ON COLUMNS TO PUBLIC WITH GRANT OPTION
GRANT SELECT, REFERENCES ON TABLE_PRIVILEGES TO PUBLIC WITH GRANT
OPTION
GRANT SELECT, REFERENCES ON COLUMN_PRIVILEGES TO PUBLIC WITH
GRANT OPTION
GRANT SELECT, REFERENCES ON USAGE_PRIVILEGES TO PUBLIC WITH GRANT
OPTION
Note: The COLUMNS view defined above anticipates an SQL Amendment that is
currently being processed
by ANSI/X3. That SQL correction, when finally approved, will add the
INTERVAL_CODE and
INTERVAL_PRECISION columns to the COLUMNS view in the
INFORMATION_SCHEMA.
These two columns were mistakenly omitted from ANSI X3.135-1992 as originally
published.
Home Page