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 Secondaryin 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 T BA 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,libfraEstimated 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 tmpdbsDBTEMP /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 ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

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 ------------------------------

yes ... oninit -rv yes best solution is using ontape -s /ontape -r with rsh Thanks ------------------------------ Garcia Benjamin ------------------------------