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
w...
To continue reading for free, register below or login
To read more you must become a member of Search400.com
hich 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.
Code
==================================
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.
