Database Schema Creation

You can create the schema for an entire database or for a portion of the database. The options for dbschema allow you to perform the following actions:

• Display CREATE SYNONYM statements by owner, for a specific table or for the entire database.
• Display the CREATE TABLE, CREATE VIEW, CREATE FUNCTION, or CREATE PROCEDURE statement for a specific table or for the entire database.
• Display all GRANT privilege statements that affect a specified user or that affect all users for a database or a specific table. The user can be either a user name or role name.
• Display user-defined and row data types with or without type inheritance.
• Display the CREATE SEQUENCE statement defining the specified sequence object, or defining all sequence objects in the database.

When you use dbschema and specify only the database name, it is equivalent to using dbschema with all its options (except for the -hd and -ss options). In addition, if Information Schema views were created for the database, this schema is shown. For example, the following two commands are equivalent:

dbschema -d stores_demo
dbschema -s all -p all -t all -f all -d stores_demo

SERIAL fields included in CREATE TABLE statements that dbschema displays do not specify a starting value. New SERIAL fields created with the schema file have a starting value of 1, regardless of their starting value in the original database. If this value is not acceptable, you must modify the schema file.

Creating Schemas for Databases Across a UNIX or Linux Network

You can specify a database on any accessible non-SE Informix database server with the -d database syntax. The following command displays the schema for the stores_demo database on the finland database server on the UNIX or Linux system console:

dbschema -d //finland/stores_demo 

Changing the Owner of an Object

The dbschema utility uses the owner.object convention when it generates any CREATE TABLE, CREATE INDEX, CREATE SYNONYM, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, CREATE FUNCTION, or GRANT statement, and when it reproduces any unique, referential, or check constraint. As a result, if you use the dbschema output to create a new object (table, index, view, procedure, constraint, sequence, or synonym), the owner of the original object owns the new object. If you want to change the owner of the new object, you must edit the dbschema output before you run it as an SQL script.

You can use the output of dbschema to create a new function if you also specify the pathname to a file in which compile-time warnings are stored. This pathname is displayed in the dbschema output.

For more information about the CREATE TABLE, CREATE INDEX, CREATE SYNONYM, CREATE VIEW, CREATE SEQUENCE, CREATE PROCEDURE, CREATE FUNCTION, and GRANT statements, see the IBM Informix: Guide to SQL Syntax.