Storing data from R to DB2 doesn't work

Question

I have a strange issue with DB2 and R connectivity. I can access the DB, can query the data but I can't store it from R. I use the RODBC package of R. I followed the guide written here https://www.ibm.com/developerworks/data/library/techarticle/dm-1402db2andr/index.html technically this package creates an "INSERT INTO" statement from the R object and executes it on DB2 to store the data from R. (I have the necessary credentials to execute these operations on the database.)

What I tried is the following (I use R and SQuirreL - http://squirrel-sql.sourceforge.net/ - together to test more than one way):

Creating a test table with 3 columns and 25 records with SQuirreL. Then I query this table from R. I try to save the first record from resulting dataset (from 2) into the same table. Then I query the table from R - with the appended row. Then I query from SQuirreL. The issue occures here, while I'm connected to R and after the INSERT. At (4) I see the added record, but I cannot query it from SQuirreL (5). It seems like R has locked the whole table. It doesn't get solved when I disconnect from the DB with R, I also get 2 error messages (I suspect these are related). When I quit R then it is solved and I can query my table from SQuirreL, but the appended record disappears.

I tried to run the same SQL statement from SQuirreL which is sent by R. I get no error messages and the record is appended. Hence the method used by R should work. I don't know where it goes sideways.

I attached a sample data from my table and the used R script (runtime messages included). Below is the CREATE TABLE statement. The table doesn't have any indices or keys yet, so adding a duplicate record is not prohibited.

     create table dm_quant.test (
         r_date         date, 
         cid             varchar(255),
         priv_person     varchar(255)
     );

Have anyone faced the same difficulties? If so how can I solve it?

Thanks in advance!

    #connect-to-db2
    rm(list=ls(all=TRUE))
    gc(reset = TRUE)

    require(RODBC)

    dsn_name <- "DB2_DB"
    user <- "user"
    pwd <- "pass"


    channel <- odbcConnect(dsn = dsn_name, uid = user, pwd)

    table_list <- sqlTables(channel, tableType = "TABLE", schema = "DM_QUANT")
    cat("There are", nrow(table_list), "tables in the DM_QUANT schema.\n")
    # There are 4 tables in the DM_QUANT schema.

    table_name <- "DM_QUANT.TEST"
    col_list <- sqlColumns(channel, table_name)
    cat("There are", nrow(col_list), "columns defined in", table_name,"\n")
    # There are 3 columns defined in DM_QUANT.TEST 

    ## Fetch test table (25 records)
    test_tbl <- sqlQuery(channel, paste("SELECT * FROM ", table_name, sep = ""),
                         as.is = TRUE, na.strings = "")

    ## Determine varTypes parameter for sqlSave
    db2_var_types <- data.frame(var = col_list$COLUMN_NAME, 
                                var_type = col_list$TYPE_NAME, 
                                var_type_ext = paste(col_list$TYPE_NAME, "(", col_list$COLUMN_SIZE, ")", sep = ""),
                                stringsAsFactors = F)

    db2_var_types$final_var_type <- db2_var_types$var_type

    ## Adding size for VARCHAR variable type.
    db2_var_types$final_var_type[db2_var_types$var_type == "VARCHAR"] <- db2_var_types$var_type_ext[db2_var_types$var_type == "VARCHAR"]


    ## Append - append 1st record of the current table again.
    sqlSave(channel, test_tbl[1,], tablename = table_name, append = T, verbose = T, 
            fast = F, rownames = F, colnames = F, varTypes = db2_var_types$final_var_type)
    # Query: INSERT INTO DM_QUANT.TEST ( "R_DATE", "CID", "PRIV_PERSON" ) VALUES ( '2016-06-30', '193303', 'N' )

    ## After I use sqlSave I cannot query from the database from SQuirreL SQL Client.
    ## Seems like I'm locking the whole table by R.

    ## Test append
    ## customer_test <- sqlFetch(channel, table_name)
    test_append <- sqlQuery(channel, paste("SELECT * FROM ", table_name, sep = ""),
                              as.is = TRUE, na.strings = "")

    nrow(test_append)
    # [1] 26
    nrow(test_append) == nrow(test_tbl) + 1
    # [1] TRUE

    ## Append seems successfull
    cat("Record appended successfully.\n")


    ## Close connections
    odbcCloseAll()
    cat("Database connections are closed.\n")

    ## Always closes with 2 error message
    # 1: [RODBC] Error in SQLDisconnect 
    # 2: [RODBC] Error in SQLFreeconnect 

    ## I still cannot query from SQuirreL SQL Client untill I close R completely
    ## After I close and test query from SQuirreL, I cannot see the appended record.

The Excel file i used for the test.

        **sample-data** 
    R_DATE      CID    PRIV_PERSON
    2016.06.30  193303  N
    2016.06.30  808739  N
    2016.06.30  585008  N
    2016.06.30  479872  N
    2016.06.30  350290  N
    2016.06.30  895961  N
    2016.06.30  822839  N
    2016.06.30  746603  N
    2016.06.30  174107  N
    2016.06.30  858942  N
    2016.06.30  710500  N
    2016.06.30  513533  N
    2016.06.30  303993  N
    2016.06.30  14983   N
    2016.06.30  91401   N
    2016.06.30  364451  N
    2016.06.30  147311  N
    2016.06.30  165897  N
    2016.06.30  988524  N
    2016.06.30  445691  N
    2016.06.30  119082  N
    2016.06.30  4668    N
    2016.06.30  8910    N
    2017.12.31  377879  
    2016.06.30  531661  N

Answer 1

Disabled auto-commit would explain this behaviour. Updated or inserted rows remain locked exclusively until they are committed. When you forcefully close the connection, the changes are likely being rolled back.

Try odbcSetAutoCommit(channel, autoCommit = TRUE) right after establishing the connection. Alternatively, you can issue odbcEndTran(channel, commit = TRUE) after your sqlSave().