%SYS.PTools.SQLUtilities

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

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

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

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

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

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