hana_sql_en

SAP HANA Database - SQL Reference Manual SAP HANA Appliance Software SPS 04 Target Audience Consultants Administrators SAP SAP Har dware Par tner tner Others

1

2 0 12 -0 8 -2 1

Copyright

© Copyright 2012 SAP AG. All rights reserved

SAP Library document classification: PUBLIC

No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be c hanged hanged without prior notice. Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. Microsoft, Windows, Excel, Outlook, PowerPoint, Silverlight, and Visual Studio are registered trademarks of Microsoft Corporation. IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, System x, System z, System z10, z10, z/VM, z/OS, OS/390, zEnterprise, PowerVM, Power Architecture, Power Systems, POWER7, POWER6+, POWER6, POWER, PowerHA, pureScale, PowerPC, BladeCenter, System Storage, Storw St orwize, ize, XIV, GPFS, HACMP, RETAIN, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, AIX, Intelligent Miner, WebSphere, Tivoli, Informix, and Smarter Planet are trademarks or registered trademarks of IBM Corporation. Linux is the registered trademark of Linus Torv Torvalds alds in the United St ates and other ot her countries. Adobe, the Adobe logo, Acrobat, PostScript, and Reader are trademarks or registered trademarks of Adobe Systems Incorporated in the United States and other countries. Oracle and Java are registered trademarks of Oracle and its affiliates. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group. Citrix, ICA, ICA, Program Program Neighborhood, Neighborhood, MetaFrame, W WinFrame, inFrame, VideoFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems Inc. HTML, XML, XHTML, and W3C are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology. Apple, App Store, iBooks, iPad, iPhone, iPhoto, iPod, iTunes, Multi-Touch, Objective-C, Retina, Safari, Siri, and Xcode are trademarks or registered trademarks of Apple Inc. IOS is a registered trademark of Cisco Systems Inc. RIM, BlackBerry, BBM, BlackBerry Curve, BlackBerry Bold, BlackBerry Pearl, BlackBerry Torch, BlackBerry Storm, BlackBerry Storm2, BlackBerry PlayBook, and BlackBerry App World are trademarks or registered trademarks of Research

2

2 0 12 -0 8 -2 1

Copyright

© Copyright 2012 SAP AG. All rights reserved

SAP Library document classification: PUBLIC

No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be c hanged hanged without prior notice. Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors. Microsoft, Windows, Excel, Outlook, PowerPoint, Silverlight, and Visual Studio are registered trademarks of Microsoft Corporation. IBM, DB2, DB2 Universal Database, System i, System i5, System p, System p5, System x, System z, System z10, z10, z/VM, z/OS, OS/390, zEnterprise, PowerVM, Power Architecture, Power Systems, POWER7, POWER6+, POWER6, POWER, PowerHA, pureScale, PowerPC, BladeCenter, System Storage, Storw St orwize, ize, XIV, GPFS, HACMP, RETAIN, RETAIN, DB2 Connect, RACF, Redbooks, OS/2, AIX, Intelligent Miner, WebSphere, Tivoli, Informix, and Smarter Planet are trademarks or registered trademarks of IBM Corporation. Linux is the registered trademark of Linus Torv Torvalds alds in the United St ates and other ot her countries. Adobe, the Adobe logo, Acrobat, PostScript, and Reader are trademarks or registered trademarks of Adobe Systems Incorporated in the United States and other countries. Oracle and Java are registered trademarks of Oracle and its affiliates. UNIX, X/Open, OSF/1, and Motif are registered trademarks of the Open Group. Citrix, ICA, ICA, Program Program Neighborhood, Neighborhood, MetaFrame, W WinFrame, inFrame, VideoFrame, VideoFrame, and MultiWin are trademarks or registered trademarks of Citrix Systems Inc. HTML, XML, XHTML, and W3C are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology. Apple, App Store, iBooks, iPad, iPhone, iPhoto, iPod, iTunes, Multi-Touch, Objective-C, Retina, Safari, Siri, and Xcode are trademarks or registered trademarks of Apple Inc. IOS is a registered trademark of Cisco Systems Inc. RIM, BlackBerry, BBM, BlackBerry Curve, BlackBerry Bold, BlackBerry Pearl, BlackBerry Torch, BlackBerry Storm, BlackBerry Storm2, BlackBerry PlayBook, and BlackBerry App World are trademarks or registered trademarks of Research

2

2 0 12 -0 8 -2 1

in Motion Limited. Google App Engine, Google Apps, Google Checkout, Checkout, Google Data Data AP I, Google Maps, Google Mobile Ads, Google Mobile Updater, Updater, Google Mobile, Google Store, Google Sync, Google Updater, Updater, Google Voice, Google Mail, Gmail, YouTube, YouTube, Dalvik and Android are trademarks or registered trademarks of Google Inc. INTERMEC is a registered trademark of Intermec Technologies Corporation. Wi-Fi is a registered trademark of Wi-Fi Alliance. Bluetooth is a registered trademark of Bluetooth SIG Inc. Motorola is a registered trademark of Motorola Trademark Holdings LLC. Computop Computop is a registered trademark trademark of Computop Computop Wirts chaftsinformatik chaftsinformatik GmbH. SAP, R/3, SAP NetWeaver, Duet, PartnerEdge, ByDesign, SAP BusinessObjects Explorer, StreamWork, SAP HANA, and other SAP products and services mentioned herein as well as their respective logos are trademarks or registered trademarks of SAP AG in Germany and other countries. Business Objects and the Business Objects logo, BusinessObjects, Crystal Reports, Crystal Decisions, Web Intelligence, Xcelsius, and other Business Objects products products and services services mentioned herein herein as well as their respective logos logos are trademarks or registered trademarks of Business Objects Software Ltd. Business Objects Objects is an SAP company. company. Sybase and Adaptive Server, iAnywhere, Sybase 365, SQL Anywhere, and other Sybase products products and services services mentioned herein herein as well as t heir respective respective logos are trademarks or registered trademarks of Sybase Inc. Sybase is an SAP company. Cross Crossgate, gate, m@gic EDDY, B2B 360°, 360°, and B2B B 2B 360° Services Services are regist registered ered trademarks of Crossgate AG in Germany and other countries. Crossgate is an SAP company. All other ot her product product and service service names mentioned are the trademarks trademarks of their respective respective companies. Data contained in this document serv s erves es info i nformational rmational purposes only. National product specifications may vary. These materials are subject to change without notice. These materials are provided by SAP AG and its affiliated companies ("SAP Group") for informational purposes only, without representation or warranty of any kind, and SAP Group shall not be liable for errors or omissions with respect to the materials. The only warranties warranties for SAP Group products products and services services are those that are set forth forth in the express warranty statements accompanying such products and services, if any. Nothing herein should be construed as constituting an additional warranty.

3

2 0 12 -0 8 -2 1

Table of Content

Table of Content Table of Content SAP HANA Database - SQL Reference Manual SQL Reference Manual Notation Introduction SQL Supported Languages and Code Pages Comment Identifiers Single Quotation Mark Double Quotation Mark SQL Reserved Words

Data Types Classification of Data Types Datetime Types Date Formats Time Formats Timestamp Formats Additional Formats Supported Functions for Date/Time types

Numeric Types Character String Ty pes Binary Ty pes Large Object (LOB) Types Mapping between SQ L Data Type and Column Store Data Type Data Type Conversion Typed Constant

Predicates Comparison Predicates Range Predicate In Predicate Exists Predicate LIKE Predicate NULL Predicate CONTAINS Predicate

Operators Unary and Binary Operators Operator Precedence Arithmetic Operators String Operators Comparsion Operators Logical Operators Set Operators

Expressions Case Expressions Function Expressions Aggregate Expressions Subqueries in Expressions

SQL Functions Introduction Data Type Conversion Functions CAST TO_ALPHANUM TO_BIGINT TO_BINARY TO_BLOB TO_CHAR TO_CLOB TO_DATE TO_DATS

4

4 8 9 10 11 11 11 11 11 11 11 12

13 13 13 13 14 14 14 15

15 16 17 17 18 18 20

22 22 22 22 22 22 22 23

25 25 25 25 26 26 26 26

28 28 28 28 28

30 30 30 30 30 30 31 31 31 31 32 32

2012-08-21

Table of Content

TO_DECIMAL TO_DOUBLE TO_INT TO_INTEGER TO_NCHAR TO_NCLOB TO_NVARCHAR TO_REAL TO_SECONDDATE TO_SMALLDECIMAL TO_SMALLINT TO_TIME TO_TIMESTAMP TO_TINYINT TO_VARCHAR

32 33 33 33 33 34 34 34 34 35 35 35 35 36 36

DateTime Functions

36

ADD_DAYS ADD_MONTHS ADD_SECONDS ADD_YEARS CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_UTCDATE CURRENT_UTCTIME CURRENT_UTCTIMESTAMP DAYNAME DAYOFMONTH DAYOFYEAR DAYS_BETWEEN EXTRACT HOUR ISOWEEK LAST_DAY LOCALTOUTC MINUTE MONTH MONTHNAME NEXT_DAY NOW QUARTER SECOND SECONDS_BETWEEN UTCTOLOCAL WEEK WEEKDAY YEAR

Number Functions ABS ACOS ASIN ATAN ATAN2 BINTOHEX BITAND CEIL COS COSH COT EXP FLOOR GREATEST HEXTOBIN LEAST LN LOG MOD POWER ROUND SIGN SIN SINH SQRT TAN TANH UMINUS

5

36 37 37 37 37 38 38 38 38 39 39 39 39 40 40 40 40 41 41 41 42 42 42 42 43 43 43 43 44 44 44

44 44 45 45 45 46 46 46 46 47 47 47 47 48 48 48 48 49 49 49 50 50 50 50 51 51 51 51 52

2012-08-21

Table of Content

String Functions

52

ASCII CHAR CONCAT LCASE LEFT LENGTH LOCATE LOWER LPAD LTRIM NCHAR REPLACE RIGHT RPAD RTRIM SUBSTR_AFTER SUBSTR_BEFORE SUBSTRING TRIM UCASE UNICODE UPPER

52 52 53 53 53 53 54 54 54 55 55 55 55 56 56 56 57 57 57 58 58 58

Miscellaneous Functions COALESCE CURRENT_CONNECTION CURRENT_SCHEMA CURRENT_USER GROUPING_ID IFNULL MAP NULLIF SESSION_CONTEXT SESSION_USER SYSUUID

SQL Statements Schema Definition and Manipulation Statements ALTER AUDIT POLICY ALTER FULLTEXT INDEX ALTER INDEX ALTER SEQUENCE ALTER TABLE CREATE AUDIT POLICY CREATE FULLTEXT INDEX CREATE INDEX CREATE SCHEMA CREATE SEQUENCE CREATE SYNONYM CREATE TABLE CREATE TRIGGER CREATE VIEW DROP AUDIT POLICY DROP FULLTEXT INDEX DROP INDEX DROP SCHEMA DROP SEQUENCE DROP SYNONYM DROP TABLE DROP TRIGGER DROP VIEW RENAME COLUMN RENAME INDEX RENAME TABLE ALTER TABLE ALTER TYPE TRUNCATE TABLE

Data Manipulation Statements DELETE EXPLAIN PLAN INSERT LOAD MERGE DELTA REPLACE | UPSERT SELECT UNLOAD UPDATE

6

59 59 59 59 59 60 61 61 62 62 63 63

64 64 64 65 66 67 68 73 75 76 77 77 78 79 83 90 90 91 91 91 92 92 93 93 94 94 95 96 96 97

97 97 98 101 102 103 104 105 113 113

2012-08-21

Table of Content

System M anagement Statements SET SYSTEM LICENSE ALTER SYSTEM ALTER CONFIGURATION ALTER SYSTEM ALTER SESSION SET ALTER SYSTEM ALTER SESSION UNSET ALTER SYSTEM CANCEL [WORK IN] SESSION ALTER SYSTEM CLEAR SQL PLAN CACHE ALTER SYSTEM CLEAR TRACES ALTER SYSTEM DISCONNECT SESSION ALTER SYSTEM LOGGING ALTER SYSTEM RECLAIM DATAVOLUME ALTER SYSTEM RECLAIM LOG ALTER SYSTEM RECLAIM VERSION SPACE ALTER SYSTEM RECONFIGURE SERVICE ALTER SYSTEM REMOVE TRACES ALTER SYSTEM RESET MONITORING VIEW ALTER SYSTEM SAVE PERFTRACE ALTER SYSTEM SAVEPOINT ALTER SYSTEM START PERFTRACE ALTER SYSTEM STOP PERFTRACE ALTER SYSTEM STOP SERVICE UNSET SYSTEM LICENSE ALL

Session Ma nagement Statements CONNECT SET HISTORY SESSION SET SCHEMA SET [SESSION] UNSET [SESSION]

Transaction Management Statements COMMIT LOCK TABLE ROLLBACK SET TRANSACTION

Access Control Statements ALTER SAML PROVIDER ALTER USER CREATE ROLE CREATE SAML PROVIDER CREATE USER DROP ROLE DROP SAML PROVIDER DROP USER GRANT REVOKE

Data Import Export Statements EXPORT IMPORT IMPORT FROM

Res trictions for SQL Statements See Also

SQL Error Code s

7

114 114 114 116 116 117 117 118 118 119 119 120 120 120 121 122 122 123 123 124 124 124

124 124 125 125 126 126

126 127 127 127 127

128 128 129 131 132 133 134 135 135 136 142

143 143 144 145

147 149

150

2012-08-21

SAP HANA Database - SQL Reference Manual

SAP HANA Database - SQL Reference Manual This guide describes SQL language supported by SAP HANA DB.

8

2012-08-21


SQL Reference Manual

9

2012-08-21


Notation This reference use BNF (Backus Naur Form) which is the notation technique used to define programming languages, t o describe SQL. B NF describes the syntax of a g rammar using a set of production rules using a set of symbols. Symbols used in BNF Symbol

Description

<>

Angle brackets are used to surround the name of a syntactic element (BNF nonterminal) of the SQL language.

::=

The definition operator is used to provide defini tions of the element appea red on the left side of the operator in a production rule.

[]

Square brackets are use d to indicate optional el ements in a formula. Optional el ements may be specified or omitted.

{}

Braces group ele ments in a formula. Repetitive ele ments (zero or more el ements) can be s pecified within brace symbols.

|

The alternative operator indicates that the portion of the formula foll owing the bar is an alternative to the portion preceding the bar.

...

The elli psis indicates that the element may be repeated any number of times. If ellips is a ppears after grouped ele ments specifying that the grouped elements enclose d with braces are repeated. If elli psis a ppears after a single ele ment, only that element is repeated.

!!

Introduces normal English text. This is used when the definition of a syntactic element is not express ed in BNF.

10

2012-08-21


Introduction This chapter describes the SAP HANA Data base implementation of Structured Query Language (SQL). It explains the characteristics of SQL, also how to manage comments and reserve words.

SQL SQL stands for Structured Query Language. I t is a standardized language for communicating with a relational database. SQL is used to r etrieve, store or manipulate information in the database. SQL statements perform the following tasks: Schema definition and manipulation Data manipulation System management Session management Transaction management

Supported Languages and Code Pages The SAP HANA Dat abase supports Unicode to allow the use of all languages in the Unicode Standard and 7 Bit ASCII code page without restriction.

Comment You can add comments to improve readability and maintainability of your SQL statements. Comments are delimited in SQL statements as follows: Double hyphens "--". Everything after the double hyphen until the end of a line is considered by the SQL parser to be a comment "/*" and "*\/". This style of commenting is used to place comments on multiple lines. All text between the opening "/*" and closing "*\/" is ignored by the SQL parser.

Identifiers Syntax: ::= | ::= [{|}, ...] ::= " ::= any character ::= A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | a | b | c | d | e | f | g | h | i | j | k | l | m | n | o | p | q | r | s | t | u | v | w | x | y | z ::= 0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 ::= | ::= _

Identifiers are used to represent names used in SQL statement including table name, view name, synonym name, column name, index name, function name, procedure name, user name, role name, and so on. There are two kinds of identifiers; undelimited identifiers and delimited identifiers. Undelimited table and column names must start with a letter and cannot contain any symbols other than digits or an underscore "_". Delimited identifiers are enclosed in the delimiter, double quotes, then the identifier can contain any character including special characters. For example, "AB $%CD" is a valid identifier name. Limitations: "_SYS_" is reserved exclusively for database engine, hence not allowed at the beginning of schema object names. Role name and user name must be specified as undelimited identifiers. Maximum length for the identifiers is 127 characters.

Single Quotation Mark Single quotation marks are used to delimit string literals and single quotation mark itself can be represented using two single quotation marks.

Double Quotation Mark

11

2012-08-21


Double quotation marks are used to delimit identifiers and double quotation mark itself can be represented using two double quotation marks.

SQL Reserved Words Reserved words are words which have a special meaning to the SQL parser in the SAP HANA Data base that cannot be used as a user-defined name. Reserved words should not be used in SQL statements for schema object names. If necessary, you can work around this limitation by delimiting a table or column name with double quotation marks. The following ta ble lists all the current and future reserved words for the SAP HANA Database. ALL

ALTER

AS

BEFORE

BEGI N

BOTH

CASE

CHAR

CONDITI ON

CONNECT

CROSS

CUBE

CU RR EN T_ CO NN ECT IO N

CU RR EN T_ DA TE

CU RR EN T_ SCH EMA

CU RR EN T_ TI ME

CU RR EN T_ TI ME ST AMP

CU RR EN T_ US ER

CU RR EN T_ UT CD AT E

CU RR EN T_ UT CT I ME

CURRENT_UTCTIMESTAMP

CURRVAL

CURSOR

DECLARE

DISTINCT

ELSE

ELSEIF

ELSIF

END

EXCEPT

EXCEPTION

EXEC

FOR

FROM

FULL

GROUP

HAVI NG

IF

IN

INNER

INOUT

INTERSECT

INTO

IS

JOIN

LEADING

LEFT

LIMI T

LOOP

MINUS

NATURAL

NEXTVAL

NULL

ON

ORDER

OUT

PRIOR

RETURN

RETURNS

REVERSE

RIGHT

ROLLUP

ROWID

SELECT

SET

SQL

START

SYSDATE

SYSTIME

SYSTIMESTAMP

SYSUUID

TOP

TRAI LING

UNI ON

USING

UTCDATE

UTCTIME

UTCTIMESTAMP

VALUES

WHEN

WHERE

WHILE

WI TH

Table 1. Reserved Words

12

2012-08-21


Data Types This section describes the data types used in the SAP HANA Database. Data type specifies the characteristics of a dat a value. A special value of NULL is included in every data type to indicate the absence of a value. The following table shows the built-in data types available in the SAP HANA Database.

Classification of Data Types In the SAP HANA Da tabase each data type can be classified by its characteristic a s follows: C lass ificat io n

Dat a Ty pe

D a te ti m e typ e s

D AT E, T IME , S ECO ND DAT E, T IME STA MP

Numeric types

TINYINT, SMALLINT, INTEGER, BIGINT, SMALLDECIMAL, DECIMAL, REAL, DOUBLE

Character string types

VARCHAR, NVARCHAR, ALPHANUM, SHORTTEXT

Bi na ry type s

VARBINARY

L a rg e Ob je ct typ e s

B LO B, CL OB , N CL OB , T EXT

Table 2: Classification of data types

Datetime Types DATE The DATE data t ype consists of year, month, and day information to represent a date value. The default format for the DATE data t ype is 'YYYY-MM-DD'. YYYY represents the year, MM r epresents the month, and DD represents the day. The range of date value is 0001-01-01 through 9999-12-31. TIME The TIME data type consists of hour, minute, and second to represent a time value. The default format for the TIME data t ype is 'HH24:MI:SS'. HH24 represents the hour from 0 to 24, MI represents the minute from 0 to 59, SS represents the second from 0 to 59. SECONDDATE The SECONDDATE data type consists of year, month, day, hour, minute and second information to represent a date with time value. The default format for the SECONDDATE data t ype is 'YYYY-MM-DD HH24:MI:SS'. YYYY represents the year, MM represents the month, D D represents the day, HH24 represents hour, MI represents minute, and SS represents seconds. The range of date value is 000101-01 00:00:01 t hrough 9999-12-31 24:00:00. TIMESTAMP The TIMESTAMP data t ype consists of date and time information. Its default format is 'YYYY-MM-DD HH24:MI:SS.FF7'. FFn represents the fractional seconds where n indicates the number of digits in fractional part. . T he range of t he timestamp value is 0001-01-01 00:00:00.0000000 through 9999-1231 23:59:59.9999999.

For details on supported formats for datetime types, refer to Table 4, Ta ble 5, Table 6 a nd Table 7 below. Date Formats

The following date/time formats ca n be used when parsing a string into a dat e/time type and converting a date/time type value into a string value. Please note that format for Timestamp is the combination of Date and Time with the additional support for fractional seconds.

13

2012-08-21


Format

Description

Examples

YYYY-MM-DD

De fa ul t forma t

INSERT INTO TBL VALUES ('1957-06-13');

YYYY/MM/DD YYYY/MM-DD YYYY-MM/DD

YYYY from 0001 to 9999, MM from 1 to 12, DD from 1 to 31. If year has less than four digits, month has les s than two digits, or day has les s than two digits, then values will be padded by one or more zeros. For example, a two digit year 45 will be saved as year 0045, a one digit month 9 will be saved as 09, and a one di git day 2 will be saved as 02.

INSERT INSERT INSERT INSERT

YYYYMMDD

ABAP Da ta Type , DATS forma t.

INSERT INTO TBL VALUES ('19570613');

Abbre vi a ted na me of month. (JAN. ~ DEC.)

INSERT INTO TBL VALUES (TO_DATE('2040Jan-10', 'YYYY-MON-DD')); INSERT INTO TBL VALUES (TO_DATE('Jan10', 'MON-DD'));

MONTH

Na me of month. (JANUARY - DECEMBER).

INSERT INTO TBL VALUES (TO_DATE('2040January-10', 'YYYY-MONTH-DD')); INSERT INTO TBL VALUES (TO_DATE('January-10', 'MONTH-DD'));

RM

Roma n numera l month (I -XII ; JAN = I).

INSERT INTO TBL VALUES (TO_DATE('2040-I10', 'YYYY-RM-DD')); INSERT INTO TBL VALUES (TO_DATE('I-10', 'RM-DD'));

DDD

Da y of ye a r (1-366).

INSERT INTO TBL VALUES (TO_DATE('204', 'DDD')); INSERT INTO TBL VALUES (TO_DATE('2001204','YYYY-DDD'));

MON

INTO TBL INTO TBL INTO TBL INTO TBL

VALUES ('1957-06-13'); VALUES ('1957/06/13'); VALUES ('1957/06-13'); VALUES ('1957-06/13');

Table 4: Supported formats for Date

Time Formats Format

Description

Examples

HH24:MI:SS

Defa ul t forma t

HH:MI[:SS][AM|PM] HH12:MI[:SS][AM|PM] HH24:MI[:SS]

HH from 0 to 23. MI from 0 to 59. SS from 0 to 59. FFF from 0 to 999. If one di git hour, minute, second is specified, then 0 will be inserted into the value. For example, 9:9:9 will be saved as 09:09:09. HH12 indicates 12 hour clock and HH24 indicates 24 hour clock. AM or PM can be specified as a suffix to indicate the time value i s before or after noon.

INSERT INTO TBL VALUES ('23:59:59'); INSERT INTO TBL VALUES ('3:47:39 AM'); INSERT INTO TBL VALUES ('9:9:9 AM'); INSERT INTO TBL VALUES (TO_TIME('11:59:59','HH12:MI:SS');

SSSSS

Seconds pa s t mi dni ght (0-86399).

INSERT INTO TBL VALUES (TO_TI ME('12345', 'SSSSS'));

Table 5: Supported formats for Time

Timestamp Formats Format

Description

YYYY-MM-DD HH24:MI:SS.FF7

Default format

FF [1..7]

Fractional s econds has the range 1 to 7 after the FF parameter to specify the number of digits in the fractional second portion of the date time value returned. If a digit is not specified, the default value is used.

Examples

INSERT INTO TBL VALUES (TO_TIMESTAMP('2011-05-11 12:59.999','YYYY-MM-DD HH:SS.FF3'));

Table 6: Supported formats for Timestamp

Additional Formats

14

2012-08-21


Format

Description

Example

D

Da y of week (1-7).

TO_CHAR(CURRENT_TIMESTAMP,'D')

DAY

Na me of da y (MONDAY - SUNDAY).

TO_CHAR(CURRENT_TIMESTAMP,'DAY')

DY

Abbre vi a ted na me of da y (MON - SUN).

TO_CHAR(CURRENT_TIMESTAMP,'DY')

MON

Abbre vi ated month na me (JAN - DEC)

TO_CHAR(CURRENT_TIMESTAMP,'MON')

MONTH

Fu ll m on th n ame ( JANUAR Y - DECEMBER )

TO_CHAR(CUR RENT_TI MESTAMP,'MONTH')

RM

Roman numeral month (I - XII; I is for January)

TO_CHAR(CURRENT_TIMESTAMP,'RM')

Q

Qua rter of yea r (1, 2, 3, 4)

TO_CHAR(CURRENT_TIMESTAMP,'Q')

W

Week of month (1-5).

TO_CHAR(CURRENT_TIMESTAMP,'W')

WW

Week of yea r (1-53).

TO_CHAR(CURRENT_TIMESTAMP,'WW')

Table 7: Additional formats for Datetime

Supported Functions for Date/Time types ADD_DAYS ADD_MONTHS ADD_SECONDS ADD_YEARS COALESCE CURRENT_DATE CURRENT_TIME CURRENT_TIMESTAMP CURRENT_UTCDATE CURRENT_UTCTIME CURRENT_UTCTIMESTAMP DAYNAME DAYOFMONTH DAYOFYEAR DAYS_BETWEEN EXTRACT GREATEST GREATEST HOUR IFNULL ISOWEEK LAST_DAY LEAST LOCALTOUTC MINUTE MONTH MONTHNAME NEXT_DAY NULLIF QUARTER SECOND SECONDS_BETWEEN TO_CHAR TO_DATE TO_DATS TO_NCHAR TO_TIME TO_TIMESTAMP UTCTOLOCAL WEEK WEEKDAY YEAR

Numeric Types TINYINT The TINYINT data type stores an 8-bit unsigned integer. The minimum value is 0 and the maximum value is 255 for TINYINT. SMALLINT The SMALLINT data type stores a 16-bit signed integer. The minimum value is -32,768 and the maximum value is 32,767 for SMALLINT.

15

2012-08-21


INTEGER The INTEGER data type stores a 32-bit signed integer. The minimum value is -2,147,483,648 and t he maximum value is 2,147,483,647 for INTEGER. BIGINT The BIGINT data type stores a 64-bit signed integer. The minimum value is 9,223,372,036,854,775,808 and the maximum value is 9,223,372,036,854,775,807 for BIGINT. DECIMAL(precision, scale) or DEC(p,s) The DECIMAL(p, s) data type specifies a fixed-point decimal number with precision p and scale s. The precision is the total number of significant digits and can range from 1 to 34.

The scale is the number of digits from the decimal point to the least significant digit and ca n range from -6,111 to 6,176, which means scale specifies the range of the exponent in the decimal number from 10-6111 to 106176. If scale is not specified, it defaults to 0. Scale is positive when the number has significant digits to the rig ht of the decimal point a nd negative when the number has significant digits to the left of the decimal point. Examples: 0.0000001234 (1234 x 10-10) has the precision 4 and the scale 10. 1.0000001234 (10000001234 x 10-10) has the precision 11 and scale 10. 1234000000 (1234x106) has the precision 4 and scale -6. When precision and scale are not specified, DECIMAL becomes a floating-point decimal number. In this case, precision and scale can vary within the range described above, 1~34 for precision and 6,111~6,176 for scale depending on the stored value. SMALLDECIMAL The SMALLDECIMAL is a floating-point decimal number. The precision and scale can vary within the range, 1~16 for precision and -369~368 for scale depending on t he stored value. SMALLDECIMAL is supported only on column store. DECIMAL and SMALLDECIMAL are floating-point types. For instance, a decimal column can store any of 3.14, 3.1415, 3. 141592 whilst maintaining their precision. DECIMAL(p, s) is the SQL standard notation for fixed-point decimal. For instance, 3.14, 3.1415, 3.141592 are stored in a decimal(5, 4) column as 3.1400, 3.1415, 3.1416, respectively keeping the specified precision(5) and scale(4). REAL The REAL data type specifies a single-precision 32-bit floating-point number. DOUBLE The DOUBLE data type specifies a single-precision 64-bit floating-point number. The minimum value is -1.79769 x 10308 and the maximum value is 1.79769x10308 . The smallest positive DOUBLE value is 2.2207x10-308 and the larg est negative DOUBLE value is -2.2207x10-308. FLOAT(n) The FLOAT(n) data type specifies a 32-bit or 64-bit real number, where n specifies the number of significant bits and can range between 1 and 53.

When you use the FLOAT(n) data type, if n is smaller than 25, it becomes a 32-bit REAL data type. I f n is greater than or equal to 25, it then becomes a 64-bit DOUBLE data t ype. If n is not declared, it becomes a 64-bit double data type by default.

Character String Types The character string data types are used to store values that contain character strings. While VARCHAR data types contain ASCII character strings, NVARCHAR are used for storing Unicode character strings. VARCHAR The VARCHAR(n) data t ype specifies a variable-length ASCII character string, where n indicates the maximum length and is an integer between 1 and 5000. NVARCHAR The NVARCHAR(n) data t ype specifies a variable-length Unicode character set string, where n indicates the maximum length and is an integer between 1 and 5000. ALPHANUM The ALPHANUM(n) data type specifies a variable-length charact er string which contains alphanumeric chara cters, where n indicates the maximum length and is an integer between 1 and 127. SHORTTEXT The SHORTTEXT(n) data type specifies veriable-length chara cter string which supports textsearch-

16

2012-08-21


features and stringsearch-features. This is not a standalone sql-type. Selecting a SHORTTEXT(n)column yields a column of type NVARCHAR(n).

::= SHORTTEXT '(' int_const ')'

::= [... ',' ] ::= | SYNC[HRONOUS]

Binary Types Binary types are used to store bytes of binary data. VARBINARY The VARBINAR Y(n) data type is used to store binary data of a specified maximum length in bytes, where n indicates the maximum length and is an integer between 1 and 5000.

Large Object (LOB) Types LOB (large objects) data types, CLOB, NCLOB and BLOB, a re used to store a large amount of data such as text documents and images. The maximum size of an LOB is 2 GB. BLOB The BLOB data type is used to store large binary data. CLOB The CLOB data type is used to store large ASCII charac ter data. NCLOB The NCLOB data t ype is used to store a la rge Unicode character object. TEXT The TEXT data type specifies which supports textsearch-features. This is not a standalone sql-type. Selecting a TEXT-column yields a column of type NCLOB. ::= TEXT ::= [... ',' ] ::= | [SYNC[HRONOUS] | [ASYNC[HRONOUS] FLUSH [QUEUE] EVERY MINUTES [[OR] AFTER DOCUMENTS] ]

Syntax-rules common to TEXT and SHORTTEXT ::= LANGUAGE COLUMN | LANGUAGE DETECTION '(' ')' | MIME TYPE COLUMN | FUZZY SEARCH INDEX [ON|OFF] | PHRASE INDEX RATIO [ON|OFF] | CONFIGURATION | SEARCH ONLY [ON|OFF] | FAST PREPROCESS [ON|OFF]

LOB types are provided for storing and retrieving such large data. LOB types support t he following operations. The length() function returns the LOB length in bytes. LIKE can be used to search LOB columns. The LOB types have the following restrictions: LOB columns cannot appear in ORD ER BY or GROUP BY c lauses. LOB columns cannot appear in FROM clauses as a join predicate. LOB columns cannot appear in WHERE clauses as a predicate except LI KE, CONTAINS, =, or <>. LOB columns cannot appear in SELECT clauses as an a ggrega te function argument. LOB columns cannot appear in SELECT DI STINCT clauses. LOB columns cannot be used in set operations such as EXCEPT. UNION ALL is an exception. LOB columns cannot be used as a primary key. LOB columns cannot be used in CREATE INDEX statements. LOB columns cannot be used in statistics update statements.

17

2012-08-21


Mapping between SQL Data Type and Column Store Data Type SQL Type I nt nte ge ge r Typ es es

Ap p ro xi ma te Typ e s

D e ci ma l Typ e s

Ch a ra ra ct cte r Typ e s

B i n a ry Typ e s

D at ate /T /Ti me me Typ es es

Column St ore Type

TI NY NYI NT NT, S MA MALLI NT NT, I NT NT

CS _I NT NT

B I G I NT

CS _F I XED (18,0)

R EAL

CS _F LOAT

D OUB LE

CS _D OU B LE

F LOAT

CS _D OU B LE

F LOAT(p )

CS _F LOAT, CS _D OU B LE

D ECI MAL

CS _D ECI MAL_F LOAT

D ECI MAL( p ,s )

CS _F I XED (p -s ,s )

S MALLD ECI MAL

CS _S D F LOAT

VAR CH AR AR

CS_STRING,CS_ALPHANUM,CS_UNITDECFLOAT,CS_DATE,CS_TIME CS_STRING,CS_ALPHANUM,CS_UNITDECFLOAT,CS_DATE,CS_TIME

NVARCHAR

CS_STRING,CS_ALPHANUM,CS_UNITDECFLOAT CS_STRING,CS_ALPHANUM,CS_UNITDECFLOAT

CLOB , NCLOB

CS _S TR I NG

ALPH ANUM

CS _ALPH AN UM

B LOB

CS _R AW

VAR B I NAR Y

CS _R AW

D A TE

CS_DAYDATE,CS_DATE CS_DAYDATE,CS_DATE

TIME

CS_SECONDTIME,CS_TIME CS_SECONDTIME,CS_TIME

TIMESTAMP

CS_LONGDATE,CS_DATE,CS_SECONDDATE CS_LONGDATE,CS_DATE,CS_SECONDDATE

S ECON D D ATE

CS _S ECOND D ATE

Data Type Conversion This section describes the data type conversion allowed in SAP HANA Database. Explicit type conversion

The type of an expression result, for example a field reference, a function on fields, or literals can be converted using using the following functions: functions: CAST, TO_ALPHANUM, TO_BI GINT, TO_VARBINARY, TO_BLOB, TO_CLOB, TO_DATE, TO_DATS, TO_DECIMAL, TO_DOUBLE, TO_INTEGER, TO_INT, TO_NCLOB, TO_NVARCHAR, TO_REAL, TO_SECONDDATE, TO_SMALLINT, TO_TINYINT, TO_TIME, TO_TIMESTAMP, TO_VARCHAR. Implicit type conversion

When a given set of operand/argument types does not match what an operator/function expects, a type conversion is carried out by the SAP HANA Database. This conversion only occurs if a relevant conversion is available and if it makes the operation/function executable. For instance, a comparison of BIGI NT and VARCHAR is performed by implicitly implicitly converting converting VARCHAR to B IGINT. The entire explicit conversions conversions can be used for implicit conversion conversion except except for the TIME and TIMESTAMP data types. TIME and TIMESTAMP can be converted to each other using TO_TIME(TIMESTAMP) and TO_TIMESTAMP(TIME). Examples Input Express Expression ion

Transformed Tran sformed Express Expression ion with Impli Implicit cit Conversi Conversion on

BIGINT BIGINT > VARC VARCHA HAR R

BIGINT BIGINT > BIGINT( BIGINT(VA VARC RCHAR HAR))

BIGINT BIGINT > DECIMA DECIMALL

DECIMA DECIMAL( L(BIGIN BIGINT) T) > DECIM DECIMAL AL

TIMESTAMP TIMESTAMP > DATE DATE

TIMESTAMP TIMESTAMP > TIMESTAM TIMESTAMP(DAT P(DATE) E)

D AT AT E > T IM IME

E rr rr or or b e ca ca u s e th th e re re i s no no co co n ve ve rs rs i o n a va va i l a b l e be be t we we e n DA DA TE TE a n d T IM IME

Table 8: Implicit Type conversion Examples

In the tables below, Boxes with "OK" means data type conversions are allowed without any checks. Boxes with "CHK" means the data t ype can be converted if the data is valid for the tar get type. Boxes with "-" indicates that data type conversion is not allowed. The rules shown are applicable to both implicit and explicit conversion except for Time to Timestamp conversion. Only explicit conversions are allowed for converting the Time data type to Timestamp using the TO_TIMESTAMP or CAST functions.

18

2 0 12 -0 8 -2 1


Target/ Source

t n i l l a m s

t n i y n i t

r e g e t n i

) s , p ( l a m i c e d

l a m i c e d

t n i g i b

l a m i c e d l l a m s

e l b u o d

l a e r

r a h c r a v

r a h c r a v n

tinyint

-

OK

OK

OK

OK

OK

OK

OK

OK

OK

OK

smallint

CH K

-

OK

OK

OK

OK

OK

OK

OK

OK

OK

integer

CH K

CH K

-

OK

OK

OK

OK

OK

OK

OK

OK

bigint

CH K

CH K

CH K

-

OK

CH K

CH K

CH K

OK

OK

OK

decimal

CH K

CH K

CH K

CH K

-

CH K

CH K

CH K

OK

OK

OK

decimal(p,s)

CH K

CH K

CH K

CH K

CH K

CH K

CH K

CH K

CH K

CH K

OK

smalldecimal

CH K

CH K

CH K

CH K

OK

CH K

-

CH K

CH K

OK

OK

real

CH K

CH K

CH K

CH K

OK

CH K

CH K

-

OK

OK

OK

double

CH K

CH K

CH K

CH K

CH K

CH K

CH K

CH K

-

OK

OK

varchar

CH K

CH K

CH K

CH K

CH K

CH K

CH K

CH K

CH K

-

OK

nvarchar

CH K

CH K

CH K

CH K

CH K

CH K

CH K

CH K

CH K

CH K

-

Table 9a: Data type conversion table

Target/ Source

time tim e

date da te

secon sec ondda ddate te

timest tim estam amp p

varc va rcha harr

nvar nv arch char ar

time

-

-

-

-

OK

OK

date

-

-

OK

OK

OK

OK

seconddate

ti m e

d a te

-

t i m e s ta m p

OK

OK

timestamp

ti m e

d a te

s e co n d d a te

-

OK

OK

varchar

CH K

CH K

CH K

CH K

-

OK

nvarchar

CH K

CH K

CH K

CH K

CH K

-

Table 9b: Data type conversion table

Target/ Source

varbin va rbinary ary

alpha al phanum num

varch va rchar ar

nvarch nva rchar ar

varbinary

-

-

-

-

alphanum

-

-

OK

OK

varchar

OK

OK

-

OK

nvarchar

OK

OK

CH K

-

Table 9c: Data type conversion table

Data Type Precedence

This section describes describes the data type precedence implemented implemented by the SAP HANA Data base. Da ta type precedence precedence specifies that the dat a t ype with lower precedence is converted converted to t he data type with higher precedence.

19

2 0 12 -0 8 -2 1


Highest

TIMESTAMP SECONDDATE DATE TIME DOUBLE REAL DECIMAL SMALLDECIMAL BIGINT INTEGER SMALLINT TINYINT NCLOB NVARCHAR CLOB VARCHAR BLOB

Lowest

VARBINARY

Typed Constant A constant is a symbol symbol that represents represents a specific specific fixed data value. Character string contant A charact er string constant is enclosed enclosed in single quotation marks.

'Brian' '100' Unicode Unicode string has a similar format to charact er string but is preceded by an N identifier (N stands for National Language in the SQL-92 standard). The N prefix must must be uppercase. N'abc' SELECT 'Brian' "character string 1", 1", '100' "character string 2", 2", N'abc' N'abc' "unicode st ring" FROM DUMMY; character string 1, character string 2, unicode string Brian, 100, abc

Number constant A number constant is represented by a string of numbers that are not enclosed in quotation marks. Numbers may contain a decimal point or scientific notation.

123 123.4 1.234e2 A hexadecimal number constant is a string of hexadecimal numbers and has the prefix 0x. 0x0abc SELECT 123 "integer" "integer", , 123.4 "decimal1" "decimal1", , 1.234e2 "decimal2" "decimal2", , 0x0abc "hexadecimal" FR OM DUMMY; integer, decimal1, decimal2, hexadecimal 123, 123.4, 123.4, 2748

Binary string constant A binary string has the prefix X and is a string of hexadecimal numbers that are enclosed in quotation marks.

X'00abcd' x'dcba00' SELECT X'00abcd' X'00abcd' "binary string 1", 1", x'dcba00' x'dcba00' "binary string 2" FROM DUMMY; binary string 1, binary string 2

20

2 0 12 -0 8 -2 1


00ABCD, DCBA00

Date/Time/Timestamp consta nt Date, Time and Timestamp each have the following prefixes.

date'2010-01-01' time'11:00:00.001' timestamp'2011-12-31 23:59:59' SELECT date'2010-01-01' "date", time'11:00:00.001' "time", timestamp'2011-12-31 23: 59:59' "timestamp" FROM DUMMY; date, time, timestamp 2010-01-01, 11:00:00, 2011-12-31 23:59:59.0

21

2012-08-21


Predicates A predicate is specified by combining one or more expressions, or logical operators, and returns one of the following logical/truth values: TR UE, FALSE, or UNKNOWN.

Comparison Predicates Two values are compared using comparison predicates and the comparison returns true, false, or unknown. Syntax: ::= { = | != | <> | > | < | >= | <= } [ ANY | SOME| ALL ] { | } ::= , ...

Expressions can be a simple expression such as a character, date, or number. An expression can also be a scalar subquery. ANY, SOME - When ANY or SOME are specified, the comparison returns true if at least one value returned by the subquery or expression_list is true. ALL - When ALL is specified, the comparison returns true if the comparison of all values returned by the subquery or expression_list is true.

Range Predicate A value is compared with a list of values within the provided range. Syntax: ::=

[NOT] BETWEEN

AND

BETWEEN .. . AND ... - When a range predicate is used, it returns true if expression1 is within the range specified by expression2 and expression3. True will only be returned if expression2 has a lesser value than expression3.

In Predicate A value is compared with a specified set of values. True will be returned if the value of expression1 is found in the expression_list (or subquery). Syntax: ::= [NOT] IN { | }

Exists Predicate Returns true if the subquery returns a result set that is not empty and returns false if the subquery returns an empty result set. Syntax: ::= [NOT] EXISTS ( )

LIKE Predicate The LIKE predicate is used for string comparisons. Expression1 is tested for a pattern contained in expression2. Wildcard characters ( % ) and ( _ ) may be used in the comparison string expression2. LIKE returns true if the pattern specified by expression2 is found. The percentage sign (%) mat ches zero or more charac ters and underscore (_) matches exactly one character. To match a percent sign or underscore in the LIKE predicate, an escape character must be used. Using the optional argument, ESCAPE expression3, you can specify the escape character that will be used allowing the underscore (_) or percentage sign (%) to be mat ched. Syntax: ::= [NOT] LIKE [ESCAPE ]

NULL Predicate

22

2012-08-21


When the IS NULL predicate is specified, a value can be compared with NULL. IS NULL returns true if the expression value is NULL. If t he IS NOT NULL predicate is specified, it returns true if a value is not NULL. Syntax: ::= IS [NOT] NULL

CONTAINS Predicate The CONTAINS predicate is used to search for text-matches to a search string in subqueries. Syntax: ::= CONTAINS '(' ',' ')' | CONTAINS '(' ',' ',' ')'

::= '*' | | '(' ')'

::=

::= |

::= empty | ::= | | ::= | ',' :=

|

::= EXACT ::= FUZZY | FUZZY '(' ')' | FUZZY '(' ',' ')' ::= LINGUISTIC ::= WEIGHT '(' ')' :: LANGUAGE '(' ')' ::=

search_string The freestyle-search-string format is used (eg. Peter " Palo Alto" OR Berlin -"SAP LABS" ) search_specifier If the search_specifier clause is not specified EXACT is taken as default EXACT EXACT returns true for those records where exact mat ches of the searchterms are found in the searchattributes FUZZY FUZZY returns true for those records where words similar to the searchterms are found in the searchattributes (e.g. spelling errors will be ignored to a certain extent) float_const If float_const is omitted, then 0.8 is t he default. This default can be overridden by defining para meter FUZZINESSTHRESHOLD supported by columnstore join-views WEIGHT If a weights list is specified, it must be the same size as the number of (expanded) columns in LANGUAGE

23

2012-08-21


LANGUAGE is used during preprocessing of the search string and as a pre-search filter. Only documents which match the search string a nd the language specified are returned. LINGUISTIC LINGUISTIC returns true for those records where word-variants of the searchterms are found in the searchattributes (e.g. searching for 'cats' will also return records which contain 'cat') Limitations: If t here are multiple CONTAINS predicates specified in the where clause of a select statement, then only one of the predicates may consist of more than one column in the list The CONTAINS-predicate works only on column store tables (simple tables and join-views)

Examples: Exact search select * from T where contains(column1, 'dog OR cat') -- EXACT is implicit select * from T where contains(column1, 'dog OR cat', EXACT) select * from T where contains(column1, '"cats and dogs"') -- phrase search

Fuzzy search select * from T where contains(column1, 'catz', FUZZY(0.8))

Linguistic search: select * from T where contains(column1, 'catz', LINGUISTIC)

Freestyle search: The freestyle-search is a search mulitple columns. select * from T where CONTAINS( (column1,column2,column3), 'cats OR dogz', FUZZY(0. 7)) select * from T where CONTAINS( (column1,column2,column3), 'cats OR dogz', FUZZY(0. 7))

24

2012-08-21


Operators You can perform arithmetic operations in expressions by using operators. Operators can be used for calculation, value comparison or to assign values.

Unary and Binary Operators Operator

Operation

Format

Description unary plus operator(+)

Unary

A unary operator applies to one operand or a single value expression.

operator operand

unary negation operator(-) logical negation(NOT) multiplicative operators ( *, / ) additive operators ( +,- )

Binary

Binary A binary operator applies to two operands or two value e xpress ions.

operand1 operator operand2

comparison operators ( =,!=,<,>,<=,>=) logical operators ( AND, OR )

Table 10. Unary and binary operators

Operator Precedence An expression can use several operators. If the number of operators is greater than one then the SAP HANA Database will evaluate t hem in order of operator precedence. You can change the order of evaluation by using parentheses, as expressions contained within parentheses are always evaluated first. If parentheses are not used, the operators have the precedence indicated by the table below. Please note, the SAP HANA Data base will evaluate operators with equal precedence from left to right within an expression. Precedence Hi ghes t

Lowe s t

Operator

Operation

()

pa renthe s e s

+, -

una ry pos i ti ve a nd ne ga ti ve ope ra ti on

*, /

mul ti pl i ca ti on, di vi s i on

+, -

a ddi ti on, s ubtra cti on

||

conca tena ti on

=, !=, <, >, <=, >=, IS NULL, LIKE, BETWEEN

comparsion

NOT

l ogi ca l nega ti on

AND

conjuncti on

OR

di s juncti on

Table 11. SQL operator precedence

Arithmetic Operators You use arithmetic operators to perform mathematical operations such as adding, subtracting, multiplying, dividing and negation of numeric values.

25

2012-08-21


Operator

Description

-

Ne ga ti on . I f th e e xp re s si on i s th e NU LL va l ue , th e re s ul t i s N ULL.

+

Addition. If either express ion i s the NULL value, the result i s NULL.

Subtraction. If either expression is the NULL value, the result is NULL.

*

Multipli cation. If either express ion i s NULL, the resul t is NULL.

/

Division. If ei ther expression i s NULL, or if the se cond expression i s 0, an error is returned.

Table 12. Arithmetic operators

String Operators A concatenation operator combines two items such as strings, expressions or constants into one. Operator

Description

||

String concatenation (two vertical bars). If ei ther string is NULL, it returns NULL.

Table 13. Concatenation operators

For VARCHAR or NVARCHAR type strings, leading or tra iling spaces are kept. If either string is of data t ype NVARCHAR, the result has data type NVARCHAR and is limited to 5000 characters. The maximum length for VARCHAR concatenation is also limited to 5000 chara cters.

Comparsion Operators Syntax: ::=

Operator

Description

Example

=

Equa l to

SELECT * FROM s tudents WHERE i d = 25;

>

Grea ter tha n

SELECT * FROM s tudents WHERE i d > 25;

<

Le s s tha n

SELECT * FROM s tudents WHERE i d < 25;

>=

Gre a te r th an or e qu al to

S ELECT * F ROM s tu de nts WH ER E i d >= 25;

<=

Le ss tha n or e qua l to

SELECT * FROM s tude nts WHERE i d <= 25;

Not e qua l

SELECT * FROM students WHERE id != 25; SELECT * FROM students WHERE id <> 25;

!=, <>

Table 14. Comparison operators

Logical Operators Search conditions can be combined using AND or OR operators. You can also negate them using the NOT operator. O pe rat or

Sy nt ax

De scr ip tio n

AND

WHERE condition1 AND condition2

When using AND, the combined condition is TRUE if both conditions are TRUE, FALSE if either condition is FALSE, and UNKNOWN otherwise.

OR

WHERE condition1 OR condition2

When usi ng OR, the combined condition is TRUE if either condition i s TRUE, FALSE if both conditions a re FALSE, and UNKNOWN otherwise.

NOT

WHERE NOT condition

The NOT operator is placed before a condition to negate the condition. The NOT condition is TRUE if condition is FALSE, FALSE if condition is TRUE, and UNKNOWN if condition is UNKNOWN.

Table 15. Logical operators

Set Operators The operators described in this section perform set operations on the results of two or more queries.

26

2012-08-21


Operator

Returned Value

U NI O N

Co mb i n e s th e r es u l ts o f t wo o r mo re s e l e c t s t a te m e nt s o r q ue ry e xp re s s i o n s

UNION ALL

Combines the results of two or more s elect statements or query express ions, including a ll duplicate rows.

INTERSECT

Combines the results of two or more s elect statements or query expressions , and returns all common rows.

EXCEPT

Takes output from the first query and then removes rows sele cted by the second query.

Table 16. Set operators

27

2012-08-21


Expressions An expression is a clause t hat ca n be evaluated to r eturn values. Syntax: ::= | | | ( ) | ( ) | - | | | | [.]

Case Expressions A case expression allows the user to use IF ... THEN .. . ELSE logic without using procedures in SQL statements. Syntax: ::= CASE WHEN THEN , ... [ ELSE ] { END | END CASE }

If the expression following the CASE statement is equal to the expression following the WHEN statement, then the expression following the THEN statement is returned. Otherwise the expression following the ELSE statement is returned if it exists.

Function Expressions SQL built-in functions can be used as an expression. Syntax: ::= ( , ... )

Aggregate Expressions An aggrega te expression uses an aggrega te function to calculate a single value from the values of multiple rows in a column. Syntax: ::= COUNT(*) | ( [ ALL | DISTINCT ] ) ::= COUNT | MIN | MAX | SUM | AVG | STDDEV | VAR

Aggregate name

Description

COUNT

Counts the numbe r of rows returned by a que ry. COUNT(*) returns the nu mber of rows, regardle ss of the value of those rows and including duplicate values. COUNT() returns the number of non-NULL val ues for that expres si on returned by the query.

MI N

R etu rns the mi ni mum va l ue of e xp re ss i on .

MAX

R etu rns the ma xi mum va l ue of exp re ss i on .

SUM

Re turns the s um of e xpre ss ion.

AVG

R etu rn s th e a ri th me ti ca l me a n o f e xp re s si on .

STDDEV

Returns the standard deviation of given expression as the square root of VARIANCE function.

V AR

R e tu rn s th e va ri a n ce o f e xp re s s i on a s th e s qu a re o f s ta n da rd de vi a ti o n.

Subqueries in Expressions

28

2012-08-21


A subquery is a SELECT statement enclosed in parentheses. The SELECT statement can contain one and only one select list item. When used as an expression a scalar subquery is allowed to return only zero or one value. Syntax: ::= ()

Within the SELECT list of the top level SELECT, or in the SET clause of an UPDATE statement, you can use a scalar subquery anywhere that you can use a column name. However, scalar_subquery cannot be used inside GROUP BY clause. Example: For example, the following statement returns the number of employees in each department, grouped by department name: SELECT DepartmentName, COUNT(*), 'out of', (SELECT COUNT(*) FROM Employees) FROM Departments AS D, Employees AS E WHERE D.DepartmentID = E.DepartmentID GROUP BY DepartmentName;

29

2012-08-21


SQL Functions Introduction This chapter describes SQL Functions that are provided by SAP HANA Database. Data Type Conversion Functions DateTime Functions Number Functions String Functions Miscellaneous Functions

Data Type Conversion Functions Data type conversion functions are used to convert arguments from one data t ype to another, or to test whether a conversion is possible. CAST Syntax

CAST (expression AS data_type) Syntax Elements

expression - The expression to be converted. data type - The target data t ype. TINYINT | SMALLINT | INTEGER | BIGI NT | DECIMAL | SMALLDECIMAL | REAL | DOUBLE | ALPHANUM | VARCHAR | NVARCHAR | DAYDATE | DATE | TIME | SECONDDATE | TIMESTAMP Description

Returns the value of an expression converted to a supplied data type. Example

SELECT CAST (7 AS VARCHAR) "cast" FROM DUMMY; cast 7

TO_ALPHANUM Syntax

TO_ALPHANUM (value) Description

Converts a given value to an ALPHANUM data type. Example

SELECT TO_ALPHANUM ('10') "to alphanum" FROM DUMMY; to alphanum 10

TO_BIGINT Syntax

TO_BIGINT (value) Description

Converts a value to a BI GINT data type.

30

2012-08-21


Example

SELECT TO_BIGINT ('10') "to bigint" FROM DUMMY; to bigint 10

TO_BINARY Syntax

TO_BINARY (value) Description

Converts a value to a BI NARY data type. Example

SELECT

TO_BINARY ('abc') "to binary" FROM DUMMY;

to binary 616263

TO_BLOB Syntax

TO_BLOB (value) Description

Converts a value to a BLOB data type. The argument value must be a binary string. Example

SELECT TO_BLOB (TO_BINARY('abcde')) "to blob" FROM DUMMY; to blob abcde

TO_CHAR Syntax

TO_CHAR (value [, format]) Description

Converts a given value to a CHAR character data t ype. If t he format specifier is omitted, the conversion is performed using the date format model as explained in Date Formats Example

SELECT

TO_CHAR (TO_DATE('2009-12-31'), 'YYYY/MM/DD') "to char" FROM DUMMY;

to char 2009/12/31

TO_CLOB Syntax

TO_CLOB (value) Description

31

2012-08-21


Converts a value to a CLOB data t ype. Example

SELECT TO_CLOB ('TO_CLOB converts the value to a CLOB data type') "to clob" FROM DU MMY; to clob TO_CLOB converts the value to a CLOB data type

TO_DATE Syntax

TO_DATE (d [, format]) Description

Converts a date string d into a D ATE data type. If t he format specifier is omitted, the conversion is performed using the date format model as explained in Date Formats. Example

SELECT TO_DATE('2010-01-12', 'YYYY-MM-DD') "to date" FROM DUMMY; to date 2010-01-12

TO_DATS Syntax

TO_DATS (d) Description

Converts a date string d into an ABAP DATE string with format 'YYYYMMD D'. Example

SELECT TO_DATS ('2010-01-12') "abap date" FROM DUMMY; abap date 20100112

TO_DECIMAL Syntax

TO_DECIMAL (value [, precision, scale]) Description

Converts the value to a D ECIMAL data type. The precision is the total number of significant digits and can range from 1 to 34. The scale is the number of digits from the decimal point to the least significant digit and ca n range from 6,111 to 6,176. This means that the scale specifies the range of t he exponent in the decimal number from 106111 to 106176. If the scale is not specified, it defaults to 0. Scale is positive when the number has significant

digits to the right of t he decimal point, and negative when the number has significant digits to the left of the decimal point. When precision and scale are not specified, DECIMAL becomes a floating-point decimal number. In this case, precision and scale can vary within the range described above, 1~34 for precision and -6,111~6,176 for scale depending on the stored value.

32

2012-08-21


Example

SELECT

TO_DECIMAL(7654 321.888888, 10, 3) "to decimal" FROM DUMMY

to decimal 7654321.889

TO_DOUBLE Syntax

TO_DOUBLE (value) Description

Converts a value t o a DOUBLE (double precision) data type. Example

SELECT 3*TO_DOUBLE ('15.12') "to double" FROM DUMMY; to double 45.36

TO_INT Syntax

TO_INT (value) Description

Converts the value to an I NTEGER dat a type. Example

SELECT TO_INT('10') "to int" FROM DUMMY; to int 10

TO_INTEGER Syntax

TO_INTEGER (value) Description

Converts the value to an I NTEGER dat a type. Example

SELECT

TO_INTEGER ('10') "to int" FROM DUMMY;

to int 10

TO_NCHAR Syntax

TO_NCHAR (value [, format]) Description

Converts the value to a NCHAR unicode character dat a type. If the format specifier is omitted, the conversion

33

2012-08-21


is performed using the date format model as explained in Date Formats. Example

SELECT

TO_NCHAR (TO_DATE('2009-12-31'), 'YYYY/MM/DD') "to nchar" FROM DUMMY;

to nchar 2009/12/31

TO_NCLOB Syntax

TO_NCLOB (value) Description

Converts a value to a NCLOB data t ype. Example

SELECT TO_NCLOB ('TO_NCLOB converts the value to a NCLOB data type') "to nclob" FRO M DUMMY; to nclob TO_NCLOB converts the value to a NCLOB data type

TO_NVARCHAR Syntax

TO_NVARCHAR (value [,format]) Description

Converts the value to a NVARCHAR unicode character data t ype. If the format specifier is omitted, the conversion is performed using the date format model as explained in Date Formats. Example

SELECT

TO_NVARCHAR(TO_DATE('2009/12/31'), 'YY-MM-DD') "to nchar" FROM DUMMY;

to nchar 09-12-31

TO_REAL Syntax

TO_REAL (value) Description

Converts a value t o a REAL (single precision) data type. Example

SELECT 3*TO_REAL ('15.12') "to real" FROM DUMMY; to real 45.36000061035156

TO_SECONDDATE Syntax

TO_SECONDDATE (d [, format])

34

2012-08-21


Description

Converts a date string d into a SECONDDATE data type. If the format specifier is omitted, t he conversion is performed using the date format model as explained in Date Formats. Example

SELECT TO_SECONDDATE ('2010-01-11 13:30:00', 'YYYY-MM-DD HH24:MI:SS') "to seconddat e" FROM DUMMY; to seconddate 2010-01-11 13:30:00.0

TO_SMALLDECIMAL Syntax

TO_SMALLDECIMAL (value) Description

Converts the value to a SMALLDECIMAL data t ype. Example

SELECT

TO_SMALLDECIMAL(7654321.89) "to smalldecimal" FROM DUMMY;

to smalldecimal 7654321.89

TO_SMALLINT Syntax

TO_SMALLINT (value) Description

Converts the value to a SMALLINT data t ype. Example

SELECT TO_SMALLINT ('10') "to smallint" FROM DUMMY; to smallint 10

TO_TIME Syntax

TO_TIME (t [, format]) Description

Converts a time string t into the TIME data t ype. If the format specifier is omitted, the conversion is performed using the time format model as explained in Time Formats. Example

SELECT TO_TIME ('08:30 AM', 'HH:MI AM') "to time" FROM DUMMY; to time 08:30:00

TO_TIMESTAMP

35

2012-08-21


Syntax

TO_TIMESTAMP (d [, format]) Description

Converts a date string d into the TIMESTAMP data type. If t he format specifier is omitted, the conversion is performed using the date format model as explained in Date Formats. Example

SELECT TO_TIMESTAMP ('2010-01-11 13:30:00', 'YYYY-MM-DD HH24:MI:SS') "to timestamp" FROM DUMMY; to timestamp 2010-01-11 13:30:00.0

TO_TINYINT Syntax

TO_TINYINT (value) Description

Converts the value to a TINYINT data type. Example

SELECT TO_TINYINT ('10') "to tinyint" FROM DUMMY; to tinyint 10

TO_VARCHAR Syntax

TO_VARCHAR (value [, format]) Description

Converts a given value to a VARCHAR character data t ype. If the format specifier is omitted, the conversion is performed using the date format model as explained in Date Formats. Example

SELECT

TO_VARCHAR (TO_DATE('2009-12-31'), 'YYYY/MM/DD') "to char" FROM DUMMY;

to char 2009/12/31

DateTime Functions ADD_DAYS Syntax

ADD_DAYS (d, n) Description

Computes the date d plus n days. Example

36

2012-08-21


SELECT ADD_DAYS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 30) "add days" FROM DUMMY; add days 2010-01-04

ADD_MONTHS Syntax

ADD_MONTHS (d, n) Description

Computes the date d plus n months. Example

SELECT ADD_MONTHS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 1) "add months" FROM DUMMY ; add months 2010-01-05

ADD_SECONDS Syntax

ADD_SECONDS (t, n) Description

Computes the time t plus n seconds. Example

SELECT ADD_SECONDS (TO_TIMESTAMP ('2012-01-01 23:30:45'), 60*30) "add seconds" FROM DUMMY; add seconds 2012-01-02 00:00:45.0

ADD_YEARS Syntax

ADD_YEARS (d, n) Description

Computes the date d plus n years. Example

SELECT ADD_YEARS (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), 1) "add years" FROM DUMMY; add years 2010-12-05

CURRENT_DATE Syntax

CURRENT_DATE Description

Returns the current local system date.

37

2012-08-21


Example

SELECT CURRENT_DATE "current date" FROM DUMMY; current date 2010-01-11

CURRENT_TIME Syntax

CURRENT_TIME Description

Returns the current local system time. Example

SELECT CURRENT_TIME "current time" FROM DUMMY; current time 17:37:37

CURRENT_TIMESTAMP Syntax

CURRENT_TIMESTAMP Description

Returns the current local system timestamp information. Example

SELECT CURRENT_TIMESTAMP "current timestamp" FROM DUMMY; current timestamp 2010-01-11 17:38:48.802

CURRENT_UTCDATE Syntax

CURRENT_UTCDATE Description

Returns the current UTC date. The UTC stands for Coordinated Universal Time, also known as Greenwich Mean Time (GMT). Example

SELECT CURRENT_UTCDATE "Coordinated Universal Date" FROM DUMMY; Coordinated Universal Time 2010-01-11

CURRENT_UTCTIME Syntax

CURRENT_UTCTIME Description

38

2012-08-21


Returns the current UTC t ime. Example

SELECT CURRENT_UTCTIME "Coordinated Universal Time" FROM DUMMY; Coordinated Universal Time 08:41:19

CURRENT_UTCTIMESTAMP Syntax

CURRENT_UTCTIMESTAMP Description

Returns the current UTC t imestamp. Example

SELECT CURRENT_UTCTIMESTAMP "Coordinated Universal Timestamp" FROM DUMMY; Coordinated Universal Timestamp 2010-01-11 08:41:42.484

DAYNAME Syntax

DAYNAME (d) Description

Returns the weekday in English for date d. Example

SELECT

DAYNAME ('2011-05-30') "dayname" FROM DUMMY;

dayname MONDAY

DAYOFMONTH Syntax

DAYOFMONTH (d) Description

Returns an integer the day of the month for date d. Example

SELECT

DAYOFMONTH ('2011-05-30') "dayofmonth" FROM DUMMY;

dayofmonth 30

DAYOFYEAR Syntax

DAYOFYEAR (d)

39

2012-08-21


Description

Returns an integer representation of the day of the year for date d. Example

SELECT

DAYOFYEAR ('2011-05-30') "dayofyear" FROM DUMMY;

dayofyear 150

DAYS_BETWEEN Syntax

DAYS_BETWEEN (d1, d2) Description

Computes the number of days between d1 and d2. Example

SELECT DAYS_BETWEEN (TO_DATE ('2009-12-05', 'YYYY-MM-DD'), TO_DATE('2010-01-05', 'Y YYY-MM-DD')) "days between" FROM DUMMY; days between 31

EXTRACT Syntax

EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM d) Description

Finds and returns the value of a specified datetime field from date d. Example

SELECT MY;

EXTRACT (YEAR FROM TO_DATE ('2010-01-04', 'YYYY-MM-DD')) "extract" FROM DUM

extract 2010

HOUR Syntax

HOUR (t) Description

Returns an integer representation of the hour for time t. Example

SELECT

HOUR ('12:34:56') "hour" FROM DUMMY;

hour 12

ISOWEEK Syntax

40

2012-08-21


ISOWEEK (d) Description

Returns the ISO year and week numbers of date d. The week number is prefixed by the letter W. Please also see WEEK Example

SELECT ISOWEEK (TO_DATE('2011-05-30', 'YYYY-MM-DD')) "isoweek" FROM DUMMY; isoweek 2011-W22

LAST_DAY Syntax

LAST_DAY (d) Description

Returns the date of the last day of the month that contains the date d. Example

SELECT LAST_DAY (TO_DATE('2010-01-04', 'YYYY-MM-DD')) "last day" FROM DUMMY; last day 2010-01-31

LOCALTOUTC Syntax

LOCALTOUTC (t, timezone) Description

Convert the local time t from a timezone to the UTC(GMT) time Example

SELECT LOCALTOUTC (TO_TIMESTAMP('2012-01-01 01:00:00', 'YYYY-MM-DD HH24:MI:SS'), ' EST') "localtoutc" FROM DUMMY; localtoutc 2012-01-01 06:00:00.0

MINUTE Syntax

MINUTE (t) Description

Returns an integer representation of the minute for t ime t. Example

SELECT

MINUTE ('12:34:56') "minute" FROM DUMMY;

minute 34

41

2012-08-21


MONTH Syntax

MONTH(d) Description

Returns the number of the month from date d. Example

SELECT

MONTH ('2011-05-30') "month" FROM DUMMY;

month 5

MONTHNAME Syntax

MONTHNAME(d) Description

Returns the name of the month in English for date d. Example

SELECT

MONTHNAME ('2011-05-30') "monthname" FROM DUMMY;

monthname MAY

NEXT_DAY Syntax

NEXT_DAY (d) Description

Returns the date of the next day after date d. Example

SELECT NEXT_DAY (TO_DATE ('2009-12-31', 'YYYY-MM-DD')) "next day" FROM DUMMY; next day 2010-01-01

NOW Syntax

NOW () Description

Returns the current timestamp. Example

SELECT

NOW () "now" FROM DUMMY;

now

42

2012-08-21


2010-01-01 16:34:19.894

QUARTER Syntax

QUARTER (d, [, start_month ]) Description

Returns the numerical year quarter of date d. The first quarter sta rts in the month specified by start_month. If start_month is not specified the first quarter is assumed to begin in January. Example

SELECT

QUARTER (TO_DATE('2012-01-01', 'YYYY-MM-DD'), 2) "quarter" FROM DUMMY;

quarter 2011-Q4

SECOND Syntax

SECOND (t) Description

Returns an integer representation of the second for time t . Example

SELECT

SECOND ('12:34:56') "second" FROM DUMMY;

second 56

SECONDS_BETWEEN Syntax

SECONDS_BETWEEN (d1, d2) Description

Computes the number of seconds between date arguments d1 and d2, which is semantically equal to d2 - d1. Example

SELECT SECONDS_BETWEEN ('2009-12-05', '2010-01-05') "seconds between" FROM DUMMY; Seconds between 2678400

UTCTOLOCAL Syntax

UTCTOLOCAL (t, timezone) Description

Convert the UTC(GMT) time t t o the local time in a timezone Example

43

2012-08-21


SELECT UTCTOLOCAL (TO_TIMESTAMP('2012-01-01 01:00:00', 'YYYY-MM-DD HH24:MI:SS'), ' EST') "utctolocal" FROM DUMMY; utctolocal 2011-12-31 20:00:00.0

WEEK Syntax

WEEK (d) Description

Returns the week number of date d. Please also see ISOWEEK Example

SELECT

WEEK (TO_DATE('2011-05-30', 'YYYY-MM-DD')) "week" FROM DUMMY;

week 23

WEEKDAY Syntax

WEEKDAY (d) Description

Returns an integer representation of the day of the week for date d. The r eturn value ranges from 0 to 6, representing Monday(0) through to Sunday(6). Example

SELECT WEEKDAY (TO_DATE ('2010-12-31', 'YYYY-MM-DD')) "week day" FROM DUMMY; week day 4

YEAR Syntax

YEAR (d) Description

Returns the year number of date d. Example

SELECT

YEAR (TO_DATE ('2011-05-30', 'YYYY-MM-DD')) "year" FROM DUMMY;

year 2011

Number Functions Number functions take numeric values, or strings with numeric characters, as inputs and return numeric values. When strings with numeric characters are provided as inputs implicit conversion from a string to a number is performed automatically before results are computed. ABS

44

2012-08-21


Syntax

ABS (n) Description

Returns the absolute value of the numeric argument n. Example

SELECT ABS (-1) "absolute" FROM DUMMY; absolute 1

ACOS Syntax

ACOS (n) Description

Returns the arc-cosine, in radians, of the numeric argument n between -1 and 1. Example

SELECT ACOS (0.5) "acos" FROM DUMMY; acos 1.0471975511965979

ASIN Syntax

ASIN (n) Description

Returns the arc-sine, in ra dians, of the numeric argument n between -1 a nd 1. Example

SELECT ASIN (0.5) "asin" FROM DUMMY; asin 0.5235987755982989

ATAN Syntax

ATAN (n) Description

Returns the arc-tangent, in radians, of the numeric argument n. The range of n is unlimited. unlimited. Example

SELECT ATAN (0.5) "atan" FROM DUMMY; atan 0.4636476090008061

45

2 0 12 -0 8 -2 1


ATAN2 Syntax

ATAN2 (n, m) Description

Returns the arc-tangent, in radians, of the ratio of t wo numbers numbers n and m. This produces the same result result as ATAN(n/m). Example

SELECT ATAN2 (1.0, 2.0) "atan2" FROM DUMMY; atan2 0.4636476090008061

BINTOHEX Syntax

BINTOHEX (expression) Description

Converts a binary value to a hexadecimal value. Example

SELECT BINTOHEX('AB' BINTOHEX('AB') ) "bintohex" FROM DUMMY; bintohex 4142

BITAND Syntax

BITAND (n, m) Description

Performs an AND operation on the bits of the arguments n and m. Both n and m must be non-negative integers. The BITAND function returns a result with BIGINT t ype. Example

SELECT BITAND (255, 123) "bitand" FROM DUMMY; bitand 123

CEIL Syntax

CEIL (n) Description

Returns the first integer that is greater or equal to the value n. Example

SELECT CEIL (14.5) "ceiling" FROM DUMMY;

46

2 0 12 -0 8 -2 1


ceiling 15

COS Syntax

COS (n) Description

Returns the cosine of the angle, in radians, of the a rgument n. Example

SELECT COS (0.0) "cos" FROM DUMMY; cos 1.0

COSH Syntax

COSH (n) Description

Computes the hyperbolic cosine of the argument n. Example

SELECT COSH (0.5) "cosh" FROM DUMMY; cosh 1.1276259652063807

COT Syntax

COT (n) Description

Computes the cotangent of a number n, where the argument is an angle expressed in radians. Example

SELECT

COT (40) "cot" FROM DUMMY;

cot -0.8950829176379128

EXP Syntax

EXP (n) Description

Returns the result of the base of natural logarithms e raised to the power of the arg ument ument n. Example

47

2 0 12 -0 8 -2 1


SELECT EXP (1.0) "exp" FROM DUMMY; exp 2.718281828459045

FLOOR Syntax

FLOOR (n) Description

Returns the largest integer not greater t han the numeric argument n. Example

SELECT FLOOR (14.5) "floor" FROM DUMMY; floor 14

GREATEST Syntax

GREATEST (n1 [, n2]...) Description

Returns the greatest value among the arguments: n1, n2, .. . Example

SELECT GREATEST ('aa', 'ab', 'ba', 'bb') "greatest" FROM DUMMY; greatest bb

HEXTOBIN Syntax

HEXTOBIN (value) Description

Converts a hexadecimal value to a binary value. Example

SELECT HEXTOBIN ('1a') "hextobin" FROM DUMMY; hextobin 1A

LEAST Syntax

LEAST (n1 [, n2]...) Description

Returns the least value among the arg uments: n1, n2.. . Example

48

2012-08-21


SELECT LEAST('aa', 'ab', 'ba', 'bb') "least" FROM DUMMY; least aa

LN Syntax

LN (n) Description

Returns the natural logarithm of the a rgument n. Example

SELECT LN (9) "ln" FROM DUMMY; ln 2.1972245773362196

LOG Syntax

LOG (b, n) Description

Returns the natural logarithm of a number n base b. B ase b must be a positive value greater than 1 and n must be any positive value. Example

SELECT LOG (10, 2) "log" FROM DUMMY; log 0.30102999566398114

MOD Syntax

MOD (n, d) Description

Returns the remainder of a number n divided by a divisor d. When n is negative this function acts differently to the standard computational modulo operation. The following explains example of what the MOD function returns as the result. If d is zero, then n is returned. If n is grea ter than 0 and n is less than d, then n is returned. If n is less than 0 a nd n is greater t han d, then n is returned. In other case that those mentioned above, remainder of the absolute value of n divided by the absolute value of d is used to calculate remainder. If n is less than 0, then the returned remainder from MOD is a negative number, and if n is grea ter than 0, then the returned remainder from MOD is a positive number. Example

SELECT MOD (15, 4) "modulus" FROM DUMMY; modulus

49

2012-08-21


3 SELECT MOD (-15, 4) "modulus" FROM DUMMY; modulus -3

POWER Syntax

POWER (b, e) Description

Calculates the base number b raised to the power of an exponent e. Example

SELECT POWER (2, 10) "power" FROM DUMMY; power 1024.0

ROUND Syntax

ROUND (n [, pos]) Description

Rounds argument n to the specified pos amount of places after the decimal point. Example

SELECT ROUND (16.16, 1) "round" FROM DUMMY; round 16.2 SELECT ROUND (16.16, -1) "round" FROM DUMMY; round 20

SIGN Syntax

SIGN (n) Description

Returns the sign (positive or negative) of the numeric argument n. Returns 1 if n is a positive value,-1 if n is a negative value, and 0 if n is equal to z ero. Example

SELECT SIGN (-15) "sign" FROM DUMMY; sign -1

SIN Syntax

50

2012-08-21


SIN (n) Description

Returns the sine of n, where the argument is an angle expressed in radians. Example

SELECT SIN ( 3.141592653589793/2) "sine" FROM DUMMY; sine 1.0

SINH Syntax

SINH (n) Description

Returns the hyperbolic sine of n, where the argument is an angle expressed in radians. Example

SELECT SINH (0.0) "sinh" FROM DUMMY; sinh 0.0

SQRT Syntax

SQRT (n) Description

Returns the square root of the argument n. Example

SELECT SQRT (2) "sqrt" FROM DUMMY; sqrt 1.4142135623730951

TAN Syntax

TAN (n) Description

Returns the tangent of n, where the argument is an angle expressed in radians. Example

SELECT TAN (0.0) "tan" FROM DUMMY; tan 0.0

TANH Syntax

51

2012-08-21


TANH (n) Description

Returns the hyperbolic tangent of the numeric argument n. Example

SELECT TANH (1.0) "tanh" FROM DUMMY; tanh 0.7615941559557649

UMINUS Syntax

UMINUS (n) Description

Returns the negated value of the numeric argument n. Example

SELECT

UMINUS(-765) "uminus" FROM DUMMY;

uminus 765

String Functions ASCII Syntax

ASCII(c) Description

Returns the integer ASCII value of the first byte in a string c. Example

SELECT ASCII('Ant') "ascii" FROM DUMMY; ascii 65

CHAR Syntax

CHAR (n) Description

Returns the character with the ASCII value of a number n. Example

SELECT CHAR (65) || CHAR (110) || CHAR (116) "character" FROM DUMMY; character Ant

52

2012-08-21


CONCAT Syntax

CONCAT (str1, str2) Description

Returns a combined string consisting of str1 followed by str2. The concatenation operator (||) is identical to this function. Example

SELECT CONCAT ('C', 'at') "concat" FROM DUMMY; concat Cat

LCASE Syntax

LCASE(str) Description

Converts all characters in string str to lowercase. Note:

The LCASE function is identical to the LOWER function. Example

SELECT

LCASE ('TesT') "lcase" FROM DUMMY;

lcase test

LEFT Syntax

LEFT (str, n) Description

Returns the first n characters/bytes from the beginning of string str. Example

SELECT

LEFT ('Hello', 3) "left" FROM DUMMY;

left Hel

LENGTH Syntax

LENGTH(str) Description

Returns the number of characters in string str. For Larg e Object (LOB) types, this function returns the length of the object in bytes. Example

53

2012-08-21


SELECT LENGTH ('length in char') "length" FROM DUMMY; length 14

LOCATE Syntax

LOCATE (haystack, needle) Description

Returns the position of a substring needle within a string haystack. Returns 0 if needle is not found within haystack. Example

SELECT LOCATE ('length in char', 'char') "locate" FROM DUMMY; Locate 11 SELECT LOCATE ('length in char', 'length') "locate" FROM DUMMY; Locate 1 SELECT LOCATE ('length in char', 'zchar') "locate" FROM DUMMY; Locate 0

LOWER Syntax

LOWER(str) Description

Converts all characters in string str to lowercase. Note:

The LOWER function is identical to the LCASE function. Example

SELECT

LOWER ('AnT') "lower" FROM DUMMY;

lower ant

LPAD Syntax

LPAD (str, n [, pattern]) Description

Pads the start of string str with spaces to make a string of n characters in length. If the patt ern argument is provided string str will be padded using sequences of these characters until the required length is met. Example

SELECT LPAD ('end', 15, '12345') "lpad" FROM DUMMY;

54

2012-08-21


lpad 123451234512end

LTRIM Syntax

LTRIM (str [, remove_set]) Description

Returns string str, t rimmed of all leading spaces. I f remove_set is specified, LTR IM removes all the charact ers contained in this set from the start of string str. This process continues until a character not the in remove_set is reached. Note:

remove_set is treated as a set of characters a nd not as a search string. Example

SELECT LTRIM ('babababAabend','ab') "ltrim" FROM DUMMY; ltrim Aabend

NCHAR Syntax

NCHAR (n) Description

Returns the Unicode character with the integer code number n. Example

SELECT NCHAR (65) "nchar" FROM DUMMY; nchar A

REPLACE Syntax

REPLACE (original_string, search_string, replace_string) Description

Searches in original_string for all occurrences of search_string and replaces them with replace_string. If original_string is an empty string, then the result will be an empty string. If t wo overlapping substrings match the search_string in the original_string, then only the first occurrence will be replaced. If original_string does not contain any occurrence of search_string, t hen original_string will be returned unchanged. If original_string, search_string, or replace_string are NULL then NULL is returned. Example

SELECT REPLACE ('DOWNGRADE DOWNWARD','DOWN', 'UP') "replace" FROM DUMMY; replace UPGRADE UPWARD

RIGHT

55

2012-08-21


Syntax

RIGHT(str, n) Description

Returns the rightmost n chara cters/bytes of string str. Example

SELECT

RIGHT('HI0123456789', 3) "right" FROM DUMMY;

right 789

RPAD Syntax

RPAD (str, n [, pattern]) Description

Pads the end of string str with spaces to make a string of n characters in length. I f the pattern a rgument is provided string str will be padded using sequences of these characters until the required length is met. Example

SELECT RPAD ('end', 15, '12345') "right padded" FROM DUMMY; right padded end123451234512

RTRIM Syntax

RTRIM (str [,remove_set ]) Description

Returns string str, t rimmed of all trailing spaces. If remove_set is specified, RTRI M removes all the characters contained in this set from the end of string str. This process continues until a character not the in remove_set is reached. Note:

remove_set is treated as a set of characters a nd not as a search string. Example

SELECT RTRIM ('endabAabbabab','ab') "rtrim" FROM DUMMY; rtrim endabA

SUBSTR_AFTER Syntax

SUBSTR_AFTER (str, pattern) Description

Returns a substring of string str that follows the first occurrence of the pattern arg ument. If str does not contain the patt ern substring, t hen an empty string is returned.

56

2012-08-21


If patt ern is an empty string, then str is returned. If str or patt ern is NULL, then NULL is returned. Example

SELECT SUBSTR_AFTER ('Hello My Friend','My ') "substr after" FROM DUMMY; substr after Friend

SUBSTR_BEFORE Syntax

SUBSTR_BEFORE (str, pattern) Description

Returns a substring of string str before the first occurrence of the pattern argument in the ta rget string. If str does not contain the patt ern substring, t hen an empty string is returned. If patt ern is an empty string, then str is returned. If str or patt ern is NULL, then NULL is returned. Example

SELECT SUBSTR_BEFORE ('Hello My Friend','My') "substr before" FROM DUMMY; substr before Hello

SUBSTRING Syntax

SUBSTRING (str, start_position [, string_length]) Description

Returns a substring of string str starting from start _position within the string. SUBSTRING can return the remaining part of a string from the start_position or optionally, a number of characters set by the string_length parameter. If start_position is less than 0, then it is considered as 1. If string_length is less than 1, then an empty string is returned. Example

SELECT

SUBSTRING ('1234567890',4,2) "substring" FROM DUMMY;

substring 45

TRIM Syntax

TRIM ([[LEADING | TRAILING | BOTH] trim_char FROM] str ) Description

Returns string str after removing leading and trailing spaces. The t rimming operation is carried out either from the start (LEADING), end (TRAILING) or both(BOTH) ends of string str. If either str or trim_char are a null values, then a NULL is returned. If no options are specified, TRIM removes both the leading a nd trailing substring trim_char from string str. If trim_char is not specified, then a single blank space is used.

57

2012-08-21


Example

SELECT

TRIM ('a' FROM 'aaa123456789aa') "trim both" FROM DUMMY;

trim both 123456789 SELECT TRIM (LEADING 'a' FROM 'aaa123456789aa') "trim leading" FROM DUMMY; trim leading 123456789aa

UCASE Syntax

UCASE (str) Description

Converts all charac ters in string str to uppercase. Note:

The UCASE function is identical to the UPPER function. Example

SELECT

UCASE ('Ant') "ucase" FROM DUMMY;

ucase ANT

UNICODE Syntax

UNICODE(c) Description

Returns an integer containing the Unicode code point of the first character in the string, or NULL if the first character is not a valid encoding. Example

SELECT UNICODE ('#') "unicode" FROM DUMMY; unicode 35

UPPER Syntax

UPPER (str) Description

Converts all charac ters in string str to uppercase. Note:

The UPPER function is identical to the UCASE function. Example

SELECT UPPER ('Ant') "uppercase" FROM DUMMY; uppercase ANT

58

2012-08-21


Miscellaneous Functions COALESCE Syntax

COALESCE (expression_list) Description

Returns the first non-NULL expression from a list. At least two expressions must be contained in expression_list, and all expressions must be comparable. The result will be NULL if all the arguments are NULL. Example

CREATE INSERT INSERT INSERT

TABLE coalesce_example (ID INT PRIMARY KEY, A REAL, B REAL); INTO coalesce_example VALUES(1, 100, 80); INTO coalesce_example VALUES(2, NULL, 63); INTO coalesce_example VALUES(3, NULL, NULL);

SELECT id, a, b, COALESCE (a, b*1.1, 50.0) "coalesce" FROM coalesce_example;

ID

A

B

coalesce

1

100.0

80.0

100.0

2

NULL

63.0

69.30000305175781

3

NULL

NULL

50.0

CURRENT_CONNECTION Syntax

CURRENT_CONNECTION Description

Returns the id of the current connection. Example

SELECT CURRENT_CONNECTION "current connection" FROM DUMMY; current connection 2

CURRENT_SCHEMA Syntax

CURRENT_SCHEMA Description

Returns a string containing the current schema name. Example

SELECT CURRENT_SCHEMA "current schema" FROM DUMMY; current schema SYSTEM

CURRENT_USER

59

2012-08-21


Syntax

CURRENT_USER Description

Returns the current user name at t he current statement context. This will be user name which is currently at the top of authorization stack. Example

-- example showing basic function operation using SYSTEM user SELECT CURRENT_USER "current user" FROM DUMMY; current user SYSTEM -- definer-mode procedure declared by USER_A CREATE PROCEDURE USER_A.PROC1 LANGUAGE SQLSCRIPT SQL SECURITY DEFINER AS BEGIN SELECT CURRENT_USER "current user" FROM DUMMY; END; -- USER_B executing USER_A.PROC1 CALL USER_A.PROC1; current user USER_A -- invoker-mode procedure declared by USER_A CREATE PROCEDURE USER_A.PROC2 LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS BEGIN SELECT CURRENT_USER "current user" FROM DUMMY; END; -- USER_B is executing USER_A.PROC CALL USER_A.PROC2; current user USER_B

GROUPING_ID Syntax

GROUPING_ID(column_name_list) Description

GROUPING_ID function can be used with GROUPING SETS to ret urn multiple levels of aggregations in a single result set. GROUPING_ID returns an integer value to identify which grouping set each row belongs to. Each column in GROUPING_ID must be an element of the GROUPING SETS. GROUPING_ID is assigned by converting the bit vector generated from GROUPING SETS to a decimal number by treating the bit vector as a binary number. When a bit vector is composed, 0 is assigned to each column specified in the GROUPING SETS and 1 otherwise in the order it appears in the GR OUPING SETS. By t reating the bit vector a s a binary number, this function returns an integer value a s the output. Example

SELECT customer, year, product, SUM(sales), GROUPING_ID(customer, year, product) FROM guided_navi_tab GROUP BY GROUPING SETS ( (customer, year, product), (customer, year), (customer, product), (year, product), (customer), (year), (product));

60

2012-08-21


CUSTOMER YEAR PRODUCT SUM(SALES) GROUPING_ID(CUSTOMER,YEAR,PRODUCT) 1 C1 2009 P1 100 0 2 C1 2010 P1 50 0 3 C2 2009 P1 200 0 4 C2 2010 P1 100 0 5 C1 2009 P2 200 0 6 C1 2010 P2 150 0 7 C2 2009 P2 300 0 8 C2 2010 P2 150 0 9 C1 2009 a 300 1 10 C1 2010 a 200 1 11 C2 2009 a 500 1 12 C2 2010 a 250 1 13 C1 a P1 150 2 14 C2 a P1 300 2 15 C1 a P2 350 2 16 C2 a P2 450 2 17 a 2009 P1 300 4 18 a 2010 P1 150 4 19 a 2009 P2 500 4 20 a 2010 P2 300 4 21 C1 a a 500 3 22 C2 a a 750 3 23 a 2009 a 800 5 24 a 2010 a 450 5 25 a a P1 450 6 26 a a P2 800 6

IFNULL Syntax

IFNULL (expression1, expression2) Description

Returns the first not NULL input expression. Returns expression1 if expression1 is not NULL. Returns expression2 if expression1 is NULL. Returns NULL if both input expressions are NULL. Example

SELECT IFNULL ('diff', 'same') "ifnull" FROM DUMMY; ifnull diff

SELECT IFNULL (NULL, 'same') "ifnull" FROM DUMMY; ifnull same

SELECT IFNULL (NULL, NULL) "ifnull" FROM DUMMY; ifnull NULL

MAP Syntax

MAP (expression, search1, result1 [, search2, result2] ... [, default_result]) Description

Searches for an expression within a set of search values and returns the corresponding result. If the expression value is not found and default_result is defined, MAP returns default_result. If the expression value is not found and default_result is not defined, MAP returns NULL.

61

2012-08-21


Note:

Search values and corresponding results are always provided in search-result pairs. Example

SELECT MAP(2, 0, 'Zero', 1, 'One', 2, 'Two', 3, 'Three', 'Default') "map" FROM DUMM Y; map Two

SELECT MAP(99, 0, 'Zero', 1, 'One', 2, 'Two', 3, 'Three', 'Default') "map" FROM DUM MY; map Default

SELECT MAP(99, 0, 'Zero', 1, 'One', 2, 'Two', 3, 'Three') "map" FROM DUMMY; map NULL

NULLIF Syntax

NULLIF (expression1, expression2) Description

NULLIF compares the values of two input expressions. If the first expression equals the second expression, NULLIF returns NULL. If expression1 does not equal expression2, NULLIF returns expression1. If expression2 is NULL, NULLIF returns expression1. Example

SELECT NULLIF ('diff', 'same') "nullif" FROM DUMMY; nullif diff SELECT NULLIF('same', 'same') "nullif" FROM DUMMY; nullif NULL

SESSION_CONTEXT Syntax

SESSION_CONTEXT(session_variable) Description

Returns the value of session_variable assigned to the current user. The session_variable accessed can either be predefined or user-defined. Predefined session variables that can be set by the client are 'APPLICATION', 'APPLICATIONUSER', and 'TR ACEPROFILE'. Session variables can be defined or modified using SET [SESSION] = command, and unset using UNSET [SESSION] . SESSION_CONTEXT returns an NVARCHAR with a maximum length of 512 chara cters. Example

To read session variables use:

62

2012-08-21


SELECT SESSION_CONTEXT('APPLICATION') "session context" FROM DUMMY; session context HDBStudio

SESSION_USER Syntax

SESSION_USER Description

Returns the user name of the current session. Example

-- example showing basic function operation using SYSTEM user SELECT SESSION_USER "session user" FROM DUMMY; session user SYSTEM -- definer-mode procedure declared by USER_A CREATE PROCEDURE USER_A.PROC1 LANGUAGE SQLSCRIPT SQL SECURITY DEFINER AS BEGIN SELECT SESSION_USER "session user" FROM DUMMY; END; -- USER_B is executing USER_A.PROC CALL USER_A.PROC1; session user USER_B -- invoker-mode procedure declared by USER_A CREATE PROCEDURE USER_A.PROC2 LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS BEGIN SELECT SESSION_USER "session user" FROM DUMMY; END; -- USER_B is executing USER_A.PROC CALL USER_A.PROC2; session user USER_B

SYSUUID SYSUUID

SYSUUID Description

Returns the SYSUUID of the connected SAP HANA instance. Example

SELECT SYSUUID FROM DUMMY; SYSUUID 4DE3CD576C79511BE10000000A3C2220

63

2012-08-21


SQL Statements This chapter describes the SQL statements that a re supported by the SAP HANA Data base. Schema Definition and Manipulation Statements Data Manipulation Statements System Management Statements Session Management Statements Transaction Management Statements Access Control Statements Data Import Export Statements

Schema Definition and Manipulation Statements ALTER AUDIT POLICY Syntax

ALTER AUDIT POLICY Syntax Elements

::=

The name of the audit policy to be altered. ::= ENABLE | DISABLE

The audit mode enables or disables the audit policy. ENABLE Enables the audit policy. DISABLE Disables the audit policy. Description

The ALTER AUDIT POLICY statement enables or disables an audit policy. must specify an existing audit policy. Only database users having the system privilege AUDIT ADMIN a re allowed to alter a n audit policy. Each database user that has t his privilege can alter any audit policy, regardless of if they are the creator of t he policy. A newly created audit policy is disabled by default and does not cause any auditing to occur. Therefore the audit policy has to be enabled to make its audit actions take effect. An audit policy can be disabled and enabled as often as required.

Configuration Parameters

The following configuration parameters for auditing ar e stored in global.ini, in t he section on auditing configuration: global_auditing_state ( 'true' / 'false' ) Regardless of the number of enabled audit policies, auditing will only occur if the global_auditing_state configuration parameter is set to true. The default is 'false'. default_audit_trail_type ( 'SYSLOGPROTOCOL' / 'CSVTEXTFILE' ) Specifies how auditing results will be stored. SYSLOGPROTOCOL: uses the system syslog. CSVTEXTFILE: stores audit information as comma-separated values in a text file. default_audit_trail_path Specifies the file path where the CSVTEXTFILE should be stored. These parameters can be selected in monitoring view M_INIFILE_CONTENTS if the c urrent user has the

64

2012-08-21


required system privileges. These parameters will only be seen in the case that they have been explicitly set. System Tables and Monitoring Views

AUDIT_POLICY: shows all audit policies and their states M_INIFILE_CONTENTS: shows the data base system configuration parameters Only database users with system privilege CATALOG READ, D ATA ADMIN or I NIFILE ADMI N can view the content of the M_INI FILE_CONTENTS view. For all other data base users this view will be empty. Example

For this example you need to first create an audit policy called priv_audit using the following statement: CREATE AUDIT POLICY priv_audit AUDITING SUCCESSFUL GRANT PRIVILEGE, REVOKE PRIVILEG E, GRANT ROLE, REVOKE ROLE LEVEL CRITICAL;

Now you can enable this audit policy with: ALTER AUDIT POLICY priv_audit ENABLE;

You can also disable the same audit policy with: ALTER AUDIT POLICY priv_audit DISABLE;

ALTER FULLTEXT INDEX Syntax

ALTER FULLTEXT INDEX Syntax Elements

::=

The identifier of the fulltext index to be altered. ::= | QUEUE

Specifies whether the parameters of the fulltext index or the state of the fulltext index queue should be changed. The latter is only possible for an asynchronous explicit fulltext index. ::= [, ...]

A list of fulltext index parameters that are to be changed. ::= FUZZY SEARCH INDEX | PHRASE INDEX RATIO |

::= ON | OFF

FUZZY SEARCH INDEX Use a fuzzy search index. PHRASE INDEX RATIO Specifies the phrase index ratio. ::=

Specifies the percentage of the phrase index ratio, the value used must be between 0.0 and 1.0 ::= SYNC[HRONOUS] | ASYNC[HRONOUS] [FLUSH [QUEUE] ]

SYNC[HRONOUS] Changes the fulltext index to synchronous mode. ASYNC[HRONOUS]

65

2012-08-21


Changes the fulltext index to asynchronous mode. ::= EVERY MINUTES | AFTER DOCUMENTS | EVERY MINUTES OR AFTER DOCUMENTS

When using an asynchronous index you use the flush_queue_elem to specify when to update the fulltext index. ::= FLUSH | SUSPEND | ACTIVATE

FLUSH Update the fulltext index with the documents in the queue which have already been processed. SUSPEND Suspends the fulltext index processing queue. ACTIVATE Activates the fulltext index processing queue. Description

With this command you can either change the parameters of a fulltext index or the state of an index processing queue. The queue is a mechanism used to enable a fulltext index to operate in asynchronous manner, i.e. inserts are not blocked until a document is processed. The ALTER FULLTEXT INDEX stat ement changes the parameters of a fulltext index. The ALTER FULLTEXT IND EX stat ement changes the processing queue state for an asynchronous fulltext index. Example

ALTER FULLTEXT INDEX i1 PHRASE INDEX RATIO 0.3 FUZZY SEARCH INDEX ON

In the example above for fulltext index 'i1' the phrase index is set to 30 percent and the fuzzy search index is turned on. ALTER FULLTEXT INDEX i2 SUSPEND QUEUE

Suspends the queue for fulltext index 'i2'. ALTER FULLTEXT INDEX i2 FLUSH QUEUE

Updates fulltext index 'i2' with all processed documents in the queue. ALTER INDEX Syntax

ALTER INDEX REBUILD Syntax Elements

::=

Specifies the name of the index to be rebuilt. Description

The ALTER I NDEX statement rebuilds an index. Example

The following example rebuilds the idx index. ALTER INDEX idx REBUILD;

66

2012-08-21


ALTER SEQUENCE Syntax

ALTER SEQUENCE [] [RESET BY ] Syntax Elements

::=

The name of the sequence to be altered. ::= , ... ::= | ::= RESTART WITH ::= | | | | | |

INCREMENT BY MAXVALUE NO MAXVALUE MINVALUE NO MINVALUE CYCLE NO CYCLE

RESTART WITH The starting value of the sequence. If you do not specify a value for the RESTART WITH clause, the current value of the sequence is used. ::=

An integer value between 0 and 4611686018427387903 which will be the first value provided by the sequence generator. INCREMENT BY The sequence increment. ::=

An integer value which will be used to increment or decrement the sequence value. MAXVALUE Defines the maximum value that can be generated by the sequence. ::=

A positive integer which defines the maximum value generated by the sequence. The maximum value must be between 0 and 4611686018427387903. NO MAXVALUE When the NO MAXVALUE directive is used the maximum value for an ascending sequence will be 4611686018427387903 and the maximum value for a descending sequences will be -1. MINVALUE Defines the minimum value that can be generated by the sequence. ::=

A positive integer which defines the minimum value generated by the sequence. The minimum value must be between 0 and 4611686018427387903. NO MINVALUE When the NO MINVALUE directive is used, the minimum value for an ascending sequence is 1 and the minimum value for a descending is -4611686018427387903. CYCLE When the CYCLE directive is used the sequence number will be restarted after it reaches its maximum or minimum value.

67

2012-08-21


NO CYCLE When the NO CYCLE directive is used the sequence number will not be restarted after it reaches its maximum or minimum value. ::=

During a restart of the database, the system automatically executes the RESET BY sta tement and the sequence value is restarted with the value determined from the RESET BY subquery. For details about subqueries please see Subquery Description

The ALTER SEQUENCE statement is used to alter the para meters of a sequence generator. Example

In the example below you change the starting sequence value of sequence seq to 2. ALTER SEQUENCE seq RESTART WITH 2;

In the example below you change the maximum value of sequence seq to 100 along with no minimum value. ALTER SEQUENCE seq MAXVALUE 100 NO MINVALUE;

In the example below you change the increment of sequence seq to 2 along with a "no cycle" restriction. ALTER SEQUENCE seq INCREMENT BY 2 NO CYCLE;

In the example below you first create a ta ble r, with a column a. You then change the reset-by subquery of sequence seq to the maximum value contained in column a. CREATE TABLE r (a INT); ALTER SEQUENCE seq RESET BY SELECT MAX(a) FROM r;

ALTER TABLE Syntax

ALTER TABLE { | | | | | | | | | | | | | | | | | } Syntax Elements

::= [ .] ::= ADD ( [], ... ) ::= DROP ( , ... ) ::= ALTER ( [], .. . ) ::= [] [] [DEFAULT ] [GENERATED ALWAYS AS ]

68

2012-08-21


::= NULL | NOT NULL | UNIQUE [BTREE | CPBTREE] | PRIMARY KEY [BTREE | CPBTREE] ::= ::= DATE | TIME | SECONDDATE | TIMESTAMP | TINYINT | SMALLINT | I NTEGER | BIGINT | SMALLDECIMAL | DECIMAL | REAL | DOUBLE | VARCHAR | NVARCHAR | ALPHANUM | SHORTTEXT | VARBINARY | BLOB | CLOB | NCLOB | TEXT ::= CS_ALPHANUM | CS_INT | CS_FIXED | CS_FLOAT | CS_ DOUBLE | CS_DECIMAL_FLOAT | CS_FIXED(p-s, s) | CS_SDFLOAT | CS_STRING | CS_UNITEDECFLOAT | CS_DATE | CS_TIME | CS_FIXEDSTRING | CS_RAW | CS_DAYDATE | CS_SECONDTIME | CS_LONGDATE | CS_SECONDDAT E ::= DDIC_ACCP | DDIC_ALNM | DDIC_CHAR | DDIC_CDAY | DDIC_CLN T | DDIC_CUKY | DDIC_CURR | DDIC_D16D | DDIC_D34D | DDIC_D16R | DDIC_D34R | DDIC_D16 S | DDIC_D34S | DDIC_DATS | DDIC_DAY | DDIC_DEC | DDIC_FLTP | DDIC_GUID | DDIC_INT1 | DDIC_INT2 | DDIC_INT4 | DDIC_INT8 | DDIC_LANG | DDIC_LCHR | DDIC_MIN | DDIC_MON | DDIC_LRAW | DDIC_NUMC | DDIC_PREC | DDIC_QUAN | DDIC_RAW | DDIC_RSTR | DDIC_SEC | DDIC_SRST | DDIC_SSTR | DDIC_STRG | DDIC_STXT | DDIC_TIMS | DDIC_UNIT | DDIC_UTCM | DDIC_UTCL | DDIC_UTCS | DDIC_TEXT | DDIC_VAR C | DDIC_WEEK ::= NULL | | | ::= [] ::= + | ::= | ::= [ []] | ::= . ::= E ::= ::= ::= [] ::= ...

DEFAULT Default specifies a value to be assigned to the column if an INSERT statement does not provide a value for the column. DATA TYPE in column definition Avaliable data types are DATE, TIME, SECONDDATE, TIMESTAMP, TINYINT, SMALLINT, INTEGER, BIGINT, SMALLDECIMAL, DECIMAL, REAL, DOUBLE, VARCHAR, NVARCHAR, ALPHANUM, SHORTTEXT, VARBINARY, BLOB, CLOB, NCLOB and TEXT. Available column store data types are CS_ALPHANUM, CS_INT, CS_FIXED, CS_FLOAT, CS_DOUBLE, CS_DECIMAL_FLOAT, CS_FIXED(p-s,s), CS_SDFLOAT, CS_STRING, CS_UNITEDECFLOAT, CS_DATE, CS_TIME, CS_FIXEDSTRING, CS_RAW, CS_DAYDATE, CS_SECONDTIME, CS_LONGDATE, and CS_SECONDDATE. Available DDIC data types are DDIC_ACCP, DDIC_ALNM, DDIC_CHAR, DDIC_CDAY, DDIC_CLNT, DDIC_CUKY, DDIC_CURR, DDIC_D16D, DDIC_D34D, DDIC_D16R, DDIC_D34R, DDIC_D16S, DDIC_D34S, DDIC_DATS, DDIC_DAY, DDIC_DEC, DDIC_FLTP, DDIC_GUID, DDIC_INT1, DDIC_INT2, DDIC_INT4, DDIC_INT8, DDIC_LANG, DDIC_LCHR, DDIC_MIN, DDIC_MON, DDIC_LRAW, DDIC_NUMC, DDIC_PREC, DDIC_QUAN, DDIC_RAW, DDIC_RSTR, DDIC_SEC, DDIC_SRST, DDIC_SSTR, DDIC_STRG, DDIC_STXT, DDIC_TIMS, DDIC_UNIT, DDIC_UTCM, DDIC_UTCL, DDIC_UTCS, DDIC_TEXT, DDIC_VARC, DDIC_WEEK. GENERATED ALWAYS AS Specifies the expression to generate the column value in runtime. ::= | | |

NULL NOT NULL UNIQUE [BTREE | CPBTREE] PRIMARY KEY [BTREE | CPBTREE]

NULL | NOT NULL The NOT NULL constraint prohibits a column value from being NULL. If NULL is specified it is not considered a constraint, it represents a c olumn that may contain a null value. The default is NULL. UNIQUE Specifies a column as a unique key.

69

2012-08-21


A composite unique key enables the specification of multiple columns as a unique key. With a unique constraint, multiple rows cannot have the same value in the same column. PRIMARY KEY A primary key constraint is a combination of a NOT NULL constraint and a UNIQUE constraint. It prohibits multiple rows from having the same value in the same column. BTREE | CPBTREE Specifies the index type. When column data types are c haracter string types, binary string types, decimal types, or when the constraint is a composite key, or non-unique constraint, the default index type is CPBTREE. Otherwise, BTR EE is used. BTREE keyword has to be used in order to use B+-tree index and the CPBTREE keyword has to be used for the CPB+-tree index. B+-tree is a tree which maintains sorted data that performs efficient insertion, deletion and search of records. CPB+-tree stands for Compressed Prefix B+-Tree, which is based on pkB-tree. CPB+-tree is a very small index because it uses 'partial key' that is only part of full key in index nodes. CPB+-tree shows better performance than B+-Tree for larger keys. If the index type is omitted, the SAP HANA Database chooses the appropriate index considering the column data t ypes. ALTER Increasing the length of a column can be done. When modifying column definition is tried in column store, no error is returned because no check is done inside the database yet. An error may be returned if the data does not fit in the new data type defined when selecting the column. ALTER does not follow data type conversion rules yet. Adding NOT NULL constraint to an existing column is allowed if the table is empty or the default value is specified when the table has data ::= ADD [CONSTRAINT ] PRIMARY KEY ( , ... ) ::=

ADD PRIMARY KEY Adds a primary key constraint. PRIMARY KEY A primary key constraint is a combination of a NOT NULL constraint and a UNIQUE constraint. It prohibits multiple rows from having the same value in the same column. CONSTRAINT Specifies the name of a constraint. ::= DROP PRIMARY KEY

DROP PRIMARY KEY Drops the primary key constraint. ::= PRELOAD ALL | PRELOAD ( ) | PRELOAD NONE

PRELOAD sets/removes the preload flag of the given tables or columns. PRELOAD ALL sets preload flags of all columns in the table, PRELOAD ( ) sets the flags of columns specified, and PRELOAD NONE removes the flags of a ll columns. As a consequence, t hese tables are a utomatically loaded into memory after an index server start. The current status of the preload flag is visible in the system table TABLES, column PRELOAD, possible values ('FULL', 'PARTIALLY', 'NO') and in system table TABLE_COLUMNS, column PRELOAD, possible values ('TRUE', 'FALSE'). ::= [ALTER TYPE] {ROW [THREADS ] | COL UMN [THREADS [BATCH ]]}

ALTER TYPE ROW | COLUMN This command is used to convert the table storage from ROW to COLUMN or from COLUMN to ROW. THREADS Specifies how many threads should be used in parallel for table conversion. The optimal value for number of threads should set to the number of available CPU cores.

70

2012-08-21


Default: The default value is param_sql_table_conversion_parallelism that is, the number of CPU cores specified in the indexserver.ini file. BATCH Specifies the number of rows inserted in batch, and the default value is 2,000,000 which is the optimal value. Insert into column table will be immediately committed after every records insertion, which may reduce memory consumption. BATCH option can be used only when the table is converted from ROW to COLUMN. However, the batch size more than 2, 000,000 might cause high memory consumption, thus it is not recommended to change this value. A new table with a different storage type can be created from an existing ta ble by copying the existing table's columns and data. This command is used to convert the table storage from ROW to COLUMN or from COLUMN to ROW. If t he source table was in ROW storage, then the cr eated table will be in COLUMN storage. ::= MOVE [PARTITION ] TO [LOCATION ] [PH YSICAL] | MOVE [PARTITION ] PHYSICAL

MOVE moves a table to another location in a distributed environment. The port number is the internal indexserver port number, 3xx03. I you have a partitioned table you can only move individual parts by specifying the optional partition number. Moving a partitioned table without specifying a partition number will result in an error. The PHYSICAL keyword is only for column store tables. Row store tables are always moved physical. If the optional keyword PHYSICAL is specified, the persistence storage is immediately moved to the target host. Otherwise move will just create a link inside the new host persistence pointing to the old host persistence. This link will be removed on the next merge or a another move operation without the TO part . The move PHYSICAL operation without TO part will just remove persistence links that might still exists from previouse move operations. LOCATION is supported only for backward-compatibility ::= ADD ::= PARTITION <= VALUES < | PARTITION = | PARTITION OTHERS ::= | ::= | ::= |

ADD PARTITION Adds a partition for tables partitioned with RANGE, HASH RANGE, ROUNDROBIN R ANGE. When adding a partition to a t able which is partitioned by range and with a rest partition, the rest partition can be repartitioned if needed. ::= DROP ::= PARTITION <= VALUES < | PARTITION = | PARTITION OTHERS ::= | ::= | ::= |

DROP PARTITION Drops a partition for tables partitioned with RANGE, HASH R ANGE, ROUNDROBI N RANGE. ::= PARTITION BY [, | ,] | PARTITION BY | PARTITION BY [,] ::= HASH ([, ...]) PARTITIONS { | GET_ NUM_SERVERS() } ::= RANGE ( ) ( ) ::= ROUNDROBIN PARTITIONS { |

71

2012-08-21


GET_NUM_SERVERS()} ::= { | [,...] } [, PARTITION OTH ERS] ::= PARTITION <= VALUES < ::= PARTITION VALUE ::= | YEAR() | M ONTH()

PARTITION BY Partitions table with RANGE, HASH RANGE, ROUNDROBIN RANGE. For table partition clause, refer to CREATE TABLE. ::= MERGE PARTITIONS

MERGE PARTITIONS Merges all parts of a partitioned table into a non-partitioned table. ::= DISABLE PERSISTENT MERGE

DISABLE PERSISTENT MERGE Instructs the merge-manager t o use main-memory merges instead of persistent merges for t he given table. ::= ENABLE PERSISTENT MERGE

ENABLE PERSISTENT MERGE Instructs the merge-manager t o use persistent merges for the given table (default behaviour). ::= ENABLE DELTA LOG

ENABLE DELTA LOG Enables logging for table. After enabling, you have to perform a savepoint to be sure that all dat a is persisted and you have to perfrom a data backup, else you can not recover this data. ::= DISABLE DELTA LOG

DISABLE DELTA LOG Disables logging for table. If logging is disabled, no log entries will be persisted for this table. Changes to t his table will only be written to the data area, when a savepoint is done. This can cause loss of committed transaction, when the indexserver terminates. In ca se of termination, you have to truncate this table and insert all data again. Only use this command for initial load ! ::= ENABLE AUTOMERGE

ENABLE AUTOMERGE Instructs the merge-manager to handle this table. ::= DISABLE AUTOMERGE

DISABLE AUTOMERGE Instructs the merge-manager to ignore this table. Description

The ALTER TABLE sta tement changes the status of a table. Example

Table t is created and the default value of column b is set to 10: CREATE TABLE t (a INT, b INT); ALTER TABLE t ALTER (b INT DEFAULT 10);

Column c is added to table t: ALTER TABLE t ADD (c NVARCHAR(10) DEFAULT 'NCHAR');

72

2012-08-21


A primary key constraint, prim_key is created on ta ble t: ALTER TABLE t ADD CONSTRAINT prim_key PRIMARY KEY (a, b);

The table type of table t is changed to COLUMN: ALTER TABLE t COLUMN;

The preload flags of column b and c are set: ALTER TABLE t PRELOAD (b, c);

Table t is partit ioned with RANGE, and another partition is added: ALTER TABLE t PARTITION BY RANGE (a) (PARTITION VALUE = 1, PARTITION OTHERS); ALTER TABLE t ADD PARTITION 2 <= VALUES < 10;

The session type of table t is changed to HI STORY: ALTER TABLE t CREATE HISTORY;

Logging of table t is disabled: ALTER TABLE t DISABLE DELTA LOG;

CREATE AUDIT POLICY Syntax

CREATE AUDIT POLICY AUDITING LEVEL Syntax Elements

::= SUCCESSFUL | UNSUCCESSFUL | ALL ::= [,]... ::= GRANT PRIVILEGE | GRANT STRUCTURED PRIVILEGE | GRANT ROLE | GRANT ANY | CREATE USER | CREATE ROLE | ENABLE AUDIT POLICY | CREATE STRUCTURED PRIVILEGE | ALTER STRUCTURED PRIVILEGE | CONNECT | SYSTEM CONFIGURATION CHANGE | SET SYSTEM LICENSE

| | | | | | | |

REVOKE PRIVILEGE REVOKE STRUCTURED PRIVILEGE REVOKE ROLE REVOKE ANY DROP USER DROP ROLE DISABLE AUDIT POLICY DROP STRUCTURED PRIVILEGE

| UNSET SYSTEM LICENSE

::= EMERGENCY | ALERT | CRITICAL | WARNING | INFO Description

The CREATE AUDIT POLICY statement creates a new audit policy. This audit policy can be enabled afterwards and will then cause the auditing of the specified audit actions to happen. Only database users having the system privilege AUDIT ADMIN a re allowed to create a n audit policy. The specified audit policy name must not be identical to the name of an existing audit policy. An audit policy defines which audit actions will be audited. Existing audit policies need to be enabled to make the auditing happen. The defines, if only successful or unsuccessful or all executions of the specified audit actions are a udited.

73

2012-08-21


The following audit a ctions are available. They are grouped in several groups. Audit actions of one group can be combined in one audit policy. Audit actions of different groups can not be combined in one audit policy. Audit Policy

Group Number

Description

GRANT PRIVILEGE

1

a udi ts the gra nt of pri vi l ege s to us ers or rol es

REVOKE PRI VILEGE

1

a udi ts the revoke of pri vi l e ges from us ers or rol es

GRANT STRUCTURED PRIVILEGE

1

audits the grant of structured/analytical privile ges to us ers or roles

REVOKE STRUCTURED PRIVILEGE

1

audits the revoke of structured/analytical privileges from users or roles

GRANT ROLE

1

a udi ts the gra nt of rol es to us e rs or rol es

REVOKE ROLE

1

a udi ts the revoke of rol e s from us ers or rol e s

GRANT ANY

1

audits the grant of privileges, structured privileges or roles to users or roles

REVOKE ANY

1

audits the revoke of privileges, structured privileges or roles from users or roles

CREATE USER

2

a udi ts the cre a te of us e rs

DROP USER

2

a udi ts the drop of us ers

CREATE ROLE

2

a udi ts the cre a te of rol e s

DROP ROLE

2

a udi ts the drop of rol es

CONNECT

3

a udi ts the conne ct of us ers to the da ta ba s e

SYSTEM CONFIGURATION CHANGE

4

a ud its cha nge s to th e s ys te m confi gu ra ti on (e .g. I NI FI LE)

ENABLE AUDIT POLICY

5

a udi ts the acti va ti on of a udi t pol i ci es

DISABLE AUDIT POLICY

5

a udi ts the dea cti va ti on of a udi t pol i ci es

CREATE STRUCTURED PRIVILEGE

6

a udi ts the cre ati on of structure d/a na lyti ca l pri vi le ge s

DROP STRUCTURED PRIVILEGE

6

a ud its the de stru cti on o f s tructure d/a na l yti ca l pri vi le ge

ALTER STRUCTURED PRIVILEGE

6

a udi ts the cha nge of s tructure d/a na lyti ca l pri vi le ge

SET SYSTEM LICENSE

7

a udi ts the i ns ta l l a ti on of a li cens e

UNSET SYSTEM LICENSE

7

a udi ts the del eti on of l i cens es

Each audit policy is assigned to a audit level. Possible levels, in decreasing order of importance, are: EMERGENCY, ALERT, CRITICAL, WARNING, INFO. To make auditing happen, audit policies have to be crea ted and enabled and the configuration parameter global_auditing_state (see below) has to be set to t rue; Configuration Parameter

Currently the configuration parameter for auditing are stored in g lobal.ini, section auditing configuration and are t he following: global_auditing_state ( 't rue' / 'false' ) to activate / deactivate the whole auditing, no matter, how many audit policies are available and enabled. The default is false, meaning: no auditing will happen at all. default_audit_trail_type ( 'SYSLOGPROTOCOL' / 'CSVTEXTFILE' ) to specify, how to store the auditing results. SYSLOGPROTOCOL is the default. CSVTEXTFILE should be used only for testing purposes. default_audit_trail_path to specify where to store the file, in case CSVTEXTFILE had been selected. As all configuration parameter, these parameters can be selected in view M_INIFILE_CONTENTS, if the current user has the needed privilege to do so. But these parameter currently will only be seen in case they have been set explicitly. This means, they will not be seen with a newly installed database instance. System and Monitoring Views

AUDIT_POLICY: shows all audit policies and their states M_INIFILE_CONTENTS: shows the configuration parameter concerning auditing Only database users with system privilege CATALOG READ, DATA ADMI N or INI FILE ADMI N can see any info out of the view M_INI FILE_CONTENTS. For other database users this view will be empty.

74

2012-08-21


Example

A new audit policy named priv_audit is created which will audit successful commands concerning granting and revoking of privileges and roles. This audit policy has the medium audit level CRITICAL. This policy has to be enabled explicity (see alter_audit_policy) to make the auditing of the audit policy to happen. CREATE AUDIT POLICY priv_audit AUDITING SUCCESSFUL GRANT PRIVILEGE, REVOKE PRIVILEG E, GRANT ROLE, REVOKE ROLE LEVEL CRITICAL;

CREATE FULLTEXT INDEX Syntax

CREATE FULLTEXT INDEX ON '(' ')' [] Syntax

::=

Specifies the name of the fulltext index. ::= [, ...] ::= LANGUAGE COLUMN | LANGUAGE DETECTION '(' ')' | MIME TYPE COLUMN | | FUZZY SEARCH INDEX | PHRASE INDEX RATIO | CONFIGURATION | SEARCH ONLY | FAST PREPROCESS ::= ON | OFF

LANGUAGE COLUMN Column where the language of a document is specified. LANGUAGE DETECTION Set of languages t o be considered for language detection. MIME TYPE COLUMN Column where the mime-type of a document is specified. FUZZY SEARCH INDEX Specifies whether the fuzzy search index should be used. PHRASE INDEX RATIO Specifies the percentage of the phrase index. Value must be between 0.0 and 1.0 CONFIGURATION Path to a custom configuration file for text analysis. SEARCH ONLY If set to ON the original document content is not stored. FAST PREPROCESS If set to ON, fast preprocessing is used, i.e. linguistic searches are not possible. ::= SYNC[HRONOUS] | ASYNC[HRONOUS] [FLUSH [QUEUE] ]

SYNC If specified a synchronous fulltext index is created. ASYNC If specified an asynchronous fulltext index is created.

75

::= EVERY MINUTES | AFTER DOCUMENTS

2012-08-21


| EVERY MINUTES OR AFTER DOCUMENTS

Specifies when to update the fulltext index if an asynchronous index is used. Description

The CREATE FULLTEXT INDE X statement creates an explicit fulltext index on the given table column. Example

CREATE FULLTEXT INDEX i1 ON A(C) FUZZY SEARCH INDEX OFF SYNC LANGUAGE DETECTION ('EN','DE','KR')

The example above creates a synchronous fulltext index named 'i1' on column 'C' of table 'A' where the fuzzy search index is not used and the set of languag es for language detection consists of 'EN','DE' a nd 'KR'. CREATE INDEX Syntax

CREATE [UNIQUE] [BTREE | CPBTREE] INDEX ON (, ...) [ASC | DESC] Syntax Elements

::= [.] ::= [ASC | DESC]

UNIQUE Used to create unique indexes. Check for duplicates will occur when an index is created and when a record is added to the table. BTREE | CPBTREE Used to select the kind of index to use. BTREE keyword has to be used in order to use B+-tree index and the CPBTREE keyword has to be used for the CPB+-tree index. B+-tree is a tree which maintains sorted data that performs efficient insertion, deletion and search of records. CPB+-tree stands for Compressed Prefix B+-Tree, which is based on pkB-tree. CPB+-tree is a very small index because it uses 'partial key' that is only part of full key in index nodes. CPB+-tree shows better performance than B+-Tree for larger keys. When column data types are charact er string t ypes, binary string types, decimal types, or when the constraint is a composite key, or a non-unique constraint, the default index type is CPBTREE; otherwise, BTREE is used. If neither BT REE nor CPBTREE keyword is specified, then SAP HANA Dat abase chooses the appropriate index type. ASC | DESC Specifies whether the index should be created in ascending or descending order. These keywords can be only used in the btree index, and can be specified only once for each column. Description

The CREATE INDEX statement creat es an index. Example

Table t is creat ed, then CPBTREE index idx is created on column b of table t with ascending order: CREATE TABLE t (a INT, b NVARCHAR(10), c NVARCHAR(20)); CREATE INDEX idx ON t(b);

CPBTREE index idx1 is created on column a of table t with ascending order and column b with desceding order: CREATE CPBTREE INDEX idx1 ON t(a, b DESC);

76

2012-08-21


CPBTREE index idx2 is created on column a and c of table t with desceding order: CREATE INDEX idx2 ON t(a, c) DESC;

UNIQUE CPBTREE index idx3 is created on column b and c of table t with ascending order: CREATE UNIQUE INDEX idx3 ON t(b, c);

UNIQUE BTREE index idx4 is created on column a of table t with ascending order: CREATE UNIQUE INDEX idx4 ON t(a);

CREATE SCHEMA Syntax

CREATE SCHEMA [OWNED BY ] Syntax Elements

::= ::=

OWNED BY Specifies the name of the schema owner. If omitted, the current user will be the owner of the schema. Description

The CREATE SCHEMA statement creates a schema in the current database. Example

CREATE SCHEMA my_schema OWNED BY system;

CREATE SEQUENCE Syntax

CREATE SEQUENCE [] [RESET BY ] Syntax Elements

::= ::= , ... ::= | ::= INCREMENT BY n | MAXVALUE n | NO MAXVALUE | MINVALUE n | NO MINVALUE | CYCLE | NO CYCLE ::= START WITH n

INCREMENT BY Defines the amount the next sequence value is incremented from the last value assigned. The default is 1. Specify a negative value to generate a descending sequence. An error is returned if the INCREMENT B Y value is 0 START WITH Defines the starting sequence value. If you do not specify a value for the START WITH clause, MINVALUE is used for ascending sequences and MAXVALUE is used for descending sequences. MAXVALUE Defines the largest value generated by the sequence and must be between 0 and 4611686018427387903. NO MAXVALUE

77

2012-08-21


When MAXVALUE is not specified, the maximum value for an ascending sequence is 4611686018427387903 and the maximum value for a descending sequences is -1. MINVALUE The minimum value of a sequence can be specified after MINVALUE and is between 0 and 4611686018427387903. NO MINVALUE When MINVALUE is not specified, the minimum value for an ascending sequence is 1 and the minimum value for a descending is -4611686018427387903. CYCLE The sequence number will be reused after it reaches its maximum or minimum value. NO CYCLE Default option. The sequence number will not be reused after it reaches its maximum or minimum value. RESET BY During the restart of the database, database automatically executes the RESET BY statement and the sequence value is restarted with the specified value from the sta tement after RESET BY . If RESET BY is not specified, the sequence value is stored persistently in database. During the restart of the database, the next value of the sequence is generated from the saved sequence value. Description

The CREATE SEQUENCE statement is used to creat e a sequence. A sequence is used to generate unique integers by multiple users. CURRVAL is used to get the current value of the sequence and NEXTVAL is used to get the next value of the sequence. CURRVAL is only valid after calling NEXTVAL in a session. Example

Example 1: Sequence seq is created, then CURRVAL and NEXTVAL are used to get the values from the sequence: CREATE SEQUENCE seq START WITH 11;

NEXTVAL returns 11: SELECT seq.NEXTVAL FROM DUMMY;

CURRVAL returns 11: SELECT seq.CURRVAL FROM DUMMY;

Example 2: If the sequence s is used to create a unique key on column A in the table R, then after a database is restarted, a UNI QUE key value can be creat ed by automatically assigning the maximum value of column A to the sequence value using a RE SET BY statement as follows: CREATE TABLE r (a INT); CREATE SEQUENCE s RESET BY SELECT IFNULL(MAX(a), 0) + 1 FROM r; SELECT s.NEXTVAL FROM DUMMY;

CREATE SYNONYM Syntax

CREATE [PUBLIC] SYNONYM FOR Syntax Elements

::= ::= | | |

78

2012-08-21


Description

The CREATE SYNONYM creates an a lternate name for a table, view, procedure or sequence. You can use a synonym to re-point functions and stored procedures to differing tables, views or sequences without needing to re-write the function or stored procedure. The optional PUBLIC element allows for the creation of a public synonym. A public synonym can be accessed by any users, but the users that have proper privilege on its base object can access the base object.

Example

CREATE SYNONYM a_synonym FOR a;

CREATE TABLE Syntax

CREATE [] TABLE [ | | | ] Syntax Elements

::= [.] ::=

For descriptions on schema name and identifer, please refer to Identifiers. table_type: ::= | | | |

COLUMN ROW HISTORY COLUMN GLOBAL TEMPORARY LOCAL TEMPORARY

ROW, COLUMN If the majority of access is through a large number of tuples but with only a few selected attributes, COLUMN-based storage should be used. If the majority of access involves selecting a few records with all attributes selected, ROW-based storage is preferable. The SAP HANA D atabase uses a combination to enable storage and interpretation in both forms. You ca n define the type of organization for each table. The default value is ROW. HISTORY COLUMN Creates a ta ble with a particular t ransaction session type called 'HISTORY'. Tables with session type HISTORY support time travel; the execution of queries against historic states of the database is possible. Timetravel can be done in the following ways. Session-level timetravel: SET HISTORY SESSION TO UTCTIMESTAMP = SET HISTORY SESSION TO COMMIT I D = ::= : := A database session can be set back to a cert ain point-in-time. The COMMIT ID variant of the stat ement takes a commitid as a para meter. The value of the commitid parameter must occur in COMMIT_ID column of the system table SYS.TRANSACTION_HISTORY, otherwise an exception will be thrown. The COMMIT I D is useful when using user defined snapshots. A user defined snapshot can be taken by simply storing the commitid which is assigned to a t ransaction during the commit phase. The commitid can be ret rieved by executing the following query directly after a transaction commit: SELECT LAST_COMMIT_ID FROM M_TRANSACTIONS WHERE CONNECTION_ID = CURRENT_CONNECTION; The TIMESTAMP-variant of the statement takes a timestamp as parameter. Internally, the timestamp is used to look up a (commit_time,commit_id)-pair inside the system-table SYS.TRANSACTION_HISTORY where the commit_time is close to the given timestamp (to be more precisely: choose pair where maximal COMMIT_TIME is smaller or equal to the given timestamp; if no such pair is found an exception will be raised). The session then will be restored with the determined commit-id as in the COMMIT ID variant. To terminate a restored session to switch back to t he current session, a n explicit COMMIT or ROLLB ACK has to be executed on the DB connection.

79

2012-08-21


Statement-level timetravel: AS OF UTCTIMESTAMP AS OF COMMIT ID

In order to be able to relate the commitid with the commit time, a system table SYS.TRANSACTION_HISTORY is maintained which stores additional information for each transaction which commits data for history table. For detailed information on setting session-level timetravel, please refer to SET HISTORY SESSION, and for details on , please refer to Subquery. Note: . Autocommit has to be turned off when a session should be restored (otherwise an exception will be thrown with an a ppropriate error message) . Non-history tables in restored sessions always show their current snapshot . Only data query statement (select) is allowed inside restored sessions. . A history table must have a primary key . The session type can be checked from the column, SESSION_TYPE of the system table SYS.TABLES. GLOBAL TEMPORARY Table definition is globally available while data is visible only to the current session. The table is truncated at the end of the session. Metadata in a global temporary table is persistent meaning the metadata exists until the table is dropped and the metadata is shared across sessions. Data in a global temporary table is session-specific meaning only the owner session of the g lobal temporary table is allowed to insert/read/truncate the data, exists for the duration of the session and data from the global temporary table is automatically dropped when the session is terminated. Global temporary table can be dropped only when the table does not have any record in it. Supported operations on Global Temporary Table: 1. Create without a primary key 2. Rename table 3. R ename column 4. Truncate 5. Drop 6. Create or Drop view on top of global temporary table 7. Create synonym 8. Select 9. Select into or I nsert 10. Delete 11. Update 12. Upsert or Replace LOCAL TEMPORARY The table definition and data is visible only to the current session. The table is truncated at the end of the session. Metadata exists for the duration of the session and is session-specific meaning only the owner session of the local temporary table is a llowed to see. Dat a in a local temporary table is session-specific meaning only the owner session of the local temporary ta ble is allowed to insert/read/truncate the data, exists for the duration of the session and data from the local temporary table is automatically dropped when the session is terminated. Supported operations on Global Temporary Table: 1. Create without a primary key 2. Truncate 3. Drop 4. Select 5. Select into or I nsert 6. Delete 7. Update 8. Upsert or Replace table_contents_source: ::= (, ...) | [WITH [NO] DATA] | [(, ...)] [WITH [NO] D ATA]] ::= [] | (, ... ) ::= [ ] [] [DEFAULT ] [GENERATED ALWAYS AS ]

80

2012-08-21


::= ::= DATE | TIME | SECONDDATE | TIMESTAMP | TINYINT | SMALLI NT | INTEGER | BIGINT | SMALLDECIMAL | DECIMAL | REAL | DOUBLE | VARCHAR | NVARCHAR | ALPHANUM | SHORTTEXT | VARBINARY | BLOB | CLOB | NCLOB | TEXT ::= CS_ALPHANUM | CS_INT | CS_FIXED | CS_FLOAT | CS_DOUBLE | CS_DECIMAL_FLOAT | CS_FIXED(p-s, s) | CS_SDFLOAT | CS_STRING | CS_UNITEDECFLOAT | CS_DATE | C S_TIME | CS_FIXEDSTRING | CS_RAW | CS_DAYDATE | CS_SECONDTIME | CS_LONGDATE | CS_SEC ONDDATE ::= DDIC_ACCP | DDIC_ALNM | DDIC_CHAR | DDIC_CDAY | DD IC_CLNT | DDIC_CUKY | DDIC_CURR | DDIC_D16D | DDIC_D34D | DDIC_D16R | DDIC_D34R | DD IC_D16S | DDIC_D34S | DDIC_DATS | DDIC_DAY | DDIC_DEC | DDIC_FLTP | DDIC _GUID | DDIC_INT1 | DDIC_INT2 | DDIC_INT4 | DDIC_INT8 | DDIC_LANG | DDIC_LCHR | DDIC _MIN | DDIC_MON | DDIC_LRAW | DDIC_NUMC | DDIC_PREC | DDIC_QUAN | DD IC_RAW | DDIC_RSTR | DDIC_SEC | DDIC_SRST | DDIC_SSTR | DDIC_STRG | DDIC_STXT | DDIC _TIMS | DDIC_UNIT | DDIC_UTCM | DDIC_UTCL | DDIC_UTCS | DDIC_TEXT | DD IC_VARC | DDIC_WEEK ::= NULL | | | ::= [] ::= + | ::= | ::= [ []] | ::= . ::= E ::= ::= ::= [] ::= ...

DEFAULT Default specifies a value to be assigned to the column if an INSERT statement does not provide a value for the column. DATA TYPE in column definition Avaliable data types are DATE, TIME, SECONDDATE, TIMESTAMP, TINYINT, SMALLINT, INTEGER, BIGINT, SMALLDECIMAL, DECIMAL, REAL, DOUBLE, VARCHAR, NVARCHAR, ALPHANUM, SHORTTEXT, VARBINARY, BLOB, CLOB, NCLOB and TEXT. Available column store data types are CS_ALPHANUM, CS_INT, CS_FIXED, CS_FLOAT, CS_DOUBLE, CS_DECIMAL_FLOAT, CS_FIXED(p-s,s), CS_SDFLOAT, CS_STRING, CS_UNITEDECFLOAT, CS_DATE, CS_TIME, CS_FIXEDSTRING, CS_RAW, CS_DAYDATE, CS_SECONDTIME, CS_LONGDATE, and CS_SECONDDATE. Available DDIC data types are DDIC_ACCP, DDIC_ALNM, DDIC_CHAR, DDIC_CDAY, DDIC_CLNT, DDIC_CUKY, DDIC_CURR, DDIC_D16D, DDIC_D34D, DDIC_D16R, DDIC_D34R, DDIC_D16S, DDIC_D34S, DDIC_DATS, DDIC_DAY, DDIC_DEC, DDIC_FLTP, DDIC_GUID, DDIC_INT1, DDIC_INT2, DDIC_INT4, DDIC_INT8, DDIC_LANG, DDIC_LCHR, DDIC_MIN, DDIC_MON, DDIC_LRAW, DDIC_NUMC, DDIC_PREC, DDIC_QUAN, DDIC_RAW, DDIC_RSTR, DDIC_SEC, DDIC_SRST, DDIC_SSTR, DDIC_STRG, DDIC_STXT, DDIC_TIMS, DDIC_UNIT, DDIC_UTCM, DDIC_UTCL, DDIC_UTCS, DDIC_TEXT, DDIC_VARC, DDIC_WEEK. GENERATED ALWAYS AS Specifies the expression to generate the column value in runtime. ::= | | |

NULL NOT NULL UNIQUE [BTREE | CPBTREE] PRIMARY KEY [BTREE | CPBTREE]

NULL | NOT NULL The NOT NULL constraint prohibits a column value from being NULL. If NULL is specified it is not considered a constraint, it represents a c olumn that may contain a null value. The default is NULL. UNIQUE Specifies a column as a unique key. A composite unique key enables the specification of multiple columns as a unique key. With a unique constraint, multiple rows cannot have the same value in the same column. PRIMARY KEY

81

2012-08-21


A primary key constraint is a combination of a NOT NULL constraint and a UNIQUE constraint. It prohibits multiple rows from having the same value in the same column. BTREE | CPBTREE Specifies the index type. When column data types are c haracter string types, binary string types, decimal types, or when the constraint is a composite key, or non-unique constraint, the default index type is CPBTREE. Otherwise, BTR EE is used. BTREE keyword has to be used in order to use B+-tree index and the CPBTREE keyword has to be used for the CPB+-tree index. B+-tree is a tree which maintains sorted data that performs efficient insertion, deletion and search of records. CPB+-tree stands for Compressed Prefix B+-Tree, which is based on pkB-tree. CPB+-tree is a very small index because it uses 'partial key' that is only part of full key in index nodes. CPB+-tree shows better performance than B+-Tree for larger keys. If the index type is omitted, the SAP HANA Database chooses the appropriate index considering the column data t ypes. ::= UNIQUE [BTREE | CPBTREE] | PRIMARY KEY [BTREE | CPBTREE]

This defines a table constraint which can be used on one or more columns of a table. There are two kinds of a table constraint. They are: ::= LIKE ::=

Creates a t able that has the same definition as like_table_name. All the column definitions with constraints and default values are copied from like_table_name. D ata is filled from the specified table when WITH DATA option is provided, however, the default value is WITH NO DATA. ::= AS '()

Creates a table and fills it with the data computed by the . Only NOT NULL constraints are copied by this clause. If column_names are specified, specified column_names override the column names from . The default value is WITH DATA. WITH [NO] DATA Specifies whether the data is copied from or . ::= LOGGING | NO LOGGING [RETENTION ] ::=

LOGGING | NO LOGGING LOGGING (default value) specifies that t able logging is activated. NO LOGGING specifies that logging is deactivated. A NO LOGGING table means that the definition of the table is persistent and globally available, data is temporary and global. RETENTION Specifies the retention time in seconds of the column table created by NOLOGGING. After the specified retention period has elapsed, the table will be dropped if used physical memory of the host reaches above 80%. ::= AUTO MERGE | NO AUTO MERGE

AUTO MERGE | NO AUTO MERGE AUTO MERGE (default value) specifies that a utomatic delta merge is trigg ered. ::= PARTITION BY [, | , ] | PARTITION BY | PARTITION BY [,] ::= HASH ( [, ...]) PARTITIONS { | GET_NUM_SERVERS()} ::= RANGE () (, ...) ::= ROUNDROBIN PARTITIONS { | GET_NUM_SE RVERS()} [, ] ::= { | } [, ...] [, PARTITION OTHE RS] ::= PARTITION <= VALUES < ::= PARTITION VALUE ::= | YEAR() | MONTH() ::= |

82

2012-08-21


::= | ::= | ::=

GET_NUM_SERVERS() is the function that returns the number of servers. PARTITION OTHER S means that all other values that are not specified in partition specification make one partition. It is possible to determine the index servers on which the partitions are created. If you specify the LOCATION, the partitions will be created on these instances using round robin. Duplicates in the list will be removed. If you specify exactly the same number of instances as partit ions in the partition specification, t hen each partition will be assigned to the respective instance in the list. All index servers in the list have to belong to the same instance. If no locations are specified, the part itions will be created randomly. If the number of partitions matches the number of servers - for example by using GET_NUM_SERVERS() - it is ensured that multiple CREATE TABLE calls distribute the partitions in the same way. In case of a multi-level partitioning, this applies for the number of partitions of the first level. This mechanism is useful if several tables are to be created which have a semantic relation to each other. ::= AT [LOCATION] {':' | (':', ...)} ::= ::=

AT LOCATION A table c an be creat ed in the specified location with host:port. Location list can be specified when creating partitioned tables that are distributed on multiple instances. When location list is provided without , the table is creat ed on the first location specified. If location information is not provided, the table will be automatically assigned to one node. This option can be used for both row store and column store tables in a distributed environment. Description

The CREATE TABLE statement creat es a table. Ta bles are created without data except when or is used with the WITH DATA option. Example

Table A is created, which has INTEGER-type column A and B. Column A has the primary key constraint: CREATE TABLE A (A INT PRIMARY KEY, B INT);

A partitioned table P1 is created, which has DATE-type column U. Column U has the primary key constraint and is used as RANGE-partitioning column: CREATE COLUMN TABLE P1 (U DATE PRIMARY KEY) PARTITION BY RANGE (U) (PARTITION '2010 -02-03' <= VALUES < '2011-01-01', PARTITION VALUE = '2011-05-01');

A partitioned table P2 is created, which has I NTEGER-type column I, J and K. Column I and J make the primary key constraint and are used as HASH-partitioning column. Column K is used as sub HASH-partitioning column: CREATE COLUMN TABLE P2 (I INT, J INT, K INT, PRIMARY KEY(I, J)) PARTITION BY HASH ( I, J) PARTITIONS 2, HASH (K) PARTITIONS 2;

A Table C1 is created, which has the same definition as table A. Table C1 also has the same records as table A: CREATE COLUMN TABLE C1 LIKE A WITH DATA;

A Table C2 is created, which has the same column data t ype and NOT NULL constraint as table A. Table C2 has no records: CREATE TABLE C2 AS (SELECT * FROM A) WITH NO DATA;

CREATE TRIGGER Syntax

CREATE TRIGGER ON [REFERENCING ] [] BEGIN []

83

2012-08-21


[] END Syntax Elements

::=

The name of trigger you create. ::=

The name of table your trigger is defined on. For descriptions on identifer, see Identifiers. ::= BEFORE | AFTER

Specify when trigger acction occur. BEFORE Trigger is executed before the subject table operation. AFTER Trigger is executed after the subject ta ble operation. ::= INSERT | DELETE | UPDATE

Specify which data modification command activate trig ger ac tion. ::= | , ::= | AS

When trigger transition variable is declared, trigger ca n access records that are being changed by trigger triggering DML. While row level trigger execution, . represents corresponding column record being changed in trig ger. Here is subject table's column name. See example of transition variable ::= OLD | NEW ::= ROW ::=

OLD You can access old record of DML in trigger, which is to-be-replaced-by-update old record or deleted-oldrecord. UPDATE trigger a nd DELETE trigg er can have OLD ROW transition variable. NEW You can access new record of DML in trigger, which is to-be-inserted new record or to-be-updated new record. UPDATE trigger a nd INSERT trigger can have NEW ROW transition variable.

Only transition variable is supported. Transition table is not supported feature. If you put 'TABLE' a s , you will see not-supported-feature error.

::= FOR EACH ROW

The trigger will be called in a row-wise fashion. Row level trigger execution is default mode without FOR EACH R OW syntax Currently statement-wise trigger is not supported. ::= DECLARE | DECLARE ::=

84

|

2012-08-21


::= ult_assign>] ; | n>] ;

CONSTANT [] [ [] [
::= ::= DATE | TIME | SECONDDATE | TIMESTAMP | TINYINT | SMALLINT | INTEGER | BIGINT | SMALLDECIMAL | DECIMAL | REAL | DOUBLE | VARCHAR | NVARCHAR | ALPHANUM | SHORTTEXT | VARBINARY | BLOB | CLOB | NCLOB | TEXT ::= NOT NULL ::=

DEFAULT | :=

::=

CONDITION ; | CONDITION FOR ;

::= ::=

SQL_ERROR_CODE

trigger_decl_list You can declare trigger variable or condition. Declared variable can be used scalar value assignment or referenced in trigger SQL statement. Declared condition name can be referenced when exception handler.

CONSTANT When CONSTANT kewword was given, you cannot change the variable while trigger execution.

::= | ::= tmt>

DECLARE EXIT HANDLER FOR
::= | ue> ::= | | |

,
SQLEXCEPTION SQLWARNING

Exception handler can be declared for catching with existing sql exception, specific error code number or condition name declared on condition variable. ::= | ::= | | | | | | | | |

Trigger body syntax is a part of procedure body syntax. See detail of create procedure definition in SAP HANA Database SQLScript guide. The syntax of trigger body complies with that of procedure, which are nested block(proc_block), scalar variable assign(proc_assign), if block(proc_if), loop block(proc_loop), for block(proc_for), for ea ch block(proc_foreach), exception signal(proc_signal), exception resignal(proc_resignal), and sql statement(proc_sql).

85

::=

BEGIN []

2012-08-21


[] END ;

You can add another 'BEGIN .. . END; ' block in a nested way. ::= := ;

var_name is variable name, which should be declared previously. ::=

IF THEN [] [] END IF ;

::= ELSEIF THEN

::= ELSE

For descriptions on condition, refer to in SELECT. Using IF ... THEN .. . ELSEIF . .. END IF, you can control execution flow with condition.

::= LOOP END LOOP ;

::= WHILE DO END WHILE ; n>

::= FOR IN [] END FOR ;

::= ::= REVERSE ::= .. ::= FOR AS [] DO END FOR ; ::= ( ) ::= | ,

::= SIGNAL [] ;

::= RESIGNAL [] [] ; ::= | ::= ::= SET MESSEGE_TEXT = '' ::=

SET MESSEGE_TEXT If you set your own message with SET MESSAGE_TEXT, the message is delivered to users when specified error is thrown from trigger execution.

SIGNAL stat ement provide raise an exception explicitly. User-defined range (10000 ~ 19999) will be allowed for the error code to issue. RESIGNAL sta tement provide raise a n exception on the action statement in exception handler. If error code is not specified, RESIGNAL will t hrow up the catched exception.

::= | | | | |

For descriptions on insert_stmt, refer to INSERT . For descriptions on delete_stmt, refer to DELETE. For descriptions on update_stmt, refer to UPDATE.

86

2012-08-21


For descriptions on replace_stmt and upsert_stmt, refer to REPLACE | UPSERT. ::=

SELECT INTO [] [] [] [{ , ... }] [] []

::= | , ::=

For descriptions on select_list, from_clause, where_clause, group_by_clause, having_clause, set_operator, subquery, order_by_clause, limit, refer to SELECT. var_name is scalar variable name which should be declared before. You can assign selected item value to the scalar variable.

Description

CREATE TRIGGER statement creates a trigger. A trigg er is special kind of stored procedure that automatically executes when an event occurs on a certain table. CREATE TRIG GER command defines a set of statements that is executed when a given operation(INSERT/UPDATE/DELETE) takes place on a given object(subject table) Only database users having t he TRIGG ER privilege for the given are allowed to creat e a trigger for that table. Current trigger limitation is described below: INSTEAD_OF trigger is not supported. Access of subject t able which is table that trigger is defined on, is not allowed in trigger body, which means any insert/update/delete/replace/select for table which trigger is on is not allowed. Only row level trigger is supported. Statement level trigger is not supported. Row level trigger means trigg er action will be executed for every row changes. Statement level trigger means trigg er action will be executed once for a statement execution. Syntax 'FOR EACH ROW' means row-wise trigger execution and it is default mode. Even when 'FOR EACH ROW' is not defined, it is still row level trigger. Transition table (OLD/NEW TABLE) is not supported. When trigger SQL statement want to refer to data that are being changed by trigger-triggering-event like insert/update/delete, transition variable/table will be a way SQL statement in trigger body access old and new data. Transition variable is used by row level trigger while transition table is used by statement level trigg er. Trigger execution on partitioned table to multiple host or table in slave node is not supported. A table can have only one trigger for ea ch DML operation which would be a insert trigger, a update trigger and a delete trigg er and three of them all can be activated together. Therefore, a table can have maximum three triggers total. Not supported trigger action feature(while procedure does) : resultset assignment(select resultset assignment to tabletype), exit/continue command(execution flow control), cursor open/fetch/close(get each record data of search result by cursor and access record in loop), procedure call(call another proecedure), dynomic sql execution(build SQL statements dynamically at runtime of SQLScript), return(end SQL statement execution) System and Monitoring Views

TRIGGER S is system view for trigger.

System view TRIGGER S shows: SCHEMA_NAME, TRIGGER_NAME, TRIGGER_OID, OWNER_NAME, OWNER_OID, SUBJECT_TABLE_SCHEMA, SUBJECT_TABLE_NAME, TRIGGER_ACTION_TIME, TRIGGER_EVENT, TRIGGERED_ACTION_LEVEL, DEFINITION

87

2012-08-21


Example

You first need table which trigger is defined on. CREATE TABLE TARGET ( A INT);

You also need tables which trigger accesses and modifies. CREATE TABLE SAMPLE ( A INT);

Following is example of create trigger. CREATE TRIGGER TEST_TRIGGER AFTER INSERT ON TARGET FOR EACH ROW BEGIN DECLARE SAMPLE_COUNT INT; SELECT COUNT(*) INTO SAMPLE_COUNT FROM SAMPLE; IF :SAMPLE_COUNT = 0 THEN INSERT INTO SAMPLE VALUES(5); ELSEIF :SAMPLE_COUNT = 1 THEN INSERT INTO SAMPLE VALUES(6); END IF; END;

Trigger TEST_TRI GGER will be executed after any record insert execution for TARGET table. Since SAPMLE table record count is zero in the first insert try, the trigger TEST_TRIGGER will insert 5 to SAMPLE table. In the second insertion to TARGET table, the t rigger insert 6 t o SAMPLE table because its count is two. INSERT SELECT 5 INSERT SELECT 5 6

INTO TARGET VALUES (1); * FROM SAMPLE; INTO TARGET VALUES (2); * FROM SAMPLE;

Followings are more example of create trigg er. FOR/WHILE Example CREATE TABLE TARGET ( A INT); CREATE TABLE SAMPLE ( A INT); CREATE TRIGGER TEST_TRIGGER_WHILE_UPDATE AFTER UPDATE ON TARGET BEGIN DECLARE found INT := 1; DECLARE val INT := 1; WHILE :found <> 0 DO SELECT count(*) INTO found FROM sample WHERE p = :val; IF :found = 0 THEN INSERT INTO sample VALUES(:val,100000); END IF; val := :val + 1; END WHILE; END; CREATE TABLE TARGET ( A INT); CREATE TABLE control_tab(id INT PRIMARY KEY, name VARCHAR(30), payment INT); CREATE TABLE message_box(message VARCHAR(200), log_time TIMESTAMP);

CREATE TRIGGER TEST_TRIGGER_FOR_INSERT AFTER INSERT ON TARGET BEGIN DECLARE v_id INT := 0; DECLARE v_name VARCHAR(20) := ''; DECLARE v_pay INT := 0; DECLARE v_msg VARCHAR(200) := ''; DELETE FROM message_box; FOR v_id IN 100 .. 103 DO SELECT name, payment INTO v_name, v_pay FROM control_tab WHERE id = :v_id; v_msg := :v_name || ' has ' || TO_CHAR(:v_pay); INSERT INTO message_box VALUES (:v_msg, CURRENT_TIMESTAMP);

88

2012-08-21


END FOR; END;

handler Examples CREATE TABLE TARGET ( A INT); CREATE TABLE MYTAB (I INTEGER PRIMARY KEY); CREATE TRIGGER MYTRIG_SQLEXCEPTION AFTER INSERT ON TARGET BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESS AGE FROM DUMMY; INSERT INTO MYTAB VALUES (1); INSERT INTO MYTAB VALUES (1); -- expected unique violation error: 301 -- not reached END; CREATE TRIGGER MYTRIG_SQL_ERROR_CODE AFTER UPDATE ON TARGET BEGIN DECLARE EXIT HANDLER FOR SQL_ERROR_CODE 301 SELECT ::SQL_ERROR_CODE, ::SQL_ERRO R_MESSAGE FROM DUMMY; INSERT INTO MYTAB VALUES (1); INSERT INTO MYTAB VALUES (1); -- expected unique violation error: 301 -- not reached END; CREATE TRIGGER MYTRIG_CONDITION AFTER DELETE ON TARGET BEGIN DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 301; DECLARE EXIT HANDLER FOR MYCOND SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FR OM DUMMY; INSERT INTO MYTAB VALUES (1); INSERT INTO MYTAB VALUES (1); -- expected unique violation error: 301 -- not reached END;

SIGNAL/RESIGNAL Examples CREATE TABLE TARGET ( A INT); CREATE TABLE MYTAB (I INTEGER PRIMARY KEY); CREATE TRIGGER MYTRIG_SIGNAL AFTER INSERT ON TARGET BEGIN DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001; DECLARE EXIT HANDLER FOR MYCOND SELECT ::SQL_ERROR_CODE, ::SQL_ERROR_MESSAGE FR OM DUMMY; INSERT INTO MYTAB VALUES (1); SIGNAL MYCOND SET MESSAGE_TEXT = 'my error'; -- not reached END; CREATE TRIGGER MYTRIG_RESIGNAL AFTER UPDATE ON TARGET BEGIN DECLARE MYCOND CONDITION FOR SQL_ERROR_CODE 10001; DECLARE EXIT HANDLER FOR MYCOND RESIGNAL; INSERT INTO MYTAB VALUES (1); SIGNAL MYCOND SET MESSAGE_TEXT = 'my error'; -- not reached END;

Transition variable Example CREATE CREATE CREATE INSERT

TABLE TARGET ( A INT, B VARCHAR(10)); TABLE SAMPLE_OLD ( A INT, B VARCHAR(10)); TABLE SAMPLE_NEW ( A INT, B VARCHAR(10)); INTO TARGET VALUES ( 1, 'oldvalue');

CREATE TRIGGER TEST_TRIGGER_VAR_UPDATE AFTER UPDATE ON TARGET REFERENCING NEW ROW mynewrow, OLD ROW myoldrow FOR EACH ROW BEGIN INSERT INTO SAMPLE_new VALUES(:mynewrow.a, :mynewrow.b) INSERT INTO SAMPLE_old VALUES(:myoldrow.a, :myoldrow.b)

89

2012-08-21


END; UPDATE TARGER SET b = 'newvalue' WHERE A = 1; SELECT * FROM SAMPLE_NEW; 1, 'newvalue' SELECT * FROM SAMPLE_OLD; 1, 'oldvalue'

CREATE VIEW Syntax

CREATE VIEW [(, ... )] AS Syntax Elements

::= [.] ::= ::= Description

The CREATE VIEW stat ement effectively creates virtual ta ble based on the results of an SQL statement. I t is not a ta ble in a real sense as it does not contain data in itself. When a column name is specified along with the view name, a query result is displayed with that column name. If a column name is omitted, a query result gives an appropriate name to the column automatically. The number of column names has to be the same as the number of columns returned from . Update operations on views are supported if the following conditions are met: . Each column in the view must map to a column of a single table . If a column in the base table has NOT NULL constraint without default value, the column must be included in view columns to be an insertable view. Update operation on a view is allowed without this condition. . Must not contain an aggrega te or analytic function in a SELECT list for example, the followings are not allowed: . TOP, SET, DISTINCT operator in a SELECT list . GROUP BY, ORDER BY clause . Must not contain a subquery in a SELECT list . Must not contain a sequence value(CURRVAL, NEXTVAL) . Must not contain a column view as the base view If base views or tables are updatable, a view on the base views or tables can be updatable if the a bove conditions are met. Example

View v is created as selects all records from table a: CREATE VIEW v AS SELECT * FROM a;

DROP AUDIT POLICY Syntax

DROP AUDIT POLICY Syntax Elements

::= Description

The DROP AUDI T POLICY stat ement drops an audit policy. must specify an existing a udit policy. Only database users having the system privilege AUDIT ADMIN a re allowed to drop an audit policy. Each database user having this privilege is allowed to drop any audit policy, no matt er if he c reated it or not. Even if an audit policy is dropped, it may happen, that the audit action specified in the dropped audit policy will be audited further. This will happen, if another audit policy is enabled and specifies that audit action as well.

90

2012-08-21


To switch off an audit policy temporarily, it can be disabled and need not be dropped. System and Monitoring Views

AUDIT_POLICY: shows all audit policies and their states M_INIFILE_CONTENTS: shows the configuration parameter concerning auditing Only database users with system privilege CATALOG READ, DATA ADMI N or INI FILE ADMI N can see any info out of the view M_INI FILE_CONTENTS. For other database users this view will be empty. Example

Assuming that the creation of an audit policy happened before using the following statement: CREATE AUDIT POLICY priv_audit AUDITING SUCCESSFUL GRANT PRIVILEGE, REVOKE PRIVILEG E, GRANT ROLE, REVOKE ROLE LEVEL CRITICAL;

Now this audit policy has to be dropped. DROP AUDIT POLICY priv_audit;

DROP FULLTEXT INDEX Syntax

DROP FULLTEXT INDEX Syntax Elements

fulltext_index_name ::=

Specifies the index to be dropped. Description

The DROP FULLTEXT IND EX statement removes a fulltext index. Example

DROP FULLTEXT INDEX idx;

DROP INDEX Syntax

DROP INDEX Syntax Elements

index_name ::=

The index name identifier specifies the index to be dropped. Description

The DROP I NDEX stat ement removes an index. Example

DROP INDEX idx;

DROP SCHEMA Syntax

DROP SCHEMA []

91

2012-08-21


Syntax Elements

::= CASCADE | RESTRICT

Default = RESTRICT Restrict drop behavior will drop the object when there is no dependent object. If there is a dependent object, an error will be thrown. Cascaded drop drops the object and dependent objects. Description

The DROP SCHEMA statement r emoves a schema. Example

Schema my_schema is created, table my_schema.t is created and then my_schema is dropped with CASCADE option: CREATE SCHEMA my_schema; CREATE TABLE my_schema.t (a INT); DROP SCHEMA my_schema CASCADE;

DROP SEQUENCE Syntax

DROP SEQUENCE [] Syntax Elements

::= CASCADE | RESTRICT

Default = RESTRICT Cascaded drop drops the object and dependent objects. When CASCADE option is not specified, non-cascaded drop behavior drops the object and does not drop the dependent objects, but invalidates the dependent objects (VIEW, PROCEDURE). The invalidated object ca n be revalidated when an object that has same schema and object name is creat ed. Object I D, schema name, and object name pair is reserved for revalidating dependent objects. Restrict drop behavior will drop the object when there is no dependent object. If there is a dependent object, an error will be thrown. Description

The DROP SEQUENCE statement removes a sequence. Example

DROP SEQUENCE s;

DROP SYNONYM Syntax

DROP [PUBLIC] SYNONYM [] Syntax Elements

::= ::= CASCADE | RESTRICT

Default = RESTRICT Cascaded drop drops the object and dependent objects. When CASCADE option is not specified, non-cascaded drop behavior drops the object and does not drop the dependent objects, but invalidates the dependent

92

2012-08-21


objects (VIEW, PROCEDURE). The invalidated object ca n be revalidated when an object that has same schema and object name is creat ed. Object ID, schema name, and object name pair will be reserved for revalidating dependent objects. Restrict drop behavior will drop the object when there is no dependent object. If there is a dependent object, an error will be thrown. Description

The DROP SYNONYM statement removes a synonym. The optional PUBLIC element allows for the removal of a public synonym. Example

Table a is created, then synonym a_synonym and public synonym pa_synonym are created for table a: CREATE TABLE a (c INT); CREATE SYNONYM a_synonym FOR a; CREATE PUBLIC SYNONYM pa_synonym FOR a;

Synonym a_synonym and public synonym pa_synonym are dropped: DROP SYNONYM a_synonym; DROP PUBLIC SYNONYM pa_synonym;

DROP TABLE Syntax

DROP TABLE [] Syntax Elements

::= [.] ::= ::= CASCADE | RESTRICT

Default = RESTRICT Cascaded drop drops the object and dependent objects. When CASCADE option is not specified, non-cascaded drop behavior drops the object and does not drop the dependent objects, but invalidates the dependent objects (VIEW, PROCEDURE). The invalidated object can be revalidated when an object that has the same schema and object name is created. The object ID, schema name, and object name pair will be reserved for revalidating dependent objects. Restrict drop behavior drops the object when there is no dependent object. If there is a dependent object, an error is thrown. Description

The DROP TABLE stat ement deletes a table. Example

Table A is cr eated, t hen dropped: CREATE TABLE A(C INT); DROP TABLE A;

DROP TRIGGER Syntax

DROP TRIGGER Syntax Elements

93

2012-08-21


::=

The name of the trigger to be dropped. Description

The DROP TRIGGER statement deletes a trigger. Only database users having the TRI GGER privilege for the table which the trig ger was defined on, are allowed to drop a trigg er for that ta ble. Example

For this example you need to first create a trigger c alled test_trigger like below : CREATE TABLE TARGET ( A INT); CREATE TABLE SAMPLE ( A INT); CREATE TRIGGER TEST_TRIGGER AFTER UPDATE ON TARGET BEGIN INSERT INTO SAMPLE VALUES(3); END;

Now you can drop trigger with: DROP TRIGGER TEST_TRIGGER;

DROP VIEW Syntax

DROP VIEW [] Syntax Elements

::= [.] ::= ::= CASCADE | RESTRICT

Default = RESTRICT Cascaded drop drops the object and dependent objects. When CASCADE option is not specified, non-cascaded drop behavior drops the object and does not drop the dependent objects, but invalidates the dependent objects (VIEW, PROCEDURE). The invalidated object ca n be revalidated when an object that has same schema and object name is creat ed. Object ID, schema name, and object name pair will be reserved for revalidating dependent objects. Restrict drop behavior will drop the object when there is no dependent object. If there is a dependent object, an error will be thrown. Description

The DROP VIEW stat ement removes a view. Example

Table t is creat ed, then view v is created as selects all r ecords from table t: CREATE TABLE t (a INT); CREATE VIEW v AS SELECT * FROM t;

View v is dropped: DROP VIEW v;

RENAME COLUMN Syntax

94

2012-08-21


RENAME COLUMN . TO Syntax Elements

::= ::= Description

The RENAME COLUMN statement changes the name of a column. For the information on column name, please refer to Identifiers. Example

Table B is created: CREATE TABLE B (A INT PRIMARY KEY, B INT);

The list of column names in table B is shown: SELECT COLUMN_NAME, POSITION FROM TABLE_COLUMNS WHERE SCHEMA_NAME = CURRENT_SCHEMA AND TABLE_NAME = 'B' ORDER BY POSITION;

Column A is renamed to C: RENAME COLUMN B.A TO C;

The list of column names in table B after renaming is shown: SELECT COLUMN_NAME, POSITION FROM TABLE_COLUMNS WHERE SCHEMA_NAME = CURRENT_SCHEMA AND TABLE_NAME = 'B' ORDER BY POSITION;

RENAME INDEX Syntax

RENAME INDEX TO Syntax Elements

::= ::= Description

The RENAME IND EX statement changes the name of an index. For the information on index name, please refer to Identifiers. Example

Table B is creat ed, then index idx is created on column B of ta ble B: CREATE TABLE B (A INT PRIMARY KEY, B INT); CREATE INDEX idx on B(B);

The list of index names in table B is shown: SELECT INDEX_NAME FROM INDEXES WHERE SCHEMA_NAME = CURRENT_SCHEMA AND TABLE_NAME = 'B';

Index idx is renamed to new_idx: RENAME INDEX idx TO new_idx;

The list of index names in table B after renaming is shown:

95

2012-08-21


SELECT INDEX_NAME FROM INDEXES WHERE SCHEMA_NAME = CURRENT_SCHEMA AND TABLE_NAME = 'B';

RENAME TABLE Syntax

RENAME TABLE TO Syntax Elements

::= ::= Description

The RENAME TABLE statement changes the name of a table to new_table_name in the same schema. For the information on table name, please refer t o Identifiers. Example

Table A is created in current schema: CREATE TABLE A (A INT PRIMARY KEY, B INT);

The list of table names in current schema is shown: SELECT TABLE_NAME FROM TABLES WHERE SCHEMA_NAME = CURRENT_SCHEMA;

Table A renamed to B: RENAME TABLE A TO B;

The list of table names in current schema after renaming is shown: SELECT TABLE_NAME FROM TABLES WHERE SCHEMA_NAME = CURRENT_SCHEMA;

Schema mySchema is creat ed, then ta ble mySchema.A is crea ted: CREATE SCHEMA mySchema; CREATE TABLE mySchema.A (A INT PRIMARY KEY, B INT);

The list of table names in schema mySchema is shown: SELECT TABLE_NAME FROM TABLES WHERE SCHEMA_NAME = 'MYSCHEMA';

Table mySchema.A is renamed to B: RENAME TABLE mySchema.A TO B;

The list of table names in schema mySchema after renaming is shown: SELECT TABLE_NAME FROM TABLES WHERE SCHEMA_NAME = 'MYSCHEMA';

ALTER TABLE ALTER TYPE Syntax

::= [ALTER TYPE] { ROW [THREADS ] | CO LUMN [THREADS [BATCH ]] } Syntax Elements

::=

Specifies how many threads should be used in parallel for table conversion. The optimal value for number of

96

2012-08-21


threads should set to the number of available CPU cores. ::=

Specifies the number of rows inserted in batch, and the default value is 2,000,000 which is the optimal value. An insertion into a column table will be immediately committed after every bach_size records insertion, which may reduce memory consumption. BATCH option can be used only when the table is converted from ROW to COLUMN. However, t he batch size more t han 2,000,000 might cause high memory consumption, thus it is not recommended to change this value. Description

A new table with a different storage type can be created from an existing ta ble by copying the existing table's columns and data. This command is used to convert the table storage from ROW to COLUMN or from COLUMN to ROW. If t he source table was in ROW storage, then the cr eated table will be in COLUMN storage. Configuration Parameters

The default number of threads used for table conversion is specified in [sql] section of indexserver.ini table_conversion_parallelism = (initial value is 8) Example

For this example you need to first create tables to be converted. CREATE COLUMN TABLE col_to_row (col1 INT, col2 INT) CREATE ROW TABLE row_to_col (col1 INT, col2 INT)

col_to_row table will be created in COLUMN storage and row_to_col table will be created in ROW storage Now you can convert the storage type of col_to_row table from COLUMN to ROW with: ALTER TABLE col_to_row ALTER TYPE ROW

You can also convert the storage type of row_to_col table from ROW to COLUMN with: ALTER TABLE row_to_col ALTER TYPE COLUMN

In order to convert in batch mode you need to add a batch option at the end of statement: ALTER TABLE row_to_col ALTER TYPE COLUMN BATCH 10000

TRUNCATE TABLE Syntax

TRUNCATE TABLE Description

Deletes all rows from a ta ble. TRUNCATE is faster than D ELETE FROM when deleting all records from a table, but TRUNCATE cannot be rollbacked. To r ollback from deleting records, "D ELETE FROM " should be used. HISTORY t ables can also be t runcated just like normal tables by executing this command. All parts of the history table (main, delta, history main and history delta) will be deleted and the content will be lost.

Data Manipulation Statements DELETE Syntax

DELETE [HISTORY] FROM [WHERE ] Syntax Elements

97

2012-08-21


::= [.] ::=

For descriptions on identifier, please refer to Identifiers. ::= | | | |

OR AND NOT ( )

For descriptions on predicates, please refer to Predicates. Description

The DELETE statement deletes records from a t able where the condition is met. I f the WHERE cla use is omitted, then it removes all records from a table. DELETE HISTORY DELETE HISTORY will mark the chosen records of the history-part of t he history-table for deletion. This means that a fter executing this command, timetravel queries referencing the deleted rows may still see these rows. In order to physically delete these rows the following statements have to be executed: ALTER SYSTEM RECLAIM VERSION SPACE; MERGE HI STORY DELTA of ; Please note that in some cases even the execution of the two statements above may not lead to physical deletion. To check whether the rows are physically deleted, the following statement can be helpful: SELECT * FROM WHERE WITH PARAMETERS ('REQUEST_FLAGS'= ('AL LCOMMITTED','HISTORYONLY'));

Note:

The “WITH PARAMETER S ('REQUEST_FLAGS'= ('ALLCOMMITTED','HISTORYONLY'))” clause may only be used for the purpose of validating t he result of using the DELE TE HI STORY statement. Example

CREATE INSERT INSERT INSERT

TABLE T (KEY INT PRIMARY KEY, VAL INT); INTO T VALUES (1, 1); INTO T VALUES (2, 2); INTO T VALUES (3, 3);

In the example below, one record will be deleted. DELETE FROM T WHERE KEY = 1; KE Y V AL 2

2

3

3

EXPLAIN PLAN EXPLAIN PLAN

EXPLAIN PLAN [SET STATEMENT_NAME = ] FOR \ref sql_subquery Syntax Elements

::= string literal used to identify the name of a specific executi on plan in the output table for a given SQL statement.

It is set to NULL if t he SET STATEMENT_NAME is not specified. Description

The EXPLAIN PLAN statement is used to evaluate the execution plan that the SAP HANA Dat abase follows to execute an SQL statement. The result of the evaluation is stored into the E XPLAIN_PLAN_TABLE view for

98

2012-08-21


later user examination. The SQL statement must be data manipulation statement, thus a schema definition language statement cannot be used with the EXPLAIN PLAN command. You can obtain SQL plan from EXPLAIN_PLAN_TABLE view. The view is shared by all users. Here is an example of reading an SQL plan from the view. SELECT * FROM EXPLAIN_PLAN_TABLE;

Columns in EXPLAIN_PLAN_TABLE view:

Table 1: Column name and description Col umn Na me

De s cri pti on

STATEMENT_NAME

The s tring speci fied as STATEMENT_NAME on executing the EXPLAIN PLAN command. This is us ed to distinguish pla ns from each other when there are multiple pl ans i n the EXPLAIN_PLAN_TABLE view .

OPERATOR_NAME

Name of an operator. Details are described in the following section.

OPERATOR_DETAILS

Details of an operator. Predicates and e xpress ions us ed by the operator are shown here.

S CH EMA_ NA ME

N a me of th e s ch e ma of th e a cce s s e d ta b l e.

TABLE_NAME

Na me of the a cce ss ed ta bl e.

TABLE_TYPE

Type of the accesse d tabl e. One of the foll owing option s: COLUMN TABLE, ROW TABLE, MONITORING VIEW, JOIN VIEW, OLAP VIEW, CALCULATION VIEW and HIERARCHY VIEW.

TABLE_SI ZE

Es ti ma te d n umbe r of rows in the acce ss ed ta bl e

O UTPU T_ SI ZE

Es ti m ate d n um be r o f ro ws pro du ce d b y a n o pe ra to r

SUBTREE_COST

Estimated cost of executing the subtree starting from an operator. This value is only for relative comparison.

O PER ATO R_I D

I D of a n op e ra to r u ni q ue i n a p l a n. I Ds a re i n te ge rs s t a rti n g f ro m 1.

OPERATOR_ID of the parent of an operator. The shape of an SQL plan is a tree and the PARENT_OPERATOR_ID topology of the tree can be reconstructed usi ng OPERATOR_ID and PARENT_OPERATOR_ID. PARENT_OPERATOR_ID of the root operator i s shown as NULL. LEVEL

Level from the root operator. Level of the root opera tor is 1, level of a chil d of the root operator is 2 and so on. This can be util ized for output indentation.

POSITION

Position in the parent operator. Position of the first child is 1, position of the s econd child is 2 and so on.

HOST

The hos tna me whe re a n ope ra tor wa s exe cuted

PORT

The TCP/IP port us e d to conne ct to the hos t

TI MES TAMP

D ate a nd ti me w he n th e EXPLAI N PLAN co mma nd wa s e xe cu te d.

CONNECTION_ID

ID of the connection where the EXPLAIN PLAN command was executed.

EXECUTION_ENGINE

Type of the execution engine where an operator is executed: COLUMN or ROW

OPERATOR_NAME column in EXPLAIN_PLAN_TABLE view: Table 2. List of column engine operators shown in the OPERATOR_NAME column. Operator Name Description COLUMN SEARCH

Starting position of column engine operators. OPERATOR_DETAILS lists projected columns.

LI MI T

Ope ra to r fo r l imi ti ng the numbe r o f ou tpu t ro ws

O RD ER B Y

O pe ra to r f or s o rti n g o utp ut ro ws

H AV I NG

O pe ra t or f or f i l te ri n g w i th pr ed i ca t e s o n t op of g ro u pi n g a n d a g gr eg a ti o n

G ROU P B Y

O pe ra to r f or g ro up i ng a nd a gg re ga ti o n

D IS TI NCT

Op era to r fo r d up li ca te el i mi na ti on

FI LTER

Ope ra to r fo r fi l te ri ng wi th pre di ca te s

JOIN

Opera tor for joi ni ng i nput re la ti ons

COLUMN TABLE

Information about accessed column table

MULTIPROVIDER

Operator for producing union-al l of multiple results ha ving the same grouping and aggregation

Table 3. List of row engine operators shown in the OPERATOR_NAME column. Operator Name

Description

ROW SEARCH

Starting position of row engine operators. OPERATOR_DETAILS lists projected columns.

99

2012-08-21


LI MI T

Op era to r f or l i mi ti ng n umb er o f o utp ut ro ws

O RD ER BY

O pe ra t or f or s o rt i ng ou tp u t ro ws

H AV I NG

O pe ra t or fo r f i l te ri n g wi t h pre d i ca t e s o n to p of g ro u pi n g a nd a g gre g a ti o n

G RO UP B Y

O pe ra t or f or g ro u pi n g a n d a gg re g a ti o n

MERGE Operator for merging the results of multipl e pa rallel grouping and aggregations AGGREGATION D I STI NCT

Op e ra to r f or d up l i ca te el i mi n a ti o n

F ILTER

Op era to r f or f il te ri ng w ith p re di ca te s

UNION ALL

Operator for producing union-all of input relations

MATERIALIZED Operator for producing union-all of input relations with intermediate result ma terial ization UNION ALL BTREE INDEX JOIN

Operator for joining input relations through B-tree index searches. Join type suffix can be added. For example, B-tree index join for left outer join is shown as BTREE INDEX JOIN (LEFT OUTER). Join without join type suffix means inner join.

CPBTREE INDEX JOIN

Operator for joining input relations through CPB-tree index searches. Join type suffix can be added.

HASH JOIN

Operator for joining input relations through probing hash table built on the fly. Join type suffix can be added.

NESTED LOOP JOIN

Operator for joining input relations through nested looping. Join type suffix can be added.

MIXED INVERTED INDEX JOIN

Operator for joining an input relation of row store format with a column table without format conversion using an inverted index of the column table. Join type suffix can be added.

BTREE INDEX SEARCH

Table access through B-tree index s earch

CPBTREE Table access through CPB-tree index search INDEX SEARCH T AB LE SCA N

T a bl e a c ce s s t hr ou gh s ca n n i ng

AGGR TABLE

Operator for aggregating base table directly

MONITOR SEARCH

Monitoring view access through search

MONITOR SCAN

Monitoring view access through scanning

COLUMN SEARCH is a mark for the starting position of column engine operators and ROW SEARCH is a mark for the starting position of row engine operators. In the example below, the intermediate result produced by a COLUMN SEARCH (ID 10) is consumed by a ROW SEARCH (ID 7), a nd the intermediate result produced by the ROW SEARCH (ID 7) is consumed by another COLUMN SEARCH (ID 1). The operators below the lowest COLUMN SEARCH (ID 10) explain how the COLUMN SEARCH (ID 10) is executed. The operators between the ROW SEARCH (ID 7) a nd the COLUMN SEARCH (ID 10) explain how the ROW SEARCH (ID 7) processes the intermediate result produced by the COLUMN SEARCH (ID 10). The operators between the t op COLUMN SEARCH (ID 1) a nd the ROW SEARCH (ID 7) explain how the top COLUMN SEARCH (ID 1) processes the intermediate result produced by the ROW SEARCH (ID 7). Table 4. Operators OPERATOR_NAME

OPERATOR_ID

PARENT_OPERATOR_ID LEVEL

POSI TION

COLUMN SEARCH

1

NULL

1

1

2

1

2

1

LIMI T ORDER BY GROUP BY JOIN COLUMN TABLE ROW SEARCH BTREE I NDEX JOIN BTREE INDEX JOIN

3

2

3

1

4

3

4

1

5

4

5

1

6

5

6

1

7 color="BLUE">5 color="BLUE">6 color="BLUE">2 8

7

7

1

9

8

8

1

9

1

COLUMN SEARCH color="RED">10 color="RED">9 FILTER COLUMN TABLE

11

10

10

1

12

11

11

1

Example of SQL plan explanation Here is an example of SQL plan explanation of a query. The query is from TPC-H Benchmark. I n the example, all ta bles are located on row store. DELETE FROM explai n_pla n_table WHERE stateme nt_name = 'TPC-H Q10';

100

2012-08-21


EXPLAIN PLAN SET S TATEMENT_NAME = 'TPC-H Q10' FOR SELECT TOP 20 c_custkey, c_name, SUM(l_extendedprice * (1 - l_discount)) AS revenue , c_acctbal, n_name, c_address, c_phone, c_comment FROM customer, orders, lineitem, nation WHERE c_custkey = o_custkey AND l_orderkey = o_orderkey AND o_orde rdate >= '1993-10-01' AND o_orde rdate < ADD_MONTHS('1993-10-01',3) AND l_returnflag = 'R' AND c_nationkey = n_nationkey GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment ORDER BY revenue DESC; SELECT operator_name, operator_details, table_name FROM explain_plan_table WHERE state ment_name = 'TPC-H Q10';

The following is the plan explanation of the above query. OPERATOR_NAME

OPERATOR_DETAILS

ROW SEARCH

CUSTOMER.C_CUSTKEY, CUSTOMER.C_NAME, SUM(LINEITEM.L_EXTENDEDPRICE * (1 LINEI TEM.L_DIS COUNT)), CUSTOMER.C_ACCTBAL, NATI ON.N_NAME, CUSTOMER.C_ADDRESS, CUSTOMER.C_PHONE, CUSTOMER.C_ None COMMENT

LIMI T

TABLE_NAME

NUM RECORDS: 20

ORDER BY

SUM(LINEITEM.L_EXTENDEDPRICE * (1 - LINEITEM.L_DISCOUNT)) DESC

MERGE AGGREGATI ON NUM PARTITIONS: 4 GROUP BY

GROUPING: N ATION.N_NAME, R_CUSTOMER.C_CUSTKEY, AGGREGATION: SUM(LINEI TEM.L_EXTENDEDPRICE * (1 LINEITEM.L_DISCOUNT))

INDEX NAME: _SYS_TREE_RS_279_#0_#P0, CPBTREE INDEX JOIN INDEX CONDITION: ORDERS.O_ORDERKEY = LINEITEM.L_ORDERKEY, INDEX FILTER: 'R' = LINEITEM.L_RETURNFLAG BTREE INDEX JOIN BTREE INDEX JOIN TABLE SCAN

None None None

LINEITEM

INDEX NAME: _SYS_TREE_RS_285_#0_#P0, INDEX CONDI TION: CUSTOMER.C_NATIONKEY = NATION.N_NATIONKEY

NATION

INDEX NAME: _SYS_TREE_RS_283_#0_#P0, INDEX CONDITION: ORDERS.O_CUSTKEY = CUSTOMER.C_CUSTKEY

CUSTOMER

FILTER CONDITION: ORDERS.O_ORDERDATE < '1994-01-01' AND ORDERS.O_ORDERDATE >= '1993-10-01'

ORDERS

This means that: 1. TABLE SCAN will be executed on ORDERS with the FILTER CONDITION. 2. BTR EE IND EX JOIN will be executed with the B-tree index of CUSTOMER and the result of the below TABLE SCAN. 3. BTR EE IND EX JOIN will be executed with the B-tree index of NATION and the result of the below BTREE INDEX JOIN. 4. CPBTREE INDEX JOIN will be executed with the CPB-tree index of LINEITEM a nd the result of the below BTREE INDEX JOIN. 5. GR OUP BY will be executed with the result of the below CPBTREE INDEX JOIN, with 4 threads. 6. MERG E AGGREGATI ON will be executed with the result of the GROUP BY below. INSERT Syntax

101

2012-08-21


INSERT INTO [ ] { | } Syntax Elements

::= [ . ] ::= ::= ( , ... ) ::=

For descriptions on identifier, please refer to Identifiers. ::= VALUES ( , ... )

For descriptions on expressions, please refer to Expressions . Description

The INSERT sta tement adds records to a table. A subquery that returns records can be used to insert records into the table. If t he subquery does not return any records, then the database will not insert a ny records into the table. The column list can be specified with the INSERT statement. The column which isn't included in the column list will be shown as a default value. If the column list is omitted, the database inserts all columns in the table. Example

CREATE TABLE T (KEY INT PRIMARY KEY, VAL1 INT, VAL2 NVARCHAR(20));

In the below example, you can insert the values. INSERT INTO T VALUES (1, 1, 'The first'); KE Y V AL 1 V AL 2 1

1

The fi rs t

You can insert a new value into the specified column. INSERT INTO T (KEY) VALUES (2); KE Y V AL 1 V AL 2 1

1

The fi rs t

2

NULL NULL

And you can use the subquery. INSERT INTO T SELECT 3, 3, 'The third' FROM DUMMY; KE Y V AL 1 V AL 2 1

1

2

NULL NULL

The fi rs t

3

3

The thi rd

LOAD Syntax

LOAD {DELTA | ALL | (, ...)} Description

The LOAD stat ement loads the column store table data to memory explicitely instead of on first access. Syntax Elements

::=

The name of the table to be loaded into memory

102

2012-08-21


::=

The name of the column to be loaded into memory. DELTA DELTA part of the column store table is loaded into memory. Since the column store is read optimized and compressed, deltas are introduced to optimize insert or updates. All insertions are passed to a delta. ALL ALL data of the column store table in main and delta is loaded into memory. Example

The example below loads the whole table a_table into memory LOAD a_table all;

The example below loads the columns a_column and another_column of table a_table into memory LOAD a_table (a_column,another_column);

The load status of a table can be querieed by select loaded from m_cs_tables where table name = ''

MERGE DELTA Syntax

MERGE [HISTORY] DELTA OF [PART n] [WITH PARAMETERS (, ...)] Syntax Elements

WITH PARAMETERS (): Column store-specific options can be passed in using the "WITH PARAMETERS" clause. ::= [.] ::= ::= , ::= = ::= 'SMART_MERGE' | 'MEMORY_MERGE' ::= 'ON' | 'OFF'

Current parameters: 'SMART_MERGE ' = 'ON' | 'OFF' When SMART_MERGE is ON, t he database does a smart merge, t his means database decides whether to merge or not based on merge criteria specified in automerge section of indexserver configuration 'MEMORY_MERGE' = 'ON' | 'OFF' Database merges ta ble delta part in memory only, it will not be persisted. Description

The MERGE D ELTA statement merges the column store table delta part to its main part. Since the column store is read optimized and compressed, deltas are introduced to optimize insert or updates in the optimized way. All insertions are passed to the delta part. At a certain point in time, the delta can be merged into the main column table. Delta will be merged into main column table. HISTORY - Can be specified to merge the history delta part into the history main part on column store temporal tables. The table is a temporal table with additional table history. PART - Can be specified to merge the delta of a specific table partition to its main part . The t able was partitioned. Example

MERGE DELTA OF A; Merges the column store table delta part to its main part. MERGE DELTA OF A WITH PARAMETERS('SMART_MERGE' = 'ON'); Smart merges the column store table delta part to its main part.

103

2012-08-21


MERGE DELTA OF A WITH PARAMETERS('SMART_MERGE' = 'ON', 'MEMORY_MERGE' = 'ON'); Smart merges the column store table delta part to its main part non-persistent, in memory only. MERGE DELTA OF A PART 1; Merge the delta of partition no. 1 of table with name "A" to main part of partion n o. 1. MERGE HISTORY DELTA OF A; Merge the history delta part of table with name "A" into its history main part. MERGE HISTORY DELTA OF A PART 1; Merges the column store table delta part of the history of table with name "A" to i ts history main part.

REPLACE | UPSERT Syntax

UPSERT [ ion> | WITH PRIMARY KEY REPLACE [ ion> | WITH PRIMARY KEY

] { [ WHERE } ] { [ WHERE }

Syntax Elements

::= [ . ] ::= ::= ( , ... ) ::=

For descriptions on identifier, please refer to Identifiers. ::= VALUES ( , ... )

For descriptions on expressions, please refer to Expressions . ::= | | | |

OR AND NOT ( )


Descriptions to detailed syntax elements of this SQL Command. The UPSERT or REPLACE statement without a subquery is similar to the UPDATE statement. The only difference is when the WHERE clause condition is false, it adds a new record to the ta ble like the INSERT statement. In case of a table which has a PRI MARY KEY, the PRIMARY KEY column must be included in the column list. Columns defined with NOT NULL without default specification have to be included in the column list as well. Other columns are filled with default value or NULL if not specified. The UPSERT or REPLACE statement with a subquery works like the INSERT statement, except that if an old row in the table has the same value as a new row for a PRI MARY KEY, then the old row is changed by values of the returned record from a subquery. Unless the table has a PRIMARY KEY, it becomes equivalent t o INSERT because there is no index to be used to determine whether or not a new row duplicates another. The UPSERT or REPLACE statement with a 'WITH PRIMARY KEY' is same as one with the subquery. It works based on the PRIMARY KEY. Example

CREATE TABLE T (KEY INT PRIMARY KEY, VAL INT);

You can insert a new value.

104

2012-08-21


UPSERT T VALUES (1, 1); KE Y V AL 1

1

If the condition in WHERE clause is false, a new value will be inserted. UPSERT T VALUES (2, 2) WHERE KEY = 2; KE Y V AL 1

1

2

2

And you can update the first record of the column "VAL". UPSERT T VALUES (1, 9) WHERE KEY = 1; KE Y V AL 1

9

2

2

Or you can use the "WITH PRI MARY KEY" keyword. UPSERT T VALUES (1, 8) WITH PRIMARY KEY; KE Y V AL 1

8

2

2

You can also insert values with a subquery. UPSERT T SELECT KEY + 2, VAL FROM T; KE Y V AL 1

8

2

2

3

8

4

2

SELECT Syntax

::= [ | ] | ( ) [ | ] ::= [] [] [] [{ , ... }] [] [] Syntax Elements SELECT clause

The select clause specifies an output to be returned either to users or to an outer select clause if exists. ::= SELECT [TOP ] [ ALL | DISTINCT ] ::= [, ...] ::= [.] | [ AS ] ::= [.] ::= ::= ::= ::= *

105

2012-08-21


TOP n TOP can be used to return the first n records from the SQL statement. DISTINCT and ALL DISTINCT can be used to return only one copy of each set of duplicate records selected. ALL can be used to return all rows selected, including all c opies of duplicates. The default is ALL. select_list The select list allows users to specify columns they want to retrieve from tables. asterisk Asterisk (*) can be used to select all columns from all tables or views listed in the FROM clause. If a schema name and a table name or a table name is provided with asterisk(*), it is used to limit the scope of the result set to t he specified table. column_alias A column alias can be used to represent an expression simply. FROM clause

The from clause specifies inputs such as tables, views, and subqueries to be used in the select statement. ::= FROM {, ... }
::= [ [AS] ] | [ [AS] ] | ::= ::=
[] JOIN
ON |
CROSS JOIN
| ::= INNER | { LEFT | RIGHT | FULL } [OUTER]

table alias A table alias can be used to represent a table or a subquery simply. The join_type specifies the type of join being performed. LEFT indicates a left outer join, RIGHT indicates a right outer join, and FULL indicates a full outer join. OUTER may or may not be used when an outer join is being performed. ON The ON clause specifies a join predicate. CROSS JOIN CROSS indicates that a cross join is being performed. A cross join produces the cross-product of two tables. WHERE clause

The WHERE clause is used to specify predicates on inputs in the FROM clause, so users can retrieve the desired records. ::= WHERE ::= OR | AND | NOT | ( ) | ::= | | | | | ::= { = | != | <> | > | < | >= | <= } [ ANY | SOME | ALL ] ({ | }) ::= [NOT] BETWEEN AND ::= [NOT] IN ( { |
106

2012-08-21


y> } ) ::= [NOT] EXISTS ( ) ::= [NOT] LIKE [ESCAPE ] ::= IS [NOT] NULL ::= {, ... } GROUP BY clause

::= GROUP BY { | } ::= { , ... } ::= { GROUPING SETS | ROLLUP | CUBE } [BEST ] [LIMIT [OFFSET ] ] [WITH SUBTOTAL] [WITH BALANCE] [WITH TOTAL] [TEXT_FILTER [FILL UP [SORT MATCHES TO TOP]]] [STRUCTURED RESULT [WITH OVERVIEW] [PREFIX ] | MULTIPLE R ESULTSETS] ( ) ::= { , ... } ::= | ( , ... ) | ( ( , ... ) )

The GROUP BY clause is used to group the selected rows based on the values in the specified columns. GROUPING SETS Generates results with specified multiple groupings of data in a single statement. If no additional options such as best and limit are set, the result is the same as UNION ALL of the aggreg ation of each specified group. For example, "select col1, col2, col3, count(*) from t group by grouping sets ( (col1, col2), (col1, col3) )" is equivalent to "select c ol1, col2, NULL, count(*) from t g roup by col1, col2 union all select col1, NULL, col3, count(*) from t group by col1, col3". In the grouping-sets query each of (col1, col2) and (col1, col3) specifies the gr ouping. ROLLUP Generates results with multiple levels of agg regation in a single statement. For example, " rollup (col1, col2, col3)" is equivalent to "g rouping sets ( (col1, col2, col3), (col1, col2), (col1) )" with a n additional aggregat ion without grouping. Thus, the number of grouping that result set contains is the number of columns in ROLLUP list plus one for last aggregation if there is no additional option. CUBE Generates results with multiple levels of agg regations in a single statement. For example, " cube (col1, col2, col3)" is equivalent to "g rouping sets ( (col1, col2, col3), (col1, col2), (col1, col3), (col2, col3), (col1), (col2), (col3) )" with an additional aggrega tion without grouping. Thus, the number of grouping that result set contains is the same as all possible permutations of columns in the CUBE list plus one for the last aggregation if there is no additional option. BEST n Returns only the top-n grouping sets sorted in descending order of the number of rows aggregated in each grouping set. n can be any of zero, positive, and negative. When n is zero, it is the same with no BEST option. When n is negative, it means sorting in ascending order. LIMIT n1 [OFFSET n2] Returns the first n1 grouped records after skipping n2 ones for each grouping set. WITH SUBTOTAL Returns for each g rouping set a n additional subtotal of the returned results as controlled by OFFSET or LI MIT. Unless OFFSET and LIMIT is set, the value is the same as t he one for WITH TOTAL. WITH BALANCE Returns for each grouping set an additional aggrega ted value of the remaining values not returned as controlled by OFFSET or LIMI T. WITH TOTAL Returns for each grouping set an additional row that is the a ggrega ted total value. OFFSET and LI MIT options

107

2012-08-21


cannot change this value. TEXT_FILTER Performs text filtering or highlighting on the grouping columns with , which is a single-quoted string that follows the below syntax. ::= '[]{, ...}' ::= + | - | NOT ::= | ::= !! Unicode letters or digits ::= !! double-quoted string that does not contain double quotations inside ::= [] ::= + | - | NOT | AND | AND NOT | OR

A filter defined by is a token/phrase or tokens/phrases connected with logical operators such as AND, OR and NOT. A token matches a string that contains its corresponding word case-insensitively. For example, 'ab' matches 'ab cd' and 'cd Ab' but does not match 'abcd' A token can contain wildcard characters ' that mat ches any string and ' that mat ches any character. Inside phrase, however, ' and ' do not work as wildcard characters. With tokens and phrases logical operators AND, OR and NOT may be used together. Since OR is the default operator, 'ab cd' is the same as 'ab OR cd' Note that logical operators should be written in uppercase characters. As a kind of logical operators, prefixes '+' and '-' mean inclusion (AND) and exclusion (AND NOT), respectively. For example, 'ab -cd' is the same as 'ab AND NOT cd' I f there is no FILL UP option, only grouped records that have mat ching values are returned. Note that a filter is applied to only the first grouping column in each grouping set. FILL UP Returns not only matched grouped records, but also non-matched ones. Function text_filter is useful to identify which one is matched. Refer to 'Related Functions' below. SORT MATCHES TO TOP Returns matching values before non-matching ones for each grouping set. This option cannot be used with SUBTOTAL, BALANCE and TOTAL. STRUCTURED RESULT Returns results as temporary ta bles. For each grouping set a single temporary table is creat ed. If WITH OVERVIEW option is set, a n additional temporary table is created for the overview of grouping sets. The names of temporary tables are specified by PREFIX option. WITH OVERVIEW Returns the overview in a separate table additionally. PREFIX value Specifies a prefix for naming the temporary tables. It must start with " #", which means the temporary table. If omitted, t he default prefix is "#GN". Then, the concatenat ion of this prefix value and a nonnegative integer number is used as the name of temporary tables; for example, "#GN0", "#GN1" and "#GN2". Refer to 'Return Format' below. MULTIPLE RESULTSETS Returns results in multiple result sets. Related Functions grouping_id ( ) function returns an integer number to identify which grouping set each grouped record belongs to. text_filter ( ) function, which is used with TEXT_FILTER, FILL UP, a nd SORT MATCHES TO TOP, displays matching values or NULL. NULL is displayed for non-matching values when FILL UP option is specified. Return Format If neither STRUCTURED R ESULT nor MULTIPLE RESULTSETS is set, the unioned result of all grouping sets is returned, with NULL values filling up att ributes that ar e not included in a specific grouping set. With STRUCTURED RESULT, temporary tables are created a dditionally which can be queried using "SELECT * FROM
" in the same session. The name of the tables follows the form 0: this table will contain t he overview if WITH OVERVIEW is specified n: n-th grouping set subject t o re-ordering by the BEST parameter With MULTIPLE RE SULTSETS, multiple result sets a re returned. Grouped records for each grouping set are in a single result set. HAVING clause

The HAVING clause is used to select the specified groups that satisfy the predicates. If t his clause is omitted, all groups are selected.

108

2012-08-21


::= HAVING SET OPERATORS

The set operators enable more than one select statements to be combined and only one result set is returned. ::= UNION [ ALL | DISTINCT ] | INTERSECT [DISTINCT] | EXCEPT [DISTIN CT]

UNION ALL Selects all records from all select stat ements. Duplicates are not removed. UNION [DISTINCT] Selects all unique records from all select statements by removing duplicates found from different select statements. UNION is the same as UNION DISTINCT. INTERSECT [DISTINCT] Returns all unique records that exist in all select statements in common. EXCEPT [DISTINCT] Returns all unique records from the first select statements after removing the duplicates in the following select statements. ORDER BY clause

::= ORDER BY { , ... } ::= [ ASC | DESC ] | [ ASC | DESC ::=

The ORDER BY clause is used to sort records by expressions or positions. A position means the index of the select list. For "select col1, col2 from t order by 2", 2 indicates col2 as the second expression used in the select list. ASC is used to sort records in ascending order and DESC is used to sort records in descending order. The default value is ASC. LIMIT

LIMIT keyword specifies the number of output records. ::= LIMIT [ OFFSET ]

LIMIT n1 [OFFSET n2] Returns the first n1 records after skipping n2 ones. FOR UPDATE

FOR UPDATE keywords lock the selected records so that other users cannot lock or change the records until end of this tra nsaction. ::= FOR UPDATE TIME TRAVEL

Keywords related wtih time travel can be used for statement-level time tra vel to go back to t he snapshot specified by commit_id or utctimestamp. ::= AS OF { { COMMIT ID } | { UTCTIMESTAMP } }

Time travel is possible only with a history column table. can be obtained from m_history_index_last_commit_id after each commit a nd its related can be obtained from sys.m_transaction_history. Here is an example. create history column table x ( a int, b int ); // after turnning off auto commit insert into x values (1,1); commit; select last_commit_id from m_history_index_last_commit_id where session_id = curren t_connection; // e.g., 10 insert into x values (2,2);

109

2012-08-21


commit; select last_commit_id t_connection; // e.g., delete from x; commit; select last_commit_id t_connection; // e.g.,

from m_history_index_last_commit_id where session_id = curren 20

from m_history_index_last_commit_id where session_id = curren 30

select * from x as of commit id 30; // return nothing select * from x as of commit id 20; // return two records (1,1) and (2,2) select * from x as of commit id 10; // return one record (1,1) select 2-01-01 select 2-01-01 select 2-01-01

commit_time from sys.transaction_history where commit_id = 10; // e.g., '201 01:11:11' commit_time from sys.transaction_history where commit_id = 20; // e.g., '201 02:22:22' commit_time from sys.transaction_history where commit_id = 30; // e.g., '201 03:33:33'

select * from x as of utctimestamp '2012-01-02 02:00:00'; // return one record (1,1 ) select * from x as of utctimestamp '2012-01-03 03:00:00'; // return two records (1, 1) and (2,2) select * from x as of utctimestamp '2012-01-04 04:00:00'; // return nothing Example

Here is t1 table. drop table t1; create column table t1 ( varchar(5), sales int ); insert into t1 values(1, insert into t1 values(2, insert into t1 values(3, insert into t1 values(4, insert into t1 values(5, insert into t1 values(6, insert into t1 values(7, insert into t1 values(8,

id int primary key, customer varchar(5), year int, product 'C1', 'C1', 'C1', 'C1', 'C2', 'C2', 'C2', 'C2',

2009, 2009, 2010, 2010, 2009, 2009, 2010, 2010,

'P1', 'P2', 'P1', 'P2', 'P1', 'P2', 'P1', 'P2',

100); 200); 50); 150); 200); 300); 100); 150);

The following GROUPING SETS query is equivalent to the second below group-by query. Note that two groups inside grouping sets in the first query are specified at each group by in the second query. select customer, year, product, sum(sales) from t1 group by GROUPING SETS ( (customer, year), (customer, product) ); select customer, year, from t1 group by customer, union all select customer, NULL, from t1 group by customer,

NULL, sum(sales) year product, sum(sales) product;

ROLLUP and CUBE are concise representations of grouping sets that are used frequently. The following ROLLUP query is equivalent to the second below grouping-set query. select customer, year, sum(sales) from t1 group by ROLLUP(customer, year); select customer, year, sum(sales) from t1 group by grouping sets ( (customer, year), (customer) ) union all select NULL, NULL, sum(sales)

110

2012-08-21


from t1;

The following CUBE query is equivalent to the second below grouping-set query. select customer, year, sum(sales) from t1 group by CUBE(customer, year); select customer, year, sum(sales) from t1 group by grouping sets ( (customer, year), (customer), (year) ) union all select NULL, NULL, sum(sales) from t1;

BEST 1 specifies that the following query returns only top-1 best group. In this example, 4 records exist for (customer, year) group and 2 records exist for (product) group, so the former 4 records are returned. For 'BEST -1' instead of 'BEST 1', the lat ter 2 records are ret urned. select customer, year, product, sum(sales) from t1 group by grouping sets BEST 1 ( (customer, year), (product) );

LIMIT 2 limits the number of records to maximum 2 for each group. For (customer, year) group, t he number of its records are 4, then only first 2 records will be returned. For (product) group, the number of its records are 2, so all the records will be returned. select customer, year, product, sum(sales) from t1 group by grouping sets LIMIT 2 ( (customer, year), (product) );

WITH SUBTOTAL produces additional one record for each group to display subtotal of returned records. These subtotal records get NULL for each of customer, year, product columns and the sum of sum(sales) values in the select list. select customer, year, product, sum(sales) from t1 group by grouping sets LIMIT 2 WITH SUBTOTAL ( (customer, year), (product) );

WITH BALNACE produces additional one record for each group to display subtotal of unreturned records. select customer, year, product, sum(sales) from t1 group by grouping sets LIMIT 2 WITH BALANCE ( (customer, year), (product) );

WITH TOTAL produces additional one record for each group to display total of all grouped records without regard that the records are returned or not. select customer, year, product, sum(sales) from t1 group by grouping sets LIMIT 2 WITH TOTAL ( (customer, year), (product)

111

2012-08-21


);

TEXT_FILTER a llows users to ret rieve the first column of each group with a given . The following query will search columns ending with '2': customers for the first grouping set and products for the second one. Only matched three records will be returned. TEXT_FILTER function in the select list is useful to see which values are matc hed. select customer, year, product, sum(sales), text_filter(customer), text_filter(prod uct) from t1 group by grouping sets TEXT_FILTER '*2' ( (customer, year), (product) );

FILL UP is used to return both matched and non-matched records with . Therefore, the following query returns six records whereas the previous query does three ones. select customer, year, product, sum(sales), text_filter(customer), text_filter(prod uct) from t1 group by grouping sets TEXT_FILTER '*2' FILL UP ( (customer, year), (product) );

SORT MATCHES TO TOP is used to raise matched records up. For each grouping set, its grouped records will be sorted. select customer, year, product, sum(sales), text_filter(customer), text_filter(prod uct) from t1 group by grouping sets TEXT_FILTER '*2' FILL UP SORT MATCHES TO TOP ( (customer, year), (product) );

STRUCTURED RESULT creates temporary tables: one for ea ch grouping set and one more for t he overview table optionally. "#GN1" t able is for (customer, year) g rouping set a nd "#GN2" t able is for (product) one. Note that ea ch table contains only related columns. That is, " #GN1" table does not have "product" column and "#GN2" table does not have "customer" and "year" columns. select customer, year, product, sum(sales) from t1 group by grouping sets STRUCTURED RESULT ( (customer, year), (product) ); select * from "#GN1"; select * from "#GN2";

WITH OVERVIEW creates a temporary table "#GN0" for the overview table. drop table "#G1"; drop table "#G2"; select customer, year, product, sum(sales) from t1 group by grouping sets structured result WITH OVERVIEW ( (customer, year), (product) ); select * from "#GN0"; select * from "#GN1"; select * from "#GN2";

Users can change the names of temporary tables by using PREFIX keyword. Note that the names still must start with '#', which is the prefix of temporary tables.

112

2012-08-21


select customer, year, product, sum(sales) from t1 group by grouping sets STRUCTURED RESULT WITH OVERVIEW PREFIX '#MYTAB' ( (customer, year), (product) ); select * from "#MYTAB0"; select * from "#MYTAB1"; select * from "#MYTAB2";

Temporary tables are dropped when the corresponding session is closed or when a user executes a drop command. A list of temporary tables a re seen in m_temporary_tables. select * from m_temporary_tables;

MULTIPLE R ESULTSETS returns resultsets as multiple resultsets. In SAP HANA Studio, the following query will return three resultsets: one is for the overview table and t wo are for grouping sets. select customer, year, product, sum(sales) from t1 group by grouping sets MULTIPLE RESULTSETS ( (customer, year), (product) );

UNLOAD Syntax

UNLOAD Syntax Elements

::=

The name of the table to be unloaded from memory Description

The UNLOAD statement unloads the column store table from memory. This can be done to free up memory. The table will be loaded again on next access. Example

In the example below the table a_table is to be unloaded from memory UNLOAD a_table;

The load status of a table can be querieed by select loaded from m_cs_tables where table name = ''

UPDATE Syntax

UPDATE [ ] [ WHERE ] Syntax Elements

::= [.] ::= ::= [AS]

For descriptions on identifier, please refer to Identifiers.

113

2012-08-21


::= SET { = },...

For descriptions on expressions, please refer to Expressions . ::= | | | |

OR AND NOT ( )


The UPDATE statement changes the values of the records of a ta ble where the condition is met. If the WHERE clause condition is true, the result of expression is assigned to that column. If the WHERE clause is omitted, then it updates all records of a ta ble. Example

CREATE TABLE T (KEY INT PRIMARY KEY, VAL INT); INSERT INTO T VALUES (1, 1); INSERT INTO T VALUES (2, 2);

If t he condition in WHERE clause is true, t he record will be updated. UPDATE T SET VAL = VAL + 1 WHERE KEY = 1; KE Y V AL 1

2

2

2

If the WHERE clause is omitted, all records will be updated. UPDATE T SET VAL = KEY + 10; KE Y V AL 1

11

2

12

System Management Statements SET SYSTEM LICENSE Syntax

SET SYSTEM LICENSE '' Description

Install a license key to the database instance. The license key (="">) is to be copied and pasted from the license key file. The system privilege LICENSE ADMIN is required to execute this command. Example

SET SYSTEM LICENSE '----- Begin SAP License ----SAPSYSTEM=HD1 HARDWARE-KEY=K4150485960 INSTNO=0110008649 BEGIN=20110809 EXPIRATION=20151231 LKEY=... SWPRODUCTNAME=SAP-HANA SWPRODUCTLIMIT=2147483647 SYSTEM-NR=00000000031047460'

ALTER SYSTEM ALTER CONFIGURATION Syntax

114

2012-08-21


ALTER CONFIGURATION (, [, ]) SET | UNSET [ WITH RECONFIGURE] Syntax Elements

::=

The filename is 'indexserver.ini' in the case of row-store engine configuration. The filename used must be one of the ini files located on the 'DEFAULT' layer. If the file selected by filename does not exist on the required layer, the file will be created in t he case of a SET command. ::=

Sets the target layer for the configuration change. This parameter can be either 'SYSTEM' or 'HOST'. The SYSTEM layer is the recommended layer for customer settings. The HOST layer should generally only be used for minor configuration, for example parameters contained in daemon.ini. ::=

If the layer parameter above is set to 'HOST', layer_name is used to target either a tenant name or a targ et host name. For example, 'selxeon12' would target the 'selxeon12' host. SET The SET command updates the value of a key if the key already exists, or inserts it if required. UNSET The UNSET command removes a key and its associated value. ::= {(,) = },...

Specifies the section, key and value of the ini file parameter to be altered as follows: ::=

The section name of the parameter to be altered. ::=

The name of the parameter to be a ltered. ::=

The value of the parameter to be a ltered. WITH RECONFIGURE When WITH RECONFIGURE is specified the configuration changes will be directly applied to the running SAP HANA Database instance. When WITH RECONFIGURE is not specified the new configuration will be written to the ini file, however the new value is not applied to the current running system and will only be applied at the next startup of the database. This means that there can be inconsistencies between the ini file contents and t he actual configuration value that the SAP HANA D atabase is currently using. Description

Sets or removes configuration parameters in an ini file. Ini file configuration is used for the layered configuration for DEFAULT, SYSTEM, HOST layers. Note:

The DEFAULT layer configuration cannot be changed or removed using this command. The following is an example of ini file locations: DEFAULT: /usr/sap//HDB/exe/config/indexserver.ini SYSTEM: /usr/sap//SYS/global/hdb/custom/config/indexserver.ini HOST: /usr/sap//HDB//indexserver.ini The priority of the configuration layers is as follows: DEFAULT < SYSTEM < HOST.

115

2012-08-21


This means that the layer that has the highest priority is the HOST layer, followed by the SYSTEM layer and finally the DEFAULT layer. The configuration with the highest priority will be applied to the running environment. If the highest priority level configuration is removed, then the configuration with the next highest priority will be applied. System and Monitoring Views

Currently available ini files are listed in t he M_INIFILES system table and the current configuration is available in M_INIFILE _CONTENTS system table. Example

Example syntax to change system layer configuration is as follows: ALTER SYSTEM ALTER CONFIGURATION ('filename', 'layer') SET ('section1', 'key1') = ' value1', ('section2', 'key2') = 'value2', ... [WITH RECONFIGURE]; ALTER SYSTEM ALTER CONFIGURATION ('filename', 'layer', 'layer_name' ) UNSET ('secti on1', 'key1'), ('section2'), ...[WITH RECONFIGURE];

ALTER SYSTEM ALTER SESSION SET Syntax

ALTER SYSTEM ALTER SESSION SET = Syntax Elements

::=

The session ID of the session where the variable should be set. ::=

The key of a session variable. The maximum length of key is 32 characters. ::=

The desired value of a session variable. The maximum length of value is 512 characters. Description

With this command you can set session variables of database sessions. Note:

There are several read-only session variables that you cannot change with this command: APPLICATION, APPLICATIONUSER, TRACEPROFILE. Session variables can be retrieved using SESSION_CONTEXT function and unset using the ALTER SYSTEM ALTER SESSION UNSET command. Example

In the example below you set the variable 'MY_VAR' to 'dummy' in session 200006. ALTER SYSTEM ALTER SESSION 200006 SET 'MY_VAR'= 'dummy';

ALTER SYSTEM ALTER SESSION UNSET Syntax

ALTER SYSTEM ALTER SESSION UNSET Syntax Elements

::=

The session ID of the session where the variable should be unset.

116

2012-08-21


::=

The key of a session variable. The maximum length of key is 32 characters. Description

With this command you can unset session variables of database sessions. Note:

There are several read-only session variables that you cannot change with this command: APPLICATION, APPLICATIONUSER, TRACEPROFILE. Session variables can be retrieved using SESSION_CONTEXT function. Example

Getting the session variables of the current session SELECT * FROM M_SESSION_CONTEXT WHERE CONNECTION_ID = CURRENT_CONNECTION

Removing a session variable from the specified session ALTER SYSTEM ALTER SESSION 200001 UNSET 'MY_VAR';

ALTER SYSTEM CANCEL [WORK I N] SESS ION Syntax

ALTER SYSTEM CANCEL [WORK IN] SESSION Syntax Elements

::=

The session ID of the required session. Description

Cancels the currently executing statement by specifying session ID. The cancelled session will be rolled back after cancelling, t he executing statement will return the error code 139 (current operation cancelled by request a nd transaction rolled back). Example

You use the following query to obtain the current connection IDs and the statements which they are executing. SELECT C.CONNECTION_ID, PS.STATEMENT_STRING FROM M_CONNECTIONS C JOIN M_PREPARED_STATEMENTS PS ON C.CONNECTION_ID = PS.CONNECTION_ID AND C.CURRENT_STATEMENT_ID = PS.STATEM ENT_ID WHERE C.CONNECTION_STATUS = 'RUNNING' AND C.CONNECTION_TYPE = 'Remote'

Using the connection ID information you obtained using the query above, you can now cancel a running query as shown below. ALTER SYSTEM CANCEL SESSION '200001'

ALTER SYSTEM CLEAR SQL PLAN CACHE Syntax

ALTER SYSTEM CLEAR SQL PLAN CACHE Description

117

2012-08-21


The SQL PLAN CACHE stores plans generated by previous SQL statement executions. The plan cache is used by the SAP HANA Da tabase to speed up query exectution if the same SQL stat ement is executed again.The plan cache also collects some statistics regarding plan preparation and execution. You can find details about the contents of the SQL plan cache from the following monitoring views: M_SQL_PLAN_CACHE, M_SQL_PLAN_CACHE_OVERVIEW

The ALTER SYSTEM CLEAR SQL PLAN CACHE statement removes all the SQL plans that are not currently executing from the plan cache. The command also removes all plans having reference count of '0' from the plan cache and resets all the stat istics of the remaining plans. La stly the command also reset the contents of M_SQL_PLAN_CACHE_OVERVIEW monitoring view. Example

ALTER SYSTEM CLEAR SQL PLAN CACHE

ALTER SYSTEM CLEAR TRACES Syntax

ALTER SYSTEM CLEAR TRACES () Syntax Elements

::= [,...]

You can clear multiple traces simultaneously by adding multiple trace_types in a comma delimited list. ::=

You can selectively clear specific trace files by setting the trac e_type to one of the following types:

Trace Files

ALERT

*a l e rt_*.trc

CLIENT

l oca l cl i ent_*.trc

CRASHDUMP

*.cra s hdump.*

EMERGENCYDUMP

*.emergencydump.*

*

a l l *.trc fi l e s of s e rvi ces l i s ted bel ow

INDEXSERVER,NAMESERVER,...,DAEMON

open *.trc files of a single service type

Description

You use the ALTER SYSTEM CLEAR TRACES to clear trace contents from trace files. When you use this command all trace files that were opened by the SAP HANA Database will be removed or cleared. On distributed systems, this command will clear all trace files on all hosts. Use this command to reduce disk space used by large trac efiles, e. g. when trace components are set to INFO or DEBUG. You can monitor trace files and trace file contents using the M_TR ACEFILES, M_TRACEFILE_CONTENTS system tables respectively. Examples

To clear t he alert t race file you use the following command: ALTER SYSTEM CLEAR TRACES('ALERT');

To clear t he alert a nd client trace files you use the following command: ALTER SYSTEM CLEAR TRACES('ALERT', 'CLIENT');

ALTER SYSTEM DISCONNECT SESSION Syntax

118

2012-08-21


ALTER SYSTEM DISCONNECT SESSION Syntax Elements

::=

The session ID of the session to be disconnected. Description

You use ALTER SYSTEM DISCONNECT SESSION to disconnect a specified session from the database. Before disconnection any currently running operations associated with the session will be terminated. Example

You use the command below to obtain the session IDs of idle sessions. SELECT FROM WHERE ORDER

CONNECTION_ID, IDLE_TIME M_CONNECTIONS CONNECTION_STATUS = 'IDLE' AND CONNECTION_TYPE = 'Remote' BY IDLE_TIME DESC

You use the command below to disconnect a session. ALTER SYSTEM DISCONNECT SESSION '200001'

ALTER SYSTEM LOGGING Syntax

ALTER SYSTEM LOGGING Syntax Elements

::= ON | OFF Description

Logging is enabled or disabled. While logging is disabled, no log entries are persisted. Only the data area will be writtten, when a savepoint is done. This can cause loss of committed transactions, when the indexserver terminated in the middle of a load. In case of termination, you have to truncate and insert all data again. After enable logging you have to perform a savepoint to be sure that all data is persisted and you have to perfrom a data backup, else you can not recover this data. Only use this command for initial load ! You can also do this for single column tables with ALTER TABL E .. . ENABLE/DI SABLE DELTA LOG ALTER SYST EM RECLAIM DATAVOLUME Syntax

ALTER SYSTEM RECLAIM DATA VOLUME [SPACE] [] Syntax Elements

::= 'host_name:port_number'

Specifies the server on which the size of the persistency should be reduced. ::=

Specifies to which percentage of the overload size the data volume should be reduced.

119

2012-08-21


::= DEFRAGMENT | SPARSIFY

Specifies the strategy t o reduce the persistency size. DEFRAGEMENT is the default. Please note that SPARSIFY is not supported yet and reserved for future use. Description

The command should be used when unused space inside the persistencies should be freed. It reduces data volume size to a N% of overload size; it works like defragmenting a hard disk, pages scattered around the data volume will be moved to the front of the data volume and the free space at the end of the data volume will be truncated. If is omitted the statement is distributed to all servers with persistence. Example

In the example below the persistencies of all servers in the landscape are defragmented and reduced to 120% of the overload size. ALTER SYSTEM RECLAIM DATAVOLUME 120 DEFRAGMENT

ALTER SYSTEM RECLAIM LOG Syntax

ALTER SYSTEM RECLAIM LOG Description

You use this command when the data base has accumulated a lot of log segments and you want to reclaim disk space of currently unused log segments. Log segment accumulation can be caused in several ways. For example, when the a utomatic log backup is not operational for a long period or the log savepoint is blocked for an extended time. When such an issue occurs you use the ALTER SYSTEM R ECLAIM LOG command only after the root cause of the log accumulation has been fixed. Example

You reclaim disk space of currently unused log segments using the following command: ALTER SYSTEM RECLAIM LOG

ALTER SYSTEM RECLAIM VERSION SPACE Syntax

ALTER SYSTEM RECLAIM VERSION SPACE Description

Performs MVCC version garbage collection for resource reuse. Example

ALTER SYSTEM RECONFIGURE SERVICE Syntax

ALTER SYSTEM RECONFIGURE SERVICE (,,) Syntax Element

::=

The name of the service you wish to reconfigure. See M_SERVICE_TYPES monitoring view for a list of available service types.

120

2012-08-21


::= ::=

The host and port number where you would like to reconfigure a service. Description

You use ALTER SYSTEM R ECONFIGURE SERVICE to r econfigures a specified service by applying the current configuration parameters. This command is used after c hanging multiple configuration parameters with ALTER CONFIGURATION without RECONFIGURE option. See ALTER SYSTEM ALTER CONFIGURATION To reconfigure a specific service specify and and leave empty. To reconfigure all services of a type, specify and leave and empty. To reconfigure all services, leave all para meters empty. Example

You use the following command to reconfigure all services on the ld8520.sap.com host using port number 30303: ALTER SYSTEM RECONFIGURE SERVICE ('','ld8520.sap.com',30303)

You use the following command to reconfigure all services of type indexserver: ALTER SYSTEM RECONFIGURE SERVICE ('indexserver','',0) See Also

ALTER SYSTEM ALTER CONFIGURATION ALTER SYSTEM REMOVE TRACES Syntax

ALTER SYSTEM REMOVE TRACES (, ) ::= ,... Syntax Elements

:==

The name of the host where the traces are to be removed. ::= [,..]

You can remove multiple traces simultaneously by adding multiple trace_file entries in a comma delimited list. :== see table below.

You set the trace_file parameter to one of the file specifications shown in the table below. Trace Type

ALERT

*a l e rt_*.trc

CLIENT

l oca l cl i ent_*.trc

CRASHDUMP

*.cra s hdump.*

EMERGENCYDUMP

*.emergencydump.*

*

a l l *.trc fi l e s of s e rvi ces l i s ted bel ow

INDEXSERVER,NAMESERVER,...,DAEMON

open *.trc files of a single service type

Description

121

2012-08-21


You use this command to Delete the trace files on a specified host to reduce disk space used by large trace files. When a service has a trace file open it cannot be deleted. I n this case you can clear the t race file using the ALTER SYSTEM CLEAR TRACES command. Example

You use the command below to delete all of the ALERT traces from the lu873.sap.com host. ALTER SYSTEM REMOVE TRACES ('lu873.sap.com', '*alert_*.trc'); See Also

ALTER SYSTEM CLEAR TRACES ALTER SYSTEM RESET MONITORING VIEW Syntax

ALTER SYSTEM RESET MONITORING VIEW Syntax Elements

::=

The name of the resettable monitoring view to be reset. Note:

Not all monitoring views can be reset using this command. You can identify a view as being able to be reset as its view name will have the suffix "_RESET". Description

You use this command to reset statistics data for the specified monitoring view. You use this command to define a starting point for your measurements. First, you reset the monitoring view, then execute an action. After the action is completed, query "_RESET" version of the monitor view to get t he statistical information gathered since the last reset. Example

In the example below you reset the " SYS"."M_HEAP_MEMORY_RESET" monitoring view. ALTER SYSTEM RESET MONITORING VIEW "SYS"."M_HEAP_MEMORY_RESET"

ALTER SYSTEM SAVE PERFTRACE Syntax

ALTER SYSTEM SAVE PERFTRACE [INTO FILE ] Syntax Elements

::=

The file where raw performance data will be saved. Description

You use this command to collect raw performance trac e data from .prf files and save the information into a single .tpt file. The . tpt file will be saved in the trace directory of your SAP HANA Database instance. If you do not specify a file name then 'perftrace.t pt' will be used. The performance trace data (.tpt) file can be downloaded from 'SAP HANA Computing Studio'->DiagnosisFiles and then the performance trace can be loaded and analyzed with HDBAdmin in any SAP HANA Database instance. Monitoring Vi ew

122

2012-08-21


The status of the performance tra ce can be monitored from M_PERFTRACE. Example

You save raw performance trac e data into the 'mytrace.tpt' file using t he following command: ALTER SYSTEM SAVE PERFTRACE INTO FILE 'mytrace.tpt'

ALTER SYSTEM SAVEPOINT Syntax

ALTER SYSTEM SAVEPOINT Description

Executes a savepoint on the persistence manager. A savepoint is a point in time when a complete consistent image of the database is persisted on the disk. The consistent image can be used to restart the database. Normally, the savepoint is executed periodically, as configured by parameter savepoint_interval_s in section [persistence]. For special (normally test) purposes, the savepoint may be disabled. In this case, you can use this command to manually execute a savepoint. ALTER SYSTEM START PERFTRACE Syntax

ALTER SYSTEM START PERFTRACE [] [] [PLAN_EXECUT ION] [FUNCTION_PROFILER] [DURATION ] Syntax Element

::=

Restrict perftrace collection to the given SQL user name ::=

Restrict perftrace collection to the given application user name. the a pplcation user ca n be defined with session variable APPLICATIONUSER PLAN_EXECUTION

Collect plan execution details FUNCTION_PROFILER

Collect function level details. ::=

After duration seconds the perftrace is automatically stopped. If not specified, perftrace will only stop with ALTER SYSTEM STOP PERFTRACE. Description

Start Performance Trace. With 'Explain Plan' or 'Visualize Plan' you can see statement execution on a logical level. With 'Perfomance Trace' the statement execution will be recorded on thread + function level. Only one perftrace ca n be active at one time. The status of performance trace can be monitored from M_PERFTRACE. Example

ALTER SYSTEM START PERFTRACE sql_user app_user PLAN_EXECUTION FUNCTION_PROFILER

123

2012-08-21


ALTER SYSTEM STOP PERFTRACE PERFTRACE Syntax

ALTER SYSTEM STOP PERFTRACE Description

Stop a previously previously started Performance Trace. After stop, t he performance performance trace data has t o be collected and saved with ALTER SYSTEM SAVE PERFTRACE. Example

ALTER SYSTEM STOP PERFTRACE

ALTER SYSTEM STOP SERVICE Syntax

ALTER SYSTEM STOP SERVICE

[IMMEDIATE [WITH COREFILE]]

Syntax Element

::= | ('' '',) ,)

Location of service to be stopped. IMMEDIATE

Immmediately stop (kill) service without waiting for regular shutdown. WITH COREFILE

Write a core file Description

Stop or kill a single or multiple services. The service will typically be restarted by the daemon. Required after changing a configuration parameter that can not be changed online. Example

ALTER SYSTEM STOP SERVICE 'ld8520:30303'

UNSET SYSTEM LICENSE ALL Syntax

UNSET SYSTEM LICENSE ALL Description

Deletes all currently installed license keys. After using this command the system will be locked down immediately and will require a new valid license key before it can be used further. The system privilege LICENSE ADMIN is required to execute this command. Example

UNSET SYSTEM LICENSE ALL

Session Management Statements CONNECT

12 4

2 0 12 -0 8 -2 1


Syntax

CONNECT Syntax Elements

::= PASSWORD | WITH SAML ASSERTION '' ::= ... Description

Connect to the database instance by specifying user_name and password or by specifying a SAML assertion. Example

CONNECT my_user PASSWORD myUserPass1 SET HISTORY SESSION Syntax

SET HISTORY SESSION TO Elements

: User should specify an exact moment when the session is to travel ::= NOW | COMMIT ID | UTCTIMESTAMP ::= ::= Description

SET HISTORY SESSSION makes current session to see a previous version of history tables. User may specify the version version in COMMIT ID or UTCTIMESTAMP format, or may get back to current version version by specify NOW. NOW. After SET HISTORY SESSION command command with a COMMIT ID or UTCTIMESTAMP is issued, issued, current session session sees an old version of history tables, and cannot write anything to the system. If NOW option is given, current session gets back to a normal session, and sees current version of history tables and be able to write to the system. This command only works for history tables, and visibility on normal tables is not affected. Example

SELECT CURRENT_UTCTIMESTAMP FROM SYS.DUMMY SELECT LAST_COMMIT_ID LAST_COMMIT_ID FROM M_HI STORY_INDEX_LAST_CO STORY_INDEX_LAST_COMMIT_ID MMIT_ID WHERE SESSION_ID = CURRENT_CONNECTION CURRENT_CONNECTION COMMIT SET HISTORY SESSION TO UTCTIMESTAMP '2012-03-09 '2012-03-09 07:01:41.428' SET HISTORY SESSION TO NOW SET SCHEMA Syntax

SET SCHEMA Syntax Elements

Schema_name schema name string Description

You can change the current schema of the session. The current schema is used when database object names

12 5

2 0 12 -0 8 -2 1


such as table names are not prefixed with the schema name. SET [SESSION] Syntax

SET [SESSION] = Syntax Elements

::=

The key of a session variable. The maximum length of key is 32 characters. ::=

The desired value of a session variable. The maximum length of value is 512 characters. Description

With this command you can set session variables of your own database session by providing key and value pairs. Note:

There are several available read-only session variables that you cannot change with this command: APPLICATION, APPLICATIONUSER, TRACEPROFILE. Session variables can be retrieved using SESSION_CONTEXT function and unset using the UNSET [SESSION] command. Example

SET 'MY_VAR' = 'dummy' 'dummy'; ; SELECT SESSION_CONTEXT('MY_VAR' SESSION_CONTEXT('MY_VAR') ) FROM dummy; UNSET 'MY_VAR';

UNSET [SESSION] Syntax

UNSET [SESSION] Syntax Elements

::=

The key of a session variable. The maximum length of key is 32 characters. Description

Using UNSET [SESSION] you can unset session variables of the current session. Note:

There are several read-only read-only session session variables and t hey are APPLICATION, APPLICATIONUSER, TRACEPROFILE. Example

SET 'MY_VAR' 'MY_VAR'= = 'dummy' 'dummy'; ; SELECT SESSION_CONTEXT('MY_VAR' SESSION_CONTEXT('MY_VAR') ) FROM dummy; UNSET 'MY_VAR';

Transaction Transaction Management Stateme nts

12 6

2 0 12 -0 8 -2 1


COMMIT Syntax

COMMIT Description

The system supports transactional consistency which guarantees current job to be either completely applied to the system or disposed. If a user wants to apply current job to the system persistently, the user should issue COMMIT command. If COMMIT command is issued and successfully processed, any change on the system which current transaction has done is applied to the system and the change will be visible to other jobs which will start in the future. The job which has already committed via COMMIT command cannot be reverted. In a distributed system, standard 2-phase-commit protocol is complied. In the first phase, transaction coordinator consults every participant whether if it is ready to commit, and sends the result t o the participants in the second phase. COMMIT command only works with 'autocommit' disabled session. Example

COMMIT

LOCK TABLE Syntax

LOCK TABLE IN EXCLUSIVE MODE [NOWAIT] Description

LOCK TABLE command explicitly tries to acquire an exclusive lock for a table. If NOWAIT option is specified, it just tries to acquire lock for the table. If it fails to acquire lock with NOWAIT option specified, an error code is returned, but current transaction is not rolled-back. Example

LOCK TABLE mytaable IN EXCLUSIVE MODE NOWAIT

ROLLBACK Syntax

ROLLBACK Description

The system supports transactional consistency which guarantees current job to be either completely applied to the system or disposed. In the middle of a transaction, it can be explicitly reverted as it has not been performed via ROLLBACK command. After ROLLBACK command is issued, any change on the system which current transaction has done is completely reverted and current session is to be in an idle state. ROLLBACK command only works with 'autocommit' disabled session. Example

ROLLBACK

SET TRANSACTION Syntax

SET TRANSACTION | Syntax Elements

isolation_level ::= ISOLATION LEVEL

127

2012-08-21


The isolation level sets the sta tement level read consistency of the data in the database. If isolation_level is ommited the default is READ COMMITTED level ::= READ COMMITTED | REPEATABLE READ | SERIALIZABLE

READ COMMITTED The READ COMMITTED isolation level provides statement level read consistency during a t ransaction. Each statement in a t ransaction sees commited state of the data in the database as t he execution of the statement begins. This means that each statement in t he same transaction may see varying snapshots of the data in the dat abase as they are executed as data ca n be commited during the transaction. REPEATABLE READ/SERIALIZABLE The REPEATABLE READ/SERIALI ZABLE isolation level provides transaction level snapshot isolation. All statements of a tra nsaction see the same snapshot of the database data. This snapshot contains all changes that were committed at the time the tra nsaction started along with the changes made by the transaction itself. transaction_access_mode ::= READ ONLY | READ WRITE

The SQL-transaction access mode controls if a transaction can modify data during execution. I f transaction_access_mode is ommited the default is READ ONLY. READ ONLY When READ ONLY a ccess mode is set, then only read operations with SELECT statements are allowed. An exception will be thrown if update or insert operations are attempted whilst in this mode. READ WRITE When READ WRITE a ccess mode is set, sta tements within a transaction can freely read or make changes to the database data as required. Description

The SAP HANA Database uses multi-version concurrency control (MVCC) to ensure consistent read operations. Concurrent read operations see a consistent view of the database data without blocking concurrent write operations. Updates are implemented by inserting new versions of data and not by overwriting existing records. The isolation level specified determines the lock operation type that will be used. The system supports both statement level snapshot isolation and transaction level snapshot isolation. For statement snapshot isolation use level READ COMMITTED. For transaction snapshot isolation use REPEATABLE READ or SERIALIZAB LE. During a transaction when rows are inserted, updated or deleted, the system sets exclusive locks on the affected rows for the duration of the transaction. The system also sets shared locks on the affected tables for the duration of the transaction. This guarantees that the table is not dropped or altered while rows of the table are being updated. The data base releases these locks at the end of the t ransaction. Note:

Reading a row does not set any locks on either tables or rows within the database regar dless of the isolation level used. Data Definition Language and Transaction Isolation

Data Definition Language (DDL) statements (CREATE TABLE, DROP TABLE, CREATE VIEW, etc) always take an instantaneous effect on following SQL statements regardless of the transaction isolation level being used. For an example of this behaviour please consider the following sequence: 1. A long running SERIALIZ ABLE isolation transaction begins operating on Table C. 2. From outside the transact ion some DDL is executed which adds a new column to Table C. 3. From within the SERIALIZABLE isolation transaction the new column will be accessible as soon as the DDL statement completes. This access occurs regardless of the isolation level of the transaction. Example

SET TRANSACTION READ COMMITTED;

Access Control Statements ALTER SAML PROVIDER

128

2012-08-21


ALTER SAML PROVIDER WITH SUBJECT ISSUER Syntax Elements

::= ::= ::= Description

The ALTER SAML PROVID ER stat ement changes the property of a SAML provider known to the SAP HANA database. has to be an existing SAML provider. Only database users having t he system privilege USER ADMI N are allowed to change a SAML provider. The and the are the corresponding names provided in the certificate of the SAML identity provider. System and Monitoring Views

SAML_PROVIDERS: shows all SAML providers with their subject name and issuer_name. Syntax

ALTER USER Syntax

ALTER USER Syntax Elements

::= ::= PASSWORD [] | | IDENTIFIED EXTERNALLY AS [] | RESET CONNECT ATTEMPTS | DROP CONNECT ATTEMPTS | DISABLE PASSWORD LIFETIME | FORCE PASSWORD CHANGE | DEACTIVATE [USER NOW] | ACTIVATE [USER NOW] | DISABLE | ENABLE | ADD IDENTITY ... | ADD IDENTITY FOR KERBEROS | DROP IDENTITY ... | DROP IDENTITY FOR KERBEROS ::= ... ::= [] | ::= SET PARAMETER CLIENT = ::= CLEAR PARAMETER CLIENT | CLEAR ALL PARAMETERS ::=

129

2012-08-21


| ::= PASSWORD | KERBEROS | SAML ::= FOR SAML PROVIDER | FOR KERBEROS ::= ANY | ::= ::= FOR SAML PROVIDER Description

The ALTER USER statement modifies the database user. must specify an existing database user. Each user can do the ALTER USER statement for his own. But not a ll of the s can be specified by the user himself. The ALTER USER statement for other users with all s can only be done by users having the system privilege USER ADMIN. Users created with PASSWORD cannot be changed to E XTERNALLY and vice versa: the users created with EXTERNALLY ca nnot be changed to PASSWORD. But their or t heir can be changed. You can change a user's password with this command. Password must follow the rules defined for the current database. The password rules include the minimal password length and the definition which of the character types ( lower, upper, digit, special characters ) have to be part of t he password. The password for a user has to be changed regularly according to the password policy specified for the database instance or changed by the user himself when first connecting to the database instance. You can change the external a uthentication. External users are authenticated using an external system, e.g. a Kerberos system. Such users do not have a password, but , e.g. a Kerberos principal name. For detailed information about external identities, contact your domain administrator. can be used to set, change or clean t he user parameter CLIENT. can be used to set the user parameter CLIENT for a user in the database. When using reports this user parameter CLIE NT can be used to restrict t he access rights of user to info concerning the specified client. The can not be specified by the user himself. If the number of MAXIMUM_INVALID_CONNECT_ATTEMPTS (see monitoring view M_PASSWORD_POLICY) is reached before a successful (correct user/password-combination) connect is done, then this user is locked for some minutes before being allowed to connect (even with correct user/password-combination) again. With the command ALTER USER RESET CONNECT ATTEMPTS a user with system privilege USER ADMI N can reset t he number of invalid attempts to 0 and therefore allow the user to connect immediately. Information on invalid connect attempts having happened can be checked in system view INVALID_CONNECT_ATTEMPTS. With the command ALTER USER DROP CONNECT ATTEMPTS a user with system privilege USER ADMIN or the user himself can delete the information of invalid connect attempts having happened. With the command ALTER USER DISABLE PASSWORD LIFETI ME a user with system privilege USER ADMIN can exclude user from all password-life-time-checks. This should be used only for technical users, not for normal database users. Please check the description of configuration parameters concerning the password policy. With the command ALTER USER FORCE PASSWORD CHANGE a user with system privilege USER ADMIN can force user to change his password immediately after t he next connect before being allowed to work any further. With the command ALTER USER DEACTIVATE USER NOW

130

2012-08-21


a user with system privilege USER ADMI N can deactivate / lock the user account of user . After the user account is deactivated / locked, the user cannot connect to the SAP HANA database. To re-activate / unlock user a user with system privilege USER ADMIN has either to use the command ALTER USER ACTIVATE USER NOW or, in case of a user with PASSWORD a uthentication mechanism, to reset the user's password with ALTER USER PASSWORD . With the command ALTER USER ACTIVATE USER NOW a user with system privilege USER ADMI N can re-activate / unlock the user account of user who had been deactivated before. Configuration Parameter

Conguration parameters concerning the password can be checked with the monitoring view M_PASSWORD_POLICY. These parameter are stored in indexserver.ini, section 'password policy'. The description of the parameters concerned can be found in SAP HANA Security Guide, Appendix, Password Policy Parameters. System and Monitoring Views

USERS: shows all users, their creat or, creat ion date and some info about their current stat es. USER_PARAMETERS: shows the defined user_parameters; currently only CLIENT is a vailable. INVALID_CONNECT_ATTEMPTS: shows how many invalid connect att empts were made for each user. LAST_USED_PASSWORDS: shows info about dates of last password-changes per user. M_PASSWORD_POLICY: shows configuration parameters describing the allowed layout of the password and its lifetimes.

Example

A user with user_name NEW_USER had been created before with the possibility to connect using the given password and with an assertion of t he existing SAML provider OUR_PROVIDER. The was set to ANY as the assertion will provide the database user name. This was done with the following statement: CREATE USER new_user PASSWORD Password1 WITH IDENTITY ANY FOR SAML PROVIDER OUR_PRO VIDER;

Now this user shall be forced to change his password. And to force him really hard, his possibility to connect using SAML is disabled. ALTER USER new_user FORCE PASSWORD CHANGE; ALTER USER new_user DISABLE SAML;

Assuming the user has tried t o connect with a wrong password too often, the administrator now wants to reset the number of invalid connect at tempts to zero. ALTER USER new_user RESET CONNECT ATTEMPTS;

The user new_user shall be allowed to use KERB EROS - mechanism to authenticate. Therefore the external identity for this connect mechanism has to be defined. Additionally the user has to be allowed to connect using KERBEROS as this is not done internally when the external identity is defined. ALTER USER new_user ADD IDENTITY 'testkerberosName' FOR KERBEROS; ALTER USER new_user ENABLE KERBEROS;

On the other hand the user new_user will loose the possibility to use an assertion of SAML provider OUR_PROVIDER. ALTER USER new_user DROP IDENTITY FOR SAML PROVIDER OUR_PROVIDER;

In the end the administrator wants to disallow any connection for this user new_user, for example because of suspicious work he did recently. ALTER USER new_user DEACTIVATE;

CREATE ROLE Syntax

131

2012-08-21


CREATE ROLE Syntax Elements

::= Description

The CREATE ROLE statement creates a new role. Only database users having the system privilege ROLE ADMI N are a llowed to create a r ole. The specified role name must not be identical to the name of an existing user or role. A role is a named collection of privileges and can be granted t o either a user or a role. If you want t o allow several database users to perform the same act ions, you can creat e a r ole, grant the needed privileges to this role, and grant the role to the different database users. Every user is allowed to grant privileges to an existing role, but only users having system privilege ROLE ADMIN a re allowed to grant roles to roles and users. There are 4 roles, which are delivered with the SAP HANA database: PUBLIC Every database user has been granted this role implicitly. This role contains read-only access to system views, monitoring views and execute privilege for some procedures. These privileges can not be revoked. This role can be gra nted further privileges which can be revoked afterwards. MODELING This role contains all privileges required for using the information modeler in the SAP HANA studio. CONTENT_ADMIN This role contains the same privileges as the MODELING role, but with the extension that this role is allowed to gra nt these privileges to other users. In addition, it contains the repository privileges to work with imported objects. MONITORING This role contains privileges for full read-only access to all meta data, the current system status in system and monitoring views, and the data of the statistics server. System and Monitoring Views

ROLES: shows all roles, their creator and creat ion date. GRANTED_ROLES: shows which roles are gra nted to which user or role. GRANTED_PRIVI LEGES: shows which privileges are granted to which user or role.

Example

A role with the name role_for_work_on_my_schema is created. CREATE ROLE role_for_work_on_my_schema;

CREATE SAML PROVIDER Syntax

CREATE SAML PROVIDER WITH SUBJECT ISSUER Syntax Elements

::= ::= ::=

132

2012-08-21


Description

The CREATE SAML PROVID ER sta tement defines a SAML provider known to the SAP HANA database. has to be different to any existing SAML provider. Only database users having the system privilege USER ADMIN are allowed to create a SAML provider. Each user having this privilege is allowed to drop any SAML provider. An existing SAML provider is needed to be able to specify SAML connection possibility for users. The and the are the X.500 distinguished names corresponding to the subject and issuer of the X.509 certificate used by the SAML identity provider to sign assertions. The syntax of these names can be found in ISO/IEC 9594-1. A detailed description of the concepts of SAML can be found in Oasis SAML 2.0. System and Monitoring Views

SAML_PROVIDERS: shows all SAML providers with their subject name and issuer name.

Example

A SAML provider with the name gm_saml_provider is created in the database specifying the subject and issuer to belong to some company. CREATE SAML PROVIDER gm_saml_provider WITH SUBJECT 'CN = wiki.detroit.generalmotors.corp,OU = GMNet,O = GeneralMotors, C = EN' ISSUER 'E = [email protected],CN = GMNetCA,OU = GMNet,O = GeneralMotors,C = EN';

CREATE USER Syntax

CREATE USER [PASSWORD ] [IDENTIFIED EXTERNALLY AS ] [WITH IDENTITY ...] [] Syntax Elements

::= ::= ... ::= | ::= FOR SAML PROVIDER | FOR KERBEROS ::= ANY | ::= ::= SET PARAMETER CLIENT = Description

The CREATE USER statement creates a new data base user. Only database users having the system privilege USER ADMIN are allowed to create another database user. The specified user name must not be identical to the name of an existing user, role, or schema. There are some users, which are delivered with the SAP HANA database: SYS, SYSTEM, _SYS_REPO, _SYS_STATISTICS.

133

2012-08-21


Users in the database ca n be authenticated by different mechanism, the internal authentication mechanism using password and by external mechanism, like Kerberos or SAML. One user can be authenticated by more than one mechanism at one time, but only one password and one external_identity can be valid at one time. In opposite to this, more than one can exist for one user at one time. At least one authentication mechanism has to be specified to allow the user to connect and work with the database instance. For compatibility reasons the syntax ID ENTIFIED EXTERNALLY AS will stay alive and do the same as the syntax FOR KERB EROS. Password must follow the rules defined for the current database. The password rules include the minimal password length and t he definition which of the charact er types ( lower, upper, digit, special chara cters ) have to be part of t he password. The password for a user has to be changed regularly according to t he password policy specified for the database instance. The password provided during CREATE USER command will be taken as provided, whereas the will be changed to upper_case as every . External users are a uthenticated using a n external system, e.g . a Kerberos system. Such users do not have a password, but , e.g. a Kerberos principal name. For detailed information about external identities, c ontact your domain administrator. If ANY is g iven as mapped user name, the SAML assertion will contain the name of the database user, the assertion is valid for. must specify an existing SAML provider. can be used to set the user parameter CLIENT when a user is created in the database. When using reports this user parameter CLIENT ca n be used to restrict the access rights of user to info concerning the specified client. For each database user a schema with the user's name is created implicitly. This can not be dropped explicitly. It will be dropped when the user is dropped. The database user owns that schema and uses it as his default schema, when he does not specify a schema name explicitly. Configuration Parameter

Conguration parameters concerning the password can be checked with the monitoring view M_PASSWORD_POLICY. These parameter are stored in indexserver.ini, section 'password policy'. The description of the parameters concerned can be found in SAP HANA Security Guide, Appendix, Password Policy Parameters. System and Monitoring Views

USERS: shows all users, their creat or, creat ion date and some info about their current stat es. USER_PARAMETERS: shows the defined user_parameters; currently only CLIENT is a vailable. INVALID_CONNECT_ATTEMPTS: shows how many invalid connect att empts were made for each user. LAST_USED_PASSWORDS: shows info about dates of last password-changes per user. M_PASSWORD_POLICY: shows configuration parameters describing the allowed layout of the password and its lifetimes. SAML_PROVIDERS: shows the existing SAML provider SAML_USER_MAPPING: shows the user's mapping names for each SAML provider Example

A user with user_name NEW_USER shall be created with the possibility to connect using the given password and with an assertion of the existing SAML provider OUR_PROVIDER. The is set to ANY as the assertion will provide the database user name. CREATE USER new_user PASSWORD Password1 WITH IDENTITY ANY FOR SAML PROVIDER OUR_PRO VIDER;

DROP ROLE Syntax

DROP ROLE Syntax Elements

::=

134

2012-08-21


Description

The DROP R OLE statement drops a role. must specify an existing role. Only database users having t he system privilege ROLE ADMIN a re allowed to drop a role. Each user having this privilege is allowed to drop any role. Only those roles which are delivered with the SAP HANA database can not be dropped: PUBLIC, CONTENT_ADMIN, MODELI NG and MONITORING. If a role was granted to a user or role, it is revoked when the role is dropped. R evoking a role may lead t o making some views inaccessible or making procedures not executable any more. This will occur if a view or procedures depends on any privilege that the role had. System and Monitoring Views

ROLES: shows all roles, their creator and creat ion date. GRANTED_ROLES: shows which roles are gra nted to which user or role. GRANTED_PRIVI LEGES: shows which privileges are granted to which user or role.

Example

A role with the name role_for_work_on_my_schema is created and dropped directly afterwards; CREATE ROLE role_for_work_on_my_schema; DROP ROLE role_for_work_on_my_schema;

DROP SAML PROVIDER DROP SAML PROVIDER Syntax Elements

::= Description

The DROP SAML PROVIDER statement drops the specified SAML provider. has to be an existing SAML provider. If the SAML provider specified is used by a SAP HANA database user, the SAML provider cannot be dropped. Only database users having the system privilege USER ADMIN are allowed to drop a SAML provider.

System and Monitoring Views

SAML_PROVIDERS: shows all SAML providers with their subject name and issuer_name. Syntax

DROP USER Syntax

DROP USER [] Syntax Elements

::= ::= CASCADE | RESTRICT

Default = RESTRICT Description

The DROP USER statement deletes a dat abase user. must specify an existing data base user.

135

2012-08-21


Only database users having the system privilege USER ADMIN are allowed to drop a data base user. Each user having this privilege is allowed to drop any user. Only those users which are delivered with the SAP HANA database can not be dropped: SYS, SYSTEM, _SYS_REPO, _SYS_STATISTICS. If the RESTRICT is specified implicitly or explicitly then the user will not be dropped in case he is the owner of any other object than the schema with his name and other schemas created by him or in case there is an object stored in one of his schemas which was not created by him. If the CASCADE is specified, the schema with the user's name and the schemas belonging to the user, together with all objects stored in them (even if they are created by other users), ar e deleted. Objects owned by the user, even if t hey are part of another schema, are deleted. Objects that ar e dependent on deleted objects are deleted. Even public synonyms owned by the deleted user are deleted. Privileges on deleted objects are revoked. Privileges granted by the deleted user are revoked. Revoke privileges may cause further revokes if they had been granted further. Users created by the deleted user and roles created by him are not deleted. Audit policies created by the deleted user are not deleted. It is possible to delete a user even if an open session of this user exists. System and Monitoring Views

The deleted user will be deleted in any of these views: USERS: shows all users, their creat or, creat ion date and some info about their current stat es. USER_PARAMETERS: shows the defined user_parameters; currently only CLIENT is a vailable. INVALID_CONNECT_ATTEMPTS: shows how many invalid connect att empts were made for each user. LAST_USED_PASSWORDS: shows info about dates of last password-changes per user. * The deletion of objects may influence all of the system views describing objects, like TABLES, VIEWS, PROCEDURES, ... . The deletion of objects may influence the view describing privileges like GRANTED_PRIVILEGES and all of the monitoring views like M_RS_TABLES, M_TABLE_LOCATIONS, . .. Example

A user with user_name NEW_USER was created before with, for example, this stat emant: CREATE USER new_user PASSWORD Password1;

The existing user new_user will now be dropped, together with all of his objects; DROP USER new_user CASCADE;

GRANT Syntax

GRANT | GRANT ION] | GRANT | GRANT | GRANT

,... TO [WITH ADMIN OPTION] ,... ON SCHEMA TO [WITH GRANT OPT ,... ON TO [WITH GRANT OPTION] ,... TO [WITH ADMIN OPTION] STRUCTURED PRIVILEGE TO

Syntax Elements

::= AUDIT ADMIN | CATALOG READ | CREATE SCHEMA | DATA ADMIN | IMPORT | LICENSE ADMIN | MONITOR ADMIN | RESOURCE ADMIN | SAVEPOINT ADMIN | SERVICE ADMIN | STRUCTUREDPRI VILEGE ADMIN | USER ADMIN | .

| | | | | | | | | | | |

BACKUP ADMIN CREATE SCENARIO CREATE STRUCTURED PRIVILEGE EXPORT INIFILE ADMIN LOG ADMIN OPTIMIZER ADMIN ROLE ADMIN SCENARIO ADMIN SESSION ADMIN TRACE ADMIN VERSION ADMIN

System privileges are used to restrict administrative tasks. The following system privileges are defined:

136

2012-08-21


AUDIT ADMIN This privilege controls the execution of the following auditing-related commands: CREATE AUDIT POLICY, DROP AUDIT POLICY and ALTER AUDIT POLICY. BACKUP ADMIN This privilege authorizes the ALTER SYSTEM BACKUP command to define and initiate a backup process or to perform a recovery process. CATALOG READ This privilege authorizes all users to have unfiltered read-only access to all system and monitoring views. Normally, the content of those views is filtered based on the privileges of the accessing user. The CATALOG READ privilege enables users to have read-only access to the full content of all system and monitoring views. CREATE SCENARIO This privilege controls the creation of ca lculation scenarios and cubes (calculation database). CREATE SCHEMA This privilege authorizes the crea tion of database schemas using the CREATE SCHEMA command. Ea ch user owns one schema. With this privilege he is allowed to create further ones. CREATE STRUCTURED PRIVILEGE This privilege a uthorizes the creation of Structured Privileges (Analytical Privileges). Note that only the owner of an Analytical Privilege can further grant it to other users or roles and revoke it ag ain. DATA ADMIN This powerful privilege authorizes to read all data in the system and monitoring views as well as execute all DDL (Data Definition Language) � and only DDL � commands in the SAP HANA database. This means, that a user having this privilege can not select or change dat a stored in other user's tables, but can modify the table definition or even drop the table. EXPORT This privilege authorizes the export activity in the database via t he EXPORT TABLE commands. Note that, beside this privilege, the user still needs the SELECT privilege on the source tables to be exported. IMPORT This privilege authorizes the import ac tivity in the data base using the IMPORT commands. Note t hat, beside this privilege, the user still needs the INSERT privilege on the target tables to be imported. INIFILE ADMIN This privilege authorizes different methods to change system settings. LICENSE ADMIN This privilege authorizes the SET SYSTEM LICENSE command install a new license. LOG ADMIN This privilege authorizes the ALTER SYSTEM LOGGING [ON|OFF] commands to enable or disable the log flush mechanism. MONITOR ADMIN This privilege a uthorizes the ALTER SYSTEM commands concerning EVENTs. OPTIMIZER ADMIN This privilege authorizes the ALTER SYSTEM commands concerning SQL PLAN CACHE and ALTER SYSTEM UPDATE STATISTICS commands, which influence the behavior of the query optimizer. RESOURCE ADMIN This privilege a uthorizes different commands concerning r esources, like ALTE R SYSTEM RECLAIM DATAVOLUME and ALTER SYSTEM R ESET MONITORING VIEW and it a uthorizes many of the commands in the Management Console. ROLE ADMIN This privilege authorizes the crea tion and deletion of roles using the CREATE ROLE a nd DROP ROLE commands. I t a lso authorizes the granting a nd revocation of roles using the G RANT and R EVOKE commands. SAVEPOINT ADMI N This privilege authorizes the execution of a savepoint process using the ALTER SYSTEM SAVEPOINT command. SCENARIO ADMIN

137

2012-08-21


This privilege authorizes all calculation scenario-related activities (including creation). SERVICE ADMIN This privilege authorizes the ALTER SYSTEM [START|CANCEL|RECONFIGURE] commands, intended for administering system services of the database. SESSION ADMIN This privilege authorizes the ALTER SYSTEM commands concerning sessions to stop or disconnect a user session or to change session variables. STRUCTUREDPRIVILEGE ADMIN This privilege a uthorizes the creation, reactivation, a nd dropping of structured privileges. TRACE ADMIN This privilege a uthorizes the ALTER SYSTEM [CLEAR|REMOVE] TRACES commands for operations on data base trace files. USER ADMIN This privilege authorizes the creation and changing of users using the CREATE USER, ALTER USER, and D ROP USER commands. VERSION ADMIN This privilege authorizes the ALTER SYSTEM RECLAIM VERSION SPACE command of the multi-version concurrency control (MVCC) mechanism. . Components of the SAP HANA database may create privileges for their own needs. Those use the componentname as first identifier of the system privilege and the component-privilege-name as the second identifier. Currently, the repository uses this feature. For description of the privileges named REPO., please see the manual of the repository.

::= CREATE ANY | DEBUG | DELETE | DROP | EXECUTE | INDEX | INSERT | SELECT | TRIGGER | UPDATE

Schema privileges are used to restrict the access and modifications on a schema and the objects stored in this schema. The following schema privileges are defined:

CREATE ANY This privilege allows the creation of all kinds of objects, in particular, tables, views, sequences, synonyms, SQL Script functions, or database procedures in a schema. DELETE, DROP, EXECUTE, INDEX, INSERT, SELECT, UPDATE The specified privilege is granted on every object stored in the specified schema currently and in future. For detailed description of the privileges please see the part describing object privileges below and please check below, which privileges are applicable for which kind of object.

::= ALL PRIVILEGES | ALTER | DEBUG | DELETE | DROP | EXECUTE | INDEX | INSERT | SELECT | TRIGGER | UPDATE | .

138

2012-08-21


Object privileges are used to restrict the access and modifications on database objects like tables, views, sequences, procedures and the like. Not all of these privileges are applicable to all kinds of database objects. For object types allowing which privilege, see table below. The following object privileges are defined:

ALL PRIVILEGES This privilege is a collection of all DDL (Data Definition Language) and DML (Data Manipulation Language) privileges that on t he one hand, t he grantor currently has and is allowed to grant further and on the other hand, can be granted on this particular object. This collection is dynamically evaluated for the given gra ntor and object. ALL PRIVILEGES is applicable to a ta ble or view. ALTER This DDL privilege authorizes the ALTER command for that object. DEBUG This DML privilege authorizes the debug-functionality for that procedure or ca lculation view or for the procedures or calculation views in that schema. DELETE This DML privilege authorizes the DELETE and TR UNCATE commands for that object. DROP This DDL privilege authorizes the D ROP commands for that object. EXECUTE This DML privilege authorizes the execution of a SQL Script function or a database procedure using the CALLS or CALL command, respectively. INDEX This DDL privilege authorizes the cr eation, changing, or dropping of indexes for that object. INSERT This DML privilege authorizes the INSERT command for that object. The I NSERT and the UPDATE privilege together are needed for one object to a llow the REPLACE and UPSERT command for that object. SELECT This DML privilege authorizes the SELECT command for that object or the usage of the sequence, respectively. TRIGGER This DDL privilege authorizes the CREATE TRI GGER / DR OP TRIGGER command for the specified table or the tables in the specified schema. UPDATE This DML privilege authorizes the UPDATE command for that object. The INSERT a nd the UPDATE privilege together are needed for one object to a llow the REPLACE and UPSERT command for that object. . Components of the SAP HANA database may create privileges for their own needs. Those use the componentname as first identifier of the privilege name and the component-privilege-name as the second identifier. Currently, the repository uses this feature. For description of the privileges named REPO., please see the manual of the repository.

139

2012-08-21


Privilege

Schema

Table

View

Sequence

Function/Procedure

ALL PRIVILEGES

---

YES

YES

---

---

ALTER

YES

YES

---

---

YES

CREATE ANY

YES

---

---

---

---

DEBUG

YES

---

YES

---

YES

DELETE

YES

YES

YES

---

---

DROP

YES

YES

YES

YES

YES

EXECUTE

YES

---

---

---

YES

INDEX

YES

YES

---

---

---

INSERT

YES

YES

YES

---

---

SELECT

YES

YES

YES

YES

---

TRIGGER

YES

YES

---

---

---

UPDATE

YES

YES

YES

---

---

DELETE, INSERT a nd UPDATE on views are valid for updatable views only, meaning such views complying to some restrictions as there are: no join included, no UNION included, no aggregation and some further restrictions. DEBUG is only valid for calculation views, not for other kinds of views. For synonyms those restrictions are valid, which are valid for the object this synonym stands for.

::= | | | |

Object privileges are used to restrict the access and modifications on database objects like tables, views, sequences, procedures and synonyms. :: = |

The grantee can be a user or a role. I n case a privilege or role is granted to a role, then all user being gra nted that r ole, will have the specified privilege or role. A role is a named collection of privileges and can be granted t o either a user or a role. If you want to a llow several database users to perform the same actions, you can create a role, g rant the needed privileges to this role, and g rant t he role to the different dat abase users. When granting roles to roles, a tree of roles can be build. When granting one role (R) to a role or user (G), G will have all privileges directly granted to R and all privileges granted to roles which had been granted to R.

::= ::= ::= ::= ::= ::= ::= ::= ::= Description

GRANT is used to g rant privileges and structured privileges to users and roles. GRANT is a lso used to grant roles to users and other roles. The specified users, roles, objects, and structured privileges have to exist before they can be used in the GRANT command. Only users having a privilege and being allowed to grant that privilege any further can g rant a privilege. Ea ch user having ROLE ADMIN system privilege is allowed to grant roles to other roles and users.

140

2012-08-21


A user can not grant a privilege to himself. User SYSTEM has at least all system privileges and the role PUBLIC. All other users have the role PUBLIC. These privileges and roles can not be revoked from them. Although user SYSTEM has many privileges, he is not able to select or change data in other user's tables if he is not granted the privilege to do so explicitly. User SYSTEM and all other users do have the privilege to create objects in their own default schema, which has the same name as the user itself. For tables created by themselves, users have all privileges and may grant all of them to users and roles. For other objects which are dependent on objects like views being dependent on tables, it may happen, that even the owner of the dependent object does not have all privileges if he does not have them on the underlying objects. Or it may happen, that he has privileges, but is not allowed to gr ant them a ny further. Then he cannot grant those privileges. WITH ADMI N OPTION and WITH GR ANT OPTION specifies that the granted privileges can be granted further by the specified user or by those user having the specified role.

With GRANT STRUCTURED PRI VILEGE a previously defined analytical privilege (based on a generic structured privilege) is granted t o a user or role. This analytical privileges is used to restrict the acc ess for read operations to certain dat a in Analytic, At tribute, and Calculation Views by filtering the att ribute values. System and Monitoring Views

USERS: shows all users, their creat or, creat ion date and some info about their current stat es. ROLES: shows all roles, their creator and creat ion date. GRANTED_ROLES: shows which roles are gra nted to which user or role. GRANTED_PRIVI LEGES: shows which privileges are granted to which user or role.

Example

Assuming a user, who has the privileges to create schemas, roles and users, is connected. He creat es a schema for some work. CREATE SCHEMA myschema;

Additionally he creates a a table, named work_done, in this new schema. CREATE TABLE myschema.work_done (t TIMESTAMP, user NVARCHAR (256), work_done VARCHA R (256);

He creates one new user, named worker, with the possibility to connect using a password and a new role, named role_for_work_on_my_schema. CREATE USER worker PASSWORD His_Password_1; CREATE ROLE role_for_work_on_my_schema;

He grants the privilege to SELECT on any object in his schema to the role role_for_work_on_my_schema. GRANT SELECT ON SCHEMA myschema TO role_for_work_on_my_schema;

Additionally he grants the privilege to INSERT into his table work_done to the role role_for_work_on_my_schema. GRANT INSERT ON myschema.work_done TO role_for_work_on_my_schema;

Then he grants this role to that new user. GRANT role_for_work_on_my_schema TO worker WITH GRANT OPTION;

Additionally the user worker is granted t he privilege to DELETE from this table directly. This privilege is granted with the option to grant this privilege further. GRANT DELETE ON myschema.work_done TO worker;

Now he grants the privilege to create any kind of object in his schema myschema to user worker directly.

141

2012-08-21


GRANT CREATE ANY ON SCHEMA myschema TO worker;

As a result, user worker has the privilege to SELECT all tables and views in schema myschema, to I NSERT into and DELETE from table myschema.work_done and to create objects in schema myschema. Additionally he is allowed to grant DELETE on myschema.work_done to other users and roles. In the second example a user having the corresponding privileges inclusive the allowance to grant them further, grants t he system privileges INIFILE AD MIN and TRACE AD MIN to t he existing user worker. He does this with the allowance for worker to grant t hem further. GRANT INIFILE ADMIN, TRACE ADMIN TO worker WITH ADMIN OPTION;

REVOKE Syntax

| | | |

REVOKE REVOKE REVOKE REVOKE REVOKE

,... FROM ,... ON SCHEMA FROM ,... ON FROM ,... FROM STRUCTURED PRIVILEGE FROM

Syntax Elements

For the definition of syntax elements, please refer to GRANT. Description

The REVOKE statement revokes the specified roles or structured privileges or the privileges for the specified objects from the specified users or roles. Only users having granted a privilege can revoke that privilege. This is true even for users having ROLE ADMIN and the revoke of roles. User SYSTEM has at least all system privileges and the role PUBLIC. All other users have the role PUBLIC. These privileges and roles can not be revoked from them. If a user was granted a role, it is not possible to revoke some of the privileges belonging to that role. In this case, the whole role has to be revoked and those privileges needed by that user granted to him. Revoking a privilege or role may lead to making some views inaccessible or making procedures not executable any more. This will occur if a view or procedures depends on the privilege revoked or on one of the privileges the role had. Revoking a privilege which had been granted with WITH G RANT OPTION or with WITH ADMIN OPTION will result in revoking that privilege not only from the user specified in the command, but from all the users and roles having been granted that privilege by this user directly and indirectly. As privileges can be granted to one user or role by different users, revoking of this privilege by one user does not necessarily mean, that this user will loose that privilege. For the description of syntax elements and privileges, please refer t o GRANT. System and Monitoring Views

USERS: shows all users, their creat or, creat ion date and some info about their current stat es. ROLES: shows all roles, their creator and creat ion date. GRANTED_ROLES: shows which roles are gra nted to which user or role. GRANTED_PRIVI LEGES: shows which privileges are granted to which user or role.

Example

Assuming that the following statements had been executed by the user currently connected. CREATE USER worker PASSWORD His_Password_1; CREATE ROLE role_for_work_on_my_schema; CREATE TABLE myschema.work_done (t TIMESTAMP, user NVARCHAR (256), work_done VARCHA R (256); GRANT SELECT ON SCHEMA myschema TO role_for_work_on_my_schema; GRANT INSERT ON myschema.work_done TO role_for_work_on_my_schema; GRANT role_for_work_on_my_schema TO worker; GRANT TRACE ADMIN TO worker WITH ADMIN OPTION; GRANT DELETE ON myschema.work_done TO worker WITH GRANT OPTION;

142

2012-08-21


The user having granted privileges is allowed to revoke those privileges. He revokes the privilege from the role and therefore implicitly revokes it from all users being granted that role. Additionally the user worker should not have the privilege TRACE AD MIN a ny more. It is revoked causing the revocation to occur for all the users, worker had gra nted this privilege to. REVOKE SELECT ON SCHEMA myschema FROM role_for_work_on_my_schema; REVOKE TRACE ADMIN FROM worker;

Data Import Export Statements EXPORT Syntax

EXPORT AS INTO [WITH ] Syntax Elements

WITH : EXPORT options can be passed in using "WITH" clause. ::= ,... | ALL ::= BINARY | CSV ::= 'FULL_PATH' ::= | ::= REPLACE | CATALOG ONLY | NO DEPENDENCIES | SCRAMBLE [BY ] | THREADS Description

The EXPORT command exports tables, views, column views, synonyms, sequences, or procedures in the specified format as BI NARY or CSV. Da ta for t emporary tables and "no logging" tables cannot be exported using EXPORT. OBJECT_NAME SQL name of the object (table, view, etc) to export. To export all objects from all schemas you should use the keyword ALL. If you want to export objects of a specific schema, you should use the schema name with the asterisk, e.g. "SYSTEM"."*" BINARY Table data will be exported in internal binary format. Exporting in t his format is orders of magnitude faster then exporting the same table in CSV format. Only column tables can be exported in binary format. The row tables are a lways exported in CSV format, even if BINARY format is specified. CSV Table data will be exported in CSV format. The exported data can be imported only in any other database. Additionaly, the exported data can be scramled. Both column and row tables can be exported in CSV format. FULL_PATH The server path to export to. Note:

When using a distributed system, the FULL_PATH must point to a shared disk! For security reasons, the path may not contain symbolic links and may not point inside the database instance folder, except its 'backup' and 'work' subfolders. Examples for valid export path (assuming the database instance is located at /usr/sap/HDB/HDB00): '/tmp'
'/usr/sap/HDB/HDB00/backup'
'/usr/sap/HDB/HDB00/work'

REPLACE With REPLACE option, previously exported data will be removed and newly exported data will be saved. If REPLACE option is not specified, an error will be thrown if there exists a previously exported data already in the specified directory.

143

2012-08-21


CATALOG ONLY With CATALOG ONLY option, only database catalog will be exported without dat a. NO DEPENDENCIES With NO DEPENDENCIES option, it will not export underlying objects of an object to be exported. SCRAMBLE Export in CSV format has an additional option to scramble sensitive customer data using SCRAMBLE [BY '']. When the optional password is not specified, a default scramble password is used. Only character string data can be scrambled. When imported, scrambled data will be imported as scrambled so that end users cannot read the data and it is not possible to unscramble the data. THREADS Indicates the number of threads that can be used for concurrent export. Number of threads to use

The given number of THREADS specifies how many objects will be exported in parallel (default is 1). Increasing number of threads may reduce export time, but also can negat ively affect the system performance. Following should be considered: For a single table THREAD S has no effect. For a view or procedure, 2 or more threads should be used (up to number of objects it depends on) For a whole schema consider using more that 10 threads (up to number of cores in the system) For a whole BW / ERP system (ALL keyword) with tens of thousands of tables, a large number of threads can be reasonable (up to 256) Number of threads to use

You can monitor the progress of the export using M_EXPORT_BINARY_STATUS system views. You can abort the export session using the session ID from the corresponding view in the following command: ALTER SYSTEM CANCEL [WORK IN] SESSION 'sessionId'

The detailed result of the export is stored in session-local temporary tables. #EXPORT_RESULT. Number of threads to use

EXPORT "SCHEMA"."*" AS CSV INTO '/tmp' WITH R EPLACE SCRAMBLE TH READS 10 IMPORT Syntax

IMPORT [AS ] FROM [WITH ] Syntax Elements

WITH : IMPORT options can be passed in using "WITH" clause. ::= ,... | ALL ::= BINARY | CSV ::= 'FULL_PATH' ::= | ::= REPLACE | CATALOG ONLY | NO DEPENDENCIES | THREADS Description

The IMPORT statement imports tables, views, column views, synonyms, sequences, or procedures. D ata for temporary tables and "no logging" tables cannot be imported using I MPORT command.

144

2012-08-21


OBJECT_NAME SQL name of the object (table, view, etc) to import. To import all objects from the import path you should use the keyword ALL. If you want to import objects of a specific schema, you should use the schema name with the asterisk, e.g. "SYSTEM"."*" BINARY | CSV Format specification may be ommited during the import, since the format will be auto-detected during import. The data will be imported in the same format in which it was exported. FULL_PATH The server path to import from. Note:

When using a distributed system, the FULL_PATH must point to a shared disk! Examples for valid import pat h (assuming t he database instance is located at /usr/sap/HDB/HDB00): '/tmp'
'/usr/sap/HDB/HDB00/backup'
'/usr/sap/HDB/HDB00/work'

REPLACE With REPLACE option, the specified table will be dropped and created and data will be imported. If REPLACE option is not specified, an error will be thrown if there exists a table with the same name in the same schema. CATALOG ONLY With CATALOG ONLY option, only database catalog will be imported without dat a. NO DEPENDENCIES With NO DEPENDENCIES option, it will not import underlying objects of an object to be imported. THREADS Indicates the number of threads that can be used for concurrent import. Number of threads to use

The given number of THREADS specifies how many objects will be imported in parallel (default is 1). Increasing number of threads may reduce import time, but a lso can negatively affect t he system performance. Following should be considered: For a single table THREAD S has no effect. For a view or procedure, 2 or more threads should be used (up to number of objects it depends on) For a whole schema consider using more that 10 threads (up to number of cores in the system) For a whole BW / ERP system (ALL keyword) with tens of thousands of tables, a large number of threads can be reasonable (up to 256) Number of threads to use

You can monitor the progress of the import using M_IMPORT_BI NARY_STATUS system views. Also, you can abort the import session using the connection ID from the corresponding view in the following command: ALTER SYSTEM CANCEL [WORK IN] SESSION 'sessionId'

The detailed result of the import is stored in session-local temporary tables. #IMPORT_RESULT. Example

IMPORT "SCHEMA"."*" AS CSV FROM '/tmp' WITH REPLACE THREADS 10

IMPORT FROM Syntax

IMPORT FROM [] [INTO ] [WITH ] Syntax Elements

145

2012-08-21


WITH : IMPORT FROM options can be passed in using "WITH" clause. ::= CSV FILE | CONTROL FILE ::= '...' ::= [.] ::= | :: = THREADS | BATCH | TABLE LOCK | NO TYPE CHECK | SKIP FIRST ROW | COLUMN LIST IN FIRST ROW | COLUMN LIST ( ) | RECORD DELIMITED BY '' | FIELD DELIMITED BY '' | OPTIONALLY ENCLOSED BY '' | DATE FORMAT '' | TIME FORMAT '' | TIMESTAMP FORMAT '' | Description

The IMPORT FROM statements imports external data from a csv file into an existing ta ble. THREADS I ndicates the number of threads that can be used for concurrent import. Default value is 1 a nd maximum allowed value is 256. BATCH Indicates the number of records to be inserted in each commit. THREADS a nd BATCH can be used to achieve high loading performance by enabling parallel loading and committing many records at once. In general, for column tables, a good setting is to use 10 parallel loading threads, with a commit frequency of 10.000 records or greater. TABLE LOCK Locks table for fast import into column store tables. If NO TYPE CHECK option is specified, then the record is inserted without checking the type of each field. SKIP FIRST ROW Skips to insert first n rows. COLUMN LIST IN FIR ST ROW Indicates the column list that is in the first row of CSV file. COLUMN LIST ( ) Indicates the column list to be inserted RECORD DELI MITED BY '' Indicates the record delimiter of the CSV file FIELD D ELIMITE D BY '' Indicates the field delimiter of the CSV file OPTIONALLY ENCLOSED BY '' Indicates t he optional enclosure of field data DATE FORMAT '' Indicates the string of dat e format. I f there is date t ype in CSV file, it will use the specified format for date type field. TIME FORMAT '' Indicates the string of time format. I f there is time type in CSV file, it will use the specified format for time type field. TIMESTAMP FORMAT '' Indicates the string of timestamp format. I f there is timestamp type in CSV file, it will use the specified format for timestamp type field. Example

IMPORT FROM CSV FILE '/data/data.csv' INTO "MYSCHEMA"."MYTABLE" WITH RECORD DELIMIT ED BY '\n' FIELD DELIMITED BY ','

146

2012-08-21


Restrictions for SQL Statements The table below shows the maximum allowable limit for each entry.

147

2012-08-21


Database

Database size limit

Limited by storage size RS: 1TB

Number of locks

Unlimited for record locks, 16384 for table locks

Number of sessions

8192

Schemas

Number of tables in a schema

131072

Identifier length

127 characters

Length of an alias name

128 characters

Table name length

Please see "Identifier length" above

Column name length

Please see "Identifier length" above

Length of a constant string literal

32767 bytes

Number of hex characters in a binary literal

8192

Tables and Views

Number of columns in a table

1000

Number of columns in a view

1000

Number of partitions of a column table

1000

Number of rows in each table

Limited by storage size RS: 1TB/sizeof(row), CS: 2^31 * number of partitions

Length of a row

Limited by RS storage size ( 1TB )

Size of a non-partitioned table


Indexes and Constraints

Number of indexes for each table

1023

Number of primary key columns in each table

16

Number of columns in an index

16

Number of columns in a UNIQUE constraint

16

Size of sum of primary key, index, UNIQUE 16384 constraint

SQL

148

2012-08-21


Length of an SQL statement

2GB

Depth of SQL view nesting

128

Depth of SQL parse tree

255

Number of joined ta bles in an SQL st at ement or view

255

Number of columns in an ORDER B Y, GR OUP BY or SELECT clause

65535

Number of elements in predicates

65535

Number of elements in SELECT clause

65535

SQLScript

Size of all stored procedures


Note:

You can obtain the current system limits from your SAP HANA Database instance by using select * from m_system_limits

See Also M_SYSTEM_LIMITS in the System Tables and Monitor Views Reference

149

2012-08-21


SQL Error Codes The following table lists error codes displayed by SAP HANA database and their descriptions.

15 0

2 0 12 -0 8 -2 1


Error Code

Description

1

G e n e ra l wa rn i n g

2

G e n e ra l e rro r

3

F a ta l e rro r

4

Ca n n o t a l l o ca te e n o u gh m e m o ry

5

I n i ti a l i z a ti o n e rro r

6

I n va l i d d a ta

7

F e a tu re n o t s u p p o rte d

8

I n va l i d a rgu me n t

9

I n de x ou t of bo und s

10

I n va l i d u s e rn a m e or or p a s s wo rd

11

I n va l i d s ta te

12

Ca n n o t o p e n fi l e

13

Ca n n o t cre a te / wri te fi l e

14

Ca n nn n o t a llll o ca te en e n o u gh di di s k sp sp a ce

15

Ca n n o t f i n d fi l e

16

S ta te me n t re try

17

Me ta ta da da ta ta s ch ch em ema v er ers i on on i nc nco mp mp a atti bl bl e b et etw ee ee n da da ta ta ba ba s e a nd nd e xe xe cu cu ta ta bl bl e f ilil e

18

S e rvi ce s h u tti n g d o wn

19

I n va l i d l i ce n s e

128

Tra n s a cti o n e rro r

129

Tra ns ns a ct cti on on ro ro l l e ed d ba ba ck ck by by a n in in te te rn rn a l er e rro r

130

Tra n s ac act io io n ro l l ed ed ba ba cckk by by i n te te gr gri ty ty co ns ns tr tra i n t vi o l a ti ti o n

131

Tra ns ns a ct cti on on ro ro l l e ed d ba ba ck ck by by l oc ock wa wa i t ti me me ou ou t

132

Tra ns ns a ct cti on on ro ro ll ll e d b a acck du du e to un un a avva i l ab ab le le re re s ou ou rc rce

133

Tra ns ns a ct cti on on ro ll ll e d b a acck b y d et ete ct cte d d ea ea dl dl oc ock

134

F a il i l ur ure in i n ac acce ss ss in in g ch e cckk p o i nt nt fi le le

135

F a iill ur ure in i n a cccce ss ss in in g a nc nch o r fi le le

136

F a iill u re in in a cccce s s i n g l og og f i l e

137

F a il i l ur ure i n a cc cce ss ss in in g a rc rch i ve ve fi le le

138

Tra ns ns ac a cti on on s er eri al al iz i za ti ti on on fa il i l ur ure

139

Cu rr rre n t o pe pe ra ra ti ti o n ca ca n ce ce l l e d b y re q ue ue s t a n d t ra ra n s ac acti o n ro l l ed ed b a ck ck

140

I nv nva l id id w ri te te -t -tra ns ns a ct cti on on i de de nt nti fi fi er er

141

F a il i l ur ure in in a cc cce ss ss in in g i nv nvi si s i bl bl e l og og fi le le

142

Exce e d m ax ax n um um o f co nc ncu rr rre nt nt tra ns ns a ct cti on on s

143

Tra ns ns a ct cti on on s er eri a l iz iza ti ti on on fa fa i l ur ure un un ti ti l ti ti me me ou ou t e xxp p ir ire s

144

Tra ns ns a ct cti on on ro ro ll ll ba ba ck ck , u ni ni qu qu e co ns ns tr tra i nt nt vi ol ol a te te d

145

Tra ns ns a ct cti on on d i s tr tri bu bu ti on on wo rk fa i lu lu re re

146

R es es ou ou rc rce bu bu sy sy a nd nd ac acq ui ui re re wi wi th th NO NOWAI T s pe pe ci ci fi fi e d

147

I nc nco n s is is te te nc ncy b e tw tw e e en n d a ta ta an an d l og og

148

Tra n s ac act io io n s ta ta rt rt i s bl bl o ck ck e d u nt nt ilil M Ma a s te te r_ r_ Re Re s ta ta rt rt f in in i s he he s

149

D is is tr tri bu bu te te d tra ns ns a ct cti on on co co m mi t fa i lu lu re

150

S ta te te me me nt nt ca nc nce ll ll ed e d d ue ue to o l d s na na ps ps ho ho t

256

S QL p ro ce s s i n g e rro r

257

S QL s yn ta x e rro r

258

I n s u f fi ci en en t p ri vi vi l e ge

259

I n va l i d ta b l e n a me

260

I n va l i d co l u mn n a me

261

I n va l i d i n d e x n a m e

262

I n va l i d q u e ry n a m e

263

I n va l i d a l i a s n a me

264

I n va l i d da da ta typ e

265

Exp re s s i o n mi s s i n g

266

I n co n s i s te n t da da ta typ e

267

S p ec eci fi fi e ed d le le ng ngth to to o l on on g fo fo r i ts ts da d a ta ta ty typ e

268

Co l um um n a mb mb i g gu u o u s ly ly d e f i ne ne d

15 1

2 0 12 -0 8 -2 1


269

To o m a n y va l u e s

270

N o t e n o u gh va l u e s

271

D u p l i ca te a l i a s

272

D u p l i ca te co l u m n n a m e

273

N o t a si si ng ngl e ch a ra ra cctte r s tr tri ng ng

274

I ns ns er erte d va lu l u e to to o la la rg rge fo fo r co l um umn

275

Aggre ga ga te te fu n cti on on n o t a ll l l ow ow e d

276

Mi ss ss in i n g a gg gg re ga ga ti ti on on o r g ro u p i ng ng

277

N o t a GR GR OU P B Y e xp re s s i on on

278

N e st ste d g ro u p f u n cti on on wi wi th th o ut ut G R OUP B Y

279

G ro u p fu n cti o n i s n e s te te d

280

OR DE DER BY BY i te te m mu st st b e th e n um um be be r o f a SE SELECT-l i s t

281

Ou te r jo in in no no t a l lo lo we we d i n o p er era nd nd of of OR or or I N

282

Tw o ta bl bl e s ca nn nn ot ot b e o ut ute rr- jo jo in in ed ed to to e a ch ch ot oth er er

283

A ta bl bl e ma ma y b e o ut ute r jo in in ed ed to to at at m os os t o ne ne ot oth er er ta bl bl e

284

Jo i n fi e l d d o e s n o t ma ttcch

285

I n va l i d jo i n co n d i ti o n

286

I d e n ti fi e r i s to to o l o n g

287

Ca nn nn ot ot i ns ns er e rt N ULL o r u p d at ate to to NU LL

288

Ca nn nn o t u s e d u p l ic ica te te ta ta bl bl e n a me me

289

Ca nn nn o t u s e d u p l ic ica te te in i n d e x n a me me

290

Ca nn nn o t u s e d u p l ic ica te te q u e ry ry n a me me

291

Argu m e nt nt i de de nt nti fi fi er e r m u s t b e p o si si ti ti ve ve

292

w ro n g n u mb e r o f a rg rgu me nt nts

293

Argu m e n t typ e mi s ma ma ttcch

294

Ca nn nn ot ot h av ave mo mo re th th a n o ne ne pr pri ma ma ry ry k e y

295

To o l on on g mu l ti k e y l e n gth

296

R ep ep l i cca a te te d ta bl bl e m u s t h a ve ve a p riri ma ma ry ry k e y

297

Ca nn nn ot ot u pd pd a atte pr pri ma ma ry ry k ey ey f ie ie l d i n re pl pl i ca ca te te d ta bl bl e

298

Ca n n o t s to re D D L

299

Ca n no no t d ro ro p i n de de x u s ed ed fo fo r e n fo fo rc rce me me n t o f u ni ni q ue ue /p /p ri ri m ar ary k e y

300

Argu m e n t i nd nd e x i s o u t o f ra ng nge

301

U n i qu qu e co n s tr tra iin n t vi ol ol a atte d

302

I nv nva li l i d CH AR o r VAR CH AR va lu lue

303

I nv nva l id id DA DATE, TI TI ME ME or or TI TI ME MES TAMP va l ue ue

304

D i vi s io io n b y ze ro u n d e fi n e d

305

S iin n gl gl e -r -ro w q ue ue ry ry re tu tu rn rn s mo re re th a an n o ne ne ro w

306

I n va l i d cu rs o r

307

N u m e ri c va l ue ue ou ou t o f ra n ge

308

Co l u m n n a m e al al re a d y e xi s ts

309

Co rr rre l at ate d s ub ub qu qu er ery ca nn nn ot ot ha ha ve ve TO TOP o r OR DE DER BY BY

310

S QL e rro r i n p ro ce d u re

311

Ca nn nn o t d ro p al al l co l um um n s in i n a ta ta bl bl e

312

S e q u e n ce is is ex e xh a u s t e d

313

I n va l i d s e q u e n ce

314

N u m e ri c o ve rfl o w

315

I n va l i d s yn o n ym

316

w ro ro ng ng nu nu mb mb er er of of a rg rgu me me nt nts i n fu fu nc ncti on on i nv nvo ca ca ti ti on on

317

P_QU ER ER YP YPLANS n ot ot e xxii s ts ts no no r va l i d fo rm rm at at

318

D ec eci ma ma l pr pre ci ci s io io n sp sp e ci ci fi fi er er i s o ut ut o f ra ng ng e

319

D e ccii ma ma l sc sca le l e sp s p e ccii fi fi er er i s ou ou t o f ra ng nge

320

Ca nn nn ot ot cr cre a te te i nd nd ex ex o n e xxp p re re s s io io n wi th th da da ta ta ty typ e LO LOB

321

I n va l i d vi e w n a me

322

Ca nn nn o t u s e d u p l ic ica te te vi vi ew ew n a me me

323

D u p l ic ica te re p l i ca ca ti o n I D

324

Ca nn nn o t u s e d u p l ic ica te te se se qu qu e nc nce na na me me

325

I n va l i d e s ca p e s e q u e n ce

15 2

2 0 12 -0 8 -2 1


326

CU RR VAL of gi ve n se qu en ce i s no t ye t d efi ne d in th is s e ss i on

327

Ca nnot e xpl ai n pl an of gi ve n s ta te me nt

328

I nva l id n ame of fu ncti on or p roce dure

329

Ca nn ot us e du pl i ca te n am e of fu ncti on or pro ce du re

330

Ca nnot us e dupl ica te synonym na me

331

Us er na me al re a dy exi s ts

332

Inva l i d us er na me

333

Col umn not a l l owe d

334

Inva l i d us er pri vi l e ge

335

Fi el d a li as na me a lre ady exi sts

336

Inva l i d defa ul t va l ue

337

I NTO cl a us e no t a l l ow ed fo r th is SELECT s ta te me nt

338

Ze ro-l ength col lumns a re not a ll owe d

339

Inva l i d number

340

Not a l l va ri a bl e s bound

341

Numeri c underfl ow

342

Col l a ti on confl i ct

343

Inva l i d col l a te na me

344

Pa rs e error i n da ta l oa de r

345

Not a re pl i ca ti on ta bl e

346

Inva l i d repl i ca ti on ID

347

Inva l id opti on i n moni tor

348

Inva l i d da te ti me forma t

349

Ca nnot CREATE UNIQUE INDEX

350

Ca nn ot dro p co lu mn s in th e pri ma ry-k ey co lu mn l is t

351

Co lu mn is re fe re nce d i n a mu lti -co lu mn co ns tra i nt

352

Ca nn ot cre ate uni que ind ex on CDX ta bl e

353

Upda te log group na me al re ady e xi sts

354

Inva li d upda te l og group na me

355

Th e ba s e ta bl e of th e up da te l og ta bl e mu st ha ve a p ri ma ry k ey

356

Exce ed ma xi mu m n umbe r of upd ate l og group

357

Th e b as e ta bl e a lre ad y h as a up da te l og ta bl e

358

Upda te l og ta bl e ca n not ha ve a up da te lo g ta bl e

359

Conca te na te d s tri ng is too long

360

Vi e w WI TH CH ECK OPTI ON wh ere -cl a us e vi ol a ti on

361

D ata ma ni pul a ti on op era ti on not le ga l on th is vi ew

362

Inva l i d s che ma na me

363

Numbe r of inde x col umns e xce eds i ts ma xi mu m

364

Inva l i d pa rti a l key s i ze

365

N o m atch in g u ni qu e or p ri ma ry k ey fo r th is co lu mn l is t

366

R efe re nce d ta bl e d oe s not h ave a pri ma ry ke y

367

N um be r o f re fe re n ci n g co l um ns m us t m a tch re fe re n ce d co l um ns

368

U ni qu e co ns tra i nt n ot a l l ow ed on te mp ora ry ta bl e

369

Exce ed ma xi mum vi ew de pth l imi t

370

Ca n no t p e rf orm DI R ECT IN SER T o pe ra ti o n o n ta b l e wi th u ni q ue i n de xe s

371

Inva l i d XML document

372

Inva l i d XPATH

373

Inva li d XML dura ti on va lue

374

Inva li d XML functi on us age

375

Inva li d XML i ndex ope ra ti on

376

Python s tore d proce dure error

377

JI T opera ti on e rror

378

Inva l i d col umn vi e w

379

Ta bl e schema mi s ma tch

380

Fa i l to cha nge run l evel

381

Fa i l to re s ta rt

382

Fa il to col le ct a ll ve rs ion ga rba ge

153

2012-08-21


383

Inva l i d i denti fi er

384

Cons ta nt s tri ng i s too long

385

Coul d not re s tore s es si on

386

Ca nnot us e dupl ica te schema na me

387

Ta bl e a mbi guous l y de fi ned

388

Rol e al rea dy exi s ts

389

Inva l i d rol e na me

390

Inva l i d us er type

391

Inva l i da ted vi e w

392

Ca n't a s si gn cycl i c rol e

393

R ol e s mu st no t re ce i ve a pri vi l ege wi th gra nt op ti on

394

Error revoki ng rol e

395

Inva li d us er-de fi ned type na me

396

Ca nn ot u se dup li ca te us er-d efi ne d typ e n ame

397

Inva l i d obje ct na me

398

Ca nnot ha ve more tha n one orde r by

399

Rol e tre e too deep

400

Pri ma ry k ey n ot a l l ow ed o n i ns e rt-o nl y ta bl e

401

U ni qu e co ns tra i nt n ot a l l ow ed on in se rt-o nl y ta bl e

402

Th e u se r wa s a l re a dy d ro pp ed be fo re qu ery e xe cu ti on

403

Interna l error

404

I nva l i d ( no n-e xi s te nt) s tru ctu re d pri vi l ege n ame

405

Ca nn ot u se du pl i ca te s tru ctu re d p ri vi l ege na me

406

I NS ER T, UPD ATE an d U PS ER T a re d is a l l ow ed on th e ge n era te d fi e l d

407

Inva l i d da te forma t

408

Pa s sword o r pa ra me te r re qu ire d fo r us er

409

Mul ti pl e va l ue s fo r a p ara me te r no t s upp orte d

410

Inva l id pri vi lege na mes pa ce

411

Inva l i d ta bl e type

412

Inva l i d pa s s word l a yout

413

La st n pa ss words ca n not be reus ed

414

Us er i s force d to cha nge pa ss word

415

Us er i s dea cti va te d

416

Us er i s l ocked

417

Ca n't d ro p w ith ou t CAS CAD E s pe ci fi ca ti on

418

I nva li d vi ew que ry for cre ati on

419

Ca n't dro p wi th RESTR ICT spe ci fi ca ti on .

420

Pa s sword ch an ge curre ntl y n ot a l lo we d

421

Ca nnot crea te ful ltext i ndex

422

Pri vi l ege s mu st be e i th er al l SQL or al l fro m o ne n am es pa ce

423

Li veCa che error

424

Inva l i d na me of pa cka ge

425

Dupl i ca te pa cka ge na me

426

Number of col umns mi sma tch

427

Ca nnot re se rve inde x I D a ny more

428

Inva l i d que ry pl a n ID

429

Integri ty che ck fa i l ed

430

Inva l i da ted procedure

431

Us er's pa ss word wi ll exp ire wi thi n fe w da ys

432

Th i s s yn ta x h a s b e en de p re ca te d a n d w i ll be re mo ve d i n n e xt re l e a s e

433

Nul l va l ue found

434

Inva l i d obje ct I D

435

Inva l i d e xpres s i on

436

Coul d not s et s ys te m l ice ns e

437

O nl y co mm a nd s f or l i ce n se ha n dl i n g a re a l lo we d i n cu rre n t s ta te

438

Inva li d us er pa ra mete r va lue

439

Compos i te e rror

154

2012-08-21


440

Ta bl e type convers ion error

441

Th i s f ea tu re ha s be e n d e pre ca te d a n d w i ll be re mo ve d i n n ext re l e a s e

442

Numbe r of col umns e xce eds i ts ma xi mum

443

I nva li d ca lcul ati on s ce na ri o na me

444

Pa cka ge ma na ger error

512

Repl i ca ti on error

513

Ca n no t e xe cu te D DL s ta te me n t o n re pl i ca ti o n ta b l e w hi l e re p li ca ti n g

514

Fa il ure in a cce ss ing a nchor fi le

515

Fa il ure in a cce s s i ng l og fi l e

516

R ep li ca ti on ta bl e h as no t co nfl i ct re po rt ta bl e

517

Co nfl i ct re port ta bl e a l re ady e na bl ed

518

Co nfl i ct re port ta bl e a l re ady di s abl ed

576

API e rror

577

Cu rs or type of forwa rd i s not a ll owe d

578

Inva l i d s ta teme nt

579

Excee d ma xi mum ba tch s ize

580

S e rve r re je cte d th e co nn e cti o n( pro to co l ve rs i o n m i s ma tch )

581

Th is f un cti on ca n be ca l le d on ly i n th e ca s e of si ngl e s ta te me nt

582

Thi s que ry doe s not ha ve re sul t s et

583

Connecti on doe s not e xi s t

584

No more l ob da ta

585

Opera ti on is not permi tte d

586

I nva l id pa ra me te r i s re ce ive d from se rve r

587

Res ul t s et i s curre ntl y i nva li d

588

Ne xt() is not ca ll ed for thi s re sul t s et

589

Too ma ny pa ra meters are se t

590

Some pa ra mters a re mi s s ing

591

Interna l error

592

Not s upporte d type conve rs ion

593

Remote-onl y functi on

594

No more res ul t row i n res ul t s et

595

Sp eci fi ed pa ra me te r i s not outp ut pa ra me te r

596

LOB s tre a mi ng is n ot p erm itte d i n a uto -co mm it m od e

597

Se s s i on context error

598

Fa i le d to e xe cu te the exte rn al sta te me nt

599

Se ss ion l aye r i s not i ni ti al ize d ye t

600

Fa il e d route d e xecuti on

601

Too ma ny s es si on va ri abl es are se t

602

Ca nnot s et re adonl y s es si on va ri abl e

603

Inva l i d LOB

604

Remote temp ta bl e a cces s fa il ure

605

Inva l i d XA joi n reques t

606

Excee d ma xi mum LOB s i ze

607

Fa il e d to cl e a nup res ources

608

Exce e d m axi mu m n um be r o f p re pa re d s ta te me nts

1024

Se s s i on error

1025

Communi ca ti on error

1026

Ca nn ot b ind a commu ni ca ti on port

1027

Co mmun ica ti on i ni ti a li za ti on e rror

1028

I/O control error

1029

Connecti on fa i l ure

1030

Se nd error

1031

Rece i ve error

1032

Ca nnot crea te a threa d

1033

Error wh il e pa rs ing proto col

1034

Exce ed ma xi mu m n umbe r of s es s io ns

1035

Not s upported vers ion

155

2012-08-21


1036

Inva l i d s es s i on ID

1037

Unknown hos tna me

1280

Sql Scri pt error

1281

W ro ng nu mb er or typ es o f p ara me te rs i n ca l l

1282

Outp ut p ara me te r not a va ri a bl e

1283

O UT a nd I N OU T p a ra m et ers m a y n ot ha ve d e fa u l t e xp re s s i on s

1284

D up li ca te p ara me te rs are no t p erm itte d

1285

A t m os t on e de cl a ra ti o n is p e rm i tte d i n th e de cl a ra ti o n s e cti o n

1286

Cu rs o r m us t b e d e cl a re d b y S ELE CT s ta te me nt

1287

I de nti fi er mus t be de cl are d

1288

Exp re s si on ca nn ot be u se d as a n a ss i gn me nt ta rge t

1 289

E xp re s s i o n ca n n ot be u s e d a s a n I N TO -t a rg e t o f S EL ECT /F ET CH s ta t e me n t

1290

Exp re s s i on i s i n ap pro pri a te a s t he l e ft ha n d si d e of a n a s si gn me n t s ta te me nt

1291

Expre ss ion is of wrong type

1292

I l le ga l EXI T s ta te me nt, it mu st ap pe a r i ns i de a l oo p

1293

I de nti fi e r n ame mu st b e a n e xce pti on n am e

1294

An I NTO cl a us e i s e xp ecte d i n S ELECT s ta te me nt

1295

EXPLA IN PLA N a n d CAL L s ta te me n t a re n ot al l o we d

1296

Identi fi er i s not a curs or

1297

W ro ng nu mb er of va l u es i n th e IN TO li s t o f a F ET CH s ta te me nt

1298

Unha nd le d us er-d efi ne d e xce pti on

1299

No da ta found

1300

F e tch re tu rn s mo re th a n re qu e s te d nu mb e r o f ro ws

1301

Numeri c or va lue e rror

1302

Pa ra l l e l i za b l e fu ncti o n ca n no t h a ve O UT or IN OU T p a ra m ete r

1303

Us er-de fi ne d e xce pti on

1304

Curs or i s a lrea dy opened

1305

Return type is inva li d

1306

Return type mi sma tch

1307

Uns upporte d da ta type is us ed

1308

Il lega l s ingl e a ss i gnment

1309

I nva li d us e of ta bl e va ri abl e

1310

Sca la r type is not a ll owe d

1311

Out pa ra me te r i s not s pe ci fi ed

1312

At mo st one ou tpu t pa ra me te r i s a ll owe d

1313

O utp ut p a ra m ete r s h ou l d b e a ta b l e o r a ta b l e va ri a b le

1 314

I n a p pro p ri a t e va ri a b l e n a me : d o no t a l l o w "" fo r t he n a me o f va r i a bl e o r p a ra m e te r

1315

R e tu rn re s ul t s et fro m se l e ct s tm t e xi s t wh e n re s ul t vi e w i s de fi n ed

1316

Some out ta bl e va r i s not as si gne d

1317

Fu ncti on na me exce ed es ma x. l i mi t

1318

Bui lt-i n functi on not de fi ne d

1319

Pa ra me te r mus t be a ta bl e na me

1320

Pa ra m et er mu s t b e a n at tri b ute n a me w i th ou t a t a bl e n a me u pf ro nt

1321

Pa ra m et er m us t b e a n a ttri b ute na m e w it ho ut a n a l i a s

1322

CE_CALC not a ll owe d

1323

Pa ra m et er m us t be a ve ct or o f co l um ns o r a gg re ga ti o ns

1324

Jo in a ttri bu te m us t be a va i la bl e i n pro je cti on l i st

1325

Pa ra m et er m us t b e a ve ct or o f S QL i d en ti fi e rs

1326

Dupl ica te a ttri bute na me

1327

Pa ra me te r h as a non sup porte d typ e

1328

Attri bute no t fo und i n col umn ta bl e

1329

Dupl ica te col umn na me

1330

S yn ta x Erro r fo r ca l cu la te d Attri bu te

1331

Synta x Error in fi lte r e xpre ss i on

1332

Pa ra me te r m us t b e a va l i d co lu mn ta bl e n am e

1333

Jo in a ttri bu te s n ot fo un d i n va ri a bl e

1334

I np ut pa ra me te rs d o n ot ha ve th e sa me ta bl e typ e

156

2012-08-21


1335

Cycl i c d e pe n de n cy f ou nd i n a ru nti m e p ro ce d ure

1 336

U ne xp e ct ed i nt e rn a l e xce p ti o n ca u gh t i n a ru n ti m e p ro ce d ure

1337

Va ri a bl e de pe nds o n a n una s si gne d va ri a bl e

1338

CE_CONVER SI ON : cu sto mi zi ng ta bl e m is s i ng

1339

Too ma ny pa ra meters

1340

Th e d ep th of th e n es te d ca ll i s too de ep

1536

Swa px e rror

1537

Thi s ta bl e ha s no s wa p s pa ce

1538

Swa p a lrea dy a cti va te d

1539

Swa p not ye t a cti va ted

1540

Swa p s pa ce is not cre ate d

1541

Fa il ure in unpi nni ng a swa p pa ge

1542

Fa i l ure i n s wa p fi l e

1543

Fa il ure i n a cce ss ing swa p da ta fi le

1544

Fa il ure in acce ss ing s wa p l og fi le

1545

Swa p buffer overfl ow

1546

Swa p bu ffe r re se rva ti on fa il ure

1792

Sha re d memory error

1793

Inva li d key or i nva li d s ize

1794

The s egme nt a lre ady e xi sts

1795

Exce e d th e s ys te m-wi de l i mi t o n s ha re d me mo ry

1 796

N o s e g me n t e xi s t s fo r t he g i ve n ke y, a n d I PC_ CR EA T w a s no t s p e ci f i e d

1797

Th e u se r do e s no t h a ve p e rm i ss i o n to a cce s s th e s ha re d me mo ry s eg me n t

1798

N o m em ory co ul d b e a l l o ca te d f or s e gm en t o ve rh e a d

1799

Inva l i d s hmi d

1800

Al low re ad a cce ss for s hmi d

1801

Sh mi d p oi nts to a re move d i de nti fi er

1802

Th e e ff ecti ve u s er ID o f th e ca l l i ng pro ce s s i s n ot th e cre a to r

1803

Th e GI D or UI D va l u e i s to o l arg e to b e s to re d i n th e s tru ct ure

1804

Th e u se r do e s no t h a ve p e rm i ss i o n to a cce s s th e s ha re d me mo ry s eg me n t

1805

Inva l i d s hmi d

1 806

N o m e mo ry co u l d b e a l l o ca t e d f or t he d e s cri p to r o r f or t he p a ge ta b l e s

1807

Unknown sha re d me mory e rror

2048

Col umn s tore e rror

2049

Pri ma ry k ey i s no t s pe ci fi e d fo r co lu mn ta bl e

2050

N ot s up po rte d d dl typ e fo r co lu mn ta bl e

2051

N ot s up po rte d d ata typ e fo r co lu mn ta bl e

2052

N ot s up po rte d d ml typ e f or co lu mn ta bl e

2053

I nva l i d re tu rn ed va l ue f ro m attri bu te e ngi ne

2304

Python DBAPI error

2305

Interfa ce fa i l ure

2306

Progra mmi ng mi s ta ke

2307

Inva li d que ry pa ra me ter

2308

Not supp orte d e ncodi ng for stri ng

2560

Di stri bute d me ta da ta error

2561

DDL redi rect error

2562

DDL noti fi ca ti on e rror

2563

DDL i nva li d conta iner ID

2564

DDL i nva l i d i ndex ID

2565

D is tri bu te d e nvi ro nme nt e rror

2566

Network error

2567

Me ta da ta u pda te n ot sup po rte d in s la ve

2568

Me ta da ta u pd ate o f m as te r i nd exs e rve r i s fa i l ed

2816

Sql Scri pt Error

2817

Sql Scri pt Bui lti n Functi on

2818 2889

SqlScript

3584

Di stri buted SQL error

157

2012-08-21

Report "hana_sql_en"

Your name

Email

Reason

Description

Copyright © 2025 IDOC.TIPS. All rights reserved.
About Us | Privacy Policy | Terms of Service | Copyright | Contact Us | Cookie Policy

Sign In

Email

Password

Remember me Forgot password?

Our partners will collect data and use cookies for ad personalization and measurement. Learn how we and our ad partner Google, collect and use data. Agree & close

hana_sql_en

Recommend Documents