Open roganp opened 4 years ago
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)
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