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.