from:"Anoop Sharma"

RE: how to setup/enable/disable AQR

2017-08-09 Thread Anoop Sharma

To disable aqr for a particular error(for example, error 8551), do:
  set session default aqr_entries '- 8551';

To add an error number, do:
  set session default aqr_entries '+ 8551';

Other aqr parameters can also be added.
For ex,
  set session default aqr_entries '+ 8551, 73, 2, 120';
Will do aqr for 8551/73(primary error 8551, secondary error 73), 
will retry 2 times, with delay of 120 secs between each retry.

To see all current aqr errors, do:
  get all aqr entries;

These set statements apply to that session only.
They cannot be added to defaults table.


anoop

-Original Message-
From: Sandhya Sundaresan [mailto:sandhya.sundare...@esgyn.com] 
Sent: Tuesday, August 8, 2017 10:39 PM
To: d...@trafodion.incubator.apache.org; user@trafodion.incubator.apache.org
Subject: RE: how to setup/enable/disable AQR

Hi Ming,

  Yes use the Cqd  AUTO_QUERY_RETRY and set it  to 'OFF'.
If you want to disable it completely set it in the defaults table. 

Sandhya

-Original Message-
From: Liu, Ming (Ming) [mailto:ming@esgyn.cn] 
Sent: Tuesday, August 8, 2017 10:36 PM
To: d...@trafodion.incubator.apache.org; user@trafodion.incubator.apache.org
Subject: how to setup/enable/disable AQR

Hi, all,

I want to disable AQR in Trafodion , is this possible? Or how should I disable 
AQR for specific SQL error?

Thanks,
Ming

RE: change the connection count

2017-07-31 Thread Anoop Sharma

mxosrvrs take some time to start and it may be a little while before they all 
come up.
dcscheck will show the running count as they come up.
If the ‘Down’ count is not zero after a few mins, then there is a problem.

From: Dave Birdsall [mailto:dave.birds...@esgyn.com]
Sent: Monday, July 31, 2017 8:32 AM
To: user@trafodion.incubator.apache.org
Subject: RE: change the connection count

Actual means that number of mxosrvrs are up and running. Down means some 
mxosrvrs did not come up. I’ll let others comment on what might cause mxsosrvrs 
to not come up.

From: Huang, Jack [mailto:jack.hu...@dell.com]
Sent: Sunday, July 30, 2017 9:26 PM
To: 
user@trafodion.incubator.apache.org
Subject: RE: change the connection count

Thanks all. Now I can reconfig the mxosrvr number as follow.
That’s right we need to change the /conf/servers and conf/dcs-site.xml related 
numbers
Still a question about the number of actual and down. What’s mean about the 
field?

[trafodion@trafodion ~]$ dcsstart

*** Checking Trafodion Environment ***

Checking if processes are up.
Checking attempt: 1; user specified max: 1. Execution time in seconds: 0.

The Trafodion environment is up!

Process Configured  Actual  Down
--- --  --  
DTM 2   2
RMS 4   4
DcsMaster   1   0   1
DcsServer   1   0   1
mxosrvr 1   0   1
RestServer  1   1

Starting the DCS environment now
starting master, logging to 
/home/trafodion/apache-trafodion-2.1.0/dcs-2.1.0/bin/../logs/dcs-trafodion-1-master-trafodion.out
trafodion: starting server, logging to 
/home/trafodion/apache-trafodion-2.1.0/dcs-2.1.0/bin/../logs/dcs-trafodion-1-server-trafodion.out
[trafodion@trafodion ~]$ dcscheck
Cluster Running  : Non-HA

Zookeeper listen port: 2181
DcsMaster listen port: 23400

Configured Primary DcsMaster: "trafodion"
Active DcsMaster: "15119 DcsMaster"

Process Configured  Actual  Down
-   --  --  
DcsMaster   1   1
DcsServer   1   1
mxosrvr 1   91  9909

Jack Huang
Dell EMC | CTD MRES Cyclone Group
mobile +86-13880577652
jack.hu...@dell.com
[dell_emc_proven_badge_RGB_small]

From: Wang, Ai-Min (Benny) [mailto:aimin.w...@esgyn.cn]
Sent: Monday, July 31, 2017 9:21 AM
To: 
user@trafodion.incubator.apache.org
Subject: RE: change the connection count

Hi:

Maybe there is a limitation for 98 numbers of connections per node. So also you 
need to modify the dcs-site.xml file to increase the port range to allow more 
connections like this.

$MY_SQROOT/dcs*/conf/dcs-site.xml:
dcs.master.port.range
200

Default range of ports.

Best Regrard！

Benny/Wang Aimin
MP：13501196050
Mail：aimin.w...@esgyn.cn

From: Suresh Subbiah [mailto:suresh.subbia...@gmail.com]
Sent: Sunday, July 30, 2017 12:16 AM
To: 
user@trafodion.incubator.apache.org
Subject: Re: change the connection count

Hi,

This may not be the suggested approach. I use it on my dev environments and it 
work OK.

Please edit the file $DCS_INSTALL_DIR/conf/servers
There should be two lines there with the value 50 at the end of each line. 
Change the numbers as you wish.
Save the file and copy it to the other node, at the same location.
Stop DCS with dcsstop. Check all mxosrvrs are down with dcscheck. Start DCS 
with dcsstart.
You should now see as many mxosrvrs as currently configured.

Thanks
Suresh

On Sat, Jul 29, 2017 at 2:19 AM, Huang, Jack 
> wrote:
Hi Trafodioner,
I installed the trafodion with the listed configuration.
As a beginner, I set the dcs_cnt_per_node as 100 in my installation, and sqlci 
display like this.
After some testing, I found the count number is not enough for my testing.
So the question is how can I change the number but did not any re-installation?

Process Configured  Actual  Down

--- --  --  

DTM 2   2

RMS 4   4

DcsMaster   1   1

DcsServer   1   1

mxosrvr 100 98  2

RestServer  1   1

+--+--+

| config type  | value  
  |

+--+--+

| dcs_cnt_per_node | 100
  |

| dcs_ha   | N

RE: is there a way to disable a specific check constraint without dropping it?

2017-06-19 Thread Anoop Sharma

when a constraint (check, unique or RI) is added to a table with existing data,
then that constraint is validated against existing data.
An error is returned and constraint creation fails if data validation fails.

For check constr failure, this error is returned:
>>alter table tc add constraint tc1 check (a < 10);

*** ERROR[1083] Validation for constraint TRAFODION.SCH.TC1 failed; 
incompatible data exists in table.

--- SQL operation failed with errors.
>>

anoop
From: Eric Owhadi [mailto:eric.owh...@esgyn.com]
Sent: Monday, June 19, 2017 12:26 PM
To: user@trafodion.incubator.apache.org
Subject: RE: is there a way to disable a specific check constraint without 
dropping it?

I don’t believe constraints are checked against existing data. Only new data 
upserted are being validated. Am I correct?
Meaning if I add constraint on existing data, there is no complaining about 
existing data violating constraints?
Eric

From: Rohit Jain [mailto:rohit.j...@esgyn.com]
Sent: Monday, June 19, 2017 2:19 PM
To: 
user@trafodion.incubator.apache.org
Subject: Re: is there a way to disable a specific check constraint without 
dropping it?

Right, because re-enabling will have to recheck the constraint anyway.

Rohit

On Jun 19, 2017, at 1:26 PM, Eric Owhadi 
> wrote:
Hi Dave,
It was a trick I was trying to use to store sql statement associated with a 
table as a check  constraint, without enforcing it.
I now know I can use the _MD_.TEXT for this purpose, so no worries about this 
feature anymore.
Thanks,
Eric

From: Dave Birdsall [mailto:dave.birds...@esgyn.com]
Sent: Monday, June 19, 2017 11:22 AM
To: 
user@trafodion.incubator.apache.org
Subject: RE: is there a way to disable a specific check constraint without 
dropping it?

Hi Eric,

There doesn’t appear to be.

I noticed the SQL parser does have such syntax, but the productions return an 
error if invoked. So someone thought about implementing ALTER TABLE  
DISABLE ALL CONSTRAINTS at one point but did not complete it.

Just curious: What’s the use case? Why does dropping and then recreating not 
work?

Dave

From: Eric Owhadi [mailto:eric.owh...@esgyn.com]
Sent: Friday, June 16, 2017 8:35 AM
To: 
user@trafodion.incubator.apache.org
Subject: is there a way to disable a specific check constraint without dropping 
it?

Hi Trafodioneers,
I am wondering if there is a way to disable check constraint without dropping 
them?
Is there?
Thanks in advance for the help,
Eric

RE: does trafodion have null value in the index?

2017-06-01 Thread Anoop Sharma

traf indexes can be created on nullable columns and they can contain null 
values.
If it is a unique index, then only one null value can be inserted.
If it is a non-unique index, then multiple null values can be inserted.
both 'is null' and 'is not null' preds can be applied on index scans.

See examples below:

>>create table t (a int not null primary key, b int);

--- SQL operation complete.
>>create index ti on t(b);

--- SQL operation complete.
>>insert into t values (1,null), (2, 2), (3, null);

--- 3 row(s) inserted.
>>explain options 'f' select b from t;

LC   RC   OP   OPERATOR  OPT   DESCRIPTION   CARD
         -

1.2root  1.00E+002
..1trafodion_index_scanTI1.00E+002

--- SQL operation complete.
>>select b from t;

B
---

  2
  ?
  ?

--- 3 row(s) selected.
>>select b from t where b is null;

B
---

  ?
  ?

--- 2 row(s) selected.
>>explain options 'f' select b from t where b is null;

LC   RC   OP   OPERATOR  OPT   DESCRIPTION   CARD
         -

1.2root  1.00E+000
..1trafodion_index_scanTI1.00E+000

--- SQL operation complete.
>>select b from t where b is not null;

B
---

  2

--- 1 row(s) selected.
>>
>>create unique index ti2 on t(b);

*** ERROR[8110] Duplicate rows detected.

*** ERROR[1053] Unique index TRAFODION.SCH.TI2 could not be created because the 
specified column(s) contain duplicate data.

--- SQL operation failed with errors.
>>delete from t;

--- 3 row(s) deleted.
>>insert into t values (1,null);

--- 1 row(s) inserted.
>>
>>create unique index ti2 on t(b);

--- SQL operation complete.
>>select b from t;

B
---

  ?

--- 1 row(s) selected.
>>

From: Liu, Ming (Ming) [mailto:ming@esgyn.cn]
Sent: Wednesday, May 31, 2017 11:43 PM
To: user@trafodion.incubator.apache.org
Subject: does trafodion have null value in the index?

Hi, all,

I heard that database index cannot save null value, so if the predicate 
contains 'is null' 'is not null', then the index will not be used. Is this true 
for Trafodion as well?

thanks,
Ming

RE: auto generated primary keys

2017-05-20 Thread Anoop Sharma

hi, 

trafodion supports identity columns that can be used to auto-generate primary 
keys.
Details are in SQL Reference manual
 http://trafodion.apache.org/docs/sql_reference/index.html#identity_column

There is also support for implicit SYSKEY column which is used to automatically 
generate a unique
value if there are no user specified primary key columns.

anoop
-Original Message-
From: pieter gmail [mailto:pieter.mar...@gmail.com] 
Sent: Saturday, May 20, 2017 10:41 AM
To: user@trafodion.incubator.apache.org
Subject: RE: auto generated primary keys

Hi,

Does/will trafodion support auto generated primary keys?
I can not see any mention of it in the docs.

Thanks
Pieter

RE: [Question]Does Trafodion support order by Chinese character?

2017-05-18 Thread Anoop Sharma

traf characters will be compared using binary collation based on
the contents of column ‘a’.
If some other non-binary collation is needed to correctly order
chinese characters, then that is not supported.

Can you run a query comparing these characters and see if the comparison
is correct. Something like:
select a from t1 where string1 < string2
(here string1, string2 are Chinese character strings and string1 should
collate lower than string2 in Chinese characters)

anoop


From: Liu, Yuan (Yuan) [mailto:yuan@esgyn.cn]
Sent: Wednesday, May 17, 2017 11:23 PM
To: user@trafodion.incubator.apache.org
Subject: [Question]Does Trafodion support order by Chinese character?

Hi,

I’d like to ask a quick question here: Does Trafodion support order by Chinese 
characters?

For example,

SQL>select a from t1 order by a;

A

他
你
我

--- 3 row(s) selected.

It seems the result is not correct. Can anyone help confirm?

Best regards,
Yuan
Email: yuan@esgyn.cn
Cellphone: (+86) 13671935540

RE: A way to recreate the _REPOS_ tables?

2016-06-08 Thread Anoop Sharma

initialize trafodion, drop repository;

initialize trafodion, create repository;



this will only drop and recreate repos, it will

not initialize/reinit traf.



anoop



*From:* Wei-Shiun Tsai [mailto:wei-shiun.t...@esgyn.com]
*Sent:* Wednesday, June 8, 2016 11:21 AM
*To:* user@trafodion.incubator.apache.org
*Subject:* A way to recreate the _REPOS_ tables?



Hi,



Does anybody know if there a way to drop and recreate the _*REPOS*_ tables
in Trafodion without having to re-initialize Trafodion?



Thanks,



Weishiun

RE: 答复: Does Trafodion support to use 'order by' in a subquery

2016-03-24 Thread Anoop Sharma

It is not allowed in ansi and is incorrect syntax which is

why a syntax error is returned. 

 

But we allow it in special mode. This was done for compatibility

with non-ansi syntax.

 

>>cqd mode_special_4 'ON';

 

--- SQL operation complete.

>> 

>>select * from (select * from t order by a)x(a);

 

--- 0 row(s) selected.

>> 

 



 Original message 
From: "Liu, Ming (Ming)"  > 
Date: 03/24/2016 8:42 AM (GMT-06:00) 
To: Rohit Jain  >, 
user@trafodion.incubator.apache.org 
  
Subject: 答复: Does Trafodion support to use 'order by' in a subquery 

Thanks Rohit,

 

That is good to know, after think it again, yes, this is wrong in the first 
place. I tested in Hive, it doesn’t support that syntax either, but seems some 
RDBMS (Oracle, DB2) allow this syntax but ignore it. 

 

However, It will be more user friendly that optimizer or parser simply give a 
warning or ignore it, because other database allows this, but since itself is 
wrong at first, so this ‘enhancement’ is not necessary, just if Trafodion could 
do that, that seems more flexible. 

 

Thanks,

Ming

 

发件人: Rohit [mailto:rohit.j...@esgyn.com] 
发送时间: 2016年3月24日 18:12
收件人: Liu, Ming (Ming)  >; 
user@trafodion.incubator.apache.org 
 
主题: RE: Does Trafodion support to use 'order by' in a subquery

 

Order by is allowed only on the outermost select in ANSI. It is meant to define 
the order of the final results of the query returned to the user. It is not 
allowed in sub queries.

 

Rohit



 Original message 
From: "Liu, Ming (Ming)"  > 
Date: 03/24/2016 2:15 AM (GMT-06:00) 
To: user@trafodion.incubator.apache.org 
  
Subject: Does Trafodion support to use 'order by' in a subquery 

Hi, all,

 

I don’t know if this is same in other database, but in Trafodion, if I want to 
have a subquery which have a ‘order by’, it seems not allowed. Is this normal 
or a limitation in Trafodion?

For example:

 

create table t1 ( c1 int, c2 int);

 

select * from (

   Select * from t1 order by c1);

*** ERROR[15001] A syntax error occurred at or before:

select * from (select * from t1 order by c1);

   ^ (44 characters from start of SQL 
statement)

 

Thanks in advance,

Ming

RE: Trafodion support of TO_DATE

2016-03-19 Thread Anoop Sharma

 

You will have to convert the TO_DATE part of the query to an explicit
datetime literal that looks like:

   timestamp '2016-03-17 11:47:06'

 

With TO_DATE support (this is on trafodion now but will be externally
available as part of Traf 2.0), you

can use the statement that you have listed.

But.the format part need to match the string datetime value. In your
example, it doesn't.

The correct syntax will be:

  to_date('20160317114706', 'MMDDHH24MISS')

 

or

 

  to_date('2016-03-17 11:47:06', '-MM-DD HH24:MI:SS')

 

anoop

 

From: Liu, Ming (Ming) [mailto:ming@esgyn.cn] 
Sent: Wednesday, March 16, 2016 8:51 PM
To: user@trafodion.incubator.apache.org
Subject: Trafodion support of TO_DATE

 

Hi, all,

 

I know there will be a new feature to support TO_DATE, currently, without
this feature, is there any way to migrate a query using TO_DATE?

 

Example:

insert into myTbl (staff_id,
orbat_code,operate_time,ip_arr,browser_version,orbat_desc)
values('ADMIN','V1369930001010',to_date('20160317114706','-MM-dd
hh24:mi:ss')  

 

 

thanks,

Ming

RE: Upsert semantics

2016-03-15 Thread Anoop Sharma

gn format, and the upsert semantic proposed by Hans in align format?

This would allow speed optimization without having the user to know about
subtle differences?

Eric

*From:* Anoop Sharma [mailto:anoop.sha...@esgyn.com]
*Sent:* Tuesday, March 15, 2016 5:14 PM
*To:* user@trafodion.incubator.apache.org
*Subject:* RE: Upsert semantics

Phoenix has upsert command and from what can tell, they originally came up
with upsert syntax.

Their semantic is to insert if not present and update the specified columns
with the specified values if present.

We did do an experiment and upsert only updates the specified columns.

Maybe we can add a cqd so full row update vs. specified column update
behavior could be chosen.

Here is their specification.

Inserts if not present and updates otherwise the value in the table. The
list of columns is optional and if not present, the values will map to the
column in the order they are declared in the schema. The values must
evaluate to constants.

Example:

UPSERT INTO TEST VALUES('foo','bar',3);
UPSERT INTO TEST(NAME,ID) VALUES('foo',123);

*From:* Dave Birdsall [mailto:dave.birds...@esgyn.com]
*Sent:* Tuesday, March 15, 2016 2:55 PM
*To:* user@trafodion.incubator.apache.org
*Subject:* RE: Upsert semantics

Hi,

It seems that when ANSI first added MERGE to the standard, it was portrayed
as “upsert” (see https://en.wikipedia.org/wiki/Merge_(SQL)).

I agree though that we are free to define our UPSERT to mean anything we
want.

I like what you suggest. Since our UPSERT syntax already specifies values
for all the columns, it makes sense for it to have “replace” semantics.
That is, if the row exists, replace it with all the new stuff (with
defaults for columns omitted). If the row doesn’t exist, it’s just a
straight insert (with defaults for omitted columns).

And if one really wants UPDATE semantics as opposed to “replace” semantics,
then the ANSI MERGE statement (which Trafodion also supports) is the way to
go.

There is an analogy to this in linguistic theory. Whenever a language has
two words that at a point in time mean the same thing, there is a tendency
for the meanings to change over time so they diverge. For example, English
“shirt” and “skirt”, originally from the same root, but one via Anglo-Saxon
the other via Old Norse.

Dave

*From:* Hans Zeller [mailto:hans.zel...@esgyn.com]
*Sent:* Tuesday, March 15, 2016 2:40 PM
*To:* user@trafodion.incubator.apache.org
*Subject:* Upsert semantics

Hi,

Here is a question on how we should define the meaning of an UPSERT
statement. UPSERT is not part of the ISO/ANSI SQL standard, so we have some
leeway to define it.

My personal feeling is that UPSERT should either insert a brand-new row or
it should completely replace an existing row, but it should never combine
columns from a new and an existing row. If users want the latter then they
should use the MERGE command.

We should probably follow what other DBMSs do. I could not yet find a DBMS
that had an UPSERT command, although there probably is one.

- PostgreSQL: Has an insert with a conflict clause, similar to our
MERGE: http://www.postgresql.org/docs/current/static/sql-insert.html

- MySQL: Has an insert with ON DUPLICATE KEY clause, similar to our
MERGE: http://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html

- Teradata: Has an update ... else insert ... command, similar to MERGE:
https://forums.teradata.com/forum/enterprise/problem-using-upsert

- Oracle just seems to have the MERGE statement and various methods to
do upsert in PL/SQL:

http://stackoverflow.com/questions/237327/oracle-how-to-upsert-update-or-insert-into-a-table

This seems to support indirectly what I'm proposing. If we want to merge
old and new row then we should use syntax specifying how to merge, which is
what the other DBMSs have done.

See also the discussion in
https://github.com/apache/incubator-trafodion/pull/380. I wanted to see
whether the user group has any input on this.

Thanks,

Hans