 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / DB2 Topics / May 2007temporary 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 ... > hi , all . > [quoted text clipped - 8 lines] > 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?  SignatureKnut Stolze DB2 z/OS Utilities Development IBM Germany > hi , all . > [quoted text clipped - 10 lines] > > 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. > > hi , all . > [quoted text clipped - 14 lines] > file system is full is pointed out in the db2diag.log. this file is > located at ~/sqllib/db2dump by default. 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 . > Filesystem 512-blocks Free %Used Iused %Iused Mounted on > /dev/lv1 2097152 0 100% 106 67% /dbtemp/ [quoted text clipped - 18 lines] > 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? SignatureKnut Stolze DB2 z/OS Utilities Development IBM Germany Ok rawu... what's in the db2diag.log ? 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. 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 > What's the situation when your procedure is not run? How much temp space do > you have then? [quoted text clipped - 6 lines] > 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 . > hi , all . > [quoted text clipped - 10 lines] > > 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 > 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 [quoted text clipped - 8 lines] > 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 ?? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.