Migrating a HCP MaxDB database to HANA

[paschmann]

Overview: This blog describes migrating a simple MaxDB database from HCP to a dedicated HCP HANA instance. Also has some details around a open source tool called XSImport - a free, open source native XS app for importing CSV files into HANA.

In the Enterprise Mobility team at SAP we are currently working on an exciting project migrating one of our existing applications to a HANA backend, while going through a major code rewrite. During this process, I had the task of migrating a HCP based MaxDB over to a dedicated HCP HANA DB. The dataset I was working with was not particularly large (@1.3 GB) with the majority of the data residing in just a few tables. (For reference this is a standalone Java HCP app). Since remote tunnel access to production HCP Databases is not available, the only way to get the raw data was through a HCP ticket, the support team were helpful and responsive, and after a couple of hours I had my exported dataset.

I loaded the data into my local MaxDB instance and chose to use the MaxDB loadercli command line tool to export the data out of the source DB and into CSV files, during this step I transformed some of the data as well put the data into the corresponding column order which matched my target table to make things simpler. I had one challenge with using loadcli which I posted to MaxDB SCN forums - but still no solution, so I had to clean some of the CSV files up using regex in my text editor.

Here is an example export statement from MaxDB. The use of CASE statements can be helpful to transform data types:

EXPORT

     COLUMNS ID,

     CREATIONDATE,

     DESCRIPTION,

     CASE WHEN TYPE = 'single' THEN '1' WHEN TYPE = 'group' THEN '2' WHEN TYPE = 'public' THEN '3' ELSE  ' ' END

FROM

     NEO_12345.T_TABLE

DATA OUTSTREAM 'T_TABLE.csv'

Once I had a set of CSV’s I needed to decide how I was going to load the data into my HANA instance, after some trial and error of using the HDBTI files, via the build in IMPORT statements (I have no way of getting access to the file system of the HANA instance), using HANA Studio, or a dedicated import script via XSJS - I decided on the latter. I found the studio process just did not work for some reason, but the xsjs import process was reasonably quick. Since I was going to do multiple loads, I thought it would be pretty helpful to have a tool which facilitates loading the CSV files and wrote a small open source tool called XS Import.

Overall the process was fairly straight forward, aside from some of the minor challenges described above :smile:

If you have done any similar migrations in HCP? and had a different experience, please feel free to share your thoughts on some of the processes and methods I chose above as I would like hear some of your opinions. (I am hoping to hear if there is faster/better/simpler?)

Thanks and cheers, Paul