Problem solve Get help with specific problems with your technologies, process and projects.

Use SQL to SELECT data directly to a CSV file

This command will direct the results of any valid SQL SELECT statement directly into a CSV file in the IFS .

This command will direct the results of any valid SQL SELECT statement directly into a CSV file in the IFS and,...

optionally, insert a column heading row based on the file/field descriptions of the result set. As a prerequisite you must have implemented the ZFFDTOCSV and ZCPYTOCSV commands which I have previously submitted as tips.

The command consists of a simple CMD/CLP combination (ZSLTTOCSV) for which I have included the source.

ZSLTTOCSV parameters ...

SQLSTMT
--------
Enter any valid SQL SELECT statement.

TOFILE
------
Fully qualified name (including path) of the target CSV file in the IFS. If not found, the file is created. If found, data is added or replaced according to DATAOPT parm value. Note if you wish the target file to have a "csv" extension, you must include it in the file name (this command does NOT add the ".csv").

DATAOPT
-------
Add or replace data in TOFILE. Valid values are *ADD/*REPLACE. Default is *ADD. Note: if *REPLACE is specified the target file will be deleted prior to execution of the CPYTOIMPF command (my quick fix to figuring out how to clear an IFS file).

COLHDG
------
Controls whether or not a column heading row is inserted into the target file. Valid values are the same as those in ZFFDTOCSV plus the value *NONE which is the default and will prevent a column heading row from being inserted.

Processing overview ...

1) The incoming SQL statement is used to create a temporary VIEW in QTEMP. This is achieved by prefixing the select statement with "create view qtemp/zslttocsv as " and feeding the resulting statement to a SQL processing command (in this case ZEXECSQL).

2) ZCPYTOCSV is then used to copy the VIEW contents to the IFS file. Column headings are added as required (see ZFFDTOCSV).

That's it ... use this command to send ad-hoc DB2/400 data to your users in a format that's Excel friendly.

Note: This command was developed at V5R1. With V5R2 you can specify "create table as ..." instead of "create view as ...". This MAY be a better way to go as you will not incur the overhead of creating the QTEMP logical file.


 

/* ================================================================= */
/*                                                                   */
/* Command    : ZSLTTOCSV                                            */
/*                                                                   */
/* Description: SQL SELECT data to a CSV file                        */
/*                                                                   */
/* ================================================================= */

 ZSLTTOCSV:  CMD        PROMPT('Convert SQL to CSV')

 SQLSTMT:    PARM       KWD(SQLSTMT) TYPE(*CHAR) LEN(300) MIN(1) +
                          EXPR(*YES) PROMPT('SQL statement')

 TOFILE:     PARM       KWD(TOFILE) TYPE(*CHAR) LEN(256) MIN(1) +
                          EXPR(*YES) PROMPT('To file')

 DATAOPT:    PARM       KWD(MBROPT) TYPE(*CHAR) LEN(8) RSTD(*YES) +
                          DFT(*ADD) VALUES(*ADD *REPLACE) +
                          EXPR(*YES) PROMPT('Replace/add TOFILE +
                          records')

 COLHDG:     PARM       KWD(COLHDG) TYPE(*CHAR) LEN(10) RSTD(*YES) +
                          DFT(*NONE) VALUES(*NONE *TEXT *ALTNAME +
                          *COLHDGS *FLDNAME) EXPR(*YES) PROMPT('Add +
                          column headings')

/* ================================================================= */
/* Program    : ZSLTTOCSV                                            */
/*                                                                   */
/* Description: SQL SELECT data to a CSV file                        */
/*                                                                   */
/* Parameters : SQLSTMT  - SQL SELECT statement                      */
/*              TOFILE   - Fully qualified target file name          */
/*              DATAOPT  - Add/Replace data in target file           */
/*              COLHDG   - Option to all column headings             */
/*                                                                   */
/* ================================================================= */

 ZSLTTOCSV:  PGM        PARM(&SQLSTMT &TOFILE &DATAOPT &COLHDG)

             DCL        VAR(&SQLSTMT) TYPE(*CHAR) LEN(3000)
             DCL        VAR(&TOFILE) TYPE(*CHAR) LEN(256)
             DCL        VAR(&DATAOPT) TYPE(*CHAR) LEN(8)
             DCL        VAR(&COLHDG) TYPE(*CHAR) LEN(8)

             DCL        VAR(&VIEWNAME) TYPE(*CHAR) LEN(10) +
                          VALUE(ZSLTTOCSV)

             DCL        VAR(&ERRORSW)  TYPE(*LGL)
             DCL        VAR(&MSGID)    TYPE(*CHAR) LEN(7)
             DCL        VAR(&MSGDTA)   TYPE(*CHAR) LEN(80)

 /* Global MONMSG for unexpected errors.                             */

             MONMSG     MSGID(CPF0000) EXEC(GOTO CMDLBL(ERROR))

 /* Use the incoming SQL SELECT statement to build a temporary VIEW  */
 /* in QTEMP. This is done by prefixing the SELECT statement with a  */
 /* "create view as" command and feeding the new statement to        */
 /* ZEXECSQL. This creates a logical file in QTEMP.                  */

             DLTF       FILE(QTEMP/&VIEWNAME)
             MONMSG     MSGID(CPF0000)
             ZEXECSQL   SQLSTMT('create view qtemp/' *CAT &VIEWNAME +
                          *BCAT 'as' *BCAT &SQLSTMT)

 /* Copy the view to the CSV file                                    */

             ZCPYTOCSV  FROMFILE(QTEMP/&VIEWNAME) TOFILE(&TOFILE) +
                          DATAOPT(&DATAOPT) COLHDG(&COLHDG)

 /* Get rid of the temporary VIEW                                   */

             DLTF       FILE(QTEMP/&VIEWNAME)

             RETURN

 /* Standard error processing                                       */

 ERROR:      IF         COND(&ERRORSW)      +
             THEN(SNDPGMMSG MSGID(CPF9999)  +
                            MSGF(QCPFMSG)   +
                            MSGTYPE(*ESCAPE))

             CHGVAR     &ERRORSW '1'

 ERROR1:     RCVMSG     MSGTYPE(*DIAG)  +
                        MSGDTA(&MSGDTA) +
                        MSGID(&MSGID)
             IF         COND(&MSGID *EQ '       ') +
             THEN(GOTO CMDLBL(ESCAPE))

             SNDPGMMSG  MSGID(&MSGID)   +
                        MSGF(QCPFMSG)   +
                        MSGDTA(&MSGDTA) +
                        MSGTYPE(*DIAG)
             GOTO       CMDLBL(ERROR1)

 ESCAPE:     RCVMSG     MSGTYPE(*EXCP)  +
                        MSGDTA(&MSGDTA) +
                        MSGID(&MSGID)
             SNDPGMMSG  MSGID(&MSGID)   +
                        MSGF(QCPFMSG)   +
                        MSGDTA(&MSGDTA) +
                        MSGTYPE(*ESCAPE)

             ENDPGM 

==================================
MORE INFORMATION ON THIS TOPIC
==================================

The Best Web Links: tips, tutorials and more.

Ask your programming questions--or help out your peers by answering them--in our live discussion forums.

Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.

Dig Deeper on iSeries CL programming

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataCenter

Close