Experience of my first SAP HANA project

INTRODUCTION

This blog is related to few of  my experience working on  SAP HANA. It has the problems that I faced during a migration project (from Oracle PL/SQL to SAP HANA SQL Script) , in which I am working on and the possible solutions for those problems.

The solutions that are explained in here may be helpful for developers who are facing similar kinds of problems. This blog will be useful for the beginners who are working on HANA development.

Usage of RESULTVIEW

Problem:

Oracle materialized view has to be converted to HANA view, So I tried to create RESULTVIEW in a procedure. I found that creation of RESULTVIEW in HANA Studio Project could not be transported via the repository.

Solution:

• RESULTVIEW is useful while creating in console (to create a runtime view of data)  but it is not transportable via the repository.
• So as an alternative I created a procedure with OUT parameter and used it to retrieve the result set.

Changing the preferences in HANA studio

Problem:

Change the number of results shown after executing a query in SQLConsole.

Solution:

• To get more than 1000 rows of a table as result of a select query I navigated to
• WINDOW->preferences->SAP HANA->Runtime->Result  and typed the max number of rows to be displayed.

Problem: 

To change the delimiter while exporting the result (data) of a query or changing the NULL representation character or any other settings related to the data export.

Solution:

To change the export settings such as DELIMIETER (Data separator) , NULL value representation , etc.

Go to    WINDOW -> PREFERENCES -> SAP HANA ->RUNTIME -> COMMON

Table types in project:

Problem:

I tried to create a table type in my project as I created in console but couldn’t be transported via repository.

Solution:

Creating table types in console is different from creating  them in a project .

While creating a table type in project HANA expects a .hdbdd file to be created and appropriate artifacts to be mapped with each of the table type columns.

Also the artifact is case sensitive. Here, I have mentioned the context as follows

Context abcd{

Type STR25 : String(25);

};

But , when creating the table type I created as

TYPE TTT{

       “coLUM” : str25;

       };

Which gives me the error (since str is in lower case):

When I assign the column to the table type , I should use coLUM (case sensitive)

Look at the following code snippet:

create procedure sample(OUT abc “SRIVATSAN”.“SRI.DEV.DDL::ABCD.TTT“)

as

begin

DECLARE colum varchar(25);

colum := ‘hello’;

abc = select colum from dummy;

end;

When I execute this , I get the error as follows :

SAP DBTech JDBC: [1306] (at 138): return type mismatch: Attribute name “coLUM”  different from Attribute name: “COLUM” : line 7 col 1 (at pos 138).

This shows that the column name should match exactly the way it is defined in .hdbdd file.

create procedure sample(OUT abc “SRIVATSAN”.“SRI.DEV.DDL::ABCD.TTT“)

as

begin

DECLARE colum varchar(25);

colum := ‘hello’;

abc = select colum as “coLUM”  from dummy;

end;

This code will work since the name we assign to the column (alias) is exactly the same as .hdbdd definition.

Using Row store tables in modeling:

Problem :

When creating modeling views, I tried to use a table (of ROW type) which I couldn’t accomplish.

Solution:

• It is not possible to use ROW tables (Created as Row type) in modeling views. Only columnar tables can be used.
• When I tried to drag and drop a ROW store table to the data foundation, it doesn’t allow.
• Also when I right click the data foundation and choose Add Objects , It shows only the tables which are of COLUMN type.

Exception handling:

Problem:

For few error codes in HANA, I was unable to retrieve the error message using ::SQL_ERROR_MESSAGE.

Solution:

For few error codes (::SQL_ERROR_CODE), HANA doesn’t return the  :: SQL_ERROR_MESSAGE  (returns a blank as error description),

so to get the DESCRIPTION , I used the SYSTEM table M_ERROR_CODES from SYS schema (SYS.M_ERROR_CODES) by passing the error code to the column CODE and getting the DESCRIPTION column’s value.

Here is a sample code ,

PROCEDURE “SRI.DEV.LIB::P_LOG_ERROR”

                                        (IN IN_ERROR_CODE INTEGER,IN IN_OBJECT NVARCHAR(100),IN IN_ERROR_MSG NVARCHAR(2000))

       LANGUAGE SQLSCRIPT   SQL SECURITY INVOKER

       AS  BEGIN

       DECLARE V_NEXTVAL INTEGER;

       DECLARE V_ERROR_MSG NVARCHAR(2000);

       SELECT “SRIVATSAN”.“SRI.DEV.DDL::SEQ_ERRLOG”.NEXTVAL

       INTO   V_NEXTVAL

       FROM DUMMY;

              V_ERROR_MSG := IN_ERROR_MSG;

       IF V_ERROR_MSG = ” THEN

              SELECT DESCRIPTION INTO :V_ERROR_MSG

               FROM SYS.M_ERROR_CODES WHERE CODE = :IN_ERROR_CODE;

       END IF;

       INSERT INTO “SRIVATSAN”.“SRI.DEV.DDL::SCP_ERRORLOG”

               VALUES (:V_NEXTVAL,

                            CURRENT_UTCTIMESTAMP,

                            IN_ERROR_CODE,

                            IN_OBJECT,

                            :V_ERROR_MSG,

                            CURRENT_USER);

     END;

This exception handling procedure will be called in all the main procedures with the 3 parameters

(::SQL_ERROR_CODE , ‘<Name of the object that caused the exception>’,::SQL_ERROR_MESSAGE) so as to insert the error details in a table .

Importing CSV file to SAP HNA table:

Problem:

I faced issues in importing CSV files to HANA tables. I was getting errors due to  formatting issues of date and time.

Solution:

• While importing a CSV file to HANA table, I ensure that the DATE data type is of the format YYYY-MM-DD and the Timestamp has YYYY-MM-DD HH:MM:SS.
• To do this I opened the CSV file in MS Excel, selected all the columns with data/time, right clicked and chose Format Cell and chose Custom option to type the exact format of the column (Date / time / Date Time).
• Saved  the excel file and then I started importing the file to table which worked fine.

Altering the table columns:

Problem:

I tried to change the data type of a column (ROW TYPE table) , which was not supported.

Solution:

Only tables created as Column store allows altering the data type of columns (Eg., When a column with DATE is being altered to TIMESTAMP , that is not allowed if the table is created as ROWstore)

Look at the following row table,

create row table rowtype (col1 integer,col2 date);

Here, I have created a row store table called “rowtype” with two columns namely col1 as integer type and col2 as date type.

Now, I try to alter the table to change the data type of col2 from date to Timestamp ,

Alter table rowtype alter (col2 timestamp)

I got the error as follows:

Could not execute ‘Alter table rowtype alter (col2 timestamp)’ in 220 ms 781 µs .

SAP DBTech JDBC: [7] (at 27): feature not supported: cannot modify data type of column: COL2: line 1 col 28 (at pos 27)

Now , the same is tried with the column type table ,

create column table coltype(col1 integer,col2 date);

Altering the column data type :

Alter table coltype alter (col2 timestamp)

Statement ‘Alter table coltype alter (col2 timestamp)’

successfully executed in 236 ms 633 µs  (server processing time: 15 ms 169 µs) – Rows Affected: 0

Whereas increasing the length of a column (same datatype) is possible in both row and column types.

create  row table rowtype(col1 varchar2(20),col2 date);

Alter table rowtype alter (col1 varchar(40))

Statement ‘Alter table rowtype alter (col1 varchar(40))’

successfully executed in 278 ms 609 µs  (server processing time: 4 ms 254 µs) – Rows Affected: 0