One of the powerful features of SEQUEL is its ability to extract data from the System i and create PC-compatible output files in a wide array of formats. You can save SEQUEL output files (to your PC or network drives) or e-mail them automatically (using the version of Help/Systems’ ESEND e-mail file and report distribution software that comes with SEQUEL.). This article focuses on saving results in Excel (XLS) format from a Web browser.

Let’s look at an example from the SEQUELEX library shipped with SEQUEL. The DDREGON view creates results that can be displayed in ViewPoint or the Web interface.

Remote users need Excel format
SEQUEL users often ask, “Can remote users running SEQUEL Web Interface save their results in Excel format?”

One way to accomplish this is to display the results in the browser with the SEQUEL Web Interface, then use the File > Save As option to save the HTML file to a local drive. When Excel opens the file, it converts HTML to its own format. The problem with this method is that you must review the file to remove unwanted headings, footers, and other formatting from the spreadsheet. In addition, you may need to address character/numeric data conversion issues.

SEQUEL scripting provides a better way
SEQUEL scripting lets you save multiple commands within a script object. Scripting gives you much of the capability of CL programming without requiring technical programming knowledge.

To continue with our example, let’s create a SEQUEL script that allows remote users to request results in native Excel format within a browser window. Enter the following commands in the SEQUEL script editor, replacing directory/file references with your own:

10 MONMSG

20 RMVLNK OBJLNK(’/tmp/ddxls*.xls’)

Where MONMSG is a standard i5/OS command that monitors system escape messages and RMVLINK removes previous output files created with this script. Continue with:

30 EXECUTE VIEW(SEQUELEX/DDREGON) PCFMT(*XLS) TOSTMF(’/tmp/ddxls&&jobnbr.xls’) REPLACE(*YES)

The SEQUEL EXECUTE command creates output files from the System i database. In this example, the DDREGON view extracts the data and saves the result in XLS format. The TOSTMF parameter puts the file in an IFS location.

Note: This script is designed to be flexible so that multiple users can run it at the same time. The flexibility comes from starting the file name with the ddxls prefix and indicating a variable value with double ampersands (&&). In this case, the variable is the system job number. Click on the Variables tab within the Script editor to define the variable.

Next, enter the following command:

40 SWIOPEN NEW(*YES) TITLE(’Customer Summary’) ROWSET((100)) COLSET((1 100 ‘http://servername/tmp/ddxls&&jobnbr.xls’))

The SEQUEL Web Interface command SWIOPEN opens a new browser window to display a URL or file (be sure “Allow pop-ups” is selected when you run this example). In this case, the full http: address of the Excel file placed in the IFS in the previous step is specified.

Finally, enter the following command:

50 SWIOPEN NEW(*NO) TITLE(’SEQUEL Web Interface’) ROWSET((100)) COLSET((1 100 ‘http://servername/SEQUEL’))

This second SWIOPEN command returns the original browser window to the SEQUEL Web Interface prompt page.

This script displays results in a browser in native Excel format, and you can save the file to your local PC or a network drive.

Can Easy View help me find null values in my files?
Yes. When you are using the Scan feature in Easy View, the comparison value *NL finds null values in fields within your files. You can even use Extended Scan to replace the null values.

Can I use Robot/CLIENT to synchronize the time on different Windows servers?
Yes, if you have Robot/CLIENT on each server and Robot/SCHEDULE on your System i. Set up a command-type job in Robot/SCHEDULE that uses the RCLEXEC command. The task to execute is the Windows command to change the time. Schedule this task to run in Robot/SCHEDULE on the clients at the same time.

I recently upgraded to Robot/SAVE 11.0. I typically use the Restricted State Utility when I run a type 9 backup (backing up all objects). Now that I’ve implemented encryption, is there anything special I need to do when I sign on to the console and use the RBSRSURDY command?
Yes, Robot/SAVE needs to have the RBTROLLIB library added to its library list to handle encryption. We recommend attaching the job description RBSPGMLIB/RBSJOBD to the profile you use to run the RBSRSURDY command or you can run the following commands manually to make sure Robot/SAVE has access to the libraries it needs:

ADDLIBLE RBTROLLIB
ADDLIBLE RBTSDTALIB
ADDLIBLE RBSPGMLIB
ADDLIBLE QGPL

If there are directories you don’t need to back up, save time and omit them

Is the Integrated File System (IFS) on your System i growing? For many companies, it is. Therefore, backing up the entire IFS can take a lot of time—and it may not be necessary. By omitting directories or objects that do not need to be backed up regularly, you can greatly reduce your backup time.

Prerequisites
To omit IFS directories, you need Robot/SAVE 11.04 and Robot/CORRAL 2.13.

Create an object list of items to omit
First, create a Robot/CORRAL object list containing the IFS objects to be omitted from your backup.

1. Display the Work with Object Lists panel and press function key 6 to add an object list.

2. When the Object List panel displays, enter a name, description, retention type, and choose selection type D (directory). When you press Enter, the Directory Selection criteria displays. Tab to the Directory path field and press function key 4 to select an IFS directory that you want to omit. You can also use file name filters (including generics) to filter objects in the specified directory. If you use a file name filter, Robot/CORRAL will not select subdirectories within the specified directory, which might require you to create multiple object lists. You can use up to 290 omit object lists for each Robot/SAVE backup set.

Tell the backup set to use the lists you created
Next, set up the Robot/SAVE backup set to omit the Robot/CORRAL object list(s).

1. Create a backup set type 9 (Save Entire System) or type 1 (User Defined) to save the IFS.

2. When you get to the Items to Be Saved within a Backup Set panel, press function key 6.

3. When the Item Type Selection panel displays, select IFS Object Lists to Omit.

When the backup runs, Robot/SAVE automatically builds the RBSSAVIFS command with the specified omit-object parameters.

Considerations
* When you set up the Robot/CORRAL omit-object lists, skip the Select Using File Dates section and the Include subdirectories field. If you enter values these fields, Robot/SAVE does not use them (an IBM limitation).

* Robot/SAVE can omit a maximum of 290 directory-type object lists from a backup set or 25,600 formatted bytes in the omit parameter.

* If your directory names or file name filters contain single or double quotation marks, they become case-sensitive and you may need multiple object lists to achieve the desired results.

* If you are familiar with creating object lists of items to save, you may be in the habit of pre-building such lists to save time during your backups. Because Robot/SAVE creates the list of objects to omit on the fly when it executes the RBSSAVIFS command, there is no need to pre-build the list. The best way to test that you are omitting the files you want to omit is to run a save and review the results.

* If you do not specify a file name filter (recommended whenever possible) when you select directories to omit, the resulting Robot/CORRAL object list will omit all subdirectories below the selected directory. For example, if you omit the /notes directory, Robot/CORRAL will omit all objects in /notes including all objects in subdirectories such as /notes/server1/*.

* If you specify a file name filter, only objects in the chosen directory that match the file name pattern will be omitted from the backup. For example, if you specify that files that match the pattern /notes/*.n?f should be omitted, only objects with .NSF and .NTF extensions in the /notes/ directory will be omitted. Because subdirectories do not match this pattern, they will not be included in the omit parameter. You must create another object list to omit them.

Go to Downloads > Help/FACTS to download complete instructions.

Contributed by Jenny Dischinger, Technical Consultant