Home > Class Reference > %SYS namespace > %SYS.PTools.UtilSQLAnalysisDB

%SYS.PTools.UtilSQLAnalysisDB

persistent class %SYS.PTools.UtilSQLAnalysisDB extends %Library.Persistent

SQL Table Name: %SYS_PTools.UtilSQLAnalysisDB

------------------------------------------------------------------------------- Class: %SYS.PTools.UtilSQLAnalysisDB Replaces: %SYS.PTools.SQLUtilResults [DEPRECATED] Purpose: This class is used by the following Methods and Queries, defined in the abstract interface class %SYS.PTools.UtilSQLAnalysis, to Analyze and Store the SQL Analysis details collected against the SQL Statements stored in the %SYS.PTools.UtilSQLStatements class: Methods Queries ------------------------------- ----------------------------------- - indexUsage - indexUsage - tableScans - tableScans - tempIndices - tempIndices - joinIndices - joinIndices - outlierIndices - outlierIndices - exportSQLUtilStmts - exportSQLUtilStmtRslts - exportSQLUtilities - clearSQLAnalysisDB Data Storage: ^%sqlcq("SAMPLES","PTools","util","SQLAnlsys","{C|D|I|S}") Error Storage: ^%sqlcq($NAMESPACE,"PTools","Error"[...])=$LIST Info -------------------------------------------------------------------------------

Properties

property BiasQueriesAsOutlier as %Library.Boolean [ InitialExpression = 0 ];
Set this flag { 0 | 1 } in the following location of the 'Storage' section of the class: {BiasQueriesAsOutlier} If this flag is set to zero (0), the query optimizer assumes that queries will not normally select on a outlier value. If this flag is set to one (1), the query optimizer will assume that queries selecting on a outlier are not rare.
Property methods: BiasQueriesAsOutlierDisplayToLogical(), BiasQueriesAsOutlierGet(), BiasQueriesAsOutlierGetStored(), BiasQueriesAsOutlierIsValid(), BiasQueriesAsOutlierLogicalToDisplay(), BiasQueriesAsOutlierNormalize(), BiasQueriesAsOutlierSet()
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 methods: BlockCountDisplayToLogical(), BlockCountGet(), BlockCountGetStored(), BlockCountIsValid(), BlockCountLogicalToDisplay(), BlockCountNormalize(), BlockCountSet()
property Counter as %Integer [ InitialExpression = $I(^%sqlcq($NAMESPACE,"PTools","util","SQLAnlsys","D")) , Required ];
2nd piece of the IDKey A unique ID counter which comprises the data row's IDKey
Property methods: CounterDisplayToLogical(), CounterGet(), CounterGetStored(), CounterIsValid(), CounterLogicalToDisplay(), CounterNormalize(), CounterSet()
property DataValues as list of %String (MAXLEN = 2000);
$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 methods: DataValuesBuildValueArray(), DataValuesCollectionToDisplay(), DataValuesCollectionToOdbc(), DataValuesDisplayToCollection(), DataValuesDisplayToLogical(), DataValuesGet(), DataValuesGetObject(), DataValuesGetObjectId(), DataValuesGetStored(), DataValuesGetSwizzled(), DataValuesIsValid(), DataValuesLogicalToDisplay(), DataValuesLogicalToOdbc(), DataValuesNormalize(), DataValuesOdbcToCollection(), DataValuesSet(), DataValuesSetObject(), DataValuesSetObjectId()
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 methods: ExtentSizeDisplayToLogical(), ExtentSizeGet(), ExtentSizeGetStored(), ExtentSizeIsValid(), ExtentSizeLogicalToDisplay(), ExtentSizeNormalize(), ExtentSizeSet()
property IndexFields as list of %String (MAXLEN = 2000);
$LISTBUILD of the Properties that will make up the Subscripts of the {IndexName} Every Index will have ID as the final subscript
Property methods: IndexFieldsBuildValueArray(), IndexFieldsCollectionToDisplay(), IndexFieldsCollectionToOdbc(), IndexFieldsDisplayToCollection(), IndexFieldsDisplayToLogical(), IndexFieldsGet(), IndexFieldsGetObject(), IndexFieldsGetObjectId(), IndexFieldsGetStored(), IndexFieldsGetSwizzled(), IndexFieldsIsValid(), IndexFieldsLogicalToDisplay(), IndexFieldsLogicalToOdbc(), IndexFieldsNormalize(), IndexFieldsOdbcToCollection(), IndexFieldsSet(), IndexFieldsSetObject(), IndexFieldsSetObjectId()
property IndexName as %String (MAXLEN = 2000);
Property methods: IndexNameDisplayToLogical(), IndexNameGet(), IndexNameGetStored(), IndexNameIsValid(), IndexNameLogicalToDisplay(), IndexNameLogicalToOdbc(), IndexNameNormalize(), IndexNameSet()
property JoinFields as list of %String (MAXLEN = 2000);
Set by joinIndices() Method $LISTBUILD of the Properties/Fields that comprise the Join Conditions This $LIST is used as a comparison against the the {IndexFields} $LIST in order to determine the viability of the specified {IndexName} and represented by the {JoinIndexFlag} field
Property methods: JoinFieldsBuildValueArray(), JoinFieldsCollectionToDisplay(), JoinFieldsCollectionToOdbc(), JoinFieldsDisplayToCollection(), JoinFieldsDisplayToLogical(), JoinFieldsGet(), JoinFieldsGetObject(), JoinFieldsGetObjectId(), JoinFieldsGetStored(), JoinFieldsGetSwizzled(), JoinFieldsIsValid(), JoinFieldsLogicalToDisplay(), JoinFieldsLogicalToOdbc(), JoinFieldsNormalize(), JoinFieldsOdbcToCollection(), JoinFieldsSet(), JoinFieldsSetObject(), JoinFieldsSetObjectId()
property JoinIndexFlag as %Integer;
Set by joinIndices() method to one of the following values: Flag has 5 values: 4 = Exact Match: An index exists where its fields match all fields from the JOIN conditions 3 = Leading Match: An index exists where its leading fields match all fields from the JOIN conditions, but there are additional fields in the index 2 = Contains Match: An index exists where its fields contains all fields from the JOIN conditions, but not the leading fields 1 = Partial Match: An index exists where its fields contains some of the fields from the JOIN conditions, but not the leading field 0 = No Match: No index exists to support the fields from the JOIN conditions
Property methods: JoinIndexFlagDisplayToLogical(), JoinIndexFlagGet(), JoinIndexFlagGetStored(), JoinIndexFlagIsValid(), JoinIndexFlagLogicalToDisplay(), JoinIndexFlagNormalize(), JoinIndexFlagSet()
property MapType as %String (MAXLEN = 100);
Set by the follow methods: tableScans(), tempIndices(), joinIndices(), outlierIndices() One of the following Map Types: { "master map " | "extent bitmap " | "bitmap index " | "index map " }
Property methods: MapTypeDisplayToLogical(), MapTypeGet(), MapTypeGetStored(), MapTypeIsValid(), MapTypeLogicalToDisplay(), MapTypeLogicalToOdbc(), MapTypeNormalize(), MapTypeSet()
property ModuleName as %String (MAXLEN = 2000);
Set by the TableScan class method Name of the Module that is doing the Table Scan
Property methods: ModuleNameDisplayToLogical(), ModuleNameGet(), ModuleNameGetStored(), ModuleNameIsValid(), ModuleNameLogicalToDisplay(), ModuleNameLogicalToOdbc(), ModuleNameNormalize(), ModuleNameSet()
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'
Property methods: OptionNameDisplayToLogical(), OptionNameGet(), OptionNameGetStored(), OptionNameIsValid(), OptionNameLogicalToDisplay(), OptionNameLogicalToOdbc(), OptionNameNormalize(), OptionNameSet()
property OutlierCondFields as list of %String (MAXLEN = 2000);
Set by outlierIndices() Method $LISTBUILD of the Properties/Fields that comprise the Outlier Conditions This $LIST is used as a comparison against the the {IndexFields} $LIST in order to determine the viability of the specified {IndexName} and represented by the {OutlierIndexFlag} field
Property methods: OutlierCondFieldsBuildValueArray(), OutlierCondFieldsCollectionToDisplay(), OutlierCondFieldsCollectionToOdbc(), OutlierCondFieldsDisplayToCollection(), OutlierCondFieldsDisplayToLogical(), OutlierCondFieldsGet(), OutlierCondFieldsGetObject(), OutlierCondFieldsGetObjectId(), OutlierCondFieldsGetStored(), OutlierCondFieldsGetSwizzled(), OutlierCondFieldsIsValid(), OutlierCondFieldsLogicalToDisplay(), OutlierCondFieldsLogicalToOdbc(), OutlierCondFieldsNormalize(), OutlierCondFieldsOdbcToCollection(), OutlierCondFieldsSet(), OutlierCondFieldsSetObject(), OutlierCondFieldsSetObjectId()
property OutlierField as %String (MAXLEN = 2000);
Property methods: OutlierFieldDisplayToLogical(), OutlierFieldGet(), OutlierFieldGetStored(), OutlierFieldIsValid(), OutlierFieldLogicalToDisplay(), OutlierFieldLogicalToOdbc(), OutlierFieldNormalize(), OutlierFieldSet()
property OutlierIndexFlag as %Integer;
Set by outlierIndices() Method Flag has 5 values: 4 = Exact Match: An index exists where its fields match all fields from the Outlier conditions 3 = Leading Match: An index exists where its leading fields match all fields from the Outlier conditions, but there are additional fields in the index 2 = Contains Match: An index exists where its fields contains all fields from the Outlier conditions, but not the leading fields 1 = Partial Match: An index exists where its fields contains some of the fields from the Outlier conditions, but not the leading field 0 = No Match: No index exists to support the fields from the Outlier conditions
Property methods: OutlierIndexFlagDisplayToLogical(), OutlierIndexFlagGet(), OutlierIndexFlagGetStored(), OutlierIndexFlagIsValid(), OutlierIndexFlagLogicalToDisplay(), OutlierIndexFlagNormalize(), OutlierIndexFlagSet()
property OutlierSelectivity as %Float;
Set this value in the following location of the 'Storage' section of the class or via $SYSTEM.SQLTuneTable(...): {OutlierSelectivity}:{OutlierValue} This is a percentage for a single property value that appears much more frequently than the other data values within the whole set of rows. Normal selectivity is still the percentage of each non-outlier data value within the whole set of rows. For example, if the outlier selectivity is 80% and the regular selectivity is 1%, then in addition to the outlier value, you can expect to find about 20 ((10-.80)/.01) additional non-outlier values. NOTE: This value represented the percentage of outliers, but without the percent sign. Hence, if the outlier value is 75%, then it should be represented at .75
Property methods: OutlierSelectivityDisplayToLogical(), OutlierSelectivityGet(), OutlierSelectivityGetStored(), OutlierSelectivityIsValid(), OutlierSelectivityLogicalToDisplay(), OutlierSelectivityNormalize(), OutlierSelectivitySet()
property OutlierValue as %String (MAXLEN = 1000);
Set this value in the following location of the 'Storage' section of the class or via $SYSTEM.SQLTuneTable(...): {OutlierSelectivity}:{OutlierValue} This is the value that should be used to compare against for conditions. If this Outlier Value is (represented in this context as the empty string "") and the conditions is IS NULL, then the Query Optimizer will use the {OutlierSelectivity} when constructing the Query Plan.
Property methods: OutlierValueDisplayToLogical(), OutlierValueGet(), OutlierValueGetStored(), OutlierValueIsValid(), OutlierValueLogicalToDisplay(), OutlierValueLogicalToOdbc(), OutlierValueNormalize(), OutlierValueSet()
property SQLPointer as %SYS.PTools.UtilSQLStatements;
Set in the following methods: tableScans(), tempIndices(), joinIndices(), outlierIndices() This is a Pointer Reference to the %SYS.PTools.UtilSQLStatements class which contains the SQL Statements found within InterSystems IRIS
Property methods: SQLPointerGet(), SQLPointerGetObject(), SQLPointerGetObjectId(), SQLPointerGetStored(), SQLPointerGetSwizzled(), SQLPointerIsValid(), SQLPointerNewObject(), SQLPointerSet(), SQLPointerSetObject(), SQLPointerSetObjectId(), SQLPointerUnSwizzle()
property SchemaName as %String (MAXLEN = 2000);
The schema of the table being queried in the SQL Statement
Property methods: SchemaNameDisplayToLogical(), SchemaNameGet(), SchemaNameGetStored(), SchemaNameIsValid(), SchemaNameLogicalToDisplay(), SchemaNameLogicalToOdbc(), SchemaNameNormalize(), SchemaNameSet()
property TableName as %String (MAXLEN = 2000);
The table being queried in the SQL Statement
Property methods: TableNameDisplayToLogical(), TableNameGet(), TableNameGetStored(), TableNameIsValid(), TableNameLogicalToDisplay(), TableNameLogicalToOdbc(), TableNameNormalize(), TableNameSet()
property UsageCount as %Integer;
Used by both IndexUsage() and TempIndices() class methods Number of times the index was used in the sql.
Property methods: UsageCountDisplayToLogical(), UsageCountGet(), UsageCountGetStored(), UsageCountIsValid(), UsageCountLogicalToDisplay(), UsageCountNormalize(), UsageCountSet()
property WhereCondition as %String (MAXLEN = 2000);
Property methods: WhereConditionDisplayToLogical(), WhereConditionGet(), WhereConditionGetStored(), WhereConditionIsValid(), WhereConditionLogicalToDisplay(), WhereConditionLogicalToOdbc(), WhereConditionNormalize(), WhereConditionSet()
property WhereOperator as %String (MAXLEN = 100);
Property methods: WhereOperatorDisplayToLogical(), WhereOperatorGet(), WhereOperatorGetStored(), WhereOperatorIsValid(), WhereOperatorLogicalToDisplay(), WhereOperatorLogicalToOdbc(), WhereOperatorNormalize(), WhereOperatorSet()
property WhereValue as %String (MAXLEN = 1000);
Property methods: WhereValueDisplayToLogical(), WhereValueGet(), WhereValueGetStored(), WhereValueIsValid(), WhereValueLogicalToDisplay(), WhereValueLogicalToOdbc(), WhereValueNormalize(), WhereValueSet()

Methods

classmethod clearSQLAnalysisDB(ns As %String = "", clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = UtilSQLAnalysisDB_clearSQLAnalysisDB ]

------------------------------------------------------------------------------- Method: clearSQLAnalysisDB Replaces: ClearResults (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: Delete all of the data stored in the '%SYS.PTools.UtilSQLAnalysisDB' class, based on the specified parameters... Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.UtilSQLAnalysisDB).clearSQLAnalysisDB(...) SQL: CALL %SYS_PTools.UtilSQLAnalysisDB_clearSQLAnalysisDB(...) SELECT %SYS_PTools.UtilSQLAnalysisDB_clearSQLAnalysisDB(...) 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.UtilSQLAnalysisDB' class in the current namespace: set status=##class(%SYS.PTools.UtilSQLAnalysisDB).clearSQLAnalysisDB() Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLAnlsys") 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.UtilSQLAnalysisDB [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","clearSQLAnalysisDB")=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.UtilSQLAnalysisDB -------------------------------------------------------------------------------

classmethod version() as %String [ Language = objectscript, SQLProc = UtilSQLAnalysisDB_version ]
Provide the current version for the UtilSQLAnalysisDB class/section of the Performance Tools (PTools) Application

Inherited Methods

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

Queries

query UtilSQLAnalysisDBView()
SQL Query as view "UtilSQLAnalysisDBView":
SELECT ID, OptionName, Counter, SQLPointer, SchemaName, TableName, ModuleName, MapType, ExtentSize, BlockCount, IndexName, UsageCount, $LISTTOSTRING(IndexFields,',') AS IndexFields, $LISTTOSTRING(DataValues,',') AS DataValues, JoinIndexFlag, $LISTTOSTRING(JoinFields,',') AS JoinFields, OutlierField, BiasQueriesAsOutlier, OutlierSelectivity, OutlierValue, OutlierIndexFlag, $LISTTOSTRING(OutlierCondFields,',') AS OutlierCondFields, WhereOperator, WhereValue, WhereCondition FROM %SYS_PTools.UtilSQLAnalysisDB

------------------------------------------------------------------------------- Query/View: UtilSQLAnalysisDBView Replaces: SQLUtilStmtResultsView (%SYS.PTools.SQLUtilResults) [DEPRECATED] Status: New Query/View to replicate Backward-Compatible Query/View it 'Replaces' Purpose: Class View which returns all of the SQL Statements information stored in the '%SYS.PTools.UtilSQLAnalysisDB' class/table -------------------------------------------------------------------------------

Indexes

index (Master on OptionName,Counter) [IdKey, Type = key];
Index methods: MasterCheck(), MasterDelete(), MasterExists(), MasterOpen(), MasterSQLCheckUnique(), MasterSQLExists(), MasterSQLFindPKeyByConstraint(), MasterSQLFindRowIDByConstraint()

Triggers

trigger OnDelete (AFTER event DELETE);

Storage

Storage Model: Storage (%SYS.PTools.UtilSQLAnalysisDB)

^%sqlcq($NAMESPACE,"PTools","util","SQLAnlsys","D")(ID)
=
%%CLASSNAME
SQLPointer
SchemaName
TableName
ModuleName
MapType
ExtentSize
BlockCount
IndexName
UsageCount
IndexFields
DataValues
JoinIndexFlag
JoinFields
OutlierField
BiasQueriesAsOutlier
OutlierSelectivity
OutlierValue
OutlierIndexFlag
OutlierCondFields
WhereOperator
WhereValue
WhereCondition