temporary tablespaces requirement size for creating indexes

rawu

hi , all .

I got a question about creating indexes in db2 .

a table has 44236333 rows
an index planed to be build include 2 field ( 6 byte )
accordding to db2 document
temporary tablespaces requirement size will be
(6+9)*44236333*3.2/1024/1024/1024 = 1.98G

now , i have 6G of my temporary tablespaces , i still fail to create
this index , saying " file system is full "

any help ?? thx ...

May 8 '07 #1

Knut Stolze

rawu wrote:

hi , all .

I got a question about creating indexes in db2 .

a table has 44236333 rows
an index planed to be build include 2 field ( 6 byte )
accordding to db2 document
temporary tablespaces requirement size will be
(6+9)*44236333*3.2/1024/1024/1024 = 1.98G

now , i have 6G of my temporary tablespaces , i still fail to create
this index , saying " file system is full "

Do you have enough space for the logs as well?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

May 8 '07 #2

Hardy

On May 8, 2:34 pm, rawu <Herma...@gmail.comwrote:

hi , all .

I got a question about creating indexes in db2 .

a table has 44236333 rows
an index planed to be build include 2 field ( 6 byte )
accordding to db2 document
temporary tablespaces requirement size will be
(6+9)*44236333*3.2/1024/1024/1024 = 1.98G

now , i have 6G of my temporary tablespaces , i still fail to create
this index , saying " file system is full "

any help ?? thx ...

hi, for this kind of error, there's a very big possibility that which
file system is full is pointed out in the db2diag.log. this file is
located at ~/sqllib/db2dump by default.

May 8 '07 #3

rawu

On 5ÔÂ8ÈÕ, ÏÂÎç7ʱ33·Ö, Hardy <wyh...@gmail.comwrote:

On May 8, 2:34 pm, rawu <Herma...@gmail.comwrote:

hi , all .

I got a question about creating indexes in db2 .

a table has 44236333 rows
an index planed to be build include 2 field ( 6 byte )
accordding to db2 document
temporary tablespaces requirement size will be
(6+9)*44236333*3.2/1024/1024/1024 = 1.98G

now , i have 6G of my temporary tablespaces , i still fail to create
this index , saying " file system is full "

any help ?? thx ...

hi, for this kind of error, there's a very big possibility that which
file system is full is pointed out in the db2diag.log. this file is
located at ~/sqllib/db2dump by default.

May 9 '07 #4

rawu

Filesystem 512-blocks Free %Used Iused %Iused Mounted on
/dev/lv1 2097152 0 100% 106 67% /dbtemp/
NODE0000
/dev/lv2 2097152 0 100% 7 8% /dbtemp/
NODE0001
/dev/lv3 1024.00 9.75 100% 7 1% /dbtemp/
NODE0002
/dev/lv4 1024.00 9.54 100% 7 1% /dbtemp/
NODE0003
/dev/lv_dbbase 4194304 932480 78% 1562 2% /dbbase
/dev/lv5 1572864 785376 51% 12 1% /dblog/
NODE0000
/dev/lv7 1048576 261184 76% 12 1% /dblog/
NODE0001
---------------------------------------------------------------

I traced the whole procedure , run command "df" and got a result as
above when it failed down .
The first four are patitions for temporary tablespace, and the 5th
line is where db2diag.log located.
I think it's clear that the problem is temp tbs 's size .

May 9 '07 #5

Knut Stolze

rawu wrote:

Filesystem 512-blocks Free %Used Iused %Iused Mounted on
/dev/lv1 2097152 0 100% 106 67% /dbtemp/
NODE0000
/dev/lv2 2097152 0 100% 7 8% /dbtemp/
NODE0001
/dev/lv3 1024.00 9.75 100% 7 1% /dbtemp/
NODE0002
/dev/lv4 1024.00 9.54 100% 7 1% /dbtemp/
NODE0003
/dev/lv_dbbase 4194304 932480 78% 1562 2% /dbbase
/dev/lv5 1572864 785376 51% 12 1% /dblog/
NODE0000
/dev/lv7 1048576 261184 76% 12 1% /dblog/
NODE0001
---------------------------------------------------------------

I traced the whole procedure , run command "df" and got a result as
above when it failed down .
The first four are patitions for temporary tablespace, and the 5th
line is where db2diag.log located.
I think it's clear that the problem is temp tbs 's size .

What's the situation when your procedure is not run? How much temp space do
you have then?

Have you analyzed (access plan) the SQL statements inside the procedure to
see if one of them uses a some temp space?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

May 9 '07 #6

kavallin

Ok rawu... what's in the db2diag.log ?

May 9 '07 #7

mirof007

On May 8, 2:34 am, rawu <Herma...@gmail.comwrote:

hi , all .

I got a question about creating indexes in db2 .

a table has 44236333 rows
an index planed to be build include 2 field ( 6 byte )
accordding to db2 document
temporary tablespaces requirement size will be
(6+9)*44236333*3.2/1024/1024/1024 = 1.98G

now , i have 6G of my temporary tablespaces , i still fail to create
this index , saying " file system is full "

any help ?? thx ...

rawu, what version of DB2 are you using? I believe prior to DB2 9
there was a limitation in the number of rows DB2 for LUW could store
on a single data page, I believe the limit was 256. Creating an index
requires a sort of the data, which will likely spill to the temporary
tablespace, given your data volume. Because your row width is so
narrow, you might end up with not fully populated temp pages when sort
is spilling. What is your temporary tablespace page size? If you can't
add more space to the tabelspace containers, then you could try using
a smaller page size for your temp.

Hope this helps,
Miro

May 9 '07 #8

rawu

On 5ÔÂ9ÈÕ, ÏÂÎç4ʱ46·Ö, Knut Stolze <sto...@de.ibm.comwrote:

>
What's the situation when your procedure is not run? How much temp spacedo
you have then?

Have you analyzed (access plan) the SQL statements inside the procedure to
see if one of them uses a some temp space?

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

sorry , my mistake , use a word "procedure" .
Maybe you misunderstand me , i just create a index , not craete a
procedure .

May 11 '07 #9

rawu

On 5ÔÂ9ÈÕ, ÏÂÎç11ʱ50·Ö, mirof007 <mirof...@gmail.comwrote:

rawu, what version of DB2 are you using? I believe prior to DB2 9
there was a limitation in the number of rows DB2 for LUW could store
on a single data page, I believe the limit was 256. Creating an index
requires a sort of the data, which will likely spill to the temporary
tablespace, given your data volume. Because your row width is so
narrow, you might end up with not fully populated temp pages when sort
is spilling. What is your temporary tablespace page size? If you can't
add more space to the tabelspace containers, then you could try using
a smaller page size for your temp.

Hope this helps,
Miro

thx .. my DB version is V8.5
I have another try yesterday ..
Given the whole data of this table cost about 7G of tablespace
I enlarge the temporary tablespaces size to 8G .
And this time , i create the index successfully .
And I get that , the peak consumption of temporary tablespaces is
67% .
That means it requires temp tablepace size up to 1:1 of data of table
to create index .
And I guess , db2 runs like that : copy all columes rather than the
columes included by index to temporary tablespace , and then sort ,
and then copy data to index tablespace . ...
So .. i need size as much as the data table .
So .. no matter how many columns index includes , it requires the same
size of temporary tablespace ..
Am I right ??

May 11 '07 #10

rawu

On 5ÔÂ9ÈÕ, ÏÂÎç6ʱ14·Ö, kaval...@hotmail.com wrote:

Ok rawu... what's in the db2diag.log ?

something like following statments ..

2007-05-08-10.57.16.890304 Instance:untinst Node:000
PID:1224782(db2pclnr 0) TID:1 Appid:none
buffer pool services sqlbClnrAsyncWriteCompletion Probe:0

ADM6017E The table space "TEMPSPACE32K" (ID "8") is full. Detected
on
container "/unttemp/NODE0000/temp" (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.

May 11 '07 #11

kavallin

Ok rawu.. I'm always using SMS for Tempspace to avoid Tempspace full
problem (as it said in the manual regarding Temporary table space
design
"t is recommended that you define a single SMS temporary table space
with a page size equal to the page size used in the majority of your
regular table spaces.
SMS is almost always a better choice than DMS for temporary table
spaces because:
* There is more overhead in the creation of a temporary table when
using DMS versus SMS.
* Disk space is allocated on demand in SMS, whereas it must be pre-
allocated in DMS. Pre-allocation can be difficult: Temporary table
spaces hold transient data that can have a very large peak storage
requirement, and a much smaller average storage requirement. With DMS,
the peak storage requirement must be pre-allocated, whereas with SMS,
the extra disk space can be used for other purposes during off-peak
hours.
* The database manager attempts to keep temporary table pages in
memory, rather than writing them out to disk. As a result, the
performance advantages of DMS are less significant. "
/roger

May 11 '07 #12