PH13428: SMARTER REORG RECOMMENDATIONS PERFORMANCE IMPROVEMENTS - REDUCE CONSUMPTION OF CPU

IBM Db2 Query Monitor for z/OS CAE Browser Client download
Obtain the fix for this APAR.

APAR status

• Closed as new function.

Error description

• Smarter Reorg Recommendations performance improvements - reduce
  consumption of CPU.
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: IBM Db2 Query Monitor for z/OS 3.3.0         *
  *                 users of component                           *
  *                 - CAE server                                 *
  *                 - CAE agent & Networking                     *
  *                 - Documentation - Public use                 *
  *                 - CQM Data Collector                         *
  *                 - Reorg Avoidance                            *
  *                 - TCz                                        *
  *                 - Data Offload                               *
  ****************************************************************
  * PROBLEM DESCRIPTION: 1.High CPU consumption by CAE Agent     *
  *                      task after performance window with      *
  *                      large job profile.                      *
  *                                                              *
  *                      2. Column names in the window don't     *
  *                      contain offload column names.           *
  *                                                              *
  *                      3. Introduce AT-TLS support in Query    *
  *                      Monitor.                                *
  *                      CAE Server Key Store configuration      *
  *                      was changed. Now it requires 2          *
  *                      certificate aliases instead of 1.       *
  *                      The first one is WEBSERVER - the        *
  *                      certificate alias for https             *
  *                      connections.                            *
  *                      The second one is                       *
  *                      CAE_AGENT_PORTAL - the certificate      *
  *                      alias for CAE Agent TLS socket.         *
  *                                                              *
  *                      4. SAMPLIBs and DENULIB (comments with  *
  *                      MAXPARTITIONS information).             *
  ****************************************************************
  * RECOMMENDATION: APPLY the PTF.                               *
  ****************************************************************
  1. This issue can happen if a customer has a large job profile
  for the Reorganization Recommendation Monitor feature.
  After each performance window CPU, consumption increases in the
  CAE Agent Task.
  
  2. Thresholds and Analyze pop-up windows show only names of
  metrics, e.g; 'Total Getpages', 'CPU time'.
  
  3. CAE Server and CAE Agents use RC4 encryption to communicate
  with each other over the network. To overcome known security
  issues with RC4 (multiple vulnerabilities, non-random keys) CAE
  Server and CAE Agents can now be configured to use AT-TLS only.
  - Now the CAE Server and CAE Agents can be configured to use
  AT-TLS instead of RC4 encryption.
  - To enable the feature the user has to generate 2 certificate
  aliases (WEBSERVER and CAE_AGENT_PORTAL) using
  cqm_cert_install.bat (Windows) or CQMCCERT JCL (USS) and import
  them into CAE Server Key Store using the cqm_import_certs.bat
  script (Windows) or CQMICERT JCL(USS).
  Note: The previous user
  custom (or default self-signed) certificate alias can be renamed
  to WEBSERVER by passing -reusewebserver parameter
  (cqm_import_certs.bat script for Windows) or
  CQM_REUSE_WEBSERVER_KS parameter (CQMICERT JCL for USS). In that
  case the first free alias in Key Store will be renamed to
  WEBSERVER.
  - CAE_AGENT_PORTAL certificate alias should be exported using
  cqm_export_cert.bat script (Windows) or CQMECERT JCL (USS) and
  should be imported into LPAR keyring for outbound connections
  using CQMICERT JCL (USS).
  - The user has to create RACF keyring for inbound connections
  and import any private certificate alias into it using CQMICERT
  JCL (USS).
  - The user has to export public certificate alias from inbound
  keyring using CQMECERT JCL (USS) and import it into CAE Server
  truststore using -importincacerts parameter of the
  cqm_import_certs.bat script (Windows) or using CQMICERT job
  (USS). Note: The CAE Server on USS does not have it's own
  truststore repository, so the public certificate alias should be
  imported into CAE Server Key Store.
  - The user has to define AT-TLS rules for inbound and outbound
  connections in his TCPPARMS.
  - 3449 port becomes the default AT-TLS port. To change it the
  user has to override CQM_TLS_CAE_AGNT_LSTNR_PORT parameter (USS)
  or com.rocketsoft.nm.qm.caeAgent.tlsListenerPort parameter
  (Windows) in CAE Server parameters.
  - A new warning message "CQMC1036W: reuseWebserver option is not
  compatible with RACF keystore. Skipping." will be issued if
  CQM_REUSE_WEBSERVER_KS is set to Y and CQM_KEYSTORE_TYPE = RACF.
  - A new CAE Server warning message "CQMC2550W CAE Server
  KeyStore does not contain alias WEBSERVER_ALIAS. Since PTF
  PH13428 we recommend to include private certificate for HTTPS
  webserver with alias WEBSERVER_ALIAS" was added.
  - A new CAE Server warning message "CQMC2551W Alias
  CAE_AGENT_PORTAL_ALIAS is not found in CAE Server KeyStore.
  Default TLS socket implementation will be used for listening
  CAE Agents" was added.
  - A new cqmexportcert script and a new CQMECERT JCL were
  added (CAE Server on USS) and a new cqm_export_cert.bat script
  was added (CAE Server on Windows)
  
  4.There were no suggestions in the jobs and scripts to change
  the MAX PARTITIONS value for large tables. As a result, the
  value MAX PARTITION = 1 was used, which led to the need to
  execute the ALTER function to change the MAXPARTITIONS value
  for some tables.
  

Problem conclusion

• 1. This APAR will improve the performance of the CAE Agent when
  a customer uses a large job profile for the Reorganization
  Recommendation Monitor feature.
  
  2. The APAR will extend info in Thresholds and Analyze pop-up
  windows with names of offload table columns, e.g.:
  'Total Getpages (GETPAGES)', 'CPU time (DB2_CPU)'
  
  3. With this feature the user can open AT-TLS and RC4 sockets.
  CAE Server and CAE Agent will continue to use RC4 encryption
  unless user disables opening RC4 sockets by specifying
  RC4_PROTOCOL_ENABLED=FALSE (USS) or
  com.rocketsoft.nm.qm.caeAgent.rc4ProtocolEnabled=false in CAE
  Server parameters. CAE Server may support CAE Agents with AT-TLS
  and CAE Agents with RC4 simultaneously, but it is recommended to
  disable RC4 support once you have migrated all CAE Agents to use
  AT-TLS.
  
  4. The following comment "THE MAXPARTITIIONS VALUE FOR A TABLE
  MAY NEED TO BE INCREASED DEPENDING ON THE EXPECTED SIZE OF THE
  TABLE" has been added to jobs and scripts.
  This comment does not affect the operation of the job or script.
  
  Search Keywords:
  PERFORMANCE REORG AVOIDANCE
  THRESHOLDS POP-UP WINDOW
  CAE Server AT-TLS Network CAE Agent CQMC1036W CQMC2550W
  CQMC2551W
  MAXPARTITIONS
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  PH31669 UI72625

Modules/Macros

• CQM#QAPI CQM#QSQL CQM$$PRM CQM$YXLG CQMCAE   CQMCAEPT CQMCCRT
  CQMDDL   CQMICERT CQMICRT  CQMJCC4  CQMSSV   CQMUPXPT CQMUTS2
  CQMUTS3
  

Fix information

• Fixed component name

  DB2 QUERY MONIT

• Fixed component ID

  5655E6701

Applicable component levels

• R330 PSY UI72625

     UP20/11/19 P F011  

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.