|
IRISLIB database
|
|
|
IRISLIB database
|
|
<style type="text/css"> .info-head { color: black; margin-top: -20px; white-space: pre; display: block; } IHD { margin-left: 26px; margin-top: -20px; white-space: pre; display: block; } IHP { margin-top: -20px; white-space: pre; display: block; font-weight: 600; font-family: 'Courier New'; } </style> More...
<style type="text/css"> .info-head { color: black; margin-top: -20px; white-space: pre; display: block; } IHD { margin-left: 26px; margin-top: -20px; white-space: pre; display: block; } IHP { margin-top: -20px; white-space: pre; display: block; font-weight: 600; font-family: 'Courier New'; } </style>
The <class>SYSTEM.SQL</class> class can used via the special $SYSTEM object:
Do $SYSTEM.SQL.CurrentSettings()
You can call help to get a list of all entry points:
Do $SYSTEM.SQL.Help()
|
static |
<style type="text/css"> .info-head { color: black; margin-top: -20px; white-space: pre; display: block; } IHD { margin-left: 26px; margin-top: -20px; white-space: pre; display: block; } IHP { margin-top: -20px; white-space: pre; display: block; font-weight: 600; font-family: 'Courier New'; } </style>
The <class>SYSTEM.SQL</class> class can used via the special $SYSTEM object:
Do $SYSTEM.SQL.CurrentSettings()
You can call help to get a list of all entry points:
Do $SYSTEM.SQL.Help()
THIS METHOD IS DEPRECATED. Refer to ABS() in <class>SYSTEM.SQL.Functions</class> instead.
ABS returns the absolute value, which is always zero or a positive number
$SYSTEM.SQL.ABS(numeric-expression)
ABS returns the same data type as numeric-expression. If numeric-expression is not a number (for example, the string 'abc') ABS returns 0. ABS returns NULL when passed a NULL value.
|
static |
THIS METHOD IS DEPRECATED.
Refer to ALPHAUP() in <class>SYSTEM.SQL.Functions</class> instead.
ALPHAUP returns the Alphaup collation of the passed in value.
|
static |
THIS METHOD IS DEPRECATED.
Refer to CEILING() in <class>SYSTEM.SQL.Functions</class> instead.
CEILING is a scalar numeric function that returns the smallest integer greater than or equal to a given numeric expression
$SYSTEM.SQL.CEILING(numeric-expression)
|
static |
THIS METHOD IS DEPRECATED.
Refer to CONVERT() in <class>SYSTEM.SQL.Functions</class> instead.
CONVERT is a SQL function that converts a given expression to a specified data type.
$SYSTEM.SQL.CONVERT(expr,convto,convfrom)
CONVERT is a SQL function that converts expression expr from type convfrom to the specified data type convto.
The convfrom value is expected to be a Logical value for SQL_DATE and SQL_TIME. When converting from SQL_DATE, SQL_POSIXTIME, or SQL_TIME to SQL_TIMESTAMP, the input value is expected to be a logical <class>Library.Date</class>, <class>Library.PosixDate</class>, or <class>Library.Time</class> value. When converting from SQL_VARCHAR to SQL_TIME, the input value is expected to be an ODBC <class>Library.Time</class> formatted value. When converting from SQL_VARCHAR to SQL_DATE, the input value is expected to be an ODBC <class>Library.Date</class> formatted value. When converting from SQL_DATE, SQL_POSIXTIME, or SQL_TIME to SQL_VARCHAR, the returned value will contain the ODBC format of the <class>Library.Date</class>, <class>Library.PosixTime</class>, or <class>Library.Time</class> value.
|
static |
Cancel the SQL Query running on the specified process (pid) and referenced by the specified SQL Statement Identifier (SQLStatementID)...
Parameters:
Returns: Status Code
Invocation:
| Object Script: | $SYSTEM.SQL.CancelQuery(pid [, SQLStatementID] [, timeout]) |
| SQL: | CALL SYSTEM.CancelQuery(pid [, SQLStatementID] [, timeout]) |
| SELECT SYSTEM.CancelQuery(pid [, SQLStatementID] [, timeout]) |
Example (ObjectScript):
Examples (SQL):
|
static |
THIS METHOD IS DEPRECATED.
Refer to CheckPrivilege() and CheckPrivilegeWithGrant() in <class>SYSTEM.SQL.Security</class> instead.
Check if user has SQL privilege for a particular action.
Parameters:
Returns: 1 - if the Username does have the privilege 0 - if the Username does not have the privilege Status - if CheckPriv call is reporting an error
Notes: If Username is a user with the All role, CheckPriv will return 1 even if the Object does not exist. If the user calling CheckPriv is not the same as Username, the calling user must hold the Admin_Secure:"U" privilege.
Examples:
|
static |
|
static |
THIS METHOD IS DEPRECATED.
Refer to ClearAll() in <class>SYSTEM.SQL.Statement</class> instead.
Clear SQL statistics for SQL statements in his namespace
Parameter:
Passed by reference. Returns an array of error messages if there are any failures while freezing plans.
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to CreateLinkedProcedure() in <class>SYSTEM.SQL.Schema</class> instead.
Create a linked process
dsn - Data Source Name
externalSchema - Schema name of the linked stored procedure of external source
externalProcedure - External selected Stored Procedure name
localPackage - New Stored Procedure's Package name. Default value is "LinkedSchema".
localClass - New class name for the new Linked stored procedure. Default value is "LinkedProcedures"
localMethod - Method name for the stored procedure. Default value is externalProcedure.
localSqlName - New SQL name. Default value is externalProcedure.
description - Description. Default value is empty
|
static |
THIS METHOD IS DEPRECATED.
Refer to CreateLinkedTable() in <class>SYSTEM.SQL.Schema</class> instead.
Create a linked table
dsn - Data Source Name
externalSchema - Schema name of the linked table of external source
externalTable - The linked table name of external source
primaryKeys - The fields for the primary keys of new created table.
Multiple keys are seperated by comma. The primary keys should be specified as the "new class property name" if you do not use the original field name of the external source
You at lease have to specify one field for this primaryKey.
localClass - The new linked class name. The default value is "User.LinkedClass"
localTable - The new table name for the linked class. Default value is localClass
columnMap - The linked fields of the external table. Default is to map everthing as read-only columns if you don't specify anything in the map. Otherwise, specify the fields in the below format. columnMap("external field name") = $lb("new class property name","new sql field name","read-only"(1/0)) or use external field name as default values by specifying columnMap("external field name") = "".
"new class property name" and "new sql field name" could be defined empty and "external field name" would be used as their default values "read only" default is on.
Below gives a example about how to link a table from MySQL
Use belows commands to create a table in MySQL
in MySQL shell:
create database test1;
use test1;
create table Person(PID int,name varchar(255));
insert into Person(PID,name) values(1,"Cache")
in Cache Terminal:
$system.SQL.CreateLinkedTable(dsn,"","Person","PID,name","User.LinkedClass","LinkedTable","")
would link all the fields of the table test1.Person from MySQL to the Cache class "User.LinkedTable"
and use both PID and name as primiary keys
|
static |
THIS METHOD IS DEPRECATED.
Refer to DATE() in <class>SYSTEM.SQL.Functions</class> instead.
DATE is a date/time function that returns the date portion of the given date or timestamp expression
$SYSTEM.SQL.DATE(exp)
If "" (null) is passed in, "" (null) is returned. If exp is not numeric, it is assumed the expression is in <class>Library.TimeStamp</class> logical format. Note, that if a string value is passed in as exp, it is expected that the value is a logical <class>Library.TimeStamp</class> format, and not $Horolog format. To convert $Horolog to DATE, use $SYSTEM.SQL.DATE(+$HOROLOG),
|
static |
THIS METHOD IS DEPRECATED.
Refer to DATEADD() in <class>SYSTEM.SQL.Functions</class> instead.
DATEADD is a general date/time function that returns a date calculated by adding a certain number of date parts (such as hours or days) to a specified timestamp
$SYSTEM.SQL.DATEADD(datepart,numeric-exp,dateexp)
|
static |
THIS METHOD IS DEPRECATED.
Refer to DATEDIFF() in <class>SYSTEM.SQL.Functions</class> instead.
DATEDIFF is a general date/time function that returns the interval between two dates
$SYSTEM.SQL.DATEDIFF(datepart,startdate,enddate)
Startdate is subtracted from enddate to determine how many datepart intervals are between the two dates.
The startdate and enddate parameters can be in any of the following formats:
If the year is given as two digits, a sliding window is checked to interpret the date. The system default for the sliding window can be set via the DATE utility, which is documented only in the "Legacy Documentation" chapter in Using InterSystems Documentation.
|
static |
THIS METHOD IS DEPRECATED.
Refer to DATENAME() in <class>SYSTEM.SQL.Functions</class> instead.
DATENAME is a general date/time function that returns a CHARACTER STRING containing the name of the specified date part of a date/time value.
$SYSTEM.SQL.DATENAME(datepart,dateexp)
The dateexp parameter can be in any of the following formats:
If the year is given as two digits, a sliding window is checked to interpret the date. The system default for the sliding window can be set via the DATE utility, which is documented only in the "Legacy Documentation" chapter in Using InterSystems Documentation.
|
static |
THIS METHOD IS DEPRECATED.
Refer to DATEPART() in <class>SYSTEM.SQL.Functions</class> instead.
DATEPART is a general date/time function that returns an INTEGER representing the specified date/time part of the specified date/time expression.
$SYSTEM.SQL.DATEPART(datepart,dateexp)
dateexp must contain a value of type datepart.
The dateexp parameter can be in any of the following formats:
If the year is given as two digits, a sliding window is checked to interpret the date. The system default for the sliding window can be set via the DATE utility, which is documented only in the "Legacy Documentation" chapter in Using InterSystems Documentation.
|
static |
THIS METHOD IS DEPRECATED.
Refer to DAYNAME() in <class>SYSTEM.SQL.Functions</class> instead.
DAYNAME is a date/time function that returns a character string containing the name of the day in a given date or datetime expression.
$SYSTEM.SQL.DAYNAME(dateexp)
If the year is given as two digits, a sliding window is checked to interpret the date. The system default for the sliding window can be set via the DATE utility, which is documented only in the "Legacy Documentation" chapter in Using InterSystems Documentation.
|
static |
THIS METHOD IS DEPRECATED.
Refer to DAYOFMONTH() in <class>SYSTEM.SQL.Functions</class> instead.
DAYOFMONTH is a date/time function that returns an integer from 1 to 31 that corresponds to the day of the month in a given date expression.
$SYSTEM.SQL.DAYOFMONTH(dateexp)
|
static |
THIS METHOD IS DEPRECATED.
Refer to DAYOFWEEK() in <class>SYSTEM.SQL.Functions</class> instead.
DAYOFWEEK is a date/time function that returns an integer from 1 to 7 that corresponds to the day of the week in a given date expression.
$SYSTEM.SQL.DAYOFWEEK(dateexp)
The returned values represent these days: 1 - Sunday, 2 - Monday, 3 - Tuesday, 4 - Wednesday, 5 - Thursday, 6 - Friday, 7 - Saturday
The default is that Sunday is the first day of the week.
This default can be overridden by specifying SET ^SYS("sql","sys","day of week")=n, where n values are 1=Monday through 7=Sunday.
Thus, to reset the default specify SET ^SYS("sql","sys","day of week")=7.
The day of week setting can also be defined on a per-namespace basis by adding an additional namespace subscript, for example:
SET ^SYS("sql","sys","day of week","USER")=n, where n values are 1=Monday through 7=Sunday.
InterSystems IRIS also supports the ISO 8601 standard for determining the day of the week, week of the year, and other date settings. This standard is principally used in European countries. The ISO 8601 standard begins counting the days of the week with Monday. To activate ISO 8601, SET ^SYS("sql","sys","week ISO8601")=1; to deactivate, set it to 0. If week ISO8601 is activated and "day of the week" is undefined or set to the default (7=Sunday), the ISO 8601 standard overrides the default. If "day of the week" is set to any other value, it overrides week ISO8601 for DAYOFWEEK.
|
static |
THIS METHOD IS DEPRECATED.
Refer to DAYOFYEAR() in <class>SYSTEM.SQL.Functions</class> instead.
DAYOFYEAR is a date/time function that returns an integer from 1 to 366 that corresponds to the day of the year in a given date expression.
$SYSTEM.SQL.DAYOFYEAR(dateexp)
|
static |
THIS METHOD IS DEPRECATED.
Refer to ImportDDL() in <class>SYSTEM.SQL.Schema</class> instead.
Import a DDL/DML script file.
Parameters:
When DDLMode=IRIS, the following statements are supported:
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to ImportDDLDir() in <class>SYSTEM.SQL.Schema</class> instead.
Import all DDL/DML script file in a given directory. All files with the extension .sql in the directory will be imported.
Parameters:
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to Default() in <class>SYSTEM.SQL.Schema</class> instead.
Return the default schema name for the current process in the current namespace
Example:
Static Public Member Functions inherited from Set CurrentSchema = $SYSTEM.SQL.DefaultSchema()
|
static |
|
static |
THIS METHOD IS DEPRECATED.
Refer to DropTable() in <class>SYSTEM.SQL.Schema</class> instead.
This entry point can be used to delete a table definition.
Parameters:
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to DropView() in <class>SYSTEM.SQL.Schema</class> instead.
This entry point can be used to delete a view definition.
Parameters:
Examples:
|
static |
Execute an SQL statement and return the result object.
Parameters:
Examples:
|
static |
THIS METHOD IS DEPRECATED.
It may not work properly for all cached queries.
Execute an xDBC or SQL.Statement Cached Query from the command line for debugging purposes.
Parameters:
Examples:
SAMPLES>do $SYSTEM.SQL.ExecuteCachedQuery("%sqlcq.SAMPLES.cls8",2)
Device:
Right margin: 225 =>
Executing xDBC Cached Query: sqlcq.SAMPLES.cls8
Query text:
select Name , SSN , DOB from Sample . Person where Age > :qpar(1)
Result Metadata:
Column: 1 Sample.Person.Name VARCHAR(50) NOT NULLABLE
Column: 2 Sample.Person.SSN VARCHAR(50) NOT NULLABLE isUnique isKeyColumn
Column: 3 Sample.Person.DOB DATE NULLABLE
Parameter Metadata:
Parameter: 1 qpar(1) INTEGER NULLABLE
This statement has 1 parameter
Enter the value for parameter '1': 20
SQLCODE(Open)=0
Row 1:
--- Cursor Variables ---
qscd(1,1) = 1
qscd(1,3) = 0
qscd(1,4) = <NULL>
qscd(1,5) = 20
qscd(1,6) = 12
qscd(1,7) = $lb("","Nagel,Molly B.","894-47-5000",54195,$lb("7425 Main Place","Newton","MN",81696),$lb("5436 Oak Court","Reston","VT",55529),"",$lb("Red","Yellow"))
qscd(1,8) = 54195
qscd(1,9) = 25
--- Output Variables ---
datax(1,1) = Nagel,Molly B.
datax(1,2) = 894-47-5000
datax(1,3) = 1989-05-19
Row 2:
--- Cursor Variables ---
qscd(1,1) = 2
qscd(1,3) = 0
qscd(1,4) = <NULL>
qscd(1,5) = 20
qscd(1,6) = 16
qscd(1,7) = $lb("","Ubertini,Angelo Y.","428-10-1982",54168,$lb("7812 Main Street","Washington","AZ",18420),$lb("2848 Second Blvd","Miami","TX",97018),"","")
qscd(1,8) = 54168
qscd(1,9) = 25
--- Output Variables ---
datax(1,1) = Ubertini,Angelo Y.
datax(1,2) = 428-10-1982
datax(1,3) = 1989-04-22
32 rows retrieved (2 displayed) in 2.649951 seconds
SQLCODE(LastFetch)=100
SQLCODE(Close)=0
SAMPLES>do $SYSTEM.SQL.ExecuteCachedQuery(".cls7")
Device:
Right margin: 225 =>
Executing SQL.Statement Cached Query: sqlcq.SAMPLES.cls7
Query text:
DELETE FROM sample . person WHERE age IN ( ? , ? , ? )
Parameter Metadata:
Parameter: 1 qpar(1) INTEGER NOT NULLABLE
Parameter: 2 qpar(2) INTEGER NOT NULLABLE
Parameter: 3 qpar(3) INTEGER NOT NULLABLE
This statement has 3 parameters
Enter the value for parameter '1': 33
Enter the value for parameter '2': 44
Enter the value for parameter '3': 55
SQLCODE(Execute)=0
1 rows affected by INSERT/UPDATE/DELETE or other statement. Execution time: 4.201253 seconds
Notes:
|
static |
This API/method generates, displays and returns the SQL Query execution plan details
for the specified SQL Statement (sql)... To display all of the alternate plans in addition to the chosen execution plan, specify alt or all for the qualifiers parameter (e.g. {"alt":1} or "/alt" OR {"all":1} or "/all") NOTE: By default, the execution plan is displayed during method invocation, but this can be suppressed by specifying silent for the qualifiers parameter (e.g. {"silent":1} OR "/silent"). The execution plan is also returned via the Output parameter (plan).
Parameters:
qualifiers Any number of command-line (qualifier) arguments in one of the following formats: [DEFAULT: "{}"]
Legacy String: "/[no]<qualifier>[=<value>][.../[no]<qualifier>[=<value>]]" JSON String: "{""<qualifier>"":<value>[...,""<qualifier>"":<value>]" Dynamic Object: {"<qualifier>":<value>[...,"<qualifier>":<value>]}
dynArgs A Pass By Reference array of dynamic arguments for the specified SQL Statement sqlStr with the following format:
dynArgs=# of dynamic arguments dynArgs(n)=Each dynamic argument value
NOTE: Each dynamic argument value will be substitute for each of the placeholders ? within the specified SQL Statement (sql) during SQL Query execution plan processing
plan An Output parameter that returns the SQL Query execution plan details as a Refactored String (which is the default), a Dynamic Object (JSON format), or an Array (if available) based on the value of the qualifiers parameter format term.
NOTE-1
The qualifiers parameter format term can have the following values:
"PRINT" - Printable/Viewable format generates a semi-XML output (format-type: Refactored String) "PRINT-ARRAY" - Printable/Viewable format generates a semi-XML output (format-type: Array) "JSON" - JSON format generates a Machine-Readable Query Plan (MRQP) rendered in a JSON format (format-type: Dynamic Object)
NOTE-2
The return format of this parameter can be requested as an array IFF the SQL Query execution plan generates an array when the value of the qualifiers parameter format term equals "PRINT-ARRAY"; Otherwise, the return format will be either "PRINT" or "JSON", whichever is appropriate
// — SQL Query plan & all alternative plans with Stats returned in the 'plan' param. Suppress screen outputs — set sql="SELECT COUNT(*) FROM Sample.Person" set tSC=$SYSTEM.SQL.Explain(sql, {"alt":1, "silent":1, "stats":1},,.plan) zwrite tSC write plan
// — SQL Query plan with verbose modules output to the current device — set sql="SELECT COUNT(*) FROM Sample.Person" set tSC=$SYSTEM.SQL.Explain(sql,{"verbose":1,"stats":1}) zwrite tSC
// — SQL Query plan output to the current device; SQL passed by reference; apply literal substitution — kill sql set sql($i(sql))="SELECT COUNT(*) FROM Sample.Person WHERE Name=?" kill dynArgs set dynArgs($i(dynArgs))="John" set tSC=$SYSTEM.SQL.Explain(.sql,{"preparse":1,"stats":1},.dynArgs) zwrite tSC
// — SQL Query plan returned in the 'plan' param as a JSON object. Suppress screen outputs — set sql="SELECT COUNT(*) FROM Sample.Person" set tSC=$SYSTEM.SQL.Explain(sql, {"format":"JSON", "silent":1},,.plan) zwrite tSC zwrite plan
// — SQL Query plan returned in the 'plan' param as an array. Suppress screen outputs — set sql="SELECT COUNT(*) FROM Sample.Person" set tSC=$SYSTEM.SQL.Explain(sql, {"format":"PRINT-ARRAY", "silent":1},,.plan) zwrite tSC zwrite plan
|
static |
THIS METHOD IS DEPRECATED.
Refer to ExportDDL() in <class>SYSTEM.SQL.Schema</class> instead.
Export a SQL DDL/DML script file containing User definitions, Role definitions, and/or Privileges.
Parameters:
Examples:
The method returns a Status Code.
A file created via $SYSTEM.SQL.Export() can be imported using one of:
The Export() method will not export the following users and roles -
|
static |
THIS METHOD IS DEPRECATED.
Refer to ExportAllFrozenPlans() in <class>SYSTEM.SQL.Statement</class> instead.
Exports all SQL Statement Index entries in this namespace, including query plans. Generated file can be loaded using $SYSTEM.SQL.ImportStatement().
The main reason to export/import an SQL Statement entry is to move a frozen plan from one location to another.
Other than moving a frozen plan, there is not much benefit of moving an SQL Statement definition from one location to another. When exporting SQL Statement definitions, the Locations (routines) the SQL Statement is defined in is not exported. This is because it is possible the Locations are different or don't exist on the target system. Recompiling the class/routines that contain the SQL Statement on the target system will redeine the Location entries for the SQL Statement definition.
Any SQL Statements the current user does not have privilege to run will not be exported.
Parameter:
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to ExportFrozenPlans() in <class>SYSTEM.SQL.Statement</class> instead.
Export an SQL Statement Index entry, including query plan. Generated file can be loaded using $SYSTEM.SQL.ImportStatement().
The main reason to export/import an SQL Statement entry is to move a frozen plan from one location to another.
Other than moving a frozen plan, there is not much benefit of moving an SQL Statement definition from one location to another. When exporting SQL Statement definitions, the Locations (routines) the SQL Statement is defined in is not exported. This is because it is possible the Locations are different or don't exist on the target system. Recompiling the class/routines that contain the SQL Statement on the target system will redeine the Location entries for the SQL Statement definition.
If the current user is not privileged to execute this SQL Statement, a privilege error will be returned and the export will not occur.
Parameter:
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to Export() in <class>SYSTEM.SQL.Stats.Table</class> instead.
Export extentsize and selectivity for tables/fields to an XML file.
Generated file can be loaded using $SYSTEM.SQL.ImportTuneStats().
Parameter:
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to LoadFDBMS() in <class>SYSTEM.SQL.Schema</class> instead.
Import a FDBMS DDL script file.
|
static |
|
static |
THIS METHOD IS DEPRECATED.
Refer to FreezeAll(), FreezeSchema(), FreezeRelation(), FreezeStatement(), UnfreezeAll(), UnfreezeSchema(), UnfreezeSchema(), and UnfreezeStatement()
in <class>SYSTEM.SQL.Statement</class> instead. Freeze or Unfreeze plans or SQL statements in this namespace
Parameter:
Passed by reference. Returns an array of error messages if there are any failures while freezing plans.
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to GetOption() in <class>SYSTEM.SQL.Util</class> instead.
Return the current process's Transaction Commit Mode value. Possible values are:
| 0 | NO AUTO TRANSACTION |
| 1 | AUTO COMMIT ON (Default) |
| 2 | AUTO COMMIT OFF |
|
static |
THIS METHOD IS DEPRECATED.
Refer to GetOption() in <class>SYSTEM.SQL.Util</class> instead.
Returns the current system-wide setting of the BitmapFriendlyCheck flag.
See SetBitmapFriendlyCheck() for more information about this setting.
Returns:
Boolean value: 1 means the class compiler will check to make sure the type of ID of the class is a positive integer if there is a bitmap index defined for the class and the class uses Storage.SQL. 0 (the default), means the class compiler will not perform this check and no error will be returned if the class contains a bitmap index and does not have a positive integer ID type.
|
static |
THIS METHOD IS DEPRECATED.
Refer to GetOption() in <class>SYSTEM.SQL.Util</class> instead.
Return the CollectionProjection setting. Possible values are:
| 0 | Do not project collections as columns if also projected as child table (DEFAULT) |
| 1 | Project all collections as columns |
|
static |
THIS METHOD IS DEPRECATED.
Refer to GetAllColumns() and GetVisibleColumns() in <class>SYSTEM.SQL.Schema</class> instead.
Given a table name, return a list of columns by name with the column number, and a list of columns sorted by column number with the name.
Parameters:
Returns: A Boolean value. 1 if the columns were returned successfully, 0 if there was no such table.
|
static |
THIS METHOD IS DEPRECATED.
Refer to GetOption() in <class>SYSTEM.SQL.Util</class> instead.
Function returns the current Compile Mode setting, either Deferred or Immediate.
|
static |
THIS METHOD IS DEPRECATED.
Refer to GetOption() in <class>SYSTEM.SQL.Util</class> instead.
Return the current IDENTITY_INSERT option value. Possible values are:
| 0 | IDENTITY cannot be set |
| 1 | IDENTITY can be set |
|
static |
THIS METHOD IS DEPRECATED.
Refer to GetOption() in <class>SYSTEM.SQL.Util</class> instead.
Return the current process's Transaction Isolation Mode value. Possible values are:
| 0 | - READ UNCOMMITTED (Default) |
| 1 | - READ COMMITTED |
| 3 | - READ VERIFIED |
|
static |
THIS METHOD IS DEPRECATED.
Refer to GetOption() in <class>SYSTEM.SQL.Util</class> instead.
Return the current Lock Threshold setting. The value returned is an integer that is the number of row locks to acquire before escalating to a table lock. This is a system wide setting that is in effect for all processes.
|
static |
THIS METHOD IS DEPRECATED.
Refer to GetOption() in <class>SYSTEM.SQL.Util</class> instead.
Return the current system Lock Timeout value as defined in the configuration settings. Note, this may be different than the lock timeout for the current process.
|
static |
THIS METHOD IS DEPRECATED.
Refer to GetMapSelectability() in <class>SYSTEM.SQL.Util</class> instead.
This entrypoint is used to determine if an SQL Map definition is selectable by the SQL Query Optimizer or not
Parameters:
Returns: 1 if the map is selectable, 0 if the map is not selectable. If the table or map does not exist, or there is an invalid input paramater, 0,message will be returned as the map is not selectable or the selectability cannot be determined.
Notes:
return of 1 by this function does not necessarily mean the table and map exist, just that the map's selectability has not been set to 0.
function checks the selectability for the map in the current namespace only. If this table is mapped to multiple namespaces, and the index needs to be built in each namespace, SetMapSelectability should be called in each namespace the index is to be built in. GetMapSelectability should be checked in each namespace the table is mapped to.
Example:
|
static |
THIS METHOD IS DEPRECATED.
Refer to GetOption() in <class>SYSTEM.SQL.Util</class> instead.
Return the Lock Timeout value for the current process.
|
static |
Function returns the current contents of the ROWID variable.
It can be called after INSERT to get ROWID value of the row inserted.
This method can also be called as a Stored Procedure named SYSTEM.SQL_GetROWID()
|
static |
Get RTPC runtime classes for a given RTPC utility class (pUtilityCls)
Parameters:
Returns: Status Code
Invocation:
| Object Script: | $SYSTEM.SQL.GetRTPCRuntimeQueries(pUtilityCls , .pRuntimeCls) |
Example (ObjectScript):
|
static |
THIS METHOD IS DEPRECATED.
Refer to GetOption() in <class>SYSTEM.SQL.Util</class> instead.
Get the current setting of the system wide flag for controlling if SQL Functions perform ODBCToLogical/DisplayToLogical on SQL Function input arguments.
Parameters:
Returns:
Current value of the SQLFunctionArgConversion setting
Examples:
NOTES: - This is a system-wide setting.
|
static |
Method: GetSQLStatsFlag [SQL: StatsSQL_GetSQLStatsFlag] Purpose: This method gets the flag that controls whether or not we collect <IHD> 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}:{flag-type} </IHD> RULES: When (flagType=""), whether to use the SQLStats-flag (System) or <IHD> 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) </IHD> Invocation: This method can be invoked in the following ways: <IHD> Object Script: $SYSTEM.SQL.GetSQLStatsFlag(...)
SQL: SELECT SYS_PTools.StatsSQL_GetSQLStatsFlag(...) </IHD> Examples: The following examples shows the use of this method: <IHD>
#1 Get the current value of the SQLStats-flag for the Job/System Flag based on SQLStats-flag Rules:
set SQLStatsFlag=##class(SYS.PTools.StatsSQL).GetSQLStatsFlag("") </IHD> Parameters: <IHP> flagType - "" = Job/System Flag based on SQLStats-flag Rules [DEFAULT] 0 = System Flag 1 = Process/Job Flag 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 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 </IHP> RETURN: The current value of the SQLStats-flag; Otherwise, return an error message if an error occurred
|
static |
Method: GetSQLStatsFlagByPID [SQL: StatsSQL_GetSQLStatsFlagByPID] Purpose: This method gets the flag that controls whether or not we collect <IHD> 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}:{flag-type} </IHD> NOTE: This method ONLY applies to the SQLStats-flag for the given <IHD> 'pid' (Process/Job) and NOT the SQLStats-flag for the (System)!
RESTRICTION: This method invocation requires Admin_Operate:Use privilege </IHD> Invocation: This method can be invoked in the following ways: <IHD> Object Script: $SYSTEM.SQL.GetSQLStatsFlagByPID(...)
SQL: SELECT SYS_PTools.StatsSQL_GetSQLStatsFlagByPID(...) </IHD> Examples: The following examples shows the use of this method: <IHD>
#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(SYS.PTools.StatsSQL).GetSQLStatsFlagByPID(12345) </IHD> Parameters: <IHP> pid - The process ID ($JOB) for which to set the SQLStats-flag [DEFAULT: Current $JOB] 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 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 </IHP> RETURN: The current value of the SQLStats-flag; Otherwise, return an error message if an error occurred
|
static |
THIS METHOD IS DEPRECATED.
Refer to GetOption() in <class>SYSTEM.SQL.Util</class> instead.
Return the select mode value for the current process. Possible values are:
| 0 | Logical |
| 1 | ODBC |
| 2 | Display |
The select mode returned by this API is used when:
#SQLCompile Select = Runtime
is specified for the compiled SQL code.
When the SQL statement is compiled in Runtime mode, the mode returned by GetSelectMode will be used at runtime to determine whether Logical(0), ODBC(1), or Display(2) mode is used for the statement. See the documentation for #SQLCompile Select for more information.
|
static |
THIS METHOD IS DEPRECATED.
Refer to GetOption() in <class>SYSTEM.SQL.Util</class> instead.
Returns ODBC/JDBC disconnect code. Upon disconnect any ObjectScript code defined in this setting will be executed immediately before the server process Halts. This code will also attempt to be executed anytime the server process encounters an unrecoverable error that causes the server process to Halt.
It will not be executed if the server process encounters a Halt in other user defined code, if the process crashes, if the process is stopped, or InterSystems IRIS is stopped or forced down. If no disconnect code is defined for this namespace, "" is returned. The disconnect code is defined on a per-namespace basis.
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to GetOption() in <class>SYSTEM.SQL.Util</class> instead.
Returns ODBC/JDBC/SQL Manager initialization code. This Initialization code is executed at login time when connecting to SQL through ODBC, JDBC, or the SQL Manager. If no initialization code is defined for this namespace, "" is returned. Initialization code is defined on a per-namespace basis.
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to GrantPrivilege() and GrantPrivilegeWithGrant() in <class>SYSTEM.SQL.Security</class> instead.
GrantObjPriv lets you grant an ObjPriv to a User via this call instead of using the SQL GRANT statement
$SYSTEM.SQL.GrantObjPriv(ObjPriv,ObjList,Type,User,wGrant)
ObjPriv
Comma delimited string of actions to grant. * for all actions:
ObjList * for all objects, else a comma delimited list of SQL object names (tables, views, procedures, schemas)
Type Table, View, Schema, Stored Procedures, or ML Configuration
Users Comma delimited list of users
wGrant 0/1 for WITH GRANT OPTION
SQLCODE By reference parameter that can be used to return an SQLCODE value for the GRANT
|
static |
THIS METHOD IS DEPRECATED.
Refer to HOUR() in <class>SYSTEM.SQL.Functions</class> instead.
HOUR is a date/time function that returns an integer from 0 to 23 that corresponds to the hour component in a given date-time expression.
$SYSTEM.SQL.HOUR(timeexp)
|
static |
THIS METHOD IS DEPRECATED.
Refer to INSTR() in <class>SYSTEM.SQL.Functions</class> instead.
INSTR is a function that searches string for substring and returns an integer indicating the position of the character in string that is the first character of this occurrence.
$SYSTEM.SQL.INSTR(string,substring,position,occurrence)
|
static |
|
static |
THIS METHOD IS DEPRECATED.
Refer to ImportDir() in <class>SYSTEM.SQL.Schema</class> instead.
Import all DDL/DML script files in a given directory. All files with the extension .sql in the directory will be imported. The caller can optionally specify a comma delimited list of file extensions to import. Subdirectories are also recursively processed if the caller specifies the pRecurse argument as 1.
Parameters:
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to ImportFrozenPlans() in <class>SYSTEM.SQL.Statement</class> instead.
Import a file containing SQL Statement Index entries. Files can be loaded that were generated with $SYSTEM.SQL.ExportStatement() or $SYSTEM.SQL.ExportAllStatements().
The main reason to export/import an SQL Statement entry is to move a frozen plan from one location to another.
Other than moving a frozen plan, there is not much benefit of moving an SQL Statement definition from one location to another. When exporting SQL Statement definitions, the Locations (routines) the SQL Statement is defined in is not exported. This is because it is possible the Locations are different or don't exist on the target system. Recompiling the class/routines that contain the SQL Statement on the target system will redeine the Location entries for the SQL Statement definition.
Parameter:
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to Import() in <class>SYSTEM.SQL.Stats.Table</class> instead.
Import extentsize, selectivity, blockcount for a table and its fields from a file created by $SYSTEM.SQL.ExportTuneStats().
Parameter:
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to LoadInformix() in <class>SYSTEM.SQL.Schema</class> instead.
Import an Informix DDL/DML script file.
The Informix DDL/DML Import Utility supports the following statements:
Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).
|
static |
THIS METHOD IS DEPRECATED.
Refer to LoadInterBase() in <class>SYSTEM.SQL.Schema</class> instead.
Import an Interbase DDL/DML script file.
The Interbase DDL/DML Import Utility supports the following statements:
Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).
|
static |
This entry point can be used to determine if a string is an SQL Reserved word.
Parameters:
Examples:
This method can also be called as a Stored Procedure named SYSTEM.SQL_IsReservedWord(word)
|
static |
This entry point can be used to determine if an SQL identifier is a valid regular SQL identifier.
An SQL regular identifier must begin with '', '_', or a letter followed by 0 or more letters, numbers, '_', '@', '#', or '$' characters. It must also not be an SQL Reserved word. Identifiers which do not qualify as Regular identifiers must be specified as Delimited identifiers in SQL statements.
Parameters:
Examples:
This method can also be called as a Stored Procedure named SYSTEM.SQL_IsValidRegularIdentifier(identifier)
|
static |
THIS METHOD IS DEPRECATED.
Refer to LASTDAY() in <class>SYSTEM.SQL.Functions</class> instead.
LASTDAY is a scalar date/time function that returns the <class>Library.Date</class> value of the last day of the month for a given date expression.
$SYSTEM.SQL.LASTDAY(dateexp)
LASTDAY returns the date of the last day of the specified month as a <class>Library.Date</class> value. Leap years differences are calculated.
LASTDAY returns 0 when an invalid date is specified: the day or month as zero; the month greater than 12; or the day larger than the number of days in that month on that year.
|
static |
THIS METHOD IS DEPRECATED.
Refer to MINUTE() in <class>SYSTEM.SQL.Functions</class> instead.
MINUTE is a date/time function that returns an integer from 0 to 59 that corresponds to the minute component in a given date-time expression.
$SYSTEM.SQL.MINUTE(timeexp)
|
static |
THIS METHOD IS DEPRECATED.
Refer to MONTH() in <class>SYSTEM.SQL.Functions</class> instead.
MONTH is a date/time function that returns an integer from 1 to 12 that corresponds to the month in a given date expression.
$SYSTEM.SQL.MONTH(dateexp)
|
static |
THIS METHOD IS DEPRECATED.
Refer to MONTHNAME() in <class>SYSTEM.SQL.Functions</class> instead.
MONTHNAME is a date/time function that returns a character string containing the name of the month in a given date expression.
$SYSTEM.SQL.MONTHNAME(dateexp)
|
static |
THIS METHOD IS DEPRECATED.
Refer to LoadMSSQLServer() in <class>SYSTEM.SQL.Schema</class> instead.
Import an MS SQL Server DDL/DML script file.
The MS SQL Server DDL/DML Import Utility supports the following statements:
Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).
|
static |
|
static |
THIS METHOD IS DEPRECATED.
Refer to LoadOracle() in <class>SYSTEM.SQL.Schema</class> instead.
Import an Oracle DDL/DML script file.
The Oracle DDL/DML Import Utility supports the following statements:
Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).
|
static |
Prepare an SQL query and return the prepared statement object.
This function returns a statement oref if the query is successfully prepared. If the query cannot be successfully prepared then an exception is thrown.
A prepared statement can be executed by calling Execute(). Execute() accepts arguments that align with parameters present as question marks in the prepared query. Literals in the prepared query are managed automatically.
A prepared statement may also be reused for a different query by simply invoking Prepare() on the statement, passing in a new query. Refer to the Prepare method in <class>SQL.Statement</class> for more information.
Parameters:
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to ProcedureExists() in <class>SYSTEM.SQL.Schema</class> instead.
This entry point can be used to determine if a stored procedure exists.
Parameters:
Examples:
Notes:
This method can also be called as a Stored Procedure named SYSTEM.SQL_ProcedureExists(procname)
|
static |
Purges Cached Queries.
Parameter:
Examples:
|
static |
Purges Cached Queries in all namespaces on this system.
Example:
|
static |
Purges Cached Queries given the name(s) of the Cached Query Classes.
Parameter:
Examples:
|
static |
Purges Cached Queries given the name(s) of the Cached Query Classes.
This entry point is deprecated. $SYSTEM.SQL.PurgeCQClass(classname) should be used instead.
Parameter:
Examples:
|
static |
Purges all Cached Queries which use table table.
Parameter:
The qualified name of the table to purge the Cached Queries for. If the schema is not specified, the default schema will be used.
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to QUARTER() in <class>SYSTEM.SQL.Functions</class> instead.
QUARTER is a date/time function that returns an integer from 1 to 4 that corresponds to the quarter of the year in a given date expression.
$SYSTEM.SQL.QUARTER(dateexp)
|
static |
THIS METHOD IS DEPRECATED.
Refer to QueryToTable() in <class>SYSTEM.SQL.Schema</class> instead.
Turn the results of a query into a table definition. This utility takes a query and the name of a new table and executes the query. The results of the query are turned into a new table of name 'table'.
Parameters:
query="sql text"
OR
query = # of lines
query(1) = sql line 1
query(n) = sql line n
error = # of error lines
error(1) = error line 1
error(n) = error line n
Examples:
Do $SYSTEM.SQL.QueryToTable("SELECT * FROM MedLab.Patient WHERE Sex = 'Male'","MedLab.MalePatient",1,.errors)
Set query=3
Set query(1)="SELECT *"
Set query(2)=" FROM ""Med Lab"".Patient""
Set query(3)=" WHERE Sex = 'Male'"
Do $SYSTEM.SQL.QueryToTable(.query,"""Med Lab"".MalePatient",1,.errors)
Do $SYSTEM.SQL.QueryToTable("SELECT * FROM Patient WHERE Sex = 'Male'","MalePatient",1,.errors) ; Creates SQLUser.MalePatient
|
static |
THIS METHOD IS DEPRECATED.
Refer to RevokePrivilege() in <class>SYSTEM.SQL.Security</class> instead.
RevokeObjPriv lets you revoke an ObjPriv from a User via this call instead of using the SQL REVOKE statement
$SYSTEM.SQL.RevokeObjPriv(ObjPriv,ObjList,Type,User,wGrant,Cascade,AsGrantor)
ObjPriv
Comma delimited string of actions to grant. * for all actions:
ObjList * for all objects, else a comma delimited list of SQL object names (tables, views, procedures, schemas)
Type Table, View, Schema, Stored Procedures, or ML Configurations
Users Comma delimited list of users
wGrant 0/1 for WITH GRANT OPTION
Cascade 0/1 cascade revoke?
AsGrantor Alternate User to remove Privs for. As Grantor can be a user name, a comma-separated list of user names, or "*".
SQLCODE By reference parameter that can be used to return an SQLCODE value for the GRANT
|
static |
THIS METHOD IS DEPRECATED.
Refer to RoleExists() in <class>SYSTEM.SQL.Security</class> instead.
This entry point can be used to determine if a role exists.
Parameters:
Examples:
This method can also be called as a Stored Procedure named SYSTEM.SQL_RoleExists(rolename)
|
static |
THIS METHOD IS DEPRECATED.
Refer to SECOND() in <class>SYSTEM.SQL.Functions</class> instead.
SECOND is a date/time function that returns an integer from 0 to 59 that corresponds to the second component in a given date-time expression.
$SYSTEM.SQL.SECOND(timeexp)
|
static |
THIS METHOD IS DEPRECATED.
Refer to SQLCODE() in <class>SYSTEM.SQL.Functions</class> instead.
This entry point can be used to return the description of an SQLCODE value.
Parameters:
Examples:
This method can also be called as a Stored Procedure named SYSTEM.SQL_SQLCODE(SQLCODE)
|
static |
THIS METHOD IS DEPRECATED.
Refer to SQLSTRING() in <class>SYSTEM.SQL.Functions</class> instead.
SQLSTRING returns the SqlString collation of the passed in value.
$SYSTEM.SQL.SQLSTRING(stringexp,maxlen)
|
static |
THIS METHOD IS DEPRECATED.
Refer to SQLUPPER() in <class>SYSTEM.SQL.Functions</class> instead.
SQLUPPER returns the SqlUpper collation of the passed in value.
$SYSTEM.SQL.SQLUPPER(stringexp,maxlen)
SQLUPPER converts stringexp to a format that is sorted as a (case-insensitive) upper-case string. SQLUPPER converts all alphabetic characters to uppercase, strips trailing white space (spaces, tabs, and so on) from the string, then adds one leading blank space to the beginning of the string. It collates any value containing only white space (spaces, tabs, and so on) as the SQL empty string. SQL Empty string is represented as $Char(0) in ObjectScript, which collated as SqlUpper is " "_$Char(0).
|
static |
THIS METHOD IS DEPRECATED.
Refer to STRING() in <class>SYSTEM.SQL.Functions</class> instead.
STRING returns the String collation of the passed in value.
$SYSTEM.SQL.STRING(stringexp,maxlen)
STRING converts all alphabetic characters are uppercased; all punctuation characters are removed, except the comma; a single space is added to the beginning of the expression. It collates any value containing only white space (spaces, tabs, and so on) as the SQL empty string. SQL Empty string is represented as $Char(0) in ObjectScript, which collated as String is " "_$Char(0).
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the configuration setting which determines if ANSI operator precedence is applied. The default is use of ANSI precedence is appied.
Parameter:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the configuration setting which determines if extrinsic functions are allowed to be used in SQL statements through ODBC, JDBC, and Dynamic SQL. The default is use of extrinsic functions is not supported.
Parameter:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Sets the AUTO_COMMIT mode for this process.
Setting takes effect immediately and lasts for the duration of the process or until SetAutoCommit is called again.
Parameter:
Returns:
Old value (0, 1, or 2) of the AutoCommit setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the configuration setting which determines if auto hinting PARALLEL is applied. The default is use of auto hinting PARALLEL is not appied.
Parameter:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the configuration setting which determines the threshold of auto hinting PARALLEL. Once auto hinting for PARALLEL is enabled, we could use this function to setup the threshold for this feature. Increasing the threshold means it would lower the chance for the auto hinting for PARALLEL to happen The default value is 3200. This value could be roughlt thinked of as how many rows in the visited map
Parameter:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Sets the flag to determine if the compiler should check if the bitmap index is allowed in a Storage.SQL class.
This setting only applies to classes using Storage.SQL. If this setting is TRUE (1), during class compilation the compiler will check to make sure the ID of the table is an INTEGER type if there are any bitmap or bitmap extent indices defined. An INTEGER type means the class must have either no IdKey index, or a single field IdKey index.
If the class has a single field IdKey index, the type of the IdKey property must be of SqlCategory="INTEGER" or SqlCategory="NUMERIC" with a SCALE=0 and MINVAL=1 or higher. If BitmapFriendlyCheck is set to 1, and the class compile finds a non bitmap friendly class, the class compilation will fail with an error. If BitmapFriendlyCheck=0 and a Storage.SQL class is defined with an ID that is not a positive integer, it is up to the developer of the class to make sure the index is data is properly stored and retrieved from the global.
Parameter:
Returns:
Status Code
NOTES:
- Changing this configuration setting will only take effect immediately for all processes. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Defines the lock timeout length used for Cached Queries when a lock needs to be acquired on Cached Query metadata. The default is 120 seconds.
Parameter:
Returns:
Status Code
Examples:
NOTES: - The setting is on a per system basis.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Defines whether or not the source code for cached queries is retained. The default is no source is saved. The setting is on a per-system basis.
Parameter:
Returns:
Status Code
Examples:
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will take effect immediately for all processes in InterSystems IRIS after this function is called. Any new cached queries created by any process will immediately begin saving (or not saving) cached query source.
Any existing cached queries will only have source saved if the system was configured to save source at the time the statement was prepared. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Sets the collection projection option. Setting takes effect when classes are recompiled.
Parameter:
Returns:
Old value (0, 1) of the CollectionProjection setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the namespace wide flag which turns on Deferred Compilation mode. 'Deferred Compilation Mode' can be used to improve the performance of 'installation-type' activities. Deferred Compilation Mode will, in most cases, reduce the number of class compilations which take place during the loading of tables through DDL scripts. When Deferred Compilation Mode is on, classes are not immediately compiled, but put in a compilation queue which will be compiled at a later time when needed. This is very useful during loading of DDL scripts of the following format:
Execute the following SQL statement:
SET OPTION COMPILEMODE = DEFERRED
Do $SYSTEM.SQL.SetCompileModeDeferred()
SET OPTION COMPILEMODE = IMMEDIATE
Do $SYSTEM.SQL.SetCompileModeImmediate()
"" (null) string
NOTES: - Changing this setting will take effect immediatly for all processes in this namespace. - This setting is not related to the macro compiler directive #SQLCompile Mode = { deferred | embedded | immediate }
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the namespace wide flag which turns on Immediate Compilation mode. Immediate Compile Mode is the default compilation mode. If there are pending compilations when switching from Deferred/Install Compile Mode, they will be compiled immediately. See <method>CompileModeDeferred</method>, <method>CompileModeInstall</method>, and <method>CompileModeNocheck</method> for more information.
Returns:
"" (null) string
NOTES: - Changing this setting will take effect immediatly for all processes in this namespace. - This setting is not related to the macro compiler directive #SQLCompile Mode = { deferred | embedded | immediate }
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the namespace wide flag which turns on Install Compilation mode. This mode should only be used for installation procedures where no data exists for any of the tables being created. If data exists for the tables definitions being manipulated through DDL statement, use Deferred Compile Mode instead. This is very useful during loading of DDL scripts of the following format:
Execute the following SQL statement:
SET OPTION COMPILEMODE = INSTALL
Do $SYSTEM.SQL.SetCompileModeInstall()
SET OPTION COMPILEMODE = IMMEDIATE
Do $SYSTEM.SQL.SetCompileModeImmediate()
"" (null) string
NOTES: - Changing this setting will take effect immediatly for all processes in this namespace. - This setting is not related to the macro compiler directive #SQLCompile Mode = { deferred | embedded | immediate }
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the namespace wide flag which turns on Nocheck Compilation mode. This mode is the same as immediate mode except that existing data is not validated against new constraints. For example if you add a unique constraint to a table that already has data, Nocheck mode will not validate that the constraint is valid. This compile mode must be used with extreme caution. You could end up with data integrity problems in your application.
In NOCHECK compile mode, the following constraints are not checked when executing DDL statements:
Otherwise, the mode behaves the same as immediate. There is no deferring of class compilations and indices are built when created. Changing this setting will only take effect for this process.
Returns:
"" (null) string
NOTES: - Changing this setting will take effect immediatly for all processes in this namespace. - This setting is not related to the macro compiler directive #SQLCompile Mode = { deferred | embedded | immediate }
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Sets the flag which determines if a class created by a DDL CREATE TABLE statement defines a bitmap extent index for the class. This setting only applies to classes created through DDL that do not also define an explicit IdKey index.
Parameters:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting. - The default setting is TRUE, the class will define a bitmap extent index.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Sets the flag which determines if a DDL DROP TABLE statement deletes the table's data.
Parameters:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Sets the flag which determines if a class created by a DDL CREATE TABLE statement is Final.
Parameters:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting. - The default setting is TRUE, the class will be defined as Final. - Temporary tables are always defined as Final, regardless of this setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetDDLIdentifierTranslations() in <class>SYSTEM.SQL.Util</class> instead.
Sets the DDL Identifier Translation mappings configuration settings.
This is for filtering/modifying valid SQL identifier characters when translating SQL identifiers into Objects identifiers. When converting an SQL identifier to an Objects Identifier at DDL run time, the characters in the 'from' list will be converted to the characters in the 'to' list. This is done through the ObjectScript $Translate function. For example:
SQL Table name = "My Table!"
from = '"! '
to = ''
Class name = $Translate("My Table!",from,to) = MyTable
Parameter:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set configuration setting for PRIMARY KEY in DDL also being the IDKey index. This configuration setting determines if a primary key constraint, specified through DDL, also becomes the IDKey index in the class definition.
By default, the primary key does NOT also become the IDKey index.
Having the primary key index also be an IDKey index generally gives better performance, but it means the Primary key fields cannot be updated.
Parameter:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Sets the flag which determines if a class created by a DDL CREATE TABLE statement defines the USEEXTENTSET class parameter to a value of 1. USEEXTENTSET=1 will generally bind the table to global names that allow for better performance when running queries against the table, especially when the index globals are used. It does mean the global names the class is mapped to are not names that attempt to match the classname. See documentation for USEEXTENTSET parameter for more information.
Parameters:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting. - The default setting is TRUE, the class will use be defined with parameter USEEXTENTSET=1.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Sets the flag which determines if a class created by a DDL CREATE TABLE statement uses $Sequence for ID assignment. The storage keyword IDFUNCTION can be defined as INCREMENT or SEQUENCE. This keyword value is used by the class compiler to determine which system function - $increment or $sequence - is to be used for assigning new ID values for a persistent class using default storage. The default value for IDFUNCTION is INCREMENT, however the default behavior for classes defined through DDL is to define IDFUNCTION as SEQUENCE. To configure the system to have classes created through DDL to define IDFUNCTION as INCREMENT, pass in 0 for the flag parameter.
Parameters:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting. - The default setting is TRUE, the class will use $Sequence for ID assignment.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetDefault() in <class>SYSTEM.SQL.Schema</class> instead.
Sets the default schema used by SQL.
This configuration setting provides the ability to define a default schema name other than SQLUser, the default. When an unqualified table name is encountered in an SQL statement (and there is no #import statement specified), the default schema will be used. You may specify _CURRENT_USER for the default schema name if you wish to use the SQL username the process logged in as the name of the default schema. If the process has not logged in to SQL, SQLUser will be used as the default schema name. You may also specify _CURRENT_USER/<default name>. In this case, if the process has not logged in to SQL, <default name> will be used as the default schema name. For example: _CURRENT_USER/HMO will use HMO as the default schema if the process has not logged in to SQL. This setting has nothing to do with the mappings between SQL schema names and the class package name, it only specifies the default schema.
Parameter:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the Default precision for the Time component of the value returned by the GETDATE(), CURRENT_TIME, and CURRENT_TIMESTAMP SQL Scalar functions.
Parameter:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the configuration setting which determines if double quote ("") in an SQL statement is used for delimited (quoted) identifiers or string constants. The default is delimited identifiers are supported.
Parameter:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Defines whether or not SQL SELECT statements perform a $SYSTEM.ECP.Sync() call in the OPEN code The default is ECP Sync is OFF.
Parameter:
Returns:
Status Code
Examples:
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will only take effect for this process and new processes starting in InterSystems IRIS after this function is called. Any existing processes will still use the old setting. - The setting is on a per system basis.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Defines whether or not SQL DISTINCT is optimized to use indexes. The default is DISTINCT optimizations are ON.
Parameter:
Returns:
Status Code
Examples:
If true (the default) many SQL queries involving DISTINCT (and GROUP BY) will run much more efficiently by making better use of indices (if available). The downside of this is that the values returned by such queries will be collated in the same way they are stored within the index (i.e., results may be in upper case). Some applications care about the case of values returned by such queries. If "Fast DISTINCT" is set to false (0), the SQL will revert to the use of uncollated values with regards to DISTINCT behavior.
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - The setting is on a per system basis.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetFieldSelectivity() in <class>SYSTEM.SQL.Stats.Table</class> instead.
Set the SELECTIVITY of a field and property to the value of the given selectivity
Parameter:
Returns:
Status Code
Example:
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the configuration setting which determines if Foreign Key Referential Integrity checks are performed in the SQL Filer. Turning off SQL Filer Referential Integrity checking will suppress any SQLCODE -121, -122, -123, and -124 errors. The default value is TRUE (Validation checks are performed).
Parameter:
Returns:
Status Code
NOTES:
- You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will only take effect for this process and new processes starting in InterSystems IRIS after this function is called. Any existing processes will still use the old setting. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the IDENTITY_INSERT option for this process.
IDENTITY_INSERT controls the ability of the user to specify a value for the IDENTITY property when saving a new object, a value for the IDENTITY column or an explicit ROWID value in an SQL INSERT. If IDENTITY_INSERT is false and the user specifies an explicit IDENTITY or ROWID value when saving a new object (ObjectScript) or inserting a new ROW (SQL) then an error condition is reported.
Setting takes effect immediately and lasts for the duration of the process or until SetIdentityInsert is called again.
Valid values are:
| 0 | IDENTITY cannot be set |
| 1 | IDENTITY can be set |
If a valid value is passed in then the IDENTITY_INSERT option for the current process will be set to that value and the previous IDENTITY_INSERT value is returned. Otherwise the IDENTITY_INSERT setting is left unchanged and pStatus will contain a Status value describing the error and the current IDENTITY_INSERT value is returned.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the Transaction Isolation Mode for this process.
The ISOLATION MODE options permit you to specify whether or not uncommitted changes to the database should be available for read access by a SELECT query. The READ COMMITTED option states that only those changes that have been committed are available for query access. If requested data has been changed, but the changes have not been committed (or rolled back), the query waits for transaction completion. If a lock timeout occurs while waiting for this data to be available, an SQLCODE error is issued. READ COMMITTED is the default ISOLATION MODE. The READ UNCOMMITTED option states that all changes are immediately available for query access. The READ VERIFIED option states that all changes are immediately available for query access, but with additional checks to skip rows containing changed data that no longer match the conditions specified in the query.
Setting takes effect immediately and lasts for the duration of the process or until SetIsolationMode is called again.
Valid values are:
| 0 | - READ UNCOMMITTED (Default) |
| 1 | - READ COMMITTED |
| 3 | - READ VERIFIED |
If a valid value is passed in then the Isolation Mode option for the current process will be set to that value and the previous Isolation Mode value is returned. Otherwise the Isolation Mode setting is left unchanged and pStatus will contain a Status value describing the error and the current Isolation Mode value is returned.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the Lock Threshold for locks acquired during filing of rows within a single transaction.
Parameter:
Returns:
Status Code
NOTES:
- You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will take effect for this process and all other current processes in InterSystems IRIS after this function is called. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the Lock timeout for locks acquired during execution of SQL statements.
Parameter:
Returns:
Status Code
NOTES:
- You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will only take effect for this process and new processes starting in InterSystems IRIS after this function is called. Any existing processes will still use the old setting. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetMapSelectability() in <class>SYSTEM.SQL.Util</class> instead.
This entrypoint is used to make an SQL Map definition [not] selectable to the SQL Query Optimizer
Parameters:
Returns:
if the call was successful, Otherwise an error message is returned in a string format.
Note:
feature is not useful for extent indices (non bitmap). When a non-bitmap extent index is defined, the data map projected to SQL will use both the extent index global and the data map global.
When a non-bitmap extent index is added to a table with existing data, the index must be populated before any queries are run against the table. If not, any queries plans that loop over the data map will return no data.
feature sets the selectability for the map in the current namespace only. If this table is mapped to multiple namespaces, and the index needs to be built in each namespace, SetMapSelectability should be called in each namespace the index is to be built in.
, this feature sets the selectability for the map in the current table only. If there is a subclass that inherits the index map, and the index is being rebuilt for the entire extent, SetMapSelectability should be called for each table/map in the extent.
Examples:
Returns:
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the Lock Timeout for this process.
The lock timeout (in seconds) for locks made during execution of SQL statements.
Setting takes effect immediately and lasts for the duration of the process or until SetProcessLockTimeout is called again.
An integer value should be specified for value.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Defines whether or not all class queries project as SQL Stored Procedures regardless of the query's SqlProc value. The default is class queries are not projected as stored procedures unless the query SqlProc setting is TRUE.
Parameter:
Returns:
Status Code
Examples:
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - The setting is on a per system basis.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the configuration setting which determines if Run Time Plan Choice is applied. The default is that Run Time Plan Choice is applied.
Parameter:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the configuration setting which determines if embedded SQL statements are retained as comments in the .INT code version of the routine. The default is no SQL comments are retained.
Parameter:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the system wide flag for controlling if SQL Functions perform ODBCToLogical/DisplayToLogical on SQL Function input arguments. Setting takes effect immediately for all new compilations/prepares of SQL statements system wide.
Change does not affect already compiled statements or cached dynamic statements compiled with the previous value.
Parameters:
Returns:
Old value of the SQLFunctionArgConversion setting
Example:
NOTES: - Setting takes effect immediately for all new compilations/prepares of SQL statements system wide.
- Change does not affect already compiled statements or cached dynamic statements compiled with the previous value.
- This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the configuration setting which determines if SQL security is enabled. If SQL security is ON, all SQL security is active. This means:
If SQL security is OFF, SQL security is inactive. This means:
Parameter:
Returns:
Status Code
NOTES:
- You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will only take effect for this process and new processes starting in InterSystems IRIS after this function is called. Any existing processes will still use the old setting. - This is a system-wide setting.
|
static |
Method: SetSQLStats [SQL: StatsSQL_SetSQLStats] Purpose: This method sets the flag that controls whether or not the <IHD> 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}:{flag-type} </IHD> Invocation: This method can be invoked in the following ways: <IHD> Object Script: $SYSTEM.SQL.SetSQLStats(...)
SQL: SELECT SYS_PTools.StatsSQL_SetSQLStats(...) </IHD> Examples: The following examples shows the use of this method: <IHD>
#1 Turn PTools ON to collects stats for all SQL modules (System):
set oldStats=##class(%SYS.PTools.StatsSQL).SetSQLStats(3)
</IHD>
Parameters: <IHP> 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 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
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
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:<min>:<raf> Where <min>=# of minutes from 1..n When specified, this value will terminate the SQLStats collection the number of minutes <min> after the current $H Where <raf>=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 <raf> to zero (0) (e.g. "M:120:1" => Terminate 120minutes after the current $H and reset SQLStats-flag/{action-flag} to 1) T:<ts>:<raf> Where <ts>=Timestamp with the format: YYYYMMDD HHMM When specified, this value will terminate the SQLStats collection after the timestamp value Where <raf>=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 <raf> 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 </IHP> RETURN: The value of the SQLStats-flag before setting it to the new value; Otherwise, return an error message if an error occurred
|
static |
Method: SetSQLStatsFlag [SQL: StatsSQL_SetSQLStatsFlag] Purpose: See <method>SetSQLStats</method> for details!
Invocation: This method can be invoked in the following ways: <IHD> Object Script: $SYSTEM.SQL.SetSQLStatsFlag(...)
SQL: SELECT SYS_PTools.StatsSQL_SetSQLStatsFlag(...) </IHD>
|
static |
Method: SetSQLStatsFlagByNS [SQL: StatsSQL_SetSQLStatsFlagByNS] Purpose: This method sets the flag that controls whether or not to collect <IHD> 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}:{flag-type} </IHD> NOTE: This method ONLY applies to the SQLStats-flag (System) for the <IHD> given 'ns' and NOT the SQLStats-flag (Process/Job)! </IHD> Invocation: This method can be invoked in the following ways: <IHD> Object Script: $SYSTEM.SQL.SetSQLStatsFlagByNS(...)
SQL: SELECT SYS_PTools.StatsSQL_SetSQLStatsFlagByNS(...) </IHD> Examples: The following examples shows the use of this method: <IHD>
#1 Turn PTools ON to collects stats for all SQL modules within the "USER" Namespace:
set oldStats=##class(SYS.PTools.StatsSQL).SetSQLStatsFlagByNS("USER",3) </IHD> Parameters: <IHP> ns - 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 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
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
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:<min>:<raf> Where <min>=# of minutes from 1..n When specified, this value will terminate the SQLStats collection the number of minutes <min> after the current $H Where <raf>=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 <raf> to zero (0) (e.g. "M:120:1" => Terminate 120minutes after the current $H and reset SQLStats-flag/{action-flag} to 1) T:<ts>:<raf> Where <ts>=Timestamp with the format: YYYYMMDD HHMM When specified, this value will terminate the SQLStats collection after the timestamp value Where <raf>=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 <raf> 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 </IHP> RETURN: The value of the SQLStats-flag before setting it to the new value; Otherwise, return an error message if an error occurred
|
static |
Method: SetSQLStatsFlagByPID [SQL: StatsSQL_SetSQLStatsFlagByPID] Purpose: This method sets the flag that controls whether or not to collect <IHD> 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}:{flag-type} </IHD> NOTE: This method ONLY applies to the SQLStats-flag for the given <IHD> 'pid' (Process/Job) and NOT the SQLStats-flag for the (System)!
RESTRICTION: This method invocation requires Admin_Operate:Use privilege </IHD> Invocation: This method can be invoked in the following ways: <IHD> Object Script: $SYSTEM.SQL.SetSQLStatsFlagByPID(...)
SQL: SELECT SYS_PTools.StatsSQL_SetSQLStatsFlagByPID(...) </IHD> Examples: The following examples shows the use of this method: <IHD>
#1 Turn PTools ON to collects stats for all SQL modules for PID# 12345:
set oldStats=##class(SYS.PTools.StatsSQL).SetSQLStatsFlagByPID(12345,3) </IHD> Parameters: <IHP> 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 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
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
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. p5 - 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
NOTE: This method currently returns no 'ptInfo', but is included for future extensibility </IHP> RETURN: The value of the SQLStats-flag before setting it to the new value; Otherwise, return an error message if an error occurred
|
static |
Method: SetSQLStatsFlagJob [SQL: StatsSQL_SetSQLStatsFlagJob] Purpose: See <method>SetSQLStatsJob</method> for details!
Invocation: This method can be invoked in the following ways: <IHD> Object Script: $SYSTEM.SQL.SetSQLStatsFlagJob(...)
SQL: SELECT SYS_PTools.StatsSQL_SetSQLStatsFlagJob(...) </IHD>
|
static |
Method: SetSQLStatsJob [SQL: StatsSQL_SetSQLStatsJob] Purpose: This method sets the flag that controls whether or not this <IHD> 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}:{flag-type} </IHD> Invocation: This method can be invoked in the following ways: <IHD> Object Script: $SYSTEM.SQL.SetSQLStatsJob(...)
SQL: SELECT SYS_PTools.StatsSQL_SetSQLStatsJob(...) </IHD> Examples: The following examples shows the use of this method: <IHD>
#1 Turn PTools ON to collects stats for all SQL modules:
set oldStats=##class(%SYS.PTools.StatsSQL).SetSQLStatsJob(3)
</IHD>
Parameters: <IHP> 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
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
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 </IHP> RETURN: The value of the SQLStats-flag before setting it to the new value; Otherwise, return an error message if an error occurred
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the select mode for this process. Setting takes effect immediately and lasts for the duration of the process or until SetSelectMode is called again.
Valid values are:
| 0 | Logical |
| 1 | ODBC |
| 2 | Display |
If a valid value is passed in, then the select mode for the current process will be set to that value and the previous select mode value is returned. Otherwise, the select mode setting is left unchanged and pStatus will contain a Status value describing the error and the current select mode value is returned.
The select mode set by this API is used when:
#SQLCompile Select = Runtime
is specified for the compiled SQL code.
When the SQL statement is compiled in Runtime mode, the mode specified by SetSelectMode will be used at runtime to determine whether Logical(0), ODBC(1), or Display(2) mode is used for the statement. See the documentation for #SQLCompile Select for more information.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Define ODBC/JDBC disconnect code. Upon disconnect any ObjectScript code defined in this setting will be executed immediately before the server process Halts. This code will also attempt to be executed anytime the server process encounters an unrecoverable error that causes the server process to Halt.
It will not be executed if the server process encounters a Halt in other user defined code, if the process crashes, if the process is stopped, or InterSystems IRIS is stopped or forced down. The disconnect code is defined on a per-namespace basis.
Parameter:
Examples:
- Changing this configuration setting will take effect immediately for all server processes in InterSystems IRIS after this function is called. Any existing processes will execute the server disconnect code disconnection.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Define ODBC/JDBC/SQL Manager initialization code. This Initialization code is executed at login time when connecting to SQL through ODBC, JDBC, or the SQL Manager. Initialization code is defined on a per-namespace basis.
Parameter:
Examples:
- Changing this configuration setting will only take effect for new processes starting in InterSystems IRIS after this function is called. Any existing processes will not have executed the server init code upon connection.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the TCP Keep Alive interval for xDBC connections.
Parameter:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting.
|
static |
THIS METHOD IS DEPRECATED.
Refer to SetOption() in <class>SYSTEM.SQL.Util</class> instead.
Set the default format for the SQL TO_DATE() function.
Parameter:
Returns:
Status Code
NOTES: - You must have the "USE" permission on the Admin Manage Resource in order to change this configuration setting. - Changing this configuration setting will affect all processes immediately. - This is a system-wide setting.
|
static |
|
static |
THIS METHOD IS DEPRECATED.
Refer to <method>Explain</method> instead.
Display the execution plan for an SQL statement
Parameter:
|
static |
THIS METHOD IS DEPRECATED.
Refer to <method>Explain</method> instead.
Display the execution plan for an SQL statement, then list other query costs the user can select to see alternate plans
Parameter:
|
static |
THIS METHOD IS DEPRECATED.
Refer to LoadSybase() in <class>SYSTEM.SQL.Schema</class> instead.
Import a Sybase DDL/DML script file.
The Sybase DDL/DML Import Utility supports the following statements:
Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).
|
static |
THIS METHOD IS DEPRECATED.
Refer to TOCHAR() in <class>SYSTEM.SQL.Functions</class> instead.
TOCHAR is a general SQL string function that converts a given date, timestamp, or number value to a string.
$SYSTEM.SQL.TOCHAR(expr,format)
The first use of TOCHAR is to convert a date, time, or datetime expression to a string.
The second use of TOCHAR is to convert a number to a string.
See the TO_CHAR Documentation in the SQL Reference for complete details.
|
static |
THIS METHOD IS DEPRECATED.
Refer to TODATE() in <class>SYSTEM.SQL.Functions</class> instead.
TODATE is a general SQL string function that converts a given string expression to a value of DATE data type.
$SYSTEM.SQL.TODATE(expr,format)
The use of TODATE is to specify the input format of a string value containing a date to be converted to a Logical <class>Library.Date</class> value. The format of expr is specified in the format parameter. format will be used as a key to translate expr into a valid <class>Library.Date</class> logical value.
See the TO_DATE Documentation in the SQL Reference for complete details.
|
static |
THIS METHOD IS DEPRECATED.
Refer to TOPOSIXTIME() in <class>SYSTEM.SQL.Functions</class> instead.
TOPOSIXTIME is a general SQL string function that converts a given string expression to a logical value of a <class>Library.PosixTime</class> data type.
$SYSTEM.SQL.TOPOSIXTIME(stringexp,format)
The use of TOPOSIXTIME is to specify the input format of a string value containing a datetime to be converted to a Logical <class>Library.PosixTime</class> value. The format of stringexp is specified in the format parameter. format will be used as a key to translate stringexp into a valid <class>Library.PosixTime</class> logical value. If an error occurs during the execution of TOPOSIXTIME, the function will return a value of 0 and an error message will be defined in the msg variable.
See the TO_POSIXTIME Documentation in the SQL Reference for complete details.
|
static |
THIS METHOD IS DEPRECATED.
Refer to TOTIMESTAMP() in <class>SYSTEM.SQL.Functions</class> instead.
TOTIMESTAMP is a general SQL string function that converts a given string expression to a value of TIMESTAMP data type.
$SYSTEM.SQL.TOTIMESTAMP(stringexp,format)
The use of TOTIMESTAMP is to specify the input format of a string value containing a datetime to be converted to a Logical <class>Library.TimeStamp</class> value. The format of stringexp is specified in the format parameter. format will be used as a key to translate stringexp into a valid <class>Library.TimeStamp</class> logical value. If an error occurs during the execution of TOTIMESTAMP, the function will return a value of 0 and an error message will be defined in the msg variable.
See the TO_TIMESTAMP Documentation in the SQL Reference for complete details.
|
static |
THIS METHOD IS DEPRECATED.
Refer to TRUNCATE() in <class>SYSTEM.SQL.Functions</class> instead.
TRUNCATE returns the Truncate collation of the passed in value.
$SYSTEM.SQL.TRUNCATE(stringexp,maxlen)
|
static |
THIS METHOD IS DEPRECATED.
Refer to LoadTSQL() in <class>SYSTEM.SQL.Schema</class> instead.
Import a TSQL script file.
TSQL source files can contain any TSQL syntax supported by InterSystems IRIS TSQL.
This API will put the caller to the SQL Shell in the default TSQL dialect as defined in the TSQL Compatibility Settings.
The caller can then use the run [filename] command from the shell to import the script file.
|
static |
This entry point can be used to invoke the TSQL shell.
(no parameters or result)
Example:
In the shell type ? for help
|
static |
THIS METHOD IS DEPRECATED.
Refer to TableExists() in <class>SYSTEM.SQL.Schema</class> instead.
This entry point can be used to determine if a base table exists.
Parameters:
Examples:
Notes:
This method can also be called as a Stored Procedure named SYSTEM.SQL_TableExists(tablename)
|
static |
THIS METHOD IS DEPRECATED.
Refer to GatherSchemaStats() in <class>SYSTEM.SQL.Stats.Table</class> instead.
Calculate accurate extentsize and selectivity for all classes/tables and their properties/fields within a schema.
Parameters:
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to GatherTableStats() in <class>SYSTEM.SQL.Stats.Table</class> instead.
Calculate accurate extentsize and selectivity for a class/table and its properties/fields.
Parameter:
Examples:
|
static |
THIS METHOD IS DEPRECATED.
Refer to UPPER() in <class>SYSTEM.SQL.Functions</class> instead.
UPPER returns the Upper collation of the passed in value.
$SYSTEM.SQL.UPPER(stringexp)
UPPER converts all alphabetic characters to upper case (i.e., the UPPER format). Note that punctuation is not changed.
|
static |
THIS METHOD IS DEPRECATED.
Refer to UserExists() in <class>SYSTEM.SQL.Security</class> instead.
This entry point can be used to determine if a user exists.
Parameters:
Examples:
This method can also be called as a Stored Procedure named SYSTEM.SQL_UserExists(username)
|
static |
THIS METHOD IS DEPRECATED.
Refer to ValidateTable() in <class>SYSTEM.SQL.Schema</class> instead.
Validate data for a table
This utility can be called via $SYSTEM.SQL.ValidateTable(tablename) or as the SYSTEM.ValidateTable(tablename) stored procedure. This method/procedure returns a resultset that contains a row for each issue found with the table's data. If the resultset is empty, the table has no data validation issues. The table's data is checked for the following:
Each field's validation code from the IsValid method of the field's datatype Required fields do not have a null value Unique constraints do not have duplicate values Foreign Key fields reference valid rows in the referenced table
Parameters:
Returns: Status Code
Example: Set status=$SYSTEM.SQL.ValidateTable("Sample.Person") // Validates the data in the Sample.Person table Set status=$SYSTEM.SQL.ValidateTable("Company") // Validates the data in the SQLUser.Company table call SYSTEM.ValidateTable('Sample.Person') // Validates the data in the Sample.Person table via SQL
Notes:
|
static |
THIS METHOD IS DEPRECATED.
Refer to ViewExists() in <class>SYSTEM.SQL.Schema</class> instead.
This entry point can be used to determine if a view exists.
Parameters:
Examples:
Notes:
This method can also be called as a Stored Procedure named SYSTEM.SQL_ViewExists(viewname)
|
static |
THIS METHOD IS DEPRECATED.
Refer to WEEK() in <class>SYSTEM.SQL.Functions</class> instead.
WEEK is a date/time function that returns an integer from 1 to 53 that corresponds to the week of the year in a given date expression.
$SYSTEM.SQL.WEEK(dateexp)
|
static |
THIS METHOD IS DEPRECATED.
Refer to YEAR() in <class>SYSTEM.SQL.Functions</class> instead.
YEAR is a date/time function that returns an integer in the range 1840-9999 that indicates the year in a given date expression.
$SYSTEM.SQL.YEAR(dateexp)
1.9.1