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:
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.
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
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:
MANUALLY START THE SCHEDULER:
/opt/wherescape/bin/ws_sched_check_600.sh tdenv >> $HOME/sched.log 2>&1
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:
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?
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
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 18.104.22.168. 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
- 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: