9. Manage Further Application Actions¶
Besides the mail export action, the following action types can be added to an application. All of them share the trigger parameters described for the mail export action: p_finished_type, p_valid_from_bdate, p_active, p_run_once, p_run_delay, p_bdate_retention, p_cal_name, p_cal_key, and p_bdate_retention_future.
ACTION TYPE |
API |
PURPOSE |
|---|---|---|
notification by mail without attachment |
||
export_file |
export into a directory on the RED server |
|
export_sftp |
export to an sFTP server |
|
jexec |
start an external job, e.g. in Control-M |
|
sexec |
execute a LUA script in the database |
|
materialize |
materialise views into tables |
Each API has a corresponding MODIFY script; actions of all types are removed with API_REDADM.REMOVE_APP_ACTION and switched on or off with API_REDADM.TOGGLE_APP_ACTION.
9.1. Send a Notification Mail¶
An email action sends a mail based on a template in REDADM.ADM_MAIL_PARAMS, e.g. app_notify_final.
Example: Notify When the Application Is Final
1EXECUTE SCRIPT API_REDADM.ADD_APP_ACT_MAIL(
2 'TEST_APP' -- p_app_name
3 ,'notify_final' -- p_action_name
4 ,'notification' -- p_description
5 ,'FINAL' -- p_finished_type
6 ,'app_notify_final' -- p_mail_template
7 ,'team@example.com' -- p_recipients
8 ,'2024-08-01' -- p_valid_from_bdate
9 ,true -- p_active
10 ,true -- p_run_once
11 ,0 -- p_run_delay
12 ,3 -- p_bdate_retention
13 ,null -- p_cal_name
14 ,null -- p_cal_key
15 ,0 -- p_bdate_retention_future
16);
9.2. Export a File to a Directory¶
An export_file action writes an action export into a directory on the RED server. The directory must be an absolute path.
Example: Export into a Directory
1EXECUTE SCRIPT API_REDADM.ADD_APP_ACT_EXP_FILE(
2 'TEST_APP' -- p_app_name
3 ,'export_dir' -- p_action_name
4 ,'daily export' -- p_description
5 ,'FINAL_DQ' -- p_finished_type
6 ,'test_exp' -- p_exp_name
7 ,'/app/redsys/env/tst1/data/out' -- p_dir_name
8 ,'2024-08-01' -- p_valid_from_bdate
9 ,true -- p_active
10 ,true -- p_run_once
11 ,0 -- p_run_delay
12 ,3 -- p_bdate_retention
13 ,null -- p_cal_name
14 ,null -- p_cal_key
15 ,0 -- p_bdate_retention_future
16);
9.3. Export a File to an sFTP Server¶
For an export_sftp action, first define the target server with API_REDADM.ADD_APP_ACT_EXP_SFTP_PAR. The password is stored encrypted, and the target directory may contain the placeholder @{bdate}.
Example: Define an sFTP Target and the Export Action
1EXECUTE SCRIPT API_REDADM.ADD_APP_ACT_EXP_SFTP_PAR(
2 'partner_sftp' -- p_sftp_name
3 ,'sftp.partner.example.com' -- p_sftp_host
4 ,'red_upload' -- p_sftp_user
5 ,'<password>' -- p_sftp_pass
6 ,'/inbound/@{bdate}' -- p_sftp_dir
7);
8
9EXECUTE SCRIPT API_REDADM.ADD_APP_ACT_EXP_SFTP(
10 'TEST_APP' -- p_app_name
11 ,'export_sftp' -- p_action_name
12 ,'delivery to partner' -- p_description
13 ,'FINAL_DQ' -- p_finished_type
14 ,'test_exp' -- p_exp_name
15 ,'partner_sftp' -- p_sftp_name
16 ,'2024-08-01' -- p_valid_from_bdate
17 ,true -- p_active
18 ,true -- p_run_once
19 ,0 -- p_run_delay
20 ,3 -- p_bdate_retention
21 ,null -- p_cal_name
22 ,null -- p_cal_key
23 ,0 -- p_bdate_retention_future
24);
9.4. Start an External Job¶
A jexec action calls the script cmd/jexec.sh of the environment instance on the RED server, e.g. to start a job in an external scheduler. The call parameter and two optional parameters are passed to the script together with the business date, the application name, and the time of the last data change.
Example: Start an External Job
1EXECUTE SCRIPT API_REDADM.ADD_APP_ACT_JEXEC(
2 'TEST_APP' -- p_app_name
3 ,'start_job' -- p_action_name
4 ,'Control-M job' -- p_description
5 ,'FINAL_DQ' -- p_finished_type
6 ,'SALES_REPORT' -- p_jexec_call
7 ,null -- p_jexec_par1
8 ,null -- p_jexec_par2
9 ,'2024-08-01' -- p_valid_from_bdate
10 ,true -- p_active
11 ,true -- p_run_once
12 ,0 -- p_run_delay
13 ,3 -- p_bdate_retention
14 ,null -- p_cal_name
15 ,null -- p_cal_key
16 ,0 -- p_bdate_retention_future
17);
9.5. Execute a Database Script¶
An sexec action executes a LUA script in the database via RED-PSS. The script name must be fully qualified (SCHEMA.SCRIPT), and the script must accept its parameters as an array of ‘name,value’ pairs. The following parameters are passed: APP_NAME, APP_LAST_CHANGE, BDATE (format YYYYMMDD), and EXEC_PAR1 / EXEC_PAR2 (p_exec_par1 / p_exec_par2).
The script runs in a separate database session, and the action waits until the script has finished. If the script fails, the action ends with ERROR and is retried (see Monitor Actions).
Example: Execute a Script
1EXECUTE SCRIPT API_REDADM.ADD_APP_ACT_SCRIPT(
2 'TEST_APP' -- p_app_name
3 ,'run_script' -- p_action_name
4 ,'post-processing' -- p_description
5 ,'FINAL' -- p_finished_type
6 ,'APB_SALES.S_REFRESH' -- p_script
7 ,null -- p_exec_par1
8 ,null -- p_exec_par2
9 ,'2024-08-01' -- p_valid_from_bdate
10 ,true -- p_active
11 ,true -- p_run_once
12 ,0 -- p_run_delay
13 ,3 -- p_bdate_retention
14 ,null -- p_cal_name
15 ,null -- p_cal_key
16 ,0 -- p_bdate_retention_future
17);
9.6. Materialise Views¶
A materialize action copies views of a centrally managed application mart (APB) into tables, e.g. for fast self-service BI. The objects are defined in a materialisation group:
Create the group with API_REDADM.ADD_APP_MAT_GROUP.
Add the objects with API_REDADM.ADD_APP_MAT_OBJECT, in the order of materialisation (p_mat_order). Before inserting, the action deletes rows of the target table. p_bdate_filter_column restricts both the delete and the insert to the business date of the action (with p_use_date_range true: from this business date on). p_del_where_clause adds a further condition to the delete (‘false’ keeps all rows).
Add the action with API_REDADM.ADD_APP_ACT_MAT.
Example: Materialise a Sales View per Business Date
1EXECUTE SCRIPT API_REDADM.ADD_APP_MAT_GROUP(
2 'SALES_MAT' -- p_name
3 ,'sales mart materialisation' -- p_description
4);
5
6EXECUTE SCRIPT API_REDADM.ADD_APP_MAT_OBJECT(
7 'SALES_MAT' -- p_mat_group
8 ,1 -- p_mat_order
9 ,'APB_SALES' -- p_mat_base_schema
10 ,'V_SALES_DAILY' -- p_mat_base_name
11 ,'APB_SALES' -- p_mat_target_schema
12 ,'SALES_DAILY' -- p_mat_target_name
13 ,null -- p_del_where_clause
14 ,'BDATE' -- p_bdate_filter_column
15 ,false -- p_use_date_range
16);
17
18EXECUTE SCRIPT API_REDADM.ADD_APP_ACT_MAT(
19 'TEST_APP' -- p_app_name
20 ,'materialize_sales' -- p_action_name
21 ,'materialise sales mart' -- p_description
22 ,'FINAL_DQ' -- p_finished_type
23 ,'SALES_MAT' -- p_mat_group
24 ,'2024-08-01' -- p_valid_from_bdate
25 ,true -- p_active
26 ,false -- p_run_once
27 ,0 -- p_run_delay
28 ,3 -- p_bdate_retention
29 ,null -- p_cal_name
30 ,null -- p_cal_key
31 ,0 -- p_bdate_retention_future
32);
Exports and jobs that need the materialised tables use the trigger type MAT: they start as soon as all materialisation actions of the business date have finished.
9.7. Restrict Actions to a Time Window¶
With API_REDADM.ADD_APP_ACT_WINDOW, an action only starts within a time window (format HHMM), e.g. outside the business hours of a target system.
Example: Only Start Between 18:00 and 23:00
1EXECUTE SCRIPT API_REDADM.ADD_APP_ACT_WINDOW(
2 'TEST_APP' -- p_app_name
3 ,'export_sftp' -- p_action_name
4 ,'1800' -- p_start_time
5 ,'2300' -- p_end_time
6);
9.8. Monitor Actions¶
The status of the actions per business date is shown in REDADM.STA_APP_ACTION_STATUS, the details of each run (including error messages) in REDADM.STA_APP_ACTION_DETAIL.
At most one execution per action and business date is open at a time. Failed actions are retried automatically after 300 seconds, or immediately if new data arrives for the business date. After 99 failed attempts the action waits until the error has been fixed and reset:
Example: Reset the Errors of Application Actions
redctl we_list_error -s REDADM -w app_act
redctl we_reset_error -s REDADM -w app_act