Are There Any Performance Issues When Long, Complex SQL Statements are Used to Update or Read the STCTBL?

Document ID : KB000118705
Last Modified Date : 30/10/2018
Show Technical Document Details
Question:
Are there any performance issues when long, complex SQL statements are used to update or read the STCTBL?

After having Strobed many DB2 jobs, it became evident that long SQL statements caused performance issues.

Is this applicable in OPS/MVS?
Can long statements cause timing issues?

Please evaluate the below code regarding efficiency:

Update SQL SSM.SSMEOM to added "AND XUP_UP='D'", after line 283.
000282 address SQL "UPDATE STCTBL SET XUP_UP='R'",
000283 "WHERE NAME='"name.1"' AND DESIRED_STATE = 'UP'",
000284 "AND 'Y' IN (SELECT CMD_RESTART FROM POSTCMD",
000285 "WHERE CMD_TYPE IN ('"NAME.1"','"TYPE.1"')",
000286 "AND CMD_POST = 'START' AND CMD_TABLE = 'STCTBL'",
000287 "AND CMD_ACTIVE = 'Y' AND CMD_RESTART = 'Y')"
Environment:
CA OPS/MVS - All releases
Answer:
The query could possibly be written using an Update Query with an Inner Join on the two tables, and then do the SET, WHERE, and clauses.
Please review your table schemas, for the STCTBL and the POSTCMD table, to decide which two fields would join the tables in the beginning.

Please refer to the W3Schools link found below for SQL Reference: 

https://stackoverflow.com/questions/3867164/sql-update-query-syntax-with-inner-join

 which includes:
UPDATE CostEntry CE
INNER JOIN ActiveCostDetails As AD ON CostEntry.lUniqueID = ActiveCostDetails.UniqueID
SET CostEntry.sJobNumber = ActiveCostDetails.JobNumber
WHERE CostEntry.SEmployeeCode = '002'
AND SubString(CostCentre, 1, 1) = sDepartmentCode
AND substring(CostCentre, 3, 1) = sCategoryCode
AND substring(CostCentre, 5, 2) = sOperationCode

Note: You would need to fix the INNER Join, with two fields that are common between the two tables, as shown on line 2.

This is a link to an article regarding performance of SQL queries:  https://stackoverflow.com/questions/21011650/update-with-subquery-vs-update-with-join-which-is-better-in-performance