|
%SYS
|
|
|
%SYS
|
|
<style type="text/css"> DEP { color: red; font-weight: bold; } BC { color: green; font-weight: bold; } .dep-head { color: red; white-space: pre; font-family: 'Courier New'; } .info-head { white-space: pre; font-family: 'Courier New'; } </style> More...
Public Attributes | |
| ImportPackage | |
| comma delimited list of Package names to use compiling queries More... | |
| Name | |
| More... | |
| SQLText | |
| More... | |
| Type | |
| More... | |
<style type="text/css"> DEP { color: red; font-weight: bold; } BC { color: green; font-weight: bold; } .dep-head { color: red; white-space: pre; font-family: 'Courier New'; } .info-head { white-space: pre; font-family: 'Courier New'; } </style>
– DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED –
Starting with ver '2.0' of the PTools application, this class is considered to be DEPRECATED and is replaced by the <class>SYS.PTools.UtilSQLStatements</class> class.
– DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED –
Class: SYS.PTools.SQLUtilities Replaced By: <class>SYS.PTools.UtilSQLStatements</class> Purpose:
This class stores the SQL Statement details collected from the following locations by invoking the 'GetSQLStatements()' method:
Data Storage: ^sqlcq($NAMESPACE,"PTools","Utils","Queries","{C|D|I|S}")
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: ChangeCost Replaced By: changeCost (<class>SYS.PTools.StatsSQL</class>) Purpose: * INTERNAL USE ONLY * Status: <BC>Maintained for Backward-Compatibility</BC> DETAILS: For additional details see the 'Replaced By' Class & Method!
|
static |
Backward-compatible Entry Point:
NOTE: See the <method>clearSQLUtilStmtResults</method> method above for more details.
|
static |
Backward-compatible Entry Point:
NOTE: See the <method>clearSQLUtilStmts</method> method above for more details.
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: GetSQLStatements Replaced By: getSQLStmts SQL: PT_getSQLStmts It is recommended that you use one of the following new methods which replaces the functionality of this method:
=> getSQLStmts(...) [Cached Queries|Class Methods|Class Queries|MAC Routines] => getAllSQLStmts(...) [Cached Queries|Class Methods|Class Queries|MAC Routines]
=> getAllCachedQrySQLStmts(...) [Cached Queries] => getCachedQrySQLStmtsByDays(...) [Cached Queries] => getCachedQrySQLStmtsByClass(...) [Cached Queries] => getAllClassMethSQLStmts(...) [Class Methods] => getClassMethSQLStmtsByClass(...) [Class Methods] => getAllClassQrySQLStmts(...) [Class Queries] => getClassQrySQLStmtsByClass(...) [Class Queries] => getAllRtnQrySQLStmts(...) [MAC Routines] => getRtnQrySQLStmtsByRtn(...) [MAC Routines] Status: <BC>Maintained for Backward-Compatibility</BC> Purpose: This method searches for at all of the SQL Statements in a namespace from the following locations and adds information about the statements (e.g. Type, Name, SQLText) to the 'SYS.PTools.SQLUtilities' class for additional processing:
Invocation: This method can be invoked in the following ways: ObjectScript: ##class(SYS.PTools.SQLUtilities).GetSQLStatements(...) SQL: CALL SYS_PTools.GetSQLStatements(...) SELECT SYS_PTools.GetSQLStatements(...)
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.SQLUtilities
GROUP BY Type
OR
SELECT * FROM %SYS_PTools.SQLUtilities_FindSQL()
#2 Show the data collected from the running of this method:
SELECT Type, Name, ImportPackage,
SQLText AS "SQL Text"
FROM %SYS_PTools.SQLUtilities
ORDER BY Type, Name
-- This returns "SQL Text" in External Format
OR
SELECT * FROM %SYS_PTools.SQLUtilities_GetSQLStatements()
-- This returns "SQL Text" in Logical Format
OR
SELECT * FROM %SYS_PTools.SQLUtilities_GetSQLStatements(1)
-- This returns "SQL Text" in External Format
OR
SELECT Type, "Class/Routine Name", ImportPackage,
%SYS_PTools.PT_streamAsText("SQL Text")
FROM %SYS_PTools.SQLUtilities_GetSQLStatements()
-- This returns "SQL Text" in External Format
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","Utils","Queries","{C|D|I|S}")
Parameters: cachedQueries - 1 = Get the SQL Statements from all Cached Queries [DEFAULT: 1] classQueries - 1 = Get the SQL Statements from all of the Class Queries [DEFAULT: 1] classMethods - 1 = Get the SQL Statements from all of the Class Methods [DEFAULT: 1] routines - 1 = Get the Embedded SQL Statements from all of the MAC Routines [DEFAULT: 1] SystemTables - 0 = Skip all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 1 = Get/Process all System Objects (Classes & Routines) 2 = Skip all System Objects that are not defined in the namespace in which this method is invoked 3 = Skip all System Objects defined by InterSystems, even if the object is also defined in the namespace in which this method is invoked NOTE: Pass the combination of the specified options if more than one option desired (e.g. 30 => #3 & #0) [DEFAULT: 0] Display - 1 = Display messages while processing method [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [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.
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: IndexUsage [SQL: IndexUsage] Replaced By: indexUsage SQL: PT_indexUsage Status: <BC>Maintained for Backward-Compatibility</BC> Purpose: This method uses the SQL Statement data stored in the 'SYS.PTools.SQLUtilities' 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 <class>SYS.PTools.SQLUtilResults</class> 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: ##class(SYS.PTools.SQLUtilities).IndexUsage(...) SQL: CALL SYS_PTools.IndexUsage(...) SELECT SYS_PTools.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.SQLUtilities
GROUP BY Type
OR
SELECT * FROM %SYS_PTools.SQLUtilities_FindSQL()
#2 Show the data collected from the running of this method:
SELECT * FROM %SYS_PTools.SQLUtilities_IndexUsage()
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","Utils","Queries","{C|D|I|S}")
Parameters: PopTable - 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.SQLUtilities' class for additional processing of this method [DEFAULT: 0*]
RETURN Value: The status from the invocation of this method
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: JoinIndices [SQL: JoinIndices] Replaced By: joinIndices SQL: PT_joinIndices Status: <BC>Maintained for Backward-Compatibility</BC> Purpose: This method uses the SQL Statement data stored in the 'SYS.PTools.SQLUtilities' 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 <class>SYS.PTools.SQLUtilResults</class> 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 <class>SYS.PTools.SQLUtilResults</class> 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 4 values: 0 - No index to support the join. (Some version of the suggested index should be created to improve this query's performance) 1 - There is an index to support the join but it does not contain all the join fields. (This will produce poor performance and for that reason is rarely used) 2 - There is an index to support the join but it is not an exact match - The first index field is not part of the join. (This might produce OK performance, but improvements should be made) 3 - There is an index to support the join but it is not an exact match - The first index field is part of the join but there are additional fields in the index) (This will produce OK performance, but improvements can be made) 4 - Index fully supports the join.
NOTE: The 'IndexFields' property would be an index we think could help improve performance.
Invocation: This method can be invoked in the following ways: ObjectScript: ##class(SYS.PTools.SQLUtilities).JoinIndices(...) SQL: CALL SYS_PTools.JoinIndices(...) SELECT SYS_PTools.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.SQLUtilities
GROUP BY Type
OR
SELECT * FROM %SYS_PTools.SQLUtilities_FindSQL()
#2 Show the data collected from the running of this method:
SELECT SchemaName, TableName,
$LISTTOSTRING(IndexFields) As "Index Needed",
JoinIndexFlag As "Join Index Flag",
ExtentSize, BlockCount As "Block Count",
SQLPointer->SQLText AS "SQL Text"
FROM %SYS_PTools.SQLUtilResults
WHERE OptionName = 'JI'
ORDER BY 1,2
-- This returns "SQL Text" in External Format
OR
SELECT * FROM %SYS_PTools.SQLUtilities_JoinIndices()
-- This returns "SQL Text" in Logical Format
OR
SELECT * FROM %SYS_PTools.SQLUtilities_JoinIndices(,1)
-- This returns "SQL Text" in External Format
OR
SELECT SchemaName, TableName, Type, "Class/Routine Name",
IndexName, "Index Needed", "Join Index Flag",
ExtentSize, "Block Count",
%SYS_PTools.PT_streamAsText("SQL Text")
FROM %SYS_PTools.SQLUtilities_JoinIndices()
-- This returns "SQL Text" in External Format
#3 Show joined-based tables with less than optimal indexes that support the JOIN specified in the query:
SELECT SchemaName, TableName, IndexFields As "Index Needed", JoinIndexFlag AS "Join Index Flag", COUNT(*) AS "Query Count" FROM SYS_PTools.SQLUtilResults 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","Utils","Queries","{C|D|I|S}")
Parameters: PopTable - 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.SQLUtilities' class for additional processing of this method [DEFAULT: 1] SystemTables - 0 = Skip all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 1 = Get/Process all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 2 = Skip all System Objects that are not defined in the namespace in which this method is invoked 3 = Skip all System Objects defined by InterSystems, even if the object is also defined in the namespace in which this method is invoked NOTE-1: Pass the combination of the specified options if more than one option desired (e.g. 30 => #3 & #0) NOTE-2: This only applies when retrieving SQL Statements (PopTable=1) [DEFAULT: 0] IgnoreEns - ??? Display - 1 = Display messages while processing method [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo("update-cnt")=The number of rows inserted in the <class>SYS.PTools.SQLUtilResults</class> class for queries that have joins indexing issues
RETURN Value: The status from the invocation of this method
RETURN Value: The number of rows inserted in the <class>SYS.PTools.SQLUtilResults</class> class for queries that have joins indexing issues; Otherwise, return an error message if an error occurred
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: PossiblePlans Replaced By: possiblePlans (<class>SYS.PTools.StatsSQL</class>) Status: <BC>Maintained for Backward-Compatibility</BC> DETAILS: For additional details see the 'Replaced By' Class & Method!
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: PossiblePlansClose Replaced By: possiblePlansClose (<class>SYS.PTools.StatsSQL</class>) Status: <BC>Maintained for Backward-Compatibility</BC> DETAILS: For additional details see the 'Replaced By' Class & Method!
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: PossiblePlansExecute Replaced By: possiblePlansExecute (<class>SYS.PTools.StatsSQL</class>) Status: <BC>Maintained for Backward-Compatibility</BC> DETAILS: For additional details see the 'Replaced By' Class & Method!
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: PossiblePlansFetch Replaced By: possiblePlansFetch (<class>SYS.PTools.StatsSQL</class>) Status: <BC>Maintained for Backward-Compatibility</BC> DETAILS: For additional details see the 'Replaced By' Class & Method!
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: PossiblePlansStatsClose Replaced By: possiblePlansStatsClose (<class>SYS.PTools.StatsSQL</class>) Status: <BC>Maintained for Backward-Compatibility</BC> DETAILS: For additional details see the 'Replaced By' Class & Method!
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: PossiblePlansStatsExecute Replaced By: possiblePlansStatsExecute (<class>SYS.PTools.StatsSQL</class>) Status: <BC>Maintained for Backward-Compatibility</BC> DETAILS: For additional details see the 'Replaced By' Class & Method!
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: PossiblePlansStatsFetch Replaced By: possiblePlansStatsFetch (<class>SYS.PTools.StatsSQL</class>) Status: <BC>Maintained for Backward-Compatibility</BC> DETAILS: For additional details see the 'Replaced By' Class & Method!
|
static |
Method: SQLTextLogicalToDisplay Replaced By: N/A Status: New Functionality Purpose: Converts the value of the parameter 'val', which represents the 'SQLText' property in logical format (Stream.GlobalCharacter), into a string value (Library.String) and return that string.
Examples: The following examples show how to return the 'SQLText' property in different formats:
#1 Show 'SQLText' as a Stream ID:
SELECT ID, %EXACT(Type) AS QueryType, %Internal(SQLText)
FROM %SYS_PTools.SQLUtilities
#2 Show 'SQLText' as a String via the 'Execute Query' interface of the 'Management Portal':
SELECT ID, EXACT(Type) AS QueryType, SQLText FROM SYS_PTools.SQLUtilities
#3 Show 'SQLText' as a String via Embedded SQL:
#SQLCompile Select=Display
&sql(DECLARE sqlCUR CURSOR FOR
SELECT ID, %EXACT(Type) AS QueryType, SQLText
FROM %SYS_PTools.SQLUtilities
)
Parameters: val - The value of the 'SQLText' property in logical format (Stream.GlobalCharacter)
RETURN Value: The SQL Statement Text in (Library.String) format; Otherwise, return an error message if an error occurred
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: TableScans [SQL: TableScans] Replaced By: tableScans SQL: PT_tableScans Status: <BC>Maintained for Backward-Compatibility</BC> Purpose: This method uses the SQL Statement data stored in the 'SYS.PTools.SQLUtilities' 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 <class>SYS.PTools.SQLUtilResults</class> 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: ##class(SYS.PTools.SQLUtilities).TableScans(...) SQL: CALL SYS_PTools.TableScans(...) SELECT SYS_PTools.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.SQLUtilities
GROUP BY Type
OR
SELECT * FROM %SYS_PTools.SQLUtilities_FindSQL()
#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->SQLText AS "SQL Text"
FROM %SYS_PTools.SQLUtilResults
WHERE OptionName = 'TS'
ORDER BY ExtentSize DESC
OR
SELECT * FROM %SYS_PTools.SQLUtilities_TableScans()
-- This returns "SQL Text" in Logical Format
OR
SELECT * FROM %SYS_PTools.SQLUtilities_TableScans(,1)
-- This returns "SQL Text" in External Format
OR
SELECT SchemaName, TableName, Type, "Class/Routine Name",
ModuleName, "Map Type" , ExtentSize, "Block Count",
%SYS_PTools.PT_streamAsText("SQL Text")
FROM %SYS_PTools.SQLUtilities_TableScans()
-- This returns "SQL Text" in External Format
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","Utils","Queries","{C|D|I|S}")
Parameters: PopTable - 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.SQLUtilities' class for additional processing of this method [DEFAULT: 1] SystemTables - 0 = Skip all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 1 = Get/Process all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 2 = Skip all System Objects that are not defined in the namespace in which this method is invoked 3 = Skip all System Objects defined by InterSystems, even if the object is also defined in the namespace in which this method is invoked NOTE-1: Pass the combination of the specified options if more than one option desired (e.g. 30 => #3 & #0) NOTE-2: This only applies when retrieving SQL Statements (PopTable=1) [DEFAULT: 0] IgnoreEns - ??? Display - 1 = Display messages while processing method [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo("update-cnt")=The number of rows inserted in the <class>SYS.PTools.SQLUtilResults</class> class for queries that require a full table scan
RETURN Value: The status from the invocation of this method
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: TempIndices [SQL: TempIndices] Replaced By: tempIndices SQL: PT_tempIndices Status: <BC>Maintained for Backward-Compatibility</BC> Purpose: This method uses the SQL Statement data stored in the 'SYS.PTools.SQLUtilities' class to pinpoint the queries that are building a Temp Index/Table.
Pertinent information about these queries is subsequently stored in the the <class>SYS.PTools.SQLUtilResults</class> 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: ##class(SYS.PTools.SQLUtilities).TempIndices(...) SQL: CALL SYS_PTools.TempIndices(...) SELECT SYS_PTools.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.SQLUtilities
GROUP BY Type
OR
SELECT * FROM %SYS_PTools.SQLUtilities_FindSQL()
#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->SQLText AS "SQL Text"
FROM %SYS_PTools.SQLUtilResults
WHERE OptionName = 'TI'
ORDER BY ExtentSize DESC
-- This returns "SQL Text" in External Format
OR
SELECT * FROM %SYS_PTools.SQLUtilities_TempIndices()
-- This returns "SQL Text" in Logical Format
OR
SELECT * FROM %SYS_PTools.SQLUtilities_TempIndices(,1)
-- This returns "SQL Text" in External Format
OR
SELECT SchemaName, TableName, Type, "Class/Routine Name",
IndexName, "Index Fields", "Data Fields",
ExtentSize, "Block Count",
%SYS_PTools.PT_streamAsText("SQL Text")
FROM %SYS_PTools.SQLUtilities_TempIndices()
-- This returns "SQL Text" in External Format
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","Utils","Queries","{C|D|I|S}")
Parameters: PopTable - 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.SQLUtilities' class for additional processing of this method [DEFAULT: 1] SystemTables - 0 = Skip all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 1 = Get/Process all System Objects (Classes & Routines) that start with: { % | DeepSee | Ens | HS | INFORMATION{.|_}SCHEMA } 2 = Skip all System Objects that are not defined in the namespace in which this method is invoked 3 = Skip all System Objects defined by InterSystems, even if the object is also defined in the namespace in which this method is invoked NOTE-1: Pass the combination of the specified options if more than one option desired (e.g. 30 => #3 & #0) NOTE-2: This only applies when retrieving SQL Statements (PopTable=1) [DEFAULT: 0] IgnoreEns - ??? Display - 1 = Display messages while processing method [DEFAULT: 1] skipInsStmts - 1 = Skip INSERT Statements, as they don't have looping logic [DEFAULT: 1] clearData - 1 = Kill all of the existing data before re-populating with the results of this method invocation [DEFAULT: 1] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo("update-cnt")=The number of rows inserted in the <class>SYS.PTools.SQLUtilResults</class> class for queries that build temporary indices to resolve the SQL
RETURN Value: The status from the invocation of this method
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: clearSQLUtilStmtResults [SQL: PT_clearSQLUtilStmtResults] Replaces: ClearResults [SQL: ClearResults (<class>SYS.PTools.SQLUtilities</class>) <DEP>[DEPRECATED]</DEP> Status: New Functionality Purpose: Delete all of the data stored in the <class>SYS.PTools.SQLUtilResults</class> class, based on the specified parameters...
Invocation: This method can be invoked in the following ways: ObjectScript: ##class(SYS.PTools.SQLUtilities).clearSQLUtilStmtResults(...) SQL: CALL SYS_PTools.clearSQLUtilStmtResults(...) SELECT SYS_PTools.clearSQLUtilStmtResults(...)
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=##class(SYS.PTools.SQLUtilities).clearSQLUtilStmtResults()
Data Storage: ^sqlcq($NAMESPACE,"PTools","Utils","Results","{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.SQLUtilResults [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","clearSQLUtilStmtResults")=The number of rows deleted via this method [Routine Method] ptInfo("cnt","ClearResults")=The number of rows deleted via this method [Class Method]
RETURN Value: Based on the value of the 'returnType' parameter, return one of the following: 0: Return a Status code of either $$$OK or $$$ERROR()
1: Return the number of Stats rows deleted from the following class; Otherwise, return an error message if an error occurred: SYS.PTools.SQLUtilResults
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: clearSQLUtilStmts [SQL: PT_clearSQLUtilStmts] Replaces: ClearStatements SQL: ClearStatements <DEP>[DEPRECATED]</DEP> Status: New Functionality Purpose: Delete all of the data stored in the 'SYS.PTools.SQLUtilities' class, based on the specified parameters...
Invocation: This method can be invoked in the following ways: ObjectScript: ##class(SYS.PTools.SQLUtilities).clearSQLUtilStmts(...) SQL: CALL SYS_PTools.clearSQLUtilStmts(...) SELECT SYS_PTools.clearSQLUtilStmts(...)
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=##class(SYS.PTools.SQLUtilities).clearSQLUtilStmts()
Data Storage: ^sqlcq($NAMESPACE,"PTools","Utils","Queries","{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.SQLUtilities [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","clearSQLUtilStmts")=The number of rows deleted via this method [Routine Method] ptInfo("cnt","ClearStatements")=The number of rows deleted via this method [Class Method]
RETURN Value: Based on the value of the 'returnType' parameter, return one of the following: 0: Return a Status code of either $$$OK or $$$ERROR()
1: Return the number of Stats rows deleted from the following class; Otherwise, return an error message if an error occurred: SYS.PTools.SQLUtilities
|
static |
Method: exportSQLUtilStmts [SQL: PT_exportSQLUtilStmts] Replaced By: exportUtilSQLStatements SQL: PT_exportUtilSQLStatements Status: New Functionality Purpose: Create a file containing all the data from the <class>SYS.PTools.SQLUtilities</class> class & return the output location... Invocation: This method can be invoked in the following ways: ObjectScript: ##class(SYS.PTools.SQLUtilities).exportSQLUtilStmts(...) SQL: CALL SYS_PTools.PT_exportSQLUtilStmts(...) SELECT SYS_PTools.PT_exportSQLUtilStmts(...)
NOTE: This 'SqlProc' method can be invoked with either the CALL-interface or the SELECT-interface. Both interfaces execute the method: the CALL-interface does not return any resulting value, while the SELECT-interface does.
Examples: The following examples shows the use of this method:
#1 Display this export file to the screen via a InterSystems IRIS Terminal:
set tSC=##class(%SYS.PTools.SQLUtilities).exportSQLUtilStmts($IO)
#2 Display this export file via the SQL Query Page of the Management Portal:
CALL SYS_PTools.PT_exportSQLUtilStmts('$IO','H')
Where: '$IO' = Output to the current device NOTE: '$IO' can be omitted, as it is the default 'file' when invoked via the CALL interface 'H' = Output the format in HTML format NOTE: 'H' can be omitted, as it is the default 'format' when invoked via the CALL interface
NOTE: This method has been added to this DEPRECATED class to correspond with the newly created 'export' method defined for the new & improved interface in the 'SYS.PTools.UtilSQLAnalysis' class
Parameters: file - The path and file in which to create and store the data from the SYS.PTools.SQLUtilities 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_exportSQLUtilStmts_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=<data-format>[:$LB(<delim>,<csv-header>,<csv-footer>)] Where the constituent elements have the following values: <data-format> - The format of the Export/Reporting file:
conds=<full condition> OR conds(0)=pos Count (WHERE pos = {1...n}) conds(pos)=$LIST() Pieces: 1) [<logical operator>] (Assumed Default: &&) 2) [{Heading}] (Omit for override cond) 3) <simple condition> | <complex condition>
WHERE: <full condition> := <full condition expression> <logical operator> := {&& | ||} (&& = AND | || = OR) <simple condition>@* := <oper>
<complex condition>@ := <complex condition expression>
@ = Contains no references to {Heading}
EXAMPLE: <full condition> conds="("",INFO,MAIN,""[("",""_{Module}_"",""))||({GlobalRefs}>20)" <simple condition> conds(0)=3 conds(1)=$LB(,"Module","=""INFO""") conds(2)=$LB("||","Module","=""MAIN""") conds(3)=$LB("||","GlobalRefs",">20") <complex condition> 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 <override condition>s, can be included as <complex condition>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
|
static |
Method: exportSQLUtilities [SQL: PT_exportSQLUtilities] Replaced By: exportUtilSQLAnalysis SQL: PT_exportUtilSQLAnalysis Status: New Functionality Purpose: Create a file containing all the data from joining both the <class>SYS.PTools.SQLUtilities</class> and <class>SYS.PTools.SQLUtilResults</class> classes & return the output location... Invocation: This method can be invoked in the following ways: ObjectScript: ##class(SYS.PTools.SQLUtilities).exportSQLUtilities(...) SQL: CALL SYS_PTools.PT_exportSQLUtilities(...) SELECT SYS_PTools.PT_exportSQLUtilities(...)
NOTE: This 'SqlProc' method can be invoked with either the CALL-interface or the SELECT-interface. Both interfaces execute the method: the CALL-interface does not return any resulting value, while the SELECT-interface does.
Examples: The following examples shows the use of this method:
#1 Display this export file to the screen via a InterSystems IRIS Terminal:
set tSC=##class(%SYS.PTools.SQLUtilities).exportSQLUtilities($IO)
#2 Display this export file via the SQL Query Page of the Management Portal:
CALL SYS_PTools.PT_exportSQLUtilities('$IO','H')
Where: '$IO' = Output to the current device NOTE: '$IO' can be omitted, as it is the default 'file' when invoked via the CALL interface 'H' = Output the format in HTML format NOTE: 'H' can be omitted, as it is the default 'format' when invoked via the CALL interface
NOTE: This method has been added to this DEPRECATED class to correspond with the newly created 'export' method defined for the new & improved interface in the 'SYS.PTools.UtilSQLAnalysis' class
Parameters: file - The path and file in which to create and store the data from the SYS.PTools.SQLUtilities & SYS.PTools.SQLUtilResults 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_exportSQLUtilities_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=<data-format>[:$LB(<delim>,<csv-header>,<csv-footer>)] Where the constituent elements have the following values: <data-format> - The format of the Export/Reporting file:
conds - A string/array of possible conditions by which to restrict the output of the Export file, in the following format:
conds=<full condition> OR conds(0)=pos Count (WHERE pos = {1...n}) conds(pos)=$LIST() Pieces: 1) [<logical operator>] (Assumed Default: &&) 2) [{Heading}] (Omit for override cond) 3) <simple condition> | <complex condition>
WHERE: <full condition> := <full condition expression> <logical operator> := {&& | ||} (&& = AND | || = OR) <simple condition>@* := <oper>
<complex condition>@ := <complex condition expression>
@ = Contains no references to {Heading}
EXAMPLE: <full condition> conds="("",INFO,MAIN,""[("",""_{Module}_"",""))||({GlobalRefs}>20)" <simple condition> conds(0)=3 conds(1)=$LB(,"Module","=""INFO""") conds(2)=$LB("||","Module","=""MAIN""") conds(3)=$LB("||","GlobalRefs",">20") <complex condition> 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 <override condition>s, can be included as <complex condition>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 - 1 = Include the SQL Text in the output as the last field [DEFAULT: 1] 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
|
static |
<DEP> – DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED — DEPRECATED – </DEP> Method: map Replaced By: map (<class>SYS.PTools.StatsSQL</class>) Purpose: * INTERNAL USE ONLY * Status: <BC>Maintained for Backward-Compatibility</BC> DETAILS: For additional details see the 'Replaced By' Class & Method!
|
static |
Method: streamAsText [SQL: PT_streamAsText] Replaced By: N/A Status: New Functionality Purpose: This method is passed a valid Stream ID based on the Stream.GlobalCharacter datatype and returns the stream text as a single String line (e.g. a SQL Statement)
Invocation: This method can be invoked in the following ways: ObjectScript: ##class(SYS.PTools.SQLUtilities).streamAsText({streamID}) SQL: SELECT SYS_PTools.PT_streamAsText({streamID}) Where {streamID} is a valid Stream ID
Examples: The following examples shows the use of this method:
#1 Show all SQL Statements in this class:
SELECT ID, %EXACT(Type) AS QueryType,
%SYS_PTools.PT_streamAsText(SQLText) AS SQLText
FROM %SYS_PTools.SQLUtilities
Parameters: streamID - A valid Stream ID based on the Stream.GlobalCharacter datatype
RETURN Value: The SQL Statement Text for the given 'streamID'; Otherwise, return an error message if an error occurred
| ImportPackage |
comma delimited list of Package names to use compiling queries
| Name |
| SQLText |
| Type |
1.9.1