Skip to main content

%SYS.PTools.SQLStats

Class %SYS.PTools.SQLStats Extends (%Persistent, %SYS.PTools.Stats) [ CompileAfter = %SYS.PTools.SQLQuery, Deprecated, Inheritance = right, System = 4 ]

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Starting with ver '2.0' of the PTools application, this class is considered to be DEPRECATED and is replaced by the %SYS.PTools.StatsSQL class. This class is still usable under extreme circumstances when invoking the 'SetSQLStatsSaveFlag()' method and passing to the (saveType) parameter the following value: 2 = Original (backward-compatible) -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --

Class: %SYS.PTools.SQLStats Replaced By: %SYS.PTools.StatsSQL Purpose: This class can be used to collect statistical information on SQL queries run on a give InterSystems IRIS system. The modules can be nested in one another. The outer module will display inclusive numbers, so the Module MAIN will be the overall results for the full query. Upon first enabling SQLStats, you should Purge cached queries in order to force code regeneration. The following commands demonstrate how to perform these tasks: >DO $SYSTEM.SQL.Purge() >DO $SYSTEM.SQL.SetSQLStatsJob(3) NOTE: To see the definition and details on how to use these methods, either view this information in the Class Reference documentation for the %SYSTEM.SQL class, or review the help via the following method invocations: >DO $SYSTEM.SQL.Help("Purge") >DO $SYSTEM.SQL.Help("SetSQLStatsJob") When you're finished collectinging statistics, disable SQLStats by using the following command: >DO $SYSTEM.SQL.SetSQLStatsJob(0) NOTE: There is no need to Purge cached queries after disabling SQLStats, because the small number of additional code-lines generated should not affect query performance. All of the PTools statistical and query informational data is stored in the %SYS.PTools.SQLQuery and '%SYS.PTools.SQLStats' classes. The %SYS.PTools.SQLQuery class holds the text of the SQL Statement, the routine name, and the module information. The '%SYS.PTools.SQLStats' class, on the other hand, holds the statistical details for each run. You can query these tables from any directory on the system, or use xDBC to view this information. The view, SQLStatsView, has been defined to make looking at the pertinent data in these classes easier. For Example: #INCLUDE %msql &SQL(DECLARE cur1 CURSOR FOR SELECT RoutineName, ModuleName, ModuleCount, GlobalRefs, DiskWait LinesOfCode, TimeSpent, RowCount, QueryType, QueryText FROM %SYS_PTools.SQLStatsView WHERE Namespace= 'SAMPLES' ) &SQL(OPEN cur1) for &SQL(FETCH cur1 INTO :A,:B,:C,:D,:E,:F,:G,:H,:I) QUIT:SQLCODE'=0 WRITE !,A," ",B," ",C," ",D," ",E," ",F," ",G," ",H," ",I &SQL(CLOSE cur1)

or for a more condensed output: #INCLUDE %msql &SQL(DECLARE cur2 CURSOR FOR SELECT V.RoutineName, V.QueryText, (SELECT COUNT(*) FROM %SYS_PTools.SQLStatsView Sub WHERE Sub.ModuleName = 'Main' AND Sub.RoutineName = V.RoutineName GROUP BY Sub.CursorName, Sub.ModuleName ) as RunCount, {fn round(avg(V.RowCount),2)} as AvgRows, {fn round(avg(V.GlobalRefs),2)} as AvgGlorefs, {fn round(avg(V.LinesOfCode),2)} as AvgLines, {fn round(avg(V.DiskWait),2)} as AvgDiskWait, {fn round(avg(V.TimeSpent),5)} as AvgTime FROM %SYS_PTools.SQLStatsView V GROUP BY V.RoutineName, V.CursorName ) &SQL(OPEN cur2) For &SQL(FETCH cur2 INTO :A,:B,:C,:D,:E,:F,:G) QUIT:SQLCODE'=0 WRITE !,A," ",B," ",C," ",D," ",E," ",F," ",G &SQL(CLOSE cur2)

Error Storage: ^%sqlcq($NAMESPACE,"PTools","Error"[...])=$LIST Info

Properties​

SQLQueryPointer​

Relationship SQLQueryPointer As %SYS.PTools.SQLQuery [ Cardinality = parent, Inverse = SQLStatsPointer ];

Counter​

Property Counter As %Integer [ SqlColumnNumber = 5 ];

This is the number of times the query has been run since the last compile.

ChildSub​

Property ChildSub As %Integer [ InitialExpression = {$$$bumpSYSPToolsSQLStatsChildSub} ];

Triggers​

OnDelete​

Trigger OnDelete [ Event = DELETE, Time = AFTER ]

Methods​

Init​

ClassMethod Init(NS As %String, Rou As %String, Cur As %String, ByRef Parms As %RawString) [ Deprecated ]

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: Init [SQL: N/A] Replaced By: startSQLStats^%SYS.PTools() Status: [DEPRECATED] Purpose: Invoked by the OPEN Cursor to initializes local variables and calles Start() for the MAIN loop to start collecting stats Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}")

Start​

ClassMethod Start(NS As %String, Rou As %String, Cur As %String, ModString As %String) [ Deprecated ]

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: Start [SQL: N/A] Replaced By: %statsStart^%SYS.PTools() Status: [DEPRECATED] Purpose: Invoked every time you enter a module to collect statistics Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}") -------------------------------------------------------------------------------

Stop​

ClassMethod Stop(NS As %String, Rou As %String, Cur As %String, ModString As %String) [ Deprecated ]

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: Stop [SQL: N/A] Replaced By: %statsStop^%SYS.PTools() Status: [DEPRECATED] Purpose: Invoked every time you exit a module to collect statistics Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}")

Report​

ClassMethod Report(NS As %String, Rou As %String, Cur As %String, RowCount As %Integer = 0) As %String [ Deprecated ]

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: Report [SQL: N/A] Replaced By: stopSQLStats^%SYS.PTools() Status: [DEPRECATED] Purpose: Invoked by the CLOSE Cursor to tidy up statistics collection OR %SYS.PTools.StatsSQL table Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}")

GlobalSave​

ClassMethod GlobalSave(ns, rou, cur, mod, rows) [ Deprecated ]

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: GlobalSave [SQL: N/A] Replaced By: saveSQLStats^%SYS.PTools() Status: [DEPRECATED] Purpose: Invoked by the Report() method to store all of the runtime data in the '%SYS.PTools.SQLStats' OR %SYS.PTools.StatsSQL table Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}")

GetStats​

ClassMethod GetStats(ByRef SQLText As %String = 0, mod As %String, showStats As %Integer = 0) As %String [ Deprecated ]

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: GetStats [SQL: N/A] Replaced By: GetStats (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility Purpose: Returns the Stats for a given module of a given SQL statement Invoked by the Show Plan code when Stats are to be included Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.StatsSQL).GetStats(...) SQL: N/A Examples: The following examples shows the use of this method: #1 Get the stats for the 'MAIN' module of the following SQL Statement from the 'SAMPLES' namespace: zn "SAMPLES" kill sql set sql($i(sql))="SELECT ID, Name FROM Sample.Person" set statsList=##class(%SYS.PTools.StatsSQL).GetStats(.sql,"MAIN",1) for pos=1:1:$LL(statsList) write !?3,$LTS($LG(statsList,pos)) ModuleName,Module,MAIN TimeSpent,Time,0.00999 GlobalRefs,Globals,1,201 LinesOfCode,Commands,41,311 DiskWait,Disk Wait,1 RowCount,Row Count,400 ModuleCount,Mod Execs,1 Counter,Run Count,1 Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}") Parameters: SQLText - An array of the SQL Statement in the following format: SQLText={ln# counter} SQLText({ln#})={SQL Statement} mod - SQL Module being processed showStats - 0 = Don't show any stats with the query plan 1 = Execute a query and get/show stats with the query plan [FROM: Query Test or ShowPlan^%apiSQL] 2 = Retrieve existing stats and show the averages with the query plan [NOTE: Query Info from ^mqh($UserName,"id") set in ##class(%CSP.UI.Portal.SQL.QButtons.RuntimeStats).PrepareShowPlan(...) [FROM: SQL Runtime Statistics->{View Stats}->Show Plan] 3 = Generate and show stats for an alternate Show Plan with the query plan RETURN Value: Query Stats as a $LIST with the following format: 1) $LIST({Property},{Header},{Value}) ... n) $LIST({Property},{Header},{Value})

SetSQLStats​

ClassMethod SetSQLStats(actionFlag As %Integer = 0) As %Integer [ Deprecated, SqlName = SetSQLStats, SqlProc ]

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: SetSQLStats [SQL: SetSQLStats] Replaced By: SetSQLStats [SQL: StatsSQL_SetSQLStats] (%SYS.PTools.StatsSQL) Status: New functionality added for Backward-Compatibility 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. Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLStats).SetSQLStats(...) SQL: SELECT %SYS_PTools.SetSQLStats(...) Examples: The following examples shows the use of this method: #1 Turn PTools ON to collects stats for all SQL modules (System): set oldStats=##class(%SYS.PTools.SQLStats).SetSQLStats(3) Data Storage: N/A Parameters: actionFlag - This parameter 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 RETURN Value: The value of the SQLStats-flag before setting it to the new value; Otherwise, return an error message if an error occurred

SetSQLStatsJob​

ClassMethod SetSQLStatsJob(actionFlag As %Integer = 0) As %Integer [ Deprecated, SqlName = SetSQLStatsJob, SqlProc ]

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: SetSQLStatsJob [SQL: SetSQLStatsJob] Replaced By: SetSQLStatsJob [SQL: StatsSQL_SetSQLStatsJob] (%SYS.PTools.StatsSQL) SetSQLStatsFlagJob [SQL: StatsSQL_SetSQLStatsFlagJob] (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility 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. Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLStats).SetSQLStatsJob(...) SQL: SELECT %SYS_PTools.SetSQLStatsJob(...) Examples: The following examples shows the use of this method: #1 Turn PTools ON to collects stats for all SQL modules: set oldStats=##class(%SYS.PTools.SQLStats).SetSQLStatsJob(3) Data Storage: N/A Parameters: actionFlag - This parameter can have one of the following values: -1 = Query Compilation: Turn SQLStats Off for this Job Query Execution: Turn SQLStats Off for this Job 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 RETURN Value: The value of the SQLStats-flag before setting it to the new value; Otherwise, return an error message if an error occurred

newSQLStats​

ClassMethod newSQLStats(SQLText, SQLType, ns, rtn, curs, compTime = 0, SQLIndexHash = "")

Stub: For the 'newSQLStats()' method in the '%SYS.PTools.MAC' routine

startSQLStats​

ClassMethod startSQLStats(ns, rtn, curs, params, SQLIndexHash, flag, collect)

Stub: For the 'startSQLStats()' method in the '%SYS.PTools.MAC' routine

stopSQLStats​

ClassMethod stopSQLStats(ns, rtn, curs, rowCount = 0, SQLIndexHash, flag, collect)

Stub: For the 'stopSQLStats()' method in the '%SYS.PTools.MAC' routine

saveSQLStats​

ClassMethod saveSQLStats(ns, rtn, curs, mod, rowCount, SQLIndexHash)

Stub: For the 'saveSQLStats()' method in the '%SYS.PTools.MAC' routine

ExportAll​

ClassMethod ExportAll(filePrefix = "", delim = "", exportPlan = 0, silent = 0, rtnName = "", modName = "", ByRef conds As %RawString, ByRef ptInfo As %RawString) As %String [ Deprecated, SqlName = SQLStats_ExportAll, SqlProc ]

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: ExportAll [SQL: SQLStats_ExportAll] Replaced By: ExportAll [SQL: StatsSQL_ExportAll] (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility. See exportSQLStats(...) Purpose: This method generates two Performance Tool files containing the data from both the '%SYS.PTools.SQLQuery' & '%SYS.PTools.SQLStats' classes & return a $LIST of the output locations Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLStats).ExportAll(...) SQL: CALL %SYS_PTools.SQLStats_ExportAll(...) SELECT %SYS_PTools.SQLStats_ExportAll(...) 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.SQLStats).ExportAll($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.SQLStats_ExportAll('$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","RunTime","SQLStats{C|D|I|S|Z}") Parameters: filePrefix - The path in which to create and store the data from the '%SYS.PTools.SQLQuery' & '%SYS.PTools.SQLStats' classes (e.g. C:\) 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}\SQLQuery.{ext} & {Current-Directory}\StatsSQL.{ext}] WHERE {Current-Directory} = The directory of the Namespace in which this method is invoked (e.g. $ZU(12,"")) delim - The delimiter by which to delimit the data of the exported file [DEFAULT: $C(9) // TAB] exportPlan - 0 - Export the SQL Query Text 1 - Export the SQL Query Plan [DEFAULT: 0] silent - 0 - Display all messages during the running of this method 1 - Don't display any messages during the running of this method 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(method,variable)=value ptInfo(method,"outputFile")=The canonical name of the Export/Reporting file RETURN Value: A $LIST of the output locations of the exported data; Otherwise, return the error status if one occurred $LB(filePrefix_"SQLStats_Qry.txt",filePrefix_"SQLStats_Stats.txt")

Export​

ClassMethod Export(file = "", delim = "", ByRef conds As %RawString, ByRef ptInfo As %RawString) As %String [ Deprecated, SqlName = SQLStats_Export, SqlProc ]

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: Export [SQL: SQLStats_Export] Replaces By: Export [SQL: StatsSQL_Export] (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility. See exportSQLStats(...) Purpose: This method generates a delimited file containing the data from the '%SYS.PTools.SQLStats' class Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLStats).Export(...) SQL: CALL %SYS_PTools.SQLStats_Export(...) SELECT %SYS_PTools.SQLStats_Export(...) 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.SQLStats).Export($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.SQLStats_Export('$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","RunTime","SQLStats{C|D|I|S|Z}") Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.SQLStats 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_StatsSQL.psql] delim - The delimiter by which to delimit the data of the exported file [DEFAULT: $C(9) // TAB] 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

exportSQLStats​

ClassMethod exportSQLStats(file = "", format = "P", silent = 0, rtnName = "", modName = "", ByRef conds As %RawString, ByRef ptInfo As %RawString, bcFlag = "") As %Status [ Deprecated, PublicList = d, SqlName = PT_exportSQLStats, SqlProc ]

Method: exportSQLStats [SQL: PT_exportSQLStats] Replaces: ExportAll [SQL: SQLStats_ExportAll] & Export [SQL: SQLStats_Export] [DEPRECATED] Replaced By: exportStatsSQL [SQL: PT_exportStatsSQL] (%SYS.PTools.StatsSQL) Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: Create a Performance Tool file containing the data from the '%SYS.PTools.SQLStats' 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.SQLStats).exportSQLStats(...) SQL: CALL %SYS_PTools.PT_exportSQLStats(...) SELECT %SYS_PTools.PT_exportSQLStats(...) 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.SQLStats).exportSQLStats($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportSQLStats('$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","RunTime","SQLStats{C|D|I|S|Z}") Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.SQLStats 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_SQLStats_ExportSQLStats_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 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

clearSQLStats​

ClassMethod clearSQLStats(ns As %String = "", rtn As %String = "", ph3 As %Integer = 0, clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %RawString) As %Status [ Deprecated, SqlName = PT_clearSQLStats, SqlProc ]

This query def joins %SYS.PTools.SQLQuery and '%SYS.PTools.SQLStats' to make SQL reporting on the data easier.

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: clearSQLStats [SQL: PT_clearSQLStats] Replaced BY: clearStatsSQL [SQL: PT_clearStatsSQL] (%SYS.PTools.StatsSQL) Status: New Entry Point added to mimic Purge for Backward-Compatibility Purpose: Delete all of the data stored in the '%SYS.PTools.SQLStats' class, based on the specified parameters... Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLStats).clearSQLStats(...) SQL: CALL %SYS_PTools.PT_clearSQLStats(...) SELECT %SYS_PTools.PT_clearSQLStats(...) NOTE: This 'SqlProc' method can be invoked with either the CALL-interface or the SELECT-interface. Both interfaces execute the method: the CALL-interface does not return any resulting value, while the SELECT-interface does. Examples: The following examples shows the use of this method: #1 Delete all of the data stored in the '%SYS.PTools.SQLStats' class in the 'SAMPLES' namespace: set stats=##class(%SYS.PTools.SQLStats).clearSQLStats("SAMPLES") Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}") Parameters: ns - The namespace in which to clear SQL Statistics If none provided, use the current namespaces where SQL Stats collected [DEFAULT: Current Namespace] rtn - The routine for which to clear SQL Statistics If none provided, clear all routines in the given 'ns' [OPTIONAL] ph3 - Placeholder Parameter for future extensibility clearAll - 0 = Perform a normal clear, but leave the 'INFO' rows 1 = Clear everything including the 'INFO' rows [DEFAULT: 0] 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.SQLStats [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","clearSQLStats")=The number of rows deleted via this method [Routine Method] ptInfo("cnt","Purge")=The number of rows deleted via this method [Class Method] RETURN Value: Based on the value of the 'returnType' parameter, return one of the following: 0: Return a %Status code of either $$$OK or $$$ERROR() 1: Return the number of Stats rows deleted from the following class; Otherwise, return an error message if an error occurred: %SYS.PTools.SQLStats

Purge​

ClassMethod Purge(ns As %String = "", rtn As %String = "", ph3 As %Integer = 0, clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %RawString) As %Status [ SqlName = Purge, SqlProc ]

Backward-compatible Entry Point: NOTE: See the clearSQLStats method above for more details.

GetLastSQLStats​

ClassMethod GetLastSQLStats(dumpResults = 0) As %Integer [ Deprecated, ReturnResultsets, SqlName = GetLastSQLStats, SqlProc ]

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: GetLastSQLStats [SQL: GetLastSQLStats] Replaced By: GetLastSQLStats [SQL: StatsSQL_GetLastSQLStats] (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility Purpose: This method return the SQLStats Result Set from the last invoked SQL Statement NOTE: This method makes use of a local variable '%sqlcontext' that gets set when the stats data is saved. This only works if you are running in the same process. Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.StatsSQL).GetLastSQLStats(...) SQL: CALL %SYS_PTools.GetLastSQLStats(...) SELECT %SYS_PTools.GetLastSQLStats(...) 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 To see the SQLStats Result Set from the last invoked SQL Statement, invoke one of the following methods: // First invoke a query with SQLStats turned on, such that the following // array value gets set: %SQLStats("LastRow") set tSC=##class(%SYS.PTools.SQLStats).GetLastSQLStats() do %sqlcontext.DumpResults() OR set tSC=##class(%SYS.PTools.SQLStats).GetLastSQLStats(1) // Automatically invokes DumpResults() OR set tSC=##class(%SQL.Statement).%ExecDirect(,"CALL %SYS_PTools.GetLastSQLStats(1)") %sqlcontext: The variable containing the instantiated object of the class %Library.SQLProcContext when a stored procedure is called. %sqlcontext consists of several properties, including an Error object, the SQLCODE error status, the SQL row count, and an error message. This variable is reset before each execution. Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}") Parameters: dumpResults - 1 = Automatically invoke the 'DumpResults()' if '%sqlcontext' is set to a valid value RETURN Value: The status of this method's execution

QueryText​

ClassMethod QueryText(CN As %String, Rtn As %String, Internal As %Boolean = 0) As %String [ Deprecated ]

Backward-Compatible Query Backward-Compatible Query

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: QueryText [SQL: N/A] Replaced By: getQueryText (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility Purpose: This method retrieves the SQL Query Text given the parameters: 'CN', 'Rtn', & 'Internal' Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLStats).QueryText(...) SQL: N/A Examples: The following examples shows the use of this method: #1 Retrieve the SQL Text for any of the data rows retrieved from invoking the 'exportSQLStats()' method: CALL %SYS_PTools.PT_exportSQLStats() set sql=##class(%SYS.PTools.SQLStats).QueryText("SQLStats0","PToolsSQLStats"_$JOB) Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}") Parameters: CN - The cursor name from which to retrieve the SQL Query Text Rtn - The routine from which to retrieve the SQL Query Text Internal - 0 = Return the External Query Text [DEFAULT] 1 = Return the Internal Query Text Return: Return either the Internal or External SQL Query Text; Otherwise, return the Error Status if an error occurs.

ImportSchema​

ClassMethod ImportSchema(CN As %String, Rtn As %String, Internal As %Boolean = 0) As %String [ Deprecated ]

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: ImportSchema [SQL: N/A] Replaced By: getImportSchema (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility Purpose: This method retrieves the Import Schema given the parameters: 'CN', 'Rtn', & 'Internal' Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLStats).ImportSchema(...) SQL: N/A Examples: The following examples shows the use of this method: #1 Retrieve the SQL Text for any of the data rows retrieved from invoking the 'exportSQLStats()' method: CALL %SYS_PTools.PT_exportSQLStats() set iSchema=##class(%SYS.PTools.SQLStats).ImportSchema("SQLStats0","PToolsSQLStats"_$JOB) Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}") Parameters: CN - The cursor name from which to retrieve the Import Schema Rtn - The routine from which to retrieve the Import Schema Internal - 0 = Return the External Query Text [DEFAULT] 1 = Return the Internal Query Text Return: Return either the Internal or External Import Schema; Otherwise, return the Error Status if an error occurs.

version​

ClassMethod version() As %String [ SqlName = SQLStats_version, SqlProc ]

Provide the current version for the %SYS.PTools.SQLStats class/section of the Performance Tools (PTools) Application

TriggerOnDelete​

ClassMethod TriggerOnDelete(timing, ns, rtn, curs)

Perform the OnDelete Trigger operations for this class...