Class Reference
IRIS for UNIX 2019.2
InterSystems: The power behind what matters   
Documentation  Search
  [%SYS] >  [%SYS] >  [PTools] >  [UtilSQLStatements]
Private  Storage   

persistent class %SYS.PTools.UtilSQLStatements extends %Persistent

------------------------------------------------------------------------------- Class: %SYS.PTools.UtilSQLStatements Replaces: %SYS.PTools.SQLUtilities [DEPRECATED] Purpose: This class is used to store the SQL Statement details collected from the following Locations within InterSystems IRIS for Index Analysis, Performance Optimizations, and Additional Processing: - cached queries - Class Methods - Class Queries - MAC Routines The following Methods and Queries, defined in the abstract interface class %SYS.PTools.UtilSQLAnalysis, are used to collect, analyze, and optimize the SQL Statements in this class: Methods Queries ------------------------------- ----------------------------------- - getAllSQLStmts - getSQLStmts - getSQLStmts - getAllCachedQrySQLStmts - getCachedQrySQLStmtsByDays - getCachedQrySQLStmtsByClass - getAllClassMethSQLStmts - getClassMethSQLStmtsByClass - getAllClassQrySQLStmts - getClassQrySQLStmtsByClass - getAllRtnQrySQLStmts - getRtnQrySQLStmtsByRtn - getAllIndices - indexUsage - indexUsage - tableScans - tableScans - tempIndices - tempIndices - joinIndices - joinIndices - outlierIndices - outlierIndices - exportSQLUtilStmts - exportSQLUtilStmtRslts - exportSQLUtilities - clearSQLStatements - clearSQLAnalysisDB - countSQLStmtTypes - UtilSQLStmtsAndAnalysisDBView Data Storage: ^%sqlcq("SAMPLES","PTools","util","SQLStmts","{C|D|I|S}") Error Storage: ^%sqlcq($NAMESPACE,"PTools","Error"[...])=$LIST Info -------------------------------------------------------------------------------

Inventory

Parameters Properties Methods Queries Indices ForeignKeys Triggers
6 2 1 1


Summary

Properties
ImportPackage MethodName Name SQLTextExt SQLTextRaw
Type

Methods
%AddToSaveSet %AddToSyncSet %BMEBuilt %CheckConstraints
%CheckConstraintsForExtent %ClassIsLatestVersion %ClassName %ComposeOid
%ConstructClone %Delete %DeleteExtent %DeleteId
%DispatchClassMethod %DispatchGetModified %DispatchGetProperty %DispatchMethod
%DispatchSetModified %DispatchSetMultidimProperty %DispatchSetProperty %Exists
%ExistsId %Extends %GUID %GUIDSet
%GetLock %GetParameter %GetSwizzleObject %Id
%InsertBatch %IsA %IsModified %IsNull
%KillExtent %KillExtentData %LoadFromMemory %LockExtent
%LockId %New %NormalizeObject %ObjectIsNull
%ObjectModified %Oid %OnBeforeAddToSync %OnDetermineClass
%Open %OpenId %OriginalNamespace %PackageName
%PhysicalAddress %PurgeIndices %Reload %RemoveFromSaveSet
%ResolveConcurrencyConflict %RollBack %Save %SaveDirect
%SaveIndices %SerializeObject %SetModified %SortBegin
%SortEnd %SyncObjectIn %SyncTransport %UnlockExtent
%UnlockId %ValidateIndices %ValidateObject clearSQLStatements
version


Properties

• property ImportPackage as %String(MAXLEN=2000);
Comma delimited list of Package names to use compiling SQL Statements
• property MethodName as %String(MAXLEN=2000);
If {Type} is 'Cached Query' OR 'Class Method', then this is the Name of the Method/ClassMethod If {Type} is 'Class Query', then this is the Name of the Query If {Type} is 'Routine', then this is the Name of the Procedure/Function This Name is the module in which the SQL Query Text {SQLTextRaw} can be found
• property Name as %String(MAXLEN=2000);
Based on the {Type}, the name of the object from where the SQL Statement was extracted: 'Cached Query' The class name which represents the Cached Queries 'Class Method' The name of the class that contains the 'method' name with SQL 'Class Query' The name of the class that contains the 'method' name with SQL 'Routine' The name of the routine that contains Embedded SQL
• property SQLTextExt as %String(COLLATION="SQLUPPER(255)",MAXLEN=30000,TRUNCATE=1) [ Calculated ];
External SQL Statement as a single string This is the exact text of the SQL Statement, including SQL Comments, parametes, etc.
• property SQLTextRaw as list of %String(MAXLEN=30000,SQLPROJECTION="table/column",TRUNCATE=1,XMLPROJECTION="wrapped");
Raw SQL Statement Text stored as a $LIST string This is the exact text of the SQL Statement, including SQL Comments, parametes, etc.
• property Type as %String(MAXLEN=100);
The {Type} is the location within InterSystems IRIS from where the SQL Statement was extracted: 'Cached Query' | 'Class Method' | 'Class Query' | 'Routine'

Methods

• classmethod clearSQLStatements() as %Status [ SQLProc = ]

------------------------------------------------------------------------------- Method: clearSQLStatements Replaces: ClearStatements (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: Delete all of the data stored in the '%SYS.PTools.UtilSQLStatements' class... Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.UtilSQLStatements).clearSQLStatements(...) SQL: CALL %SYS_PTools.UtilSQLStatements_clearSQLStatements(...) SELECT %SYS_PTools.UtilSQLStatements_clearSQLStatements(...) NOTE: This 'SqlProc' method can be invoked with either the CALL-interface or the SELECT-interface. Both interfaces execute the method: the CALL-interface does not return any resulting value, while the SELECT-interface does. Examples: The following examples shows the use of this method: #1 Delete all of the data stored in the '%SYS.PTools.UtilSQLStatements' class in the current namespace: set status=##class(%SYS.PTools.UtilSQLStatements).clearSQLStatements() Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts") Parameters: N/A RETURN Value: The status of this method; Otherwise, return an error message if an error occurred -------------------------------------------------------------------------------

• classmethod version() as %String [ SQLProc = UtilSQLStatements_version ]
Provide the current version for the UtilSQLStatements class/section of the Performance Tools (PTools) Application

Queries

• query UtilSQLStatementsView()
SQL Query as view "UtilSQLStatementsView":
SELECT ID, Type, Name, MethodName, ImportPackage, SQLTextExt, SQLTextRaw
FROM %SYS_PTools.UtilSQLStatements

------------------------------------------------------------------------------- Query: UtilSQLStatementsView [SQL: UtilSQLStatementsView] Status: New Query/View to replicate Backward-Compatible Query/View it 'Replaces' Purpose: Class View which returns all of the SQL Statements information stored in the '%SYS.PTools.UtilSQLStatements' class/table -------------------------------------------------------------------------------


Triggers

•trigger OnDelete (AFTER event DELETE)


Copyright (c) 2019 by InterSystems Corporation. Cambridge, Massachusetts, U.S.A. All rights reserved. Confidential property of InterSystems Corporation.