Category Archives: Developer Tools

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 (http://cmder.net/)

scp -r "/sqlsource/migrations" myuser@mysystem.example.com:/sqlsource/migrations

This will recursively copy the local directory /sqlsource/migrations to the remote system mysystem.example.com 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:

PromoteSQL.sh

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

RollbackSQL.sh

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 myuse@mysystem.example.com < PromoteSQL.sh

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

ssh myuse@mysystem.example.com < RollbackSQL.sh

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

./promoteAndExecuteSQL.sh dev myuse mysystem.example.com

PromoteAndExecuteSQL.sh

#!/bin/bash
#ZZZPromoteCodeAndDB.sh dev 1205 1206
ENVIRONMENT=${1}
DEPLOYMENTUSER=${2}
DEPLOYMENTSERVER=${3}
echo ${ENVIRONMENT}
echo ${DEPLOYMENTUSER}
echo ${DEPLOYMENTSERVER}
scp -r "/sqlsource/migrations" ${DEPLOYMENTUSER}@${ENVIRONMENT}-${DEPLOYMENTSERVER}:/sqlsource/migrations
ssh ${DEPLOYMENTUSER}@${DEPLOYMENTSERVER} < PromoteSQL.sh
Advertisements

Run Linux commands on Windows via cmder

I know a lot about Linux commands and run them on Linux and IBM i, but never found a tool that could do it on windows until today.  If you have to run on windows and would like access to :

  • ls, grep, cat, tail, vim, ssh, scp, phpcli (via setting your windows path), etc..
  • history of commands
  • color coded screen
  • full screen

You can download cmder @ http://cmder.net/

I guess I won’t need Putty or Google’s ssh client app anymore and goodbye cmd.exe.

Shout out to @tweetjbh for recommending it

New downlaod for #IBMi Access Client Solutions released. (Data transfer, 5250)

IBM just released a new Access Solutions IBMiAccess_v1r1m4_Update2.zip on July 16!

1. You can download it here:

ftp://public.dhe.ibm.com/as400/products/clientaccess/solutions/updates/

2. unzip it

3. run the script in the corresponding OS folder (Linux_Application,Mac_Application,Windows_Application)

4. click the desktop shortcut or use the hotkeys CTRL+ALT+SHIFT+A, CTRL+ALT+SHIFT+B

PHP Remote Debugging on Zend Server for IBM i using xdebug and Netbeans

This is a guide on how to setup xdebug on Zend Server for IBM i to use with any IDE w/ a xdebug client such as Netbeans.

Step 1  Get xdebug.so and put it on the IBM i IFS

Zend Server usually already has the file so just check that its in the

/usr/local/zendphp7/lib/php_extensions

If its not there, you can either compile your own xdebug.so by downloading the source from http://xdebug.org/ or you can just download this xdebug.so file.   Once you have the .so file you upload it to the IFS directory above.  Note on older versions of ZS the location might be:

/usr/local/zendsvr/lib/php_extensions

/usr/local/zendsvr6/lib/php_extensions

Step 2 – Setup php.ini file and other config files

You’ll also have to set

output_buffering = Off

in the php.ini.  The default for this is output_buffering=4096.  Make sure the default is commented out.

PHP.ini location: /usr/local/zendphp7/etc/php.ini

Disable Zend Debugger and Zend Optimizer

You’ll have to turn off Zend Debugger and Zend Optimizer as these files will interfere with xdebug.  To turn off this go to /usr/local/zendphp7/etc/conf.d/debugger.ini and comment out

; zend_extension_manager.dir.debugger=/usr/local/zendphp7/lib/debugger

with  a semi colon at the beggining of the line.  In older versions of Zend Server you may have to comment out optimizerplus.ini in the same directory

;zend_extension_manager.dir.optimizerplus=/usr/local/zendphp7/lib/optimizerplus

in the xdebug.ini file in the directory /usr/local/zendphp7/etc/conf.d modify it to look like the xdebug.ini below.

Step 3 – Restart Apache

On the i5/as400/iseries type:

Go ZENDPHP7/ZSMENU

Option 5 – Service Management menu

Option 6 – Stop Apache server instances

Option 5 – Start Apache server instances

or do it at https://myibmi:2010/HTTPAdmin

Step 4 – Setup xdebug client in Netbeans

Right click a project and go to project properties.  In sources, set the Web Root to the local files that map to your Web Root on the i5 (/www/zendphp7/htdocs/{myapp}).   In the Run Configuration, set up the Project URL and the index file to point to your starting index.php file.  On the toolbar menu click Tools->Options ->PHP tab-> Debugging sub tab.  At this location you can set the debugger port, session ID, if it should stop at the first line of a file, what URLs were requested and to show the debugger console.

netbeans-php-debugging-options.png

Step 5 – Launch Debugger

You lauch the debugger by pressing CTRL+F5 or by clicking the debug icon.

debug icon netbeans.png

Hopefully if everything was setup right you’ll be able to start the debugger and Netbeans will open your website up in a browser and then connect to xdebug on the server.  The way it works is the client sends the request on port 80 and then the server and the client communicate over port 9000 or the port you selected.  You’ll get all the Variables, Call stack and be able to step through the PHP code and set up Break points through out the file. If your having troubles getting this working let me know.  Running phpinfo() will also give you an idea if you’ve correctly installed xdebug.

Misc. Tips

1. You may have to move the CW wrapper for the old i5 toolkit into your project file.  You can find those files in the /usr/local/zendphp7/share/ToolkitAPI folder on the IFS.

2. You can use xdebug’s wizard that uses your phpinfo and helps you detect and install xdebug – http://xdebug.org/wizard.php

3. If your looking to compile php on the iseries this site might help – http://www.deloli.net/distrib.php

4. Netbeans wiki on how to configure xdebug  – http://wiki.netbeans.org/HowToConfigureXDebug

What phpinfo() will display about xdebug.

 

Netbeans IDE for PHP on the iSeries better than Zend Studio

Sick of Zend Studio and Eclipse?   Do you think “Zend Studio for IBM i” sucks?  I was sick of it and found the Netbeans IDE and found its a great alternative to the Clunky Zend Studio mess.

What can Netbeans do?  Everything all in one!

PHP IDE – Code Highlighting, Auto completing etc…

IBMi DB2 Database access via the JTOpen

Source Control via SVN or GIT

Debugging via xdebug

FTP your files to the I5 after saving the file locally

to name a few features i was looking for in the Zend Studio for i, here’s what else it can do.

Get Started


Download Netbeans
Here’s the download link for Netbeans IDE.

SVN Setup
Learn how to setup subversion in Netbeans from their online tutorial.

Connecting to the AS400 / I5 / IBMi to run SQL from the IDE
To set up the database connection to the I5 you’ll need to download JTOPEN then go to the services tab (click ctrl+5 when in Netbeans), right click drivers, and click new drivers, click add and find the jt400.jar file from the JTOPEN download folder

After the driver is set up, right click it and click “connect using”, set the driver to JTOpen400 fill in the username and password and set the  url to jdbc:as400://as400servername/libraryname

Auto-FTP of files
Go over the tutorial on netbeans.org to setup a remote project so that your files are automatically FTP’d to your I5.


Alternative to System i Navigator

Why not use the System I Navigator?  Well I find it annoying the number of things I have to click before I can finally run a sql query.  Also I like having 1 application to do everything.  There are some times where I run a script in System i Navigator’s “Run SQL” window because it supports CL commands via “CL: <CL Command here>