By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,689 Members | 1,759 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,689 IT Pros & Developers. It's quick & easy.

Cannot alocate 2,4 GB for Bufferpools, DB v 8.2.1 on AIX 5L

P: n/a
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


Nov 12 '05 #1
Share this Question
Share on Google+
22 Replies


P: n/a
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:
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
--------------------------


Nov 12 '05 #2

P: n/a

Uzytkownik "Philip Sherman" <ps******@ameritech.net> napisal w wiadomosci
news:c0****************@newssvr17.news.prodigy.com ...
You've stated nothing about what platform you are running DB2 on. This
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).
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,
Do you know how AIX 5L limits it ?
and is definitely larger than the memory you have available.
As I said, I have free physical memory shwown by "svmon -G".
You never want to force the system into paging to support the buffer pools.
I have heard that AIX is self-configuring OS. OK will look for info about
such a parameters.

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.
OK. I will try it.
sincerely Olek

Phil Sherman

Nov 12 '05 #3

P: n/a
"Smutny30" <sm******@poczta.onet.pl> wrote in message
news:d4**********@news.onet.pl...

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

DB2 cannot address that much memory in a 32 bit instance. The maximum
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).
Nov 12 '05 #4

P: n/a

Użytkownik "Smutny30" <sm******@poczta.onet.pl> napisał w wiadomości
news:d4**********@news.onet.pl...

Uzytkownik "Philip Sherman" <ps******@ameritech.net> napisal w wiadomosci
news:c0****************@newssvr17.news.prodigy.com ...
You've stated nothing about what platform you are running DB2 on. This
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.


OK. I will try it.


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

Nov 12 '05 #5

P: n/a

Użytkownik "Mark A" <no****@nowhere.com> napisał w wiadomości
news:ua********************@comcast.com...
"Smutny30" <sm******@poczta.onet.pl> wrote in message
news:d4**********@news.onet.pl...

As I said, I have free physical memory shwown by "svmon -G".
DB2 cannot address that much memory in a 32 bit instance. The maximum
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.
Hello Mark, thank you for your answer.

Your remarks sound probably, basic on my experience with one big bufferpool
that I described in separate answer in this thread. I was able to allocate
one 1,5 GB bufferpool, that added to some other db2 alocations (dbheap,
agents, sort heap) would give 1,75 GB.

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


I think I have 64 bit OS since it is the newest AIX 5L.

I have no information about 64 bit DB2 instance. As I know DB2 comes in one
32 & 64 bit installation version and I think it is 32 bit product able to
run in 64 bit OS. Corrrect me if I am wrong and, please, give some tip how
to create 64 bit DB instance.

sincerely Olek
Nov 12 '05 #6

P: n/a

Użytkownik "Smutny30" <sm******@poczta.onet.pl> napisał w wiadomości
news:d4**********@news.onet.pl...

Użytkownik "Mark A" <no****@nowhere.com> napisał w wiadomości
news:ua********************@comcast.com...
"Smutny30" <sm******@poczta.onet.pl> wrote in message
news:d4**********@news.onet.pl...

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


I think I have 64 bit OS since it is the newest AIX 5L.

I have no information about 64 bit DB2 instance. As I know DB2 comes in
one 32 & 64 bit installation version and I think it is 32 bit product able
to run in 64 bit OS. Corrrect me if I am wrong and, please, give some tip
how to create 64 bit DB instance.


Hello , I found out that db2icrt has -w switch that declares the instance to
be 32 or 64. Is it a correct way ?

sincerely Olek
Nov 12 '05 #7

P: n/a
Smutny30 wrote:
I have no information about 64 bit DB2 instance. As I know DB2 comes in one
32 & 64 bit installation version and I think it is 32 bit product able to
run in 64 bit OS. Corrrect me if I am wrong and, please, give some tip how
to create 64 bit DB instance.


Output of db2level would indicate whether you are using 32- or 64-bit instance.

db2icrt command has also -w option which allows you to specify word width of
the instance.
Jan M. Nelken

(Nie badz taki smutny...)
Nov 12 '05 #8

P: n/a

Uzytkownik "Jan M. Nelken" <Un**********@Invalid.Domain> napisal w
wiadomosci news:42********@news1.prserv.net...
Smutny30 wrote:
I have no information about 64 bit DB2 instance. As I know DB2 comes in
one 32 & 64 bit installation version and I think it is 32 bit product
able to run in 64 bit OS. Corrrect me if I am wrong and, please, give
some tip how to create 64 bit DB instance.
Output of db2level would indicate whether you are using 32- or 64-bit
instance.

db2icrt command has also -w option which allows you to specify word width
of the instance.


Hello,

I have already created an 64 instance. I will try to create my db in that
instance. Only one doubt:

$ ps -ef | grep db2
dasusr1 344318 1 0 20:45:43 - 0:00
/u01/home/dasusr1/das/bin/db2fmd -i dasusr1 -m
/u01/home/dasusr1/das/lib/libdb2dasgcf.a
root 823534 1 0 20:45:43 - 0:00
/usr/opt/db2_08_01/bin/db2fmcd

I can see some 32 bit processes are running. What they are ? How to change
them to 64 bit ?
Is db2admin independent of 32 or 64 bit instance ?

Sincerely Olek.

Jan M. Nelken

(Nie badz taki smutny...)

A skad znasz polski ? Dzieki za pomoc.
Nov 12 '05 #9

P: n/a
"Smutny30" <sm******@poczta.onet.pl> wrote in message
news:d4**********@news.onet.pl...

Hello , I found out that db2icrt has -w switch that declares the instance
to be 32 or 64. Is it a correct way ?

sincerely Olek

Yes, but you will have create a new instance, you cannot change the existing
32 bit instance to 64 bit.
Nov 12 '05 #10

P: n/a

Użytkownik "Mark A" <no****@nowhere.com> napisał w wiadomości
news:p6********************@comcast.com...
"Smutny30" <sm******@poczta.onet.pl> wrote in message
news:d4**********@news.onet.pl...

Hello , I found out that db2icrt has -w switch that declares the instance
to be 32 or 64. Is it a correct way ?

sincerely Olek

Yes, but you will have create a new instance, you cannot change the
existing 32 bit instance to 64 bit.


I have already created a 64 bit instance. But when trying to create DB I
obtain: "SQL1032N No start database manager command was issued.
SQLSTATE=57019". I issued "db2admin start" but it not helped. Maybe some 64
bit db2admin is also needed ?

sincerely Olek
Nov 12 '05 #11

P: n/a
> I have already created a 64 bit instance. But when trying to create DB I
obtain: "SQL1032N No start database manager command was issued.
SQLSTATE=57019". I issued "db2admin start" but it not helped. Maybe some
64 bit db2admin is also needed ?

sincerely Olek

You need db2start to start the instance. "db2admin start" starts the
administrative server. Logon as the instance owner before you issue
db2start.
Nov 12 '05 #12

P: n/a

Użytkownik "Mark A" <no****@nowhere.com> napisał w wiadomości
news:6M********************@comcast.com...
I have already created a 64 bit instance. But when trying to create DB I
obtain: "SQL1032N No start database manager command was issued.
SQLSTATE=57019". I issued "db2admin start" but it not helped. Maybe some
64 bit db2admin is also needed ?

sincerely Olek

You need db2start to start the instance. "db2admin start" starts the
administrative server. Logon as the instance owner before you issue
db2start.


Believe me I knew it. Big thanks Mark. I really appreciate your support. It
seems I am on a correct way ...
sincerely Olek
Nov 12 '05 #13

P: n/a

Użytkownik "Smutny30" <sm******@poczta.onet.pl> napisał w wiadomości
news:d4**********@news.onet.pl...
Hello,

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

Hello,

I have now my 64-bit DB2 instance up and runing, and allocating all 2,4 GB
for bufferpools. Great appreciation for all the people that helped me to
solve it.

sincerely Olek
Nov 12 '05 #14

P: n/a
Ian
Smutny30 wrote:
Użytkownik "Mark A" <no****@nowhere.com> napisał w wiadomości
news:ua********************@comcast.com...
"Smutny30" <sm******@poczta.onet.pl> wrote in message
news:d4**********@news.onet.pl...
As I said, I have free physical memory shwown by "svmon -G".


DB2 cannot address that much memory in a 32 bit instance. The maximum
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.

Hello Mark, thank you for your answer.

Your remarks sound probably, basic on my experience with one big bufferpool
that I described in separate answer in this thread. I was able to allocate
one 1,5 GB bufferpool, that added to some other db2 alocations (dbheap,
agents, sort heap) would give 1,75 GB.

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

I think I have 64 bit OS since it is the newest AIX 5L.


Also make sure that you're running a 64-bit kernel. The p5 servers with
AIX 5.3 preloaded I've worked on have all come with the 32 bit kernel
by default instead of the 64-bit kernel. 'bootinfo -K' will tell you
what kernel you have.

Nov 12 '05 #15

P: n/a
Ian
Mark A wrote:
"Smutny30" <sm******@poczta.onet.pl> wrote in message
news:d4**********@news.onet.pl...
Hello , I found out that db2icrt has -w switch that declares the instance
to be 32 or 64. Is it a correct way ?

sincerely Olek


Yes, but you will have create a new instance, you cannot change the existing
32 bit instance to 64 bit.


Yes, you can. db2iupdt -w 64 <instance>

Nov 12 '05 #16

P: n/a
Uzytkownik "Ian" <ia*****@mobileaudio.com> napisal w wiadomosci
news:42********@newsfeed.slurp.net...
Smutny30 wrote:
Użytkownik "Mark A" <no****@nowhere.com> napisał w wiadomości
news:ua********************@comcast.com...
"Smutny30" <sm******@poczta.onet.pl> wrote in message
news:d4**********@news.onet.pl...
Also make sure that you're running a 64-bit kernel. The p5 servers with
AIX 5.3 preloaded I've worked on have all come with the 32 bit kernel
by default instead of the 64-bit kernel. 'bootinfo -K' will tell you
what kernel you have.


Hello Ian , I'm 64-bit

bash-2.05b# bootinfo -K
64

Ian, maybe you know wheather I should change daemons:

# bash
bash-2.05b# ps -ef | grep db2
dasusr1 327836 1 0 14:29:08 - 0:01
/u01/home/dasusr1/das/bin/db2fmd -i dasusr1 -m
/u01/home/dasusr1/das/lib/libdb2dasgcf.a
root 643326 1 0 14:29:08 - 0:09
/usr/opt/db2_08_01/bin/db2fmcd
Both db2fmd and db2fmcd have their 64-bit versions.

Currently I run 64-bit instance with these 32-bit daemons.

Sincerely Olek
Nov 12 '05 #17

P: n/a
"Ian" <ia*****@mobileaudio.com> wrote in message
news:42********@newsfeed.slurp.net...

Yes, but you will have create a new instance, you cannot change the
existing 32 bit instance to 64 bit.


Yes, you can. db2iupdt -w 64 <instance>


Thanks, I didn't know that.

Here is doc for db2iupdt from the 8.1 Command Reference:

-w WordWidth
Specifies the width, in bits, of the instance to be created. Valid values
are 32 and 64. This parameter is only valid on AIX, HP-UX, and the Solaris
Operating Environment. The requisite version of DB2 must be installed
(32-bit or 64-bit).

and the doc for db2iupdt in 8.2 Command Reference:

-w WordWidth
Specifies the width, in bits, of the instance to be created. Valid values
are 31, 32, and 64. This parameter is only valid on AIX, HP-UX, Linux for
AMD64, and the Solaris Operating Environment. The requisite version of DB2
must be installed (31-bit, 32-bit, or 64-bit). The default value is the bit
width of the instance that is being updated.

I noitce that in both cases, the say "of the instance to be created". I
wonder why that is? Just a typo?
Nov 12 '05 #18

P: n/a
Ian
Mark A wrote:
"Ian" <ia*****@mobileaudio.com> wrote in message
news:42********@newsfeed.slurp.net...
Yes, but you will have create a new instance, you cannot change the
existing 32 bit instance to 64 bit.


Yes, you can. db2iupdt -w 64 <instance>


Thanks, I didn't know that.

Here is doc for db2iupdt from the 8.1 Command Reference:

-w WordWidth
Specifies the width, in bits, of the instance to be created. Valid values
are 32 and 64. This parameter is only valid on AIX, HP-UX, and the Solaris
Operating Environment. The requisite version of DB2 must be installed
(32-bit or 64-bit).

and the doc for db2iupdt in 8.2 Command Reference:

-w WordWidth
Specifies the width, in bits, of the instance to be created. Valid values
are 31, 32, and 64. This parameter is only valid on AIX, HP-UX, Linux for
AMD64, and the Solaris Operating Environment. The requisite version of DB2
must be installed (31-bit, 32-bit, or 64-bit). The default value is the bit
width of the instance that is being updated.

I noitce that in both cases, the say "of the instance to be created". I
wonder why that is? Just a typo?


Probably a typo. My guess is that the person writing the doc copied the
text from the 'db2icrt' command.

Nov 12 '05 #19

P: n/a
"Ian" <ia*****@mobileaudio.com> wrote in message
news:42**********@newsfeed.slurp.net...
I noitce that in both cases, the say "of the instance to be created". I
wonder why that is? Just a typo?


Probably a typo. My guess is that the person writing the doc copied the
text from the 'db2icrt' command.

Probably, but since they changed the text for 8.2, I would have thought that
some would have read the text and noticed it.
Nov 12 '05 #20

P: n/a
Smutny30 wrote:

I have now my 64-bit DB2 instance up and runing, and allocating all 2,4 GB
for bufferpools. Great appreciation for all the people that helped me to
solve it.

sincerely Olek


Plz. post solution, may help a lot of people who are stuck.

-R-

Nov 12 '05 #21

P: n/a
"Jurgen Haan" <ju****@fake.dom> wrote in message
news:42*********************@news.xs4all.nl...
I have now my 64-bit DB2 instance up and runing, and allocating all 2,4
GB for bufferpools. Great appreciation for all the people that helped me
to solve it.

sincerely Olek


Plz. post solution, may help a lot of people who are stuck.

-R-

The solution was to create a 64 bit instance instead of a 32 bit instance.
That way, the limit on the amount of memory that a DB2 instance can use is
no longer restricted to 1.75 GB for AIX (limit depends on OS).
Nov 12 '05 #22

P: n/a
Mark A wrote:
"Jurgen Haan" <ju****@fake.dom> wrote in message
news:42*********************@news.xs4all.nl...
I have now my 64-bit DB2 instance up and runing, and allocating all 2,4
GB for bufferpools. Great appreciation for all the people that helped me
to solve it.

sincerely Olek


Plz. post solution, may help a lot of people who are stuck.

-R-


The solution was to create a 64 bit instance instead of a 32 bit instance.
That way, the limit on the amount of memory that a DB2 instance can use is
no longer restricted to 1.75 GB for AIX (limit depends on OS).


Ah... apologies. Due to the seperate post in the thread I thought it was
solved by different means than the mere 32 -> 64 bit conversion.

-R-
Nov 12 '05 #23

This discussion thread is closed

Replies have been disabled for this discussion.