Monthly Archives: August 2015

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

PHP CLI on IBMi PASE Memory Limit Problem (AIX OS via QP2TERM)

I ran into one of the hardest things to figure out the dreaded “Segmentation fault” and “Illegal instruction” while running php-cli in a QP2TERM session (a PASE/AIX/”IBMs unix” shell)The exact errors were:

php-cli[9]: 12345 Illegal instruction (coredump)  –when ran non-interactive

php-cli[9]: 12345 Segmentation fault(coredump) –when ran interactive

Note: php-cli is the shell script that calls the PHP interpreter on line 9 from the command line and 12345 is the AIX process id that had the issue.  Segmentation fault means you are addressing memory outside of your data segment which has a predefined size (256MB). coredump should be a data dump in the system log

In /usr/local/zendsvr/etc/php.ini I tried to increase the
memory_limit = 512M ; Maximum amount of memory a script may consume (512M). 

I tried to set the value in my script with
ini_set(‘memory_limit’, ‘512M’); 

I even tried to set it on the command line with the -d option
/usr/local/zendsvr/bin/php-cli -d memory_limit=512M myscript.php

I figured out the amount of memory my script was using by reducing the number of records it was processing and running the follow echo command to get the peak memory usage:

echo “Memory Peak Usage: “.(memory_get_peak_usage()/1024/1024).” MB”;

The actual memory bottle neck was happening further up the chain at the AIX process/job level.  The default memory limit of AIX process is 256MB with additional Data Segments of 256MB (Hex 0x10000000 2^8) with a max size of 8 additional data segments

Solution

Set the LDR_CNTRL environment variable in the parent process (PHP-CLI) to multiple data segments (in the example below 8 additional data segments of 256MB for 2.25GB of memory [this is the max…]) and then run your php script and then unset the memory limit so you don’t affect other processes.   Modify the shell script /usr/local/zendsvr/bin/php-cli and wrap the call to the php interpreter ($ZCE_PREFIX/bin/php “$@”) with the export and unsetting of LDR_CNTRL as shown below

export LDR_CNTRL=MAXDATA=0xB0000000@DSA
$ZCE_PREFIX/bin/php “$@”
unset LDR_CNTRL

putenv should probably not work because the parent process has to set LDR_CNTRL not the PHP script

putenv(“LDR_CNTRL=MAXDATA=0xB0000000@DSA”);

Use echo getenv(“LDR_CNTRL”); to see what its set to in your PHP script.

If you are using the FASTCGI w/ apache you can modify the config file (/www/zendsvr/conf/fastcgi.conf) and add to the end of  the line starting with Server type=”application/x-httpd-php” …

SetEnv=”LDR_CNTRL=MAXDATA=0xB0000000@DSA”

Caution

If you are hitting this limit you should probably look at the program you created because there may be something that is inefficiently using memory and that should be fixed instead of changing the memory limit.

What is this DSA

“The @DSA which can be appended to this value allows the boundary been private data and shared memory to be changed, allowing more segments to be used and the heap to start in segment 3. It also allows shared objects to be moved into segment 2 to give more contiguous space (See Figure 4).” – http://ibmsystemsmag.com/CMSTemplates/IBMSystemsMag/Print.aspx?path=/aix/administrator/systemsmanagement/Avoiding-Those–Segmentation-Fault–Failure-Messag

More info here: https://www.ibm.com/support/knowledgecenter/en/ssw_aix_61/com.ibm.aix.genprogc/lrg_prg_support.htm

These issues might also go away when we go from 32 bit to 64 bit.

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

IBMi DB2 File/Table field naming convention – More than 10 character abbreviations!

Are you tired of being stuck to 10 Upper Case characters for field names?

Well before you start changing your current naming convention you’ll want to make sure the others you work with are cool with your idea for naming conventions.  People will need to realize the differences between a SQL (strsql,PHP (db2_*)) and System perspective (dbu, query, etc…).    There’s really 3 scenarios for naming fields:

1) Always use system naming 10 character uppercase.  This is best for applications where RPG and PHP will both be accessing the table.  Keeps both developers on the same page
MYCOLTHATS DECIMAL(9, 0) DEFAULT NULL ,

2) Use SQL Name and System name all uppercase
MYCOLTHATSLONG FOR COLUMN MYCOLTHATS DECIMAL(9, 0) DEFAULT NULL ,

3) Use SQL Name in mix case (camel case, all lower, etc…) and System name
“MyColThatsLong” FOR COLUMN MYCOLTHATS DECIMAL(9, 0) DEFAULT NULL ,

Tip:  use the “FOR COLUMN <system field name>” to specify a nice system name instead of an autogenerated system name like MYCOL00001.  If you do:

MYCOLTHATSLONG DECIMAL(9, 0) DEFAULT NULL ,

you’ll get

MYCOLTHATSLONG FOR COLUMN MYCOL00001 DECIMAL(9, 0) DEFAULT NULL ,

Example:

Gotcha Note: The actual “File Member” will have the ugly name with MYTAB00001