Inheritance diagram for UtilSQLStatements:

[legend]

Collaboration diagram for UtilSQLStatements:

[legend]

Static Public Member Functions
_.Library.Status	clearSQLStatements (_.Library.String ns, _.Library.Integer clearErrs, _.Library.Integer returnType, _.Library.RawString ptInfo)

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

Public Attributes
	ImportPackage
	Comma delimited list of Package names to use compiling SQL Statements. More...

	MethodName
	<PROP> More...

	Name
	<PROP> More...

	SQLTextExt
	External SQL Statement as a single string. More...

	SQLTextRaw
	Raw SQL Statement Text stored as a $LIST string. More...

	Type
	The {Type} is the location within InterSystems IRIS from where the SQL Statement was extracted: More...

Detailed Description

Class: SYS.PTools.UtilSQLStatements Replaces: <class>SYS.PTools.SQLUtilities</class> [DEPRECATED] Purpose:
This class is used to store the SQL Statement details collected from the following Locations within InterSystems IRIS for Index Analysis, Performance Optimizations, and Additional Processing:

cached queries
Class Methods
Class Queries
MAC Routines

The following Methods and Queries, defined in the abstract interface class <class>SYS.PTools.UtilSQLAnalysis</class>, are used to collect, analyze, and optimize the SQL Statements in this class:

Methods Queries

getAllSQLStmts
getSQLStmts - getSQLStmts
getAllCachedQrySQLStmts
getCachedQrySQLStmtsByDays
getCachedQrySQLStmtsByClass
getAllClassMethSQLStmts
getClassMethSQLStmtsByClass
getAllClassQrySQLStmts
getClassQrySQLStmtsByClass
getAllRtnQrySQLStmts
getRtnQrySQLStmtsByRtn
getAllIndices
indexUsage - indexUsage
tableScans - tableScans
tempIndices - tempIndices
joinIndices - joinIndices
outlierIndices - outlierIndices
exportSQLUtilStmts
exportSQLUtilStmtRslts
exportSQLUtilities
clearSQLStatements
clearSQLAnalysisDB
- countSQLStmtTypes
- UtilSQLStmtsAndAnalysisDBView

Data Storage: ^sqlcq("SAMPLES","PTools","util","SQLStmts","{C|D|I|S}") Error Storage: ^sqlcq($NAMESPACE,"PTools","Error"[...])=$LIST Info

Member Function Documentation

◆ clearSQLStatements()

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

static

Method: clearSQLStatements Replaces: ClearStatements (<class>SYS.PTools.SQLUtilities</class>) [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: Delete all of the data stored in the 'SYS.PTools.UtilSQLStatements' class, based on the specified parameters...

Invocation: This method can be invoked in the following ways: ObjectScript: ##class(SYS.PTools.UtilSQLStatements).clearSQLStatements(...) SQL: CALL SYS_PTools.UtilSQLStatements_clearSQLStatements(...) SELECT SYS_PTools.UtilSQLStatements_clearSQLStatements(...)

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.UtilSQLStatements' class in the current namespace:

set status=##class(SYS.PTools.UtilSQLStatements).clearSQLStatements()

Data Storage: ^sqlcq($NAMESPACE,"PTools","util","SQLStmts")

Parameters: ns - The namespace in which to clear SQL Index/Analysis Statstics [DEFAULT: Current Namespace] 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.UtilSQLStatements [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","clearSQLStatements")=The number of rows deleted via this method

RETURN Value: Based on the value of the 'returnType' parameter, return one of the following: 0: Return a Status code of either $$$OK or $$$ERROR()

1: Return the number of Stats rows deleted from the following class; Otherwise, return an error message if an error occurred: SYS.PTools.UtilSQLStatements

Member Data Documentation

◆ ImportPackage

ImportPackage

Comma delimited list of Package names to use compiling SQL Statements.

◆ MethodName

MethodName

<PROP>

If {Type} is 'Cached Query' OR 'Class Method', then this is the Name of the Method/ClassMethod If {Type} is 'Class Query', then this is the Name of the Query If {Type} is 'Routine', then this is the Name of the Procedure/Function This Name is the module in which the SQL Query Text {SQLTextRaw} can be found </PROP>

◆ Name

Name

<PROP>

Based on the {Type}, the name of the object from where the SQL Statement was extracted: 'Cached Query' The class name which represents the Cached Queries 'Class Method' The name of the class that contains the 'method' name with SQL 'Class Query' The name of the class that contains the 'method' name with SQL 'Routine' The name of the routine that contains Embedded SQL </PROP>

◆ SQLTextExt

SQLTextExt

External SQL Statement as a single string.

This is the exact text of the SQL Statement, including SQL Comments, parametes, etc.

◆ SQLTextRaw

SQLTextRaw

Raw SQL Statement Text stored as a $LIST string.

This is the exact text of the SQL Statement, including SQL Comments, parametes, etc.

◆ Type

Type

The {Type} is the location within InterSystems IRIS from where the SQL Statement was extracted:

'Cached Query' | 'Class Method' | 'Class Query' | 'Routine'

Static Public Member Functions

Public Attributes