Skip to main content

%SYSTEM.SQL

abstract class %SYSTEM.SQL extends %SYSTEM.Help

The %SYSTEM.SQL 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()

Method Inventory

Methods

deprecated classmethod ABS(val As %Library.String = "") as %Library.Float
THIS METHOD IS DEPRECATED. Refer to ABS() in %SYSTEM.SQL.Functions instead. ABS returns the absolute value, which is always zero or a positive number

$SYSTEM.SQL.ABS(numeric-expression)

numeric-expression
A number whose absolute value is to be returned
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.
deprecated classmethod ALPHAUP(stringexp As %Library.String = "") as %Library.String
THIS METHOD IS DEPRECATED. Refer to ALPHAUP() in %SYSTEM.SQL.Functions instead. ALPHAUP returns the Alphaup collation of the passed in value.
$SYSTEM.SQL.ALPHAUP(stringexp)
stringexp Any string expression value.
ALPHAUP converts all alphabetic characters to upper case (i.e., the ALPHAUP format) and removes all punctuation except commas and question marks.
deprecated classmethod CEILING(val As %Library.String = "") as %Library.Float
THIS METHOD IS DEPRECATED. Refer to CEILING() in %SYSTEM.SQL.Functions 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)

numeric-expression
A number whose ceiling is to be calculated
deprecated classmethod CONVERT(expr As %Library.String = "", convto As %Library.String = "", convfrom As %Library.String = "") as %Library.String
THIS METHOD IS DEPRECATED. Refer to CONVERT() in %SYSTEM.SQL.Functions instead. CONVERT is a SQL function that converts a given expression to a specified data type.

$SYSTEM.SQL.CONVERT(expr,convto,convfrom)

expr
The expression to be converted
convto
The data type to which expr is to be converted. Currently, only "SQL_TIMESTAMP", "SQL_POSIXTIME", "SQL_TIME", "SQL_DATE", "SQL_VARCHAR", "SQL_INTEGER", and "SQL_DOUBLE" are supported
convfrom
The data type of expr. Currently, only "SQL_TIMESTAMP", "SQL_POSIXTIME", "SQL_TIME", "SQL_DATE", "SQL_VARCHAR", "SQL_INTEGER", and "SQL_DOUBLE" are supported
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 %Library.Date, %Library.PosixDate, or %Library.Time value. When converting from SQL_VARCHAR to SQL_TIME, the input value is expected to be an ODBC %Library.Time formatted value. When converting from SQL_VARCHAR to SQL_DATE, the input value is expected to be an ODBC %Library.Date 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 %Library.Date, %Library.PosixTime, or %Library.Time value.
classmethod CancelQuery(pid As %String = "", SQLStatementID As %Integer = "", timeout As %Integer = 10) as %Library.Status [ SQLProc = CancelQuery ]
Projected as the stored procedure: CancelQuery

Cancel the SQL Query running on the specified process (pid) and referenced by the specified SQL Statement Identifier (SQLStatementID)...


Parameters:
pid
The process ID ($JOB) running the SQL Query to cancel
SQLStatementID
A unique SQL Statement Identifier which references the running SQL Statement/Query to cancel
If a SQLStatementID is not specified, then cancel the first SQL Query found running on the specified pid.
timeout
The maximum number of seconds to process the cancel query. [DEFAULT: 10]

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

  • set tSC=$SYSTEM.SQL.CancelQuery(12345,11235)

Examples (SQL):

  • CALL %SYSTEM.CancelQuery(12345,11235)
  • SELECT %SYSTEM.CancelQuery(12345,11235)
deprecated classmethod CheckPriv(Username As %String, Object As %String, Action As %String, Namespace As %String = "", Grant As %Integer = 0) as %String
THIS METHOD IS DEPRECATED. Refer to CheckPrivilege() and CheckPrivilegeWithGrant() in %SYSTEM.SQL.Security instead. Check if user has SQL privilege for a particular action.

Parameters:
Username
Name of the user to check. Required.
Object
ObjectType,ObjectName of the table, view, procedure, or ml configuration to check the privilege of. ObjectTypes are 1 (table), 3 (view), 6 (ml configuration), or 9 (procedure). For example, "1,Sample.Person" or "9,SQLUser.My_Procedure". Required.
Action
Comma delimited string of actions letters to check privileges for. Actions are one or more of the letters "a,s,i,u,d,r,e,l" (in any order) which stands for ALTER,SELECT,INSERT,UPDATE,DELETE,REFERENCES,EXECUTE,USE (for ML Configurations). Privilege "e" is only allowed for Procedures. Privilege "l" is only allowed for ML Configurations. CheckPriv will only return 1 if the user has privileges on all Actions specified. Required.
Namespace
Namespace object resides in (optional) default is current namespace
Grant
1/0 flag - check grant privileges (optional) default is 0

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:

    • Set return=$SYSTEM.SQL.CheckPriv($username,"1,HHR.ProductionValues","s,i,u,d","USER",1)
    • Set return=$SYSTEM.SQL.CheckPriv("Miranda","3,SQLUser.Person","s","PRODUCT",0)
    • Set return=$SYSTEM.SQL.CheckPriv("Miranda","6,%H2O","l","DEPLOY",0)
  • deprecated classmethod CleanStaleStatements()
    THIS METHOD IS DEPRECATED. Refer to Clean() in %SYSTEM.SQL.Statement instead. Cleanup any SQL statement index entries that no longer have any routines/classes associated with them
    deprecated classmethod ClearStatistics(Scope As %Library.SmallInt = 1, ScopeArgument As %Library.String = "", ByRef Errors As %Library.String)
    THIS METHOD IS DEPRECATED. Refer to ClearAll() in %SYSTEM.SQL.Statement instead. Clear SQL statistics for SQL statements in his namespace

    Parameter:
    Scope
    If Scope=1, clear the stats for all query plans in this namespace. This is the default Scope.
    If Scope=2, clear the stats for all query plans for the schema provided in ScopeArgument.
    If Scope=3, clear the stats for all query plans for the relation provided in ScopeArgument.
    If Scope=4, clear the stats for the query plan with the statement hash provided in ScopeArgument.
    ScopeArgument
    If Scope=2, the name of the schema for which to freeze the plans, if Scope=3, the name of the relation for which to freeze the plans.
    If Scope=3, the name of the relation needs to be in what we call internal qualified format. This is typically simply Schema.Tablename, but if tablename itself contains any "." characters, it needs to be in Schema_"."_$translate(Tablename,".",$Char(2)) format.
    If Scope=4, the value of the statement hash id. This is typically hash value like "3DgIqc72NS+Np6nybddb719NKb8=".
    Errors
    Passed by reference. Returns an array of error messages if there are any failures while freezing plans.

    Examples:

    • Do $SYSTEM.SQL.ClearStatistics(1,,.Errors)) // Clear SQL statistics for all statements in the current namespace
    • Do $SYSTEM.SQL.ClearStatistics(2,"XLT",.Errors)) // Clear SQL statistics for all statements for the current namespace for each statement that references a relation in the XLT schema.
    • Do $SYSTEM.SQL.ClearStatistics(3,"XLT.Person",.Errors)) // Clear SQL statistics for all statements for the current namespace for each statement that references the XLP.Person table.
    • Do $SYSTEM.SQL.ClearStatistics(4,"3DgIqc72NS+Np6nybddb719NKb8=",.Errors)) // Clear SQL statistics for statement identified by hash "3DgIqc72NS+Np6nybddb719NKb8=".
    deprecated classmethod CreateLinkedProcedure(dsn As %String, externalSchema As %String, externalProcedure As %String, localPackage As %String = "LinkedSchema", localClass As %String = "LinkedProcedure", localMethod As %String = "", localSqlName As %String = "", description As %String = "") as %Status
    THIS METHOD IS DEPRECATED. Refer to CreateLinkedProcedure() in %SYSTEM.SQL.Schema 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

    deprecated classmethod CreateLinkedTable(dsn As %String, externalSchema As %String, externalTable As %String, primaryKeys As %String, localClass As %String = "User.LinkedClass", localTable As %String, ByRef columnMap As %String = "") as %Status
    THIS METHOD IS DEPRECATED. Refer to CreateLinkedTable() in %SYSTEM.SQL.Schema 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

    classmethod CurrentSettings() as %Library.String
    Displays all the current SQL settings to the current device.
    deprecated classmethod DATE(exp As %Library.String = "") as %Library.Date
    THIS METHOD IS DEPRECATED. Refer to DATE() in %SYSTEM.SQL.Functions instead. DATE is a date/time function that returns the date portion of the given date or timestamp expression

    $SYSTEM.SQL.DATE(exp)

    exp
    An expression that is a logical %Library.Date, %Library.TimeStamp, %Library.PosixTime, %Library.Integer, or %Library.String value.
    If "" (null) is passed in, "" (null) is returned. If exp is not numeric, it is assumed the expression is in %Library.TimeStamp logical format. Note, that if a string value is passed in as exp, it is expected that the value is a logical %Library.TimeStamp format, and not $Horolog format. To convert $Horolog to DATE, use $SYSTEM.SQL.DATE(+$HOROLOG),
    deprecated classmethod DATEADD(datepart As %Library.String = "", val As %Library.Numeric = "", dateexp As %Library.String = "") as %Library.TimeStamp
    THIS METHOD IS DEPRECATED. Refer to DATEADD() in %SYSTEM.SQL.Functions 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)

    datepart
    The full name or abbreviation of a date or time part.
    The following date parts are supported for DATEADD
    year yy yyyy
    quarter qq q
    month mm m
    week wk ww
    dayofyear dy y
    day dd d
    hour hh
    minute mi n
    second ss s
    millisecond ms
    numeric-exp
    A numeric expression of any number type. The value is truncated to an integer. The value indicates the number of dateparts that will be added to dateexp.
    dateexp
    The date/time expression that will be modified.
    The dateexp parameter can be in any of the following formats, and may include or omit fractional seconds:
    • A %Library.Date logical value (+$H)
    • A %Library.PosixTime logical value
    • A %Library.TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
    • A %Library.String (or compatible) value
    • The %Library.String (or compatible) value can be in any of the following formats:
      %Library.PosixTime logical value 99999,99999 ($H format)
      Sybase/SQL-Server-date Sybase/SQL-Server-time
      Sybase/SQL-Server-time Sybase/SQL-Server-date
      Sybase/SQL-Server-date (default time is 00:00:00)
      Sybase/SQL-Server-time (default date is 01/01/1900)
      Sybase/SQL-Server-date is one of these five formats:
      mmdelimiterdddelimiter[yy]yy
      dd Mmm[mm][,][yy]yy
      dd [yy]yy Mmm[mm]
      yyyy Mmm[mm] dd
      yyyy [dd] Mmm[mm]
      where delimiter is a slash (/), hyphen (-), or period (.).
      Sybase/SQL-Server-time represents one of these three formats:
      HH:MM[:SS:SSS][{AM|PM}]
      HH:MM[:SS.S]
      HH['']{AM|PM}
    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.
    deprecated classmethod DATEDIFF(datepart As %Library.String = "", startdate As %Library.String = "", enddate As %Library.String = "") as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to DATEDIFF() in %SYSTEM.SQL.Functions instead. DATEDIFF is a general date/time function that returns the interval between two dates

    $SYSTEM.SQL.DATEDIFF(datepart,startdate,enddate)

    datepart
    The date part in which the interval is to be measured.
    The following date parts are supported for DATEDIFF
    year yy yyyy
    month mm m
    week wk ww
    day dd d
    hour hh
    minute mi n
    second ss s
    millisecond ms
    startdate
    The starting date for the interval.
    enddate
    The ending date for the interval.
    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:
    • A %Library.Date logical value (+$H)
    • A %Library.PosixTime logical value
    • A %Library.TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
    • A %Library.String (or compatible) value
    • The %Library.String (or compatible) value can be in any of the following formats, and may include or omit fractional seconds:

    • 99999,99999 ($HOROLOG format)
      Sybase/SQL-Server-date Sybase/SQL-Server-time
      Sybase/SQL-Server-time Sybase/SQL-Server-date
      Sybase/SQL-Server-date (default time is 00:00:00)
      Sybase/SQL-Server-time (default date is 01/01/1900)
    • Sybase/SQL-Server-date is one of these five formats:

    • mmdelimiterdddelimiter[yy]yy
      dd Mmm[mm][,][yy]yy
      dd [yy]yy Mmm[mm]
      yyyy Mmm[mm] dd
      yyyy [dd] Mmm[mm]
      where delimiter is a slash (/), hyphen (-), or period (.).
    • Sybase/SQL-Server-time represents one of these three formats:

    • HH:MM[:SS:SSS][{AM|PM}]
      HH:MM[:SS.S]
      HH['']{AM|PM}
    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.
    deprecated classmethod DATENAME(datepart As %Library.String = "", dateexp As %Library.String = "") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to DATENAME() in %SYSTEM.SQL.Functions 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)

    datepart
    The type of date part that the returned value will represent.
    The following date parts are supported for DATENAME
    year yy yyyy
    quarter qq q
    month mm m
    week wk ww
    weekday dw
    dayofyear dy y
    day dd d
    hour hh
    minute mi n
    second ss s
    millisecond ms
    dateexp
    A date/time expression from which the date part is to be returned.
    The dateexp parameter can be in any of the following formats:
    • A %Library.Date logical value (+$H)
    • A %Library.PosixTime logical value
    • A %Library.TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
    • A %Library.String (or compatible) value
    • The %Library.String (or compatible) value can be in any of the following formats, and may include or omit fractional seconds:

    • 99999,99999 ($HOROLOG format)
      Sybase/SQL-Server-date Sybase/SQL-Server-time
      Sybase/SQL-Server-time Sybase/SQL-Server-date
      Sybase/SQL-Server-date (default time is 00:00:00)
      Sybase/SQL-Server-time (default date is 01/01/1900)
    • Sybase/SQL-Server-date is one of these five formats:

    • mmdelimiterdddelimiter[yy]yy
      dd Mmm[mm][,][yy]yy
      dd [yy]yy Mmm[mm]
      yyyy Mmm[mm] dd
      yyyy [dd] Mmm[mm]
      where delimiter is a slash (/), hyphen (-), or period (.).
    • Sybase/SQL-Server-time represents one of these three formats:

    • HH:MM[:SS:SSS][{AM|PM}]
      HH:MM[:SS.S]
      HH['']{AM|PM}
    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.
    deprecated classmethod DATEPART(datepart As %Library.String = "", dateexp As %Library.String = "") as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to DATEPART() in %SYSTEM.SQL.Functions 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)

    datepart
    The type of date part that the returned value will represent.
    The following date parts are supported for DATEPART
    year yy yyyy
    quarter qq q
    month mm m
    week wk ww
    weekday dw
    dayofyear dy y
    day dd d
    hour hh
    minute mi n
    second ss s
    millisecond ms
    sqltimestamp sts
    The sqltimestamp (abbreviated sts) datepart value is for use only with DATEPART. Do not attempt to use this parameter value in other contexts
    dateexp
    A date/time expression from which the date part is to be returned.
    dateexp must contain a value of type datepart.
    The dateexp parameter can be in any of the following formats:
    • A %Library.Date logical value (+$H)
    • A %Library.PosixTime logical value
    • A %Library.TimeStamp logical value (YYYY-MM-DD HH:MM:SS)
    • A %Library.String (or compatible) value
    • The %Library.String (or compatible) value can be in any of the following formats, and may include or omit fractional seconds:

    • 99999,99999 ($HOROLOG format)
      Sybase/SQL-Server-date Sybase/SQL-Server-time
      Sybase/SQL-Server-time Sybase/SQL-Server-date
      Sybase/SQL-Server-date (default time is 00:00:00)
      Sybase/SQL-Server-time (default date is 01/01/1900)
    • Sybase/SQL-Server-date is one of these five formats:

    • mmdelimiterdddelimiter[yy]yy
      dd Mmm[mm][,][yy]yy
      dd [yy]yy Mmm[mm]
      yyyy Mmm[mm] dd
      yyyy [dd] Mmm[mm]
      where delimiter is a slash (/), hyphen (-), or period (.).
    • Sybase/SQL-Server-time represents one of these three formats:

    • HH:MM[:SS:SSS][{AM|PM}]
      HH:MM[:SS.S]
      HH['']{AM|PM}
    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.
    deprecated classmethod DAYNAME(dateexp As %Library.String = "") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to DAYNAME() in %SYSTEM.SQL.Functions 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)

    dateexp
    An expression that is a logical %Library.Date, %Library.TimeStamp, or %Library.PosixTime value.
    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.
    deprecated classmethod DAYOFMONTH(dateexp As %Library.String = "") as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to DAYOFMONTH() in %SYSTEM.SQL.Functions 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)

    dateexp
    An expression that is a logical %Library.Date, %Library.TimeStamp, or %Library.PosixTime.
    deprecated classmethod DAYOFWEEK(dateexp As %Library.String = "") as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to DAYOFWEEK() in %SYSTEM.SQL.Functions 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)

    dateexp
    An expression that is a logical %Library.Date value, %Library.TimeStamp, %Library.PosixTime, or an $Horolog value.
    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.
    deprecated classmethod DAYOFYEAR(dateexp As %Library.String = "") as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to DAYOFYEAR() in %SYSTEM.SQL.Functions 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)

    dateexp
    An expression that is a logical %Library.Date value, %Library.TimeStamp, or %Library.PosixTime value.
    deprecated classmethod DDLImport(DDLMode As %Library.String = "", SQLUser As %Library.String, infile As %Library.String, outfile As %Library.String, nosup As %Library.Boolean = 0, nosupfile As %Library.String, deos As %Library.String, errpause As %SmallInt = 5, runtimemode As %String = "Logical") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to ImportDDL() in %SYSTEM.SQL.Schema instead. Import a DDL/DML script file.

    Parameters:
    DDLMode
    Vendor from which the script file originated. This parameter is required. Supported values are:
    • IRIS
    • CACHE
    • FDBMS
    • Informix
    • Interbase
    • MSSQL
    • MSSQLServer - same as MSSQL
    • MySQL
    • Oracle
    • Sybase
    SQLUser
    This parameter is deprecated and no longer used. The owner of new classes is always defined as the $Username of the process that executed the DDL statement
    infile
    The full path name of the script file to import. This parameter is required. A $list value may also be passed in with the filename and the TranslateTable to use for the file. (For information on translate tables, see Translation Tables.) The first element is the file name and the second element is the TranslateTable for the input stream file. This is only supported for DDLMode MSSQL, Sybase, Informix, or MySQL.
    outfile
    The full path name of the file to report errors in. This parameter is Optional. Default is the same as the infile with a _Errors.log extension.
    nosup
    TRUE(1)/FALSE(0) flag. Determines if unsupported statements from the script file should be recorded in the nosupfile. This parameter is optional, 0 is the default.
    nosupfile
    If nosup is true, the name of the file to log the unsupported statements in. Default is the same as the infile with a _Unsupported.log extension. This parameter is optional.
    deos
    End of statement delimiter. Will default to an appropriate value based on the value of %DDLMode. This parameter is optional.
    errpause
    Number of seconds to pause after an error occurs. The default is 5 seconds. This parameter is optional.
    runtimemode
    Which selectmode to run the imported statement in, ODBC, DISPLAY, or LOGICAL. This only applies when DDLMode=IRIS. If any value other than ODBC or DISPLAY is specified, LOGICAL mode is used. This parameter is optional.

    When DDLMode=IRIS, the following statements are supported:

    • CREATE ...
    • DROP ...
    • TRUNCATE TABLE ...
    • ALTER ...
    • INSERT ...
    • UPDATE ...
    • DELETE ...
    • SET OPTION ...
    • GRANT ...
    • REVOKE ...

    Examples:

    • Do $SYSTEM.SQL.DDLImport("Sybase","_SYSTEM","C:\PT\Patient.sql")
    • Do $SYSTEM.SQL.DDLImport("Oracle","DAVE","C:\DDT\all_tables.sql",all.log,0,"",";",2)
    deprecated classmethod DDLImportDir(DDLMode As %Library.String = "", directory As %Library.String = "", logfile As %Library.String = "", eosDelimiter As %Library.String = "")
    THIS METHOD IS DEPRECATED. Refer to ImportDDLDir() in %SYSTEM.SQL.Schema instead. Import all DDL/DML script file in a given directory. All files with the extension .sql in the directory will be imported.

    Parameters:
    DDLMode
    Vendor from which the script file originated. This parameter is required. Supported values are:
    • Informix
    • MSSQL
    • MSSQLServer - same as MSSQL
    • Sybase
    directory
    The full path name of the directory to import. This parameter is required.
    logfile
    The full path name of the file to report errors in. This parameter is Optional. Default is DDLImportDir.log in the directory loaded. If this parameter value is 1, a separate log file will be generated for each file loaded. The name of the log file will be the same as the file imported, but with the extension .log instead of .sql.
    eosDelimiter
    End of statement delimiter. Will default to an appropriate value based on the value of %DDLMode. This parameter is optional.

    Examples:

    • Do $SYSTEM.SQL.DDLImportDir("Informix","C:\Work\db1\","C:\Work\db1\import.log",";")
    • Do $SYSTEM.SQL.DDLImportDir("Informix","C:\Work\db1\",1,";")
    deprecated classmethod DefaultSchema() as %Library.String
    THIS METHOD IS DEPRECATED. Refer to Default() in %SYSTEM.SQL.Schema instead. Return the default schema name for the current process in the current namespace

    Example:

    Set CurrentSchema = $SYSTEM.SQL.DefaultSchema()
    
    deprecated classmethod DropAll() as %Integer
    THIS METHOD IS DEPRECATED. Refer to CloseAllGateways() in %SYSTEM.SQL.Util instead. This method closes all the open ODBC/JDBC Gateway connections and unloads the SQL Gateway shared library.
    deprecated classmethod DropTable(tablename As %Library.String, deldata As %Library.Boolean = 0, ByRef SQLCODE As %Library.Integer, ByRef %msg As %Library.String) as %Library.String
    THIS METHOD IS DEPRECATED. Refer to DropTable() in %SYSTEM.SQL.Schema instead. This entry point can be used to delete a table definition.

    Parameters:
    tablename
    Name of the SQL table to delete. The name may be qualified with a schema name: Medical.Patient. If the schema name is omitted, the default schema is used.
    deldata
    TRUE(1)/FALSE(0) flag which determine if the table's data is to be deleted or not. If deldata="" or undefined, use system flag to determine if data should be deleted.
    SQLCODE
    Passed by reference. Returns SQLCODE. Contains 0 (success) or number < 0 (error).
    %msg
    Passed by reference. If SQLCODE<0, contains error message.

    Examples:

    • Do $SYSTEM.SQL.DropTable("MedLab.Patient",1,.SQLCODE,.%msg)
    • Do $SYSTEM.SQL.DropTable("IscPerson",1,.SQLCODE,.%msg) ; Deletes SQLUser.IscPerson
    deprecated classmethod DropView(viewname As %Library.String, ByRef SQLCODE As %Library.Integer, ByRef %msg As %Library.String) as %Library.String
    THIS METHOD IS DEPRECATED. Refer to DropView() in %SYSTEM.SQL.Schema instead. This entry point can be used to delete a view definition.

    Parameters:
    viewname
    Name of the SQL view to delete. The name may be qualified with a schema name: Medical.PatientView If the schema name is omitted, the default schema is used.
    SQLCODE
    Passed by reference. Returns SQLCODE. Contains 0 (success) or number < 0 (error)
    %msg
    Passed by reference. If SQLCODE<0, contains error message.

    Examples:

    • Do $SYSTEM.SQL.DropView("MedLab.PatientView",.SQLCODE,.%msg)
    • Do $SYSTEM.SQL.DropView("IscPersonView",.SQLCODE,.%msg) ; Deletes SQLUser.IscPerson
    classmethod Execute(ByRef SQL As %String(MAXLEN=""), SelectMode As %String = {$zu(115, 5)}, Dialect As %String = "", ObjectSelectMode As %Integer = 0) as %SQL.StatementResult
    Execute an SQL statement and return the result object.
    Parameters:
    SQL
    SQL statement to execute. This can be an array of SQL statement lines with the base node set to the number of lines or it can be a single string..
    SelectMode
    The statement SELECTMODE - LOGICAL, ODBC or DISPLAY.
    Can also be specified as 0, 1, or 2.
    Dialect
    The SQL dialect used for this SQL statement. Valid values are IRIS, MSSQLSERVER, MSSQL, and SYBASE. The default is IRIS. Support for MSSQLSERVER and SYBASE dialects is limited to a subset of the TSQL grammar supported by the InterSystems IRIS TSQL language mode.
    ObjectSelectMode
    The statement %ObjectSelectMode value - 0 or 1. Refer to %SQL.Statement for more information on ObjectSelectMode.

    Examples:

    • set result=$SYSTEM.SQL.Execute("select top 5 name,dob,ssn from sample.person order by name")
    • set result=$SYSTEM.SQL.Execute("select top 5 name,dob,ssn,home_street,,home_city + ', ' + home_state + ' ' + home_zip from sample.person order by name",1,"MSSQL")
    • set sql=2,sql(1)="select top 5 name,dob,ssn from sample.person"
      set sql(2)=" order by name"
      set result=$SYSTEM.SQL.Execute(.sql)
    • set result=$SYSTEM.SQL.Execute("")
    • set result=$SYSTEM.SQL.Execute("insert into sample.person (name,dob,ssn) values ('Doe,John Q.','02/29/1952','111-22-3333')",2)
    deprecated classmethod ExecuteCachedQuery(pCQName As %String = "", pNumRowsToDisplay As %Integer = 10, pArgs... As %String)
    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:
    pCQName
    Name of the cached query class to execute. This can be the entire class name, like %sqlcq.SAMPLES.cls5, or the short version of the name like cls5.
    pNumRowsToDisplay
    The number of rows for the select query to output. The default is 10.
    pArgs...
    The input arguments to the query. If the statement has input arguments and you don't pass them in here, the caller will be promoted for the input values.

    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) = %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) = %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:

    • - Only cached queries created through ODBC, JDBC, or %SQL.Statement are supported
    • - The cached query must have been created with a client from version 5.2 or newer
    • - SQL Privilege checking is NOT performed for $SYSTEM.SQL.ExecuteCachedQuery
    • - As this is intended as a developer debugging tool, there is no locking of the cached query to prevent a Purge of the cached query during $SYSTEM.SQL.ExecuteCachedQuery.
    classmethod Explain(ByRef sql As %String, qualifiers As %DynamicObject = "{}", ByRef dynArgs As %RawString, Output plan As %RawString) as %Status

    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:

    sql
    A SQL Statement passed in as one of the following two formats:
    String: sql=The SQL Statement text
    Array: sql=# of SQL Statement lines; sql(n)=Each line of the SQL Statement text
    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>]}
    silent
    0 or 1, default is 0. If 1, suppress progress messages to the current device and only store the SQL Query execution plan in the output parameter plan.
    alt or all
    0 or 1, default is 0. If either alt or all is 1, display all plans, which includes the SQL Query execution plan and all alternate plans.
    ids
    default is "", ignored when either alt or all is 0. $LIST(...) of Cost IDs for which to generate SQL Query execution plans. Used by SMP, Alternate Show Plans
    stat or stats
    0 or 1, default is 0. If either stat or stats is 1, run the SQL query to generate Runtime Performance Statistics for each module within the plan.
    packages
    default is "". $LIST(...) of Package names that will be used as default packages/schemas. Each item in this list becomes an #import statement.
    schemaPath
    default is "". Any non-NULL value will be set into %sqlSchemaPath to overriding its current value. This value is used by the SQL Statement in the following way: #sqlcompile path=schemaPath
    selectMode
    default is "" (Logical), ignored when either alt or all is 1. The select mode to use for the compilation that produces the plan. Possible values: Logical, Display, ODBC, Runtime, Text, and FDBMS.
    frozenPlans
    0 or 1, default is 1, ignored when either alt or all is 1. If 0 then do not use any frozen plan
    outliers
    0 or 1, default is 1, ignored when either alt or all is 1. If 0, just ignore outlier processing. If 1, show plan for SQL RTU if one exists.
    verbose
    0 or 1, default is 0. If 1, populate plan with all module details. If 0, populate plan with ONLY top-level module details
    format
    default is "PRINT". The format in which to generate the SQL query execution plan:
    "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. The query plan is the same regardless of the rendering format, but the MRQP offers more fine-grained information about the modules and steps in the query plan, as well as rich metadata about the tables and views involved in the plan (format-type: Dynamic Object)
    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

    Examples:

       // --- 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
      
    deprecated classmethod Export(Schema As %Library.String, Table As %Library.String, File As %Library.String, FileOpenParam As %Library.String = "WNS", Users As %Library.Boolean = 1, Roles As %Library.Boolean = 1, Privileges As %Library.Boolean = 1, SQLSystemPrivileges As %Library.Boolean = 1) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to ExportDDL() in %SYSTEM.SQL.Schema instead. Export a SQL DDL/DML script file containing User definitions, Role definitions, and/or Privileges.

    Parameters:
    Schema
    Name of the schema in the current namespace to export the privileges for. Use "*" to specify the export of SQL Privileges for all schemas in the current namespace. The default is "*". Note that when "*" is specified, '%' schemas (such as %Dictionary) are not exported. To export privileges for tables in a schema that begins with '%' you must explicitly specify that schema name in Schema.
    Table
    Name of the table/view in the specified schema to export the privileges for. Use "*" to specify the export of SQL Privileges for all table/s views in the specified schema(s). The default is "*". Note that if you "*" for Schema the export will behave as if you entered "*" for Table as well. For example there is no mechanism for exporting the Person table/view in each schema in the namespace.
    File
    Name of the file to export the SQL statement to.
    FileOpenParam
    Parameters to use when opening the File. The default is "WNS".
    Users
    1/0 flag. Specify 1 to export the User definition. The default is 1.
    Roles
    1/0 flag. Specify 1 to export the Role definition, and the GRANT statements to assign the Roles to the Users and Roles they have been granted to. The default is 1.
    Privileges
    1/0 flag. Specify 1 to export the Table and View privileges for the tables and views specified in the Schema/Table specification. The default is 1.
    SQLSystemPrivileges
    1/0 flag. Specify 1 to export the SQL System privileges defined in the current namespace for the users and roles. The default is 1.

    Examples:

    • Do $SYSTEM.SQL.Export("Sample","*","C:\PT\Sample.sql","WNS",0,0,1,1)
    • Do $SYSTEM.SQL.DDLImport("*","*","C:\DDT\UsersAndRoles.sql","WNS",1,1,0,0)

    The method returns a Status Code.

    A file created via $SYSTEM.SQL.Export() can be imported using one of:

    • Do $SYSTEM.SQL.DDLImport("IRIS",...)
    • Do $SYSTEM.SQL.IRIS()

    The Export() method will not export the following users and roles -

    Users:
    SuperUser, Admin, UnknownUser, %System, CSPSystem, _SYSTEM, _PUBLIC
    Roles:
    %All, %Developer, %Manager, %Operator, %SQL, %LegacyTerminal, %LegacyCacheDirect, %LegacySQL
    deprecated classmethod ExportAllSQLStatements(Filename As %Library.String = "") as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to ExportAllFrozenPlans() in %SYSTEM.SQL.Statement 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:
    Filename
    Name of the file to output the SQL Statement definitions to.

    Examples:

    • Do $SYSTEM.SQL.ExportAllSQLStatements("C:\MyAllStatements.xml") // Exports all SQL Satements
    deprecated classmethod ExportSQLStatement(Filename As %Library.String = "", Hash As %Library.String = "") as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to ExportFrozenPlans() in %SYSTEM.SQL.Statement 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:
    Filename
    Name of the file to output the SQL Statement definition to.
    Hash
    SQL Statement definition hash, used as the ID of the SQL Statement Definition index entry

    Examples:

    • Do $SYSTEM.SQL.ExportSQLStatement("C:\MyStatement.xml","Kod99B0VFOn/aXQ9PyRGfb64q04=") // Exports SQL Satement with ID hash of 'Kod99B0VFOn/aXQ9PyRGfb64q04='
    deprecated classmethod ExportTuneStats(pFilename As %Library.String, pSchemaFilter As %Library.String = "", pTableFilter As %Library.String = "", pDisplay As %Library.Boolean = 1) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to Export() in %SYSTEM.SQL.Stats.Table instead. Export extentsize and selectivity for tables/fields to an XML file. Generated file can be loaded using $SYSTEM.SQL.ImportTuneStats().

    Parameter:
    pFilename
    Name of the file to output the table(s) tuning statistics to.
    pSchemaFilter
    Filter to limit the schemas output. The default is "", which means there is no filter applied and all schemas in the namespace are exported. pSchemaFilter uses '_' to signify any single character, '*' to signify 0 through N characters, and ' to signify NOT.
    pTableFilter
    Filter to limit the tables output. The default is "", which means there is no filter applied and all tables in the specified schemas are exported. pTableFilter uses '_' to signify any single character, '*' to signify 0 through N characters, and ' to signify NOT.
    pDisplay
    TRUE(1)/FALSE(0) flag. Displays progress messages to the current device if TRUE. The default is TRUE (1).

    Examples:

    • Do $SYSTEM.SQL.ExportTuneStats("C:\AllStats.xml") // Exports TuneTable Statistics for all schemas/tables in the current namespace
    • Do $SYSTEM.SQL.ExportTuneStats("C:\SampleStats.xml","Sample") // Exports TuneTable Statistics for all Sample.* tables in the current namespace
    • Do $SYSTEM.SQL.ExportTuneStats("C:\SamplePStats.xml","Sample","P*") // Exports TuneTable Statistics for all Sample.P* in the current namespace
    • Do $SYSTEM.SQL.ExportTuneStats("C:\SamplePersonStats.xml","Sample","Person") // Exports TuneTable Statistics for table Sample.Person in the current namespace
    deprecated classmethod FDBMS() as %Library.String
    THIS METHOD IS DEPRECATED. Refer to LoadFDBMS() in %SYSTEM.SQL.Schema instead. Import a FDBMS DDL script file.
    deprecated classmethod FLOOR(val As %Library.Numeric = "") as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to FLOOR() in %SYSTEM.SQL.Functions instead. FLOOR is a numeric function that returns the largest integer less than or equal to a given numeric expression

    $SYSTEM.SQL.FLOOR(dateexp)

    numeric-exp
    A number whose floor is to be calculated.
    deprecated classmethod FreezePlans(Action As %Library.SmallInt = 1, Scope As %Library.SmallInt = 1, ScopeArgument As %Library.String = "", ByRef Errors As %Library.String)
    THIS METHOD IS DEPRECATED. Refer to FreezeAll(), FreezeSchema(), FreezeRelation(), FreezeStatement(), UnfreezeAll(), UnfreezeSchema(), UnfreezeSchema(), and UnfreezeStatement() in %SYSTEM.SQL.Statement instead. Freeze or Unfreeze plans or SQL statements in this namespace

    Parameter:
    Action
    If Action=0, Unfreeze all plans within the Scope that are marked Frozen/Explicit or Frozen/Upgrade.
    If Action=1, Freeze all plans within the Scope that are marked Unfrozen or Frozen/Upgrade. This is the default Action.
    If Action=2, Unfreeze all plans within the Scope that are marked Frozen/Upgrade.
    Scope
    If Scope=1, perform the Action for all query plans in this namespace. This is the default Scope.
    If Scope=2, perform the Action for all query plans for the schema provided in ScopeArgument.
    If Scope=3, perform the Action for all query plans for the relation provided in ScopeArgument.
    If Scope=4, perform the Action for the query plan with the statement hash provided in ScopeArgument.
    ScopeArgument
    If Scope=2, the name of the schema for which to freeze the plans, if Scope=3, the name of the relation for which to freeze the plans.
    If Scope=3, the name of the relation needs to be in what we call internal qualified format. This is typically simply Schema.Tablename, but if tablename itself contains any "." characters, it needs to be in Schema_"."_$translate(Tablename,".",$Char(2)) format.
    If Scope=4, the value of the statement hash id. This is typically hash value like "3DgIqc72NS+Np6nybddb719NKb8=".
    Errors
    Passed by reference. Returns an array of error messages if there are any failures while freezing plans.

    Examples:

    • Do $SYSTEM.SQL.FreezePlans(1,1,,.Errors) // Freezes all SQL statement plans in the current namespace
    • Do $SYSTEM.SQL.FreezePlans(1,2,"XLT",.Errors) // Freezes all SQL statement plans in the current namespace for each statement that references a relation in the XLT schema.
    • Do $SYSTEM.SQL.FreezePlans(1,3,"XLT.Person",.Errors) // Freezes all SQL statement plans in the current namespace for each statement that references the XLP.Person table.
    • Do $SYSTEM.SQL.FreezePlans(1,4,"3DgIqc72NS+Np6nybddb719NKb8=",.Errors) // Freezes SQL statement plan for statement identified by hash "3DgIqc72NS+Np6nybddb719NKb8=".
    • Do $SYSTEM.SQL.FreezePlans(0,1,,.Errors) // Unfreezes all SQL statement plans in the current namespace
    • Do $SYSTEM.SQL.FreezePlans(2,1,,.Errors) // Unfreezes all SQL statement plans in the current namespace that were marked Frozen/Upgrade.
    • Do $SYSTEM.SQL.FreezePlans(0,2,"XLT",.Errors) // Unfreezes all SQL statement plans in the current namespace for each statement that references a relation in the XLT schema.
    • Do $SYSTEM.SQL.FreezePlans(0,3,"XLT.Person",.Errors) // Unfreezes all SQL statement plans in the current namespace for each statement that references the XLP.Person table.
    • Do $SYSTEM.SQL.FreezePlans(2,3,"XLT.Person",.Errors) // Unfreezes all SQL statement plans in the current namespace for each statement that references the XLP.Person table and that were marked Frozen/Upgrade.
    • Do $SYSTEM.SQL.FreezePlans(0,4,"3DgIqc72NS+Np6nybddb719NKb8=",.Errors)) // Unfreezes SQL statement plan for statement identified by hash "3DgIqc72NS+Np6nybddb719NKb8=".
    deprecated classmethod GetAutoCommit() as %Integer
    THIS METHOD IS DEPRECATED. Refer to GetOption() in %SYSTEM.SQL.Util instead. Return the current process's Transaction Commit Mode value. Possible values are:

    0NO AUTO TRANSACTION
    1AUTO COMMIT ON (Default)
    2AUTO COMMIT OFF
    deprecated classmethod GetBitmapFriendlyCheck() as %Library.Boolean
    THIS METHOD IS DEPRECATED. Refer to GetOption() in %SYSTEM.SQL.Util 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.

    deprecated classmethod GetCollectionProjection() as %Integer
    THIS METHOD IS DEPRECATED. Refer to GetOption() in %SYSTEM.SQL.Util instead. Return the CollectionProjection setting. Possible values are:

    0Do not project collections as columns if also projected as child table (DEFAULT)
    1Project all collections as columns
    deprecated classmethod GetColumns(tablename As %String = "", ByRef byName As %String, ByRef byNumber As %String, skipHiddenFields As %Boolean = 0) as %Boolean
    THIS METHOD IS DEPRECATED. Refer to GetAllColumns() and GetVisibleColumns() in %SYSTEM.SQL.Schema 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:
    tableName
    Name of the table to return the columns for. Name is expected to be in the format Schema.Tablename If the name is unqualified, the default schema will be used.
    byName
    Passed By Reference. Returns columns ordered by column name with SqlColumnNumber as data. For example: byName("Id")=1,byName("Name")=2
    byNumber
    Passed By Reference. Returns columns ordered by SqlColumnNumber with column name as data. For example: byName(1)="Id",byName(2)="Name"
    skipHiddenFields
    Passed By Value. Default is 0. If TRUE, do not include any SQL hidden columns in the byName and byNumber output arrays. SQL fields are defined as hidden if they are projected by a property defined as private, a RowID field defined as SqlRowIDPrivate, a RowID field for a linked table, a serial field that is not a collection and the storage structure is known, the %%CLASSNAME field, or a list or array collection property projected as a child table.
    Returns: A %Boolean value. 1 if the columns were returned successfully, 0 if there was no such table.
    deprecated classmethod GetCompileMode() as %Library.String
    THIS METHOD IS DEPRECATED. Refer to GetOption() in %SYSTEM.SQL.Util instead. Function returns the current Compile Mode setting, either Deferred or Immediate.
    deprecated classmethod GetIdentityInsert() as %Integer
    THIS METHOD IS DEPRECATED. Refer to GetOption() in %SYSTEM.SQL.Util instead. Return the current IDENTITY_INSERT option value. Possible values are:

    0IDENTITY cannot be set
    1IDENTITY can be set

    deprecated classmethod GetIsolationMode() as %Integer
    THIS METHOD IS DEPRECATED. Refer to GetOption() in %SYSTEM.SQL.Util instead. Return the current process's Transaction Isolation Mode value. Possible values are:

    0 - READ UNCOMMITTED (Default)
    1 - READ COMMITTED
    3 - READ VERIFIED
    deprecated classmethod GetLockThreshold() as %Integer
    THIS METHOD IS DEPRECATED. Refer to GetOption() in %SYSTEM.SQL.Util 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.
    deprecated classmethod GetLockTimeout() as %Integer
    THIS METHOD IS DEPRECATED. Refer to GetOption() in %SYSTEM.SQL.Util 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.
    deprecated classmethod GetMapSelectability(pTablename As %Library.String = "", pMapname As %Library.String = "") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to GetMapSelectability() in %SYSTEM.SQL.Util instead.
    This entrypoint is used to determine if an SQL Map definition is selectable by the SQL Query Optimizer or not

    Parameters:
    pTablename
    Name of the table to check.
    pTablename can be qualified or unqualified. If unqualified, the default schema is applied.
    pMapname
    Name of the SQL Map in the table to check the selectable for.

    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:

      A 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.
      This 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:

    • Write $SYSTEM.SQL.GetMapSelectability("Sample.Person","NameIdx")
    deprecated classmethod GetProcessLockTimeout() as %Integer
    THIS METHOD IS DEPRECATED. Refer to GetOption() in %SYSTEM.SQL.Util instead. Return the Lock Timeout value for the current process.
    classmethod GetROWID() as %Library.String [ SQLProc = SQL_GetROWID ]
    Projected as the stored procedure: SQL_GetROWID
    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()
    classmethod GetRTPCRuntimeQueries(pUtilityCls As %String = "", Output pRuntimeCls) as %Library.Status

    Get RTPC runtime classes for a given RTPC utility class (pUtilityCls)


    Parameters:
    pUtilityCls
    The utility class, such as "%sqlcq.USER.cls1"
    pRuntimeCls
    Output, an array containing all the RTPC runtime classes for the specified utility class pUtilityCls with the following format:
    pRuntimeCls=# of runtime classes
    pRuntimeCls(n)=Runtime class name

    Returns:

    Status Code

    Invocation:

    Object Script:$SYSTEM.SQL.GetRTPCRuntimeQueries(pUtilityCls , .pRuntimeCls)

    Example (ObjectScript):

    • set tSC=$SYSTEM.SQL.GetRTPCRuntimeQueries("%sqlcq.USER.cls1",.RuntimeCls) zw RuntimeCls
    deprecated classmethod GetSQLFunctionArgConversion() as %Boolean
    THIS METHOD IS DEPRECATED. Refer to GetOption() in %SYSTEM.SQL.Util instead. Get the current setting of the system wide flag for controlling if SQL Functions perform ODBCToLogical/DisplayToLogical on SQL Function input arguments.

    Parameters:
    flag
    TRUE(1)/FALSE(0) If flag is FALSE(0) (the default), SQL will not convert input arguments for SQL Functions from Odbc/Display to Logical format. If TRUE(1), SQL will convert input arguments for SQL Functions from Odbc/Display to Logical format if needed.
    pStatus
    Status Code reports the success or failure of this API call
    Returns:

    Current value of the SQLFunctionArgConversion setting

    Examples:

    • Set return = $SYSTEM.SQL.GetSQLFunctionArgConversion()
    NOTES:
    - This is a system-wide setting.
    classmethod GetSQLStatsFlag(flagType As %Integer = "", returnActionFlag As %Integer = 0, ByRef ptInfo As %ArrayOfObjects) as %Library.Integer

    Method: GetSQLStatsFlag [SQL: StatsSQL_GetSQLStatsFlag] Purpose: This method gets the flag that controls whether or not we collect SQL Statistics for each SQL Query execution Get the current value of the SQLStats-flag for the given 'flagType'. The SQLStats-flag (System/Job) controls whether or not SQL Statistics are collected for each SQL Query execution, and which performance statistics to be collected. The SQLStats-flag is a colon (:) delimited string comprised of the following individual components: SQLStats-flag={action-flag}:{collect-flag}:{condition-flag}:{condition-value}:{terminate-flag}:{flag-type} RULES: When (flagType=""), whether to use the SQLStats-flag (System) or the SQLStats-flag (Process/Job) is determined by the following rules: IF {action-flag} (Process/Job) = 0, then use SQLStats-flag (System) ELSE use SQLStats-flag (Process/Job) Invocation: This method can be invoked in the following ways: Object Script: $SYSTEM.SQL.GetSQLStatsFlag(...) ##class(%SYS.PTools.StatsSQL).GetSQLStatsFlag(...) SQL: SELECT %SYS_PTools.StatsSQL_GetSQLStatsFlag(...) Examples: The following examples shows the use of this method: #1 Get the current value of the SQLStats-flag for the Job/System Flag based on SQLStats-flag Rules: set SQLStatsFlag=##class(%SYS.PTools.StatsSQL).GetSQLStatsFlag("") Parameters: 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 RETURN: The current value of the SQLStats-flag; Otherwise, return an error message if an error occurred

    classmethod GetSQLStatsFlagByPID(pid As %String = "", returnActionFlag As %Integer = 0, ByRef ptInfo As %ArrayOfObjects) as %Integer

    Method: GetSQLStatsFlagByPID [SQL: StatsSQL_GetSQLStatsFlagByPID] Purpose: This method gets the flag that controls whether or not we collect SQL Statistics about each SQL Query execution for the given 'pid' Get the current value of the SQLStats-flag for the given 'pid'. The SQLStats-flag (Process/Job) controls whether or not SQL Statistics are collected for each SQL Query execution, and which performance statistics to be collected. The SQLStats-flag is a colon (:) delimited string comprised of the following individual components: SQLStats-flag={action-flag}:{collect-flag}:{condition-flag}:{condition-value}:{terminate-flag}:{flag-type} NOTE: This method ONLY applies to the SQLStats-flag for the given 'pid' (Process/Job) and NOT the SQLStats-flag for the (System)! RESTRICTION: This method invocation requires %Admin_Operate:Use privilege Invocation: This method can be invoked in the following ways: Object Script: $SYSTEM.SQL.GetSQLStatsFlagByPID(...) ##class(%SYS.PTools.StatsSQL).GetSQLStatsFlagByPID(...) SQL: SELECT %SYS_PTools.StatsSQL_GetSQLStatsFlagByPID(...) Examples: The following examples shows the use of this method: #1 Get the current value of the SQLStats-flag for the Job/System Flag based on SQLStats-flag Rules for PID# 12345: set SQLStatsFlag=##class(%SYS.PTools.StatsSQL).GetSQLStatsFlagByPID(12345) Parameters: 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 RETURN: The current value of the SQLStats-flag; Otherwise, return an error message if an error occurred

    deprecated classmethod GetSelectMode() as %Integer
    THIS METHOD IS DEPRECATED. Refer to GetOption() in %SYSTEM.SQL.Util instead. Return the select mode value for the current process. Possible values are:

    0Logical
    1ODBC
    2Display
    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.
    deprecated classmethod GetServerDisconnectCode() as %Library.String
    THIS METHOD IS DEPRECATED. Refer to GetOption() in %SYSTEM.SQL.Util 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:

    • Write $SYSTEM.SQL.GetServerDisconnectCode()
    deprecated classmethod GetServerInitCode() as %Library.String
    THIS METHOD IS DEPRECATED. Refer to GetOption() in %SYSTEM.SQL.Util 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:

    • Write $SYSTEM.SQL.GetServerInitCode()
    deprecated classmethod GrantObjPriv(ObjPriv As %String, ObjList As %String, Type As %String, User As %String, wGrant As %Integer = 0, ByRef SQLCODE As %Integer) as %Status
    THIS METHOD IS DEPRECATED. Refer to GrantPrivilege() and GrantPrivilegeWithGrant() in %SYSTEM.SQL.Security 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)

    Paramaters:

    ObjPriv
    Comma delimited string of actions to grant. * for all actions:
    • Alter
    • Select
    • Insert
    • Update
    • Delete
    • References
    • Execute
    • Use
    • or any combination
    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
    deprecated classmethod HOUR(timeexp As %Library.String = "") as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to HOUR() in %SYSTEM.SQL.Functions 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)

    timeexp
    An expression that is a logical %Library.Time, %Library.TimeStamp, or %Library.PosixTime value.
    deprecated classmethod INSTR(string As %Library.String = "", substring As %Library.String = "", position As %Library.Integer = 1, occurrence As %Library.Integer = 1) as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to INSTR() in %SYSTEM.SQL.Functions 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)

    string
    The string to search in.
    substring
    The string to search for in string.
    position
    A nonzero integer indicating the character of string where InterSystems IRIS begins the search. If position is negative, then InterSystems IRIS counts backward from the end of string and then searches backward from the resulting position.
    occurrence
    An integer indicating which occurrence of substring InterSystems IRIS should search for. If occurrence is greater than 1, then InterSystems IRIS searches for the second occurrence beginning with the second character in the first occurrence of string, and so forth. An occurrence of less than 1 is treated the same as 1.
    deprecated classmethod IRIS() as %Status
    THIS METHOD IS DEPRECATED. Refer to Run() in %SYSTEM.SQL.Schema instead. Import an IRIS SQL script file. For InterSystems IRIS SQL script files, the default end-of-statement marker is the string GO. The end-of-statement marker must be on a line by itself after the statement.
    deprecated classmethod ImportDir(pDialect As %Library.String = "", pDirectory As %Library.String = "", pLogfile As %Library.String = "", pExtensions As %Library.String = "sql", eosDelimiter As %Library.String = "", pRecurse As %Integer = 0, pMessageMode As %Boolean = 1, pEchoMode As %Boolean = 1, pErrorPause As %Integer = 5)
    THIS METHOD IS DEPRECATED. Refer to ImportDir() in %SYSTEM.SQL.Schema 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:
    pDialect
    Vendor from which the script file originated. This parameter is required. Supported values are:
    • MSSQL
    • MSSQLServer - same as MSSQL
    • Sybase
    pDirectory
    The full path name of the directory to import. This parameter is required.
    pLogfile
    The full path name of the file to report errors in. This parameter is Optional. Default is SQLImportDir.log in the directory loaded. If this parameter value is 1, a separate log file will be generated for each file loaded. The name of the log file will be the same as the file imported, but with the extension .log instead of .sql.
    pExtensions
    A comma delimited list of file extensions to import. This parameter is optional and defaults to "sql".
    eosDelimiter
    End of statement delimiter. Will default to an appropriate value based on the value of %DDLMode. This parameter is optional.
    pRecurse
    If pRecurse is 1 then subdirectories of pDirectory will be recursively imported. This parameter is optional and defaults to 0.
    pMessageMode
    If true then all messages reported by executing imported statements will be displayed on the current device.
    pEchoMode
    If true, all statement source is displayed on the current device.
    pErrorPause
    The number of seconds to pause when an error is reported. The default is five seconds.

    Examples:

    • Do $SYSTEM.SQL.ImportDir("Sybase","C:\Work\db1\","C:\Work\db1\import.log","sql",";")
    • Do $SYSTEM.SQL.ImportDir("Sybase","C:\Work\db1\","C:\Work\db1\import.log","sql,tab,sp",";",1)
    • Do $SYSTEM.SQL.ImportDir("Sybase","C:\Work\db1\",,,";")
    deprecated classmethod ImportSQLStatement(Filename As %Library.String = "", Display As %Library.Boolean = 1) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to ImportFrozenPlans() in %SYSTEM.SQL.Statement 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:
    Filename
    Name of the file to output the SQL Statement definition to.
    Display
    1/0 Flag. If 1, display import progress on the screen. Default is 1.

    Examples:

    • Do $SYSTEM.SQL.ImportSQLStatement("C:\MyStatements.xml",1) // Imports SQL Statement(s) defined in the file
    deprecated classmethod ImportTuneStats(pFilename As %Library.String, pDisplay As %Library.Boolean = 1, pKeepClassUpToDate As %Library.Boolean = 0, pClearCurrentStats As %Library.Boolean = 0) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to Import() in %SYSTEM.SQL.Stats.Table instead. Import extentsize, selectivity, blockcount for a table and its fields from a file created by $SYSTEM.SQL.ExportTuneStats().

    Parameter:
    pFilename
    Name of the file to output the table(s) tuning statistics to.
    pDisplay
    TRUE(1)/FALSE(0) flag. Displays progress messages to the current device if TRUE. The default is TRUE (1).
    pKeepClassUpToDate
    TRUE(1)/FALSE(0) flag. If TRUE (and update is TRUE), the class definition will be updated with the new EXTENTSIZE and SELECTIVITY values, but the class definition will be kept as up-to-date. In many cases, however, it is desirable to recompile the class after its table has been tuned so that queries in the class definition can be recompiled and the SQL query optimizer can use the updated data statistics. The default is FALSE (0). Note that if the class is deployed the class definition will not be updated.
    pClearCurrentStats
    TRUE(1)/FALSE(0) flag. If TRUE(1), any EXTENTSIZE, SELECTIVITY, BLOCKCOUNT, etc. will be cleared from the existing table prior to importing the stats. This can be used if you want to completely clear stats that are not specified in the import file instead of leaving them defined in class/table. The default is FALSE (0)

    Examples:

    • Do $SYSTEM.SQL.ImportTuneStats("C:\AllStats.xml") // Import TuneTable Statistics for all schemas/tables that were exported with the $SYSTEM.SQL.ExportTuneStats() to the AllStats.xml file
    deprecated classmethod Informix() as %Library.String
    THIS METHOD IS DEPRECATED. Refer to LoadInformix() in %SYSTEM.SQL.Schema instead. Import an Informix DDL/DML script file.
    The Informix DDL/DML Import Utility supports the following statements:
    • CREATE TABLE ...
    • ALTER TABLE ...
    • CREATE INDEX ...
    • CREATE VIEW ...
    • SET OPTION ...
    • GRANT { ALTER | SELECT | INSERT | UPDATE | DELETE | REFERENCES | ALL PRIVILEGES } ...
    Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).
    deprecated classmethod InterBase() as %Library.String
    THIS METHOD IS DEPRECATED. Refer to LoadInterBase() in %SYSTEM.SQL.Schema instead. Import an Interbase DDL/DML script file.
    The Interbase DDL/DML Import Utility supports the following statements:
    • CREATE TABLE ...
    • ALTER TABLE ...
    • CREATE INDEX ...
    • CREATE UNIQUE INDEX ...
    • CREATE VIEW ...
    • CREATE ROLE ...
    Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).
    classmethod IsReservedWord(word As %Library.String) as %Library.Boolean [ SQLProc = SQL_IsReservedWord ]
    Projected as the stored procedure: SQL_IsReservedWord
    This entry point can be used to determine if a string is an SQL Reserved word.

    Parameters:
    word
    The word to check against the SQL Reserved Word list.

    Examples:

    • Write $SYSTEM.SQL.IsReservedWord("select") // Writes a 1
    • Write $SYSTEM.SQL.IsReservedWord("football") // Writes a 0
    This method can also be called as a Stored Procedure named %SYSTEM.SQL_IsReservedWord(word)
    classmethod IsValidRegularIdentifier(identifier As %Library.String) as %Library.Boolean [ SQLProc = SQL_IsValidRegularIdentifier ]
    Projected as the stored procedure: SQL_IsValidRegularIdentifier
    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:
    identifier
    Name of the identifier to check.

    Examples:

    • Write $SYSTEM.SQL.IsValidRegularIdentifier("select") // Writes a 0 (reserved word)
    • Write $SYSTEM.SQL.IsValidRegularIdentifier("football") // Writes a 1
    • Write $SYSTEM.SQL.IsValidRegularIdentifier("%Correct") // Writes a 1
    • Write $SYSTEM.SQL.IsValidRegularIdentifier("%Correct_$Amount") // Writes a 1
    This method can also be called as a Stored Procedure named %SYSTEM.SQL_IsValidRegularIdentifier(identifier)
    deprecated classmethod LASTDAY(dateexp As %Library.String = "") as %Library.Date
    THIS METHOD IS DEPRECATED. Refer to LASTDAY() in %SYSTEM.SQL.Functions instead. LASTDAY is a scalar date/time function that returns the %Library.Date value of the last day of the month for a given date expression.

    $SYSTEM.SQL.LASTDAY(dateexp)

    dateexp
    A %Library.Date, %Library.TimeStamp, or %Library.Posix logical value.
    LASTDAY returns the date of the last day of the specified month as a %Library.Date 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.

    deprecated classmethod MINUTE(timeexp As %Library.String = "") as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to MINUTE() in %SYSTEM.SQL.Functions 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)

    timeexp
    An expression that is a logical %Library.Time, %Library.TimeStamp, or %Library.PosixTime value.
    deprecated classmethod MONTH(dateexp As %Library.String = "") as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to MONTH() in %SYSTEM.SQL.Functions 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)

    dateexp
    An expression that is a logical %Library.Date, %Library.TimeStamp, or %Library.PosixTime value.
    deprecated classmethod MONTHNAME(dateexp As %Library.String = "") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to MONTHNAME() in %SYSTEM.SQL.Functions 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)

    dateexp
    An expression that is a logical %Library.Date, %Library.TimeStamp, or %Library.PosixTime value.
    deprecated classmethod MSSQLServer() as %Library.String
    THIS METHOD IS DEPRECATED. Refer to LoadMSSQLServer() in %SYSTEM.SQL.Schema instead. Import an MS SQL Server DDL/DML script file.
    The MS SQL Server DDL/DML Import Utility supports the following statements:
    • CREATE [GLOBAL TEMPORARY] TABLE ...
    • CREATE VIEW ...
    • ALTER TABLE ...
    • CREATE INDEX ...
    • CREATE CLUSTERED INDEX ...
    • CREATE UNIQUE INDEX ...
    • INSERT ...
    • UPDATE ...
    • DELETE ...
    • SET OPTION ...
    Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).
    deprecated classmethod MVR(stringexp As %Library.String = "") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to MVR() in %SYSTEM.SQL.Functions instead. MVR returns the MVR collation of the passed in value.

    $SYSTEM.SQL.MVR(stringexp)

    stringexp
    Any string expression value.
    MVR performs collation translations needed for MultiValue
    deprecated classmethod Oracle() as %Library.String
    THIS METHOD IS DEPRECATED. Refer to LoadOracle() in %SYSTEM.SQL.Schema instead. Import an Oracle DDL/DML script file.
    The Oracle DDL/DML Import Utility supports the following statements:
    • CREATE TABLE ...
    • CREATE VIEW ...
    • ALTER TABLE ...
    • CREATE INDEX ...
    • CREATE UNIQUE INDEX ...
    • DROP TABLE ...
    • DROP VIEW ...
    • DROP INDEX ...
    • INSERT ...
    • UPDATE ...
    • DELETE ...
    • SET OPTION ...
    • CREATE USER ...
    • CREATE ROLE ...
    • GRANT { SELECT | INSERT | UPDATE | DELETE | REFERENCES | ALL PRIVILEGES } ...
    Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).
    classmethod Prepare(ByRef SQL As %String(MAXLEN=""), SelectMode As %String = {$zu(115, 5)}, Dialect As %String = "", ObjectSelectMode As %Integer = 0) as %SQL.Statement

    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 %SQL.Statement for more information.


    Parameters:
    SQL
    SQL statement to prepare. This can be an array of SQL statement lines with the base node set to the number of lines or it can be a single string..
    SelectMode
    The statement SELECTMODE - LOGICAL, ODBC or DISPLAY.
    Can also be specified as 0, 1, or 2.
    Dialect
    The SQL dialect used for this SQL statement. Valid values are IRIS, MSSQLSERVER, MSSQL, and SYBASE. The default is IRIS. Support for MSSQLSERVER and SYBASE dialects is limited to a subset of the TSQL grammar supported by the InterSystems IRIS TSQL language mode.
    ObjectSelectMode
    The statement %ObjectSelectMode value - 0 or 1. Refer to %SQL.Statement for more information on ObjectSelectMode.

    Examples:

    • set statement = $SYSTEM.SQL.Prepare("select top ? name,dob,ssn from sample.person order by name")
    • set statement = $SYSTEM.SQL.Prepare("select top 5 name,dob,ssn,home_street,,home_city + ', ' + home_state + ' ' + home_zip from sample.person order by name",1,"MSSQL")
    • set sql=2,sql(1)="select top 5 name,dob,ssn from sample.person"
      set sql(2)=" order by name"
      set statement = $SYSTEM.SQL.Prepare(.sql)
    • set statement = $SYSTEM.SQL.Prepare("insert into sample.person (name,dob,ssn) values (?,?,?)",2)
    deprecated classmethod ProcedureExists(procname As %Library.String = "", ByRef metadata As %String) as %Library.Boolean [ SQLProc = SQL_ProcedureExists ]
    Projected as the stored procedure: SQL_ProcedureExists
    THIS METHOD IS DEPRECATED. Refer to ProcedureExists() in %SYSTEM.SQL.Schema instead. This entry point can be used to determine if a stored procedure exists.

    Parameters:
    procname
    Name of the stored procedure to check.
    procname can be qualified or unqualified. If unqualified, the default schema is applied.
    metadata
    Passed by reference, optional argument.
    If the stored procedure exists, returns the following information about the procedure: $ListBuild(SchemaName,ProcedureName,Classname that projected the stored procedure,procedure type)

    Examples:

    • Write $SYSTEM.SQL.ProcedureExists("SQLUser.stpSalaryReport") // Writes a 1 if procedure SQLUser.stpSalaryReport exists
    • Write $SYSTEM.SQL.ProcedureExists("stpSalaryReport",.metadata) // Writes a 1 if procedure [DefaultSchema].stpSalaryReport exists, returns metadata=$lb("SQLUser","stpSalaryReport","User.stpSalaryReport","function")

    Notes:

    • If the user calling the function does not hold any privileges for the procedure, 0 will be returned.
    • If a class exists that would project this procedure to SQL during compilation, but the class has not been compiled, 0 will be returned.
    • If a procedure is marked as hidden, 0 will be returned.
    • metadata will be set to "" if 0 is returned by the function.
    This method can also be called as a Stored Procedure named %SYSTEM.SQL_ProcedureExists(procname)
    classmethod Purge(days As %Library.Integer = 0) as %Library.String
    Purges Cached Queries.

    Parameter:
    days
    Number of days. Purge cached queries not prepared in more than days days.

    Examples:

    • Do $SYSTEM.SQL.Purge(0) // Purge all Cached Queries
    • Do $SYSTEM.SQL.Purge() // Purge all Cached Queries
    • Do $SYSTEM.SQL.Purge(30) // Purge all Cached Queries that have not be used (prepared) in the last 30 days
    classmethod PurgeAllNamespaces() as %Library.String
    Purges Cached Queries in all namespaces on this system

    Example:

    • Do $SYSTEM.SQL.PurgeAllNamespaces() // Purge all Cached Queries on this system
    classmethod PurgeCQClass(Classlist As %Library.String) as %Library.String
    Purges Cached Queries given the name(s) of the Cached Query Classes.

    Parameter:
    Classlist
    The name of the cached query class to purge, or a comma delimited list of cached query class names to purge.

    Examples:

    • Do $SYSTEM.SQL.PurgeCQClass("%sqlcq.USER.cls13") ; Purge this Cached Query
    • Do $SYSTEM.SQL.PurgeCQClass("%sqlcq.USER.cls13,%sqlcq.USER.cls16,%sqlcq.USER.cls124") ; Purge these 3 Cached Queries
    classmethod PurgeForRoutine(routine As %Library.String) as %Library.String
    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:
    routine
    The name of the cached query class to purge, or a comma delimited list of cached query class names to purge.

    Examples:

    • Do $SYSTEM.SQL.PurgeForRoutine("%sqlcq.USER.cls13") ; Purge this Cached Query
    • Do $SYSTEM.SQL.PurgeForRoutine("%sqlcq.USER.cls13,%sqlcq.USER.cls16,%sqlcq.USER.cls124") ; Purge these 3 Cached Queries
    classmethod PurgeForTable(table As %Library.String) as %Library.String
    Purges all Cached Queries which use table table.

    Parameter:
    table
    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:

    • Do $SYSTEM.SQL.PurgeForTable("MedLab.Patient")
    • Do $SYSTEM.SQL.PurgeForTable("IscPerson") ; Purges Cached Queries for SQLUser.IscPerson
    deprecated classmethod QUARTER(dateexp As %Library.String = "") as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to QUARTER() in %SYSTEM.SQL.Functions 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)

    dateexp
    An expression that is a logical %Library.Date, %Library.TimeStamp, or %Library.PosixTime value.
    deprecated classmethod QueryToTable(ByRef query As %Library.String, table As %Library.String, display As %Library.Boolean = 0, ByRef error As %Library.String) as %Library.String
    THIS METHOD IS DEPRECATED. Refer to QueryToTable() in %SYSTEM.SQL.Schema 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
    The query text to execute. query can be of the format:
    query="sql text"
    OR
    query = # of lines
    query(1) = sql line 1
    query(n) = sql line n
    
    table
    Name of the new SQL table to generate. The name may be qualified with a schema name: Medical.Patient If the schema name is omitted, the default schema is used.
    display
    TRUE(1)/FALSE(0) flag. Displays progress messages to the current device if TRUE. The default is FALSE (0).
    error
    Array of error messages returned if there is a problem. The format is:
    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
    
    deprecated classmethod RevokeObjPriv(ObjPriv As %String, ObjList As %String, Type As %String, User As %String, wGrant As %Integer = 0, Cascade As %Integer = 0, AsGrantor As %String = "", ByRef SQLCODE As %Integer) as %Status
    THIS METHOD IS DEPRECATED. Refer to RevokePrivilege() in %SYSTEM.SQL.Security 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)

    Paramaters:

    ObjPriv
    Comma delimited string of actions to grant. * for all actions:
    • Alter
    • Select
    • Insert
    • Update
    • Delete
    • References
    • Execute
    • Use
    • or any combination
    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
    deprecated classmethod RoleExists(rolename As %Library.String = "") as %Library.Boolean [ SQLProc = SQL_RoleExists ]
    Projected as the stored procedure: SQL_RoleExists
    THIS METHOD IS DEPRECATED. Refer to RoleExists() in %SYSTEM.SQL.Security instead. This entry point can be used to determine if a role exists.

    Parameters:
    rolename
    Name of the role to check.

    Examples:

    • Write $SYSTEM.SQL.RoleExists("SalesManager") // Writes a 1 if role SalesManager exists
    This method can also be called as a Stored Procedure named %SYSTEM.SQL_RoleExists(rolename)
    deprecated classmethod SECOND(timeexp As %Library.String = "") as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to SECOND() in %SYSTEM.SQL.Functions 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)

    timeexp
    An expression that is a logical %Library.Time, %Library.TimeStamp, or %Library.PosixTime value.
    deprecated classmethod SQLCODE(SQLCODE As %Library.Integer) as %Library.String [ SQLProc = SQL_SQLCODE ]
    Projected as the stored procedure: SQL_SQLCODE
    THIS METHOD IS DEPRECATED. Refer to SQLCODE() in %SYSTEM.SQL.Functions instead. This entry point can be used to return the description of an SQLCODE value.

    Parameters:
    SQLCODE
    SQLCODE value.

    Examples:

    • Write $SYSTEM.SQL.SQLCODE(-105)
    • Write $SYSTEM.SQL.SQLCODE(100)
    This method can also be called as a Stored Procedure named %SYSTEM.SQL_SQLCODE(SQLCODE)
    deprecated classmethod SQLSTRING(stringexp As %Library.String = "", maxlen As %Library.Integer) as %Library.String
    THIS METHOD IS DEPRECATED. Refer to SQLSTRING() in %SYSTEM.SQL.Functions instead. SQLSTRING returns the SqlString collation of the passed in value.

    $SYSTEM.SQL.SQLSTRING(stringexp,maxlen)

    stringexp
    Any string expression value.
    maxlen
    Optional. An integer, which specifies that the collated value will be truncated to the value of maxlen.
    SQLSTRING converts stringexp to a format that is sorted as a (case-sensitive) string. SQLSTRING 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 SqlString is " "_$Char(0).
    deprecated classmethod SQLUPPER(stringexp As %Library.String = "", maxlen As %Library.Integer) as %Library.String
    THIS METHOD IS DEPRECATED. Refer to SQLUPPER() in %SYSTEM.SQL.Functions instead. SQLUPPER returns the SqlUpper collation of the passed in value.

    $SYSTEM.SQL.SQLUPPER(stringexp,maxlen)

    stringexp
    Any string expression value.
    maxlen
    Optional. An integer, which specifies that the collated value will be truncated to the value of 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).
    deprecated classmethod STRING(stringexp As %Library.String = "", maxlen As %Library.Integer) as %Library.String
    THIS METHOD IS DEPRECATED. Refer to STRING() in %SYSTEM.SQL.Functions instead. STRING returns the String collation of the passed in value.

    $SYSTEM.SQL.STRING(stringexp,maxlen)

    stringexp
    Any string expression value.
    maxlen
    Optional. An integer, which specifies that the collated value will be truncated to the value of maxlen.
    STRING converts stringexp to a STRING collation format.
    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).
    deprecated classmethod SetANSIPrecedence(flag As %Library.Boolean = 1, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util instead. Set the configuration setting which determines if ANSI operator precedence is applied. The default is use of ANSI precedence is appied.

    Parameter:
    flag
    TRUE(1)/FALSE(0) If flag is FALSE(0), do not apply ANSI precedence in SQL statements. If TRUE(1) (the default), do apply ANSI precedence in SQL.
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetAllowExtrinsicFunctions(flag As %Library.Boolean = 1, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    flag
    TRUE(1)/FALSE(0) If flag is FALSE(0) (the default), do not allow extrinsic functions in SQL statements through ODBC, JDBC, or Dynamic SQL. If TRUE(1), do allow extrinsic functions in SQL statements through ODBC, JDBC, or Dynamic SQL
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetAutoCommit(flag As %Library.Integer = 0) as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    flag
    0 - No AutoCommit
    1 - AutoCommit ON
    2 - AutoCommit OFF
    Returns:
    Old value (0, 1, or 2) of the AutoCommit setting.
    deprecated classmethod SetAutoParallel(flag As %Library.Boolean = 1, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    flag
    TRUE(1)/FALSE(0) If flag is FALSE(0) do not apply auto hinting for %PARALLEL in SQL statements. If TRUE(1) (the default), do apply auto hinting for %PARALLEL in SQL.
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetAutoParallelThreshold(threshold As %Integer = 3200, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    threshold
    Integer.
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetBitmapFriendlyCheck(pFlag As %Library.Integer = 0, ByRef pOldVal As %Library.Integer) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    pFlag
    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.
    pOldVal
    Passed By Reference. Contains the previous value of the setting.
    Returns:

    Status Code

    NOTES:

    - Changing this configuration setting will only take effect immediately for all processes.
    - This is a system-wide setting.
    deprecated classmethod SetCachedQueryLockTimeout(timeout As %Library.Integer = 120, ByRef oldval As %Library.Integer) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    timeout
    Number of seconds the lock command should timeout after when attempting to lock cached query definitions.
    oldval
    Passed By Reference. Contains the previous value of the setting.

    Returns:

    Status Code

    Examples:

    • Set sc=$SYSTEM.SQL.SetCachedQueryLockTimeout(60) // Sets cached query lock timeout to 60 seconds
    • Set sc=$SYSTEM.SQL.SetCachedQuerySaveSource(300,.oldval) // Sets lock timeout to 5 minutes, returns previous lock timeout setting in oldval.
    NOTES:
    - The setting is on a per system basis.
    deprecated classmethod SetCachedQuerySaveSource(flag As %Library.Boolean = 0, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    flag
    TRUE(1)/FALSE(0) flag to set the flag to retain source code for all cached queries. If TRUE (1), source is retained. If FALSE (0), source is deleted after the cached query has compiled.
    oldval
    Passed By Reference. Contains the previous value of the setting.

    Returns:

    Status Code

    Examples:

    • Set sc=$SYSTEM.SQL.SetCachedQuerySaveSource(1) // Retain source
    • Set sc=$SYSTEM.SQL.SetCachedQuerySaveSource(0,.oldval) // Do not retain source
    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.
    deprecated classmethod SetCollectionProjection(value As %Library.Integer = 0) as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util instead. Sets the collection projection option. Setting takes effect when classes are recompiled.

    Parameter:
    value
    0 - do not project collections as a column if the collection is projected as a child table.
    1 - project all collections as columns.
    Returns:
    Old value (0, 1) of the CollectionProjection setting.
    deprecated classmethod SetCompileModeDeferred() as %Library.String
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:

    - CREATE TABLE MyTable ...
    - CREATE INDEX MyIndex1 ON MyTable ...
    - CREATE INDEX MyIndex2 ON MyTable ...
    - CREATE INDEX MyIndex3 ON MyTable ...
    - CREATE INDEX MyIndex4 ON MyTable ...
    - CREATE INDEX MyIndex5 ON MyTable ...
    - CREATE TABLE MyOtherTable ...
    - CREATE INDEX MyOtherIndex1 ON MyOtherTable ...
    - CREATE INDEX MyOtherIndex2 ON MyOtherTable ...
    - CREATE INDEX MyOtherIndex3 ON MyOtherTable ...
    - CREATE INDEX MyOtherIndex4 ON MyOtherTable ...
    - CREATE INDEX MyOtherIndex5 ON MyOtherTable ...
    - ALTER TABLE MyOtherTable ADD FOREIGN KEY MyFKey (MyField) REFERENCES MyTable(MyField)
    - INSERT INTO MyTable ...

    Running with Deferred Compilation Mode off would require 13 class compilations for the sequence above. With Deferred Compilation Mode on, only two class compilations are required. Classes MyTable and MyOtherTable are compiled when the INSERT statement is encountered.
    There are two ways to turn Deferred Compilation Mode on:

    - Execute the following SQL statement:
        SET OPTION COMPILEMODE = DEFERRED
    - Call the API entry point:
        Do $SYSTEM.SQL.SetCompileModeDeferred()

    If Deferred Compilation Mode is already turned on, no error will be returned.

    To return to Immediate Compilation Mode:

    - Execute the following SQL statement:
        SET OPTION COMPILEMODE = IMMEDIATE
    - Call the API entry point:
        Do $SYSTEM.SQL.SetCompileModeImmediate()

    Changing from Deferred to Immediate Compilation Mode will cause any classes in the Deferred Compile Queue to be compiled immediately. If all class compilations were successful, SQLCODE will return 0. If there were any errors, SQLCODE will equal -400. Class compilation errors are logged in the ^mtemp2($namespace,"Deferred Compile Mode","Error"). If SQLCODE=-400 you should view this global structure for more precise error messages.

    When in Deferred Compilation Mode, any of the following actions will trigger the classes in the Deferred Compilation Queue to be immediately compiled:
    • Switch back to immediate compile mode.
    • An xDBC Catalog query is called.
    • A DML statement is issued (INSERT, SELECT, GRANT, etc)
    • CREATE INDEX statement is executed.
    • DROP INDEX statement is executed.
    • ALTER TABLE to add a required field to a table with a default value.
    • ALTER TABLE to modify a field to be required.

    Deferred Compilation Mode should only be used in an installation-type setting where only a single process is working on the namespace at the time.

    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 }
    deprecated classmethod SetCompileModeImmediate() as %Library.String
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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 CompileModeDeferred(), CompileModeInstall(), and CompileModeNocheck() 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 }
    deprecated classmethod SetCompileModeInstall() as %Library.String
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:

    - CREATE TABLE MyTable ...
    - CREATE INDEX MyIndex1 ON MyTable ...
    - CREATE INDEX MyIndex2 ON MyTable ...
    - CREATE INDEX MyIndex3 ON MyTable ...
    - CREATE INDEX MyIndex4 ON MyTable ...
    - CREATE INDEX MyIndex5 ON MyTable ...
    - CREATE TABLE MyOtherTable ...
    - CREATE INDEX MyOtherIndex1 ON MyOtherTable ...
    - CREATE INDEX MyOtherIndex2 ON MyOtherTable ...
    - CREATE INDEX MyOtherIndex3 ON MyOtherTable ...
    - CREATE INDEX MyOtherIndex4 ON MyOtherTable ...
    - CREATE INDEX MyOtherIndex5 ON MyOtherTable ...
    - ALTER TABLE MyOtherTable ADD FOREIGN KEY MyFKey (MyField) REFERENCES MyTable(MyField)
    - INSERT INTO MyTable ...

    Running with Install Compilation Mode off would require 13 class compilations for the sequence above. With Install Compilation Mode on, only one class compilation is required. Classes MyTable and MyOtherTable are compiled when the INSERT statement is encountered.
    There are two ways to turn Install Compilation Mode on:

    - Execute the following SQL statement:
        SET OPTION COMPILEMODE = INSTALL
    - Call the API entry point:
        Do $SYSTEM.SQL.SetCompileModeInstall()

    If Install Compilation Mode is already turned on, no error will be returned.

    To return to Immediate Compilation Mode:

    - Execute the following SQL statement:
        SET OPTION COMPILEMODE = IMMEDIATE
    - Call the API entry point:
        Do $SYSTEM.SQL.SetCompileModeImmediate()

    Changing from Install to Immediate Compilation Mode will cause any classes in the Deferred Compile Queue to be compiled immediately. If all class compilations were successful, SQLCODE will return 0. If there were any errors, SQLCODE will equal -400. Class compilation errors are logged in the ^mtemp2($namespace,"Deferred Compile Mode","Error"). If SQLCODE=-400 you should view this global structure for more precise error messages.

    When in Install Compilation Mode, any of the following actions will trigger the classes in the Deferred Compilation Queue to be immediately compiled:
    • Switch back to immediate compile mode.
    • An xDBC Catalog query is called.
    • A DML statement is issued (INSERT, SELECT, GRANT, etc)

    Install Compilation Mode should only be used in an installation-type setting where only a single process is working on the namespace at the time and there is no data in the tables.

    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 }
    deprecated classmethod SetCompileModeNocheck() as %Library.String
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    • If a table is dropped, there are no checks to see if any foreign key constraints in other tables reference this table.
    • If a foreign key constraint is added, there are no checks to see if any existing data in the table is valid for the foreign key.
    • If a NOT NULL constraint is added to a table with existing data, there is no validation that any existing rows are NOT NULL, nor is there an update existing rows to assign the field's default value if there is one.
    • If a UNIQUE or Primary key constraint is deleted, there is no check to see if a foreign key in this or another table had referenced the key.
    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 }
    deprecated classmethod SetDDLDefineBitmapExtent(flag As %Library.Boolean = 1, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    flag
    1 or 0. If TRUE, set the system wide flag to define a bitmap extent index for classes created by CREATE TABLE. Otherwise, do not define the bitmap extent index for the class.
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetDDLDropTabDelData(flag As %Library.Boolean = 1, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util instead. Sets the flag which determines if a DDL DROP TABLE statement deletes the table's data.

    Parameters:
    flag
    1 or 0. If TRUE, set the system wide flag to DELETE the table's data when the table is dropped. Otherwise, the data is not deleted.
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetDDLFinal(flag As %Library.Boolean = 1, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util instead. Sets the flag which determines if a class created by a DDL CREATE TABLE statement is Final.

    Parameters:
    flag
    1 or 0. If TRUE, set the system wide flag to define a class created by CREATE TABLE as Final. Otherwise, do not define the class as Final.
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetDDLIdentifierTranslations(from As %Library.String, to As %Library.String = "", ByRef oldfrom As %Library.String, ByRef oldto As %Library.String) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetDDLIdentifierTranslations() in %SYSTEM.SQL.Util 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:
    from
    A string of characters to translate from
    to
    A string of characters to translate to (by position in the string) Optional. Default is "".
    oldfrom
    Passed By Reference. Contains the previous value of the 'from' setting
    oldto
    Passed By Reference. Contains the previous value of the 'to' setting
    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.
    deprecated classmethod SetDDLPKeyNotIDKey(flag As %Library.Boolean = 1, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    flag
    TRUE(1)/FALSE(0) If flag is TRUE (1), set the system wide configuration setting for the current configuration to NOT make Primary Key constraints become IDKey indices. If flag is FALSE (0), the Primary Key index will also become the IDKey index. The default is TRUE (1).
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetDDLUseExtentSet(flag As %Library.Boolean = 1, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    flag
    1 or 0. If TRUE, set the system wide flag to define a class created by CREATE TABLE to define the USEEXTENTSET class parameter to a value of 1. Otherwise, do not define the class with the USEEXTENTSET parameter.
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetDDLUseSequence(flag As %Library.Boolean = 1, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    flag
    1 or 0. If TRUE, set the system wide flag to define a class created by CREATE TABLE uses $Sequence for ID assignment. Otherwise, do not define the class as Final.
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetDefaultSchema(schema As %Library.String = "", ByRef oldval As %Library.String, Namespace As %Library.Boolean = 0) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetDefault() in %SYSTEM.SQL.Schema 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/. In this case, if the process has not logged in to SQL, 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:
    schema
    String containing the default SQL schema name. If schema is "" or not defined, the default schema will be set to SQLUser.
    oldval
    Passed By Reference. Contains the previous value of the setting.
    Namespace
    Boolean 1/0 flag. If TRUE, set the default schema for the current namespace only. The default for Namespace is FALSE
    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.
    deprecated classmethod SetDefaultTimePrecision(value As %Library.Integer = 0, ByRef oldval As %Library.Integer) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    value
    Precision (number of decimal places for the millisecond portion of the time value). The default is 0, milliseconds are not returned in the values returned by the GETDATE(), CURRENT_TIME, and CURRENT_TIMESTAMP functions.
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetDelimitedIdentifiers(flag As %Library.Boolean = 1, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    flag
    TRUE(1)/FALSE(0) If flag is TRUE (1), "..." is treated as an identifier. If flag is FALSE (0), "..." is treated as a string literal. The default is TRUE (1).
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetECPSync(flag As %Library.Boolean = 0, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    flag
    TRUE(1)/FALSE(0) flag to define the setting to Perform ECP Syncs for Select queries. If TRUE (1), ECP Sync is turned on. If FALSE (0), ECP Sync is turned OFF.
    oldval
    Passed By Reference. Contains the previous value of the setting.

    Returns:

    Status Code

    Examples:

    • Set sc=$SYSTEM.SQL.SetECPSync(1) // ECP Sync ON
    • Set sc=$SYSTEM.SQL.SetECPSync(0,.oldval) // ECP Sync OFF
    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.
    deprecated classmethod SetFastDistinct(flag As %Library.Boolean = 0, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util instead. Defines whether or not SQL DISTINCT is optimized to use indexes. The default is DISTINCT optimizations are ON.

    Parameter:
    flag
    TRUE(1)/FALSE(0) flag to define the setting to allow SQL optimizations of DISTINCT. If TRUE (1), DISTINCT optimization is turned on. If FALSE (0), DISTINCT optimization is turned OFF.
    oldval
    Passed By Reference. Contains the previous value of the setting.

    Returns:

    Status Code

    Examples:

    • Set sc=$SYSTEM.SQL.SetFastDistinct(1) // DISTINCT Optimization ON
    • Set sc=$SYSTEM.SQL.SetFastDistinct(0,.oldval) // DISTINCT Optimization OFF
    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.
    deprecated classmethod SetFieldSelectivity(schema As %Library.String, tablename As %Library.String, fieldname As %Library.String, selectivity As %Library.String, KeepClassUpToDate As %Boolean = 0) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetFieldSelectivity() in %SYSTEM.SQL.Stats.Table instead. Set the SELECTIVITY of a field and property to the value of the given selectivity

    Parameter:
    schema
    Name of the table's schema. Default is the default schema.
    tablename
    Name of the table the field is in (required).
    fieldname
    Name of the field to set the SELECTIVITY for (required).
    selectivity
    New selectivity value for the field (required). The selectivity of a property specifies the approximate frequency of specific values within the entire distribution of values. The Selectivity value for a column is generally the percentage of rows within a table that would be returned as a result of query searching for a typical value of the column. For example, suppose a table contains a Gender column whose value is evenly distributed between "M" and "F". The Selectivity value for the Gender column would be 50%, as a typical query based on Gender would find 50% of the rows within the table. The Selectivity value for a more unique property, such as TaxID, is typically a small percentage that varies according to the size of the table. Examples of values you can specify here are:
  • "10%" - Means that typical values for this column will return 10% of the rows in the table
  • "1" - Means this field is unique. For any given value, it will return 1 row from the table.
  • - A pure number will calculate the selectivity as EXTENTSIZE/selectivity. For example, if EXTENTSIZE is 100000 and selectivity is 1000, this will set the selectivity to 1%.
  • "NUMROWS" - This is the same as specifying "1", it means the field is unique. This is allowed for legacy support of M/SQL tables that have been converted to class definitions.
  • NUMROWS/positive_integer - This will calculate the SQL SELECTIVITY as EXTENTSIZE/positive_integer. For example if EXTENTSIZE is 100000 and you specify NUMROWS/5000, this will set the SQL SELECTIVITY to 20, which means for a typical value for the field, 20 rows of the table will be returned. This is allowed for legacy support of M/SQL tables that have been converted to class definitions.
  • There is no validation of the value you enter for the SELECTIVITY. If you enter something not recognized as a valid SELECTIVITY, such as the string "nonsense", it will be turned into a value of 0. If the SQL query processor sees a SELECTIVITY of 0, it will attempt to come up with a typical SELECTIVITY value for the field based on how many rows are in the table and whether or not the field is a reference column, is part of the IDKEY field, has a VALUELIST specification, etc.
    KeepClassUpToDate
    TRUE(1)/FALSE(0) flag. If TRUE the class definition will be updated with the new SELECTIVITY value, but the class definition will be kept as up-to-date. In many cases, however, it is desirable to recompile the class after its table has been tuned so that queries in the class definition can be recompiled and the SQL query optimizer can use the updated data statistics. The default is FALSE (0). Note that if the class is deployed the class definition will not be updated.

    Returns:

    Status Code

    Example:

    • Do $SYSTEM.SQL.SetFieldSelectivity("MedLab","Patient","Home_Phone","2.5%",0)
    deprecated classmethod SetFilerRefIntegrity(flag As %Library.Boolean = 1, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    flag
    TRUE(1)/FALSE(0) flag to determine if SQL Filer referential integrity checks are performed. If flag is FALSE (0), the SQL Filer will skip referential integrity checks. Checks will be performed if flag is TRUE (1). The default is TRUE (1).
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetIdentityInsert(value As %Integer = 1, ByRef pStatus As %Library.Status) as %Integer
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:

    0IDENTITY cannot be set
    1IDENTITY 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.

    deprecated classmethod SetIsolationMode(value As %Integer = 1, ByRef pStatus As %Library.Status) as %Integer
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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.

    deprecated classmethod SetLockThreshold(value As %Library.Integer = $$$DefaultLockThreshold, ByRef oldval As %Library.Integer) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util instead. Set the Lock Threshold for locks acquired during filing of rows within a single transaction.

    Parameter:
    value
    Number of row locks to acquire before escalating to a table lock. The default is 1000.
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetLockTimeout(timeout As %Library.Integer = 10, ByRef oldval As %Library.Integer) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util instead. Set the Lock timeout for locks acquired during execution of SQL statements.

    Parameter:
    timeout
    Number of seconds to set the lock timeout to. The default is 10 seconds.
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetMapSelectability(pTablename As %Library.String = "", pMapname As %Library.String = "", pValue As %Boolean = "") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to SetMapSelectability() in %SYSTEM.SQL.Util instead. This entrypoint is used to make an SQL Map definition [not] selectable to the SQL Query Optimizer

    Parameters:
    pTablename
    Name of the table to check.
    pTablename can be qualified or unqualified. If unqualified, the default schema is applied.
    pMapname
    Name of the SQL Map in the table to make selectable or not selectable.
    pValue
    1/0 flag. 1 means make this map Selectable - the SQL Query Optimizer will be able to choose this map. 0 means make this map Not Selectable - the SQL Query Optimizer will NOT be able to consider this map

    Returns:

      1 if the call was successful, Otherwise an error message is returned in a string format.

    Note:

      This 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.
      This 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.
      Also, 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:

    • Write $SYSTEM.SQL.SetMapSelectability("Sample.Person","NameIndex",1) // SQL Query Optimizer WILL consider map NameIndex
    • Write $SYSTEM.SQL.SetMapSelectability("Sample.Person","NameIndex",0) // SQL Query Optimizer WILL NOT consider map NameIndex

    Returns:

    deprecated classmethod SetProcessLockTimeout(value As %Integer = 1, ByRef pStatus As %Library.Status) as %Integer
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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.

    deprecated classmethod SetQueryProcedures(flag As %Library.Boolean = 0, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    flag
    TRUE(1)/FALSE(0) flag to define the setting force all class queries to be projected as stored procedures. If FALSE (0), only class queries with SqlProc = TRUE are projected as stored procedures. If TRUE (1), all class queries are projected as stored procedures.
    oldval
    Passed By Reference. Contains the previous value of the setting.

    Returns:

    Status Code

    Examples:

    • Set sc=$SYSTEM.SQL.SetQueryProcedures(1) // All class queries projected as procedures
    • Set sc=$SYSTEM.SQL.SetQueryProcedures(0,.oldval) // Only SqlProc=TRUE class queries projected as procedures
    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.
    deprecated classmethod SetRTPC(flag As %Library.Boolean = 0, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    flag
    TRUE(1)/FALSE(0) If flag is FALSE(0), do not apply Run Time Plan Choice in SQL statements. If TRUE(1), the default, do apply Run Time Plan Choice in SQL.
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetRetainSQL(flag As %Library.Boolean = 0, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    flag
    TRUE(1)/FALSE(0) If flag is TRUE (1), SQL text will be retained as comments in the .INT code. No comments will be created if flag is FALSE (0). The default is FALSE (0).
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetSQLFunctionArgConversion(flag As %Integer = 1, ByRef pStatus As %Library.Status) as %Boolean
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    flag
    TRUE(1)/FALSE(0) If flag is FALSE(0) (the default), SQL will not convert input arguments for SQL Functions from Odbc/Display to Logical format. If TRUE(1), SQL will convert input arguments for SQL Functions from Odbc/Display to Logical format if needed.
    pStatus
    Status Code reports the success or failure of this API call
    Returns:

    Old value of the SQLFunctionArgConversion setting

    Example:

    • Set oldvalue = $SYSTEM.SQL.GetSQLFunctionArgConversion(1,.sc)
      SELECT SQLUser.AddDay('2013-01-01')
      In the statement above, if the type of the argument to AddDay is %Library.Date and this statement is executed in ODBC mode, you may or may not want the input argument to be converted from ODBC date format to Logical %Library.Date format. If you would like InterSystems IRIS to handle the conversion automatically, use $SYSTEM.SQL.SetSQLFucntionArgConverion(1)
    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.
    deprecated classmethod SetSQLSecurity(flag As %Library.Boolean = 1, ByRef oldval As %Library.Boolean) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util instead. Set the configuration setting which determines if SQL security is enabled. If SQL security is ON, all SQL security is active. This means:
    • Privilege-based table/view security is active. A user may only perform actions on a table or view they have been granted privilege for.
    If SQL security is OFF, SQL security is inactive. This means:
    • Privilege-based table/view security is suppressed. A user may perform actions on a table or view even if they have not been granted privileges to do so.
    Parameter:
    flag
    TRUE(1)/FALSE(0) If flag is TRUE (1), SQL security is ON If flag is FALSE (0), SQL security is OFF. The default is TRUE(1).
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    classmethod SetSQLStats(actionFlag As %Integer = 0, returnActionFlag As %Integer = 0, collectFlag As %String = "", terminateCond As %String = 0, ByRef ptInfo As %ArrayOfObjects) as %Integer

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

    classmethod SetSQLStatsFlag(actionFlag As %Integer = 0, returnActionFlag As %Integer = 0, collectFlag As %String = "", terminateCond As %String = 0, ByRef ptInfo As %ArrayOfObjects) as %Integer

    Method: SetSQLStatsFlag [SQL: StatsSQL_SetSQLStatsFlag] Purpose: See SetSQLStats() for details! Invocation: This method can be invoked in the following ways: Object Script: $SYSTEM.SQL.SetSQLStatsFlag(...) ##class(%SYS.PTools.StatsSQL).SetSQLStatsFlag(...) SQL: SELECT %SYS_PTools.StatsSQL_SetSQLStatsFlag(...)

    classmethod SetSQLStatsFlagByNS(ns As %String = "", actionFlag As %Integer = 0, returnActionFlag As %Integer = 0, collectFlag As %String = "", terminateCond As %String = 0, ByRef ptInfo As %ArrayOfObjects) as %Integer

    Method: SetSQLStatsFlagByNS [SQL: StatsSQL_SetSQLStatsFlagByNS] Purpose: This method sets the flag that controls whether or not to collect SQL Statistics about each run of a query within the given 'ns' (Namespace) You can invoke different levels of SQL Statistics collection by setting the SQLStats-flag, for a given 'ns'. The SQLStats-flag (System) controls whether or not SQL Statistics are collected for each SQL Query execution, and which performance statistics to be collected. The SQLStats-flag is a colon (:) delimited string comprised of the following individual components: SQLStats-flag={action-flag}:{collect-flag}:{condition-flag}:{condition-value}:{terminate-flag}:{flag-type} NOTE: This method ONLY applies to the SQLStats-flag (System) for the given 'ns' and NOT the SQLStats-flag (Process/Job)! Invocation: This method can be invoked in the following ways: Object Script: $SYSTEM.SQL.SetSQLStatsFlagByNS(...) ##class(%SYS.PTools.StatsSQL).SetSQLStatsFlagByNS(...) SQL: SELECT %SYS_PTools.StatsSQL_SetSQLStatsFlagByNS(...) Examples: The following examples shows the use of this method: #1 Turn PTools ON to collects stats for all SQL modules within the "USER" Namespace: set oldStats=##class(%SYS.PTools.StatsSQL).SetSQLStatsFlagByNS("USER",3) Parameters: 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 +8 Total Disk Read Latency Time === 9 Collect both of these performance metrics By passing 9 for the value of the 'collectFlag' parameter, the sum of the two aforementioned performance metrics MVal numbers, the SQLStats PTool application will only collect statistical data for these two performance metrics whenever an SQL Query is invoked. EXAMPLE #2: The following example represents the collection of 3 performance metrics, 'Query Execution Time', 'Total Global References' & 'Total Lines of Code Executed': MVal Metric Name ---- ---------------------------- 1 Query Execution Time 2 Total Global References +4 Total Lines of Code Executed === 7 Collect both of these performance metrics By passing 7 for the value of the 'collectFlag' parameter, the sum of the three aforementioned performance metrics MVal numbers, the SQLStats PTool application will only collect statistical data for these two performance metrics whenever an SQL Query is invoked. terminateCond - The portion of the SQLStats-flag which is represented by the 5th colon (:) piece, and is determined by this parameter value. An optional condition to determine when to terminate the SQLStats collection, by turning off the SQLStats-flag (System), or by setting it to a specified reset value. This parameter accepts the following values: 0 No Action [DEFAULT] M:: Where =# of minutes from 1..n When specified, this value will terminate the SQLStats collection the number of minutes after the current $H Where =Reset {action-flag} value from 0..3 When specified, this is the value to which the {action-flag} portion of the SQLStats-flag will be set upon expiration If not specified, default to zero (0) (e.g. "M:120:1" => Terminate 120minutes after the current $H and reset SQLStats-flag/{action-flag} to 1) T:: Where =Timestamp with the format: YYYYMMDD HHMM When specified, this value will terminate the SQLStats collection after the timestamp value Where =Reset {action-flag} value from 0..3 When specified, this is the value to which the {action-flag} portion of the SQLStats-flag will be set upon expiration If not specified, default to zero (0) (e.g. "T:20171018 1330:1" => Terminate after 10/18/2017 01:30PM and reset SQLStats-flag/{action-flag} to 1) ptInfo - A Pass By Reference information array that returns to the user detailed information in the following format: ptInfo(category,variable)=value Where category = { "current" | "terminate" | ... } variable = A variable that corresponds to the given 'category' Example: ptInfo("current","SQLStatsFlag")=The SQLStats-flag value after method execution RETURN: The value of the SQLStats-flag before setting it to the new value; Otherwise, return an error message if an error occurred

    classmethod SetSQLStatsFlagByPID(pid As %String = "", actionFlag As %Integer = 0, returnActionFlag As %Integer = 0, collectFlag As %String = "", p5 As %String = "", ByRef ptInfo As %ArrayOfObjects) as %Integer

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

    classmethod SetSQLStatsFlagJob(actionFlag As %Integer = 0, returnActionFlag As %Integer = 0, collectFlag As %String = "", p4 As %String = "", ByRef ptInfo As %ArrayOfObjects) as %Integer

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

    classmethod SetSQLStatsJob(actionFlag As %Integer = 0, returnActionFlag As %Integer = 0, collectFlag As %String = "", p4 As %String = "", ByRef ptInfo As %ArrayOfObjects) as %Integer

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

    deprecated classmethod SetSelectMode(value As %Integer = 1, ByRef pStatus As %Library.Status) as %Integer
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:

    0Logical
    1ODBC
    2Display
    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.
    deprecated classmethod SetServerDisconnectCode(code As %Library.String = "") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    code
    A single line of ObjectScript to be executed. Call with code="" or undefined to delete disconnect code for this namespace.

    Examples:

    • Do $SYSTEM.SQL.SetServerDisconnectCode("Do Cleanup^%ZMedPatUtil")
    • Do $SYSTEM.SQL.SetServerDisconnectCode("")
    - 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.
    deprecated classmethod SetServerInitCode(code As %Library.String = "") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util 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:
    code
    A single line of ObjectScript to be executed. Call with code="" or undefined to delete initialization code for this namespace.

    Examples:

    • Do $SYSTEM.SQL.SetServerInitCode("Do Setup^%ZMedPatSetup")
    • Do $SYSTEM.SQL.SetServerInitCode("")
    - 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.
    deprecated classmethod SetTCPKeepAlive(seconds As %Library.Integer = 300, ByRef oldval As %Library.Integer) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util instead. Set the TCP Keep Alive interval for xDBC connections.

    Parameter:
    seconds
    Number of seconds to set the TCP Keep Alive interval to. The default is 300 seconds (5 minutes).
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    deprecated classmethod SetToDateDefaultFormat(value As %Library.String = "", ByRef oldval As %Library.String) as %Library.Status
    THIS METHOD IS DEPRECATED. Refer to SetOption() in %SYSTEM.SQL.Util instead. Set the default format for the SQL TO_DATE() function.

    Parameter:
    value
    String value with the default format the TO_DATE function will return.
    oldval
    Passed By Reference. Contains the previous value of the setting.
    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.
    classmethod Shell() as %Library.String
    Interactive SQL Shell. Execute SQL statements from the command line. Refer to %SQL.Shell for more info.
    deprecated classmethod ShowPlan(sql As %Library.String, array As %Library.Boolean = 0, showstats As %Library.Boolean = 0, packages As %Library.List = "", schemapath As %Library.String = "", preparse As %Library.Boolean = 0, selectmode As %Library.String = "", nofplan As %Library.Boolean = 0, silent As %Library.Boolean = 0, ignoreoutliers As %Library.Boolean = 0, ByRef dynargs As %ArrayOfObjects, verbose As %Library.Boolean = 0)
    THIS METHOD IS DEPRECATED. Refer to Explain() instead. Display the execution plan for an SQL statement
    Parameter:
    sql
    Passed by reference. sql=# of SQL lines, sql(1)=first SQL line, ... sql(n)=last SQL line.
    array
    Optional, 1 or 0, default is 0. If 1, leave the resulting plan lines in the %plan() array, otherwise generate the plan output using Write commands.
    showstats
    Optional, 1 or 0, default is 0. If 1, run the SQL query to generate stats and output the stats as part of the plan text.
    packages
    Optional, default is "". $List of Package names that will be used as default packages/schemas.
    schemapath
    Optional, default is "". Any non-NULL value will be set into %sqlSchemaPath overriding any currently defined %sqlSchemaPath
    preparse
    Optional, 1 or 0, default is 0. If 1, ShowPlan will first preparse the SQL statement to perform literal replacement.
    selectmode
    select mode to use for the compile that produces the plan.
    nofplan
    Optional, 1 or 0, default is 0. If 1 then do not use any frozen plan
    silent
    Optional, 1 or 0, default is 0. If 1, don't perform any writes within this method.
    ignoreoutliers
    Optional, 1 or 0, default is 0. If 1, just ignore outlier processing. If 0, show plan for SQL RTU if one exists.
    dynargs
    passed by reference { dynargs(i) : i=1..dynargs is the parameters passed in }
    verbose
    Optional, 1 or 0, default is 0. If 1, populate the %plan() array with all module details. If 0, populate the %plan() array with ONLY top-level module details
    deprecated classmethod ShowPlanAlt(sql As %Library.String, array As %Library.Boolean = 0, showstats As %Library.Boolean = 0, packages As %Library.List = "", schemapath As %Library.String = "", preparse As %Library.Boolean = 0, idList As %Library.List = "", silent As %Library.Boolean = 0, p10 As %Library.Boolean = 0, ByRef p11 As %ArrayOfObjects, verbose As %Library.Boolean = 0)
    THIS METHOD IS DEPRECATED. Refer to Explain() instead. Display the execution plan for an SQL statement, then list other query costs the user can select to see alternate plans
    Parameter:
    sql
    Passed by reference. sql=# of SQL lines, sql(1)=first SQL line, ... sql(n)=last SQL line.
    array
    Optional, 1 or 0, default is 0. If 1, leave the resulting plan lines in the %AltPlan() array, otherwise generate the plan output using Write commands.
    showstats
    Optional, 1 or 0, default is 0. If 1, run the SQL query to generate stats and output the stats as part of the plan text.
    packages
    Optional, default is "". $List of Package names that will be used as default packages/schemas.
    schemapath
    Optional, default is "". Any non-NULL value will be set into %sqlSchemaPath overriding any currently defined %sqlSchemaPath
    preparse
    Optional, 1 or 0, default is 0. If 1, ShowPlan will first preparse the SQL statement to perform literal replacement.
    idList
    Optional, default is "". $List of Cost IDs you want to generate stats for, Used by SMP, Alternate Show Plans
    silent
    Optional, 1 or 0, default is 0. If 1, don't perform any writes within this method.
    p10
    For future use.
    p11
    For future use.
    verbose
    Optional, 1 or 0, default is 0. If 1, populate the %plan() array with all module details. If 0, populate the %plan() array with ONLY top-level module details
    deprecated classmethod Sybase() as %Library.String
    THIS METHOD IS DEPRECATED. Refer to LoadSybase() in %SYSTEM.SQL.Schema instead. Import a Sybase DDL/DML script file.
    The Sybase DDL/DML Import Utility supports the following statements:
    • CREATE [GLOBAL TEMPORARY] TABLE ...
    • CREATE VIEW ...
    • ALTER TABLE ...
    • CREATE INDEX ...
    • CREATE CLUSTERED INDEX ...
    • CREATE UNIQUE INDEX ...
    • INSERT ...
    • UPDATE ...
    • DELETE ...
    • SET OPTION ...
    • GRANT CONNECT ... (Same as SQL CREATE USER ...)
    • GRANT { ALTER | SELECT | INSERT | UPDATE | DELETE | REFERENCES | ALL PRIVILEGES } ...
    Statements other than the ones above are not handled by the utility and must be added to your application manually (if applicable).
    deprecated classmethod TOCHAR(expr As %Library.String = "", format As %Library.String = "") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to TOCHAR() in %SYSTEM.SQL.Functions 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)

    expr
    A Logical %Library.Date, logical %Library.TimeStamp, logical %Library.Time value, $Horolog, or number expression to be converted.
    format
    A date or number format specifying the format for the expr conversion. If format contains the characters "Y", "MM", "RR", "DD", "J", "HH", "MI", "SS", "MON", "MONTH", "AM", "PM", or "D", expr is assumed to be a %Library.Date, %Library.TimeStamp, or $Horolog value.
    If format contains he characters "HH", "MI", "SS", "AM", or "PM" and it does not contain a date format, expr is assumed to be a %Library.Time value.
    Otherwise, it is a numeric value.
    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.
    deprecated classmethod TODATE(dateexp As %Library.String = "", format As %Library.String = "") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to TODATE() in %SYSTEM.SQL.Functions 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)

    expr
    The expression to be converted. The expression can be a string date expression, the name of a column, or a value where the underlying format is of type CHAR or VARCHAR. It can also be an integer between 1 and 2980013.
    format
    A date format specifying the format for expr. If 'J' is specified, expr must be an integer. If format is omitted, 'DD MON YYYY' is the default value.
    The use of TODATE is to specify the input format of a string value containing a date to be converted to a Logical %Library.Date value. The format of expr is specified in the format parameter. format will be used as a key to translate expr into a valid %Library.Date logical value.

    See the TO_DATE Documentation in the SQL Reference for complete details.

    deprecated classmethod TOPOSIXTIME(stringexp As %Library.String = "", format As %Library.String = "") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to TOPOSIXTIME() in %SYSTEM.SQL.Functions instead. TOPOSIXTIME is a general SQL string function that converts a given string expression to a logical value of a %Library.PosixTime data type.

    $SYSTEM.SQL.TOPOSIXTIME(stringexp,format)

    stringexp
    The expression to be converted. The expression can be a string datetime expression, the name of a column, or a value where the underlying format is of type CHAR or VARCHAR.
    format
    A datetime format code specifying the format for stringexp. If format is omitted, 'DD MON YYYY HH:MM:SS' is the default value.
    The use of TOPOSIXTIME is to specify the input format of a string value containing a datetime to be converted to a Logical %Library.PosixTime value. The format of stringexp is specified in the format parameter. format will be used as a key to translate stringexp into a valid %Library.PosixTime 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.

    deprecated classmethod TOTIMESTAMP(stringexp As %Library.String = "", format As %Library.String = "") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to TOTIMESTAMP() in %SYSTEM.SQL.Functions 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)

    stringexp
    The expression to be converted. The expression can be a string datetime expression, the name of a column, or a value where the underlying format is of type CHAR or VARCHAR.
    format
    A datetime format code specifying the format for stringexp. If format is omitted, 'DD MON YYYY HH:MI:SS' is the default value.
    The use of TOTIMESTAMP is to specify the input format of a string value containing a datetime to be converted to a Logical %Library.TimeStamp value. The format of stringexp is specified in the format parameter. format will be used as a key to translate stringexp into a valid %Library.TimeStamp 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.

    deprecated classmethod TRUNCATE(stringexp As %Library.String = "", maxlen As %Library.Integer = 32768) as %Library.String
    THIS METHOD IS DEPRECATED. Refer to TRUNCATE() in %SYSTEM.SQL.Functions instead. TRUNCATE returns the Truncate collation of the passed in value.

    $SYSTEM.SQL.TRUNCATE(stringexp,maxlen)

    stringexp
    Any string expression value.
    maxlen
    Optional. An integer, which specifies that the collated value will be truncated to the length of maxlen. If maxlen is not specified, TRUNCATE behaves the same as EXACT.
    TRUNCATE leaves stringexp in the exact format it receives and is sorted as a (case-sensitive) string. TRUNCATE simply returns the first maxlen characters of the expression.
    deprecated classmethod TSQL() as %Library.String
    THIS METHOD IS DEPRECATED. Refer to LoadTSQL() in %SYSTEM.SQL.Schema 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.
    classmethod TSQLShell()
    This entry point can be used to invoke the TSQL shell.

    (no parameters or result)

    Example:

    • Do $SYSTEM.SQL.TSQLShell()

    In the shell type ? for help

    deprecated classmethod TableExists(tablename As %Library.String = "", ByRef metadata As %String) as %Library.Boolean [ SQLProc = SQL_TableExists ]
    Projected as the stored procedure: SQL_TableExists
    THIS METHOD IS DEPRECATED. Refer to TableExists() in %SYSTEM.SQL.Schema instead. This entry point can be used to determine if a base table exists.

    Parameters:
    tablename
    Name of the table to check.
    tablename can be qualified or unqualified. If unqualified, the default schema is applied.
    metadata
    Passed by reference, optional argument.
    If the table exists, returns the following information about the table: $ListBuild(SchemaName,TableName,Classname that projected the table,System Flag)

    Examples:

    • Write $SYSTEM.SQL.TableExists("Sample.Person") // Writes a 1 if table Sample.Person exists
    • Write $SYSTEM.SQL.TableExists("Sample.Person",.metadata) // Writes a 1 if table Sample.Person exists, returns metadata=$lb("Sample","Person","Sample.Person","0")

    Notes:

    • If the user calling the function does not hold any privileges for the table, 0 will be returned.
    • If a class exists that would project this table to SQL during compilation, but the class has not been compiled, 0 will be returned.
    • metadata will be set to "" if 0 is returned by the function.
    This method can also be called as a Stored Procedure named %SYSTEM.SQL_TableExists(tablename)
    deprecated classmethod TuneSchema(schema As %Library.String, update As %Library.Boolean = 1, display As %Library.Boolean = 0, ByRef pMessage As %Library.String, KeepClassUpToDate As %Library.Boolean = 0, ClearValues As %Library.Boolean = 0, LogFile As %Library.String = "", RecompileCQ As %Library.Boolean = 0, SamplePercent As %Library.String = "") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to GatherSchemaStats() in %SYSTEM.SQL.Stats.Table instead. Calculate accurate extentsize and selectivity for all classes/tables and their properties/fields within a schema.

    Parameters:
    schema
    Name of a schema to tune tables. If the schema name is omitted, the default schema is used.
    update
    TRUE(1)/FALSE(0) flag. Determines whether TuneTable updates the table and class definitions with the new extentsize and selectivity values. If update=1, the values will be updated in the table and class definition. If update=0, the computed tune values will be determined, but the definitions will not be updated. The default is TRUE (1).
    display
    TRUE(1)/FALSE(0) flag. Displays progress messages to the current device if TRUE. The default is FALSE (0).
    pMessage
    Passed by reference. May return error information.
    KeepClassUpToDate
    TRUE(1)/FALSE(0) flag. If TRUE (and update is TRUE), the class definition will be updated with the new EXTENTSIZE and SELECTIVITY values, but the class definition will be kept as up-to-date. In many cases, however, it is desirable to recompile the class after its table has been tuned so that queries in the class definition can be recompiled and the SQL query optimizer can use the updated data statistics. The default is FALSE (0). Note that if the class is deployed the class definition will not be updated.
    ClearValues
    TRUE(1)/FALSE(0) flag. If TRUE (and update is TRUE), the SELECTIVITY and EXTENTSIZE settings will be cleared from the class and table definition. Note that if the class is deployed the class definition will not be updated.
    LogFile
    Optional name of a file to log the output of the TuneTable utility to. If display is also TRUE, output will go to the current device and the log file.
    RecompileCQ
    Default value is 0. Optional value, if TRUE(1) attempt to recompile cached queries instead of purging them. If the recompile encounters any errors, the cached query will be purged. This will only work if the Keep Cached Query Source SQL setting is TRUE. When the Keep Cached Query Source setting is FALSE the cached classes are deployed and cannot be recompiled. This argument also only has an effect when KeepClassUpToDate is FALSE.
    SamplePercent
    The percentage of rows of the table to be used for sampling the data for the TuneTable utility. This percentage can be specified as .## or ##%. For example .12 or 12% will cause TuneTable to use 12% of the rows in the table when sampling the data. This value does not usually need to be specified when calling TuneTable. Only specify this value when potential outlier values for a field are not evenly distributed among rows throughout the table. Note, for any table with an extentsize < 1000, the entire extent will be used by TuneTable.

    Examples:

    • Do $SYSTEM.SQL.TuneSchema("MedLab",1,1,.errors,1,0,"TuneLog.txt",0,"40%")
    • Do $SYSTEM.SQL.TuneSchema("""Medical Lab""",1,1,.errors,0)
    • Do $SYSTEM.SQL.TuneSchema("") ; Tunes SQLUser schema
    deprecated classmethod TuneTable(table As %Library.String, update As %Library.Boolean = 1, display As %Library.Boolean = 0, ByRef pMessage As %Library.String, KeepClassUpToDate As %Library.Boolean = 0, ClearValues As %Library.Boolean = 0, LogFile As %Library.String = "", ExtentSize As %Library.Integer = "", RecompileCQ As %Library.Boolean = 0, SamplePercent As %Library.String = "") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to GatherTableStats() in %SYSTEM.SQL.Stats.Table instead. Calculate accurate extentsize and selectivity for a class/table and its properties/fields.

    Parameter:
    table
    Name of a table or '*' to tune all tables. The name may be qualified with a schema name: Medical.Patient If the schema name is omitted, the default schema is used.
    update
    TRUE(1)/FALSE(0) flag. Determines whether TuneTable updates the table and class definitions with the new extentsize and selectivity values. If update=1, the values will be updated in the table and class definition. If update=0, the computed tune values will be determined, but the definitions will not be updated. The default is TRUE (1).
    display
    TRUE(1)/FALSE(0) flag. Displays progress messages to the current device if TRUE. The default is FALSE (0).
    pMessage
    Passed by reference. May return error information.
    KeepClassUpToDate
    TRUE(1)/FALSE(0) flag. If TRUE (and update is TRUE), the class definition will be updated with the new EXTENTSIZE and SELECTIVITY values, but the class definition will be kept as up-to-date. In many cases, however, it is desirable to recompile the class after its table has been tuned so that queries in the class definition can be recompiled and the SQL query optimizer can use the updated data statistics. The default is FALSE (0). Note that if the class is deployed the class definition will not be updated.
    ClearValues
    TRUE(1)/FALSE(0) flag. If TRUE (and update is TRUE), the SELECTIVITY and EXTENTSIZE settings will be cleared from the class and table definition. Note that if the class is deployed the class definition will not be updated.
    LogFile
    Optional name of a file to log the output of the TuneTable utility to. If display is also TRUE, output will go to the current device and the log file.
    ExtentSize
    Default value is "". Optional value to be used as the table ExtentSize instead of calculating it.
    RecompileCQ
    Default value is 0. Optional value, if TRUE(1) attempt to recompile cached queries instead of purging them. If the recompile encounters any errors, the cached query will be purged. This will only work if the Keep Cached Query Source SQL setting is TRUE. When the Keep Cached Query Source setting is FALSE the cached classes are deployed and cannot be recompiled. This argument also only has an effect when KeepClassUpToDate is FALSE.
    SamplePercent
    The percentage of rows of the table to be used for sampling the data for the TuneTable utility. This percentage can be specified as .## or ##%. For example .12 or 12% will cause TuneTable to use 12% of the rows in the table when sampling the data. This value does not usually need to be specified when calling TuneTable. Only specify this value when potential outlier values for a field are not evenly distributed among rows throughout the table. Note, for any table with an extentsize < 1000, the entire extent will be used by TuneTable.

    Examples:

    • Do $SYSTEM.SQL.TuneTable("MedLab.Patient",1,1,.errors,1,,"Tune.log",999999,0,"30%")
    • Do $SYSTEM.SQL.TuneTable("""Medical Lab"".Patient",1,1,.errors,0)
    • Do $SYSTEM.SQL.TuneTable("IscPerson") ; Tunes SQLUser.IscPerson
    deprecated classmethod UPPER(stringexp As %Library.String = "") as %Library.String
    THIS METHOD IS DEPRECATED. Refer to UPPER() in %SYSTEM.SQL.Functions instead. UPPER returns the Upper collation of the passed in value.

    $SYSTEM.SQL.UPPER(stringexp)

    stringexp
    Any string expression value.
    UPPER converts all alphabetic characters to upper case (i.e., the UPPER format). Note that punctuation is not changed.
    deprecated classmethod UserExists(username As %Library.String = "") as %Library.Boolean [ SQLProc = SQL_UserExists ]
    Projected as the stored procedure: SQL_UserExists
    THIS METHOD IS DEPRECATED. Refer to UserExists() in %SYSTEM.SQL.Security instead. This entry point can be used to determine if a user exists.

    Parameters:
    username
    Name of the user to check.

    Examples:

    • Write $SYSTEM.SQL.UserExists("Robert") // Writes a 1 if user Robert exists
    This method can also be called as a Stored Procedure named %SYSTEM.SQL_UserExists(username)
    deprecated classmethod ValidateTable(tablename As %Library.String = "", lockOption As %TinyInt = 0, index As %BigInt = 0) as %Library.Status [ SQLProc = ValidateTable ]
    Projected as the stored procedure: ValidateTable
    THIS METHOD IS DEPRECATED. Refer to ValidateTable() in %SYSTEM.SQL.Schema 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:
    tablename
    Name of the table to validate data. Must be a table name, not a view name.
    lockOption
    Not currently used - reserved for future use
    index
    Internal use only

    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:

    • After calling $SYSTEM.SQL.ValidateTable(...), the resultset will be held in the %sqlcontext object. Do %sqlcontext.%Display() will dump the results to the current device
    • If the table is sharded, this should be called on the shard master table
    • There is no locking performed by the utility. If run on a table in a live system, you could receive false-positive error reports
    deprecated classmethod ViewExists(viewname As %Library.String = "", ByRef metadata As %String) as %Library.Boolean [ SQLProc = SQL_ViewExists ]
    Projected as the stored procedure: SQL_ViewExists
    THIS METHOD IS DEPRECATED. Refer to ViewExists() in %SYSTEM.SQL.Schema instead. This entry point can be used to determine if a view exists.

    Parameters:
    viewname
    Name of the view to check.
    viewname can be qualified or unqualified. If unqualified, the default schema is applied.
    metadata
    Passed by reference, optional argument.
    If the view exists, returns the following information about the view: $ListBuild(SchemaName,ViewName,Classname that projected the view,System Flag)

    Examples:

    • Write $SYSTEM.SQL.ViewExists("SQLUser.STestView") // Writes a 1 if view SQLUser.STestView exists
    • Write $SYSTEM.SQL.ViewExists("STestView",.metadata) // Writes a 1 if view [DefaultSchema].STestView exists, returns metadata=$lb("SQLUser","STestView","User.STestView","0")

    Notes:

    • If the user calling the function does not hold any privileges for the view, 0 will be returned.
    • If a class exists that would project this view to SQL during compilation, but the class has not been compiled, 0 will be returned.
    • If a class that projects the view is marked as hidden, 0 will be returned.
    • metadata will be set to "" if 0 is returned by the function.
    This method can also be called as a Stored Procedure named %SYSTEM.SQL_ViewExists(viewname)
    deprecated classmethod WEEK(dateexp As %Library.String = "") as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to WEEK() in %SYSTEM.SQL.Functions 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)

    dateexp
    An expression that is a logical %Library.Date, %Library.TimeStamp, or %Library.PosixTime value.
    deprecated classmethod YEAR(dateexp As %Library.String = "") as %Library.Integer
    THIS METHOD IS DEPRECATED. Refer to YEAR() in %SYSTEM.SQL.Functions 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)

    dateexp
    An expression that is a logical %Library.Date, %Library.TimeStamp, or %Library.PosixTime value.

    Inherited Members

    Inherited Methods

    FeedbackOpens in a new tab