Creating a schema by using the schema processor
You can use the schema processor (DSNHSP) to create a schema and objects that it contains.
About this task
The ability to process
schema definitions is provided for conformance to ISO/ANSI standards. 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 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, complete the following steps:
- Write a CREATE SCHEMA statement.
- Use the schema processor (DSNHSP) to execute the statement. You can use the sample JCL statement that is provided in member DSNTEJ1S of the SDSNSAMP library. For more information, see Processing schema definitions.
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