Configuration considerations for transactional tables

Transactional tables in Db2® Big SQL are managed by Hive, and to fully enable transactional changes in Db2 Big SQL requires some additional configuration changes.

Validate and complete the following configuration updates in Cloudera Manager to enable full support for transactional tables.
  1. Click Hive on Tez > Configuration and verify that existing properties are set. If they are not set, add them as part of Step 2.
    1. Type hive.support.concurrency into the search box and verify that this property is enabled.
    2. Type hive.txn.manager into the search box and verify that this property is set to org.apache.hadoop.hive.ql.lockmgr.DbTxnManager.
  2. Click Hive on Tez > Configuration and type hive-site into the search box to update HiveServer2 Advanced Configuration Snippet (Safety Valve) for hive-site.xml. Click + and add the following properties:
    1. Set the hive.compactor.initiator.on property to true.
    2. Set the hive.metastore.runworker.in property to hs2.
    3. Set the hive.compactor.worker.threads property to 5.
  3. Save the changes.
  4. Click Hive > Configuration and type hive-site into the search box to update Hive Metastore Server Advanced Configuration Snippet (Safety Valve) for hive-site.xml. Click + and add the following properties:
    1. Set the hive.compactor.initiator.on property to true.
    2. Set the hive.compactor.worker.threads property to 5.
    3. Set the hive.metastore.runworker.in property to hs2.
  5. Save the changes.
  6. Verify that the Hive scratch directory has an access control list (ACL) set for the Db2 Big SQL administrator user ID. Check the Hive configuration for hive.exec.scratchdir in Cloudera Manager (the default is /tmp/hive/hive). The ACL is necessary for compaction to run successfully. Be sure to also set the ACL on /tmp/hive. For example, as the hdfs user, run the following commands to get the current ACLs: 
    hadoop fs -getfacl /tmp/hive
hadoop fs -getfacl /tmp/hive/hive
    If /tmp/hive does not exist, create it and then define the correct ACL. If the Db2 Big SQL administrator user ID is not listed, be sure to add it. For example, as the hdfs user, run the following commands to set the ACLs:
    hadoop fs -setfacl -R -m default:user:bigsql:rwx /tmp/hive
hadoop fs -setfacl -R -m default:user:bigsql:rwx /tmp/hive/hive
    If the scratch directory is not empty, delete the existing contents.
  7. Update the $BIGSQL_HOME/conf/bigsql-conf.xml file to add the following entry:
    bigsql.table.transactional.enabled = true
  8. Restart the Hive, Hive on Tez, and Db2 Big SQL services.