pajama

인포믹스 11.5버전에서 sysmaster DB 통계갱신을 MEDIUM/HIGH 모드로 실행했을 때 오류가 발생합니다.

10버전에서는 아래와 같이 오류가 발생했었습니다.

$ dbaccess sysmaster -

Database selected.

> update statistics high;

  243: Could not position within a table (informix.syscrtadt).

  158: ISAM error: Operation disallowed on SMI pseudo table

11.5버전에서는 아래와 같이 오류가 발생합니다.

$ echo "update statistics high " | dbaccess sysmaster

Database selected.

  244: Could not do a physical-order read to fetch next row.

  101: ISAM error:  file is not open.

low 모드로 통계갱신을 수행하면 이상이 없습니다.

medium, high 모드로는 오류가 발생하는 것이 정상으로 보입니다.

IC91526: UPDATE STATISTICS MEDIUM OR HIGH on SYSMASTER DATABASE PRODUCES LOTS OF ERROR MESSAGES IN MESSAGE LOG AND FAILS WITH ERROR 158

http://www-01.ibm.com/support/docview.wss?uid=swg1IC91526

IC70263: SELECT FROM REMOTE TABLES RETURNS ERROR 245 AND 101 AFTER UPDATESTATISTICS MEDIUM OR HIGH.

http://www-01.ibm.com/support/docview.wss?uid=swg1IC70263

Question

What is the recommended way of running UPDATE STATISTICS in a database so that the optimizer will have the information it needs to run the most efficiently? This article includes a utility to produce an SQL command file that runs all of the UPDATE STATISTICS command recommended for a particular database.

Answer

You can make sure that the optimizer has all of the data it needs to make your queries more efficient by running the SQL command UPDATE STATISTICS on your database. 

1. Run UPDATE STATISTICS LOW on all tables in the database. 

2. Run UPDATE STATISTICS MEDIUM on all columns which are in an index, but are not the first column of any index. 

3. Run UPDATE STATISTICS HIGH on all columns which are the first column in an index. 

4. Run UPDATE STATISTICS on all stored procedures.

This should be done any time that there are significant changes made to the distribution of the data. 

To make the task of running all of these UPDATE STATISTICS statements easier you could put them into a single SQL command file. 


makeupdate.sql 

Here is a command file named makeupdate.sql that can be used to generate an SQL command file that runs the recommended UPDATE STATISTICS commands for your particular database. 

To use the makeupdate.sql script follow these steps: 

1. Copy the file makeupdate.sql.txt to a working directory and change the name to makeupdate.sql



2. Run the makeupdate.sql command file using dbaccess.

Example: 

If your database is named mydb and the makeupdate.sql command file is in your current directory then you can run it by entering this at the command line:

dbaccess mydb makeupdate.sql

The SQL commands in makeupdate.sql produce an SQL command file named update_stats.sql in the current directory. 

To complete the UPDATE STATISTICS process you must run this SQL command file using dbaccess. 

Example: 

If your database is named mydb and the update_stats.sql command file is in your current directory then you can run it by entering this at the command line:

dbaccess mydb update_stats.sql

You may also consider the Auto Update Statistics (AUS) maintenance system available in IBM™ Informix Dynamic Server version 11.50 and later. Refer to the topic "Automatic statistics updating" in the System Administration section of the Informix 11.50 information center (see Related URL section)

출처:

http://www.ibm.com/support/docview.wss