Skip to main content

%SYS.PTools.SQLQuery

deprecated persistent class %SYS.PTools.SQLQuery extends %Library.Persistent, %SYS.PTools.FileCreation

SQL Table Name: %SYS_PTools.SQLQuery

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

Property Inventory

Method Inventory

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()
property SQLIndexHash as %String (MAXLEN = 32);
Internal unique statement hash used as the ID for the SQL Statement Index
Property methods: SQLIndexHashDisplayToLogical(), SQLIndexHashGet(), SQLIndexHashGetStored(), SQLIndexHashIsValid(), SQLIndexHashLogicalToDisplay(), SQLIndexHashLogicalToOdbc(), SQLIndexHashNormalize(), SQLIndexHashSet()
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

deprecated classmethod Export(file="", delim="", exportPlan=0) as %String [ SQLProc = SQLQuery_Export ]
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

classmethod clearSQLQuery(ns As %String = "", rtn As %String = "", ph3 As %Integer = 0, clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %RawString) as %Status [ SQLProc = PT_clearSQLQuery ]
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

classmethod purgeSQLQuery(ns As %String = "", rtn As %String = "", ph3 As %Integer = 0, clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %RawString) as %Status [ SQLProc = PT_purgeSQLQuery ]
Projected as the stored procedure: PT_purgeSQLQuery
Entry Point providing new functionality to delete Backward-compatible data: NOTE: See the clearSQLQuery() method above for more details.
classmethod version() as %String [ SQLProc = SQLQuery_version ]
Projected as the stored procedure: SQLQuery_version
Provide the current version for the %SYS.PTools.SQLQuery class/section of the Performance Tools (PTools) Application

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: Storage (%SYS.PTools.SQLQuery)

^%sqlcq($NAMESPACE,"PTools","RunTime","SQLQueryD")(ID)
=
%%CLASSNAME
InternalQueryText
QueryType
CompileTime
Hash
NameSpace
RoutineName
CursorName
QueryText
ImportSchema
RunCount
details
SQLIndexHash
FeedbackOpens in a new tab