db2 column organized tables error on select

[roganp]

Hello,

I get this error when selecting from column organized table:

ERROR: error executing query: OCIStmtExecute failed to execute remote query
DETAIL: SQLSTATE = 42858 SQLCODE = -1667
line=1484
file=db2_utils.c
[IBM][OCI Driver][DB2/LINUXX8664] SQL1667N The operation failed because the operation is not supported with the type of the specified table. Specified table: "WINGS_DW_MMIS_LEGACY.ZTEST". Table type: "ORGANIZE BY COLUMN". Operation: "WITH RR". SQLSTATE=42858

If I recreate the table as row organized explicitly, select works without error.

SELECT DB2_diag('bludb');

db2_diag | db2_fdw 1.0devel, PostgreSQL 12.2, DB2 client 11.1.3.0.0, DB2 server 11.01.0900

SELECT DB2_diag();

db2_diag | db2_fdw 1.0devel, PostgreSQL 12.2, DB2 client 11.1.3.0.0, DB2INSTANCE=db2inst1, DB2_HOME=/home/db2inst1/sqllib, DB2LIB=/home/db2\

dunno if this is related, found this report: https://www.ibm.com/support/pages/using-db2batch-query-involving-columnar-table-may-fail-sql1667n-error

Test case (this fails):

create table myschema.ztest (c1 varchar(128) not null) in ts_mmis_d_32k organize by column;
insert into myschema.ztest values ('test1');
insert into myschema.ztest values ('test2');

This does not:

create table myschema.ztest (c1 varchar(128) not null) in ts_mmis_d_32k organize by row;
insert into myschema.ztest values ('test1');
insert into myschema.ztest values ('test2');

Regards,
Peter

[roganp]

This hack gets past it:

$ git diff -w
diff --git a/db2_utils.c b/db2_utils.c
index 179ff0d..60d0f58 100644
--- a/db2_utils.c
+++ b/db2_utils.c
@@ -602,7 +602,7 @@ retry_connect:
     db2Debug2 ("db2_fdw: begin serializable remote transaction");
 
     /* start a read-only or "serializable" (= repeatable read) transaction */
-    if (checkerr (OCITransStart (svchp, errhp, (uword) 0, OCI_TRANS_SERIALIZABLE), (dvoid *) errhp, OCI_HTYPE_ERROR,__LINE__, __FILE__) != OCI_SUCCESS) {
+    if (checkerr (OCITransStart (svchp, errhp, (uword) 0, 0x00000200), (dvoid *) errhp, OCI_HTYPE_ERROR,__LINE__, __FILE__) != OCI_SUCCESS) {
       db2Error_d (FDW_UNABLE_TO_ESTABLISH_CONNECTION, "error connecting to DB2: OCITransStart failed to start a transaction", db2Message);
       if (retry && (err_code == 1012 || err_code == 28 || err_code == 3113 || err_code == 3135)) {
         db2Debug2 ("db2_fdw: session has been terminated, try to reconnect");

0x00000200 is taken from oci.h in oracle client, using OCI_TRANS_READ_WRITE failed on undeclared identifier (where are these defined in db2_fdw?)

Also tried this from postgres side, at the session level, but it did not work (closed db2 connections before testing):

set session characteristics as transaction isolation level read uncommitted;
SET
show default_transaction_isolation;
 default_transaction_isolation
-------------------------------
 read uncommitted
(1 row)