Exam 918 Sql Features For Administrators

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.

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-Share Alike 2.5 License.