RPTRTR - A Simple UNIX Report Router

Rich Holoch

A number of large commercial two-tier and three-tier client server application packages run on PCs connected to UNIX-based database servers. Some of these products do a good job at both the client and server ends, but they sometimes fall short when called upon to seamlessly integrate the two. One such task is routing UNIX-based report output files from the UNIX server back to the client PC. Although there are many commercially available bidirectional print spoolers, they are relatively easy to build. This article concentrates on report file routing from UNIX to PC clients, but any file that needs to be moved from one network node to another can be routed with RPTRTR.

RPTRTR allows users to submit, view, and print reports from their PC workstation, without having to learn a new report queue system. In fact, RPTRTR does not have a user interface - it runs silently in the background every 5 minutes and automatically routes report output to a specified destination. The destination can either be a network file server or a user's email in-basket. Thus, members of a mobile sales force can request and receive reports while on the road. RPTRTR is used at Trans Ocean, Ltd. to support a global PeopleSoft Financials 5.01 system.

RPTRTR - How It Works

A number of commercial client/server-based products use a UNIX or PC-based Batch Job Scheduler to run process intensive jobs. Oracle Financials, PeopleSoft Financials, and HRMS are examples of such products. I will use the PeopleSoft Financials 5.01 example throughout this article, but RPTRTR is in no way PeopleSoft specific. Figure 1 provides an overview of the functional elements of RPTRTR.

After a user invokes a (UNIX) PeopleSoft Process Scheduler Print Request, report output files are written to the directory specified in the the Process Scheduler Request Printer/File field. RPTRTR wakes up every 5 minutes and looks for report output in a centrally designated print queue (a UNIX subdirectory). Any existing report files are then automatically transfered via ftp from the UNIX server to the regional LAN file servers using an ftpsrv router (a PC running ftp Software's ftpsrv).

Because all machines are on the Ethernet backbone running TCP/IP, users see the report output on their regional file server, and the output can be viewed or printed directly using the Windows 95 or Windows NT 4.0 Explorer. RPTRTR is platform independent - it can run on any TCP/IP LAN. It uses the native print services found on Novell Netware, Windows NT, UNIX, or Win95. For very small offices, or home offices, users will need to dial into a regional office, download the report file, and print on their local printer. (Posting reports on an intranet server might make more sense in these cases.)

How to Use RPTRTR

The only requirement for using RPTRTR is that your application must support the ability to save UNIX job output to a UNIX file system, and a field must exist in which your users can enter their name or a file location. Make this task as simple as possible. Try to avoid using UNIX file system directory paths - non-UNIX users always seem to use backslashes and ignore case sensitivity.

I was not lucky enough to be able to modify the PeopleSoft Process Scheduler Request Panel in order to make the report output file location user friendly. Instead, the users were taught to enter full UNIX paths to the report queue directories. These directories were either their name (using the First_Last format required by our SMTP gateway), or a geographic location (for files routing to regional servers). If users enter their name, the report is mailed to them; if they enter a geographic location, the report is sent to a regional Netware file server.

Using Email (cc:Mail) Report Routing

To request that the PeopleSoft batch report job is sent via cc:Mail, users click on the Server and File radio buttons and enter their cc:Mail name in the Printer/File field (Figure 2). Again, this panel is used only as an example. If your application's job output panel, screen, or dialog box is fully customizable, you should consider using something more user friendly. Use pull down fields or radio buttons to avoid typos.

PeopleSoft text reports can be printed directly from cc:Mail, and PostScript reports can be saved as a file on your C:\ drive or file server, then copied to your printer port (lpt1) using the Win95 Explorer. Note that some SMTP gateways do not support mail files wider than 80 characters, and some cannot handle attachments or non-ascii text files.

Using Location Report Routing

Users request that their batch report jobs be sent to their regional file server by clicking on the Server and File radio buttons, and then entering their file server (location) in the Printer/File field. Users need only check the regional server for the print output. Using Windows or NT 4.0's Explorer, a simple click on the file name with the right mouse button allows users to print.

RPTRTR is a Korn shell script (Listing 1) that simply loops through report queue directories, ftps the files to regional servers and then cleans up the UNIX print queue directories. It automatically overwrites existing report files with the same name. This assumes that the latest version of a report wins. Other schemes, such as time-date stamping and archiving old files can be added.

RPTRTR must reside in UNIX user's .bin directory under $HOME. All mail and server location directories under reports $HOME act as "buckets" to temporarily hold print files. These directories are built from a copy of the first column in an Excel spreadsheet (ccmail.xls). cc2dist is an awk script that reformats the cc:Mail column and creates the report router email distribution list:

awk -F"," '{print $2"_"$1}' ccmail > distlist

Next, the distribution list is turned into a series of mkdir commands using mkdistdir:

awk -F"," '{print "mkdir "$2"_"$1}' ccmail > mkdist

After running mkdist to build the directories, the directory (file) permissions are set to 777 so that anyone on the UNIX box can drop files into the distribution directories. Run chmoddirs to set these:

awk -F"," '{print "chmod 777 "$2"_"$1}' \
ccmail > chmoddirs

To set up the report router location ftp server, make sure that the PC used as the ftp server is in the /etc/hosts file. Also, make sure that all regional file servers are mapped properly on the PC ftp server.

The following shows how RPTRTR is automatically run every 5 minutes by user PSOFT.

$ crontab -l

0,5,10,15,20,25,30,35,40,45,50,55 * * * * \
/home/reports 2>&1 >/dev/nul
l

The crontab entry is on the PSOFT UNIX account, but the report queue is located where ample disk exists, under /home/reports:

/home/reports/Rich_Holoch:
total 104
drwxrwxrwx  2 reports  users  8192 Feb 14 08:50 .
drwxrwxrwx  4 reports  users  8192 Feb  8 16:41 ..
-rw-------  1 reports  users  86446 Feb 14 08:50 fin0018.lis

FIN00018.LIS in the Print Queue

Note that each user who will receive an emailed report must have a subdirectory under the reports directory. This must be maintained when users' security status changes for applications (e.g., PeopleSoft). You might want to consider eliminating this messy administration by using a more dynamic process.

Conclusions

RPTRTR is a simple and cost-effective solution, but needs to be improved to eliminate userid administration. I plan to rewrite this as an OraPerl CGI application, in which mail userids are stored in an Oracle database. Then, I will be able to create one directory for all user names, instead of one per user. RPTRTR will check the database, validate the user, and name/time/date stamp the report in the central report "bucket." I might do the same for the regional file server names. This will allow me to add an easy-to-use GUI user administration screen. Then perhaps I can hand the administration over to someone else.

About the Author

Rich Holoch is a consultant specializing in Oracle and PeopleSoft database administration and systems programming on UNIX and NT. Rich has worked for Oracle Corporation, Gupta Technologies, and PeopleSoft, Inc.