Generate spreadsheet with SQL in batch job

When I wrote about the Db2 for i, or SQL, scalar function to create a Microsoft Excel spreadsheet in the IFS my example program ran interactively. I recently received a message asking the following: "good stuff even though I started to use it, it has some limitations so far: doesn't work with QTEMP files. Am I right?"

I submitted my example program to batch and found that the expected spreadsheet was not generated. Looking in the job's job log I could see several messages related to files I created in QTEMP not being found. It would appear that the Java called by the GENERATE_SPREADSHEET submits a job to batch with the type BATCHI.

If I should not use files in QTEMP then I need a Standard Output file, STDOUT, in another library. I chose to create it in QGPL, but it could be in any library that is in the users' library list. As STDOUT is a source file I create it with the following:

CRTSRCPF FILE(QGPL/STDOUT) RCDLEN(266) + TEXT('Standard output file')

The modifications I need to make to the previous example program are not complicated. The first part of this program looks like:

01 **free 02 ctl-opt main(Main) option(*srcstmt) dftactgrp(*no) ; 03 dcl-s JobTimestamp timestamp ; 04 dcl-s Statement varchar(100) ; 05 dcl-proc Main ; 06 dcl-s FileName varchar(10) ; 07 dcl-s JobNbr char(6) ; 08 dcl-s MbrName varchar(10) ; 09 dcl-s ReturnCode int(3) ; 10 exec sql SELECT JOB_NUMBER,JOB_ENTERED_SYSTEM_TIME INTO :JobNbr,:JobTimestamp FROM TABLE(QSYS2.ACTIVE_JOB_INFO( JOB_NAME_FILTER => '*', DETAILED_INFO => 'ALL')) ; 11 FileName = 'XLS_' + JobNbr ; 12 MbrName = 'M' + JobNbr ; 13 Statement = 'CRTDUPOBJ OBJ(FILE1) FROMLIB(QTEMP) OBJTYPE(*FILE) + TOLIB(QGPL) NEWOBJ(' + FileName + ') + DATA(*YES)' ; 14 exec sql CALL QSYS2.QCMDEXC(:Statement) ; 15 Statement = 'ADDPFM FILE(STDOUT) MBR(' + MbrName + ')' ; 16 exec sql CALL QSYS2.QCMDEXC(:Statement) ; 17 Statement = 'OVRDBF FILE(STDOUT) + MBR(' + MbrName + ') + TOFILE(*LIBL/STDOUT) + OVRSCOPE(*JOB)' ; 18 exec sql CALL QSYS2.QCMDEXC(:Statement) ; 19 exec sql SET :ReturnCode = SYSTOOLS.GENERATE_SPREADSHEET( PATH_NAME => '/home/MyFolder/temp_data', LIBRARY_NAME => 'QGPL', FILE_NAME => :FileName, SPREADSHEET_TYPE => 'xlsx', COLUMN_HEADINGS => 'LABEL') ; 20 exec sql CALL QSYS2.QCMDEXC('DLTOVR FILE(STDOUT) LVL(*JOB)') ; 21 if (ReturnCode = 1) ; 22 Statement = 'RMVM FILE(STDOUT) MBR(' + MbrName + ')' ; 23 exec sql CALL QSYS2.QCMDEXC(:Statement) ; 24 endif ; 25 LeftOverJob() ; 26 on-exit ; 27 Statement = 'DLTF QGPL/' + FileName ; 28 exec sql CALL QSYS2.QCMDEXC(:Statement) ; 29 end-proc ;

Line 1: You knew that this program was going to be modern free format RPG.

Line 2: I am using a Main procedure, and I have included some of my favorite control options.

Lines 3 and 4: This the definition of "global" variables that are available to all procedures and subprocedures in this program. The first will be used for the timestamp of when this submitted job entered the system. The second is a generic field I am going to use to create command strings in.

Line 5: Start of the Main procedure.

Lines 6 – 9: These variables are defined to be "local" to the main procedure only.

Line 10: This SQL statement uses the ACTIVE_JOB_INFO table function to retrieve the current job's job name number and the time the job entered the system. To get the time I have to return the detailed results from ACTIVE_JOB_INFO using the Detailed Info parameter. The two columns from the results are placed in the Job Number, JobNbr, and Job timestamp, JobTimestamp, RPG variables.

As this batch job cannot use QTEMP I need to create a unique file in QGPL and member in the STDOUT file. I will use the job number for that as that will be unique to this job. You don't have to create the file in QGPL, you can create it in any library you like, except QTEMP.

Line 11: The unique file's name will start with the character 'XLS_' followed by the job number.

Line 12: The unique member's name will start with 'M' followed by the job number.

Line 13 and 14: I use the Create Duplicate Object command, CRTDUPOBJ, to make a copy of the file in QTEMP, FILE1, to a file with the unique file name, from line 11, in QGPL.

Lines 15 and 16: I am adding a new member to the STDOUT file to contain the output from Java program that creates the spreadsheet.

Lines 17 and 18: I use the Over Database File command, OVRDBF, to override any reference the Java program does to STDOUT to the member I created.

Line 19: The GENERATE_SPREADSHEET scalar function statement. Here I am using the Library and File name parameters, rather than a SQL statement, as I want all of the file's contents to be convert to a spreadsheet.

Lines 21 – 24: GENERATE_SPREADSHEET returns a return code of '1' if it completed successfully. I decided that if it had then I don't want to keep the member that contains the log generated by the Java program. Therefore, I remove the member I created from STDOUT. If you want to keep the log for successful conversions then remove this code from your program.

Line 25: I found that on several of the partitions I test upon a job, QJVAEXC, was started but not finished when this completed. If I wanted to I can cancel that job. The logic to do that is in the LeftOverJob subprocedure, which I will describe below. If you would rather leave it active then delete this line and the subprocedure.

Lines 26 - 28: I have an On exit section in the Main procedure that will be executed no matter how the program ends. Within it I am deleting the file I created on line 14 as I no longer need it, and I don't want to clutter QGPL with files that are no longer needed.

The JobLeftOver will cancel the QJVAEXEC job that was started by this job:

30 dcl-proc LeftOverJob ; 31 dcl-s JobName varchar(28) ; 32 dcl-s UserName varchar(10) inz(*user) ; 33 exec sql SELECT JOB_NAME INTO :JobName FROM TABLE(QSYS2.ACTIVE_JOB_INFO( CURRENT_USER_LIST_FILTER => :UserName, DETAILED_INFO => 'ALL')) WHERE JOB_NAME_SHORT = 'QJVAEXEC' AND JOB_ENTERED_SYSTEM_TIME > :JobTimestamp LIMIT 1 ; 34 if (JobName = ' ') ; 35 return ; 36 endif ; 37 Statement = 'ENDJOB JOB(' + JobName + ') OPTION(*IMMED) LOGLMT(0)' ; 38 exec sql CALL QSYS2.QCMDEXC(:Statement) ; 39 end-proc ;

Lines 31 and 32: "Local" variables that are only available in this subprocedure. JobName will contain the full job name of the QJVAEXEC job. UserName is initialized to contain the user profile name of the person who is executing this job.

Line 33: I am using the ACTIVE_JOB_INFO table function to return the job name into the RPG variable JobName. Again I need to have the Detailed information parameter as I want to use the Job Entered System Time column for a comparison to the time this job started. The QJVAEXEC job was started by this job, therefore, I am only looking for any QJVAEXEC jobs that I started after this job started.

Lines 34 – 36: If no job is found, that matches the selection criteria, no job name is returned. As the variable JobName is blank I return from this subprocedure to the procedure that called it.

Lines 37 and 38: I use the End Job command, ENDJOB, to end the QJVAEXEC job immediately.

The program is longer than the one in the previous example I gave for GENERATE_SPREADSHEET, but it is not too complicated to make it hard for someone else who would have to change it to understand.

 

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