Tag 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

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

ZF2 – Zend_DB Support for number sign # (AKA pound, hash) in a field / column on the IBMi DB2

If you use Zend Framework 2’s Zend\Db\Sql\Select or Zend\Db\Sql\Join “on expression” to create queries for the IBM i DB2 you’ll sometime’s run into the error

“Column or global variable SomeField”# not found. SQLCODE=-206”

This is because the the Adapter/Platform/AbstractPlatform’s quoteIdentifierInFragment function is used to parse the input you provide. It currently doesn’t allow for the pound sign # to be in a column name and it incorrectly puts quotes around the #.  SomeField# becomes “SomeField””#” , i believe.

If you have this problem you can use my change to /Adapter/Platform/IbmDb2.php found here https://github.com/zendframework/zend-db/pull/95/files .  This change creates a quoteIdentifierInFragment function in /Adapter/Platform/IbmDb2.php that overrides the Base Class AbstractPlatform’s quoteIdentifierInFragment function and uses one that supports # by adding # to the regex.

More info on this getting added to the master branch can be found on my pull request here.  Please feel free to add comments, or commit other code that could be helpful:

https://github.com/zendframework/zend-db/pull/95

Insert statements are not effected because columns are quoted differently.  An array of columns is process and passes the entire column element to: $platform->quoteIdentifier($column); https://github.com/zendframework/zend-db/blob/master/src/Sql/Insert.php#L164

The main change is the regex and you can try different cases here: http://sandbox.onlinephpfunctions.com/code/9b50346237ed0de83f2244282081ae5080ad9f75

 

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.

 

Protecting against SQL Injection in PHP and DB2 on IBM i

With many IBMi developers new to PHP, SQL and the web environment its important to cover a common mistake people make. The mistake is concatenating a value from $_REQUEST, $_GET or $_POST with their SQL statement string. This opens up the possibility for a SQL injection which allows someone to retrieve other data, bypass certain logic by making the statement always true, or worse (dropping a table, altering data, anything you can do in SQL). Below is how you can use a prepared SQL statement to safely execute SQL.

PHP DB2 on IBMi error: MYTABLE in MYFILE not valid for operation. SQLCODE=-7008

UPDATE:  Make sure to check if i5_allow_commit is not 0 (No Commit *NC *NONE).  You can check by running this in your PHP code

var_dump(ini_get(‘ibm_db2.i5_allow_commit’));

If the value is not 0 you might fix the problem by setting it to 0 in /usr/local/zendsvr/etc/conf.d/ibm_db2.ini and doing an Apache restart on Zend Server going to the 5250 typing “GO ZENDSVR/ZSMENU” option 6, option 5.

In case you run into the issue where your SQL updates and inserts statements aren’t processing you may want to try appending “WITH NONE” to the SQL statement.

When I ran the update statement

UPDATE MYFILE.MYTABLE SET FIELD1 = ? WHERE FIELD2 = ?

I got

MYTABLE in MYFILE not valid for operation. SQLCODE=-7008

Its a new file created via SQL
When I added WITH NONE it works

UPDATE MYFILE.MYTABLE SET FIELD1 = ? WHERE FIELD2 = ? WITH NONE

Even hard coding the values created the same error.  The problem appears to be a commitment control / journaling issue with IBMi DB2 connection.  You could try to change the settings of your connection.

How to get a table’s primary key(s) from IBMi DB2 via SQL

How to get the primary key(s) of an IBMi DB2 table via SQL.
Below is some example code on how to pull back the primary keys of a table via SQL. This is useful for when your application needs to update or a delete a unique record that your currently working with (be it in PHP, or another language). Below is the SQL code to retrieve MYTABLE primary keys. To learn more about adding a unique field to an existing table – click here.

Note: Primary keys can be multiple columns.  Primary keys require uniqueness so the database will not allow duplicate values in the Primary Key(s).

Add/Change Primary Key of Table

On an existing table you can add or change the primary key via
ALTER TABLE MYLIB.MYTABLE ADD PRIMARY KEY (ID) ;
ALTER TABLE MYLIB.MYTABLE DROP PRIMARY KEY  ADD PRIMARY KEY (ID) ;

Find Primary Key of a Keyed Physical File (aka Table in sql)

You may run into  a Keyed physical file.  To find out what keys that file is using you’ll have to run the following command on the 5250 or CL
DSPFD FILE(MYLIB/MYTABLE)
Page Down until you get to Access Path Description and look for the Number of key fields and Key field (s).  Or if you have System I Navigator.  Get to the table -> right click for context menu -> Show Indexes.  In Indexes for LIB.TABLE click F12, and add Key Columns to the view.

I’ve attempted to create a UDTF to get a keyed physical via SQL.  This might need a little work:

You ‘ll typically run into this issue if you don’t realized the file is already keyed

SQL State: 23515
Vendor Code: -603
Message: [SQL0603] Unique index cannot be created because of duplicate keys. Cause . . . . . :   An attempt was made to create unique index Q_LIBRARY_TABLE_PKEY1_00001 in LIBRARY or add unique constraint Q_LIBRARY_TABLE_PKEY1_00001 in LIBRARY. The operation cannot be performed because the rows in table TABLE in LIBRARY contain one or more duplicate values in the columns used to create the index. Recovery  . . . :   Do one of the following and try the request again: — Remove the UNIQUE attribute from the CREATE INDEX statement. — Remove the UNIQUE constraint from the ALTER TABLE statement. — Change the data in the related table so that all key values are unique. — Specify UNIQUE WHERE NOT NULL on the CREATE INDEX statement if the duplicate keys contain nulls. The uniqueness restriction would not apply when the key value contains nulls. For information on what rows contain the duplicate key values, see the previously listed messages in the job log.

or

SQL State: 42830
Vendor Code: -538
Message: [SQL0538] The FOREIGN key in constraint Q_LIBRARY_TABLE_KEY1_00001 in LIBRARY not same as the parent key. Cause . . . . . :   The FOREIGN key in constraint Q_LIBRARY_TABLE_KEY1_00001 in LIBRARY is not the same as the parent key of table *N in *N. The FOREIGN key must have the same number of columns as the parent key and the data type, attributes, and field procedure of the FOREIGN key must be identical to the data type, attributes, and field procedure of the corresponding column of the parent key. Recovery  . . . :   Correct the statement so that the description of the FOREIGN key conforms to that of the parent key of the specified table.

Thanks to Scott Forstie for the help w/ finding the primary key