16.20 - Scheduling a SQL Request - Query Scheduler

Teradata Query Scheduler User Guide

prodname
Query Scheduler
vrm_release
16.10
16.20
created_date
May 2017
category
User Guide
featnum
B035-2512-086K

The scheduling information provided is used by Teradata QS as the intended start time for the request. The Teradata QS server attempts to execute requests during the time period specified, but Teradata Database workload management rules determine if or when the scheduled request is executed.

Because a request may not be run until some time after the requested start time, a time interval in which the request may be run must be specified. Submitted requests are immediately rejected if an execution time frame is not available during the specified interval.

  1. Start Teradata QS and connect to a Teradata QS server. See Starting the Teradata Query Scheduler Viewer to learn how.
  2. Click Command > Schedule SQL or click .

    The SQL Text dialog box appears.

  3. Add the SQL statements to schedule in the edit area of the SQL Text dialog box using one of these methods:
    • Type the SQL statement text.

    • Copy ( Ctrl+C ) the SQL statement from another tool and paste it using Ctrl+V or click Paste.

      Scheduled requests cannot be used to insert large object (LOB) data into a Teradata Database. However, a SQL request can be scheduled that retrieves LOB data from a table, and saves those results to a table or a file.
  4. Click OK to save the statement and close the SQL Text dialog box.

    The Teradata Query Scheduler Submit Request dialog box appears with the Request tab selected by default.



  5. Complete the following information in the Request tab:
    Teradata Query Scheduler Submit Request Dialog Box: Request Tab 
    Option Description
    User Name (Read-only) Your Teradata Database logon name. This information is obtained from the Query Scheduler Viewer or Teradata SQL Assistant.
    Account String (Read-only) Your Teradata Database account string This information is obtained from the Teradata QS Viewer or Teradata SQL Assistant.
    Request Name [Optional] Enter a user-defined name for the request.
    Session Character Set Select the Teradata session character set under which the scheduled request will execute.
    E-mail address for notification on completion [Optional] E-mail address where a notification message is sent when a request job finishes execution. Option availability is based on the existence of a dbcmngr.AlertRequest table in the Teradata Database. For more information, see Specifying E-Mail Notifications.

    For information on installing and configuring the software to enable this feature, see the Teradata Query Scheduler Administrator Guide (B035-2511).

    Session QueryBand [Optional] Query band to be applied to the session for the scheduled request. See the SQL Data Definition Language (B035-1144) for more information on query banding.
    Modify parameters to make a new request Select this check box to change the request parameters and submit a new request.

    This check box is not available for new requests.

  6. Select the SQL tab, and then complete the following options:
    Teradata Query Scheduler Submit Request Dialog Box: SQL Tab 
    Option/Control Description
    SQL box Enter text for one or more SQL statements to be submitted to the Teradata QS server. Statements are delimited by semicolons. Create these statements directly in this box by using the SQL Text dialog box or by using Teradata SQL Assistant.
    Estimates Rows and Time box (Read-only) The estimated time the request is expected to execute and the estimated number of rows generated by the last statement. These values are calculated by the Teradata QS server using the EXPLAIN statement run on the request.
    Update button Refresh the estimates associated with the scheduled request. This command is available only when editing the SQL text of a scheduled request. See Modifying a Scheduled Request or Job.
  7. Select the Scheduling tab, and then complete the options.


    Teradata Query Scheduler Submit Request Dialog Box: Scheduling Tab 
    Option/Control Description
    Frequency box Choose the frequency at which the request will run using one of these options:
    • Once
    • Daily
    • Weekly
    • Monthly

    Option availability is based on profile type. The default is Once; the scheduled SQL statement executes only one time.

    Start Days box Enter the month, day, and year when the request will run. Selecting a different Frequency option changes these values.

    By default, this box is set to the first date when a scheduled request execution time frame is available as computed by the Teradata QS server.

    If the Once option is selected, enter the month, day, and year when the request is to execute in the corresponding boxes.

    If the Daily option is selected, the boxes in the Start Days frame are not available.

    If the Weekly option is selected, use the Days of the Week check boxes to select when the request is to execute.

    If the Monthly option is selected, enter the dates, delimited by a comma, in the Days of the Month box to select when the request is to execute.

    Start Time box Enter the time when the request is to run in the Hour and Minute boxes.

    The default values indicate the next time the Teradata QS server predicts the request can be executed.

    If Once is selected, enter the time when the request is to run in the Hour and Minute boxes.

    If the Daily, Weekly, or Monthly option is selected, enter the time when the request is to run in the Hour and Minute boxes.

    For the Daily, Weekly, or Monthly options, one or more start hours may be entered as a comma-delimited list (without spaces). For example, to schedule a request at 15 minutes after midnight, 6am, noon and 6pm, enter:
    • Hour: 0,6,12,18
    • Minute: 15
    For the Daily, Weekly, or Monthly options, a range of contiguous start hours may be specified by entering a hyphen between the start and end hours of the range. For example, to schedule a request every hour on the hour between 8 am and 5pm enter:
    • Hour: 8-17
    • Minute: 0
    To schedule a request every hour on the half hour between 10 pm and 2 am, enter:
    • Hour: 0-2, 22-23
    • Minute: 30
    For the Daily, Weekly, or Monthly options, an asterisk (*) may be used to schedule a request for every hour of the day. For example, to schedule a request at 10 minutes after every hour, enter:
    • Hour: *
    • Minute: 10
    Maximum Start Interval Days/Hours Enter the maximum interval beyond the start time that the job can begin to execute in the Day and Hour boxes.

    For new requests, the initial value is determined by the profile type. The default value is defined by the Teradata QS Administrator.

  8. Select the Results tab, and then complete the following options to specify where and how the results of the scheduled request are saved.


    If no results are generated, the controls in the Results tab are not available. Option availability is based on your profile type.
    Teradata Query Scheduler Submit Request Dialog Box: Results Tab 
    Option/Control Description
    Save To options Select where to store the results from the last statement of the query using one of the following options:
    • DB Table
    • Server File

    The default value is defined from the Teradata Query Scheduler Administrator.

    Database Name box Enter the Teradata Database name where to store the results from the last statement of the query. When results are saved to a database table, this option is initialized to a default database name defined from the Teradata Query Scheduler Administrator.
    Table/File Name box The name of this text box depends on the Save To option selected.

    In the Table Name box, enter the name of the Teradata Database table where to store the results from the last statement of the query.

    In the File Name box, enter the name of the Teradata QS server file where to store the results from the last statement of the query.

    Options list Choose how the results are handled when the database table already exists using one of these options:
    • Append if exists: Appended to an existing table
    • Drop if exists: Stored after dropping the existing table. This option is the default.
    • Fail if exists: Not stored if scheduled request failed because the result table already exists.
    Table Options check boxes [Optional] If choosing to create a Teradata Database table, choose the type of table created:
    • Fallback
    • Multi-set
    Field Separator list Choose a character field separator for results saved to a file. The default value is defined using the Teradata Query Scheduler Administrator. The values are:
    • Tab
    • Space
    • Semicolon
    • Comma
    • Bar |
    Encoding box Select the file encoding format (ANSI, UTF-8, or Unicode) for saving results. This is available only if Server File is selected for the Save As option.
    Automatically cleaned up after list (Read-only) The number of days the results are saved before being automatically deleted by Teradata QS. This value is set using the Teradata Query Scheduler Administrator. It indicates the number of days a history of completed jobs and un-updated results are retained before being automatically deleted by Teradata QS.
  9. Click OK to submit a request to the Teradata QS server.

    All of the entered parameters are checked for validity.

    If the request is approved, a message box appears showing a Request ID and Job ID for the scheduled request. For example:

    Your request has been successfully scheduled.

    Request ID = 32901, Job ID = 69753

  10. Click OK to close the message box.

If the request is not approved, change the parameters and resubmit the request.

See SQL Text Dialog Box to find out more about using the dialog box to create SQL statements.