Start of change

GENERATE_SPREADSHEET scalar function

Edit online

The GENERATE_SPREADSHEET scalar function generates a file in the integrated file system containing a spreadsheet based on either the results of a query or the complete content of a database file.

This scalar function relies upon the CLDownload feature provided by the IBM i Access Client Solutions (ACS) jar that ships on every IBM i. The ACS jar is delivered via the IBM HTTP SERVER FOR i PTF Group, and is found at this path: /QIBM/proddata/Access/ACS/Base/acsbundle.jar

CLOB, BLOB, DBCLOB, and XML data is not supported by CLDownload. It can be returned by casting the column to a non-LOB string data type.

Authorization: See Note below.

Read syntax diagramSkip visual syntax diagram GENERATE_SPREADSHEET (PATH_NAME => path-name,SPREADSHEET_QUERY => spreadsheet-query,LIBRARY_NAME => library-name,FILE_NAME => file-name,SPREADSHEET_TYPE => spreadsheet-type,COLUMN_HEADINGS => column-headings )
The schema is SYSTOOLS.
path-name
A character string containing the name of the path where the result spreadsheet file is to be written. An existing file is overwritten.
spreadsheet-query
A character string containing a query to run that identifies the rows to be included in the spreadsheet. The query can be up to 4000 characters long. Objects referenced in the query cannot be in QTEMP and must be fully qualified.
If this parameter is omitted, library-name and file-name must be specified.
library-name
The name of the library containing file-name. QTEMP, *LIBL, and *CURLIB are not supported.
file-name
A character string containing the name of the database file that contains the rows and columns of data to include in the spreadsheet. The file must be in SYSBASE.
If spreadsheet-query is specified, values for library-name and file-name are ignored.
spreadsheet-type
A character string containing the type of spreadsheet to generate. The supported values are csv, ods, Start of changetxt, xls,End of change and xlsx. These values must be provided in lower case.
If this parameter is omitted, csv is used.
column-headings
A character string that specifies whether a heading row is included in the spreadsheet result.
COLUMN
The column names are used as the heading.
LABEL
The column labels are used as the heading. If no column label exists, the column name is used.
NONE
No heading row is returned. This is the default.
The result of the function is an integer. If the function is successful, it returns a value of 1. If the function encounters an error, it returns a value of -1.

Note

This function is provided in the SYSTOOLS schema as an example of invoking a Qshell command from within an SQL scalar function. Similar to other Db2® for i provided tools within SYSTOOLS, the SQL source can be extracted and used as a model for building similar helper functions, or to create a customized version within a user-specified schema.

Services provided in SYSTOOLS have authorization requirements that are determined by the interfaces used to implement the service. To understand the authority requirements, extract the SQL for the service and examine the implementation.

Example

  • Create a spreadsheet that contains the data from MYLIB/A.
     VALUES SYSTOOLS.GENERATE_SPREADSHEET(
                             PATH_NAME     => '/usr/fileA_spreadsheet', 
                             FILE_NAME     => 'A',
                             LIBRARY_NAME  => 'MYLIB');
  • Create a spreadsheet that contains the data from a query. Return column names in the first row of the spreadsheet.
     VALUES SYSTOOLS.GENERATE_SPREADSHEET(
                             PATH_NAME         => '/usr/query_spreadsheet', 
                             SPREADSHEET_QUERY => 'select order_number, customer, balance from orders where shipped = ''YES''',
                             COLUMN_HEADINGS   => 'COLUMN');
End of change