[ https://issues.apache.org/jira/browse/TRAFODION-2769?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16210745#comment-16210745 ]
苏锦佩 edited comment on TRAFODION-2769 at 10/19/17 8:55 AM: ---------------------------------------------------------- I tried to reproduce this issue on latest version odb, but seems it has already been fixed. {code:java} -bash-4.1$ ./odb64luo -l src=sampledata.txt:tgt=starbucks:fs=\|:rows=K10 odb [2017-10-19 16:44:22]: starting ODBC connection(s)... 0 Connected to Trafodion [0] odb [Oloadbuff(9473)] - Error loading row 1 (State: 22003, Native 0) [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. Row: 1 Column: 6 >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude [0] odb [Oloadbuff(9473)] - Error loading row 1 (State: 22003, Native 0) [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. Row: 1 Column: 7 >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude [0] 21 records inserted [commit] [0] 43 records inserted [commit] [0] 65 records inserted [commit] [0] 87 records inserted [commit] [0] 109 records inserted [commit] [0] 131 records inserted [commit] [0] 153 records inserted [commit] [0] 175 records inserted [commit] [0] 197 records inserted [commit] [0] 219 records inserted [commit] [0] 228 records inserted [commit] [0] odb version 1.1.0 Load(2) statistics: [0] Target table: (null).(null).STARBUCKS [0] Source: sampledata.txt [0] Pre-loading time: 0.283 s (00:00:00.283) [0] Loading time: 0.325 s(00:00:00.325) [0] Total records read: 229 [0] Total records inserted: 228 [0] Total number of columns: 7 [0] Total bytes read: 21,187 [0] Average input row size: 92.5 B [0] ODBC row size: 400 B (data) + 56 B (len ind) [0] Rowset size: 22 [0] Rowset buffer size: 9.80 KiB [0] Load throughput (real data): 63.663 KiB/s [0] Load throughput (ODBC): 274.038 KiB/s odb [2017-10-19 16:44:22]: exiting. Session Elapsed time 0.622 seconds (00:00:00.622) -bash-4.1$ ./odb64luo -l src=sampledata.txt:tgt=starbucks:fs=\| odb [2017-10-19 16:45:01]: starting ODBC connection(s)... 0 Connected to Trafodion [0] odb [Oloadbuff(9473)] - Error loading row 1 (State: 22003, Native 0) [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. Row: 1 Column: 6 >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude [0] odb [Oloadbuff(9473)] - Error loading row 1 (State: 22003, Native 0) [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. Row: 1 Column: 7 >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude [0] 99 records inserted [commit] [0] 199 records inserted [commit] [0] 228 records inserted [commit] [0] odb version 1.1.0 Load(2) statistics: [0] Target table: (null).(null).STARBUCKS [0] Source: sampledata.txt [0] Pre-loading time: 0.285 s (00:00:00.285) [0] Loading time: 0.201 s(00:00:00.201) [0] Total records read: 229 [0] Total records inserted: 228 [0] Total number of columns: 7 [0] Total bytes read: 21,187 [0] Average input row size: 92.5 B [0] ODBC row size: 400 B (data) + 56 B (len ind) [0] Rowset size: 100 [0] Rowset buffer size: 44.53 KiB [0] Load throughput (real data): 102.937 KiB/s [0] Load throughput (ODBC): 443.097 KiB/s odb [2017-10-19 16:45:02]: exiting. Session Elapsed time 0.501 seconds (00:00:00.501) -bash-4.1$ ./odb64luo -l src=sampledata.txt:tgt=starbucks:fs=\|:rows=M10 odb [2017-10-19 16:45:12]: starting ODBC connection(s)... 0 Connected to Trafodion [0] odb [Oloadbuff(9473)] - Error loading row 1 (State: 22003, Native 0) [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. Row: 1 Column: 6 >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude [0] odb [Oloadbuff(9473)] - Error loading row 1 (State: 22003, Native 0) [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. Row: 1 Column: 7 >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude [0] 228 records inserted [commit] [0] odb version 1.1.0 Load(2) statistics: [0] Target table: (null).(null).STARBUCKS [0] Source: sampledata.txt [0] Pre-loading time: 0.285 s (00:00:00.285) [0] Loading time: 0.182 s(00:00:00.182) [0] Total records read: 229 [0] Total records inserted: 228 [0] Total number of columns: 7 [0] Total bytes read: 21,187 [0] Average input row size: 92.5 B [0] ODBC row size: 400 B (data) + 56 B (len ind) [0] Rowset size: 22,995 [0] Rowset buffer size: 10,239.96 KiB [0] Load throughput (real data): 113.684 KiB/s [0] Load throughput (ODBC): 489.354 KiB/s odb [2017-10-19 16:45:13]: exiting. Session Elapsed time 0.486 seconds (00:00:00.486) {code} was (Author: sujinpei): I tried to reproduce this issue on latest version odb, but seems it has already been fixed. {panel:title=My title} -bash-4.1$ ./odb64luo -l src=sampledata.txt:tgt=starbucks:fs=\|:rows=K10 odb [2017-10-19 16:44:22]: starting ODBC connection(s)... 0 Connected to Trafodion [0] odb [Oloadbuff(9473)] - Error loading row 1 (State: 22003, Native 0) [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. Row: 1 Column: 6 >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude [0] odb [Oloadbuff(9473)] - Error loading row 1 (State: 22003, Native 0) [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. Row: 1 Column: 7 >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude [0] 21 records inserted [commit] [0] 43 records inserted [commit] [0] 65 records inserted [commit] [0] 87 records inserted [commit] [0] 109 records inserted [commit] [0] 131 records inserted [commit] [0] 153 records inserted [commit] [0] 175 records inserted [commit] [0] 197 records inserted [commit] [0] 219 records inserted [commit] [0] 228 records inserted [commit] [0] odb version 1.1.0 Load(2) statistics: [0] Target table: (null).(null).STARBUCKS [0] Source: sampledata.txt [0] Pre-loading time: 0.283 s (00:00:00.283) [0] Loading time: 0.325 s(00:00:00.325) [0] Total records read: 229 [0] Total records inserted: 228 [0] Total number of columns: 7 [0] Total bytes read: 21,187 [0] Average input row size: 92.5 B [0] ODBC row size: 400 B (data) + 56 B (len ind) [0] Rowset size: 22 [0] Rowset buffer size: 9.80 KiB [0] Load throughput (real data): 63.663 KiB/s [0] Load throughput (ODBC): 274.038 KiB/s odb [2017-10-19 16:44:22]: exiting. Session Elapsed time 0.622 seconds (00:00:00.622) -bash-4.1$ ./odb64luo -l src=sampledata.txt:tgt=starbucks:fs=\| odb [2017-10-19 16:45:01]: starting ODBC connection(s)... 0 Connected to Trafodion [0] odb [Oloadbuff(9473)] - Error loading row 1 (State: 22003, Native 0) [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. Row: 1 Column: 6 >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude [0] odb [Oloadbuff(9473)] - Error loading row 1 (State: 22003, Native 0) [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. Row: 1 Column: 7 >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude [0] 99 records inserted [commit] [0] 199 records inserted [commit] [0] 228 records inserted [commit] [0] odb version 1.1.0 Load(2) statistics: [0] Target table: (null).(null).STARBUCKS [0] Source: sampledata.txt [0] Pre-loading time: 0.285 s (00:00:00.285) [0] Loading time: 0.201 s(00:00:00.201) [0] Total records read: 229 [0] Total records inserted: 228 [0] Total number of columns: 7 [0] Total bytes read: 21,187 [0] Average input row size: 92.5 B [0] ODBC row size: 400 B (data) + 56 B (len ind) [0] Rowset size: 100 [0] Rowset buffer size: 44.53 KiB [0] Load throughput (real data): 102.937 KiB/s [0] Load throughput (ODBC): 443.097 KiB/s odb [2017-10-19 16:45:02]: exiting. Session Elapsed time 0.501 seconds (00:00:00.501) -bash-4.1$ ./odb64luo -l src=sampledata.txt:tgt=starbucks:fs=\|:rows=M10 odb [2017-10-19 16:45:12]: starting ODBC connection(s)... 0 Connected to Trafodion [0] odb [Oloadbuff(9473)] - Error loading row 1 (State: 22003, Native 0) [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. Row: 1 Column: 6 >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude [0] odb [Oloadbuff(9473)] - Error loading row 1 (State: 22003, Native 0) [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. Row: 1 Column: 7 >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude [0] 228 records inserted [commit] [0] odb version 1.1.0 Load(2) statistics: [0] Target table: (null).(null).STARBUCKS [0] Source: sampledata.txt [0] Pre-loading time: 0.285 s (00:00:00.285) [0] Loading time: 0.182 s(00:00:00.182) [0] Total records read: 229 [0] Total records inserted: 228 [0] Total number of columns: 7 [0] Total bytes read: 21,187 [0] Average input row size: 92.5 B [0] ODBC row size: 400 B (data) + 56 B (len ind) [0] Rowset size: 22,995 [0] Rowset buffer size: 10,239.96 KiB [0] Load throughput (real data): 113.684 KiB/s [0] Load throughput (ODBC): 489.354 KiB/s odb [2017-10-19 16:45:13]: exiting. Session Elapsed time 0.486 seconds (00:00:00.486) {panel} > ODB only inserts the last rowset, others are ignored > ---------------------------------------------------- > > Key: TRAFODION-2769 > URL: https://issues.apache.org/jira/browse/TRAFODION-2769 > Project: Apache Trafodion > Issue Type: Bug > Components: db-utility-odb > Affects Versions: 2.1-incubating > Environment: Any > Reporter: Hans Zeller > Assignee: Suresh Subbiah > Attachments: sampledata.txt > > > This may be a user error, it's my first attempt at using ODB. Here is what I > did: > Created the following table: > {noformat} > create table starbucks( > Name char(64 bytes) character set utf8, > Street char(200 bytes) character set utf8, > City char(64 bytes) character set utf8, > Country char(16 bytes) character set utf8, > PostalCode char(12 bytes) character set utf8, > Latitude double precision, > Longitude double precision); > {noformat} > Then, used the following odb command: > {noformat} > odb64luo -u trafodion -p traf123 -d traf -l > src=sampledata:tgt=trafodion.seabase.starbucks:fs=\| > {noformat} > The sample data is attached (it's from a public database). It has a list of > 228 Starbucks stores. > When I try with different row set sizes (values for the rows= parameter), it > seems to me that all but the last rowset is just ignored, only the last one > is inserted. Using a buffer big enough for all the rows leads to a successful > insert. > This is the odb output I got for various rowsets: > {noformat} > $ odb64luo -u trafodion -p traf123 -d traf -l > src=sampledata:tgt=trafodion.seabase.starbucks:fs=\| > odb [2017-10-09 23:46:50]: starting ODBC connection(s)... 0 > Connected to Trafodion > [0] 0 records inserted [commit] > [0] 0 records inserted [commit] > [0] 29 records inserted [commit] > [0] odb version 1.1.0 Load(2) statistics: > [0] Target table: TRAFODION.SEABASE.STARBUCKS > [0] Source: sampledata > [0] Pre-loading time: 0.212 s (00:00:00.212) > [0] Loading time: 0.021 s(00:00:00.021) > [0] Total records read: 229 > [0] Total records inserted: 29 > [0] Total number of columns: 7 > [0] Total bytes read: 21,187 > [0] Average input row size: 92.5 B > [0] ODBC row size: 400 B (data) + 56 B (len ind) > [0] Rowset size: 100 > [0] Rowset buffer size: 44.53 KiB > [0] Load throughput (real data): 985.259 KiB/s > [0] Load throughput (ODBC): 539.435 KiB/s > odb [2017-10-09 23:46:50]: exiting. Session Elapsed time 0.239 seconds > (00:00:00.239) > $ odb64luo -u trafodion -p traf123 -d traf -l > src=sampledata:tgt=trafodion.seabase.starbucks:fs=\|:rows=M10 > odb [2017-10-09 23:47:45]: starting ODBC connection(s)... 0 > Connected to Trafodion > [0] odb [Oloadbuff(9474)] - Error loading row 1 (State: 22003, Native 0) > [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. > Row: 1 Column: 6 > >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude > [0] odb [Oloadbuff(9474)] - Error loading row 1 (State: 22003, Native 0) > [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. > Row: 1 Column: 7 > >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude > [0] 228 records inserted [commit] > [0] odb version 1.1.0 Load(2) statistics: > [0] Target table: TRAFODION.SEABASE.STARBUCKS > [0] Source: sampledata > [0] Pre-loading time: 0.225 s (00:00:00.225) > [0] Loading time: 0.118 s(00:00:00.118) > [0] Total records read: 229 > [0] Total records inserted: 228 > [0] Total number of columns: 7 > [0] Total bytes read: 21,187 > [0] Average input row size: 92.5 B > [0] ODBC row size: 400 B (data) + 56 B (len ind) > [0] Rowset size: 22,995 > [0] Rowset buffer size: 10,239.96 KiB > [0] Load throughput (real data): 175.343 KiB/s > [0] Load throughput (ODBC): 754.767 KiB/s > odb [2017-10-09 23:47:46]: exiting. Session Elapsed time 0.351 seconds > (00:00:00.351) > $ odb64luo -u trafodion -p traf123 -d traf -l > src=sampledata:tgt=trafodion.seabase.starbucks:fs=\|:rows=M1 > odb [2017-10-09 23:48:04]: starting ODBC connection(s)... 0 > Connected to Trafodion > [0] odb [Oloadbuff(9474)] - Error loading row 1 (State: 22003, Native 0) > [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. > Row: 1 Column: 6 > >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude > [0] odb [Oloadbuff(9474)] - Error loading row 1 (State: 22003, Native 0) > [Trafodion ODBC Driver] NUMERIC VALUE OUT OF RANGE. Incorrect Format or Data. > Row: 1 Column: 7 > >>> Name|Street Combined|City|Country|Postal Code|Latitude|Longitude > [0] 228 records inserted [commit] > [0] odb version 1.1.0 Load(2) statistics: > [0] Target table: TRAFODION.SEABASE.STARBUCKS > [0] Source: sampledata > [0] Pre-loading time: 0.221 s (00:00:00.221) > [0] Loading time: 0.095 s(00:00:00.095) > [0] Total records read: 229 > [0] Total records inserted: 228 > [0] Total number of columns: 7 > [0] Total bytes read: 21,187 > [0] Average input row size: 92.5 B > [0] ODBC row size: 400 B (data) + 56 B (len ind) > [0] Rowset size: 2,299 > [0] Rowset buffer size: 1,023.77 KiB > [0] Load throughput (real data): 217.794 KiB/s > [0] Load throughput (ODBC): 937.500 KiB/s > odb [2017-10-09 23:48:04]: exiting. Session Elapsed time 0.323 seconds > (00:00:00.323) > $ odb64luo -u trafodion -p traf123 -d traf -l > src=sampledata:tgt=trafodion.seabase.starbucks:fs=\|:rows=k10 > odb [2017-10-09 23:48:13]: starting ODBC connection(s)... 0 > Connected to Trafodion > [0] 0 records inserted [commit] > [0] 0 records inserted [commit] > [0] 0 records inserted [commit] > [0] 0 records inserted [commit] > [0] 0 records inserted [commit] > [0] 0 records inserted [commit] > [0] 0 records inserted [commit] > [0] 0 records inserted [commit] > [0] 0 records inserted [commit] > [0] 0 records inserted [commit] > [0] 9 records inserted [commit] > [0] odb version 1.1.0 Load(2) statistics: > [0] Target table: TRAFODION.SEABASE.STARBUCKS > [0] Source: sampledata > [0] Pre-loading time: 0.220 s (00:00:00.220) > [0] Loading time: 0.010 s(00:00:00.010) > [0] Total records read: 229 > [0] Total records inserted: 9 > [0] Total number of columns: 7 > [0] Total bytes read: 21,187 > [0] Average input row size: 92.5 B > [0] ODBC row size: 400 B (data) + 56 B (len ind) > [0] Rowset size: 22 > [0] Rowset buffer size: 9.80 KiB > [0] Load throughput (real data): 2,069.043 KiB/s > [0] Load throughput (ODBC): 351.562 KiB/s > odb [2017-10-09 23:48:14]: exiting. Session Elapsed time 0.236 seconds > (00:00:00.236) > [trafodion@nap007 kmeans]$ sqlci > {noformat} -- This message was sent by Atlassian JIRA (v6.4.14#64029)