Category Archives: SQL

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

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/

 

 

Netbeans: Creating Stored Procedures or running Dynamic compound statement on IBMi DB2

I finally figured out how you can create a stored procedure or run Dynamic compound statements in Netbean’s Database service on the IBMi.  My problem was that whenever I tried to run a create stored procedure script the SQL editor didn’t know how to parse it correctly so it would send the command line by line to the database using the semi colon as the delimiter instead of as one contiguous command.

The key to getting this to work in Netbeans is to create a Delimiter that marks the end of the SQL that you want to send to the system.  In the example below I send a request that creates 2 temp tables, and then 2 requests to pull the data from those tables.  This example is simple and doesnt show the full power of a DCS.  The dynamic compound statements allow you to use variables, control logic, cursors, etc… to do database specific tasks.  Its like a stored procedure except you don’t have to create it first.   see:

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/Dynamic%20Compound%20statement

and

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/sqlp/rbafydyncompound.htm

It did seem that a dynamic compound statement can’t return a result set for some reason (at least when i tried).

Below is an example of the error that I was getting when i was running into this issue where the database service was not parsing the CREATE PROCEDURE statement as one contiguous chunk.

Error code -104, SQL state 42601: [SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: ;.
Line 2, column 1

Below is me trying DCS in different IBMi tools

cimmfjkweaexyhx

Kill all jobs with a record lock on a specific file via “DB2 for i” SQL

Below is a stored procedure that uses the DB2 for i service QSYS2.RECORD_LOCK_INFO to get all the record locks on a specific file.  It then iterates over all the records and passes the JOB_NAME to ENDJOB to kill the jobs that are creating the lock on the file.  Of course this is very dangerous and should be only used in certain instances where you are sure that the jobs you’ll be killing will not matter.

 

Detecting if a character field isNumeric via SQL in IBM i DB2 (Translate)

The translate function is pretty amazing. You pass in the variable, a string of characters you want the character to be replaced with followed by a string of characters you want to search for.  In the example below all numeric characters become N and all N’s become X.  This makes all your Digits the character N which you can then compare to ‘NNNNN’ in this instance of a char(5) datatype column to select your numeric only fields, you can then do a numeric comparison such as greater than <10000 without running into a SQL exception with casting nonnumeric characters.

SQL Reference for IBM i – to learn more about TRANSLATE

ftp://ftp.software.ibm.com/ps/products/db2/info/vr7/pdf/letter/db2s0e70.pdf

IBM i DB2 – Synchronizing local database table with Remote table

Setup remote db in Green Screen:

-- Use ADDSVRAUTE so that the system knows the username and password
-- when connecting to the remote system
ADDSVRAUTE USRPRF(MYUSRPRF) SERVER(PRODSERVER) PASSWORD()
-- Or if you already have an entry you may need to change your entry with
CHGSVRAUTE USRPRF(MYUSRPRF) SERVER(PRODSERVER) PASSWORD()
-- Use wrkrdbdire to create/edit remote db connection configurations
WRKRDBDIRE
-- Use CHGDDMTCPA to setup how TCP connection will work (enforcing
-- password policy and encryption)
CHGDDMTCPA AUTOSTART(*YES) PWDRQD(*USRENCPWD) ENCALG(*DES)

alternatively you could create the connection every time doing:


CONNECT TO REMOTE_SYSTEM_NAME USER USERPRF_NAME USING 'MY_PASSWD_IN_SINGLE_QUOTES';

SQL

-- ::Local IBMi::

-- Let's back up the table before we wipe and replace it
CREATE SCHEMA BACKUP

--Create backup
CREATE TABLE BACKUP.TABLETOSYNC
AS (SELECT * FROM MYLIB.TABLETOSYNC)
WITH DATA;

--Wipe local table
DELETE FROM MYLIB.TABLETOSYNC;

--Replace with Production data
INSERT INTO MYLIB.TABLETOSYNC (
SELECT * FROM PRODSERVER.MYLIB.TABLETOSYNC);

--View the table data
SELECT * FROM MYLIB.TABLETOSYNC;

--::MORE ADVANCED WAY WITH MERGE::
--::This would be more elegant but time consuming by setting up MERGE with update insert rules
--instead of wiping and replacing. More advanced example here:http://www.itjungle.com/fhg/fhg092210-story02.html

--Create temp table from production data
CREATE TABLE QTEMP.TABLETOSYNC
AS (SELECT * FROM PRODSERVER.MYLIB.TABLETOSYNC)
WITH DATA;

MERGE INTO MYLIB.TABLETOSYNC AS TARGET_TABLE
USING QTEMP.TABLETOSYNC AS SOURCE_TABLE
ON (TARGET_TABLE.TABLEKEYFIELD=SOURCE_TABLE.TABLEKEYFIELD )
WHEN MATCHED THEN
UPDATE SET
TARGET_TABLE.COL1 = SOURCE_TABLE.COL1
WHEN NOT MATCHED THEN
INSERT (COL1)
VALUES(SOURCE_TABLE.COL1 )
ELSE IGNORE;

-- More info:
-- https://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/ddp/rbal1mst63.htm
-- http://www.itjungle.com/fhg/fhg072512-story02.html

-- Another thing i looked into using
-- The connect to command actually puts you into that server instead of just giving you a reference to it....
-- CONNECT TO PRODSERVER USER MYUSRPRF USING 'password';

UPDATE: I’ve put all this logic into a stored procedure and you can download the code here: https://github.com/phpdave/DB2-for-IBMi/blob/master/Stored%20Procedures/SYNCTABLE.sql Let me know if you have any comments for improvement. The current stored procedure has commitment control to save you from sql exceptions or something going wrong in the middle of execution. If a problem occurs everything will be rolled back to a save point (Imagine deleting the database and then it not inserting.)

This might also be helpful and simpler-  https://www.ibm.com/developerworks/community/wikis/home?lang=en#/wiki/IBM%20i%20Technology%20Updates/page/INSERT%20with%20remote%20SUBSELECT

iSeries Navigator

You can also just go to the table in iSeries navigator MS windows client and right click the table and click copy. Then go to the target system and right click on the library you want to paste the table in. This is great for quick manual copies.