1 2 3 61 Previous Next

SAP HANA and In-Memory Computing

904 Posts

Hello Experts,


This blog is about one of the feature that SAP HANA provides, FUZZY SEARCH.


Now the question arises, what is Fuzzy search?!... So, Fuzzy search is the technique of finding strings that match a pattern approximately (rather than exactly). It is a type of search that will find matches even when users misspell words or enter only partial words for the search. It is also known as approximate string matching.


According to Fuzzy Search Reference guide, Fuzzy Search is a fast and fault-tolerant search feature for SAP HANA. The term “fault-tolerant search” means that a database query returns records even if the search term (the user input) contains additional or missing characters, or other types of spelling error.


Fuzzy search can be used in various applications, like:

  • Fault-tolerant check for Misspelled words and typos
  • Fault-tolerant search in text columns
  • Fault-tolerant search in structured database content
  • Fault-tolerant check for duplicate records

 

The best real world example of such fault-tolerant search is when you type “The United States of Amerika” in the Google Search, it automatically displays result for “The United States of America”.

 

In SAP HANA, Fuzzy Search can be called by using the CONTAINS() predicate with the FUZZY() option in the WHERE clause of a SELECT statement.

 

The basic SYNTAX is:

       

SELECT * FROM <tablename> WHERE CONTAINS (<column_name>, <search_string>, FUZZY (x))


Where, x is an argument that defines fuzzy threshold. It ranges from 0.0 to 1.0 and defines the level of error tolerance for the search. A search with FUZZY(x) returns all values that have a fuzzy score greater than or equal to x.

 

Fuzzy Search can only be applied for:

  • Column Table
  • Attribute View
  • Also on SQL views (created with the CREATE VIEW statement), and on joins of multiple tables and views, in some cases

       

          having column types as:

    • String (VARCHAR, NVARCHAR)
    • Text (TEXT, SHORTTEXT, FULLTEXT INDEX)
    • DATE

 

The CONTAINS() predicate can be used in the WHERE clause of a SELECT statement. It performs:

  1. A free style search on multiple columns
  2. A full-text search on one column containing large documents
  3. A search on one database column containing structured data

 

The type of search it performs depends on its arguments.

 

 

The SCORE() Function

 

The fuzzy search algorithm calculates a fuzzy score for each comparison, the SCORE() function can be used to retrieve the score. This is a numeric value between 0.0 and 1.0.

 

The score defines the similarity between the user input and the records returned by the search. A score of 1.0 means the strings are identical. A score of 0.0 means that there is no similarity. The higher the score, the more similar a record is to the search input.

 

We can request the score in the SELECT statement by using the SCORE() function. You can sort the results of a query by score in descending order to get the best records first (the best record is the record that is most similar to the user input). When more than one CONTAINS() is given in the WHERE clause or multiple columns is used in a SELECT statement, the score is calculated as a weighted average of the scores of all columns.

 

For example, consider we have a column table with two fields (ID integer, TXT TEXT) having values like different variations of word ‘hello world’. Then the Fuzzy Search for word ‘hello’ with Score will return the following:

 

SELECT TO_DECIMAL(SCORE(),3,2) AS score, * FROM <table_name> WHERE CONTAINS(txt, 'Hello', FUZZY(0.8))
ORDER BY score DESC;

1.png

Here, the words ‘hello’ and ‘Hello’ are having the score as 1, since the string matches completely. Whereas, word ‘ello’ is having the lowest score.


We can specify additional search options that change the default behavior of the fuzzy search as an additional string parameter for the FUZZY() function.

 

There are so many possible combinations of search options available. Lets try out combination of FUZZY() with similarCalculationMode.

 

Step 1. Create 1 column table as:

 

create column table <table_name>(
ID integer,
TXT varchar(20));


Step 2. Run following commands to Insert values into the table:


insert into <table_name> values(1,'hello');
insert into <table_name> values(3,'hell');
insert into <table_name> values(4,'hel');
insert into <table_name> values(5,'ello');
insert into <table_name> values(7,'hello world');
insert into <table_name> values(8,'hell world');
insert into <table_name> values(14,'helloworld');
insert into <table_name> values(15,'hellworld');
insert into <table_name> values(16,'HelloWorld');
insert into <table_name> values(17,'HELLO');
insert into <table_name> values(21,'world');
insert into <table_name> values(22,'word');


Step 3. Perform string search with option similarCalculationMode


SELECT TO_DECIMAL(SCORE(),3,2) AS score, * FROM <table_name>
WHERE CONTAINS(txt, 'Hello', FUZZY(0.8,'similarCalculationMode=compare'))
ORDER BY score DESC;

    

     We will get the output as:

     2.png

     Here, the FUZZY() compares all the strings in the table with the search string and gives the best matching results having SCORE() greater than 0.8

 

SELECT TO_DECIMAL(SCORE(),3,2) AS score, * FROM <table_name>
WHERE CONTAINS(txt, 'Hello', FUZZY(0.8,'similarCalculationMode=search'))
ORDER BY score DESC;

    

     We will get the output as:

     3.png

     Here, the FUZZY() searches all the strings in the table with the search string and gives the best matching results having SCORE() greater than 0.8. Notice the difference between search and compare here. As the part of result it also includes the strings composed of two words.

 

SELECT TO_DECIMAL(SCORE(),3,2) AS score, * FROM <table_name>
WHERE CONTAINS(txt, 'Hello', FUZZY(0.8,'similarCalculationMode=substringsearch'))
ORDER BY score DESC;

    

     We will get the output as:

     4.png

     Here, the FUZZY() searches all the strings in the table having substring ‘hello’ as the search string and gives the best matching results having SCORE() greater than 0.8.


Similarly, we can try other Available Properties of FUZZY SEARCH mentioned in the reference guide. Also, we can try different combinations of these properties to get the best possible result as per the requirement.

 

I hope you liked my first blog.

Happy Learning!

 

Thanks,

Pragati Gupta

This Blog is 'To start with' page for SAP Solution Integration with HADOOP.

 

The strength of HADOOP is it can store very huge amount of data of various formats, good for manipulating very large files and is tolerant to hardware and software failures, the drawback it can not deliver that in real time. This is where power of HANA kicks in, SAP HANA with its In-memory technology is best suited for processing data in Real time and fast. Hence SAP HANA and Hadoop should be a perfect match.


(This Blog will be updated regularly don't forget to Bookmark)

 

SAP :

.


HADOOP :



                                                                                     Reference Architecture


Capture.GIF

Purpose

 

The purpose of this post is show how simple is to a complete BW on HANA installation.

This is the first of four posts that will help user to complete the installation and BW activation.

Overview

 

This is the first post and is focus on the BW installation on a Linux OS.

The OS choice was according to the available resources.

Supported OS information can be find at the following link:

 

SAP NetWeaver Technology Platform

Technology – SAP Help Portal Page

 

 

Step by Step

 

To start the HANA database installation you have to download the installation files as per below steps:

> Installations and Upgrades
       >> Browse our Download Catalog
>>> SAP IN-MEMORY (SAP HANA )
>>>> SAP In-Memory (SAP HANA )
>>>>> HANA Platform Edition
>>>>>> SAP HANA PLATFORM EDITION
>>>>>>> SAP HANA PLATFORM EDIT. 1.0
>>>>>>>> Installation 
01.png

 

The installation files are stored in a folder defined by the user

In below example the folder containing the files will be:


tmp/HANA090/095/server/linuxx86_64/SAP_HANA_DATABASE
That is the folder where the installation files were extracted.

  2.png


To start the installation you have to execute the file mentioned below:

hdblcmgui
(Linux command ./hdblcmgui)
After the execution, the below screen will be open:
3.png
Click 'Next' to continue the installation.
4.png
Click on 'Next' button
5.png
Select the first option 'Install SAP HANA Database Client Verison...'
Click on 'Next' button
6.png
On that screen select option 'Multiple'. this option is selected because on that example the installation will be done in 4 hosts (the hosts will be: from DEWDFLHANA2327 to DEWDFLHANA2331)
Click on 'Next' button
7.png
On that screen you add the host. You can add all hosts at this time,but it is recommended to install one host per time. If any issue occurs during the host installation, the initial installation will not be lost.

 

In below link you can check the steps for adding hosts after the initial installation:

 

Easy Installation Guide BW on HANA - 2/4
8.png
After adding the host, click on Next button.
9.png
Click on Next button
10.png
On that screen you definy the 'System ID' for the database.
Select option 'single_container' as Database Mode.
Click on Next button
11.png
On that screen you definy the store location for Data and Logs.
Click on Next button

Note: You can check on operational system how the folders were created12.png
13.png
Click on Next button
14.png
The information on that screen are fulfilled already, since the definition was made on previous screens
Click on Next button

 


15.png
Click on Next button
16.png
Click on Install button
17.png
You can follow the installation on that screen
In the end of installation the below screen is displayed:
18.png
Click on Finish button.


Check the entire guide by links below:

Purpose

 

The purpose of this post is show how simple is to a complete BW on HANA installation.

This is the second of four posts that will help user to complete the installation and BW activation.

Overview

 

This is the second post and is focus on the BW installation on a Linux OS.

The OS choice was according to the available resources.

Supported OS information can be find at the following link:

 

SAP NetWeaver Technology Platform

Technology – SAP Help Portal Page

 

 

Step by Step


To start the host addition run the below file from the Installation Hana Database done (Folder: /hana/shared/HA0)

 

hdblcmgui (Linux command ./hdblcmgui)

1.png

2.png

 

3.png

Select option 'Add additional Hosts to the SAP HANA System'
Click on Next button

 

4.png

Click on 'Addd Host...' button

 

5.png

Set the host name on 'Hostname' field
Click on 'OK'

 

6.png

Provide the password for user ROOT and for the Administrator user
Click on Next

 

8.png

Click on 'Add Hosts'
The belowscreeb will be displayed to following the Installation

9.png

 

When the process is finished the below screen is displayed:

10.png

Click on Finish button

 

 

Check the entire guide by links below:

Easy Installation Guide BW on HANA - 1/4

Easy Installation Guide BW on HANA - 2/4

Easy Installation Guide BW on HANA - 3/4

Easy Installation Guide BW on HANA - 4/4

The BW (Business Warehouse) on HANA installation is simple but you have some important details. Because of those important details, we created this easy installation guide BW on HANA.

 

This guide were separated on four posts to a better organization and understand.

 

Pictureen.png

 

Easy Installation Guide BW on HANA - 1/4

On the first post the HANA installation on a Linux OS is detailed.

 

Easy Installation Guide BW on HANA - 2/4

On the second post is described how to add a Host on the HANA installation.

 

Easy Installation Guide BW on HANA - 3/4

On the third post the Netweaver 740 installation on a Linux OS is described.

 

Easy Installation Guide BW on HANA - 4/4

On the fourth post the BW activation is described.

 

All the guide was developed to show how easy is to install a BW on HANA with the BW activation.

Each step is with the respective screen to facilitate the understand.

Purpose

 

The purpose of this post is show how simple is to a complete BW on HANA installation.

This is the fourth of four posts that will help user to complete the installation and BW activation.

 

Overview

 

This is the fourth post and is focus on the BW installation on a Linux OS.

The OS choice was according to the available resources.

Supported OS information can be find at the following link:

 

SAP NetWeaver Technology Platform

Technology – SAP Help Portal Page

 

 

Step by Step

 

To start the BW activation will be necessary a system access creation (on the installed system (D02)) by SAPLOGON:

 

  • On the first system access you need to access with user "SAP*" with defined password on the Netweaver installation.
    • <you will have some performance delay on each transaction at the first access. It will compile every first access.
  • Create an user to system administration. Use user "SAP*" as model.

 

Logoff to the system and Logon with your new user.

Go to transaction RSA1.

1.pngCreate.2.png3.png

Fulfill the fields "LOG.SYSTEM" and  "NAME".

 

  • LOGSYSTEM: system technical name
  • NAME: System description

 

Save.

F3.

 

One Request creation screen will appear for a request creation. This is needed because it will change some basis tables.

 

 

4.png

 

Create new request (White paper icon).


5.png

 

Fulfill the requested fields.

Save.

 

7.png

 

OK.

 

8.png

 

Assign.

 

9.png

 

Duble click on "client" row (s. 001).

 

10.png

 

11.png

 

Add the "Logical System" (s. D02CLNT001).

 

12.png

 

Only Activate.

 

13.png

OK.

 

 

Check the entire guide by links below:

Easy Installation Guide BW on HANA - 1/4

Easy Installation Guide BW on HANA - 2/4

Easy Installation Guide BW on HANA - 3/4

Easy Installation Guide BW on HANA - 4/4

Purpose

 

The purpose of this post is show how simple is to a complete BW on HANA installation.

This is the third of four posts that will help user to complete the installation and BW activation.


Overview

 

This is the third post and is focus on the BW installation on a Linux OS.

The OS choice was according to the available resources.

Supported OS information can be find at the following link:

 

SAP NetWeaver Technology Platform

Technology – SAP Help Portal Page

 

 

Step by Step

 

As described on ¼ post, the Linux OS environment was chose so some specifics commands will be need.

The first step is a Netweaver download. Follow path below to start the download:

 

> http://service.sap.com/swdc

>> Installations and Upgrades

>>> Browse or download catalog

>>>> SAP NetWeaver and complementary products

>>>>> SAP NetWeaver

>>>>>> SAP NETWEAVER 7.4

>>>>>>> Installation and Upgrade

 

After the download, you will need to locate the folder where you made the download and will execute the file nw_install:

 

./nw_install

1.png


After the execution the following screen will appear:

2.png

 

Select "STANDARD SYSTEM" as follow path:

 

3.png

Next.

 

4.png

 

Next.

 

5.png

 

Define the System ID (s. D02) and mount directory (s. /sapmnt).

Next.

 

6.png

 

Browse.

 

7.png

 

Select Kernel folder (Always select the numeric folder).

OK.

 

8.png

 

Define the neatweaver password "MASTER PASSWORD" (this password will be set authomatically on the following screens).

Next.

 

9.png

 

OK.

 

10.png

 

Define the database that you will connect your Netweaver.

Set previous defined DBSID, "host name" and instance.

Next.

 

11.png

 

Next.

 

12.png

 

Browse.

 

13.png

 

Select the database folder "client".

OK.

Next.

 

14.png

 

Browse.

 

15.png

 

Select the "Installation Export" folder.

s. In case of a  netweaver copy,you will select the copy file with all database information on this moment..

OK.

Next.

 

17.png

 

In case of a different password than netweaver, set the password here.

Next.

 

17.png

 

In case of a different password than netweaver, set the password here.

Next.

 

18.png

 

Next.

 

19.png

 

Next.

 

20.png

 

OK.

Next.

 

21.png

 

Next.

 

22.png

 

Next.

The installation will start and the installation screen will appear as below.

 

23.png

 

Installation ended with success:

 

24.png

 

OK.

 

Check the entire guide by links below:

Easy Installation Guide BW on HANA - 1/4

Easy Installation Guide BW on HANA - 2/4

Easy Installation Guide BW on HANA - 3/4

Easy Installation Guide BW on HANA - 4/4

New and Best Practices for Data Modeling with SAP HANA selected as TechEd Lecture of the Week

 

There is no better proof of the value to customers of a key SAP HANA capability as getting featured as ‘Lecture of the Week’ for the week of August 24th 2015, of course based on your interest on the SCN lecture replays as well as your presence and interest at TechEd on the floor!

Modeling SAP HANA Views as virtual data flows for operational reporting as well as analytic scenarios is a key approach to enable the real-time flavor of your application with SAP HANA. Therefore you need to be up-to-date on new capabilities as well as latest best practices.

 

 

In case that you missed the session or want to have a repeat, you can watch the replay below:

This lecture provides an overview and guidance from a best practice perspective about the modeling approaches in SAP HANA, while focusing on the unified editor for attribute-, analytics-, and calculation views. Further context is given about alternative approaches as well as indications of future directions in SAP HANA modeling.

 

 

Updates and Outlook for TechEd 2015

 

Since TechEd last year SAP HANA has moved on with lots of new capabilities also in the area of SAP HANA View modeling. For details see What’s New in Modeling with SAP HANA SPS09 (doc, recording) and What’s New in Modeling with SAP HANA SPS10 (doc, recording). We have specifically highlighted the new capabilities around SQL enablement of SAP HANA hierarchies with Calculation views in this blog.

 

For TechEd 2015 we’ve again prepared a series of sessions building on the new capabilities and practices like focusing on Calculation View modeling and introducing web-based graphical modeling tools. Of course a new and best practices session will summarize the key advancements again.

Here is a session recommendation list for your planning:

Session IDSession FormatSession Title
DMM163Hands-On (2h)Introduction to Data Modeling in SAP HANA
DMM360Hands-On (2h)Advanced Data Modeling in SAP HANA
DMM208Lecture (1h)New and Best Practices for Data Modeling with SAP HANA
DMM261Hands-On (2h)Managing Hot and Warm Data with SAP HANA Dynamic Tiering
DMM264Hands-On (2h)Geospatial and Unstructured Data in SAP HANA

 

Thanks for your interest in the topic and see you at TechEd 2015!

 

Christoph

The annual SAP HANA Innovation Award is designed to recognize the innovations customers have executed over the last year on the SAP HANA platform. This year, the reach was extended to 31 countries.

Quentin Clark, CTO, SAP has announced the winners for 2015.

 

The detailed report is here. Find out the results of the awards program, including the number of submissions, the judges’ panel, and, of course, the winners. See which companies won, and read a short synopsis that details their successful SAP HANA journeys.


Congratulation to winners for their brilliant ideas and efforts.

SAP HANA, being still a young technology, is evolving and maturing. A sign of this maturity is the increased deployment options it increasingly provides. This whitepaper from EMC, will demystify SAP HANA Tailored Datacenter Integration as a concept, while presenting its business rationale and benefits, helping you to navigate through the currently available options, and showcasing why this is the option most organizations chose when transitioning from a “SAP HANA PoC phase”, to a “SAP HANA mainstream adoption phase”. It is the business case for SAP HANA Tailored Datacenter Integration as the mainstream option for on-premise SAP HANA deployments.

 

EMC is prepared to help you navigate through your IT transformation and assure your organization is “SAP HANA ready.”

 

This whitepaper is destined for enterprise architects, SAP technical architects, directors of operations, directors of IT engineering, directors of architecture, and all senior technologists involved in defining and deciding the deployment model for SAP software based on SAP HANA in their organizations.

 

EMC whitepaper : Making SAP HANA Mainstream in your Datacenter

The innovative Cisco Application Centric Infrastructure(ACI) architecture radically simplifies, optimizes, and accelerates the entire application deployment lifecycle in next-generation data centers and clouds.


Cisco Unleashes the SAP HANA ACI process. For SAP, it is next step after HANA TDI.

For Cisco, a big footstep for "Software Defined Everything":

- Policy-based applications and infrastructure

- Automated provisioning and deployment

- Continuously monitored and adapted

 

SAP, Cisco, VCE and Vnomic, a SAP Startup focus program member, highlight their latest co-innovations for delivery and governance of SAP applications on Cisco Application Centric Infrastructure (ACI) using the next-generation of policy driven software.

 

Confused? Ok, I hope following materials will be helpful for complete understanding:

Automate Deployment with Application-Centric Infrastructure

Cisco Application Centric Infrastructure Integration with Virtual Computing Environment (VCE™)

Unleashing SAP with Cisco ACI

In Quick notes during a fiddle-session I played around with a table that contained all possible values for a date column.

 

To fill it I used a FOR LOOP with an INSERT and a commit every 10000 records.

That took 9:37 minutes for 3.652.061 records.

 

That's terrible performance and nothing that needs to be accepted with SAP HANA!

 

I found a way to do the same much quicker:

 

Statement 'insert into rmanydates (select * from all_days)'

successfully executed in 6.120 seconds  (server processing time: 4.630 seconds) - Rows Affected: 3652060 ;

 

Question 1:

The dates inserted above have not been pre-computed in this example.

How do I do this?

 

--------

Answer 1


The 6,120 seconds to create every possible date once and insert it into the row table is a lot faster than the one-by-one looping approach I took in the procedure (manly for clarity purposes).

But, as Fernando Ros demonstrated, there are other options to do that.

 

The one I chose was part of a rather new feature in SAP HANA: SAP HANA Series

 

SELECT to_date(GENERATED_PERIOD_START) 
    FROM SERIES_GENERATE_TIMESTAMP('INTERVAL 1 DAY', '01.01.0001', '31.12.9999')

--------

 

I also looked at the memory consumption for the stored date tuples.

In the blog post I just used a row store table, because I thought: well, there's not going to be any column store compression anyhow.

(you know, because column store compression mainly builds on compressing duplicate column values. But with every possible date once, there are no duplicates, so no compression - or is there?)

 

However, I managed to get the data loaded into a column store table and use a lot less memory.

 

Question 2:

How to get from


BEFORE

--- General ---

Total Memory Consumption (KB): 25.719

Number of Entries: 3.652.061

Size on Disk (KB): 23.152

 

 

Memory Consumption in Main Storage (KB): 25.700

Memory Consumption in Delta Storage (KB): 19

Estimated Maximum Memory Consumption (KB): 25.719

 

to

 

AFTER

--- General ---

Total Memory Consumption (KB): 1.645

Number of Entries: 3.652.061

Size on Disk (KB): 12.912

 

Memory Consumption in Main Storage (KB): 1.626

Memory Consumption in Delta Storage (KB): 19

Estimated Maximum Memory Consumption (KB): 1.645

 

Both tables were fully loaded when the space consumption was analyzed.

 

if you know how that works, put your answers into the comments section!

 

--------

Answer 2

 

This one is again a feature from SAP HANA Series: the Series Table,


create column table smanydates (dday date) 
SERIES (  EQUIDISTANT INCREMENT BY INTERVAL 1 DAY
     MINVALUE '01.01.0001'
     MAXVALUE '31.12.9999'
     PERIOD FOR SERIES (dDAY));

 

The above CREATE TABLE statements, specifies that the timestamp information in column DDAY are not explicitly stored.

Instead the internal storage is merely a calculation formula that "knows" how every date between MIN/MAXVALUE with the granularity of 1 DAY can be computed.

Instead of storing a full date now, only a number uniquely identifying a day needs to be stored.

Given the starting date '01.01.0001' the current day would then be stored just as the integer 735.832 (20 bits).

 

Of course there are also limitations to this type of data storage but for ever increasing regular date or timestamp information, this is really an option to save a lot of memory.

 

For more information around SAP HANA series please check

--------

 

- Lars

For a while now I am working as a Solution Architect for the SAP Custom Development organisation.

This is the team you'd call when you want custom built solutions that perfectly fit into your standard solution landscape.

 

My job there is not coding or implementing systems, but rather designing Solutions and turning those designs into commercial offers.

Obviously it seems like a good idea to every now and then go and check that what I remember of doing "real work" is actually still true. and so I try to keep up with our tools and platforms as well as anyone.

 

While trying out different stuff for something that might become a blog post later on, I came across some things I considered noteworthy, "interesting" or otherwise out of the expected.

All this is based on SAP HANA rev. 101 and SAP HANA Studio 2.1.6.

 

Change of default statement preparation in SAP HANA Studio

 

In Issue: MDX Query not executing in Hana Studio 2.0.8 Version and Re: Regarding "Generate Time Data" Option in HANA folks complained about the fact that MDX statements suddenly started to return error messages even though the worked in older SAP HANA Studio versions.

The error message was:

 

SAP DBTech JDBC: [2]: general error: MDX query doesn't support prepared statement

 

I first proposed to circumvent this by creating a little wrapper procedure but this is inconvenient.

Shady Shen provided the correct hint: un-check the check box "Prepare statements before execution".

 

16-08-2015 14-25-02.png

This seem to have been set to active by default in the more recent versions of SAP HANA Studio. Once again a change that doesn't occur in any documentation or what's-new note...

 

At least it's not necessary to close/re-open the SQL editor to get this changed setting active.

 

No DEFAULT value specification in INSERT/UPDATE/UPSERT possible

 

Let's say you were diligent with your table column specification and not only provided name and data type but also a NOT NULL constraint and a DEFAULT value clause.

Something as extravagant as this:

 

create table test (id int not null default 0);


Now you want to make use of this and by following SQL standard you use the DEFAULT keyword to specify that you want to have the pre-defined default value:

 

insert into test (id) values (default);


SAP DBTech JDBC: [260]: invalid column name: DEFAULT: line 1 col 31 (at pos 30)

 

This is not to say SAP HANA doesn't support default values.

If you want to use default values, you mustn't include them in the DML statement at all.

So the following works without problems:

 

alter table test add (id2 int not null default 1);
insert into test (id) values (1);
select * from test;


IDID2
11

 

Would be lovely to find such nuances in the documentation.

 

Limits of date operations

 

When working with date data types it's real easy to forget about the limitations of this data type.

One example could be that non-existing dates like the February 30 or dates between 05.10.1582 and 14.10.1582 are rejected or mapped to the correct dates.

Another limitation I actually ran into while generating random garbage data for a date column is the smallest/largest date.

This time the documentation is clear about this: "The range of the date value is between 0001-01-01 and 9999-12-31."

 

Usually I wouldn't think much about this, but when using expressions like the following to create random dates, I hit an error:

select add_days (date'01.01.1980', rand()*10000000) from dummy;


[303]: invalid DATE, TIME or TIMESTAMP value: internal error in AddDays() at function add_days()

 

In my example the 10000000 was in fact a sequence number in a rather large data set.

So, the quick fix would be to set an upper limit for the value that is fed into add_days().

 

select add_days (date'01.01.1980', least(rand()*10000000, 3560) from dummy;


The least() function comes in real handy here at it makes sure that the largest possible value returned will be 3560 (roughly 10 years worth of days).

Together with its sister the greatest() function it's easy to create upper and lower limits for generated values.

 

But, what if we don't want to arbitrarily set the limit below what would be possible with this data type?

Easy too:

select days_between (date'31.12.9999', current_date) from dummy;


DAYS_BETWEEN(31.12.9999,CURRENT_DATE)

-2916233                         

 

Gives you the number of days between today and the end of times - huh... gotta hurry!

 

Admittedly this example is super artificial and has no connection to real-life dates.

However, it shows that there are limitations to the date data type in SQL that it's not too difficult to step into them.

There are of course other examples where a richer semantic for this data type would be useful.

 

Being able to have a MIN_DATE or MAX_DATE entry that would explicitly show up like this would be

 

Wandering off...

The date topic side-tracked me at this point and I looked into some other points, too:

I've shown the "generator query"-trick (cross join of objects system table to generate millions of rows) before.

This is of course not the only way to create a lot of rows.

 

Another option is to write SQL Script and this was the first time I used the new SPS 10 capability to run SQL script directly in the SQL console without the need for a wrapper procedure:

 

create table manydates (dday date);
do
begin
    declare i bigint; 
    for i in 0 .. 3652060 do
        insert into manydates (dday) values (add_days(date'01.01.0001', :i) );
        if (mod(i, 10000) = 0 )
        then
            commit;
        end if;
    
    end for;
end


This little gem runs for a while and fills a table with all possible date values.

 

Statement 'do begin declare i bigint; for i in 0 .. 3652060 do insert into manydates (dday) values ...'

successfully executed in 9:37.694 minutes  (server processing time: 9:37.681 minutes) - Rows Affected: 0

 

Now, about we claimed that no "no-dates" entries would be created.

And checking for duplicates via

select count(distinct dday) from manydates;


and

select dday, count(*) from manydates
group by dday having count(*) >1;

show that there aren't any.

 

But how's this?

We are looking at how many years worth of days here? 9999 exactly.

 

So, leaving out leap years we should end up with 365 * 9999 = 3.649.635 separate days.

That number is 2.426 days short of what we actually find in our table.

 

Alright, so then just taking 366 days per year will help us, maybe?

Nearly: 3.659.634 days result here, which is 7.573 days too many.

 

Maybe trying to account just for the leap years separately can help here.

Leap years occur roughly every four years, so for 9999/4 = 2.499 years we should add one day.

 

That gets us to 3.652.134, which is just 73 days off from the actual number of days in this dates table.

 

We can even check this in SQL again:

select count(distinct year(dday))  from manydates
where mod(to_integer(year(dday)), 4) = 0;

COUNT(DISTINCT YEAR(DDAY) )

2499                   

 

But what leap years didn't we cover with our simple arithmetic?

 

(select year(dday), count(dday)
    from manydates
    where  mod(to_integer(year(dday)), 4) = 0 
    group by year(dday))
      
EXCEPT
(select year(dday),  count(dday)
    from manydates
    group by year(dday) having count(dday) > 365
)
order by year(dday);

Fetched 63 row(s) in 21 ms 617 µs

 

YEAR(DDAY)COUNT(DDAY)
1700    365     
1800    365     
1900    365     
2100    365     
2200    365     
2300    365     
2500    365     
2600    365     
2700    365     
2900    365     
3000    365     
3100    365     
3300    365     
3400    365     
3500    365     
3700    365     
3800    365     
3900    365     
4100    365     
4200    365     
4300    365     
4500    365     
4600    365     
4700    365     
4900    365     
5000    365     
5100    365     
5300    365     
5400    365     
5500    365     
5700    365     
5800    365     
5900    365     
6100    365     
6200    365     
6300    365     
6500    365     
6600    365     
6700    365     
6900    365     
7000    365     
7100    365     
7300    365     
7400    365     
7500    365     
7700    365     
7800    365     
7900    365     
8100    365     
8200    365     
8300    365     
8500    365     
8600    365     
8700    365     
8900    365     
9000    365     
9100    365     
9300    365     
9400    365     
9500    365     
9700    365     
9800    365     
9900    365     

 

If you followed up to here, you'll notice, that we were looking for 73 "missing days".

Comparing the set of years where the number of the year is divisible by 4 without remainder and the set of years where the number of days is larger than 365 revealed 63 days.

So, where are the 10 last days here?

 

Remember that odd reference on the Gregorian Calendar reformation?

That took ten days away in 1582!

 

select count(distinct dday) from manydates
where year(dday) = 1582;

COUNT(DISTINCT DDAY)

355

 

So far, so good.

We found all the "missing records", but why aren't these counted as leap years anyhow?

That's because, I used a too simple rule here.

Leap Year nearly every 4 years explains that a leap year is only counted as such if

  • The year is evenly divisible by 4;
  • If the year can be evenly divided by 100, it is NOT a leap year, unless;
  • The year is also evenly divisible by 400. Then it is a leap year.

 

Let's run that against our dates table:

 

select year(dday) yr, count(dday) day_cnt
from manydates
where
       mod(to_integer(year(dday)), 4) = 0
       and
       not (mod(to_integer(year(dday)), 100) = 0)
      or
        mod(to_integer(year(dday)), 4) = 0
        and
        mod(to_integer(year(dday)), 400) = 0
group by year(dday)
;

...

Fetched 2424 row(s) in 123 ms 147 µs (server processing time: 1 ms 665 µs)

 

Alright - the rule gives us 12 years less than what is in our dates table.

Which ones?

 

select mod_y.yr mod_yr, long_y.yr long_yr
from
  (select year(dday) yr, count(dday) day_cnt
  from manydates
  where
        mod(to_integer(year(dday)), 4) = 0
        and
        not (mod(to_integer(year(dday)), 100) = 0)
       or
         mod(to_integer(year(dday)), 4) = 0
         and
         mod(to_integer(year(dday)), 400) = 0
        group by year(dday)
  ) mod_y
      
FULL OUTER JOIN
  (select year(dday) yr,  count(dday) day_cnt
     from manydates
     group by year(dday) having count(dday) > 365
  ) long_y
  on mod_y.yr = long_y.yr
WHERE
   mod_y.yr is null or long_y.yr is null;

 

MOD_YRLONG_YR
100 
500 
900 
1300
200 
600 
1000
1400
300 
700 
1100
1500

 

Can you spot the reason?

All of those records violate the rule of not being divisible by 100 except when they are divisible by 400.

Why had those years been generated with one day too many by our add_days() function?

 

The Gregorian Calendar Reformation clearly did change history but it didn't change the past

Before this regulation got active, the leap years didn't follow those rules (which called for the Reformation in the first place).

And as we see there are no false leap years after year 1500.

 

That's it for today.

Funny things come up when one spends some hours on a rainy Sunday afternoon fiddling with SAP HANA SQL.

 

There you go; now you know!

 

- Lars

Once I was working with 3 tier HANA replication I got this "beautiful" error:

 

Database was not coming up , Daemon process was dying. I can see errors in two trace files:

 

daemon_<hostname>.39200.000.trc:[11022]{-1}[-1/-1] 2015-08-05 16:37:43.337557 i Daemon TrexDaemon.cpp(13699) : comment file contains: nameserver: landscape ID mismatch between nameserver.ini/[landscape]/id (5548nnnn-7699-9b81-e100-0000ac113e11) and topology.ini (5548oooo-86a0-48fd-e100-0000ac113e0e)

 

nameserver_alert_<hostname>.trc:[4823]{-1}[-1/-1] 2015-08-05 16:25:58.683109 f NameServer       TREXNameServer.cpp(03323) : landscape ID mismatch between nameserver.ini/[landscape]/id (5548nnnn-7699-9b81-e100-0000ac113e11) and topology.ini (5548oooo-86a0-48fd-e100-0000ac113e0e)


Finding nameserver.ini was not a big deal, we know about, or you can also refer to one of my earlier blog Hardware Key for the HANA Database

 

I started looking for topology.ini; the more I looked the more curious I got, and it was nowhere to be found.

I do found SAP giving us command to modify parameters in topology.ini for various issues or purposes. Examples mentioned in the notes 1649519, 1867324, 1950221, 1697613 and 2093572

  • Remove the SAP HANA Statitisticsserver service entry from the topology. To do this, carry out the following command for every <host name> with an SAP HANA Statitisticsserver service:

ALTER SYSTEM ALTER CONFIGURATION ('topology.ini', 'system') UNSET ('/host/<host name>', 'statisticsserver')  WITH RECONFIGURE

  • Remove the SAP HANA XS service entry from the topology. To do this, carry out the following command for every <host name> with an SAP HANA XS service:

ALTER SYSTEM ALTER CONFIGURATION ('topology.ini', 'system') UNSET ('/host/<host name>', 'xsengine')  WITH RECONFIGURE

 

I confirmed with SAP that its not a physical file, its more like a virtual one, the naming convention is still being carried till today which started from the other SAP product TREX.

 

That make some sense, but even though its not physical file, but HANA can relate/associate values to it, in some cases we can alter the content of this "ghost" file through ALTER command.

 

How to know what is the content of this file.

Here is the simple one:

login with <sid>adm on Hana server and execute  -> cdpy

it will take you to the directory $DIR_INSTANCE/exe/python_support

 

on the command prompt you can type

$> python fullSystemInfoDump.py

 

it will take some time and create single zip file stuffed with all core files and traces and history of the world since big bang.

 

If you will unzip it you can find The Golden file :  topology.txt

 

but just to get this file I hate to wait so long and get a bulky file. Instead I just need only topology.txt file.

 

Here is how to get only that file:

copy the file fullSystemInfoDump.py to zLeanSystemInfoDump.py

and modify like below

orignal.png

Original code ↑

modified.pngModified code ↑ (deleted few lines creating unwanted file for our case)

execute.png

Here we can execute the command as shown in above screenshot, later we can unzip the output file to get topology.txt file.

This file contains good information at one place. its a good read.

 

To read my other blogs please follow the link Ishteyaque Ahmad

 

Regards.

There are couple of ways we configure HANA Transports and moving them across our landscape. This blog mainly concentrates on configuring and moving transports using HANA LCM.

 

Lets say you have DEV, QAS & PRD systems in your landscape and want to do developments in DEV and move it across the QAS & PRD systems. Below are the steps that you need to follow.

 

  1. Create technical user in DEV for Transport Management
  2. Enable Change management in DEV
  3. Register DEV system in QAS.
  4. Maintain Transport Routes
  5. Move Transports.

 

  • Create technical user in DEV for Transport Management


This is one of the first pre requisite that we need to follow. We need to create a user with the following previliges.

 

  1. HANA Studio -> security -> users -> create new user -> user name as "CHANGE_MANAGER"
  2. Assign Role -> 1. sap.hana.xs.lm.roles::Administrator 2. sap.hana.xs.lm.roles::Transport 3. sap.hana.xs.lm.roles::Execute Transport
  3. Assign System Previliage ->REPO.CONFIGURE
  4. Assign Packaage Previliage -> sap.hana.xs.lm.core.dest


Ensure that user "CHANGE_MANAGER" should not have any password & user validity expriation.


  • Enable Change management in DEV


1. In SAP HANA studio, open the SAP HANA Application Lifecycle Management. or alternatevely, you can open the below link

    http://<DEVServerHost>:80<SAPHANAinstance>/sap/hana/xs/lm


 

2. Login into the above link and Choose the SETTINGS tab.

 

3. Select the Enable Change Recording checkbox.

 

 


  • Register DEV system in QAS

 

1. Login into your QAS system with the below URL

http://<QASHost>:80<SAPHANAinstance>/sap/hana/xs/lm

 

2. Goto Transport Tab and click on "Register"

 

 

3.  Enter Hostname : DEV Hostname,  XS Port: 80<nr> , Comment: DEV and then click Next

 

 

4. Click on Maintian Destinations

 

 

5. Select Authentication type as "Basic" -> Enter user name and password created at step 1.

 

 

6. Click on Finish .

 

This will complete the regestration of DEV in QAS.

 

How to create Transport routes and How to move tranports across the land scape will present as Part 2

 

Regards,

Pavan Gunda

Actions

Filter Blog

By author:
By date:
By tag: