We're new into DB2 and have some problem with DB2 8.1 (?) on a Linux system.
We load some big tables of a DB2 database from files every day. We do a
"DELETE FROM table" for each table and then we load the data by something
like that:
LOAD FROM "/pathto/artikel.data" OF DEL MODIFIED BY DATESISO CHARDEL0xbf
SAVECOUNT 50000
MESSAGES "/pathto/artikel.msg"
INSERT INTO artikel;
Although there is only a slightly bigger amount of data every day, the
number of used pages in USERSPACE1 (DMS) is constantly increasing until
after some days there's no free space. It looks almost like the old data is
still kept in USERPSACE1.
After we dropped all the tables that are loaded daily, all the space was
free again. We could then create the tables again and load the same data
without problems and with still having tons of free pages.
We tried to modify NUM_DB_BACKUPS and REC_HIS_RETENTN but the used pages are
still growing.
Any idea? Thanks in advance for your help.
Olaf
--8<--
db2inst1> db2 get db cfg for liqua
Database Configuration for Database liqua
Database configuration release level = 0x0a00
Database release level = 0x0a00
Database territory = DE
Database code page = 923
Database code set = ISO885915
Database country/region code = 49
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
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20
Backup pending = NO
Database is consistent = NO
Rollforward pending = NO
Restore pending = NO
Multi-page file allocation enabled = NO
Log retain for recovery status = NO
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) = 1200
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4)
Log buffer size (4KB) (LOGBUFSZ) = 8
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
Buffer pool size (pages) (BUFFPAGE) = 20000
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) = 100
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) = 256
SQL statement heap (4KB) (STMTHEAP) = 2048
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) = 10
Lock timeout (sec) (LOCKTIMEOUT) = -1
Changed pages threshold (CHNGPGS_THRESH) = 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1
Number of I/O servers (NUM_IOSERVERS) = 3
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32
Track modified pages (TRACKMOD) = OFF
Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
Max number of active applications (MAXAPPLS) = 40
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 64
Log file size (4KB) (LOGFILSIZ) = 200000
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 2
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 =
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) = OFF
User exit for logging enabled (USEREXIT) = OFF
Auto restart enabled (AUTORESTART) = ON
Index re-creation time (INDEXREC) = SYSTEM (RESTART)
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 2
Recovery history retention (days) (REC_HIS_RETENTN) = 2
TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =
db2inst1> 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) = 2,834066e-07
Max number of concurrently active databases (NUMDB) = 8
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/lib/IBMJava2-1.3.1
Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 3
Diagnostic data directory path (DIAGPATH) =
/home/db2inst1/sqllib/db2dump
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
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) =
Database manager authentication (AUTHENTICATION) = SERVER
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Use SNA authentication (USE_SNA_AUTH) = NO
Bypass federated authentication (FED_NOAUTH) = NO
Default database path (DFTDBPATH) = /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) = 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) = 100(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 client connections (MAX_CONNECTIONS) = MAX_COORDAGENTS
Keep fenced process (KEEPFENCED) = YES
Number of pooled fenced processes (FENCED_POOL) = MAX_COORDAGENTS
Initialize fenced process with JVM (INITFENCED_JVM) = NO
Initial number of fenced processes (NUM_INITFENCED) = 0
Index re-creation time (INDEXREC) = RESTART
Transaction manager database name (TM_DATABASE) = 1ST_CONN
Transaction resync interval (sec) (RESYNC_INTERVAL) = 180
SPM name (SPM_NAME) =
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
Discovery communication protocols (DISCOVER_COMM) = TCPIP
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 5 3994
Hi
just reorg all your tables to reclaim free space after massive deletes
Paul
Olaf Gschweng wrote: We're new into DB2 and have some problem with DB2 8.1 (?) on a Linux system.
We load some big tables of a DB2 database from files every day. We do a "DELETE FROM table" for each table and then we load the data by something like that:
LOAD FROM "/pathto/artikel.data" OF DEL MODIFIED BY DATESISO CHARDEL0xbf SAVECOUNT 50000 MESSAGES "/pathto/artikel.msg" INSERT INTO artikel;
Although there is only a slightly bigger amount of data every day, the number of used pages in USERSPACE1 (DMS) is constantly increasing until after some days there's no free space. It looks almost like the old data is still kept in USERPSACE1.
After we dropped all the tables that are loaded daily, all the space was free again. We could then create the tables again and load the same data without problems and with still having tons of free pages.
We tried to modify NUM_DB_BACKUPS and REC_HIS_RETENTN but the used pages are still growing.
Any idea? Thanks in advance for your help.
Olaf
--8<--
db2inst1> db2 get db cfg for liqua
Database Configuration for Database liqua
Database configuration release level = 0x0a00 Database release level = 0x0a00
Database territory = DE Database code page = 923 Database code set = ISO885915 Database country/region code = 49
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 Number of frequent values retained (NUM_FREQVALUES) = 10 Number of quantiles retained (NUM_QUANTILES) = 20
Backup pending = NO
Database is consistent = NO Rollforward pending = NO Restore pending = NO
Multi-page file allocation enabled = NO
Log retain for recovery status = NO 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) = 1200 Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4) Log buffer size (4KB) (LOGBUFSZ) = 8 Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000 Buffer pool size (pages) (BUFFPAGE) = 20000 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) = 100
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) = 256 SQL statement heap (4KB) (STMTHEAP) = 2048 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) = 10 Lock timeout (sec) (LOCKTIMEOUT) = -1
Changed pages threshold (CHNGPGS_THRESH) = 60 Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1 Number of I/O servers (NUM_IOSERVERS) = 3 Index sort flag (INDEXSORT) = YES Sequential detect flag (SEQDETECT) = YES Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32
Track modified pages (TRACKMOD) = OFF
Default number of containers = 1 Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
Max number of active applications (MAXAPPLS) = 40 Average number of active applications (AVG_APPLS) = 1 Max DB files open per application (MAXFILOP) = 64
Log file size (4KB) (LOGFILSIZ) = 200000 Number of primary log files (LOGPRIMARY) = 3 Number of secondary log files (LOGSECOND) = 2 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 = 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) = OFF User exit for logging enabled (USEREXIT) = OFF
Auto restart enabled (AUTORESTART) = ON Index re-creation time (INDEXREC) = SYSTEM (RESTART) Default number of loadrec sessions (DFT_LOADREC_SES) = 1 Number of database backups to retain (NUM_DB_BACKUPS) = 2 Recovery history retention (days) (REC_HIS_RETENTN) = 2
TSM management class (TSM_MGMTCLASS) = TSM node name (TSM_NODENAME) = TSM owner (TSM_OWNER) = TSM password (TSM_PASSWORD) =
db2inst1> 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) = 2,834066e-07
Max number of concurrently active databases (NUMDB) = 8 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/lib/IBMJava2-1.3.1
Diagnostic error capture level (DIAGLEVEL) = 3 Notify Level (NOTIFYLEVEL) = 3 Diagnostic data directory path (DIAGPATH) = /home/db2inst1/sqllib/db2dump
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 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) =
Database manager authentication (AUTHENTICATION) = SERVER Cataloging allowed without authority (CATALOG_NOAUTH) = NO Trust all clients (TRUST_ALLCLNTS) = YES Trusted client authentication (TRUST_CLNTAUTH) = CLIENT Use SNA authentication (USE_SNA_AUTH) = NO Bypass federated authentication (FED_NOAUTH) = NO
Default database path (DFTDBPATH) = /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) = 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) = 100(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 client connections (MAX_CONNECTIONS) = MAX_COORDAGENTS
Keep fenced process (KEEPFENCED) = YES Number of pooled fenced processes (FENCED_POOL) = MAX_COORDAGENTS Initialize fenced process with JVM (INITFENCED_JVM) = NO Initial number of fenced processes (NUM_INITFENCED) = 0
Index re-creation time (INDEXREC) = RESTART
Transaction manager database name (TM_DATABASE) = 1ST_CONN Transaction resync interval (sec) (RESYNC_INTERVAL) = 180
SPM name (SPM_NAME) = 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 Discovery communication protocols (DISCOVER_COMM) = TCPIP 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
Olaf Gschweng wrote: We're new into DB2 and have some problem with DB2 8.1 (?) on a Linux system.
We load some big tables of a DB2 database from files every day. We do a "DELETE FROM table" for each table and then we load the data by something like that:
When you delete records, in most cases DB2 does not return empty pages
to the "free space" in the tablespace. This is done to enhance
performance, but the penalty is that it requires periodic maintenance,
especially if you're deleting large amounts of data.
You can use the REORG utility to ask DB2 to reclaim the unused space.
However, see below:
LOAD FROM "/pathto/artikel.data" OF DEL MODIFIED BY DATESISO CHARDEL0xbf SAVECOUNT 50000 MESSAGES "/pathto/artikel.msg" INSERT INTO artikel;
If you're re-loading the entire table, try using the REPLACE INTO syntax
instead of doing a delete and then using LOAD ... INSERT INTO. MUCH
faster, and no space issues.
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =----- http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----
Olaf Gschweng <in*****@invalid.invalid> wrote in message news:<2p************@uni-berlin.de>... We're new into DB2 and have some problem with DB2 8.1 (?) on a Linux system.
We load some big tables of a DB2 database from files every day. We do a "DELETE FROM table" for each table and then we load the data by something like that:
LOAD FROM "/pathto/artikel.data" OF DEL MODIFIED BY DATESISO CHARDEL0xbf SAVECOUNT 50000 MESSAGES "/pathto/artikel.msg" INSERT INTO artikel;
Although there is only a slightly bigger amount of data every day, the number of used pages in USERSPACE1 (DMS) is constantly increasing until after some days there's no free space. It looks almost like the old data is still kept in USERPSACE1.
After we dropped all the tables that are loaded daily, all the space was free again. We could then create the tables again and load the same data without problems and with still having tons of free pages.
We tried to modify NUM_DB_BACKUPS and REC_HIS_RETENTN but the used pages are still growing.
Any idea? Thanks in advance for your help.
Olaf
Try using the REPLACE option on the load. Or do a reorg of the table.
The delete would leave some hole in the data pages, those holes might be
fill in by the following insert operations. Load utility will bypass SQL
interface, in order to improve the load performance.
All the reponse already told you how to reclaim those unused space from data
pages. I will not say more.
"Olaf Gschweng" <in*****@invalid.invalid> wrote in message
news:2p************@uni-berlin.de... We're new into DB2 and have some problem with DB2 8.1 (?) on a Linux
system. We load some big tables of a DB2 database from files every day. We do a "DELETE FROM table" for each table and then we load the data by something like that:
LOAD FROM "/pathto/artikel.data" OF DEL MODIFIED BY DATESISO CHARDEL0xbf SAVECOUNT 50000 MESSAGES "/pathto/artikel.msg" INSERT INTO artikel;
Although there is only a slightly bigger amount of data every day, the number of used pages in USERSPACE1 (DMS) is constantly increasing until after some days there's no free space. It looks almost like the old data
is still kept in USERPSACE1.
After we dropped all the tables that are loaded daily, all the space was free again. We could then create the tables again and load the same data without problems and with still having tons of free pages.
We tried to modify NUM_DB_BACKUPS and REC_HIS_RETENTN but the used pages
are still growing.
Any idea? Thanks in advance for your help.
Olaf
--8<--
db2inst1> db2 get db cfg for liqua
Database Configuration for Database liqua
Database configuration release level = 0x0a00 Database release level = 0x0a00
Database territory = DE Database code page = 923 Database code set = ISO885915 Database country/region code = 49
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 Number of frequent values retained (NUM_FREQVALUES) = 10 Number of quantiles retained (NUM_QUANTILES) = 20
Backup pending = NO
Database is consistent = NO Rollforward pending = NO Restore pending = NO
Multi-page file allocation enabled = NO
Log retain for recovery status = NO 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) = 1200 Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*4) Log buffer size (4KB) (LOGBUFSZ) = 8 Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000 Buffer pool size (pages) (BUFFPAGE) = 20000 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) = 100
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) = 256 SQL statement heap (4KB) (STMTHEAP) = 2048 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) = 10 Lock timeout (sec) (LOCKTIMEOUT) = -1
Changed pages threshold (CHNGPGS_THRESH) = 60 Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1 Number of I/O servers (NUM_IOSERVERS) = 3 Index sort flag (INDEXSORT) = YES Sequential detect flag (SEQDETECT) = YES Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32
Track modified pages (TRACKMOD) = OFF
Default number of containers = 1 Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
Max number of active applications (MAXAPPLS) = 40 Average number of active applications (AVG_APPLS) = 1 Max DB files open per application (MAXFILOP) = 64
Log file size (4KB) (LOGFILSIZ) = 200000 Number of primary log files (LOGPRIMARY) = 3 Number of secondary log files (LOGSECOND) = 2 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 = 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) = OFF User exit for logging enabled (USEREXIT) = OFF
Auto restart enabled (AUTORESTART) = ON Index re-creation time (INDEXREC) = SYSTEM
(RESTART) Default number of loadrec sessions (DFT_LOADREC_SES) = 1 Number of database backups to retain (NUM_DB_BACKUPS) = 2 Recovery history retention (days) (REC_HIS_RETENTN) = 2
TSM management class (TSM_MGMTCLASS) = TSM node name (TSM_NODENAME) = TSM owner (TSM_OWNER) = TSM password (TSM_PASSWORD) =
db2inst1> 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) = 2,834066e-07
Max number of concurrently active databases (NUMDB) = 8 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/lib/IBMJava2-1.3.1
Diagnostic error capture level (DIAGLEVEL) = 3 Notify Level (NOTIFYLEVEL) = 3 Diagnostic data directory path (DIAGPATH) = /home/db2inst1/sqllib/db2dump
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 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) =
Database manager authentication (AUTHENTICATION) = SERVER Cataloging allowed without authority (CATALOG_NOAUTH) = NO Trust all clients (TRUST_ALLCLNTS) = YES Trusted client authentication (TRUST_CLNTAUTH) = CLIENT Use SNA authentication (USE_SNA_AUTH) = NO Bypass federated authentication (FED_NOAUTH) = NO
Default database path (DFTDBPATH) = /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) = 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) = 100(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 client connections (MAX_CONNECTIONS) = MAX_COORDAGENTS
Keep fenced process (KEEPFENCED) = YES Number of pooled fenced processes (FENCED_POOL) = MAX_COORDAGENTS Initialize fenced process with JVM (INITFENCED_JVM) = NO Initial number of fenced processes (NUM_INITFENCED) = 0
Index re-creation time (INDEXREC) = RESTART
Transaction manager database name (TM_DATABASE) = 1ST_CONN Transaction resync interval (sec) (RESYNC_INTERVAL) = 180
SPM name (SPM_NAME) = 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 Discovery communication protocols (DISCOVER_COMM) = TCPIP 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
Thank you all for your quick and helpful replies. As we load more than one
file per table, I think we will use REORG.
Olaf
--
gso This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Seth Broomer |
last post by:
Hi, i have a .aspx Page, inside that page is a web user control.
The user control is basically a login control.
if the user is logged in the page shows one thing.
if the user isn't logged in...
|
by: Chris Zoper |
last post by:
Hello,
Norton Internet Security blocks some of my ASP.NET pages. The pages are
very 'normal' pages though. There is no 'dangerous' code in it or
something like that. Also, the page is not in a...
|
by: shandra |
last post by:
I have a file I need to delete or truncate. I tried using the KILL
command in VB6. I tried using the file.delete command in VB.net. I
tried manually deleting, renaming, and copying over the...
|
by: 418928 |
last post by:
Hi everybody,
I would like to know if there is some trick to avoid loading an HTML
page again is it is already loaded in another browser tab. In that
case, instead of loading the page again, I...
|
by: Phil |
last post by:
Jerry posed some good ideas, a while back, on website a security issue
that comes up often. Gary Jones was asking how to keep users from
directly accessing php pages, out of sequence.
Jerry...
|
by: vinayakk |
last post by:
Hello webies,
I have a problem with loading html pages in frameset. I am using location.href to load html pages one by one upon the user request and these pages in turn load flash files. When one...
|
by: Bart Steur |
last post by:
Hi,
I'm writing an app to maintain products. The products are listed in a
listbox and when I click a product in a listbox some info of that product is
shown including a picture of the product.
...
|
by: Freelancer Deepak |
last post by:
how i can increase my flash loading on my flash websites
|
by: krungkrung |
last post by:
hi again to everyone!
I made a simple program(for my VB.Net practice). The program loads an image file to a picturebox upon clicking a button. after loading the image file i have another button to...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |