2.3 Setting up Job steps in a job and tbuild -s option

2.3 Using and Setting up Job Steps:

Using job steps is optional, but when used, they can execute multiple operations within a single Teradata PT job.

Following are some rules of using job steps:

A job must have at least one step, but jobs with only one step do not need to use the STEP syntax.

Each job step contains an APPLY statement

Some job steps may use a single standalone operator, such as:

• DDL operator, for setup or cleanup operations in the Teradata Database.

• The Update operator, for bulk delete of data from the Teradata Database.

• OS Command operator, for operating system tasks such as file backup.

Job steps are executed in the order in which they appear within the DEFINE JOB statement.

Each job step must complete before the next step can begin.

Ex:

   STEP setup

   (

      APPLY

      ('DROP TABLE SOURCE_EMP_TABLE;'),

      ('DROP TABLE TARGET_EMP_TABLE;'),

      ('DROP TABLE GT11_LOADOPER_ERRTABLE1;'),

      ('DROP TABLE GT11_LOADOPER_ERRTABLE2;'),

      ('DROP TABLE GT11_LOADOPER_LOGTABLE;'),

      ('CREATE TABLE SOURCE_EMP_TABLE(EMP_ID INTEGER,

                                      EMP_NAME CHAR(10));'),

      ('CREATE TABLE TARGET_EMP_TABLE(EMP_ID INTEGER,

                                      EMP_NAME CHAR(10));'),

      ('INSERT INTO SOURCE_EMP_TABLE(1,''JOHN'');'),

      ('INSERT INTO SOURCE_EMP_TABLE(2,''PETER'');'),

      ('INSERT INTO SOURCE_EMP_TABLE(3,''FRANK'');'),

      ('INSERT INTO SOURCE_EMP_TABLE(4,''MARY'');'),

      ('INSERT INTO SOURCE_EMP_TABLE(5,''ELLEN'');'),

      ('INSERT INTO SOURCE_EMP_TABLE(6,''MICHAEL'');'),

      ('INSERT INTO SOURCE_EMP_TABLE(7,''SAM'');'),

      ('INSERT INTO SOURCE_EMP_TABLE(8,''JONATHAN'');'),

      ('INSERT INTO SOURCE_EMP_TABLE(9,''MICHELLE'');'),

      ('INSERT INTO SOURCE_EMP_TABLE(10,''ALICE'');')

      TO OPERATOR (DDL_OPERATOR () );

   );

   STEP load_data

   (

      APPLY

      ('INSERT INTO TARGET_EMP_TABLE (:EMP_ID, :EMP_NAME);')

      TO OPERATOR (LOAD_OPERATOR () [1] )

      SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] );

   );

Here in the above example we have two steps 'setup' and 'load_data'. Note the keyword STEP is used to define a step and has its own opening and closing brackets with an ending semi colon.

Each Step should have one APPLY clause.

'load_data' step runs only after step 'setup' is complete.

We can start a job from step one or from an intermediate step.

The tbuild -s command option allows you to specify the step from which the job should start, identifying it by either  the step name, as specified in the job STEP syntax, or by the implicit step number, such as 1, 2, 3, and so on. Job execution begins at the specified job step, skipping the job steps that precede  it in the script.

Say we are running a TPT job that contains 5 steps. If the job has run 1st two steps completely and failed in 3rd we can fix the issue and restart the job directly from failed step using the -s option on tbuild command.