pajama

-244 Could not do a physical-order read to fetch next row.
-197 ISAM error: Partition recently appended to; can't open for
write or logging.

A High Performance Loader express mode load was run on this table.

You must perform a level-0 backup before you can write to the target database. See the Related Information section below for more information regarding express mode load.

What is TBLspace TBLspace?

You are running IBM® Informix® Dynamic Server (IDS) database server and create a regular Dbspace. This Dbspace contains an internal table called TBLspace TBLspace. You cannot access the table using SQL commands.

What it does?

The TBLspace TBLspace table tracks all the partitions (tables or indexes) that you create in a Dbspace, including itself. You can track every table, detached index, or table fragment in the TBLspace TBLspace table through a partition page. The first partition page contains information about the TBLspace TBLspace table itself and the following partition pages track other tables. The partition page contains important information about a table and you can see part the data using theoncheck -pt command (oncheck  -pt database:table):

oncheck   -pt database : table 


database
name of the database 

table
name of the table 

Example: 

The following is a partial output of an  oncheck -pe run on an IDS 7.x system. It is the first extent (50 pages) of the TBLspace TBLspace in chunk 2:

DBspace Usage Report: dbs1        Ownner: informix  Created: 10/11/2003 

Chunk: 2 /informix/dbs1_chunk1 Size Used  Free 
                                5000   53  4947 

Disk usage for Chunk 2      Start  Length 

------------------------------------------- 
 OTHER RESERVED Pages         0      2 
 CHUNK FREE LIST PAGE         2      1 
  TBLSPACE TBLSPACE             3      50 
 FREE                        53     4947 

Chunk: 3 /informix/dbs1_chunk2 Size Used Free 
                                5000   3  4997 
Disk usage for Chunk 3        Start   Length 
-------------------------------------------- 
   OTHER RESERVED Pages         0      2 
   CHUNK FREE LIST PAGE         2      1 
   FREE  

Messages printed to your Informix Server online.log file reference alarmprogram.sh and a return code of 126 or 127. Examples:

06:45:09 Process exited with return code 126: /bin/sh /bin/sh -c /IDS/1170uc7/etc/alarmprogram.sh 2 23 "Logical Log 12 Complete, timestamp: 0xd3d56." "Logical Log 12 Complete, timestamp: 0xd3d56." "" 23001

20:01:55 Process exited with return code 127: /bin/sh /bin/sh -c /usr/informix/etc/alarmprogram.sh 2 18 "Log Backup completed: 9." "Logical Log 9 - Backup Completed" "" 18001

The return codes are bourne and korn shell return codes. Their meanings:

• 126....not an executable 
  
• 127....command not found

Confirm that the alarm script designated by ALARMPROGRAM is correctly referenced by the configured path and confirm that it is executable. By default, alarmprogram.sh uses the korn shell (ksh). If the return code is 126, confirm that the korn shell is installed and located in /bin (/bin/ksh).

Note: If the korn shell is not installed, and the default shell is bash, you may want to change "#! /bin/ksh" at the top of alarmprogram.sh to "#! /bin/bash" as an alternative to installing the korn shell.

When you connect to the database via OAT and while browsing through the menu in OAT, you click on "Storage" under "Space Administration" section, it will deploy a task called "mon_compression_estimates" in the scheduler. Whenever the task executes, it generates the message, SCHAPI Estimate Compression, in the online.log.

You can run the following SQL statement to stop the task from being executed, hence stop the message from being displayed in the online.log.

UPDATE sysadmin:ph_task 
SET tk_enable = "F" 
WHERE tk_name = "mon_compression_estimates";

You will still need to bounce the informix instance after the update.

online.log messages:

Warning: PDQ functionality is not supported in IDS Growth Edition.

The value of PDQPRIORITY cannot be set to 10. It is reset to 0.  

the default PDQ priority configured to run AUS is 10%

IDS 11.50.FC8GE Growth Edition. C is getting warning messages in the online.log after the following tasks are executed.

Auto Update Statistics Evaluation

Auto Update Statistics Refresh  

PDQPRIORITY is set to 0 in the onconfig

If the following select retunr integer bigger than zero ( 0 ):

select value from ph_threshold where name="AUS_PDQ";

Execute the following sentences in dbaccess

database sysadmin;                                  

update ph_threshold set value=0 where name="AUS_PDQ";

The MAXIMUM number of REQUESTS kernel parameter is set low or to the default value of 4096 at the operating system level.

MAXIMUM number of REQUESTS
The maximum number of requests for Asynchronous I/O (AIO).

Please note smitty is not available on AIX 6 or higher  Asynchronous I/O setup via smit / smitty is also not feasible, since its dynamically enabled. I would contact AIX Support to see how to manually change the Maximum number of requests

The solution is to change the MAXIMUM number of REQUESTS by using SMIT:

1. From the command line type: 

$ smitty chgaio

2. From the menu select:

MAXIMUM number of REQUESTS

3. Change the value for this option to 12288 then press Enter

4. Press the F10 key to exit SMIT

5. Reboot the UNIX system

INTRODUCTION

IBM® Informix® Dynamic Server (IDS) uses two types of temporary objects to store temporary data:temporary files and temporary tables. How Informix server use the temporary dbspace depends on whether temporary objects are logged or not.

IDS has many factors that effect this, such as database logging mode, create temp table WITH NO LOG, DBSPACETEMP or PSORT_DBTEMP setting, how a temporary space is created, and so on.

Temporary tables in the server can be automatically routed to a dbspace. The DBSPACETEMP environment variable can be set to one or more dbspaces. If the DBSPACETEMP environment variable is not set, the server uses the value of the DBSPACETEMP configuration parameter. Temporary tables are fragmented across the dbspaces listed in either the environment variable or the configuration parameter. This occurs regardless of whether the query is a PDQ query or not.

DBSPACETEMP config값 또는 환경변수에 명시한 dbspace를 사용하여 round-robin fragmented 테이블이 생성된다.

If DBSPACETEMP is not specified, the temporary table is placed in either the root dbspace or the dbspace where the database was created. SELECT...INTO TEMP statements place the temporary table in the root dbspace. CREATE TEMP TABLE statements place the temporary table in the dbspace where the database was created.

DBSPACETEMP에 명시한 dbspace를 먼저 사용한다. 명시된 dbspace가 없다면 데이터베이스가 생성된 dbspace를 사용한다.

Temporary files can be created in either dbspaces or in file-system space. If PSORT_DBTEMP is set to one or more directories, temporary files are created in round-robin fashion across all the directories listed (the first file in one directory and the second file in the next) regardless of whether the sort is a parallel sort or not. If the PSORT_DBTEMP environment variable is not set, temporary files are fragmented across the dbspaces listed in the DBSPACETEMP environment variable. This means space is allocated and utilized in all the dbspaces for a single temporary file regardless of whether the query is a PDQ query or not. If the DBSPACETEMP environment variable is not set, the dbspaces listed in the DBSPACETEMP configuration parameter are used to store temporary files.

PSORT_DBTEMP와 DBSPACETEMP 환경변수는 DBSPACETEMP config 설정값보다 우선한다.

RECOMMENDATION

It is recommended that you create multiple temporary dbspaces on different devices in your server instance. A performance benefit results because temporary dbspaces are not logged nor are they archived. Also, when temporary files and tables are created they are fragmented across the available temporary dbspaces, therefore enabling parallel access.

Temporary tables should be created in a dbspace that is specifically designated for temporary tables within the Informix system. A temporary dbspace does not accommodate logging. Therefore, temporary tables created in a temporary dbspace must be from an unlogged database or be created using the syntax WITH NO LOG.

If your database is not logged and DBSPACETEMP exists, then temporary tables are created automatically in DBSPACETEMP. In databases that are logged, temporary tables are logged by default and are not created in DBSPACETEMP. To utlize the DBSPACETEMP feature it is necessary to append the SQL phase WITH NO LOG to either the SELECT...INTO TEMP, or the CREATE TEMP TABLE statements.

로깅 데이터베이스에서 TEMPTAB_NOLOG config 는 0이고, TEMP 테이블 생성시 WITH NO LOG 옵션을 사용하지 않는다고 가정

EXAMPLES

Assuming the databases has logging:

1. If we have created a dbspace named tmpdbs,but we could not see it was marked as 'T' in the result of onstat -d. We set DBSPACETEMP configuration parameter to tmpdbs.
On this condition, tmpdbs will be used for logged temporary tables.That means if a temp table is created with 'WITH NO LOG' option, the server will not use it.

2. If we have created a temporary dbspace (it was marked as 'T' in the result of onstat -d). However, we have not set the DBSPACETEMP configuration parameter to this tmpdbs.
On this condition,the tmpdbs dbspace will not be used when creating temporary tables unless you specify the "in dbspace" clause when creating the temporary table.