Informix

 View Only
  • 1.  Problem with EXTERNAL TABLE

    Posted 30 days ago
    Informix 14.10.FC7W1, RHEL 8.5 (I think)

    I have a table invoice_tbl, and I want to unload some of the data from that table to an external table.  The table has six rows that meet the selection criteria, but the resulting file contains only one row.

    I have the following SQL:

    SELECT count(*)
    FROM invoice_tbl
    WHERE batchno = 148;
    
    DROP TABLE IF EXISTS ext_invoice_table;
    
    --
    -- the above query returns a result of 6
    --
    
    CREATE EXTERNAL TABLE ext_invoice_table 
                ( id INTEGER
                , region SMALLINT
                , invoiceno INTEGER
                , invdat DATE
                , firstname VARCHAR(20)
                , midname VARCHAR(20)
                , lastname VARCHAR(20)
                , suffix CHAR(3)
                , add1 VARCHAR(30)
                , add2 VARCHAR(30)
                , city VARCHAR(20)
                , state CHAR(2)
                , zip CHAR(10)
                , totamt MONEY(16,2)
                )
    USING( FORMAT 'DELIMITED'
         , DELIMITER '|'
         , RECORDEND '|'
         , DATAFILES('DISK:/tmp/ext_tab_invoice')
         );
    
    
    
    INSERT INTO ext_invoice_table
    SELECT id
         , region
         , invoiceno
         , invdat
         , upper(firstname) AS firstname
         , upper(midname) AS midname
         , upper(lastname) AS lastname
         , upper(suffix) AS suffix
         , upper(add1) AS add1
         , upper(add2) AS add2
         , upper(city) AS city
         , upper(state) AS state
         , zip
         , totamt
    FROM invoice_tbl
    WHERE batchno = 148
    ORDER BY region, invoiceno
    INTO EXTERNAL ext_invoice_table;
    
    SELECT dbinfo('sqlca.sqlerrd2') as rows_processed FROM sysmaster:sysdual;
    
    --
    -- the above query also returns a result of 6
    --
    


    However, as I said earlier, the actual file /tmp/ext_tab_invoice has just one row.  It appears to be the first row, based on the invoiceno.

    I've also tried it as:

    SELECT count(*)
    FROM invoice_tbl
    WHERE batchno = 148;
    
    DROP TABLE IF EXISTS ext_invoice_table;
    
    SELECT id
         , region
         , invoiceno
         , invdat
         , upper(firstname) AS firstname
         , upper(midname) AS midname
         , upper(lastname) AS lastname
         , upper(suffix) AS suffix
         , upper(add1) AS add1
         , upper(add2) AS add2
         , upper(city) AS city
         , upper(state) AS state
         , zip
         , totamt
    FROM invoice_tbl
    WHERE batchno = 148
    ORDER BY region, invoiceno
    INTO EXTERNAL ext_invoice_table
    USING( FORMAT 'DELIMITED'
         , DELIMITER '|'
         , RECORDEND '|'
         , DATAFILES('DISK:/tmp/ext_tab_invoice')
         )
    ;
    
    SELECT dbinfo('sqlca.sqlerrd2') as rows_processed FROM sysmaster:sysdual;
    


    Again, dbinfo() shows 6 rows processed, but there is only one row in the file /tmp/ext_tab_invoice.

    I know that I ran into a situation in 11.50 where if you had two separate INSERT INTO statements, the second one overwrote the results of the first, rather than appending.  But this is a single INSERT statement, so all of the rows in the result set should be included in the output file.

    Any recommendations before I open a ticket w/IBM?



    ------------------------------
    Mark Collins
    ------------------------------


  • 2.  RE: Problem with EXTERNAL TABLE

    IBM Champion
    Posted 30 days ago
    Mark:

    Remove the "RECORDEND '|' " parameter from the external table definition. It is causing all six rows to be appended to each other in a single line in the file.

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: Problem with EXTERNAL TABLE

    Posted 30 days ago
    Thanks Art.  That fixed it.

    ------------------------------
    Mark Collins
    ------------------------------



  • 4.  RE: Problem with EXTERNAL TABLE

    InnerCircle
    Posted 30 days ago
    Really just a quick guess, but shouldn't RECORDEND and (column) DELIMITER be different?

    ------------------------------
    Andreas Legner
    ------------------------------



  • 5.  RE: Problem with EXTERNAL TABLE

    Posted 30 days ago
    Thanks.  You and Art both spotted that problem.

    ------------------------------
    Mark Collins
    ------------------------------



  • 6.  RE: Problem with EXTERNAL TABLE

    Posted 30 days ago
    Mark, I can see you create ext table does not feature à reject file that may report you errors at the row level. Did you  try this?






  • 7.  RE: Problem with EXTERNAL TABLE

    Posted 29 days ago
    I generally do include a REJECTFILE when we're using an EXTERNAL TABLE for loading data from other systems, since you can't always control what the inbound file might contain.  In this case, since the purpose is unloading data, we opted to omit that.

    ------------------------------
    Mark Collins
    ------------------------------