INSERT data to a table and file both

Dear Friends, We need to INSERT data to a table and the same time we need the same data to write to a file. We cannot do this activity from the application end since there are different types of applications doing the INSERT operation. So, we have to run this activity from DB end only. Thanks ! ------------------------------ Indika Jinadasa ------------------------------

If the table you are inserting into was an external table built on a file, then you would have what you want - a file that can also be used as a table. I expect though that this would have too many restrictions to make it a viable option. However, you can create an external table on a file, then add an insert trigger on the (real) table so that every insert to the table also automatically performs an insert into the external table which writes to the underlying file. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Hi Mike, afaik you can't INSERT INTO <external_table> VALUES (...) -> -26191 -26191 Incorrect use of an external table tablename in query. This statement describes an operation that is not allowed on an external table. The types of operations allowed for external tables are CREATE EXTERNAL TABLE, SELECT, DROP TABLE, GRANT (only SELECT and INSERT), REVOKE, CREATE SYNONYM, CREATE SCHEMA, RENAME COLUMN, RENAME TABLE, SELECT ... INTO EXTERNAL. The types of operations not allowed for external tables are TRUNCATE, UPDATE STATISTICS, START VIOLATION, STOP VIOLATION, ALTER TABLE, UPDATE, DELETE, CREATE TRIGGER, CREATE INDEX, CONNECT BY, INSERT INTO ... VALUES, LBAC operations, target tables of MERGE statements and template table for SAMEAS clause of CREATE EXTERNAL TABLE. Moreover, every new insert, e.g. from an INSERT INTO <external_table> SELECT FROM .... would implicitly wipe out pre-existing external table (i.e. file) content. If the operation had to occur synchronously (still not 'within same transaction'), maybe CDC, PushData or PostCommit Triggers would be options... with some programming required, but there's demo code (in $INFORMIXDIR/demo) that could easily be adjusted for this purpose. Or an INSERT trigger with a simply SPL UDR (or something more fancy non-SPL), which would run within same transaction, but also at the cost of INSERT performance. HTH, Andreas ------------------------------ Andreas Legner ------------------------------

Thanks Andreas - as you and Art reminded me, each new insert zaps the file contents. And the syntax required with the single inserts isn't supported. Ugh. That's a shame...would have been a simple solution... ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Maybe an easy approach would be to add a trigger and call a stored procedure for each insert which puts either a rowid (or some other unique identifier) in a "changelog" table (or the complete data if updates should be ignored). Then, add a cronjob which will read through the changelog table (once per minute or 5 minutes, depending on the requirements) and export the data, after successful export delete row from the changelog table. Would possibly be doable also synchronously with the system() statement in a procedure (use a script which locks the file using flock and then appends the data in csv format). Or a java stored procedure ... MARCUS HAARMANN

I smell an RFE! I already proposed an append flag for external tables. Maybe need to request INSERT to work! Anyone? Arr

create table test ( value1 integer,value2 integer ); create procedure dump_test (p_val1 integer, p_val2 integer)DEFINE line varchar (100); begin let line = p_val1 || ";" || p_val2 || ";"; system 'echo "' || line || '" >> /tmp/test.csv'; end end procedure; create trigger tr_test insert on testreferencing new as new_test for each row ( execute procedure dump_test (new_test.value1, new_test.value2) ); insert into test (value1, value2) values (1,2);Just a small (working, but not thread-safe) example. Instead of echo, use a script which locks the file .... MARCUS HAARMANN

Hi Indika, the "insert" is a Change Data operation - and why not use the CDC API to manage your outside data file? The benefit is to write back committed operations, only. Best Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany IBM Champion 2021 2022 2023 IIUG Board Member ------------------------------

Hi Marcus, Thank you ! This works perfectly. Thank you all of my friends for helping me to resolve the issue. Best Regards, Indika ------------------------------ Indika Jinadasa ------------------------------

Write it up and I'll vote for it. Hopefully both features (append and INSERT) can be included on a single RFE, but if it needs to be split into separate RFEs, post both numbers and I'll vote for both of them. ------------------------------ mark collins ------------------------------

Mark: The APPENDABLE EXTERNAL TABLES has been there for a few years: Appendable External | IBM Data and AI Ideas Portal for Customers OK, I've entered an RFE for the ability to use INSERT with external tables and included a note that it would work well with appendability: Allow INSERT to write to | IBM Data and AI Ideas Portal for Customers Art ------------------------------ Art S. Kagel, President and Principal Consultant ASK Database Management Corp. www.askdbmgt.com ------------------------------

Thanks Art - I have voted for both. ------------------------------ Mike Walker xDB Systems, Inc www.xdbsystems.com ------------------------------

Art, Thanks. I've voted for both. ------------------------------ mark collins ------------------------------