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
Connect to SAP.
Connect to backend database where we save the results of the RFC call.
Call the SAP RFC function.
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 availableat 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
- Perl was installed to C:\strawberry
- The QueueDownload.pl script is at c:\strawberry\pi_monitor
- 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.
