used pages increase after deleting and loading again

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

re: used pages increase after deleting and loading again

Hi
just reorg all your tables to reclaim free space after massive deletes

Paul


Olaf Gschweng wrote:[color=blue]
> 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[/color]

re: used pages increase after deleting and loading again

Olaf Gschweng wrote:[color=blue]
> 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:[/color]

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:
[color=blue]
> LOAD FROM "/pathto/artikel.data" OF DEL MODIFIED BY DATESISO CHARDEL0xbf
> SAVECOUNT 50000
> MESSAGES "/pathto/artikel.msg"
> INSERT INTO artikel;[/color]

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

re: used pages increase after deleting and loading again

Olaf Gschweng <invalid@invalid.invalid> wrote in message news:<2pqur6Fntc9cU1@uni-berlin.de>...[color=blue]
> 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
>
>[/color]
Try using the REPLACE option on the load. Or do a reorg of the table.

re: used pages increase after deleting and loading again

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" <invalid@invalid.invalid> wrote in message
news:2pqur6Fntc9cU1@uni-berlin.de...[color=blue]
> We're new into DB2 and have some problem with DB2 8.1 (?) on a Linux[/color]
system.[color=blue]
>
> 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[/color]
is[color=blue]
> 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[/color]
are[color=blue]
> 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[/color]
(RESTART)[color=blue]
> 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
>[/color]

re: used pages increase after deleting and loading again

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