Skip to Content
Author's profile photo Rajesh Jeyaram Raja

Scheduling a job in SAP HANA using HDBSQL and windows task scheduler

Applies to:

SAP HANA developers.


Job scheduling option is important in all databases for particular scenarios. As of now SAP HANA does’nt have an option to schedule jobs. But we can have a workaround using hdbsql and windows task scheduler to schedule jobs in SAP HANA. In this document, we describe step-by-step procedure to follow for such a scenario.

Authors: Rajesh Jeyaram Raja, Prasath Vettrinathan, Nikhil Joy

Company: Infosys Limited

Created on: 04-Jan-2013

Author’s Bio

Rajesh Jeyaram Raja:

Rajesh has around 2 years of professional experience in the IT industry and has experience in SAP BW, BO and HANA . He is a Systems Engineer working in Infosys.

Prasath Vettrinathan:

Prasath has around 5 years of professional experience and has expertise in SAP BW, BO and HANA. He is an Associate consultant working in Infosys.

Nikhil Joy:

Nikhil has 2 years of professional experience and has expertise in SAP BW , BO and HANA. He is a Systems Engineer working in  Infosys.

Table of Contents

  • Introduction
  • The Scenario
  • Challenges faced
  • Workaround
  • References


SAP HANA as we know is the talk of the town and with its humongous capabilities has made a huge impact in the market. Since SAP HANA is still at its nascent stage it throws as many challenges. This document discusses about two such challenges faced and suggests a workaround for them.

The Scenario:

The requirement was to generate a file with pay roll data on last day of every month using the following data.

The Master data for the entire scenario included the Employee master data (Employee number, name and salary), Store master data (Store number, Area/Locality, Sales Manager). The daily transactions of the stores are stored in a separate table. There is also a table which shows the Monthly Target fixed for each employee.

Let us consider that the following are the data in the tables

Employee Master Table:


Store Master Table:


Sales Target Table:


Store Transaction Table:


The logic for the incentive is to compare the sales carried out by the employee in a month with his target and give him incentives as shown below.



Total sales is greater than twice the target set

10% of the base salary

Total sales is lesser than twice the target set but greater than or equal to  the target

5% of the base salary

Total sales is lesser than the target set

Challenges Faced:

The solution that was initially thought of was to create attribute view by joining the Employee master and Store master tables.


Then to create an analytic view with the Store transaction table and the above mentioned attribute view.


Finally creating a scripted calculation view to calculate the revised salary and update it to the table. But the calling of procedures with read/write operations was not allowed inside a calculation view.


The work around that was thought of was to create a procedure that reads the calculation view and update the details in the table. But the problem here was that the user had to run the calculation view every time whenever the details had to be updated.

To sum up, the following were the challenges faced in implementing this scenario

1.    Calling of a procedure with write operations is prohibited within a calculation view

2.    Scheduling of a procedure without the usage of third party scheduling tools


A fitting answer to overcome these challenges is HDBSQL – a command line tool that is available when HANA client is installed in a system.

As mentioned earlier , HDBSQL is a command line tool that can be used to access the database directly without the usage of HANA Studio. To know more on HDBSQL please refer to the HANA Database Administration guide.

As we became familiar with HDBSQL, we came up with workarounds

  1.    Using procedure to update the table with the data from calculation view
  2.    Using windows task scheduler along with HDBSQL to schedule the procedure.

A scripted calculation view is created as shown below.



A procedure is created to update the table as shown below.


Finally we could use HDBSQL to call the procedure and pass the data from the result table in to the file. But the procedure has to be scheduled. To do so a sql script is created as shown


\o is the command used in HDBSQL to pass the results of the query into a file.

Then a batch file is created which can call the script using HDBSQL.


Now this batch file can be scheduled using the Task scheduler.(Start->All programs->Accessories->System tools->Task scheduler). And then create a task and in Actions tab click on new and select the batch file that is to be run.


Finally in the triggers tab, the task can be scheduled based on the requirements. For analysis, we have chosen one time.



Task runs at the scheduled time. Here in our scenario, scheduled time is 5:37 AM.


Finally the file is generated with data as shown.



Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Lars Breddemann
      Lars Breddemann

      Nice setup and a pretty blog post!


      Two remarks:

      • Please don't ever use the logon parameters for hdbsql to schedule anything or to put them into a batch file.
        You're putting logon data to highly confidential information (salary data...) into a plain text file.
        Instead please do use the secure userstore (hdbuserstore) facility that allows to store the full set of logon data encrypted (see,
        , and my super old blog post HANA backup script  hint... a little bit more security, please! if you like to) for details on how to use it.
      • Maybe I don't see it, but why exactly are you looping over the result of your calc view when you could easily create a calculated measure directly in a calc view instead?
        You could then easily directly UPSERT the result of the calc view to your table.

      - Lars

      Author's profile photo Raj Kumar S
      Raj Kumar S

      Nice workaround. I am impressed.

      Author's profile photo Former Member
      Former Member

      Good work is of much help..thanks a lot:)

      Author's profile photo Former Member
      Former Member

      I hear that SAP maybe will add native job scheduler in next release.


      But not shore, because there are 0 information about SPS6.

      Author's profile photo Rama Shankar
      Rama Shankar

      Nice step-by-step - thanks.

      Author's profile photo Sreelatha kokkanti
      Sreelatha kokkanti

      Hi Rajesh,


      Nice workaround and it is very much helpful.

      But I have a query. Is this workaround specific to a particular system.

      How to schedule in the production environment ?

      How to transport this scheduler ?