%SYS.PTools.Stats

Method: Report [SQL: Stats_Report] Replaces: N/A Status: Maintained for Backward-Compatibility. See exportStats(...) [DEPRECATED] Purpose: This method generates a comma delimited file containing the data from the '%SYS.PTools.Stats' class By default the file will be created in the MGR directory of the InterSystems IRIS instance. You can pass a different value for the 'file' parameters if you wish to override the default location and file name. Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.Stats).Report(...) SQL: CALL %SYS_PTools.Stats_Report(...) SELECT %SYS_PTools.Stats_Report(...) 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 io=##class(%SYS.PTools.Stats).Report($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.Stats_Report('$IO') Where: '$IO' = Output to the current device NOTE: '$IO' can be omitted, as it is the default 'file' when invoked via the CALL interface Data Storage: ^%sqlcq($NAMESPACE,"PTools","db","Stats{C|D|I|S|Z}") Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.Stats 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: {MGR-Directory}\{computerName}_{ConfigName}_YYYYMMDD_HHMMSS.Stats] 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 output locations of the exported data; Otherwise, return the error status if one occurred

Method: Start 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: ##class(%SYS.PTools.Stats).Start(...) SQL: N/A Examples: The following examples shows the use of this method: #1 Start collecting stats to analyze the class 'Populate' method: do ##class(%SYS.PTools.Stats).Start($NAMESPACE,"Test","Populate") do ##class(Sample.Person).Populate(100) do ##class(%SYS.PTools.Stats).Stop($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 Rou - 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: Stop 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: ##class(%SYS.PTools.Stats).Stop(...) SQL: N/A Examples: The following examples shows the use of this method: #1 Stop collecting stats to analyze the class 'Populate' method: do ##class(%SYS.PTools.Stats).Start($NAMESPACE,"Test","Populate") do ##class(Sample.Person).Populate(100) do ##class(%SYS.PTools.Stats).Stop($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 Rou - The routine name for which the stats collection is occurring Mod - The module name for which the stats collection is occurring RETURN Value: (NONE)

Method: clearPToolsError [SQL: PT_clearPToolsError] 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: ##class(%SYS.PTools.Stats).clearPToolsError(...) SQL: SELECT %SYS_PTools.PT_clearPToolsError(...) 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.

Method: clearStats [SQL: PT_clearStats] 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 ##class(%SYS.PTools.Stats).Start(NS,Rou,Mod) // Start Stats Collection // Invoke the method in which statistics are to be collected do ##class(%SYS.PTools.Stats).Stop(NS,Rou,Mod) // Stop Stats Collection Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.Stats).clearStats(...) SQL: CALL %SYS_PTools.PT_clearStats(...) SELECT %SYS_PTools.PT_clearStats(...) 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 'ns' [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

C L E A R 'Stats' D A T A --------------------------- The following methods are used to remove all of the 'Stats' data: - clearStatsALL() - clearStats() - clearStatsAllNS() The aforementioned methods remove data stored in the following classes: - %SYS.PTools.Stats (old/new) NOTE: old = Backward-compatible data

Method: clearStatsALL [SQL: PT_clearStatsALL] Replaces: N/A Status: New Functionality Purpose: This method deletes all of the data stored in the Original (backward-compatible) and the Optimal (new) PTools Stats class (%SYS.PTools.Stats), based on the specified parameters... NOTE: Clear ALL PTools Stats data: OLD & NEW (By Default) Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.Stats).clearStatsALL(...) ..clearStatsALL(...) SQL: CALL %SYS_PTools.PT_clearStatsALL(...) SELECT %SYS_PTools.PT_clearStatsALL(...) 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 PTools Stats [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 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","clearStatsALL")=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: clearStatsAllNS [SQL: PT_clearStatsAllNS] Replaces: N/A Status: New Functionality Purpose: This method deletes all of the data stored in the '%SYS.PTools.Stats' class in All Namespaces on a system for which the user has Write privileges (Write infers Read), based on the optional 'rtn' parameter. This method clears statistics in all namespaces which are gathered by the invocation of the following two methods: do ##class(%SYS.PTools.Stats).Start(NS,Rou,Mod) // Start Stats Collection // Invoke the method in which statistics are to be collected do ##class(%SYS.PTools.Stats).Stop(NS,Rou,Mod) // Stop Stats Collection Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.Stats).clearStatsAllNS(...) SQL: CALL %SYS_PTools.PT_clearStatsAllNS(...) SELECT %SYS_PTools.PT_clearStatsAllNS(...) 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: rtn - The routine in which to clear SQL Statistics If none provided, clear all routines in all appropriate namespaces [OPTIONAL] ph2 - Placeholder Parameter for future extensibility 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 total number of Stats rows deleted from the following class, for each namespace: %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","clearStatsAllNS")=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

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() - clearStatsSQLOLD() - clearSQLStats() %SYS.PTools.SQLStats - clearSQLQuery() %SYS.PTools.SQLQuery - clearSQLBenchMarkQry() %SYS.PTools.SQLBenchMarkQueries - clearSQLBenchMarkRslt() %SYS.PTools.SQLBenchMarkResults - clearStatsSQLNEW() - 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: clearStatsSQLALL [SQL: PT_clearStatsSQLALL] Replaces: N/A Status: New Functionality Purpose: This method deletes all of the data stored in the Original (backward-compatible) and the Optimal (new) PTools SQL Stats classes, based on the specified parameters... NOTE: Clear ALL PTools SQL Stats data: OLD & NEW (By Default) Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.Stats).clearStatsSQLALL(...) ..clearStatsSQLALL(...) SQL: CALL %SYS_PTools.PT_clearStatsSQLALL(...) SELECT %SYS_PTools.PT_clearStatsSQLALL(...) 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 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) clearSQLQuery - 0 = Don't delete the data stored in the SQLQuery class: - %SYS.PTools.SQLQuery (old) 1 = Delete all of the data stored in the SQLQuery class: [DEFAULT] - %SYS.PTools.SQLQuery (old) clearBench - 0 = Don't delete the data stored in the SQLBenchmark class: - %SYS.PTools.SQLBenchMarkQueries (old) 1 = Delete all of the data stored in the SQLBenchmark class: [DEFAULT] - %SYS.PTools.SQLBenchMarkQueries (old) clearSQLIndex - 0 = Don't delete the data stored in the Utility SQL [DEFAULT] Index/Analysis classes: - %SYS.PTools.SQLUtilities (old) - %SYS.PTools.SQLUtilResults (old) - %SYS.PTools.UtilSQLStatements (new) - %SYS.PTools.UtilSQLAnalysisDB (new) 1 = Delete all of the data stored in the Utility SQL Index/Analysis classes: - %SYS.PTools.SQLUtilities (old) - %SYS.PTools.SQLUtilResults (old) - %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","clearStatsSQLALL")=The number of rows deleted via this method NOTE: old = Backward-compatible data 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 'SQL Index Stats' D A T A ------------------------------------- The following methods are used to remove all of the 'SQL Index Stats' data: - clearStatsSQLIndexALL() - clearStatsSQLIndexOLD() - ClearStatements() %SYS.PTools.SQLUtilities - ClearResults() %SYS.PTools.SQLUtilities - clearStatsSQLIndexNEW() - 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: clearStatsSQLIndexALL [SQL: PT_clearStatsSQLIndexALL] Replaces: N/A Status: New Functionality Purpose: This method deletes all of the data stored in the Original (backward-compatible) and the Optimal (new) PTools Utility SQL Index/Analysis Stats classes, based on the specified parameters... NOTE: Clear ALL PTools Utility SQL Index/Analysis Stats: OLD & NEW (By Default) Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.Stats).clearStatsSQLIndexALL(...) ..clearStatsSQLIndexALL(...) SQL: CALL %SYS_PTools.PT_clearStatsSQLIndexALL(...) SELECT %SYS_PTools.PT_clearStatsSQLIndexALL(...) 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

Method: clearStatsSQLIndexNEW [SQL: PT_clearStatsSQLIndexNEW] 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: ##class(%SYS.PTools.Stats).clearStatsSQLIndexNEW(...) ..clearStatsSQLIndexNEW(...) SQL: CALL %SYS_PTools.PT_clearStatsSQLIndexNEW(...) SELECT %SYS_PTools.PT_clearStatsSQLIndexNEW(...) 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 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","clearStatsSQLIndexNEW")=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: clearStatsSQLIndexOLD [SQL: PT_clearStatsSQLIndexOLD] Replaces: N/A Status: New Functionality Purpose: This method deletes all of the data stored in the Original (backward-compatible) PTools Utility SQL Index/Analysis Stats classes, based on the specified parameters... Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.Stats).clearStatsSQLIndexOLD(...) ..clearStatsSQLIndexOLD(...) SQL: CALL %SYS_PTools.PT_clearStatsSQLIndexOLD(...) SELECT %SYS_PTools.PT_clearStatsSQLIndexOLD(...) 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 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","clearStatsSQLIndexOLD")=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: clearStatsSQLNEW [SQL: PT_clearStatsSQLNEW] 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: ##class(%SYS.PTools.Stats).clearStatsSQLNEW(...) ..clearStatsSQLNEW(...) SQL: CALL %SYS_PTools.PT_clearStatsSQLNEW(...) SELECT %SYS_PTools.PT_clearStatsSQLNEW(...) 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","clearStatsSQLNEW")=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: clearStatsSQLOLD [SQL: PT_clearStatsSQLOLD] Replaces: N/A Status: New Functionality Purpose: This method deletes all of the data stored in the Original (backward-compatible) PTools SQL Stats classes, based on the specified parameters... Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.Stats).clearStatsSQLOLD(...) ..clearStatsSQLOLD(...) SQL: CALL %SYS_PTools.PT_clearStatsSQLOLD(...) SELECT %SYS_PTools.PT_clearStatsSQLOLD(...) 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 clearSQLQuery - 0 = Don't delete the data stored in the SQLQuery class: - %SYS.PTools.SQLQuery (old) 1 = Delete all of the data stored in the SQLQuery class: [DEFAULT] - %SYS.PTools.SQLQuery (old) clearBench - 0 = Don't delete the data stored in the SQLBenchmark class: - %SYS.PTools.SQLBenchMarkQueries (old) 1 = Delete all of the data stored in the SQLBenchmark class: [DEFAULT] - %SYS.PTools.SQLBenchMarkQueries (old) clearSQLIndex - 0 = Don't delete the data stored in the Utility SQL [DEFAULT] Index/Analysis classes: - %SYS.PTools.SQLUtilities (old) - %SYS.PTools.SQLUtilResults (old) 1 = Delete all of the data stored in the Utility SQL Index Analysis classes: - %SYS.PTools.SQLUtilities (old) - %SYS.PTools.SQLUtilResults (old) 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","clearStatsSQLOLD")=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: exportStats [SQL: PT_exportStats] Replaces: Report [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: Create a Performance Tool file containing the data from the '%SYS.PTools.Stats' and return the output location... By default, the file will be created in the current directory of the InterSystems IRIS instance. NOTE: Current directory can be obtained in the following way, from a InterSystems IRIS Terminal: >write $ZU(12,"") You can pass a different value for the 'file' parameters if you wish to override the default location and file name. Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.Stats).exportStats(...) SQL: CALL %SYS_PTools.PT_exportStats(...) SELECT %SYS_PTools.PT_exportStats(...) 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.Stats).exportStats($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportStats('$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 Data Storage: ^%sqlcq($NAMESPACE,"PTools","db","Stats{C|D|I|S|Z}") Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.Stats 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_Stats_exportStats_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 statsType - 0 - Return all rows from %SYS.PTools.Stats ONLY [DEFAULT] 1 - Return all rows from %SYS.PTools.StatsSQL ONLY 2 - Return all rows from %SYS.PTools.Stats & %SYS.PTools.StatsSQL rtnName - If specified, return only the rows where the 'RoutineName' matches the value of this parameter; Otherwise, return rows for all 'RoutineName' values modName - If specified, return only the rows where the 'ModuleName' matches the value of this parameter; Otherwise, return rows for all 'ModuleName' values 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 bcFlag - This is a Backward-Compatibility flag used for [Internal Purposes ONLY] RETURN Value: The status from the invocation of this method

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() - clearPToolsError() The aforementioned methods remove data stored in the following global: - ^%sqlcq($NAMESPACE,"PTools","Error",{errRowID}) (old/new) NOTE: old = Backward-compatible data

Method: getPToolsError [SQL: PT_getPToolsError] 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: ##class(%SYS.PTools.Stats).getPToolsError(...) SQL: SELECT %SYS_PTools.PT_getPToolsError(...) 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.