You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafodion.apache.org by Radu Marias <ra...@gmail.com> on 2015/07/17 18:21:38 UTC
An invalid character value encountered in TRANSLATE function
Hi,
Using Trafodion Sandbox 1.1 with VirtualBox I get this error while loading
data in a table. The scenario is:
*>>create TABLE sna_persons (*
* id NUMERIC not null,*
* last_name varchar(70),*
* first_name varchar(30),*
* middle_name varchar(30),*
* sex char(1),*
* fraud_flag char(1),*
* party_id varchar(24),*
* primary key (id)*
*)*
*salt using 8 partitions on (id);*
*>>create index sna_persons_i1 on sna_persons(first_name)*
*salt like table;*
*>>create index sna_persons_i2 on sna_persons(last_name)*
*salt like table;*
*>>create index sna_persons_i3 on sna_persons(party_id) salt like table;*
*>>load into TRAFODION.SEABASE.SNA_PERSONS select * from
hive.hive.sna_persons;*
*Task: LOAD Status: Started Object:
TRAFODION.SEABASE.SNA_PERSONS*
*Task: CLEANUP Status: Started Object:
TRAFODION.SEABASE.SNA_PERSONS*
*Task: CLEANUP Status: Ended Object:
TRAFODION.SEABASE.SNA_PERSONS*
*Task: DISABLE INDEXE Status: Started Object:
TRAFODION.SEABASE.SNA_PERSONS*
*Task: DISABLE INDEXE Status: Ended Object:
TRAFODION.SEABASE.SNA_PERSONS*
*Task: PREPARATION Status: Started Object:
TRAFODION.SEABASE.SNA_PERSONS*
**** ERROR[8690] An invalid character value encountered in TRANSLATE
function.*
If I skip the LAST_NAME column it works. I cannot see anything specific in
logs, how can I determine which value is invalid? I have 1 million rows in
table, will not be easy to isolate it ;)
--
And in the end, it's not the years in your life that count. It's the life
in your years.
Fwd: An invalid character value encountered in TRANSLATE function
Posted by Qifan Chen <qi...@esgyn.com>.
Hi Radu,
To help debug the translation error, could you please turn on the update
stats logging, repeat the update stats command and send us the ULOG file
saved in the local directory?
>From sqlci, execute the following commands.
sh rm ULOG;
update statistics log on;
cqd ustat_log 'ulog';
update statistics ....
The log will contain the SQL statements used by the utility and hopefully
we can construct a test query later on that can pin point the invalid
character (if any).
Thanks
On Fri, Jul 17, 2015 at 12:17 PM, Amanda Moran <am...@esgyn.com>
wrote:
> Hi Radu-
>
> If you want to install the latest version of Trafodion (on your
> current sandbox) you will need from our downloads site:
> http://downloads.trafodion.org/
>
> The latest daily build of Trafodion and the newest Installer:
> installer-1.1.0_v2.tar.gz
> trafodion-20150716_0830.tar.gz
>
> Installation instructions are here:
> https://wiki.trafodion.org/wiki/index.php/Trafodion_Installation
>
> Please let me know if you have any issues with installing the latest
> version.
>
> Thanks!
> Amanda
>
> On Fri, Jul 17, 2015 at 10:03 AM, Selvaganesan Govindarajan
> <se...@esgyn.com> wrote:
> > Hi,
> >
> > LOAD command is enhanced to log error rows post 1.1. The new syntax for
> the LOAD command is
> >
> > load with log error rows into ...
> >
> > The error rows are logged into sub-directories at /bulkload/logs/ in
> hdfs.
> >
> > Is it possible to install the later version of Trafodion and try it out.
> >
> > Selva
> >
> > -----Original Message-----
> > From: Radu Marias [mailto:radumarias@gmail.com]
> > Sent: Friday, July 17, 2015 9:22 AM
> > To: dev@trafodion.incubator.apache.org
> > Subject: An invalid character value encountered in TRANSLATE function
> >
> > Hi,
> >
> > Using Trafodion Sandbox 1.1 with VirtualBox I get this error while
> loading data in a table. The scenario is:
> >
> > *>>create TABLE sna_persons (*
> > * id NUMERIC not null,*
> > * last_name varchar(70),*
> > * first_name varchar(30),*
> > * middle_name varchar(30),*
> > * sex char(1),*
> > * fraud_flag char(1),*
> > * party_id varchar(24),*
> > * primary key (id)*
> > *)*
> > *salt using 8 partitions on (id);*
> >
> > *>>create index sna_persons_i1 on sna_persons(first_name)* *salt like
> table;*
> >
> > *>>create index sna_persons_i2 on sna_persons(last_name)* *salt like
> table;*
> >
> > *>>create index sna_persons_i3 on sna_persons(party_id) salt like table;*
> >
> > *>>load into TRAFODION.SEABASE.SNA_PERSONS select * from
> > hive.hive.sna_persons;*
> > *Task: LOAD Status: Started Object:
> > TRAFODION.SEABASE.SNA_PERSONS*
> > *Task: CLEANUP Status: Started Object:
> > TRAFODION.SEABASE.SNA_PERSONS*
> > *Task: CLEANUP Status: Ended Object:
> > TRAFODION.SEABASE.SNA_PERSONS*
> > *Task: DISABLE INDEXE Status: Started Object:
> > TRAFODION.SEABASE.SNA_PERSONS*
> > *Task: DISABLE INDEXE Status: Ended Object:
> > TRAFODION.SEABASE.SNA_PERSONS*
> > *Task: PREPARATION Status: Started Object:
> > TRAFODION.SEABASE.SNA_PERSONS*
> >
> > **** ERROR[8690] An invalid character value encountered in TRANSLATE
> > function.*
> >
> > If I skip the LAST_NAME column it works. I cannot see anything specific
> in logs, how can I determine which value is invalid? I have 1 million rows
> in table, will not be easy to isolate it ;)
> >
> > --
> > And in the end, it's not the years in your life that count. It's the
> life in your years.
> >
>
>
>
> --
> Thanks,
>
> Amanda Moran
>
--
Regards, --Qifan
--
Regards, --Qifan
Re: An invalid character value encountered in TRANSLATE function
Posted by Qifan Chen <qi...@esgyn.com>.
Hi Renu,
To help debug the translation error, could you please turn on the update
stats logging, repeat the update stats command and send us the ULOG file
saved in the local directory?
>From sqlci, execute the following commands.
sh rm ULOG;
update statistics log on;
cqd ustat_log 'ulog';
update statistics ....
The log will contain the SQL statements used by the utility and hopefully
we can construct a test query later on that can pin point the invalid
character (if any).
Thanks
On Fri, Jul 17, 2015 at 12:17 PM, Amanda Moran <am...@esgyn.com>
wrote:
> Hi Radu-
>
> If you want to install the latest version of Trafodion (on your
> current sandbox) you will need from our downloads site:
> http://downloads.trafodion.org/
>
> The latest daily build of Trafodion and the newest Installer:
> installer-1.1.0_v2.tar.gz
> trafodion-20150716_0830.tar.gz
>
> Installation instructions are here:
> https://wiki.trafodion.org/wiki/index.php/Trafodion_Installation
>
> Please let me know if you have any issues with installing the latest
> version.
>
> Thanks!
> Amanda
>
> On Fri, Jul 17, 2015 at 10:03 AM, Selvaganesan Govindarajan
> <se...@esgyn.com> wrote:
> > Hi,
> >
> > LOAD command is enhanced to log error rows post 1.1. The new syntax for
> the LOAD command is
> >
> > load with log error rows into ...
> >
> > The error rows are logged into sub-directories at /bulkload/logs/ in
> hdfs.
> >
> > Is it possible to install the later version of Trafodion and try it out.
> >
> > Selva
> >
> > -----Original Message-----
> > From: Radu Marias [mailto:radumarias@gmail.com]
> > Sent: Friday, July 17, 2015 9:22 AM
> > To: dev@trafodion.incubator.apache.org
> > Subject: An invalid character value encountered in TRANSLATE function
> >
> > Hi,
> >
> > Using Trafodion Sandbox 1.1 with VirtualBox I get this error while
> loading data in a table. The scenario is:
> >
> > *>>create TABLE sna_persons (*
> > * id NUMERIC not null,*
> > * last_name varchar(70),*
> > * first_name varchar(30),*
> > * middle_name varchar(30),*
> > * sex char(1),*
> > * fraud_flag char(1),*
> > * party_id varchar(24),*
> > * primary key (id)*
> > *)*
> > *salt using 8 partitions on (id);*
> >
> > *>>create index sna_persons_i1 on sna_persons(first_name)* *salt like
> table;*
> >
> > *>>create index sna_persons_i2 on sna_persons(last_name)* *salt like
> table;*
> >
> > *>>create index sna_persons_i3 on sna_persons(party_id) salt like table;*
> >
> > *>>load into TRAFODION.SEABASE.SNA_PERSONS select * from
> > hive.hive.sna_persons;*
> > *Task: LOAD Status: Started Object:
> > TRAFODION.SEABASE.SNA_PERSONS*
> > *Task: CLEANUP Status: Started Object:
> > TRAFODION.SEABASE.SNA_PERSONS*
> > *Task: CLEANUP Status: Ended Object:
> > TRAFODION.SEABASE.SNA_PERSONS*
> > *Task: DISABLE INDEXE Status: Started Object:
> > TRAFODION.SEABASE.SNA_PERSONS*
> > *Task: DISABLE INDEXE Status: Ended Object:
> > TRAFODION.SEABASE.SNA_PERSONS*
> > *Task: PREPARATION Status: Started Object:
> > TRAFODION.SEABASE.SNA_PERSONS*
> >
> > **** ERROR[8690] An invalid character value encountered in TRANSLATE
> > function.*
> >
> > If I skip the LAST_NAME column it works. I cannot see anything specific
> in logs, how can I determine which value is invalid? I have 1 million rows
> in table, will not be easy to isolate it ;)
> >
> > --
> > And in the end, it's not the years in your life that count. It's the
> life in your years.
> >
>
>
>
> --
> Thanks,
>
> Amanda Moran
>
--
Regards, --Qifan
Re: An invalid character value encountered in TRANSLATE function
Posted by Amanda Moran <am...@esgyn.com>.
Hi Radu-
If you want to install the latest version of Trafodion (on your
current sandbox) you will need from our downloads site:
http://downloads.trafodion.org/
The latest daily build of Trafodion and the newest Installer:
installer-1.1.0_v2.tar.gz
trafodion-20150716_0830.tar.gz
Installation instructions are here:
https://wiki.trafodion.org/wiki/index.php/Trafodion_Installation
Please let me know if you have any issues with installing the latest version.
Thanks!
Amanda
On Fri, Jul 17, 2015 at 10:03 AM, Selvaganesan Govindarajan
<se...@esgyn.com> wrote:
> Hi,
>
> LOAD command is enhanced to log error rows post 1.1. The new syntax for the LOAD command is
>
> load with log error rows into ...
>
> The error rows are logged into sub-directories at /bulkload/logs/ in hdfs.
>
> Is it possible to install the later version of Trafodion and try it out.
>
> Selva
>
> -----Original Message-----
> From: Radu Marias [mailto:radumarias@gmail.com]
> Sent: Friday, July 17, 2015 9:22 AM
> To: dev@trafodion.incubator.apache.org
> Subject: An invalid character value encountered in TRANSLATE function
>
> Hi,
>
> Using Trafodion Sandbox 1.1 with VirtualBox I get this error while loading data in a table. The scenario is:
>
> *>>create TABLE sna_persons (*
> * id NUMERIC not null,*
> * last_name varchar(70),*
> * first_name varchar(30),*
> * middle_name varchar(30),*
> * sex char(1),*
> * fraud_flag char(1),*
> * party_id varchar(24),*
> * primary key (id)*
> *)*
> *salt using 8 partitions on (id);*
>
> *>>create index sna_persons_i1 on sna_persons(first_name)* *salt like table;*
>
> *>>create index sna_persons_i2 on sna_persons(last_name)* *salt like table;*
>
> *>>create index sna_persons_i3 on sna_persons(party_id) salt like table;*
>
> *>>load into TRAFODION.SEABASE.SNA_PERSONS select * from
> hive.hive.sna_persons;*
> *Task: LOAD Status: Started Object:
> TRAFODION.SEABASE.SNA_PERSONS*
> *Task: CLEANUP Status: Started Object:
> TRAFODION.SEABASE.SNA_PERSONS*
> *Task: CLEANUP Status: Ended Object:
> TRAFODION.SEABASE.SNA_PERSONS*
> *Task: DISABLE INDEXE Status: Started Object:
> TRAFODION.SEABASE.SNA_PERSONS*
> *Task: DISABLE INDEXE Status: Ended Object:
> TRAFODION.SEABASE.SNA_PERSONS*
> *Task: PREPARATION Status: Started Object:
> TRAFODION.SEABASE.SNA_PERSONS*
>
> **** ERROR[8690] An invalid character value encountered in TRANSLATE
> function.*
>
> If I skip the LAST_NAME column it works. I cannot see anything specific in logs, how can I determine which value is invalid? I have 1 million rows in table, will not be easy to isolate it ;)
>
> --
> And in the end, it's not the years in your life that count. It's the life in your years.
>
--
Thanks,
Amanda Moran
RE: An invalid character value encountered in TRANSLATE function
Posted by Selvaganesan Govindarajan <se...@esgyn.com>.
Hi,
LOAD command is enhanced to log error rows post 1.1. The new syntax for the LOAD command is
load with log error rows into ...
The error rows are logged into sub-directories at /bulkload/logs/ in hdfs.
Is it possible to install the later version of Trafodion and try it out.
Selva
-----Original Message-----
From: Radu Marias [mailto:radumarias@gmail.com]
Sent: Friday, July 17, 2015 9:22 AM
To: dev@trafodion.incubator.apache.org
Subject: An invalid character value encountered in TRANSLATE function
Hi,
Using Trafodion Sandbox 1.1 with VirtualBox I get this error while loading data in a table. The scenario is:
*>>create TABLE sna_persons (*
* id NUMERIC not null,*
* last_name varchar(70),*
* first_name varchar(30),*
* middle_name varchar(30),*
* sex char(1),*
* fraud_flag char(1),*
* party_id varchar(24),*
* primary key (id)*
*)*
*salt using 8 partitions on (id);*
*>>create index sna_persons_i1 on sna_persons(first_name)* *salt like table;*
*>>create index sna_persons_i2 on sna_persons(last_name)* *salt like table;*
*>>create index sna_persons_i3 on sna_persons(party_id) salt like table;*
*>>load into TRAFODION.SEABASE.SNA_PERSONS select * from
hive.hive.sna_persons;*
*Task: LOAD Status: Started Object:
TRAFODION.SEABASE.SNA_PERSONS*
*Task: CLEANUP Status: Started Object:
TRAFODION.SEABASE.SNA_PERSONS*
*Task: CLEANUP Status: Ended Object:
TRAFODION.SEABASE.SNA_PERSONS*
*Task: DISABLE INDEXE Status: Started Object:
TRAFODION.SEABASE.SNA_PERSONS*
*Task: DISABLE INDEXE Status: Ended Object:
TRAFODION.SEABASE.SNA_PERSONS*
*Task: PREPARATION Status: Started Object:
TRAFODION.SEABASE.SNA_PERSONS*
**** ERROR[8690] An invalid character value encountered in TRANSLATE
function.*
If I skip the LAST_NAME column it works. I cannot see anything specific in logs, how can I determine which value is invalid? I have 1 million rows in table, will not be easy to isolate it ;)
--
And in the end, it's not the years in your life that count. It's the life in your years.