Configuring the IBM Big SQL server

You can change how IBM® Big SQL runs in your environment by changing Big SQL database resources, and MapReduce resources. You can also manage and update the Big SQL password.

Updating the Linux kernel runtime parameters

You might see your queries fail with SQL1229N when you run many concurrent queries on your Big SQL clusters. This error usually indicates a node failure or connection failure. One of the possible causes of this failure is an incorrect kernel configuration for the network adapter cards.

You can avoid these kind of connection issues in Big SQL by updating the Linux kernal runtime parameters. You can view or update the kernal settings by using the /sbin/sysctl command.

To view the current values, type the following command:

/sbin/sysctl -a

For a 10GBit Ethernet card, use the following list as a recommended guideline when you run a large set of queries concurrently:


sysctl -w net.core.netdev_max_backlog=262144
sysctl -w net.core.wmem_max=134217728
sysctl -w net.core.rmem_max=134217728
sysctl -w net.core.wmem_default=134217728
sysctl -w net.core.rmem_default=134217728
sysctl -w net.core.optmem_max=134217728
sysctl -w net.ipv4.tcp_rmem="8192 87380 134217728"
sysctl -w net.ipv4.tcp_wmem="8192 87380 134217728"
sysctl -w net.core.somaxconn=262144
sysctl -w net.ipv4.tcp_mem="6093984 8125312 32777216"

Changes that you make with the sysctl command are reset to their defaults after a reboot. To make these changes permanent, add them into the /etc/sysctl.conf file.

Updating the database resource percentage for the Big SQL database instance

When you install Hortonworks Data Platform, the database components page provides defaults for node resources percentage. As the administrator, you can change that value after you install the product. Be aware however, that the percentages that you use for the database resources affect the resources that remain for Hadoop Map/Reduce processes.

As the bigsql user, run the following command from the active/primary headnode to verify that all Big SQL services are running:
```
$BIGSQL_HOME/bin/bigsql status
```
If Big SQL is stopped, start Big SQL with the following command:
```
$BIGSQL_HOME/bin/bigsql start
```
Log on to the Big SQL head node by running the following commands:
```
cd $BIGSQL_HOME/bin
```
```
su bigsql
```
When prompted, enter the bigsql administrator password that you created at installation time.
If you want to know the maximum amount of memory to be consumed with the current configuration, run the following commands:
Issue the ATTACH command to connect the application to the database instance:
```
db2 attach to bigsql
```
Show the detail of the INSTANCE_MEMORY configuration
```
db2 get dbm cfg show detail | grep INSTANCE_MEMORY
```
The result shows both the percentage and the actual memory that is allocated in 4 K units.
Terminate this session
```
db2 terminate
```
As the owner of the Big SQL instance, run the following command to connect to the bigsql database:
```
db2 connect to BIGSQL USER <bigsql_user> USING <bigsql_pw>
```
As the owner of the Big SQL instance, run the following commands to update the node resources percentage:
```
db2 "call syshadoop.big_sql_service_mode('on')"
db2 autoconfigure using mem_percent <percentage> workload_type complex is_populated no apply db and dbm
db2 "call syshadoop.big_sql_service_mode('off')"
```
In the example, substitute an integer value 1 - 90 in place of <percentage>. The value in <percentage> represents the percentage of memory on the machine that the local database is allowed to consume.
Disconnect from the BIGSQL database and then terminate the session by using the following two commands:
```
db2 disconnect BIGSQL 
```
```
db2 terminate
```
As the bigsql user, run the following commands from the active/primary headnode to stop and then restart Big SQL:
```
$BIGSQL_HOME/bin/bigsql stop
```
```
$BIGSQL_HOME/bin/bigsql start
```
You can verify the change in the resources percentage by using the following command:
```
cd $BIGSQL_HOME/bin
```
```
su bigsql
```
```
db2 get dbm cfg | grep INSTANCE_MEMORY
```
In the Ambari UI, navigate to Big SQL service > Configs > Advanced bigsql-env > Big SQL Resource Allocation and update the value to match the resource percentage set in step 5.
After you change the Big SQL resource percentage, you must reduce the resources that are available to MapReduce jobs. See Alter the resources available to MapReduce jobs.

Alter the resources available to MapReduce jobs

If you increase the resources that are available to Big SQL, you must reduce the resources that are available to MapReduce jobs. For example, if you increase the resources that are available to Big SQL from 25% to 50%, then reduce the resources that are available to MapReduce jobs from 75% to 50%.

Follow these steps to change the resource availability:

In the Ambari UI, you can adjust the MapReduce resource allocation from the Configs tab on the MapReduce service page.
Click Restart all affected for the changes to take effect.