Does anyone out there have experience with using Db2 8.2 with a program
called MISys (also known as ACCPAC). I am having some problems with the
application running exteremly slow on workstations throughout my
office. One of the more accessed tables, which only has 3000 rows, take
almost 30 seconds to open through their application. MISys insists that
it is a db2 tweaking problem and to check my configuration settings.
When I select the same informatiom from the command line the retreival
is instantaneuos. Here is my current configuration for the DB and DB
Manager. There should be more then enough memory and processing power
on the server (Dual Xeon 3.2 Ghz, 4 Gb Ram). Anyone see any problems
that would cause serious slow down.



Database Configuration for Database



Database configuration release level = 0x0a00

Database release level = 0x0a00



Database territory = US

Database code page = 1252

Database code set = IBM-1252

Database country/region code = 1

Database collating sequence = UNIQUE

Alternate collating sequence (ALT_COLLATE) =

Database page size = 4096



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

Rollforward pending = NO

Restore pending = NO



Multi-page file allocation enabled = YES



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

Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC

Catalog cache size (4KB) (CATALOGCACHE_SZ) = 973

Log buffer size (4KB) (LOGBUFSZ) = 132

Utilities heap size (4KB) (UTIL_HEAP_SZ) = 88338

Buffer pool size (pages) (BUFFPAGE) = 400000

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



Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 16523

Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70

Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 160



Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 26646

Sort list heap (4KB) (SORTHEAP) = 192

SQL statement heap (4KB) (STMTHEAP) = 2048

Default application heap (4KB) (APPLHEAPSZ) = 256

Package cache size (4KB) (PCKCACHESZ) = 1149

Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384



Interval for checking deadlock (ms) (DLCHKTIME) = 10000

Percent. of lock lists per application (MAXLOCKS) = 60

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

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

Average number of active applications (AVG_APPLS) = 1

Max DB files open per application (MAXFILOP) = 64



Log file size (4KB) (LOGFILSIZ) = 1024

Number of primary log files (LOGPRIMARY) = 3

Number of secondary log files (LOGSECOND) = 0

Changed path to log files (NEWLOGPATH) =

Path to log files =
C:\DB2\NODE0000\SQL00002\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) = 120

Log retain for recovery enabled (LOGRETAIN) = OFF

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

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

Automatic database backup (AUTO_DB_BACKUP) = OFF

Automatic table maintenance (AUTO_TBL_MAINT) = ON

Automatic runstats (AUTO_RUNSTATS) = ON

Automatic statistics profiling (AUTO_STATS_PROF) = OFF

Automatic profile updates (AUTO_PROF_UPD) = OFF

Automatic reorganization (AUTO_REORG) = ON

The server OS is Windows Server 2003. All the workstations are Windows
XP or Windows 2000.

In article <1145632652.800286.49430@z34g2000cwc.googlegroups. com>,
briggs@lsmp.com says...

> Utilities heap size (4KB) (UTIL_HEAP_SZ) = 88338
> Buffer pool size (pages) (BUFFPAGE) = 400000
> Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 26646
>

You need to check if the sizes mentioned above are not too high. Check
the size of the available bufferpools with the command 'select char
(bpname, 30) as bpname, npages, pagesize from syscat.bufferpools'. If
NPAGES = -1 then the bufferpool uses at least 1.6 GB, depending on the
pagesize. This might be too much for your system, check if it's paging
too much.
With a bufferpool of this size it will also take some extra time for
your first connection to connect because all memory needs to be
allocated. To prevent this you can activate the db with the ACTIVATE
DATABASE command. Before creating a backup you need to use the
DEACTIVATE DATABASE command because otherwise the backup will fail.

When I run the SQL statement select char(bpname,30) as bpname, npages,
pagesize from syscat.bufferpools I get the following results
BPNAME NPAGES PAGESIZE
------------------------------ ----------- -----------
IBMDEFAULTBP 350000 4096

How can I tell if the system is paging to much?

Ray

Gert,
I don't think this is a memory/bufferpool issue. Otherwise, Ray should
see db2 error messages in the windows server event log.

Ray:
I think you have an user authentication problem. Check how long does it
take you to connect to server with your network username & password?
Then create a local user on the server machine and then connect with
this user. If there is a big difference in between them, this can
explain your problem.

Regards,

Mehmet