472,950 Members | 2,621 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,950 software developers and data experts.

temporary tablespaces requirement size for creating indexes

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
11 6157
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
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
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
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
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

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

May 9 '07 #7
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
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
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
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
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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

18
by: zebi | last post by:
hello, What's your opinion : The best type of temporary tablespace (DB2V7.2 SP 7 in AIX 4.3 / multiprocessor ) is DMS ou SMS ? Thanks ZEB
1
by: Erik Hendrix | last post by:
Hi, I have a question here regarding setting the prefetch size. So far we took the rule that for OLTP, prefetchsize = extent size and for DSS prefetchsize = extent size * number. However,...
4
by: mairhtin o'feannag | last post by:
Hello, I want to ask the question "how large is each of the indexes in a particular tablespace??" since I want to know which user-created indexes are taking up huge amounts of space. I used the...
6
by: mike_dba | last post by:
Can anyone tell me why a EEE system might be created to have two separate tablespaces with the same 4k pagesize ? They both appear to be in use as I can see the underlying files being modified. ...
0
by: rcamarda | last post by:
As some readers of this newgroup can recall, I use Cognos' program called Data Manager to build my data warehouse. I has the ability to create indexes, however I cannot add other arguments to its...
4
by: RobertoRigoli | last post by:
Hi, I should create a new tablespaces with a page size to 32K, with e buffer pool dedicated with the same page size. The temporary tablespaces page size is 4K, I have to modify the temporary...
0
by: johnfinch | last post by:
I am currently running DB2 for Z/OS V7.1. When I try to create and index on a declared temporary table, a -690 SQL return code occurs. My declarations are as below : DECLARE GLOBAL TEMPORARY...
0
by: okonita | last post by:
Hi all, I need to create one or two indexes on date columns to support MAX and MIN aggregate functions on a very large table and to make the SQL perform/run faster. I remember a group discussion...
6
by: Troels Arvin | last post by:
Hello, I have recently run a rather large data import where the imported data i pumped through some updatable views equipped with INSTEAD OF triggers. For various reasons, the exact same data...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
0
by: Mushico | last post by:
How to calculate date of retirement from date of birth
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be focusing on the Report (clsReport) class. This simply handles making the calling Form invisible until all of the Reports opened by it have been closed, when it...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.