SAP performance problem using HADR with db2 9.5

[Former Member]

Dear partners

I have implemented HADR with db2 9.5 with SAP ECC 5.0, when we changed hardware the SAP performance was seriously affected, now we only can sync database in the night because in the day cause poor performance and locks and users can´t work in SAP.

Can you help me to any idea to solve problem?

thanks

Erika

[malte_schuenemann]

- Is the performance OK in case HADR is switched off ?

- Did you run an SQL trace of some action inside SAP ? Which actions take extended time ? Is that some SQL statement(s), or e.g. COMMITs ?

- Did you run HADR snapshots / db2pd -db ... -hadr ? Any information from there ?

- which OS are you running on ?

- Which FP level of DB2 are you running on ?

Malte

[Former Member]

Thanks Malte

-When HADR is switched off the peerformance is OK.

- We have OS AIX 5.3, DB2 9.5 FP 3

The action that take long time are updates and spool process, for example NRIV table have many lock and printed documents ends with error, all workprocess are full.

When HADR is ON each log take 20 or 30 minutes to do retrieve to standby server. but this process takes 5 minutes

[malte_schuenemann]

Can you show the size of the rcp buffers for send + receive ? To get them, use

no -a

What does the HADR snapshot say at times when the problem is visible ?

Malte

[Former Member]

HADR only continue the sync but we see the spool process response time increase to 200%

The command no -a display

CI and DB server are equal

arpqsize = 12

arpt_killc = 20

arptab_bsiz = 7

arptab_nb = 149

bcastping = 0

clean_partial_conns = 0

delayack = 0

delayackports = {}

dgd_packets_lost = 3

dgd_ping_time = 5

dgd_retry_time = 5

directed_broadcast = 0

extendednetstats = 0

fasttimo = 200

icmp6_errmsg_rate = 10

icmpaddressmask = 0

ie5_old_multicast_mapping = 0

ifsize = 256

inet_stack_size = 16

ip6_defttl = 64

ip6_prune = 1

ip6forwarding = 0

ip6srcrouteforward = 1

ip_ifdelete_notify = 0

ip_nfrag = 200

ipforwarding = 0

ipfragttl = 2

ipignoreredirects = 1

ipqmaxlen = 100

ipsendredirects = 1

ipsrcrouteforward = 1

ipsrcrouterecv = 1

ipsrcroutesend = 1

llsleep_timeout = 3

lo_perf = 1

lowthresh = 90

main_if6 = 0

main_site6 = 0

maxnip6q = 20

maxttl = 255

medthresh = 95

mpr_policy = 1

multi_homed = 1

nbc_limit = 2654208

nbc_max_cache = 131072

nbc_min_cache = 1

nbc_ofile_hashsz = 12841

nbc_pseg = 0

nbc_pseg_limit = 5308416

ndd_event_name = ndd_event_tracing = 0 ndp_mmaxtries = 3 ndp_umaxtries = 3 ndpqsize = 50 ndpt_down = 3 ndpt_keep = 120 ndpt_probe = 5 ndpt_reachable = 30 ndpt_retrans = 1 net_buf_size =

net_buf_type =

net_malloc_police = 0

nonlocsrcroute = 1

nstrpush = 8

passive_dgd = 0

pmtu_default_age = 10

pmtu_expire = 10

pmtu_rediscover_interval = 30

psebufcalls = 20

psecache = 1

pseintrstack = 24576

psetimers = 20

rfc1122addrchk = 0

rfc1323 = 0

rfc2414 = 1

route_expire = 1

routerevalidate = 1

rto_high = 64

rto_length = 13

rto_limit = 7

rto_low = 1

sack = 0

sb_max = 1048576

send_file_duration = 300

site6_index = 0

sockthresh = 85

sodebug = 0

sodebug_env = 0

somaxconn = 1024

strctlsz = 1024

strmsgsz = 0

strthresh = 85

strturncnt = 15

subnetsarelocal = 1

tcp_bad_port_limit = 0

tcp_ecn = 0

tcp_ephemeral_high = 65535

tcp_ephemeral_low = 32768

tcp_finwait2 = 1200

tcp_icmpsecure = 0

tcp_init_window = 0

tcp_inpcb_hashtab_siz = 24499

tcp_keepcnt = 8

tcp_keepidle = 14400

tcp_keepinit = 150

tcp_keepintvl = 150

tcp_limited_transmit = 1

tcp_low_rto = 0

tcp_maxburst = 0

tcp_mssdflt = 1460

tcp_nagle_limit = 65535

tcp_nagleoverride = 0

tcp_ndebug = 100

tcp_newreno = 1

tcp_nodelayack = 0

tcp_pmtu_discover = 1

tcp_recvspace = 16384

tcp_sendspace = 16384

tcp_tcpsecure = 0

tcp_timewait = 1

tcp_ttl = 60

tcprexmtthresh = 3

thewall = 10616832

timer_wheel_tick = 0

udp_bad_port_limit = 0

udp_ephemeral_high = 65535

udp_ephemeral_low = 32768

udp_inpcb_hashtab_siz = 24499

udp_pmtu_discover = 1

udp_recvspace = 42080

udp_sendspace = 9216

udp_ttl = 30

udpcksum = 1

use_isno = 1

use_sndbufpool = 1

This weekend saw the delay in server dialog to connect to the central instance by saplogon when we try to enter and appear error message

"Failed to load distribution routine for the system input rc = 82, err = 7; logon group" ALFA01 "not found - check spelling and case sensitivity"

And HADR was off

Thanks

Erika Llanos

[malte_schuenemann]

If I remember old problem cases correctly, the values

tcp_recvspace = 16384

tcp_sendspace = 16384

are by far too low. To my memory, 131072 still was too low and the recommendation was to use 262144.

I think you can adjust the settings with the command no ..

Malte

[Former Member]

What syncmode are you running your HADR instances under? You will find this information in the database configuration for the HADR_SYNCMODE parameter.

- Sameer

[brian_walker]

This question appears to be for DB2 LUW and not DB2 z/OS, so this forum is not the best place for this question

[thomas_vogt]

Moved thread to DB6 forum.