October 13 2014

Intercepting your record with triggles

If you try to change a flag of your record, but you don’t want to make any changes on your application
example :
Your product code use to be “Non Tax”
But now to have to be stored at “With GST”

Below is a sample table structure for illustraction purpose
c1 is the key and c2 is the content :-

create table t1
(
c1 integer,
c2 char(20),
primary key (c1) constraint c1
);

Create the procedure and triggle for the update
The triggle only takes effect for INSERT statement :-

create procedure update_t1_prc(pc1 int) returning CHAR(20);
DEFINE pc2 CHAR(20);

SELECT c2 into pc2
from t1
where c1 = pc1;

IF pc2 is not null THEN
LET pc2 = ‘With GST’;
END IF

return pc2;
end procedure;

create trigger update_t1_trig
insert on t1
REFERENCING NEW AS n
for each row (execute procedure update_t1_prc(n.c1) into c2);

Now, to some insert.
> insert into t1 values (24,’Non Tax’);

1 row(s) inserted.

> select * from t1 where c1=24;

c1 c2

24 With GST

1 row(s) retrieved.

Hope you find this useful

September 19 2014

How to check from Informix server if connections came from shared memory

from onstat -g ath output
grep for sm_read

informix@xxxxxxx: onstat -g ath | grep sm_read
23519 5d7ce028 5aa622d0 1 cond wait sm_read 8cpu sqlexec

To trace back the session
grep the 3rd column with onstat -u
informix@devlx005: onstat -u | grep 5aa622d0
5aa622d0 Y–P— 21045 informix 6 5bc6bd48 0 1 0 0

Session ID = 21045

August 21 2014

Informix Archecker – point in time restore/recover table (Scenario 2)

Tagged Under : , , , ,

I would like to share this feature with all of you – Informix Archecker.
It can be used to restore/recover (point-in-time) to a new target table (new table name) from backup.

Scenario 2:
– Someone accidentally performed a mass update for all rows in table “customer” on 2014-08-21 10:48:00.
– You need to restore & recover (point in time) to a new table called “customer_res” until 2014-08-21 10:46:00. (Current table “customer” will be maintained)
– Database = stores_demo, Source Table = customer, Target New Table = customer_res
– You are using Informix Archecker with Netbackup Informix Agent for backup/restore.

Note: Informix Archecker is available since IDS 10.00 or higher.

(1) 2014-08-21 09:50:00:

$ dbaccess stores_demo –
> select count(*) from customer ;

(count(*))

28