Informix

 View Only
Expand all | Collapse all

INSERT data to a table and file both

  • 1.  INSERT data to a table and file both

    Posted Mon January 30, 2023 09:26 AM
    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
    ------------------------------


  • 2.  RE: INSERT data to a table and file both

    IBM Champion
    Posted Mon January 30, 2023 09:36 AM
    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
    ------------------------------



  • 3.  RE: INSERT data to a table and file both

    IBM Champion
    Posted Mon January 30, 2023 09:58 AM
    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
    ------------------------------



  • 4.  RE: INSERT data to a table and file both

    IBM Champion
    Posted Mon January 30, 2023 11:07 AM
    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
    ------------------------------



  • 5.  RE: INSERT data to a table and file both

    IBM Champion
    Posted Mon January 30, 2023 11:17 AM
    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






  • 6.  RE: INSERT data to a table and file both

    IBM Champion
    Posted Mon January 30, 2023 02:37 PM
    I smell an RFE! I already proposed an append flag for external tables. Maybe need to request INSERT to work! Anyone?

    Arr





  • 7.  RE: INSERT data to a table and file both

    Posted Tue January 31, 2023 09:53 AM
    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
    ------------------------------



  • 8.  RE: INSERT data to a table and file both

    IBM Champion
    Posted Tue January 31, 2023 10:19 AM
    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
    ------------------------------



  • 9.  RE: INSERT data to a table and file both

    IBM Champion
    Posted Tue January 31, 2023 10:27 AM
    Thanks Art - I have voted for both.

    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------



  • 10.  RE: INSERT data to a table and file both

    Posted Tue January 31, 2023 10:39 AM
    Art,

    Thanks.  I've voted for both.

    ------------------------------
    mark collins
    ------------------------------



  • 11.  RE: INSERT data to a table and file both

    IBM Champion
    Posted Mon January 30, 2023 05:26 PM
    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 test
        referencing 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







  • 12.  RE: INSERT data to a table and file both

    Posted Tue January 31, 2023 04:44 AM
    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
    ------------------------------



  • 13.  RE: INSERT data to a table and file both

    IBM Champion
    Posted Tue January 31, 2023 02:28 AM
    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
    ------------------------------