Table of Contents
Designing the Database
Designing a physical database should follow a thorough investigation of the requirements, data needs, and development of a solid logical data model.
Normalization Versus Denormalization
A fully normalized database offers flexibility to the data but can cause complexities when accessing and maintaining it.
Denormalizing the database involves splitting tables if there is a logical separation of how the columns are selected or maintained. In some cases, it means combining tables where columns from those tables are always selected together. In addition to splitting and combining tables, duplicating columns can also be done for performance reasons.
Online Transaction Processing and Decision Support
Databases in which the data is constantly changing are called online transaction processing (OLTP) databases.
When the purpose of the database is primarily decision support, it is called a Decision Support System (DSS) or an OnLine Analytical Processing (OLAP) database. These types of databases are also known as data warehouses.
When the database serves both purposes, it is important to understand the trade-offs in performance when designing the database and tuning the database engine.
Table Placement and Fragmentation
Tables that will be accessed or updated frequently should be placed on separate disks to limit disk head movement and contention.
Indexes provide a shortcut to the data. When the optimizer determines that an index is available and will be used to access the data, the index pages are read into the buffers. When you create multiple indexes on a table, many different qualifiers can be used to more effectively implement the request for data.
A feature of OnLine Dynamic Server Architecture (DSA) is the capability to detach and fragment indexes.
Detaching indexes separates the index pages from the data pages on separate disks. Parallel reads and writes can happen when the data being accessed is on separate disks. You must provide a dbspace name for the index to be created in if you want the index to be detached.
SET EXPLAIN is an SQL statement that is very useful in revealing how the database engine will access the data for a given query. When you run SET EXPLAIN ON, the output is written to the file sqexplain.out in the current directory. Output from each query continues to be appended to sqexplain.out for the entire session unless you issue a SET EXPLAIN OFF.
Business Rules and Referential Integrity
Business rules are implemented at the database through referential integrity. Many features are available to enforce integrity at the database level.
Constraints are implemented at the table or column level.
Triggers can also be used to implement referential integrity. There are three types of triggers: insert, update, and delete. For each trigger, you can do many things, including calling a stored procedure, which really gives you a lot of flexibility. A triggered event can execute triggered actions BEFORE, AFTER, or FOR EACH ROW. Separate update triggers can be created for each column, but each column can be included in only one update trigger event.
Tthey can be called from triggers to extend the ability of the trigger. They can be embedded in SQL statements such as the SELECT statement. They can be called with the EXECUTE PROCEDURE statement. Stored procedures can have arguments passed to them, making them dynamic.
A view can be defined to allow access to only certain columns from a table or to summarize and derive data columns.
CREATE VIEW utah_customers (cust_id, f_name, l_name, city, state) AS SELECT cust_id, f_name, l_name, city, state FROM customers WHERE state = "UT" WITH CHECK OPTION ;
A synonym creates an alias to a table or view. Synonyms are a convenient way of making a remote table look local.
CREATE SYNONYM informix.corp_sales FOR sales;
Business-critical databases that cannot go down for any period of time can be replicated to ensure availability. Replication duplicates on an entirely separate database server all the data that a database server manages, not just dbspaces like mirroring.
To minimize downtime, consider mirroring some or all of your dbspaces. If a dbspace is mirrored and the disk crashes the mirror, dbspace takes over until the disk of the primary dbspace is recovered and brought back online.
Security levels for the database are:
- CONNECT allows the user to access database objects for the database in which the privilege is granted.
- RESOURCE has the same privileges as CONNECT, plus it allows the user to create database objects. After creating objects, the user can ALTER or DROP only those objects created by that user.
- DBA privilege allows the user unlimited capabilities in that database.
Database Design Considerations
Users and Transactions
Resources allocated to the database server are limited. These resources must be shared by all users and processes that connect to the database server. As the number of users and transactions increases, the likelihood is greater for contention of these limited resources.
When designing large databases, give extra attention so that you reduce major administrative activities later. Most maintenance tasks will take longer and consume more resources because of the sheer volume of the data.
Access Tools and Development Tools
Many tools are available to access and manipulate the data in the database, such as dbaccess, which comes with Informix
When you log your database, many updates can be treated as a single logical unit of work. If any statement from that unit fails, the data that was changed is returned to its original state. Logging a database maintains a before and an after copy of every row that changes. Maintaining a before and an after copy of each row that changes has a considerable amount of overhead associated with it. Use BEGIN WORK to start a transaction and COMMIT WORK to end the transaction. ROLLBACK WORK undoes any changes during a transaction. If BEGIN WORK and COMMIT WORK are not coded in the application and the database has logging, each SQL statement will be treated as a single transaction.
Populating the Database
Migrating from Legacy Mainframe Systems
Moving data from the legacy system can be done with a gateway product that often has tools to take data in one format and put it into another for a specific database. Some of the tools Informix has will reformat data as it loads into the database.
Migrating from Desktop Databases
Nearly all desktop databases can be unloaded in a format that can be read by the Informix load utilities. Knowing the utilities and the formats they support will simplify moving data from the desktop database to an Informix database.
Monitoring, Tuning, and Configuring
The INFORMIX-OnLine DSA configuration file is where the database engine tuning takes place. Changing the parameters in the ONCONFIG file might significantly increase performance and eliminate bottlenecks.
Information about a table and the data in it is gathered and stored by the INFORMIX - UPDATE STATISTICS command.
oncheck verifies the consistency of the database. This includes checking, displaying, and repairing the database structures.
Backups must be considered when designing and implementing a database. Two utilities are provided for online backups—ontape and onarchive. These utilities back up the data while there is activity against the database.
More details in Informix Backup and Recovery section.