System i users and experts will meet June 17 - 20

This summer, Help/Systems, the world’s leader in System i Software Solutions, will host Camp Help/Systems, an opportunity for System i users to learn more about automated operations and business intelligence. Scheduled for June 17-20, 2008, at the Oak Ridge Hotel and Conference Center in Chaska, Minnesota, Camp Help/Systems offers users the most current System i information in a friendly and fun environment.

Camp Help/Systems brings together a unique mix of technical professionals and popular speakers. Users who attend can choose from a variety of sessions on a wide range of topics, including the latest in System i news from IBM experts, techniques for using Help/Systems products, and new ways to integrate their professional and personal lives. Among those presenting are Dr. Frank Soltis, the creator of the System i architecture; Richard Dolewski, an expert in disaster recovery; and Kent Milligan, a specialist in System i database topics. Users also can enjoy the antics of Stevie Ray, a well-known improv comedian and corporate trainer.

In addition, users have the opportunity to meet with other attendees and learn from their experiences.

To augment the Camp Help/Systems experience, users can take two full days of pre-camp workshops on June 16 and 17 that will provide in-depth training on Robot/SCHEDULE, Robot/CONSOLE, and SEQUEL.

Camp Help/Systems reflects Help/Systems’ ongoing commitment to the education of the System i community. For more information, contact Katie Carnicom at 1-800-328-1000. Or, visit www.helpsystems.com/education/camp/ to download a brochure or register online.

Do you work with complex data requests in SEQUEL? You do if any of these situations sound familiar:

* The records you want to select are dependent on values in another record

* You need to compare records in a file to other records in the same file

* You build work files and query them to accomplish the data request

What you might not realize is that you can use subqueries help with these complex requests. Using a subquery, you can nest a SELECT statement to make additional comparisons in the WHERE or HAVING clauses. A subquery can return one value, or a list of values, for comparison.

Let’s look at an example using some data from the SEQUELEX/ORDHEAD file. This file contains one record per order. Figure 1 (below) shows a small subset of the data.

Subqueries that return one value
You can use a subquery to return a single grouped (summarized) result that you can compare to data values in a primary record.

For instance, you can find all the orders (ORDNO) whose Retail Value (ORVAL) is greater than the average for the entire Order Header (ORDHEAD) file as easily as this:

SELECT cusno, ordno, orval

FROM sequelex/ordhead

WHERE orval > (SELECT AVG (orval) FROM sequelex/ordhead)

ORDER BY cusno Asc, ordno Asc

After SEQUEL calculates the average retail value for the records in the file, it compares each retail value against the average and returns a row only if its retail value is higher than the average.

Subqueries that return a list of values
Sometimes you want your query to compare a value against a list of values. You may have used the IN comparison to test a field or an expression result against a list of values.

But, what if the situation is a bit more complicated? Suppose you have a list of customers and you want to see which ones have backorders. Not only do you want to see the backorder (an OTYPE of B), but also if the same customer made other purchases that are not back-ordered. The following statement won’t be enough.

WHERE OTYPE=”B”

This statement returns only orders that have a backorder, as shown in Figure 2.

We actually want to see all the order records, including the order types of R and O, for customers with backorders.

You can use the following query to select all orders for customers who also have a B order-type record.

SELECT cusno, ordno, orval

FROM sequelex/ordhead

WHERE cusno IN (SELECT cusno FROM sequelex/ordhead WHERE otype=”B”)

It’s easy to build subqueries
ViewPoint makes it easy to build a subquery. The WHERE clause Expression Editor provides a list of subquery comparisons under the WHERE/HAVING section of the Function list.

Contributed by Sheryl Quinlan, Technical Consultant

Is there a report that shows my Robot/SCHEDULE security setup?
Yes, if you are running Robot/SCHEDULE 9.25 (or higher). Here’s how:

* Make sure the ROBOTLIB library is in your library list

* Go to a command line and enter CALL RBT400

This command generates a report listing Robot/SCHEDULE’s current security setup.

How can I tell which libraries and objects Robot/SAVE is encrypting?
It’s easy:

1. Go to the Robot/SAVE Main Menu and select option 4 (Reports Menu).

2. When the Reports Menu displays, select option 1 (System Reports).

3. Print the report titled “Library and IFS Objects Selected for Encryption Report.”

Robot/SAVE generates a spooled file (RBS441P) listing all libraries and objects that are either set up for encryption globally, or are encrypted only for a specific backup set.

Can Robot/SPACE generate a list of duplicate objects on my system?
Yes. Create a Storage Audit for all libraries and objects. Add the task “List Duplicate Library Objects.” Make sure you select “Print Results of this task” to create a spooled file with the results.

How can I tell SEQUEL to calculate the last day of the current month?
Use this statement:

CVTDATE(YEAR(Current Date),MONTH(Current Date),01)+1 month - 1 day

Updated! Are Help/Systems products compatible with i5/OS V6R1?
Help/Systems is committed to making its products compatible with the new release of i5/OS. We began shipping V6R1-compatible code for most products at the end of April 2008. For more information, download the new Help/FACTS, “Compatibility information for Help/Systems products moving to i5/OS V6R1 (IBM i 6.1)” or contact technical support.

Robot/SAVE’s Guided System Restoration process is now even easier to use. If you are using Robot/SAVE 11.15 (or higher), you have access to several improvements:

• Volume ID and Sequence number now display the Guided System Restoration Procedures panel. (Note: You set restoration sequence values on the Library Save Information panel. This allows you to identify which libraries are the most important to get you up and running. Then, Robot/SAVE can automatically restore your core libraries first.)
• Select Sort (function key 9) now allows you to sort by Volume ID.
• Print Report (function key 8 ) is a new option that prints an audit report showing the libraries selected and not selected for restoration and the volume(s) where they reside. You can use the report in advance of a disaster recovery (DR) test to verify the libraries that will be restored. You also can use this report to satisfy your auditors, to practice and fine tune your selection criteria, or to keep track of what is selected in a real Guided System Restoration.
• View (function key 11) is another new option that shows the Volume IDs required to restore each of the libraries in the list. The Volume ID in the Volume column is the volume where the library begins. By pressing function key 11, you can see if the library spans more than one volume.
• Restore Existing (function key 22) selects only the items that were on your system at the time of the last backup for restoration. If you press function key 22 and return to the Guided System Restoration Procedures panel, you can locate libraries with high sequence numbers that should not be restored at the DR site, such as test libraries or other non-essential items. This can save you precious time in the restore process.
• Message clarification. When you back of out the Restore All (function key 21) or the Restore Existing (function key 22) options, the message now reads, “Restore cancelled. Select/deselect items and press Enter to begin.”

The net effect of these changes is that it is easier to modify what will be restored.

Contributed by Theresa Aleckson, Technical Consultant

SEQUEL views can be regrouped or drilled into to provide new insights

SEQUEL ViewPoint allows you to view the data in your databases in different ways. When you create a view by using a function that groups (summarizes) data, you automatically create views that can be “drilled into” to see the supporting detail. Views that include functions, such as SUM(), MIN(), MAX, COUNT(), or AVG() let you drill into the displayed results.

Note: This feature requires fields to be added to the Dynamic Drill Down Field list. This list is stored in a file (SEQUEL/VPTDYNFLTR) that you can maintain easily using ViewPoint Administrator, or a green screen command line with UPDATE, INSERT, or DFU.

Drilling down from the summary level to the detail is easy. When you right-click on a result, you can select from two options: Regroup or Show details.

Regroup
The Regroup option lets you sort the entire result set and re-summarize it by one of the many fields. You can run this process repeatedly without any changes to the view.

The Regroup Data window lists all fields from all files used in the view that are also defined in the Dynamic Drill Down Field List. If no fields from the starting view exist in the Dynamic Drill Down List, a dialog displays allowing you to list all fields for the view. Just place a check next to all the fields for which you want to regroup the data.

Right-click on BMXCARTON and choose to regroup the data.

If you choose to regroup using the state field (left) and press the OK button, a second window opens displaying the new results (right).

Show Detail
This option shows the supporting detail for the selected grouped record, allowing you to drill-into the data that created the summary information. Right-click on a specific line, select “show details,” and select the data you want to see from the detail of that line.

Again, you can run this process repeatedly without any changes to the view.

In this example, right-click on Illinois and choose “Show details” and name to display customer detail.

SEQUEL filters the original data set to display customers in Illinois.

Contributed by Carlo Panzarella, Technical Consultant

Are Help/Systems products compatible with i5/OS V6R1?
Help/Systems is committed to making our products compatible with the new release of i5/OS. We began shipping V6R1-compatible code for most products at the end of April 2008. For more information, download the new Help/FACTS, “Compatibility information for Help/Systems products moving to i5/OS V6R1 (IBM i 6.1)” or contact technical support.

Does Help/Systems hold a User Conference?
Yes, Help/Systems is hosting a User Conference June 17-20, 2008 in Minnesota. If you haven’t received a brochure in the mail, visit www.helpsystems.com/education/camp/ to download a copy and register online.

Are hyperlinks supported in my ESEND e-mail messages?
Yes, if the message type is *HTML. Here are some formatting tips:

• E-mail links: Use mailto:myname@mydomain.com or just myname@mydomain.com.
• Web site links: Use http://www.helpsystems.com or just www.helpsystems.com.
• Document links: Enter the complete HTML reference <A href=”http://www.helpsystems.com/intel/video.html”>a SEQUEL movie page</A>. This will display as a SEQUEL movie page in your e-mail.

I am trying to move a Microsoft Access project to SEQUEL ViewPoint and I am stuck on one formula. In Access, I use this formula to add a trailing 00001 to a trimmed field:

Left([eqmfsn],4) & “00001″

How would I do this in Viewpoint?

Here’s a good way to handle this in Viewpoint:

SELECT DECIMAL(CAT(SST(CHAR(cusno),1,6), CHAR(’00001′)),15,2) name(calc1), cusno*100000+1 LEN(15,2) name(calc2)

FROM sequelex/custmast

Just replace the name variables with your own file names.

Robot/SPACE 3.0 includes important new enhancements that will help you manage your disk space even better than before.

Active Job Storage Monitoring
Robot/SPACE monitors the amount of storage used by active jobs. A looping job or a server communication job can consume a large amount of temporary storage and push your disk storage to its capacity, causing a storage crisis. In addition to the job temporary storage monitor that evaluates temporary storage allocated by jobs, Robot/SPACE 3.0 offers two new monitors: Job QTEMP Monitor and Job Spooled File Monitor.

• The Job QTEMP Monitor evaluates the storage allocated by the QTEMP library for active jobs on your system. It notifies you when active jobs are using excessive storage for their QTEMP library. QTEMP libraries are reclaimed automatically by the system when the owning job ends.
• The Job Spooled File Monitor evaluates the storage allocated for spooled files by active jobs on your system and can notify you when active jobs are using excessive storage for spooled files.

In Robot/SPACE 3.0, job monitors now can track detailed information for every threshold exception, in addition to summary information.

For any job monitor, you can define a default job threshold to notify you of problems. Notification can be through a Robot/ALERT message (to an e-mail address, cell phone, or other device), a Robot/NETWORK status message, or a message to a message queue monitored by Robot/CONSOLE, allowing Robot/CONSOLE to run OPAL code to correct the situation.

Robot/SPACE 3.0 provides a new Robot/GUIDE to help you set up the Job Storage Monitors.

Storage Audits
Robot/SPACE 3.0 includes two enhancements to storage audits:

• The Storage Audit properties panel now supports specifying *ALLAVL for the Auxiliary Storage Pool (ASP) Group in addition to a specific ASP or *SYSBAS. Select *SYSBAS to run the audit over objects from available ASPs from 1 to 32. Select *ALLAVL to run the audit from all available ASPs from 1 to 255.
• The Storage Audit History window now allows you to display job information such as job sttributes, job spooled files, and the job log.

Storage Collections
Robot/SPACE provides “collection groups” to collect statistics for objects on your system (type, size, growth, last used date, and so on). You can collect statistics for native System i objects, IFS objects, output queues, and for clients connected to your System i with Robot/CLIENT.

Robot/SPACE 3.0 includes a number of enhancements to collection groups:

• A new Advanced Options tab includes options for tracking member statistics (logical and/or source members) and using object size filters. Use the object size filters to exclude library object details or IFS file details of objects under a specified size.
• IFS filters now support ASP group restrictions.
• In addition to comparing library and object growth between collection runs, Robot/SPACE 3.0 now can compare members, output queues, IFS files, and IFS directories. You can display these reports from the Collection Explorer, or schedule them in Robot/SCHEDULE from the Collection Reports menu.
• You can create on-demand reports of collection statistics. From the Collection Explorer, you can create collection history reports in PDF, HTML, or text format. Just right-click a library in Collection Group history, select Report, then select All Records or Selected Records.
• You can view job information for collection jobs from the Collection Explorer. Easily look at the job attributes, job spooled files, and the job log for a collection group.

Queries and Reports
Robot/QUERY allows you to create customized graphs and reports from your collection group history by using specific selection and sort criteria. Results are viewable as growth history graphs, growth trend graphs, and/or reports. In Robot/SPACE 3.0 the following enhancements were made to Robot/QUERY:

• A new command, SPCQRYRPT, allows Robot/QUERY reports to be scheduled. Previously, each query had to be run on demand from the Robot/SPACE Explorer.
• The Reports section has additional collection comparison reports that allow you to generate reports to compare members, output queues, IFS files, and IFS directories.

Other Product Enhancements
Robot/SPACE 3.0 also includes the following enhancements:

• The Import/Export function now allows you to move multiple items at one time.
• Role-based product security allows you to create roles (such as administrators, operators, or users) and define what each role is allowed to do. For example, you can define who is allowed to change the setup of collection groups, storage audits, and so on. You also can define those who cannot change, or even view, those functions.
• The Data Filter Manager has been enhanced so you can create, save, and edit filters. When viewing collection group history, the Data Filter Manager makes it easy to view specific data. You can also export filters to other systems using the Import/Export function.

Contributed by Jenny Dischinger and Jeff Pedretti, Technical Consultants

Everyone deals with reports, but different IT departments have different needs and concerns. Help/Systems offers three products to help you manage your System i reports. This article looks at five common business needs and describes how ESEND, Robot/REPORTS, and Robot/TRANSFORM handle them. You’ll discover that there is no “one size fits all” solution.

1. I need to generate PDFs from i5/OS spooled files and e-mail them to users.
You can use ESEND to generate a PDF from a spooled file. The PDF it creates from a *AFPDS file is a graphical representation of the data and is not searchable. The PDF it creates from a *SCS file, with or without image overlays, is searchable. You can have ESEND e-mail the PDF to an individual, or to a list of recipients.

Use Robot/TRANSFORM to create PDF output from a spooled file or a PCL file. You can create a PDF from *SCS, *USRASCII, or *AFPDS spooled files. It will work with overlays on the *AFPDS spooled files. Robot/TRANSFORM lets you choose whether the PDF it creates is searchable or non-searchable. To e-mail the PDF, you need Robot/ALERT.

You can use Robot/REPORTS to create searchable PDF output from *SCS spooled files, including overlays. To e-mail the PDF, you need Robot/ALERT.

2. I want to split my report so sales reps are getting only the information they need.
You can use ESEND to burst a report based on a “rule set” that you define. You can select from PDF, RTF, HTML, TXT, and spooled file output. If you choose spooled file output, ESEND sends the complete spooled file to the user’s output queue, but prints only the pages that fit the requirements of the rule set. (Note: If the user displays the spooled file, they may see more of the report than you intended.) ESEND can e-mail PDF, RTF, HTML, and TXT files to an individual user, or to a list of users.

Robot/REPORTS provides two ways to split your report. You can define “bursting instructions” to burst reports by specified criteria. Or, you can use OPAL segmenting for reports that are more complex. Robot/REPORTS can output the segment different ways: PDF, HTML, .CSV file, Microfiche, Print, View, or Robot/TRANSFORM. (Note: Robot/TRANSFORM is not a bursting tool.) Regardless of which output form you select, only the segment of information you have selected is available to the recipient.

3. I don’t want to print my reports. I want users to view them online.
ESEND doesn’t have a online viewer, but it does allow users to display spooled files using the WRKSPLFE command, and many ESEND and System i spooled file options. You can specify the spooled files a user can see. You can specify whether a user can access:

• Only their spooled files
• Spooled files on a particular output queue
• Spooled files based on the spooled file name

Robot/REPORTS has an online viewer that allows users to view their reports as soon as Robot/REPORTS finishes processing them. Users can decide if they would like to save a copy of the report in a different format (PDF or HTML), and whether to print the report on demand.

Robot/REPORTS lets users define and save their own views of report, to display certain columns and change the order in which columns appear. While viewing reports online, users can add public or private notes to the report.

Another Robot/REPORTS online viewing option is the Robot Browser Interface. This interface uses Portals for single-point access to multiple types of information and services. Portals let users access information located throughout a network, quickly and easily, from a single location using any device—PC, laptop, or PDA—that can read HyperText Markup Language (HTML).

Portals also reduce the load on an IT department because there is no software to install or update on a user’s computer. A Web server pulls information from the System i and processes requests, so the administrator performs all software maintenance on the Web server. This makes Portal software easy to use, easy to maintain, and especially helpful for people who connect to work over the Web. Because, Portals are browser-based, they are familiar, which translates to less training time and fewer Help Desk calls.

The main reason people have Robot/TRANSFORM is to create PDFs. If you have a Java interface, you can view spooled files through the Robot/TRANSFORM spooled file viewer, but this is not really meant for online viewing of end users.

4. I want to archive my reports and access them later.
You can use ESEND to create PDFs and e-mail them or place them in the IFS.

You can use Robot/TRANSFORM to create PDFs that you store in the IFS or e-mail using Robot/ALERT.

Many people find that the IFS is an adequate report archive. However if you need more, Robot/REPORTS has it. Robot/REPORTS is a robust tool for archiving. You can define an archive (retention) strategy for each report using three different archive stages: online, short-term, and long-term. (Short-term storage can be on disk, tape, or optical volume; long-term storage can be on tape or optical volumes.)

5. I want my PDF to have an overlay or form with it.
Use ESEND to create a PDF with an overlay. It can process *AFPDS and *SCS spooled files with overlays.

Use Robot/TRANSFORM to create a PDF with an overlay from an *AFPDS spooled file.

You can use Robot/REPORTS to create printed output containing an overlay, but it cannot create a PDF with an overlay.

Summary
Each of the three report products we discussed has different capabilities and focus. Take a closer look at the ones that best match your primary business needs.

Contributed by Marie Stangl, Maintenance Software Engineer

Lansing School District in Lansing, Michigan, is made up of over 16,000 students and covers three counties in the mid-Michigan area. They are the largest school district in the tri-county area with 27 elementary schools, four middle schools, and three high schools. They currently have nine magnet schools within their district and many specialty programs for children and adults.

SEQUEL supports the “data to the desktop” initiative
Steve Maiville, a supervisor in the Technology Department, explains the many ways Lansing uses SEQUEL. “Our application software is CIMS, which has traditionally been a 5250 green-screen application. We’ve been using SEQUEL for about seven years now. Ironically, we originally purchased SEQUEL to allow us to format three-up labels. But we’ve gone way beyond the simple report formatting that labels require. Our district has started an initiative we call ‘Data to the Desktop.’ The goal is to make data available to the people who need it at their desktop, often in real-time. This includes people who, in many cases, have never had real-time access to this data before. SEQUEL is the tool that allows us to make this happen quickly and efficiently.

“We’ve even been able to extend data to bus drivers in our transportation department using the SEQUEL Web Interface. They can access information about the students on their bus routes. This includes pictures of the students, parent phone numbers, e-mail addresses, and emergency contact information, as well as information about any special needs that a student may have, such as asthma or allergies to bee stings.”

SEQUEL dashboards aid administrators
Steve has found SEQUEL executive dashboards to be effective for displaying information. He explains, “We use SEQUEL dashboards to enable administrative and support people to see student information on discipline, testing, and attendance. We integrate run time prompting into our dashboards. CIMS stores data for different years in different file members on the same file. Depending on the prompt value, we can have the dashboard automatically assign the appropriate library and members to use when opening the files. We pass the library and member names from one view to the next with the drill downs. By using this practice, we can compare our staffing this year with past years. With SEQUEL, I’ve been able to greatly simplify the data extraction process.

“We also automated the process of distributing the monthly budget reports using SEQUEL. Previously, the staff of the Finance Department would print thousands of pages of monthly reports which they would separate and mail to different schools and departments. It was very costly and time-consuming. Now, we use the RUNCMD command in a SEQUEL script to automatically e-mail monthly formatted budget reports, as PDF attachments, to each of our schools and departments. Each PDF report has data specific to the recipient. I have a simple file with a record for each recipient, and fields for department or school number(s), a salutation, and an e-mail address. This file drives the whole process. It has saved us a lot of time and money, and is very easy to modify.”

SEQUEL helps speed purchasing
In the Lansing district, purchase orders normally have to be approved at multiple levels. Steve describes the process, “Twice a week we run a process using SEQUEL to examine all unapproved purchase orders (POs), determine who is next in line to approve or disapprove each PO, and e-mail that person reminding them that they need to take action so that the approval process can proceed.”

SEQUEL provides information in real time
Steve continues, “With SEQUEL, I can provide teachers with real-time information on their students through a Web browser. They are really excited, using words like ‘empowered’, and coming up with good ideas on how to improve the system. Currently, they can access information about how to contact parents, which allergies a student may have, assessment test scores, grades—whatever they might need.”

SEQUEL assists with data analysis
Lansing School District takes advantage of SEQUEL’s analysis functions in multiple ways. Says Steve, “We are also using SEQUEL to analyze data in the aggregate. Teachers and administrators can look at averages and summaries for test scores, days absent, sick days, grades, and so on. They can drill down any number of ways to see the supporting detail. Currently, our testing and research people are working with me to identify ways to streamline the data collection needs of the district.

“We also use SEQUEL’s date manipulation capabilities extensively. For example, we have blood drives where students, who will reach the age of 17 by the date of the drive, can volunteer to donate. SEQUEL subtracts their date of birth from the eligible date and creates letters to send to the students who qualify. The date and the age they need to be on that date are passed as run time variables. I really like the way I can use date arithmetic in my decision logic.”

SEQUEL helps simplify and improve things
Steve sums up the power and usefulness of SEQUEL. “I am always looking for ways to simplify and improve things. SEQUEL is the perfect tool for the job. I have had the opportunity to call the support staff on many occasions, and they are wonderful to work with. I love SEQUEL—it lets me solve problems creatively!”

Adds menu system, role-based security, enhanced troubleshooting, and reports

Help/Systems, the world’s leader in automated operations and business intelligence software for the IBM System i, announces the release of Robot/REPLAY 3.0, the newest version of its companion software to Robot/SCHEDULE, the automated System i job scheduler. Robot/REPLAY works with Robot/SCHEDULE to automate interactive System i processes that include menu systems and 5250 screen applications that cannot be run in batch mode.

Robot/REPLAY now sports a complete menu system for easy access to Replay objects (macros) and functions. The menu options allow users to learn, preview, schedule, and execute Replay objects; access Replay object information and view execution history; enroll Replay profiles; work with Replay applications; manage security settings, request reports; and maintain Robot/SCHEDULE reserved command variables embedded in Replay objects.

With Version 3.0, Robot/REPLAY offers role-based security to control access to Replay objects and functions. Robot/REPLAY administrators can organize their Robot/REPLAY users according to the Replay objects and functions they are allowed to learn, view, maintain, or execute; control access to Robot/REPLAY functions (such as menus or commands) and data (Replay objects); create security roles, specify their access rights, and group them using Robot/REPLAY applications. Administrators can even prevent unwanted access by users with *ALLOBJ authority to Replay objects that contain sensitive data.

Robot/REPLAY 3.0 provides a number of troubleshooting enhancements. A new visual log allows users to review the sequence of events that occurred during the execution of a Replay object. In addition, Robot/REPLAY bundles critical diagnostics information into a single, easy-to-use package, to save time during problem analysis. And, Robot/REPLAY log messages now provide more information for faster, easier problem resolution.

A suite of new reports allows users to list Replay objects and setup details; print Replay object screen images; review the relationships of the jobs that ran when a Replay object was executed; pinpoint Replay objects that contain Robot/SCHEDULE reserved command variables; print security roles, members, and access rights; and create a Robot/REPLAY security evaluation indicating which Replay objects a user profile can execute.

For more information about Robot/REPLAY 3.0, contact your Help/Systems Regional Sales Manager at 1-800-328-1000 or your local distributor.