Scheduler Installation and Configuration

The Install Scheduler option installs and starts a copy of the WhereScape Windows scheduler. The scheduler runs as a system service and can also be maintained through the normal Windows services functions.

This chapter covers both Windows and UNIX/Linux scheduler installation and maintenance

Diagram Overview of RED and Scheduler Database Access

The following diagrams in this chapter provide an overview of how WhereScape RED and the scheduler access SQL Server, Oracle and Teradata and databases.

SQL Server Diagram Overview

The following diagram provides an overview of how WhereScape RED and the scheduler access a SQL Server database.
The scheduler makes use of the ODBC layer to perform the routine tasks involved in scheduling and running jobs. WhereScape RED and the  scheduler can also use SSIS to load data into SQL Server.
For file or script-based loads, the scheduler actions the load on the host system using BULK INSERT.

Oracle Diagram Overview

The following diagram provides an overview of how WhereScape RED and the scheduler access an Oracle database.
The scheduler makes use of the ODBC layer to perform the routine tasks involved in scheduling and running jobs. When loading data from an ODBC source the scheduler can either use the ODBC layer or the Oracle Direct Path load facility.
For file or script-based loads, the scheduler actions the load on the host system utilizing Oracle SQL* Loader.

Teradata Diagram Overview

The following diagram provides an overview of how WhereScape RED and the scheduler access a Teradata database.
The scheduler makes use of the ODBC layer to perform the routine tasks involved in scheduling and running jobs. WhereScape RED and the scheduler can also use the TPT ODBC operator to load data into Teradata. For file or script-based loads, the scheduler actions the load on the host system using fastload, multiload and TPT (load and update operators).

Installing a Windows Scheduler

1. In WhereScape's Setup Administrator, click the Scheduler tab in the left pane and then click Install Scheduler.

1. The Windows Scheduler Definition window appears. (The windows below show SQL Server, Oracle and Teradata scheduler definition examples).

For configuration settings specific to each database, please see the field description below:

Example:

Example:

Example:

Unique Identifier
Each scheduler requires a unique name, this unique name is the name by which the service is referenced.

             Possibly the database name and user, e.g. WslWarehouse_sa

            Possibly the SID and user, e.g. ORCL_dssdemo

            Possibly the environment name and metadata owner, e.g. prod_dssdemo

Scheduler Name
When scheduling a job with WhereScape RED it is possible to assign the job to a specific scheduler. Each scheduler should therefore have a unique name. The default name is WIN0001. Most users change this to be the scheduler machine's name followed by a number, e.g.: DWHP01, where DWHP is the name of the windows scheduler server.
Service Display Name
This field displays the name that the scheduler will appear under when viewed through the Windows services. It is a combination of the string 'WhereScape' and the unique identifier.
Service Logged on Under User
This is the name of the Windows local user account that the scheduler service will use when logging on. The default setting is System Account, also known as the Local System service account. To set the scheduler to use a Windows domain user account with a password, use the Service Account option from the right-click menu. If a domain user account is specified then the contents of this field (on the Windows Scheduler Definition dialog) are ignored.

Work Directory
The directory used to store some temporary files created by the scheduler.

Log File
The full path for this scheduler to write a log file. The log file contains information on the scheduler service, it does not contain information about individual job tasks run by the scheduler. This file may become large if log level 9 is set.
Poll Interval
This defines the frequency of the scheduler polling the database, looking for work. The default is 30 seconds. If set to a very low interval such as 1, then the impact on the database may be noticeable.
Log Level
This defines the level of detail written to the log file by the scheduler service. This should be left at the default level unless requested by WhereScape Support.
The following logging levels are available:

• 0 = log no information
• 1 = log fatal errors only
• 2 = log major events (default)
• 3 = log major events and write ODBC Load statistics to the audit log in the database
• 9 = full debug information

Direct Path Load

        This should always be ODBC SQL load for SQL Server and Teradata schedulers.

        When using an Oracle repository, this option determines the method used by the scheduler to insert data; when using the ODBC Load type.

ODBC SQL load: When this option is selected, the scheduler always load data using the standard ODBC driver method.
Direct Path load: When this option is selected, the scheduler attempts to load data using the Oracle Direct Path method, via the OCI library. If it is unable to use the Direct Path method, the scheduler will revert to using the standard ODBC driver method.
Direct Path loads are considerably faster and so this option should be set to Direct Path load in most situations.

TNS Service

        This is not required for SQL Server or Teradata schedulers.

        If you select the Direct Path load option from the Direct Path load or ODBC drop-down-list, enter the database server as defined in the tnsnames.ora file on this field. Normally this will be the same name as entered for the 'server' in an ODBC source definition.

ODBC Connection
The ODBC connection field requires the ODBC source name that provides a connection to the data warehouse system. The scheduler uses ODBC to connect to the data warehouse.
Database User Id
For Windows Authentication, leave this field empty. For SQL Server Authentication, enter a database username.
Database Password

        For Windows Authentication, leave this field empty. For SQL Server Authentication, enter the password for the database username entered in the Database User Id field.

        The password for the User the Scheduler will use when connecting to the data warehouse database.

       The password or Teradata Wallet String for the User the Scheduler will use when connecting to the data warehouse Teradata database. 

Metadata Database (Teradata)

        This is not set for SQL Server/Oracle schedulers.

        The Database Name or User Name where the metadata repository is stored. This must be entered for Teradata.

Error Messages:

The following error message may appear as the scheduler starts. In such an event, the scheduler will not function until the problem is resolved.

This error may be the result of an old version of the Oracle Call Interface (OCI) dynamic link library (dll) being in the path. The WhereScape scheduler requires Oracle client 11.2 or above. This situation can occur when an Oracle product, such as Discoverer is installed. Discoverer may install an older version of the oci.dll file. If it does, its location will be put in the Windows path in front of the 11.2+ client version.
A work-around is to copy an 11.2 or later version of the oci.dll file into the WhereScape program directory. To see if an oci.dll is valid, right-click the file and select properties and then version. A valid oci.dll will have version 11.2 or later.

Installing a UNIX/Linux Scheduler

The Unix scheduler must be manually installed and maintained.
The scheduler consists of a series of shell scripts that are controlled by the cron process. The bulk of the work undertaken by the WhereScape scheduler takes place in the WhereScape procedures within the database. To obviate the need to provide a port to each Unix platform this series of shell scripts are used. They should run with no modification on all Unix platforms and have been tested under Unix offerings from Sun, HP, IBM and Linux.
The Unix scheduler can process every type of load except for ODBC loads, Native ODBC loads and XML loads. It cannot process OLAP Cubes.

Installation

The installation of the Unix scheduler needs to be undertaken by someone familiar with the Unix environment and the running of database processes from Unix. The tasks that need to be undertaken are:

1. Unix User and Shell

Acquire a Unix user name that has access to cron and the database data warehouse environment. A reasonable quantity of disk space is also required if the automated backups of the metadata repository are used. The user should log on to Unix without passing through a menu system if possible. Log on to the user.
This user will need to have a .profile file in "/home/<user>/.profile" otherwise ws_sched_check_600.sh will not work. If this file doesn't exist it will need to be created.
The WhereScape scheduler consists of several shell scripts. By default, some of these shell scripts will run in the Bourne shell and some will run in the Korn shell. If Linux is being used, then the Bash shell is used exclusively.

2. Create directories

Create the following directories (if not already present) to be used by the WhereScape scheduler.
If the directory names are not appropriate and others must be used, then it will be necessary to alter the WhereScape supplied scripts.

• • wsl (top level directory for WhereScape directories)
  • wsl/bin (used to store the WhereScape scripts)
  • wsl/sched (used for the scheduler job and log files)
  • wsl/sched/job (used for the scheduler job files)
  • wsl/sched/joblog (used for the scheduler job logs)
  • wsl/sched/log (used for the scheduler logs)
  • wsl/export (used for the automated exports of the metadata)
  • wsl/expback (used to hold compressed copies of the metadata exports)
  • wsl/log (used for export and archive logs)

      Oracle only directories

• • wsl/mon (used for the monitoring process)
  • wsl/mon/db (used for database monitoring scripts and control files)
  • wsl/mon/job (used for job monitoring scripts)
  • wsl/mon/log (used for monitoring log files)

3. Copy supplied Unix files 

FTP the scheduler files shipped with WhereScape RED to the wsl/bin directory on the Unix system (use ASCII mode). All files must be copied over. WhereScape RED ships with several different versions of the Unix files for the scheduler. The appropriate version of the files must be copied to the server. The following lists the files shipped:

• • ...\WhereScape\Oracle\Unix\Version_nnn\...
  • ...\WhereScape\Oracle\Linux\Version_nnn\...

         

• • ...\WhereScape\Teradata\Unix\Version_nnn\...
  • ...\WhereScape\Teradata\Linux\Version_nnn\...
    

    Notes

    There may be several different versions of the scheduler files for a given database and platform (Unix or Linux). These are identified by version numbers nnn. For example, there may be three different folders in:
    ...\WhereScape\Oracle\Linux\: Version_010, Version_410 and Version_560.
    ...\WhereScape\Teradata\Linux\: Version_560 and Version_600.
    The highest version number script less than or equal to the version of RED in use should always be used.
    In the table that follows, the nnn in file names refers to the version number of the script. Some scripts will have 010, some will have 411, others 560 or 680, etc.

    
    A definition of the files follows:

    File	Purpose
    oraenv (for Oracle) tdenv (for Teradata)	Contains the template of a script for setting environmental variables. This needs to reside in the home directory of the Unix user and must be tailored for the database user and environment. It may be renamed to reflect the actual environment. e.g. oraenv_SID_user for Oracle e.g. tdenv_TDPID_user for Teradata
    crontab	Contains the template of the required cron commands. This file needs to be modified to include the HOME directory of the Unix user, and the name of the environment file. It is then used to update the crontab tables.
    ws_start_nnn.sh	Script to start the scheduler. This script is normally called by the ws_sched_check_nnn.sh script and is passed the database environment file name as a parameter.
    ws_sched_check_nnn.sh	Script to check that the scheduler is running. This script is normally executed by cron every 20 minutes. If the scheduler is found to be absent it attempts to start a new iteration. By default, the scheduler will terminate if its database is shut down. This script will therefore start the scheduler after a system start-up or a database start-up.
    ws_sched_nnn.sh	The actual scheduler script. This script looks for new scheduler jobs every x seconds. The default is every 30 seconds. This script must be modified to change the default behavior. It calls the ws_job_wait_010.sh script to perform the actual checking and initiating of a job. It terminates when ws_job_wait_010.sh returns an error and relies upon cron for a restart.
    ws_sched_status_nnn.sh	This script is called by ws_sched_nnn.sh to update the scheduler status in the scheduler metadata. It's a wrapper of the metadata database procedure ws_sched_status (i.e. it calls the database procedure ws_sched_status and returns its results to the calling script).
    ws_job_wait_nnn.sh	This script performs the bulk of the work. It is called by ws_sched_nnn.sh on a regular basis. It checks for any job waiting to run. If it locates a job that is ready to run it will create and start a script in the $HOME/wsl/sched/job directory for each thread of that job. These job threads in turn perform the actual processing and log their results to $HOME/wsl/sched/joblog. Under normal operation it is not necessary to check or inspect these files.
    ws_job_check_nnn.sh	This script is called to check the status of running jobs. It is called by ws_sched_nnn.sh on a regular basis (the interval is defined in ws_sched_nnn.sh). It checks that any job running in the scheduler is actually running in the Unix environment. If a job is not found under Unix, then it is aborted in the scheduler. This scenario can occur as the result of a database or machine crash. If a job abort is required, the results are logged to $HOME/wsl/sched/log/job_check_'env file name'.log. Under normal operation it is not necessary to check or inspect this file.
    cleanup_nnn.sh	This script is normally executed by cron and cleans up files created by the WhereScape scheduler. It deletes old versions of the jobs, job logs, and backups. It will also delete any temporary scripts created in the running of host scripts or file loads from within the scheduler. The location of these temporary scripts is defined within the WhereScape RED Unix Connection object. The default value in the cleanup script is /tmp and this must be altered if a different temporary directory is used for a Connection.
    Oracle only files
    meta_backup_nnn.sh	This script is normally called by the cron process once or maybe more times a day to perform an automated export backup of the metadata tables. It creates a number of command files and a database export in the $HOME/wsl/export directory named with the date/time of the export. It is recommended that users copy the new meta_backup_680.sh file to use Datapump's expdb instead the deprecated exp tool. This version uses the data pump export executable expdp. It assumes that the scheduler and the Oracle database reside on the same server.
    meta_archive_nnn.sh	This script is normally called by cron once a day to compress and tar the files in the $HOME/wsl/export directory and relocate them to the $HOME/wsl/expback directory. This action is performed purely to save on disk space usage.
    meta_restore_nnn.sh	This script can be executed to restore from a meta repository backup. It will overwrite any existing metadata. It can be used to restore to a schema/database/user other than the one from which the backup was taken. It does not however restore the WhereScape procedures. If a full recovery or a new user create is required, then the WhereScape metadata install would normally be performed before actioning this restore. If the restore is from an export that has been moved to the expback directory, you will need to uncompress the export and move it and associated files back to the export directory. It is recommended that users copy the new meta_restore_680.sh file to use Datapump's impd instead the deprecated imp tool. This version uses the data pump import executable impdp. It assumes that the scheduler and the Oracle database reside on the same server.
    Oracle only files
    ws_mon_nnn.sh	The main monitoring script. This script in turn calls the database and job monitoring scripts to perform the monitoring. By default, the monitor process checks the jobs every 15 minutes and the database every 60 minutes. This script can be edited to change the frequency of the checks if required. Also, a log level of 9 can be set to produce debug logging.
    ws_mon_db_nnn.sh	This script checks the database status and if necessary, calls the notify script to notify of a database down. Also checks to see if the criteria for database monitoring has changed and if changed calls the refresh script.
    ws_mon_notify_nnn.sh	This script checks that notifications are valid for the day/time and if valid sends a notification.
    ws_mon_refresh_nnn.sh	This script refreshes the scripts and command files stored in the mon/db directory. These scripts and command files are used to ascertain the action should the database be unavailable.
    ws_mon_jobs_nnn.sh	This script performs the checking of jobs for which monitoring is defined. If a job has a notification required, then this script will issue the notification.
    ws_mon_start_nnn.sh	This script starts the monitor process.
    ws_mon_check_nnn.sh	This script checks that the monitor process is running. If not running, the script will start the monitor process. This script is normally called via cron every 30 minutes.

4. Set permissions:

Change the permissions on the copied scripts so that they are executable (e.g. chmod 750 $HOME/wsl/bin/*.sh).

5. Create an environment file (see below for relevant Oracle or Teradata environment file instructions).

Using the environment file (oraenv) as a template and adhering to the variable names within, create an environment file for the database environment and user under which the scheduler is to run.
This file should be located in the $HOME directory. It may be called anything, but a meaningful name may be oraenv_SID_user for a given Oracle scheduler.
Check the users .profile and database environment file to see the normal settings. For Oracle, check to see if the TNS_ADMIN variable is in use and if so, include it in the environment file.
Make the following replacements in the environment file:

• • Replace all occurrences of HOME with the full pathname of your home directory.
  • Replace USER with the data warehouse database user name.
  • Replace PASSWORD with the data warehouse database user password.
  • Replace all occurrences of SID with the SID of the data warehouse database.
  • Replace all occurrences of ORAHOME with the full pathname of the Oracle home directory.

Using the environment file (tdenv) as a template and adhering to the variable names within, create an environment file for the database environment and user under which the scheduler is to run.
The environment file must be copied to the $HOME directory of the user selected in Step (1). It may be called anything, but a meaningful name may be tdenv_TDPID_user for a given Teradata scheduler.
Check the users .profile and database environment file to see the normal settings.

• • Replace all occurrences of HOME with the full pathname of your home directory.
  • Replace all occurrences of TDPID with the host name of the data warehouse database server, used by the scheduler as the database server name for Teradata load and unload utilities.
  • Replace all occurrences of BTEQDB with the host name or IP address of the data warehouse database server, used by the scheduler as the database server name/address for reading and writing WhereScape RED Metadata in Teradata (may be set to the same value as TDPID).
  • Replace USER with the data warehouse database user name.
  • Replace PASSWORD with the data warehouse database user password.
  • If using a Teradata Wallet String, comment out the first Password line and comment back in the second line containing WALLETSTRING. Replace all occurrences of WALLETSTRING with the data warehouse user wallet string.
  • Replace all occurrences of METABASE with the user or database where the metadata resides
  • Replace all occurrences of WORKDIR with the name of the directory that will be used as the work directory by default (e.g. /tmp/ )

6. Setup crontab

Ascertain if there are any existing crontab entries, and if so save them to a file for re-inclusion later. (e.g. crontab –l crontab –l >/tmp/my_crontab ).
Edit the crontab file and perform the following changes. Also check all entries and make sure you are happy with the times and actions:

• • Change all occurrences of HOME with the full pathname of the Unix users home directory.
  • Change all occurrences of the environment file name (oraenv or tdenv) to the name of the file created in step (4).
  • Remove comment markers from the beginning of the second line of code (the line containing this: ws_sched_check_nnn.sh).
  • Include any existing crontab entries as recorded above.

7. Initialize crontab entries

• • Load the crontab file into cron (e.g. crontab crontab ).
  • Perform a crontab list to ensure it all looks OK (e.g. crontab –l ).

8. Start Scheduler

Manually start the scheduler by issuing the ws_sched_check_nnn.sh script with the environment file name from step (4) as the parameter (e.g. ws_sched_check_010.sh oraenv ) / (e.g. ws_sched_check_600.sh tdenv ) / .
If the scheduler is not running it will be started. Execute the script a few more times to ensure that only one copy of the scheduler is started. Check that the scheduler is running by executing a 'ps –ef' statement and checking that the ws_sched_nnn.sh process is present.

9. Start the Monitor process

Oracle only.
Manually start the monitor by issuing the ws_mon_check_010.sh script with the environment file name from step (4) as the parameter (e.g. ws_mon_check_010.sh oraenv ). If the monitor is not running it will be started. Execute the script a few more times to ensure that only one copy of the scheduler is started. Check that the monitor is running by executing a 'ps –ef' statement and checking that the ws_mon_010.sh process is present.

10. Test the Scheduler

Run a job from WhereScape RED and ensure that it works. In the event of the job not starting or going into and remaining in a pending state, then the following investigations should be undertaken:

• • Ensure all WhereScape procedures are compiled. Use the Validate metadata option in the Setup Administrator to confirm that all procedures are valid.
  • Review the job logs in $HOME/wsl/sched/joblog.
  • Review the logs in $HOME/wsl/sched/log.
  • Isolate the name of the job and locate the relevant files for this job in $HOME/wsl/sched/job and $HOME/wsl/sched/joblog if they exist. Mail these files together with the files in $HOME/wsl/sched/log to support@WhereScape.com. It may be necessary to remove password references from the file(s) in $HOME/wsl/sched/job.
    

    Note

    It is possible to stop a Linux/UNIX Scheduler from within WhereScape RED. Refer to the WhereScapeRED User Guide or See the Teradata WhereScapeRED User Guide for further details.

Unix Scheduler setup for Hadoop loads using Oracle's Big Data Connectors

To set up the UNIX scheduler for Oracle loads from Hadoop using Oracle's Big Data connectors with OSCH and OLH, the following environment variables need to be edited in oraenv:
Example
export JAVA_HOME=/usr/lib/jvm/java-openjdkexport HADOOP_HOME=/home/oracle/hadoop-2.4.1
export PATH=$ORACLE_HOME/bin:$HADOOP_HOME/bin:$PATHexport JAVA_LIBRARY_PATH=$HADOOP_HOME/lib/native:$JAVA_LIBRARY_PATH
export HADOOP_COMMON_LIB_NATIVE_DIR=$HADOOP_HOME/lib/nativeexport HADOOP_OPTS="$HADOOP_OPTS -Djava.library.path=$HADOOP_HOME/lib/native"
For either OSCH or OLH, add one of the following to oraenv:
#OSCHexport OSCH_HOME=/home/oracle/orahdfs-3.1.0export PATH=$OSCH_HOME/bin:$PATHexport HADOOP_CLASSPATH=$OSCH_HOME/jlib/*:$HADOOP_CLASSPATH
#OLHexport OLH_HOME=/home/oracle/oraloader-3.2.0-h2export HADOOP_CLASSPATH=$OLH_HOME/jlib/*:$HADOOP_CLASSPATH

Maintaining the Windows Scheduler

1. To maintain the Windows scheduler, click the Scheduler tab and then click Scheduler Maintenance.

2. This option lists all currently active WhereScape schedulers.

Right-click a scheduler name to display a context menu as follows:

The options are: