Inheritance diagram for SQLStats:

[legend]

Collaboration diagram for SQLStats:

[legend]

Public Attributes
	ChildSub
	More...

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

	SQLQueryPointer
	More...

Public Attributes inherited from Stats
	CommandsExecuted
	The number of ObjectScript commands that were executed in this Module for the given query. More...

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

	CursorName
	Placeholder (*) indicating that this is not a Cursor Name for an SQL Statement [Used in SYS.PTools.StatsSQL]. More...

	DiskWait
	Number of Milliseconds spent waiting for Disk reads in this Module for the given query. More...

	ExeName
	Name of the executable that ran this query. More...

	GlobalRefs
	The number globals that were referenced in this Module for the given query. More...

	IPAddress
	IP Address of the machine that ran this query. More...

	LinesOfCode

	MachineName
	Name of the machine that ran this query. More...

	ModuleCount
	Number of times we entered this module for the run of the query. More...

	ModuleName
	This is the name of the method within a given routine to be analyzed [DEFAULT: 1]. More...

	NameSpace
	The NameSpace in which the Stats collection is being invoked. More...

	Pid
	OS level Process ID. More...

	RoutineInfo
	<PROP> More...

	RoutineName
	The name of the routine in which the Stats collection is being invoked. More...

	RowCount
	How many rows did the query return for this run. More...

	StartTime
	TimeStamp of when the query was compiled/run. More...

	StatsGroup
	<PROP> More...

	StatsMarker
	<PROP> More...

	TimeSpent
	How much time was spent in this Module for the given query. More...

	TimeToFirstRow
	How much time was spent finding the first row. More...

	TotalTime
	<BC>Maintained for Backward-Compatibility</BC>. More...

	UserName
	InterSystems IRIS UserName of the person who ran this query. More...

_.Library.String	Export (file, delim, _.Library.RawString conds, _.Library.RawString ptInfo)

_.Library.String	ExportAll (filePrefix, delim, exportPlan, silent, rtnName, modName, _.Library.RawString conds, _.Library.RawString ptInfo)

_.Library.Integer	GetLastSQLStats (dumpResults)

_.Library.String	GetStats (_.Library.String SQLText, _.Library.String mod, _.Library.Integer showStats)

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

_.Library.String	ImportSchema (_.Library.String CN, _.Library.String Rtn, _.Library.Boolean Internal)

	Init (_.Library.String NS, _.Library.String Rou, _.Library.String Cur, _.Library.RawString Parms)

_.Library.Status	Purge (_.Library.String ns, _.Library.String rtn, _.Library.Integer ph3, _.Library.Integer clearErrs, _.Library.Integer returnType, _.Library.RawString ptInfo)
	Backward-compatible Entry Point: More...

_.Library.String	QueryText (_.Library.String CN, _.Library.String Rtn, _.Library.Boolean Internal)

_.Library.String	Report (_.Library.String NS, _.Library.String Rou, _.Library.String Cur, _.Library.Integer RowCount)

_.Library.Integer	SetSQLStats (_.Library.Integer actionFlag)

_.Library.Integer	SetSQLStatsJob (_.Library.Integer actionFlag)

	Start (_.Library.String NS, _.Library.String Rou, _.Library.String Cur, _.Library.String ModString)

	Stop (_.Library.String NS, _.Library.String Rou, _.Library.String Cur, _.Library.String ModString)

	TriggerOnDelete (timing, ns, rtn, curs)
	Perform the OnDelete Trigger operations for this class...

_.Library.Status	clearSQLStats (_.Library.String ns, _.Library.String rtn, _.Library.Integer ph3, _.Library.Integer clearErrs, _.Library.Integer returnType, _.Library.RawString ptInfo)

_.Library.Status	exportSQLStats (file, format, silent, rtnName, modName, _.Library.RawString conds, _.Library.RawString ptInfo, bcFlag)

	newSQLStats (SQLText, SQLType, ns, rtn, curs, compTime, SQLIndexHash)
	<STUB>Stub: For the 'newSQLStats()' method in the 'SYS.PTools.MAC' routine</STUB>

	saveSQLStats (ns, rtn, curs, mod, rowCount, SQLIndexHash)
	<STUB>Stub: For the 'saveSQLStats()' method in the 'SYS.PTools.MAC' routine</STUB>

	startSQLStats (ns, rtn, curs, params, SQLIndexHash, flag, collect)
	<STUB>Stub: For the 'startSQLStats()' method in the 'SYS.PTools.MAC' routine</STUB>

	stopSQLStats (ns, rtn, curs, rowCount, SQLIndexHash, flag, collect)
	<STUB>Stub: For the 'stopSQLStats()' method in the 'SYS.PTools.MAC' routine</STUB>

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

def	__init__ (self)
	* DEPRECATED via MRP1300 as of 05/30/2022 * More...

Additional Inherited Members
Public Member Functions inherited from Stats
Static Public Member Functions inherited from Stats
_.Library.String	Report (file, conds, ptInfo)

	Start (_.Library.String NS, _.Library.String Rou, _.Library.String Mod)

	Stop (_.Library.String NS, _.Library.String Rou, _.Library.String Mod)

	TriggerOnDelete (timing, ns, rtn, curs, sgrp, mod)
	Perform the OnDelete Trigger operations for this class...

_.Library.Integer	clearPToolsError (_.Library.String errRowID)

_.Library.Status	clearStats (_.Library.String ns, _.Library.String rtn, _.Library.Integer ph3, _.Library.Integer clearErrs, _.Library.Integer returnType, _.Library.RawString ptInfo)

_.Library.Status	clearStatsALL (_.Library.String ns, _.Library.String rtn, _.Library.Integer ph3, _.Library.Integer clearErrs, _.Library.Integer silent, _.Library.Integer returnType, _.Library.RawString ptInfo)

_.Library.Status	clearStatsAllNS (_.Library.String rtn, _.Library.Integer ph2, _.Library.String ph3, _.Library.Integer clearErrs, _.Library.Integer returnType, _.Library.RawString ptInfo)

_.Library.Status	clearStatsSQLALL (_.Library.String ns, _.Library.String rtn, _.Library.Integer ph3, _.Library.Integer clearAll, _.Library.Integer clearSQLQuery, _.Library.Integer clearBench, _.Library.Integer clearSQLIndex, _.Library.Integer clearErrs, _.Library.Integer purgeCQ, _.Library.Integer silent, _.Library.Integer returnType, _.Library.RawString ptInfo)

_.Library.Status	clearStatsSQLIndexALL (_.Library.String ns, _.Library.Integer clearErrs, _.Library.Integer silent, _.Library.Integer returnType, _.Library.RawString ptInfo)

_.Library.Status	clearStatsSQLIndexNEW (_.Library.String ns, _.Library.Integer clearErrs, _.Library.Integer silent, _.Library.Integer returnType, _.Library.RawString ptInfo)

_.Library.Status	clearStatsSQLIndexOLD (_.Library.String ns, _.Library.Integer clearErrs, _.Library.Integer silent, _.Library.Integer returnType, _.Library.RawString ptInfo)

_.Library.Status	clearStatsSQLNEW (_.Library.String ns, _.Library.String rtn, _.Library.Integer ph3, _.Library.Integer clearAll, _.Library.Integer clearSQLIndex, _.Library.Integer clearErrs, _.Library.Integer purgeCQ, _.Library.Integer silent, _.Library.Integer returnType, _.Library.RawString ptInfo)

_.Library.Status	clearStatsSQLOLD (_.Library.String ns, _.Library.String rtn, _.Library.Integer ph3, _.Library.Integer clearSQLQuery, _.Library.Integer clearBench, _.Library.Integer clearSQLIndex, _.Library.Integer clearErrs, _.Library.Integer purgeCQ, _.Library.Integer silent, _.Library.Integer returnType, _.Library.RawString ptInfo)

_.Library.Status	exportStats (file, format, silent, statsType, rtnName, modName, _.Library.RawString conds, _.Library.RawString ptInfo, bcFlag)

_.Library.Integer	getPToolsError (_.Library.String errRowID, _.Library.RawString PToolsErr)

Static Public Member Functions inherited from FileCreation
	CSVtabHead (metadata, delim)
	CSV Table Header.

	CSVtabRow (metadata, delim)
	CSV Table Data Row.

	DLMtabHead (metadata, delim)
	DLM Table Header.

	DLMtabRow (metadata, delim)
	DLM Table Data Row.

	HTMLtabHead (aTag, title, metadata)
	HTML Table Header.

	HTMLtabRow (metadata)
	HTML Table Row.

	TXTtabHead (metadata, delim)
	Text Table Header.

	TXTtabRow (metadata, delim)
	Text Table Data Row.

	XMLcolAuto ()
	XML Column AutoFit Width Element.

	XMLcolWidth (width)
	XML Column Width Element.

	XMLrowHeader (metadata)
	XML Header Row Element.

	XMLsheet (name)
	XML Worksheet Element.

	XMLsort (sortColList, sortOrderList)
	XML Sorting Element.

	XMLtabRow (metadata)
	XML Table Data Row Element.

	XMLtable (colCnt, rowCnt)
	XML Table Element.

_.Library.Status	condsMatch (conds, d, condValExp)
	Check Conditional Matches.

_.Library.Status	createAndOpenFile (file, timeout)
	Create & Open File.

_.Library.String	createOrderByList (orderBy, d)
	Create a valid SQL <order-by-list>

	fileBody (file, format, metadata)
	File Body.

_.Library.Status	fileFooter (file, format, footerMsg, metadata, elapseTime, rowCnt)
	File Footer.

_.Library.Status	fileHeader (file, format, title, class, method, version, metadata, bcFlag)
	File Header.

_.Library.String	getOutputFile (file, className, methodName, format, hDate, tSC)
	Get the 'outputFile'.

_.Library.Status	openFileForRead (file, timeout)
	Open File For Read.

Detailed Description

– 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 <class>SYS.PTools.StatsSQL</class> 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: <class>SYS.PTools.StatsSQL</class> 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 <class>SYSTEM.SQL</class> 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 <class>SYS.PTools.SQLQuery</class> and 'SYS.PTools.SQLStats' classes. The <class>SYS.PTools.SQLQuery</class> 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, <query>SQLStatsView</query>, 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

Constructor & Destructor Documentation

◆ init()

def __init__ ( self )

*** DEPRECATED via MRP1300 as of 05/30/2022 ***

The 'LinesOfCode' property has been deprecated and replaced by the 'CommandsExecuted' property, but is left here for backwards compatibility and returns the 'CommandsExecuted' value. At some point in the future this property will be removed from the product, so users should not rely on its availability going forward and should use the 'CommandsExecuted' property instead.

Reimplemented from Stats.

Member Function Documentation

◆ Export()

_.Library.String Export	(		file,
			delim,
		_.Library.RawString	conds,
		_.Library.RawString	ptInfo
	)

static

<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: Export [SQL: SQLStats_Export] Replaces By: Export SQL: StatsSQL_Export Status: <BC>Maintained for Backward-Compatibility.</BC> 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=<full condition> OR conds(0)=pos Count (WHERE pos = {1...n}) conds(pos)=$LIST() Pieces: 1) [<logical operator>] (Assumed Default: &&) 2) [{Heading}] (Omit for override cond) 3) <simple condition> | <complex condition>

WHERE: <full condition> := <full condition expression> <logical operator> := {&& | ||} (&& = AND | || = OR) <simple condition>@* := <oper>

@ = Contains no references to {Heading}

= Contains no references to {*}

EXAMPLE: <full condition> conds="("",INFO,MAIN,""[("",""_{Module}_"",""))||({GlobalRefs}>20)" <simple condition> conds(0)=3 conds(1)=$LB(,"Module","=""INFO""") conds(2)=$LB("||","Module","=""MAIN""") conds(3)=$LB("||","GlobalRefs",">20") <complex condition> 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 <override condition>s, can be included as <complex condition>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

◆ ExportAll()

_.Library.String ExportAll	(		filePrefix,
			delim,
			exportPlan,
			silent,
			rtnName,
			modName,
		_.Library.RawString	conds,
		_.Library.RawString	ptInfo
	)

static

<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: ExportAll [SQL: SQLStats_ExportAll] Replaced By: ExportAll SQL: StatsSQL_ExportAll Status: <BC>Maintained for Backward-Compatibility.</BC> 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=<full condition> OR conds(0)=pos Count (WHERE pos = {1...n}) conds(pos)=$LIST() Pieces: 1) [<logical operator>] (Assumed Default: &&) 2) [{Heading}] (Omit for override cond) 3) <simple condition> | <complex condition>

WHERE: <full condition> := <full condition expression> <logical operator> := {&& | ||} (&& = AND | || = OR) <simple condition>@* := <oper>

@ = Contains no references to {Heading}

= Contains no references to {*}

EXAMPLE: <full condition> conds="("",INFO,MAIN,""[("",""_{Module}_"",""))||({GlobalRefs}>20)" <simple condition> conds(0)=3 conds(1)=$LB(,"Module","=""INFO""") conds(2)=$LB("||","Module","=""MAIN""") conds(3)=$LB("||","GlobalRefs",">20") <complex condition> 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 <override condition>s, can be included as <complex condition>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")

◆ GetLastSQLStats()

_.Library.Integer GetLastSQLStats ( dumpResults )

static

<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: GetLastSQLStats [SQL: GetLastSQLStats] Replaced By: GetLastSQLStats SQL: StatsSQL_GetLastSQLStats Status: <BC>Maintained for Backward-Compatibility</BC> 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

◆ GetStats()

_.Library.String GetStats	(	_.Library.String	SQLText,
		_.Library.String	mod,
		_.Library.Integer	showStats
	)

static

<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: GetStats [SQL: N/A] Replaced By: GetStats (<class>SYS.PTools.StatsSQL</class>) Status: <BC>Maintained for Backward-Compatibility</BC> 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})

◆ GlobalSave()

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

static

<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: GlobalSave [SQL: N/A] Replaced By: saveSQLStats^SYS.PTools() Status: <DEP>[DEPRECATED]</DEP> Purpose: Invoked by the Report() method to store all of the runtime data in the 'SYS.PTools.SQLStats' OR <class>SYS.PTools.StatsSQL</class> table

Data Storage: ^sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}")

◆ ImportSchema()

_.Library.String ImportSchema	(	_.Library.String	CN,
		_.Library.String	Rtn,
		_.Library.Boolean	Internal
	)

static

<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: ImportSchema [SQL: N/A] Replaced By: getImportSchema (<class>SYS.PTools.StatsSQL</class>) Status: <BC>Maintained for Backward-Compatibility</BC> 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.

◆ Init()

Init	(	_.Library.String	NS,
		_.Library.String	Rou,
		_.Library.String	Cur,
		_.Library.RawString	Parms
	)

static

<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP>

Method: Init [SQL: N/A] Replaced By: startSQLStats^SYS.PTools() Status: <DEP>[DEPRECATED]</DEP> 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}")

◆ Purge()

_.Library.Status Purge	(	_.Library.String	ns,
		_.Library.String	rtn,
		_.Library.Integer	ph3,
		_.Library.Integer	clearErrs,
		_.Library.Integer	returnType,
		_.Library.RawString	ptInfo
	)

static

Backward-compatible Entry Point:

NOTE: See the <method>clearSQLStats</method> method above for more details.

◆ QueryText()

_.Library.String QueryText	(	_.Library.String	CN,
		_.Library.String	Rtn,
		_.Library.Boolean	Internal
	)

static

<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: QueryText [SQL: N/A] Replaced By: getQueryText (<class>SYS.PTools.StatsSQL</class>) Status: <BC>Maintained for Backward-Compatibility</BC> 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.

◆ Report()

_.Library.String Report	(	_.Library.String	NS,
		_.Library.String	Rou,
		_.Library.String	Cur,
		_.Library.Integer	RowCount
	)

static

<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: Report [SQL: N/A] Replaced By: stopSQLStats^SYS.PTools() Status: <DEP>[DEPRECATED]</DEP> Purpose: Invoked by the CLOSE Cursor to tidy up statistics collection OR <class>SYS.PTools.StatsSQL</class> table

Data Storage: ^sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}")

◆ SetSQLStats()

_.Library.Integer SetSQLStats ( _.Library.Integer actionFlag )

static

<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: SetSQLStats [SQL: SetSQLStats] Replaced By: SetSQLStats SQL: StatsSQL_SetSQLStats Status: <BC>New functionality added for Backward-Compatibility</BC> 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()

_.Library.Integer SetSQLStatsJob ( _.Library.Integer actionFlag )

static

<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: SetSQLStatsJob [SQL: SetSQLStatsJob] Replaced By: SetSQLStatsJob SQL: StatsSQL_SetSQLStatsJob SetSQLStatsFlagJob SQL: StatsSQL_SetSQLStatsFlagJob Status: <BC>Maintained for Backward-Compatibility</BC> 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

◆ Start()

Start	(	_.Library.String	NS,
		_.Library.String	Rou,
		_.Library.String	Cur,
		_.Library.String	ModString
	)

static

<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: Start [SQL: N/A] Replaced By: statsStart^SYS.PTools() Status: <DEP>[DEPRECATED]</DEP> Purpose: Invoked every time you enter a module to collect statistics

Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}")

◆ Stop()

Stop	(	_.Library.String	NS,
		_.Library.String	Rou,
		_.Library.String	Cur,
		_.Library.String	ModString
	)

static

<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: Stop [SQL: N/A] Replaced By: statsStop^SYS.PTools() Status: <DEP>[DEPRECATED]</DEP> Purpose: Invoked every time you exit a module to collect statistics

Data Storage: ^sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}")

◆ clearSQLStats()

_.Library.Status clearSQLStats	(	_.Library.String	ns,
		_.Library.String	rtn,
		_.Library.Integer	ph3,
		_.Library.Integer	clearErrs,
		_.Library.Integer	returnType,
		_.Library.RawString	ptInfo
	)

static

<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: clearSQLStats [SQL: PT_clearSQLStats] Replaced BY: clearStatsSQL SQL: PT_clearStatsSQL Status: <BC>New Entry Point added to mimic <method>Purge</method> for Backward-Compatibility</BC> 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

◆ exportSQLStats()

_.Library.Status exportSQLStats	(		file,
			format,
			silent,
			rtnName,
			modName,
		_.Library.RawString	conds,
		_.Library.RawString	ptInfo,
			bcFlag
	)

static

Method: exportSQLStats [SQL: PT_exportSQLStats] Replaces: ExportAll [SQL: SQLStats_ExportAll] & Export [SQL: SQLStats_Export] <DEP>[DEPRECATED]</DEP> Replaced By: exportStatsSQL SQL: PT_exportStatsSQL 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=<data-format>[:$LB(<delim>,<csv-header>,<csv-footer>)] Where the constituent elements have the following values: <data-format> - 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 <delim> element is required or defaulted to $C(9) (TAB) Optional Elements: <delim> - User-defined delimiter [DEFAULT: $C(9) (TAB)] NOTE: This is generally used for <data-format>="Z" <csv-header> - .csv file header for <data-format>="D":
0 = Don't add non-standard information header to file [DEFAULT]
1 = Add non-standard information header to file <csv-footer> - .csv file footer for <data-format>="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=<full condition> OR conds(0)=pos Count (WHERE pos = {1...n}) conds(pos)=$LIST() Pieces: 1) [<logical operator>] (Assumed Default: &&) 2) [{Heading}] (Omit for override cond) 3) <simple condition> | <complex condition>

WHERE: <full condition> := <full condition expression> <logical operator> := {&& | ||} (&& = AND | || = OR) <simple condition>@* := <oper>

@ = Contains no references to {Heading}

= Contains no references to {*}

EXAMPLE: <full condition> conds="("",INFO,MAIN,""[("",""_{Module}_"",""))||({GlobalRefs}>20)" <simple condition> conds(0)=3 conds(1)=$LB(,"Module","=""INFO""") conds(2)=$LB("||","Module","=""MAIN""") conds(3)=$LB("||","GlobalRefs",">20") <complex condition> 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 <override condition>s, can be included as <complex condition>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

Member Data Documentation

◆ ChildSub

ChildSub

◆ Counter

Counter

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

◆ SQLQueryPointer

SQLQueryPointer

Public Attributes

Additional Inherited Members

Detailed Description

Constructor & Destructor Documentation

◆ __init__()

Member Function Documentation

◆ Export()

◆ ExportAll()

◆ GetLastSQLStats()

◆ GetStats()

class(%CSP.UI.Portal.SQL.QButtons.RuntimeStats).PrepareShowPlan(...)

◆ GlobalSave()

◆ ImportSchema()

◆ Init()

◆ Purge()

◆ QueryText()

◆ Report()

◆ SetSQLStats()

◆ SetSQLStatsJob()

◆ Start()

Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}")

◆ Stop()

◆ clearSQLStats()

◆ exportSQLStats()

Member Data Documentation

◆ ChildSub

◆ Counter

◆ SQLQueryPointer

◆ init()