Creating Excel spreadsheets with SQL

One of the more eye-catching additions to Db2 for i in the latest round of Technology Refreshes, IBM i 7.5 TR3 and 7.4 TR9, was a SQL scalar function that would create a Microsoft Excel compatible spreadsheet in the IFS.

For years we have been able to create Common Separated Variable length file, CSV, by copying data to the IFS. This can be opened using Microsoft Excel and then saved as an Excel compatible spreadsheet. This is the first time an easy-to-use method has been provided, as part of native IBM i, to allow us to create an Excel in the IFS from data in the Db2 database.

The new scalar function is called GENERATE_SPREADSHEET and is found in the library SYSTOOLS. It has a number of parameters that need to be passed to it:

• PATH_NAME:  The location and file name, without extension, of where the file will be created
• SPREADSHEET_QUERY:  The SQL statement that selects the data to be sent to the IFS file. Cannot be used if the following two parameters are given
• LIBRARY_NAME and FILE_NAME:  If you want to dump the entire contents of a file to the spreadsheet you can use these two parameters in place of using a SQL statement in the above parameter
• SPREADSHEET_TYPE:  What format do you want your output file to be in:
  • xlsx:  Microsoft Excel format
  • ods:  OpenDocument spreadsheet format
  • csv:  CSV format
  • If not given CSV is the default
• COLUMN_HEADINGS:  What headings do you want the columns in the spreadsheet to have:
  • COLUMN:  Column names are used
  • LABEL:  Column labels are used
  • NONE:  No headings
  • If not given no column labels are added

The SQL statement looks like:

01 VALUES SYSTOOLS.GENERATE_SPREADSHEET(PATH_NAME => '/home/MyFolder/MyFile', 02 SPREADSHEET_QUERY => 'select * from mylib.file', 03 SPREADSHEET_TYPE => 'xlsx', 04 COLUMN_HEADINGS => 'COLUMN')

Line 1: This is the path where the file will be created, or it will overlay an existing file with the same name. Notice that no extension is given for the file.

Line 2: This is the SQL statement that generates the data that will be copied into the spreadsheet. I have no idea why I chose to show this example in lower case. Upper case works equally well.

Line 3: I want an Excel compatible file. This is appended to the path name. In other words, my file will be called: MyFile.xlsx

Line 4: I want the column headings to be the column names from the file.

When executed GENERATE_SPREADSHEET returns a return code. "1" means successful, anything else means it was not.

As I am copying all the records from the file to the spreadsheet I could use this statement instead:

01 VALUES SYSTOOLS.GENERATE_SPREADSHEET(PATH_NAME => '/home/MyFolder/MyFile', 02 LIBRARY_NAME => 'MYLIB', 03 FILE_NAME => 'FILE', 04 SPREADSHEET_TYPE => 'xlsx', 05 COLUMN_HEADINGS => 'LABEL')

Lines 2 and 3: These two lines replace the Spreadsheet query parameter in the previous statement.

Line 5: I have decided I want the columns' labels as their headings, rather than the columns' names.

To create an ODS format file I would just need to change the Spreadsheet type, on line 3:

01 VALUES SYSTOOLS.GENERATE_SPREADSHEET(PATH_NAME => '/home/MyFolder/MyFile', 02 SPREADSHEET_QUERY => 'select * from mylib.file', 03 SPREADSHEET_TYPE => 'ods', 04 COLUMN_HEADINGS => 'COLUMN')

For a CSV I just need to remove the Spreadsheet type line:

01 VALUES SYSTOOLS.GENERATE_SPREADSHEET(PATH_NAME => '/home/MyFolder/MyFile', 02 SPREADSHEET_QUERY => 'select * from mylib.file', 03 COLUMN_HEADINGS => 'COLUMN')

If you run these you will notice that it takes some time for the statement to finish. The spreadsheets are created running a Java program, I will give the statement at the end of this post, and this takes time to complete.

If you execute any of the above statements using the STRSQL command you will be presented with the Java UI. You will have to wait until the message "Press ENTER to end terminal session." is displayed. You can then press Enter to return to the STRSQL interface. This is another reason why ACS's Run SQL Scripts is the better tool.

I can check to see if the files have been created either by using ACS's "Integrated File System" tool, or I can use the IFS Object Statistics table function:

01 SELECT PATH_NAME,OBJECT_TYPE,DATA_SIZE 02 FROM TABLE(QSYS2.IFS_OBJECT_STATISTICS('/home/MyFolder/','NO','*STMF'))

Which returns to me the three spreadsheet files I created:

OBJECT DATA PATH_NAME _TYPE _SIZE -------------------------- ------ ----- /home/MyFolder/MyFile.csv *STMF 96 /home/MyFolder/MyFile.ods *STMF 1570 /home/MyFolder/MyFile.xlsx *STMF 3578

While showing examples in Run SQL Scripts demonstrates how to use this, the useful example is to include it in a program. I decided to use a RPG program:

01 **free 02 dcl-s ReturnCode int(3) ; 03 exec sql SET :ReturnCode = 04 SYSTOOLS.GENERATE_SPREADSHEET( 05 PATH_NAME => '/home/MyFolder/data', 06 SPREADSHEET_QUERY => 07 'SELECT A.NAME,B.DATA + 08 FROM MYLIB.PERSON A + 09 LEFT JOIN MYLIB.DETAILS B + 10 ON A.NAME = B.KEY + 11 WHERE NAME = ''SIMON''', 12 SPREADSHEET_TYPE => 'xlsx', 13 COLUMN_HEADINGS => 'LABEL') ; 14 dsply %char(ReturnCode) ;

Line 1: I only write modern RPG.

Line 2: Definition for an integer variable to contain the return code value.

Line 3: I am using a SQL Set statement to capture the return code from GENERATE_SPREADSHEET into the RPG variable ReturnCode.

Lines 4 13: This is pretty much the same as I showed above. The only difference is the SQL statement, lines 6 - 11. Here I am joining the PERSON, which contains one record per person, to the DETAILS file, which could contain zero or many records for the person. Using a LEFT OUTER JOIN, line 9, I will return one result for each matching record in DETAILS where the person code is 'SIMON'. As SIMON is a string it needs to be surrounded by double apostrophes ( ' ), and an additional apostrophe is needed after SIMON as it is the end of the SQL statement string parameter.

Line 14: I use the Display Operation Code, DSPLY, to display the return code returned from GENERATE_SPREADSHEET.

After compiling the program when I call it the Java UI is displayed while the Java program called by the SQL scalar function is executed creating the spreadsheet. Like I did before I have to wait until I see the "Press ENTER to end terminal session." message to press Enter. The Java environment is exited, and control is returned to the program.

IMHO that is a nasty user experience. When I have encountered this before I found that if I redirect the STDOUT to a file the Java UI is not displayed, and I don't have to press Enter to exit it. I need to make some additions to my program:

01 **free 02 dcl-s ReturnCode int(3) ; 03 exec sql CALL QSYS2.QCMDEXC('DROP TABLE IF EXISTS QTEMP.STDOUT') ; 04 exec sql CALL QSYS2.QCMDEXC('OVRDBF FILE(STDOUT) TOFILE(QTEMP/STDOUT) + OVRSCOPE(*JOB)') ; 05 exec sql SET :ReturnCode = SYSTOOLS.GENERATE_SPREADSHEET( PATH_NAME => '/home/MyFolder/data', SPREADSHEET_QUERY => 'SELECT A.NAME,B.DATA + FROM MYLIB.PERSON A + LEFT JOIN MYLIB.DETAILS B + ON A.NAME = B.KEY + WHERE NAME = ''SIMON''', SPREADSHEET_TYPE => 'xlsx', COLUMN_HEADINGS => 'LABEL') ; 06 exec sql CALL QSYS2.QCMDEXC('DLTOVR FILE(STDOUT) LVL(*JOB)') ; 07 dsply %char(ReturnCode) ;

The additions to the program are:

Line 3: The Standard Output, STDOUT will be directed to a disk file. If one already exists I use the DROP SQL statement to delete it. By using the IF EXISTS if the file does not exist the statement will not error.

Line 4: I use the Override Database File command, OVRDBF, to redirect the STDOUT to a file. I also need to give this an override scope of *JOB.

Line 6: After the spreadsheet has been generated I want to delete the override for STDOUT.

After compiling this when I run it the Java UI is not displayed.

The file created by overriding STDOUT is a source file. The data listing what happened during the running of the Java program is listed within the SRCDTA field. I can display using the following:

01 SELECT SRCDTA FROM QTEMP.STDOUT

Which returns the following:

SRCDTA ------------------------------- The java.version property wa... Transfer request is complete. Transfer statistics: 00:00:06 Rows transferred: 3

Rather than use a string for the SQL statement I can put my statement in a variable and use that in GENERATE_SPREADSHEET. Below is an example of doing that:

01 **free 02 dcl-s ReturnCode int(3) ; 03 dcl-s SQL varchar(250) ; 04 SQL = 'SELECT A.NAME,B.DATA + FROM MYLIB.PERSON A + LEFT JOIN MYLIB.DETAILS B + ON A.NAME = B.KEY + WHERE NAME = ''PATTY''' ; 05 exec sql CALL QSYS2.QCMDEXC('DROP TABLE IF EXISTS QTEMP.STDOUT') ; 06 exec sql CALL QSYS2.QCMDEXC('OVRDBF FILE(STDOUT) TOFILE(QTEMP/STDOUT) + OVRSCOPE(*JOB)') ; 07 exec sql SET :ReturnCode = 08 SYSTOOLS.GENERATE_SPREADSHEET( 09 PATH_NAME => '/home/MYLIB/data', 10 SPREADSHEET_QUERY => :SQL, 11 SPREADSHEET_TYPE => 'xlsx', 12 COLUMN_HEADINGS => 'LABEL') ; 13 exec sql CALL QSYS2.QCMDEXC('DLTOVR FILE(STDOUT) LVL(*JOB)') ; 14 dsply %char(ReturnCode) ;

Below is what is different from the previous program.

Line 3: I have defined a variable to contain the SQL statement I will be creating.

Line 4: This SQL is similar to the last one, except this is for Patty.

After compiling I run the program and I look in the STDOUT file in QTEMP, and its contents are:

SRCDTA ------------------------------- The java.version property wa... Transfer request is complete. Transfer statistics: 00:00:07 Rows transferred: 1

It is as easy as that.

I did manage to find the Java program that is called behind the scenes:

01 JAVA CLASS('/QIBM/ProdData/Access/ACS/Base/acsbundle.jar') 02 PARM('/plugin=cldownload' 03 '/system=localhost' 04 '/sql=select * from mylib/person' 05 '/clientfile=java_out.xlsx' 06 '/colheadings=1' 07 '/usecollabels')

Line 3: localhist is my default IFS folder, /home/MyFolder.

Line 4: The SQL statement to execute.

Line 5: Name of the file to be created.

Line 6: I want column headings in my spreadsheet.

Line 7: use the columns' labels for the headings.

The GENERATE_SPREADSHEET is a lot easier to use than the Java program's parameters.

 

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

 

This article was written for IBM i 7.5 TR3 and 7.4 TR9.

---

Addendum

If you want to run the above in a batch job read this.