Informix

 View Only
  • 1.  Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

    Posted Fri September 30, 2022 11:53 AM
    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
    ------------------------------


  • 2.  RE: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

    Posted Fri September 30, 2022 11:58 AM
    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
    ------------------------------



  • 3.  RE: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

    InnerCircle
    Posted Sat October 01, 2022 05:15 PM
    Edited by Andreas Legner Sat October 01, 2022 05:17 PM
    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
    ------------------------------



  • 4.  RE: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

    Posted Sun October 02, 2022 04:23 AM
    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
    ------------------------------



  • 5.  RE: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

    Posted Wed October 05, 2022 03:06 AM
    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
    ------------------------------



  • 6.  RE: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

    InnerCircle
    Posted Wed October 05, 2022 05:44 AM
    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
    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
    ------------------------------



  • 7.  RE: Ifx 14.10xc8 - ISAM error: operation illegal on a DR Secondary

    Posted Wed October 05, 2022 08:43 AM
    yes ... oninit -rv

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

    Thanks

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