Monthly Archives: March 2017

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