Configuring the Db2 Big SQL server

You can change how Db2® Big SQL runs in your environment by changing Db2 Big SQL database resources and MapReduce resources. You can also manage and update the Db2 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 Db2 Big SQL clusters. This error usually indicates a node failure or connection failure. One of the possible causes of this failure is a suboptimal kernel configuration for the network adapter cards.

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

To view the current values, run 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 Db2 Big SQL database instance

When you install Cloudera 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 head node to verify that all Db2 Big SQL services are running:
```
$BIGSQL_HOME/bin/bigsql status
```
If Db2 Big SQL is stopped, start Db2 Big SQL with the following command:
```
$BIGSQL_HOME/bin/bigsql start
```
Log on to the Db2 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-KB units.
Terminate this session:
```
db2 terminate
```
As the owner of the Db2 Big SQL instance, run the following command to connect to the BIGSQL database:
```
db2 connect to BIGSQL USER <bigsql_user> USING <bigsql_pw>
```
Use the Db2 Big SQL configuration utility to update the memory percentage value. For example:
```
/usr/ibmpacks/IBM-Big_SQL/7.0.0.0/bigsql-cli/bigsql-config -set "BIGSQL_MEM_PERCENT=25"
```
For more information, see Db2 Big SQL configuration utility.
Disconnect from the BIGSQL database and then terminate the session by running the following commands:
```
db2 disconnect BIGSQL 
db2 terminate
```
As the bigsql user, run the bigsql-admin Db2 Big SQL cluster administration utility with the -stop option and then with the -restart to stop and then restart Db2 Big SQL:
```
./bigsql-admin -stop
./bigsql-admin -restart
```
You can verify the change in the resources percentage by running the following commands:
```
cd $BIGSQL_HOME/bin
su bigsql
db2 get dbm cfg | grep INSTANCE_MEMORY
```
After you change the Db2 Big SQL resource percentage, you must reduce the resources that are available to MapReduce jobs. See Alter the resources that are available to MapReduce jobs.

Alter the resources that are available to MapReduce jobs

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

Follow these steps to change the resource availability:

In Cloudera Manager, to adjust the MapReduce resource allocation, click Yarn > Resource Management.
Click Restart all affected for the changes to take effect.

For more information about tuning Db2 Big SQL memory usage, see Best Practice and Guidelines – Performance Adjusting Big SQL Memory Allocation.

Changing the Db2 Big SQL service user password

From time to time, it might be necessary to change the account password for the Db2 Big SQL service user. This change is usually made on systems that require passwords to expire after a certain time period. The password must be changed manually on each Db2 Big SQL node.

Modifying the bigsql-conf.xml file

You can use the Db2 Big SQL configuration utility to modify the bigsql-conf.xml file. For more information, see Db2 Big SQL configuration utility.