Automate Recurring Data Updates In SAP HANA Cloud

Introduction

In a recent demo scenario, there was a need to automatically update product inventory records in SAP HANA Cloud when they had not been refreshed for more than 30 days. This is a common pattern for housekeeping and status management, and it can be handled neatly using the built-in scheduler in SAP HANA Cloud through SAP HANA Database Explorer.

For the scheduler syntax and options, refer to SAP Help: Create Scheduler Job Statement.

Assumed PRODUCT_INVENTORY Table Schema

For this example, assume there is a PRODUCT_INVENTORY table with the following structure:

CREATE COLUMN TABLE PRODUCT_INVENTORY (
    PRODUCT_ID NVARCHAR(20) NOT NULL PRIMARY KEY,
    PRODUCT_NAME NVARCHAR(255) NOT NULL,
    QUANTITY INTEGER DEFAULT 0,
    INVENTORY_STATUS NVARCHAR(20) DEFAULT 'ACTIVE', -- 'ACTIVE', 'OUTDATED'
    UPDATED_AT TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

The requirement is to mark records as OUTDATED when their UPDATED_AT value is older than 30 days and the current status is ACTIVE.

Use-case

Assume there is a PRODUCT_INVENTORY table and the requirement is to mark records as OUTDATED when their UPDATED_AT value is older than 30 days. This approach keeps the logic inside the database and works well for simple recurring operational tasks.

Step 1: Create the procedure

CREATE OR REPLACE PROCEDURE .PR_UPDATE_PRODUCT_INVENTORY_STATUS ()
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
    UPDATE .PRODUCT_INVENTORY
       SET INVENTORY_STATUS = 'OUTDATED',
           UPDATED_AT = CURRENT_TIMESTAMP
     WHERE INVENTORY_STATUS = 'ACTIVE'
       AND UPDATED_AT < ADD_DAYS(CURRENT_TIMESTAMP, -30);
END;

Step 2: Schedule the procedure

In SAP HANA Cloud, job scheduling is done with CREATE SCHEDULER JOB. The HANA Cloud cron format uses a 7-field pattern:

Year, Month, Day, DayOfWeek, Hour, Minute, Second

To run the procedure every day at midnight, use:

CREATE SCHEDULER JOB 
.JOB_UPDATE_PRODUCT_INVENTORY
CRON '* * * * 0 0 0'
ENABLE
PROCEDURE .PR_UPDATE_PRODUCT_INVENTORY_STATUS;

Run the procedure on demand

If you want to execute the procedure manually instead of waiting for the scheduled job, you can run it directly in SAP HANA Database Explorer using the CALL statement. This is useful for testing the procedure logic before enabling the scheduler or for triggering an immediate update when needed.

CALL .PR_UPDATE_PRODUCT_INVENTORY_STATUS();

Monitor the job

After creating the job, you can verify it using the scheduler monitoring views in HANA Cloud. The scheduler metadata is available in M_SCHEDULER_JOBS, and the correct job-name column is SCHEDULER_JOB_NAME.

SELECT *
FROM M_SCHEDULER_JOBS
WHERE SCHEDULER_JOB_NAME = 'JOB_UPDATE_PRODUCT_INVENTORY';

Clean-up

If the procedure and scheduler job are no longer needed, they can be removed with standard SQL statements in SAP HANA Cloud. Use DROP SCHEDULER JOB to delete the scheduled job and DROP PROCEDURE to remove the stored procedure.

DROP SCHEDULER JOB .JOB_UPDATE_PRODUCT_INVENTORY;

DROP PROCEDURE .PR_UPDATE_PRODUCT_INVENTORY_STATUS;

If needed, you can verify the objects are gone by checking M_SCHEDULER_JOBS and the procedure catalog views afterward.

Alternative scheduling options

For scenarios where scheduling should be managed outside the database, these are common alternatives on SAP BTP:

SAP Integration Suite – Timer Event
Useful when the database update is one step in a broader integration or orchestration flow.

SAP Automation Pilot Service
Commands for infrastructure operations can be scheduled within the SAP Automation Pilot, making it a good fit for maintenance and capacity management scenarios, for example: starting and stopping an SAP HANA Cloud instance at scheduled times.

Key Takeaways

For lightweight recurring database updates, the built-in SAP HANA Cloud scheduler is the simplest option because the logic stays inside HANA Cloud and is easy to maintain and monitor using SAP HANA Database Explorer. If the process spans multiple systems or requires platform-level orchestration, consider using SAP Integration Suite Timer Event, SAP Job Scheduling Service, or a custom CAP-based approach. For enterprise applications, CAP is often the recommended approach because it allows you to trigger CAP REST APIs on specific schedules or cron expressions without tying up your app’s core resources.

Source link