Troubleshooting
Problem
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.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21605942