News
Abstract
Automated DBE tasks for Navigator
Content
You are in: IBM i Technology Updates > Db2 for i - Technology Updates > Db2 for i Performance Enhancements > Automated DBE tasks for Navigator
More procedures have been added to allow customers to automate frequent tasks related to database performance and performance analysis.
These procedures can be used in conjunction with the QSYS2/DUMP_PLAN_CACHE() and QSYS2/DUMP_PLAN_CACHE_topN() procedures to automate the collection, import and removal of a SQE Plan Cache snapshots. Prior to the existence of these procedures, the user would have to manually choose the Import or Delete action within IBM i Navigator.
- QSYS2/IMPORT_PLAN_CACHE() procedure
- QSYS2/REMOVE_PLAN_CACHE() procedure
- QSYS2/IMPORT_EVENT_MONITOR() procedure
- QSYS2/REMOVE_EVENT_MONITOR() procedure
See the IBM i Knowledge Center for details: Plan cache services
Example usage:
CREATE OR REPLACE PROCEDURE SNAP_AND_IMPORT()
LANGUAGE SQL
BEGIN
DECLARE SNAP_NAME CHAR(10);
DECLARE OLDEST_SNAP_NAME CHAR(10);
DECLARE SNAP_COMMENT VARCHAR(100);
SET SNAP_NAME = 'SNAP' CONCAT DAYOFYEAR(current date) CONCAT
SUBSTR(YEAR(current date),3,2);
SUBSTR(YEAR(current date),3,2);
SET OLDEST_SNAP_NAME = 'SNAP' CONCAT
DAYOFYEAR(current date - 60 days) CONCAT
SUBSTR(YEAR(current date - 60 days),3,2);
DAYOFYEAR(current date - 60 days) CONCAT
SUBSTR(YEAR(current date - 60 days),3,2);
CALL QSYS2.DUMP_PLAN_CACHE_topN('SNAPSHOTS', SNAP_NAME, 100);
CALL QSYS2.IMPORT_PC_SNAPSHOT('SNAPSHOTS',
SNAP_NAME, 'Top 100 Queries-' CONCAT CHAR(CURRENT DATE));
SNAP_NAME, 'Top 100 Queries-' CONCAT CHAR(CURRENT DATE));
CALL QSYS2.REMOVE_PC_SNAPSHOT('SNAPSHOTS', OLDEST_SNAP_NAME);
END;
CALL SNAP_AND_IMPORT();
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Was this topic helpful?
Document Information
Modified date:
21 January 2020
UID
ibm11171144