SQL error -229 and ISAM error -140 on a replicate secondary

Technote (troubleshooting)


Problem(Abstract)

A query is run on a target server of an HDR pair. Whether the target is a read-only or updateable secondary, RSS, or SDS, the server can be enabled to run queries that can be held in a temporary dbspace. When a 'Select' query is run that requires 'into temp with no log', an SQL error -229 and ISAM error -140 may be returned.

Cause

The temp dbspace ('T') flag or the file permissions for the chunk are incorrect.

Diagnosing the problem

There are two possible causes which can reveal this problem.

1. At the OS level:

In the location where the chunk for the dbspace is located, examine the permission settings for any chunk, which is intended to be used as a temporary dbspace, using 'ls -al'.

    [informix@my dbspaces]$ ls -al
    total 546792
    drwxr-xr-x. 2 informix informix 4096 Jan 3 11:31 .
    drwxr-xr-x. 3 informix informix 4096 Jan 21 11:06 ..
    -rw-rw----. 1 informix informix 52428800 Jan 28 15:07 datadbs
    -rw-rw----. 1 informix informix 102400000 Jan 28 15:07 indexdbs
    -rw-rw----. 1 informix informix 62914560 Jan 28 15:07 llogdbs
    -rw-rw----. 1 informix informix 51380224 Jan 28 15:32 plogdbs
    -rw-rw----. 1 informix informix 204800000 Jan 28 15:32 rootdbs
    -rw-rw----. 1 informix informix 33554432 Jan 28 15:12 sbspace
    -rw--w----. 1 informix informix 52428800 Jan 28 15:12 tempdbs <==

    (Note: File permissions should be informix:informix 660 rw-rw-) .


2. At the dbspace level:

Compare the temp dbspace listing with the output from onstat -d

    · onconfig shows: DBSPACETEMP tempdbs

    · onstat -d:

      Dbspaces
      address number flags fchunk nchunks pgsize flags owner name
      4b8b1028 1 0x70001 1 1 2048 N BA informix rootdbs
      4b8b1438 2 0x70001 2 1 2048 N BA informix physdbs
      4b8b15e0 3 0x60001 3 1 2048 N BA informix logdbs
      4b8b1788 4 0x60001 4 1 2048 N BA informix datadbs
      4b8b1930 5 0x68001 5 1 2048 N SBA informix sbspace
      4b8b1ad8 6 0x24001 6 1 2048 N WBA informix tempdbs <==
      4b8b1c80 7 0x60001 7 1 2048 N BA informix indexdbs

(Note the absence of a 'T' in the flags column in the onstat -d output for the temp spaces.)

Resolving the problem

1. If the OS level permissions are incorrect, reset the permissions:

chmod 660 tempdbs

chown informix:informix tempdbs


    Result after modification:


    -rw-rw----. 1 informix informix 52428800 Jan 28 15:13 tempdbs

2. If the dbspace on the secondary is not flagged properly as Temporary, temporary dbspace will need to be created using the onspaces command with the '-t' option:


    onspaces - c -d tempdbs -t -p /pathname/tempdbs -o 0-s 100000

Subsequent flag appearance in a correct onstat -d output:
      Dbspaces
      address number flags fchunk nchunks pgsize flags owner name
      4b8b1028 1 0x70001 1 1 2048 N BA informix rootdbs
      4b8b1438 2 0x70001 2 1 2048 N BA informix physdbs
      4b8b15e0 3 0x60001 3 1 2048 N BA informix logdbs
      4b8b1788 4 0x60001 4 1 2048 N BA informix datadbs
      4b8b1930 5 0x68001 5 1 2048 N SBA informix sbspace
      4b8b1ad8 6 0x42001 6 1 2048 N TBA informix tempdbs <==
      4b8b1c80 7 0x60001 7 1 2048 N BA informix indexdbs

After these corrections, DML and DDL queries into temp dbspace should work. If the problem continues, consider opening a case with technical support.

Rate this page:

(0 users)Average rating

Document information


More support for:

Informix Servers
Informix Internet Foundation

Software version:

10.0, 11.1, 11.5, 11.7

Operating system(s):

AIX, HP-UX, Linux, Solaris

Software edition:

Enterprise, Growth, Ultimate, Workgroup

Reference #:

1605942

Modified date:

2012-11-28

Translate my page

Machine Translation

Content navigation