DB2 Memory High in SAP

[Former Member]

Hi Team,

We have allocated 82 GB memory for SAP production database DB2. Database always occupied more than 98% always. Please let us know which db2 process occupied more memory.

topas_nmon──.=OnlyBusyMode─────Host=huxp0012───────Refresh=2 secs───08:04.31─────────────────────────────────────────────────────────────────────────────────────────┐

│ Memory ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────│

│          Physical  PageSpace |        pages/sec  In     Out | FileSystemCache                                                                                        │

│% Used      98.6%      2.1%  | to Paging Space   0.0    0.0 | (numperm) 17.5%                                                                                        │

│% Free        1.4%     97.9%  | to File System   32.5  107.5 | Process   61.1%                                                                                        │

│MB Used   80780.6MB   955.2MB | Page Scans        0.0        | System    19.9%                                                                                        │

│MB Free    1139.4MB 44100.8MB | Page Cycles       0.0        | Free       1.4%                                                                                        │

│Total(MB) 81920.0MB 45056.0MB | Page Steals       0.0        |           ------                                                                                       │

│                              | Page Faults      56.5        | Total    100.0%                                                                                        │

│------------------------------------------------------------ | numclient 17.5%                                                                                        │

│Min/Maxperm     2383MB(  3%)  71495MB( 90%) <--% of RAM      | maxclient 90.0%                                                                                        │

│Min/Maxfree     960   1088       Total Virtual  124.0GB      | User      75.7%                                                                                        │

│Min/Maxpgahead    2      8    Accessed Virtual   64.6GB 52.1%| Pinned    21.3%                                                                                        │

│                                                             | lruable pages   20336400.0                                                                             │

│ Network ─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────│

│I/F Name Recv=KB/s Trans=KB/s packin packout insize outsize Peak->Recv TransKB                                                                                        │

│    en0       0.2       1.7      3.0     17.5   65.3   97.8        8.7   170.8                                                                                        │

│    en1    7276.7   69863.1  28124.3  74410.0  264.9  961.4    11943.8 116139.2                                                                                       │

│    en2     380.8      73.4    586.5    441.5  664.8  170.2      435.1    73.4                                                                                        │

│    en3       0.0       0.0      0.5      0.0   46.0    0.0        0.8     0.7                                                                                        │

│    en4       0.0       0.0      0.5      0.0   46.0    0.0      143.8 17602.7                                                                                        │

│    lo0       0.1       0.1      0.5      0.5  116.0  116.0       93.8    93.8                                                                                        │

│  Total       7.5      68.3 in Mbytes/second   Overflow=0                                                                                                             │

│I/F Name  MTU  ierror oerror collision Mbits/s Description                                                                                                            │

│    en0   1500      0      0      0  10240 Standard Ethernet Network Interface                                                                                        │

│    en1   1500      0      0      0  10240 Standard Ethernet Network Interface                                                                                        │

│    en2   1500      0      0      0  10240 Standard Ethernet Network Interface                                                                                        │

│    en3   1500      0      0      0  10240 Standard Ethernet Network Interface                                                                                        │

│    en4   1500      0      0      0  10240 Standard Ethernet Network Interface                                                                                        │

│    lo0  16896      0      0      0      0 Loopback Network Interface 

Regards,

MK

[Former Member]

Hi, MK

Try  db2mtrk -i -d -v -p   and   db2pd -db FCP -mempools/memsets/memblocks

Best Regards,

Dmitry Krivov

[former_member188883]

Hi MK,

1) Please share configuration value for db2 parameter instance_memory

2) Share db2 release and patch level

Regards,

Deepak Kori

[Former Member]

Hi Team,

Please find the requested information below.

     Database Manager Configuration

     Node type = Enterprise Server Edition with local and remote clients

Database manager configuration release level            = 0x0d00

CPU speed (millisec/instruction)             (CPUSPEED) = 3.345772e-07

Communications bandwidth (MB/sec)      (COMM_BANDWIDTH) = 1.000000e+02

Max number of concurrently active databases     (NUMDB) = 8

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) = /db2/db2ttp/sqllib/java/jdk64

Diagnostic error capture level              (DIAGLEVEL) = 3

Notify Level                              (NOTIFYLEVEL) = 3

Diagnostic data directory path               (DIAGPATH) = /db2/FCP/db2dump

Alternate diagnostic data directory path (ALT_DIAGPATH) =

Size of rotating db2diag & notify logs (MB)  (DIAGSIZE) = 150

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

Monitor health of instance and databases   (HEALTH_MON) = OFF

SYSADM group name                        (SYSADM_GROUP) = DBFCPADM

SYSCTRL group name                      (SYSCTRL_GROUP) = DBFCPCTL

SYSMAINT group name                    (SYSMAINT_GROUP) = DBFCPMNT

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

Cluster manager                           (CLUSTER_MGR) =

Database manager authentication        (AUTHENTICATION) = SERVER_ENCRYPT

Alternate authentication           (ALTERNATE_AUTH_ENC) = NOT_SPECIFIED

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) = /db2/FCP

Database monitor heap size (4KB)          (MON_HEAP_SZ) = AUTOMATIC(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) = 13107200

Agent stack size                       (AGENT_STACK_SZ) = 2048

Sort heap threshold (4KB)                  (SHEAPTHRES) = 0

Directory cache support                     (DIR_CACHE) = NO

Application support layer heap size (4KB)   (ASLHEAPSZ) = 16

Max requester I/O block size (bytes)         (RQRIOBLK) = 65535

Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10

Priority of agents                           (AGENTPRI) = SYSTEM

Agent pool size                        (NUM_POOLAGENTS) = AUTOMATIC(100)

Initial number of agents in pool       (NUM_INITAGENTS) = 5

Max number of coordinating agents     (MAX_COORDAGENTS) = AUTOMATIC(200)

Max number of client connections      (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)

Keep fenced process                        (KEEPFENCED) = YES

Number of pooled fenced processes         (FENCED_POOL) = 10

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

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

Discovery mode                               (DISCOVER) = SEARCH

Discover server instance                (DISCOVER_INST) = ENABLE

SSL server keydb file                   (SSL_SVR_KEYDB) =

SSL server stash file                   (SSL_SVR_STASH) =

SSL server certificate label            (SSL_SVR_LABEL) =

SSL service name                         (SSL_SVCENAME) =

SSL cipher specs                      (SSL_CIPHERSPECS) =

SSL versions                             (SSL_VERSIONS) =

SSL client keydb file                  (SSL_CLNT_KEYDB) =

SSL client stash file                  (SSL_CLNT_STASH) =

Maximum query degree of parallelism   (MAX_QUERYDEGREE) = ANY

Enable intra-partition parallelism     (INTRA_PARALLEL) = NO

Maximum Asynchronous TQs per query    (FEDERATED_ASYNC) = 0

No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(4096)

No. of int. communication channels   (FCM_NUM_CHANNELS) = AUTOMATIC(2048)

Node connection elapse time (sec)         (CONN_ELAPSE) = 10

Max number of node connection retries (MAX_CONNRETRIES) = 5

Max time difference between nodes (min) (MAX_TIME_DIFF) = 60

db2start/db2stop timeout (min)        (START_STOP_TIME) = 10

Informational tokens are "DB2 v9.7.0.5", "special_28492", "IP23285_28492", and

Fix Pack "5".

Regards,

MK

[Former Member]

Hi All,

Is any help on this post.

Regards,

MK

[Former Member]

Hello Manikandan Kamatchichettiyar,

First of all you have to decide whether you do a manual or automatic backup deletion for obsolete backups which you can run with

db2adutl delete...

or setting AUTO_DEL_REC_OBJ = ON.

For the other parameter of auto pruning read the document carefully.

Both deletetions depends on your backup and recovery scenario with the SLA's how long you would keep your backups.

Best regards,

Joachim