Skip to Content

Synchronization with HANA - Part 1

This article seeks to prove the following bold claim:

It's easy to set up MobiLink to synchronize a SQL Anywhere database with HANA.

Yes, it IS easy, but not easy like walking to the kitchen and back, more like walking to work. In other words, there are a LOT of little steps, each one of them easy, but there's still a lot of them.

More importantly, there are no time-sinks along the way. This might be a single-table two-row demonstration, but it could just as easily have been a million rows: there's no hardware to buy, no Linux scripts to code, no software to download (well, maybe just a little bit, more on that later).

This article will demonstrate:

  • a working MobiLink synchronization setup
  • that sends data back and forth
  • between a local SQL Anywhere remote database
  • and a HANA consolidated database running in the cloud,
  • using a single "Hello World" table.

Why Hello World?

Because "(c)onfiguring a complete programming toolchain from scratch to the point where even trivial programs can be compiled and run can involve substantial amounts of work. For this reason, a simple program is used first when testing a new tool chain." - en.wikipedia.org/wiki/Hello_world_program

Introduction

This article is intended for developers with relational database and synchronization experience. It does not assume any familiarity with HANA, CloudShare or Eclipse, but it does assume a basic understanding of SQL Anywhere and MobiLink, or failing that, a willingness to look stuff up if questions arise along the way.

Here's how the various components fit together:

  • The HANA software and database is provided by SAP as part of the 30-day HANA Trial.
  • In particular, the SAP HANA SPS6 database server and the SAP HANA Studio run as two VMs on cloudshare.com.
  • The HANA VM runs Linux, SUSE Enterprise 11 SP1 64 bit to be exact, but relax! It just sits there, you don't have to run any Linux commands or code any shell scripts.
  • The HANA Studio component runs on Windows 7 at CloudShare, and you work with an Eclipse-style GUI via remote control.
  • The SQL Anywhere database server dbeng16.exe runs on your Windows desktop, or laptop, or whatever local workstation computer you have available.
    Note: If you use HANA 1.00.64 or later, you will need SQL Anywhere 16.0.1761 or later. This article was written using a combination of HANA 1.00.68 for the database and 1.00.70 for the ODBC driver, and SQL Anywhere 16.0.1823.
    Tip: Here's how to see the version numbers: SELECT * FROM M_DATABASE for HANA, and SELECT @@VERSION for SQL Anywhere.

  • In MobiLink parlance the central HANA database is called the "consolidated database" and the SQL Anywhere database is one of many "remote databases". Yup, there's opportunity for confusion here: SQL Anywhere is running on a computer that is local to you but the docs call it a "remote" database. That's why this article will henceforth (try to) use the terms "HANA" and "SQL Anywhere" instead of "consolidated" and "remote".
  • The MobiLink software consists of two components: The MobiLink client dbmlsync.exe which runs on the same computer as the SQL Anywhere database, and the MobiLink server mlsrv16.exe which should be run on or near the computer running HANA. For the purposes of this article, however, the MobiLink server will be run on your local computer.

To summarize: HANA runs on CloudShare, and all three of the SQL Anywhere database server, MobiLink server and MobiLink client run on your workstation.

Here's an overview of how the data flows during a MobiLink synchronization:

  • The MobiLink client establishes a local database connection to the SQL Anywhere database and builds an upload stream consisting of rows that have changed since the previous synchronization.
  • The MobiLink client establishes a network connection to the MobiLink server and sends the upload stream. In this article the "network connection" is actually a local connection since the MobiLink client and server are on the same computer (your workstation).
  • The MobiLink server establishes an ODBC database connection to the HANA database and applies the upload stream. In the real world the ODBC connection would be entirely "behind the firewall", but for the purposes of this article it crosses the internet from your workstation to the VM on CloudShare.
  • The MobiLink server then uses the ODBC connection to gather rows from HANA that have changed since the previous synchronization, forming a download stream which it sends over the network, er, local connection to the MobiLink client on your workstation.
  • The MobiLink client then applies the download stream to the SQL Anywhere database.

Step 1. Sign Up For HANA In The Cloud

Sounds easy, doesn't it? Just sign up and go!

Well, it is easy, but there are a lot of little steps inside Step 1... each one is easy, but there are a lot of them. Easy doesn't mean trivial.

There's also more than one way to get access to HANA for evaluation purposes, some easier than others... and SAP website is continuously changing.

In other words, the method described here did work at the time this article was written.

First, go to the "Get 30 days of free access to SAP HANA, developer edition" web page: scn.sap.com/docs/DOC-28191

If the page displays "Welcome, Guest" like it does above, you'll need to log in to the SAP Community Network (SCN) website. Click on the Login link at the top of the page, or, if you don't have a user id, click on Register to get one.

Once you see "Welcome, Your Name" at the top of the page you'll be all set to proceed:

Scroll down to the bottom and click on "Start your 30-day HANA Trial here".

At this point, you may be required to "upgrade" your SCN account:

Unlike most cloud services, "Upgrade Your Account" does not mean "Spend Money", so if you see the page shown above just accept the terms and click on Register.

The next page looks like you're logging in to something called Netweaver Cloud, but you're not, you're just filling in (yet) another form:

If you thought you were done with the paperwork, think again, but at least the tone changes on the next page: "You have been invited to a CloudShare environment!"

You're not in Walldorf any more (where SAP resides), you're in San Mateo (CloudShare's home town).

Judging by what the subsequent "Loading from snapshot" page displays, the whole CloudShare environment must be mind-numbingly complicated... under the hood, that is; on the surface it's completely automated. There might be a zillion things on the screen but each one is easy to read, and you can click on stuff while you're waiting, to read more about what's going on.

For example, you can click on "More details" to see what's in the two components you're getting:

  • SAP HANA SPS6 server - This is your HANA database. You'll be using it, but only indirectly. In other words, you won't be clicking on "View VM" for HANA, at least not after the first time when you discover there's nothing much to look at.
  • SAP HANA Studio - This one, you'll be using a lot. And when you click on View VM for the HANA Studio, you'll be glad you have a super-duper-high-speed internet link. If you don't have one of those, be patient, and remember what GUIs were like in 1993: click, wait, move mouse, wait, click, wait, move mouse, wait... ...but that comes later. For now, the GUI is lickety-split fast because it's browser based (CloudShare) rather than remote control (HANA Studio).

CloudShare comes with something called Persistent DNS, which you want, which you get automatically, and which you can read about here: support.cloudshare.com/entries/23712067-Persistent-DNS

CloudShare also offers something called Vanity URL which you don't get automatically; you can read about here to see if you want it (you do): support.cloudshare.com/entries/21525712-Web-Access-Vanity-URLs

If you want to create a Vanity URL (let's say you do), first you have to tell CloudShare to "enable web access" for your VM.

Click on Edit Environment button over on the right, then Edit users & access to get the Edit VM Access page.

Then check the two Enable Web access boxes and click on Save changes.

The second step in creating a Vanity URL is to switch to the My Account tab at the top of the CloudShare page, then pick the Vanity URLs tab, then check the Vanity URL box next to SAP HANA SPS5 server, type in your vanity name, and check the Web Access box.

In this case, breckcarterhana is the chosen vanity name. Remember the name you use, you'll need it in later steps:

Server:Port breckcarterhana.vm.cld.sr:30015

Tip: Don't forget to click on "Save changes"... and make sure the green "Vanity urls configuration saved" appears.

Tip: If mess up and make your trial copy of HANA on CloudShare completely unusable by, say, clicking on Reboot VM and then discovering that HANA is now "System locked; license invalid or expired", you can try fixing the problem... or ... you can bite the bullet and start over: Create a new SCN login, sign up for the HANA trial again, create a new CloudShare login and so on. If you do all that, and you want to reuse your old vanity url (e.g., breckcarterhana), you'll first have to sign on to CloudShare with your old login and release breckcarterhana over there by unchecking the Vanity URL box shown above.

Tip: If you do end up with more than one CloudShare account, make sure you're always working in the right one. Keep an eye on the "Hello, Your Name" field in the top right corner of the CloudShare pages.

Step 2. Get Started With HANA Studio

To open the SAP HANA Studio, switch to the My Environments tab at the top of the CloudShare page, then click on the View environment button on the right, followed by the View VM button for SAP HANA Studio.

Tip: If what you see doesn't look at all like the screenshot below, try resizing the Notepad box, and try clicking on the left side of the page to expand the Cloudshare frame. What you're looking at is a funky combination web page and remote desktop, similar to GoToMyPC but not as slick.

Tip: If you see a "Missing plugin detected" popup, it's probably safe to ignore. Several months ago the plugin failed to install on Chrome but it might work now; it does install OK on IE11.

Tip: Check out the FAQ section at the bottom left of the CloudShare page for topics like "How do I transfer files to this machine?"

Tip: There are several ideograms (hieroglyphics?) at the top right corner of the CloudShare page. The middle one is called "Resolution", and if the "Auto resolution" setting isn't working for you try one of the others; e.g., "800x500" was used keep the screenshots small for this article.

To open the HANA Administration Console, double-click on the SAP HANA Studio icon on the desktop.

If that doesn't work (if double-click opens the Properties dialog box instead), try clicking right mouse - Open.

The "Secure Storage" popup shown above may not be the first thing you see, but if so, click "No"... past experience shows that "Yes" leads to confusion followed by a long and winding path back to "No"

On the Overview window above, click on Open Administration Console to get the following to appear. If you don't see the Cheat Sheets tab on the right (and you probably won't), try clicking on the inverted triangle "New Menu" ideogram on the far left.

Tip: You may have to resize and move the window around to see all of it, AND you may have to be ... very ... patient ... with your sluggish mouse.

Tip: If you take a break and get timed out, here's how to get back in to CloudShare: use.cloudshare.com/Pro/Login.mvc. You'll need your CloudShare password to login, not your SAP SCN password, and then you'll have to click on the View Environment button to reload the HANA VMs.

In the Cheat Sheets tab on the right, expand the Adding New Systems and Folders item, select Creating a New Folder, then click on "Start working on this task".

This Cheat Sheet doesn't actually do anything for you, but it does show you what steps to take... the first of which is to click on the File - New - Folder menu item.

The New Folder dialog box will then open, and you can type in your Folder name, in this case breck.

The next step on the Cheat Sheet is "Adding a New System" which tells you to right-click on "breck" in the left-hand Navigator tab, then click on Add System...

That brings up the Specify System dialog box where you fill in the fields like this:

Hostname: breckcarterhana.vm.cld.sr

Instance Number: 00

Tip: If your mouse pointer disappears when you move it over the HANA Studio window, click and it'll come back.

Click on Next to get the Connection Properties dialog box, and provide the all-powerful-default HANA Authentication by database user:

User Name: SYSTEM

Password:  manager

At this point, you can click on Finish, or Next to see the Additional Properties dialog box which shows you the Host value breckcarterhana.vm.cld.sr:30015.

At this point, it's time to click on Finish, after which you may or may not see a variety of error messages; for example, this:

Can not access system on host 'breckcarterhana.vm.cld.sr'

may be followed quickly by this:

Connection Properties

The system cannot be reached. The login data could not be used.

Are you sure you want to add the system to your workspace?

Yes No

When you click on Yes (because you don't want to discard your changes), you might see this:

System

The system is not accessible. Specify the system ID if you want to register

the specified system anyway.

Host Name:       breckcarterhana.vm.cld.sr

Instance Number: 0

System ID:       ______

OK Cancel

After googling "system id" on site:sap.com you might (correctly) guess it should be HDB, so you enter that and press OK, only to see this:

[HDB breckcarterhana.vm.cld.sr 00 (SYSTEM)]Could not create conn...

com.sap.ndb.studio.jdbc.JDBCConnectionStatus SAP DBTech JDBC: Cannot

connect to jdbc.sap://breckcarterhana.vm.cld.sr:30015[Unknown host

breckcarterhana.vm.cld.sr:30015[breckcarterhana.vm.cld.sr], -709 ].

OK

...but at least your work got saved

At this point, you could try googling some more, or asking questions on scn.sap.com, but first... go back and check your work. In particular,

  • check the CloudShare "SAP HANA SPS6 server" VM to make sure HANA is still running, then
  • make sure your "Vanity URL" settings were saved properly.

The eventual goal is a window that looks something like this, proof the Administration Console is actually connected to HANA:

Step 3. Install SQL Anywhere And MobiLink

As mentioned earlier, this article assumes "a basic understanding of SQL Anywhere and MobiLink, or failing that, a willingness to look stuff up". If that means you don't already have them installed, this step is for you.

MobiLink comes "in the box" with SQL Anywhere and they are both installed by default when you download and install a copy of the SQL Anywhere 16 Developer Edition: global.sap.com/campaign/na/usa/CRM-XU13-CST-SQLDEVEDM/index.html

As far as your "willingness to look stuff up" is concerned, here's where to start:

If you need to ask a question, here's how:

  • To ask a question about SQL Anywhere or MobiLink:
    • Go to the SQL Anywhere forum: sqlanywhere-forum.sap.com

    • Click on the "ask a question" box near the top right corner.
    • You will have to login or sign up for a new account before your question will be published, but after that there are no delays.
  • To check if your question's been answered:
    • Go to the SQL Anywhere forum: sqlanywhere-forum.sap.com

    • Your question will appear at or near the top of the "active" list if anyone has responded.
  • To ask a question about HANA:
    • Go to the SAP HANA Developer Center: scn.sap.com/community/developer-center/hana

    • Login if necessary, then click on the Actions - Start a discussion link over on the right.
    • When the "Start a discussion" editor appears, check Mark this discussion as a question.
    • If you have any problems (say, with Insert Image not working) try a different browser; e.g., Internet Explorer instead of Chrome.
    • If you're a new SCN user, your question won't be visible to other people until it is approved by a moderator.
  • To check if your question's been answered:
    • Go to the SAP HANA Developer Center: scn.sap.com/community/developer-center/hana

    • Click on the Communications button in the top right corner of the page.
    • The question you asked should appear in the bottom right section of the page. Click on the subject to expand the discussion and see if there are any new replies.
    • Replies are also subject to moderation, so a conversation between two new users might ... require ... patience.

If you prefer to google for docs (who doesn't?) here are some examples that make use of the Google "site:" keyword to narrow the searches:

  • Search the SQL Anywhere 16 Help for information about the MobiLink upload_insert script: "upload_insert" site:dcx.sybase.com/sa160/en

  • Search the SQL Anywhere forum for questions about global autoincrement columns: "global autoincrement" site:sqlanywhere-forum.sap.com
    Tip: The forum recently moved from sybase.com to sap.com. At the time of writing, a Google search using the new domain name did not get as many hits as the old domain name; try it and see: "global autoincrement" site:sqlanywhere-forum.sybase.com. Eventually, it might not matter, or it might become important to use the new domain name; time will tell.
  • Search the HANA Help for information about isolation levels: hana "isolation level" site:help.sap.com
  • Search the SAP Community Network (SCN) for an error message: "System locked; license invalid or expired" site:scn.sap.com
    Tip: In the world of SQL Anywhere documentation there's usually only one place to read about something; not so with SAP. The main SAP product lines (and HANA is nothing if not a major product) are accompanied by endless reams of documentation, shifting and changing and moving and multiplying with every passing day.
    Don't Panic: You don't have to read it all, you just have to understand enough to do your job. And if the document you happen to be reading isn't helping, find a better one, it is surely somewhere out there on sap.com or beyond.

Step 4. Connect To The HANA Database Via ISQL

The Interactive SQL utility (ISQL, or dbisql) that ships with SQL Anywhere 16 works with HANA databases as well as SQL Anywhere databases. First, however, you have to download the HANA client software from SAP in order to get the right ODBC driver.

When it comes to downloading the HANA client, there are at least three YouTube videos on the subject. Here's one: youtu.be/i0fmHNLhD-c

Or, if that's a case of tl;dw (too long; didn't watch) you can use this URL to get there directly: hanadeveditionsapicl.hana.ondemand.com/hanadevedition/

Here's what you'll see as the HANA Database Client is installed on your Windows PC:

Here's how to create an ODBC DSN for your HANA database.

  • Open the ODBC Data Source Administrator on your workstation (start - Control Panel - Administrative Tools - Data Sources (ODBC)), select the User DSN tab, click on the Add... button, and select the HDBODBC entry.
  • Click on Finish to display the "SAP HDB (1.00.70.58439)" dialog box, and fill in these fields where breckcarterHANA is anything you want, and breckcarterhana.vm.cld.sr:30015 is the vanity URL and port number that was determined earlier.

Data Source Name  breckcarterHANA

Server:Port       breckcarterhana.vm.cld.sr:30015

  • You can click OK now to save the DSN, or you can test it by clicking on Connect and filling in these fields:

User      SYSTEM

Password  manager

  • Click OK to test the connection, and... oops! Here's what happens if your HANA VM isn't running on CloudShare:

ODBC-Error

SQLSTATE: 08S01

NATIVE ERROR: -10709

MESSAGE TEXT: [SAP AG][LIBODBCHDB DLL][HDBODBC]

Communication link failure;-10709 Connection failed

(RTE:[89006] System call 'connect' failed, rc=10060:A connection

attempt failed because the connected party did not properly

respond after a period of time, or establish

OK



  • Here's what you'll see when your HANA database is up and running:

At this point, don't forget to click OK, and then OK again to save your DSN. That's a common error with the ODBC Administrator: savoring the glow of a successful connection and then wandering off to celebrate without actually saving anything

Tip: And... are you wondering why this article contains redundant stuff like the "ODBC-error" in both image and text form?

Google is the answer! ...so folks can search on the message text and find this article

We're not in Kansas anymore!

Once you have an ODBC DSN like breckcarterHANA set up, and your HANA database is up and running on CloudShare, you can use this Windows command line to launch Interactive SQL:

"%SQLANY16%\bin64\dbisql" -c "DSN=breckcarterHANA; UID=SYSTEM; PWD=manager;"

Tip: Did you know? SQL Anywhere's ISQL utility can be used with HANA but you have use the dbisql -hana option to avoid that warning message. There's also an -iq option for Sybase IQ databases if that interests you.

Tip: When you run dbisql with -hana you can use DSN=breckcarterHANA in the connection string but you cannot specify UID=SYSTEM or PWD=manager. If you do, dbisql will ignore them and prompt you for the user id and password.

To avoid that annoyance, specify USER=SYSTEM and PASSWORD=manager instead... seriously... I'm not kidding... them's the rules, apparently

Here's the new improved full-function ISQL command line:

"%SQLANY16%\bin64\dbisql" -c "DSN=breckcarterHANA; USER=SYSTEM; PASSWORD=manager;" -hana

Here's a simple query to run:

SELECT *,

  'No' AS "We're not in Kansas anymore."

  FROM DUMMY;

to get a result set that's just a ... little ... bit ... different from the same SELECT on SQL Anywhere:

What's different, and what proves that ISQL is connected to HANA rather than SQL Anywhere, is the result set: a column named DUMMY instead of dummy_col and an X instead of a zero.

Step 5. Switch To A Different User Id On HANA

It might be OK to use a default user id and password like DBA/sql when building an embedded database using SQL Anywhere, but not so with HANA.

No, in the enterprise world of HANA databases you're expected to use something other than SYSTEM/manager; here's how to set that up:

  • Login to CloudShare: use.cloudshare.com/Pro/Login.mvc
  • Click on View Environment and wait for everything to start.
  • Click on View VM for SAP HANA Studio. If you have to login, use SYSTEM/manager.
  • Expand breck - HDB (SYSTEM) - Security
  • Click right mouse on Users, then New User to open the "HDB (SYSTEM)" frame.
  • Fill in the fields...

User Name: DBA

Password:  SqlHappy1

Tip: Passwords in HANA have to be L0nGaNdFuNkY... but, there's good news! Copy and paste via Ctrl-C and Ctrl-V now work between your desktop and the CloudShare remote control window!

  • Click on the green "Deploy (F8)" arrow at the top right of the HDB (SYSTEM) frame.

In order to actually do anything with the new DBA user, it will need some privileges. Here's how:

  • Scroll down and select the Object Privileges tab at the bottom of frame "HDB (SYSTEM)" frame; the tab's empty but that will soon change.


Tip: If you think this GUI has too many frames, title bars and resizable borders for its own good, welcome to Modern Overdesign. Get used to it, and to its changing nature like when the tab "HDB - New User" switches to "HDB - DBA" (watch for it!).

Speaking of change, the whole SAP HANA Studio GUI could change at any time; it has in the past, like when "SQL Privileges" changed to "Object Privileges". The advice stands: Get used to it.

  • Click on the green "+" sign to open up the Select Catalog Object dialog box.
  • Type "system" to get some "Matching items" to appear.
  • Select the bottom-most SYSTEM entry and press OK to get the SQL Privileges tab... oops, the Object Privileges tab... to show what's available for 'SYSTEM'. Check all the boxes and all the Yes buttons; you may have to resize and move stuff around to see what you're doing:
  • ...then click on the green "Deploy (F8)" arrow at the top right of the HDB (SYSTEM) frame.

Be careful! That list of privileges you see above isn't the whole list, even though the scroll bar looks like it's at the bottom... that isn't the scroll bar for the privileges! The scroll bar you want is off to the right, and you need to use it to see the rest of the privileges like DELETE.

The new DBA user id can now be used to create and fill tables, but if the MobiLink server's going to use it to connect to the HANA database it also needs the CATALOG READ privilege. Here's how:

  • Click on the System Privileges tab, then the green "+" to get the Select System Privilege dialog box.
  • Select CATALOG READ. If any of the other privileges look interesting to you, you can read about them in the description of the GRANT statement here: help.sap.com/hana_platform#section7
  • Click OK to return to the HDB (SYSTEM) frame, then check "Grantable to other users and roles".

Tip: Now might be a good time to grant the USER ADMIN privilege if you want your new user id to be able to create other user ids. That's because the CREATE ANY privilege ain't good enough to CREATE USER, for that you need USER ADMIN.

Tip: The user id that creates the MobiLink system tables in the next step should be the same as the one used by the MobiLink server to connect to HANA in a later step. Enterprise security rules might insist on different user ids for those two roles, and here's the tip: Resist! or face extra development effort, testing and increased chances of error... by default MobiLink assumes one user id.

Tip: It's easy to have a separate user id just for MobiLink, and if you want one now's the time to create it. You can use your new DBA user to do that, it's why "Grantable" was checked all over the place. The point is this: the same single MobiLink user id should be used to create the MobiLink system tables and connect with the MobiLink server.

  • Don't forget to click on the green "Deploy (F8)" arrow...

You can now login to HANA via ISQL with the new DBA user id...

"%SQLANY16%\bin64\dbisql" -c "DSN=breckcarterHANA; USER=DBA; PASSWORD=SqlHappy1;" -hana

and it looks like it's working...

but as soon as you try to do something (like run SELECT * FROM DUMMY) you see this:

Could not execute statement.

SAP DBTech JDBC: [414]: user is forced to change password: alter

password required for user DBA

SQLCODE=414, ODBC 3 State="HY000"

Line 1, column 1

SELECT * FROM DUMMY

Click OK to clear the error, then use the ALTER USER command to change the password:

ALTER USER DBA PASSWORD HappyHANA1;

SELECT * FROM DUMMY;

Now it works:

Tip: HANA doesn't force you to clickety-clack through the Studio GUI for everything. You can also code Old School SQL statements, save them in Old School text files, document those scripts with "--" comments, and keep a history of changes... even use source control!

For example, here's the ISQL alternative for all the HANA Studio work done earlier this step:

CREATE USER DBA PASSWORD SqlHappy1;

GRANT CREATE ANY ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT ALTER      ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT DROP       ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT EXECUTE    ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT SELECT     ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT INSERT     ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT UPDATE     ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT DELETE     ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT INDEX      ON SCHEMA SYSTEM TO DBA WITH GRANT OPTION;

GRANT CATALOG READ TO DBA WITH ADMIN OPTION;

Gadzooks! that was easy... for more about HANA SQL statements see help.sap.com/hana_platform#section7

We're getting there! The tutorial continues in Part 2.

Tags: