Creating a schema by using the schema processor
Schemas provide a logical classification of objects in the database. You can use the schema processor to create a schema.
About this task
Creating a schema by using the CREATE SCHEMA statement is also supported for compliance testing.
CREATE
SCHEMA statements cannot be embedded in a host program or executed
interactively. To process the CREATE SCHEMA statement, you must use
the schema processor. The ability to process schema definitions is
provided for conformance to ISO/ANSI standards. The result of processing
a schema definition is identical to the result of executing the SQL
statements without a schema definition.
Outside of the schema processor, the order of statements is important. They must be arranged so that all referenced objects have been previously created. This restriction is relaxed when the statements are processed by the schema processor if the object table is created within the same CREATE SCHEMA. The requirement that all referenced objects have been previously created is not checked until all of the statements have been processed. For example, within the context of the schema processor, you can define a constraint that references a table that does not exist yet or GRANT an authorization on a table that does not exist yet.
Procedure
To create a schema:
- Write a CREATE SCHEMA statement.
- Use the schema processor to execute the statement.
Example
The following example shows schema processor input that includes the definition of a schema.
CREATE SCHEMA AUTHORIZATION SMITH
CREATE TABLE TESTSTUFF
(TESTNO CHAR(4),
RESULT CHAR(4),
TESTTYPE CHAR(3))
CREATE TABLE STAFF
(EMPNUM CHAR(3) NOT NULL,
EMPNAME CHAR(20),
GRADE DECIMAL(4),
CITY CHAR(15))
CREATE VIEW STAFFV1
AS SELECT * FROM STAFF
WHERE GRADE >= 12
GRANT INSERT ON TESTSTUFF TO PUBLIC
GRANT ALL PRIVILEGES ON STAFF
TO PUBLIC