DB2 for i – FAQ & Howtos (EN)

iDB2-FAQ-011 – How to get a CSV file from a SQL statement?

As you know, you can get a CSV file (or a TXT, XLS and so on file) from the result of a query with ACS SQL Script… but if you need to generate a CSV directly from a SQL script you could mix SQL and CL command, as in the following example where we get a CSV file an IFS directory from a query on a NETSTAT system view:

create table qtemp.netstat as
 (SELECT *
     FROM qsys2.netstat_info)
     with data;
 cl: CPYTOIMPF FROMFILE(QTEMP/NETSTAT)
     TOSTMF('/home/faq/netstat.csv')
     STMFCCSID(PCASCII) RCDDLM(CRLF) ADDCOLNAM(*SQL);  

And if you want schedule or call this script from another program you could execute it with RUNSQLSTM, as in this simple CL program

       PGM          
         RUNSQLSTM  SRCFILE(FAQ400/SRC) SRCMBR(XNETSTAT) COMMIT(*NONE) OUTPUT(*PRINT)   
       ENDPGM