Informix

Hello,

I need help for :

IBM Informix Dynamic Server Version 14.10.FC8DE -- Updatable (RSS)

we are testing an hdr/rss (updatable) configuration and we are getting systematic errors like this on rss  :

                229: Could not open or create a temporary file.
                140: ISAM error: operation illegal on a DR Secondary

in the onconfig the TEMPTAB_NOLOG=1 and
                               DBSPACETEMP=tmpdbs (onstat -d show dbspace with T flag)

Dbspaces
address number flags fchunk nchunks pgsize flags owner name
48a73028 1 0x4020801 1 1 2048 NL BA informix rootdbs
48bd4d60 2 0x4020801 2 1 16384 NL BA informix datadbs
4a361028 3 0x4020801 3 1 2048 NL BA informix logdbs
4a361268 4 0x2001 4 1 2048 N TBA informix tmpdbs


our env. variables DBTEMP=/tmp and
                             DBSPACETEMP=tmpdbs are initialized


when executing a select with an IN clause informix throw error 229 ....

eg : select * from twsarterp where serart in (select serart from twsartweb where numart=1))

QUERY: (OPTIMIZATION TIMESTAMP: 09-30-2022 17:34:22)
------
select * from twsarterp where serart in (select serart from twsartweb where numart=1)

Estimated Cost: 2
Estimated # of Rows Returned: 1

1) wss.twsarterp: INDEX PATH

(1) Index Name: wss.i00_twsarterp
Index Keys: serart (Serial, fragments: ALL)
Lower Index Filter: wss.twsarterp.serart = ANY <subquery>

Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1

1) wss.twsartweb: INDEX PATH

(1) Index Name: wss.i01_twsartweb
Index Keys: numart serart (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: wss.twsartweb.numart = 1

Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 twsarterp

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 1 0 00:00.00 2
Subquery statistics:
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 twsartweb

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 1 0 00:00.00 1

type rows_sort est_rows rows_cons time
-------------------------------------------------
sort 0 0 0 00:00.00



when executing a select with an ORDER BY CLAUSE no error ....

eg : select * from twsarterp order by untweb2,libfra

QUERY: (OPTIMIZATION TIMESTAMP: 09-30-2022 17:30:56)
------
select * from twsarterp order by untweb2,libfra

Estimated Cost: 24003
Estimated # of Rows Returned: 45455
Temporary Files Required For: Order By

1) wss.twsarterp: SEQUENTIAL SCAN


Thanks in advance
Benjamin


------------------------------
Garcia Benjamin
------------------------------

#Informix

Hi forget to paste the onstat -g env of my test session :

Environment for session 47344:

Variable Value [values-list]
CLIENT_LOCALE en_US.8859-1
CLNT_PAM_CAPABLE 1
DBCENTURY R
DBDATE DMY4.
DBDELIMITER |
DBMONEY F,
DBPATH /opt/informix/etc
[/opt/informix/etc]
[//ifxsrv5]
DBPRINT lp -s
DBSPACETEMP tmpdbs
DBTEMP /tmp
IFX_DEF_TABLE_LOCK ROW
IGNORE_UNDERFLOW 1
INFORMIXCONRETRY 3
INFORMIXCONTIME 15
[15]
[15]
INFORMIXDIR /opt/informix
[/opt/informix]
[/usr/informix]
INFORMIXSERVER ifxsrv5
INFORMIXSTACKSIZE 128
INFORMIXTERM terminfo
LANG en_US.UTF-8
LC_COLLATE en_US.UTF-8
LC_CTYPE en_US.UTF-8
LC_MONETARY en_US.UTF-8
LC_NUMERIC en_US.UTF-8
LC_TIME en_US.UTF-8
LKNOTIFY yes
LOCKDOWN no
NODEFDAC no
[no]
[no]
ONCONFIG onconfig.wbs
OPTOFC 1
PATH /usr/local/bin:/usr/bin:/bin:/usr/local/games:/usr/games:/o
pt/informix/bin
PSORT_DBTEMP /tmp
SERVER_LOCALE en_US.819
SHELL /bin/bash
SUBQCACHESZ 10
TERM dumb
TERMCAP /etc/termcap


------------------------------
Garcia Benjamin
------------------------------

Original Message:
Sent: Fri September 30, 2022 11:53 AM
From: Garcia Benjamin
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

Hello,

I need help for :

IBM Informix Dynamic Server Version 14.10.FC8DE -- Updatable (RSS)

we are testing an hdr/rss (updatable) configuration and we are getting systematic errors like this on rss  :

                229: Could not open or create a temporary file.
                140: ISAM error: operation illegal on a DR Secondary

in the onconfig the TEMPTAB_NOLOG=1 and
                               DBSPACETEMP=tmpdbs (onstat -d show dbspace with T flag)

Dbspaces
address number flags fchunk nchunks pgsize flags owner name
48a73028 1 0x4020801 1 1 2048 NL BA informix rootdbs
48bd4d60 2 0x4020801 2 1 16384 NL BA informix datadbs
4a361028 3 0x4020801 3 1 2048 NL BA informix logdbs
4a361268 4 0x2001 4 1 2048 N TBA informix tmpdbs


our env. variables DBTEMP=/tmp and
                             DBSPACETEMP=tmpdbs are initialized


when executing a select with an IN clause informix throw error 229 ....

eg : select * from twsarterp where serart in (select serart from twsartweb where numart=1))

QUERY: (OPTIMIZATION TIMESTAMP: 09-30-2022 17:34:22)
------
select * from twsarterp where serart in (select serart from twsartweb where numart=1)

Estimated Cost: 2
Estimated # of Rows Returned: 1

1) wss.twsarterp: INDEX PATH

(1) Index Name: wss.i00_twsarterp
Index Keys: serart (Serial, fragments: ALL)
Lower Index Filter: wss.twsarterp.serart = ANY <subquery>

Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1

1) wss.twsartweb: INDEX PATH

(1) Index Name: wss.i01_twsartweb
Index Keys: numart serart (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: wss.twsartweb.numart = 1

Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 twsarterp

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 1 0 00:00.00 2
Subquery statistics:
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 twsartweb

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 1 0 00:00.00 1

type rows_sort est_rows rows_cons time
-------------------------------------------------
sort 0 0 0 00:00.00



when executing a select with an ORDER BY CLAUSE no error ....

eg : select * from twsarterp order by untweb2,libfra

QUERY: (OPTIMIZATION TIMESTAMP: 09-30-2022 17:30:56)
------
select * from twsarterp order by untweb2,libfra

Estimated Cost: 24003
Estimated # of Rows Returned: 45455
Temporary Files Required For: Order By

1) wss.twsarterp: SEQUENTIAL SCAN


Thanks in advance
Benjamin


------------------------------
Garcia Benjamin
------------------------------

#Informix

The temp table being attempted here would be the equivalent of

select serart from twsartweb where numart=1 into temp temp_tab;

Not a big deal, one would think, and probably going well when executed this way?

Nonetheless the server, for some reason (space, page size, ...?) seems to decide against tmpdbs for this internal temp table, so trying rootdbs which then - correctly - triggers ISAM error 140.

If nothing helps, I'd say this is a case for tech support.  Report all circumstances, esp. onstat -cd and table details on the two tables (oncheck -pt, dbschema (-ss/-hd)) or, ideally, the entire tables if possible.
Might also want to trap error 140 (onmode -I 140, trigger the problem, collect resulting af file). Do turn off shmem dumping for this exercise, then disable the trap using simple onmode -I.

HTH,
 Andreas

------------------------------
Andreas Legner
------------------------------

Original Message:
Sent: Fri September 30, 2022 11:53 AM
From: Garcia Benjamin
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

Hello,

I need help for :

IBM Informix Dynamic Server Version 14.10.FC8DE -- Updatable (RSS)

we are testing an hdr/rss (updatable) configuration and we are getting systematic errors like this on rss  :

                229: Could not open or create a temporary file.
                140: ISAM error: operation illegal on a DR Secondary

in the onconfig the TEMPTAB_NOLOG=1 and
                               DBSPACETEMP=tmpdbs (onstat -d show dbspace with T flag)

Dbspaces
address number flags fchunk nchunks pgsize flags owner name
48a73028 1 0x4020801 1 1 2048 NL BA informix rootdbs
48bd4d60 2 0x4020801 2 1 16384 NL BA informix datadbs
4a361028 3 0x4020801 3 1 2048 NL BA informix logdbs
4a361268 4 0x2001 4 1 2048 N TBA informix tmpdbs


our env. variables DBTEMP=/tmp and
                             DBSPACETEMP=tmpdbs are initialized


when executing a select with an IN clause informix throw error 229 ....

eg : select * from twsarterp where serart in (select serart from twsartweb where numart=1))

QUERY: (OPTIMIZATION TIMESTAMP: 09-30-2022 17:34:22)
------
select * from twsarterp where serart in (select serart from twsartweb where numart=1)

Estimated Cost: 2
Estimated # of Rows Returned: 1

1) wss.twsarterp: INDEX PATH

(1) Index Name: wss.i00_twsarterp
Index Keys: serart (Serial, fragments: ALL)
Lower Index Filter: wss.twsarterp.serart = ANY <subquery>

Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1

1) wss.twsartweb: INDEX PATH

(1) Index Name: wss.i01_twsartweb
Index Keys: numart serart (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: wss.twsartweb.numart = 1

Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 twsarterp

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 1 0 00:00.00 2
Subquery statistics:
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 twsartweb

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 1 0 00:00.00 1

type rows_sort est_rows rows_cons time
-------------------------------------------------
sort 0 0 0 00:00.00



when executing a select with an ORDER BY CLAUSE no error ....

eg : select * from twsarterp order by untweb2,libfra

QUERY: (OPTIMIZATION TIMESTAMP: 09-30-2022 17:30:56)
------
select * from twsarterp order by untweb2,libfra

Estimated Cost: 24003
Estimated # of Rows Returned: 45455
Temporary Files Required For: Order By

1) wss.twsarterp: SEQUENTIAL SCAN


Thanks in advance
Benjamin


------------------------------
Garcia Benjamin
------------------------------
#Informix

Hello Andreas,

Reading your suggestions carefully I realised that the dbspace that contains the database was configured with a page size = 16K and that the tempdbs was in 2K. So I created a tempdbs16 with a page size of 16K and changed the DBSPACETEMP=tmpdbs16:tmpdbs and the error 140 disappeared.

Thanks a lot

------------------------------
Garcia Benjamin
------------------------------

Original Message:
Sent: Sat October 01, 2022 05:14 PM
From: Andreas Legner
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

The temp table being attempted here would be the equivalent of

select serart from twsartweb where numart=1 into temp temp_tab;

Not a big deal, one would think, and probably going well when executed this way?

Nonetheless the server, for some reason (space, page size, ...?) seems to decide against tmpdbs for this internal temp table, so trying rootdbs which then - correctly - triggers ISAM error 140.

If nothing helps, I'd say this is a case for tech support.  Report all circumstances, esp. onstat -cd and table details on the two tables (oncheck -pt, dbschema (-ss/-hd)) or, ideally, the entire tables if possible.
Might also want to trap error 140 (onmode -I 140, trigger the problem, collect resulting af file). Do turn off shmem dumping for this exercise, then disable the trap using simple onmode -I.

HTH,
 Andreas

------------------------------
Andreas Legner

Original Message:
Sent: Fri September 30, 2022 11:53 AM
From: Garcia Benjamin
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

Hello,

I need help for :

IBM Informix Dynamic Server Version 14.10.FC8DE -- Updatable (RSS)

we are testing an hdr/rss (updatable) configuration and we are getting systematic errors like this on rss  :

                229: Could not open or create a temporary file.
                140: ISAM error: operation illegal on a DR Secondary

in the onconfig the TEMPTAB_NOLOG=1 and
                               DBSPACETEMP=tmpdbs (onstat -d show dbspace with T flag)

Dbspaces
address number flags fchunk nchunks pgsize flags owner name
48a73028 1 0x4020801 1 1 2048 NL BA informix rootdbs
48bd4d60 2 0x4020801 2 1 16384 NL BA informix datadbs
4a361028 3 0x4020801 3 1 2048 NL BA informix logdbs
4a361268 4 0x2001 4 1 2048 N TBA informix tmpdbs


our env. variables DBTEMP=/tmp and
                             DBSPACETEMP=tmpdbs are initialized


when executing a select with an IN clause informix throw error 229 ....

eg : select * from twsarterp where serart in (select serart from twsartweb where numart=1))

QUERY: (OPTIMIZATION TIMESTAMP: 09-30-2022 17:34:22)
------
select * from twsarterp where serart in (select serart from twsartweb where numart=1)

Estimated Cost: 2
Estimated # of Rows Returned: 1

1) wss.twsarterp: INDEX PATH

(1) Index Name: wss.i00_twsarterp
Index Keys: serart (Serial, fragments: ALL)
Lower Index Filter: wss.twsarterp.serart = ANY <subquery>

Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1

1) wss.twsartweb: INDEX PATH

(1) Index Name: wss.i01_twsartweb
Index Keys: numart serart (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: wss.twsartweb.numart = 1

Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 twsarterp

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 1 0 00:00.00 2
Subquery statistics:
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 twsartweb

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 1 0 00:00.00 1

type rows_sort est_rows rows_cons time
-------------------------------------------------
sort 0 0 0 00:00.00



when executing a select with an ORDER BY CLAUSE no error ....

eg : select * from twsarterp order by untweb2,libfra

QUERY: (OPTIMIZATION TIMESTAMP: 09-30-2022 17:30:56)
------
select * from twsarterp order by untweb2,libfra

Estimated Cost: 24003
Estimated # of Rows Returned: 45455
Temporary Files Required For: Order By

1) wss.twsarterp: SEQUENTIAL SCAN


Thanks in advance
Benjamin


------------------------------
Garcia Benjamin
------------------------------
#Informix

Hello,

After several tests, the problem of error 140 does not come from the fact that you need a tmpdbs with a page size identical to the page size of the datadbs containing the user tables. The problem is the following. To create the "RSS" we do a "scp" of the chunks of the master instance with an onmode -rv + onmode -g RSS "master
If on the RSS instance we try to create a temporary table the error -229/-140 is triggered. To solve this error you have to recreate the "tmpdbs" from the master with onspaces -d to delete the current tmpdbs and onspaces -c -d -t to recreate it.

I don't know if this is a "bug" but this procedure works

Sincerely



------------------------------
Garcia Benjamin
------------------------------

Original Message:
Sent: Sun October 02, 2022 04:22 AM
From: Garcia Benjamin
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

Hello Andreas,

Reading your suggestions carefully I realised that the dbspace that contains the database was configured with a page size = 16K and that the tempdbs was in 2K. So I created a tempdbs16 with a page size of 16K and changed the DBSPACETEMP=tmpdbs16:tmpdbs and the error 140 disappeared.

Thanks a lot

------------------------------
Garcia Benjamin

Original Message:
Sent: Sat October 01, 2022 05:14 PM
From: Andreas Legner
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

The temp table being attempted here would be the equivalent of

select serart from twsartweb where numart=1 into temp temp_tab;

Not a big deal, one would think, and probably going well when executed this way?

Nonetheless the server, for some reason (space, page size, ...?) seems to decide against tmpdbs for this internal temp table, so trying rootdbs which then - correctly - triggers ISAM error 140.

If nothing helps, I'd say this is a case for tech support.  Report all circumstances, esp. onstat -cd and table details on the two tables (oncheck -pt, dbschema (-ss/-hd)) or, ideally, the entire tables if possible.
Might also want to trap error 140 (onmode -I 140, trigger the problem, collect resulting af file). Do turn off shmem dumping for this exercise, then disable the trap using simple onmode -I.

HTH,
 Andreas

------------------------------
Andreas Legner

Original Message:
Sent: Fri September 30, 2022 11:53 AM
From: Garcia Benjamin
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

Hello,

I need help for :

IBM Informix Dynamic Server Version 14.10.FC8DE -- Updatable (RSS)

we are testing an hdr/rss (updatable) configuration and we are getting systematic errors like this on rss  :

                229: Could not open or create a temporary file.
                140: ISAM error: operation illegal on a DR Secondary

in the onconfig the TEMPTAB_NOLOG=1 and
                               DBSPACETEMP=tmpdbs (onstat -d show dbspace with T flag)

Dbspaces
address number flags fchunk nchunks pgsize flags owner name
48a73028 1 0x4020801 1 1 2048 NL BA informix rootdbs
48bd4d60 2 0x4020801 2 1 16384 NL BA informix datadbs
4a361028 3 0x4020801 3 1 2048 NL BA informix logdbs
4a361268 4 0x2001 4 1 2048 N TBA informix tmpdbs


our env. variables DBTEMP=/tmp and
                             DBSPACETEMP=tmpdbs are initialized


when executing a select with an IN clause informix throw error 229 ....

eg : select * from twsarterp where serart in (select serart from twsartweb where numart=1))

QUERY: (OPTIMIZATION TIMESTAMP: 09-30-2022 17:34:22)
------
select * from twsarterp where serart in (select serart from twsartweb where numart=1)

Estimated Cost: 2
Estimated # of Rows Returned: 1

1) wss.twsarterp: INDEX PATH

(1) Index Name: wss.i00_twsarterp
Index Keys: serart (Serial, fragments: ALL)
Lower Index Filter: wss.twsarterp.serart = ANY <subquery>

Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1

1) wss.twsartweb: INDEX PATH

(1) Index Name: wss.i01_twsartweb
Index Keys: numart serart (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: wss.twsartweb.numart = 1

Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 twsarterp

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 1 0 00:00.00 2
Subquery statistics:
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 twsartweb

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 1 0 00:00.00 1

type rows_sort est_rows rows_cons time
-------------------------------------------------
sort 0 0 0 00:00.00



when executing a select with an ORDER BY CLAUSE no error ....

eg : select * from twsarterp order by untweb2,libfra

QUERY: (OPTIMIZATION TIMESTAMP: 09-30-2022 17:30:56)
------
select * from twsarterp order by untweb2,libfra

Estimated Cost: 24003
Estimated # of Rows Returned: 45455
Temporary Files Required For: Order By

1) wss.twsarterp: SEQUENTIAL SCAN


Thanks in advance
Benjamin


------------------------------
Garcia Benjamin
------------------------------
#Informix

Oh ... you probably meant 'oninit -rv' ?

Don't do it this way, use 'oninit -PHY' instead (and add -v if you like), and of course do the scp only with the primary server either down or blocked (onmode -c block).

But since you're transmitting the chunks anyway, why not using

• either ontape Simultaneous backup and restore by using standard I/O   (backup into a network pipe -> phys. restore from that pipe)
• or ifxclone utility for Cloning a primary server (which, under the hood, also relies on backup and restore functionality, but the parallel flavor - think 'onbar') - esp. well suited for multiple large dbspaces.

In any case, let us know if this solves your problem too.
(I already had my doubts whether the (missing) 16k pagesize space really was required - it should not.)

HTH,
 Andreas

------------------------------
Andreas Legner
------------------------------

Original Message:
Sent: Wed October 05, 2022 03:06 AM
From: Garcia Benjamin
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

Hello,

After several tests, the problem of error 140 does not come from the fact that you need a tmpdbs with a page size identical to the page size of the datadbs containing the user tables. The problem is the following. To create the "RSS" we do a "scp" of the chunks of the master instance with an onmode -rv + onmode -g RSS "master
If on the RSS instance we try to create a temporary table the error -229/-140 is triggered. To solve this error you have to recreate the "tmpdbs" from the master with onspaces -d to delete the current tmpdbs and onspaces -c -d -t to recreate it.

I don't know if this is a "bug" but this procedure works

Sincerely



------------------------------
Garcia Benjamin

Original Message:
Sent: Sun October 02, 2022 04:22 AM
From: Garcia Benjamin
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

Hello Andreas,

Reading your suggestions carefully I realised that the dbspace that contains the database was configured with a page size = 16K and that the tempdbs was in 2K. So I created a tempdbs16 with a page size of 16K and changed the DBSPACETEMP=tmpdbs16:tmpdbs and the error 140 disappeared.

Thanks a lot

------------------------------
Garcia Benjamin

Original Message:
Sent: Sat October 01, 2022 05:14 PM
From: Andreas Legner
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

The temp table being attempted here would be the equivalent of

select serart from twsartweb where numart=1 into temp temp_tab;

Not a big deal, one would think, and probably going well when executed this way?

Nonetheless the server, for some reason (space, page size, ...?) seems to decide against tmpdbs for this internal temp table, so trying rootdbs which then - correctly - triggers ISAM error 140.

If nothing helps, I'd say this is a case for tech support.  Report all circumstances, esp. onstat -cd and table details on the two tables (oncheck -pt, dbschema (-ss/-hd)) or, ideally, the entire tables if possible.
Might also want to trap error 140 (onmode -I 140, trigger the problem, collect resulting af file). Do turn off shmem dumping for this exercise, then disable the trap using simple onmode -I.

HTH,
 Andreas

------------------------------
Andreas Legner

Original Message:
Sent: Fri September 30, 2022 11:53 AM
From: Garcia Benjamin
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

Hello,

I need help for :

IBM Informix Dynamic Server Version 14.10.FC8DE -- Updatable (RSS)

we are testing an hdr/rss (updatable) configuration and we are getting systematic errors like this on rss  :

                229: Could not open or create a temporary file.
                140: ISAM error: operation illegal on a DR Secondary

in the onconfig the TEMPTAB_NOLOG=1 and
                               DBSPACETEMP=tmpdbs (onstat -d show dbspace with T flag)

Dbspaces
address number flags fchunk nchunks pgsize flags owner name
48a73028 1 0x4020801 1 1 2048 NL BA informix rootdbs
48bd4d60 2 0x4020801 2 1 16384 NL BA informix datadbs
4a361028 3 0x4020801 3 1 2048 NL BA informix logdbs
4a361268 4 0x2001 4 1 2048 N TBA informix tmpdbs


our env. variables DBTEMP=/tmp and
                             DBSPACETEMP=tmpdbs are initialized


when executing a select with an IN clause informix throw error 229 ....

eg : select * from twsarterp where serart in (select serart from twsartweb where numart=1))

QUERY: (OPTIMIZATION TIMESTAMP: 09-30-2022 17:34:22)
------
select * from twsarterp where serart in (select serart from twsartweb where numart=1)

Estimated Cost: 2
Estimated # of Rows Returned: 1

1) wss.twsarterp: INDEX PATH

(1) Index Name: wss.i00_twsarterp
Index Keys: serart (Serial, fragments: ALL)
Lower Index Filter: wss.twsarterp.serart = ANY <subquery>

Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1

1) wss.twsartweb: INDEX PATH

(1) Index Name: wss.i01_twsartweb
Index Keys: numart serart (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: wss.twsartweb.numart = 1

Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 twsarterp

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 1 0 00:00.00 2
Subquery statistics:
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 twsartweb

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 1 0 00:00.00 1

type rows_sort est_rows rows_cons time
-------------------------------------------------
sort 0 0 0 00:00.00



when executing a select with an ORDER BY CLAUSE no error ....

eg : select * from twsarterp order by untweb2,libfra

QUERY: (OPTIMIZATION TIMESTAMP: 09-30-2022 17:30:56)
------
select * from twsarterp order by untweb2,libfra

Estimated Cost: 24003
Estimated # of Rows Returned: 45455
Temporary Files Required For: Order By

1) wss.twsarterp: SEQUENTIAL SCAN


Thanks in advance
Benjamin


------------------------------
Garcia Benjamin
------------------------------
#Informix

yes ... oninit -rv

yes best solution is using ontape -s /ontape -r with rsh

Thanks

------------------------------
Garcia Benjamin
------------------------------

Original Message:
Sent: Wed October 05, 2022 05:44 AM
From: Andreas Legner
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

Oh ... you probably meant 'oninit -rv' ?

Don't do it this way, use 'oninit -PHY' instead (and add -v if you like), and of course do the scp only with the primary server either down or blocked (onmode -c block).

But since you're transmitting the chunks anyway, why not using

• either ontape Simultaneous backup and restore by using standard I/O   (backup into a network pipe -> phys. restore from that pipe)
• or ifxclone utility for Cloning a primary server (which, under the hood, also relies on backup and restore functionality, but the parallel flavor - think 'onbar') - esp. well suited for multiple large dbspaces.

In any case, let us know if this solves your problem too.
(I already had my doubts whether the (missing) 16k pagesize space really was required - it should not.)

HTH,
 Andreas

------------------------------
Andreas Legner

Original Message:
Sent: Wed October 05, 2022 03:06 AM
From: Garcia Benjamin
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

Hello,

After several tests, the problem of error 140 does not come from the fact that you need a tmpdbs with a page size identical to the page size of the datadbs containing the user tables. The problem is the following. To create the "RSS" we do a "scp" of the chunks of the master instance with an onmode -rv + onmode -g RSS "master
If on the RSS instance we try to create a temporary table the error -229/-140 is triggered. To solve this error you have to recreate the "tmpdbs" from the master with onspaces -d to delete the current tmpdbs and onspaces -c -d -t to recreate it.

I don't know if this is a "bug" but this procedure works

Sincerely



------------------------------
Garcia Benjamin

Original Message:
Sent: Sun October 02, 2022 04:22 AM
From: Garcia Benjamin
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

Hello Andreas,

Reading your suggestions carefully I realised that the dbspace that contains the database was configured with a page size = 16K and that the tempdbs was in 2K. So I created a tempdbs16 with a page size of 16K and changed the DBSPACETEMP=tmpdbs16:tmpdbs and the error 140 disappeared.

Thanks a lot

------------------------------
Garcia Benjamin

Original Message:
Sent: Sat October 01, 2022 05:14 PM
From: Andreas Legner
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

The temp table being attempted here would be the equivalent of

select serart from twsartweb where numart=1 into temp temp_tab;

Not a big deal, one would think, and probably going well when executed this way?

Nonetheless the server, for some reason (space, page size, ...?) seems to decide against tmpdbs for this internal temp table, so trying rootdbs which then - correctly - triggers ISAM error 140.

If nothing helps, I'd say this is a case for tech support.  Report all circumstances, esp. onstat -cd and table details on the two tables (oncheck -pt, dbschema (-ss/-hd)) or, ideally, the entire tables if possible.
Might also want to trap error 140 (onmode -I 140, trigger the problem, collect resulting af file). Do turn off shmem dumping for this exercise, then disable the trap using simple onmode -I.

HTH,
 Andreas

------------------------------
Andreas Legner

Original Message:
Sent: Fri September 30, 2022 11:53 AM
From: Garcia Benjamin
Subject: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

Hello,

I need help for :

IBM Informix Dynamic Server Version 14.10.FC8DE -- Updatable (RSS)

we are testing an hdr/rss (updatable) configuration and we are getting systematic errors like this on rss  :

                229: Could not open or create a temporary file.
                140: ISAM error: operation illegal on a DR Secondary

in the onconfig the TEMPTAB_NOLOG=1 and
                               DBSPACETEMP=tmpdbs (onstat -d show dbspace with T flag)

Dbspaces
address number flags fchunk nchunks pgsize flags owner name
48a73028 1 0x4020801 1 1 2048 NL BA informix rootdbs
48bd4d60 2 0x4020801 2 1 16384 NL BA informix datadbs
4a361028 3 0x4020801 3 1 2048 NL BA informix logdbs
4a361268 4 0x2001 4 1 2048 N TBA informix tmpdbs


our env. variables DBTEMP=/tmp and
                             DBSPACETEMP=tmpdbs are initialized


when executing a select with an IN clause informix throw error 229 ....

eg : select * from twsarterp where serart in (select serart from twsartweb where numart=1))

QUERY: (OPTIMIZATION TIMESTAMP: 09-30-2022 17:34:22)
------
select * from twsarterp where serart in (select serart from twsartweb where numart=1)

Estimated Cost: 2
Estimated # of Rows Returned: 1

1) wss.twsarterp: INDEX PATH

(1) Index Name: wss.i00_twsarterp
Index Keys: serart (Serial, fragments: ALL)
Lower Index Filter: wss.twsarterp.serart = ANY <subquery>

Subquery:
---------
Estimated Cost: 1
Estimated # of Rows Returned: 1

1) wss.twsartweb: INDEX PATH

(1) Index Name: wss.i01_twsartweb
Index Keys: numart serart (Key-Only) (Serial, fragments: ALL)
Lower Index Filter: wss.twsartweb.numart = 1

Query statistics:
-----------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 twsarterp

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 1 0 00:00.00 2
Subquery statistics:
--------------------
Table map :
----------------------------
Internal name Table name
----------------------------
t1 twsartweb

type table rows_prod est_rows rows_scan time est_cost
-------------------------------------------------------------------
scan t1 0 1 0 00:00.00 1

type rows_sort est_rows rows_cons time
-------------------------------------------------
sort 0 0 0 00:00.00



when executing a select with an ORDER BY CLAUSE no error ....

eg : select * from twsarterp order by untweb2,libfra

QUERY: (OPTIMIZATION TIMESTAMP: 09-30-2022 17:30:56)
------
select * from twsarterp order by untweb2,libfra

Estimated Cost: 24003
Estimated # of Rows Returned: 45455
Temporary Files Required For: Order By

1) wss.twsarterp: SEQUENTIAL SCAN


Thanks in advance
Benjamin


------------------------------
Garcia Benjamin
------------------------------
#Informix