Number of Seconds it takes to compile the query

What is the name of the cursor for this SQL Statement

hash the SQL so we can match incoming SQL with already stored Stats

ImportSchema is used in generated code to handle sql statements that have unqualified table names.

What NameSpace the SQL was generated in

Store the first 15000 charactors of the SQL Query in this field.

Is the Query an INSERT UPDATE DELETE or SELECT query

What Routine is the SQL generated in

Number of times this query has be executed

Internal unique statement hash used as the ID for the SQL Statement Index

Do we have level 3 stats for this query

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: Export [SQL: SQLQuery_Export] Replaces By: Export [SQL: StatsSQL_Export] (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility See exportSQLQuery(...) Purpose: This method generates a comma delimited file containing the data from the '%SYS.PTools.SQLQuery' class Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLQuery).Export(...) SQL: CALL %SYS_PTools.SQLQuery_Export(...) SELECT %SYS_PTools.SQLQuery_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.SQLQuery).Export($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.SQLQuery_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","SQLQuery{C|D|I|S|Z}") Parameters: file - The path and file in which to create and store the data from the %SYS_PTools.SQLQuery class/table: (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_SQLQuery.psql] delim - The delimiter by which to delimit the data of the exported file [DEFAULT: $C(9) // TAB] exportPlan - 0 - Export the SQL Query Text [DEFAULT] 1 - Export the SQL Query Plan RETURN Value: The output locations of the exported data; Otherwise, return the error status if one occurred

Method: clearSQLQuery [SQL: PT_clearSQLQuery] Replaced BY: N/A Status: New Functionality Purpose: Delete all of the data stored in the '%SYS.PTools.SQLQuery' class, based on the specified parameters... Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLQuery).clearSQLQuery(...) SQL: CALL %SYS_PTools.PT_clearSQLQuery(...) SELECT %SYS_PTools.PT_clearSQLQuery(...) 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.SQLQuery' class in the 'SAMPLES' namespace: set stats=##class(%SYS.PTools.SQLQuery).clearSQLQuery("SAMPLES") Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLQuery{C|D|I|S|Z}") Parameters: ns - The namespace in which to clear SQLQuery data If none provided, use the current namespaces where SQLQuery data collected [DEFAULT: Current Namespace] rtn - The routine for which to clear SQLQuery data If none provided, clear all routines in the given 'ns' [OPTIONAL] ph3 - Placeholder Parameter for future extensibility clearErrs - 0 = Don't delete the 'PTools' Application Errors 1 = Delete all of the 'PTools' Application Errors [DEFAULT: 0] returnType - 0 = Return a %Status code of either $$$OK or $$$ERROR() 1 = Return the number of Stats rows deleted from the following class: %SYS.PTools.SQLQuery [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","clearSQLQuery")=The number of rows deleted via this method [Routine Method] ptInfo("cnt","purgeSQLQuery")=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.SQLQuery

Method: exportSQLQuery [SQL: PT_exportSQLQuery] Replaces: Export [SQL: SQLQuery_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.SQLQuery' 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.SQLQuery).exportSQLQuery(...) SQL: CALL %SYS_PTools.PT_exportSQLQuery(...) SELECT %SYS_PTools.PT_exportSQLQuery(...) 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.SQLQuery).exportSQLQuery($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportSQLQuery('$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.SQLQuery 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_SQLQuery_exportSQLQuery_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 exportPlan - 0 - Export the SQL Query Text [DEFAULT] 1 - Export the SQL Query Plan 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

Entry Point providing new functionality to delete Backward-compatible data: NOTE: See the clearSQLQuery method above for more details.

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