Global table error (DB2)

[PatWilson]

Issue Description and Expected Result

Hello I am having trouble getting a query when I declare a global temporary table. I can connect to all the other table involved, and I tested the sql query in other software (dbvisualizer) and it runs correctly there.

Database

DB2 v10.5

Here is my code, sorry cannot reproduce

query <-dbSendQuery(conn, 
     "declare global temporary table session.gtt_test
     (Test_id BIGINT)
     ON COMMIT PRESERVE ROWS NOT LOGGED
     WITH REPLACE;

     INSERT into session.gtt_test
      SELECT gtt.id 
          FROM  schema.DIM_bank gtt
      
      Fetch First 1600 Rows Only;
     
     SELECT hey.* 
          FROM  session.gtt_test hey
          Fetch First 50 Rows Only;")

result <-dbFetch(query)
sessionInfo()

Here is my session info

R version 3.4.2 (2017-09-28)
Platform: x86_64-pc-linux-gnu (64-bit)
Running under: CentOS release 6.10 (Final)

Matrix products: default
BLAS: /usr/lib64/libblas.so.3.2.1
LAPACK: /usr/lib64/atlas/liblapack.so.3.0

locale:
 [1] LC_CTYPE=en_US.UTF-8       LC_NUMERIC=C               LC_TIME=en_US.UTF-8        LC_COLLATE=en_US.UTF-8    
 [5] LC_MONETARY=en_US.UTF-8    LC_MESSAGES=en_US.UTF-8    LC_PAPER=en_US.UTF-8       LC_NAME=C                 
 [9] LC_ADDRESS=C               LC_TELEPHONE=C             LC_MEASUREMENT=en_US.UTF-8 LC_IDENTIFICATION=C       

attached base packages:
[1] graphics  grDevices utils     datasets  stats     methods   base     

other attached packages:
 [1] DBI_1.0.0       odbc_1.1.5      netRc_0.1.6     dart_1.5-7      forcats_0.2.0   stringr_1.3.1   dplyr_0.8.0.1  
 [8] purrr_0.2.5     readr_1.1.1     tidyr_0.8.1     tibble_2.1.1    ggplot2_3.1.0   tidyverse_1.2.1 rlocal_4.4.0   
[15] arsenal_3.1.1  

loaded via a namespace (and not attached):
 [1] tidyselect_0.2.5  haven_2.1.0       lattice_0.20-35   colorspace_1.3-2  testthat_2.0.0    yaml_2.2.0       
 [7] blob_1.1.0        XML_3.98-1.9      rlang_0.3.4       pillar_1.3.1      glue_1.3.0        withr_2.1.2      
[13] bit64_0.9-7       modelr_0.1.1      readxl_1.0.0      plyr_1.8.4        munsell_0.5.0     gtable_0.2.0     
[19] cellranger_1.1.0  rvest_0.3.2       knitr_1.20        broom_0.5.0       Rcpp_1.0.1        scales_1.0.0     
[25] backports_1.1.2   jsonlite_1.6      bit_1.1-14        mayopkglogs_0.2.0 hms_0.3           stringi_1.2.4    
[31] grid_3.4.2        cli_1.0.1         tools_3.4.2       magrittr_1.5      lazyeval_0.2.1    crayon_1.3.4     
[37] pkgconfig_2.0.2   xml2_1.2.0        lubridate_1.7.3   assertthat_0.2.1  httr_1.3.1        rstudioapi_0.8   
[43] R6_2.2.2          nlme_3.1-131      compiler_3.4.2 

[PatWilson]

I figured it out. Basically you cannot have multiple statements in one dbSendQuery. You can solve the problem by breaking up your query into multiple dbExecute statements before you run your final query to get your data

dbExecute(
  edt4p,
  "declare global temporary table session.gtt_test
     (Test_id BIGINT)
     ON COMMIT PRESERVE ROWS NOT LOGGED
     WITH REPLACE;")

dbExecute(
  edt4p,
  "INSERT into session.gtt_test
      SELECT gtt.id 
          FROM  schema.DIM_bank gtt
      Fetch First 1600 Rows Only;"
)

query <-dbSendQuery(conn, 
     "SELECT hey.* 
          FROM  session.gtt_test hey
          Fetch First 50 Rows Only;")

result <-dbFetch(query)
sessionInfo()

Hopefully someone finds this useful. :)