Saturday, July 26, 2014

IBM i transfer Convert UNICODE to CHAR

For the past couple of years I have noticed when transferring a file to IBM i from Excel the default field type is UNICODE. That caused issues with data after transfer so I have been manually changing them during file transfer definition process to CHARACTER.

The spreadsheets had few fields so not much of a pain and I did not look for a reason why this is happening. I am now working with a spreadsheet that has 38 columns to be transferred and changing from UNICODE to CHARACTER has become a pain.

Fifteen minutes of Google search and problem solved. With Google the answers are always only a few keystrokes away!

Microsoft Excel Data Transfer Add-Ins: Convert UNICODE to CHAR

The command did return something about failed but it did just what was needed. Not sure what the failed message is for.

When the world says, "Give up,"
Hope whispers, "Try it one more time."
~Author Unknown

Have your best day!


Tuesday, May 20, 2014

Whoohoo!! Factory released KitKat 4.4.2 is installed and rooted!!!!!

Finally after many months of waiting and not really knowing if Samsung would update my beloved Galaxy Note 8” 3G GT-N5100 phone. 

Now I'm rocking 4.4.2, yeah baby!!

A simple process I followed at International Business Times ran smooth. I had a number of apps that did not update and received many failed to load messages when I restarted. All were fixed by uninstalling and reloading from play store.  All except Amazon Kindle, of course I am deep into a book I would like to finish. I have not found a fix as of yet but will chase it down this evening.

Also ran root as well from here

Thursday, February 20, 2014

EDI Invoicing

Another tough day ahead with EDI Invoicing. When the going gets tough the tough get going. We will overcome!

Monday, January 13, 2014

Welcome Aboard!

Today most excellent RPGLE programmer and good friend Rick Santiago starts as Senior Programmer/Analyst at Capitol Lighting.

Rick and I first met at GE Aircraft Engines back in 1996. Since then I have sub-contracted Rick to do my "heavy lifting" RPGLE programming at every job I have worked at. Every single one was a huge success.

At Pylon Manufacturing we created awesome "Pick to Pallet" process and modified BPCS order ingestion program to incorporate Extol EDI to automate inbound processing.

At Quipp Systems we created Payroll time tracking and really wild commissions program utilizing MAPICS.

At NanjingUSA Rick assisted in several projects which included implementation of S2K ERP and automated EDI process.

The next five years are going to be really exciting. We now work together at Capitol Lighting and sky is the limit. Capitol is dedicated to the IBMi and is looking to us to help integrate technology into operations as Capitol grows to 100 million dollar sales mark. This is a terrific challenge and opportunity for us and Capitol Lighting.

We are IBMi, watch us grow!


Tuesday, August 6, 2013

We're not in Kansas anymore....

Web Services, SOA, SOAP, HTTP, C++, WSDL, DISCO, Lions, Tigers and Bears. I’m used to diving into the deep end of the pool but after two days I think I have fallen into the Marianas Trench still waiting to hit bottom.

My current challenge is to send Name, Order and Email for new orders to LISTRAK Email Marketing Services utilizing API and SOAP formatted XML from our IBMi.

A little lite reading.....


If we are facing in the right direction, all we have to do is keep on walking.  ~Buddhist Saying

Monday, May 27, 2013

Whooohooo! IBMi V7R1 installed and working with no problems.

Good to be back and up to date, was at V5R4 and I was missing some of the enhancements that were in V6R1. I upgraded several machines three years ago to V6R1 and did not like going back and working with V5R4.

Since I am now essentially a one man IT shop (software/IBMi) and buried deep in fixing and supporting a failed ERP implementation I outsourced the upgrade to Source Data Products. Over the years I have done about 15 installs/upgrades but has never been my focus and it takes planning to do it right. I did not have the time to plan and do the upgrade myself.

Bob and Mark did a fantastic job. Bob from the standpoint of providing a price that could not be beat and Mark for performing a flawless upgrade. As a bonus he configured ASMI port. Now I have full remote control access to the IBMi. From a powered off status I can start machine without manual intervention at the CO-LO and see status of machine before IPL.

Total time for upgrade excluding software downloads and third party software upgrades was around 7 hours. One small bump with credit card processing software, they missed sending be the bulletin to change JAVA path. IBM changed the JAVA path to QOPNSYS which is where it should have been in the first place.

For under $3k Source Data Products cannot be beat. Now I have to quickly get up to speed with RDi Development Tools and V7R1 enhancements. Can't wait to utilize RUNSQL in CLLE, SDA and RLU in RDi.

Thursday, February 21, 2013

SQL Update a Multi-Table View using INSTEAD OF Triggers

Yesterday I added another SQL trick to my bag ‘o tricks. I am currently working on fixing EDI invoicing process and I needed a way to delete records from four files based on the same key. I could have wrote RPGLE Free Embedded SQL that would have been about 60 lines of code but wheres the fun in that.

I popped open the Google window and searched on “iseries sql delete from multiple tables”. the second hit directed me to IBM and the fourth to Preform the Impossible: Update a Multi-table View using INSTEAD OF Triggers. What did we did before Google, oh yeah, read a manual!

Impossible, that’s for me. It took me a few minutes to understand and a peek at IBM documentation I had to create a view and then the trigger. Once created any time I delete from the view all associated records in the four files are deleted. Very cool.

After view and trigger created I can run this SQL interactive or embedded.

delete from yourlib.ediinvw
where ihedoc = 0000046475

My new view is EDIINVW and I am deleting by EDI document control number.

To accomplish this I created the view with the below code using SQL Explorer.

create view yourlib.ediinvw as
select ih.ihedoc, id.idedoc, ss.iaedoc, si.isedoc
from yourlib.vedinih ih
join yourlib.vedinid id on id.idedoc = ih.ihedoc
join yourlib.vediniss ss on ss.iaedoc = ih.ihedoc
join yourlib.vedinis si on si.isedoc = ih.ihedoc

The four fields in the Select statement is the key field for each of the four files.

I then attempted to create the delete Trigger. SQL Explorer did not like the syntax so I copied and pasted into the green screen iSeries SQL interactive command line. My guess is that SQL Explorer has an issue with INSTEAD OF DELETE ON.

create trigger yourlib.ediinvdel
Instead of delete on yourlib.ediinvw
referencing old as EdiInvwOld
for each row mode DB2SQL
begin atomic
delete from yourlib.vedinih
where ihedoc = EdiInvwOld.ihedoc;
delete from yourlib.vedinid
where idedoc = EdiInvwOld.idedoc;
delete from yourlib.vedinid
where iaedoc = EdiInvwOld.iaedoc;
delete from yourlib.vedinid
where isedoc = EdiInvwOld.isedoc;

I learned another very cool SQL trick, Thanks IT JUNGLE and Four Hundred Guru. This will come in handy over the next few weeks as I fix the EDI processes.

Have your best day!


In programming, as in everything else, to be in error is to be reborn.  ~Alan J. Perlis