Class Reference
IRIS for UNIX 2024.1.2
 InterSystems: The power behind what matters   
Documentation  Search
  [USER] >  [%SYS] >  [PTools] >  [SQLStats]
Private  Storage   

deprecatedpersistent class %SYS.PTools.SQLStats extends %Persistent, %SYS.PTools.Stats

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

Inventory

Parameters Properties Methods Queries Indices ForeignKeys Triggers
3 22 3 1 1


Summary

Properties
ChildSub CommandsExecuted Counter CursorName
DiskWait ExeName GlobalRefs IPAddress
LinesOfCode MachineName ModuleCount ModuleName
NameSpace Pid RoutineInfo RoutineName
RowCount SQLQueryPointer StartTime StatsGroup
StatsMarker TimeSpent TimeToFirstRow TotalTime
UserName

Methods
%%CLASSNAMELogicalToStorage %%CLASSNAMEStorageToLogical %AddToSaveSet %AddToSyncSet
%BMEBuilt %BuildIndicesAsync %BuildIndicesAsyncResponse %CheckConstraints
%CheckConstraintsForExtent %ClassIsLatestVersion %ClassName %ComposeOid
%ConstructClone %Delete %DeleteExtent %DeleteId
%DispatchClassMethod %DispatchGetModified %DispatchGetProperty %DispatchMethod
%DispatchSetModified %DispatchSetMultidimProperty %DispatchSetProperty %Exists
%ExistsId %Extends %GUID %GUIDSet
%GetLock %GetParameter %GetSwizzleObject %Id
%InsertBatch %IsA %IsModified %IsNull
%KillExtent %KillExtentData %LoadFromMemory %LockExtent
%LockId %New %NormalizeObject %ObjectIsNull
%ObjectModified %Oid %OnBeforeAddToSync %OnDeleteFinally
%OnDetermineClass %OnOpenFinally %OnSaveFinally %Open
%OpenId %OriginalNamespace %PackageName %PhysicalAddress
%PurgeIndices %Reload %RemoveFromSaveSet %ResolveConcurrencyConflict
%RollBack %Save %SaveDirect %SaveIndices
%SerializeObject %SetModified %SortBegin %SortEnd
%SyncObjectIn %SyncTransport %UnlockExtent %UnlockId
%ValidateIndices %ValidateObject %ValidateTable CSVtabHead
CSVtabRow DLMtabHead DLMtabRow Export
ExportAll GetLastSQLStats GetStats GlobalSave
HTMLtabHead HTMLtabRow ImportSchema Init
Purge QueryText Report SetSQLStats
SetSQLStatsJob Start Stop TXTtabHead
TXTtabRow TriggerOnDelete XMLcolAuto XMLcolWidth
XMLrowHeader XMLsheet XMLsort XMLtabRow
XMLtable clearPToolsError clearSQLStats clearStats
clearStatsALL clearStatsAllNS clearStatsSQLALL clearStatsSQLIndexALL
clearStatsSQLIndexNEW clearStatsSQLIndexOLD clearStatsSQLNEW clearStatsSQLOLD
condsMatch createAndOpenFile createOrderByList exportSQLStats
exportStats fileBody fileFooter fileHeader
getOutputFile getPToolsError newSQLStats openFileForRead
saveSQLStats startSQLStats stopSQLStats version


Properties

• property ChildSub as %Integer [ InitialExpression = $$$bumpSYSPToolsSQLStatsChildSub ];
• property Counter as %Integer;
This is the number of times the query has been run since the last compile.
• relationship SQLQueryPointer as %SYS.PTools.SQLQuery [ Inverse = SQLStatsPointer,Cardinality = parent ];

Methods

• deprecatedclassmethod Export(file="", delim="", ByRef conds As %RawString, ByRef ptInfo As %RawString) as %String [ SQLProc = SQLStats_Export ]

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

• deprecatedclassmethod ExportAll(filePrefix="", delim="", exportPlan=0, silent=0, rtnName="", modName="", ByRef conds As %RawString, ByRef ptInfo As %RawString) as %String [ SQLProc = SQLStats_ExportAll ]

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

• deprecatedclassmethod GetLastSQLStats(dumpResults=0) as %Integer [ SQLProc = GetLastSQLStats ]

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

• deprecatedclassmethod GetStats(ByRef SQLText As %String = 0, mod As %String, showStats As %Integer = 0) as %String

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

• deprecatedclassmethod GlobalSave(ns, rou, cur, mod, rows)

-- 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}")

• deprecatedclassmethod ImportSchema(CN As %String, Rtn As %String, Internal As %Boolean = 0) as %String

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

• deprecatedclassmethod Init(NS As %String, Rou As %String, Cur As %String, ByRef Parms As %RawString)

-- 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}")

• 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 [ SQLProc = Purge ]
Backward-compatible Entry Point: NOTE: See the clearSQLStats method above for more details.
• deprecatedclassmethod QueryText(CN As %String, Rtn As %String, Internal As %Boolean = 0) as %String

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

• deprecatedclassmethod Report(NS As %String, Rou As %String, Cur As %String, RowCount As %Integer = 0) as %String

-- 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}")

• deprecatedclassmethod SetSQLStats(actionFlag As %Integer = 0) as %Integer [ SQLProc = SetSQLStats ]

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

• deprecatedclassmethod SetSQLStatsJob(actionFlag As %Integer = 0) as %Integer [ SQLProc = SetSQLStatsJob ]

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

• deprecatedclassmethod Start(NS As %String, Rou As %String, Cur As %String, ModString As %String)

-- 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}") -------------------------------------------------------------------------------

• deprecatedclassmethod Stop(NS As %String, Rou As %String, Cur As %String, ModString As %String)

-- 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}")

• classmethod TriggerOnDelete(timing, ns, rtn, curs)
Perform the OnDelete Trigger operations for this class...
• deprecatedclassmethod 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 [ SQLProc = PT_clearSQLStats ]

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

• deprecatedclassmethod exportSQLStats(file="", format="P", silent=0, rtnName="", modName="", ByRef conds As %RawString, ByRef ptInfo As %RawString, bcFlag="") as %Status [ SQLProc = PT_exportSQLStats ]

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

• classmethod newSQLStats(SQLText, SQLType, ns, rtn, curs, compTime=0, SQLIndexHash="")
Stub: For the 'newSQLStats()' method in the '%SYS.PTools.MAC' routine
• classmethod saveSQLStats(ns, rtn, curs, mod, rowCount, SQLIndexHash)
Stub: For the 'saveSQLStats()' method in the '%SYS.PTools.MAC' routine
• classmethod startSQLStats(ns, rtn, curs, params, SQLIndexHash, flag, collect)
Stub: For the 'startSQLStats()' method in the '%SYS.PTools.MAC' routine
• classmethod stopSQLStats(ns, rtn, curs, rowCount=0, SQLIndexHash, flag, collect)
Stub: For the 'stopSQLStats()' method in the '%SYS.PTools.MAC' routine
• classmethod version() as %String [ SQLProc = SQLStats_version ]
Provide the current version for the %SYS.PTools.SQLStats class/section of the Performance Tools (PTools) Application

Queries

• query SQLStatsView()
SQL Query as view "SQLStatsView":
SELECT S.ID, Q.NameSpace, Q.RoutineName, Q.CursorName, Q.CompileTime, Q.ImportSchema,
S.StartTime, S.Counter, S.ModuleName, S.ModuleCount,
S.GlobalRefs, S.LinesOfCode, S.CommandsExecuted, S.DiskWait, S.TimeSpent, S.TimeToFirstRow, S.RowCount,
S.UserName, S.ExeName, S.MachineName, S.IPAddress, S.Pid, Q.QueryType, Q.QueryText, Q.Details, Q.RunCount
FROM %SYS_PTools.SQLQuery Q LEFT OUTER JOIN %SYS_PTools.SQLStats S ON Q.ID = S.SQLQueryPointer
This query def joins %SYS.PTools.SQLQuery and '%SYS.PTools.SQLStats' to make SQL reporting on the data easier.
• query ViewStats(NameSpace As %String)
Selects RoutineName As %String, QueryText As %String, RunCount As %Integer, AvgRows As %Integer, AvgGlobalRefs As %Integer, AvgDiskWait As %Integer, AvgCommands As %Integer, AvgTime As %Integer, Details As %Integer, CursorName As %Integer, RoutineCursor As %Integer, ImportSchema As %String
SQL Query :
SELECT Main.RoutineName, Main.QueryText, RunCount,
{fn round(avg(Main.RowCount),2)} as AvgRows,
{fn round(avg(Main.GlobalRefs),2)} as AvgGlorefs,
{fn round(avg(Main.DiskWait),2)} as AvgDiskWait,
{fn round(avg(Main.LinesOfCode),2)} as AvgCommands,
{fn round(avg(Main.TimeSpent),5)} as AvgTime,
Details, CursorName,RoutineName||'^'||CursorName, ImportSchema

FROM %SYS_PTools.SQLStatsView Main
WHERE Main.Namespace= :NameSpace
AND RoutineName <> 'source lines'
AND ModuleName = 'Main'
GROUP BY Main.RoutineName, Main.CursorName
Backward-Compatible Query
• query ViewStatsDetails(CursorName As %String, RoutineName As %String)
Selects QueryText As %String, RunCount As %Integer, AvgRows As %Integer, AvgGlobalRefs As %Integer, AvgDiskWait As %Integer, AvgCommands As %Integer, AvgTime As %Integer, ModuleName As %String, AvgModCount As %Integer
SQL Query :
SELECT QueryText, RunCount,
{fn round(avg(RowCount),2)} as AvgRows,
{fn round(avg(GlobalRefs),2)} as AvgGlorefs,
{fn round(avg(DiskWait),2)} as AvgDiskWait,
{fn round(avg(LinesOfCode),2)} as AvgCommands,
{fn round(avg(TimeSpent),5)} as AvgTime,
ModuleName,
{fn round(avg(ModuleCount),2)} as AvgModCount
FROM %SYS_PTools.SQLStatsView
WHERE CursorName= :CursorName
AND RoutineName = :RoutineName
GROUP BY CursorName, ModuleName
Backward-Compatible Query

Indices

•index (MasterIndex on Counter,ChildSub) [IdKey,Unique];

Triggers

•trigger OnDelete (AFTER event DELETE)

Copyright (c) 2025 by InterSystems Corporation. Cambridge, Massachusetts, U.S.A. All rights reserved. Confidential property of InterSystems Corporation.