Are There Any Performance Issues When Long, Complex SQL Statements are Used to Update or Read the STCTBL?
Article ID: 118705
Updated On:
Products
OPS/MVS Event Management & Automation
Issue/Introduction
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
Resolution
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
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
Feedback
Yes
No
Powered by
