Personal Workspace Template Installation Guide

Tools
Tools covers the tools and utilities you use to work with Teradata and its supporting ecosystem. You'll find information on everything from the Teradata Eclipse plug-in to load/extract tools.
Teradata Employee

Personal Workspace Template Installation Guide

Overview

Personal Workspace Template is a demo portlet application that allows business users and developers to easily allocate a private Workspace (i.e. a database) within a Teradata System. Using this self service portlet, a user can create a database, upload comma separated values (.CSV) data to begin analysis of their data – all within 5-10 minutes.

This guide explains how to install and configure the Personal Workspace Template portlet and its utilities.

Prerequisites

  • Teradata Viewpoint 13.03 or newer running on Linux
  • Teradata Database 6.2 or newer
  • Bteq version associated with Teradata Database release

Step 1 - Download PersonalWorkspaceTemplate.zip package

Download PersonalWorkspaceTemplate.zip.

Step 2 - Extract the zip file

Extract the zip file to /<your-install-path>/PersonalWorkspaceTemplate/.

Run the following command:

cd <your-install-path>
unzip PersonalWorkspaceTemplate.zip

The contents of the extracted zip file should look like:

/<your-install-path>/PersonalWorkspaceTemplate/Documentation/Personal Workspace Template - User Guide.pdf
/<your-install-path>/PersonalWorkspaceTemplate/Portlets/DataUploadPortlet.war
/<your-install-path>/PersonalWorkspaceTemplate/Portlets/DownloadServletManager.war
/<your-install-path>/PersonalWorkspaceTemplate/Properties/sandbox.properties
/<your-install-path>/PersonalWorkspaceTemplate/Properties/.personalworkspacetemplateprofile
/<your-install-path>/PersonalWorkspaceTemplate/Scripts/DB_Sandbox_Install.bteq

Step 3 - Configure the database installation script

The parameters in the database installation script need to be modified to your database system settings. Open the DB_Sandbox_Install.bteq script and modify the parameters. Note: You must configure the values that are marked required.

Step 3a - Specify Database System & Admin User Login

Specify the admin user (i.e. DBC) used to create the Personal Workspace Template database structure and maintenance tables. Note: The admin user must have the following two permissions:

  • CREATE USER permission ON target_owner_DB (i.e. the DB that will own the Personal Workspace Template objects to be created
  • CREATE ROLE WITH GRANT OPTION.

Replace DBS_Name with the name of the target DB system. This value is found in the BTEQ file (Step 1). Replace super_user and super_user_password with the correct login/password of the admin user that will be used to create the database structure. This value is found in the BTEQ file (Step 1).

Step 3b - Create Admin User

Create the Personal Workspace Template Admin account. This admin user will house the maintenance tables and Personal Workspaces that are to be created. This admin user should get assigned enough PERM space to store the created personal workspaces as well as any maintenance tables/objects required by the admin user.

  • Change the owning database. All of the personal workspaces and maintenance tables will be created under this database. By default, the user's owner will be DBC. However, this value can be changed to any other database with enough PERM space. Modify this line: FROM DBC AS and replace DBC with the name of the new database/user. This value is found in the BTEQ file (Step 2).
  • Change the PERM space. By default, the PERM space allocated is 932GB, but this can be changed depending on your system. Modify this line: PERM = 1001000000000 and replace with the desired PERM value (in bytes). This value is found in the BTEQ file (Step 2).

Step 3c - Create database to house Personal Workspaces

This step creates the database that will house the personal workspaces. The database resides under the Personal Workspace Template Admin account.

  • Change the PERM space. This database should get the majority of the PERM space assigned to the Admin account. If the default PERM value was changed in Step 3b, then you should also change this value. Modify this line: PERM = 1000000000000 and replace with the desired PERM value (in bytes). This value must be less than the value specified in Step 3b. This value is found in the BTEQ file (Step 3).

Step 3d - Grant permissions on public databases/tables to the default role

This step grants permissions on any databases/tables that all personal workspaces will have access to. For security reasons, this should be limited to SELECT permissions.

  • Uncomment the series of GRANT statements and change each one as needed. Make sure to replace the <public_database_name1> lines with the actual database/object names. Also, make sure to leave the role name unchanged in the GRANT statement. This value is found in the BTEQ file (Step 9).

Step 4 - Run the database installation script

You must have Bteq installed on your machine to run the installation script. Change directories to where the bteq script DB_Sandbox_Install.bteq is extracted. Copy/paste this command:

bteq < DB_Sandbox_Install.bteq > output.txt

The output of the script will be stored in a text file called output.txt.

Step 5 - Configure and install the properties file

The properties file is located in this path: <your-install-path>/PersonalWorkspaceTemplate/Properties/sandbox.properties

Step 5a - Modify properties file

Open the sandbox.properties file and modify it with the correct values. See the table below for field descriptions and required fields.

Field NameDescriptionRequiredComments
dbssandbox.commons.db.netlogTeradata database server nameYesReplace <db_server_name> with the correct value.
dbssandbox.commons.db.classfornameTeradata jdbc driver class for name Default: com.teradata.jdbc.TeraDriver
dbssandbox.commons.db.jdbc.urlTeradata jdbc connection URLYesReplace <db_server_name> with the correct value.
dbssandbox.commons.db.adminDatabase user with rights to create the parent databaseYesReplace <db_admin_user> with the correct value.
dbssandbox.commons.db.passwordPassword for the user defined aboveYesReplace <db_admin_password> with the correct value.
dbssandbox.commons.db.encodingEncoding type Default: utf8
dbssandbox.commons.db.upload.dirTemporary files system for upload functionalityYesReplace <server_upload_dir> with the correct value.  Directories must be created in advance and terminated with a forward slash "/".
dbssandbox.commons.db.download.dirTemporary files system for download functionalityYesReplace <server_download_dir> with the correct value. Directories must be created in advance and terminated with a forward-slash "/".
dbssandbox.commons.server.nameViewpoint Portal server name or ip address of the Viewpoint Portal is deployedYesReplace <viewpoint_server_name> with the correct value.
dbssandbox.commons.server.portHTTP port where Viewpoint Portal is running Default: 80
dbssandbox.commons.mail.serverServer mail relayYesReplace <smtp_server_name> with the correct value.
dbssandbox.commons.mail.portServer mail relay port Default: 25
dbssandbox.commons.mail.fromEmail address for from which e-mails will be sentYesReplace <user_name>@<company_name> with the correct value.
dbssandbox.commons.mail.from.useridE-mail user account from which e-mails will be sentYesReplace <user_name> with the correct value.
dbssandbox.portlets.maxpermlimitDefault permanent space limit for sandbox user db in bytes Default: 1073741824 (1GB)
dbssandbox.commons.mail.from.passwordPassword for the e-mail user accountYesReplace <user_name_password> with the correct value.
dbssandbox.commons.mailmanager.switchTurns email on/off Default: on
dbssandbox.portlets.uploadsizelimitDefault size limit for file uploads in bytes Default: 26214400 (25MB)

Step 5b - Copy properties file to final location

Once you are done modifying the sandbox.properties file, copy it to $CATALINA_HOME/bin using the command below:

cp sandbox.properties $CATALINA_HOME/bin

Step 6 - Install the Portlet WAR files

Change directory to /<your-install-path>/PersonalWorkspaceTemplate/Portlets. Copy the two portlet war files to the Viewpoint portlet deployment directory using the commands below:

cp DataUploadPortlet.war $CATALINA_HOME/viewpoint-working/liferay-autodeploy
cp DownloadServletManager.war $CATALINA_HOME/webapps

Once the war files are placed in this file system location, Viewpoint will automatically deploy them. This may take a minute or two.

Step 7 - Configure the Personal Workspace Template Nightly Maintenance Script

The nightly maintenance script will send an e-mail to owners of expiring personal workspaces asking to renew or delete their mart. The script will also delete all expired personal workspaces. The script also cleans out the upload/download directories and removes temporary files.

Step 7a - Create Symbolic link

Create a symbolic link to $CATALINA_HOME/bin in order for script to correctly identify the location of the sandbox.properties file. To do so run the follow commands:

mkdir \-p /var/opt/teradata
ln \-s $CATALINA_HOME/bin /var/opt/teradata/personal_workspace_template

Step 7b - Modify the .personalworkspacetemplateprofile file

Edit the .personalworkspacetemplateprofile file located in: /<your-install-path>/PersonalWorkspaceTemplate/Properties/

Change <replace-with-your-viewpoint-server-location> with the actual location of your Viewpoint server. Typically it would be /var/opt/teradata/viewpoint.

(Optional - Set JAVA_HOME location)

If your machine has multiple Java versions installed, you will need to point the nightly maintenance script to use JDK 1.5 (if that is not already the default).

Add the following line to the top of .personalworkspacetemplateprofile:

export JAVA_HOME = <your-jdk1.5-install-location>

Step 7c - Configure the cron job to run nightly.

This will drop expired sandboxes and clean up the temporary files on the system.

00 00 * * * source /<your-install-path>/PersonalWorkspaceTemplate/Properties/.personalworkspacetemplateprofile;ls -l;java com.teradata.dbsandbox.commons.sandboxapi.admin.DBSandboxAdminDriver > /root/output.txt
  1. Type "crontab -e"
  2. Add the following line to the crontab (make sure to change <your-install-path> to the actual location of the previously extracted elaticMartsBuilder.zip file):

You should now be ready to use the Personal Workspace Template portlet.