Help/Systems recently conducted a survey of Robot/NEWS readers. Readers who completed the survey had the option of entering their names in a drawing for a Nintendo Wii game system. When the survey closed on November 16, the winner was:

Mike Kammer
Sunrise Senior Living, Inc.
McLean, VA

Our congratulations go out to Mike. We thank everyone who responded to the survey.

Four-step process can be totally automated

Monitoring and adjusting your IBM System i for maximum efficiency and operations can be time-consuming and difficult. Add the complexities of coordinating resource movements for business processes in a logically partitioned System i, and you can have difficult operational issues. What you need is an easy-to-use interface to your Hardware Management Console (HMC) that helps you move your partitioned resources for maximum performance. That interface is Robot/LPAR, the software package that works with the HMC to move processor, memory, and I/O devices among your partitions interactively or in batch mode.

You install Robot/LPAR on one or more i5/OS partitions on your System i and it communicates with the HMC to manage partitions and set, adjust, or move resources. Robot/LPAR is especially useful for system administrators who must share or provide resources across partitions because of Service Level Agreements (SLAs).

Robot/LPAR works with the HMC to move resources to meet your business needs. With Robot/LPAR, you can move shared resources based on critical batch processes and you can schedule these movements using Robot/SCHEDULE. You can eliminate guesswork, mistakes, and the need to have someone monitor your LPAR processes—through automation. Robot/LPAR helps you ensure that your memory, processor resources, and devices are where they need to be to minimize the costs and maximize the efficiency of your partitioned environment. Your System i processes run quickly because Robot/LPAR works with the HMC at the operating system (i5/OS) level so all resource movements are fast and efficient.

Instead of buying a larger System i to handle workload changes, or buying separate devices for each partition, you can save money by using Robot/LPAR to share your resources. You share your memory, processor, and device resources across several partitions by moving them as they are needed.

Let’s look at a common scenario. (This article assumes you have Robot/LPAR, Robot/SCHEDULE, and Robot/NETWORK installed on both partitions to completely automate this process.) You control the movement of resources from the Robot/LPAR Move Resources Menu.

Each day at the end of daytime processing on partition A, you want to perform a system backup and then start the nightly batch processing. To do so, you must move a shared tape device, known as TAP01, from partition B to partition A for the backup. Then, after the backup on partition A completes, you must move the device back to partition B for its backup.

1. Because partition B currently owns the device TAP01, you can use Robot/LPAR, Robot/SCHEDULE, and Robot/NETWORK (with cross-system reactivity), to issue a Put I/O Resource (LPRPUTIOR) command from partition B. (Note: Cross-system reactivity is the ability to perform dependent job processing across multiple systems using Robot/NETWORK and Robot/SCHEDULE. Robot/NETWORK tells Robot/SCHEDULE to start a job on one system based on the successful completion of a Robot/SCHEDULE job on another system.) Robot/LPAR automatically varies off the device, its IOP, and any attached devices before it moves them. Because this is an IOP device, you use the *IOP parameter to indicate that the device, its IOP, and any other devices attached on the IOP must be moved. (Note: If this were an IOP-less IOA device, you would specify *IOA, and only the device would be moved.)
2. After TAP01, its IOP, and any associated devices have been moved, you can have Robot/NETWORK tell Robot/SCHEDULE on Partition A to run a job to vary on the device and start the backup job(s).
3. After the daily backup completes on partition A, you react to move TAP01 back to partition B to start daily backups there. Because partition A now owns the tape device, you use another LPRPUTIOR command to move the device, its IOP, and its attached devices to partition B. Robot/LPAR varies off the device before moving it.
4. After the device(s) are moved successfully, you have Robot/NETWORK tell Robot/SCHEDULE on partition B to start a job to vary on the device in partition B and start the backup process.

Other functions
The Robot/LPAR Move Resources menu also allows you to:

• Adjust and set resource levels for processor capacity and memory size
• Display partition information about the managed system and partitions.
• Change the partition state to shut down or activate a partition.

Contributed by Anna Deuel, Product Manager

Here’s a rundown of some ways to use SEQUEL’s CVTDATE function

At some point, almost all SEQUEL users need to work with dates that are stored differently in several databases. This article looks at some uses of the CVTDATE function to help you handle this common problem.

Using the CVTDATE function
SEQUEL has a unique date conversion function called convert date (CVTDATE). This function converts numeric or character date fields into System i date data-type fields. When you convert numeric or character values to System i date values, the system recognizes the values as dates. The System i then can handle all kinds of date calculations, such as counting days between dates, subtracting dates, or sorting by dates. Not only can you perform calculations on dates, but you can present difficult date formats (such as Julian dates) in more readable formats. The general syntax is:

CVTDATE(fieldname,fieldtype)

Enter the name of a numeric or character date field in the fieldname portion of the expression. Use the fieldtype to indicate to SEQUEL the format of the date, as it is stored in the file.

How SEQUEL looks at dates
Let’s look at how SEQUEL works with some common date formats.

Dates with century values
MDY1 is shorthand for dates represented as mmddyyyy. For instance, if your field name is SHIPDATE and it is stored as mmddyyyy (09302007), you have an MDY1 type date.

Date types of CYMD and CJUL must have a century digit preceding the year value. The century digit is zero (0) for years between 1900 and 1999 and one (1) for years between 2000 and 2099. For example, the CYMD representation of September 30, 2007, is 1070930.

Dates without a century value
SEQUEL interprets date types without a century value (MDY, DMY, YMD, JUL) by assuming years between 40 and 99 are the years 1940 to 1999, and years between 00 and 39 are the years 2000 to 2039. For example, a MDY-type date of 093007 would be read as September 30, 2007. However, 093040 would be September 30, 1940. If you have dates in your database, such as birth dates, that are earlier than 1940 and don’t have a century digit, contact technical support to determine the best way to handle your situation.

Creating one date from several fields
You can also use CVTDATE to unite dates that are separated into three or four fields containing century, year, month, and day values. The field names in the CVTDATE calculation must be specified in the following order: century (if available), year, month, day. CVTDATE can work with character or numeric fields and the value must be a valid date. To unite dates, use one of these syntaxes:

CVTDATE(yy or yyyy,mm,dd)

CVTDATE(cc,yy,mm,dd)

You can use the CVTDATE syntax in the SELECT, WHERE, or HAVING clauses. Like other calculated fields in SEQUEL, you can use the alias names of the converted date fields from the SELECT clause in other clauses, including the ORDER BY clause.

Controlling Date Styles
SEQUEL commands contain a date style (DTSTYLE) parameter. This parameter controls the date formats that are shown when you use CVTDATE. You can control the default style from the Define View or Define Table screen.

Working with dates containing zero or invalid dates
Data mapping errors result when records contain zero as the converted date because zero is not a valid date. You can use SEQUEL’s GREATEST and CASE functions to avoid these errors. The statement below converts any field with a date value of zero, or a value lower than January 1, 1940, to the date of 01/01/40:

CVTDATE(GREATEST (FIELDNAME,400101),FIELDTYPE)

The statement below changes zero values to null, producing output of N/A, using SEQUEL’s CASE function:

CASE FIELDNAME WHEN 0

THEN NULL

ELSE CVTDATE(FIELDNAME,FIELDTYPE) END

A data mapping error also occurs if there is invalid data in a date field, such as a date of 09/32/2007. In this case, you can identify the record number in the error message. If you have the proper authority, you can edit the date using using SEQUEL’s UPDATE command.

Calculating working days
Sometimes you need to calculate the number of working days (Monday through Friday) between two dates. For example, how would you calculate the number of working days between June 1, 2006 and June 17, 2006 using SEQUEL?

A SELECT statement is a good approach:

SELECT

CVTDATE(2006,6,1) NAME(a), date 1

CVTDATE(2006,6,17) NAME(b), date 2

DAYS(b)-DAYS(a) LEN(5,0) NAME(c), delta

FLOOR(c/7)*5 LEN(3,0) NAME(workdayweek), days in whole weeks

c MOD 7 LEN(1,0) NAME(d), restdays

DAYOFWEEK(a) NAME(da), day of week date 1

DAYOFWEEK(b) NAME(db), day of week date 2

CASE WHEN da=1 THEN 2 if date 1 is equal

WHEN da=7 THEN 2 Sunday or Saturday

ELSE da END I start on Monday

LEN(1,0) NAME(dc),

CASE WHEN db=1 THEN 6 if date 2 is equal

WHEN db=7 THEN 6 Sunday or Saturday

ELSE db END I stop on Friday

LEN(1,0) NAME(dd),dd-dc

LEN(1,0) NAME(de), helpfield

CASE WHEN da IN(1,7) AND db IN (1,7) THEN workdayweek {correction of weekends}

WHEN de<0 THEN workdayweek+5+de+1

ELSE workdayweek+de+1 END LEN(5,0) NAME(tot)

This solution isn’t perfect if date 1 is a Sunday and date 2 is a Saturday. But with some revision, you can still use this approach. SEQUEL is very flexible. Just use your imagination!

Adds Web-based access to more Robot products

Help/Systems, the world’s leader in automated operations and business intelligence for the IBM System i, announces the release of Robot Browser Interface 2.0, Help/Systems’ Web browser-based interface for the System i. The Robot Browser Interface provides Web-based access to five Robot products: Robot/SCHEDULE, Robot/NETWORK, Robot/REPORTS, Robot/SPACE, and Robot/ALERT.

Robot/SCHEDULE users can use the Robot Browser Interface to view and filter the status of Robot/SCHEDULE jobs, view colored icons for a quick visual reference of the job type and status of each job, and view their Good Morning Report.

Robot/NETWORK users can use the Robot Browser Interface Statuses page to quickly view, filter, and reply to statuses, and view their Good Morning Report.

Robot/REPORTS users can use the Robot Browser Interface to view, save, and print their System i reports to a PC or System i printer, add notes to reports, and create a personalized “My Reports” page, which allows the user to specify whether they want your reports stopped, how they should be printed, and other options.

Robot/SPACE users can use the Robot Browser Interface to view ASP usage and thresholds across multiple systems or partitions. The ASP display indicates the percentage of storage space used plotted against the time range that the user specifies.

Robot/ALERT users can use the Robot Browser Interface group page to send a message to selected users. Users just select a device, select a single recipient or broadcast list, and enter the text of the message.

The Robot Browser Interface can be used with any standard Web browser, such as Internet Explorer or Firefox.

As part of the introduction of Robot/SECURITY, Help/Systems sent a mailing to selected customers describing the new product as “above top secret.” This mailer, resembling a dossier about a spy and including a security badge, provided the option to enter a sweepstakes to win night-vision goggles. Entrants had to read the mailing carefully and enter their security badge number on a special landing page on the Help/Systems Web site.

When the sweepstakes closed on October 24, the winner was:
Duane Jessup
Information Technology
Toll Brothers, Inc.

Our congratulations go out to Duane. We thank everyone who entered the sweepstakes.

How do I copy a list of reports from one recipient to another in Robot/REPORTS?
Robot/REPORTS (7.19 or higher) includes the REPCPYDST command to copy distribution from one recipient to another. This command copies both the distribution and its related history, so the recipient sees reports processed in the past, as well as receiving future reports. You must supply three parameters to the command:

• From Recipient (FROM): Enter the name of the report recipient whose report list will be copied.
• To Recipient (TO): Enter the name of the report recipient who will receive the reports.
• Copy Distribution List reports (COPYDIST): Specify whether to copy all of the reports the From recipient receives, including reports distributed from distribution lists.

• Y (Yes): Include the reports the From recipient receives from distribution lists.
• N (No): Exclude the reports the From recipient receives from distribution lists.

Here are some other things you should know about this command:

• You cannot use the name of a distribution list for either the To or From recipient.
• The REPCPYDST command produces a report (REP746) listing which reports will be distributed to the To recipient.

If I have the Secure Socket Layer (SSL) enabled in Client Access, can I use
SEQUEL ViewPoint?
Yes, as long Client Access Certificates have been established for the Sign-On Server, Database Server, Remote Command, Central Server, Data Queue Server, and DDM/DRDA Server. Run the iSeries Navigator SSL Connection test. If it is successful for all but the Network File application, Print application, and Telnet application, you should be good to go.

Certainly not any time soon, but there are trends to be aware of

Few companies have eliminated all forms of tape backup. Ultimately, it may be more important to focus on automating the process than upgrading the media.

About 10 years ago, many experts predicted that media backups would be replaced by disk-to-disk backups. Yet today, most System i installations still are doing some form of tape backup. Why? Is it a comfort zone? Is it really needed? And, if we are stuck with some form of backup, can we automate the process?

The truth is that the backup copies of our data are valuable assets. The backup media and other aspects of the backup task might change (for example, we might perform the backups through the network, across fiber optics, or by replicating data to a high availability system), but the need for backups will never go away.

I like to think of backups as an insurance policy. Performing a backup is like paying the monthly premium. The payoff comes when you can successfully restore your company’s data at a recovery center without jumping through hoops. The real backup issues are recoverability and automation.

Six key areas to automate
Automating computer systems has two huge benefits. It increases staff productivity and it reduces errors. If we start to think about automated backups as an insurance policy, what features should we look for in such a policy? I think there are six key areas:

• Tape/media tracking
• Robotics
• Virtual tape
• Restricted state
• Restoration reporting
• Regulations
• Tape/media tracking

Whether you are using a disk-to-disk network backup or a disk-to-tape solution, it is important to have a system that eliminates manual recording of storage information. A good tape/media tracking feature provides an easy-to-use inventory of what is stored on the backup media and where the media are located. Ideally, the system also provides a central repository of information for all systems in the data center, defines media retention periods, provides easy-to-use reporting, and tracks ad hoc backups by IT staff.

Robotics
Today, robotic devices can load tapes by infrared bar codes, eliminating the need to manually mount tapes. While this technology is needed to run automated backups, it introduces another level of complexity. When you acquire this equipment, get all the vendor training you can. Unfortunately, hardware vendors don’t seem to have a lot of knowledge about backup and recovery software, so it falls to the users to try to marry the two successfully. Once you get over that hurdle, you can enjoy the benefits of automation.

Virtual tape
IBM introduced virtual tape as part of V5R4. System administrators now can save data to a directory in the Integrated File System (IFS) to perform essentially a disk-to-disk backup. Virtual tape can be on an auxiliary storage pool (ASP) that is local or remote. Using a high-speed optical connection, backup speed is good and media errors are eliminated.

IBM designed virtual tape to mimic saving to a tape drive and retained the concept of a “volume.” That gives backup-and-recovery software the ability to support the technology through media management features.

Restricted state
If you can afford downtime once a week, I recommend automating restricted state backups. With increased tape capacity and software automation tools, you can run an automated and monitored restricted state backup completely unattended. For companies that need 24×7 system availability, this is a more painful issue. From a technical standpoint, you don’t really need to do a SAVSYS operation in i5/OS. The dynamic part of the OS can be backed up by commands that do not require a restricted state. However, very few people skip this backup because a recent SAVSYS makes it easier to restore the system. To eliminate downtime, some companies use high availability (HA) software to replicate the system and then run restricted-state backups on the HA system.

Restoration reporting
As backups complete, the data restoration plan should be updated in the database automatically. Then, the updated plan should be transmitted automatically to your remote hot site or off-site storage. The restoration process should be tested periodically. In speaking with experts at both IBM and SunGard, I have concluded that many people test as though disasters have a timetable. Administrators do a full-system save one weekend and a system test the next. This is not realistic. You need to test recoverability with the assumption that the failure has occurred at a less-than-optimal time, such as midday.

Regulations
If your team cannot figure out how to automate the processes and procedures around backup and recovery, you will spend enormous amounts of time handling Sarbanes-Oxley (SOX) and other regulatory requirements. Backups will not be going away anytime soon, and you should focus on alleviating the manual paper trail in this area. Your tape management systems should provide the reporting you need for your auditors. If they don’t, you should look for a better solution.

The need for backups is not going away. We will continue to see technological improvements. Solid-state technology (such as thumb drives) will become cheaper, eating away at the standard technologies of disk and tapes. As technological changes occur, evaluate whether they help your data center become more automated.

By Tom Huntington, Vice President of Technical Services