DB2 connection with PDO throws General Error -7008 when Inserting or Deleting

Question

We're developing a PHP application that connects both to a PostgreSQL server and an IBM i server with DB2. While the PDO connection to PGSQL works just fine, the connection to DB2 can only fetch from tables; trying to Insert or Delete results in the following error:

SQLSTATE[HY000]: General error: -7008 (SQLExecute[4294960288] at /build/php7.0-ltLrbJ/php7.0-7.0.33/ext/pdo_odbc/odbc_stmt.c:260)

This error happens both on our development and production environments. Both servers are Ubuntu (different versions, but not by much); I'm using the ODBC driver for PDO.

We tried to connect to other IBM i servers, and with different users, but the exact same problem still arises. Can Select, but not Insert. Googling the error code doesn't yield any useful result, and as you can see the error message itself it's as unhelpful as can be. The code in the SQLExecute particularly doesn't appears anywhere, not even a single result (there is a result from an IBM page, but it's actually a different error code).

The code is pretty simple, but perhaps there is some obvious and glaring error there.

The test script:

include("DB2.php");
$oDAO = new DAO();
$res = $oDAO->ejecuta("INSERT INTO <Library>.<File> VALUES (1,0,1)");

The DAO:

class DAO{
    var $link;

    public function __construct(){
        // función constructora, inicia la conexión

        $this->link = new PDO("odbc:DRIVER={IBM i Access ODBC Driver};SYSTEM=<System>;PROTOCOL=TCPIP",
                            '<user>', '<pass>');
        $this->link->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $this->link->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
    }

    private function begin()    { $this->link->beginTransaction(); }
    private function rollback() { $this->link->rollBack(); }
    private function commit()   { $this->link->commit(); }

    public function ejecuta($query){
        try{
            $this->begin();
            $oResult = $this->link->query($query);
            if($oResult){
                $bResult = true;
                $this->commit();
            }else{
                $bResult = false;
                $this->rollback();
            }
        }
        catch (Exception $e){
            echo $e->getMessage();
            $bResult = false;
            $this->rollback();
        }
        return $bResult;
    }
}

Frankly, we're out of options and I already wasted two weeks with this. We just need to insert and delete records. So any help is welcome.

Answer 1

The symptoms you describe are consistent with attempting to modify the database under commitment control, but without journaling enabled.

There are three common ways to deal with this:

1. Turn journaling on. This is pretty extreme, since the folks who administer the database would have to do this, and if they've got journaling turned off, it's likely they either don't really know how to deal with journals, or don't want to. But it's the only practical way to have full commitment control on Db2 for i.

2. Connect with autocommit on. This will add an implicit commit to any database-modifying SQL statements executed with this connection. In my experience, this is the most common and convenient way to handle the situation.

3. Add WITH NC to each relevant SQL statement. In principle, this gives you statement-by-statement control over whether to suspend commitment control. In practice, if you are thinking of doing this in the first place, you probably don't have journaling enabled, and thus you will have to do this on each and every database-modifying SQL statement. This is why most people gravitate toward option 2.