Cheetah spot by spot: C style comments in dbaccess

Another quick note on another minor (from my point of view) feature of IDS 11.10.
Beginning with v11.10 dbaccess will accept C language style multi-line comments.

As such, you can write things like:

SELECT * /* this is the
select clause */
FROM customer /* This is the FROM clause */

This will allow for better compatibility with other RDBMS SQL scripts, and it's also SQL-99 compliant.

The other forms of comments recognized by dbaccess will still be available:

• "-- comments" for single line comments
• "{ comments }" for multi-line comments

The introduction of this commenting style also affects the ways to specify optimizer directives.
The syntax /*+ */ are also accepted in the new version. So, you could use this:

SELECT /*+ FULL ( customer ) */  *
FROM
        customer
WHERE
        customer_num = 101;

to force the query to make a full table scan.

Cheetah spot by spot: Multiple triggers for same event/table

Welcome to another IDS 11.10 (Cheetah) new feature close-up. This time we'll take a look at the possibility of creating multiple triggers on the same table and same event.
Up to IDS 11 you could have only one INSERT/UPDATE/DELETE/SELECT trigger for each table. That means that if you wanted to do more in one of the events for which you already had a trigger you would have to change the existing trigger definition.

Now, with Cheetah, you can simply create another trigger. When the event happens both triggers will be fired and their actions will be done. It's a simple concept, but you should be aware of one important aspect: The sequence of execution of the triggered actions.

There is no guarantee to which trigger will execute first, but all the BEFORE actions will be done before all FOR EACH ROW actions, and finally the AFTER actions will be executed. So, the events have a pre-defined order to be executed, but you can't tell which trigger's event will be fired first. I've set up a test case that tries to show this issues. Let's take a look:

1 drop procedure trigger_change;
2 drop table test;
3 drop table action_log;
4 drop procedure trigger_proc;
5 CREATE TABLE test
6 (
7       col1 INTEGER,
8       col2 CHAR(20)
9 ) LOCK MODE ROW;
10
11 CREATE TABLE action_log
12 (
13      trig_name CHAR(20),
14      trig_event CHAR(20),
15      trig_action CHAR(20),
16      trig_seq SERIAL
17 ) LOCK MODE ROW;
18
19 CREATE PROCEDURE trigger_change () REFERENCING OLD AS old NEW AS new FOR test
20      LET new.col1 = 0;
21 END PROCEDURE;
22 CREATE PROCEDURE trigger_proc (t_name CHAR(20), t_action CHAR(20))
23      DEFINE t_event CHAR(20);
24      LET t_event = NULL;
25      IF t_action = 'FOR EACH ROW'
26      THEN
27              SELECT
28              CASE
29                      WHEN DELETING THEN "DELETING"
30                      WHEN UPDATING THEN "UPDATING"
31                      WHEN INSERTING THEN "INSERTING"
32                      WHEN SELECTING THEN "SELECTING"
33              END
34              INTO t_event
35              FROM sysmaster:sysdual;
36      END IF
37      INSERT INTO action_log VALUES (t_name, t_event, t_action, 0);
38 END PROCEDURE;
39
40 CREATE TRIGGER ti_1_test INSERT ON test
41 BEFORE
42 (
43      EXECUTE PROCEDURE trigger_proc('ti_1_test','BEFORE')
44 )
45 FOR EACH ROW
46 (
47      EXECUTE PROCEDURE trigger_proc('ti_1_test','FOR EACH ROW'),
48      EXECUTE PROCEDURE trigger_change() WITH TRIGGER REFERENCES
49 )
50 AFTER
51 (
52      EXECUTE PROCEDURE trigger_proc('ti_1_test','AFTER')
53 );
54
55 CREATE TRIGGER ti_2_test INSERT ON test
56 BEFORE
57 (
58      EXECUTE PROCEDURE trigger_proc('ti_2_test','BEFORE')
59 )
60 FOR EACH ROW
61 (
62      EXECUTE PROCEDURE trigger_proc('ti_2_test','FOR EACH ROW')
63 )
64 AFTER
65 (
66      EXECUTE PROCEDURE trigger_proc('ti_2_test','AFTER')
67 );
68
69 INSERT INTO TEST VALUES(1,"one...");
70 SELECT * FROM test;
71 SELECT * FROM action_log;

The execution result is:

cheetah@PacMan.domus.online.pt:informix-> dbaccess stores_demo multiple_triggers.sql
Your evaluation license will expire on 2007-11-24 00:00:00

Database selected.


Routine dropped.


Table dropped.


Table dropped.


Routine dropped.


Table created.


Table created.


Routine created.


Routine created.


Trigger created.


Trigger created.


1 row(s) inserted.



      col1 col2

         0 one...

1 row(s) retrieved.



trig_name            trig_event           trig_action             trig_seq

ti_1_test                                 BEFORE                         1
ti_2_test                                 BEFORE                         2
ti_1_test            INSERTING            FOR EACH ROW                   3
ti_2_test            INSERTING            FOR EACH ROW                   4
ti_1_test                                 AFTER                          5
ti_2_test                                 AFTER                          6

6 row(s) retrieved.


Database closed.

So, let's make a line by line examination of the script. Hopefully, at the end, and after looking at the results, you'll get a clear picture of what's new in this area.

Lines 1 to 18 just create two tables: "test" table will be used as the triggers object and "action_log" will save the name of the trigger (in a minute...), the event that was being executed (BEFORE, FOR EACH ROW, AFTER), the action (INSERT,DELETE...) and the sequence by which they were called.

Lines 19 to 21 create what is called a triggered UDR. This UDR must be created in SPL, but can call other UDRs in another languages (C, JAVA...). This UDR must contain the REFERENCING and FOR clauses to be considered a triggered UDR. The referencing clause is similar to the same clause in triggers. The FOR clause defines which table's triggers can call this UDR in the FOR EACH ROW clause. I've included this to show that you can change the values that the user process used as VALUES in an INSERT statement for example. So, actually you can replace the values given by the client process.

The next lines (22-38) contain another procedure that will be called by the triggers and receives the trigger name and action. This procedure contains two new things:

1. It uses the new operators "DELETING", "INSERTING", "UPDATING" and "SELECTING". These new operators can be used in the context of a FOR EACH ROW action and return true if the trigger event is respectively DELETE, INSERT, UPDATE and SELECT
2. It also uses a new IDS 11.10 feature, the sysmaster:sysdual table. This is an Oracle ©right; compatibility feature. You could create a "dual" table in any Informix version, so this isn't really a big feature...

This procedure logs the data in the action_log table, inserting a zero into the trig_seq field. This forces IDS to generate a value by increasing the last one inserted. So we will get the execution order of the triggered actions by looking at this fields.

Lines 40-68 include the two trigger definitions. They are both INSERT triggers and they call the second procedure in each of it's actions. The first one also calls the first procedure (trig_change) to show that the insert value can be changed.

Line 69 just does an INSERT on the test table with the values "1" and "One". This will fire both triggers.

Lines 70 shows the content of table test, after the previous INSERT, and line 71 shows the contents of the action_log table.

So, what do we see in the results?:

1. The value inserted in the test table was actually "0" and not "1"
2. The sequence column of the action_log table shows that the BEFORE actions of both triggers were executed first, then the FOR EACH ROW and last the both AFTER clauses.

In this case the triggers were executed in the creation order, but as stated in the manual we can't assume this. What is documented and can be assumed is that the order of execution of the actions is (it couldn't be any other way...):

1. First, all the BEFORE actions of all the triggers for the event
2. Then all the FOR EACH ROW actions of all the triggers for the event
3. In the end, all the AFTER actions of all the triggers for the event

We should also keep in mind that we can write triggered UDRs, and in these we can actually change the new values. Finally, we can also use the new operators to let the procedure code know which event are we running (INSERT/UPDATE/DELETE/SELECT).

Why would we want to use this feature? Well, for start, it can be handy in application conversions from other RDBMS where this is available and the existing application already uses it. Besides this, DBAs can use this to implement some triggered behavior in "blackbox applications". By this I mean applications where you don't have any influence on the developing phase. You can create your own triggers without interfering with existing ones. This can be used for auditing purposes or for some data extraction, based on changes made on the data fields.

So, in conclusion, the usefulness of this feature will depend largely on your environment requirements, but in some scenarios this can be a very valuable tool.

I think that if you look at the example above you'll see the most important aspects of this feature. So, take your time, and if any doubts remain don't hesitate to check the URL references to the manual and other sources. You can also leave a comment of course.

References:

• Information Center page of SQL Syntax Guide for CREATE PROCEDURE (REFERENCING and FOR clauses)
• Information Center page of SQL Syntax Guied for CREATE TRIGGER
  

Cheetah spot by spot: Read all about all the spots!

This is not exactly an article about another Cheetah feature. Instead of bothering myself writing about another one, I point you to someone who already done it:

Carlton Doe, a very well known IDS specialist, author of several books and user groups presentations has just written a new book about IDS 11.10.
The book should be around on time for IDS 11 GA. You can see a reference to it on the author's website.

I don't have any information about the book structure or contents, but given the author's history and background, I sure hope this will be a great and informative book where all the spots are covered.

Cheetah spot by spot: Derived tables in FROM clause

Here we are for a very quick note on another usability feature of IDS 11.10 (Cheetah).
This time we'll see derived tables in the FROM clause of SELECT statements. This referrers to the possibility of "creating" tables on the fly as sub-querys in the FROM clause.

If you work in an environment where the developers also work with other RDBMS software you've probably had a few situations where they ask you: How can I do it with Informix?
There were two answers for this:

1. If using IDS 7.31, we would have to use temporary tables
2. If using IDS 9.40+ we could use the collection derived tables: TABLE(MULTISET(SELECT ... FROM ... WHERE ...))

Usually none of this would be welcome. Sometimes we have to make the query in environments where the use of temp tables isn't acceptable or desirable (ETL tools for example where we might not have control over connection lifetime, so we cannot guarantee that the temporary tables will be there when needed). The second option looks strange to an Informix outsider, and in some situation it may force the query to be different depending on the RDBMS to use - This is not desirable to application developers that want to be RDBMS neutral.

So, once more, IBM decided to ease our lives, and implement what is also an ANSI standard: Derived tables. Let's see an example:

select
o1.*
from
orders o1,
(
select max(order_num) , c.customer_num
from orders o0, customer c where c.customer_num = o0.customer_num
group by 2
) as max_orders(order_id,customer_id)
where
o1.order_num = max_orders.order_id

This is possible in IDS 11.10. In IDS 10.00 we could use:

select
o1.*
from
orders o1,
table(multiset(
select max(order_num)::integer as order_id, c.customer_num::integer as customer_id
from orders o0, customer c
where c.customer_num = o0.customer_num group by 2
)) max_orders
where
o1.order_num = max_orders.order_id

So, it was equally possible, but the syntax would be non-standard and looked more complex.
Note: The cast to integer (::integer) is required because the fields are of type SERIAL which is not allowed in MULTISETS.

For those that use this form, when you upgrade to IDS 11 you may want to consider rewriting your queries. Accordingly to the SQL syntax guide manual, the performance of a query written in the new format is "as least as good as a that of a query that uses the Informix-extension TABLE (MULTISET ( SELECT ... )) syntax to specify an equivalent derived table in the FROM clause". This insinuates that there may be performance advantages in this syntax. This is probably related to the fact that IDS does not materialize derived tables that are considered "simple". Clauses like aggregates, order by or unions define this tables as complex.

So, this is a very simple feature with usability, ANSI compliance and application portability in mind.