From kenneth.cater at bankofamerica.com  Wed Aug 12 18:10:16 2009
From: kenneth.cater at bankofamerica.com (kenneth.cater at bankofamerica.com)
Date: Wed, 12 Aug 2009 18:10:16 +0200 (CEST)
Subject: [Rd] Problem with Linux x86_64 RODBC and Netezza (PR#13889)
Message-ID: <20090812161016.2FEAD282EF4F@mail.pubhealth.ku.dk>

Full_Name: Kenneth Cater
Version: 2.9.1
OS: Linux x86_64
Submission from: (NULL) (171.159.192.10)


It appears that R closes my connection to the Netezza device before the Netezza
has completed its return of data, below are two files clean_test.log which
tracks the systems call made when running the test.r file, and clean_test.out
which is the output generated when test.r is executed.  Notice how the second
select systems call times out before all the data has been returned from the
device.  If you need any additional information please feel free to contact me
as this is a major priority for me, thank you for your assistance.

::::::::::::::
clean_test.log
::::::::::::::
socket(PF_INET, SOCK_STREAM, IPPROTO_IP) = 3
setsockopt(3, SOL_TCP, TCP_NODELAY, [1], 4) = 0
fcntl(3, F_GETFL)                       = 0x2 (flags O_RDWR)
fcntl(3, F_SETFL, O_RDWR)               = 0
connect(3, {sa_family=AF_INET, sin_port=htons(5480),
sin_addr=inet_addr("###.###.###.###")}, 16) = 0
sendto(3, "\0\0\0\10\0\1\0\2", 8, 0, NULL, 0) = 8
recvfrom(3, "N", 16384, 0, NULL, NULL)  = 1
sendto(3, "\0\0\0\r\0\2ECR_RND", 13, 0, NULL, 0) = 13
recvfrom(3, "N", 16384, 0, NULL, NULL)  = 1
sendto(3, "\0\0\0\r\0\3#######", 13, 0, NULL, 0) = 13
recvfrom(3, "N", 16384, 0, NULL, NULL)  = 1
sendto(3, "\0\0\0\n\0\t\0\3\0\4", 10, 0, NULL, 0) = 10
recvfrom(3, "N", 16384, 0, NULL, NULL)  = 1
sendto(3, "\0\0\0\n\0\6\0\0A\272", 10, 0, NULL, 0) = 10
recvfrom(3, "N", 16384, 0, NULL, NULL)  = 1
sendto(3, "\0\0\0\10\0\10\0\2", 8, 0, NULL, 0) = 8
recvfrom(3, "N", 16384, 0, NULL, NULL)  = 1
sendto(3, "\0\0\0\6\3\350", 6, 0, NULL, 0) = 6
recvfrom(3, "R\0\0\0\0054V", 16384, 0, NULL, NULL) = 7
sendto(3, "\0\0\0\0333prlIFXmej+6Oj0se6tVkA\0", 27, 0, NULL, 0) = 27
recvfrom(3, "R\0\0\0\0", 16384, 0, NULL, NULL) = 5
recvfrom(3, "K\0\0\0\0\0\0\0\10\0\0 \220\246\266\2151Z\0\0\0\0\0\0\0\0", 16384,
0, NULL, NULL) = 26
sendto(3, "P\0\0\0\1set nz_encoding to \'utf8\'\0", 31, 0, NULL, 0) = 31
recvfrom(3, "C\0\0\0\1\0\0\0\rSET VARIABLE\0Z\0\0\0\1\0\0\0\0", 16384, 0, NULL,
NULL) = 31
sendto(3, "P\0\0\0\2set DateStyle to \'ISO\'\0", 28, 0, NULL, 0) = 28
recvfrom(3, "C\0\0\0\2\0\0\0\rSET VARIABLE\0Z\0\0\0\2\0\0\0\0", 16384, 0, NULL,
NULL) = 31
sendto(3, "P\0\0\0\3select version()\0", 22, 0, NULL, 0) = 22
recvfrom(3, "P\0\0\0\3\0\0\0\6blank\0T\0\0\0\3\0\0\0\25\0\1VERSIO"..., 16384, 0,
NULL, NULL) = 114
sendto(3, "P\0\0\0\4select feature from _v_odbc"..., 66, 0, NULL, 0) = 66
recvfrom(3, "P\0\0\0\4\0\0\0\6blank\0T\0\0\0\4\0\0\0\25\0\1FEATUR"..., 16384, 0,
NULL, NULL) = 146
sendto(3, "P\0\0\0\5select identifier_case, cur"..., 59, 0, NULL, 0) = 59
recvfrom(3, "P\0\0\0\5\0\0\0\6blank\0T\0\0\0\5\0\0\0P\0\3IDENTI"..., 16384, 0,
NULL, NULL) = 174
write(1, "> ", 2)                       = 2
write(1, "conn\n", 5)                   = 5
getrusage(RUSAGE_SELF, {ru_utime={0, 357945}, ru_stime={0, 82987}, ...}) = 0
getrusage(RUSAGE_CHILDREN, {ru_utime={0, 0}, ru_stime={0, 999}, ...}) = 0
write(1, "RODBC Connection ", 17)       = 17
write(1, "1", 1)                        = 1
write(1, "\nDetails:\n  ", 12)          = 12
write(1, "case=nochange", 13)           = 13
write(1, "\n  ", 3)                     = 3
write(1, "DSN=ECR_RND", 11)             = 11
write(1, "\n  ", 3)                     = 3
write(1, "Database=ECR_RND", 16)        = 16
write(1, "\n  ", 3)                     = 3
write(1, "Servername=###.###.###.###", 23) = 23
write(1, "\n  ", 3)                     = 3
write(1, "Username=#######", 16)        = 16
write(1, "\n  ", 3)                     = 3
write(1, "Password=########", 17)       = 17
write(1, "\n  ", 3)                     = 3
write(1, "Port=5480", 9)                = 9
write(1, "\n  ", 3)                     = 3
write(1, "ReadOnly=0", 10)              = 10
write(1, "\n  ", 3)                     = 3
write(1, "SQLBitOneZero=0", 15)         = 15
write(1, "\n  ", 3)                     = 3
write(1, "LegacySQLTables=0", 17)       = 17
write(1, "\n  ", 3)                     = 3
write(1, "NumericAsChar=0", 15)         = 15
write(1, "\n  ", 3)                     = 3
write(1, "ShowSystemTables=0", 18)      = 18
write(1, "\n  ", 3)                     = 3
write(1, "LoginTimeout=0", 14)          = 14
write(1, "\n  ", 3)                     = 3
write(1, "QueryTimeout=200000", 19)     = 19
write(1, "\n  ", 3)                     = 3
write(1, "DateFormat=1", 12)            = 12
write(1, "\n", 1)                       = 1
write(1, "> ", 2)                       = 2
write(1, "SampleData = sqlQuery(conn, \"sel"..., 78) = 78
write(1, "+ ", 2)                       = 2
write(1, "\n", 1)                       = 1
write(1, "+ ", 2)                       = 2
write(1, "            TB_CH_CBSA_CODE_CBSA"..., 46) = 46
write(1, "+ ", 2)                       = 2
write(1, "\n", 1)                       = 1
write(1, "+ ", 2)                       = 2
write(1, "            where a.CBSA_CODE = "..., 45) = 45
write(1, "+ ", 2)                       = 2
write(1, "\n", 1)                       = 1
write(1, "+ ", 2)                       = 2
write(1, "            and a.CBSA_CODE in ("..., 91) = 91
getrusage(RUSAGE_SELF, {ru_utime={0, 359945}, ru_stime={0, 82987}, ...}) = 0
getrusage(RUSAGE_CHILDREN, {ru_utime={0, 0}, ru_stime={0, 999}, ...}) = 0
semop(594378758, 0x7fff36b1e510, 2)     = 0
semop(594378758, 0x7fff36b1e510, 1)     = 0
brk(0x72fb000)                          = 0x72fb000
sendto(3, "P\0\0\0\6select * from TB_CH_LEV_CBS"..., 175, 0, NULL, 0) = 175
select(4, [3], NULL, NULL, {200000, 0}) = 1 (in [3], left {199999, 215000})
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 4344
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 3848
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 4344
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 16384
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 12040
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
write(1, "> ", 2)                       = 2
write(1, "\n", 1)                       = 1
write(1, "> ", 2)                       = 2
write(1, "SampleData\n", 11)            = 11
getrusage(RUSAGE_SELF, {ru_utime={0, 361944}, ru_stime={0, 82987}, ...}) = 0
getrusage(RUSAGE_CHILDREN, {ru_utime={0, 0}, ru_stime={0, 999}, ...}) = 0
write(1, "character(0)\n", 13)          = 13
write(1, "> ", 2)                       = 2
write(1, "summary(SampleData)\n", 20)   = 20
getrusage(RUSAGE_SELF, {ru_utime={0, 361944}, ru_stime={0, 82987}, ...}) = 0
getrusage(RUSAGE_CHILDREN, {ru_utime={0, 0}, ru_stime={0, 999}, ...}) = 0
write(1, "   Length ", 10)              = 10
write(1, "    Class ", 10)              = 10
write(1, "     Mode ", 10)              = 10
write(1, "\n", 1)                       = 1
write(1, "        0 ", 10)              = 10
write(1, "character ", 10)              = 10
write(1, "character ", 10)              = 10
write(1, "\n", 1)                       = 1
write(1, "> ", 2)                       = 2
write(1, "close(conn)\n", 12)           = 12
getrusage(RUSAGE_SELF, {ru_utime={0, 362944}, ru_stime={0, 82987}, ...}) = 0
getrusage(RUSAGE_CHILDREN, {ru_utime={0, 0}, ru_stime={0, 999}, ...}) = 0
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 16384
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8688
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8688
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8648
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
...
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 8192
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 10592
recvfrom(3, "...DATA REDACTED..."..., 16384, 0, NULL, NULL) = 7545
select(4, [3], NULL, NULL, {0, 0})      = 0 (Timeout)
sendto(3, "X", 1, 0, NULL, 0)           = 1
close(3)                                = 0
semop(594378758, 0x7fff36b1e2f0, 2)     = 0
semop(594378758, 0x7fff36b1e2f0, 1)     = 0
write(1, "> ", 2)                       = 2
write(1, "\n", 1)                       = 1
write(1, "> ", 2)                       = 2
read(0, "", 4096)                       = 0
write(1, "\n", 1)                       = 1
rt_sigaction(SIGINT, {SIG_IGN}, {0x2aaaaadb8fa0, [INT], SA_RESTORER|SA_RESTART,
0x2aaaab4770b0}, 8) = 0
rt_sigaction(SIGQUIT, {SIG_IGN}, {SIG_IGN}, 8) = 0
rt_sigprocmask(SIG_BLOCK, [CHLD], [], 8) = 0
clone(child_stack=0, flags=CLONE_PARENT_SETTID|SIGCHLD,
parent_tidptr=0x7fff36b20808) = 16828
wait4(16828, [{WIFEXITED(s) && WEXITSTATUS(s) == 0}], 0, NULL) = 16828
rt_sigaction(SIGINT, {0x2aaaaadb8fa0, [INT], SA_RESTORER|SA_RESTART,
0x2aaaab4770b0}, NULL, 8) = 0
rt_sigaction(SIGQUIT, {SIG_IGN}, NULL, 8) = 0
rt_sigprocmask(SIG_SETMASK, [], NULL, 8) = 0
--- SIGCHLD (Child exited) @ 0 (0) ---
close(0)                                = 0
exit_group(0)                           = ?
::::::::::::::
clean_test.out
::::::::::::::

R version 2.9.1 (2009-06-26)
Copyright (C) 2009 The R Foundation for Statistical Computing
ISBN 3-900051-07-0

R is free software and comes with ABSOLUTELY NO WARRANTY.
You are welcome to redistribute it under certain conditions.
Type 'license()' or 'licence()' for distribution details.

  Natural language support but running in an English locale

R is a collaborative project with many contributors.
Type 'contributors()' for more information and
'citation()' on how to cite R or R packages in publications.

Type 'demo()' for some demos, 'help()' for on-line help, or
'help.start()' for an HTML browser interface to help.
Type 'q()' to quit R.

> library("RODBC")
> 
> conn = odbcConnect("ECR_RND",uid="#######", pwd="########")
> conn
RODBC Connection 1
Details:
  case=nochange
  DSN=ECR_RND
  Database=ECR_RND
  Servername=###.###.###.###
  Username=#######
  Password=########
  Port=5480
  ReadOnly=0
  SQLBitOneZero=0
  LegacySQLTables=0
  NumericAsChar=0
  ShowSystemTables=0
  LoginTimeout=0
  QueryTimeout=200000
  DateFormat=1
> SampleData = sqlQuery(conn, "select * from TB_CH_LEV_CBSA_PRODUCT_VINTAGE a, 
+ 
+             TB_CH_CBSA_CODE_CBSA_TITLE_XREF b
+ 
+             where a.CBSA_CODE = b.CBSA_CODE 
+ 
+             and a.CBSA_CODE in ('14460', '33100', '35620', '41860');",
believeNRows=FALSE)
> 
> SampleData
character(0)
> summary(SampleData)
   Length     Class      Mode 
        0 character character 
> close(conn)
> 
>



[Rd] Problem with Linux x86_64 RODBC and Netezza (PR#13889)
kenneth.cater at bankofamerica.com