VENKAT SAP HANA

An operator is a special character used primarily in SQL statement's with WHERE clause to perform operation, such as comparisons and arithmetic operations. They are used to pass conditions in a SQL query.

Operator types given below can be used in SQL statements in HANA −

Arithmetic Operators
Comparison/Relational Operators
Logical Operators
Set Operators

Arithmetic Operators

Arithmetic operators are used to perform simple calculation functions like addition, subtraction, multiplication, division and percentage.

Operator	Description
+	Addition − Adds values on either side of the operator
-	Subtraction − Subtracts right hand operand from left hand operand
*	Multiplication − Multiplies values on either side of the operator
/	Division − Divides left hand operand by right hand operand
%	Modulus − Divides left hand operand by right hand operand and returns remainder

Comparison Operators

Comparison operators are used to compare the values in SQL statement.

Operator	Description
=	Checks if the values of two operands are equal or not, if yes then condition becomes true.
!=	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.
<>	Checks if the values of two operands are equal or not, if values are not equal then condition becomes true.
>	Checks if the value of left operand is greater than the value of right operand, if yes then condition becomes true.
<	Checks if the value of left operand is less than the value of right operand, if yes then condition becomes true.
>=	Checks if the value of left operand is greater than or equal to the value of right operand, if yes then condition becomes true.
<=	Checks if the value of left operand is less than or equal to the value of right operand, if yes then condition becomes true.
!<	Checks if the value of left operand is not less than the value of right operand, if yes then condition becomes true.
!>	Checks if the value of left operand is not greater than the value of right operand, if yes then condition becomes true.

Logical operators

Logical operators are used to pass multiple conditions in SQL statement or are used to manipulate the results of conditions.

Operator	Description
ALL	The ALL Operator is used to compare a value to all values in another value set.
AND	The AND operator allows the existence of multiple conditions in an SQL statement's WHERE clause.
ANY	The ANY operator is used to compare a value to any applicable value in the list according to the condition.
BETWEEN	The BETWEEN operator is used to search for values that are within a set of values, given the minimum value and the maximum value.
EXISTS	The EXISTS operator is used to search for the presence of a row in a specified table that meets certain criteria.
IN	The IN operator is used to compare a value to a list of literal values that have been specified.
LIKE	The LIKE operator is used to compare a value to similar values using wildcard operators.
NOT	The NOT operator reverses the meaning of the logical operator with which it is used. Eg − NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.
OR	The OR operator is used to compare multiple conditions in an SQL statement's WHERE clause.
IS NULL	The NULL operator is used to compare a value with a NULL value.
UNIQUE	The UNIQUE operator searches every row of a specified table for uniqueness (no duplicates).

Set Operators

Set operators are used to combine results of two queries into a single result. Data type should be same for both the tables.

UNION − It combines the results of two or more Select statements. However it will eliminate duplicate rows.
UNION ALL − This operator is similar to Union but it also shows the duplicate rows.
INTERSECT − Intersect operation is used to combine the two SELECT statements, and it returns the records, which are common from both SELECT statements. In case of Intersect, the number of columns and datatype must be same in both the tables.
MINUS − Minus operation combines result of two SELECT statements and return only those results, which belong to first set of result and eliminate the rows in second statement from the output of first.

You can create row or Column store tables in SAP HANA using create table option. A table can be created by executing a data definition create table statement or using graphical option in HANA studio.

When you create a table, you also need to define attributes inside it.

SQL statement to create a table in HANA Studio SQL Console −

Create column Table TEST (
   ID INTEGER,
   NAME VARCHAR(10),
   PRIMARY KEY (ID)
);

Creating a table in HANA studio using GUI option −

When you create a table, you need to define the names of columns and SQL data types. The Dimension field tells the length of value and the Key option to define it as primary key.

SAP HANA supports the following data types in a table −

SAP HANA supports 7 categories of SQL data types and it depends on the type of data you have to store in a column.

Numeric
Character/ String
Boolean
Date Time
Binary
Large Objects
Multi-Valued

The following table gives the list of data types in each category −

Date Time

These data types are used to store date and time in a table in HANA database.

DATE − data type consists of year, month and day information to represent a date value in a column. Default format for a Date data type is YYYY-MM-DD.
TIME − data type consists of hours, minutes, and seconds value in a table in HANA database. Default format for Time data type is HH: MI: SS.
SECOND DATE − data type consists of year, month, day, hour, minute, second value in a table in HANA database. Default format for SECONDDATE data type is YYYY-MM-DD HH:MM:SS.
TIMESTAMP − data type consists of date and time information in a table in HANA database. Default format for TIMESTAMP data type is YYYY-MM-DD HH:MM:SS:FFn, where FFn represents fraction of second.

Numeric

TinyINT − stores 8 bit unsigned integer. Min value: 0 and max value: 255
SMALLINT − stores 16 bit signed integer. Min value: -32,768 and max value: 32,767
Integer − stores 32 bit signed integer. Min value: -2,147,483,648 and max value: 2,147,483,648
BIGINT − stores 64 bit signed integer. Min value: -9,223,372,036,854,775,808 and max value: 9,223,372,036,854,775,808
SMALL − Decimal and Decimal: Min value: -10^38 +1 and max value: 10^38 -1
REAL − Min Value:-3.40E + 38 and max value: 3.40E + 38
DOUBLE − stores 64 bit floating point number. Min value: -1.7976931348623157E308 and max value: 1.7976931348623157E308

Boolean

Boolean data types stores Boolean value, which are TRUE, FALSE

Character

Varchar − maximum of 8000 characters.
Nvarchar − maximum length of 4000 characters
ALPHANUM − stores alphanumeric characters. Value for an integer is between 1 to 127.
SHORTTEXT − stores variable length character string which supports text search features and string search features.

Binary

Binary types are used to store bytes of binary data.

VARBINARY − stores binary data in bytes. Max integer length is between 1 and 5000.

Large Objects

LARGEOBJECTS are used to store a large amount of data such as text documents and images.

NCLOB − stores large UNICODE character object.
BLOB − stores large amount of Binary data.
CLOB − stores large amount of ASCII character data.
TEXT − it enables text search features. This data type can be defined for only column tables and not for row store tables.
BINTEXT − supports text search features but it is possible to insert binary data.

Multivalued

Multivalued data types are used to store collection of values with same data type.

Array

Arrays store collections of value with the same data type. They can also contain null values.

SQL stands for Structured Query Language.

It is a standardized language for communicating with a database. SQL is used to retrieve the data, store or manipulate the data in the database.

SQL statements perform the following functions −

Data definition and manipulation
System management
Session management
Transaction management
Schema definition and manipulation

The set of SQL extensions, which allow developers to push data into database, is called SQL scripts.

Data Manipulation Language (DML)

DML statements are used for managing data within schema objects. Some examples −

SELECT − retrieve data from the database
INSERT − insert data into a table
UPDATE − updates existing data within a table

Data Definition Language (DDL)

DDL statements are used to define the database structure or schema. Some examples −

CREATE − to create objects in the database
ALTER − alters the structure of the database
DROP − delete objects from the database

Data Control Language (DCL)

Some examples of DCL statements are −

GRANT − gives user's access privileges to database
REVOKE − withdraw access privileges given with the GRANT command

Why do We Need SQL?

When we create Information Views in SAP HANA Modeler, we are creating it on top of some OLTP applications. All these in back end run on SQL. Database understands only this language.

To do a testing if our report will meet the business requirement we have to run SQL statement in database if Output is according to the requirement.

HANA Calculation views can be created in two ways - Graphical or using SQL script. When we create more complex Calculation views, then we might have to use direct SQL scripts.

How to open SQL console in HANA Studio?

Select the HANA system and click on SQL console option in system view. You can also open SQL console by right click on Catalog tab or any on any Schema name.

SAP HANA can act both as Relational as well as OLAP database. When we use BW on HANA, then we create cubes in BW and HANA, which act as relational database and always produce a SQL Statement. However, when we directly access HANA views using OLAP connection, then it will act as OLAP database and MDX will be generated.

The SAP HANA system logs all the transactions that change application data or the database catalog in log entries and stores them in log area. It uses these log entries in log area to roll back or repeat SQL statements. The log files are available in HANA system and can be accessed via HANA studio on Diagnosis files page under Administrator editor.

During a log backup process, only the actual data of the log segments is written from the log area to service-specific log backup files or to a third-party backup tool.

After a system failure, you may need to redo log entries from log backups to restore the database to the desired state.

If a database service with persistence stops, it is important to ensure that it is restarted, otherwise recovery will be possible only to a point before service is stopped.

Configuring Log backup Timeout

The log backup timeout determines the interval at which the log segments are backed up if a commit has taken place in this interval. You can configure the log backup timeout using the Backup Console in SAP HANA studio −

You can also configure the log_backup_timeout_s interval in the global.ini configuration file.

The log backup to the “File” and backup mode “NORMAL” are the default settings for the automatic log backup function after installation of SAP HANA system. Automatic log backup only works if at least one complete data backup has been performed.

Once the first complete data backup has been performed, the automatic log backup function is active. SAP HANA studio can be used to enable/disable the automatic log backup function. It is recommended to keep automatic log backup enabled otherwise log area will continue to fill. A full log area can result a database freeze in HANA system.

You can also change the enable_auto_log_backup parameter in the persistence section of the global.ini configuration file.

SAP HANA provides mechanism for business continuity and disaster recovery for system faults and software errors. High availability in HANA system defines set of practices that helps to achieve business continuity in case of disaster like power failures in data centers, natural disasters like fire, flood, etc. or any hardware failures.

SAP HANA high availability provides fault tolerance and ability of system to resume system operations after an outage with minimum business loss.

The following illustration shows the phases of high availability in HANA system −

First phase is being prepared for the fault. A fault can be detected automatically or by an administrative action. Data is backed up and stand by systems take over the operations. A recovery process is put in action includes repair of faulty system and original system to be restored to previous configuration.

To achieve high availability in HANA system, key is the inclusion of extra components, which are not necessary to function and use in case of failure of other components. It includes hardware redundancy, network redundancy and data center redundancy. SAP HANA provides several levels of hardware and software redundancies as below −

HANA System Hardware Redundancy

SAP HANA appliance vendors offer multiple layers of redundant hardware, software and network components, such as redundant power supplies and fans, error-correcting memories, fully redundant network switches and routers, and uninterrupted power supply (UPS). Disk storage system guarantees writing even in the presence of power failure and use striping and mirroring features to provide redundancy for automatic recovery from disk failures.

SAP HANA Software Redundancy

SAP HANA is based on SUSE Linux Enterprise 11 for SAP and includes security pre-configurations.

SAP HANA system software includes a watchdog function, which automatically restarts configured services (index server, name server, and so on), in case of detected stoppage (killed or crashed).

SAP HANA Persistence Redundancy

SAP HANA provides persistence of transaction logs, savepoints and snapshots to support system restart and recovery from failures, with minimal delay and without loss of data.

HANA System Standby and Failover

SAP HANA system involves separate standby hosts that are used for failover, in case of failure of the primary system. This improves the availability of HANA system by reducing the recovery time from an outage.

SAP HANA backup and recovery is used to perform HANA system backups and recovery of system in case of any database failure.

Overview Tab

It tells the status of currently running data backup and last successful data backup.

Backup now option can be used to run data backup wizard.

Configuration Tab

It tells about the Backup interval settings, file based data backup settings and log based data backup setting.

Backup Interval Settings

Backint settings give an option to use third party tool for data and log back up with configuration of backing agent.

Configure the connection to a third-party backup tool by specifying a parameter file for the Backint agent.

File and Log Based Data Backup Settings

File based data backup setting tells the folder where you want to save the data backup on HANA system. You can change your backup folder.

You can also limit the size of data backup files. If system data backup exceeds this set file size, it will split across the multiple files.

Log backup settings tell the destination folder where you want to save log backup on external server. You can choose a destination type for log backup

File − ensures that sufficient space in system to store backups

Backint − is special named pipe exists on file system but require no disk space.

You can choose backup interval from drop down. It tells the longest amount of time that can pass before a new log backup is written. Backup Interval: It can be in seconds, minutes or hours.

Enable Automatic log backup option: It helps you to keep log area vacant. If you disable this log area will continue to fill and that can result database to hang.

Open Backup Wizard − to run the backup of system.

Backup wizard is used to specify backup settings. It tells the Backup type, destination Type, Backup Destination folder, Backup prefix, size of backup, etc.

When you click on next → Review Backup settings → Finish

It runs the system backups and tells the time to complete backup for the each server.

Recovery of HANA System

To recover SAP HANA database, the database needs to be shut down. Hence, during recovery, end users or SAP applications cannot access the database.

Recovery of SAP HANA database is required in the following situations −

A disk in the data area is unusable or disk in the log area is unusable.
As a consequence of a logical error, the database needs to be reset to its state at a particular point in time.
You want to create a copy of the database.

How to recover a HANA system?

Choose HANA system → Right click → Back and Recovery → Recover System

Types of recovery in HANA system

Most Recent State − Used for recovering the database to the time as close as possible to the current time. For this recovery, the data backup and log backup have to be available since last data backup and log area are required to perform the above type recovery.

Point in Time − Used for recovering the database to the specific point in time. For this recovery, the data backup and log backup have to be available since last data backup and log area are required to perform the above type recovery

Specific Data Backup − Used for recovering the database to a specified data backup. Specific data backup is required for the above type of recovery option.

Specific Log Position − This recovery type is an advanced option that can be used in exceptional cases where a previous recovery failed.

Note − To run recovery wizard you should have administrator privileges on HANA system.

STRATEGY & CREATIVITY

PORTFOLIO

Arithmetic Operators

Comparison Operators

Logical operators

Set Operators

Date Time

Numeric

Boolean

Character

Binary

Large Objects

Multivalued

Array

Data Manipulation Language (DML)

Data Definition Language (DDL)

Data Control Language (DCL)

Why do We Need SQL?

How to open SQL console in HANA Studio?

Configuring Log backup Timeout

HANA System Hardware Redundancy

SAP HANA Software Redundancy

SAP HANA Persistence Redundancy

HANA System Standby and Failover

Overview Tab

Configuration Tab

Backup Interval Settings

File and Log Based Data Backup Settings

Recovery of HANA System

How to recover a HANA system?

Types of recovery in HANA system

Subscribe to Our Newsletter...!

Content

Search This Blog

Total Pageviews

Author Profile

Labels

Social Plugin

Popular Posts

Comments

Breaking Ticker

CONTENT

SAP HANA Introduction

SAP HANA Sequrity

SAP HANA Data Replication

SAP HANA Monitoring

SAP HANA Modeling

SAP HANA Reporting

CONTACT US

VENKAT SAP HANA