A schema file specifies an authorization ID and a list of table, view, and privilege definitions using the syntax of the CREATE TABLE, CREATE VIEW, and GRANT statements. The SCHEMA command reads and processes the statements from a schema file.
Table 7. SCHEMA Command Syntax
Format:
(1)
>>--------SCHEMA INFILE--(--ddname----| option-c |--)----------->
>-----+---------------------+----------------------------------><
'-IN--(dbspace_name)--'
Notes:
option-c
|--+---------------------------+---+----------------------------------+->
| .-2000--. | | .-REWIND---. |
'-BLKSZ--(--+-size--+---)---' | .-(TAPE)--+-NOREWIND-+--. |
'-PDEV--+-(DASD)----------------+--'
>---------------------------------------------------------------|
|
| Example:
SCHEMA INFILE(IN1 BLKSZ(800)) |
| Authorization:
You must be connected as the AUTHORIZATION ID specified in the CREATE SCHEMA statement. |
Alternatively, SCHEMA can read its input from SYSIPT by using a READ MEMBER. You use the READ MEMBER NOCONT option to properly close the SYSIPT file. An example of using READ MEMBER with NOCONT is:
SCHEMA INFILE(SYSIPT) READ MEMBER schema-member (NOCONT
If you do not specify the NOCONT option, the database manager reads in the SYSIPT records following the READ MEMBER statement as part of the SCHEMA file; then SYSIPT can provide additional input after the READ MEMBER statement.
in DB2 Server for VM: this is the name of the sequential input file. It must have records with a fixed length of 80 characters. The file characteristics specified in the FILEDEF command or the default FILEDEF options are the source of the input record definition information for the Database Services Utility. Do not specify SYSIN or SYSPRINT as the ddname.
Table 8. Contents of the Schema (in a Sequential Input File)
Format:
.-;-.
>>-CREATE SCHEMA AUTHORIZATION--authorization_id--+---+--------->
.--------------------------------.
V |
>--------+--------------------------+--+-----------------------><
| .-;-. |
'-schema_statement-+---+---'
|
Example:
-- create table TAB1 and give Jones SELECT privilege CREATE SCHEMA AUTHORIZATION SMITH CREATE TABLE SMITH.TAB1 (COL1 CHAR(4)) GRANT SELECT ON TAB1 TO JONES |
The first line of the above example is a comment line. To insert comments within the schema file:
The sequential input file must contain only one CREATE SCHEMA statement, which must be the first statement in the file (unless the preceding lines are comments); otherwise, the Database Services Utility issues an error message and stops processing the SCHEMA command.
The schema statements must be entered in uppercase. See the DB2 Server for VSE & VM SQL Reference for the correct syntax of valid schema statements. Successful statements are committed if the Database Services Utility AUTOCOMMIT indicator is ON.
The Database Services Utility issues an error message if any invalid statement is in the schema, and might or might not continue processing on the next statement, depending on the setting of ERRORMODE.
You can change the setting of the ERRORMODE and AUTOCOMMIT indicators before the SCHEMA command by issuing the Database Services Utility SET ERRORMODE and SET AUTOCOMMIT commands. If ERRORMODE is set to CONTINUE, processing continues on the next statement following a minor error on any sequential input file statement other than the CREATE SCHEMA statement. If AUTOCOMMIT is ON, the work is committed after each successful statement. For a complete description of these commands, see SET ERRORMODE and SET AUTOCOMMIT.
The schema file must have a fixed length of 80 characters. This format is used if you do not specify any FILEDEF options when you define a schema file. For example, you can create a FILEDEF such as this:
FILEDEF DBSFILE DISK DBSFILE SCHEMIN A
where DBSFILE is the name of the schema file as you refer to it in the SCHEMA command.
For a procedure to construct a FILEDEF command, see Using File Definitions. For more information about FILEDEF parameters and options, see Appendix B, FILEDEF Command Syntax and Notes.