Tempspace full at MQT refresh in DB2 V8 for Solaris

[Mats Mohlin]

We are testing Db2 Cube Views on DB2 V8 for Solaris
Small MQT get created OK but for a big MQT we get this message in the
db2diag.log

ADM6017E The table space "TEMPSPACE1" (ID "1") is full. Detected on
container
"/db2/db2inst2/db2inst2/NODE0000/SQL00002/SQLT0001.0" (ID "0"). The
underlying
file system is full or the maximum allowed space usage for the file
system has
been reached. It is also possible that there are user limits in place
with respect to maximum file size and these limits have been reached.

The file system is not full.
The remaining limit we can think of is that we might need a file size
of more then 2 GB
But the Solaris file size limits are checked and should be unlimited
(infinity).
Can a 32 bits DB2 instance allocate larger files than 2 GB ?
Will DB2 split a large tempfile into 2 files if needed ?
Would an extra tempspcae help ?
Any other comment are wellcome

Mats Mohlin IBM SW Sweden

[miro flasza]

Mats Mohlin wrote:

We are testing Db2 Cube Views on DB2 V8 for Solaris
Small MQT get created OK but for a big MQT we get this message in the
db2diag.log

ADM6017E The table space "TEMPSPACE1" (ID "1") is full. Detected on
container
"/db2/db2inst2/db2inst2/NODE0000/SQL00002/SQLT0001.0" (ID "0"). The
underlying
file system is full or the maximum allowed space usage for the file
system has
been reached. It is also possible that there are user limits in place
with respect to maximum file size and these limits have been reached.

The file system is not full.
The remaining limit we can think of is that we might need a file size
of more then 2 GB
But the Solaris file size limits are checked and should be unlimited
(infinity).
Can a 32 bits DB2 instance allocate larger files than 2 GB ?
Will DB2 split a large tempfile into 2 files if needed ?
Would an extra tempspcae help ?
Any other comment are wellcome

Mats Mohlin IBM SW Sweden

Yes, DB2 can allocate files > 2GB in the temp space and no, we will
not split the file if a limit is reached. Check the quota for the id
of the instance owner, many installations don't allow users to create
files > 2GB.

Regards,
Miro

[Blair Adamache]

To clarify - DB2 has supported >2 GB files since back in v5, when there
was no 64-bit memory addressable version of DB2. I would suspect a
ulimit problem first.

(When we talk about 32-bit and 64-bit DB2 in the manuals, it refers to
DB2's memory addressability. 32-bit DB2 has no particular 32-bit limits
for stuff like files or BIGINT values - just for memory addressability.)

miro flasza wrote:

Mats Mohlin wrote:

We are testing Db2 Cube Views on DB2 V8 for Solaris
Small MQT get created OK but for a big MQT we get this message in the
db2diag.log

ADM6017E The table space "TEMPSPACE1" (ID "1") is full. Detected on
container
"/db2/db2inst2/db2inst2/NODE0000/SQL00002/SQLT0001.0" (ID "0"). The
underlying
file system is full or the maximum allowed space usage for the file
system has
been reached. It is also possible that there are user limits in place
with respect to maximum file size and these limits have been reached.

The file system is not full.
The remaining limit we can think of is that we might need a file size
of more then 2 GB
But the Solaris file size limits are checked and should be unlimited
(infinity).
Can a 32 bits DB2 instance allocate larger files than 2 GB ?
Will DB2 split a large tempfile into 2 files if needed ?
Would an extra tempspcae help ?
Any other comment are wellcome

Mats Mohlin IBM SW Sweden

Yes, DB2 can allocate files > 2GB in the temp space and no, we will
not split the file if a limit is reached. Check the quota for the id
of the instance owner, many installations don't allow users to create
files > 2GB.

Regards,
Miro

[AK]

maybe it would be nice to have a look at the query that defines the
MQT.
Some techniques such as index-only access couls significantly reduce
the need for temporary space