SQL_QUERYTOCSV
This activity queries a database using an SQL SELECT statement and writes the results in CSV format into the specified file.
This activity does not support the use of SQL parameter markers (usually designated by a question mark) in the SQL statement. If you need the SQL statement to be variable (for example, selection values in a WHERE clause), then you must dynamically construct the SQL statement to include the variable values as constants.
For more information about the SQL database activities, refer to the description of the SQL_CONNECT activity. For a list of supplied working (*) examples using the SQL database activities, refer to:
Example Processing Sequences using the SQL database activities
INPUT Parameters:
SQLHANDLE : Required
This parameter must specify the connection handle value that identifies the SQL connection upon which this activity should operate. The connection handle value is returned by the SQL_CONNECT activity.
SQLQUERY : Required
This parameter must specify the SQL SELECT statement that will execute on the target database for the query. The following are examples of SQL SELECT statements that might be specified in this parameter:
select distinct bchnum from tutordh
select bchnum, ordnum, cusnum, orddat from tutordh
where bchnum = '12345687890'
Since processing sequence variables are restricted to 256 characters, a single value may not be sufficiently large to hold some queries. For this reason, this parameter is defined as a variable list. This means that you can split your long SQL statement into more than one part and provide the parts in this parameter using a variable list. If you do this, then the SQL_QUERY activity will re-assemble them into a single statement before execution.
SQLFILEPATH : Required
This parameter must specify the path and file name of the CSV file to be created or appended by this activity. If the file already exists (and NO is specified or assumed for the SQLAPPEND parameter), then the file will be overwritten by this activity.
SQLAPPEND : Optional
If the specified output file already exists, then you can specify YES for this parameter to cause the activity to append the new results to the end of the existing contents of the file. Otherwise the file is replaced with the new contents. If the specified output file does not already exist, then this parameter is ignored.
SQLCCSID : Optional
This parameter applies only on IBM i servers. It allows you to specify the IBM i CCSID with which the output CSV file is created.
If the parameter is not specified, a default of *DEFAULT is assumed, which instructs the activity to create the output text file using the CCSID for the job in which the activity is executing. (You can also specify the special value of *JOB which has the same effect.)
Otherwise, you should specify the numeric CCSID value required. For example, a value of 1208 means UTF-8. Refer to IBM i documentation for a complete list and description of the available CCSIDs.
NOTE: the assumed or explicit CCSID is applied only when the specified output file does NOT already exist. If the specified output file already exists and is being replaced or appended-to by this activity, then its CCSID will not be changed.
SQLCOLUMNS : Optional
This parameter must specify the number of columns from the expected result set that are to be written to the CSV file, up to a maximum of 25. This, of course, will depend on the SELECT statement you are using.
You may specify fewer columns than in your query, however, if you specify more, then a run-time error will occur, for example
, or
COLSEPARATOR : Optional
You may specify the separator character that is used to separate the column values in each line of the CSV output file. The most common form of CSV uses a comma as the separator, and that is the default value for this parameter if you do not specify it.
If a non-comma separator is specified, it should be 1 character in length and can consist of any character.
COLHEADINGS : Optional
If you wish the first row of the output CSV file to contain comma-separated column headings, you may specify the heading line in this parameter. Note you should specify the entire string, including the separators, as a single value – for example:
Heading1,Heading2,Heading3
If you do not specify a value for this parameter, then no heading row will be written to the CSV file. If you specified YES for the SQLAPPEND parameter, then the COLHEADINGS value is not used.
SQLMAXROWS : Optional
This parameter may specify a maximum number of rows to be returned. This guards against the possibility of SELECT statements that select much more data than was intended.
If not specified, a default value of 100 (one hundred) is used. Remember that the SQL database activities are not intended and not usually suitable for high-throughput, high-volume database operations. If you expect that your query will return a large number of records, then you should possibly consider an alternate implementation, such as using a Transformation Map.
OUTPUT Parameters:
SQLROWS :
Upon successful completion, this parameter will contain the actual number of rows returned by the query and written to the CSV file.