Monthly Archives: September 2017

Support ticket ID not sent back to Supplier system

OOB the support ticket ID (PTxxx) is not propagated back to the supplier system when the synchronization is done.

Solution

The property sx.onboarding.sendPropelHumanReadableIdToSupplier is set to false by default on /opt/hp/propel/sx/WEB-INF/sx.properties

This needs to be updated to true and restart jetty-sx service. After this, the PT number will be send back to supplier system (SM) and will be shown at the beginning of the interaction (SD) title.

Advertisements

SM: the supported configuration of SM LB and HW LB

Customer is building an environment with both SM LB and HW LB.
They are planning to load balancing the SM app servers (#1 and #2) by using both a Hardware Load Balancer (HWLB) and the SM Software Load Balancer (SWLB).
Like below:

SM APP VIP(HWLB)
/ \
SM LB#1 SM LB #2
SM APP#1 SM APP#2

They want to know whether this kind of configuration is supported or not.


Solution

No, this type of configuration is not supported.
For each deployed SM environment (e.g. Production, Development, Testing, etc) there is only one SM load balancer process allowed.
We cannot run more than one SM load balancer process in a deployed environment.

Load balncing in SM is allowed to be:

1) HWLB only (disable SWLB process)
or
2) SWLB only (disable HWLB) and activate only one LB process (cannot have multiple LB process running)

How to trace query in Oracle for performance tuning

1. check location for trace file
SQL> show parameter user_dump_dest
NAME                                 TYPE        VALUE
———————————— ———– ——————————
user_dump_dest                       string      c:\app\administrator\diag\rdbms\ora11\ora11\trace

2. set environment in a session
SQL> alter session set TIMED_STATISTICS = true;
SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER = ‘hpsupport_trace_id’;
SQL> alter session set SQL_TRACE = true;

3. execute queries
SQL> SELECT m1.”UNIQUE_KEY”,m1.”FILE_NAME”,m1.”NAME”,m1.”COMPONENT”
FROM APPROVALM1 m1 JOIN APPROVALA1 a1
ON (((m1.”FILE_NAME” = a1.”FILE_NAME”)
OR (m1.”FILE_NAME” IS NULL AND a1.”FILE_NAME” IS NULL)) AND ((m1.”COMPONENT” = a1.”COMPONENT”)
OR (m1.”COMPONENT” IS NULL AND a1.”COMPONENT” IS NULL)) AND ((m1.”UNIQUE_KEY” = a1.”UNIQUE_KEY”)
OR (m1.”UNIQUE_KEY” IS NULL AND a1.”UNIQUE_KEY” IS NULL)) AND ((m1.”NAME” = a1.”NAME”)
OR (m1.”NAME” IS NULL AND a1.”NAME” IS NULL)))
WHERE ((m1.”APPROVAL_STATUS”=’pending’ and
(m1.”APPROVALS_RECORD” IS NULL or m1.”APPROVALS_RECORD”=’t’)
and (m1.”FILE_NAME”=’cm3r’ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’,’Application’,’Field Support (North America)’,’Hardware’,’Network’,’Service Manager’) or
m1.”FILE_NAME”=’cm3t’ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’,’Application’,’Field Support (North America)’,’Hardware’,’Network’,’Service Manager’) or
m1.”FILE_NAME”=’ocm1′ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’) or m1.”FILE_NAME”=’incidents’
and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’) or m1.”FILE_NAME”=’svcCartItem’ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’))))
ORDER BY m1.”UNIQUE_KEY” ASC,m1.”FILE_NAME” ASC,m1.”NAME” ASC,m1.”COMPONENT” ASC

4. close trace and run TKPROF 
SQL> alter session set SQL_TRACE = false;

file is created in
c:\app\administrator\diag\rdbms\ora11\ora11\trace\ora11_ora_4448_hpsupport_trace_id.trc
c:\app\administrator\diag\rdbms\ora11\ora11\trace\ora11_ora_4448_hpsupport_trace_id.trm
CMD> cd c:\app\administrator\diag\rdbms\ora11\ora11\trace

generate result file
CMD> tkprof  ora11_ora_4448_hpsupport_trace_id.trc result1.txt

5. verify results
cmd> notepad result1.txt
example)
********************************************************************************

 SELECT m1.”UNIQUE_KEY”,m1.”FILE_NAME”,m1.”NAME”,m1.”COMPONENT”
FROM APPROVALM1 m1 JOIN APPROVALA1 a1
ON (((m1.”FILE_NAME” = a1.”FILE_NAME”)
OR (m1.”FILE_NAME” IS NULL AND a1.”FILE_NAME” IS NULL)) AND ((m1.”COMPONENT” = a1.”COMPONENT”)
OR (m1.”COMPONENT” IS NULL AND a1.”COMPONENT” IS NULL)) AND ((m1.”UNIQUE_KEY” = a1.”UNIQUE_KEY”)
OR (m1.”UNIQUE_KEY” IS NULL AND a1.”UNIQUE_KEY” IS NULL)) AND ((m1.”NAME” = a1.”NAME”)
OR (m1.”NAME” IS NULL AND a1.”NAME” IS NULL)))
WHERE ((m1.”APPROVAL_STATUS”=’pending’ and
(m1.”APPROVALS_RECORD” IS NULL or m1.”APPROVALS_RECORD”=’t’)
and (m1.”FILE_NAME”=’cm3r’ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’,’Application’,’Field Support (North America)’,’Hardware’,’Network’,’Service Manager’) or
m1.”FILE_NAME”=’cm3t’ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’,’Application’,’Field Support (North America)’,’Hardware’,’Network’,’Service Manager’) or
m1.”FILE_NAME”=’ocm1′ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’) or m1.”FILE_NAME”=’incidents’
and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’) or m1.”FILE_NAME”=’svcCartItem’ and a1.”CURRENT_PENDING_GROUPS” IN (‘Change.Approver’))))
ORDER BY m1.”UNIQUE_KEY” ASC,m1.”FILE_NAME” ASC,m1.”NAME” ASC,m1.”COMPONENT” ASC

call     count       cpu    elapsed       disk      query    current        rows
——- ——  ——– ———- ———- ———- ———-  ———-
Parse        1      0.09       0.10          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.04       0.19        198       1960          0           8
——- ——  ——– ———- ———- ———- ———-  ———-
total        4      0.14       0.30        198       1960          0           8

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 

Rows     Row Source Operation
——-  —————————————————
8  SORT ORDER BY (cr=1960 pr=198 pw=0 time=0 us cost=695 size=2030 card=10)
8   CONCATENATION  (cr=1960 pr=198 pw=0 time=224 us)
0    NESTED LOOPS  (cr=190 pr=187 pw=0 time=0 us)
0     NESTED LOOPS  (cr=190 pr=187 pw=0 time=0 us cost=68 size=203 card=1)
0      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=187 pw=0 time=0 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=149 card=1)
0    NESTED LOOPS  (cr=201 pr=5 pw=0 time=0 us)
0     NESTED LOOPS  (cr=201 pr=5 pw=0 time=0 us cost=70 size=203 card=1)
8      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=63 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=11 pr=5 pw=0 time=0 us cost=1 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=149 card=1)
0    NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us)
0     NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us cost=68 size=203 card=1)
0      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=0 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=149 card=1)
0    NESTED LOOPS  (cr=201 pr=0 pw=0 time=0 us)
0     NESTED LOOPS  (cr=201 pr=0 pw=0 time=0 us cost=70 size=203 card=1)
8      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=7 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=11 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=149 card=1)
0    NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us)
0     NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us cost=68 size=203 card=1)
0      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=0 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=149 card=1)
0    NESTED LOOPS  (cr=201 pr=0 pw=0 time=0 us)
0     NESTED LOOPS  (cr=201 pr=0 pw=0 time=0 us cost=70 size=203 card=1)
8      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=14 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=11 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=149 card=1)
0    NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us)
0     NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us cost=68 size=203 card=1)
0      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=0 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=149 card=1)
0    NESTED LOOPS  (cr=201 pr=0 pw=0 time=0 us)
0     NESTED LOOPS  (cr=201 pr=0 pw=0 time=0 us cost=71 size=203 card=1)
8      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=7 us cost=69 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=11 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=2 size=149 card=1)
0    NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us)
0     NESTED LOOPS  (cr=190 pr=0 pw=0 time=0 us cost=68 size=203 card=1)
0      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=0 us cost=68 size=54 card=1)
0      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 74630)
0     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=149 card=1)
8    NESTED LOOPS  (cr=206 pr=6 pw=0 time=210 us)
8     NESTED LOOPS  (cr=201 pr=0 pw=0 time=133 us cost=71 size=203 card=1)
8      TABLE ACCESS FULL APPROVALA1 (cr=190 pr=0 pw=0 time=28 us cost=69 size=54 card=1)
8      INDEX RANGE SCAN APPROVALM169BD06C3 (cr=11 pr=0 pw=0 time=0 us cost=1 size=0 card=3)(object id 74630)
8     TABLE ACCESS BY INDEX ROWID APPROVALM1 (cr=5 pr=6 pw=0 time=0 us cost=2 size=149 card=1)

********************************************************************************

SM Need to specify preferredFQHN if there are multiple FQDNs

If you are using SSO at 9.34.3003 with the following pattern:
ServiceManager Server <——> Tomcat(WebTier) <—> IIS <—> Web browser(login to SSO)

If you are planning to upgrade the RTE to 9.41.2003.
Upgrade was performed at the same environment,and all the configuration was moved to 9.41 from 9.34 without change.
But they found the configuration which worked at 9.34, now didn’t work at 9.41, and the SSO login failed.

After checking with customer’s environment, we found there are two FQDNs.
Horizontal scaling is also implemented.

We suggest to perform one of the following configuration, then the login worked fine.
1. at sm.ini, specify “preferredFQHN” to one of the FQDNs.
2. at host file, specify one of the FQDNs.

Solution

There hasn’t been any change at SSO from 9.34 to 9.41.
But there was a loadbalancer issue fixed at 9.41 QCCR1E111013.

The scenarios are as follows:
1. With both preferredFQHN and groupbindaddress => Service Manager loadBalancer redirects client requests to the preferredFQHN.
2. With preferredFQHN and without groupbindaddress => Service Manager loadBalancer redirects client requests to the preferredFQHN.
3. With groupbindaddress and without preferredFQHN => Service Manager loadBalancer redirects client requests to the hostname which is matched with groupbindaddress.
4. Both preferredFQHN and groupbindaddress are not existed => Service Manager loadBalancer redirects client requests to the local hostname.

So from 9.41, if there are multiple FQDNs, it is a must to specify preferredFQHN.