SQL built-in functions
An SQL statement can contain not only operators and column names, but also one or more functions. The use of functions makes it possible to perform more complex database queries and data manipulation. You can use one or more of the already built-in functions, or you can write those functions yourself.
A function expression is evaluated for each row in the query. Every function requires arguments.
SQL administration API functions
SQL administration APIs are built-in SQL administration API functions that can be used to accomplish administrative tasks through EXECUTE FUNCTION statements of SQL that emulate various administrative command-line utilities of Dynamic Server.
There are two built-in functions — the ADMIN and TASK that are defined only in the sysadmin database of each Dynamic Server instance. These functions can be invoked only by user Informix.
Enhanced data-type and UDR support in cross-server distributed operations
Distributed operations are queries that access data on tables or views that belong to databases other than the local database.
There are two kinds of distributed operations:
- Cross-database operations are queries that access data on databases that belong to the same instance.
- Cross-server operations are queries that access data on databases that belong to other instances.
Data-type
The following data types are also supported on cross-server operations:
- BOOLEAN
- LVARCHAR
- DISTINCT of built-in types that are not opaque
- DISTINCT of BOOLEAN
- DISTINCT of LVARCHAR
- DISTINCT of the DISTINCT types listed above
- DISTINCT types of basic SQL types: CHAR, INT, SMALLINT, FLOAT, SMALLFLOAT, DECIMAL, MONEY, SERIAL, DATE, DATETIME, INTERVAL, BYTE, TEXT, VARCHAR, NCHAR, NVARCHAR, SERIAL8 & INT8
UDR
UDRs are now valid on distributed operations in most contexts where an SPL is valid locally. Also, C or JAVA UDRs are valid where SPL UDRs are valid.
Optimizer directives
The optimizer calculates the best query plan for every query. Even though the optimizer uses a lot of statistics, there are cases where the optimizer does not make the best decision. To overwrite the decision of the optimizer, you have the option to use optimizer directives to force the optimizer to use a special query plan.
Improved statistics maintenance
To make a good decision about query plans, the optimizer needs information about the indexes and the distributions of data. When you create a new table or index, there is no distribution information available in the systemtables. To get that information, you have to run UPDATE STATISTICS. UPDATE STATISTICS creates and updates the distribution information, and updates the system catalog table with the information needed to optimize queries. It also re-optimizes stored procedure and is used to convert indexes when the server is being upgraded.
You can run UPDATE STATISTICS for a whole database, a single table, a single column, or a procedure.
UPDATE STATISTICS has three modes to be run:
- LOW
- MEDIUM
- HIGH
Index self-join
Index self-join is a type of index that optimizes the query if the leading columns of a composite index has a lot of duplicates and the non-leading columns have better selectivity.
In previous versions of IDS, the optimizer scanned the full range of a composite index that fulfilled the search criteria or performed a sequential scan if no criteria were associated with the leading columns.
Index self-join searches for unique combinations within the highly duplicated leading columns and then, with these unique combinations, performs small queries, applying filters on the more selective non-leading columns. The table is logically joined to itself.
Along with index self-join, there are two new directive access-methods.
- INDEX_SJ: Forces the optimizer to perform an index self-join.
- AVOID_INDEX_SJ: Forces the optimizer not to perform an index self-join.
XML publishing
With Version 11, you are able to publish the results of SQL statements to properly formatted XML. This can be done with functions provided in the GenXML library.
Functions
In general, there are two types of functions: Those that return LVARCHAR, and those that return CLOB. All functions can handle NULL values and special characters. If the result is greater than LVARCHAR (32739), use the clob version of the command — genxmlclob, instead of genxml.
Label-based access control
Label-based access control (LBAC) is a way to control access to data. It controls who has read and write access to a database object. A database object in this case is a row or a column. LBAC has row level granularity, column level granularity, or both. LBAC controls access by matching the security label assigned to a database object and the security label granted to a user. The IDSLBACRULES access rules is a set of predefined access rules that will govern access to protected rows and columns of a protected table. Only the database security administrator (DBSECADM) can configure the LBAC objects.