GENERATE_SPREADSHEET scalar function

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 passed by casting the column to a non-LOB string data type.

Start of changeIf there is not an existing override for STDOUT, the result output from CLDownload is overridden to the QTEMP/QGENSPREAD file. If there is an error, the file can be examined to understand the error that occurred. If the STDOUT file has been overridden within the job, the output will not be redirected to the QTEMP/QGENSPREAD file.End of change

Start of changeGENERATE_SPREADSHEET cannot be invoked by a profile using multi-factor authentication when its time-based one-time password (TOTP) interval is expired. When used in a batch or scheduled job, the user profile cannot have an authentication method of *TOTP.End of change

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,SPREADSHEET_QUERY_IFS => spreadsheet-query-ifs,OVERWRITE => overwrite,STARTING_SHEET => starting-sheet,STARTING_ROW => starting-row,STARTING_COLUMN => starting-column,KILL_DAEMON => kill-daemon )
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 either replaced or appended to based on the value of the overwrite parameter.
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, or spreadsheet-query-ifs 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 or spreadsheet-query-ifs 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, txt, xls, 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.
Start of changespreadsheet-query-ifsEnd of change
Start of changeA character string that specifies an integrated file system (IFS) file that contains a query to run that identifies the rows to be included in the spreadsheet. The IFS file must be encoded in CCSID 1208 (UTF-8). Objects referenced in the query cannot be in QTEMP and must be fully qualified. The SQL statement cannot end with a semicolon (;).End of change
Start of changeIf this parameter is omitted, library-name and file-name, or spreadsheet-query must be specified.End of change
Start of changeoverwriteEnd of change
Start of changeA character string that specifies what action to take if path-name already exists.
APPEND
The spreadsheet data is appended to the end of the existing file.
This is only allowed when spreadsheet-type is xls or xlsx.
REPLACE
The spreadsheet data replaces the current contents of the file. This is the default.
End of change
Start of changestarting-sheetEnd of change
Start of changeAn integer that indicates the starting sheet for writing the spreadsheet. The default is the first sheet.End of change
Start of changeThis parameter can only be provided when spreadsheet-type is xls, xlsx, or ods.End of change
Start of changestarting-rowEnd of change
Start of changeAn integer that indicates the starting row for writing the spreadsheet. The default is the first row.End of change
Start of changeThis parameter can only be provided when spreadsheet-type is xls, xlsx, or ods.End of change
Start of changestarting-columnEnd of change
Start of changeA character string that indicates the starting column for writing the spreadsheet. Expected values are A through ZZZ. The default is the first column.End of change
Start of changeThis parameter can only be provided when spreadsheet-type is xls, xlsx, or ods.End of change
Start of changekill-daemonEnd of change
Start of changeA character string that indicates whether the daemon threads should be killed at the end of the request.
NO
The daemon threads will remain active. This is the default.
YES
The daemon threads will be killed.
End of change
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');
  • Start of changeCreate a spreadsheet that contains the data from a query saved in an integrated file system file. Start writing the data in the second column of the spreadsheet.
     VALUES SYSTOOLS.GENERATE_SPREADSHEET(
                     PATH_NAME             => '/usr/ifs_query_spreadsheet', 
                     SPREADSHEET_QUERY_IFS => '/usr/order_query.txt',
                     STARTING_COLUMN       => 'B');
    End of change