Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
Back to discussions
Expand all | Collapse all

Problems with temporary dbspaces

  • 1.  Problems with temporary dbspaces

    Posted 09/13/21 05:04 PM
    Dear colleagues:

    I come to you, because it is the first time that I have encountered this problem, which I have investigated a lot through documentation and I cannot find the solution.

    I have an Informix IDS 7.31 TD6 engine on Windows 10, which is mounted as a test, to be able to make the modifications in it and then pass them to the production engine that is in a Windows 2008 Server.

    The problem that I have in the test server is the following:
    I run the following query:

    SELECT cv.Rowid Row1, cvc.Rowid Row2
    FROM cbtes_vence cv, OUTER (cbtes_vence_cptos cvc)
    WHERE cv.idsucursal = cvc.idsucursal
    AND cv.cbte_type = cvc.cbte_type
    AND cv.grupo_cbte = cvc.grupo_cbte
    AND cv.letra_cbte = cvc.letra_cbte
    AND cv.nrocbte = cvc.nrocbte
    AND cv.nrorenglon = cvc.nrorenglon
    AND cv.nrovto = cvc.nrovto
    INTO TEMP xx Missing WITH NO LOG;

    And I get the error message "SQL Error (-264): Could not write to a temporary file. [ISAM error -131: ISAM error: no free disk space"

    But in the data chunks I have more than 12GB available, and I have created 30 temporary dbspaces of 2GB each, making a total of 60GB, which is a lot.

    I have declared each of the temporary dbspaces in the ONCONFIG configuration variable DBSPACETEMP as indicated in the manuals, separated by commas and without spaces between them.

    I got to install a third engine on a PC, but the same error keeps getting.

    I hope some of you can help me.

    In advance, I am very grateful for your time and attention.

    Greetings!

    Gustavo Echenique

    ------------------------------
    Gustavo Echenique
    ------------------------------

    #Informix


  • 2.  RE: Problems with temporary dbspaces

    Posted 09/13/21 05:18 PM
    Try fewer larger temp spaces or fragment the temp table 

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 3.  RE: Problems with temporary dbspaces

    Posted 09/13/21 06:03 PM
    Hello Paul!

    Thank you very much for your quick response.

    I have done what you suggested, but the error persists.

    I send you a hug!

    Gustavo Echenique

    ------------------------------
    Gustavo Echenique
    ------------------------------

    Original Message


  • 4.  RE: Problems with temporary dbspaces

    Posted 09/14/21 08:44 AM
    Hello,

    Change the DBSPACETEMP entry in onconfig file , separated them with semi colon (;), instead of comma (,).

    Rebounce ids, and see whether the error disappear.

    Hope it works 

    -Fauziah- 




    Original Message


  • 5.  RE: Problems with temporary dbspaces

    Posted 09/14/21 09:06 AM
    You could just test by setting DBSPACETEMP in the env, no need for a bounce to test

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC



    Original Message


  • 6.  RE: Problems with temporary dbspaces

    Posted 09/14/21 02:08 AM
    Hello,

    The documentation says that DBSPACETEMP must not be longer than 254 characters. Whether Informix then truncates or ignores the list is not documented. Try to bring the list under 254 characters.

    'A list of dbspaces. The length of the list cannot exceed 254 characters.'

    If the statement runs, can you use onstat -d to see which DBSpaces are filled? Are all temp. DBSpaces from DBSPACETEMP really used?


    ------------------------------
    Andreas Seifert
    CURSOR Software AG
    http://www.admin-scout.com
    ------------------------------



  • 7.  RE: Problems with temporary dbspaces

    Posted 09/14/21 07:38 AM
    Hi Andreas!

    First of all, thank you very much for your reply.

    I had no full knowledge of how many characters the DBSPACETEMP configuration parameter allows, but I assumed that it should be around the number you indicate.

    That is why I have named each temporary dbspace as t0,t1,t2,t3 ...,t30 so that they occupy 112 characters.

    Regarding the second thing you mention, I see that the first four (4) temporary dbspaces are occupied, and to a small extent.

    Greetings!

    Gustavo Echenique

    ------------------------------
    Gustavo Echenique
    ------------------------------

    Original Message


  • 8.  RE: Problems with temporary dbspaces

    Posted 09/14/21 04:11 AM
    Hey Gustavo

    Spill your sort/merge operations out to the f/s using PSORT_DBTEMP and PSORT_NPROC. I that way you will be by-passing the temp dbaspaces.

    ------------------------------
    Kirit Rana
    ------------------------------



  • 9.  RE: Problems with temporary dbspaces

    Posted 09/14/21 04:48 AM

    How many rows are in:

    cbtes_vence cv

    cbtes_vence_cptos cvc

    Provide an onstat -d to validate the DBSPACETEMPs


    I wonder what value is returned by the following (i.e. beware of OUTER joins):

    SELECT count(*)
    FROM cbtes_vence cv, OUTER (cbtes_vence_cptos cvc)
    WHERE cv.idsucursal = cvc.idsucursal
    AND cv.cbte_type = cvc.cbte_type
    AND cv.grupo_cbte = cvc.grupo_cbte
    AND cv.letra_cbte = cvc.letra_cbte
    AND cv.nrocbte = cvc.nrocbte
    AND cv.nrorenglon = cvc.nrorenglon
    AND cv.nrovto = cvc.nrovto;



    ------------------------------
    Jon Ritson
    ------------------------------



  • 10.  RE: Problems with temporary dbspaces

    Posted 09/14/21 09:10 AM
    Edited by System Admin 01/20/23 04:27 PM
    Hi Jon!

    I thank you for the reply.

    The table cbtes_vence has 21571173 records, while cbtes_vence_cptos has 103593498 records. They are many.

    What I need is to find all the records of cbtes_vence_cptos that could be orphaned, and if I do it with the following query:

    SELECT Rowid FROM cbtes_vence_cptos cvc
         WHERE NOT EXISTS (SELECT 1 FROM cbtes_vence cv
                                                WHERE cv.idempresa = cvc.idempresa
                                                AND cv.idsucursal = cvc.idsucursal
                                                AND cv.cbte_type = cvc.cbte_type
                                                AND cv.grupo_cbte = cvc.grupo_cbte
                                                AND cv.letra_cbte = cvc.letra_cbte
                                                AND cv.nrocbte = cvc.nrocbte
                                                AND cv.nrorenglon = cvc.nrorenglon
                                                AND cv.nrovto = cvc.nrovto);

    On PC it takes at least two (2) days, that's why I was inclined to use an OUTER and then search the temporary table for records that had the Row2 column as null.

    The output of the onstat -d command is as follows:

    Informix Dynamic Server Version 7.31.TD6 -- On-Line -- Up 14:41:57 -- 377152 Kbytes

    Dbspaces
    address number flags fchunk nchunks flags owner name
    1f09e150   1       1   1     1 N    informix rootdbs
    1f4cb568   2 2001   2     1 N T informix t0
    1f4cb628   3 2001   3     1 N T informix t1
    1f4cb6e8   4 2001   4     1 N T informix t2
    1f4cb7a8   5 2001   5     1 N T informix t3
    1f4cb868   6 2001   6     1 N T informix t4
    1f4cb928   7 2001   7     1 N T informix t5
    1f4cb9e8   8 2001   8     1 N T informix t6
    1f4cbaa8   9 2001   9     1 N T informix t7
    1f4cbb68 10 2001 10     1 N T informix t8
    1f4cbc28 11 2001 11      1 N T informix t9
    1f4cbce8 12 2001 12     1 N T informix t10
    1f4cbda8 13 2001 13     1 N T informix t11
    1f4cbe68 14 2001 14     1 N T informix t12
    1f4cbf28  15 2001 15     1 N T informix t13
    1f4d2018 16 2001 16     1 N T informix t14
    1f4d20d8 17 2001 17     1 N T informix t15
    1f4d2198 18 2001 18     1 N T informix t16
    1f4d2258 19 2001 19     1 N T informix t17
    1f4d2318 20 2001 20     1 N T informix t18
    1f4d23d8 21 2001 21     1 N T informix t19
    1f4d2498 22 2001 22     1 N T informix t20
    1f4d2558 23 2001 23     1 N T informix t21
    1f4d2618 24 2001 24     1 N T informix t22
    1f4d26d8 25 2001 25     1 N T informix t23
    1f4d2798 26 2001 26     1 N T informix t24
    1f4d2858 27 2001 27     1 N T informix t25
    1f4d2918 28 2001 28     1 N T informix t26
    1f4d29d8 29 2001 29     1 N T informix t27
    1f4d2a98 30 2001 30     1 N T informix t28
    1f4d2b58 31 2001 31     1 N T informix t29
    1f4d2c18 32       1 32     1 N     informix logdbs
    1f4d2cd8 33       1 33     1 N     informix physdbs
    1f4d2d98 34       1 34 158 N     informix datos
    34 active, 2047 maximum
    ​
    Saludos!

    Gustavo



    ------------------------------
    Gustavo Echenique
    ------------------------------

    Original Message


  • 11.  RE: Problems with temporary dbspaces

    Posted 09/14/21 10:31 AM
    Gustavo:

    Try this query instead:

    SELECT cvc.rowid
    FROM cbtes_vence_cptos cvc
    LEFT OUTER JOIN cbtes_vence cv
    ON  cv.idempresa = cvc.idempresa AND cv.idsucursal = cvc.idsucursal
           AND cv.cbte_type = cvc.cbte_type AND cv.grupo_cbte = cvc.grupo_cbte
           AND cv.letra_cbte = cvc.letra_cbte AND cv.nrocbte = cvc.nrocbte
           AND cv.nrorenglon = cvc.nrorenglon AND cv.nrovto = cvc.nrovto
    WHERE cv.rowid IS NULL;


    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


  • 12.  RE: Problems with temporary dbspaces

    Posted 09/16/21 06:54 PM
    Hi Art!

    It worked perfectly, and in a more than satisfactory time (19 minutes).

    You know, I had previously written the query like this:
    SELECT cvc.Rowid Row1, cv.Rowid Row2
    FROM cbtes_vence_cptos cvc, OUTER (cbtes_vence cv)
    WHERE company cvc. = company cv
    AND cvc.idsucursal = cv.idsucursal
    AND cvc.cbte_type = cv.tipo_cbte
    AND cvc.grupo_cbte = cv.grupo_cbte
    AND cvc.letra_cbte = cv.letra_cbte
    AND cvc.nrocbte = cv.nrocbte
    AND cvc.nrorenglon = cv.nrorenglon
    AND cvc.nrovto = cv.nrovto
    AND cv.Rowid IS NULL

    Which in appearance is similar, but it was returning all Row2 rows as null.

    Only in Oracle do I use the LEFT OUTER JOIN, or whatever form of JOIN you use in your query, I don't know why I got used to writing only OUTER in Informix.

    Thank you!

    ------------------------------
    Gustavo Echenique
    ------------------------------

    Original Message


  • 13.  RE: Problems with temporary dbspaces

    Posted 09/17/21 07:37 AM
    Gustavo:

    The form of OUTER JOIN that you tried is the older syntax. In that syntax Informix applies all filters pre-join so you cannot filter for unmatched rows. 

    Using the newer syntax, Informix follows the ANSI processing rules whereby filters in the ON clause are applied pre-join and filters in the WHERE clause are applied post-join which allows one to filter for only matched or only unmatched rows from the inner table. Of course matched rows will just be the same as for an inner join, so not interesting. 

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


Related Content