Run Unix Shell

The module starts a Unix shell command, waits for the execution result, and optionally writes STDOUT to the datasqill log. If an error occurs during command execution, the module fails and displays the result from STDERR. STDOUT and STDERR can be viewed in the run. The command is started with the datasqill Unix user.

The module can be used to execute shell commands as well as to control external tools. The module supports Apache Freemarker logic and can also use datasqill variables in the Action Body.

Name Meaning
Module Run Unix Shell
Module Class DsModRunShell
Type Java
Purpose Execute Unix commands
Transformation Code Shell command(s)
Sources not required
Targets not required

Attributes

Name Type Meaning
Monitor Stdout Boolean Write STDOUT to the datasqill log. The stdout log is visible in the run.
Loop Query SQL Query to invoke shell calls multiple times with parameters
Error if Loop empty Boolean When enabled and the Loop Query returns no results, the transformation is terminated with an error

Variables

The module always provides the following variables:

Name Data Type Meaning
request_id Number ID of the unique request (see request table)
schedule_date String Start date in UTC time of the batch in format yyyy-MM-ddTHH:mm:ss.SSSSSSSSSZ
action String The transformation instruction (Shell script)
iteration Number For Loop Queries there is a sequential number per loop result. Otherwise 1
batch_instance_id Number ID of the batch instance (running batch), or -1 during Validate
action_id Number ID of the action (transformation)
action_name Number Name of the action (transformation)
module_name Number Name of the module
module_start_timestamp String Module execution start in local time in format yyyy-MM-dd HH:mm:ss.SSS
module_start_datetime String Module execution start in local time in format yyyy-MM-dd HH:mm:ss
module_start_date String Module execution start in local time in format yyyy-MM-dd
module_utc_start_timestamp String Module execution start in UTC time in format yyyy-MM-dd HH:mm:ss.SSS
module_utc_start_datetime String Module execution start in UTC time in format yyyy-MM-dd HH:mm:ss
module_utc_start_date String Module execution start in UTC time in format yyyy-MM-dd
connection_id Number Database ID (Connection ID) of the database connection when there is a Loop Query
sources Object A list of source objects of data type ObjectDs
targets Object A list of target objects of data type ObjectDs

When there is a loop query, all result columns of the loop query are also made available as variables.

The data type ObjectDs is derived directly from the vv_sqts_object table in the repository database:

Name Data Type Comment
sqtsObjectId Number Unique ID of the object (table)
sqtsObjectTypeId Number ID of the object type.
serverId Number ID of the connection (database_id)
objectName Number Name of the object (table name)
objectOwner Number Name of the owner (schema name)
objectPartition Number Partition name

STDOUT Output

The output of the shell script can optionally be stored in the run data so that the output can be displayed in the datasqill GUI.

In addition, outputs can be generated that are written to the logs or that store statistical information about the number of records processed.

To do this, the output mode can be switched to channels. The following commands can be sent as complete lines:

#datasqill stdout#
#datasqill log#
#datasqill stat#

With one of these commands, you switch to the desired output mode (channel). By default, the stdout channel is selected.

stdout channel

The normal output of the shell script can optionally be stored in the run data so that the output can be displayed in the datasqill GUI.

log channel

A JSON is expected on this channel. The JSON has the following format:

{
  "level" : "SEVERE/WARNING/INFO/CONFIG/FINE/FINER/FINEST",
  "message" : "Your log message here"
}
  • Case does not matter for level.
  • If no valid JSON is sent, the message is ignored
  • These log messages are sent to the existing datasqill logging system

stat channel

A JSON is expected on this channel. The JSON has the following format:

[
    { "serverId" : 1, "name": "stat 1", "value": 34 },
    { "serverId" : 1, "name": "stat 2", "value": 15 },
    { ... },
    { "serverId" : 1, "name": "stat n", "value": 5 }
]
  • serverId indicates the connection number for which the values apply
  • Case does not matter for name
  • Numeric values are expected for serverId and value
  • If no valid JSON is sent, the message is ignored
  • Currently the last statistic is displayed if several were received

The following statistical values (for name) are currently offered: | Name | Meaning| | :------------------------- | :------------- | | rows_processed | Number of rows processed | | rows_ins | Number of rows inserted | | rows_del | Number of rows deleted | | rows_upd_ignored | Number of rows that were ignored | | rows_upd_non_versioned | Number of rows that were modified without versioning | | rows_upd_versioned | Number of rows that were modified with versioning | | rows_upd_non_increment | Number of rows that were modified without increment detection | | rows_rejected | Number of rows rejected | | row_width | Maximum row width of all rows | | read_duration | Read time in µ seconds | | write_duration | Write time in µ seconds | | convert_duration | Other time in µ seconds | | delete_duration | Delete time in µ seconds | | java_memory | Maximum size of the heap used | | tempspace | Maximum temporary tablespace used | | pga_memory | Maximum database memory used | | session_id | Session number |

Examples

The examples show the Action Body code.

Starting simple shell commands

sleep 5
echo "I am awake"

Shell commands using Freemarker directly in the Action Body

sleep 5
echo "I am awake at ${.now?string.iso}"

Starting a shell script

~/my_script.sh ${batch_instance_id}

Starting an external Python script parameterized with the date

python3 script.py '${schedule_date?string[0..9]}'

Disabling the Freemarker interpreter in shell commands

# The interpreter is on. So Freemarker replaces the variable
# If the variable is not set in Freemarker, Freemarker reports an error
echo ${free_marker_variable}

[#noparse]
# The interpreter is off. So Freemarker no longer replaces anything
echo ${bash_variable}

[/#noparse]
# The interpreter is on. So Freemarker replaces the variable
# If the variable free_marker_variable_2 is not set in Freemarker, the text "otherwise" is used
echo ${free_marker_variable_2!otherwise}
# If the variable free_marker_variable_3 is not set in Freemarker, the text is empty
echo ${free_marker_variable_3!}

# Outputs the concatenation of a Freemarker and a Bash variable
echo ${free_marker_variable}[#noparse]${bash_variable}[/#noparse]

Output of logs and statistics

echo "This is a normal message"
echo "#datasqill log#"
echo '{ "level": "fine", "message": "Log message sddssdsdsd" }'
echo '{ "level": "info", "message": "Info message1" }'
echo '{ "level": "xxx", "message": "Info message2" }'
echo "#datasqill log#"
echo '{ "level": "xxx", "message": "Info message3" }'
echo "#datasqill stdout#"
echo "This is a normal message again"
echo "#datasqill stat#"
cat << EOF
[
    { "serverId" : 10, "name": "rows_processed", "value": 34 },
    { "serverId" : 10, "name": "rows_ins", "value": 15 },
    { "serverId" : 10, "name": "rows_upd_versioned", "value": 14 },
    { "serverId" : 10, "name": "rows_del", "value": 5 }
]
EOF
echo "#datasqill stdout#"
echo "And again a normal message"
echo "#datasqill log#"
sleep 1
echo '{ "level": "warning", "message": "Test warning" }'
echo "#datasqill stdout#"
echo ${request_id}

The stdout output:

This is a normal message
This is a normal message again
And again a normal message
95420

And the logging (excerpt):

2025.11.13 18:49:58.561 [DsModRunShell runInLoop] [#include "dsmod_runshell.ftl"]
${interpret(action)}
2025.11.13 18:49:58.687 [          Shell fine] Log message sddssdsdsd
2025.11.13 18:49:58.687 [          Shell info] Info message1
2025.11.13 18:49:58.693 [           Shell xxx] Info message2
2025.11.13 18:49:58.693 [           Shell xxx] Info message3
2025.11.13 18:49:59.682 [       Shell warning] Test warning
2025.11.13 18:49:59.683 [DsModRunShell runInLoop] Loop Query end

Note that the log outputs are live. Thus the log output "Test warning" appears one second after the previous one.

And here the statistics:

Statistics