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.

Did you already verify that the specific userid can insert/update into the table (i.e has the right permissions)? Please EDIT your question to state the exact versions of the IBM driver, of the pdo_ibm, and of the php itself (all three versions please). Also you can trace (CLI trace) to see what's happening under the covers, documentation shows how to do this with an ibm driver. — mao
Yes, as I said we tried different different users, and even different files. We even logged in with QSECOFR, still the same error. And on different systems with different versions, too. I'm going to update the original question, but I don't know how to make a CLI trace so it might take a while. — Klint_psk
Sounds like the table may not be journaled. Here's the error description. — mustaccio

John Y John Y · Accepted Answer · 2019-06-26T18:01:26

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:

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.
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.
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.

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

1 Answers