You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by li...@apache.org on 2017/08/24 03:10:42 UTC
[1/5] incubator-trafodion git commit: cherry-pick altercolumn
Repository: incubator-trafodion
Updated Branches:
refs/heads/release2.2 d53816273 -> 2048d253c
cherry-pick altercolumn
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/ef73b771
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/ef73b771
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/ef73b771
Branch: refs/heads/release2.2
Commit: ef73b7719c746bc721587f2b4b4a032d71d03c65
Parents: d538162
Author: liu.yu <yu...@esgyn.cn>
Authored: Tue Aug 15 12:12:48 2017 +0800
Committer: liu.yu <yu...@esgyn.cn>
Committed: Tue Aug 22 11:16:47 2017 +0800
----------------------------------------------------------------------
.../sql_functions_and_expressions.adoc | 22 ++++++++++----------
1 file changed, 11 insertions(+), 11 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/ef73b771/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index 1dfe031..27d9f2c 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -503,7 +503,7 @@ ADD_MONTHS(timestamp'2008-02-29 00:00:00',12,1)
The ASCII function returns the integer that is the ASCII code of the
first character in a character string expression associated with either
-the ISO8891 character set or the UTF8 character set.
+the ISO88591 character set or the UTF8 character set.
ASCII is a {project-name} SQL extension.
@@ -720,7 +720,7 @@ The AUTHNAME function is similar to the <<user function,USER Function>>.
=== Considerations for AUTHNAME
* This function can be specified only in the top level of a SELECT statement.
-* The value returned is string data type VARCHAR(128) and is in ISO8859-1 encoding.
+* The value returned is string data type VARCHAR(128) and is in ISO88591 encoding.
[[examples_of_authname]]
=== Examples of AUTHNAME
@@ -1451,7 +1451,7 @@ CONCAT (character-expr-1, character-expr-2)
+
are SQL character value expressions (of data type CHAR or VARCHAR) that
specify two strings of characters. Both character value expressions must
-be either ISO8859-1 character expressions or UTF8 character expressions.
+be either ISO88591 character expressions or UTF8 character expressions.
The result of the CONCAT function is the concatenation of
_character-expr-1_ with _character-expr-2_. The result type is CHAR if
both expressions are of type CHAR and it is VARCHAR if either of the
@@ -1561,7 +1561,7 @@ the displayed output shows the internal value and is, consequently, not
particularly meaningful to general users and is subject to change in
future releases.
-CONVERTTOHEX returns ASCII characters in ISO8859-1 encoding.
+CONVERTTOHEX returns ASCII characters in ISO88591 encoding.
<<<
[[examples_of_converttohex]]
@@ -2079,7 +2079,7 @@ The CURRENT_USER function is similar to the <<user_function,USER Function>>.
=== Considerations for CURRENT_USER
* This function can be specified only in the top level of a SELECT statement.
-* The value returned is string data type VARCHAR(128) and is in ISO8859-1 encoding.
+* The value returned is string data type VARCHAR(128) and is in ISO88591 encoding.
[[examples_of_current_user]]
@@ -2429,7 +2429,7 @@ specifies a format for a datetime value. See <<datetime_literals,Datetime Litera
[[considerations_for_dateformat]]
=== Considerations for DATEFORMAT
-The DATEFORMAT function returns the datetime value in ISO8859-1
+The DATEFORMAT function returns the datetime value in ISO88591
encoding.
[[examples_of_dateformat]]
@@ -2690,7 +2690,7 @@ TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
[[considerations_for_dayname]]
=== Considerations for DAYNAME
-The DAYNAME function returns the name of the day in ISO8859-1.
+The DAYNAME function returns the name of the day in ISO88591.
[[examples_of_dayname]]
=== Examples of DAYNAME
@@ -4641,7 +4641,7 @@ TIMESTAMP. See <<datetime_value_expressions,Datetime Value Expressions>>.
[[considerations_for_monthname]]
=== Considerations for MONTHNAME
-The MONTHNAME function returns the name of the month in ISO8859-1.
+The MONTHNAME function returns the name of the month in ISO88591.
[[examples_of_monthname]]
=== Examples of MONTHNAME
@@ -6899,7 +6899,7 @@ SPACE (length [, char-set-name])
+
specifies the number of characters to be returned. The number _count_
must be a value greater than or equal to zero of exact numeric data type
-and with a scale of zero. _length_ cannot exceed 32768 for the ISO8859-1
+and with a scale of zero. _length_ cannot exceed 32768 for the ISO88591
or UTF8 character sets.
* `_char-set-name_`
@@ -7827,7 +7827,7 @@ is one of these translation names:
[cols="25%l,25%l,25%l,25%",options="header"]
|===
| Translation Name | Source Character Set | Target Character Set | Comments
-| ISO88591TOUTF8 | ISO88591 | UTF8 | Translates ISO8859-1 characters to UTF8 characters. No data loss is possible.
+| ISO88591TOUTF8 | ISO88591 | UTF8 | Translates ISO88591 characters to UTF8 characters. No data loss is possible.
| UTF8TOISO88591 | UTF8 | ISO88591 | Translates UTF8 characters to ISO88591 characters. {project-name} SQL will
display an error if it encounters a Unicode character that cannot be converted to the target character set.
|===
@@ -8127,7 +8127,7 @@ and the <<current_user_function,CURRENT USER Function>>.
=== Considerations for USER
* This function can be specified only in the top level of a SELECT statement.
-* The value returned is string data type VARCHAR(128) and is in ISO8859-1 encoding.
+* The value returned is string data type VARCHAR(128) and is in ISO88591 encoding.
[[examples_of_user]]
=== Examples of USER
[4/5] incubator-trafodion git commit: cherry-pick Load XML Files
Posted by li...@apache.org.
cherry-pick Load XML Files
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/fb007c00
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/fb007c00
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/fb007c00
Branch: refs/heads/release2.2
Commit: fb007c00469a36b37e58d1821581fdbe38527626
Parents: 08b41ed
Author: liu.yu <yu...@esgyn.cn>
Authored: Sun Aug 13 16:06:25 2017 +0800
Committer: liu.yu <yu...@esgyn.cn>
Committed: Tue Aug 22 11:54:15 2017 +0800
----------------------------------------------------------------------
docs/odb_user/src/asciidoc/_chapters/load.adoc | 38 +++++++++------------
1 file changed, 17 insertions(+), 21 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/fb007c00/docs/odb_user/src/asciidoc/_chapters/load.adoc
----------------------------------------------------------------------
diff --git a/docs/odb_user/src/asciidoc/_chapters/load.adoc b/docs/odb_user/src/asciidoc/_chapters/load.adoc
index 6a2eb86..91c0125 100644
--- a/docs/odb_user/src/asciidoc/_chapters/load.adoc
+++ b/docs/odb_user/src/asciidoc/_chapters/load.adoc
@@ -455,7 +455,7 @@ JOB:FIXED:23:10 <- insert into JOB characters starting at position
Load as follows:
```
-$ odb64luo –u user –p xx –d dsn \
+$ odb64luo –u user –p xx –d dsn \
-l src=frends1.dat:tgt=TRAFODION.MFTEST.FRIENDS1:map=ff.map:ns=\?:pc=32
```
@@ -494,16 +494,16 @@ You can use a mapfile like this:
```
~/Devel/odb $ cat person.map
PID:SEQ:100
-FNAME:DSRAND:datasets/first_names.txt
-LNAME:DSRAND:datasets/last_names.txt
+FNAME:DSRAND:datasets/first_names.txt
+LNAME:DSRAND:datasets/last_names.txt
COUNTRY:DSRAND:datasets/countries.txt
-CITY:DSRAND:datasets/cities.txt
+CITY:DSRAND:datasets/cities.txt
BDATE:DRAND:1800:2012
SEX:LSTRAND:M,F,U
-EMAIL:EMRAND:3:12:5:8:com,edu,org,net
-SALARY:NRAND:9:2
-EMPL:DSRAND:datasets/fortune500.txt
-NOTES:TXTRAND:20:80:datasets/lorem_ipsum.txt
+EMAIL:EMRAND:3:12:5:8:com,edu,org,net
+SALARY:NRAND:9:2
+EMPL:DSRAND:datasets/fortune500.txt
+NOTES:TXTRAND:20:80:datasets/lorem_ipsum.txt
LOADTS:CTSTAMP
```
@@ -599,7 +599,7 @@ Then:
* Fifth column (`COMMENT`) is loaded with the fifth column in the input file (`COMMENT:4`)
[[load_binary_files]]
-== Loading Binary Files
+== Load Binary Files
Assuming that your back-end database (and your ODBC Driver) supports BLOB data types, or equivalent,
you can use odb to directly load binary (or any other) files into a database column using the `[:em=char]` symbol
to identify the file to be loaded into that specific database field.
@@ -637,7 +637,6 @@ odb considers the string following the “em” character as the path of the fil
NOTE: odb does not load rows where the size of the input file is greater than the target database column.
-=======
[[load_xml_files]]
== Load XML Files
Trafodion odb supports loading XML files into tables, the key construct for XML files can be an element or an attribute.
@@ -741,7 +740,7 @@ And an input file that contains:
The max length of the second field in this file is:
```
-~/Devel/odb $ awk -F\, 'BEGIN\{max=0} \{if(NF==2)\{len=length($i);if(len>max)max=len}}
+~/Devel/odb $ awk -F\, 'BEGIN\{max=0} \{if(NF==2)\{len=length($i);if(len>max)max=len}}
END\{print max}' tmx.dat
15
```
@@ -841,7 +840,7 @@ The following table describes each extract operator:
[cols="30%,70%",options="header",]
|===
-| Extract option | Meaning
+| Extract option | Meaning
| `src=<CAT.SCH.TAB>\|-file` | Defines the source table(s). You can use: +
+
- a single table name (for example TRAFODION.MFTEST.LINEITEM) +
@@ -1178,7 +1177,7 @@ You can use odb to copy a list of tables from one database to another.
*Example*
```
-~/Devel/odb $ cat tlist.txt
+~/Devel/odb $ cat tlist.txt
# List of tables to extract
src=TRAFODION.MAURIZIO.ORDERS
@@ -1198,7 +1197,7 @@ Please note the `src=-tlist.txt`. This command copies:
[cols="50%,50%",options="header",]
|===
-| Source | Target
+| Source | Target
| `TRAFODION.MAURIZIO.ORDERS` | `tpch.stg_orders`
| `TRAFODION.MAURIZIO.CUSTOMER` | `tpch.stg_customer`
| `TRAFODION.MAURIZIO.PART` | `tpch.stg_part`
@@ -1216,10 +1215,10 @@ Using different _splitby columns_.
```
~/Devel/odb $ cat tlist2.txt
-# List of tables to extract and their “splitby columns”
-src=TRAFODION.MAURIZIO.ORDERS:splitby=O_ORDERKEY
-src=TRAFODION.MAURIZIO.CUSTOMER:splitby=C_CUSTOMERKEY
-src=TRAFODION.MAURIZIO.PART:splitby=P_PARTKEY
+# List of tables to extract and their “splitby columns”
+src=TRAFODION.MAURIZIO.ORDERS:splitby=O_ORDERKEY
+src=TRAFODION.MAURIZIO.CUSTOMER:splitby=C_CUSTOMERKEY
+src=TRAFODION.MAURIZIO.PART:splitby=P_PARTKEY
src=TRAFODION.MAURIZIO.LINEITEM:splitby=L_PARTKEY
```
@@ -1285,6 +1284,3 @@ relational database. For example, you can copy to from HIVE and other databases
from/to Hadoop. odb interacts directly with the HDFS file system using *libhdfs*.
+
This option is currently available only under Linux.
-
-
-
[2/5] incubator-trafodion git commit: Capitalize XML
Posted by li...@apache.org.
Capitalize XML
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/a465ec83
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/a465ec83
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/a465ec83
Branch: refs/heads/release2.2
Commit: a465ec83025a8a3664a1b0f9a28c86021287952a
Parents: ef73b77
Author: liu.yu <yu...@esgyn.cn>
Authored: Tue Aug 15 11:22:21 2017 +0800
Committer: liu.yu <yu...@esgyn.cn>
Committed: Tue Aug 22 11:33:03 2017 +0800
----------------------------------------------------------------------
docs/odb_user/src/asciidoc/_chapters/load.adoc | 59 +++++++++++++++++++++
1 file changed, 59 insertions(+)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/a465ec83/docs/odb_user/src/asciidoc/_chapters/load.adoc
----------------------------------------------------------------------
diff --git a/docs/odb_user/src/asciidoc/_chapters/load.adoc b/docs/odb_user/src/asciidoc/_chapters/load.adoc
index 087c0dc..6a2eb86 100644
--- a/docs/odb_user/src/asciidoc/_chapters/load.adoc
+++ b/docs/odb_user/src/asciidoc/_chapters/load.adoc
@@ -637,6 +637,65 @@ odb considers the string following the “em” character as the path of the fil
NOTE: odb does not load rows where the size of the input file is greater than the target database column.
+=======
+[[load_xml_files]]
+== Load XML Files
+Trafodion odb supports loading XML files into tables, the key construct for XML files can be an element or an attribute.
+
+=== Load XML Files Where Data is Stored in Element Nodes
+
+. Create a table.
+```
+./odb64luo -x "create table testxmlload(id int, name char(20))"
+```
+
+[start=2]
+. Suppose you have a xml file where data is stored in element nodes like the following.
+```
+-bash-4.1$ cat test.xml
+<?xml version="1.0" encoding="UTF-8"?>
+<data>
+<id>1</id>
+<name>hello</name>
+</data>
+```
+
+TIP: To check what will be loaded before loading XML file into table, run the following command
+`./odb64luo -l src=test.xml :tgt=testxmlload:xmltag=data:xmldump`
+
+[start=3]
+. Load the test.xml file into the table, run the following command.
+```
+./odb64luo -l src=test.xml:tgt=testxmlload:xmltag=data
+```
+
+TIP: `xmltag=data` means odb will load data from the element nodes. For more information, see <<Data Loading Operators>>.
+
+=== Load XML Files Where Data is Stored in Attribute Nodes
+. Create a table.
+```
+./odb64luo -x "create table testxmlload(id int, name char(20))"
+```
+
+[start=2]
+. Suppose you have a XML file where data is stored in attribute nodes like the following.
+```
+-bash-4.1$ cat test.xml
+<?xml version="1.0" encoding="UTF-8"?>
+<data id="1" name="hello"></data>
+```
+
+TIP: To check what will be loaded before loading XML file into table, run the following command.
+`./odb64luo -l src=test.xml:tgt=testxmlload:xmltag=data:xmldump`
+
+[start=3]
+. Load the test.xml file into the table, run the following command.
+```
+./odb64luo -l src=test.xml:tgt=testxmlload:xmltag=+data
+```
+
+TIP: `xmltag=+data` (with a plus sign specified) means odb will load data from the attribute nodes. For more information, see <<Data Loading Operators>>.
+
<<<
== Reduce the ODBC Buffer Size
odb allocates memory for the ODBC buffers during load/extract operations based on the max possible length of the
[5/5] incubator-trafodion git commit: Merge Fix for Cherry-pick
changes of [TRAFODION-2703] & [TRAFODION-2710] to 2.2
Posted by li...@apache.org.
Merge Fix for Cherry-pick changes of [TRAFODION-2703] & [TRAFODION-2710] to 2.2
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/2048d253
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/2048d253
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/2048d253
Branch: refs/heads/release2.2
Commit: 2048d253ca0ec8e01bf9f8b2c959a67f82da530a
Parents: d538162 fb007c0
Author: Liu Ming <ov...@sina.com>
Authored: Thu Aug 24 03:09:27 2017 +0000
Committer: Liu Ming <ov...@sina.com>
Committed: Thu Aug 24 03:09:27 2017 +0000
----------------------------------------------------------------------
docs/odb_user/src/asciidoc/_chapters/load.adoc | 95 +++++++++++++++-----
.../sql_functions_and_expressions.adoc | 24 ++---
.../src/asciidoc/_chapters/sql_statements.adoc | 62 +++++++++++--
3 files changed, 140 insertions(+), 41 deletions(-)
----------------------------------------------------------------------
[3/5] incubator-trafodion git commit: cherry pick add alter column 2
Posted by li...@apache.org.
cherry pick add alter column 2
Project: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/commit/08b41edb
Tree: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/tree/08b41edb
Diff: http://git-wip-us.apache.org/repos/asf/incubator-trafodion/diff/08b41edb
Branch: refs/heads/release2.2
Commit: 08b41edb08d313ed524e64392271d17c38ee9b19
Parents: a465ec8
Author: liu.yu <yu...@esgyn.cn>
Authored: Mon Aug 14 11:56:00 2017 +0800
Committer: liu.yu <yu...@esgyn.cn>
Committed: Tue Aug 22 11:44:19 2017 +0800
----------------------------------------------------------------------
.../sql_functions_and_expressions.adoc | 2 +-
.../src/asciidoc/_chapters/sql_statements.adoc | 62 +++++++++++++++++---
2 files changed, 54 insertions(+), 10 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/08b41edb/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
index 27d9f2c..fc81e08 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_functions_and_expressions.adoc
@@ -2523,7 +2523,7 @@ timestamp or a date expression.
DATE_PART is a {project-name} extension.
```
-DATEPART(text, datetime-expr)
+DATE_PART(text, datetime-expr)
```
* `_text_`
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/08b41edb/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
----------------------------------------------------------------------
diff --git a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
index 354a3c3..e8f446e 100644
--- a/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
+++ b/docs/sql_reference/src/asciidoc/_chapters/sql_statements.adoc
@@ -377,21 +377,19 @@ To run this statement, AUTOCOMMIT must be turned ON (the default) for the sessio
ALTER TABLE name alter-action
alter-action is:
-
ADD [IF NOT EXISTS][COLUMN] column-definition
| ADD [CONSTRAINT constraint-name] table-constraint
| DROP CONSTRAINT constraint-name [RESTRICT]
| RENAME TO new-name
| DROP COLUMN [IF EXISTS] column-name
+ | ALTER COLUMN column-definition
column-definition is:
-
column-name data-type
([DEFAULT default]
[[constraint constraint-name] column-constraint])
data-type is:
-
char[acter] [(length)[characters]]
[CHARACTER SET char-set-name]
[UPSHIFT] [[not] casespecific]
@@ -418,7 +416,6 @@ data-type is:
| interval { start-field to end-field | single-field }
default is:
-
literal
| null
| currentdate
@@ -426,24 +423,20 @@ default is:
| currenttimestamp }
column-constraint is:
-
not null
| unique
| check (condition)
| references ref-spec
table-constraint is:
-
unique (column-list)
| check (condition)
| foreign key (column-list) references ref-spec
ref-spec is:
-
referenced-table [(column-list)]
column-list is:
-
column-name[, column-name]...
```
@@ -616,6 +609,16 @@ the clauses for the _column-definition_ are the same as described in add [column
+
drops the specified column from _table_, including the column’s data. you cannot drop a primary key column.
+* `alter column _column-definition_`
++
+alters the data-type of a column in an existing Trafodion table. The following conditions have to be met, else SQL error 1404 will be returned.
+
+** the old (existing) column datatype and new column datatype must be VARCHAR (variable length string datatype).
+** new column length must be greater than or equal to old column length.
+** old and new character sets must be the same.
+** altered columns cannot be part of primary key or secondary index key.
+** table cannot be a VOLATILE table.
+
<<<
[[alter_table_considerations]]
=== Considerations for ALTER TABLE
@@ -655,7 +658,7 @@ If the constraint refers to the other table in a query expression, you must also
[[alter_table_examples]]
=== Example of ALTER TABLE
-This example adds a column:
+* This example adds a column.
```
ALTER TABLE persnl.project
@@ -663,6 +666,47 @@ ALTER TABLE persnl.project
NUMERIC (4) UNSIGNED
```
+
+* This example alters a column of an existing table – showing both positive and negative cases.
+
+```
+>>INVOKE T;
+
+-- Definition of Trafodion table TRAFODION.SEABASE.T
+-- Definition current Wed Jul 8 01:28:40 2015
+
+ (
+ SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
+ , A INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , B VARCHAR(30) CHARACTER SET ISO88591 COLLATE
+ DEFAULT DEFAULT NULL
+ )
+
+--- SQL operation complete.
+>>ALTER TABLE T ALTER COLUMN B VARCHAR(40);
+
+--- SQL operation complete.
+>>INVOKE T;
+
+-- Definition of Trafodion table TRAFODION.SEABASE.T
+-- Definition current Wed Jul 8 01:29:03 2015
+
+ (
+ SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
+ , A INT NO DEFAULT NOT NULL NOT DROPPABLE
+ , B VARCHAR(40) CHARACTER SET ISO88591 COLLATE
+ DEFAULT DEFAULT NULL
+ )
+
+--- SQL operation complete.
+>>ALTER TABLE T ALTER COLUMN B VARCHAR(30);
+
+*** ERROR[1404] Datatype for column B cannot be altered.
+
+--- SQL operation failed with errors.
+```
+
+
<<<
[[alter_user_statement]]
== ALTER USER Statement