Skip to Content
Technical Articles
Author's profile photo Rajarajeswari Kaliyaperumal

How to export and import table from HANA DB

HANA DB TABLE EXPORT AND IMPORT:

Being a HANA DBA and if you wanted to know how to export and import a table below is the steps.

EXPORT:

1.Create a folder inside work directory(Not inside trace directory or any other location which is not accessible by all nodes in case of a distributed environment)

In above I have created a folder called dbtab.

2.Execute the below command to export

4=>Here 4 represents the number of simultaneous threads that we want HANA to use to run our import . This can be increased as well

3.Once the above command has run succesfully, we can see folders inside the folder we created like index/*/*

This ends the EXPORT process.

TRANSFER TO THE TARGET:

If you want this specific table to be imported into target, we need to transfer the folder dbtab inside work folder of the target system . It can be done using scp or WinSCP.

scp -r dbtab dcxadm@servername:/usr/sap/SID/HDBnn/work

 

IMPORT PROCESS:

Prechecks:

1.If the table that we are going to replace belongs to SLT, that target replication has to be suspended. Any other activity that is going to hold a exclusive locks should be stopped to prevent lock time out

2..Before we import any table, we need to check if that table is present in the target HANA DB. If yes, we need to rename the existing table before importing the new one. Though this is not mandatory if we are going to use the additional clause “WITH REPLACE” in IMPORT command, it is always better to have backups before renaming or replacing any db related objects .

Command to rename :

rename table SAPDBR.Targettablename>  to SAPDBR.Targettablename>_OLD;

Steps:

Once we have  placed the target table inside work directory , we can proceed with table import.

To Import this table back in overwrite mode execute the below command.

IMPORT “SAPERP”.”DBTABLOG” AS BINARY FROM ‘/usr/sap/<SID>/HDB<nn>/work/dbtab’ WITH REPLACE THREADS 4;

4=>Here 4 represents the number of simultaneous threads that we want HANA to use to run our import . This can be increased as well.

NOTE:

IMPORT/EXPORT of tables process are considered as jobs in HANA DB and we can check the progress of the same in JOB PROGRESS tab in HANA Studio.

 

Thanks for reading!

Like and leave a comment if it was useful!

Assigned Tags

      4 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Sakti Naik
      Sakti Naik

      Nice blog.

      We can also export import using the HANA Studio Catalog folder (without using command line).

      One question, why you are using WITH REPLACE command while exporting the table from one database to a file system? While importing the table 'WITH REPLACE' command is understood.

      Author's profile photo Rajarajeswari Kaliyaperumal
      Rajarajeswari Kaliyaperumal
      Blog Post Author

      Hi Sakti, As you said WITH REPLACE is not a mandatory clause and i used it in EXPORT as i was running EXPORT of same table for the second time in the same folder . As you said, WITH REPLACE in EXPORT is not required for newly created folder .

      Author's profile photo Anil Manda
      Anil Manda

      Nice Blog

      Looking for syntax to export a bil record table into multiple flat files and import the same back into a different Hana DB.

      Author's profile photo Patricia Rolon
      Patricia Rolon

      Nice blog!

      Question: is it possible to use the export sentence in an sqlscript using a variable instead of a conttant for the the destination file? I tried different ways, but could not make it work.

      Thank you!

      Patricia