Install/Configure WhereScape Scheduler on Linux

Modified on Thu, 13 Aug 2015 at 12:11 PM

Requirements and Assumptions

A user account will be provisioned to Linux to run the scripts provided by WhereScape. This user is a trusted account and requires no other permissions that are described in this document.

The trusted user and the scheduler employs CRON services.

The scheduler is setup under /opt on Linux for purposes of documenting this process. It is not required to install the WhereScape RED scheduler under this directory. You may change the directory and modify the steps in this document accordingly.

We use FTP in this document to provision the scheduler scripts from the folder containing the source code on Windows to Linux. FTP is not required in the operation of the RED scheduler.

We assume that the user will not inadvertently convert the Linux scripts to Windows format; we advise that they be reviewed in Linux to avoid accidental conversion.

The scheduler scripts are provided in WhereScape program files directory, by default this is:

C:\Program Files (x86)\WhereScape\Teradata\Linux\Version_600

RUN THESE STEPS AS ROOT

The user svcAcct is used for documentation purposes, it should be changed to meet company naming standards. Similarly, the password "wsl" is used only for example needs and does not conform to any strength standard.

useradd svcAcct -d /home/svcAcct -m -p $(openssl passwd -1 wsl)

chmod a+r /etc/profile
chmod a+x /etc/profile
chmod a+r /usr/bin/crontab
chmod a+x /usr/bin/crontab

mkdir /opt/wherescape
chown svcAcct:users /opt/wherescape

RUN THESE STEPS AS UNDER THE TRUSTED ACCOUNT SVCACCT

After logging into Linux as user svcAcct perform these steps:

CREATE DIRECTORIES UNDER /OPT/WHERESCAPE/

cd opt
mkdir wherescape
cd ./wherescape
mkdir bin sched export expback log mon odbc tmp
cd ./sched
mkdir job joblog log
cd ..
cd ./mon
mkdir db job log

PROVISION THE SCHEDULER SCRIPTS TO LINUX

The Linux scripts are found under this directory in WhereScape Windows:

C:\Program Files (x86)\WhereScape\Teradata\Linux\Version_600

These scripts should be installed in:

/opt/wherescape/bin

Be sure to use ASCII mode in the transport mode of the files to the Linux server.

SET SCRIPT PERMISSIONS & ENVIRONMENT VARIABLES

chmod 750 *

Modify the tdenv file supplied to describe the Teradata environment. The tdenv file can be named to reflect the environment as needed. For example, if a development scheduler is needed, then the tdenv file could be named RED_Development.

Edit the tdenv file to reflect your environment – using vi these steps are:

Make sure that a hosts or DNS entry exists for both the server IP and a cop entry for the Teradata database.

vi tdenv

These replacements change the _PLACEHOLDER_ value to your environment:

:1,$s/_HOME_\/wsl/\/opt\/wherescape/g
:1,$s/_USER_/redrepo/g
:1,$s/_PASSWORD_/red/g
:1,$s/_DB_;/localhost;/g
:1,$s/_BTEQDB_/localhost/g
:1,$s/_METABASE_/redrepo/g

COPY THE TDENV FILE TO THE RED USER HOME DIRECTORY

cp tdenv $HOME

Add a .profile to the $HOME directory of the red user account. It does not have to contain anything, but it must exist. This is required as of 6.6.3

SETUP CRON

Copy the file crontab in /opt/wherescape/bin to /home/red

make these replacement:

:1,$s/_HOME_\/wsl/\/opt\/wherescape/g (five substitutions)
:1,$s/######//g  (three substitutions)

Please uncomment the line for ws_sched_check_600.sh as this is the script that operates the timer service.

TD14.10_GCA_Sles10:~ # crontab -l
# DO NOT EDIT THIS FILE - edit the master and reinstall.
# (crontab installed on Fri Sep 13 16:31:20 2013)
# (Cron version V5.0 -- $Id: crontab.c,v 1.12 2004/01/23 18:56:42 vixie Exp $)
#
## each day cleanup log files etc.
##
######00 18 * * * _HOME_/wsl/bin/cleanup_600.sh >/dev/null 2>&1
#
## check and if required start the scheduler
##
0,20,40 * * * */opt/wherescape/bin/ws_sched_check_600.sh tdenv >>/opt/wherescape/sched/log/sched_tdenv.log 2>&1
#
## check and if required start the monitor
##
######0,30 * * * */opt/wherescape/bin/ws_mon_check_600.sh tdenv >>/opt/wherescape/sched/log/mon_tdenv.log 2>&1
TD14.10_GCA_Sles10:~ #

Note: ws_mon_check_600.sh does not exist; therefore please leave this line commented out.

CONFIGURE CRON TO ENABLE RED TO CONSUME THE SERVICE

Logon as root. If the file cron.allow does not exist, create the file in /etc and add the svcAcct user to /etc/cron.allow

Load the cron entries:

Change directory to /opt/wherescape/bin to locate the crontab file and run this as root:

crontab crontab

MANUALLY START THE SCHEDULER:

Or:

/opt/wherescape/bin/ws_sched_check_600.sh tdenv >> $HOME/sched.log 2>&1

IN SVCACCT

Logon to WhereScape RED in Windows and browse to the Scheduler tab

START A JOB

THE JOB IS RUNNING:

THE JOB HAS FINISHED:

THE AUDIT TRAIL OF THE JOB:

You will see this in the scheduler log file on Linux after the job has run:

BASIC TROUBLESHOOTING

You must be able to logon using BTEQ on the Linux server. The logon is

Bteq
.logon DSS_BTEQDB/DSS_USER,DSS_PWD;

Why? This proves that the environment variables can log on, just as the scheduler does via ws_sched_??

WHERE DO YOU LOOK?

/tmp/wsl_sched_status_600_tdenv.txt

This is where the bteq logon results are posted.

WHAT SHOWS UP IN WSL_SCHED_STATUS_600_TDENV.TXT

TDExpress14.0_Sles10:/tmp # cat wsl_sched_status_600_tdenv.txt
BTEQ 14.00.00.02 Sat Dec  8 20:52:08 2012

+---------+---------+---------+---------+---------+---------+---------+----
.logon td1410/jack,
 *** CLI error: MTDP: EM_NOHOST(224): name not in HOSTS file or names database.
 *** Return code from CLI is: 224
 *** Error: Logon failed!




 *** Total elapsed time was 8 minutes and 1 second.




+---------+---------+---------+---------+---------+---------+---------+----
CALL redrepo.ws_sched_status('STOP','UNIX','LINUX001','td1400','"ws_job_wai
t_600.sh tdenv returned a status of 143"','jack',30,6000000,0,?);
 *** Warning: You must log on before sending SQL requests.
+---------+---------+---------+---------+---------+---------+---------+----
.exit
 *** Exiting BTEQ...
 *** RC (return code) = 8

When it is successful:

BTEQ 14.00.00.02 Sat Dec  8 21:11:03 2012


+---------+---------+---------+---------+---------+---------+---------+----
.logon td1400/jack,




 *** Logon successfully completed.
 *** Teradata Database Release is 14.00.00.01
 *** Teradata Database Version is 14.00.00.01
 *** Transaction Semantics are BTET.
 *** Session Character Set Name is 'ASCII'.

Type .exit to leave BTEQ

+---------+---------+---------+---------+---------+---------+---------+----
.exit
 *** You are now logged off from the DBC.
 *** Exiting BTEQ...
 *** RC (return code) = 0

OTHER PLACES TO LOOK?

The table that hosts the status of a scheduler is zt_wrk_scheduler

The overall work flow is:

1) CRON starts the scheduler script

2) Scheduler logs into database and writes an entry into ws_wrk_scheduler

3) Scheduler polls for stuff to do and starts any jobs

4) Scheduler sleeps for 30 seconds (assuming default setting for wait)

5) Every 20 minutes (??) goto #2

6) Otherwise goto #3

If the scheduler script is unable to log into the database, you will not get an entry in ws_wrk_scheduler <- this is where RED is looking. No entry in ws_wrk_scheduler means no jobs will run.

INSTALLING ODBC DRIVERS ON LINUX

OVERVIEW

WhereScape ODBC driver installation is accomplished through a few simple steps – at an overview level they are:

Create a directory to host the drivers.>/li>
Un-compress and un-tar the drivers to their directory.
Modify environment variables to refer to the odbc directories.

This process was developed and tested on Suse Linux 2.6.16.60. Assuming the Linux scheduler has been installed in /opt/wherescape, use the "red" user id to perform all steps to follow; this will avoid permission and ownership issues.

CREATE THE ODBC DIRECTORY

Assuming the Linux scheduler has been installed in /opt/wherescape using the "red" user id.

/opt/wherescape> mkdir odbc

The complete path of the this directory is /opt/wherescape/odbc

UN-COMPRESS AND EXTRACT THE DRIVERS

Move the red_odbc.tar.gz file to / and extract the files:

mv /red_odbc.tar.gz .
tar -xvf red_odbc.tar.gz

MODIFY ENVIRONMENT VARIABLES

The odbc driver has been configured to use /opt/wherescape/odbc to make installation easy. Environment variables are needed to enable the solution. The environment variables are found in the zzz-odbc.sh bash script. It does not matter how the environment variables are set, but they all must be set. Managing the variables in the .profile file of the red user account is acceptable as well.

One method is to place zzz-odbc.sh in /etc/profile.d with the necessary permissions to be in this directory. Change the owner and group of the zzz-odbc.sh file to align with the administrative policies of user management. Note that the user and group on this script controls who may consume the environment variables. Changing the owner to the RED user, and Users group, will enable the RED user to have environment variables set properly:

chown red:users zzz-odbc.sh

The name zzz-odbc.sh makes this file the last to be executed in the /etc/profile.d processing that is done at boot. This was done in reaction to other applications outside of WhereScape overwrite existing variable values when assigning their values.

SUPPORTED DATABASES BY WHERESCAPE ODBC DRIVERS

Microsoft SQL Server 7, 2000, 2005, 2008, and 2012
Oracle (8.1.7 through 11.2)
IBM DB2 UDB

Linux, Unix, and Windows - 8.x, V9.1, V9.5, V9.7, V10.1
z/OS - 8.x, V9.1, V10
iSeries - i7.1, i6.1,
i5/OS - V5R4

Informix
Sybase 11.x, 12.x, 12.5.x, 15

SETTING UP AN ODBC LINUX DSN

Modify the odbc.ini file found in /opt/wherescape/odbc/ as shown through the yellow highlighter. The DSN name of “YourDSN” should be replaced with the name you want to use. With the [YourDSN] section, only three things are required to be different than this – the IP Address where Oracle is found, the Port Oracle is listening on, and the Oracle SID.

[ODBC Data Sources]
DB2 Wire Protocol=WhereScape 7.1 DB2 Wire Protocol
Informix Wire Protocol=WhereScape 7.1 Informix Wire Protocol
Oracle Wire Protocol=WhereScape 7.1 Oracle Wire Protocol
Sybase Wire Protocol=WhereScape 7.1 Sybase Wire Protocol
SQL Server Wire Protocol=WhereScape 7.1 SQL Server Wire Protocol
YourDSN=WhereScape 7.1 Oracle Wire Protocol




[ODBC]
IANAAppCodePage=4
InstallDir=/opt/wherescape/odbc
TRACE=1
TraceFile=odbctrace.out
TraceDll=/opt/wherescape/odbc/lib/W#trc27.so




[YourDSN]
Driver=/opt/wherescape/odbc/lib/W#ora27.so
Description=WhereScape 7.1 Oracle Wire Protocol
ApplicationUsingThreads=1
ArraySize=60000           <-- May be "tuned" - Default for setup is ok 
CatalogOptions=0
ConnectionReset=0
ConnectionRetryCount=0
ConnectionRetryDelay=3
DefaultLongDataBuffLen=1024
HostName=192.168.75.1     <-- Set the IP Address
PortNumber=1522           <-- Set the Port Number
ProcedureRetResults=0
QueryTimeout=0
ReportCodePageConversionErrors=0
ReportRecycleBin=0
SID=orcl                   <-- Set the Oracle SID
TNSNamesFile=
WireProtocolMode=2

TESTING A DSN

After modifying the odbc.ini file, the settings may be tested through using the adhoc binary found in this directory. Adhoc is installed by default by Teradata Tools & Utilities – adjust 15.00 to your version:

/opt/teradata/client/15.00/odbc_32/samples/C