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:
Sent: Tue September 14, 2021 10:30 AM
From: Art Kagel
Subject: Problems with temporary dbspaces
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:
Sent: Tue September 14, 2021 09:09 AM
From: Gustavo Echenique
Subject: Problems with temporary dbspaces
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:
Sent: Tue September 14, 2021 04:47 AM
From: Jon Ritson
Subject: Problems with temporary dbspaces
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
Original Message:
Sent: Mon September 13, 2021 05:03 PM
From: Gustavo Echenique
Subject: 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