Delta merge with python ? mergeDeltaTable.py

[henningbussmann]

Dear all,

we have many small tables the have never been merged and that are mainly located in the Delta-store. According to SAP support it would improve storage and performance if they were forced merged.

It can be done one by one with the HANA Studio.

SAP also deliveres a python script mergeDeltaTable.py to automatically force-merge several tables with one command. Unfortunately it ends with a syntax error.

Is there anybody else who uses it and can suggest what we are doing wrong?

Best regards

Henning

<sid>adm@<hostname>.<domain>:/usr/sap/<SID>/HDB<NN>/exe/python_support> python mergeDeltaTable.py --user=<Admin-User> --password=<Password> --address=sap<sid>.<domain>.de --port=<port> <Tablename> [139741556451136, 0.001] >> starting mergeDeltaTable (at Fri Jul 13 14:04:24 2018) [139741556451136, 0.001] args: ('<Tablename>',) [139741556451136, 0.001] keys: {'address': 'sap<sid>.<domain>.de', 'password': '<Password>', 'port': '<port>', 'user': '<Admin-User>'} [139741556451136, 0.191] INFO: Configuration file /hana/shared/<SID>/exe/linuxx86_64/HDB_1.00.122.04.1478575636_3308086/testscripts/config/testConfig.ini not found, falling back to autoconfigTraceback (most recent call last): [139741556451136, 0.191] File "/hana/shared/<SID>/exe/linuxx86_64/HDB_1.00.122.04.1478575636_3308086/python_support/testbase.py", line 720, in run [139741556451136, 0.192] ret = self.runHandler(*rest, **self.extractInheritedArgs(keys)) [139741556451136, 0.192] File "mergeDeltaTable.py", line 35, in runHandler [139741556451136, 0.192] self.openConnection(user,password,address,port,autocommit,tracelevel,remotemode) [139741556451136, 0.192] File "/hana/shared/<SID>/exe/linuxx86_64/HDB_1.00.122.04.1478575636_3308086/python_support/testbase.py", line 1772, in openConnection [139741556451136, 0.192] self.connection = dbapi.connect(address, port, user, password, autocommit) [139741556451136, 0.192] File "/hana/shared/<SID>/exe/linuxx86_64/HDB_1.00.122.04.1478575636_3308086/python_support/hdbcli/dbapi.py", line 82, in __init__ [139741556451136, 0.192] self.__connection = pyhdbcli.connect("%s:%d" % (address, port), 'HDB', user, password, self.__properties) [139741556451136, 0.192] TypeError: %d format: a number is required, not str [139741556451136, 0.192] << ending mergeDeltaTable, rc = 1 (RC_GENERAL_EXCEPTION), after 0.191 secs <sid>adm@<hostname>.<domain>:/usr/sap/<SID>/HDB<NN>/exe/python_support> which python /usr/sap/<SID>/HDB<NN>/exe/Python/bin/python <sid>adm@<hostname>.<domain>:/usr/sap/<SID>/HDB<NN>/exe/python_support> python Python 2.7.10 (sap:7, Dec 16 2015, 14:23:27) [GCC 4.8.3] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> exit() <sid>adm@<hostname>.<domain>:/usr/sap/<SID>/HDB<NN>/exe/python_support>

[henningbussmann]

Dear Lars,

have you tried the script on your system?

I tried to fix the script by adding line 24 as "port=int(port)" to force the conversion to integer. Now I get a different error.

My plan wasn't to debug and fix the script but to learn if for anybody it works as delivered.

Best regards

Henning

---

<sid>adm@<hostname>.<domain>:/usr/sap/<SID>/home> python mergeDeltaTable_test.py --user=<Admin-User> --password=<Password> --address=<Host-Alias> --port=<Port> <Tablename>

[140209680496448, 0.001] >> starting mergeDeltaTable_test (at Mon Jul 16 08:02:57 2018)

[140209680496448, 0.001] args: ('<Tablename>',)

[140209680496448, 0.001] keys: {'address': '<Host-Alias>', 'password': '<Password>', 'port': '<Port>', 'user': '<Admin-User>'}

Traceback (most recent call last):

File "mergeDeltaTable_test.py", line 50, in runHandler

self.executeSQLStatement('MERGE DELTA OF "'+index[0] + '"."' + index[1] + '" ')

File "/usr/sap/<SID>/HDB01/exe/python_support/testbase.py", line 1785, in executeSQLStatement

ret = cursor.execute(statement)

File "/usr/sap/<SID>/HDB01/exe/python_support/hdbcli/dbapi.py", line 351, in execute

ret = self.__execute(operation)

File "/usr/sap/<SID>/HDB01/exe/python_support/hdbcli/dbapi.py", line 245, in __execute

ret = self.__cursor.execute(operation, parameters=parameters, iscall=iscall, scrollable=self._scrollable)

Error: (259, 'invalid table name: <Tablename>: line 1 col 25 (at pos 24)')

[140209680496448, 0.127] INFO: Configuration file /usr/sap/<SID>/HDB01/exe/testscripts/config/testConfig.ini not found, falling back to autoconfigwhile handling table SYSTEM.<Tablename>

[140209680496448, 0.135] << ending mergeDeltaTable_test, rc = 0 (RC_TEST_OK), after 0.134 secs

<sid>adm@<hostname>.<domain>:/usr/sap/<SID>/home>

[lbreddemann]

I've used the script (actually the mergeAllDeltaTables.py) a long time ago (and never after, really) and I tried it out now again and could only get it to work with the SYSTEM user:

python mergeAllDeltaTables.py --user=SYSTEM --password=<password> --address=localhost --nameSpace=<schemaName>

As it's not part of the official functionality I'm not sure whether it will be supported via a support incident. I'd recommend opening one (if not already done) nevertheless.

Besides this script it's relatively easy to generate the MERGE DELTA OF <schema_name>.<table_name> commands for all tables that haven't been merged yet:

select   
'MERGE DELTA OF "'||schema_name||'"."'||table_name||'" FORCE REBUILD;' 
from m_cs_tables
where last_merge_time is null and loaded !='NO';

This will generate the commands for all currently loaded tables that never had any merge operation.

For other conditions, e.g. tables with overly large number of records in the delta store or tables that are not currently loaded, you will need to change the selection criteria.

[lbreddemann]

Looks like the way you provided the hostname and port wasn't correct. Check that the --port parameter really is only a number (no non-digit characters).