I have created a connection to a remote database. How do I connect to it and use the tables?
The way to do this is to create a new view in SEQUEL ViewPoint. On the design menu, select File > Properties. Use the database drop-down on the View tab to select the remote database. Then, select files and fields, creating a view as you normally would.

How can I calculate the first and last days of the current month?
For the first day of the month use the following calculation:

(CURRENT DATE - DAY

(CURRENT DATE) DAYS) + 1 DAY

For the last day of the month, use:

CURRENT DATE + 1 MONTH - DAY (CURRENT DATE + 1 MONTH) DAYS

How can I remove non-alpha characters from a string?
This requires using two functions. Use TRANSLATE to convert non-alpha characters to blanks, and use STRIPX to remove the blanks from the string.

Let’s look at building this calculation step by step. For example, if field X has a value of a!b@c#d$e, you can use TRANSLATE to replace the non-alpha characters with blanks:

TRANSLATE(X,” “,”!@#$”)

The result is a b c d e.

By adding STRIPX, you can remove multiple occurrences of a character from a string. In this case, you want STRIPX to remove the blanks created by the TRANSLATE function. The final calculation would be:

STRIPX(TRANSLATE(X,” “,”!@#$”),” “)

The final result is abcde.

Security Mutual Life Insurance Company of New York was founded in 1886. Over the years, they have weathered depressions, wars, and recessions to emerge as one of the leading mutual life insurance companies in the United States. Currently, Security Mutual markets products in all 50 states, the District of Columbia, the U.S. Virgin Islands, and Puerto Rico. From $15,000 in assets its first year in existence, Security Mutual has grown to more than $1.9 billion in assets, with more than $29 billion of life insurance in force.

SEQUEL extracts data easily
David Lunzman, a senior software developer, describes the role SEQUEL plays at Security Mutual. “I started with Security Mutual three and a half years ago and I had used SEQUEL for several years at my previous employer. Naturally, when I came to Security Mutual I recommended we purchase SEQUEL. Now, we use SEQUEL with our Genelco LSP application database to extract data. Extracting data is really easy with SEQUEL. Before SEQUEL we had to use SQL/400, or write RPG programs.

“With SEQUEL, I can pull out the data I need and format it into reports or create Excel files. We often use SEQUEL’s conditional processing, string handling, and date manipulation capabilities when we extract data. Date conversion has also been very helpful. We store our dates as packed fields. SEQUEL lets us convert them to date data types and show the number of days between dates. SEQUEL’s UNIQUE KEY function lets us remove duplicate records.

Drag-and-drop makes everything faster
“Using SEQUEL Viewpoint with its drag-and-drop capabilities makes everything easier and faster. For example, a user called and asked for an ad hoc report on our agents. I delivered it within five minutes. Most of our users access SEQUEL-manipulated data using a browser and the SEQUEL Web Interface. A browser is all the software they need to get the information they need.

Reports accessible through Windows or a browser
“We use SEQUEL to provide reports containing vital operating statistics for upper management. We often build run-time prompts into the reports to allow users to select a date range or a company. And, SEQUEL lets us provide drill-down options to our browser users. For example, management might run a summary report by agent, then pick an agent and drill for further detail. With SEQUEL, we can quickly build a view or report and make it immediately accessible through a browser or Windows. Our users can print a report or create Excel files. They really like the versatility of SEQUEL.

E-mail distribution a plus
“SEQUEL’s e-mail distribution is very useful. We have some reports that we run during our nightly cycle. With SEQUEL, we can automatically e-mail the reports or Excel files as attachments. SEQUEL also lets us e-mail from a browser. SEQUEL prompts for the e-mail address and sends out an Excel file attachment.

Short learning curve another bonus
“Recently, we hired a developer. With SEQUEL’s short learning curve, he learned SEQUEL quickly and thinks it’s fantastic. When I program, I use SEQUEL about half the time. I find that combining SEQUEL and RPG makes it easy to develop applications on the IBM System i. Because SEQUEL is command driven, it is easy to invoke from a CL program. I also like SEQUEL scripting, which we can run from a browser. I can do amazing things with SEQUEL—convert data to Excel, format and e-mail reports, perform drill-down analysis, and so on.”

To sum it up, David concludes, “SEQUEL is great. I have only good things to say about it.”

Enhanced visual network monitoring tool lets you fix problems faster

When you upgrade to Robot/NETWORK 10, you get a great new way to view events from all your systems—the Map Center. The Robot/NETWORK Map Center is a graphical display that you can customize to represent and access your Robot/NETWORK System i servers. Each server in your network is represented by an icon on the main Map Center tab. Each separate Host system is also represented, with its Nodes, on its own tab. The Map Center supports TCP/IP and gives you point-and-click access to network problem resolution.

To start the Map Center, select the Map Center Icon from the Robot/NETWORK Explorer tool bar.

You are notified of statuses in the Map Center while you are connected to a Host. When status notification is on, a status counter on an icon on the Host, the Node, and the tab head indicates the number of statuses waiting. The color of the icon indicates the highest severity status received using the following scheme:

For example, a blue icon displaying a number 2 indicates that two statuses are waiting and that the highest severity status is Informational. A yellow icon displaying a number 4 indicates that four statuses are waiting and that the highest severity is Warning, and so on.

When Robot/CONSOLE receives an inquiry message, a Robot/SCHEDULE job ends abnormally, or another defined event occurs, the Node’s color can change depending on the severity of the event.

With the Robot/NETWORK Map Center, you can spot any System i having trouble. Multiple people, each using their own PC and their own view of the network, can view the Map Center simultaneously. It’s a great visual tool to quickly identify problems across multiple networks or with individual nodes in large networks.

When you move your mouse over a system, Robot/NETWORK displays a pop-up window showing the severity and number of outstanding events. When an event arrives on a Node, you can access it directly from the Map Center by selecting either the Node or the Host. When you select a system, the Status Center displays, allowing you to reply to the event, acknowledge it, or research it. When all events for a system have been acknowledged, the system’s color changes to green, indicating there are no outstanding events.

The arrows connecting systems in the Map Center indicate the status of the current communication between Hosts and Nodes. If the arrow connecting the Host and Node is red, the Node is not communicating with the Host. This may mean that Robot/NETWORK is not active on the Node (the Node is offline) or that there is a communication issue. A dashed arrow indicates that the status of the connection is unknown.

When you right-click the system, you have access to many other functions.

• View Statuses displays the Status Center. If you view statuses from a Node, the Status Center displays only the statuses from that Node. If you view statuses from a Host, the Status Center displays the statuses for the Host and its Nodes. Exactly what is displayed depends on the filter (if any) you are using for the display.
• Properties. For a Host, you can use the Properties dialog to view the Host properties; change the listening port, notification port, and time zone offset; set escalation options for product status messages from the Nodes; and set visibility with other Hosts. For a Node, you can use the dialog to view the Node’s properties and change the escalation options for its product status messages.
• Server. Using the Server options, you can locate specific information about your System i Host hardware and software; view system statuses; work with the active jobs on the server; work with the spooled files on the server; and start a TELNET session to access different servers (Host or Node) in the network.

A dashed rectangle identifies the selected system. Double-click to display the Status Center or right-click to display more options, as shown.
Contributed by Terri Preston, Technical Consultant

How can I prepare for converting to Robot/NETWORK 10?

You must have the following software installed on your System i:

For more information about converting to Robot/NETWORK 10, download the following Help/FACTS:

• Instructions for converting Robot/NETWORK Version 9.0 to 10.0
• Robot/NETWORK 10 compatibility with other Robot products

I have a report with an account number in the same position on every page. It seems like I could use Robot/REPORTS to burst the report by account numbers. However, I have over 100 different accounts. Can I burst the report that many times?
Yes, you just need to create a bursting segment for each account number.

Plus six reasons to update Robot/REPLAY today!

Robot/REPLAY, the plug-in for Robot/SCHEDULE, lets you automate System i processes you thought you couldn’t automate. Robot/REPLAY reliably automates interactive functions used by many popular software packages (such as BPCS, Infinium, MAPICS, and Prism), as well as by your internally developed applications.

You might be surprised at how quickly Robot/REPLAY can transform manual tasks to automated tasks that run unattended. Like Robot/SCHEDULE, Robot/REPLAY has a “learn” mode that allows you to perform the actions you normally do to submit the job. Robot/REPLAY “learns” the keystrokes required by your application, and stores them in a Replay object. The Replay object can be edited, played back, and scheduled to run in Robot/SCHEDULE. You can even run a Replay object from your own program using the RPYEXECUTE command.

Key concepts to understand
When working with Robot/REPLAY, there are a few key concepts that can help you use the product to its full potential and are useful if you need to contact technical support.

1. Multiple Jobs. When you execute a Replay object, it runs a number of System i jobs. At minimum, there are two—a parent (batch) and a Virtual Terminal (interactive) job.
   • Parent job: The parent job initiates a Replay session when you run the command RPYEXECUTE or RPYSTRLRN. It is responsible for starting a Virtual Terminal job and communicating with it. The job log for this job contains status messages that let you determine the success or failure of specific screen images.
   • Virtual Terminal job: The Virtual Terminal (VT) job is an interactive job initiated by the parent job. The keystrokes recorded in the Replay object are executed by this job. The job log for this job contains information about what was actually run by the object. This job is listed on Robot/SCHEDULE’s Job Completion History panel with the job name VTSESSION.
   • For the rare occasions when a Replay object ends abnormally, job logs from both the parent and VT jobs are very helpful in problem resolution.
   • Child job(s): A child job is a batch job submitted by the VT job and is known to Robot/REPLAY. Not all Replay objects run child jobs, but if they exist there can be more than one. If you have specified that Robot/SCHEDULE should wait for the completion of a child job(s), the parent job waits to report its completion status to Robot/SCHEDULE until the child job(s) finishes.
2. Play Back Replay Object: When you play back a Replay object, the application does not execute. Robot/REPLAY displays the images as they were captured, unless Robot/SCHEDULE reserved command variables (RCVs) have been substituted for fixed values. If so, their value is resolved during playback.
3. Virtual Terminal job attributes: Robot/REPLAY sets the user library list, current library, and several job attributes to match the parent job prior to executing any screen images. If you need more specific information, check the VT job log.
4. Authority: To perform any function on a Replay object, the requesting user profile must have *USE authority to the user profile defined for the Replay object.

Reasons to update Robot/REPLAY
Help/Systems continually improves its products. If you aren’t running Robot/REPLAY 2.30 (or higher), you may not have the following recent changes:

1. Work with Replay objects:
   • New Option 14 lets you print all images of an object.
   • New Profile column displays the user profile attached to the Replay object.
   • New Var column quickly identifies if the object uses Robot/SCHEDULE reserved command variables.
   • New User Profile sort added to F9=Select Search.
2. Work with Replay Images: New Number of Variables column identifies where you have used Robot/SCHEDULE reserved command variables.
3. In the past, the user profile used to run the Replay object had to have display sign-on information set to *NO and password expiration interval set to *NOMAX. This is no longer required. (Note: If a password expires on the System i and is changed, the new password also must be updated in Replay User Profile Enrollment.)
4. The user profile RBTREPLAY is no longer required to run the product. However, you can continue to use it.
5. The Virtual Terminal job log contains new informational messages RY19906, RY19907, and CPF9897. These explain when the VT job changes its job attributes and library list to match the parent job and specifically what was changed.
6. Message ID RY72402, which can be issued in failure situations, has been updated with complete cause and recovery sections.

Contributed by Brenda Peroutka, Maintenance Software Engineer

New product addresses the need to manage System i security policies

Help/Systems, the world’s leader in automated operations and business intelligence software for the IBM System i, announces the release of Robot/SECURITY, the i5/OS security monitoring and auditing package. In today’s business climate, System i security is vital. Robot/SECURITY is the only security software that combines five critical System i security tools in one package to help users develop a complete security strategy for their System i.

Profile Exchange authorizes users to assume the authority of another user profile temporarily. Administrators define alternate user profiles that are assigned special authorities. When users need a different authority level, they can exchange to an alternate user profile, assume the necessary authority, complete their tasks, then return to their standard authority level.

Exit Point Monitoring controls who is allowed to access the System i through server exit points. Robot/SECURITY monitors selected exit points and helps control access to the system. Users define which exit points to control and the level of management needed.

The Security Audit checks system security settings and compares them to industry best practices. Users can audit system value settings, user profiles, network attributes, servers, libraries, files, commands, programs, directories and other objects for proper private and public authorities, and set authority policies for their company. A comprehensive set of reports provides a complete audit record.

QAUDJRN Monitoring provides real-time monitoring of the security audit journal. Robot/SECURITY monitors for authority failures, user profile changes, invalid password attempts, and system value changes, and notifies the user when potential security problems occur.

The Forensics Analysis Utility is a PC tool that consolidates data from several sources, including Robot/SECURITY profile exchange and exit point monitoring, the system audit journal (QAUDJRN), the system history log (QHST), QSYSOPR and other message queues, and other Help/Systems products, to help users research system security activity by user, job, or date.

Robot/SECURITY makes it easy to implement, audit, and maintain security policies, while providing the flexibility users need to take full control of their System i security procedures.

For more information, visit www.helpsystems.com/ops/security.html.