Skip to main content

%SYS.PTools.SQLUtilities

deprecated persistent class %SYS.PTools.SQLUtilities extends %Library.Persistent

SQL Table Name: %SYS_PTools.SQLUtilities

-- 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 ------------------------------------------------------------------------------

Property Inventory

Method Inventory

Properties

property ImportPackage as %String;
comma delimited list of Package names to use compiling queries
Property methods: ImportPackageDisplayToLogical(), ImportPackageGet(), ImportPackageGetStored(), ImportPackageIsValid(), ImportPackageLogicalToDisplay(), ImportPackageLogicalToOdbc(), ImportPackageNormalize(), ImportPackageSet()
property Name as %String;
Property methods: NameDisplayToLogical(), NameGet(), NameGetStored(), NameIsValid(), NameLogicalToDisplay(), NameLogicalToOdbc(), NameNormalize(), NameSet()
property SQLText as %Stream.GlobalCharacter;
Property methods: SQLTextDelete(), SQLTextGet(), SQLTextGetObject(), SQLTextGetObjectId(), SQLTextGetStored(), SQLTextGetSwizzled(), SQLTextIsValid(), SQLTextNewObject(), SQLTextOid(), SQLTextOpen(), SQLTextSet(), SQLTextSetObject(), SQLTextSetObjectId(), SQLTextUnSwizzle()
property Type as %String;
Property methods: TypeDisplayToLogical(), TypeGet(), TypeGetStored(), TypeIsValid(), TypeLogicalToDisplay(), TypeLogicalToOdbc(), TypeNormalize(), TypeSet()

Methods

deprecated 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 %RawString) as %Status [ SQLProc = ClearResults ]
Projected as the stored procedure: 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 %RawString) as %Status [ SQLProc = ClearStatements ]
Projected as the stored procedure: ClearStatements
Backward-compatible Entry Point: NOTE: See the clearSQLUtilStmts() method above for more details.
deprecated 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 %RawString) as %Status [ SQLProc = GetSQLStatements ]
Projected as the stored procedure: 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 - 0 = Skip all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 1 = Get/Process all System Objects (Classes & Routines) 2 = Skip all System Objects that are not defined in the namespace in which this method is invoked 3 = Skip all System Objects defined by InterSystems, even if the object is also defined in the namespace in which this method is invoked NOTE: Pass the combination of the specified options if more than one option desired (e.g. 30 => #3 & #0) [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.

deprecated 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 %RawString) as %Status [ SQLProc = IndexUsage ]
Projected as the stored procedure: 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 - 0 = Skip all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 1 = Get/Process all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 2 = Skip all System Objects that are not defined in the namespace in which this method is invoked 3 = Skip all System Objects defined by InterSystems, even if the object is also defined in the namespace in which this method is invoked NOTE-1: Pass the combination of the specified options if more than one option desired (e.g. 30 => #3 & #0) NOTE-2: This only applies when retrieving SQL Indices (getIndices=1) and/or SQL Statements (PopTable=1) [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

deprecated 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 %RawString) as %Status [ SQLProc = JoinIndices ]
Projected as the stored procedure: 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 - 0 = Skip all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 1 = Get/Process all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 2 = Skip all System Objects that are not defined in the namespace in which this method is invoked 3 = Skip all System Objects defined by InterSystems, even if the object is also defined in the namespace in which this method is invoked NOTE-1: Pass the combination of the specified options if more than one option desired (e.g. 30 => #3 & #0) NOTE-2: This only applies when retrieving SQL Statements (PopTable=1) [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

deprecated 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!

deprecated 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!

deprecated 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!

deprecated 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!

deprecated 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!

deprecated 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!

deprecated 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

deprecated 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 %RawString) as %Status [ SQLProc = TableScans ]
Projected as the stored procedure: 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 - 0 = Skip all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 1 = Get/Process all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 2 = Skip all System Objects that are not defined in the namespace in which this method is invoked 3 = Skip all System Objects defined by InterSystems, even if the object is also defined in the namespace in which this method is invoked NOTE-1: Pass the combination of the specified options if more than one option desired (e.g. 30 => #3 & #0) NOTE-2: This only applies when retrieving SQL Statements (PopTable=1) [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

deprecated 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 %RawString) as %Status [ SQLProc = TempIndices ]
Projected as the stored procedure: 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 - 0 = Skip all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 1 = Get/Process all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 2 = Skip all System Objects that are not defined in the namespace in which this method is invoked 3 = Skip all System Objects defined by InterSystems, even if the object is also defined in the namespace in which this method is invoked NOTE-1: Pass the combination of the specified options if more than one option desired (e.g. 30 => #3 & #0) NOTE-2: This only applies when retrieving SQL Statements (PopTable=1) [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

deprecated classmethod clearSQLUtilStmtResults(ns As %String = "", clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %RawString) as %Status [ SQLProc = PT_clearSQLUtilStmtResults ]
Projected as the stored procedure: 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

deprecated classmethod clearSQLUtilStmts(ns As %String = "", clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %RawString) as %Status [ SQLProc = PT_clearSQLUtilStmts ]
Projected as the stored procedure: 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

deprecated classmethod exportSQLUtilStmts(file As %String = "", format As %String = "", silent As %Integer = 0, type As %String = "", ByRef conds As %RawString, ByRef ptInfo As %RawString) as %Status [ SQLProc = PT_exportSQLUtilStmts ]
Projected as the stored procedure: 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

deprecated classmethod exportSQLUtilities(file As %String = "", format As %String = "", silent As %Integer = 0, type As %String = "", option As %String = "", ByRef conds As %RawString, includeSQL As %Integer = 1, ByRef ptInfo As %RawString) as %Status [ SQLProc = PT_exportSQLUtilities ]
Projected as the stored procedure: 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

deprecated 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!

deprecated classmethod streamAsText(streamID="") as %String [ SQLProc = PT_streamAsText ]
Projected as the stored procedure: 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 ]
Projected as the stored procedure: SQLUtilities_version
Provide the current version for the SQLUtilities class/section of the Performance Tools (PTools) Application

Queries

deprecated 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()

deprecated 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

deprecated 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!

deprecated 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

deprecated 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!

deprecated 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:

deprecated 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

deprecated 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

Indexes

index (IDKEY on ) [IdKey, Type = key];
Index methods: IDKEYCheck(), IDKEYDelete(), IDKEYExists(), IDKEYOpen(), IDKEYSQLCheckUnique(), IDKEYSQLExists(), IDKEYSQLFindPKeyByConstraint(), IDKEYSQLFindRowIDByConstraint()

Triggers

trigger OnDelete (AFTER event DELETE);

Inherited Members

Inherited Methods

Storage

Storage Model: Storage (%SYS.PTools.SQLUtilities)

^%sqlcq($NAMESPACE,"PTools","Utils","Queries","D")(ID)
=
%%CLASSNAME
Type
Name
SQLText
ImportPackage
FeedbackOpens in a new tab