Generate Spreadsheet from an IBM i Table Without the need for SQL

A few years back IBM created the SYSTOOLS.GENERATE_SPREADSHEET SQL function which wraps around the IBM i Access acsbundle.jar Java JAR file to generate Excel spreadsheets from a database file. This function is typically used from RPG applications to create spreadsheets.

Internally the Java functionality uses the Apache POI JAR file to create the actual spreadsheet output to an IFs file. 

But what about CL, RPG's distant cousin. 

I discovered a post from Scott Schollenberger over on TechXchange where he used my QSHONI utility to create a wrapper process to export an IBM i database table to an Excel file without needing RPG. I had seen this technique before but hadn't used it until this weekend. 

Essentially he called the acsbundle.jar file using Java and the appropriate command line parameters and the QSHEXEC command which is my high level wrapper around QShell and PASE calls. 

After mixing the appropriate elements a spreadsheet complete with column heading magically appears in IFS. 

If you have a need to use the GENERATE_SPREADSHEET style functionality from a CL program or command, check out the GENXLSACS command I created. This might save you several hours of programming output to Excel files. 

You can also marry this command with DB2 services or other summary data by first generating an output file (OUTFILE) from an SQL query using a command such as QSHQRYTMP or QSHQRYSRC from my QShell on i utilities. These two commands allow you to run an SQL query with or without soft coded parameter data and output the results to an output file in the QTEMP library or elsewhere.

I Can't Find My IBM i Open Source Packages

If you're like I am you may have struggled a little when starting to use PASE and open source packages for your IBM i application development or operations. 

The open source packages are usually installed in a directory named: /QOpenSys/pkgs/bin

For example the first thing you may have run into when trying to call a git command from a QShell or PASE session might have been: git: command not found.

You development or operations leaders told you to use git, but it's not running for you. And you don't want to look dumb asking why the git command won't run. 

Well just like we have library lists for classic IBM i jobs, PASE jobs and command lines use a directory search path which is the PASE equivalent to the library list. 

If you run the following commands from a QShell, PASE or SSH terminal session you should be all set to start using open source command line apps because the commands create a profile file including a search path for QShell and other PASE apps if you happen to be using bash instead of QShell.

To run these PASE profile creation commands you should be able to run STRQSH from a 5250 command line. 

First make sure your user profile has a home directory. This same value must also be set in the user profile Home Directory setting if you do a WRKUSRPRF or DSPUSRPRF on your user profile.

mkdir /HOME/RICHARD.  

Note: If your home directory gets created or already exists you're in good shape either way.

Then type and run each of the following individual commands.

What they will do:

The rm command first deletes the profile file if it exists. 

Note: If you get an error that file does not exist, that should be OK just continue to the next command.  

The touch command creates an empty profile file with CCSID 819 which is ASCII. This will make sure that QShell write ASCII data to your profile files.

The echo command writes the desired open source search path and appends the existing search path to it using the $PATH directive.

Let's create the profiles now.

WARNING: If you already have bash or QShell profile files you may not want to run these commands as they overwrite those profile files. Or make backups of the files beforehand. If you want to just edit your existing profile files, you might need to edit the file with the EDTF command from 5250 or vim or nano from the PASE command line instead and simply add the path statement. 

Create a QShell profile 

rm ~/.profile 

touch -C 819 ~/.profile 

echo PATH=/QOpenSys/pkgs/bin:$PATH > ~/.profile

Create bash profiles

rm ~/.bashrc

touch -C 819 ~/.bashrc 

echo export PATH=/QOpenSys/pkgs/bin:$PATH > ~/.bashrc

rm ~/.bash_profile

touch -C 819 ~/.bash_profile 

echo export PATH=/QOpenSys/pkgs/bin:$PATH > ~/.bash_profile

 

After you've created these profile files, you should be able to access any of the IBM i open source packages from any of the command line shells, QShell, PASE (CALL QP2TERM) or bash command lines in an SSH terminal client.

 

Simply exit and restart your favorite PASE command line shell, type git (or whatever other command you were trying to access) and it should show work as expected.

If you happen to be using my QShell on i utilities to run PASE commands the path handling is done automatically when you run a command. Check them out at: https://github.com/richardschoen/QshOni 

Creating, Distributing and Restoring IBM i Objects via Save Files

One of the object types I use the most with the IBM i operating system is the save file. 

Save file objects can be used to save and restore IBM i libraries using the SAVLIB/RSTLIB commands. 

You can also save and restore individual objects or groups of objects using the SAVOBJ/RSTOBJ commands. 

And for saving and restoring IFS files for archive or distribution you can use the SAV/RST commands.

The following GitHub page I created shows some PASE and CL commands that just might make your life a little easier when dealing with saving and restoring information using save file objects.  

https://github.com/richardschoen/howtostuff/blob/master/ibmi_savefile_copying.md