I am trying to go for the benefit of being able to start one instance
while the other remains. I have looked through all of the performance
guides and most show only configuration of one instance. If I run the
tuning wizard it does not seem like it takes into consideration that
there is another instance. Currently (which I do not think is right)
is that the same amount of memory is defined to each instance. Below
are the configurations of both instances: This is a test server so
far. I have 2 GB of memory total in the box. INSTANCE1 is the
"PRODUCTION" DB that is OLTP. INSTANCE2 will hold the archive database
which is less seldom used. Any recommendations on how I should set
this up?
InSTANCE1
Database Manager Configuration
Node type = Database Server with local and remote clients
Database manager configuration release level = 0x0900
Maximum total of files open (MAXTOTFILOP) = 16000
CPU speed (millisec/instruction) (CPUSPEED) =
4.644720e-007
Max number of concurrently active databases (NUMDB) = 8
Data Links support (DATALINKS) = NO
Federated Database System Support (FEDERATED) = YES
Transaction processor monitor name (TP_MON_NAME) =
Default charge-back account (DFT_ACCOUNT_STR) =
Java Development Kit 1.1 installation path (JDK11_PATH) =
Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 2
Diagnostic data directory path (DIAGPATH) =
Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = OFF
Lock (DFT_MON_LOCK) = OFF
Sort (DFT_MON_SORT) = OFF
Statement (DFT_MON_STMT) = OFF
Table (DFT_MON_TABLE) = OFF
Unit of work (DFT_MON_UOW) = OFF
SYSADM group name (SYSADM_GROUP) =
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
Database manager authentication (AUTHENTICATION) = SERVER
Cataloging allowed without authority (CATALOG_NOAUTH) = YES
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Default database path (DFTDBPATH) = G:
Database monitor heap size (4KB) (MON_HEAP_SZ) = 32
UDF shared memory set size (4KB) (UDF_MEM_SZ) = 256
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Backup buffer default size (4KB) (BACKBUFSZ) = 1024
Restore buffer default size (4KB) (RESTBUFSZ) = 1024
Agent stack size (AGENT_STACK_SZ) = 16
Minimum committed private memory (4KB) (MIN_PRIV_MEM) = 32
Private memory threshold (4KB) (PRIV_MEM_THRESH) = 1296
Sort heap threshold (4KB) (SHEAPTHRES) = 10000
Directory cache support (DIR_CACHE) = YES
Application support layer heap size (4KB) (ASLHEAPSZ) = 15
Max requester I/O block size (bytes) (RQRIOBLK) = 32767
DOS requester I/O block size (bytes) (DOS_RQRIOBLK) = 4096
Query heap size (4KB) (QUERY_HEAP_SZ) = 1000
DRDA services heap size (4KB) (DRDA_HEAP_SZ) = 128
Priority of agents (AGENTPRI) = SYSTEM
Max number of existing agents (MAXAGENTS) = 200
Agent pool size (NUM_POOLAGENTS) = 4
(calculated)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = MAXAGENTS
Max no. of concurrent coordinating agents (MAXCAGENTS) =
MAX_COORDAGENTS
Max number of logical agents (MAX_LOGICAGENTS) =
MAX_COORDAGENTS
Keep DARI process (KEEPDARI) = YES
Max number of DARI processes (MAXDARI) =
MAX_COORDAGENTS
Initialize DARI process with JVM (INITDARI_JVM) = NO
Initial number of fenced DARI process (NUM_INITDARIS) = 0
Index re-creation time (INDEXREC) = ACCESS
Transaction manager database name (TM_DATABASE) = 1ST_CONN
Transaction resync interval (sec) (RESYNC_INTERVAL) = 180
SPM name (SPM_NAME) = MMPSSDB0
SPM log size (SPM_LOG_FILE_SZ) = 256
SPM resync agent limit (SPM_MAX_RESYNC) = 20
SPM log path (SPM_LOG_PATH) =
NetBIOS Workstation name (NNAME) =
TCP/IP Service name (SVCENAME) = db2cDB2
APPC Transaction program name (TPNAME) =
IPX/SPX File server name (FILESERVER) =
IPX/SPX DB2 server object name (OBJECTNAME) =
IPX/SPX Socket number (IPX_SOCKET) = 879E
Discovery mode (DISCOVER) = SEARCH
Discovery communication protocols (DISCOVER_COMM) = TCPIP
Discover server instance (DISCOVER_INST) = ENABLE
Directory services type (DIR_TYPE) = NONE
Directory path name (DIR_PATH_NAME) =
/.:/subsys/database/
Directory object name (DIR_OBJ_NAME) =
Routing information object name (ROUTE_OBJ_NAME) =
Default client comm. protocols (DFT_CLIENT_COMM) =
Default client adapter number (DFT_CLIENT_ADPT) = 0
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) = 512
Number of FCM connection entries (FCM_NUM_CONNECT) =
(FCM_NUM_RQB * 0.75)
Number of FCM message anchors (FCM_NUM_ANCHORS) =
(FCM_NUM_RQB * 0.75)
INSTANCE2
Database Manager Configuration
Node type = Database Server with local and remote clients
Database manager configuration release level = 0x0900
Maximum total of files open (MAXTOTFILOP) = 16000
CPU speed (millisec/instruction) (CPUSPEED) =
4.369185e-007
Max number of concurrently active databases (NUMDB) = 8
Data Links support (DATALINKS) = NO
Federated Database System Support (FEDERATED) = YES
Transaction processor monitor name (TP_MON_NAME) =
Default charge-back account (DFT_ACCOUNT_STR) =
Java Development Kit 1.1 installation path (JDK11_PATH) =
Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 2
Diagnostic data directory path (DIAGPATH) =
Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = OFF
Lock (DFT_MON_LOCK) = OFF
Sort (DFT_MON_SORT) = OFF
Statement (DFT_MON_STMT) = OFF
Table (DFT_MON_TABLE) = OFF
Unit of work (DFT_MON_UOW) = OFF
SYSADM group name (SYSADM_GROUP) =
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
Database manager authentication (AUTHENTICATION) = SERVER
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Default database path (DFTDBPATH) = G:
Database monitor heap size (4KB) (MON_HEAP_SZ) = 32
UDF shared memory set size (4KB) (UDF_MEM_SZ) = 256
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Backup buffer default size (4KB) (BACKBUFSZ) = 1024
Restore buffer default size (4KB) (RESTBUFSZ) = 1024
Agent stack size (AGENT_STACK_SZ) = 16
Minimum committed private memory (4KB) (MIN_PRIV_MEM) = 32
Private memory threshold (4KB) (PRIV_MEM_THRESH) = 1296
Sort heap threshold (4KB) (SHEAPTHRES) = 10000
Directory cache support (DIR_CACHE) = YES
Application support layer heap size (4KB) (ASLHEAPSZ) = 15
Max requester I/O block size (bytes) (RQRIOBLK) = 32767
DOS requester I/O block size (bytes) (DOS_RQRIOBLK) = 4096
Query heap size (4KB) (QUERY_HEAP_SZ) = 1000
DRDA services heap size (4KB) (DRDA_HEAP_SZ) = 128
Priority of agents (AGENTPRI) = SYSTEM
Max number of existing agents (MAXAGENTS) = 200
Agent pool size (NUM_POOLAGENTS) = 4
(calculated)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = MAXAGENTS
Max no. of concurrent coordinating agents (MAXCAGENTS) =
MAX_COORDAGENTS
Max number of logical agents (MAX_LOGICAGENTS) =
MAX_COORDAGENTS
Keep DARI process (KEEPDARI) = YES
Max number of DARI processes (MAXDARI) =
MAX_COORDAGENTS
Initialize DARI process with JVM (INITDARI_JVM) = NO
Initial number of fenced DARI process (NUM_INITDARIS) = 0
Index re-creation time (INDEXREC) = ACCESS
Transaction manager database name (TM_DATABASE) = 1ST_CONN
Transaction resync interval (sec) (RESYNC_INTERVAL) = 180
SPM name (SPM_NAME) = MMPSSDB1
SPM log size (SPM_LOG_FILE_SZ) = 256
SPM resync agent limit (SPM_MAX_RESYNC) = 20
SPM log path (SPM_LOG_PATH) =
NetBIOS Workstation name (NNAME) =
TCP/IP Service name (SVCENAME) =
APPC Transaction program name (TPNAME) =
IPX/SPX File server name (FILESERVER) =
IPX/SPX DB2 server object name (OBJECTNAME) =
IPX/SPX Socket number (IPX_SOCKET) = 879E
Discovery mode (DISCOVER) = SEARCH
Discovery communication protocols (DISCOVER_COMM) =
Discover server instance (DISCOVER_INST) = ENABLE
Directory services type (DIR_TYPE) = NONE
Directory path name (DIR_PATH_NAME) =
/.:/subsys/database/
Directory object name (DIR_OBJ_NAME) =
Routing information object name (ROUTE_OBJ_NAME) =
Default client comm. protocols (DFT_CLIENT_COMM) =
Default client adapter number (DFT_CLIENT_ADPT) = 0
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) = 512
Number of FCM connection entries (FCM_NUM_CONNECT) =
(FCM_NUM_RQB * 0.75)
Number of FCM message anchors (FCM_NUM_ANCHORS) =
(FCM_NUM_RQB * 0.75)
Blair Adamache <ba*******@2muchspam.yahoo.com> wrote in message news:<c8**********@hanover.torolab.ibm.com>...
Different releasse levels by instance is only supported on UNIX and
Linux (for now).
On large UNIX SMP's, I would always recommend one database per instance
to get the benefits of isolation - so a problem in one database is
isolated to one instance. The resources overhead per instance is low.
Also, registry variables and database config parameters are set at the
instance level (and can require the database to be stopped and restarted
to pick up new settings in some cases) - another benefit of isolation.
Chuck wrote:
While on the topic of multiple instances, I have a question. What's the
benefit of having multiple instances instead of one instance with
multiple databases? --
Chuck
Remove "_nospam" to reply by email
Pierre Saint-Jacques <se*****@attglobal.net> wrote in
news:TU********************@wagner.videotron.net:
You can look in the DB2 Admin. Guide Performance.
There'S a discussion there about memory utilisation.
You'll find it is not so much the instance as the active db's that
consume memory resources.
On the systems we use to teach, using Win2K, the DAS and the active
instance take about 55MB of memory. Whereas the db can "chew up" as
much as 1.5GB.
Iwould think that, unlees you are SMP (MultiProcessors) enabled, after
starting the DAS, you could use 30MB per started instance.
Also visiting the Admin. Guide, investigate the parms. in the DBM CFG.
Search in Google with" db2 and performance", all the DB2 docs. can be
searched with Google.
HTH, Pierre.
Mike wrote:
Looking to find any information on how to properly configure multiple
instances of DB2. This is on Win2k db2 ver 7.2. I am basically
looking for information on how the multiple instance settings should
configured to work, how memory is shared or not, etc. I can not seem
to find any good links to this information.
Thanks,
Mike
