Support for Big SQL

Big SQL V 6.0.0 is supported from HDP 3.1 with Mpack 2.7.0.3 and HDFS Transparency 3.1.0-1.

The IBM Db2 Big SQL service > Configs > Advanced bigsql-env > Database path cannot be changed after Big SQL is installed. Therefore, Big SQL must be installed after IBM Spectrum® Scale service is installed so that the Database path is set correctly to use the IBM Spectrum Scale storage.

For more information about IBM® Db2® Big SQL V 6.0.0, see IBM Db2 Big SQL V6.0 documentation.

Issue:

All the HDP services are up but the Big SQL job might fail with the following error if the hdp_version (after Big SQL integration) is not setup properly:

[04:16:25 ERROR     ]    <Stream 1-Main>    Caused by: 
[04:16:25 ERROR     ]    <Stream 1-Main>    com.ibm.utm.core.sql.UtmSqlException: Error executing statement: 
LOAD HADOOP USING FILE URL '/user/user1/biga_load//data/small_types_pos_data.unl' WITH SOURCE PROPERTIES 
('field.delimiter'='|', 'date.time.format'='yyyy-MM-dd-HH.mm.ss.SSSSSSSSS', 
'date.time.format'='yyyy-MM-dd-HH.mm.ss.SSSSSS', 'date.time.format'='MM-dd-yyyy',
'escape.char'='\\') INTO TABLE ALL_TYPES_TAB
Sql Code:  -5111
IsamCode:  0
Sql State: 58005
[04:16:25 ERROR     ]    <Stream 1-Main>    Error code: -5111
[04:16:25 ERROR     ]    <Stream 1-Main>    SQLSTATE  : 58005
[04:16:25 ERROR     ]    <Stream 1-Main>    Message   : The LOAD HADOOP statement failed because of an 
error with a component. Component name: "". Reason code: "4:URISyntaxExce". Log entry identifier: 
"[BSL-0-1d2d2361d]". Job identifier: "".. SQLCODE=-5111, SQLSTATE=58005, DRIVER=3.71.22
[04:16:25 ERROR     ]    <Stream 1-Main>      Caused by: 
[04:16:25 ERROR     ]    <Stream 1-Main>      com.ibm.db2.jcc.am.SqlException: The LOAD HADOOP statement 
failed because of an error with a component. Component name: "". Reason code: "4:URISyntaxExce". Log entry 
identifier: "[BSL-0-1d2d2361d]". Job identifier: "".. SQLCODE=-5111, SQLSTATE=58005, DRIVER=3.71.22

The JobHistory will have the following error:

Log Type: prelaunch.err 
Log Upload Time: Wed Apr 03 04:22:00 -0400 2019 
Log Length: 1027 
/hadoop/yarn/local/usercache/bigsql/appcache/application_1554277915663_0004
/container_e28_1554277915663_0004_02_000001/launch_container.sh: line 38: $PWD:$PWD
/mr-framework/hadoop/share/hadoop/mapreduce/*:$PWD/mr-framework/hadoop/share/hadoop/mapreduce/lib/*:
$PWD/mr-framework/hadoop/share/hadoop/common/*:$PWD/mr-framework/hadoop/share/hadoop/common/lib/*:
$PWD/mr-framework/hadoop/share/hadoop/yarn/*:$PWD/mr-framework/hadoop/share/hadoop/yarn/lib/*:
$PWD/mr-framework/hadoop/share/hadoop/hdfs/*:$PWD/mr-framework/hadoop/share/hadoop/hdfs/lib/*:
$PWD/mr-framework/hadoop/share/hadoop/tools/lib/*:
/usr/hdp/${hdp.version}/hadoop/lib/hadoop-lzo-0.6.0.${hdp.version}.jar:
/etc/hadoop/conf/secure:/usr/ibmpacks/bigsql/6.0.0.0/bigsql/hive-client/lib/*:
/usr/ibmpacks/bigsql/6.0.0.0/bigsql/hive-client/lib/*:/usr/hdp/3.1.0.0-78/atlas/hook/hive/*:
/usr/ibmpacks/bigsql/6.0.0.0/bigsql/hive-client/lib/hive-hcatalog-core-3.1.0.3.1.0.0-78.jar:job.jar/*:
job.jar/classes/:job.jar/lib/*:$PWD/*:$PWD/atlas-application.properties: bad substitution
 
Log Type: prelaunch.out 
Log Upload Time: Wed Apr 03 04:22:00 -0400 2019 
Log Length: 25 
Setting up env variables

To fix the issue:

On an HDP node, run the hdp-select versions command to get the HDP version.
```
[root@c902f14x01 ~]# hdp-select versions
3.1.0.0-78
[root@c902f14x01 ~]#
```
This example found 3.1.0.0-78 as the HDP version value.
Go to the Ambari GUI, select MapReduce2 service > Configs and search for hdp.version.
Replace all the ${hdp.version} values with the actual HDP version number.
For example, change all the fields below with ${hdp.version} tag to 3.1.0.0-78 value.
Restart all the required services.