Exploring the New Features of Informix Dynamic Server Version 12.10
Lester is loafing off the coast of a South Sea island, so he asked me to write the column for this issue.
IBM® Informix® Dynamic Server Version 12.10, formerly code named Centaurus, was released on March 26, 2013 with the usual IBM fanfare. Among the new features and internal improvements is a new concentration on the SQL layer with a dual purpose:
- Provide better support to users of business intelligence (BI) and other online analytical processing (OLAP) tools
- Create an easier path for migrating software from other relational database management system (RDBMS) vendors to Informix
The list of SQL processing improvements and language features includes:
- INTERSECT and MINUS (and EXCEPT) set operators
- OLAP Windowing numbering, ranking, and aggregation functions
- Enhancements to the processing speed of ANSI equality joins
The list of new compatibility features includes:
- Ordering of NULLs as lowest or highest in ORDER BY clauses
- DISTINCT expressions as arguments to aggregation functions
- Multiple DISTINCT expressions in a single query
- Creating a permanent table from the results of SELECT statements
- SELECT … INTO a permanent table
- CASE expressions in an ORDER BY clause
- Numbering and ranking functions
In this article, I am going to address only a few of these features. Full descriptions can be found in the IBM Informix Information Center or in the Guide to SQL Syntax PDF manual, which you can download from the Informix web site.
CASE in ORDER BY
Have you ever wanted to ORDER BY a code column, but not in alphabetical order? In the past, you would have to either add an ordinal column to the code’s lookup table or write a function to return the correct ordinal based on the code value passed in. Now, you can simply map the codes to the desired ordinal directly in the ORDER BY clause. Here is an example of ordering database object names by object type with views listed before tables, tables listed before synonyms, and indexes listed last:
select idxname, owner, ‘I’ as tabtype
order by case when tabtype = ‘V’ then 1 when tabtype = ‘T’ then 2 when tabtype = ‘S’ then 3 when tabtype = ‘I’ then 4 else 5 end, owner, tabname;
Have you ever tried to copy data from one table to another and accidentally typed this?
SELECT * FROM source_table INTO target_table;
Then, you scratched your head for a minute over the -201 SQL error before you remembered that you should have typed this instead:
INSERT INTO target_table SELECT * FROM source_table;
I make that mistake all the time. Imagine my surprise when, while participating in the Centaurus EVP program, I typed a SELECT like the first one above and it worked! Before 12.10, this syntax worked only if the target table was a temp table that didn’t exist yet or an external table. Now you can do this:
SELECT * FROM source_table INTO std_target_table;
When you do, the engine will create a new RAW or STANDARD mode table with the specifications of the column types in the projection clause. You can even include calculated columns as long as you include a column name alias—plus, you can specify locking mode, storage location (IN or FRAGMENT BY), and extent details if desired. Similarly, you can do the following:
CREATE TABLE mytable AS SELECT * FROM existing_table;
Which is a syntax supported by other RDBMS products.
Where do you put nothing?
Informix traditionally sorts NULL-valued column entries as smaller than the smallest non-NULL value for that column type. This is still the default. However, to better support reporting software that assumes the behavior of its competitors, and to improve the ease of porting such software to Informix, you now have the option to sort NULL-valued entries as larger than the largest non-NULL value:
SELECT … ORDER BY col2 DESC, col3 DESC NULLS LAST;
If you want to document the explicit behavior you want, you can obtain the default behavior as shown here:
SELECT … ORDER BY col4 DESC NULLS FIRST;
That will do it for this issue. I’ll let Lester continue the survey of new features in V12.10 of Informix Dynamic Server when he gets back.
In the meantime, let me know what you think of Informix 12.10 in the comments.