Hi everyone,

I'm using DB2 UDB Enterprise 8.2 FixPak 10 on Itanium2 64bits (2 procs 16GB memory) and Linux RedHat 3.0 update 5

we are facing a big problem. Our server has a lot of I/Os (write). I got a snapshot on bufferpools and saw that "Buffer pool data writes" on temporary tablespace's buffer pool in one day is 60.000.000 pages, about 95% of all data writes. The buffer pool has 128.000 pages.

No Piped sorts rejected, sorts overflow is less than 1%.

Why DB2 is writting so much pages to disk (temporary tablespace), although I have a big buffer pool?

I'd like to put here two samples that write to disk.

1) this UDF simulates the INITCAP function from oracle:

CREATE FUNCTION utl.fn_initcap(
p_string VARCHAR(400) )
RETURNS VARCHAR(400)
SPECIFIC utl.fn_initcap
DETERMINISTIC
NO EXTERNAL ACTION
LANGUAGE SQL
CONTAINS SQL
BEGIN ATOMIC
DECLARE vTamanho SMALLINT DEFAULT 0;
DECLARE vDscString VARCHAR(400) DEFAULT '';
DECLARE vNmrIndice SMALLINT DEFAULT 1;
SET vTamanho = LENGTH(p_string) + 1;
WHILE vNmrIndice < vTamanho DO
IF vNmrIndice <> 1 THEN
IF SUBSTR(p_string,vNmrIndice -1,1) = '' THEN
IF ( LCASE(SUBSTR(p_string,vNmrIndice,3)) IN ('de ', 'da ')) OR
( LCASE(SUBSTR(p_string,vNmrIndice,4)) IN ('dos ', 'das ')) THEN
SET vDscString = vDscString || LCASE( SUBSTR(p_string,vNmrIndice,1 ));
ELSE
SET vDscString = vDscString || UCASE( SUBSTR(p_string,vNmrIndice,1 ));
END IF;
ELSE
SET vDscString = vDscString || LCASE( SUBSTR(p_string,vNmrIndice,1 ));
END IF;
ELSE
SET vDscString = vDscString || UCASE( SUBSTR(p_string,vNmrIndice,1 ));
END IF;
SET vNmrIndice = vNmrIndice + 1;
END WHILE;
RETURN vDscString;
END@

when I executed the following SELECT (only me connected) I noticed some I/Os (written blocks) with iostat command on linux (Blk_wrtn/s). I used db2batch utility and saw that the writes were going to temporary buffer pool.

SELECT utl.fn_initcap('askldas askldas asdklas asdas') FROM sysibm.sysdummy1


2) The second example is a simple SELECT with an ORDER BY and a DESC clause. The index on the table is in ASC order and there isn't ALLOW REVERSE SCANS. When I take the DESC off, DB2 doesn't write to disk, but my question is: this select returns only 2 rows and sortheap is 512 pages, so why db2 writes to disk? in the same way, if there is memory in buffer pool of the temporary tablespace, why DB2 write it to disk?

SELECT * FROM apn.apn_doacao
WHERE x_id = 10232 AND x_date >= CURRENT TIMESTAMP - 730 DAYS
ORDER BY x_date DESC

The index is: x_id + x_date ASC


we have 200 users and are going to 800 users.
it's a call center system (OLTP with some complex queries)
sheapthres = 100.000 pages
sortheap = 512 pages

1) Why DB2 is writting to disk (temporary tablespace) if there is memory on buffer pool?
2) Why UDFs are also writting to disk? I don't know if I'm certain, but the UDFs with BEGIN ATOMIC are generating writes to temporary tablespace.

thank you very much and sorry for my bad english

Just because DB2 has lots of memory for bufferpools does not mean that disk I/O will not occur. Bufferpools do not prevent I/O from taking place, it only prevents synchronous I/O so that no significant performance impact will occur.

The one exception to this is the transaction log. DB2 must update the transaction log for all insert, update, and delete SQL statements in a synchronous mode when a commit is issued by the transaction (and before the commit is considered sucessful) so that DB2 can recover from a crash to a consistant point if necessary.

Futhermore, DB2 uses temporary tablespaces for things other than sort overflows. For example, DB2 sometimes needs to materialize an anwer set in a temporary table before it returns the first row to the application.

Assuming that you have a high bufferpool hit ratio, I think you should forget about the way you are analyzing performace and instead you shoud measure how long each SQL statement takes and don't worry so much about what DB2 is doing under the covers. You can measure the SQL performance with a Dynamic SQL snapshot.

Other things like sort overflows and package cache inserts are covered by other DB2 memory heaps that should also be monitored.

Another thing to remember is that LONG VARCHAR, and LOB's do not use bufferpools and always require synchronous I/O. Therefore, these column types should be avoided if possible in a system that requires extremely high performance.

Hi, thanks,

I managed to solve the problem.

CPU utilization was very high (%iowait) with just 200 users. One simple select was writting like 256kb in temporary tablespace. I had the same application executing in oracle (because we migrated from oracle to db2) and temp was not like that.
We changed the temporary tablespace from DMS to SMS and the blocks stopped from writting.
Now the disk where temp is located has 12 kb/s and before it was like 4000 kb/s.

Before, %cpu idle was 50% and now is 85%.