PH36246: CA7 INGESTION TAKES A LONG TIME

APAR status

• Closed as fixed if next.

Error description

• When processing a CA7 listing, it takes 30 minutes to remove the
  old data, 3.25 hours to process the 75,439 jobs (which is
  reasonable) then 5 hours performing some repository processing
  (two example SQL statements below).
  
  UPDATE CA7_JobsDependentOnJobs cjdj
      SET cjdj . DependentOn_JobID = ( SELECT cj1 . JobID FROM
  CA7_Jobs
          cj1 INNER JOIN CA7_Libraries cl1 ON cj1 . LibID = cl1 .
  LibID
      where cl1 . RootID = cjdj . RootID
            AND UPPER ( cj1 . JOB_NAME ) = UPPER ( cjdj .
            DependentOn_JobName ) FETCH FIRST ROWS ONLY )
      WHERE EXISTS ( SELECT 1 FROM CA7_Jobs cj2 INNER JOIN
  CA7_Libraries
            cl2 ON cj2 . LibID = cl2 . LibID
      where cl2 . RootID = cjdj . RootID
            AND UPPER ( cj2 . JOB_NAME ) = UPPER ( cjdj .
            DependentOn_JobName ) )
  
  UPDATE CA7_JobsTriggeredByJobs cjtj
      SET cjtj . TriggeredBy_JOBID = ( SELECT cj1 . JobID FROM
  CA7_Jobs
          cj1 INNER JOIN CA7_Libraries cl1 ON cj1 . LibID = cl1 .
  LibID
      WHERE cl1 . RootID = cjtj . RootID
            AND UPPER ( cj1 . JOB_NAME ) = UPPER ( cjtj .
            TriggeredBy_JobName ) FETCH FIRST ROWS ONLY )
      WHERE EXISTS ( SELECT 1 FROM CA7_Jobs cj2 INNER JOIN
  CA7_Libraries
            cl2 ON cj2 . LibID = cl2 . LibID
      WHERE cl2 . RootID = cjtj . RootID
            AND UPPER ( cj2 . JOB_NAME ) = UPPER ( cjtj .
            TriggeredBy_JobName ) )
  
  The first took more than 3 hours and the second more than 30
  minutes.
  

Local fix

• N/A
  

Problem summary

• Problem summary: 'CA7 import' post-process takes too long to
  execute on DB2 on Z.
  Problem conclusion: UPDATE queries(which are executed during
  'CA7 import' post-process) have been modified, eliminating calls
  to the UPPER function.
  

Problem conclusion

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:

Fix information

Applicable component levels