%SYS.PTools.SQLQuery

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

SQL Table Name: %SYS_PTools.SQLQuery

This is the parent class of %SYS_PTools.SQLStats.

This class stores the general info of a query from compile time: QueryText, QueryType, CursorName, RoutineName, and NameSpace. All of the run time data is stored in the %SYS_PTools.SQLStats class.

Error information is stored in ^%sqlcq(NAMESPACE,"PTools","Error",$J).

Method Inventory

Export()
LogHeader()
NewQuery()

Properties

property CompileTime as %Numeric;

Number of Seconds it takes to compile the query

Property methods: CompileTimeDisplayToLogical(), CompileTimeGet(), CompileTimeGetStored(), CompileTimeIsValid(), CompileTimeLogicalToDisplay(), CompileTimeNormalize(), CompileTimeSet()

property CursorName as %String [ Required ];

What is the name of the cursor for this SQL Statement

Property methods: CursorNameDisplayToLogical(), CursorNameGet(), CursorNameGetStored(), CursorNameIsValid(), CursorNameLogicalToDisplay(), CursorNameLogicalToOdbc(), CursorNameNormalize(), CursorNameSet()

property Hash as %String (MAXLEN = 500);

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

Property methods: HashDisplayToLogical(), HashGet(), HashGetStored(), HashIsValid(), HashLogicalToDisplay(), HashLogicalToOdbc(), HashNormalize(), HashSet()

property ImportSchema as %String;

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

Property methods: ImportSchemaDisplayToLogical(), ImportSchemaGet(), ImportSchemaGetStored(), ImportSchemaIsValid(), ImportSchemaLogicalToDisplay(), ImportSchemaLogicalToOdbc(), ImportSchemaNormalize(), ImportSchemaSet()

property InternalQueryText as %String (MAXLEN = 30000, TRUNCATE = 1);

Property methods: InternalQueryTextDisplayToLogical(), InternalQueryTextGet(), InternalQueryTextGetStored(), InternalQueryTextIsValid(), InternalQueryTextLogicalToDisplay(), InternalQueryTextLogicalToOdbc(), InternalQueryTextNormalize(), InternalQueryTextSet()

property NameSpace as %String;

What NameSpace the SQL was generated in

Property methods: NameSpaceDisplayToLogical(), NameSpaceGet(), NameSpaceGetStored(), NameSpaceIsValid(), NameSpaceLogicalToDisplay(), NameSpaceLogicalToOdbc(), NameSpaceNormalize(), NameSpaceSet()

property QueryText as %String (COLLATION = "SQLUPPER(255)", MAXLEN = 30000, TRUNCATE = 1) [ Calculated ];

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

Property methods: QueryTextCompute(), QueryTextDisplayToLogical(), QueryTextGet(), QueryTextIsValid(), QueryTextLogicalToDisplay(), QueryTextLogicalToOdbc(), QueryTextNormalize(), QueryTextSQLCompute()

property QueryType as %String;

Is the Query an INSERT UPDATE DELETE or SELECT query

Property methods: QueryTypeDisplayToLogical(), QueryTypeGet(), QueryTypeGetStored(), QueryTypeIsValid(), QueryTypeLogicalToDisplay(), QueryTypeLogicalToOdbc(), QueryTypeNormalize(), QueryTypeSet()

property RoutineName as %String [ Required ];

What Routine is the SQL generated in

Property methods: RoutineNameDisplayToLogical(), RoutineNameGet(), RoutineNameGetStored(), RoutineNameIsValid(), RoutineNameLogicalToDisplay(), RoutineNameLogicalToOdbc(), RoutineNameNormalize(), RoutineNameSet()

property RunCount as %Integer [ InitialExpression = 0 ];

Number of times this query has be executed

Property methods: RunCountDisplayToLogical(), RunCountGet(), RunCountGetStored(), RunCountIsValid(), RunCountLogicalToDisplay(), RunCountNormalize(), RunCountSet()

relationship SQLStatsPointer as array of %SYS.PTools.SQLStats [ InitialExpression = $listbuild("%SYS.PTools.SQLStats","SQLQueryPointer",+$this,"children",1,1) , Transient , Inverse = SQLQueryPointer , Cardinality = children ];

Property methods: SQLStatsPointerGet(), SQLStatsPointerGetObject(), SQLStatsPointerGetObjectId(), SQLStatsPointerGetSwizzled(), SQLStatsPointerIsEmpty(), SQLStatsPointerIsValid(), SQLStatsPointerNewObject(), SQLStatsPointerRClose(), SQLStatsPointerRExec(), SQLStatsPointerRFetch(), SQLStatsPointerRelate(), SQLStatsPointerSQLCompute(), SQLStatsPointerSet(), SQLStatsPointerUnRelate()

property details as %Integer;

Do we have level 3 stats for this query

Property methods: detailsDisplayToLogical(), detailsGet(), detailsGetStored(), detailsIsValid(), detailsLogicalToDisplay(), detailsNormalize(), detailsSet()

Methods

classmethod Export(FileName="", delim="", ExportPlan=0) as %String

##class(%SYS.PTools.SQLQuery).Export(FileName,delim,ExportPlan)

FileName: Path and name of the file you want to export to.
delim: Default value $c(9) (tab) Delimiter for the columns of the export file.
ExportPlan: Optional. Default value = 0 if 1 then the SQL Show Plan will be included as part of the export
This method is used to export data from the %SYS.PTools.SQLQuery class to a delimited text file.

classmethod LogHeader(File, version, delim)

classmethod NewQuery(ByRef SQLText As %String, SQLType As %String, NameSpace As %String, RoutineName As %String, CursorName As %String, CompileTime As %Numeric = 0) as %Status

This method is called at compile time for a query It deletes all current stats for this query, as they could be very different after the compile

Indexes

index (master on RoutineName,CursorName) [IdKey, Type = key, Unique];

Index methods: masterCheck(), masterDelete(), masterExists(), masterOpen(), masterSQLCheckUnique(), masterSQLExists(), masterSQLFindPKeyByConstraint(), masterSQLFindRowIDByConstraint()

Inherited Members

Inherited Methods

Storage

Storage Model: CacheStorage (%SYS.PTools.SQLQuery)

^%sqlcq($NAMESPACE,"PTools","RunTime","SQLQueryD")(ID)

%%CLASSNAME

InternalQueryText

QueryType

CompileTime

Hash

NameSpace

RoutineName

CursorName

QueryText

ImportSchema

RunCount

details