------------------------------------------------------------------------------
-- 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.UtilSQLAnalysisDB class.
-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --
------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Class: %SYS.PTools.SQLUtilResults
Replaced By: %SYS.PTools.UtilSQLAnalysisDB
Purpose:
This class is used by the following methods to store the SQL Analysis
details collected against the SQL Statements stored in the
%SYS.PTools.SQLUtilities class:
- IndexUsage()
- TableScans()
- TempIndices()
- JoinIndices()
Data Storage: ^%sqlcq("SAMPLES","PTools","Utils","Results","{C|D|I|S}")
Error Storage: ^%sqlcq($NAMESPACE,"PTools","Error"[...])=$LIST Info
------------------------------------------------------------------------------
$LISTBUILD of the Properties that will be the data node of the {IndexName}
The extra data is stored as part of the index to avoid reading from the Data Map
Set by the follow methods: tableScans(), tempIndices(), joinIndices(), outlierIndices()
The Number of Rows in the Table Based on TuneTable Data or set by the user
Set by joinIndices() method to one of the following values:
Flag has 4 values:
0 - No index to support the JOIN
1 - There is an index to support the join but it is not an exact match, the first index field is not part of the join.
2 - There is an index to support the join and all of its fields, but it is not an exact match, the first index field is part of the join.
3 - There is an index to support the join and all of its fields, but it is not an exact match, the first index field is start of all the join fields.
4 - Index fully supports the join, this is not included in the table as there is nothing to improve.
Set by the follow methods: tableScans(), tempIndices(), joinIndices(), outlierIndices()
One of the following Map Types: { "master map " | "extent bitmap " | "bitmap index " | "index map " }
property OptionName as %String (VALUELIST = ",IU,TS,TI,JI,OI,") [ Required ];
1st piece of the IDKey
The value for this property indicates the SQL Statement Analysis Method producinc the result row:
IndexUsage() => 'IU'
TableScans() => 'TS'
TempIndices() => 'TI'
JoinIndices() => 'JI'
OutlierIndices() => 'OI'
Set in the following methods: tableScans(), tempIndices(), joinIndices(), outlierIndices()
Reference to the %SYS.PTools.SQLUtilities class which is a pointer back to the SQL Statement
classmethod exportSQLUtilStmtRslts(file As %String = "", format As %String = "", silent As %Integer = 0, option As %String = "", ByRef conds As %ArrayOfObjects, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = PT_exportSQLUtilStmtRslts ]
-------------------------------------------------------------------------------
Method: exportSQLUtilStmtRslts [SQL: PT_exportSQLUtilStmtRslts]
Replaced By: exportUtilSQLAnalysisDB [SQL: PT_exportUtilSQLAnalysisDB] (%SYS.PTools.UtilSQLAnalysis)
Purpose: Create a file containing all the data from the
%SYS.PTools.SQLUtilResults class & return the
output location...
Status: New Functionality
Invocation: This method can be invoked in the following ways:
ObjectScript: ##class(%SYS.PTools.SQLUtilResults).exportSQLUtilStmtRslts(...)
SQL: CALL %SYS_PTools.PT_exportSQLUtilStmtRslts(...)
SELECT %SYS_PTools.PT_exportSQLUtilStmtRslts(...)
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.SQLUtilResults).exportSQLUtilStmtRslts($IO)
#2 Display this export file via the SQL Query Page of the
Management Portal:
CALL %SYS_PTools.PT_exportSQLUtilStmtRslts('$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
NOTE: This method has been added to this DEPRECATED class to correspond
with the newly created 'export' method defined for the new &
improved interface in the '%SYS.PTools.UtilSQLAnalysis' class
Parameters:
file - The path and file in which to create and store the data from the
%SYS.PTools.SQLUtilResults 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_exportSQLUtilStmtRslts_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
option - A single option, or a comma-delimited list of {OptionName} values
to restrict the output of rows from the '%SYS.PTools.SQLUtilResults'
class. The {OptionName} specifies the Index Usage Analysis method
used to collect the data:
{OptionName} values: { IU - getAllIndices & indexUsage |
TS - tableScans | TI - tempIndices |
JI - joinIndices | OI - outlierIndices }
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 status from the invocation of this method
-------------------------------------------------------------------------------
classmethod version() as %String [ Language = objectscript, SQLProc = SQLUtilResults_version ]
Provide the current version for the SQLUtilResults class/section of the Performance Tools (PTools) Application
Indexes
index (Master on OptionName,Counter) [IdKey, Type = key];
Index methods: MasterCheck(), MasterDelete(), MasterExists(), MasterOpen(), MasterSQLCheckUnique(), MasterSQLExists(), MasterSQLFindPKeyByConstraint(), MasterSQLFindRowIDByConstraint()