Wednesday, December 16, 2009

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.

    Monday, November 9, 2009

    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!



    Thursday, October 15, 2009

    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;
    

    Saturday, September 26, 2009

    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

    Friday, September 25, 2009

    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

    Thursday, September 24, 2009

    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

    Wednesday, May 27, 2009

    Yeah! I Got My Free Google ION !



    Since my JetBlue mileage free ticket will soon expire and I am also working on switching some of our Lotus Notes applications to Google App which gave me a very good excuse to fly to San Francisco to attend the Google IO 2009. My only interest here is Google App, didn't have any intention to get involved in the Android developing. However, after Google gave me this Google Ion( HTC Magic) with 30-day free voice and unlimited 3G data, I am thinking about picking up some Android sessions .....

    N.....no! Better just post it on ebay to make up some of my traveling expense! :-(

    Friday, May 22, 2009

    X60 goes internet thru HTC FUZE 3G

    Got my AT&T FUZE couple days ago, since I will be on the road very soon, just want to make sure the 3G data network will allow my laptop to access internet by "sharing the internet" function to save some of the hotel internet surcharges. However, after I connected it thru USB with my IBM X60, it kept giving me - "Cannot Connect. The Remote party has ended this connection...." error message!?

    After Googleing -

    First,need to download the REGEDIT program for the Windows Mobile 6.1, which can be found at
    http://www.phm.lu/Products/PocketPC/RegEdit/

    pick "PocketPC 2002/2003 (ARM/PXA)

    Modify [HKEY_LOCAL_MACHINE\Comm\InternetSharing\settings]
    original setting is "AT&T ISP". Change it to "Media NET"

    Restart the Windows Mobile, connect to laptop thru USB, choose "Internet sharing" when connection windows pop-out, make sure your 3G data network is ON.

    My laptop started surfing the internet thru 3G!!! Tried couple YouTube videos, speed is pretty good, no delay at all!

    Thursday, April 30, 2009

    How To Pass ASPX Page Control Parameter to Master Page Javascript Function

    Microsoft introduced the Master Page concept at .NET 2.0 which supports developing a common look and feel across the entire web site. However, there is no directly way to pass the value from the web pages controls to master page' javascript section. I have tested a simple example for me to do it easily -

    The codes below passing the value of FormView control (FormView1) field named "EMAILTextBox" to Label1 at Master page, then the Javascript function pfb() retrieve the value from the Label field - "Label1"


    the aspx page -
    <%@ Page Language="vb" AutoEventWireup="true" <span style="color: rgb(255, 0, 0);">MasterPageFile="~/MasterPage.Master"</span>
    title="JUST A TEST" %>
    <span style="color: rgb(255, 0, 0);"><%@ MasterType VirtualPath="~/MasterPage.Master" %></span>
    
    <script runat="server">
    Sub Page_Load(ByVal Sender As Object, ByVal e As System.EventArgs)
    
    Dim mtb As Label
    Dim tb As TextBox
    tb = FormView1.FindControl("EMAILTextBox")
    mtb = Master.FindControl("Label1")
    mtb.Text = tb.Text
    
    End Sub 
    </script>
    <asp:content id="Content1" contentplaceholderid="head" runat="server">
    <asp:formview id="FormView1" runat="server" datasourceid="SqlDataSource1" defaultmode="Edit">
    <edititemtemplate>
    LASTNAME:
    <asp:textbox id="LASTNAMETextBox" runat="server" text="'<%#">' />
    
    
    FIRSTNAME:
    <asp:textbox id="FIRSTNAMETextBox" runat="server" text="'<%#">' />
    
    
    OPHONE:
    <asp:textbox id="OPHONETextBox" runat="server" text="'<%#">' />
    
    
    EMAIL:
    <asp:textbox id="EMAILTextBox" runat="server" text="'<%#">' />
    
    
    <asp:linkbutton id="UpdateButton" runat="server" causesvalidation="True" commandname="Update" text="Update">
    <asp:linkbutton id="UpdateCancelButton" runat="server" causesvalidation="False" commandname="Cancel" text="Cancel">
    </edititemtemplate>
    ..................... codes ........................
    </asp:Content>
    

    master page (MasterPage.Master) -
    <%@ Master Language="VB" AutoEventWireup="false"  Inherits="JustATest.MasterPage" %>
    
    <script language="javascript" type="text/javascript">
    
    function pfb(){
    var data1;
    document.write(<span style="color: rgb(255, 0, 0);">document.getElementById("<%= Label1.ClientID %>").innerText</span>);
    }
    </script>
    
    
    .............
    <asp:contentplaceholder id="ContentPlaceHolder1" runat="server">
    
    </asp:contentplaceholder>
    

    Sunday, March 29, 2009

    Kiss My Yahoo Briefcase Goodbye!

    Even though there are two more days for Yahoo Briefcase to live, Yahoo just could not wait to remove the icon from my Yahoo Toolbar. Anyway, I have relocated all my stuffs to MSN Skydrive, this place is no longer must-have for me, just feel kind of sad to see it disappearing.

    Like the most of the free internet storage service, Yahoo Briefcase has become the latest falling star! Xdrive, briefcase.com ..... most of the free internet storages I used are RIP now! Yahoo Briefcase is the one offered the least storage -30MB, however, for me, it is the one being last longest and being used almost everyday!

    Good-bye! My Dear Yahoo Briefcase!

    Friday, March 27, 2009

    iNotes and Offline Gmail

    I have been using Gmail from the very early beta stage, The biggest incentive was the big volume it offered and the superb spam filtering performance! And now it even comes withe offline function!!! After using the offline Gmail for past couple weeks , I am so amazed by its performance! It is almost doing 100% Lotus iNotes can do except the contacts, calendar not support offline function, but thats good enough for what I need so far.

    Wednesday, March 25, 2009

    Explode A BOM with Tree View

    To generate a Tree View BOM (Bill Of Materials) for users easy to explore the details. The tree level can be up to 8. The statement also brings in the description, Unit of Meansure, Vendor, Vendor P/N from other tables.

    Four Oracle tables -
    BOM -
    BOMNO : BOM P/N
    COMNO: Component P/N
    QPS: QTY per Assembly

    IM -
    ITNO: P/N
    DESCRIP: item description
    REV: revision
    SCODE: S code

    IM2
    ITNO: P/N
    UOM: Unit of Measure

    VDPN -
    ITNO: P/N
    VNCRMNAM: Vendor name
    VENPN: Vendor P/N

    SQL Statement -
    select P1, P2, P3, P4, P5, P6, P7, P8, qpa, qta, descrip, uom, rev, scode, VC, (case when VNCRMNAM is NULL then ' ' else VNCRMNAM end) MFR, (case when VENPN is NULL then ' ' else VENPN end) VPN from
    (select (case when lev=1 then comno else ' ' end) P1, (case when lev=2 then comno else ' ' end) P2, (case when lev=3 then comno else ' ' end) P3,
    (case when lev=4 then comno else ' ' end) P4, (case when lev=5 then comno else ' ' end) P5, (case when lev=6 then comno else ' ' end) P6,
    (case when lev=7 then comno else ' ' end) P7, (case when lev=8 then comno else ' ' end) P8, lev, qpa, DECODE(LEV,
    1, QPA,
    2, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, LENGTH(QT) - INSTR(QT, '*', 1, 2))),
    3, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *
    TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, LENGTH(QT) - INSTR(QT, '*', 1, 3))),
    4, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *
    TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, INSTR(QT, '*', 1, 4) - INSTR(QT, '*', 1, 3)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 4) +1, LENGTH(QT) - INSTR(QT, '*', 1, 4))),
    5, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *
    TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, INSTR(QT, '*', 1, 4) - INSTR(QT, '*', 1, 3)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 4) +1, INSTR(QT, '*', 1, 5) - INSTR(QT, '*', 1, 4)-1)) *
    TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 5) +1, LENGTH(QT) - INSTR(QT, '*', 1, 5))),
    6, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *
    TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, INSTR(QT, '*', 1, 4) - INSTR(QT, '*', 1, 3)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 4) +1, INSTR(QT, '*', 1, 5) - INSTR(QT, '*', 1, 4)-1)) *
    TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 5) +1, INSTR(QT, '*', 1, 6) - INSTR(QT, '*', 1, 5)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 6) +1, LENGTH(QT) - INSTR(QT, '*', 1, 6))),
    7, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *
    TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, INSTR(QT, '*', 1, 4) - INSTR(QT, '*', 1, 3)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 4) +1, INSTR(QT, '*', 1, 5) - INSTR(QT, '*', 1, 4)-1)) *
    TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 5) +1, INSTR(QT, '*', 1, 6) - INSTR(QT, '*', 1, 5)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 6) +1, INSTR(QT, '*', 1, 7) - INSTR(QT, '*', 1, 6)-1)) *
    TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 7) +1, LENGTH(QT) - INSTR(QT, '*', 1, 7))),
    8, TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 1) +1, INSTR(QT, '*', 1, 2) - INSTR(QT, '*', 1, 1)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 2) +1, INSTR(QT, '*', 1, 3) - INSTR(QT, '*', 1, 2)-1)) *
    TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 3) +1, INSTR(QT, '*', 1, 4) - INSTR(QT, '*', 1, 3)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 4) +1, INSTR(QT, '*', 1, 5) - INSTR(QT, '*', 1, 4)-1)) *
    TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 5) +1, INSTR(QT, '*', 1, 6) - INSTR(QT, '*', 1, 5)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 6) +1, INSTR(QT, '*', 1, 7) - INSTR(QT, '*', 1, 6)-1)) *
    TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 7) +1, INSTR(QT, '*', 1, 8) - INSTR(QT, '*', 1, 7)-1)) * TO_NUMBER(SUBSTR(QT, INSTR(QT, '*', 1, 8) +1, LENGTH(QT) - INSTR(QT, '*', 1, 8)))
    ) QTA,
    descr AS descrip, uom, rev, scode, (case when vcode IS NOT NULL then vcode else ' ' end) VC from
    (select SYS_CONNECT_BY_PATH(COMNO,'-->') TREE, SYS_CONNECT_BY_PATH(TO_CHAR(QPA),'*') QT, CONNECT_BY_ROOT(COMNO) ROOT, Level As lev, qpa, comno, LPAD( COMNO, LEVEL + LENGTH(COMNO), '^') PART
    from BOM start with bomno=BOM_NEED_EXPLODE connect by prior comno=bomno ), IM, IM2
    where comno=im.itno AND comno=im2.itno
    order by tree) LEFT JOIN (select itno, vncrmnam, venpn from
    (select itno, vncrmnam, venpn, row_number() over (partition by itno order by vncrmnam, venpn) RK
    from VDPN)
    where RK =1 AND LENGTH(TRIM(vncrmnam || venpn)) > 0)
    ON TRIM(P1 || P2 || P3 || P4 || P5 || P6 || P7 || P8) =TRIM(ITNO)
    

    Tuesday, March 24, 2009

    Forest & Trees RIP?




    Forest & Trees is a data analysis software created by Platinum Technology back in 1995, it was one of the most powerful reporting and dashboard software in the market then. Unfortunately, the company was purchased by Computer Associates (CA) in 1999 and the software was renamed to CleverPath Forest & Trees, and like most of the S/W purchased by CA, either they died very quick or just slowly fading away! The current version of the F&T is V7.1, and the basic functions are pretty much the same as the V5.0 which was released in 1999! that means CA didn't even bother to improve it after take-over.

    If F&T didn't sell itself to CA in 1999, maybe it would have become a nice size BI software company and IBM might has purchased it instead of Cognos!

    F&T is still my favor reporting/dashboard s/w, I use it for most of my data analysis projects involved with Oracle, Excel, ODBC and Lotus Notes.

    Following are my wish lists for future F&T functions, I hope CA can offer them and make this S/W more competitive!
    1. front-end formula and query language supports global dynamic variables (current version's variables need to specified at certain section)
    2. supports a basic back-end script language (like the SQL stored procedure language)
    3. supports the database update functions (current version only supports READ)

    Monday, March 23, 2009

    LEI 7.0.1

    Due to the upgrade of our main Domino servers from 6.5 to 7.03, I decided to upgrade our LEI too. It was a LEI 6.5 running on a dedicated machine w Notes 6.5 client. The first trial was a LEI 7.0 running on a Domino 7.03 server as a task, I was expecting a better records copy/replication performance which turned out to be a totally disappointed.


    LEI PUMPING SPEED TEST
    Direct Transfer (Oracle2 to Oracle1) 1,000 records/min (each record has 90 columns)
    Direct Transfer (Domino to Oracle1) 12,000 records/min (each record has 25 columns)
    Replication (Oracle1 to Domino)
    Replication(Oracle2 to Oracle1) 1,000 records/min (each record has 25 columns)

    Oracle1 and Domino are under same 100M LAN
    Oracle2 connected thru WAN by 2 T1

    Compare with SQL Loaded used on Oracle1, the pumping speed -
    1,000 records/sec with 13 columns
    2,500 records/sec with 3 columns


    The LEI 7.0 has a big bug - the daily scheduled task only run every other day! I had to switched to LEI 7.0.1 to had it fixed. This is a terrible product release Quality Control, and it happened on the IBM's product!

    For database has records fewer than 100K, LEI is not a bad choice, however, if you have millions records need to upload or replicate, just forget about LEI. I believe the problem come from the LSXLC which has very poor data processing performance! Even ODBC driver has much better performance. Maybe it's time for IBM to rewrite the LSXLC.

    Saturday, January 10, 2009

    Using Google Apps As Your Lotus Domino Email Spam Filter



    Spam is always the biggest headache for system administrators, and the situation is getting worse for us. We have been using DNS Blacklist and Symantec Mail Security as our Domino servers(6, 7 and 8) anti-spam solution, however, the junk mails are still overloading everybody's inbox!

    I have been a GMail user since Google released it, I am so impressed by its anti-spam engine which is based on the Postini technology. I want to use this filter so bad and the great news is - if you do not want to pay the $12/user/year, you still can get it for free - Google Apps Standard Edition!

    Here is how -
    1. prepare two domain names, first one is your current email domain name (like yourcompanyname.com) which email will be hosted by Google and the second one is for the Notes server (like yourcompanyname.net)
    2. sign up with Google Apps
    3. verify your first domain name (yourcompanyname.com) ownership by creating a CNAME record on your DNS server
    4. update your MX records per Google instructions
    5. create the administrator account under Google Apps, and then create users one by one make sure they have the same id(short name) and password as Notes
    6. log in to activate indivual user email, enable email forwarding to notes email which using the second domain name, for example userid@yourcompanyname.net
    7. on the Domino server Global Domain document adds the second domain (yourcompany.net ) under "Local primary Internet domain" field
    8. on the Domino server configuration document, the section "Inbound Connection Control" needs to enable "verify connecting hostname in DNS" and add "google.com" at "Allow connections only from ..." field
    9. on the Domino Person document adds the userid@yourcompany.net at Short name field and the internet address field still keep the same userid@yourcompany.com