Learning
Documentation
Community
Open Exchange
Global Masters
Home / Class Reference / ENSLIB namespace / %SYS.PTools.SQLUtilities
Private  Storage   

%SYS.PTools.SQLUtilities


persistent class %SYS.PTools.SQLUtilities extends
%Persistent

------------------------------------------------------------------------------ -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Starting with ver '2.0' of the PTools application, this class is considered to be DEPRECATED and is replaced by the %SYS.PTools.UtilSQLStatements class. -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- ------------------------------------------------------------------------------

------------------------------------------------------------------------------- Class: %SYS.PTools.SQLUtilities Replaced By: %SYS.PTools.UtilSQLStatements Purpose: This class stores the SQL Statement details collected from the following locations by invoking the 'GetSQLStatements()' method: - Cached Queries - Class Methods - Class Queries - MAC Routines Data Storage: ^%sqlcq($NAMESPACE,"PTools","Utils","Queries","{C|D|I|S}") Error Storage: ^%sqlcq($NAMESPACE,"PTools","Error"[...])=$LIST Info ------------------------------------------------------------------------------

Inventory


Parameters Properties Methods Queries Indices ForeignKeys Triggers
4 24 9 1


Summary


Properties
ImportPackage Name SQLText Type

Methods
%AddToSaveSet %AddToSyncSet %BMEBuilt %BuildIndicesAsync
%BuildIndicesAsyncResponse %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 BuildPPcost ChangeCost ClearResults
ClearStatements GetSQLStatements IndexUsage JoinIndices
PossiblePlans PossiblePlansClose PossiblePlansExecute PossiblePlansFetch
PossiblePlansStatsClose PossiblePlansStatsExecute PossiblePlansStatsFetch SQLTextLogicalToDisplay
TableScans TempIndices clearSQLUtilStmtResults clearSQLUtilStmts
exportSQLUtilStmts exportSQLUtilities map streamAsText
version


Properties


• property ImportPackage as %String;
comma delimited list of Package names to use compiling queries
• property Name as %String;
• property SQLText as %Stream.GlobalCharacter;
• property Type as %String;

Methods


• classmethod BuildPPcost(qoqn, dmt, dmts, dalg, PPcost)

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: BuildPPcost Replaced By: buildPPcost (%SYS.PTools.StatsSQL) Purpose: * INTERNAL USE ONLY * Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Method! -------------------------------------------------------------------------------

• classmethod ChangeCost(PPcost, num, level, dmt, dmts, dalg)

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: ChangeCost Replaced By: changeCost (%SYS.PTools.StatsSQL) Purpose: * INTERNAL USE ONLY * Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Method! -------------------------------------------------------------------------------

• classmethod ClearResults(ns As %String = "", clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %ArrayOfObjects) as %Status [ SQLProc = ClearResults ]
Backward-compatible Entry Point: NOTE: See the clearSQLUtilStmtResults method above for more details.
• classmethod ClearStatements(ns As %String = "", clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %ArrayOfObjects) as %Status [ SQLProc = ClearStatements ]
Backward-compatible Entry Point: NOTE: See the clearSQLUtilStmts method above for more details.
• classmethod GetSQLStatements(cachedQueries As %Integer = 1, classQueries As %Integer = 1, classMethods As %Integer = 1, routines As %Integer = 1, SystemTables As %Integer = 0, Display As %Integer = 1, skipInsStmts As %Integer = 1, clearData As %Integer = 1, returnType As %Integer = 0, ByRef ptInfo As %ArrayOfObjects) as %Status [ SQLProc = GetSQLStatements ]

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: GetSQLStatements Replaced By: getSQLStmts [SQL: PT_getSQLStmts] (%SYS.PTools.UtilSQLAnalysis) It is recommended that you use one of the following new methods which replaces the functionality of this method: ##class(%SYS.PTools.UtilSQLAnalysis). => getSQLStmts(...) [Cached Queries|Class Methods|Class Queries|MAC Routines] => getAllSQLStmts(...) [Cached Queries|Class Methods|Class Queries|MAC Routines] => getAllCachedQrySQLStmts(...) [Cached Queries] => getCachedQrySQLStmtsByDays(...) [Cached Queries] => getCachedQrySQLStmtsByClass(...) [Cached Queries] => getAllClassMethSQLStmts(...) [Class Methods] => getClassMethSQLStmtsByClass(...) [Class Methods] => getAllClassQrySQLStmts(...) [Class Queries] => getClassQrySQLStmtsByClass(...) [Class Queries] => getAllRtnQrySQLStmts(...) [MAC Routines] => getRtnQrySQLStmtsByRtn(...) [MAC Routines] Status: Maintained for Backward-Compatibility Purpose: This method searches for at all of the SQL Statements in a namespace from the following locations and adds information about the statements (e.g. Type, Name, SQLText) to the '%SYS.PTools.SQLUtilities' class for additional processing: - Cached Queries - Class Methods - Class Queries - MAC Routines Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLUtilities).GetSQLStatements(...) SQL: CALL %SYS_PTools.GetSQLStatements(...) SELECT %SYS_PTools.GetSQLStatements(...) 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 provide analytical information produced from the invocation of this method: #1 Show info from where the SQL Statements were collected: SELECT %EXACT(Type) AS QueryType, Count(*) AS QueryCount FROM %SYS_PTools.SQLUtilities GROUP BY Type OR SELECT * FROM %SYS_PTools.SQLUtilities_FindSQL() #2 Show the data collected from the running of this method: SELECT Type, Name, ImportPackage, SQLText AS "SQL Text" FROM %SYS_PTools.SQLUtilities ORDER BY Type, Name -- This returns "SQL Text" in External Format OR SELECT * FROM %SYS_PTools.SQLUtilities_GetSQLStatements() -- This returns "SQL Text" in Logical Format OR SELECT * FROM %SYS_PTools.SQLUtilities_GetSQLStatements(1) -- This returns "SQL Text" in External Format OR SELECT Type, "Class/Routine Name", ImportPackage, %SYS_PTools.PT_streamAsText("SQL Text") FROM %SYS_PTools.SQLUtilities_GetSQLStatements() -- This returns "SQL Text" in External Format NOTE: These queries can be invoked from a number of tools, such as the SQL Query tool in the 'Management Portal' or via 'SQL Query Shell' within a 'InterSystems IRIS Terminal' as shown below: >do $SYSTEM.SQL.Shell() Data Storage: ^%sqlcq($NAMESPACE,"PTools","Utils","Queries","{C|D|I|S}") Parameters: cachedQueries - 1 = Get the SQL Statements from all Cached Queries [DEFAULT: 1] classQueries - 1 = Get the SQL Statements from all of the Class Queries [DEFAULT: 1] classMethods - 1 = Get the SQL Statements from all of the Class Methods [DEFAULT: 1] routines - 1 = Get the Embedded SQL Statements from all of the MAC Routines [DEFAULT: 1] SystemTables - 1 = Get all System Tables that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } [DEFAULT: 0] Display - 1 = Display messages while processing method [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] returnType - 0 = Return a %Status code of either $$$OK or $$$ERROR() 1 = Return a $LIST of SQL Statements added to the '%SYS.PTools.UtilSQLStatements' class [DEFAULT: 0] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo("insert-list")=The number of rows inserted in the '%SYS.PTools.UtilSQLStatements' class in the following $LIST format $LIST() pieces: 1) Grand Total of all SQL Query Statements added 2) Total of all Cached Query Statements added 3) Total of all Class Method Statements added 4) Total of all Class Query Statements added 5) Total of all Routine Statements added RETURN Value: Based on the value of the 'returnType' parameter, return one of the following: 0: Return a %Status code of either $$$OK or $$$ERROR() 1: Return the number of SQL Statements added to the '%SYS.PTools.UtilSQLStatements' class in the following $LIST format; Otherwise, return an error message if an error occurred. -------------------------------------------------------------------------------

• classmethod IndexUsage(PopTable As %Integer = 0, SystemTables As %Integer = 0, IgnoreEns As %Integer = 1, IgnoreIDKeys As %Integer = 1, Display As %Integer = 1, getIndices As %Integer = 0, skipInsStmts As %Integer = 1, clearData As %Integer = 1, ByRef ptInfo As %ArrayOfObjects) as %Status [ SQLProc = IndexUsage ]

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: IndexUsage [SQL: IndexUsage] Replaced By: indexUsage [SQL: PT_indexUsage] (%SYS.PTools.UtilSQLAnalysis) Status: Maintained for Backward-Compatibility Purpose: This method uses the SQL Statement data stored in the '%SYS.PTools.SQLUtilities' class to generate a ShowPlan for each query, and to keeps a count of how many times each index is used by each query. This index usage count is subsequently stored in the 'UsageCount' field of the %SYS.PTools.SQLUtilResults class, which can be used to find and remove unneeded indices in addition to being used for other analytical purposes... Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLUtilities).IndexUsage(...) SQL: CALL %SYS_PTools.IndexUsage(...) SELECT %SYS_PTools.IndexUsage(...) 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 provide analytical information produced from the invocation of this method: #1 Show info from where the SQL Statements were collected: SELECT %EXACT(Type) AS QueryType, Count(*) AS QueryCount FROM %SYS_PTools.SQLUtilities GROUP BY Type OR SELECT * FROM %SYS_PTools.SQLUtilities_FindSQL() #2 Show the data collected from the running of this method: SELECT * FROM %SYS_PTools.SQLUtilities_IndexUsage() NOTE: These queries can be invoked from a number of tools, such as the SQL Query tool in the 'Management Portal' or via 'SQL Query Shell' within a 'InterSystems IRIS Terminal' as shown below: >do $SYSTEM.SQL.Shell() Data Storage: ^%sqlcq($NAMESPACE,"PTools","Utils","Queries","{C|D|I|S}") Parameters: PopTable - 1 = Get all the SQL Statements from InterSystems IRIS locations (Cached Queries, Class Methods, Class Queries, MAC Routines) and add them to the '%SYS.PTools.SQLUtilities' class for additional processing of this method [DEFAULT: 0*] * - If '%SYS.PTools.SQLUtilities' contains NO data, then DEFAULT 'PopTable' to 1 because this method requires SQL Statement data for processing! SystemTables - 1 = Get all System Tables that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } [DEFAULT: 0] IgnoreEns - ??? IgnoreIDKeys - 1 = Skip all the index that will be used to form the Object Identity value (IDKEY) for the given class NOTE: This only applies when retrieving SQL Indices (getIndices=1) [DEFAULT: 1] Display - 1 = Display messages while processing method [DEFAULT: 1] getIndices - 1 = Get all the SQL Indexes from the Class Methods in this Namespace and add them to the '%SYS.PTools.SQLUtilResults' class for additional processing of this method [DEFAULT: 0*] * - If '%SYS.PTools.SQLUtilResults' contains NO Index Usage (IU) data, then DEFAULT 'getIndices' to 1, even if a 0 is passed in for this paramter, because this method requires Index Usage (IU) data for processing! skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo("update-cnt")=The number of rows in the %SYS.PTools.SQLUtilResults class which were updated with their Index Usage details RETURN Value: The status from the invocation of this method -------------------------------------------------------------------------------

• classmethod JoinIndices(PopTable As %Integer = 1, SystemTables As %Integer = 0, IgnoreEns As %Integer = 1, Display As %Integer = 1, skipInsStmts As %Integer = 1, clearData As %Integer = 1, ByRef ptInfo As %ArrayOfObjects) as %Status [ SQLProc = JoinIndices ]

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: JoinIndices [SQL: JoinIndices] Replaced By: joinIndices [SQL: PT_joinIndices] (%SYS.PTools.UtilSQLAnalysis) Status: Maintained for Backward-Compatibility Purpose: This method uses the SQL Statement data stored in the '%SYS.PTools.SQLUtilities' class to pinpoint the queries that perform a JOIN between tables where their joined-fields use an index that supports the join. This method will then ranks the indices available to support the join from 0 (no index present) to 4 (index fully supports the join). Pertinent information about these queries is subsequently stored in the the %SYS.PTools.SQLUtilResults class for future processing and analysis. It's worth noting that OUTER JOINs require an index in one direction, whereas INNER JOINs require an index in both directions. The result-set stored in the the %SYS.PTools.SQLUtilResults class only contains rows that have a 'JoinIndexFlag < 4'. Rows that have a 'JoinIndexFlag = 4', which means that there exists an index that fully supports the JOIN, are not stored since they are already fully optimized. The stored queries should be reviewed to determine if an index could be added to the class to satisfy the highest 'JoinIndexFlag' possible: The 'JoinIndexFlag' property has the following 4 values: 0 - No index to support the join. (Some version of the suggested index should be created to improve this query's performance) 1 - There is an index to support the join but it does not contain all the join fields. (This will produce poor performance and for that reason is rarely used) 2 - There is an index to support the join but it is not an exact match - The first index field is not part of the join. (This might produce OK performance, but improvements should be made) 3 - There is an index to support the join but it is not an exact match - The first index field is part of the join but there are additional fields in the index) (This will produce OK performance, but improvements can be made) 4 - Index fully supports the join. Optimizations ------------- - Creating a new index in the case of JoinIndexFlag = 0 or 1 should show good performance gains. - Creating a new index for JoinIndexFlag = 2 will help, improvements will depend on the number of leading subscripts and their selectivity - Creating a new index for JoinIndexFlag = 3 could help, in most cases you will only see small improvements. - The Order of the subscripts does not matter for the join, but could make a difference in performance. - The property with the lowest selectivity should be first. NOTE: The 'IndexFields' property would be an index we think could help improve performance. Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLUtilities).JoinIndices(...) SQL: CALL %SYS_PTools.JoinIndices(...) SELECT %SYS_PTools.JoinIndices(...) 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 provide analytical information produced from the invocation of this method: #1 Show info from where the SQL Statements were collected: SELECT %EXACT(Type) AS QueryType, Count(*) AS QueryCount FROM %SYS_PTools.SQLUtilities GROUP BY Type OR SELECT * FROM %SYS_PTools.SQLUtilities_FindSQL() #2 Show the data collected from the running of this method: SELECT SchemaName, TableName, $LISTTOSTRING(IndexFields) As "Index Needed", JoinIndexFlag As "Join Index Flag", ExtentSize, BlockCount As "Block Count", SQLPointer->SQLText AS "SQL Text" FROM %SYS_PTools.SQLUtilResults WHERE OptionName = 'JI' ORDER BY 1,2 -- This returns "SQL Text" in External Format OR SELECT * FROM %SYS_PTools.SQLUtilities_JoinIndices() -- This returns "SQL Text" in Logical Format OR SELECT * FROM %SYS_PTools.SQLUtilities_JoinIndices(,1) -- This returns "SQL Text" in External Format OR SELECT SchemaName, TableName, Type, "Class/Routine Name", IndexName, "Index Needed", "Join Index Flag", ExtentSize, "Block Count", %SYS_PTools.PT_streamAsText("SQL Text") FROM %SYS_PTools.SQLUtilities_JoinIndices() -- This returns "SQL Text" in External Format #3 Show joined-based tables with less than optimal indexes that support the JOIN specified in the query: SELECT SchemaName, TableName, IndexFields As "Index Needed", JoinIndexFlag AS "Join Index Flag", COUNT(*) AS "Query Count" FROM %SYS_PTools.SQLUtilResults WHERE JoinIndexFlag < 4 and OptionName = 'JI' GROUP BY SchemaName, TableName, IndexFields ORDER BY 4,5 DESC -- These tables should be reviewed to see if a better -- index can be added to fully support the JOIN conditions NOTE: These queries can be invoked from a number of tools, such as the SQL Query tool in the 'Management Portal' or via 'SQL Query Shell' within a 'InterSystems IRIS Terminal' as shown below: >do $SYSTEM.SQL.Shell() Data Storage: ^%sqlcq($NAMESPACE,"PTools","Utils","Queries","{C|D|I|S}") Parameters: PopTable - 1 = Get all the SQL Statements from InterSystems IRIS locations (Cached Queries, Class Methods, Class Queries, MAC Routines) and add them to the '%SYS.PTools.SQLUtilities' class for additional processing of this method [DEFAULT: 1] SystemTables - 1 = Get all System Tables that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } [DEFAULT: 0] IgnoreEns - ??? Display - 1 = Display messages while processing method [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo("update-cnt")=The number of rows inserted in the %SYS.PTools.SQLUtilResults class for queries that have joins indexing issues RETURN Value: The status from the invocation of this method RETURN Value: The number of rows inserted in the %SYS.PTools.SQLUtilResults class for queries that have joins indexing issues; Otherwise, return an error message if an error occurred -------------------------------------------------------------------------------

• classmethod PossiblePlans(sql, PPcost=0, num=0, level=0, arr, showstats=0, packages, schemapath="", preparse=0, hash="", silent=0)

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: PossiblePlans Replaced By: possiblePlans (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Method! -------------------------------------------------------------------------------

• classmethod PossiblePlansClose(ByRef qHandle As %Binary) as %Status

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: PossiblePlansClose Replaced By: possiblePlansClose (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Method! -------------------------------------------------------------------------------

• classmethod PossiblePlansExecute(ByRef qHandle As %Binary, sql As %String) as %Status

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: PossiblePlansExecute Replaced By: possiblePlansExecute (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Method! -------------------------------------------------------------------------------

• classmethod PossiblePlansFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) as %Status

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: PossiblePlansFetch Replaced By: possiblePlansFetch (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Method! -------------------------------------------------------------------------------

• classmethod PossiblePlansStatsClose(ByRef qHandle As %Binary) as %Status

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: PossiblePlansStatsClose Replaced By: possiblePlansStatsClose (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Method! -------------------------------------------------------------------------------

• classmethod PossiblePlansStatsExecute(ByRef qHandle As %Binary, sql As %String, ids As %String) as %Status

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: PossiblePlansStatsExecute Replaced By: possiblePlansStatsExecute (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Method! -------------------------------------------------------------------------------

• classmethod PossiblePlansStatsFetch(ByRef qHandle As %Binary, ByRef Row As %List, ByRef AtEnd As %Integer = 0) as %Status

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: PossiblePlansStatsFetch Replaced By: possiblePlansStatsFetch (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Method! -------------------------------------------------------------------------------

• classmethod SQLTextLogicalToDisplay(%val As %String) as %String

------------------------------------------------------------------------------- Method: SQLTextLogicalToDisplay Replaced By: N/A Status: New Functionality Purpose: Converts the value of the parameter '%val', which represents the 'SQLText' property in logical format (%Stream.GlobalCharacter), into a string value (%Library.String) and return that string. Examples: The following examples show how to return the 'SQLText' property in different formats: #1 Show 'SQLText' as a Stream ID: SELECT ID, %EXACT(Type) AS QueryType, %Internal(SQLText) FROM %SYS_PTools.SQLUtilities #2 Show 'SQLText' as a String via the 'Execute Query' interface of the 'Management Portal': SELECT ID, %EXACT(Type) AS QueryType, SQLText FROM %SYS_PTools.SQLUtilities #3 Show 'SQLText' as a String via Embedded SQL: #SQLCompile Select=Display &sql(DECLARE sqlCUR CURSOR FOR SELECT ID, %EXACT(Type) AS QueryType, SQLText FROM %SYS_PTools.SQLUtilities ) Parameters: %val - The value of the 'SQLText' property in logical format (%Stream.GlobalCharacter) RETURN Value: The SQL Statement Text in (%Library.String) format; Otherwise, return an error message if an error occurred -------------------------------------------------------------------------------

• classmethod TableScans(PopTable As %Integer = 0, SystemTables As %Integer = 0, IgnoreEns As %Integer = 1, Display As %Integer = 1, skipInsStmts As %Integer = 1, clearData As %Integer = 1, ByRef ptInfo As %ArrayOfObjects) as %Status [ SQLProc = TableScans ]

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: TableScans [SQL: TableScans] Replaced By: tableScans [SQL: PT_tableScans] (%SYS.PTools.UtilSQLAnalysis) Status: Maintained for Backward-Compatibility Purpose: This method uses the SQL Statement data stored in the '%SYS.PTools.SQLUtilities' class to pinpoint the queries that perform a table scan, which could be over an index or the master map. Pertinent information about these queries is subsequently stored in the the %SYS.PTools.SQLUtilResults class for future processing and analysis. For some queries a table scan can't be avoided, but any query that uses such a scan should be reviewed in order to determine if an index could be added for performance optimizations. Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLUtilities).TableScans(...) SQL: CALL %SYS_PTools.TableScans(...) SELECT %SYS_PTools.TableScans(...) 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 provide analytical information produced from the invocation of this method: #1 Show info from where the SQL Statements were collected: SELECT %EXACT(Type) AS QueryType, Count(*) AS QueryCount FROM %SYS_PTools.SQLUtilities GROUP BY Type OR SELECT * FROM %SYS_PTools.SQLUtilities_FindSQL() #2 Show the data collected from the running of this method: SELECT SQLPointer->Type As "Routine Type", SQLPointer->Name As "Routine Name", SchemaName, TableName, ModuleName, ExtentSize, SQLPointer->SQLText AS "SQL Text" FROM %SYS_PTools.SQLUtilResults WHERE OptionName = 'TS' ORDER BY ExtentSize DESC OR SELECT * FROM %SYS_PTools.SQLUtilities_TableScans() -- This returns "SQL Text" in Logical Format OR SELECT * FROM %SYS_PTools.SQLUtilities_TableScans(,1) -- This returns "SQL Text" in External Format OR SELECT SchemaName, TableName, Type, "Class/Routine Name", ModuleName, "Map Type" , ExtentSize, "Block Count", %SYS_PTools.PT_streamAsText("SQL Text") FROM %SYS_PTools.SQLUtilities_TableScans() -- This returns "SQL Text" in External Format NOTE: These queries can be invoked from a number of tools, such as the SQL Query tool in the 'Management Portal' or via 'SQL Query Shell' within a 'InterSystems IRIS Terminal' as shown below: >do $SYSTEM.SQL.Shell() Data Storage: ^%sqlcq($NAMESPACE,"PTools","Utils","Queries","{C|D|I|S}") Parameters: PopTable - 1 = Get all the SQL Statements from InterSystems IRIS locations (Cached Queries, Class Methods, Class Queries, MAC Routines) and add them to the '%SYS.PTools.SQLUtilities' class for additional processing of this method [DEFAULT: 1] SystemTables - 1 = Get all System Tables that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } [DEFAULT: 0] IgnoreEns - ??? Display - 1 = Display messages while processing method [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo("update-cnt")=The number of rows inserted in the %SYS.PTools.SQLUtilResults class for queries that require a full table scan RETURN Value: The status from the invocation of this method -------------------------------------------------------------------------------

• classmethod TempIndices(PopTable As %Integer = 1, SystemTables As %Integer = 0, IgnoreEns As %Integer = 1, Display As %Integer = 1, skipInsStmts As %Integer = 1, clearData As %Integer = 1, ByRef ptInfo As %ArrayOfObjects) as %Status [ SQLProc = TempIndices ]

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: TempIndices [SQL: TempIndices] Replaced By: tempIndices [SQL: PT_tempIndices] (%SYS.PTools.UtilSQLAnalysis) Status: Maintained for Backward-Compatibility Purpose: This method uses the SQL Statement data stored in the '%SYS.PTools.SQLUtilities' class to pinpoint the queries that are building a Temp Index/Table. Pertinent information about these queries is subsequently stored in the the %SYS.PTools.SQLUtilResults class for future processing and analysis. It's worth noting that the results of this method and that of the 'TableScans()' method might have a large overlap. For some queries the creation of a Temp Index/Table can be avoided, but often the creation of a Temp Index/Table is done so to provide optimal data traversal and retrieval. These queries should be reviewed to determine if an index could be added to the class for performance optimizations, because often the structure of the Temp Index/Table is the basis for the creation of a class index to help with query performance. Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLUtilities).TempIndices(...) SQL: CALL %SYS_PTools.TempIndices(...) SELECT %SYS_PTools.TempIndices(...) 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 provide analytical information produced from the invocation of this method: #1 Show info from where the SQL Statements were collected: SELECT %EXACT(Type) AS QueryType, Count(*) AS QueryCount FROM %SYS_PTools.SQLUtilities GROUP BY Type OR SELECT * FROM %SYS_PTools.SQLUtilities_FindSQL() #2 Show the data collected from the running of this method: SELECT SchemaName, TableName, $LISTTOSTRING(IndexFields) As "Index Fields", $LISTTOSTRING(DataValues) As "Data Fields", ExtentSize, BlockCount As "Block Count", SQLPointer->SQLText AS "SQL Text" FROM %SYS_PTools.SQLUtilResults WHERE OptionName = 'TI' ORDER BY ExtentSize DESC -- This returns "SQL Text" in External Format OR SELECT * FROM %SYS_PTools.SQLUtilities_TempIndices() -- This returns "SQL Text" in Logical Format OR SELECT * FROM %SYS_PTools.SQLUtilities_TempIndices(,1) -- This returns "SQL Text" in External Format OR SELECT SchemaName, TableName, Type, "Class/Routine Name", IndexName, "Index Fields", "Data Fields", ExtentSize, "Block Count", %SYS_PTools.PT_streamAsText("SQL Text") FROM %SYS_PTools.SQLUtilities_TempIndices() -- This returns "SQL Text" in External Format NOTE: These queries can be invoked from a number of tools, such as the SQL Query tool in the 'Management Portal' or via 'SQL Query Shell' within a 'InterSystems IRIS Terminal' as shown below: >do $SYSTEM.SQL.Shell() Data Storage: ^%sqlcq($NAMESPACE,"PTools","Utils","Queries","{C|D|I|S}") Parameters: PopTable - 1 = Get all the SQL Statements from InterSystems IRIS locations (Cached Queries, Class Methods, Class Queries, MAC Routines) and add them to the '%SYS_PTools.SQLUtilities' class for additional processing of this method [DEFAULT: 1] SystemTables - 1 = Get all System Tables that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } [DEFAULT: 0] IgnoreEns - ??? Display - 1 = Display messages while processing method [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo("update-cnt")=The number of rows inserted in the %SYS.PTools.SQLUtilResults class for queries that build temporary indices to resolve the SQL RETURN Value: The status from the invocation of this method -------------------------------------------------------------------------------

• classmethod clearSQLUtilStmtResults(ns As %String = "", clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %ArrayOfObjects) as %Status [ SQLProc = PT_clearSQLUtilStmtResults ]

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: clearSQLUtilStmtResults [SQL: PT_clearSQLUtilStmtResults] Replaces: ClearResults [SQL: ClearResults (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Functionality Purpose: Delete all of the data stored in the %SYS.PTools.SQLUtilResults class, based on the specified parameters... Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLUtilities).clearSQLUtilStmtResults(...) SQL: CALL %SYS_PTools.clearSQLUtilStmtResults(...) SELECT %SYS_PTools.clearSQLUtilStmtResults(...) 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.SQLUtilities).clearSQLUtilStmtResults() Data Storage: ^%sqlcq($NAMESPACE,"PTools","Utils","Results","{C|D|I|S}") Parameters: ns - The namespace in which to clear SQL Index/Analysis Statstics [DEFAULT: Current Namespace] clearErrs - 0 = Don't delete the 'PTools' Application Errors 1 = Delete all of the 'PTools' Application Errors [DEFAULT: 0] returnType - 0 = Return a %Status code of either $$$OK or $$$ERROR() 1 = Return the number of Stats rows deleted from the following class: %SYS.PTools.SQLUtilResults [DEFAULT: 0] ptInfo - A Pass By Reference information array that returns detailed information to the user in the following format: ptInfo(category,variable)=value OR $LB(val1,...,valn) Where category = { "cnt" | "curr" | "term" | ... } variable = A var corresponding to the given 'category' Example: ptInfo("cnt","clearSQLUtilStmtResults")=The number of rows deleted via this method [Routine Method] ptInfo("cnt","ClearResults")=The number of rows deleted via this method [Class Method] RETURN Value: Based on the value of the 'returnType' parameter, return one of the following: 0: Return a %Status code of either $$$OK or $$$ERROR() 1: Return the number of Stats rows deleted from the following class; Otherwise, return an error message if an error occurred: %SYS.PTools.SQLUtilResults -------------------------------------------------------------------------------

• classmethod clearSQLUtilStmts(ns As %String = "", clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %ArrayOfObjects) as %Status [ SQLProc = PT_clearSQLUtilStmts ]

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: clearSQLUtilStmts [SQL: PT_clearSQLUtilStmts] Replaces: ClearStatements [SQL: ClearStatements] (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Functionality Purpose: Delete all of the data stored in the '%SYS.PTools.SQLUtilities' class, based on the specified parameters... Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLUtilities).clearSQLUtilStmts(...) SQL: CALL %SYS_PTools.clearSQLUtilStmts(...) SELECT %SYS_PTools.clearSQLUtilStmts(...) 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.SQLUtilities).clearSQLUtilStmts() Data Storage: ^%sqlcq($NAMESPACE,"PTools","Utils","Queries","{C|D|I|S}") Parameters: ns - The namespace in which to clear SQL Index/Analysis Statstics [DEFAULT: Current Namespace] clearErrs - 0 = Don't delete the 'PTools' Application Errors 1 = Delete all of the 'PTools' Application Errors [DEFAULT: 0] returnType - 0 = Return a %Status code of either $$$OK or $$$ERROR() 1 = Return the number of Stats rows deleted from the following class: %SYS.PTools.SQLUtilities [DEFAULT: 0] ptInfo - A Pass By Reference information array that returns detailed information to the user in the following format: ptInfo(category,variable)=value OR $LB(val1,...,valn) Where category = { "cnt" | "curr" | "term" | ... } variable = A var corresponding to the given 'category' Example: ptInfo("cnt","clearSQLUtilStmts")=The number of rows deleted via this method [Routine Method] ptInfo("cnt","ClearStatements")=The number of rows deleted via this method [Class Method] RETURN Value: Based on the value of the 'returnType' parameter, return one of the following: 0: Return a %Status code of either $$$OK or $$$ERROR() 1: Return the number of Stats rows deleted from the following class; Otherwise, return an error message if an error occurred: %SYS.PTools.SQLUtilities -------------------------------------------------------------------------------

• classmethod exportSQLUtilStmts(file As %String = "", format As %String = "", silent As %Integer = 0, type As %String = "", ByRef conds As %ArrayOfObjects, ByRef ptInfo As %ArrayOfObjects) as %Status [ SQLProc = PT_exportSQLUtilStmts ]

------------------------------------------------------------------------------- Method: exportSQLUtilStmts [SQL: PT_exportSQLUtilStmts] Replaced By: exportUtilSQLStatements [SQL: PT_exportUtilSQLStatements] (%SYS.PTools.UtilSQLAnalysis) Status: New Functionality Purpose: Create a file containing all the data from the %SYS.PTools.SQLUtilities class & return the output location... Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLUtilities).exportSQLUtilStmts(...) SQL: CALL %SYS_PTools.PT_exportSQLUtilStmts(...) SELECT %SYS_PTools.PT_exportSQLUtilStmts(...) 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 Display this export file to the screen via a InterSystems IRIS Terminal: set tSC=##class(%SYS.PTools.SQLUtilities).exportSQLUtilStmts($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportSQLUtilStmts('$IO','H') Where: '$IO' = Output to the current device NOTE: '$IO' can be omitted, as it is the default 'file' when invoked via the CALL interface 'H' = Output the format in HTML format NOTE: 'H' can be omitted, as it is the default 'format' when invoked via the CALL interface NOTE: This method has been added to this DEPRECATED class to correspond with the newly created 'export' method defined for the new & improved interface in the '%SYS.PTools.UtilSQLAnalysis' class Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.SQLUtilities class: (e.g. C:\exportFile.txt) NOTE: $IO = Outputs the data to the screen '$IO' = Output the data to the current device when invoked from the CALL/SELECT-interface '$DEF' = Ouput the data to the DEFAULT file when invoked from the CALL/SELECT-interface [DEFAULT: {Current-Directory}\PT_exportSQLUtilStmts_YYYYMMDD_HHMMSS.{ext}] WHERE {Current-Directory} = The directory of the Namespace in which this method is invoked (e.g. $ZU(12,"")) NOTE 2: If Passed By Reference, then 'file' will be returned as the canonical name of the Export/Reporting file format - The output format of the Export/Reporting file This parameter has the following structure of constituent elements: format=[:$LB(,,)] Where the constituent elements have the following values: - The format of the Export/Reporting file: - P = Printable/Viewable report file (.txt file, no pagination) - D = Comma-delimited data file (.csv file) which can be read into a spreadsheet - X = Microsoft Excel XML markup file suitable for import into Excel (.xml file) - H = HTML page file (.html file) - Z = User-defined delimiter "P" formatted file (.dlm file) NOTE: The element is required or defaulted to $C(9) (TAB) Optional Elements: - User-defined delimiter [DEFAULT: $C(9) (TAB)] NOTE: This is generally used for ="Z" - .csv file header for ="D": - 0 = Don't add non-standard information header to file [DEFAULT] - 1 = Add non-standard information header to file - .csv file footer for ="D": - 0 = Don't add non-standard information footer to file [DEFAULT] - 1 = Add non-standard information footer to file EXAMPLES: Valid 'format' values: - "H" // HTML file - "D" // CSV file with No information header or footer - "D:"_$LB(,1,1) // CSV file with information header & footer - "Z:"_$LB("^") // User-defined delimiter file (delim="^") silent - 0 - Display all messages during the running of this method 1 - Don't display any messages during the running of this method type - A single type, or a comma-delimited list of {Type} values to restrict the output of rows from the %SYS.PTools.SQLUtilities class. The {Type} is the location within InterSystems IRIS from where the SQL Query was extracted: {Type} values: { cached query | Class Method | Class Query | Routine } conds - A string/array of possible conditions by which to restrict the output of the Export file, in the following format: conds= OR conds(0)=pos Count (WHERE pos = {1...n}) conds(pos)=$LIST() Pieces: 1) [] (Assumed Default: &&) 2) [{Heading}] (Omit for override cond) 3) | WHERE: := := {&& | ||} (&& = AND | || = OR) @* := @ := @ = Contains no references to {Heading} * = Contains no references to {*} EXAMPLE: conds="("",INFO,MAIN,""[("",""_{Module}_"",""))||({GlobalRefs}>20)" conds(0)=3 conds(1)=$LB(,"Module","=""INFO""") conds(2)=$LB("||","Module","=""MAIN""") conds(3)=$LB("||","GlobalRefs",">20") conds(0)=2 conds(1)=$LB(,"Module",""",INFO,MAIN,""[("",""_{*}_"","")") conds(2)=$LB("||","GlobalRefs","{*}>20") WHERE: {*} = value substitution for the 'Module' & 'GlobalRefs' fields NOTE: Conditions that don't contain any references to {Heading} fields, often called s, can be included as s, as in the following example: conds(pos)=$LB(,,"$g(^zAction(""runIt""))=1") NOTE: All conditions must be satisfied for the conds() to be considered true and for the row to be exported [PASS BY REFERENCE] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo(variable)=value ptInfo("outputFile")=The canonical name of the Export/Reporting file RETURN Value: The status from the invocation of this method -------------------------------------------------------------------------------

• classmethod exportSQLUtilities(file As %String = "", format As %String = "", silent As %Integer = 0, type As %String = "", option As %String = "", ByRef conds As %ArrayOfObjects, includeSQL As %Integer = 1, ByRef ptInfo As %ArrayOfObjects) as %Status [ SQLProc = PT_exportSQLUtilities ]

------------------------------------------------------------------------------- Method: exportSQLUtilities [SQL: PT_exportSQLUtilities] Replaced By: exportUtilSQLAnalysis [SQL: PT_exportUtilSQLAnalysis] (%SYS.PTools.UtilSQLAnalysis) Status: New Functionality Purpose: Create a file containing all the data from joining both the %SYS.PTools.SQLUtilities and %SYS.PTools.SQLUtilResults classes & return the output location... Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLUtilities).exportSQLUtilities(...) SQL: CALL %SYS_PTools.PT_exportSQLUtilities(...) SELECT %SYS_PTools.PT_exportSQLUtilities(...) 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 Display this export file to the screen via a InterSystems IRIS Terminal: set tSC=##class(%SYS.PTools.SQLUtilities).exportSQLUtilities($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportSQLUtilities('$IO','H') Where: '$IO' = Output to the current device NOTE: '$IO' can be omitted, as it is the default 'file' when invoked via the CALL interface 'H' = Output the format in HTML format NOTE: 'H' can be omitted, as it is the default 'format' when invoked via the CALL interface NOTE: This method has been added to this DEPRECATED class to correspond with the newly created 'export' method defined for the new & improved interface in the '%SYS.PTools.UtilSQLAnalysis' class Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.SQLUtilities & %SYS.PTools.SQLUtilResults classes: (e.g. C:\exportFile.txt) NOTE: $IO = Outputs the data to the screen '$IO' = Output the data to the current device when invoked from the CALL/SELECT-interface '$DEF' = Ouput the data to the DEFAULT file when invoked from the CALL/SELECT-interface [DEFAULT: {Current-Directory}\PT_exportSQLUtilities_YYYYMMDD_HHMMSS.{ext}] WHERE {Current-Directory} = The directory of the Namespace in which this method is invoked (e.g. $ZU(12,"")) NOTE 2: If Passed By Reference, then 'file' will be returned as the canonical name of the Export/Reporting file format - The output format of the Export/Reporting file This parameter has the following structure of constituent elements: format=[:$LB(,,)] Where the constituent elements have the following values: - The format of the Export/Reporting file: - P = Printable/Viewable report file (.txt file, no pagination) - D = Comma-delimited data file (.csv file) which can be read into a spreadsheet - X = Microsoft Excel XML markup file suitable for import into Excel (.xml file) - H = HTML page file (.html file) - Z = User-defined delimiter "P" formatted file (.dlm file) NOTE: The element is required or defaulted to $C(9) (TAB) Optional Elements: - User-defined delimiter [DEFAULT: $C(9) (TAB)] NOTE: This is generally used for ="Z" - .csv file header for ="D": - 0 = Don't add non-standard information header to file [DEFAULT] - 1 = Add non-standard information header to file - .csv file footer for ="D": - 0 = Don't add non-standard information footer to file [DEFAULT] - 1 = Add non-standard information footer to file EXAMPLES: Valid 'format' values: - "H" // HTML file - "D" // CSV file with No information header or footer - "D:"_$LB(,1,1) // CSV file with information header & footer - "Z:"_$LB("^") // User-defined delimiter file (delim="^") silent - 0 - Display all messages during the running of this method 1 - Don't display any messages during the running of this method type - A single type, or a comma-delimited list of {Type} values to restrict the output of rows from the %SYS.PTools.SQLUtilities class. The {Type} is the location within InterSystems IRIS from where the SQL Query was extracted: {Type} values: { cached query | Class Method | Class Query | Routine } option - A single option, or a comma-delimited list of {OptionName} values to restrict the output of rows from the %SYS.PTools.SQLUtilResults class. The {OptionName} specifies the Index Usage Analysis method used to collect the data: {OptionName} values: { IU - IndexUsage & AllIndices | TS - TableScans | TI - TempIndices | JI - JoinIndices } conds - A string/array of possible conditions by which to restrict the output of the Export file, in the following format: conds= OR conds(0)=pos Count (WHERE pos = {1...n}) conds(pos)=$LIST() Pieces: 1) [] (Assumed Default: &&) 2) [{Heading}] (Omit for override cond) 3) | WHERE: := := {&& | ||} (&& = AND | || = OR) @* := @ := @ = Contains no references to {Heading} * = Contains no references to {*} EXAMPLE: conds="("",INFO,MAIN,""[("",""_{Module}_"",""))||({GlobalRefs}>20)" conds(0)=3 conds(1)=$LB(,"Module","=""INFO""") conds(2)=$LB("||","Module","=""MAIN""") conds(3)=$LB("||","GlobalRefs",">20") conds(0)=2 conds(1)=$LB(,"Module",""",INFO,MAIN,""[("",""_{*}_"","")") conds(2)=$LB("||","GlobalRefs","{*}>20") WHERE: {*} = value substitution for the 'Module' & 'GlobalRefs' fields NOTE: Conditions that don't contain any references to {Heading} fields, often called s, can be included as s, as in the following example: conds(pos)=$LB(,,"$g(^zAction(""runIt""))=1") NOTE: All conditions must be satisfied for the conds() to be considered true and for the row to be exported [PASS BY REFERENCE] includeSQL - 1 = Include the SQL Text in the output as the last field [DEFAULT: 1] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo(variable)=value ptInfo("outputFile")=The canonical name of the Export/Reporting file RETURN Value: The status from the invocation of this method -------------------------------------------------------------------------------

• classmethod map(data, mt, mts, alg, qnum, str)

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: map Replaced By: map (%SYS.PTools.StatsSQL) Purpose: * INTERNAL USE ONLY * Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Method! -------------------------------------------------------------------------------

• classmethod streamAsText(streamID="") as %String [ SQLProc = PT_streamAsText ]

------------------------------------------------------------------------------- Method: streamAsText [SQL: PT_streamAsText] Replaced By: N/A Status: New Functionality Purpose: This method is passed a valid Stream ID based on the %Stream.GlobalCharacter datatype and returns the stream text as a single String line (e.g. a SQL Statement) Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLUtilities).streamAsText({streamID}) SQL: SELECT %SYS_PTools.PT_streamAsText({streamID}) Where {streamID} is a valid Stream ID Examples: The following examples shows the use of this method: #1 Show all SQL Statements in this class: SELECT ID, %EXACT(Type) AS QueryType, %SYS_PTools.PT_streamAsText(SQLText) AS SQLText FROM %SYS_PTools.SQLUtilities Parameters: streamID - A valid Stream ID based on the %Stream.GlobalCharacter datatype RETURN Value: The SQL Statement Text for the given 'streamID'; Otherwise, return an error message if an error occurred -------------------------------------------------------------------------------

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

Queries


• query FindSQL()
Selects QueryType As %String, QueryCount As %Integer
SQL Query :
SELECT %EXACT(Type), Count(*)
FROM %SYS_PTools.SQLUtilities
GROUP BY Type

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Query: FindSQL Replaced By: countSQLStmtTypes (%SYS.PTools.UtilSQLAnalysis) Status: Maintained for Backward-Compatibility Purpose: Class Query to return the SQL Statement Types and the number of Statements collected for each Query Types. Example: The following example show the locations (QueryType) from which the SQL Statements were collected from the invocation of the 'GetSQLStatements()' methods: - Cached Queries - Class Methods - Class Queries - MAC Routines SELECT * FROM %SYS_PTools.SQLUtilities_FindSQL() -------------------------------------------------------------------------------

• query GetSQLStatements(SQLTextExt As %Integer = 0)
Selects Type As %String, Class/Routine Name As %String, ImportPackage As %String, SQL Text As %String
SQL Query :
SELECT Type, Name, ImportPackage,
CASE
WHEN :SQLTextExt = 1 THEN %SYS_PTools.PT_streamAsText(SQLText)
ELSE SQLText
END AS "SQL Text"
FROM %SYS_PTools.SQLUtilities
ORDER BY Type, Name

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Query: GetSQLStatements Replaced By: getSQLStmts (%SYS.PTools.UtilSQLAnalysis) Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Query! Parameters: SQLTextExt - 0 = Don't convert the 'SQLText' property; just return it as a stream (%Stream.GlobalCharacter) value 1 = Converts the value of the 'SQLText' property from a logical stream (%Stream.GlobalCharacter) format into a string (%Library.String) format, and return that string -------------------------------------------------------------------------------

• query IndexUsage(schema)
Selects SchemaName As %String, TableName As %String, IndexName As %String, Usage Count As %Integer
SQL Query :
SELECT %Exact(SchemaName), %Exact(Tablename),
%Exact(IndexName), UsageCount AS "Usage Count"
FROM %SYS_PTools.SQLUtilResults
WHERE OptionName = 'IU'
AND (SchemaName = :schema OR :schema IS NULL)
ORDER BY UsageCount

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Query: IndexUsage Replaced By: indexUsage (%SYS.PTools.UtilSQLAnalysis) DETAILS: For additional details see the 'Replaced By' Class & Query! -------------------------------------------------------------------------------

• query JoinIndices(schema, SQLTextExt As %Integer = 0)
Selects SchemaName As %String, TableName As %String, Type As %String, Class/Routine Name As %String, IndexName As %String, Index Needed As %String, Join Index Flag As %Integer, ExtentSize As %Integer, Block Count As %Integer, SQL Text As %String
SQL Query :
SELECT %Exact(SchemaName), %Exact(TableName), SQLPointer->Type, SQLPointer->Name,
%Exact(IndexName), $LISTTOSTRING(IndexFields) As "Index Needed",
JoinIndexFlag AS "Join Index Flag",
ExtentSize, BlockCount AS "Block Count",
CASE
WHEN :SQLTextExt = 1 THEN %SYS_PTools.PT_streamAsText(SQLPointer->SQLText)
ELSE SQLPointer->SQLText
END AS "SQL Text"
FROM %SYS_PTools.SQLUtilResults AS oq
WHERE OptionName = 'JI'
AND (SchemaName = :schema OR :schema IS NULL)
AND JoinIndexFlag < 4
-- MRP907 (DON'T USE): GROUP BY JoinIndexFlag, SchemaName, TableName, IndexFields
ORDER BY JoinIndexFlag DESC

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Query: JoinIndices Replaced By: joinIndices (%SYS.PTools.UtilSQLAnalysis) Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Query! Parameters: schema - The schema of the table being queried in the SQL Statement to match against the 'SchemaName' property/field SQLTextExt - 0 = Don't convert the 'SQLText' property; just return it as a stream (%Stream.GlobalCharacter) value 1 = Converts the value of the 'SQLText' property from a logical stream (%Stream.GlobalCharacter) format into a string (%Library.String) format, and return that string -------------------------------------------------------------------------------

• query PossiblePlans(sql As %String)
Selects ID As %Integer, Cost As %Integer, MapType As %String(MAXLEN=15), StartingMap As %String(MAXLEN=30), Plan As %String(MAXLEN=15)

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Query: PossiblePlans [SQL: PossiblePlans] Replaced By: possiblePlans [SQL: PT_possiblePlans] (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Query! -------------------------------------------------------------------------------

• query PossiblePlansStats(sql As %String, ids As %String)
Selects ID As %Integer, Cost As %Integer, StartingMap As %String, GlobalRef As %Integer, Commands As %Integer, TotalTime As %Integer, RowsReturned As %Integer, Plan As %String

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Query: PossiblePlansStats [SQL: PossiblePlansStats] Replaced By: possiblePlansStats [SQL: PT_possiblePlansStats] (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Query! -------------------------------------------------------------------------------

• query SQLUtilStmtsAndResultsView()
SQL Query as view "SQLUtilStmtsAndResultsView":
SELECT SUS.ID AS Stmt_ID, SUS.Type, SUS.Name, '' AS MethodName, SUS.ImportPackage,
SUR.ID AS Result_ID, SUR.OptionName, SUR.Counter,
SUR.SchemaName, SUR.TableName, SUR.ModuleName, SUR.MapType, SUR.ExtentSize, SUR.BlockCount,
SUR.IndexName, SUR.UsageCount, $LISTTOSTRING(SUR.IndexFields,',') AS IndexFields, $LISTTOSTRING(SUR.DataValues,',') AS DataValues,
SUR.JoinIndexFlag, '' As JoinFields,
SUS.SQLText AS SQLTextExt
FROM %SYS_PTools.SQLUtilities AS SUS JOIN %SYS_PTools.SQLUtilResults AS SUR ON SUS.ID = SUR.SQLPointer

------------------------------------------------------------------------------- Query/View: SQLUtilStmtsAndResultsView Replaced By: UtilSQLStmtsAndAnalysisDBView (%SYS.PTools.UtilSQLAnalysis) Purpose: -------------------------------------------------------------------------------

• query TableScans(schema, SQLTextExt As %Integer = 0)
Selects SchemaName As %String, TableName As %String, Type As %String, Class/Routine Name As %String, ModuleName As %String, Map Type As %String, ExtentSize As %Integer, Block Count As %Integer, SQL Text As %String
SQL Query :
SELECT %Exact(SchemaName), %Exact(TableName), SQLPointer->Type, SQLPointer->Name,
ModuleName, MapType AS "Map Type",
ExtentSize, BlockCount AS "Block Count",
CASE
WHEN :SQLTextExt = 1 THEN %SYS_PTools.PT_streamAsText(SQLPointer->SQLText)
ELSE SQLPointer->SQLText
END AS "SQL Text"
FROM %SYS_PTools.SQLUtilResults
WHERE OptionName = 'TS'
AND (SchemaName = :schema OR :schema IS NULL)
ORDER BY BlockCount DESC

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Query: TableScans Replaced By: tableScans (%SYS.PTools.UtilSQLAnalysis) Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Query! Parameters: schema - The schema of the table being queried in the SQL Statement to match against the 'SchemaName' property/field SQLTextExt - 0 = Don't convert the 'SQLText' property; just return it as a stream (%Stream.GlobalCharacter) value 1 = Converts the value of the 'SQLText' property from a logical stream (%Stream.GlobalCharacter) format into a string (%Library.String) format, and return that string -------------------------------------------------------------------------------

• query TempIndices(schema, SQLTextExt As %Integer = 0)
Selects SchemaName As %String, TableName As %String, Type As %String, Class/Routine Name As %String, IndexName As %String, Index Fields As %String, Data Fields As %String, ExtentSize As %Integer, Block Count As %Integer, SQL Text As %String
SQL Query :
SELECT %Exact(SchemaName), %Exact(TableName), SQLPointer->Type, SQLPointer->Name,
%Exact(IndexName), $LISTTOSTRING(IndexFields) As "Index Fields", $LISTTOSTRING(DataValues) As "Data Fields",
ExtentSize, BlockCount AS "Block Count",
CASE
WHEN :SQLTextExt = 1 THEN %SYS_PTools.PT_streamAsText(SQLPointer->SQLText)
ELSE SQLPointer->SQLText
END AS "SQL Text"
FROM %SYS_PTools.SQLUtilResults
WHERE OptionName = 'TI'
AND (SchemaName = :schema OR :schema IS NULL)
ORDER BY BlockCount DESC

------------------------------------------------------------------------------- -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Query: TempIndices Replaced By: tempIndices (%SYS.PTools.UtilSQLAnalysis) Status: Maintained for Backward-Compatibility DETAILS: For additional details see the 'Replaced By' Class & Query! Parameters: schema - The schema of the table being queried in the SQL Statement to match against the 'SchemaName' property/field SQLTextExt - 0 = Don't convert the 'SQLText' property; just return it as a stream (%Stream.GlobalCharacter) value 1 = Converts the value of the 'SQLText' property from a logical stream (%Stream.GlobalCharacter) format into a string (%Library.String) format, and return that string -------------------------------------------------------------------------------


Triggers


•trigger OnDelete (AFTER event DELETE)