Hi,

We're trying to get a new server up with 64-BIT 8.1 FP 18 DB2 UDB on Linux ( CentOS). Here is some more info on the new server --

4 CPUs
RAID 10 with 4 drives
CentOS 32GB RAM

BUFFERPOOL SETUP --

db2 "select varchar(bpname,20) as bpname,npages,pagesize from syscat.bufferpools"

BPNAME NPAGES PAGESIZE
-------------------- ----------- -----------
IBMDEFAULTBP 4000 4096
BKDB_BP 40000 8192
BKDB_DATABP 40000 8192
TEMPSP2_BP 8000 8192
HISTBK_BP 8000 8192
UTILBK_BP 8000 8192
BKDBREPL_BP 12000 8192

( Total = Around 1 GB )

I've been setting / updating some of the config / performance variables. Based on an earlier thread, I tried to increase the bufferpools based on this :

'I would increase the IBMDEFAULTBP to 3000 pages to make sure the system catalog is always in memory. The remaining bufferpools (in total) should be at least 50% of the physical memory on the machine (assuming DB2 is pretty much the only thing running on that server).

I tried to increase the NPAGES of BKDB_BP to 160000 but immediately got this error :

SQL20189W. The buffer pool operation (CREATE/ALTER) will not take effect until the next database startup due to insufficient memory.


The database is the only thing running on the server so am confused as to why it wouldn't let me increase it considering the total RAM = 32 GB. What can I do to speed up my queries further? Is there anything I should change in the configuration parameters below as well? Would it be a good idea to add the database_memory config parameter as well? Thanks!!

Here are some other performance and configuration variables that have been set --

db2set -all
[i] DB2_FMP_COMM_HEAPSZ=60000
[i] DB2_HASH_JOIN=ON
[i] DB2COMM=tcpip
[i] DB2_PARALLEL_IO=*
[g] DB2ADMINSERVER=db2ins1as



[db2inst1@localhost ~]$ db2 get db cfg

Database Configuration for Database

Database configuration release level = 0x0a00
Database release level = 0x0a00

Database territory = US
Database code page = 819
Database code set = ISO8859-1
Database country/region code = 1
Database collating sequence = UNIQUE
Alternate collating sequence (ALT_COLLATE) =
Database page size = 4096

Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

Discovery support for this database (DISCOVER_DB) = DISABLE

Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) = 0
Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20

Backup pending = NO

Database is consistent = YES
Rollforward pending = NO
Restore pending = NO

Multi-page file allocation enabled = NO

Log retain for recovery status = RECOVERY
User exit for logging status = NO

Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0

Database heap (4KB) (DBHEAP) = 4800
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 800
Log buffer size (4KB) (LOGBUFSZ) = 256
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 10000
Buffer pool size (pages) (BUFFPAGE) = 1000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 9600

Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 30000
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 1024
SQL statement heap (4KB) (STMTHEAP) = 4096
Default application heap (4KB) (APPLHEAPSZ) = 256
Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 30
Lock timeout (sec) (LOCKTIMEOUT) = 60

Changed pages threshold (CHNGPGS_THRESH) = 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 5
Number of I/O servers (NUM_IOSERVERS) = 10
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = 64

Track modified pages (TRACKMOD) = ON

Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

Max number of active applications (MAXAPPLS) = 1200
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 256

Log file size (4KB) (LOGFILSIZ) = 8000
Number of primary log files (LOGPRIMARY) = 10
Number of secondary log files (LOGSECOND) = 5
Changed path to log files (NEWLOGPATH) =
Path to log files = /home/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file = S0041116.LOG
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Percent of max active log space by transaction(MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = OFF

HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) =
HADR local service name (HADR_LOCAL_SVC) =
HADR remote host name (HADR_REMOTE_HOST) =
HADR remote service name (HADR_REMOTE_SVC) =
HADR instance name of remote server (HADR_REMOTE_INST) =
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC

First log archive method (LOGARCHMETH1) = LOGRETAIN
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =
Number of log archive retries on error (NUMARCHRETRY) = 5
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
Vendor options (VENDOROPT) =

Auto restart enabled (AUTORESTART) = ON
Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART)
Log pages during index build (LOGINDEXBUILD) = OFF
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366

TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =

Automatic maintenance (AUTO_MAINT) = OFF
Automatic database backup (AUTO_DB_BACKUP) = OFF
Automatic table maintenance (AUTO_TBL_MAINT) = OFF
Automatic runstats (AUTO_RUNSTATS) = OFF
Automatic statistics profiling (AUTO_STATS_PROF) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF
Automatic reorganization (AUTO_REORG) = OFF

That is a warning message. Restart the instance and see if you memory looks ok (db2mtrk). Also check "top" to see total memory used on server.

Thanks Marcus.. will check it out. Also, do you think any of my configuration variables are set too high, for example.. DBHEAP, LOGBUFSZ, etc?


I want to check the bufferpool hit ratio as well.. so gave this command --

db2 update monitor switches using BUFFERPOOL on


After running the sql query, when I give db2pd -d dbname.. all I get is this :

Database Partition 0 -- Database DBNAME -- Active -- Up 0 days 00:16:04 -- Date 05/25/2011 18:22:58

BufferPools:
First Active Pool ID 1
Max Bufferpool ID 7
Max Bufferpool ID on Disk 7
Num Bufferpools 11

Address Id Name PageSz PA-NumPgs BA-NumPgs BlkSize ES NumTbsp PgsLeft CurrentSz PostAlter SuspndTSCt
0x15CB8390 1 IBMDEFAULTBP 4096 4000 0 0 N 5 0 4000 4000 0
0x15CB8630 2 BKDB_BP 8192 40000 0 0 N 1 0 40000 40000 0
0x15CB88D0 3 BKDB_DATABP 8192 40000 0 0 N 1 0 40000 40000 0
0x15CB8B70 4 TEMPSP2_BP 8192 8000 0 0 N 1 0 8000 8000 0
0x15CB8E10 5 HISTBK_BP 8192 8000 0 0 N 1 0 8000 8000 0
0x15CB90B0 6 UTILBK_BP 8192 8000 0 0 N 1 0 8000 8000 0
0x15CB9350 7 BKDBREPL_BP 8192 12000 0 0 N 1 0 12000 12000 0
0x15CAF470 4096 IBMHIDDENBP4K 4096 16 0 0 N 0 0 16 16 0
0x15CAF710 4097 IBMHIDDENBP8K 8192 16 0 0 N 0 0 16 16 0
0x15CAF9B0 4098 IBMHIDDENBP16K 16384 16 0 0 N 0 0 16 16 0
0x15CAFC50 4099 IBMHIDDENBP32K 32768 16 0 0 N 0 0 16 16


How can I get the hit ratio percent value? Is there a another command for that or some other setting that I have to enable? Thanks!!

Database Heap and Log Buffer Size look OK, although Database heap is a little tricky in V8 (completely automatic in V9.7).

Bufferpool hit ratio can be calculated by looking ratio of logical and physical reads. This is available in Database Snapshot or Dynamic SQL Snapshot.

Thanks.. will take a look at that.. I think I'll leave DBHEAP as is, the other variables I'm curious about are --

1. DFT_PREFETCH_SZ ( should I set this to AUTOMATIC -- currently have it set to 64)

2. CATALOGCACHE_SZ = 800 ( Is this too high?)

3. UTIL_HEAP_SZ = 10000 ( Is this too high?)

4. LOCKLIST = 9600 ( Is this too high?)

5. STMTHEAP = 4096 ( Is this too high?)

6. (LOGFILSIZ) = 8000 ( Is this too high?)


Also, have you ever used the database_memory config variable?

Another question is whether I should change the value of the buffpage configuration variable? Thanks so much for the help!

I would increase CATALOGCACHE_SZ to about 4096. The others look OK, unless you have a problem. Given the amount of memory you have, don't worry too much about them being too high. These are number of 4K pages, so do the math, and you will see it is not that much to worry about.

The LOGFILSIZ is file size on disk, so no memory considerations. That is a reasonable size. But number of secondary logs and LOGRETAIN=RECOVERY are questionable. Why are you not using LOGARCHMETH1?

The database_memory config allows you to cap database memory. Probably leave it automatic, especially if only one database on that server.

Thanks Marcus. I will change CATALOGCACHE_SZ to 4096 and leave the others as is.

I guess I'm not sure what the values of LOGRETAIN and LOGARCHMETH1 should be. These are settings that we have on another server and with the restore, these values were also set on the new server. Also, not sure if it matters but our online backups are compressed and include logs. Then on the server where it is restored, we give the rollforward command after detaching the log files from the image and putting them in a 'logs' folder under the instance home dir --

db2 rollforward db db2inst1 to end of logs and stop overflow log path \(/home/db2inst1/logs\)

It has to do with archiving the log files, not a restore and rollforward. You can read about LOGARCHMETH1 and decide if you want it or not.

thanks marcus, i will look into it... also, I was able to bump up the bufferpool size so that the total is around 16 GB ( system has 32 GB RAM)..

I also found this as the total query execution time for data of a particular id for a whole year on the new server ( with the new bufferpool settings) --

real 4m27.212s

Comparatively, on another server that only has 4 GB RAM , it took half the time to execute the same query.

On the new server, the query immediately after that with another id for the same range was much faster. Shouldn't the new server settings with much more memory cause faster execution?

It is very hard to tune a database based on the little information you have provided in this thread (or is practical to provide).

However, can you give us an estimate of the size of the database (tables and indexes)?

If the size of your database is significantly larger than 16 GB, then you might want to consider putting your indexes in a separate tablespaces with a separate bufferpool from the data. Database tuning (especially bufferpool configuration and tuning) requires knowledge of the database design and application that is using the database.

If the particular data that a query needs is not already in bufferpool memory, then it must be read from disk and placed in memory by DB2 before it can be used by DB2 to get the results of the query (except for certain LOB data). If you run the query the second time, then hopefully a large chunk of the data needed for the query is already in memory from the first query and should run faster. These are basic DB2 concepts.

thanks marcus.. yes I'm aware that the initial query should definitely be slower than when running the queries when some data is in memory... I just thought since the bufferpools are much bigger in size compared to our other servers ( 4 times as much actually!) , that it would execute faster.. our database is pretty big.. around 91 GB and the query that I'm running is against a table that is atleast 70GB.. this table is in it's own tablespace. The index on all of our tables have been created with a command like this so not really sure what tablespace / bufferpool it's going to--

GRANT INDEX ON TABLE TABLE1 TO USER DB2INST1;

Also, another problem I'm having is with the db2look command --. when I give it to see the definition of a table I get this error --

db2look -d dbname -t site -e -x -o site.ddl
-- No userid was specified, db2look tries to use Environment variable USER
-- USER is: DB2INST1
-- The db2look utility will consider only the specified tables
-- Creating DDL for table(s)
-- Output is sent to file: site.ddl
SQL0007N The character "\" following "HIJKLMNOPQRSTUVWXYZ["


Any idea how I can fix this? Thanks!!

I don't understand the above. This statement allows user db2inst1 the ability to create indexes on that table. I suspect that db2inst1 is the instance owner, who already has authority to do whatever it wants to do.

I would suggest you hire a consultant to help you tune your database. This is a complex subject and not something that can be done by someone responding in a forum. You could ask the consultant to provide you with some skills transfer on this subject as part of the work to be performed.

thanks for the advice marcus, I agree that it is complex and needs some expertise...

oh and sorry, I actually pasted the wrong index command in... I actually meant to put the create index statement instead --

CREATE INDEX IDX_NAME ON TABLE1 ("TID" ASC) ;