database - Storing data from R to DB2 doesn't work -

i have strange issue db2 , r connectivity. can access db, can query data can't store r. use rodbc package of r. followed guide written here https://www.ibm.com/developerworks/data/library/techarticle/dm-1402db2andr/index.html technically package creates "insert into" statement r object , executes on db2 store data r. (i have necessary credentials execute these operations on database.)

what tried following (i use r , squirrel - http://squirrel-sql.sourceforge.net/ - test more 1 way):

creating test table 3 columns , 25 records squirrel. query table r. try save first record resulting dataset (from 2) same table. query table r - appended row. query squirrel. issue occures here, while i'm connected r , after insert. @ (4) see added record, cannot query squirrel (5). seems r has locked whole table. doesn't solved when disconnect db r, 2 error messages (i suspect these related). when quit r solved , can query table squirrel, appended record disappears.

i tried run same sql statement squirrel sent r. no error messages , record appended. hence method used r should work. don't know goes sideways.

i attached sample data table , used r script (runtime messages included). below create table statement. table doesn't have indices or keys yet, adding duplicate record not prohibited.

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

have faced same difficulties? if how can 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 dm_quant schema.\n")     # there 4 tables in 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 3 columns defined in dm_quant.test       ## fetch test table (25 records)     test_tbl <- sqlquery(channel, paste("select * ", table_name, sep = ""),                          as.is = true, na.strings = "")      ## determine vartypes parameter 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 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 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 dm_quant.test ( "r_date", "cid", "priv_person" ) values ( '2016-06-30', '193303', 'n' )      ## after use sqlsave cannot query database squirrel sql client.     ## seems i'm locking whole table r.      ## test append     ## customer_test <- sqlfetch(channel, table_name)     test_append <- sqlquery(channel, paste("select * ", 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 closed.\n")      ## closes 2 error message     # 1: [rodbc] error in sqldisconnect      # 2: [rodbc] error in sqlfreeconnect       ## still cannot query squirrel sql client untill close r     ## after close , test query squirrel, cannot see appended record. 

the excel file used 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 

disabled auto-commit explain behaviour. updated or inserted rows remain locked exclusively until committed. when forcefully close connection, changes being rolled back.

try odbcsetautocommit(channel, autocommit = true) right after establishing connection. alternatively, can issue odbcendtran(channel, commit = true) after sqlsave().

wiki