Tag Archives: AS400

IBM i DB2 – CREATE TABLE IF NOT EXISTS

Below is a simple example of how to create a UDF to Check if a table exists on the IBM i DB2. We then use that function to determine if we need to create a table.

Advertisements

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

 

Rename a Column in DB2 for IBM i

Below is some SQL that can be used to rename a column by dropping the column and adding the new column name.  Do realize that this will make you lose the data in the OLDCOLUMNNAME field so you’ll want to create a backup and re-populate the new field using the backup.

Note: If you get Error code -952, SQL state 57014: [SQL0952] Processing of the SQL statement ended. Reason code 10. Its because the ALTER TABLE will result in data loss. The database is trying to warn you and wants you to confirm deletion but jdbc can’t handle the prompt. I’m unsure if there is a JDBC option to always say yes to the prompt. You may have to run this in a 5250 session with STRSQL

Alternatively you could also use the command below
CHGPF FILE(MYLIB/MYTABLE) SRCFILE(QDDSSRC)

You would just need to create the DDS in the source file member MYTABLE that changes the column

Wow! look at whats coming for #IBMi TR11 TR3 #db2 #sql

IBMi Software Announcement: Complete info from IBM

  1. IBM i Access Client Solutions has “Run SQL” interface
    • “Run SQL” scripts was previously only available in the Windows Client (System i Navigator / System i Access for Windows).
  2. Web Sockets w/ Apache level 2.4.12
    • allows for real-time communication.  A lot of chat applications, and real time collaboration apps are using Web Sockets because of the server’s connection to the client.
    • Learn more here
  3. Free-form RPG is no longer forced to columns 8 – 80.
  4. Zend Server for IBM i 8.5 (5639-ZC1)

DB2 for I new features and enhancements

    • ASSOCIATE LOCATOR for processing result sets from a remote db.
    • LOCATE_IN_STRING() – SQL Function –  Find a string inside a string. (PHP devs think strpos)
    • OVERLAY() – SQL Function – easily insert a string/variable inside a string (PHP devs think sprintf but not exactly)
    • Viewing Record locks or Object Locks in SQL by utilizing the DB2 for i services

Other New features:

  1. Open Source tools now included
    1. GCC, Git, .zip, .tar, bash, Python 2.7, and many others in Open Source for IBM i (5733-OPS)
  2. Integrated Web Services (IWS) server makes it easy to open your ILE objects up to webservices done in SOAP or REST

References:

DB2 for i services

System Lock

Record Lock

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.

Handling a DB2 record lock exception in PHP on IBMi

Ever run into this exception?

SQLSTATE[HY000]: General error: -913 [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0913 – Row or object TABLE in LIB type *FILE in use

Below is some example code on how you could handle a DB2 file lock in your PHP script, by catching the exception and doing want you want to do when someone has locked the record.  It could be that you email that person and tell them the need to get out of that file for your PHP script to work.  You need to make sure that your script can put what it was working on hold until someone can turn off the lock.

Alternative solution (Use a stored Procedure to find out about the file lock ahead of time.)

You could use a stored procedure to call

DSPRCDLCK   FILE(MASTER/PAYROLL)  MBR(*FIRST)  RCDNBR(1)  OUTPUT(*)

and you could get the RCDNBR from a SQL query like:

SELECT RRN(MY_TABLE)
FROM MY_TABLE WHERE ID=2

Just make sure your program doesn’t get into a race condition.  You may want to do all your sensitive changes in a SQL stored procedure that’s able to lock the file make changes and then release the locks.  You’ll also get the ability to rollback changes or not commit changes in case of a failure.

It appears that db2 php extension IBM_DB2.so (db2_* functions) will actually wait for the record lock to go away.  This will most likely make the PHP script hit its max execution time (causing a timeout) before the record lock is removed.  Therefore you have to check for the record lock ahead of time, create a record lock then do some update then unlock the record.

If you want to dive deeper into this you should check out commitment control, lock table, commit, rollback atomic etc…in the IBMi DB2 SQL reference pdf .

LOCK TABLE MY_TABLE IN EXCLUSIVE MODE  -- Will try to obtain a lock on the table