You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Hans Zeller (JIRA)" <ji...@apache.org> on 2017/10/10 00:18:00 UTC
[jira] [Updated] (TRAFODION-2769) ODB only inserts the last rowset,
others are ignored
[ https://issues.apache.org/jira/browse/TRAFODION-2769?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Hans Zeller updated TRAFODION-2769:
-----------------------------------
Description:
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).
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}
was:
This may be a user error, it's my first attempt at using ODB. Here is what I did:
Created the following table:
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);
Then, used the following odb command:
odb64luo -u trafodion -p traf123 -d traf -l src=sampledata:tgt=trafodion.seabase.starbucks:fs=\|
The sample data is attached (it's from a public database).
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:
$ 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
> 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).
> 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)