Hello,

I am preparing a database that will store 10 n * GBs - 100 n * GBs of data.

I calculated to have 1,2 GB of bufferpools. I run the DB2 v. 8.2.1 alone on
4 GB box.

I obtain :

"SQL1478W The defined buffer pools could not be started. Instead, one small
buffer pool for each page size supported by DB2 has been started.
SQLSTATE=01626"

when trying to start database with desires bufferpool sizes.

my "svmon -G" shows 411635 * 4 kB = 1,57 GB of free physical RAM.

I enclose the complete information for someone who would be able to help me.

CREATING:
################################

db2 -v "CREATE DATABASE LODB ON /u01/dbfiles USING CODESET UTF-8 TERRITORY
PL COLLATE USING SYSTEM CATALOG TABLESPACE MANAGED BY SYSTEM USING (
'/u01/dbfiles/dbcont/syscon' ) USER TABLESPACE MANAGED BY SYSTEM USING (
'/u01/dbfiles/dbcont/usrcon') TEMPORARY TABLESPACE MANAGED BY SYSTEM USING
('/u01/dbfiles/dbcont/tmpcon' )"

sleep 10

db2 -v "CONNECT TO LOGDB""



# Utworzenie potrzebnych BUFFERPOOLS"

# 76800 * 4 kB = 300 MB"
db2 -v "CREATE Bufferpool SYBP IMMEDIATE SIZE 76800 PAGESIZE 4 K "

# 307200 * 4 kB = 1200 MB"
# 38400 * 32 kB = 1200 MB"
db2 -v "CREATE Bufferpool USRBP IMMEDIATE SIZE 38400 PAGESIZE 32 K "

# 76800 * 4 kB = 300 MB"
db2 -v "CREATE Bufferpool TEMPBP IMMEDIATE SIZE 76800 PAGESIZE 4 K "

# 153600 * 4 kB = 600 MB"
# 38400 * 16 kB = 600 MB"
db2 -v "CREATE Bufferpool IDXBP IMMEDIATE SIZE 38400 PAGESIZE 16 K "

# Razem na Booferpools 2400 MB"


# Utworzenie PODSTAWOWEGO TABLESPACE dla USER"

db2 -v "CREATE REGULAR TABLESPACE USRSPACE PAGESIZE 32 K MANAGED BY
DATABASE USING ( FILE '/u01/dbfiles/dbcont/usrcon1/u1' 65536 ) EXTENTSIZE
16 OVERHEAD 10.5 PREFETCHSIZE 40 TRANSFERRATE 0.14 BUFFERPOOL USRBP DROPPED
TABLE RECOVERY OFF "
db2 -v "COMMENT ON TABLESPACE USRSPACE IS 'USRSPACE'"

sleep 10

# Utworzenie dodatkowego TABLESPACE dla indeksow"

db2 -v "CREATE REGULAR TABLESPACE INDEXSPACE PAGESIZE 16 K MANAGED BY
DATABASE USING ( FILE '/u01/dbfiles/dbcont/idx1/i1' 65536 ) EXTENTSIZE 16
OVERHEAD 10.5 PREFETCHSIZE 40 TRANSFERRATE 0.14 BUFFERPOOL IDXBP DROPPED
TABLE RECOVERY OFF "
db2 -v "COMMENT ON TABLESPACE INDEXSPACE IS 'INDEXSPACE'"

sleep 10
# Utworzenie dodatkowego TABLESPACE dla CLOBow"

# Podobno dla CLOBow nie definiujemy pagesize ani dodatkowego bufferpoola"

db2 -v "CREATE LARGE TABLESPACE CLOBSPACE PAGESIZE 32 K MANAGED BY
DATABASE USING ( FILE '/u01/dbfiles/dbcont/clob1/c1' 262144 ) EXTENTSIZE 64
OVERHEAD 10.5 PREFETCHSIZE 80 TRANSFERRATE 0.14 BUFFERPOOL USRBP DROPPED
TABLE RECOVERY OFF"
db2 -v "COMMENT ON TABLESPACE CLOBSPACE IS 'CLOBSPACE'"

sleep 10
# Dodnie kontenerow do USERSPACE i CLOBSPACE"

# sta³e dla specyfikowania rozmiarów"

# 524288 * 8 kB = 4 GB dla CLOB "
# 262144 * 32 kB = 8 GB dla CLOB "
# 524288 * 4 kB = 2 GB dla USR "
# 262144 * 4 kB = 1 GB dla IDX "

# 65536 * 32 kB = 2 GB dla USR "
# 32768 * 32 kB = 1 GB dla IDX "
# 65536 * 16 kB = 1 GB dla IDX "



db2 -v "ALTER TABLESPACE USRSPACE ADD ( FILE
'/u01/dbfiles/dbcont/usrcon1/u2' 65536 )"
sleep 10

db2 -v "ALTER TABLESPACE CLOBSPACE ADD ( FILE '/u01/dbfiles/dbcont/clob1/c2'
262144 )"
sleep 10

db2 -v "ALTER TABLESPACE INDEXSPACE ADD ( FILE '/u01/dbfiles/dbcont/idx1/i2'
65536 )"
sleep 10

db2 -v "ALTER TABLESPACE USRSPACE ADD ( FILE
'/u01/dbfiles/dbcont/usrcon1/u3' 65536 )"
sleep 10

db2 -v "ALTER TABLESPACE CLOBSPACE ADD ( FILE '/u01/dbfiles/dbcont/clob1/c3'
262144 )"
sleep 10

db2 -v "ALTER TABLESPACE INDEXSPACE ADD ( FILE '/u01/dbfiles/dbcont/idx1/i3'
65536 )"
sleep 10

db2 -v "ALTER TABLESPACE USRSPACE ADD ( FILE
'/u01/dbfiles/dbcont/usrcon1/u4' 65536 )"
sleep 10

db2 -v "ALTER TABLESPACE CLOBSPACE ADD ( FILE '/u01/dbfiles/dbcont/clob1/c4'
262144 )"
sleep 10

db2 -v "ALTER TABLESPACE INDEXSPACE ADD ( FILE '/u01/dbfiles/dbcont/idx1/i4'
65536 )"
sleep 10

# Przypisanie BUFFERPOOLS do TABLESPAC'ow"

db2 -v "ALTER TABLESPACE SYSCATSPACE BUFFERPOOL SYBP"
db2 -v "ALTER TABLESPACE TEMPSPACE1 BUFFERPOOL TEMPBP"

db2 -v "CREATE DATABASE LOGDB ON /u01/dbfiles USING CODESET UTF-8 TERRITORY
PL COLLATE USING SYSTEM CATALOG TABLESPACE MANAGED BY SYSTEM USING (
'/u01/dbfiles/dbcont/syscon' ) USER TABLESPACE MANAGED BY SYSTEM USING (
'/u01/dbfiles/dbcont/usrcon') TEMPORARY TABLESPACE MANAGED BY SYSTEM USING
('/u01/dbfiles/dbcont/tmpcon' )"

sleep 10

db2 -v "CONNECT TO LOGDB""



# Utworzenie potrzebnych BUFFERPOOLS"

# 76800 * 4 kB = 300 MB"
db2 -v "CREATE Bufferpool SYBP IMMEDIATE SIZE 76800 PAGESIZE 4 K "

# 307200 * 4 kB = 1200 MB"
# 38400 * 32 kB = 1200 MB"
db2 -v "CREATE Bufferpool USRBP IMMEDIATE SIZE 38400 PAGESIZE 32 K "

# 76800 * 4 kB = 300 MB"
db2 -v "CREATE Bufferpool TEMPBP IMMEDIATE SIZE 76800 PAGESIZE 4 K "

# 153600 * 4 kB = 600 MB"
# 38400 * 16 kB = 600 MB"



TUNING
##################################

--------------------------
-- STROJENIE BAZY DANYCH
--------------------------


-- Parametry menedzera bazy danych

update dbm cfg using NUMDB 2
update dbm cfg using NOTIFYLEVEL 2

update dbm cfg using DFT_MON_BUFPOOL ON
update dbm cfg using DFT_MON_LOCK ON
update dbm cfg using DFT_MON_SORT ON
update dbm cfg using DFT_MON_STMT ON
update dbm cfg using DFT_MON_TABLE ON
update dbm cfg using DFT_MON_TIMESTAMP ON

update dbm cfg using QUERY_HEAP_SZ 2000

update dbm cfg using NUM_POOLAGENTS 60
update dbm cfg using NUM_INITAGENTS 50

update dbm cfg using DIAGPATH /u01/dbfiles/diag

-- Parametry bazy danych LOGDB

update db cfg for logdb using DBHEAP 9600
update db cfg for logdb using LOCKLIST 3000
update db cfg for logdb using APP_CTL_HEAP_SZ 256
update db cfg for logdb using SORTHEAP 2048
update db cfg for logdb using APPLHEAPSZ 1024
update db cfg for logdb using PCKCACHESZ 3000
update db cfg for logdb using MAXLOCKS 5
update db cfg for logdb using LOCKTIMEOUT 60

update db cfg for logdb using NUM_IOCLEANERS 9
update db cfg for logdb using NUM_IOSERVERS 12

update db cfg for logdb using MAXAPPLS 50
update db cfg for logdb using AVG_APPLS 50
update db cfg for logdb using MAXFILOP 128

-- tworzymy 120 plikow logow transakcyjnych kazdy po 2560 * 4 kB = 10 MB
update db cfg for logdb using LOGFILSIZ 2560
update db cfg for logdb using LOGPRIMARY 120
update db cfg for logdb using LOGSECOND 0
update db cfg for logdb using LOGRETAIN RECOVERY

-- rozmiar bufora logow 2560 * 4 kB = 10 MB
update db cfg for logdb using LOGBUFSZ 2560
update db cfg for logdb using NEWLOGPATH /db2arch

--update db cfg for logdb using MINCOMMIT 2

CONNECT RESET


db2 get dbm cfg
##################

bash-2.05b$ db2 get dbm cfg

Database Manager Configuration

Node type = Database Server with local and remote clients

Database manager configuration release level = 0x0a00

CPU speed (millisec/instruction) (CPUSPEED) = 4.251098e-07

Max number of concurrently active databases (NUMDB) = 2
Data Links support (DATALINKS) = NO
Federated Database System Support (FEDERATED) = NO
Transaction processor monitor name (TP_MON_NAME) =

Default charge-back account (DFT_ACCOUNT_STR) =

Java Development Kit installation path (JDK_PATH) = /usr/java131

Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 2
Diagnostic data directory path (DIAGPATH) = /u01/dbfiles/diag

Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = ON
Lock (DFT_MON_LOCK) = ON
Sort (DFT_MON_SORT) = ON
Statement (DFT_MON_STMT) = ON
Table (DFT_MON_TABLE) = ON
Timestamp (DFT_MON_TIMESTAMP) = ON
Unit of work (DFT_MON_UOW) = OFF
Monitor health of instance and databases (HEALTH_MON) = OFF

SYSADM group name (SYSADM_GROUP) = DB2GRP1
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
SYSMON group name (SYSMON_GROUP) =

Client Userid-Password Plugin (CLNT_PW_PLUGIN) =
Client Kerberos Plugin (CLNT_KRB_PLUGIN) =
Group Plugin (GROUP_PLUGIN) =
GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =
Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED
Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) =
Server Userid-Password Plugin (SRVCON_PW_PLUGIN) =
Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED
Database manager authentication (AUTHENTICATION) = SERVER
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Bypass federated authentication (FED_NOAUTH) = NO

Default database path (DFTDBPATH) =
/u01/home/db2inst1

Database monitor heap size (4KB) (MON_HEAP_SZ) = 90
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Size of instance shared memory (4KB) (INSTANCE_MEMORY) = AUTOMATIC
Backup buffer default size (4KB) (BACKBUFSZ) = 1024
Restore buffer default size (4KB) (RESTBUFSZ) = 1024

Sort heap threshold (4KB) (SHEAPTHRES) = 20000

Directory cache support (DIR_CACHE) = YES

Application support layer heap size (4KB) (ASLHEAPSZ) = 15
Max requester I/O block size (bytes) (RQRIOBLK) = 32767
Query heap size (4KB) (QUERY_HEAP_SZ) = 2000

Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

Priority of agents (AGENTPRI) = SYSTEM
Max number of existing agents (MAXAGENTS) = 200
Agent pool size (NUM_POOLAGENTS) = 60
Initial number of agents in pool (NUM_INITAGENTS) = 50
Max number of coordinating agents (MAX_COORDAGENTS) = MAXAGENTS
Max no. of concurrent coordinating agents (MAXCAGENTS) = MAX_COORDAGENTS
Max number of client connections (MAX_CONNECTIONS) = MAX_COORDAGENTS

Keep fenced process (KEEPFENCED) = YES
Number of pooled fenced processes (FENCED_POOL) = MAX_COORDAGENTS
Initial number of fenced processes (NUM_INITFENCED) = 0

Index re-creation time and redo index build (INDEXREC) = RESTART

Transaction manager database name (TM_DATABASE) = 1ST_CONN
Transaction resync interval (sec) (RESYNC_INTERVAL) = 180

SPM name (SPM_NAME) = csdbaza
SPM log size (SPM_LOG_FILE_SZ) = 256
SPM resync agent limit (SPM_MAX_RESYNC) = 20
SPM log path (SPM_LOG_PATH) =

TCP/IP Service name (SVCENAME) = db2c_db2inst1
Discovery mode (DISCOVER) = SEARCH
Discover server instance (DISCOVER_INST) = ENABLE

Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = NO

No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 1024
Number of FCM request blocks (FCM_NUM_RQB) = AUTOMATIC
Number of FCM connection entries (FCM_NUM_CONNECT) = AUTOMATIC
Number of FCM message anchors (FCM_NUM_ANCHORS) = AUTOMATIC


db2 get db cfg for lodb
#####################

bash-2.05b$ db2 get db cfg for logdb

Database Configuration for Database logdb

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

Database territory = PL
Database code page = 1208
Database code set = UTF-8
Database country/region code = 48
Database collating sequence = BINARY
Alternate collating sequence (ALT_COLLATE) =

Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

Discovery support for this database (DISCOVER_DB) = ENABLE

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 = YES

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) = 9600
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4)
Log buffer size (4KB) (LOGBUFSZ) = 2560
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
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) = 3000

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) = 256

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

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

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

Track modified pages (TRACKMOD) = OFF

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

Max number of active applications (MAXAPPLS) = 50
Average number of active applications (AVG_APPLS) = 50
Max DB files open per application (MAXFILOP) = 128

Log file size (4KB) (LOGFILSIZ) = 2560
Number of primary log files (LOGPRIMARY) = 30
Number of secondary log files (LOGSECOND) = 0
Changed path to log files (NEWLOGPATH) =
Path to log files = /db2arch/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file = S0000007.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

You've stated nothing about what platform you are running DB2 on. This
will have an impact on what you can do with buffer pools. I added up the
allocated pools and determined that you have 2.4g defined. This is
probably larger than the maximum allowed by your operating system, and
is definitely larger than the memory you have available. You never want
to force the system into paging to support the buffer pools.

Since this is a new database and is not in use; you can use the quick &
dirty approach to resolve the problem. Create a new database and play
with the siingle buffer pool size to determine exactly how large you can
make it. Drop the new database and use the buffer pool size to setup
your pools in the target database.

Phil Sherman


Smutny30 wrote:

Uzytkownik "Philip Sherman" <psherman (AT) ameritech (DOT) net> napisal w wiadomosci
news:c0rbe.1180$Ne7.682 (AT) newssvr17 (DOT) news.prodigy.com...
Hello,

First of all thank you for the answer.

This is partialy said in the topic. This is AIX 5L, v 5.3 on pSeries
machine. The machine has 4 GB of RAM, and as I stated in my initial post the
"svmon -G" shows that there is as much as 2 GB of free physical memory in
the system, ater allocating half-sized bufferpools (1,2 GB).

Do you know how AIX 5L limits it ?

As I said, I have free physical memory shwown by "svmon -G".

I have heard that AIX is self-configuring OS. OK will look for info about
such a parameters.

OK. I will try it.
sincerely Olek

"Smutny30" <smutny30 (AT) poczta (DOT) onet.pl> wrote

depends on the OS, but is about 1.75 GB total for AIX. This includes memory
needed for bufferpools and all other DB2 memory requirements. I would lower
the bufferpool usage to about 1.25 GB and see if that works.

If you have 64 bit OS and create a 64 bit DB2 instance, the memory
limitations don't exist (the maximum is significantly higher).

U¿ytkownik "Smutny30" <smutny30 (AT) poczta (DOT) onet.pl> napisa³ w wiadomo¶ci
news:d4lql3$l29$1 (AT) news (DOT) onet.pl...
The creation of 32 kB * 57600 was unsuccessfull.
I was able to define a one bufferpool of 32 kB * 48000 = 1500 MB

After this time the svmon still shows :

bash-2.05b# svmon -G
size inuse free pin virtual
memory 1048576 745740 302836 108799 711564
pg space 1048576 3239

work pers clnt lpage
pin 108799 0 0 0
in use 710564 0 35176 0


That means that still 4 kB * 302836 = ~1 GB is available in the system.

sincerely Olek