-- 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.SQLQuery
Replaced By: %SYS.PTools.StatsSQL
Purpose:
This is the parent class of %SYS.PTools.SQLStats.
This class is used to store the the following general info of a query from
compile time:
QueryText, QueryType, CursorName, RoutineName, & NameSpace.
All of the run time data is stored in the %SYS.PTools.SQLStats class.
Error information is stored in the following error global:
^%sqlcq(NAMESPACE,"PTools","Error",[...])=$LIST Info
Projected as the stored procedure: SQLQuery_Export
-- 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
Projected as the stored procedure: PT_clearSQLQuery
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
classmethod exportSQLQuery(file="", format="P", silent=0, exportPlan=0, ByRef conds As %RawString, ByRef ptInfo As %RawString, bcFlag="") as %Status [ SQLProc = PT_exportSQLQuery ]
Projected as the stored procedure: PT_exportSQLQuery
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
