Home > Class Reference > %SYS namespace > %SYSTEM.SQL.Stats.Runtime

%SYSTEM.SQL.Stats.Runtime

abstract class %SYSTEM.SQL.Stats.Runtime extends %SYSTEM.Help

Method Inventory (Including Private)

Methods (Including Private)

classmethod aggregateSQLStats(ns="", rtn="", curs="") as %Status [ Language = objectscript, SQLProc = Runtime_aggregateSQLStats ]
** SQL Stats **///

------------------------------------------------------------------------------- Method: aggregateSQLStats [SQL: PT_aggregateSQLStats] Replaces: N/A Status: New Functionality Purpose: This function aggregates all of the statistical properties for each SQL Query where data was collected Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.aggregateSQLStats(...) SQL: CALL %SYS_PTools.PT_aggregateSQLStats(...) SELECT %SYS_PTools.PT_aggregateSQLStats(...) 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 Aggregate all of the statistical properties for the 'SAMPLES' namespace: set status=$SYSTEM.SQL.Stats.Runtime.aggregateSQLStats("SAMPLES") Data Storage: ^%sqlcq($NAMESPACE,"PTools","db","Stats{C|D|I|S|Z}") Parameters: ns - The namespace in which to aggregate SQL Statistics If none provided, spin through all namespaces where SQL Stats collected rtn - The routine in which to aggregate SQL Statistics If none provided, spin through all routines where SQL Stats collected curs - The cursor in which to aggregate SQL Statistics If none provided, spin through all cursor where SQL Stats collected Return: Error Status -------------------------------------------------------------------------------

classmethod clearGeneralStats(namespace As %String = "", routine As %String = "", clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = Runtime_clearGeneralStats ]

------------------------------------------------------------------------------- Method: clearGeneralStats [SQL: PT_clearStats] Replaces: N/A Status: New Functionality Purpose: This method deletes all of the data stored in the '%SYS.PTools.Stats' class, based on the specified 'ns' and 'rtn' parameters... This method clears statistics which are gathered by the invocation of the following two methods: do $SYSTEM.SQL.Stats.Runtime.startGeneralStats(namespace,routine,mod) // Start Stats Collection // Invoke the method in which statistics are to be collected do $SYSTEM.SQL.Stats.Runtime.stopGeneralStats(namespace,routine,mod) // Stop Stats Collection Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.clearGeneralStats(...) SQL: CALL %SYS_PTools.PT_clearStats(...) SELECT %SYS_PTools.PT_clearStats(...) 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. Data Storage: ^%sqlcq($NAMESPACE,"PTools","db","Stats{C|D|I|S|Z}") Parameters: namespace - The namespace in which to clear Statistics If none provided, use the current namespaces where Stats collected [DEFAULT: Current Namespace] routine - The routine for which to clear Statistics If none provided, clear all routines in the given 'namespace' [OPTIONAL] 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.Stats [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","clearStats")=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.Stats -------------------------------------------------------------------------------

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

------------------------------------------------------------------------------- Method: clearSQLAnalysisDB [SQL: PT_clearSQLAnalysisDB] Replaces: ClearResults [SQL: 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... This method clears SQL Index Usage Analytical statistics which are gathered when one of the following methods are invoked: - indexUsage() - tableScans() - tempIndices() - joinIndices() - outlierIndices() Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.clearSQLAnalysisDB(...) SQL: CALL %SYS_PTools.PT_clearSQLAnalysisDB(...) SELECT %SYS_PTools.PT_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=$SYSTEM.SQL.Stats.Runtime.clearSQLAnalysisDB() Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLAnlsys","{C|D|I|S}") 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 clearSQLIndexStats(silent As %Integer = 0, clearErrs As %Integer = 1, returnType As %Integer = 0, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = Runtime_clearSQLIndexStats ]
** Analysis **///

------------------------------------------------------------------------------ C L E A R 'SQL Index Stats' D A T A ------------------------------------------------------------------------------ The following methods are used to remove all of the 'SQL Index Stats' data: - clearStatsSQLIndexALL() - clearStatsSQLIndexOLD() - ClearStatements() %SYS.PTools.SQLUtilities - ClearResults() %SYS.PTools.SQLUtilities - clearStatsSQLIndexNEW() - clearSQLStatements() %SYS.PTools.UtilSQLAnalysis - clearSQLAnalysisDB() %SYS.PTools.UtilSQLAnalysis The aforementioned methods remove data stored in the following classes: - %SYS.PTools.SQLUtilities (old) - %SYS.PTools.SQLUtilResults (old) - %SYS.PTools.UtilSQLStatements (new) - %SYS.PTools.UtilSQLAnalysisDB (new) NOTE: old = Backward-compatible data ------------------------------------------------------------------------------

------------------------------------------------------------------------------ Method: clearSQLIndexStats [SQL: PT_clearStatsSQLIndexALL] Replaces: N/A Status: New Functionality Purpose: This method deletes all of the data stored in the Original (backward-compatible) and the Optimal (new) PTools Utility SQL Index/Analysis Stats classes, based on the specified parameters... NOTE: Clear ALL PTools Utility SQL Index/Analysis Stats: OLD & NEW (By Default) Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.clearSQLIndexStats(...) ..clearStatsSQLIndexALL(...) SQL: CALL %SYS_PTools.PT_clearStatsSQLIndexALL(...) SELECT %SYS_PTools.PT_clearStatsSQLIndexALL(...) 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. Since this method returns a $LIST() of %Status codes, the output can be converted to a comma-delimited string by using the $LISTTOSTRING(...) function on the ObjectScript or SQL result. Data Storage: ^%sqlcq($NAMESPACE,"PTools",...) PARAMETERS: silent - 0 = Display all messages during this method invocation [DEFAULT] 1 = Don't display any messages during this method invocation clearErrs - 0 = Don't delete the 'PTools' Application Errors 1 = Delete all of the 'PTools' Application Errors [DEFAULT] returnType - 0 = Return a %Status code of either $$$OK or $$$ERROR() [DEFAULT] 1 = Return the total number of Stats rows deleted 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","clearStatsSQLIndexALL")=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 total number of Stats rows deleted; Otherwise, return an error message if an error occurred ------------------------------------------------------------------------------

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

------------------------------------------------------------------------------ C L E A R D A T A ------------------------------------------------------------------------------ For each of the SQL Statements saved in the %SYS.PTools.UtilSQLStatements class, and each of the SQL Statement Results in the %SYS.PTools.UtilSQLAnalysisDB class, use one of the following methods to delete all of the data stored in these classes: - clearSQLStatements() - clearSQLAnalysisDB() ------------------------------------------------------------------------------

------------------------------------------------------------------------------- Method: clearSQLStatements [SQL: PT_clearSQLStatements] Replaces: ClearStatements [SQL: ClearStatements] (%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.UtilSQLStatements class, based on the specified parameters... This method clears SQL Index Usage Analytical statistics which are gathered when one of the following methods are invoked: - indexUsage() - tableScans() - tempIndices() - joinIndices() - outlierIndices() Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.Stats.Runtime).clearSQLStatements(...) SQL: CALL %SYS_PTools.PT_clearSQLStatements(...) SELECT %SYS_PTools.PT_clearSQLStatements(...) 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.UtilSQLStatements' class in the current namespace: set status=$SYSTEM.SQL.Stats.Runtime.clearSQLStatements() Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") 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.UtilSQLStatements [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","clearSQLStatements")=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.UtilSQLStatements -------------------------------------------------------------------------------

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

------------------------------------------------------------------------------- Method: clearSQLStats [SQL: PT_clearStatsSQL] Replaces: Purge [SQL: StatsSQL_Purge] (%SYS.PTools.SQLStats) [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: This method deletes all of the data stored in the '%SYS.PTools.StatsSQL' class, based on the specified parameters: 'ns' & 'rtn' This method clears SQLStats which are gathered when the SQLStats-flag is turned ON, a SQL Statement is invoked, and the SQLStats-flag is turned OFF, as in the following example: set SQLStatsJOB=$SYSTEM.SQL.SetSQLStatsFlagJob(3) // SQLStats-flag ON set sql($i(sql))="SELECT COUNT(*) FROM Sample.Person" // Use Dynamic SQL Query Interface (%SQL.Statement) to invoke // SQL Statement & collect SQLStats data: // %New() ... %Prepare(.sql) ... %Execute() ... %Next() ... %OnClose() set SQLStatsJOB=$SYSTEM.SQL.SetSQLStatsFlagJob(0) // SQLStats-flag OFF Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.clearSQLStats(...) SQL: CALL %SYS_PTools.PT_clearStatsSQL(...) SELECT %SYS_PTools.PT_clearStatsSQL(...) 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.StatsSQL' class in the 'SAMPLES' namespace: set stats=$SYSTEM.SQL.Stats.Runtime.clearSQLStats("SAMPLES") Data Storage: ^%sqlcq($NAMESPACE,"PTools","db","Stats{C|D|I|S|Z}") Parameters: ns - The namespace in which to clear SQL Statistics If none provided, use the current namespaces where SQL Stats collected [DEFAULT: Current Namespace] rtn - The routine for which to clear SQL Statistics If none provided, clear all routines in the given 'ns' [OPTIONAL] clearAll - 0 = Perform a normal clear, but leave the 'INFO' rows 1 = Clear everything including the 'INFO' rows [DEFAULT: 0] 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.StatsSQL [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","clearStatsSQL")=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.StatsSQL -------------------------------------------------------------------------------

classmethod exportGeneralStats(file="", format="P", silent=0, statsType=0, rtnName="", modName="", ByRef conds As %ArrayOfObjects, ByRef ptInfo As %ArrayOfObjects, bcFlag="") as %Status [ Language = objectscript, SQLProc = Runtime_exportGeneralStats ]

------------------------------------------------------------------------------- Method: exportGeneralStats [SQL: PT_exportStats] Replaces: Report [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: Create a Performance Tool file containing the data from the '%SYS.PTools.Stats' 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: $SYSTEM.SQL.Stats.Runtime.exportGeneralStats(...) SQL: CALL %SYS_PTools.PT_exportStats(...) SELECT %SYS_PTools.PT_exportStats(...) 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=$SYSTEM.SQL.Stats.Runtime.exportGeneralStats($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportStats('$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","db","Stats{C|D|I|S|Z}") Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.Stats 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_Stats_exportStats_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 statsType - 0 - Return all rows from %SYS.PTools.Stats ONLY [DEFAULT] 1 - Return all rows from %SYS.PTools.StatsSQL ONLY 2 - Return all rows from %SYS.PTools.Stats & %SYS.PTools.StatsSQL rtnName - If specified, return only the rows where the 'RoutineName' matches the value of this parameter; Otherwise, return rows for all 'RoutineName' values modName - If specified, return only the rows where the 'ModuleName' matches the value of this parameter; Otherwise, return rows for all 'ModuleName' values 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 exportIUAnalysis(file As %String = "", format As %String = "", silent As %Integer = 0, p4 As %String = "", orderBy As %String = "4d", ByRef conds As %ArrayOfObjects, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = Runtime_exportIUAnalysis ]

------------------------------------------------------------------------------ E X P O R T I N G / R E P O R T I N G ------------------------------------------------------------------------------ The following Exporting/Reporting methods allow for the exporting & reporting of SQL Statements and accompanying SQL Index Usage Analytical data generated from the invocation of one of the following SQL Index Analyzer methods: SQL Index Analyzer Exporting/Reporting ================== ==================== - indexUsage() - exportIUAnalysis() - tableScans() - exportTSAnalysis() - tempIndices() - exportTIAnalysis() - joinIndices() - exportJIAnalysis() - outlierIndices() - exportOIAnalysis() NOTE: The data from the aforementioned methods are stored in the following classes/tables, and output the data from each method's corresponding Query with the same name: - %SYS.PTools.UtilSQLStatements - %SYS.PTools.UtilSQLAnalysisDB ------------------------------------------------------------------------------

------------------------------------------------------------------------------- Method: exportIUAnalysis [SQL: PT_exportIUAnalysis] Replaces: N/A Status: New Functionality Purpose: Create a file containing all the data from joining both the %SYS.PTools.UtilSQLStatements and %SYS.PTools.UtilSQLAnalysisDB classes & return the output location... Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.exportIUAnalysis(...) SQL: CALL %SYS_PTools.PT_exportIUAnalysis(...) SELECT %SYS_PTools.PT_exportIUAnalysis(...) 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=$SYSTEM.SQL.Stats.Runtime.exportIUAnalysis($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportIUAnalysis('$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: To query this information via SQL, use the following View: %SYS_PTools.UtilSQLAnalysis_indexUsage() Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.UtilSQLStatements & %SYS.PTools.UtilSQLAnalysisDB classes: (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_exportIUAnalysis_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 p4 - Placeholder Parameter for 'export*Analysis()' method conformity orderBy - A single field, or a comma-delimited list of fields in the format: field#[a|d][,field#[a|d],...,field#[a|d]] field# field Name ------ ------------- 0 NO 1 SchemaName 2 TableName 3 IndexName 4 UsageCount [DEFAULT: 4d] EXAMPLE: 4d,1,2a => ORDER BY UsageCount DESC,SchemaName,TableName ASC 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 exportJIAnalysis(file As %String = "", format As %String = "", silent As %Integer = 0, type As %String = "", orderBy As %String = "7d", ByRef conds As %ArrayOfObjects, includeSQL As %Integer = 1, skipJIF4 As %Integer = 1, indxFlgInfo As %Integer = 0, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = Runtime_exportJIAnalysis ]

------------------------------------------------------------------------------- Method: exportJIAnalysis [SQL: PT_exportJIAnalysis] Replaces: N/A Status: New Functionality Purpose: Create a file containing all the data from joining both the %SYS.PTools.UtilSQLStatements and %SYS.PTools.UtilSQLAnalysisDB classes & return the output location... Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.exportJIAnalysis(...) SQL: CALL %SYS_PTools.PT_exportJIAnalysis(...) SELECT %SYS_PTools.PT_exportJIAnalysis(...) 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.Stats.Runtime).exportJIAnalysis($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportJIAnalysis('$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: To query this information via SQL, use the following View: %SYS_PTools.UtilSQLAnalysis_joinIndices() Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.UtilSQLStatements & %SYS.PTools.UtilSQLAnalysisDB classes: (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_exportJIAnalysis_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 type - A single type, or a comma-delimited list of {Type} values to restrict the output of rows from the %SYS.PTools.UtilSQLStatements class. The {Type} is the location within InterSystems IRIS from where the SQL Query was extracted: {Type} values: { Cached Query | Class Method | Class Query | Routine } orderBy - A single field, or a comma-delimited list of fields in the format: field#[a|d][,field#[a|d],...,field#[a|d]] field# field Name ------ ------------- 0 NO 1 SchemaName 2 TableName 3 Name AS QryLocName 4 Type AS QryLocType 5 IndexName 6 JoinFields AS IndexNeeded 7 JoinIndexFlag [DEFAULT: 7d] 8 ExtentSize 9 BlockCount EXAMPLE: 7d,1,2a => ORDER BY JoinIndexFlag DESC,SchemaName,TableName ASC 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] includeSQL - 0 = Don't include the SQL Text in the output as the last field 1 = Include the SQL Text in the output as the last field [DEFAULT] skipJIF4 - 0 - Export all rows regardless of the 'JoinIndexFlag' value 1 - Export all rows where the value of the 'JoinIndexFlag' is not 4 [DEFAULT] indxFlgInfo - The specific information that should be returned for the 'JoinIndexFlag' field: 0 = The Index-Flag Internal Value [DEFAULT] 1 = The Index-Flag Title 2 = The Index-Flag Definition 3 = The Index-Flag as 'Title: Definition' 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 exportOIAnalysis(file As %String = "", format As %String = "", silent As %Integer = 0, type As %String = "", orderBy As %String = "8d", ByRef conds As %ArrayOfObjects, includeSQL As %Integer = 1, skipOIF4 As %Integer = 1, indxFlgInfo As %Integer = 0, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = Runtime_exportOIAnalysis ]

------------------------------------------------------------------------------- Method: exportOIAnalysis [SQL: PT_exportOIAnalysis] Replaces: N/A Status: New Functionality Purpose: Create a file containing all the data from joining both the %SYS.PTools.UtilSQLStatements and %SYS.PTools.UtilSQLAnalysisDB classes & return the output location... Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.exportOIAnalysis(...) SQL: CALL %SYS_PTools.PT_exportOIAnalysis(...) SELECT %SYS_PTools.PT_exportOIAnalysis(...) 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=$SYSTEM.SQL.Stats.Runtime.exportOIAnalysis($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportOIAnalysis('$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: To query this information via SQL, use the following View: %SYS_PTools.UtilSQLAnalysis_outlierIndices() Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.UtilSQLStatements & %SYS.PTools.UtilSQLAnalysisDB classes: (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_exportOIAnalysis_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 type - A single type, or a comma-delimited list of {Type} values to restrict the output of rows from the %SYS.PTools.UtilSQLStatements class. The {Type} is the location within InterSystems IRIS from where the SQL Query was extracted: {Type} values: { Cached Query | Class Method | Class Query | Routine } orderBy - A single field, or a comma-delimited list of fields in the format: field#[a|d][,field#[a|d],...,field#[a|d]] field# field Name ------ ------------- 0 NO 1 SchemaName 2 TableName 3 Name AS QryLocName 4 Type AS QryLocType 5 IndexName 6 IndexFields 7 BiasQueriesAsOutlier AS BiasOutlier 8 OutlierIndexFlag [DEFAULT: 8d] 9 OutlierField 10 OutlierValue AS OutlierCond 11 OutlierSelectivity AS OutlierSel 12 OutlierWhereCondition 13 ExtentSize 14 BlockCount EXAMPLE: 8d,1,2a => ORDER BY OutlierIndexFlag DESC,SchemaName,TableName ASC 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] includeSQL - 0 = Don't include the SQL Text in the output as the last field 1 = Include the SQL Text in the output as the last field [DEFAULT] skipOIF4 - 0 - Export all rows regardless of the 'OutlierIndexFlag' value 1 - Export all rows where the value of the 'OutlierIndexFlag' is not 4 [DEFAULT] indxFlgInfo - The specific information that should be returned for the 'OutlierIndexFlag' field: 0 = The Index-Flag Internal Value [DEFAULT] 1 = The Index-Flag Title 2 = The Index-Flag Definition 3 = The Index-Flag as 'Title: Definition' 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 exportSQLAnalysis(file As %String = "", format As %String = "", silent As %Integer = 0, type As %String = "", option As %String = "", ByRef conds As %ArrayOfObjects, includeSQL As %Integer = 1, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = Runtime_exportSQLAnalysis ]

------------------------------------------------------------------------------- Method: exportSQLAnalysis [SQL: PT_exportUtilSQLAnalysis] Replaces: exportSQLUtilities [SQL: PT_exportSQLUtilities] (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: Create a file containing all the data from joining both the %SYS.PTools.UtilSQLStatements and %SYS.PTools.UtilSQLAnalysisDB classes & return the output location... Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.exportSQLAnalysis(...) SQL: CALL %SYS_PTools.PT_exportUtilSQLAnalysis(...) SELECT %SYS_PTools.PT_exportUtilSQLAnalysis(...) 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=$SYSTEM.SQL.Stats.Runtime.exportSQLAnalysis($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportUtilSQLAnalysis('$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: To query this information via SQL, use the following View: %SYS_PTools.UtilSQLStmtsAndAnalysisDBView Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.UtilSQLStatements & %SYS.PTools.UtilSQLAnalysisDB classes: (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_exportUtilSQLAnalysis_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 type - A single type, or a comma-delimited list of {Type} values to restrict the output of rows from the %SYS.PTools.UtilSQLStatements class. The {Type} is the location within InterSystems IRIS from where the SQL Query was extracted: {Type} values: { cached query | Class Method | Class Query | Routine } option - A single option, or a comma-delimited list of {OptionName} values to restrict the output of rows from the %SYS.PTools.UtilSQLAnalysisDB 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] includeSQL - 0 = Don't include the SQL Text in the output as the last field 1 = Include the SQL Text in the output as the last field [DEFAULT] 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 exportSQLAnalysisDB(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 = Runtime_exportSQLAnalysisDB ]

------------------------------------------------------------------------------- Method: exportSQLAnalysisDB [SQL: PT_exportUtilSQLAnalysisDB] Replaces: exportSQLUtilStmts [SQL: PT_exportSQLUtilStmts] (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: Create a file containing all the data from the %SYS.PTools.UtilSQLAnalysisDB class & return the output location... Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.exportSQLAnalysisDB(...) SQL: CALL %SYS_PTools.PT_exportUtilSQLAnalysisDB(...) SELECT %SYS_PTools.PT_exportUtilSQLAnalysisDB(...) 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=$SYSTEM.SQL.Stats.Runtime.exportSQLAnalysisDB($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportUtilSQLAnalysisDB('$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 Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.UtilSQLAnalysisDB 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_exportUtilSQLAnalysisDB_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.UtilSQLAnalysisDB 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 exportSQLStatements(file As %String = "", format As %String = "", silent As %Integer = 0, type As %String = "", ByRef conds As %ArrayOfObjects, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = Runtime_exportSQLStatements ]

------------------------------------------------------------------------------ E X P O R T I N G / R E P O R T I N G ------------------------------------------------------------------------------ The following methods allow for the exporting and reporting of SQL Statements and accompanying data that have been saved in one of the following two classes/tables: - %SYS.PTools.UtilSQLStatements - %SYS.PTools.UtilSQLAnalysisDB ------------------------------------------------------------------------------

------------------------------------------------------------------------------- Method: exportSQLStatements [SQL: PT_exportUtilSQLStatements] Replaces: exportSQLUtilStmts [SQL: PT_exportSQLUtilStmts] (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: Create a file containing all the data from the %SYS.PTools.UtilSQLStatements class & return the output location... Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.exportSQLStatements(...) SQL: CALL %SYS_PTools.PT_exportUtilSQLStatements(...) SELECT %SYS_PTools.PT_exportUtilSQLStatements(...) 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=$SYSTEM.SQL.Stats.Runtime.exportSQLStatements($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportUtilSQLStatements('$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 Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.UtilSQLStatements 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_exportUtilSQLStatements_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 type - A single type, or a comma-delimited list of {Type} values to restrict the output of rows from the %SYS.PTools.UtilSQLStatements class. The {Type} is the location within InterSystems IRIS from where the SQL Query was extracted: {Type} values: { cached query | Class Method | Class Query | Routine } 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 exportSQLStats(file="", format="P", silent=0, statsType=0, exportPlan=0, rtnName="", modName="", ByRef conds As %ArrayOfObjects, ByRef ptInfo As %ArrayOfObjects, contRowCnt="", bcFlag="") as %Status [ Language = objectscript, SQLProc = Runtime_exportSQLStats ]

------------------------------------------------------------------------------- Method: exportSQLStats [SQL: PT_exportStatsSQL] Replaces: Export & ExportAll (%SYS.PTools.SQLStats) [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: Create a Performance Tool file containing the data from the '%SYS.PTools.StatsSQL' 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: $SYSTEM.SQL.Stats.Runtime.exportSQLStats(...) SQL: CALL %SYS_PTools.PT_exportStatsSQL(...) SELECT %SYS_PTools.PT_exportStatsSQL(...) 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=$SYSTEM.SQL.Stats.Runtime.exportSQLStats($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportStatsSQL('$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","db","Stats{C|D|I|S|Z}") Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.StatsSQL 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_StatsSQL_exportStatsSQL[_{Stmt|Plan}]_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 statsType - 0 - Return SQL Query Stats rows from %SYS.PTools.StatsSQL [DEFAULT] 1 - Return SQL Query Stmt/Plan rows from %SYS.PTools.StatsSQL exportPlan - 0 - Export the SQL Query Stmt Text [DEFAULT] 1 - Export the SQL Query Plan NOTE: This parameter is ONLY applicable when statsType=1 rtnName - If specified, return only the rows where the 'RoutineName' matches the value of this parameter; Otherwise, return rows for all 'RoutineName' values modName - If specified, return only the rows where the 'ModuleName' matches the value of this parameter; Otherwise, return rows for all 'ModuleName' values 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 contRowCnt - "" - No Action >=0 - Continuation from another Export, so skip the preamble code [InterSystems: Internal Purposes ONLY] bcFlag - This is a Backward-Compatibility flag used for [InterSystems: Internal Purposes ONLY] RETURN Value: The status from the invocation of this method -------------------------------------------------------------------------------

classmethod exportTIAnalysis(file As %String = "", format As %String = "", silent As %Integer = 0, type As %String = "", orderBy As %String = "9d", ByRef conds As %ArrayOfObjects, includeSQL As %Integer = 1, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = Runtime_exportTIAnalysis ]

------------------------------------------------------------------------------- Method: exportTIAnalysis [SQL: PT_exportTIAnalysis] Replaces: N/A Status: New Functionality Purpose: Create a file containing all the data from joining both the %SYS.PTools.UtilSQLStatements and %SYS.PTools.UtilSQLAnalysisDB classes & return the output location... Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.exportTIAnalysis(...) SQL: CALL %SYS_PTools.PT_exportTIAnalysis(...) SELECT %SYS_PTools.PT_exportTIAnalysis(...) 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=$SYSTEM.SQL.Stats.Runtime.exportTIAnalysis($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportTIAnalysis('$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: To query this information via SQL, use the following View: %SYS_PTools.UtilSQLAnalysis_tempIndices() Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.UtilSQLStatements & %SYS.PTools.UtilSQLAnalysisDB classes: (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_exportTIAnalysis_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 type - A single type, or a comma-delimited list of {Type} values to restrict the output of rows from the %SYS.PTools.UtilSQLStatements class. The {Type} is the location within InterSystems IRIS from where the SQL Query was extracted: {Type} values: { Cached Query | Class Method | Class Query | Routine } orderBy - A single field, or a comma-delimited list of fields in the format: field#[a|d][,field#[a|d],...,field#[a|d]] field# field Name ------ ------------- 0 NO 1 SchemaName 2 TableName 3 Name AS QryLocName 4 Type AS QryLocType 5 IndexName 6 IndexFields 7 DataValues 8 ExtentSize 9 BlockCount [DEFAULT: 9d] EXAMPLE: 9d,1,2a => ORDER BY BlockCount DESC,SchemaName,TableName ASC 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] includeSQL - 0 = Don't include the SQL Text in the output as the last field 1 = Include the SQL Text in the output as the last field [DEFAULT] 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 exportTSAnalysis(file As %String = "", format As %String = "", silent As %Integer = 0, type As %String = "", orderBy As %String = "8d", ByRef conds As %ArrayOfObjects, includeSQL As %Integer = 1, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = Runtime_exportTSAnalysis ]

------------------------------------------------------------------------------- Method: exportTSAnalysis [SQL: PT_exportTSAnalysis] Replaces: N/A Status: New Functionality Purpose: Create a file containing all the data from joining both the %SYS.PTools.UtilSQLStatements and %SYS.PTools.UtilSQLAnalysisDB classes & return the output location... Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.exportTSAnalysis(...) SQL: CALL %SYS_PTools.PT_exportTSAnalysis(...) SELECT %SYS_PTools.PT_exportTSAnalysis(...) 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=$SYSTEM.SQL.Stats.Runtime.exportTSAnalysis($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportTSAnalysis('$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: To query this information via SQL, use the following View: %SYS_PTools.UtilSQLAnalysis_tableScans() Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.UtilSQLStatements & %SYS.PTools.UtilSQLAnalysisDB classes: (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_exportTSAnalysis_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 type - A single type, or a comma-delimited list of {Type} values to restrict the output of rows from the %SYS.PTools.UtilSQLStatements class. The {Type} is the location within InterSystems IRIS from where the SQL Query was extracted: {Type} values: { Cached Query | Class Method | Class Query | Routine } orderBy - A single field, or a comma-delimited list of fields in the format: field#[a|d][,field#[a|d],...,field#[a|d]] field# field Name ------ ------------- 0 NO 1 SchemaName 2 TableName 3 Name AS QryLocName 4 Type AS QryLocType 5 ModuleName 6 MapType 7 ExtentSize 8 BlockCount [DEFAULT: 8d] EXAMPLE: 8d,1,2a => ORDER BY BlockCount DESC,SchemaName,TableName ASC 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] includeSQL - 0 = Don't include the SQL Text in the output as the last field 1 = Include the SQL Text in the output as the last field [DEFAULT] 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 getError(errRowID As %String = "", PToolsErr As %ArrayOfObjects) as %Integer [ Language = objectscript, SQLProc = Runtime_getError ]
** Information **///

------------------------------------------------------------------------------ G E T / C L E A R 'PToolsError' D A T A ------------------------------------------------------------------------------ The following methods are used to retrieve & remove all of the 'PToolsError' data: - getPToolsError() - clearPToolsError() The aforementioned methods remove data stored in the following global: - ^%sqlcq($NAMESPACE,"PTools","Error",{errRowID}) (old/new) NOTE: old = Backward-compatible data ------------------------------------------------------------------------------

------------------------------------------------------------------------------- Method: getError [SQL: PT_getPToolsError] Replaces: N/A Status: New Functionality Purpose: This method retrieve a specific 'PTools' Application Error for the given 'errRowID' or retrieve all errors if no 'errRowID' is provided. Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.getError(...) SQL: SELECT %SYS_PTools.PT_getPToolsError(...) NOTE: 'PTools' Application Errors will be returned in the 'PToolsErr' array as follows: PToolsErr={errRowID} [Counter] PToolsErr({errRowID},0)=$LIST: 1) $HOROLOG [Timestamp Internal] 2) Timestamp External [Timestamp External: YYYY-MM-DD HH:MM:SS] 3) $NAMESPACE [Current Namespace] 4) $JOB [Current $JOB] 5) $ZN [Current Routine] 6) $zu(41) [Stack Level] PToolsErr({errRowID},1)=$LIST: 1) {ns} [Error Namespace] 2) {job} [Error $JOB] 3) {rtn} [Error Routine] 4) {curs} [Error SQL Cursor] 5) {mod} [Error SQL Module] 6) {func} [Error Function/Method] 7) {ze} [Error $ZE] 8) {errMsg} [Error Message] --- SQL Errors --- 9) {class} [Error Class] 10) {table} [Error Table] 11) {SQLCODE} [Error SQLCODE] 12) {%msg} [Error SQL Message] PToolsErr({errRowID},2)=errLog(errLogID)=errLogMsg PToolsErr({errRowID},2,{errLogID})={errLogMsg} Parameters: errRowID - The 'PTools' Error RowID to retrieve; If none specified, then retrieve all errors into the 'PToolsErr()' array PToolsErr - An array in which the 'PTools' Application Errors will be returned [PASS BY REFERENCE] RETURN Value: The 'PTools' Error RowID ({errRowID}) retrieved, the empty string ("") if {errRowID} invalid, or the word 'all' indicating an array of all errors returned. -------------------------------------------------------------------------------

classmethod getFlag(flagType As %Integer = "", Output ptInfo As %ArrayOfObjects) as %Library.Integer [ Language = objectscript ]
** Flags **//

Method: getFlag [SQL: StatsSQL_GetSQLStatsFlag] Purpose: This method gets the flag that controls whether or not we collect SQL Statistics for each SQL Query execution Get the current value of the SQLStats-flag for the given 'flagType'. The SQLStats-flag (System/Job) controls whether or not SQL Statistics are collected for each SQL Query execution, and which performance statistics to be collected. The SQLStats-flag is a colon (:) delimited string comprised of the following individual components: SQLStats-flag={action-flag}:{collect-flag}:{condition-flag}:{condition-value}:{terminate-flag} RULES: When (flagType=""), whether to use the SQLStats-flag (System) or the SQLStats-flag (Process/Job) is determined by the following rules: IF {action-flag} (Process/Job) = 0, then use SQLStats-flag (System) ELSE use SQLStats-flag (Process/Job) Invocation: This method can be invoked in the following ways: Object Script: $SYSTEM.SQL.Stats.Runtime.getFlag(...) ##class(%SYSTEM.SQL.Stats.Runtime).getFlag(...) SQL: SELECT %SYS_PTools.StatsSQL_GetSQLStatsFlag(...) Examples: The following examples shows the use of this method: #1 Get the current value of the SQLStats-flag for the Job/System Flag based on SQLStats-flag Rules: set SQLStatsFlag=##class(%SYSTEM.SQL.Stats.Runtime).getFlag("") Parameters: flagType - "" = Job/System Flag based on SQLStats-flag Rules [DEFAULT] 0 = System Flag 1 = Process/Job Flag ptInfo - A Pass By Reference information array that returns to the user detailed information in the following format: ptInfo(category,variable)=value Where category = { "current" | "terminate" | ["expired"] | ... } variable = A variable that corresponds to the given 'category' Example: ptInfo("current","SQLStatsFlag")=The SQLStats-flag value after method execution RETURN: The current value of the SQLStats-flag; Otherwise, return an error message if an error occurred

classmethod getFlagByPID(pid As %String = "", ByRef ptInfo As %ArrayOfObjects) as %Integer [ Language = objectscript ]

Method: getFlagByPID [SQL: StatsSQL_GetSQLStatsFlagByPID] Purpose: This method gets the flag that controls whether or not we collect SQL Statistics about each SQL Query execution for the given 'pid' Get the current value of the SQLStats-flag for the given 'pid'. The SQLStats-flag (Process/Job) controls whether or not SQL Statistics are collected for each SQL Query execution, and which performance statistics to be collected. The SQLStats-flag is a colon (:) delimited string comprised of the following individual components: SQLStats-flag={action-flag}:{collect-flag}:{condition-flag}:{condition-value}:{terminate-flag} NOTE: This method ONLY applies to the SQLStats-flag for the given 'pid' (Process/Job) and NOT the SQLStats-flag for the (System)! RESTRICTION: This method invocation requires %Admin_Operate:Use privilege Invocation: This method can be invoked in the following ways: Object Script: $SYSTEM.SQL.Stats.Runtime.getFlagByPID(...) ##class(%SYSTEM.SQL.Stats.Runtime).getFlagByPID(...) SQL: SELECT %SYS_PTools.StatsSQL_GetSQLStatsFlagByPID(...) Examples: The following examples shows the use of this method: #1 Get the current value of the SQLStats-flag for the Job/System Flag based on SQLStats-flag Rules for PID# 12345: set SQLStatsFlag=##class(%SYSTEM.SQL.Stats.Runtime).getFlagByPID(12345) Parameters: pid - The process ID ($JOB) for which to set the SQLStats-flag [DEFAULT: Current $JOB] ptInfo - A Pass By Reference information array that returns to the user detailed information in the following format: ptInfo(category,variable)=value NOTE: This method currently returns no 'ptInfo', but is included for future extensibility RETURN: The current value of the SQLStats-flag; Otherwise, return an error message if an error occurred

classmethod getImportSchema(ns As %String = "", rtn As %String = "", curs As %String = "") as %String [ Language = objectscript ]

------------------------------------------------------------------------------- Method: getImportSchema [SQL: N/A] Replaces: ImportSchema (%SYS.PTools.SQLStats) [DEPRECATED] Status: New Functionality Purpose: This method retrieves the SQL Query Text given the parameters: 'ns', 'rtn', & 'curs' Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.getImportSchema(...) SQL: N/A Examples: The following examples shows the use of this method: #1 Retrieve the SQL Text for any of the data rows retrieved from invoking the 'exportStatsSQL()' method: CALL %SYS_PTools.PT_exportStatsSQL() set iSchema=$SYSTEM.SQL.Stats.Runtime.getImportSchema($NAMESPACE,"PToolsSQLStats"_$JOB,"SQLStats0") Data Storage: ^%sqlcq($NAMESPACE,"PTools","db","Stats{C|D|I|S|Z}") Parameters: ns - The namespace from which to retrieve the SQL Query Text rtn - The routine from which to retrieve the SQL Query Text curs - The cursor from which to retrieve the SQL Query Text Return: Return the Import Schema; Otherwise, return the Error Status if an error occurs. -------------------------------------------------------------------------------

classmethod getIndexFlagInfo(flagInt As %Integer = "", indexMeth As %String = "", returnInfo As %Integer = 3) as %String [ Language = objectscript, SQLProc = Runtime_getIndexFlagInfo ]

------------------------------------------------------------------------------- Method: getIndexFlagInfo [SQL: PT_getIndexFlagInfo] Replaces: N/A Status: New Functionality Purpose: Given an Index-Flag Internal Value 'flagInt', and the Index Analyzer Method 'indexMeth' for which this flag is associated, return the specific information requested by the 'returnInfo' parameter NOTE: This is a helper-method for the following Queries: - joinIndices - outlierIndices Parameters: flagInt - The Index-Flag Internal Value NOTE: The following example are the values specified for the 'joinIndices()' method: 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 indexMeth - The Index Analyzer Method for which the 'flagInt' parameter is associated - joinIndices - outlierIndices returnInfo - The specific information that should be returned from this method: 1 = The Index-Flag Title 2 = The Index-Flag Definition 3 = The Index-Flag as 'Title: Definition' RETURN Value: See the 'returnInfo' parameter for details ; Otherwise, return an error message if an error occurred -------------------------------------------------------------------------------

classmethod getQueryText(ns As %String = "", rtn As %String = "", curs As %String = "", getInt As %Boolean = 0) as %String [ Language = objectscript ]

------------------------------------------------------------------------------- Method: getQueryText [SQL: N/A] Replaces: QueryText (%SYS.PTools.SQLStats) [DEPRECATED] Status: New Functionality Purpose: This method retrieves the SQL Query Text given the parameters: 'ns', 'rtn', 'curs', & 'getInt' Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.getQueryText(...) SQL: N/A Examples: The following examples shows the use of this method: #1 Retrieve the SQL Text for any of the data rows retrieved from invoking the 'exportStatsSQL()' method: CALL %SYS_PTools.PT_exportStatsSQL() set sql=$SYSTEM.SQL.Stats.Runtime.getQueryText($NAMESPACE,"PToolsSQLStats"_$JOB,"SQLStats0") Data Storage: ^%sqlcq($NAMESPACE,"PTools","db","Stats{C|D|I|S|Z}") Parameters: ns - The namespace from which to retrieve the SQL Query Text rtn - The routine from which to retrieve the SQL Query Text curs - The cursor from which to retrieve the SQL Query Text getInt - 0 = Return the External Query Text 1 = Return the Internal Query Text Return: Return either the Internal or External SQL Query Text; Otherwise, return the Error Status if an error occurs. -------------------------------------------------------------------------------

classmethod indexUsage(clearData As %Integer = 1, getIndices As %Integer = 0, getSQLStmts As %Integer = 0, skipSysObj As %Integer = 1, skipIDKeys As %Integer = 1, skipInsStmts As %Integer = 1, display As %Integer = 1, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = Runtime_PT_indexUsage ]

============================================================================== A N A L Y Z E S Q L S T A T E M E N T S ============================================================================== ------------------------------------------------------------------------------ I N D E X U S A G E ------------------------------------------------------------------------------ For each of the SQL Statements saved in the %SYS.PTools.UtilSQLStatements class, generates a ShowPlan and keeps a count of how many times each index is used by each query, along with the total usage for each index by all queries in the namespace, and store this information in the %SYS.PTools.UtilSQLAnalysisDB class. ------------------------------------------------------------------------------

------------------------------------------------------------------------------- Method: indexUsage [SQL: PT_indexUsage] Replaces: IndexUsage [SQL: IndexUsage] (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: This method uses the SQL Statement data stored in the %SYS.PTools.UtilSQLStatements class to generate a ShowPlan for each query, and to keeps a count of how many times each index is used by each query. This index usage count is subsequently stored in the 'UsageCount' field of the %SYS.PTools.UtilSQLAnalysisDB class, which can be used to find and remove unneeded indices in addition to being used for other analytical purposes... Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.indexUsage(...) SQL: CALL %SYS_PTools.PT_indexUsage(...) SELECT %SYS_PTools.PT_indexUsage(...) 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 provide analytical information produced from the invocation of this method: #1 Show info from where the SQL Statements were collected: SELECT %EXACT(Type) AS QueryType, Count(*) AS QueryCount FROM %SYS_PTools.UtilSQLStatements GROUP BY Type OR SELECT * FROM %SYS_PTools.UtilSQLAnalysis_countSQLStmtTypes() #2 Show the data collected from the running of this method: SELECT * FROM %SYS_PTools.UtilSQLAnalysis_indexUsage() NOTE: These queries can either be invoked from a number of tools, such as the SQL Query tool in the 'Management Portal' or via 'SQL Query Shell' within a 'InterSystems IRIS Terminal' as show below: >do $SYSTEM.SQL.Shell() Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLAnlsys","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] getIndices - 1 = Get all the SQL Indexes from the Class Methods in this Namespace and add them to the %SYS.PTools.UtilSQLAnalysisDB class for additional processing of this method [DEFAULT: 0*] * - If '%SYS.PTools.UtilSQLAnalysisDB' contains NO Index Usage (IU) data, then DEFAULT 'getIndices' to 1, even if a 0 is passed in for this paramter, because this method requires Index Usage (IU) data for processing! getSQLStmts - 1 = Get all the SQL Statements from InterSystems IRIS locations (Cached Queries, Class Methods, Class Queries, MAC Routines) and add them to the %SYS.PTools.UtilSQLStatements class for additional processing of this method NOTE: This is an all inclusive call which could be time consuming. The %SYS.PTools.UtilSQLStatements class can populated via APIs that offer more precise collection alternatives which are less time consuming [DEFAULT: 0*] * - If '%SYS.PTools.UtilSQLStatements' contains NO data, then DEFAULT 'getSQLStmts' to 1 because this method requires SQL Statement data for processing! skipSysObj - 1 = Skip all System Object (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } NOTE: This only applies when retrieving SQL Indices (getIndices=1) and/or SQL Statements (getSQLStmts=1) [DEFAULT: 1] skipIDKeys - 1 = Skip all the index that will be used to form the Object Identity value (IDKEY) for the given class NOTE: This only applies when retrieving SQL Indices (getIndices=1) [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo("update-cnt")=The number of rows in the %SYS.PTools.UtilSQLAnalysisDB class which were updated with their Index Usage details RETURN Value: The status from the invocation of this method -------------------------------------------------------------------------------

classmethod joinIndices(clearData As %Integer = 1, getSQLStmts As %Integer = 0, skipSysObj As %Integer = 1, skipInsStmts As %Integer = 1, display As %Integer = 1, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = Runtime_PT_joinIndices ]

------------------------------------------------------------------------------ Q U E R I E S W I T H M I S S I N G J O I N I N D I C E S ------------------------------------------------------------------------------ For each of the SQL Statements saved in the %SYS.PTools.UtilSQLStatements class, identify all queries that have joins, and determines if there is an index defined to support that join. It ranks the indices available to support the joins from 0 (no index present) to 4 (index fully supports the join). Outer joins require an index in one direction. Inner joins require an index in both directions. The result set only contains rows that have a JoinIndexFlag < 4. JoinIndexFlag=4 means there is an index that fully supports the join; these are not listed. ------------------------------------------------------------------------------

------------------------------------------------------------------------------- Method: joinIndices [SQL: PT_joinIndices] Replaces: JoinIndices [SQL: JoinIndices] (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: This method uses the SQL Statement data stored in the %SYS.PTools.UtilSQLStatements class to pinpoint the queries that perform a JOIN between tables where their joined-fields use an index that supports the join. This method will then ranks the indices available to support the join from 0 (no index present) to 4 (index fully supports the join). Pertinent information about these queries is subsequently stored in the the %SYS.PTools.UtilSQLAnalysisDB class for future processing and analysis. It's worth noting that OUTER JOINs require an index in one direction, whereas INNER JOINs require an index in both directions. The result-set stored in the the %SYS.PTools.UtilSQLAnalysisDB class only contains rows that have a 'JoinIndexFlag < 4'. Rows that have a 'JoinIndexFlag = 4', which means that there exists an index that fully supports the JOIN, are not stored since they are already fully optimized. The stored queries should be reviewed to determine if an index could be added to the class to satisfy the highest 'JoinIndexFlag' possible: The 'JoinIndexFlag' property has the following 5 values: 0 - No Match: No index exists to support the fields from the JOIN conditions (Some version of the suggested index should be created to improve this query's performance) 1 - Partial Match: An index exists where its fields contains some of the fields from the JOIN conditions, but not the leading field (This will produce poor performance and for that reason is rarely used) 2 - Contains Match: An index exists where its fields contains all fields from the JOIN conditions, but not the leading fields (This might produce OK performance, but improvements should be made) 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 (This will produce OK performance, but improvements can be made) 4 - Exact Match: An index exists where its fields match all fields from the JOIN conditions (This is a fully optimized index) Optimizations ------------- - Creating a new index in the case of JoinIndexFlag = 0 or 1 should show good performance gains. - Creating a new index for JoinIndexFlag = 2 will help, improvements will depend on the number of leading subscripts and their selectivity - Creating a new index for JoinIndexFlag = 3 could help, in most cases you will only see small improvements. - The Order of the subscripts does not matter for the join, but could make a difference in performance. - The property with the lowest selectivity should be first. NOTE: The fields returned by the query's 'Index Needed' could be used to create an index that should help improve performance. Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.joinIndices(...) SQL: CALL %SYS_PTools.PT_joinIndices(...) SELECT %SYS_PTools.PT_joinIndices(...) 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 provide analytical information produced from the invocation of this method: #1 Show info from where the SQL Statements were collected: SELECT %EXACT(Type) AS QueryType, Count(*) AS QueryCount FROM %SYS_PTools.UtilSQLStatements GROUP BY Type OR SELECT * FROM %SYS_PTools.UtilSQLAnalysis_countSQLStmtTypes() #2 Show the data collected from the running of this method: SELECT SchemaName, TableName, SQLPointer->Type, SQLPointer->Name, IndexName, $LISTTOSTRING(JoinFields) AS "Index Needed", $LISTTOSTRING(IndexFields) AS "Indexed Fields", $LISTTOSTRING(JoinFields) AS "JOIN Fields", JoinIndexFlag AS "Join Index Flag", ExtentSize, BlockCount AS "Block Count", SQLPointer->SQLTextExt AS "SQL Text" FROM %SYS_PTools.UtilSQLAnalysisDB WHERE OptionName = 'JI' ORDER BY 1,2 OR SELECT * FROM %SYS_PTools.UtilSQLAnalysis_joinIndices() NOTE: This query will return JoinIndexFlag AS "Join Index Flag" as a numeric value (0-3) as specified above. However, in order to return more specificity for this field, one of the following values can be passed to the 2nd parameter 'indexFlagInfo' of the joinIndices() query: 0 = The Index-Flag Internal Value [DEFAULT] 1 = The Index-Flag Title 2 = The Index-Flag Definition 3 = The Index-Flag as 'Title: Definition' EXAMPLE: SELECT * FROM %SYS_PTools.UtilSQLAnalysis_joinIndices(,3) OR SELECT SchemaName, TableName, Type, "Class/Routine Name", IndexName, "Index Needed", "Join Index Flag", ExtentSize, "Block Count", "SQL Text" FROM %SYS_PTools.UtilSQLAnalysis_joinIndices() #3 Show joined-based tables with less than optimal indexes that support the JOIN specified in the query: SELECT SchemaName, TableName, $LISTTOSTRING(JoinFields) AS "Index Needed", JoinIndexFlag AS "Join Index Flag", COUNT(*) AS "Query Count" FROM %SYS_PTools.UtilSQLAnalysisDB WHERE JoinIndexFlag < 4 and OptionName = 'JI' GROUP BY SchemaName, TableName, IndexFields ORDER BY 4,5 DESC -- These tables should be reviewed to see if a better -- index can be added to fully support the JOIN conditions NOTE: These queries can be invoked from a number of tools, such as the SQL Query tool in the 'Management Portal' or via 'SQL Query Shell' within a 'InterSystems IRIS Terminal' as shown below: >do $SYSTEM.SQL.Shell() Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLAnlsys","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] getSQLStmts - 1 = Get all the SQL Statements from InterSystems IRIS locations (Cached Queries, Class Methods, Class Queries, MAC Routines) and add them to the %SYS.PTools.UtilSQLStatements class for additional processing of this method NOTE: This is an all inclusive call which could be time consuming. The %SYS.PTools.UtilSQLStatements class can populated via APIs that offer more precise collection alternatives which are less time consuming [DEFAULT: 0] skipSysObj - 1 = Skip all System Object (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo("insert-cnt")=The number of rows inserted in the %SYS.PTools.UtilSQLAnalysisDB class for queries that have joins indexing issues RETURN Value: The status from the invocation of this method -------------------------------------------------------------------------------

classmethod loadAllCachedQrySQLStmts(clearData As %Integer = 1, skipInsStmts As %Integer = 1, display As %Integer = 1) as %Integer [ Language = objectscript, SQLProc = Runtime_loadAllCachedQrySQLStmts ]

------------------------------------------------------------------------------ C A C H E D Q U E R I E S ------------------------------------------------------------------------------ Gather SQL Statements from all of the Cached Queries within InterSystems IRIS and add them to the %SYS.PTools.UtilSQLStatements class for Index Analysis and additional processing... ------------------------------------------------------------------------------

------------------------------------------------------------------------------- Method: loadAllCachedQrySQLStmts [SQL: PT_getAllCachedQrySQLStmts] Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all of the Cached Queries created and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Cached Queries added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred -------------------------------------------------------------------------------

classmethod loadAllClassMethSQLStmts(clearData As %Integer = 1, skipSysClass As %Integer = 1, skipInsStmts As %Integer = 1, display As %Integer = 1) as %Integer [ Language = objectscript, SQLProc = Runtime_loadAllClassMethSQLStmts ]

------------------------------------------------------------------------------ C L A S S M E T H O D S ------------------------------------------------------------------------------ Gather SQL Statements from all of the Class Methods within InterSystems IRIS and add them to the %SYS.PTools.UtilSQLStatements class for Index Analysis and additional processing... ------------------------------------------------------------------------------

------------------------------------------------------------------------------- Method: loadAllClassMethSQLStmts [SQL: PT_getAllClassMethSQLStmts] Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all of the Class Methods created and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipSysClass - 1 = Skip all System Classes that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of cached queries added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred -------------------------------------------------------------------------------

classmethod loadAllClassQrySQLStmts(clearData As %Integer = 1, skipSysClass As %Integer = 1, skipInsStmts As %Integer = 1, display As %Integer = 1) as %Integer [ Language = objectscript, SQLProc = Runtime_loadAllClassQrySQLStmts ]

------------------------------------------------------------------------------ C L A S S Q U E R Y ------------------------------------------------------------------------------ Gather SQL Statements from all of the Class Queries within InterSystems IRIS and add them to the %SYS.PTools.UtilSQLStatements class for Index Analysis and additional processing... ------------------------------------------------------------------------------

------------------------------------------------------------------------------- Method: loadAllClassQrySQLStmts [SQL: PT_getAllClassQrySQLStmts] Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all of the Class Queries created and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipSysClass - 1 = Skip all System Classes that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Class Queries added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred -------------------------------------------------------------------------------

classmethod loadAllIndices(clearData As %Integer = 1, skipSysClass As %Integer = 1, skipIDKeys As %Integer = 1, display As %Integer = 1) as %Integer [ Language = objectscript, SQLProc = Runtime_PT_loadAllIndices ]

============================================================================== C O L L E C T S Q L I N D E X E S ============================================================================== ------------------------------------------------------------------------------ A L L S Q L I N D E X E S ------------------------------------------------------------------------------ Gather SQL Indexes from all classes within InterSystems IRIS and add them to the %SYS.PTools.SQLUtilResult class for Index Analysis and additional processing ------------------------------------------------------------------------------

------------------------------------------------------------------------------ Method: loadAllIndices [SQL: PT_getAllIndices] Replaces: N/A [AllIndices^%SYS.PToolsDEP] Status: New Functionality Purpose: This method processes all of the classes/tables on the system and collects all of the defined Indexes and adds them to the %SYS.PTools.UtilSQLAnalysisDB class for future processing... Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.loadAllIndices(...) SQL: CALL %SYS_PTools.PT_getAllIndices(...) SELECT %SYS_PTools.PT_getAllIndices(...) 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 provide analytical information produced from the invocation of this method: #1 Show the data collected from the running of this method: SELECT * FROM %SYS_PTools.UtilSQLAnalysis_getAllIndices() NOTE: These queries can either be invoked from a number of tools, such as the SQL Query tool in the 'Management Portal' or via 'SQL Query Shell' within a 'InterSystems IRIS Terminal' as show below: >do $SYSTEM.SQL.Shell() Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLAnlsys","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipSysClass - 1 = Skip all System Classes that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } skipIDKeys - 1 = Skip all the index that will be used to form the Object Identity value (IDKEY) for the given class [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Indexes added to the %SYS.PTools.UtilSQLAnalysisDB class; Otherwise, return an error message if an error occurred ------------------------------------------------------------------------------

classmethod loadAllRtnQrySQLStmts(clearData As %Integer = 1, skipSysRtn As %Integer = 1, skipInsStmts As %Integer = 1, display As %Integer = 1) as %Integer [ Language = objectscript, SQLProc = Runtime_loadAllRtnQrySQLStmts ]

------------------------------------------------------------------------------ R O U T I N E Q U E R Y ------------------------------------------------------------------------------ Gather SQL Statements from all of the MAC Routines within InterSystems IRIS and add them to the %SYS.PTools.UtilSQLStatements class for Index Analysis and additional processing... ------------------------------------------------------------------------------

------------------------------------------------------------------------------- Method: loadAllRtnQrySQLStmts [SQL: PT_getAllRtnQrySQLStmts] Replaces: N/A Status: New Functionality Purpose: Get the Embedded SQL Statements from all of the MAC Routines and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipSysRtn - 1 = Skip all System Classes that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Embedded SQL Statements added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred -------------------------------------------------------------------------------

classmethod loadAllSQLStmts(clearData As %Integer = 1, skipSysObj As %Integer = 1, skipInsStmts As %Integer = 1, display As %Integer = 1, returnType As %Integer = 0, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = Runtime_PT_loadAllSQLStmts ]

------------------------------------------------------------------------------ A L L S Q L S T A T E M E N T S ------------------------------------------------------------------------------ Gather SQL Statements from all places within InterSystems IRIS and add them to the %SYS.PTools.UtilSQLStatements class for Index Analysis and additional processing... ------------------------------------------------------------------------------

------------------------------------------------------------------------------ Method: loadAllSQLStmts [SQL: PT_getAllSQLStmts] Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all of the following locations and add them to the %SYS.PTools.UtilSQLStatements class for additional processing: - Cached Queries - Class Methods - Class Queries - MAC Routines Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.loadAllSQLStmts(...) SQL: CALL %SYS_PTools.PT_getAllSQLStmts(...) SELECT %SYS_PTools.PT_getAllSQLStmts(...) 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 provide analytical information produced from the invocation of this method: #1 Show info from where the SQL Statements were collected: SELECT %EXACT(Type) AS QueryType, Count(*) AS QueryCount FROM %SYS_PTools.UtilSQLStatements GROUP BY Type OR SELECT * FROM %SYS_PTools.UtilSQLAnalysis_countSQLStmtTypes() #2 Show the data collected from the running of this method: SELECT Type, Name, MethodName, ImportPackage, SQLTextExt AS "SQL Text" FROM %SYS_PTools.UtilSQLStatements ORDER BY Type, Name OR SELECT * FROM %SYS_PTools.UtilSQLAnalysis_getAllSQLStmts() OR SELECT Type, "Class/Routine Name", "Method Name", ImportPackage, "SQL Text" FROM %SYS_PTools.UtilSQLAnalysis_getAllSQLStmts() NOTE: These queries can be invoked from a number of tools, such as the SQL Query tool in the 'Management Portal' or via 'SQL Query Shell' within a 'InterSystems IRIS Terminal' as shown below: >do $SYSTEM.SQL.Shell() Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipSysObj - 1 = Skip all System Object (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] returnType - 0 = Return a %Status code of either $$$OK or $$$ERROR() 1 = Return a $LIST of SQL Statements added to the %SYS.PTools.UtilSQLStatements class [DEFAULT: 0] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo("insert-list")=The number of rows inserted in the '%SYS.PTools.UtilSQLStatements' class in the following $LIST format $LIST() pieces: 1) Grand Total of all SQL Query Statements added 2) Total of all Cached Query Statements added 3) Total of all Class Method Statements added 4) Total of all Class Query Statements added 5) Total of all Routine Statements added 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 SQL Statements added to the %SYS.PTools.UtilSQLStatements class in the following $LIST format; Otherwise, return an error message if an error occurred. $LIST() pieces: 1) Grand Total of all SQL Query Statements added 2) Total of all cached query Statements added 3) Total of all Class Method Statements added 4) Total of all Class Query Statements added 5) Total of all Routine Statements added ------------------------------------------------------------------------------

classmethod loadCachedQrySQLStmtsByClass(ByRef items As %ArrayOfObjects, clearData As %Integer = 1, skipInsStmts As %Integer = 1, display As %Integer = 1) as %Integer [ Language = objectscript, SQLProc = Runtime_loadCachedQrySQLStmtsByClass ]

------------------------------------------------------------------------------- Method: loadCachedQrySQLStmtsByClass [SQL: PT_getCachedQrySQLStmtsByClass] Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all Cached Queries provided in the 'items' array and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: item - A comma delimited list or array of Cached Query class names NOTE: The class names must either all contain class extensions (e.g. myClass.CLS) or none at all (e.g. myClass) EXAMPLE: items="%sqlcq.SAMPLES.cls1,%sqlcq.SAMPLES.cls2" OR items("%sqlcq.SAMPLES.cls1")="" items("%sqlcq.SAMPLES.cls2")="" clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Cached Queries added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred -------------------------------------------------------------------------------

classmethod loadCachedQrySQLStmtsByDays(days As %Integer = 0, clearData As %Integer = 1, skipInsStmts As %Integer = 1, display As %Integer = 1) as %Integer [ Language = objectscript, SQLProc = Runtime_loadCachedQrySQLStmtsByDays ]

------------------------------------------------------------------------------- Method: loadCachedQrySQLStmtsByDays [SQL: PT_getCachedQrySQLStmtsByDays] Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all Cached Queries that have had a Prepare in the last 'days' days and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: days - The number of days since the last Cached Query was Prepared. If days=0, then get all Cached Queries. [DEFAULT: 0 (All)] clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Cached Queries added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred -------------------------------------------------------------------------------

classmethod loadClassMethSQLStmtsByClass(ByRef items As %ArrayOfObjects, clearData As %Integer = 1, skipInsStmts As %Integer = 1, display As %Integer = 1) as %Integer [ Language = objectscript, SQLProc = Runtime_loadClassMethSQLStmtsByClass ]

------------------------------------------------------------------------------- Method: loadClassMethSQLStmtsByClass [SQL: PT_getClassMethSQLStmtsByClass] Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all Class Methods provided in the 'items' array and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: item - A comma delimited list or array of Class Methods class names NOTE: The class names must either all contain class extensions (e.g. myClass.CLS) or none at all (e.g. myClass) EXAMPLE: items="Cinema.Film,Sample.Person" OR items("Cinema.Film")="" items("Sample.Person")="" clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Class Methods added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred -------------------------------------------------------------------------------

classmethod loadClassQrySQLStmtsByClass(ByRef items As %ArrayOfObjects, clearData As %Integer = 1, skipInsStmts As %Integer = 1, display As %Integer = 1) as %Integer [ Language = objectscript, SQLProc = Runtime_loadClassQrySQLStmtsByClass ]

------------------------------------------------------------------------------- Method: loadClassQrySQLStmtsByClass [SQL: PT_getClassQrySQLStmtsByClass] Replaces: N/A Status: New Functionality Purpose: Get the SQL Statements from all Class Queries provided in the 'items' array and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: item - A comma delimited list or array of Class Query class names NOTE: The class names must either all contain class extensions (e.g. myClass.CLS) or none at all (e.g. myClass) EXAMPLE: items="Cinema.Film,Sample.Person" OR items("Cinema.Film")="" items("Sample.Person")="" clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Class Queries added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred -------------------------------------------------------------------------------

classmethod loadRtnQrySQLStmtsByRtn(ByRef items As %ArrayOfObjects, clearData As %Integer = 1, skipInsStmts As %Integer = 1, display As %Integer = 1) as %Integer [ Language = objectscript, SQLProc = Runtime_loadRtnQrySQLStmtsByRtn ]

------------------------------------------------------------------------------- Method: loadRtnQrySQLStmtsByRtn [SQL: PT_getRtnQrySQLStmtsByRtn] Replaces: N/A Status: New Functionality Purpose: Get the Embedded SQL Statements from all of the MAC Routines provided in the 'items' array and add them to the %SYS.PTools.UtilSQLStatements class for additional processing... Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: item - A comma delimited list or array of MAC Routine names NOTE: The routine names must either all contain extensions (e.g. myRtn.MAC) or none at all (e.g. myRtn) EXAMPLE: items="MRPtest1,MRPtest2" OR items("MRPtest1")="" items("MRPtest2")="" clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] RETURN Value: The number of Cached Queries added to the %SYS.PTools.UtilSQLStatements class; Otherwise, return an error message if an error occurred -------------------------------------------------------------------------------

classmethod loadSQLStmts(clearData As %Integer = 1, skipSysObj As %Integer = 1, skipInsStmts As %Integer = 1, getCachedQry As %Integer = 1, getClassMethQry As %Integer = 1, getClassQry As %Integer = 1, getRtnQry As %Integer = 1, display As %Integer = 1, returnType As %Integer = 0) as %Status [ Language = objectscript, SQLProc = Runtime_PT_loadSQLStmts ]

------------------------------------------------------------------------------ Method: loadSQLStmts [SQL: PT_getSQLStmts] Replaces: GetSQLStatements (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: This method searches for at all of the SQL Statements in a namespace from the following locations based on the parameter values (getCachedQry,getClassMethQry,getClassQry,getRtnQry) and adds information about the statements (e.g. Type, Name, SQLText) to the %SYS.PTools.UtilSQLStatements class for additional processing: - Cached Queries [ if getCachedQry=1 ] - Class Methods [ if getClassMethQry=1 ] - Class Queries [ if getClassQry=1 ] - MAC Routines [ if getRtnQry=1 ] Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.loadSQLStmts(...) SQL: CALL %SYS_PTools.PT_getSQLStmts(...) SELECT %SYS_PTools.PT_getSQLStmts(...) 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 provide analytical information produced from the invocation of this method: #1 Show info from where the SQL Statements were collected: SELECT %EXACT(Type) AS QueryType, Count(*) AS QueryCount FROM %SYS_PTools.UtilSQLStatements GROUP BY Type OR SELECT * FROM %SYS_PTools.UtilSQLAnalysis_countSQLStmtTypes() #2 Show the data collected from the running of this method: SELECT Type, Name, MethodName, ImportPackage, SQLTextExt AS "SQL Text" FROM %SYS_PTools.UtilSQLStatements ORDER BY Type, Name OR SELECT * FROM %SYS_PTools.UtilSQLAnalysis_getSQLStmts() OR SELECT Type, "Class/Routine Name", "Method Name", ImportPackage, "SQL Text" FROM %SYS_PTools.UtilSQLAnalysis_getSQLStmts() NOTE: These queries can be invoked from a number of tools, such as the SQL Query tool in the 'Management Portal' or via 'SQL Query Shell' within a 'InterSystems IRIS Terminal' as shown below: >do $SYSTEM.SQL.Shell() Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLStmts","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] skipSysObj - 1 = Skip all System Object (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] getCachedQry - 1 = Get the SQL Statements from all Cached Queries [DEFAULT: 1] getClassMethQry - 1 = Get the SQL Statements from all of the Class Methods [DEFAULT: 1] getClassQry - 1 = Get the SQL Statements from all of the Class Queries [DEFAULT: 1] getRtnQry - 1 = Get the Embedded SQL Statements from all of the MAC Routines [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] returnType - 0 = Return a %Status code of either $$$OK or $$$ERROR() 1 = Return a $LIST of SQL Statements added to the %SYS.PTools.UtilSQLStatements class [DEFAULT: 0] RETURN Value: Based on the value of the 'returnType' parameter, return one of the following: 0: Return the status of this method; Otherwise, return an error message if an error occurred 1: Return the number of SQL Statements added to the %SYS.PTools.UtilSQLStatements class in the following $LIST format; Otherwise, return an error message if an error occurred. $LIST() pieces: 1) Grand Total of all SQL Query Statements added 2) Total of all cached query Statements added 3) Total of all Class Method Statements added 4) Total of all Class Query Statements added 5) Total of all Routine Statements added ------------------------------------------------------------------------------

classmethod outlierIndices(clearData As %Integer = 1, getSQLStmts As %Integer = 0, skipSysObj As %Integer = 1, skipInsStmts As %Integer = 1, display As %Integer = 1, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = Runtime_PT_outlierIndices ]

------------------------------------------------------------------------------ Q U E R I E S W I T H M I S S I N G O U T L I E R I N D I C E S ------------------------------------------------------------------------------ For each of the SQL Statements saved in the %SYS.PTools.UtilSQLStatements class, identify all queries that have outliers, and determines if there is an index defined to support that outlier. It ranks the indices available to support the outlier from 0 (no index present) to 4 (index fully supports the outlier). ------------------------------------------------------------------------------

------------------------------------------------------------------------------- Method: outlierIndices [SQL: PT_outlierIndices] Replaces: N/A Status: New Functionality Purpose: This method uses the SQL Statement data stored in the %SYS.PTools.UtilSQLStatements class to pinpoint the queries that have outliers, and determines if there is an index defined to support the Outlier Condition. This method will then ranks the indices available to support the Outlier Condition from 0 (no index present) to 4 (index fully supports the Outlier Condition). Pertinent information about these queries is subsequently stored in the the %SYS.PTools.UtilSQLAnalysisDB class for future processing and analysis. While the result-set stored in the the %SYS.PTools.UtilSQLAnalysisDB contains rows for all value of the 'OutlierIndexFlag' field, the outlierIndices() Query for this class/table only returns the rows that have the following : 'OutlierIndexFlag < 4'. Rows that have a 'OutlierIndexFlag = 4', which means that there exists an index that fully supports the Outlier Condition, are not returned by the outlierIndices() Query, since they are already fully optimized, however this information can be obtained by directly querying the %SYS_PTools.UtilSQLAnalysisDB table. The stored queries should be reviewed to determine if an index could be added to the class to satisfy the highest 'OutlierIndexFlag' possible: The 'OutlierIndexFlag' property has the following 5 values: 0 - No Match: No index exists to support the fields from the Outlier conditions (Some version of the suggested index should be created to improve this query's performance) 1 - Partial Match: An index exists where its fields contains some of the fields from the Outlier conditions, but not the leading field (This will produce poor performance and for that reason is rarely used) 2 - Contains Match: An index exists where its fields contains all fields from the Outlier conditions, but not the leading fields (This might produce OK performance, but improvements should be made) 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 (This will produce OK performance, but improvements can be made) 4 - Exact Match: An index exists where its fields match all fields from the Outlier conditions (This is a fully optimized index) Optimizations ------------- - Creating a new index in the case of OutlierIndexFlag = 0 or 1 should show good performance gains. - Creating a new index for OutlierIndexFlag = 2 will help, improvements will depend on the number of leading subscripts and their selectivity - Creating a new index for OutlierIndexFlag = 3 could help, in most cases you will only see small improvements. - The Order of the subscripts does not matter for the Outlier Conditions, but could make a difference in performance. - The property with the lowest selectivity should be first. NOTE: The fields returned by the query's 'IndexFields' could be used to create an index that should help improve performance. Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYSTEM.SQL.Stats.Runtime).outlierIndices(...) SQL: CALL %SYS_PTools.PT_outlierIndices(...) SELECT %SYS_PTools.PT_outlierIndices(...) 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 provide analytical information produced from the invocation of this method: #1 Show info from where the SQL Statements were collected: SELECT %EXACT(Type) AS QueryType, Count(*) AS QueryCount FROM %SYS_PTools.UtilSQLStatements GROUP BY Type OR SELECT * FROM %SYS_PTools.UtilSQLAnalysis_countSQLStmtTypes() #2 Show the data collected from the running of this method: SELECT SchemaName, TableName, SQLPointer->Type, SQLPointer->Name, $LISTTOSTRING(IndexFields) As "Index Fields", JoinIndexFlag As "Join Index Flag", ExtentSize, BlockCount As "Block Count", SQLPointer->SQLTextExt AS "SQL Text" FROM %SYS_PTools.UtilSQLAnalysisDB WHERE OptionName = 'OI' ORDER BY 1,2 OR SELECT * FROM %SYS_PTools.UtilSQLAnalysis_outlierIndices() NOTE: This query will return OutlierIndexFlag AS "Outlier Index Flag" as a numeric value (0-3) as specified above. However, in order to return more specificity for this field, one of the following values can be passed to the 2nd parameter 'indexFlagInfo' of the outlierIndices() query: 0 = The Index-Flag Internal Value [DEFAULT] 1 = The Index-Flag Title 2 = The Index-Flag Definition 3 = The Index-Flag as 'Title: Definition' EXAMPLE: SELECT * FROM %SYS_PTools.UtilSQLAnalysis_outlierIndices(,3) OR SELECT SchemaName, TableName, Type, "Class/Routine Name", IndexName, "Index Fields", "Bias Outlier", "Outlier Index Flag", "Outlier Field", "Outlier Condition", "Outlier Selectivity", "WHERE Condition", ExtentSize, "Block Count", "SQL Text" FROM %SYS_PTools.UtilSQLAnalysis_outlierIndices() #3 Show outlier-based tables with less than optimal indexes that support the Outlier Condition specified in the query: SELECT SchemaName, TableName, IndexFields As "Index Fields", OutlierIndexFlag AS "Outlier Index Flag", COUNT(*) AS "Query Count" FROM %SYS_PTools.UtilSQLAnalysisDB WHERE OutlierIndexFlag < 4 and OptionName = 'OI' GROUP BY SchemaName, TableName, IndexFields ORDER BY 4,5 DESC -- These tables should be reviewed to see if a better -- index can be added to fully support the Outlier Conditions NOTE: These queries can be invoked from a number of tools, such as the SQL Query tool in the 'Management Portal' or via 'SQL Query Shell' within a 'InterSystems IRIS Terminal' as shown below: >do $SYSTEM.SQL.Shell() Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLAnlsys","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] getSQLStmts - 1 = Get all the SQL Statements from InterSystems IRIS locations (Cached Queries, Class Methods, Class Queries, MAC Routines) and add them to the %SYS.PTools.UtilSQLStatements class for additional processing of this method NOTE: This is an all inclusive call which could be time consuming. The %SYS.PTools.UtilSQLStatements class can populated via APIs that offer more precise collection alternatives which are less time consuming [DEFAULT: 0] skipSysObj - 1 = Skip all System Object (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo("insert-cnt")=The number of rows inserted in the %SYS.PTools.UtilSQLAnalysisDB class for queries that have outliers indexing issues RETURN Value: The status from the invocation of this method -------------------------------------------------------------------------------

classmethod setFlag(actionFlag As %Integer = 0, collectFlag As %String = "", terminateCond As %String = 0, Output ptInfo As %ArrayOfObjects) as %Integer [ Language = objectscript ]

Method: setFlag [SQL: StatsSQL_SetSQLStats] Purpose: This method sets the flag that controls whether or not the System collects SQL Statistics about each run of a query You can invoke different levels of SQL Statistics collection by setting the SQLStats-flag, for the System with this method. The SQLStats-flag controls whether or not SQL Statistics are collected for each SQL Query execution, and which performance statistics to collect. The SQLStats-flag is a colon (:) delimited string comprised of the following individual components: SQLStats-flag={action-flag}:{collect-flag}:{condition-flag}:{condition-value}:{terminate-flag} Invocation: This method can be invoked in the following ways: Object Script: $SYSTEM.SQL.Stats.Runtime.setFlag(...) ##class(%SYSTEM.SQL.Stats.Runtime).setFlag(...) SQL: SELECT %SYS_PTools.StatsSQL_SetSQLStatsFlag(...) Examples: The following examples shows the use of this method: #1 Turn PTools ON to collects stats for all SQL modules (System): set oldStats=##class(%SYSTEM.SQL.Stats.Runtime).setFlag(3) Parameters: actionFlag - The portion of the SQLStats-flag which is represented by the 1st colon (:) piece, and can have one of the following values: 0 = Query Compilation: Don't generator SQLStats collection code for any Query Modules Query Execution: Don't collect SQLStats for any Query Modules 1 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Don't collect SQLStats for any Query Modules 2 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Collect SQLStats for MAIN Query Module (Start & Stop) 3 = Query Compilation: Generator SQLStats collection code for ALL Query Modules Query Execution: Collect SQLStats for ALL Query Modules collectFlag - The portion of the SQLStats-flag which is represented by the 2nd colon (:) piece, and is a numeric value representing which SQL Performance Statistics/Metrics to collect, with one of the following values: "" = Collect All SQL Performance Metrics [DEFAULT] n = Where n=SUM(MVal) [See Below] In general, all of the following performance metrics are collected for each SQL module, along with a cumulative value representing all SQL modules, when the SQLStats-flag is turned on: MVal Metric Name Metric Description ---- ---------------------------- ---------------------------------- 1 Query Execution Time Total number of seconds elapsed 2 Total Global References Total number of global references 4 Total Lines of Code Executed Total number of COS lines executed 8 Total Disk Read Latency Time Total milliseconds spent waiting ==== for Disk Reads 15 ALL Collect all Performance Metrics This parameter provides a mechanism by which to choose a combination of which performance metrics to collect, by specifying a SUM of the performance metrics MVal numbers (specified above) that you wish to collect. EXAMPLE #1: The following example represents the collection of 2 performance metrics, 'Query Execution Time' & 'Total Disk Read Latency Time': MVal Metric Name ---- ---------------------------- 1 Query Execution Time +8 Total Disk Read Latency Time === 9 Collect both of these performance metrics By passing 9 for the value of the 'collectFlag' parameter, the sum of the two aforementioned performance metrics MVal numbers, the SQLStats PTool application will only collect statistical data for these two performance metrics whenever an SQL Query is invoked. EXAMPLE #2: The following example represents the collection of 3 performance metrics, 'Query Execution Time', 'Total Global References' & 'Total Lines of Code Executed': MVal Metric Name ---- ---------------------------- 1 Query Execution Time 2 Total Global References +4 Total Lines of Code Executed === 7 Collect both of these performance metrics By passing 7 for the value of the 'collectFlag' parameter, the sum of the three aforementioned performance metrics MVal numbers, the SQLStats PTool application will only collect statistical data for these two performance metrics whenever an SQL Query is invoked. terminateCond - The portion of the SQLStats-flag which is represented by the 5th colon (:) piece, and is determined by this parameter value. An optional condition to determine when to terminate the SQLStats collection, by turning off the SQLStats-flag (System), or by setting it to a specified reset value. This parameter accepts the following values: 0 No Action [DEFAULT] M:: Where =# of minutes from 1..n When specified, this value will terminate the SQLStats collection the number of minutes after the current $H Where =Reset {action-flag} value from 0..3 When specified, this is the value to which the {action-flag} portion of the SQLStats-flag will be set upon expiration If not specified, default to zero (0) (e.g. "M:120:1" => Terminate 120minutes after the current $H and reset SQLStats-flag/{action-flag} to 1) T:: Where =Timestamp with the format: YYYYMMDD HHMM When specified, this value will terminate the SQLStats collection after the timestamp value Where =Reset {action-flag} value from 0..3 When specified, this is the value to which the {action-flag} portion of the SQLStats-flag will be set upon expiration If not specified, default to zero (0) (e.g. "T:20171018 1330:1" => Terminate after 10/18/2017 01:30PM and reset SQLStats-flag/{action-flag} to 1) ptInfo - A Pass By Reference information array that returns to the user detailed information in the following format: ptInfo(category,variable)=value Where category = { "current" | "terminate" | ... } variable = A variable that corresponds to the given 'category' Example: ptInfo("current","SQLStatsFlag")=The SQLStats-flag value after method execution RETURN: The value of the SQLStats-flag before setting it to the new value; Otherwise, return an error message if an error occurred

classmethod setFlagByNS(namespace As %String = "", actionFlag As %Integer = 0, collectFlag As %String = "", terminateCond As %String = 0, Output ptInfo As %ArrayOfObjects) as %Integer [ Language = objectscript ]

Method: setFlagByNS [SQL: StatsSQL_SetSQLStatsFlagByNS] Purpose: This method sets the flag that controls whether or not to collect SQL Statistics about each run of a query within the given 'ns' (Namespace) You can invoke different levels of SQL Statistics collection by setting the SQLStats-flag, for a given 'ns'. The SQLStats-flag (System) controls whether or not SQL Statistics are collected for each SQL Query execution, and which performance statistics to be collected. The SQLStats-flag is a colon (:) delimited string comprised of the following individual components: SQLStats-flag={action-flag}:{collect-flag}:{condition-flag}:{condition-value}:{terminate-flag} NOTE: This method ONLY applies to the SQLStats-flag (System) for the given 'namespace' and NOT the SQLStats-flag (Process/Job)! Invocation: This method can be invoked in the following ways: Object Script: $SYSTEM.SQL.Stats.Runtime.setFlagByNS(...) ##class(%SYSTEM.SQL.Stats.Runtime).setFlagByNS(...) SQL: SELECT %SYS_PTools.StatsSQL_SetSQLStatsFlagByNS(...) Examples: The following examples shows the use of this method: #1 Turn PTools ON to collects stats for all SQL modules within the "USER" Namespace: set oldStats=##class(%SYSTEM.SQL.Stats.Runtime).setFlagByNS("USER",3) Parameters: namespace - The Namespace ($NAMESPACE) for which to set the SQLStats-flag [REQUIRED] actionFlag - The portion of the SQLStats-flag which is represented by the 1st colon (:) piece, and can have one of the following values: 0 = Query Compilation: Don't generator SQLStats collection code for any Query Modules Query Execution: Don't collect SQLStats for any Query Modules 1 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Don't collect SQLStats for any Query Modules 2 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Collect SQLStats for MAIN Query Module (Start & Stop) 3 = Query Compilation: Generator SQLStats collection code for ALL Query Modules Query Execution: Collect SQLStats for ALL Query Modules collectFlag - The portion of the SQLStats-flag which is represented by the 2nd colon (:) piece, and is a numeric value representing which SQL Performance Statistics/Metrics to collect, with one of the following values: "" = Collect All SQL Performance Metrics [DEFAULT] n = Where n=SUM(MVal) [See Below] In general, all of the following performance metrics are collected for each SQL module, along with a cumulative value representing all SQL modules, when the SQLStats-flag is turned on: MVal Metric Name Metric Description ---- ---------------------------- ---------------------------------- 1 Query Execution Time Total number of seconds elapsed 2 Total Global References Total number of global references 4 Total Lines of Code Executed Total number of COS lines executed 8 Total Disk Read Latency Time Total milliseconds spent waiting ==== for Disk Reads 15 ALL Collect all Performance Metrics This parameter provides a mechanism by which to choose a combination of which performance metrics to collect, by specifying a SUM of the performance metrics MVal numbers (specified above) that you wish to collect. EXAMPLE #1: The following example represents the collection of 2 performance metrics, 'Query Execution Time' & 'Total Disk Read Latency Time': MVal Metric Name ---- ---------------------------- 1 Query Execution Time +8 Total Disk Read Latency Time === 9 Collect both of these performance metrics By passing 9 for the value of the 'collectFlag' parameter, the sum of the two aforementioned performance metrics MVal numbers, the SQLStats PTool application will only collect statistical data for these two performance metrics whenever an SQL Query is invoked. EXAMPLE #2: The following example represents the collection of 3 performance metrics, 'Query Execution Time', 'Total Global References' & 'Total Lines of Code Executed': MVal Metric Name ---- ---------------------------- 1 Query Execution Time 2 Total Global References +4 Total Lines of Code Executed === 7 Collect both of these performance metrics By passing 7 for the value of the 'collectFlag' parameter, the sum of the three aforementioned performance metrics MVal numbers, the SQLStats PTool application will only collect statistical data for these two performance metrics whenever an SQL Query is invoked. terminateCond - The portion of the SQLStats-flag which is represented by the 5th colon (:) piece, and is determined by this parameter value. An optional condition to determine when to terminate the SQLStats collection, by turning off the SQLStats-flag (System), or by setting it to a specified reset value. This parameter accepts the following values: 0 No Action [DEFAULT] M:: Where =# of minutes from 1..n When specified, this value will terminate the SQLStats collection the number of minutes after the current $H Where =Reset {action-flag} value from 0..3 When specified, this is the value to which the {action-flag} portion of the SQLStats-flag will be set upon expiration If not specified, default to zero (0) (e.g. "M:120:1" => Terminate 120minutes after the current $H and reset SQLStats-flag/{action-flag} to 1) T:: Where =Timestamp with the format: YYYYMMDD HHMM When specified, this value will terminate the SQLStats collection after the timestamp value Where =Reset {action-flag} value from 0..3 When specified, this is the value to which the {action-flag} portion of the SQLStats-flag will be set upon expiration If not specified, default to zero (0) (e.g. "T:20171018 1330:1" => Terminate after 10/18/2017 01:30PM and reset SQLStats-flag/{action-flag} to 1) ptInfo - A Pass By Reference information array that returns to the user detailed information in the following format: ptInfo(category,variable)=value Where category = { "current" | "terminate" | ... } variable = A variable that corresponds to the given 'category' Example: ptInfo("current","SQLStatsFlag")=The SQLStats-flag value after method execution RETURN: The value of the SQLStats-flag before setting it to the new value; Otherwise, return an error message if an error occurred

classmethod setFlagByPID(pid As %String = "", actionFlag As %Integer = 0, collectFlag As %String = "", Output ptInfo As %ArrayOfObjects) as %Integer [ Language = objectscript ]

Method: setFlagByPID [SQL: StatsSQL_SetSQLStatsFlagByPID] Purpose: This method sets the flag that controls whether or not to collect SQL Statistics about each run of a query for the given 'pid' You can invoke different levels of SQL Statistics collection by setting the SQLStats-flag, for a given 'pid'. The SQLStats-flag (Process/Job) controls whether or not SQL Statistics are collected for each SQL Query execution, and which performance statistics to be collected. The SQLStats-flag is a colon (:) delimited string comprised of the following individual components: SQLStats-flag={action-flag}:{collect-flag}:{condition-flag}:{condition-value}:{terminate-flag} NOTE: This method ONLY applies to the SQLStats-flag for the given 'pid' (Process/Job) and NOT the SQLStats-flag for the (System)! RESTRICTION: This method invocation requires %Admin_Operate:Use privilege Invocation: This method can be invoked in the following ways: Object Script: $SYSTEM.SQL.Stats.Runtime.setFlagByPID(...) ##class(%SYSTEM.SQL.Stats.Runtime).setFlagByPID(...) SQL: SELECT %SYS_PTools.StatsSQL_SetSQLStatsFlagByPID(...) Examples: The following examples shows the use of this method: #1 Turn PTools ON to collects stats for all SQL modules for PID# 12345: set oldStats=##class(%SYSTEM.SQL.Stats.Runtime).setFlagByPID(12345,3) Parameters: pid - The process ID ($JOB) for which to set the SQLStats-flag [DEFAULT: Current $JOB] actionFlag - The portion of the SQLStats-flag which is represented by the 1st colon (:) piece, and can have one of the following values: -1 = Query Compilation: Turn SQLStats Off for the given 'pid' Query Execution: Turn SQLStats Off for the given 'pid' 0 = Query Compilation: Use the {action-flag} (System) setting Query Execution: Use the {action-flag} (System) setting 1 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Don't collect SQLStats for any Query Modules 2 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Collect SQLStats for MAIN Query Module (Start & Stop) 3 = Query Compilation: Generator SQLStats collection code for ALL Query Modules Query Execution: Collect SQLStats for ALL Query Modules collectFlag - The portion of the SQLStats-flag which is represented by the 2nd colon (:) piece, and is a numeric value representing which SQL Performance Statistics/Metrics to collect, with one of the following values: "" = Collect All SQL Performance Metrics [DEFAULT] n = Where n=SUM(MVal) [See Below] In general, all of the following performance metrics are collected for each SQL module, along with a cumulative value representing all SQL modules, when the SQLStats-flag is turned on: MVal Metric Name Metric Description ---- ---------------------------- ---------------------------------- 1 Query Execution Time Total number of seconds elapsed 2 Total Global References Total number of global references 4 Total Lines of Code Executed Total number of COS lines executed 8 Total Disk Read Latency Time Total milliseconds spent waiting ==== for Disk Reads 15 ALL Collect all Performance Metrics This parameter provides a mechanism by which to choose a combination of which performance metrics to collect, by specifying a SUM of the performance metrics MVal numbers (specified above) that you wish to collect. EXAMPLE #1: The following example represents the collection of 2 performance metrics, 'Query Execution Time' & 'Total Disk Read Latency Time': MVal Metric Name ---- ---------------------------- 1 Query Execution Time +8 Total Disk Read Latency Time === 9 Collect both of these performance metrics By passing 9 for the value of the 'collectFlag' parameter, the sum of the two aforementioned performance metrics MVal numbers, the SQLStats PTool application will only collect statistical data for these two performance metrics whenever an SQL Query is invoked. EXAMPLE #2: The following example represents the collection of 3 performance metrics, 'Query Execution Time', 'Total Global References' & 'Total Lines of Code Executed': MVal Metric Name ---- ---------------------------- 1 Query Execution Time 2 Total Global References +4 Total Lines of Code Executed === 7 Collect both of these performance metrics By passing 7 for the value of the 'collectFlag' parameter, the sum of the three aforementioned performance metrics MVal numbers, the SQLStats PTool application will only collect statistical data for these two performance metrics whenever an SQL Query is invoked. ptInfo - A Pass By Reference information array that returns to the user detailed information in the following format: ptInfo(category,variable)=value NOTE: This method currently returns no 'ptInfo', but is included for future extensibility RETURN: The value of the SQLStats-flag before setting it to the new value; Otherwise, return an error message if an error occurred

classmethod setFlagJob(actionFlag As %Integer = 0, collectFlag As %String = "", Output ptInfo As %ArrayOfObjects) as %Integer [ Language = objectscript ]

Method: setFlagJob [SQL: StatsSQL_SetSQLStatsFlagJob] Purpose: See SetSQLStatsJob() for details! Invocation: This method can be invoked in the following ways: Object Script: $SYSTEM.SQL.Stats.Runtime.setFlagJob(...) ##class(%SYSTEM.SQL.Stats.Runtime).setFlagJob(...) SQL: SELECT %SYS_PTools.StatsSQL_SetSQLStatsFlagJob(...)

classmethod setJob(actionFlag As %Integer = 0, collectFlag As %String = "", Output ptInfo As %ArrayOfObjects) as %Integer [ Language = objectscript ]

Method: setJob [SQL: StatsSQL_SetSQLStatsJob] Purpose: This method sets the flag that controls whether or not this Process/Job collects SQL Statistics about each run of a query You can invoke different levels of SQL Statistics collection by setting the SQLStats-flag, for your current Process/Job with this method. The SQLStats-flag controls whether or not SQL Statistics are collected for each SQL Query execution, and which performance statistics to collect. The SQLStats-flag is a colon (:) delimited string comprised of the following individual components: SQLStats-flag={action-flag}:{collect-flag}:{condition-flag}:{condition-value}:{terminate-flag} Invocation: This method can be invoked in the following ways: Object Script: $SYSTEM.SQL.Stats.Runtime.setJob(...) ##class(%SYSTEM.SQL.Stats.Runtime).setJob(...) SQL: SELECT %SYS_PTools.StatsSQL_SetSQLStatsJob(...) Examples: The following examples shows the use of this method: #1 Turn PTools ON to collects stats for all SQL modules: set oldStats=##class(%SYSTEM.SQL.Stats.Runtime).setJob(3) Parameters: actionFlag - The portion of the SQLStats-flag which is represented by the 1st colon (:) piece, and can have one of the following values: -1 = Query Compilation: Turn SQLStats Off for this Process/Job Query Execution: Turn SQLStats Off for this Process/Job [-1 ONLY when flagType=1] 0 = Query Compilation: Use the {action-flag} (System) setting Query Execution: Use the {action-flag} (System) setting 1 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Don't collect SQLStats for any Query Modules 2 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Collect SQLStats for MAIN Query Module (Start & Stop) 3 = Query Compilation: Generator SQLStats collection code for ALL Query Modules Query Execution: Collect SQLStats for ALL Query Modules returnActionFlag - 0 = Return the entire SQLStats-flag value [DEFAULT] 1 = Return only the {action-flag} value, which is the portion of the SQLStats-flag represented by the 1st colon (:) piece NOTE: This is the backward-compatible value collectFlag - The portion of the SQLStats-flag which is represented by the 2nd colon (:) piece, and is a numeric value representing which SQL Performance Statistics/Metrics to collect, with one of the following values: "" = Collect All SQL Performance Metrics [DEFAULT] n = Where n=SUM(MVal) [See Below] In general, all of the following performance metrics are collected for each SQL module, along with a cumulative value representing all SQL modules, when the SQLStats-flag is turned on: MVal Metric Name Metric Description ---- ---------------------------- ---------------------------------- 1 Query Execution Time Total number of seconds elapsed 2 Total Global References Total number of global references 4 Total Lines of Code Executed Total number of COS lines executed 8 Total Disk Read Latency Time Total milliseconds spent waiting ==== for Disk Reads 15 ALL Collect all Performance Metrics This parameter provides a mechanism by which to choose a combination of which performance metrics to collect, by specifying a SUM of the performance metrics MVal numbers (specified above) that you wish to collect. EXAMPLE #1: The following example represents the collection of 2 performance metrics, 'Query Execution Time' & 'Total Disk Read Latency Time': MVal Metric Name ---- ---------------------------- 1 Query Execution Time +8 Total Disk Read Latency Time === 9 Collect both of these performance metrics By passing 9 for the value of the 'collectFlag' parameter, the sum of the two aforementioned performance metrics MVal numbers, the SQLStats PTool application will only collect statistical data for these two performance metrics whenever an SQL Query is invoked. EXAMPLE #2: The following example represents the collection of 3 performance metrics, 'Query Execution Time', 'Total Global References' & 'Total Lines of Code Executed': MVal Metric Name ---- ---------------------------- 1 Query Execution Time 2 Total Global References +4 Total Lines of Code Executed === 7 Collect both of these performance metrics By passing 7 for the value of the 'collectFlag' parameter, the sum of the three aforementioned performance metrics MVal numbers, the SQLStats PTool application will only collect statistical data for these two performance metrics whenever an SQL Query is invoked. p4 - Placeholder Parameter for future extensibility ptInfo - A Pass By Reference information array that returns to the user detailed information in the following format: ptInfo(category,variable)=value Where category = { "current" | "terminate" | ... } variable = A variable that corresponds to the given 'category' Example: ptInfo("current","SQLStatsFlag")=The SQLStats-flag value after method execution RETURN: The value of the SQLStats-flag before setting it to the new value; Otherwise, return an error message if an error occurred

classmethod startGeneralStats(namespace As %String, routine As %String, mod As %String = 1) [ Language = objectscript ]
** General Stats **///

------------------------------------------------------------------------------- Method: startGeneralStats Replaces: N/A Status: Original Functionality Purpose: This method is used to START the PTools statistics collection Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.startGeneralStats(...) SQL: N/A Examples: The following examples shows the use of this method: #1 Start collecting stats to analyze the class 'Populate' method: do $SYSTEM.SQL.Stats.Runtime.startGeneralStats($NAMESPACE,"Test","Populate") do ##class(Sample.Person).Populate(100) do $SYSTEM.SQL.Stats.Runtime.stopGeneralStats($NAMESPACE,"Test","Populate") Data Storage: ^%sqlcq($NAMESPACE,"PTools","db","Stats{C|D|I|S|Z}") Parameters: namespace - The namespace in which the stats collection is occurring routine - The routine name for which the stats collection is occurring mod - The module name for which the stats collection is occurring [DEFAULT: 1] RETURN Value: (NONE) -------------------------------------------------------------------------------

classmethod stopGeneralStats(namespace As %String, routine As %String, mod As %String = 1) [ Language = objectscript ]

------------------------------------------------------------------------------- Method: stopGeneralStats Replaces: N/A Status: Original Functionality Purpose: This method is used to STOP the PTools statistics collection This method INSERTs the stats information into the %SYS_PTools.Stats table Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.stopGeneralStats(...) SQL: N/A Examples: The following examples shows the use of this method: #1 Stop collecting stats to analyze the class 'Populate' method: do $SYSTEM.SQL.Stats.Runtime.startGeneralStats($NAMESPACE,"Test","Populate") do ##class(Sample.Person).Populate(100) do $SYSTEM.SQL.Stats.Runtime.stopGeneralStats($NAMESPACE,"Test","Populate") Data Storage: ^%sqlcq($NAMESPACE,"PTools","db","Stats{C|D|I|S|Z}") Parameters: namespace - The namespace in which the stats collection is occurring routine - The routine name for which the stats collection is occurring mod - The module name for which the stats collection is occurring RETURN Value: (NONE) -------------------------------------------------------------------------------

classmethod tableScans(clearData As %Integer = 1, getSQLStmts As %Integer = 0, skipSysObj As %Integer = 1, skipInsStmts As %Integer = 1, display As %Integer = 1, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = Runtime_PT_tableScans ]

------------------------------------------------------------------------------ Q U E R I E S W I T H T A B L E S C A N S ------------------------------------------------------------------------------ For each of the SQL Statements saved in the %SYS.PTools.UtilSQLStatements class, identify all queries that do table scans. Table scans should be avoided if at all possible, but a table scan can't always be avoided. However, if a table has a large number of table scans, the indices defined for that table should be reviewed. Often the list of table scans and the list of temp indices will overlap; fixing one will remove the other. The result set lists the tables from largest Block Count to smallest Block Count. ------------------------------------------------------------------------------

------------------------------------------------------------------------------- Method: tableScans [SQL: PT_tableScans] Replaces: TableScans [SQL: TableScans] (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: This method uses the SQL Statement data stored in the %SYS.PTools.UtilSQLStatements class to pinpoint the queries that perform a table scan, which could be over an index or the master map. Pertinent information about these queries is subsequently stored in the the %SYS.PTools.UtilSQLAnalysisDB class for future processing and analysis. For some queries a table scan can't be avoided, but any query that uses such a scan should be reviewed in order to determine if an index could be added for performance optimizations. Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.tableScans(...) SQL: CALL %SYS_PTools.PT_tableScans(...) SELECT %SYS_PTools.PT_tableScans(...) 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 provide analytical information produced from the invocation of this method: #1 Show info from where the SQL Statements were collected: SELECT %EXACT(Type) AS QueryType, Count(*) AS QueryCount FROM %SYS_PTools.UtilSQLStatements GROUP BY Type OR SELECT * FROM %SYS_PTools.UtilSQLAnalysis_countSQLStmtTypes() #2 Show the data collected from the running of this method: SELECT SQLPointer->Type As "Routine Type", SQLPointer->Name As "Routine Name", SchemaName, TableName, ModuleName, ExtentSize, SQLPointer->SQLTextExt AS "SQL Text" FROM %SYS_PTools.UtilSQLAnalysisDB WHERE OptionName = 'TS' ORDER BY ExtentSize DESC OR SELECT * FROM %SYS_PTools.UtilSQLAnalysis_tableScans() OR SELECT SchemaName, TableName, Type, "Class/Routine Name", ModuleName, "Map Type" , ExtentSize, "Block Count", "SQL Text" FROM %SYS_PTools.UtilSQLAnalysis_tableScans() NOTE: These queries can be invoked from a number of tools, such as the SQL Query tool in the 'Management Portal' or via 'SQL Query Shell' within a 'InterSystems IRIS Terminal' as shown below: >do $SYSTEM.SQL.Shell() Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLAnlsys","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] getSQLStmts - 1 = Get all the SQL Statements from InterSystems IRIS locations (Cached Queries, Class Methods, Class Queries, MAC Routines) and add them to the %SYS.PTools.UtilSQLStatements class for additional processing of this method NOTE: This is an all inclusive call which could be time consuming. The %SYS.PTools.UtilSQLStatements class can populated via APIs that offer more precise collection alternatives which are less time consuming [DEFAULT: 0] skipSysObj - 1 = Skip all System Object (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo("insert-cnt")=The number of rows inserted in the %SYS.PTools.UtilSQLAnalysisDB class for queries that require a full table scan RETURN Value: The status from the invocation of this method -------------------------------------------------------------------------------

classmethod tempIndices(clearData As %Integer = 1, getSQLStmts As %Integer = 0, skipSysObj As %Integer = 1, skipInsStmts As %Integer = 1, display As %Integer = 1, ByRef ptInfo As %ArrayOfObjects) as %Status [ Language = objectscript, SQLProc = Runtime_PT_tempIndices ]

------------------------------------------------------------------------------ Q U E R I E S W I T H T E M P I N D I C E S ------------------------------------------------------------------------------ For each of the SQL Statements saved in the %SYS.PTools.UtilSQLStatements class, identify all queries that build temporary indices to resolve the SQL. Sometimes the use of a temp index is helpful and improves performance, for example building a small index based on a range condition that InterSystems IRIS can then use to read the master map in order. Sometimes a temp index is simply a subset of a different index and might be very efficient. Other times a temporary index degrades performance, for example scanning the master map to build a temporary index on a property that has a condition. This situation indicates that a needed index is missing; you should add an index to the class that matches the temporary index. The result set lists the tables from largest Block Count to smallest Block Count. ------------------------------------------------------------------------------

------------------------------------------------------------------------------- Method: tempIndices [SQL: PT_tempIndices] Replaces: TempIndices [SQL: TempIndices] (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: This method uses the SQL Statement data stored in the %SYS.PTools.UtilSQLStatements class to pinpoint the queries that are building a Temp Index/Table. Pertinent information about these queries is subsequently stored in the the %SYS.PTools.UtilSQLAnalysisDB class for future processing and analysis. It's worth noting that the results of this method and that of the 'tableScans()' method might have a large overlap. For some queries the creation of a Temp Index/Table can be avoided, but often the creation of a Temp Index/Table is done so to provide optimal data traversal and retrieval. These queries should be reviewed to determine if an index could be added to the class for performance optimizations, because often the structure of the Temp Index/Table is the basis for the creation of a class index to help with query performance. Invocation: This method can be invoked in the following ways: ObjectScript: $SYSTEM.SQL.Stats.Runtime.tempIndices(...) SQL: CALL %SYS_PTools.PT_tempIndices(...) SELECT %SYS_PTools.PT_tempIndices(...) 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 provide analytical information produced from the invocation of this method: #1 Show info from where the SQL Statements were collected: SELECT %EXACT(Type) AS QueryType, Count(*) AS QueryCount FROM %SYS_PTools.UtilSQLStatements GROUP BY Type OR SELECT * FROM %SYS_PTools.UtilSQLAnalysis_countSQLStmtTypes() #2 Show the data collected from the running of this method: SELECT SchemaName, TableName, $LISTTOSTRING(IndexFields) As "Index Fields", $LISTTOSTRING(DataValues) As "Data Fields", ExtentSize, BlockCount As "Block Count", SQLPointer->SQLTextExt AS "SQL Text" FROM %SYS_PTools.UtilSQLAnalysisDB WHERE OptionName = 'TI' ORDER BY ExtentSize DESC OR SELECT * FROM %SYS_PTools.UtilSQLAnalysis_tempIndices() OR SELECT SchemaName, TableName, Type, "Class/Routine Name", IndexName, "Index Fields", "Data Fields", ExtentSize, "Block Count", "SQL Text" FROM %SYS_PTools.UtilSQLAnalysis_tempIndices() NOTE: These queries can be invoked from a number of tools, such as the SQL Query tool in the 'Management Portal' or via 'SQL Query Shell' within a 'InterSystems IRIS Terminal' as shown below: >do $SYSTEM.SQL.Shell() Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLAnlsys","{C|D|I|S}") Parameters: clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] getSQLStmts - 1 = Get all the SQL Statements from InterSystems IRIS locations (Cached Queries, Class Methods, Class Queries, MAC Routines) and add them to the %SYS.PTools.UtilSQLStatements class for additional processing of this method NOTE: This is an all inclusive call which could be time consuming. The %SYS.PTools.UtilSQLStatements class can populated via APIs that offer more precise collection alternatives which are less time consuming [DEFAULT: 0] skipSysObj - 1 = Skip all System Object (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] display - 1 = Display messages while processing method [DEFAULT: 1] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo("insert-cnt")=The number of rows inserted in the %SYS.PTools.UtilSQLAnalysisDB class for queries that build temporary indices to resolve the SQL. RETURN Value: The status from the invocation of this method -------------------------------------------------------------------------------

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

Queries

query indexUsage(schema)
Selects SchemaName As %String, TableName As %String, IndexName As %String, Usage Count As %Integer
SQL Query:
SELECT %Exact(SchemaName), %Exact(Tablename), %Exact(IndexName), UsageCount AS "Usage Count" FROM %SYS_PTools.UtilSQLAnalysisDB WHERE OptionName = 'IU' AND (SchemaName = :schema OR :schema IS NULL) ORDER BY UsageCount

------------------------------------------------------------------------------- Query: indexUsage Replaces: IndexUsage (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Query to replicate Backward-Compatible Query it 'Replaces' Purpose: A query to return the results generated by the invocation of the 'indexUsage()' method... Parameters: schema - The schema of the table being queried in the SQL Statement to match against the 'SchemaName' property/field -------------------------------------------------------------------------------

query joinIndices(schema="", indexFlagInfo=0, skipJIF4=1)
Selects SchemaName As %String, TableName As %String, Type As %String, Class/Routine Name As %String, IndexName As %String, Index Needed As %String, Join Index Flag As %String, ExtentSize As %Integer, Block Count As %Integer, SQL Text As %String
SQL Query:
SELECT %Exact(SchemaName), %Exact(TableName), SQLPointer->Type, SQLPointer->Name, %Exact(IndexName), $LISTTOSTRING(JoinFields) AS "Index Needed", CASE WHEN :indexFlagInfo > 0 THEN %SYSTEM_SQL_Stats.Runtime_getIndexFlagInfo(JoinIndexFlag,'joinIndices',:indexFlagInfo) ELSE JoinIndexFlag END AS "Join Index Flag", ExtentSize, BlockCount AS "Block Count", SQLPointer->SQLTextExt AS "SQL Text" FROM %SYS_PTools.UtilSQLAnalysisDB WHERE OptionName = 'JI' AND (SchemaName = :schema OR :schema IS NULL) AND (JoinIndexFlag < 4 OR :skipJIF4 = 0) -- MRP907 (DON'T USE): GROUP BY JoinIndexFlag, SchemaName, TableName, IndexFields ORDER BY JoinIndexFlag DESC

------------------------------------------------------------------------------- Query: joinIndices Replaces: JoinIndices (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Query to replicate Backward-Compatible Query it 'Replaces' Purpose: A query to return the results generated by the invocation of the 'joinIndices()' method... Invocation: This Query can be invoked in the following way: SELECT * FROM %SYS_PTools.UtilSQLAnalysis_joinIndices() Parameters: schema - The schema of the table being queried in the SQL Statement to match against the 'SchemaName' property/field "" = All Schemas [DEFAULT] indexFlagInfo - The specific information that should be returned for the "Join Index Flag" field: 0 = The Index-Flag Internal Value [DEFAULT] 1 = The Index-Flag Title 2 = The Index-Flag Definition 3 = The Index-Flag as 'Title: Definition' skipJIF4 - 0 - Export all rows regardless of the 'JoinIndexFlag' value 1 - Export all rows where the value of the 'JoinIndexFlag' is not 4 [DEFAULT] -------------------------------------------------------------------------------

query loadAllIndices(schema)
Selects SchemaName As %String, TableName As %String, IndexName As %String, Usage Count As %Integer
SQL Query:
SELECT %Exact(SchemaName), %Exact(Tablename), %Exact(IndexName), UsageCount AS "Usage Count" FROM %SYS_PTools.UtilSQLAnalysisDB WHERE OptionName = 'IU' AND (SchemaName = :schema OR :schema IS NULL) ORDER BY UsageCount

------------------------------------------------------------------------------- Query: loadAllIndices Replaces: N/A Status: New Functionality Purpose: A query to return the results generated by the invocation of the 'getAllIndices()' method... Parameters: schema - The schema of the table being queried in the SQL Statement to match against the 'SchemaName' property/field -------------------------------------------------------------------------------

query loadAllSQLStmts()
Selects Type As %String, Class/Routine Name As %String, Method Name As %String, ImportPackage As %String, SQL Text As %String
SQL Query:
SELECT Type, Name, MethodName, ImportPackage, SQLTextExt FROM %SYS_PTools.UtilSQLStatements ORDER BY Type, Name

------------------------------------------------------------------------------- Query: loadAllSQLStmts Replaces: N/A Status: New Functionality Purpose: A query to return the results generated by the invocation of the 'loadAllSQLStmts()' method... Parameters: -------------------------------------------------------------------------------

query loadSQLStmts()
Selects Type As %String, Class/Routine Name As %String, Method Name As %String, ImportPackage As %String, SQL Text As %String
SQL Query:
SELECT Type, Name, MethodName, ImportPackage, SQLTextExt FROM %SYS_PTools.UtilSQLStatements ORDER BY Type, Name

------------------------------------------------------------------------------- Query: getSQLStmts Replaces: N/A Status: New Functionality Purpose: A query to return the results generated by the invocation of the 'getSQLStmts()' method... Parameters: N/A -------------------------------------------------------------------------------

query outlierIndices(schema="", indexFlagInfo=0, skipOIF4=1)
Selects SchemaName As %String, TableName As %String, Type As %String, Class/Routine Name As %String, IndexName As %String, Index Fields As %String, Bias Outlier As %Boolean, Outlier Index Flag As %String, Outlier Field As %String, Outlier Condition As %String, Outlier Selectivity As %Float, Outlier WHERE Condition As %String, ExtentSize As %Integer, Block Count As %Integer, SQL Text As %String
SQL Query:
SELECT %Exact(SchemaName), %Exact(TableName), SQLPointer->Type AS Type, SQLPointer->Name, %Exact(IndexName), $LISTTOSTRING(IndexFields) As "Index Fields", BiasQueriesAsOutlier AS "Bias Outlier", CASE WHEN :indexFlagInfo > 0 THEN %SYSTEM_SQL_Stats.Runtime_getIndexFlagInfo(OutlierIndexFlag,'outlierIndices',:indexFlagInfo) ELSE OutlierIndexFlag END AS "Outlier Index Flag", OutlierField AS "Outlier Fields", OutlierValue AS "Outlier Condition", OutlierSelectivity AS "Outlier Selectivity", WhereCondition AS "Outlier WHERE Condition", ExtentSize, BlockCount AS "Block Count", SQLPointer->SQLTextExt AS "SQL Text" FROM %SYS_PTools.UtilSQLAnalysisDB WHERE OptionName = 'OI' AND (SchemaName = :schema OR :schema IS NULL) AND (OutlierIndexFlag < 4 OR :skipOIF4 = 0) -- MRP907 (DON'T USE): GROUP BY OutlierIndexFlag, SchemaName, TableName, IndexFields ORDER BY OutlierIndexFlag DESC

------------------------------------------------------------------------------- Query: outlierIndices Replaces: N/A Status: New Functionality Purpose: A query to return the results generated by the invocation of the 'outlierIndices()' method... Invocation: This Query can be invoked in the following way: SELECT * FROM %SYS_PTools.UtilSQLAnalysis_outlierIndices() Parameters: schema - The schema of the table being queried in the SQL Statement to match against the 'SchemaName' property/field "" = All Schemas [DEFAULT] indexFlagInfo - The specific information that should be returned for the "Outlier Index Flag" field: 0 = The Index-Flag Internal Value [DEFAULT] 1 = The Index-Flag Title 2 = The Index-Flag Definition 3 = The Index-Flag as 'Title: Definition' skipOIF4 - 0 - Export all rows regardless of the 'OutlierIndexFlag' value 1 - Export all rows where the value of the 'OutlierIndexFlag' is not 4 [DEFAULT] -------------------------------------------------------------------------------

query tableScans(schema)
Selects SchemaName As %String, TableName As %String, Type As %String, Class/Routine Name As %String, ModuleName As %String, Map Type As %String, ExtentSize As %Integer, Block Count As %Integer, SQL Text As %String
SQL Query:
SELECT %Exact(SchemaName), %Exact(TableName), SQLPointer->Type, SQLPointer->Name, ModuleName, MapType AS "Map Type", ExtentSize, BlockCount AS "Block Count", SQLPointer->SQLTextExt AS "SQL Text" FROM %SYS_PTools.UtilSQLAnalysisDB WHERE OptionName = 'TS' AND (SchemaName = :schema OR :schema IS NULL) ORDER BY BlockCount DESC

------------------------------------------------------------------------------- Query: tableScans Replaces: TableScans (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: A query to return the results generated by the invocation of the 'tableScans()' method... Parameters: schema - The schema of the table being queried in the SQL Statement to match against the 'SchemaName' property/field -------------------------------------------------------------------------------

query tempIndices(schema)
Selects SchemaName As %String, TableName As %String, Type As %String, Class/Routine Name As %String, IndexName As %String, Index Fields As %String, Data Fields As %String, ExtentSize As %Integer, Block Count As %Integer, SQL Text As %String
SQL Query:
SELECT %Exact(SchemaName), %Exact(TableName), SQLPointer->Type, SQLPointer->Name, %Exact(IndexName), $LISTTOSTRING(IndexFields) As "Index Fields", $LISTTOSTRING(DataValues) As "Data Fields", ExtentSize, BlockCount AS "Block Count", SQLPointer->SQLTextExt AS "SQL Text" FROM %SYS_PTools.UtilSQLAnalysisDB WHERE OptionName = 'TI' AND (SchemaName = :schema OR :schema IS NULL) ORDER BY BlockCount DESC

------------------------------------------------------------------------------- Query: tempIndices Replaces: TempIndices (%SYS.PTools.SQLUtilities) [DEPRECATED] Status: New Query to replicate Backward-Compatible Query it 'Replaces' Purpose: A query to return the results generated by the invocation of the 'tempIndices()' method... Parameters: schema - The schema of the table being queried in the SQL Statement to match against the 'SchemaName' property/field -------------------------------------------------------------------------------

Inherited Members

Inherited Methods (Including Private)