Kbase P113051: Error (7631) when executing a SQL-92 query from an ODBC client
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
8/6/2010 |
|
Status: Unverified
SYMPTOM(s):
Error (7631) when executing a SQL-92 query from an ODBC client
Unable to allocate disk block for temp table use [MM - No data block] (7631)
The query contains the MINUS set operator
The MM temporary file has reached the default 500Mb size
When increasing the -SQLTempDisk parameter to 10Gb, the query runs for hours
When executing the same query from SQL Explorer, the error below appears:
=== SQL Exception 1 ===
SQLState=HY000
ErrorCode=-16001
[JDBC Progress Driver]: (7631)
A sql trace file (.trc) is generated
MM exception - status = -16001
Each query ran separately returns a result set
FACT(s) (Environment):
Progress 9.1x
OpenEdge 10.x
CAUSE:
Bug# OE00126357
FIX:
To work around this issue, create 2 views for each separate query, and then run the query with the MINUS set operator against those 2 views.
For example, the following query fails:
SELECT
Table1.Field1,
Table2.Field1
FROM
PUB.Table1,
PUB.Table2
WHERE
(Table1.Field2 = Table2.Field2)
AND (Table1.Field3 = "SomeValue");
MINUS
SELECT
Table1.Field1,
Table2.Field1
FROM
PUB.Table1,
PUB.Table2
WHERE
(Table1.Field2 = Table2.Field2)
AND (Table1.Field4 = "AnotherValue");
The two views to create are therefore:
CREATE VIEW View1 AS
SELECT
Table1.Field1,
Table1.Field3,
Table2.Field1
FROM
PUB.Table1,
PUB.Table2
WHERE
Table1.Field2 = Table2.Field2;
CREATE VIEW View2 AS
SELECT
Table1.Field1,
Table2.Field1,
Table1.Field4
FROM
PUB.Table1,
PUB.Table2
WHERE
Table1.Field2 = Table2.Field2;
Then, the query to run is:
SELECT
Field1,
Field1
FROM
View1
WHERE
(Field3 = "SomeValue");
MINUS
SELECT
Field1,
Field1
FROM
View2
WHERE
(Field4 = "AnotherValue");