Category Archives: DB2 for i (iseries, as400)

DB2 on IBM i Power CPUs

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

Clean phone number field with IBM DB2 for i SQL REGEXP_REPLACE

Say you have a phone number in various formats but you just want the numeric characters and all the non-numeric characters removed. Well use the SQL function REGEXP_REPLACE and look for characters with the regular expression [^\d] and replace it with empty character ”. [^\d] is a negated set which means match any characters not in between [^ and ]. \d is a short hand way of saying all digit characters 0-9.

SELECT PHONE_NUMBER, REGEXP_REPLACE(PHONE_NUMBER,'[^\d]’,”)
FROM MYTABLE;

123-123-1234,1231231234

References:
IBM documentation on REGEXP_REPLACE
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzscaregexp_replace.htm

Online regex tester tool
http://regexr.com/

Execute SQL remotely on an IBM i via PHP’s PDO ODBC from a Windows or Linux

You’ll first have to configure an ODBC connection “System DSN”/”System Data Source” via the “iSeries Access ODBC driver” or it might be call the IBM i Access driver which you can learn how to download and install here:

http://www-01.ibm.com/support/docview.wss?uid=nas8N1010355

Here’s some of the ways to configure the odbc connection

https://godzillai5.wordpress.com/2015/07/08/setting-a-default-sql-library-and-library-list-on-a-odbc-connection-for-a-ibmi-db2-connection-in-windows-and-linux/

Once you have the ODBC connection setup you can use it in PHP and here’s a Simple Example on how to execute SQL remotely and safely on an IBM i via ODBC.  Note you won’t want to dump the error info in production its there to alert you about errors.

Here’s an example on how to run a SQL extenal stored procedure

 

Make sure to enable SSL to encrypt your SQL statements that are sent

 

Windows

Run C:\Windows\SysWOW64\odbcad32.exe and edit connection options and make sure ssl is selected or IBM i navigator is defaulted to sslodbc windows secure.png

Linux 

Setup stunnel

guide is here: under 2.6 Encrypt the connection with Secure Sockets Layer

Other References:
Windows: http://www-03.ibm.com/systems/power/software/i/access/windows.html
Linux: http://www-03.ibm.com/systems/power/software/i/access/linux.html

JDBC JT400 setting to get CrUD and Show SQL features added in Netbeans with IBM DB2 for i

I finally figured out the missing connection property needed so that Netbeans will allow you to add, edit, and delete rows with inline editors and selected rows.  In addition to showing SQL Scripts for Create,Insert,Delete and Update.  It is the extended metadata property which is by default set to false and needs to be set to true.  I found this out by looking the jdbc properties at http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/rzahh/jdbcproperties.htm and looking for properties effecting read only status, which is why those features were blocked on Netbeans.

In your connection properties add

extended metadata=true

by right clicking your connection in the database panel of netbeans while disconnected and going to properties

Screen Shot 2016-08-21 at 5.45.29 PM.png

Click the ellipses …) next to connection properties
Screen Shot 2016-08-21 at 5.45.53 PM.png

Click under the property column and type extended metadata.  Click under Value and type true

Screen Shot 2016-08-21 at 5.46.28 PM.png

Now run a SELECT statement and you’ll be able to Create, Update and Delete records by using the icons above the result set or by right clicking the result set area and using the context menu.  Which also had the Show SQL Scripts for CrUD.

Screen Shot 2016-08-21 at 5.28.59 PM.png

DB2 for i in the Cloud – Connecting to PUB400 via JDBC in Netbeans

1.Sign up for an account at http://pub400.com/signup.html

2. Download and extract jtopen which contains lib/jt400.jar driver for connecting to an IBM i http://jt400.sourceforge.net/

3. Download and install Netbeans https://netbeans.org/ .  I do the PHP bundle, but i believe they all have the Database module.

4. Go into the “services” tab under database and right click the Drivers folder and click “New Driver”Screen Shot 2016-08-20 at 7.30.57 PM.png

Find where you extracted JT400 and select lib/jt400.jar

Screen Shot 2016-08-20 at 7.32.19 PM.png

Press the “Find” button so it can find the AS400JDBCDriver class in the jar file and give the driver a name like “JT400 JDBC Driver”

Screen Shot 2016-08-20 at 7.34.55 PM.png

Right click the driver and click “connect using” Screen Shot 2016-08-20 at 7.36.33 PM.png

Fill out the connection wizard with your information given to you from PUB400.com

User Name: <Your username for PUB400>
Password: <Your password for PUB400>
JDBC URL: jdbc:as400://pub400.com/{replace curly bracket and this with your schema name}

Screen Shot 2016-08-20 at 7.09.37 PM.png

After clicking next, Select your Schema

Screen Shot 2016-08-20 at 7.10.21 PM.png

Give the connection a name

Screen Shot 2016-08-20 at 7.10.38 PM.png

Now you can connect to the DB2 for i in the cloud by right clicking the connection and pressing connect

Screen Shot 2016-08-20 at 7.46.26 PM.png

Now you have an easy to get to database connection to a sandbox DB2 for i!  Time to play with the temporal table support and other new enhancements for DB2!

Screen Shot 2016-08-20 at 7.48.05 PM.png

After connecting, your connection properties should show what settings are being used on the connection and even what version of the DB2.  In this case the server is on V7R2m0 :

Screen Shot 2016-08-20 at 7.13.29 PM.png

Step2 add CrUD features by modifying the connection property

https://godzillai5.wordpress.com/2016/08/21/jdbc-jt400-setting-to-get-crud-and-show-sql-features-added-in-netbeans-with-ibm-db2-for-i/

 

 

Using a sequence object in DB2

Simple snippet to show how to use a Sequence object that auto increments in an SQL statement.  Just realize that when used in a stored procedure you cannot drop the sequence as the database will let you know that there’s a dependency of this object within a stored proc.

CREATE SEQUENCE MYLIB.MYAUTO_INCREMENTING_SEQ
AS BIGINT
START WITH 1
INCREMENT BY 1
NO ORDER
NO CYCLE
NO MINVALUE
NO MAXVALUE
CACHE 20;

INSERT INTO MYLIB.MYTABLE
(PRODUCTID,PRODUCTNAME)
VALUES (NEXT VALUE FOR MYLIB.MYAUTO_INCREMENTING_SEQ,'Godzilla Ice Cream');