%SYSTEM.SQL.PTools
abstract class %SYSTEM.SQL.PTools extends %SYSTEM.Help
Class: %SYSTEM.SQL.PTools
Replaces: %SYSTEM.SQL [PTools/SQLStats Methods]
Purpose:
This class is designed to provides an interface for all of the
PTools/SQLStats runtime methods. While most of these methods remain
available from the classes in which they are defined, this interface
is provided as a quick way to invoke these methods in one central
location.
The special $SYSTEM variable, which is an alias for the %SYSTEM
package containing classes that provide methods for a wide variety of
needs, can be used to invoke methods within this class:
Method Inventory
- aggregateSQLStats()
- clearError()
- clearGeneralStats()
- clearSQLAnalysisDB()
- clearSQLIndexStats()
- clearSQLStatements()
- clearSQLStatsALL()
- exportGeneralStats()
- exportIUAnalysis()
- exportJIAnalysis()
- exportOIAnalysis()
- exportSQLAnalysis()
- exportSQLAnalysisDB()
- exportSQLStatements()
- exportSQLStats()
- exportTIAnalysis()
- exportTSAnalysis()
- getAllCachedQrySQLStmts()
- getAllClassMethSQLStmts()
- getAllClassQrySQLStmts()
- getAllIndices()
- getAllRtnQrySQLStmts()
- getAllSQLStmts()
- getCachedQrySQLStmtsByClass()
- getCachedQrySQLStmtsByDays()
- getClassMethSQLStmtsByClass()
- getClassQrySQLStmtsByClass()
- getError()
- getImportSchema()
- getIndexFlagInfo()
- getQueryText()
- getRtnQrySQLStmtsByRtn()
- getSQLStatsFlag()
- getSQLStatsFlagByPID()
- getSQLStmts()
- indexUsage()
- joinIndices()
- outlierIndices()
- setSQLStatsFlag()
- setSQLStatsFlagByNS()
- setSQLStatsFlagByPID()
- setSQLStatsFlagJob()
- startGeneralStats()
- stopGeneralStats()
- tableScans()
- tempIndices()
- version()
- versionSQLAnalysis()
- versionSQLStats()
Methods
Method: aggregateSQLStats [SQL: PTools_aggregateSQLStats] Replaces: N/A Status: New Functionality Purpose: This function aggregates all of the statistical properties for each SQL Query where data was collected Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.PTools.aggregateSQLStats(...) SQL: CALL %SYSTEM_SQL.PTools_aggregateSQLStats(...) SELECT %SYSTEM_SQL.PTools_aggregateSQLStats(...) 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 Aggregate all of the statistical properties for the 'SAMPLES' namespace: set status=$SYSTEM.SQL.PTools.aggregateSQLStats("SAMPLES") Data Storage: ^%sqlcq($NAMESPACE,"PTools","db","Stats{C|D|I|S|Z}") Parameters: ns - The namespace in which to aggregate SQL Statistics If none provided, spin through all namespaces where SQL Stats collected rtn - The routine in which to aggregate SQL Statistics If none provided, spin through all routines where SQL Stats collected curs - The cursor in which to aggregate SQL Statistics If none provided, spin through all cursor where SQL Stats collected Return: Error Status
Method: clearError [SQL: PTools_clearError Replaces: N/A Status: New Functionality Purpose: This method deletes a specific 'PTools' Application Error for the given 'errRowID' or Deletes all errors if no 'errRowID' is provided. Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.PTools.clearError(...) SQL: SELECT %SYSTEM_SQL.PTools_clearError(...) Data Storage: ^%sqlcq($NAMESPACE,"PTools","Error",{errRowID}) Parameters: errRowID - The 'PTools' Error RowID to delete; If none specified, then delete all recorded PTools errors RETURN Value: The 'PTools' Error RowID ({errRowID}) deleted, the empty string ("") if {errRowID} invalid, or the word 'all' indicating an array of all errors deleted.
C L E A R 'Stats' D A T A --------------------------- The following methods are used to remove all of the 'Stats' data: - clearStatsALL() %SYS.PTools.Stats - clearStats() %SYS.PTools.Stats - clearStatsAllNS() %SYS.PTools.Stats The aforementioned methods remove data stored in the following classes: - %SYS.PTools.Stats (old/new) NOTE: old = Backward-compatible data
Method: clearGeneralStats [SQL: PTools_clearGeneralStats] Replaces: N/A Status: New Functionality Purpose: This method deletes all of the data stored in the '%SYS.PTools.Stats' class, based on the specified 'ns' and 'rtn' parameters... This method clears statistics which are gathered by the invocation of the following two methods: do $SYSTEM.SQL.PTools.startGeneralStats(namespace,routine,mod) // Start Stats Collection // Invoke the method in which statistics are to be collected do $SYSTEM.SQL.PTools.stopGeneralStats(namespace,routine,mod) // Stop Stats Collection Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.PTools.clearGeneralStats(...) SQL: CALL %SYSTEM_SQL.PTools_clearGeneralStats(...) SELECT %SYSTEM_SQL.PTools_clearGeneralStats(...) 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. Data Storage: ^%sqlcq($NAMESPACE,"PTools","db","Stats{C|D|I|S|Z}") Parameters: ns - The namespace in which to clear Statistics If none provided, use the current namespaces where Stats collected [DEFAULT: Current Namespace] rtn - The routine for which to clear Statistics If none provided, clear all routines in the given 'namespace' [OPTIONAL] ph3 - Placeholder Parameter for future extensibility 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.Stats [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","clearStats")=The number of rows deleted via this 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.Stats
Method: clearSQLAnalysisDB [SQL: PTools_clearSQLAnalysisDB]
Replaces: ClearResults [SQL: ClearResults] (%SYS.PTools.SQLUtilities)
C L E A R 'SQL Index Stats' D A T A ------------------------------------- The following methods are used to remove all of the 'SQL Index Stats' data: - clearStatsSQLIndexALL() %SYS.PTools.Stats - clearStatsSQLIndexOLD() %SYS.PTools.Stats - ClearStatements() %SYS.PTools.SQLUtilities - ClearResults() %SYS.PTools.SQLUtilities - clearStatsSQLIndexNEW() %SYS.PTools.Stats - clearSQLStatements() %SYS.PTools.UtilSQLAnalysis - clearSQLAnalysisDB() %SYS.PTools.UtilSQLAnalysis The aforementioned methods remove data stored in the following classes: - %SYS.PTools.SQLUtilities (old) - %SYS.PTools.SQLUtilResults (old) - %SYS.PTools.UtilSQLStatements (new) - %SYS.PTools.UtilSQLAnalysisDB (new) NOTE: old = Backward-compatible data
Method: clearSQLIndexStats [SQL: PTools_clearSQLIndexStats] Replaces: N/A Status: New Functionality Purpose: This method deletes all of the data stored in the Optimal (new) PTools Utility SQL Index/Analysis Stats classes, based on the specified parameters... Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.PTools.clearSQLIndexStats(...) SQL: CALL %SYSTEM_SQL.PTools_clearSQLIndexStats(...) SELECT %SYSTEM_SQL.PTools_clearSQLIndexStats(...) 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. Since this method returns a $LIST() of %Status codes, the output can be converted to a comma-delimited string by using the $LISTTOSTRING(...) function on the ObjectScript or SQL result. Data Storage: ^%sqlcq($NAMESPACE,"PTools",...) Parameters: ns - The namespace in which to clear PTools Stats [DEFAULT: $NAMESPACE] clearErrs - 0 = Don't delete the 'PTools' Application Errors 1 = Delete all of the 'PTools' Application Errors [DEFAULT] silent - 0 = Display all messages during this method invocation [DEFAULT] 1 = Don't display any messages during this method invocation returnType - 0 = Return a %Status code of either $$$OK or $$$ERROR() [DEFAULT] 1 = Return the total number of Stats rows deleted 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","clearStatsSQLIndexALL")=The number of rows deleted via this 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 total number of Stats rows deleted; Otherwise, return an error message if an error occurred
C L E A R D A T A ------------------- For each of the SQL Statements saved in the %SYS.PTools.UtilSQLStatements class, and each of the SQL Statement Results in the %SYS.PTools.UtilSQLAnalysisDB class, use one of the following methods to delete all of the data stored in these classes: - clearSQLStatements() - clearSQLAnalysisDB()
Method: clearSQLStatements [SQL: PTools_clearSQLStatements]
Replaces: ClearStatements [SQL: ClearStatements] (%SYS.PTools.SQLUtilities)
C L E A R 'SQL Stats' D A T A ------------------------------- The following methods are used to remove all of the 'SQL Stats' data: - clearStatsSQLALL() %SYS.PTools.Stats - clearStatsSQLOLD() %SYS.PTools.Stats - clearSQLStats() %SYS.PTools.SQLStats - clearSQLQuery() %SYS.PTools.SQLQuery - clearSQLBenchMarkQry() %SYS.PTools.SQLBenchMarkQueries - clearSQLBenchMarkRslt() %SYS.PTools.SQLBenchMarkResults - clearStatsSQLNEW() %SYS.PTools.Stats - clearStatsSQL() %SYS.PTools.StatsSQL - clearStatsSQLAllNS() %SYS.PTools.StatsSQL The aforementioned methods remove data stored in the following classes: - %SYS.PTools.SQLStats (old) - %SYS.PTools.SQLQuery (old) - %SYS.PTools.SQLBenchMarkQueries (old) - %SYS.PTools.SQLBenchMarkResults (old) - %SYS.PTools.StatsSQL (new) Optional: - %SYS.PTools.SQLUtilities (old) - %SYS.PTools.SQLUtilResults (old) - %SYS.PTools.UtilSQLStatements (new) - %SYS.PTools.UtilSQLAnalysisDB (new) NOTE: old = Backward-compatible data
Method: clearSQLStatsALL [SQL: PTools_clearSQLStatsALL] Replaces: N/A Status: New Functionality Purpose: This method deletes all of the data stored in the Optimal (new) PTools SQL Stats classes, based on the specified parameters... Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.PTools.clearSQLStatsALL(...) SQL: CALL %SYSTEM_SQL.PTools_clearSQLStatsALL(...) SELECT %SYSTEM_SQL.PTools_clearSQLStatsALL(...) 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. Data Storage: ^%sqlcq($NAMESPACE,"PTools",...) Parameters: ns - The namespace in which to clear PTools SQL Statistics [DEFAULT: $NAMESPACE] rtn - The routine in which to clear PTools SQL Statistics [DEFAULT: ""] If none provided, clear all routines in the given 'ns' ph3 - Placeholder Parameter for future extensibility clearAll - 0 = Delete all of the data, except the 'INFO' rows, stored in the StatsSQL class: - %SYS.PTools.StatsSQL (new) 1 = Delete all of the data, including the 'INFO' rows, [DEFAULT] stored in the StatsSQL class: - %SYS.PTools.StatsSQL (new) clearSQLIndex - 0 = Don't delete the data stored in the Utility SQL [DEFAULT] Index/Analysis classes: - %SYS.PTools.UtilSQLStatements (new) - %SYS.PTools.UtilSQLAnalysisDB (new) 1 = Delete all of the data stored in the Utility SQL Index/Analysis classes: - %SYS.PTools.UtilSQLStatements (new) - %SYS.PTools.UtilSQLAnalysisDB (new) clearErrs - 0 = Don't delete the 'PTools' Application Errors 1 = Delete all of the 'PTools' Application Errors [DEFAULT] purgeCQ - 0 = Don't purge all of the Cached Queries [DEFAULT] 1 = Purge all of the Cached Queries silent - 0 = Display all messages during this method invocation [DEFAULT] 1 = Don't display any messages during this method invocation returnType - 0 = Return a %Status code of either $$$OK or $$$ERROR() [DEFAULT] 1 = Return the total number of Stats rows deleted 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","clearSQLStatsALL")=The number of rows deleted via this 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 total number of Stats rows deleted; Otherwise, return an error message if an error occurred
Method: exportGeneralStats [SQL: PTools_exportGeneralStats]
Replaces: Report
E X P O R T I N G / R E P O R T I N G ----------------------------------------- The following Exporting/Reporting methods allow for the exporting & reporting of SQL Statements and accompanying SQL Index Usage Analytical data generated from the invocation of one of the following SQL Index Analyzer methods: SQL Index Analyzer Exporting/Reporting ================== ==================== - indexUsage() - exportIUAnalysis() - tableScans() - exportTSAnalysis() - tempIndices() - exportTIAnalysis() - joinIndices() - exportJIAnalysis() - outlierIndices() - exportOIAnalysis() NOTE: The data from the aforementioned methods are stored in the following classes/tables, and output the data from each method's corresponding Query with the same name: - %SYS.PTools.UtilSQLStatements - %SYS.PTools.UtilSQLAnalysisDB
Method: exportIUAnalysis [SQL: PTools_exportIUAnalysis]
Replaces: N/A
Status: New Functionality
Purpose: Create a file containing all the data from joining both the
%SYS.PTools.UtilSQLStatements and %SYS.PTools.UtilSQLAnalysisDB
classes & return the output location...
Invocation: This method can be invoked in the following ways:
ObjectScript: $SYSTEM.SQL.PTools.exportIUAnalysis(...)
SQL: CALL %SYSTEM_SQL.PTools_exportIUAnalysis(...)
SELECT %SYSTEM_SQL.PTools_exportIUAnalysis(...)
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=$SYSTEM.SQL.PTools.exportIUAnalysis($IO)
#2 Display this export file via the SQL Query Page of the
Management Portal:
CALL %SYSTEM_SQL.PTools_exportIUAnalysis('$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: To query this information via SQL, use the following View:
%SYS_PTools.UtilSQLAnalysis_indexUsage()
Parameters:
file - The path and file in which to create and store the data from the
%SYS.PTools.UtilSQLStatements & %SYS.PTools.UtilSQLAnalysisDB 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_exportIUAnalysis_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=
Method: exportJIAnalysis [SQL: PTools_exportJIAnalysis]
Replaces: N/A
Status: New Functionality
Purpose: Create a file containing all the data from joining both the
%SYS.PTools.UtilSQLStatements and %SYS.PTools.UtilSQLAnalysisDB
classes & return the output location...
Invocation: This method can be invoked in the following ways:
ObjectScript: $SYSTEM.SQL.PTools.exportJIAnalysis(...)
SQL: CALL %SYSTEM_SQL.PTools_exportJIAnalysis(...)
SELECT %SYSTEM_SQL.PTools_exportJIAnalysis(...)
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=$SYSTEM.SQL.PTools.exportJIAnalysis($IO)
#2 Display this export file via the SQL Query Page of the
Management Portal:
CALL %SYSTEM_SQL.PTools_exportJIAnalysis('$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: To query this information via SQL, use the following View:
%SYS_PTools.UtilSQLAnalysis_joinIndices()
Parameters:
file - The path and file in which to create and store the data from the
%SYS.PTools.UtilSQLStatements & %SYS.PTools.UtilSQLAnalysisDB 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_exportJIAnalysis_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=
Method: exportOIAnalysis [SQL: PTools_exportOIAnalysis]
Replaces: N/A
Status: New Functionality
Purpose: Create a file containing all the data from joining both the
%SYS.PTools.UtilSQLStatements and %SYS.PTools.UtilSQLAnalysisDB
classes & return the output location...
Invocation: This method can be invoked in the following ways:
ObjectScript: $SYSTEM.SQL.PTools.exportOIAnalysis(...)
SQL: CALL %SYSTEM_SQL.PTools_exportOIAnalysis(...)
SELECT %SYSTEM_SQL.PTools_exportOIAnalysis(...)
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=$SYSTEM.SQL.PTools.exportOIAnalysis($IO)
#2 Display this export file via the SQL Query Page of the
Management Portal:
CALL %SYSTEM_SQL.PTools_exportOIAnalysis('$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: To query this information via SQL, use the following View:
%SYS_PTools.UtilSQLAnalysis_outlierIndices()
Parameters:
file - The path and file in which to create and store the data from the
%SYS.PTools.UtilSQLStatements & %SYS.PTools.UtilSQLAnalysisDB 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_exportOIAnalysis_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=
Method: exportSQLAnalysis [SQL: PTools_exportSQLAnalysis]
Replaces: exportSQLUtilities [SQL: PT_exportSQLUtilities] (%SYS.PTools.SQLUtilities)
Method: exportSQLAnalysisDB [SQL: PTools_exportSQLAnalysisDB]
Replaces: exportSQLUtilStmts [SQL: PT_exportSQLUtilStmts] (%SYS.PTools.SQLUtilities)
E X P O R T I N G / R E P O R T I N G ----------------------------------------- The following methods allow for the exporting and reporting of SQL Statements and accompanying data that have been saved in one of the following two classes/tables: - %SYS.PTools.UtilSQLStatements - %SYS.PTools.UtilSQLAnalysisDB
Method: exportSQLStatements [SQL: PTools_exportSQLStatements]
Replaces: exportSQLUtilStmts [SQL: PT_exportSQLUtilStmts] (%SYS.PTools.SQLUtilities)
Method: exportSQLStats [SQL: PTools_exportSQLStats]
Replaces: Export & ExportAll (%SYS.PTools.SQLStats)
Method: exportTIAnalysis [SQL: PTools_exportTIAnalysis]
Replaces: N/A
Status: New Functionality
Purpose: Create a file containing all the data from joining both the
%SYS.PTools.UtilSQLStatements and %SYS.PTools.UtilSQLAnalysisDB
classes & return the output location...
Invocation: This method can be invoked in the following ways:
ObjectScript: $SYSTEM.SQL.PTools.exportTIAnalysis(...)
SQL: CALL %SYSTEM_SQL.PTools_exportTIAnalysis(...)
SELECT %SYSTEM_SQL.PTools_exportTIAnalysis(...)
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=$SYSTEM.SQL.PTools.exportTIAnalysis($IO)
#2 Display this export file via the SQL Query Page of the
Management Portal:
CALL %SYSTEM_SQL.PTools_exportTIAnalysis('$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: To query this information via SQL, use the following View:
%SYS_PTools.UtilSQLAnalysis_tempIndices()
Parameters:
file - The path and file in which to create and store the data from the
%SYS.PTools.UtilSQLStatements & %SYS.PTools.UtilSQLAnalysisDB 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_exportTIAnalysis_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=
Method: exportTSAnalysis [SQL: PTools_exportTSAnalysis]
Replaces: N/A
Status: New Functionality
Purpose: Create a file containing all the data from joining both the
%SYS.PTools.UtilSQLStatements and %SYS.PTools.UtilSQLAnalysisDB
classes & return the output location...
Invocation: This method can be invoked in the following ways:
ObjectScript: $SYSTEM.SQL.PTools.exportTSAnalysis(...)
SQL: CALL %SYS_PTools.PTools_exportTSAnalysis(...)
SELECT %SYS_PTools.PTools_exportTSAnalysis(...)
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=$SYSTEM.SQL.PTools.exportTSAnalysis($IO)
#2 Display this export file via the SQL Query Page of the
Management Portal:
CALL %SYS_PTools.PTools_exportTSAnalysis('$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: To query this information via SQL, use the following View:
%SYS_PTools.UtilSQLAnalysis_tableScans()
Parameters:
file - The path and file in which to create and store the data from the
%SYS.PTools.UtilSQLStatements & %SYS.PTools.UtilSQLAnalysisDB 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_exportTSAnalysis_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=
C A C H E D Q U E R I E S --------------------------- Gather SQL Statements from all of the Cached Queries within InterSystems IRIS and add them to the %SYS.PTools.UtilSQLStatements class for Index Analysis and additional processing...
Method: getAllCachedQrySQLStmts [SQL: PTools_getAllCachedQrySQLStmts] Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all of the Cached Queries created and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Cached Queries added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred
C L A S S M E T H O D S ------------------------- Gather SQL Statements from all of the Class Methods within InterSystems IRIS and add them to the %SYS.PTools.UtilSQLStatements class for Index Analysis and additional processing...
Method: getAllClassMethSQLStmts [SQL: PTools_getAllClassMethSQLStmts] Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all of the Class Methods created and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipSysClass - 0 = Don't skip all System Classes 1 = Skip all System Classes that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 2 = Skip all System Classes that are not defined in the namespace in which this method is invoked 3 = Skip all System Classes defined by InterSystems, even if the class 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. 13 => #1 & #3) [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of cached queries added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred
C L A S S Q U E R Y --------------------- Gather SQL Statements from all of the Class Queries within InterSystems IRIS and add them to the %SYS.PTools.UtilSQLStatements class for Index Analysis and additional processing...
Method: getAllClassQrySQLStmts [SQL: PTools_getAllClassQrySQLStmts] Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all of the Class Queries created and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipSysClass - 0 = Don't skip all System Classes 1 = Skip all System Classes that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 2 = Skip all System Classes that are not defined in the namespace in which this method is invoked 3 = Skip all System Classes defined by InterSystems, even if the class 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. 13 => #1 & #3) [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Class Queries added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred
C O L L E C T S Q L I N D E X E S ===================================== A L L S Q L I N D E X E S ----------------------------- Gather SQL Indexes from all classes within InterSystems IRIS and add them to the %SYS.PTools.SQLUtilResult class for Index Analysis and additional processing
Method: getAllIndices [SQL: PTools_getAllIndices] Replaces: N/A [AllIndices^%SYS.PToolsDEP] Status: New Functionality Purpose: This method processes all of the classes/tables on the system and collects all of the defined Indexes and adds them to the %SYS.PTools.UtilSQLAnalysisDB class for future processing... Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.PTools.getAllIndices(...) SQL: CALL %SYSTEM_SQL.PTools_getAllIndices(...) SELECT %SYSTEM_SQL.PTools_getAllIndices(...) 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 the data collected from the running of this method: SELECT * FROM %SYS_PTools.UtilSQLAnalysis_getAllIndices() NOTE: These queries can either 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 show below: >do $SYSTEM.SQL.Shell() Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLAnlsys","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipSysClass - 0 = Don't skip all System Classes 1 = Skip all System Classes that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 2 = Skip all System Classes that are not defined in the namespace in which this method is invoked 3 = Skip all System Classes defined by InterSystems, even if the class 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. 13 => #1 & #3) [DEFAULT: 1] skipIDKeys - 1 = Skip all the index that will be used to form the Object Identity value (IDKEY) for the given class [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Indexes added to the %SYS.PTools.UtilSQLAnalysisDB class; Otherwise, return an error message if an error occurred
R O U T I N E Q U E R Y ------------------------- Gather SQL Statements from all of the MAC Routines within InterSystems IRIS and add them to the %SYS.PTools.UtilSQLStatements class for Index Analysis and additional processing...
Method: getAllRtnQrySQLStmts [SQL: PTools_getAllRtnQrySQLStmts] Replaces: N/A Status: New Functionality Purpose: Get the Embedded SQL Statements from all of the MAC Routines and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipSysRtn - 0 = Don't skip all System Routines 1 = Skip all System Routines that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 2 = Skip all System Routines that are not defined in the namespace in which this method is invoked 3 = Skip all System Routines defined by InterSystems, even if the routine 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. 13 => #1 & #3) [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Embedded SQL Statements added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred
A L L S Q L S T A T E M E N T S ----------------------------------- Gather SQL Statements from all places within InterSystems IRIS and add them to the %SYS.PTools.UtilSQLStatements class for Index Analysis and additional processing...
Method: getAllSQLStmts [SQL: PTools_getAllSQLStmts] Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all of the following locations and add them to the %SYS.PTools.UtilSQLStatements class for additional processing: - Cached Queries - Class Methods - Class Queries - MAC Routines Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.PTools.getAllSQLStmts(...) SQL: CALL %SYSTEM_SQL.PTools_getAllSQLStmts(...) SELECT %SYSTEM_SQL.PTools_getAllSQLStmts(...) 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.UtilSQLStatements GROUP BY Type OR SELECT * FROM %SYS_PTools.UtilSQLAnalysis_countSQLStmtTypes() #2 Show the data collected from the running of this method: SELECT Type, Name, MethodName, ImportPackage, SQLTextExt AS "SQL Text" FROM %SYS_PTools.UtilSQLStatements ORDER BY Type, Name OR SELECT * FROM %SYS_PTools.UtilSQLAnalysis_getAllSQLStmts() OR SELECT Type, "Class/Routine Name", "Method Name", ImportPackage, "SQL Text" FROM %SYS_PTools.UtilSQLAnalysis_getAllSQLStmts() 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","util","SQLStmts","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipSysObj - 0 = Don't skip all System Objects (Classes & Routines) 1 = Skip 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: Pass the combination of the specified options if more than one option desired (e.g. 13 => #1 & #3) [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [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. $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
Method: getCachedQrySQLStmtsByClass [SQL: PTools_getCachedQrySQLStmtsByClass Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all Cached Queries provided in the 'items' array and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: item - A comma delimited list or array of Cached Query class names NOTE: The class names must either all contain class extensions (e.g. myClass.CLS) or none at all (e.g. myClass) EXAMPLE: items="%sqlcq.SAMPLES.cls1,%sqlcq.SAMPLES.cls2" OR items("%sqlcq.SAMPLES.cls1")="" items("%sqlcq.SAMPLES.cls2")="" clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Cached Queries added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred
Method: getCachedQrySQLStmtsByDays [SQL: PTools_getCachedQrySQLStmtsByDays] Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all Cached Queries that have had a Prepare in the last 'days' days and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: days - The number of days since the last Cached Query was Prepared. If days=0, then get all Cached Queries. [DEFAULT: 0 (All)] clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Cached Queries added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred
Method: getClassMethSQLStmtsByClass [SQL: PTools_getClassMethSQLStmtsByClass] Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all Class Methods provided in the 'items' array and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: item - A comma delimited list or array of Class Methods class names NOTE: The class names must either all contain class extensions (e.g. myClass.CLS) or none at all (e.g. myClass) EXAMPLE: items="Cinema.Film,Sample.Person" OR items("Cinema.Film")="" items("Sample.Person")="" clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Class Methods added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred
Method: getClassQrySQLStmtsByClass [SQL: PTools_getClassQrySQLStmtsByClass] Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all Class Queries provided in the 'items' array and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: item - A comma delimited list or array of Class Query class names NOTE: The class names must either all contain class extensions (e.g. myClass.CLS) or none at all (e.g. myClass) EXAMPLE: items="Cinema.Film,Sample.Person" OR items("Cinema.Film")="" items("Sample.Person")="" clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Class Queries added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred
G E T / C L E A R 'PToolsError' D A T A ----------------------------------------- The following methods are used to retrieve & remove all of the 'PToolsError' data: - getPToolsError() %SYS.PTools.Stats - clearPToolsError() %SYS.PTools.Stats The aforementioned methods remove data stored in the following global: - ^%sqlcq($NAMESPACE,"PTools","Error",{errRowID}) (old/new) NOTE: old = Backward-compatible data
Method: getError [SQL: PTools_getError] Replaces: N/A Status: New Functionality Purpose: This method retrieve a specific 'PTools' Application Error for the given 'errRowID' or retrieve all errors if no 'errRowID' is provided. Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.PTools.getError(...) SQL: SELECT %SYSTEM_SQL.PTools_getError(...) NOTE: 'PTools' Application Errors will be returned in the 'PToolsErr' array as follows: PToolsErr={errRowID} [Counter] PToolsErr({errRowID},0)=$LIST: 1) $HOROLOG [Timestamp Internal] 2) Timestamp External [Timestamp External: YYYY-MM-DD HH:MM:SS] 3) $NAMESPACE [Current Namespace] 4) $JOB [Current $JOB] 5) $ZN [Current Routine] 6) $zu(41) [Stack Level] PToolsErr({errRowID},1)=$LIST: 1) {ns} [Error Namespace] 2) {job} [Error $JOB] 3) {rtn} [Error Routine] 4) {curs} [Error SQL Cursor] 5) {mod} [Error SQL Module] 6) {func} [Error Function/Method] 7) {ze} [Error $ZE] 8) {errMsg} [Error Message] --- SQL Errors --- 9) {class} [Error Class] 10) {table} [Error Table] 11) {SQLCODE} [Error SQLCODE] 12) {%msg} [Error SQL Message] PToolsErr({errRowID},2)=errLog(errLogID)=errLogMsg PToolsErr({errRowID},2,{errLogID})={errLogMsg} Parameters: errRowID - The 'PTools' Error RowID to retrieve; If none specified, then retrieve all errors into the 'PToolsErr()' array PToolsErr - An array in which the 'PTools' Application Errors will be returned [PASS BY REFERENCE] RETURN Value: The 'PTools' Error RowID ({errRowID}) retrieved, the empty string ("") if {errRowID} invalid, or the word 'all' indicating an array of all errors returned.
Method: getImportSchema [SQL: N/A]
Replaces: ImportSchema (%SYS.PTools.SQLStats)
Method: getIndexFlagInfo [SQL: PTools_getIndexFlagInfo] Replaces: N/A Status: New Functionality Purpose: Given an Index-Flag Internal Value 'flagInt', and the Index Analyzer Method 'indexMeth' for which this flag is associated, return the specific information requested by the 'returnInfo' parameter NOTE: This is a helper-method for the following Queries: - joinIndices - outlierIndices Parameters: flagInt - The Index-Flag Internal Value NOTE: The following example are the values specified for the 'joinIndices()' method: 4 = Exact Match: An index exists where its fields match all fields from the JOIN conditions 3 = Leading Match: An index exists where its leading fields match all fields from the JOIN conditions, but there are additional fields in the index 2 = Contains Match: An index exists where its fields contains all fields from the JOIN conditions, but not the leading fields 1 = Partial Match: An index exists where its fields contains some of the fields from the JOIN conditions, but not the leading field 0 = No Match: No index exists to support the fields from the JOIN conditions indexMeth - The Index Analyzer Method for which the 'flagInt' parameter is associated - joinIndices - outlierIndices returnInfo - The specific information that should be returned from this method: 1 = The Index-Flag Title 2 = The Index-Flag Definition 3 = The Index-Flag as 'Title: Definition' RETURN Value: See the 'returnInfo' parameter for details ; Otherwise, return an error message if an error occurred
Method: getQueryText [SQL: N/A]
Replaces: QueryText (%SYS.PTools.SQLStats)
Method: getRtnQrySQLStmtsByRtn [SQL: PTools_getRtnQrySQLStmtsByRtn] Replaces: N/A Status: New Functionality Purpose: Get the Embedded SQL Statements from all of the MAC Routines provided in the 'items' array and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: item - A comma delimited list or array of MAC Routine names NOTE: The routine names must either all contain extensions (e.g. myRtn.MAC) or none at all (e.g. myRtn) EXAMPLE: items="MRPtest1,MRPtest2" OR items("MRPtest1")="" items("MRPtest2")="" clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Cached Queries added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred
Method: getSQLStatsFlag [SQL: PTools_getSQLStatsFlag] Purpose: This method gets the flag that controls whether or not we collect SQL Statistics for each SQL Query execution Get the current value of the SQLStats-flag for the given 'flagType'. The SQLStats-flag (System/Job) controls whether or not SQL Statistics are collected for each SQL Query execution, and which performance statistics to be collected. The SQLStats-flag is a colon (:) delimited string comprised of the following individual components: SQLStats-flag={action-flag}:{collect-flag}:{condition-flag}:{condition-value}:{terminate-flag}:{flag-type} RULES: When (flagType=""), whether to use the SQLStats-flag (System) or the SQLStats-flag (Process/Job) is determined by the following rules: IF {action-flag} (Process/Job) = 0, then use SQLStats-flag (System) ELSE use SQLStats-flag (Process/Job) NOTE: This method has the following additional rule when passed the parameter: flagType="" RULE-G1: If {action-flag}=-1, then return zero (0) for each component-flag of the SQLStats-flag, except for the {flag-type} which is one (1) [indicating a Process/Job Flag setting], as in the following example: write $SYSTEM.SQL.PTools.getSQLStatsFlag("") => "0:0:0::0:1" To retrieve the actual value for each component-flag of the SQLStats-flag in this example, pass a one (1) for the 'flagType' parameter: write $SYSTEM.SQL.PTools.getSQLStatsFlag(1) => "-1:0:0::0:1" Invocation: This method can be invoked in the following ways: Object Script: $SYSTEM.SQL.PTools.getSQLStatsFlag(...) SQL: SELECT %SYSTEM_SQL.PTools_getSQLStatsFlag(...) Examples: The following examples shows the use of this method: #1 Get the current value of the SQLStats-flag for the Job/System Flag based on SQLStats-flag Rules: set SQLStatsFlag=$SYSTEM.SQL.PTools.getSQLStatsFlag("") Parameters: flagType - "" = Job/System Flag based on SQLStats-flag Rules [DEFAULT] 0 = System Flag 1 = Process/Job Flag ptInfo - A Pass By Reference information array that returns to the user detailed information in the following format: ptInfo(category,variable)=value Where category = { "current" | "terminate" | ["expired"] | ... } variable = A variable that corresponds to the given 'category' Example: ptInfo("current","SQLStatsFlag")=The SQLStats-flag value after method execution RETURN: The current value of the SQLStats-flag; Otherwise, return an error message if an error occurred
Method: getSQLStatsFlagByPID [SQL: PTools_getSQLStatsFlagByPID] Purpose: This method gets the flag that controls whether or not we collect SQL Statistics about each SQL Query execution for the given 'pid' Get the current value of the SQLStats-flag for the given 'pid'. The SQLStats-flag (Process/Job) controls whether or not SQL Statistics are collected for each SQL Query execution, and which performance statistics to be collected. The SQLStats-flag is a colon (:) delimited string comprised of the following individual components: SQLStats-flag={action-flag}:{collect-flag}:{condition-flag}:{condition-value}:{terminate-flag}:{flag-type} NOTE: This method ONLY applies to the SQLStats-flag for the given 'pid' (Process/Job) and NOT the SQLStats-flag for the (System)! RESTRICTION: This method invocation requires %Admin_Operate:Use privilege Invocation: This method can be invoked in the following ways: Object Script: $SYSTEM.SQL.PTools.getSQLStatsFlagByPID(...) SQL: SELECT %SYSTEM_SQL.PTools_getSQLStatsFlagByPID(...) Examples: The following examples shows the use of this method: #1 Get the current value of the SQLStats-flag for the Job/System Flag based on SQLStats-flag Rules for PID# 12345: set SQLStatsFlag=$SYSTEM.SQL.PTools.getSQLStatsFlagByPID(12345) Parameters: pid - The process ID ($JOB) for which to set the SQLStats-flag [DEFAULT: Current $JOB] ptInfo - A Pass By Reference information array that returns to the user detailed information in the following format: ptInfo(category,variable)=value NOTE: This method currently returns no 'ptInfo', but is included for future extensibility RETURN: The current value of the SQLStats-flag; Otherwise, return an error message if an error occurred
Method: getSQLStmts [SQL: PTools_getSQLStmts]
Replaces: GetSQLStatements (%SYS.PTools.SQLUtilities)
A N A L Y Z E S Q L S T A T E M E N T S =========================================== I N D E X U S A G E --------------------- For each of the SQL Statements saved in the %SYS.PTools.UtilSQLStatements class, generates a ShowPlan and keeps a count of how many times each index is used by each query, along with the total usage for each index by all queries in the namespace, and store this information in the %SYS.PTools.UtilSQLAnalysisDB class.
Method: indexUsage [SQL: PTools_indexUsage]
Replaces: IndexUsage [SQL: IndexUsage] (%SYS.PTools.SQLUtilities)
Q U E R I E S W I T H M I S S I N G J O I N I N D I C E S ----------------------------------------------------------------- For each of the SQL Statements saved in the %SYS.PTools.UtilSQLStatements class, identify all queries that have joins, and determines if there is an index defined to support that join. It ranks the indices available to support the joins from 0 (no index present) to 4 (index fully supports the join). Outer joins require an index in one direction. Inner joins require an index in both directions. The result set only contains rows that have a JoinIndexFlag < 4. JoinIndexFlag=4 means there is an index that fully supports the join; these are not listed.
Method: joinIndices [SQL: PTools_joinIndices]
Replaces: JoinIndices [SQL: JoinIndices] (%SYS.PTools.SQLUtilities)
Q U E R I E S W I T H M I S S I N G O U T L I E R I N D I C E S ----------------------------------------------------------------------- For each of the SQL Statements saved in the %SYS.PTools.UtilSQLStatements class, identify all queries that have outliers, and determines if there is an index defined to support that outlier. It ranks the indices available to support the outlier from 0 (no index present) to 4 (index fully supports the outlier).
Method: outlierIndices [SQL: PTools_outlierIndices]
Replaces: N/A
Status: New Functionality
Purpose: This method uses the SQL Statement data stored in the
%SYS.PTools.UtilSQLStatements class to pinpoint the
queries that have outliers, and determines if there is an index
defined to support the Outlier Condition. This method
will then ranks the indices available to support the Outlier
Condition from 0 (no index present) to 4 (index fully supports
the Outlier Condition).
Pertinent information about these queries is subsequently
stored in the the %SYS.PTools.UtilSQLAnalysisDB class
for future processing and analysis.
While the result-set stored in the the %SYS.PTools.UtilSQLAnalysisDB
contains rows for all value of the 'OutlierIndexFlag' field, the
outlierIndices() Query for this class/table only returns the rows
that have the following
S Q L S T A T S M E T H O D S ================================= %SYS.PTools.StatsSQL CLASS -------------------------- The following methods are defined in the %SYS.PTools.StatsSQL class and are designed to gather performance statistics for SQL Queries. Additional details can be found within the defining class.
Method: setSQLStatsFlag [SQL: PTools_setSQLStatsFlag]
Purpose: This method sets the flag that controls whether or not the
System collects SQL Statistics about each run of a query
You can invoke different levels of SQL Statistics collection by
setting the SQLStats-flag, for the System with this method.
The SQLStats-flag controls whether or not SQL Statistics are
collected for each SQL Query execution, and which performance
statistics to collect.
The SQLStats-flag is a colon (:) delimited string comprised of
the following individual components:
SQLStats-flag={action-flag}:{collect-flag}:{condition-flag}:{condition-value}:{terminate-flag}:{flag-type}
Invocation: This method can be invoked in the following ways:
Object Script: $SYSTEM.SQL.PTools.setSQLStatsFlag(...)
SQL: SELECT %SYSTEM_SQL.PTools_setSQLStatsFlag(...)
Examples: The following examples shows the use of this method:
#1 Turn PTools ON to collects stats for all SQL modules (System):
set oldStats=$SYSTEM.SQL.PTools.setSQLStatsFlag(3)
Parameters:
actionFlag - The portion of the SQLStats-flag which is represented by the
1st colon (:) piece, and can have one of the following values:
0 = Query Compilation: Don't generator SQLStats collection
code for any Query Modules
Query Execution: Don't collect SQLStats for any Query
Modules
1 = Query Compilation: Generator SQLStats collection code for
MAIN Query Module (Start & Stop)
Query Execution: Don't collect SQLStats for any Query
Modules
2 = Query Compilation: Generator SQLStats collection code for
MAIN Query Module (Start & Stop)
Query Execution: Collect SQLStats for MAIN Query Module
(Start & Stop)
3 = Query Compilation: Generator SQLStats collection code for
ALL Query Modules
Query Execution: Collect SQLStats for ALL Query Modules
collectFlag - The portion of the SQLStats-flag which is represented by the 2nd
colon (:) piece, and is a numeric value representing which SQL
Performance Statistics/Metrics to collect, with one of the
following values:
"" = Collect All SQL Performance Metrics [DEFAULT]
n = Where n=SUM(MVal) [See Below]
In general, all of the following performance metrics are collected
for each SQL module, along with a cumulative value representing
all SQL modules, when the SQLStats-flag is turned on:
MVal Metric Name Metric Description
---- ---------------------------- ----------------------------------
1 Query Execution Time Total number of seconds elapsed
2 Total Global References Total number of global references
4 Total Lines of Code Executed Total number of COS lines executed
8 Total Disk Read Latency Time Total milliseconds spent waiting
==== for Disk Reads
15 ALL Collect all Performance Metrics
This parameter provides a mechanism by which to choose a
combination of which performance metrics to collect, by specifying
a SUM of the performance metrics MVal numbers (specified above)
that you wish to collect.
EXAMPLE #1:
The following example represents the collection of 2 performance
metrics, 'Query Execution Time' & 'Total Disk Read Latency Time':
MVal Metric Name
---- ----------------------------
1 Query Execution Time
+8 Total Disk Read Latency Time
===
9 Collect both of these performance metrics
By passing 9 for the value of the 'collectFlag' parameter, the
sum of the two aforementioned performance metrics MVal numbers,
the SQLStats PTool application will only collect statistical data
for these two performance metrics whenever an SQL Query is invoked.
EXAMPLE #2:
The following example represents the collection of 3 performance
metrics, 'Query Execution Time', 'Total Global References' &
'Total Lines of Code Executed':
MVal Metric Name
---- ----------------------------
1 Query Execution Time
2 Total Global References
+4 Total Lines of Code Executed
===
7 Collect both of these performance metrics
By passing 7 for the value of the 'collectFlag' parameter, the
sum of the three aforementioned performance metrics MVal numbers,
the SQLStats PTool application will only collect statistical data
for these two performance metrics whenever an SQL Query is invoked.
terminateCond - The portion of the SQLStats-flag which is represented by the
5th colon (:) piece, and is determined by this parameter value.
An optional condition to determine when to terminate the SQLStats
collection, by turning off the SQLStats-flag (System), or by setting
it to a specified reset value. This parameter accepts the following
values:
0 No Action [DEFAULT]
M:
Method: setSQLStatsFlagByNS [SQL: PTools_setSQLStatsFlagByNS]
Purpose: This method sets the flag that controls whether or not to collect
SQL Statistics about each run of a query within the given 'ns'
(Namespace)
You can invoke different levels of SQL Statistics collection by
setting the SQLStats-flag, for a given 'ns'.
The SQLStats-flag (System) controls whether or not SQL Statistics
are collected for each SQL Query execution, and which performance
statistics to be collected.
The SQLStats-flag is a colon (:) delimited string comprised of
the following individual components:
SQLStats-flag={action-flag}:{collect-flag}:{condition-flag}:{condition-value}:{terminate-flag}:{flag-type}
NOTE: This method ONLY applies to the SQLStats-flag (System) for the
given 'namespace' and NOT the SQLStats-flag (Process/Job)!
Invocation: This method can be invoked in the following ways:
Object Script: $SYSTEM.SQL.PTools.setSQLStatsFlagByNS(...)
SQL: SELECT %SYSTEM_SQL.PTools_setSQLStatsFlagByNS(...)
Examples: The following examples shows the use of this method:
#1 Turn PTools ON to collects stats for all SQL modules within the
"USER" Namespace:
set oldStats=$SYSTEM.SQL.PTools.setSQLStatsFlagByNS("USER",3)
Parameters:
ns - The Namespace ($NAMESPACE) for which to set the SQLStats-flag
[REQUIRED]
actionFlag - The portion of the SQLStats-flag which is represented by the
1st colon (:) piece, and can have one of the following values:
0 = Query Compilation: Don't generator SQLStats collection
code for any Query Modules
Query Execution: Don't collect SQLStats for any Query
Modules
1 = Query Compilation: Generator SQLStats collection code for
MAIN Query Module (Start & Stop)
Query Execution: Don't collect SQLStats for any Query
Modules
2 = Query Compilation: Generator SQLStats collection code for
MAIN Query Module (Start & Stop)
Query Execution: Collect SQLStats for MAIN Query Module
(Start & Stop)
3 = Query Compilation: Generator SQLStats collection code for
ALL Query Modules
Query Execution: Collect SQLStats for ALL Query Modules
collectFlag - The portion of the SQLStats-flag which is represented by the 2nd
colon (:) piece, and is a numeric value representing which SQL
Performance Statistics/Metrics to collect, with one of the
following values:
"" = Collect All SQL Performance Metrics [DEFAULT]
n = Where n=SUM(MVal) [See Below]
In general, all of the following performance metrics are collected
for each SQL module, along with a cumulative value representing
all SQL modules, when the SQLStats-flag is turned on:
MVal Metric Name Metric Description
---- ---------------------------- ----------------------------------
1 Query Execution Time Total number of seconds elapsed
2 Total Global References Total number of global references
4 Total Lines of Code Executed Total number of COS lines executed
8 Total Disk Read Latency Time Total milliseconds spent waiting
==== for Disk Reads
15 ALL Collect all Performance Metrics
This parameter provides a mechanism by which to choose a
combination of which performance metrics to collect, by specifying
a SUM of the performance metrics MVal numbers (specified above)
that you wish to collect.
EXAMPLE #1:
The following example represents the collection of 2 performance
metrics, 'Query Execution Time' & 'Total Disk Read Latency Time':
MVal Metric Name
---- ----------------------------
1 Query Execution Time
+8 Total Disk Read Latency Time
===
9 Collect both of these performance metrics
By passing 9 for the value of the 'collectFlag' parameter, the
sum of the two aforementioned performance metrics MVal numbers,
the SQLStats PTool application will only collect statistical data
for these two performance metrics whenever an SQL Query is invoked.
EXAMPLE #2:
The following example represents the collection of 3 performance
metrics, 'Query Execution Time', 'Total Global References' &
'Total Lines of Code Executed':
MVal Metric Name
---- ----------------------------
1 Query Execution Time
2 Total Global References
+4 Total Lines of Code Executed
===
7 Collect both of these performance metrics
By passing 7 for the value of the 'collectFlag' parameter, the
sum of the three aforementioned performance metrics MVal numbers,
the SQLStats PTool application will only collect statistical data
for these two performance metrics whenever an SQL Query is invoked.
terminateCond - The portion of the SQLStats-flag which is represented by the
5th colon (:) piece, and is determined by this parameter value.
An optional condition to determine when to terminate the SQLStats
collection, by turning off the SQLStats-flag (System), or by setting
it to a specified reset value. This parameter accepts the following
values:
0 No Action [DEFAULT]
M:
Method: setSQLStatsFlagByPID [SQL: PTools_setSQLStatsFlagByPID] Purpose: This method sets the flag that controls whether or not to collect SQL Statistics about each run of a query for the given 'pid' You can invoke different levels of SQL Statistics collection by setting the SQLStats-flag, for a given 'pid'. The SQLStats-flag (Process/Job) controls whether or not SQL Statistics are collected for each SQL Query execution, and which performance statistics to be collected. The SQLStats-flag is a colon (:) delimited string comprised of the following individual components: SQLStats-flag={action-flag}:{collect-flag}:{condition-flag}:{condition-value}:{terminate-flag}:{flag-type} NOTE: This method ONLY applies to the SQLStats-flag for the given 'pid' (Process/Job) and NOT the SQLStats-flag for the (System)! RESTRICTION: This method invocation requires %Admin_Operate:Use privilege Invocation: This method can be invoked in the following ways: Object Script: $SYSTEM.SQL.PTools.setSQLStatsFlagByPID(...) SQL: SELECT %SYSTEM_SQL.PTools_setSQLStatsFlagByPID(...) Examples: The following examples shows the use of this method: #1 Turn PTools ON to collects stats for all SQL modules for PID# 12345: set oldStats=$SYSTEM.SQL.PTools.setSQLStatsFlagByPID(12345,3) Parameters: pid - The process ID ($JOB) for which to set the SQLStats-flag [DEFAULT: Current $JOB] actionFlag - The portion of the SQLStats-flag which is represented by the 1st colon (:) piece, and can have one of the following values: -1 = Query Compilation: Turn SQLStats Off for the given 'pid' Query Execution: Turn SQLStats Off for the given 'pid' 0 = Query Compilation: Use the {action-flag} (System) setting Query Execution: Use the {action-flag} (System) setting 1 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Don't collect SQLStats for any Query Modules 2 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Collect SQLStats for MAIN Query Module (Start & Stop) 3 = Query Compilation: Generator SQLStats collection code for ALL Query Modules Query Execution: Collect SQLStats for ALL Query Modules collectFlag - The portion of the SQLStats-flag which is represented by the 2nd colon (:) piece, and is a numeric value representing which SQL Performance Statistics/Metrics to collect, with one of the following values: "" = Collect All SQL Performance Metrics [DEFAULT] n = Where n=SUM(MVal) [See Below] In general, all of the following performance metrics are collected for each SQL module, along with a cumulative value representing all SQL modules, when the SQLStats-flag is turned on: MVal Metric Name Metric Description ---- ---------------------------- ---------------------------------- 1 Query Execution Time Total number of seconds elapsed 2 Total Global References Total number of global references 4 Total Lines of Code Executed Total number of COS lines executed 8 Total Disk Read Latency Time Total milliseconds spent waiting ==== for Disk Reads 15 ALL Collect all Performance Metrics This parameter provides a mechanism by which to choose a combination of which performance metrics to collect, by specifying a SUM of the performance metrics MVal numbers (specified above) that you wish to collect. EXAMPLE #1: The following example represents the collection of 2 performance metrics, 'Query Execution Time' & 'Total Disk Read Latency Time': MVal Metric Name ---- ---------------------------- 1 Query Execution Time +8 Total Disk Read Latency Time === 9 Collect both of these performance metrics By passing 9 for the value of the 'collectFlag' parameter, the sum of the two aforementioned performance metrics MVal numbers, the SQLStats PTool application will only collect statistical data for these two performance metrics whenever an SQL Query is invoked. EXAMPLE #2: The following example represents the collection of 3 performance metrics, 'Query Execution Time', 'Total Global References' & 'Total Lines of Code Executed': MVal Metric Name ---- ---------------------------- 1 Query Execution Time 2 Total Global References +4 Total Lines of Code Executed === 7 Collect both of these performance metrics By passing 7 for the value of the 'collectFlag' parameter, the sum of the three aforementioned performance metrics MVal numbers, the SQLStats PTool application will only collect statistical data for these two performance metrics whenever an SQL Query is invoked. ptInfo - A Pass By Reference information array that returns to the user detailed information in the following format: ptInfo(category,variable)=value NOTE: This method currently returns no 'ptInfo', but is included for future extensibility RETURN: The value of the SQLStats-flag before setting it to the new value; Otherwise, return an error message if an error occurred
Method: setSQLStatsFlagJob [SQL: PTools_setSQLStatsFlagJob] Purpose: This method sets the flag that controls whether or not this Process/Job collects SQL Statistics about each run of a query You can invoke different levels of SQL Statistics collection by setting the SQLStats-flag, for your current Process/Job with this method. The SQLStats-flag controls whether or not SQL Statistics are collected for each SQL Query execution, and which performance statistics to collect. The SQLStats-flag is a colon (:) delimited string comprised of the following individual components: SQLStats-flag={action-flag}:{collect-flag}:{condition-flag}:{condition-value}:{terminate-flag}:{flag-type} Invocation: This method can be invoked in the following ways: Object Script: $SYSTEM.SQL.PTools.setSQLStatsFlagJob(...) SQL: SELECT %SYSTEM_SQL.PTools_setSQLStatsFlagJob(...) Examples: The following examples shows the use of this method: #1 Turn PTools ON to collects stats for all SQL modules: set oldStats=$SYSTEM.SQL.PTools.setSQLStatsFlagJob(3) Parameters: actionFlag - The portion of the SQLStats-flag which is represented by the 1st colon (:) piece, and can have one of the following values: -1 = Query Compilation: Turn SQLStats Off for this Process/Job Query Execution: Turn SQLStats Off for this Process/Job [-1 ONLY when flagType=1] 0 = Query Compilation: Use the {action-flag} (System) setting Query Execution: Use the {action-flag} (System) setting 1 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Don't collect SQLStats for any Query Modules 2 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Collect SQLStats for MAIN Query Module (Start & Stop) 3 = Query Compilation: Generator SQLStats collection code for ALL Query Modules Query Execution: Collect SQLStats for ALL Query Modules collectFlag - The portion of the SQLStats-flag which is represented by the 2nd colon (:) piece, and is a numeric value representing which SQL Performance Statistics/Metrics to collect, with one of the following values: "" = Collect All SQL Performance Metrics [DEFAULT] n = Where n=SUM(MVal) [See Below] In general, all of the following performance metrics are collected for each SQL module, along with a cumulative value representing all SQL modules, when the SQLStats-flag is turned on: MVal Metric Name Metric Description ---- ---------------------------- ---------------------------------- 1 Query Execution Time Total number of seconds elapsed 2 Total Global References Total number of global references 4 Total Lines of Code Executed Total number of COS lines executed 8 Total Disk Read Latency Time Total milliseconds spent waiting ==== for Disk Reads 15 ALL Collect all Performance Metrics This parameter provides a mechanism by which to choose a combination of which performance metrics to collect, by specifying a SUM of the performance metrics MVal numbers (specified above) that you wish to collect. EXAMPLE #1: The following example represents the collection of 2 performance metrics, 'Query Execution Time' & 'Total Disk Read Latency Time': MVal Metric Name ---- ---------------------------- 1 Query Execution Time +8 Total Disk Read Latency Time === 9 Collect both of these performance metrics By passing 9 for the value of the 'collectFlag' parameter, the sum of the two aforementioned performance metrics MVal numbers, the SQLStats PTool application will only collect statistical data for these two performance metrics whenever an SQL Query is invoked. EXAMPLE #2: The following example represents the collection of 3 performance metrics, 'Query Execution Time', 'Total Global References' & 'Total Lines of Code Executed': MVal Metric Name ---- ---------------------------- 1 Query Execution Time 2 Total Global References +4 Total Lines of Code Executed === 7 Collect both of these performance metrics By passing 7 for the value of the 'collectFlag' parameter, the sum of the three aforementioned performance metrics MVal numbers, the SQLStats PTool application will only collect statistical data for these two performance metrics whenever an SQL Query is invoked. p3 - Placeholder Parameter for future extensibility ptInfo - A Pass By Reference information array that returns to the user detailed information in the following format: ptInfo(category,variable)=value Where category = { "current" | "terminate" | ... } variable = A variable that corresponds to the given 'category' Example: ptInfo("current","SQLStatsFlag")=The SQLStats-flag value after method execution RETURN: The value of the SQLStats-flag before setting it to the new value; Otherwise, return an error message if an error occurred
G E N E R A L S T A T S M E T H O D S ========================================= %SYS.PTools.Stats CLASS ----------------------- The following methods are defined in the %SYS.PTools.Stats class and are designed to gather performance statistics for any type of routine or code block. Additional details can be found within the defining class.
Method: startGeneralStats Replaces: N/A Status: Original Functionality Purpose: This method is used to START the PTools statistics collection Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.PTools.startGeneralStats(...) SQL: N/A Examples: The following examples shows the use of this method: #1 Start collecting stats to analyze the class 'Populate' method: do $SYSTEM.SQL.PTools.startGeneralStats($NAMESPACE,"Test","Populate") do ##class(Sample.Person).Populate(100) do $SYSTEM.SQL.PTools.stopGeneralStats($NAMESPACE,"Test","Populate") Data Storage: ^%sqlcq($NAMESPACE,"PTools","db","Stats{C|D|I|S|Z}") Parameters: ns - The namespace in which the stats collection is occurring rtn - The routine name for which the stats collection is occurring mod - The module name for which the stats collection is occurring [DEFAULT: 1] RETURN Value: (NONE)
Method: stopGeneralStats Replaces: N/A Status: Original Functionality Purpose: This method is used to STOP the PTools statistics collection This method INSERTs the stats information into the %SYS_PTools.Stats table Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.PTools.stopGeneralStats(...) SQL: N/A Examples: The following examples shows the use of this method: #1 Stop collecting stats to analyze the class 'Populate' method: do $SYSTEM.SQL.PTools.startGeneralStats($NAMESPACE,"Test","Populate") do ##class(Sample.Person).Populate(100) do $SYSTEM.SQL.PTools.stopGeneralStats($NAMESPACE,"Test","Populate") Data Storage: ^%sqlcq($NAMESPACE,"PTools","db","Stats{C|D|I|S|Z}") Parameters: ns - The namespace in which the stats collection is occurring rtn - The routine name for which the stats collection is occurring mod - The module name for which the stats collection is occurring [DEFAULT: 1] RETURN Value: (NONE)
Q U E R I E S W I T H T A B L E S C A N S ----------------------------------------------- For each of the SQL Statements saved in the %SYS.PTools.UtilSQLStatements class, identify all queries that do table scans. Table scans should be avoided if at all possible, but a table scan can't always be avoided. However, if a table has a large number of table scans, the indices defined for that table should be reviewed. Often the list of table scans and the list of temp indices will overlap; fixing one will remove the other. The result set lists the tables from largest Block Count to smallest Block Count.
Method: tableScans [SQL: PTools_tableScans]
Replaces: TableScans [SQL: TableScans] (%SYS.PTools.SQLUtilities)
Q U E R I E S W I T H T E M P I N D I C E S ------------------------------------------------- For each of the SQL Statements saved in the %SYS.PTools.UtilSQLStatements class, identify all queries that build temporary indices to resolve the SQL. Sometimes the use of a temp index is helpful and improves performance, for example building a small index based on a range condition that InterSystems IRIS can then use to read the master map in order. Sometimes a temp index is simply a subset of a different index and might be very efficient. Other times a temporary index degrades performance, for example scanning the master map to build a temporary index on a property that has a condition. This situation indicates that a needed index is missing; you should add an index to the class that matches the temporary index. The result set lists the tables from largest Block Count to smallest Block Count.
Method: tempIndices [SQL: PTools_tempIndices]
Replaces: TempIndices [SQL: TempIndices] (%SYS.PTools.SQLUtilities)
Method: versionSQLAnalysis [SQL: PTools_versionSQLAnalysis] Replaces: N/A Status: New Functionality Purpose: Get the current version for the %SYS.PTools.UtilSQLStatements & %SYS.PTools.UtilSQLAnalysisDB classes/sections of the Performance Tools (PTools) Application... Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.PTools.versionSQLAnalysis(...) SQL: SELECT %SYSTEM_SQL.PTools_versionSQLAnalysis(...) Examples: The following examples shows the use of this method: #1 Display the UtilSQLAnalysis version to the screen via an InterSystems IRIS Terminal: write $SYSTEM.SQL.PTools.versionSQLAnalysis() // Simple Version write $SYSTEM.SQL.PTools.versionSQLAnalysis(1) // Full Version #2 Display the UtilSQLAnalysis version via the SQL Query Page of the Management Portal: SELECT %SYSTEM_SQL.PTools_versionSQLAnalysis() /* Simple Version */ SELECT %SYSTEM_SQL.PTools_versionSQLAnalysis(1) /* Full Version */ Data Storage: N/A Parameters: fullVer - 0 = Display the UtilSQLAnalysis Simple Version [DEFAULT] 1 = Display the UtilSQLAnalysis Full Version RETURN Value: The UtilSQLAnalysis {Simple | Full} Version