Warehouse Automation (II) - LEI As The Data Pump

IBM Lotus Enterprise Integration (LEI) 7.01 is used to act as the data pump, it retrieves scan data (CSV file) on File Server into MySQL database at application server. Even the whole process is nothing to do with Lotus Notes database, the LEI is still my best choice(if performance meets) since no programming(Java, LotusScript or PHP) needed and easy to schedule the tasks.

LEI connection document for Text ODBC Connection :

this is to connect the S/N text file uploaded by GoodSync

 

LEI connection document for MySQL ODBC Connection :

LEI activity document which is a "Direct Transfer" process :

this copy the S/N data into MySQL SNOUT2 table

 

If success, it will run an additional job - "UPDATE SNOUT WITH NEW SN FROM SNOUT2"

  

This is a schedule job, however, it also can be called by other process

 

Command Activity document - UPDATE SNOUT WITH NEW SN FROM SNOUT2

It execute the following SQL command which is to update the SN database with new SN on SNOUT2 table

INSERT INTO champ.SNOUT SELECT * FROM champ.SNOUT2 WHERE CONCAT(INVNO, SN) NOT IN (SELECT CONCAT(INVNO, SN) FROM champ.SNOUT) GROUP BY INVNO, PN, SN;

  

  

Warehouse Automation (I) - Create A Low-cost Smart Bar-Code Scanning Solution

Purpose -
To implement a low-cost smart bar-code scanning solution for the warehouse inventory control, automatic data upload process through MS ActiveSync synchronization.

H/W (PDA with built-in Barcode scanner) - 

• Motorola(Symbol) PPT8800 or MC series, you can find PPT8800 under $200 at eBay, however, I used MC70 for this practice
• Networked PC with Windows XP, connect the PDA thru USB port
• Server hosting the database which keep all S/N information, I used a Linux server with MySQL for this practice. You can connect to any database depneds on your environment, even to those non-database applications like Quickbooks, Peachtree, Navision, SAP B1, Lotus Notes ... etc. As long as you set up the connector correctly (normally ODBC)
  

S/W - 

• TracerPlus Pro - barcode scan S/W ($215, trial version available)
• Tracer Desktop - layout editor (free)
• MS ActiveSync 4.5 - connect PDA and PC for synchronization (download from MS)
• GoodSync - since ActiveSync only sync data under My document folder we use GoodSync to sync scan data which is under program folder

Example file - download
This is a TracerPlus Desktop project file, it includes two scan forms, one for outgoing invoices S/N scanning, he other for receiving S/N scanning. Some checking mechanism have been added to avoid double scan or wrong formats.
Fields of Outgoing Invoice S/N Form :
INVNO: Invoice#
PN: P/N
SN: S/N
DATE: scanned date
SCANNER: scanned by

Fields of Receiving Orders S/N Form :
REFNO: PO#
PN: P/N
SN: S/N
DATE: received date
SCANNER: scanned by

Implement Data Synchronization and Database Connector -
set up the GoodSync to upload the scan data to file server everytime when the scanner is attached to the PC, this is a one-way sync








ODBC setting: the export files from TracerPlus we have predefined as CSV, therefore, we use the "MS Text Driver" for our ODBC System DSN, 

Once the ODBC ready, we need to decide how to connect to the target database to add the new S/N information. We should have a front-end process to filter the scan data make sure they are before we update the database.

I implemented three scheduled tasks by different tools which just wan to show you the basic concept to automate your inventory management -

• update MySQL S/N table by Using PHP and Cron jobs
  
• use Invoice# as key to update Quickbooks invoices and add the S/N information into Memo field by using QODBC, ASP and Windows scheduled tasks
  
• use Invoice# as key to update Lotus Notes S/N control database by using Lotus LEI (Lotus Enterprise Integrator)

On the following article, I will publish more details about how to implement these three data pumping tasks.

Migration From Lotus Notes/Domino to Google Apps

Google introduced the free Notes to Google Apps tool in July to reduce the migration pain for IT people, the Google Apps Migration for Lotus Notes is a native Notes database tool that migrates mail, calendar, contacts, and group information from your Notes accounts to Google Apps. It is very easy to install, all you need -

• Domino server 6.02 or higher
  
• Windows server 2000, 2003, 2008 or XP (only the server host the migration database need to be Windows-based, not the servers keep the mails)
  
• MS Core XML Services 6.0
  
• Google Apps Premier or Education Edition
  

You can download the software tool from Google site which includes two Notes application templates, sign them with administrator id and copy the feeder template to target server, and then create a Google Apps Administration database on target server based on the administration template. Following the manual to create the necessary documents.

There is a bug on current download (R2.0.9) - when creating a Site document the "Migration server" field is unable to enter any name! You will need to use the Domino designer to modify the "Site" form, under the Form editor, go to the "Servers" TAB, double click at "MigrationServer" field -





Click at "Control" Tab and Check the "Refresh fields on keyword change" options, save the change and you are good to go!

MySQL Nested Cursor Declaration Issue

Unlikes the Oracle PL/SQL which you can declare the CURSORs in the beginning of the program then execute the SELECT statement anywhere in the code body, MySQL needs to declare the CURSORs in the beginning of the program and applys the SELECT statement right behind the declaration. If you try to declare a Cursor in the middle of the codes, you will get -

You have an error in your sql syntax, check the manual that corresponds to your Mysql server version for right syntax to use .....

To avoid the problem, you can either issue a FUNCTION to handle the inside nested cursor statements or issue a nested BLOCK to declare the Cursor -

CREATE PROCEDURE FIND_ITEM()
BLOCK1: BEGIN
DECLARE done INT DEFAULT 0;

DECLARE XINVNO TEXT;

DECLARE cur CURSOR FOR
SELECT INVNO FROM TMP_SN5 WHERE TOT1=0 AND ITEMS='';
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN cur;
p_loop:LOOP
FETCH cur INTO XINVNO;
IF done=1 THEN
LEAVE p_loop;
END IF;

BLOCK2: BEGIN
DECLARE DIS_ITEM TEXT;
DECLARE XQTY INT;
DECLARE XITEM TEXT;
DECLARE done1 INT DEFAULT 0;
DECLARE cur2 CURSOR FOR
SELECT ITEM, QTYSHP FROM ARTRAN01 WHERE TRIM(INVNO)=TRIM(XINVNO);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1=1;
OPEN cur2;
SET DIS_ITEM='';
k_loop:LOOP
FETCH cur2 INTO XITEM, XQTY;
IF done1=1 THEN
LEAVE k_loop;
END IF;
SET DIS_ITEM = CONCAT(DIS_ITEM,  XITEM, ' (' , CONVERT(XQTY, CHAR), ') ') ; 
END LOOP k_loop;

CLOSE cur2;

IF DIS_ITEM<>'' THEN
UPDATE TMP_SN5 SET ITEMS=DIS_ITEM WHERE TRIM(INVNO)=TRIM(XINVNO);
END IF;
END BLOCK2;

END LOOP p_loop;
CLOSE cur;

END BLOCK1;

Set Up Lotus Domino 8.02 Partitioned Servers on Amazon EC2 - Part III

Once all notes.ini files have been updated, you need to restart the server to find out if -
1. the Windows services have been raised automatically ?
2. the attached volume is assigned to correct drive letter ?
3. each partitioned server is binding to correct IP and port ?

Instance Health Check -
It is recommended that you should use CloudWatch to monitor the instance's health condition($0.015 per instance-hour), you do not need to enable it all the time, however, try to use it to monitor the system during the high volume data exchange(replications).

I only use the Domino Cloud as the backup purpose, therefore, the replication will be the major loading for these partitioned servers. I had forced them to replicate big amount of data in the same time to watch the system performance. Most of the time the CPU is under 50% usage line -


Create Your Own AMI & snapshot -
To create your own AMI, you will need to bundle the instance first. Make sure you have a directory ready in S3 before you create the bundle task, otherwise, you will get an error at the end of the process. You can create a directory under S3 only for this bundle use, do not delete it unless you do not want to keep the AIM converted by this bundle.

The bucket name for bundle instance is the new directory name you created on S3.
Once the bundle is ready for use, you can register it as your own AMI. Again! This AMI only includes the C drive image, none of the D drive or your attached volume data will be store into!

Create a Snapshot of Volume -
The most important part for us is the datastore(data directories) - F drive. A snapshot of the volume represent a backup at that time. If you want to keep this instance alive, then you can write a script to do the backup automatically.
Amazon also offers different type of instance called Reserved Instance which you can only run it at specified period of time and stop the instance most of the time to save money.

---

Set Up Lotus Domino 8.02 Partitioned Servers on Amazon EC2 - Part I
Set Up Lotus Domino 8.02 Partitioned Servers on Amazon EC2 - Part II

Set Up Lotus Domino 8.02 Partitioned Servers on Amazon EC2 - Part II

Some Recommend Tools Beside AWS Management Console -
Firefox 3 or higher - to run add-ons like Elasticfox, S3Fox
Elasticfox - EC2 management tool
S3Fox S3 Organizer - S3 management
Ultra VNC Server - remote access through web browser

Once the instance launched, it takes about 10 - 15 minutes to be ready for first use. Follow the instructions to get the administrator password, then you have to use the MS Remote Desktop Connection to make your first contact -

the computer name field uses the "Public DNS" name of this instance. However, this is a fixed IP address, the address can be tell by the number after "ec2-". You can use this as the public IP address of this instance, as long as you do not attach an elastic IP, the address won't change till you terminate this instance.

You can use the MS Remote Desktop as the remote login client, however, a VNC-based remote control is recommended since you can use the web browser to do all the jobs. Especially, you might not be able to see the Domino console when using MS Remote Desktop. If MS RDC is your only choice, then you will need to use the Domino remote set up to install the Domino servers.

Install VNC Server
Launch IE, Download Ultra VNC latest version and install the VNC server, make sure you set the VNC password and check the "Enable JavaViewer (Http Connect). Make it as a Windows Service when pop-out windows ask so.
To access the instance through web browser, you just need to point URL to http://your instance public address:5800

S3 Utilities
The S3 account is necessary not only to store our own AMIs and snapshots, but to exchange programs and data between our network and EC2. The Domino server installation file can be uploaded to S3 then download from the instance machine. S3 Organizer add-on on Firefox is the tool we use -

New EBS Volume for Notes Datastore
Since we would like to keep the machine image even after instance terminated, we will need to attach a EBS volume for the data directory. The Disk Management program's "change drive letter" function is not working under EC2 Windows running instance, therefore, an utility program - ec2config need to be installed to make sure the volume we attach has correct drive letter we need (I used "F" as the data drive letter).

After the data drive has been created from the attached volume, create the necessary directories for each partitioned server, download the necessary Domino server installation file(mine is 8.02) and those partitioned server IDs from S3. Start the installation to install the program file at C:\Lotus\Domino and set correct partitioned server directories.

Once the installation finished, if you use VNC then you can click at the Domino server shortcut icon to start the setup process for each partitioned server, otherwise, you will need to use remote setup to complete the partitioned server setup which can be done by opening MS-DOS prompt, go to C:\Lotus\Domino directory then type
nserver "F:\partition server dir\notes.ini" -listen
then use one of the running Notes server run follwoing command -
nserver -remote
For detail Remote Domino server setup click here.


Currently, Amazon only offers one public IP for each instance, therefore, you can only enable following services for one of the servers - POP3, SMTP, IMAP, HTTP, HTTPS, LDAP. You still can have multiple HTTP services for these servers, however, you need to use different port numbers instead of port 80.

Modify notes.ini file on partitioned servers
After finishing all partitioned server setups, you need to modify the note.ini file to make sure they are binding to the correct IPs and ports. The one IP limit also forces you to use port mapping method to host multiple partitioned servers. Click here for detail port mapping setting. Again, each instance has one private IP and one public IP, you will need to use both IPs to configure the correct notes.ini file. Use the private IP to bind the socket when server start up, use the public IP for port mapping.

The notes.ini TCPIP setting on Port Mapping server (the 1st partitioned server)

notes.ini IP setting on other partitioned servers

---

Set Up Lotus Domino 8.02 Partitioned Servers on Amazon EC2 - Part I
Set Up Lotus Domino 8.02 Partitioned Servers on Amazon EC2 - Part III

Set Up Lotus Domino 8.02 Partitioned Servers on Amazon EC2 - Part I

a snapshot of 4 Domino servers running on 1 Amazon EC2 instance
The purpose of this practice is to find a reliable(over 99.9% up time) and cheap back-up/disaster recovery plan for Lotus Domino systems.

You must have an Amazon account to start your own EC2 and S3 account. Go to Amazon web site, click at "For Developers" at left hand bottom, then click at "Amazon Web Services". Once you get into the Amazon Web Services page, sign up for a free AWS account. Then click at "AWS Management Console" to start your EC2 own journey. Couple hours' digging will only cost you less than $2 (a Windows 2003 instance cost $0.125/hr), so do not afraid to try anything new!

IBM does offer some AMIs on Amazon EC2. However, there is no Lotus Domino pre-installed AMI, therefore, we have to build it from the ground. I will use Windows 2003 server for this practice, I did try the Cent OS 5.3 which was also working fine.

We would like to build four partitioned servers on a single instance, since one instance can only attache one IP, therefor, we will need Port Mapping to share this IP.

What we need -
Basic Microsoft Windows Server 2003 (AMI Id: ami-de4daab7)
100GB EBS Volume for datastore
1 Elastic IP(optional)
Domino Server 8.02



We pick the Small Instance (m1.small) which has
1.7 GB memory
1 EC2 Compute Unit (1 virtual core with 1 EC2 Compute Unit)
160 GB instance storage (150 GB plus 10 GB root partition)
32-bit platform

If you plan to save the image for future use (your own AMI)then don't bother to use the 150GB D drive at all, since the data on D drive won't be saved when creating new image from this instance. Therefore, we need a datastore(a new Volume) created from EBS, I set the size at 100GB according to my case, try not to use the size much bigger than you need because you need to pay for the storage too!

Security Setting -
Before creating your first instance you need to create a new Security Group (mine named W2K3) add some more ports to fit our instance needs


port POP3, IMAP, SMTP, HTTP, HTTPS ports open or not depend on your applications
Port 1352 is the Domino default port
port 1353 - 1356 used by partitioned servers which need port mapping
port 8585 for Domino server remote setup
port 5800/5900 for VNC server
port 3389 for Windows Remote Desktop

You can launch instance now!

---

Set Up Lotus Domino 8.02 Partitioned Servers on Amazon EC2 - Part II
Set Up Lotus Domino 8.02 Partitioned Servers on Amazon EC2 - Part III