Overview of Sequel Scripts
Before we can get into the details of the new Sequel Script Views™, let’s begin with an overview of a Sequel Script. Occasionally the need will arise to create a multiple step job stream in order to complete a complex task. A Sequel Script allows you to include multiple commands in a script definition, thus avoiding the technical requirements of writing and compiling CL programs (CLP).
You have the ability to create work files when multiple passes over your database are required. For example, here is a common use of the Execute command in Sequel as run in a script:
EXECUTE VIEW(SEQUELEX/ORDERS) OUTFILE(QTEMP/WRKFILE1)
In this example, the view ORDERS can be designed to gleam some of the data in the desired format. The work file, WRKFILE1, holds those results, which can be used in subsequent views or commands.
The views used in Scripts can have variables to prompt the user running the request. The commands, like EXECUTE, have a SETVAR parameter to handle the passing of the values. This allows the user to be prompted once, and the values can be passed to multiple commands. This is especially valuable when the views all require the same input, like at month end.
The view being used to create the work file on the host IBM i can be designed to look at supported remote databases: DB2, SQL Server, MySQL or Oracle. Then your remote data can be placed into files that can be joined with existing IBM i files, or the local view and remote view can both populate a new file. Here is another example of the merging of the view results into one file:
EXECUTE VIEW(SEQOBJ/RMTVIEW1) OUTFILE(QTEMP/RMTFILE)
EXECUTE VIEW(SEQOBJ/RMTVIEW2) OUTFILE(QTEMP/RMTFILE) MBROPT(*ADD)
DISPLAY SQL(‘SELECT * FROM QTEMP/RMTFILE’)
In an earlier release of Sequel, the ability to have internal variables, loops and conditional expressions were added. This functionality is still there for Script Views.
What is a Script View?
In a script, if you want your results printed you add a PRINT command. The DISPLAY command will bring the results to the screen. The EXECUTE command can email, create files, etc. A script can be designed in such a way as to prompt the user for their desired course of action, although, the script designer needs a little creativity to make this work. With Script Views one command allows the user to choose, display, print, email, or file creation, etc.
Like a Script, Script Views can return a single result set, issue multiple commands or steps, and contain prompts. A Script View looks and acts more like a Sequel View to the user, but under the covers, a Script View can run almost any multiple step process while providing the Viewpoint user the same abilities as a Sequel View. Users can:
- Display results
- Save results as a PC, IFS, or physical file as well as a remote table
- Send results via email
- Print results via PC or output queue
You can also design Sequel host reports, Client Reports, and Client Tables over a script view.
You can change a setting in the Script Designer to change an existing script into a Script View. Then you simply use the command SCRETURN in place of the DISPLAY or PRINT commands at the end of the script. It is required that SCRETURN be at the end of the Script View. In a Script View, the commands DISPLAY, PRINT, REPORT are not allowed.
So how does a Script View work? The Script View is focused on returning a single result set. The parameters of the SCRETURN are the same as those in the DISPLAY command. Everything else in the design phase is the same, but one script with one specific command at the end gives the user the same options they enjoy when running a view.
Here a Script View runs one view built over IBM i data, one built over SQL Server data and a third over Oracle data. The data is merged into one file that is then returned using the new SCRETURN command.
From Viewpoint, the user gets most of the options that are available for a view: display, print, email, PC file creation, and host or remote file creation, as well as a new Sequel Host Report, new Client Report or New Client Table.
The Impact on Sequel Web Interface
Another benefit of Script View can be seen when it’s run from the Sequel Web Interface (SWI). When a Script is designed to show the results and is run from the browser with SWI, the results are displayed, but the user is only presented the option to print the displayed results. With a Script View, the user is given the full range of options that are given to a view: save results as PC file, print, and email.
Whether you need to merge data from different sources or need to run a complex multi-step process over your data, Script Views provide the power of a script with the flexibility of a view. You can design one from scratch or modify an existing script. Minor changes to your script can provide your users with additional functionalities and abilities.
Give your users the ability to transfer their data into information with data access software that makes it easy.