NexusDB execution failed

Question

NexusDB : Query execution failed: Operating system error in Temporary Storage Manager: Espace insuffisant sur le disque($70/112)

[$2B27/11047]

I don't understand this message. What's the real problem?

Query code:

SELECT d.Code, c.RaisonSociale, c.EMail, m.Article, m.Quantite, m.TotalTTC, m.NumeroTicket 
FROM C_Client c, C_Mouvement m, C_Depot d, C_Article a, C_Ticket t 
WHERE c.Ident = m.RefClient 
AND m.Article = a.Code 
AND t.DateFacture BETWEEN :dateDebut AND :dateFin

Can you add your SQL code to the question? You're more likely to get a response that way. (Although initial impression is that you've run out of disk space.) — Robert, Nov 12 '15 at 10:54
SELECT d.Code, c.RaisonSociale, c.EMail, m.Article, m.Quantite, m.TotalTTC, m.NumeroTicket FROM C_Client c, C_Mouvement m, C_Depot d, C_Article a, C_Ticket t WHERE c.Ident = m.RefClient AND m.Article = a.Code AND t.DateFacture BETWEEN :dateDebut AND :dateFin — TDX, Nov 12 '15 at 10:58

score 2 · Answer 1 · answered Nov 13 '15 at 00:45

The "Temporary Storage Manager" is responsible for storing dirty blocks if the Buffer Manager has reached the maximum amount of memory it is allowed and has to evict blocks from memory.

"Espace insuffisant sur le disque" is an OS error message, and I would guess that says something like "disk is full". Which meant that the the disk the TSM is using for it's storage is full.

Probably because the resultset your query is producing is so humongous that it exceeds both your available memory and disk space.

Which isn't really surprising if you look at your query:

FROM 
  C_Client c, 
  C_Mouvement m, 
  C_Depot d, 
  C_Article a, 
  C_Ticket t 
WHERE 
  c.Ident = m.RefClient AND 
  m.Article = a.Code AND 
  t.DateFacture BETWEEN :dateDebut AND :dateFin

You are joining 5 tables, but you are only defining join conditions between 3 of them (c, m, and a) which will produce a full unrestricted cross join with the other 2 tables. If we only assume 1000 records for each (c, m, and a), d, and t that's already a 1,000,000,000 record resultset.

That's one of the reasons why the join syntax you are using has been deprecated by the SQL standard for over two decades already. If you try to write your query using current syntax, the problem becomes obvious very quickly:

FROM 
    C_Client AS c
  INNER JOIN
    C_Mouvement AS m ON c.Ident = m.RefClient
  INNER JOIN 
    C_Article AS a ON m.Article = a.Code
  INNER JOIN
    C_Depot AS d ON --???
  INNER JOIN
    C_Ticket AS t ON --???
WHERE 
  t.DateFacture BETWEEN :dateDebut AND :dateFin

NexusDB execution failed

1 Answers1