You've learned how to automate data transfers. So what's the next step?

You've read my first tip in this series, "Automate Data Transfers Using FTP." Now, let's go a step further and use an automated FTP data transfer in a real-life situation.

Consider the following scenario: Your company has several branches throughout the country, each with its own server, and you need to get their orders into your core business system (which is a System i). You have a Lotus Domino mail server running within the same network as your i. Currently, an operator checks the orders mailbox, saves the order files to a folder on the file server, transfers them to the System i, and runs a program to integrate them on the Orders physical file. If you have (let's assume that the business is going well) 100 branches, this means repeating the same set of tasks 100 times. A bit boring, to say the least.

From this point on, I'll be referring to Lotus Domino because that's the mail server used in my company. I'm not entirely sure, but it's fair to assume that there's a way to do the same on an Exchange server or some other mail server.

What if you could automate the whole procedure? In part 1 of this series of tips, I described how to automate data transfers using plain and simple FTP. Using that together with a Lotus Domino agent that is triggered by an email's arrival to the Orders mailbox, you can transfer the file to the System i and run the appropriate program to integrate it on your database.

There's plenty of information on LotusScript (one of the languages that can be used to write Lotus Domino agents) online, so I'll skip to the good stuff! You might want to read a bit about the basics of LotusScript before continuing.

Within the Orders mailbox, start by creating a new agent, triggered on event after a new email arrives (see Figure 1).

Figure 1: Create a new agent.

Since this agent will act upon all messages arriving at that mailbox, it's a good idea to arrange a fixed Subject line with your branches (in order to avoid processing messages that do not contain order files) and set the Document Selection to that subject (see Figure 2).

Figure 2: Set the Document Selection to the Subject line of your choice. (Click image to enlarge.)

Now let's go over the script itself, step by step, starting with the declarations section.

Sub Initialize

%REM  

   Program  ...: Transfer branch orders file and integrate it into main orders PF

   Date           ...:  January 2010

   Programmer ...: Rafael Victoria-Pereira

   Remarks  ...:

%END REM

      ' Notes Structure related variables

      Dim s As NotesSession

      Dim documents As NotesDocumentCollection

      Dim db As NotesDatabase

      Dim doc As NotesDocument

      Dim rtitem As Variant

      'File transfer related variables

      Dim TransfPath As String

      Dim fileName As String

      Dim cmd As String

      Dim taskId As Integer

      Dim NumDocs As Integer

      Dim SysName As String

We're going to have to identify and open the newly arrived message (represented as a document on a Notes database), starting from the top of the Domino structure. For that, we need to define a new Notes Session, Notes Database, Notes Document Collection, Notes Document, and Rich Text Item (shown here as variant data type) to receive the attachment. This structure will allow us to manipulate the contents of the message (subject, body, and attachment).

The next group of variables will be used to transfer the attachment to the System i and run a program to process it. First, there's the temporary folder in which to store the attachment, then the file's name, and finally, two variables that will be used to execute the DOS commands—in this case, the batch file that transfers the attachment and the program execution to integrate it into the order's main file.

Next, there are some initialization procedures:

'======================================================================    

'Indicate to which temporary folder the attachment should be saved

      TransfPath = "C:\TEMP\ "

'Indicate the name of the system

      SysName = "MySystemi"

'Access the Notes structure right down to the document

      Set s = New NotesSession

      Set db = s.CurrentDatabase

      Set documents = db.UnprocessedDocuments

These should be self-explanatory, as they are fairly standard script initialization commands.

Next, the script will process all the newly arrived email (documents) using a FOR cycle:

      NumDocs = documents.count

      Print ("Num docs - " + Str(NumDocs))

      For n = 1 To NumDocs

            Set doc = documents.GetNthDocument(n)    

            Set rtitem = doc.GetFirstItem( "Body" ) 

            notesEmbeddedObject = rtitem.EmbeddedObjects

            If ( rtitem.Type = RICHTEXT ) Then

                  Forall o In rtitem.EmbeddedObjects         

                        objectName = o.Name

                        'Save the attachment to disk

                        Call o.ExtractFile(TransfPath + objectName)

                        ' Transfer it to the system i using ftp

                        cmd= "D:Scripts\SofinlocContratos.bat " + SysName + " " + objectName

                        taskId% = Shell(cmd)

                        ' Wait 10 seconds before continuing, in order to ensure that the file transfer is finished

                        Sleep(10)

                        ' Command to execute on system i

                        cmd=  "RMTCMD SBMJOB  CMD(CALL LPPTPOBJ/PORRXS PARM('M' '" + objectName+ "')) " + _

                        +     " JOB(SFL_OBJ) JOBD(LPPTPOBJ/NOLSJOBD)  JOBQ(QGPL/XML_JOBQ) INLLIBL(*JOBD) " +_

                        +  "  //" + SysName

                        ' Execute the command

                        taskId% = Shell(cmd)

                        ' Wait 20 seconds before processing the next email

                        Sleep(20)

                  End Forall

It saves the attachment to disk, transfers it to the System i (putting part 1 of this TechTip series to good use), and ends by running a program via RMTCMD (see the sidebar "RMTCMD and REXEC" at the end of this article for more details). To make sure that the program has enough time to process the file, the agent waits for 20 seconds before continuing to the next email.

Finally, the agent transfers the processed message to another folder in the Orders mailbox, called Orders Archive, and loops to process the next document:

                  ' Archive the email

                  Call s.UpdateProcessedDoc( doc )

                  Call doc.PutInFolder( "Orders Archive" ) 

                  Call doc.RemoveFromFolder( "($Inbox)" )

            End If

      Next n

End Sub

Here's the whole agent:

Sub Initialize

%REM  

   Program  ...: Transfer branch orders file and integrate it into main orders PF

   Date           ...:  January 2010

   Programmer ...: Rafael Victoria-Pereira

   Remarks  ...:

%END REM

      ' Notes Structure related variables

      Dim s As NotesSession

      Dim documents As NotesDocumentCollection

      Dim db As NotesDatabase

      Dim doc As NotesDocument

      Dim rtitem As Variant

      'File transfer related variables

      Dim TransfPath As String

      Dim fileName As String

      Dim cmd As String

      Dim taskId As Integer

      Dim NumDocs As Integer

      Dim SysName As String

'======================================================================    

'Indicate to which temporary folder the attachment should be saved

      TransfPath = "C:\TEMP\ "

'Indicate the name of the system

      SysName = "MySystemi"

'Access the Notes structure right down to the document

      Set s = New NotesSession

      Set db = s.CurrentDatabase

      Set documents = db.UnprocessedDocuments

      NumDocs = documents.count

      Print ("Num docs - " + Str(NumDocs))

      For n = 1 To NumDocs

            Set doc = documents.GetNthDocument(n)    

            Set rtitem = doc.GetFirstItem( "Body" ) 

            notesEmbeddedObject = rtitem.EmbeddedObjects

            If ( rtitem.Type = RICHTEXT ) Then

                  Forall o In rtitem.EmbeddedObjects         

                        objectName = o.Name

                        'Save the attachment to disk

                        Call o.ExtractFile(TransfPath + objectName)

                        ' Transfer it to the System i using ftp

                        cmd= "D:Scripts\SofinlocContratos.bat " + SysName + " " + objectName

                        taskId% = Shell(cmd)

                        ' Wait 10 seconds before continuing, in order to ensure that the file transfer is finished

                        Sleep(10)

                        ' Command to execute on system i

                        cmd=  "RMTCMD SBMJOB  CMD(CALL LPPTPOBJ/PORRXS PARM('M' '" + objectName+ "')) " + _

                        +     " JOB(SFL_OBJ) JOBD(LPPTPOBJ/NOLSJOBD)  JOBQ(QGPL/XML_JOBQ) INLLIBL(*JOBD) " +_

                        +  "  //" + SysName

                        ' Execute the command

                        taskId% = Shell(cmd)

                        ' Wait 20 seconds before processing the next email

                        Sleep(20)

                  End Forall

                  ' Archive the email

                  Call s.UpdateProcessedDoc( doc )               

                  Call doc.PutInFolder( "Orders Archive" ) 

                  Call doc.RemoveFromFolder( "($Inbox)" )

            End If

      Next n

End Sub

Please note that the RMTCMD command is part of the iSeries Access installation, so if you use other emulation software, you'll have to replace it with the proper command or use REXEC (again, see the sidebar "RMTCMD and REXEC" for more details).

Also, keep in mind that this type of agent requires a full text index on the mailbox. This takes up some disk space, especially if there's a high volume of emails arriving at this mailbox.

In part 3, I'll explain how to transfer the data from a text file—fixed columns, comma-separated values (CSV), or other—to a physical file within an RPGLE program.

RMTCMD and REXEC

The RMTCMD command is used to send a command to the System i. It assumes that a connection between the machine invoking the command and the target System i is established. You might get a popup window asking for the user and password, if there's no connection. A nice workaround is to leave a 5250 emulation window open, with the sign-on screen.

RMTCMD used to be a SNA-only command, but now it runs over TCP/IP as well and goes through the Remote Command host server. In order to use it, you need to start the server first. Do that by typing the following command (on your System i):

STRHOSTSRV SERVER(*RMTCMD)

Here's a complete description from an (old) IBM manual:

Remote Command (RMTCMD)

You can use the RMTCMD command from PCs running Client Access Express for

Windows to send an AS/400 command or group of commands to an AS/400 system. The commands are sent to the default system, unless otherwise specified. The RMTCMD command is useful for running batch jobs on the AS/400 system and other commands that do not produce any output on an AS/400 display. For example, you can use the command to create a file on the AS/400 system, but not to display the contents of a file.

Client Access Express for Windows provides the same Remote Command API that is available with DOS clients. This is the program that you have to run. The syntax of the RMTCMD command and an example of how it can be used are shown here:

Syntax

RMTCMD command [//remotesystem] [/Z]

RMTCMD /I [drive] [path] filename [/Q] [//remotesystem] [/Z]

Parameters

The AS/400 CL command with parameters to send to the AS/400 system.

/I specifies that the commands to be sent are found in the filename specified after the /I parameter. There must be a blank space after the /I.

drive specifies the drive to use.

path specifies the name of the directory path to use on the drive.

filename specifies the name of the file containing the AS/400 CL commands.

The file must have one command (with the necessary parameters) per line.

/Q specifies that no prompts should be displayed when an error is detected by one of the commands.

//remotesystem specifies the name of the system to receive and run the commands.

/Z specifies to only display the required messages on your workstation.

Example:

To issue a series of AS/400 CL commands, on the default system, that are in a file named command.lst on the same drive and path from which the RMTCMD command is being executed, issue the following command:

RMTCMD /I COMMANDS.LST

As an alternative, you can use REXEC. REXEC as a TCP/IP remote command execution protocol.

If you want the System i to be a REXEC server, you must start the *REXEC TCP/IP server via the command STRTCPSVR *REXEC (again, on your i).

Once that server is started, type the following command on the remote machine (PC or any other server running TCP/IP):

REXEC [systeminame] -l [userid] [systemi command]

Keep in mind that the System i commands must be non-interactive.

You'll find more information about REXEC here.