Class Reference
IRIS for UNIX 2019.2
InterSystems: The power behind what matters   
Documentation  Search
  [ENSLIB] >  [%SYS] >  [PTools] >  [SQLUtilResults]
Private  Storage   

persistent class %SYS.PTools.SQLUtilResults extends %Persistent

------------------------------------------------------------------------------ -- 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 ------------------------------------------------------------------------------

Inventory

Parameters Properties Methods Queries Indices ForeignKeys Triggers
14 2 1 1


Summary

Properties
BlockCount Counter DataValues ExtentSize IndexFields
IndexName JoinIndexFlag MapType ModuleName OptionName
SQLPointer SchemaName TableName UsageCount

Methods
%AddToSaveSet %AddToSyncSet %BMEBuilt %CheckConstraints
%CheckConstraintsForExtent %ClassIsLatestVersion %ClassName %ComposeOid
%ConstructClone %Delete %DeleteExtent %DeleteId
%DispatchClassMethod %DispatchGetModified %DispatchGetProperty %DispatchMethod
%DispatchSetModified %DispatchSetMultidimProperty %DispatchSetProperty %Exists
%ExistsId %Extends %GUID %GUIDSet
%GetLock %GetParameter %GetSwizzleObject %Id
%InsertBatch %IsA %IsModified %IsNull
%KillExtent %KillExtentData %LoadFromMemory %LockExtent
%LockId %New %NormalizeObject %ObjectIsNull
%ObjectModified %Oid %OnBeforeAddToSync %OnDetermineClass
%Open %OpenId %OriginalNamespace %PackageName
%PhysicalAddress %PurgeIndices %Reload %RemoveFromSaveSet
%ResolveConcurrencyConflict %RollBack %Save %SaveDirect
%SaveIndices %SerializeObject %SetModified %SortBegin
%SortEnd %SyncObjectIn %SyncTransport %UnlockExtent
%UnlockId %ValidateIndices %ValidateObject exportSQLUtilStmtRslts
version


Properties

• property BlockCount as %Integer;
The approximate numbers of map blocks used by each SQL map NOTE: The size of all SQL map blocks is 2048 bytes (2K bytes).
• property Counter as %Integer [ InitialExpression = $I(^%sqlcq($NAMESPACE,"PTools","Utils","Results","D")) ];
2nd piece of the IDKey A unique ID counter which comprises the data row's IDKey
• property DataValues as list of %String;
$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
• property ExtentSize as %Integer;
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
• property IndexFields as list of %String;
$LISTBUILD of the Properties that will make up the Subscripts of the {IndexName} Every Index will have ID as the final subscript
• property IndexName as %String;
• property JoinIndexFlag as %Integer;
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.
• property MapType as %String;
Set by the follow methods: tableScans(), tempIndices(), joinIndices(), outlierIndices() One of the following Map Types: { "master map " | "extent bitmap " | "bitmap index " | "index map " }
• property ModuleName as %String;
Set by the TableScan class method Name of the Module that is doing the Table Scan
• property OptionName as %String(VALUELIST=",IU,TS,TI,JI,OI,");
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'
• property SQLPointer as %SYS.PTools.SQLUtilities;
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
• property SchemaName as %String;
The schema of the table being queried in the SQL Statement
• property TableName as %String;
The table being queried in the SQL Statement
• property UsageCount as %Integer;
Used by both IndexUsage() and TempIndices() class methods Number of times the index was used in the sql.

Methods

• 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 [ 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 [ SQLProc = SQLUtilResults_version ]
Provide the current version for the SQLUtilResults class/section of the Performance Tools (PTools) Application

Indices

•index (Master on OptionName,Counter) [IdKey];

Triggers

•trigger OnDelete (AFTER event DELETE)


Copyright (c) 2019 by InterSystems Corporation. Cambridge, Massachusetts, U.S.A. All rights reserved. Confidential property of InterSystems Corporation.