Author Archives: PHPDave

Data Integrations thoughts

Data Tranformations:
1. merge – combining multiple data sets into a single data set (sql merge)
2. aggregation – combining data to show summary views (OLAP – min, max, avg, etc… sql group by)
3. enrichment – taking a master file and joining additional details from another source that’s believed to have trust worthy information to gain deeper insights
4. summarization – readily digestible information to help make decisions
5. filtering – getting just the records you want to focus on – where clause of a sql statement

What is a triggered ETL process
Triggered – an event such a recurring job or an event triggers a ETL process
Extract – extracting data from source system
Transformation – transforming the data fields (data types and values) and record format
Load – loading the data into a target system

Ways to Move files between 2 IBM is

Pase Shell

  1. rsync
  2. scp -r -3 me@host1:/www/copyme/ me@host2:/www/

iSeries Navigator for windows

  1. Copy and Paste, or Drag and Drop between IBM i’s (sometimes causes sys i to crash)


  1. FTP


  1. WinSCP,

SSH Mounting w/ local computer

  1. sshfs

Thanks to @jjglyph for the scp idea and @jordiewies for the FTP idea and @theprez for winscp and sshfs.  Its good to know multiple ways

Giving your application eyes ūüĎÄ – Inspired by Common Conference’s talk about Watson

At common this year they seemed to want to expand our usage of tools that require Machine Learning or advanced algorithms that take a long time to come up with and may require a lot of processing power to accomplish.  Therefore you send your data offsite via an API to Watson and get back information about your data.  So below are some things I played with.

Given this recent tweet:

Lets see what we could do.  We could have an application that scans a certain hashtag or user and sends the images that person or topic posts about to a cloud service to find out what these services think about it.

Watson – BlueMix – Visual Recognition Demo

Below are the results of submitting this image to Watson and what it thinks about the image. ¬†The first result doesn’t make much sense to me, I’m not sure what a reformer instrument is… but the pommel horse and the gymnastic apparatus are clearly related to what Aaron is attempting. ¬†The 5th and 6th result seem to do with the cover of his computer. ¬†What could we do with this information? ¬†Perhaps you could use it to auto-classify images of an conference without any human resources involved. ¬†So your attendees could see images of things they might find interesting and the photographer only has to worry about snapping interesting photos and not have to worry about the dreaded work of organizing photos.


Dec 2019Screen Shot 2019-12-14 at 10.55.41 AM.png

Google Cloud Vision API

As a developer we can’t sit on 1 person or companies view of the IT World. ¬†Therefore I decided to check out what Google’s Cloud vision API saw. ¬†It knew Aaron was human, in a room and doing “physical fitness”. ¬†All very relevant to the picture

May 2017

Screen Shot 2017-05-11 at 10.02.30 PM.png

Dec 2019

Screen Shot 2019-12-14 at 10.35.33 AM.png

It also gave us a what the dominant colors are of the picture and where we could crop the image

Screen Shot 2017-05-11 at 10.03.02 PM.png

One other thing it can provide us is if the image is “safe” to make sure obscene images are not used

May 2017Screen Shot 2017-05-11 at 10.03.09 PM.png

Dec 2019

Screen Shot 2019-12-14 at 10.36.45 AM.png

Web relation

Dec 2019

Screen Shot 2019-12-14 at 10.39.25 AM.png


Dec 2019

Screen Shot 2019-12-14 at 10.40.20 AM.png

Azure Dec 2019Screen Shot 2019-12-14 at 11.02.42 AM.png

Screen Shot 2019-12-14 at 11.09.00 AM.png

Screen Shot 2019-12-14 at 11.09.21 AM.png


Tesseract Open Source OCR Engine

Lets say you don’t wait to give your data over to IBM or Google you could look into other repositories that allow you to do the algorithms locally such as tesseract that can be used for OCR. ¬†I’m not sure what other libraries are available for doing the other things that Watson and Google Cloud Vision provide, but I believe the open source community will be coming up with intuitive ideas and perhaps searching more we could find some libraries that can do what IBM and Google are doing.

Round 2

Lets see how these services work with a different photo with text and faces turned directly to the camera.


Can see two males that are 18-24 years old and believes someone is their little brother and someone is their father and perhaps someone is wearing a wet suit (watson will need to improve on picking up skinny jeans.)

Screen Shot 2017-05-11 at 10.24.21 PM.png

Dec 2019

Screen Shot 2019-12-14 at 10.54.37 AM.png

Google’s turn

Now what Google was able to do was pretty crazy and I was wondering why Watson isn’t doing this or perhaps it will catch up to google after they have a big enough data set and a proper ML model.

Google was able to identify that the picture had faces expressing Joy! and thinks there’s possibly headwear but there wasn’t. ¬†It also new the roll, tilt and pan of the image which could be used to autocorrect the image to align correctly. ¬†It new the image was taken from twitter and that they were wearing a t-shirt and were having a “Good Time”. ¬†It also had links to web pages where this image could ¬†be found. ¬†It was able to pick up the text on the shirt, and on the marketing displays in the expo. ¬†It could even separate the text into the separate entities in the picture ( the shirt text, the 2 marketing display texts). ¬† It even could keep the text together even though the “o” in “Power” was hidden by some dangling glasses. ¬†It also new that it was a safe image

Screen Shot 2017-05-11 at 10.22.15 PM.png

May 2017

Screen Shot 2017-05-11 at 10.22.23 PM.png

Dec 2019

Screen Shot 2019-12-14 at 10.48.20 AM.png


May 2017

Screen Shot 2017-05-11 at 10.22.35 PM.png

Dec 2019

Screen Shot 2019-12-14 at 10.49.10 AM.png


May 2017

Screen Shot 2017-05-11 at 10.22.44 PM.png

May 2017

Screen Shot 2017-05-11 at 10.22.51 PM.png

Dec 2019

Screen Shot 2019-12-14 at 10.50.11 AM.png

Screen Shot 2017-05-11 at 10.23.16 PM.png

NEW 2019 Objects

Screen Shot 2019-12-14 at 10.47.38 AM.png

Azure Dec 2019

Screen Shot 2019-12-14 at 11.03.25 AM.png

Screen Shot 2019-12-14 at 11.07.14 AM.png

Screen Shot 2019-12-14 at 11.07.21 AM.png


This is the future like it or not, and there’s some crazy applications that we can create when we combine the power of ML of both IBM, Google and open source projects ran locally. ¬† I sort of which we could put pandora’s box away but its already open so we can’t bury our heads in the sand and should start looking into how we can use this innovation for the good of humanity.



Whats going to change when I update PHP on IBM i. PHPinfo is a clue

e Here’s a great little script to see whats going to change when you update PHP on IBM i. ¬†It compares the phpinfo from your production site to your new instance of PHP. ¬†This way you can scroll down and take note of specific differences to PHP. ¬†This isn’t all the changes so there’s more to check but can get you pretty far.

There is an issue with library lists if your using the PDO_IBM driver.

Disable Z-Ray on PHP on IBM i
Edit the file /usr/local/zendphp7/etc/conf.d/zray.ini and change zray.enable=1 to zray.enable=0

; Enable Z-Ray
; zray.enable=1

Quickly install ZS9.1 PHP 7.1.3 on your IBM i

I created this simple short shell script to download the current version of Zend Server for IBM i. ¬†Unzip the downloaded zip file and then securely copy the save file to the savf ZENDPHP7 in the QGPL library. ¬†Then we send a remote system command to submit a job to batch to silently restore the savf file. ¬†You’ll have to wait for the batch job to run but afterward it finishes ZS9.1 will be installed in /usr/local/zendphp7

#get the shell script from the gist
#modify the user name and IBM i name
#Add execute permission to the owner of the file
chmod o+x
#run the shell script
./ Source

Automating deployment of IBM i SQL Database changes to a remote system

If you ever wanted to automate your SQL changes to a remote systems here’s one way of accomplishing that.

First you’ll have to ¬†manually create two local sql files that have the database SQL that you would like to run. ¬†The 1st file up{version#}.sql¬† will be ran when you want to promote your code, and the 2nd file down{version#}.sql will be ran when you want to rollback your changes. ¬†The version# allows you to¬†keep a history of what changes were made each time you this or you could tie it to source control commit#/version#/tag etc.. use whatever helps you.

Now that we have these two files we have to securely send them over to our IBM i.  Simply run this command on your local computer in a shell.  If your on windows install this so you can run it (

scp -r "/sqlsource/migrations"

This will recursively copy the local directory /sqlsource/migrations to the remote system in the directory /sqlsource/migrations .  This is the directory that must contain our up and down sql scripts.

Now that we have the source on the remote system we need to¬†create two local shell scripts that we’ll send to the remote system and call to promote or rollback the sql. ¬†Create the two files below on your local machine:

system -i "RUNSQLSTM SRCSTMF('/sqlsource/migrations/up1.sql') COMMIT(*NONE) NAMING(*SQL)"

system -i "RUNSQLSTM SRCSTMF('/sqlsource/migrations/down1.sql') COMMIT(*NONE) NAMING(*SQL)"

We now have the source on the remote server, and 2 local shells scripts that we can send to the remote system. ¬†To actually send the remote command we’ll use this command below on our local machine to send the promotion shell script to the remote system and it’ll execute immediately the shell script that was sent over

ssh <

If we need to rollback the changes we’ll just use this command:

ssh <

I’ve kept this example pretty basic, but you can get more extensive by sending parameters to your shell scripts to dynamically tell it what to do. ¬†For example you might want to pass it the environment, the user, the server and this is how you can do that

./ dev myuse

#!/bin/bash dev 1205 1206
scp -r "/sqlsource/migrations" ${DEPLOYMENTUSER}@${ENVIRONMENT}-${DEPLOYMENTSERVER}:/sqlsource/migrations