Shell commands for SQL Data Insights configuration, administration, and object model management

SQL Data Insights (SQL DI) provides a command line interface through shell scripts, particularly the main sqldi.sh script. You can invoke the script with a set of commands to configure SQL DI in silent mode, administer its server settings, create connections and AI objects, enable objects for AI query, and manage object models.

Syntax

You can execute the sqldi.sh shell script and issue a command as shown below:

sqldi.sh [action] [action options] [-Xms <value>] [-Xmx <value>]

Where:

action refers to a specific command.
action options refers to the options that you can specify for the command.
-Xms and -Xmx refer to the options for setting the initial memory size and the maximum memory size for your JVM configuration.

Commands

You can execute the sqldi.sh to perform a specific action with the following commands:

create: Configures the SQL DI application. The command requires the -s <install_config> option for SQL DI configuration in silent mode.
create_connection: Adds a connection from SQL DI to Db2. This command requires the -f <user_config_file> option.
create_credential: Creates a credentials file that stores and encrypts the username and password for accessing SQL DI and connecting to Db2. The command requires the -o <path_to_output_file> option.
create_object: Adds an AI object to a specific connection and enables the object for AI query. This command requires the -f <user_config_file> option.
deploy: Deploys the retrained model for an AI object of a specific connection. This command requires the -f <user_config_file> option.
list_all_objects: Lists all AI objects for a specified connection. This command requires the -f <user_config_file> option.
list_column_config: Retrieves the column configuration for an AI object. This command requires the -f <user_config_file> option. If needed, also specify the -o <path_to_output_file> option to save the command output or the column configuration.
list_connections: Lists the connections from SQL DI to the same Db2 system. This command requires the -f <user_config_file> option.
list_default_settings: Retrieves the default settings of the SQL DI server. This command requires the -f <user_config_file> option.
list_object: Retrieves the details of a specific AI object for the specified connection. This command requires the -f <user_config_file> option. Optionally, specify the -status_rc option if you want the command to return the AI query enablement status of the object.
list_settings: Retrieves the current settings of the SQL DI server. This command requires the -f <user_config_file> option.
remove_connection: Removes a connection from SQL DI to Db2. This command requires the -f <user_config_file> option.
remove_model: Stops model retraining for an AI object of a specific connection. This command requires the -f <user_config_file> option.
remove_object: Removes an AI object from a specific connection. This command requires the -f <user_config_file> option.
retrain: Starts model retraining for an AI object of a specific connection. This command requires the -f <user_config_file> option.
set_settings: Modifies the current settings of the SQL DI server. This command requires the -f <user_config_file> option.
start: Starts the SQL DI application.
start_spark: Starts the embedded Spark cluster.
stop: Stops the SQL DI application.
stop_spark: Stops the embedded Spark cluster.
update_connection: Modifies a connection from SQL DI to Db2. This command requires the -f <user_config_file> option.
update_group: Updates the RACF® group that is used to manage SQL DI user access.
update_keyring: Updates RACF keyring information.

Options

You can specify the following options for certain shell commands:

-s <install_config>: Specifies the input file when you issue the create command for configuring SQL DI in silent mode. The input file contains all the properties and values for the configuration.
-o <path_to_output_file>: Specifies the path and file to store the output credentials file when you issue the create_credential command or retrieve the column configuration of an AI object when you issue the list_column_config command.
-f <user_input_file>: Specifies the input file when you issue the create_connection, create_object, deploy, list_column_config, list_connections, list_all_objects, list_object, list_default_settings, list_settings, remove_connection, remove_model, remove_object, retrain, set_settings, and update_connection commands.
-status_rc: Retrieves the AI query enablement status of an object when you specify it for the list_object command. Without the option, the command returns the processing status (success or failure) of the command action.
-Xms '': Specifies the initial size of memory that is allocated for the JVM program. This option is not required.
-Xmx '': Specifies the maximum size of memory that is allocated for the JVM program. The option is not required.

Input files

When you use the shell commands to configure SQL DI in silent mode, manage connections and object models, and administrate the application, you must specify an input file. The input file contains the properties and values that you want to set or accept for a specific command action.

SQL DI provides input file templates that you can copy and customize. Each supplied template file contains the configuration properties and in some cases, environment variables, for a specific command action. Some properties are required, while others are optional. For a required property, specify a value based on your SQL DI environment or the purpose of a command action. Otherwise, accept the default value, if available, for a required property. Do not include any additional shell commands or environment variable expansions when you customize the settings_scripts.template, connection_scripts.template, or aiobject_scripts.template files. SQL DI does not treat the resulting input files as executables.

The template files are USS text entities, and you can edit them with a text editor, such as OEDIT or vi. You can also rename the template files after you customize them. For the new file name, consider using alphanumeric characters, underscores, and periods, which helps avoid potential character conversion errors if you invoke the sqldi.sh shell script with JCL. In addition, consider encoding and tagging the renamed file as an EBCDIC IBM-1047, ASCII ISO8859-1, or Unicode UTF-8 file.

You can find the following templates in the $SQLDI_INSTALL_DIR/templates directory:

install_file.template: Provides input to the create command for SQL DI configuration in silent mode.
settings_scripts.template: Provides input to the set_settings, list_settings, and list_default_settings commands for SQL DI administration.
connection_scripts.template: Provides input to the create_connection, update_connection, list_connection, and remove_connection commands for connection management.
aiobject_scripts.template: Provides input to the create_object, deploy, list_all_objects, list_column_config, list_object, retrain, remove_model, and remove_object commands for object model management.

Examples

The following examples show how you invoke the sqldi.sh script with different shell commands and options.

For configuring SQL DI interactively or silently:

./sqldi.sh create

./sqldi.sh create -s install_file

./sqldi.sh create -Xms 512M -Xmx 1024M

For managing SQL DI application:
```
./sqldi.sh start
```
```
./sqldi.sh stop
```

For managing the embedded Spark cluster:

./sqldi.sh start_spark

./sqldi.sh stop_spark

For administrating SQL DI server environment and settings:

./sqldi.sh update_keyring

./sqldi.sh update_group

./sqldi.sh set_settings -f settings_scripts_file

./sqldi.sh list_default_settings -f settings_scripts_file

./sqldi.sh list_settings -f settings_scripts_file

For creating and encrypting SQL DI and Db2 user credentials:
```
./sqldi.sh create_credential -o credential_file
```

For managing the connections from SQL DI to Db2:

./sqldi.sh create_connection -f connection_scripts_file

./sqldi.sh update_connection -f connection_scripts_file

./sqldi.sh list_connections -f connection_scripts_file

./sqldi.sh remove_connection -f connection_scripts_file

For managing SQL DI AI query enablement and object models:

./sqldi.sh create_object -f aiobject_scripts_file

./sqldi.sh list_object -f aiobject_scripts_file -status_rc

./sqldi.sh list_all_objects -f aiobject_scripts_file

./sqldi.sh list_column_config -f aiobject_scripts_file -o column_config_file

./sqldi.sh remove_object -f aiobject_scripts_file

./sqldi.sh retrain -f aiobject_scripts_file

./sqldi.sh deploy -f aiobject_scripts_file

./sqldi.sh remove_model -f aiobject_scripts_file

Command aliases

When the SQL DI installation and configuration process completes successfully, the sqldi.sh script appends the following set of command aliases to your $HOME/.profile. After you execute a source $HOME/.profile command, you can use the aliases to manage the SQL DI application and the embedded Spark cluster.

start_sqldi: Starts the SQL DI application.
stop_sqldi: Stops the SQL DI application.
start_spark: Starts the embedded Spark cluster.
stop_spark: Stops the embedded Spark cluster.

If needed, you can create aliases for other SQL DI shell commands that you use frequently with the sqldi.sh script.