Refinement Analysis: System maintenance techniques for Informix Database

Informix is a large database management system with advanced technology, performance and reliability, widely used in a wide range of applications worldwide, including government, financial insurance, postal telecommunications, manufacturing and retail and other important industries or areas. Based on the author's experience of using INFORMIX-4GL and Informix-sql in SCO Unix/xenix, this paper briefly introduces several special problems and their solutions in the maintenance of Informix system.

Repair of table Documents

An Informix database is a collection of tables, each of which corresponds to two documents, the data document (suffix. dat) and the indexed document (suffix. idx). When the system appears abnormal, when you crash, power off, or shut down normally, some of the table documents that are in use are corrupted, and when the system makes the tables again, it reports error messages such as "Cannot retrieve the next record", "Cannot delete records."

Typically, data documents are rarely problematic. To determine whether a data document is normal, simply execute a SELECT * from〈table-name〉 statement or a similar statement, but you cannot use the Where, order by, and so on to avoid using the indexed document to read data sequentially from the data document.

If the data is read smoothly and the number of records is correct, the document is intact, otherwise there is a problem and usually can only be recovered with its data backup.

If the data document is correct, then you should check the corresponding index document. Informix provides a utility bcheck that is designed to check and repair

Index documents, which in turn compare data documents and indexed documents, and if not, ask whether to delete and rebuild the problematic index. Bcheck has many options to choose from, where-N and-y is used to answer "no" or "yes" to any question, allowing the system to automate a series of actions. The syntax is as follows:

bcheck [option]〈 table document name]

To check the index document for a table, run the bcheck-n command first. If everything is OK, there is no problem with the index. Once an error report is found (such as how many error data record pointers are lost, how many data record pointers are missing, or index node pointers, etc.), the bcheck-y command is then executed to fix it.

Statement check table〈 table name and repair table〈 table name in Informix-sql invoke the Bcheck command, respectively, with options-N and-y at run time,

Features are the same, except that the table name is used instead of the table document name.

If the index document does not have the appropriate read and write permissions, or if it does not correctly indicate its path, the information "cannot open the index document" appears when bcheck. If the index document is deleted or the format is corrupted, the same information is available. The corresponding index document can be copied back from the data backup, or you can temporarily create a new table with exactly the same field and index as the original table, copy the index document to the original table, and then run the bcheck-y command to fix it.

Table Space Recycling

Informix manages the datasheet more specifically, and as the amount of data increases, the disk space occupied by the table increases, but when the data record is deleted, the previously occupied space is not released and is still controlled by the table, and is used as an added record later. In order to improve the performance of the system and the query speed of each table, this "free" disk space should be recycled in time.

Use the Bcheck-s command to recycle the space of an indexed document, where the-s option is to recalculate the size of the indexed document. In order to reclaim the space of data documents and index documents at the same time, the DBMS (database management system) can be used to modify the table structure, but the table structure before and after the modification and related permissions should be ensured the same. Use Informix-sql for this purpose, the most insurance approach is to add a field to the corresponding table, and then delete the field. You can also use the ALTER TABLE command to "spoof" the DBMS to modify the structure of the table, such as ALTER TABLE AA modify (BB smallint), where the type of the AA table's field BB is smallint.

Migration of tables

Any Informix database has nine system tables that record information about the database, where the system table Systables (which describes the tables in the database) Dirpath indicates the absolute or relative path of each table document.

The search path of Informix database is usually set by the environment variable DBPath, the system can find the corresponding database and its tables according to the DBPath's correct setting.

When migrating Informix database tables, such as migrating from the root document System of Unix/xenix to the separate document System/U, the system will normally function as soon as the DBPath is reset. If the report is difficult to find some tables (in fact, the table documents are still, and the relevant permissions), the problem is that the Systables table in the Dirpath field value of the absolute path, at this point to use the Update command to modify its value, preferably a relative path, that is, directly to the table document name

Optimization of system Query

Informix uses its supplied optimizer (cost-based optimizer, cost-based optimizer) before executing queries (especially multiple table queries) directives, based on information about each table as recorded by the system at that time, According to a certain rule of judgement analysis and choose a most efficient way to carry out. The system must control the correct data in each table to avoid making the wrong choice. However, for system efficiency considerations, it is not possible to modify the documentation of the current status of each table at any time, otherwise it will add a lot of additional input/output burden. Therefore, updates to this information should be performed on a regular basis.

In the system table Systables of the Informix database, there is a field nrows that is specifically used to record the number of records in each table. When the optimizer is judged by the rules of operation,

The nrows value of each table has very high reference value. Nrows updates can be accomplished by using the following command, namely:

update statistics [for table 〈table—name〉]

The clauses in brackets [] are optional, specifying the table name to update the table, otherwise all the tables in the database are updated.