Introduction 

Apache Tomcat provides a robust and free web server. Since it can be installed by simply unzipping a file, it can be installed without special Administrator privileges. The steps here outline how to get Tomcat started on your PC. these instructions were written for Tomcat 6.0.26 on Windows 32bit.

1. Extract it to c:apache-tomcat-6.0.26
2. Modify c:apache-tomcat-6.0.26confserver.xml. Change 8080 to port 10080.
3. Go to c:apache-tomcat-6.0.26 in and run *startup.bat*. If it doesn't start, check if JAVA_HOME or JRE_HOME is set correctly to a Java 1.6 version.
4. If it doesn't start because the port 10080 (default Tomcat port) is already used, edit server.xml and select another ununsed port number.

At this point you have a functioning web server. Now I will copy over the PI-Monitor web files.

1. Xcopy c:strawberrypi_monitorweb c:apache-tomcat-6.0.26webappsROOTweb

Next I must modify the download.cmd batch script. I tell the QueueUpload.pl script the location to store the jQuery chart javascript files.

@echo off
setlocal
set PATH=c:strawberryperl in;c:strawberryc in;c:
wrfcsdklib;%PATH%
set TERM=dumb
date /T >>log.txt
perl QueueDownload.pl PXP 2>>log.txt
perl QueueDownload.pl PP4 2>>log.txt
Rem Set your Amazon S3 account details here.
Rem If you don't have an Amazon S3 account, delete the next three lines.
rem set AWS_ACCESS=my_amazon_access_key
rem set AWS_SECRET=my_amazon_s3_secret_key
rem set AWS_BUCKET=my_amazon_bucket
Rem If you need a proxy to connect to the Internet, set it here.
rem set  HTTP_PROXY=http://user:password@proxyhost:proxyport
rem set HTTPS_PROXY=http://user:password@proxyhost:proxyport
*perl QueueUpload.pl PXP QueueMonitoring.sqdb c:/apache-tomcat-6.0.26/webapps/ROOT/web/charts 2>>log.txt
perl QueueUpload.pl PP4 QueueMonitoring.sqdb c:/apache-tomcat-6.0.26/webapps/ROOT/web/charts2>>log.txt
*endlocal

Its has been a long blog series to get here. By combining various free technology components, an iPhone web application was created as a mashup. The ideas here can be extended to many other uses. I'm interested in how you will adapt them to your own uses.

Note: Later, I may add a short section using Amazon S3 rather than Tomcat in a later blog. Let me know if you are interested.

Introduction 

This blog is part of a series on Monitoring XI/PI queues on your iPhone . In Part 1 of this series, I described the goal of the project. In this part, I discuss how to use RFC functions to get queue information. You can download the full perl environment, perl script and download.cmd from PI-monitor project at sourceforge. Read the Quick Start section in Part 1 to setup the environment.

About Queues and SAP PI

SAP PI/XI relies on queues in the ABAP stack to get messages processed. The blog XI Asynchronous Message Processing: Understanding XI Queues -Part I provides a good explanation of how PI uses queues. SAP provides RFC functions that provide information about queues in the ABAP stack. These are TRFC_QOUT_OVERVIEW, TRFC_QIN_OVERVIEW and TRFC_QINS_OVERVIEW. PI uses QIN (all message queues) and QINS (messages in parked queues). QOUT is used extensively by BW extractors.

Program structure

The Perl program has the following structure

1. Connect to SAP.
2. Connect to backend database where we save the results of the RFC call.
3. Call the SAP RFC function.
4. Save the results to the database.

Connect to SAP

The Perl module sapnwrfc expects all SAP connection information to be stored in an external configuration file (*.yml). The default configuration file is SAP.yml, however you can pass your own filename as a parameter. In the code snippet below, I use the first command line argument as the SAPSID. The code expects SAPSID.yml file to have the configuration.

--- Perl code ---

use sapnwrfc;
     use strict;
     my $sapsid=@ARGV[0];
     my $sapconfig;
     if (defined($sapsid))
     {
          $sapconfig=$sapsid . ".yml";
     } else {
          $sapconfig="MTP.yml";
          $sapsid="MTP";
     }
     SAPNW::Rfc->load_config($sapconfig);

--- MTP.YML file --

     ashost: PIServerHost
     sysnr: "10"
     client: "100"
     user: UserID
     passwd: Password
     lcheck: 1
     lang: EN
     trace: 0
     debug: 0

If you have a gateway router, use "/H/router/H/pihost" for the ashost parameter. Alternatively you can also use a load balanced configuration and connect to mshost.

Connect to backend database

Once I get the data from SAP, I would like to save it in a database for analysis at a later time. Fortunately, Strawberry Perl includes SQLite - which is a fast RDBMS supporting SQL. You could also use Microsoft SQL Server or Microsoft Access, but you will need to modify the DSN. I use the builtin DBI module to access the database. This hides the details of the backend from me (which simplifies switching from one database to another).

      my $dbfile = 'QueueMonitoring.sqdb'; 
      my $dsn = "dbi:SQLite:$fbfile";
      my $dbh = DBI->connect($dsn,undef,undef, {RaiseError=>1, AutoCommit=>1});
       # The following creates the table in SQLite if it isn't there.
       # If you are using Microsoft Access, you'll need to do it manually
       # Check and create the database table if it doesn't exist
          my @tablelist = $dbh->tables('%','%','QueueData','TABLE');
          if ($#tablelist<0) {

               $dbh->do("CREATE TABLE QueueData (ID, System, SnapshotTime, " .
               " QueueDirection," .
               " MANDT INTEGER, QNAME, DEST, QDEEP INTEGER, QSTATE, WQNAME, FDateTime, FDATE, " .
               " FTIME, LDateTime, LDATE, LTIME, FQCOUNT, LQCOUNT, ERRMESS, " . 
               " QLUWCNT, BATCHPLA, FIRSTTID); ");
          }
       my $sth = $dbh->prepare("INSERT INTO QueueData (MANDT,QNAME,DEST,QDEEP,
         QSTATE,WQNAME,FDATE,FTIME,LDATE,LTIME,
         FQCOUNT,LQCOUNT,ERRMESS,QLUWCNT,BATCHPLA,
         FIRSTTID,QueueDirection,SnapshotTime,
         FDateTime,LDateTime,System) 
         values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
     #If you are using Microsoft Access, replace the first two lines with
     my $dbfile = 'QueueMonitoring.accdb'; 
     my $dsn = 'dbi:ODBC:driver=Microsoft Access Driver (*.mdb, *.accdb);dbq=' 
                     . $dbfile;  
 

Call the SAP RFC function

This is the easiest part of the program, thanks to the magic of the SAPNWRFC Perl module.

# Inbound queues
  $rd = $conn->function_lookup("TRFC_QIN_OVERVIEW");
  $rc = $rd->create_function_call;
  $rc->invoke;

The returned data can be access through $rc. That is shown in the next section.

Save the results to the database

Finally, after calling SAP, I need to save the data. These functions return a table with a single row for each queue. I would highly recommend looking at the data. It has a lot of good information (e.g. which appserver is processing the queue, what is the timestamp of the message being processed, etc.)

# call a subroutine to save the RFC data.
  saveQueueData($rc,$sth,'IN',$snapshottime,$sapsid);
# The actual subroutine is defined below
sub saveQueueData($$$$$)
{
  my ($rc,$sth,$direction,$snapshottime,$system) = @_;
  foreach my $row (@{$rc->QVIEW}) {
     #warn Dumper($row); #uncomment this if you want to see the RFC data
     #print STDERR $row->{LINE}."\n";
     $sth->execute($row->{MANDT}, $row->{QNAME}, $row->{DEST}, $row->{QDEEP}, 
                   $row->{QSTATE}, $row->{WQNAME}, $row->{FDATE}, $row->{FTIME}, 
                   $row->{LDATE}, $row->{LTIME}, $row->{FQCOUNT}, $row->{LQCOUNT}, 
                   $row->{ERRMESS}, $row->{QLUWCNT}, $row->{BATCHPLA}, $row->{FIRSTTID},
                   $direction, $snapshottime, 
                   getsapdate($row->{FDATE},$row->{FTIME}),
                   getsapdate($row->{LDATE},$row->{LTIME}),$system
                   );
  }
}

$rc->QVIEW is the returned table. ${$rc->QVIEW}[0] returns the first row. Once you have a row of the table, access individual structure members as $row->QSTATE. For this application, the fields that are interesting are QSTATE (queue status) and QDEEP.

A note about PI timestamp fields - these are double numbers with 7 digits after the decimal point. The format is YYYYMMDDHHMMSS.sssssss . This format must be converted to an ISO date. The getsapdate function does this conversion. $snapshottime is a timestamp to indicate when the data was queried from SAP.

The last line $sth->execute will run the INSERT statement prepared earlier.

While debugging, you can use warn Dumper($row) to display each row.

Finishing the script

The above steps showed the basic steps needed to call an RFC function and save the results to a database. To complete the script, I've added more error checking and sending messages to the Windows Eventlog. The full script is available at the PI-Monitor project on Sourceforge.

Scheduling it to run every 5 minutes (on Windows)

Once you have this script running, run it periodically (e.g. every 5 minutes) to start collecting data in your database. First, some assumptions made

1. Perl was installed to C:\strawberry
2. The QueueDownload.pl script is at c:\strawberry\pi_monitor
3. You have created your *.yml files.

First, a small wrapper shell script to setup the correct Perl environment and running the QueueDownload.pl script. This one queries two SAP systems (PXP and PP4 which are SAP PI systems).

--- download.cmd ---

@echo off
set PATH=c:\strawberry\perl\bin;c:\strawberry\c\bin;c:\nwrfcsdk\lib;%PATH%
set TERM=dumb
date /T >>log.txt
perl QueueDownload.pl PXP 2>>log.txt
perl QueueDownload.pl PP4 2>>log.txt

Verify that the script is running. Next, schedule a repeating task to run automatically every 5 minutes. Use the schtasks command line on Windows XP and higher.

 schtasks /create /sc minute /mo 5 /tn "Download SAP Queues" /tr c:\strawberry\pi_monitor\download.cmd

Conclusion and next steps

With this, you have completed the first part of collecting queue data from your SAP PI system. The next part will show how the data can be analyzed and shown in a graph.

You can download the full perl environment, perl script and download.cmd from PI-monitor project at sourceforge. Read the Quick Start section in Part 1 to setup the environment.

However compiling the module on Strawberry Perl was not easy. So I decided to document my experience in this blog.

1. Select your operating system and download the SAR file. (In my case it was NWRFC_2-20002217.SAR).
2. You need SAPCAR to uncompress this file. To get SAPCAR (Download->Support Packages and Patches -> Entry by Application Group. Then choose "Additional Components" -> SAPCAR -> SAPCAR 7.10)
3. At the command line, uncompress the SAR file
   

Perl needs a connection to CPAN network to install the modules.  Before you can install modules, you need to setup your proxy settings. If you don't need a proxy, ignore the first HTTP_PROXY command.

• Optionally cpan install Net::Amazon::S3  -- if you want to upload files to Amazon S3.