Improvements to GENERATE_SPREADSHEET

The Fall 2025 Technical Refreshes brought some significant improvements to the GENERATE_SPREADSHEET SQL scalar function. These being:

• Execute SQL statement within an IFS files
• Append results from another SQL statement to an existing spreadsheet
• Create a new worksheet, and start the output at a specific row and column
• End the Java program that is started to generate the spreadsheet
• No longer have to use STDOUT to capture information from GENERATE_SPREADSHEET

I am not going to repeat what I have said in previous posts. But, I am going to give an example, below of generating a spreadsheet as a reminder:

01 VALUES SYSTOOLS.GENERATE_SPREADSHEET( 02 PATH_NAME => '/home/MyDirectory/test_1', 03 SPREADSHEET_TYPE => 'csv', 04 SPREADSHEET_QUERY => 'select pid,lname,fname from mylib.person limit 1') ;

Line 1: As I am using ACS's Run SQL Scripts, RSS, I can use VALUES to execute any scalar function, including GENERATE_SPREADSHEET.

Line 2: This is the location and the name of the file I will be sending the output to. Notice that there is not a file extension.

Line 3: I want to create a CSV, therefore, the spreadsheet type is 'csv'. This extension is appended to the path name.

Line 4: In this parameter I can give an SQL statement, whose results is sent to the output file.

When I execute this statement it returns a return code:

00001 ------ 1

'1' informs me that the statement completed successfully. '-1' is returned when the statement errors.

I can use the IFS_OBJECT_STATISTICS table function to check if my file was created:

01 SELECT PATH_NAME 02 FROM TABLE (QSYS2.IFS_OBJECT_STATISTICS('/home/MyDirectory/')) ;

This returns a list of the objects in my directory:

OBJECT PATH_NAME _TYPE ----------------------------- ------ /home/MyDirectory/ *DIR /home/MyDirectory/test_1.csv *STMF

The output file is shown in the results. I can display its contents with IFS_READ table function:

01 SELECT * FROM TABLE(QSYS2.IFS_READ('/home/MyDirectory/test_1.csv')) ;

Which returns:

LINE_NUMBER LINE ----------- --------------------- 1 1,"ALLEN","REG"

Having shown this brief reminder I can now show the new features.

Execute SQL statement with in an IFS files

GENERATE_SPREADSHEET can execute a SQL statement that is contained with an IFS file with this new parameter:

• SPREADSHEET_QUERY_IFS:  Path and name of an IFS file.

I created a Text file, sql_statement_1.txt, that contained a SQL statement. I can use IFS_READ to display its contents:

01 SELECT * FROM TABLE(QSYS2.IFS_READ('/home/MyDirectory/sql_statement_1.txt')) ;

This statement displays the contents of the Text file:

LINE_NUMBER LINE ----------- ------------------------------------------ 1 SELECT PID,LNAME,FNAME FROM RPGPGM1.PERSON 2 WHERE PID = 21

I can now use this in the GENERATE_SPREADSHEET:

01 VALUES SYSTOOLS.GENERATE_SPREADSHEET( 02 PATH_NAME => '/home/MyDirectory/test_2', 03 SPREADSHEET_TYPE => 'csv', 04 SPREADSHEET_QUERY_IFS => '/home/MyDirectory/sql_statement_1.txt') ;

Line 4: SPREADSHEET_QUERY_IFS is the parameter where I give the IFS path for the file that contains the SQL statement.

This statement was successful returning '1'. I can show the output files contents using the following:

05 SELECT * FROM TABLE(QSYS2.IFS_READ('/home/MyDirectory/test_2.csv')) ;

Which shows:

LINE_NUMBER LINE ----------- ------------------------------------------ 1 21,"MCSHANE","HAROLD"

Append results from another SQL statement to an existing spreadsheet

• OVERWRITE:  Append the results from a second statement to the end of those from a first. If not used, the default is to replace an existing file.

This parameter is only for use with XLS and XLSX files. For example:

01 VALUES SYSTOOLS.GENERATE_SPREADSHEET( 02 PATH_NAME => '/home/MyDirectory/test_3', 03 SPREADSHEET_TYPE => 'xls', 04 SPREADSHEET_QUERY => 'select pid,lname,fname from mylib.person limit 1') ; 05 VALUES SYSTOOLS.GENERATE_SPREADSHEET( 06 PATH_NAME => '/home/MyDirectory/test_3', 07 SPREADSHEET_TYPE => 'xls', 08 SPREADSHEET_QUERY => 'select pid,lname,fname from mylib.person where pid = 21', 09 OVERWRITE => 'APPEND') ;

Line 9: 'APPEND' must be in uppercase, otherwise the statement will error.

I could not get this to work. Every time I ran the second statement the results from the first was replaced.

If this parameter is not given the default is replace, 'REPLACE'.

Create a new worksheet, and start the output at a specific row and column

I can now create more than one worksheet in a XLS or XLSX file, by using these new parameters:

• STARTING_SHEET:  This integer value is used to create an new worksheet. If a value is not given, the default is the first worksheet.
• STARTING_COLUMN:  The column within the XLS or XLSX where the data is started, valid values are A to ZZZ. If not given, the default is the first column, A.
• STARTING_ROW:  The row within where the data is started. If not given, the default is the first row.

For example:

01 VALUES SYSTOOLS.GENERATE_SPREADSHEET( 02 PATH_NAME => '/home/MyDirectory/test_4', 03 SPREADSHEET_TYPE => 'xls', 04 SPREADSHEET_QUERY => 'select pid,lname,fname from mylib.person limit 1', 05 STARTING_SHEET => 2, 06 STARTING_COLUMN => 'B', 07 STARTING_ROW => 2) ;

Line 5: I want to create new sheet, called 2.

Line 6: I want the results to start in column B.

Line 7: The results will start in the second column.

The XLS created has two worksheets, '1' and '2', and the image below does show that the results start in cell B2.

What I did find is that I can add the results from a second statement to the end of the results from a first using the OVERWRITE parameter:

01 VALUES SYSTOOLS.GENERATE_SPREADSHEET( 02 PATH_NAME => '/home/MyDiretory/test_4', 03 SPREADSHEET_TYPE => 'xls', 04 SPREADSHEET_QUERY => 'select pid,lname,fname from mylib.person limit 1') ; 05 VALUES SYSTOOLS.GENERATE_SPREADSHEET( 06 PATH_NAME => '/home/MyDirectory/test_4', 07 SPREADSHEET_TYPE => 'xls', 08 SPREADSHEET_QUERY => 'select pid,lname,fname from mylib.person where pid=21', 09 OVERWRITE => 'APPEND', 10 STARTING_ROW => 2) ;

Line 9: I want to append the result from this statement to the same XLS I created in the first.

Line 10: The result from this statement will start on the second row.

Is this how the Append works? If so, it does seem somewhat difficult to use. I would need to know how many results are written to the XLS by the first statement, and use that in the second.

End the Java program that is started to generate the spreadsheet

GENERATE_SPREADSHEET calls a Java program to transform the results and create a file in the IFS. When the statement has completed the program remain active in the background, acting as a daemon. To end this daemon a new parameter has been added:

• KILL_DAEMON:  End daemon (background program). If not given, the default is No.

If I am not going to run several GENERATE_SPREADSHEET statements in the same job, in a short period of time, I will be using this option to not have it running, and using resources, when I do not need it. I would kill the daemon like this:

01 VALUES SYSTOOLS.GENERATE_SPREADSHEET( 02 PATH_NAME => '/home/MyDirectory/test_1', 03 SPREADSHEET_TYPE => 'csv', 04 SPREADSHEET_QUERY => 'select pid,lname,fname from mylib.person limit 1', 05 KILL_DAEMON => 'yes') ;

Line 7: The parameter can be entered in either case.

No longer have to use STDOUT to capture information from GENERATE_SPREADSHEET

In the past if I used GENERATE_SPREADSHEET is a program I had to handle the STDOUT it generated. I know longer have to do this as the output will be directed to a new file, QGENSPREAD, which will be created in QTEMP. If GENERATE_SPREADSHEET is executed more than once the file is always replaced by the STDOUT from the later statement.

Here is a simple RPG program that uses GENERATE_SPREADSHEET, without STDOUT.

01 dcl-s IFSpath varchar(100) inz('/home/MyDirectory/test_5') ; 02 dcl-s ReturnCode int(3) ; 03 dcl-s SQL varchar(150) ; 04 SQL = 'select pid,lname,fname from person limit 1' ; 05 exec sql SET :ReturnCode = 06 SYSTOOLS.GENERATE_SPREADSHEET( 07 PATH_NAME => :IFSpath, 08 SPREADSHEET_TYPE => 'xlsx', 09 SPREADSHEET_QUERY => :SQL, 10 KILL_DAEMON => 'YES') ; 11 dsply ('Return Code = ' + %char(ReturnCode)) ;

Lines 1 – 3: Defining the variables I will be using in this program.

Line 4: This is the SQL statement that will be executed.

Lines 5 – 10: The GENERATE_SPREADSHEET statement, with two of the new parameters on lines 9 and 10. The return code from the statement is placed in the variable ReturnCode.

Line 11: The return code it displayed.

After compiling the program, I call it, and the following is displayed:

DSPLY Return Code = 1

When I look in the QTEMP/QGENSPREAD I see the following:

Transfer request is complete. Transfer statistics: 00:00:04 Rows transferred: 1

What do I see if there is an error? I change line 4 to contain the name of a file I know does not exist:

04 SQL = 'select pid,lname,fname from no_file limit 1' ;

I compile the program, and call it again. The following return code is displayed:

DSPLY Return Code = -1

Which indicates that GENERATE_SPREADSHEET encountered an error. I can see what the error is by looking in QTEMP/QGENSPREAD:

MSGDB0036 - Server returned SQL error ([SQL0204] NO_FILE in *LIBL type *FILE not found.)

IMHO these are all good additions. My favorites is no longer needing to handle the STDOUT, and I wish that the Append would really append the results of one to the end of those from an earlier one.

 

You can learn more about the GENERATE_SPREADSHEET SQL scalar function from the IBM website here.

 

This article was written for IBM i 7.6 TR1 and 7.5 TR7.