%SYSTEM.SQL.Util
abstract class %SYSTEM.SQL.Util extends %SYSTEM.Help
Method Inventory
- CloseAllGateways()
- GetMapSelectability()
- GetOption()
- SetDDLIdentifierTranslations()
- SetMapSelectability()
- SetOption()
Methods
classmethod CloseAllGateways() as %Integer [ Language = objectscript ]
This method closes all the open ODBC/JDBC Gateway connections and unloads the SQL Gateway shared library.
classmethod GetMapSelectability(pTablename As %Library.String = "", pMapname As %Library.String = "") as %Library.String [ Language = objectscript ]
This entrypoint is used to determine if an SQL Map definition is selectable by the SQL Query Optimizer or not
Parameters:
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: The method returns 1 if the map is selectable. If the map is not selectable, 0 is returned. If the Selectability of the map cannot be determined, a message string will be returned.
Note:
- A return of 1 by this function does not necessarily mean the map exists, just that the map's selectability has not been set to 0.
- Also, this feature 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.
Examples:
- Write $SYSTEM.SQL.Util.GetMapSelectability("Sample.Person","NameIndex")
classmethod GetOption(pOption As %String = "") as %String [ Language = objectscript, SQLProc = Util_GetOption ]
Given one of the following valid, case-insensitive values for pOption, return the current value for that option.
AutoCommit - return the current process's Transaction Commit Mode value. Possible values are:
BitmapFriendlyCheck - returns the current system-wide setting of the BitmapFriendlyCheck flag, a 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.
CollectionProjection - returns the collection projection setting. Possible values are:
CompileMode - returns the current Compile Mode setting, either Deferred or Immediate.
IdentityInsert - return the current IDENTITY_INSERT option value. Possible values are:
IsolationMode - returns current process's Transaction Isolation Mode value. Possible values are:
LockThreshold - returns 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.
LockTimeout - 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.
ProcessLockTimeout - return the Lock Timeout value for the current process.
SQLFunctionArgConversion - return the current setting of the system wide flag for controlling if SQL Functions perform ODBCToLogical/DisplayToLogical on SQL Function input arguments.
SelectMode - return the select mode value for the current process. Possible values are:
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. ServerDisconnectCode - 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. ServerInitCode - 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. Example: write $SYSTEM.SQL.Util.GetOption("AutoCommit")
| 0 | NO AUTO TRANSACTION |
| 1 | AUTO COMMIT ON (Default) |
| 2 | AUTO COMMIT OFF |
| 0 | Do not project collections as columns if also projected as child table (DEFAULT) |
| 1 | Project all collections as columns |
| 0 | IDENTITY cannot be set |
| 1 | IDENTITY can be set |
| 0 | - READ UNCOMMITTED (Default) |
| 1 | - READ COMMITTED |
| 3 | - READ VERIFIED |
| 0 | Logical |
| 1 | ODBC |
| 2 | Display |
#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. ServerDisconnectCode - 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. ServerInitCode - 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. Example: write $SYSTEM.SQL.Util.GetOption("AutoCommit")
classmethod SetDDLIdentifierTranslations(from As %Library.String, to As %Library.String = "", ByRef oldfrom As %Library.String, ByRef oldto As %Library.String) as %Library.Status [ Language = objectscript ]
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: - 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.
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
- Status Code
classmethod SetMapSelectability(pTablename As %Library.String = "", pMapname As %Library.String = "", pValue As %Boolean = "") as %Library.Status [ Language = objectscript ]
This entrypoint is used to make an SQL Map definition [not] selectable to the SQL Query Optimizer
Parameters:
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:
- A %Status indicating whether the operation was successful
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.Util.SetMapSelectability("Sample.Person","NameIndex",1) // SQL Query Optimizer WILL consider map NameIndex
- Write $SYSTEM.SQL.Util.SetMapSelectability("Sample.Person","NameIndex",0) // SQL Query Optimizer WILL NOT consider map NameIndex
Returns:
classmethod SetOption(pOption As %String, pValue As %String = "", Output oldValue) as %Status [ Language = objectscript, SQLProc = Util_SetOption ]
Given one of the following valid, case-insensitive values in pOption, update the option to pValue, store the previous value in oldValue
if applicable, and return a status object reflecting whether the update was successful.
ANSIPrecedence - determines if ANSI operator precedence is applied.
TRUE(1)/FALSE(0) If pValue is FALSE(0), do not apply ANSI precedence in SQL statements.
If TRUE(1) (the default), do apply ANSI precedence in SQL.
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.
AllowExtrinsicFunctions - determines if extrinsic functions are allowed to be used in SQL statements through ODBC, JDBC, and Dynamic SQL. TRUE(1)/FALSE(0) If pValue 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 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.
AutoCommit - 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. 0 - No AutoCommit
1 - AutoCommit ON
2 - AutoCommit OFF
AutoParallel - Set the configuration setting which determines if auto hinting %PARALLEL is applied. The default is use of auto hinting %PARALLEL is not appied. TRUE(1)/FALSE(0) If pValue is FALSE(0) (the default), do not apply auto hinting for %PARALLEL in SQL statements. If TRUE(1), do apply auto hinting for %PARALLEL in SQL. 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.
AutoParallelThreshold - Set the configuration setting which determines if auto hinting %PARALLEL is applied. The default is use of auto hinting %PARALLEL is not appied. TRUE(1)/FALSE(0) If pValue is FALSE(0) (the default), do not apply auto hinting for %PARALLEL in SQL statements. If TRUE(1), do apply auto hinting for %PARALLEL in SQL. 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.
BitmapFriendlyCheck - 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. - Changing this configuration setting will only take effect immediately for all processes. - This is a system-wide setting.
CachedQueryLockTimeout - Defines the lock timeout length, in seconds, used for Cached Queries when a lock needs to be acquired on Cached Query metadata. The default is 120 seconds. - The setting is on a per system basis.
CachedQuerySaveSource - 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. 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. - 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.
CollectionProjection - Sets the collection projection option. Setting takes effect when classes are recompiled. 0 - do not project collections as a column if the collection is projected as a child table.
1 - project all collections as columns.
CompileModeDeferred - 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.
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:
CompileModeImmediate - 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. - 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 }
CompileModeInstall - 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. 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:
CompileModeNocheck - 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:
DDLDefineBitmapExtent - 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. 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. - 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.
DDLDropTabDelData - Sets the flag which determines if a DDL DROP TABLE statement deletes the table's data. 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. - 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.
DDLFinal - Sets the flag which determines if a class created by a DDL CREATE TABLE statement is Final. 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. - 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.
DDLPKeyNotIDKey - 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. TRUE(1)/FALSE(0) If pValue 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). - 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.
DDLUseExtentSet - 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. 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. - 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.
DDLUseSequence - 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. 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. - 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.
DefaultTimePrecision - Set the Default precision for the Time component of the value returned by the GETDATE(), CURRENT_TIME, and CURRENT_TIMESTAMP SQL Scalar functions. Input precision to pValue, the 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. - 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.
DelimitedIdentifiers - 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. TRUE(1)/FALSE(0) If pValue is TRUE (1), "..." is treated as an identifier. If flag is FALSE (0), "..." is treated as a string literal. The default is TRUE (1). - 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.
ECPSync - Defines whether or not SQL SELECT statements perform a $SYSTEM.ECP.Sync() call in the OPEN code. The default is ECP Sync is OFF. 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. - 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.
FastDistinct - Defines whether or not SQL DISTINCT is optimized to use indexes. The default is DISTINCT optimizations are ON. TRUE(1)/FALSE(0) pValue 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. 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. - 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.
FilerRefIntegrity - 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). TRUE(1)/FALSE(0) flag to determine if SQL Filer referential integrity checks are performed. If pValue is FALSE (0), the SQL Filer will skip referential integrity checks. Checks will be performed if pValue is TRUE (1). The default is TRUE (1). - 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.
IdentityInsert - 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:
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.
IsolationMode - 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:
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.
LockThreshold - Set the Lock Threshold for locks acquired during filing of rows within a single transaction. Input to pValue the number of row locks to acquire before escalating to a table lock. The default is 1000. - 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.
LockTimeout - Set the Lock timeout for locks acquired during execution of SQL statements. Input to pValue the number of seconds to set the lock timeout to. The default is 10 seconds. - 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.
ProcessLockTimeout - Set the Lock Timeout for this process. Input to pValue lock timeout (in seconds) for locks made during execution of SQL statements. The input should always be an integer value. Setting takes effect immediately and lasts for the duration of the process or until SetProcessLockTimeout is called again. QueryProcedures - 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. TRUE(1)/FALSE(0) pValue 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. - 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.
RTPC - Set the configuration setting which determines if Run Time Plan Choice is applied. The default is that Run Time Plan Choice is applied. TRUE(1)/FALSE(0) If pValue 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. - 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.
RetainSQL - 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. 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). - 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.
SQLFunctionArgConversion - 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. TRUE(1)/FALSE(0) If pValue 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. - 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.
SQLSecurity - 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. Input to pValue is TRUE(1)/FALSE(0). If pValue is TRUE (1), SQL security is ON. If pValue is FALSE (0), SQL security is OFF. The default is TRUE(1). - 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.
SelectMode - 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:
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.
ServerDisconnectCode - 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. Input to pValue a single line of ObjectScript to be executed. Call with code="" or undefined to delete disconnect code for this namespace. - 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.
ServerInitCode - 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.
TCPKeepAlive - Set the TCP Keep Alive interval for xDBC connections. Input to pValue the number of seconds to set the TCP Keep Alive interval to. The default is 300 seconds (5 minutes). - 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.
ToDateDefaultFormat - Set the default format for the SQL TO_DATE() function. Input to pValue a string value with the default format the TO_DATE function will return. - 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.
AllowExtrinsicFunctions - determines if extrinsic functions are allowed to be used in SQL statements through ODBC, JDBC, and Dynamic SQL. TRUE(1)/FALSE(0) If pValue 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 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.
AutoCommit - 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. 0 - No AutoCommit
1 - AutoCommit ON
2 - AutoCommit OFF
AutoParallel - Set the configuration setting which determines if auto hinting %PARALLEL is applied. The default is use of auto hinting %PARALLEL is not appied. TRUE(1)/FALSE(0) If pValue is FALSE(0) (the default), do not apply auto hinting for %PARALLEL in SQL statements. If TRUE(1), do apply auto hinting for %PARALLEL in SQL. 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.
AutoParallelThreshold - Set the configuration setting which determines if auto hinting %PARALLEL is applied. The default is use of auto hinting %PARALLEL is not appied. TRUE(1)/FALSE(0) If pValue is FALSE(0) (the default), do not apply auto hinting for %PARALLEL in SQL statements. If TRUE(1), do apply auto hinting for %PARALLEL in SQL. 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.
BitmapFriendlyCheck - 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. - Changing this configuration setting will only take effect immediately for all processes. - This is a system-wide setting.
CachedQueryLockTimeout - Defines the lock timeout length, in seconds, used for Cached Queries when a lock needs to be acquired on Cached Query metadata. The default is 120 seconds. - The setting is on a per system basis.
CachedQuerySaveSource - 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. 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. - 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.
CollectionProjection - Sets the collection projection option. Setting takes effect when classes are recompiled. 0 - do not project collections as a column if the collection is projected as a child table.
1 - project all collections as columns.
CompileModeDeferred - 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.
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.
CompileModeImmediate - 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. - 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 }
CompileModeInstall - 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. 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)
CompileModeNocheck - 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.
DDLDefineBitmapExtent - 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. 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. - 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.
DDLDropTabDelData - Sets the flag which determines if a DDL DROP TABLE statement deletes the table's data. 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. - 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.
DDLFinal - Sets the flag which determines if a class created by a DDL CREATE TABLE statement is Final. 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. - 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.
DDLPKeyNotIDKey - 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. TRUE(1)/FALSE(0) If pValue 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). - 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.
DDLUseExtentSet - 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. 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. - 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.
DDLUseSequence - 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. 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. - 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.
DefaultTimePrecision - Set the Default precision for the Time component of the value returned by the GETDATE(), CURRENT_TIME, and CURRENT_TIMESTAMP SQL Scalar functions. Input precision to pValue, the 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. - 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.
DelimitedIdentifiers - 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. TRUE(1)/FALSE(0) If pValue is TRUE (1), "..." is treated as an identifier. If flag is FALSE (0), "..." is treated as a string literal. The default is TRUE (1). - 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.
ECPSync - Defines whether or not SQL SELECT statements perform a $SYSTEM.ECP.Sync() call in the OPEN code. The default is ECP Sync is OFF. 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. - 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.
FastDistinct - Defines whether or not SQL DISTINCT is optimized to use indexes. The default is DISTINCT optimizations are ON. TRUE(1)/FALSE(0) pValue 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. 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. - 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.
FilerRefIntegrity - 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). TRUE(1)/FALSE(0) flag to determine if SQL Filer referential integrity checks are performed. If pValue is FALSE (0), the SQL Filer will skip referential integrity checks. Checks will be performed if pValue is TRUE (1). The default is TRUE (1). - 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.
IdentityInsert - Set the IDENTITY_INSERT option for this process. IDENTITY_INSERT controls the ability of the user to specify a value for the IDENTITY property when saving a new object, a value for the IDENTITY column or an explicit ROWID value in an SQL INSERT. If IDENTITY_INSERT is false and the user specifies an explicit IDENTITY or ROWID value when saving a new object (ObjectScript) or inserting a new ROW (SQL) then an error condition is reported. Setting takes effect immediately and lasts for the duration of the process or until SetIdentityInsert is called again. Valid values are:
| 0 | IDENTITY cannot be set |
| 1 | IDENTITY can be set |
IsolationMode - 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 |
LockThreshold - Set the Lock Threshold for locks acquired during filing of rows within a single transaction. Input to pValue the number of row locks to acquire before escalating to a table lock. The default is 1000. - 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.
LockTimeout - Set the Lock timeout for locks acquired during execution of SQL statements. Input to pValue the number of seconds to set the lock timeout to. The default is 10 seconds. - 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.
ProcessLockTimeout - Set the Lock Timeout for this process. Input to pValue lock timeout (in seconds) for locks made during execution of SQL statements. The input should always be an integer value. Setting takes effect immediately and lasts for the duration of the process or until SetProcessLockTimeout is called again. QueryProcedures - 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. TRUE(1)/FALSE(0) pValue 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. - 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.
RTPC - Set the configuration setting which determines if Run Time Plan Choice is applied. The default is that Run Time Plan Choice is applied. TRUE(1)/FALSE(0) If pValue 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. - 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.
RetainSQL - 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. 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). - 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.
SQLFunctionArgConversion - 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. TRUE(1)/FALSE(0) If pValue 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. - 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.
SQLSecurity - 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. Input to pValue is TRUE(1)/FALSE(0). If pValue is TRUE (1), SQL security is ON. If pValue is FALSE (0), SQL security is OFF. The default is TRUE(1). - 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.
SelectMode - Set the select mode for this process. Setting takes effect immediately and lasts for the duration of the process or until SetSelectMode is called again. Valid values are:
| 0 | Logical |
| 1 | ODBC |
| 2 | Display |
ServerDisconnectCode - 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. Input to pValue a single line of ObjectScript to be executed. Call with code="" or undefined to delete disconnect code for this namespace. - 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.
ServerInitCode - 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.
TCPKeepAlive - Set the TCP Keep Alive interval for xDBC connections. Input to pValue the number of seconds to set the TCP Keep Alive interval to. The default is 300 seconds (5 minutes). - 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.
ToDateDefaultFormat - Set the default format for the SQL TO_DATE() function. Input to pValue a string value with the default format the TO_DATE function will return. - 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.