Problems with temporary dbspaces

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

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

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

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

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

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

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

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-

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

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

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

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

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