Creating the Database
Now you are ready to create the data model as tables in a database. You do this with the CREATE DATABASE, CREATE TABLE, and CREATE INDEX statements. The Informix Guide to SQL: Syntax shows the syntax of these statements in detail. This section discusses the use of CREATE DATABASE and CREATE TABLE in implementing a data model. The use of CREATE INDEX is covered in Chapter 11, "Granting and Limiting Access to Your Database."
Remember that the telephone-directory data model is used for illustrative purposes only. For the sake of the example, it is translated into SQL statements.
You might have to create the same database model more than once. However, the statements that create the model can be stored and executed automatically. For more information, see "Using Command Scripts".
When the tables exist, you must populate them with rows of data. You can do this manually, with a utility program, or with custom programming.
Using CREATE DATABASE
A database is a container that holds all the parts of a data model. These parts include not only the tables but also views, indexes, synonyms, and other objects that are associated with the database. You must create a database before you can create anything else.
When the database server creates a database, it stores the locale of the database that is derived from the DB_LOCALE environment variable in its system catalog. This locale determines how the database server interprets character data that is stored within the database. By default, the database locale is the U.S. English locale that uses the ISO8859-1 code set. For information on using alternative locales, see the Guide to GLS Functionality. 
Using CREATE DATABASE with INFORMIX-Universal Server
Universal Server differs from other database servers in the way that it creates databases and tables. When Universal Server creates a database, it sets up records that show the existence of the database and its mode of logging. It manages disk space directly, so these records are not visible to operating-system commands.
Avoiding Name Conflicts
Normally, only one copy of Universal Server is running on a computer, and it manages the databases that belong to all users of that computer. It keeps only one list of database names. The name of your database must be different from that of any other database managed by that database server. (It is possible to run more than one copy of the database server. This is sometimes done, for example, to create a safe environment for testing apart from the operational data. In that case, be sure that you are using the correct database server when you create the database, and again when you access it later.)
Selecting a Dbspace
Universal Server offers you the option of creating the database in a particular dbspace. A dbspace is a named area of disk storage. Ask your Universal Server administrator whether you should use a particular dbspace. The administrator can put a database in a dbspace to isolate it from other databases or to locate it on a particular disk device. (The INFORMIX-Universal Server Administrator's Guide discusses dbspaces and their relationship to disk devices.)
Some dbspaces are mirrored (duplicated on two disk devices for high reliability); your database can be put in a mirrored dbspace if its contents are of exceptional importance.
Choosing the Type of Logging
Universal Server offers the following choices for transaction logging:
When you do not choose logging, BEGIN WORK and other SQL statements that are related to transaction processing are not permitted in the database. This situation affects the logic of programs that use the database.
Buffered logging is best for databases that are updated frequently (so that speed of updating is important), but you can re-create the updates from other data in the event of a crash. Use the SET LOG statement to alternate between buffered and regular logging.
The design of ANSI SQL prohibits the use of buffered logging.
The Universal Server administrator can turn transaction logging on and off later. For example, the administrator can turn it off before inserting a large number of new rows.
Using CREATE TABLE
Use the CREATE TABLE statement to create each table that you designed in the data model. This statement has a complicated form, but it is basically a list of the columns of the table. For each column, you supply the following information:
In short, the CREATE TABLE statement is an image in words of the table as you drew it in the data-model diagram. The following example shows the statements for the telephone-directory model:
CREATE TABLE address
(
id_num SERIAL PRIMARY KEY,
rec_num INT,
street VARCHAR (50,20),
city VARCHAR (40,10),
state CHAR(5) DEFAULT 'CA',
zipcode CHAR(10),
FOREIGN KEY (rec_num) REFERENCES name (rec_num)
);
CREATE TABLE voice
(
vce_num CHAR(13) PRIMARY KEY,
vce_type CHAR(10),
rec_num INT,
FOREIGN KEY (rec_num) REFERENCES name (rec_num)
);
CREATE TABLE fax
(
fax_num CHAR(13),
oper_from DATETIME HOUR TO MINUTE,
oper_till DATETIME HOUR TO MINUTE,
PRIMARY KEY (fax_num)
);
CREATE TABLE faxname
(
fax_num CHAR(13),
rec_num INT,
PRIMARY KEY (fax_num, rec_num),
FOREIGN KEY (fax_num) REFERENCES fax (fax_num),
FOREIGN KEY (rec_num) REFERENCES name (rec_num)
);
CREATE TABLE modem
(
mdm_num CHAR(13) PRIMARY KEY,
rec_num INT,
b_type CHAR(5),
FOREIGN KEY (rec_num) REFERENCES name (rec_num)
);
Using Command Scripts
You can create the database and tables by entering the statements interactively. But, in some cases you might have to do it again or several more times.
You might have to do it again to make a production version after a test version is satisfactory. You might have to implement the same data model on several computers. To save time and reduce the chance of errors, you can put all the commands to create a database in a file and execute them automatically.
Capturing the Schema
You can write the statements to implement your model into a file. However, you can also have a program do it for you. See the Informix Migration Guide for information about the dbschema utility, a program that examines the contents of a database and generates all the SQL statements required to re-create it. You can build the first version of your database interactively, making changes until it is exactly as you want it. Then you can use dbschema to generate the SQL statements necessary to duplicate it.
Executing the File
Programs that you use to enter SQL statements interactively, such as DB-Access or SQL Editor, can be driven from a file of commands. The use of these products is covered in the DB-Access User Manual or the INFORMIX-SQL User Guide. You can start DB-Access or INFORMIX-SQL to read and execute a file of commands that you or dbschema prepared.
An Example
Most Informix database server products come with a demonstration database called stores7 (the database used for most of the examples in this book). The stores7 database is delivered as an operating-system command script that calls Informix products to build the database. You can copy this command script and use it as the basis for automating your own data model.
Populating the Tables
For your initial tests, the easiest way to populate the tables interactively is to type INSERT statements in DB-Access or the SQL Editor. To insert a row into the manufact table of the stores7 database in DB-Access, enter the following command:
If you are preparing an application program in another language, you can use the program to enter rows.
If your database contains typed tables or tables that contain complex data types, the syntax you use to insert data into tables is somewhat different than that shown in the preceding example. For information about how to perform an insert or update on a table that contains a complex data type, see Chapter 12, "Accessing Complex Data Types."
Often, the initial rows of a large table can be derived from data that is stored in tables in another database or in operating-system files. You can move the data into your new database in a bulk operation. If the data is in another Informix database, you can retrieve it in several ways.
If you are using Universal Server, you can simply select the data you want from the other database on another database server as part of an INSERT statement in your database. As the following example shows, you could select information from the items table in the stores7 database to insert into a new table:
When the source is another kind of file or database, you must find a way to convert it into a flat ASCII file; that is, a file of printable data in which each line represents the contents of one table row.
After you have the data in a file, you can use the dbload utility to load it into a table. For more information on dbload, see the Informix Migration Guide. The LOAD statement in DB-Access and the SQL Editor can also load rows from a flat ASCII file. For information about the LOAD and UNLOAD statements, see the Informix Guide to SQL: Syntax.
Inserting hundreds or thousands of rows goes much faster if you turn off transaction logging. No point exists in logging these insertions because, in the event of a failure, you can easily re-create the lost work. The following list contains the steps of a large bulk-load operation:
The existing logs can be used to recover the database in its present state, and you can run the bulk insertion again to recover those rows if they are lost.
If you are using Universal Server, either ask the administrator to perform a full or incremental backup, or use the onunload utility to make a binary copy of your database only.
If you are using other database servers, use operating-system commands to back up the files that represent the database.
You can enclose the steps of populating a database in a script of operating-system commands. You can automate the database server administrator commands by invoking the command-line equivalents to ON-Monitor.
|
