[Oracle Database Setup Procedures]


JobServer depends on a RDBMS to store and manage its critical data. This document 
will cover the basic steps needed to configure your database such that it is 
operational for Jobserver to use. However, this document is not intended to be a 
thorough discussion of Oracle database configuration, administration, or
tuning. For example, you will need to insure that your Oracle database is 
configured properly to allow for remote jdbc clients, ...etc. Tuning your 
database will depend, in part, on how large you expect the database tables to 
grow and how many concurrent users and concurrently running jobs you expect
JobServer to manage.


Database Requirements:
----------------------

  Oracle versions 8.1.7.x and higher are supported.


Instructions for setting up Oracle JobServer database:
------------------------------------------------------

 1) Have your database administrator make a database instance available for use by JobServer. 
    Make note of the hostname, port number, and database instance name. 

    Installation steps:
       
        a) Have your administrator create a database user named "jsuser" (you may use
           a different name if you like). This will be the user that will be referenced 
           by the JobServer applications when connecting to the database. If you already 
           have an existing database user that you want to use then skip this step and 
           go to part "b" now. Otherwise, your database administrator may use the 
           following commands to create the "jsuser":

              > CREATE USER jsuser                 
                       IDENTIFIED BY changeme      /* Make sure to set the password           */
                       DEFAULT TABLESPACE SYSTEM   /* Set QUOTA, TABLESPACE, and any other    */
                       TEMPORARY TABLESPACE SYSTEM /* resource as your administrator sees fit. */
                       QUOTA unlimited ON SYSTEM;

              > GRANT CREATE SESSION TO jsuser;
              
        b) To create the JobServer tables you can either do it manually by applying the sql
           file "oracle-jobserver.sql", or you may use the "dbsetup" tool which walks you 
           interactively through the process. In both cases you will need to have access to
           a username/password with administrative rights to create tables, indexes, and
           grant rights. Choose one of these two steps to complete the installation of the
           database tables:

             i) dbsetup tool:
                   If you choose to use the "dbsetup" tool, it will walk you
                   through the remaining steps to create the JobServer tables.
                   Run the command "dbsetup" found in the "jobserver/database"
                   directory and follow its instructions as shown here:
                   
                     > dbsetup.sh  (unix)
                     > dbsetup.bat (windows)

            ii) Manually apply script:
                   Have your database administrator run the database script 
                   "jobserver/database/oracle-jobserver.sql" to create the tables for
                   JobServer. Ideally you should run the script such that the tables
                   are owned by the the user "jsuser". If you use a different username 
                   other than "jsuser", you will need to edit the "oracle-jobserver.sql" 
                   file and replace all occurrences of "jsuser" with the database 
                   username you chose instead.

 2) Once the database setup has been completed, the JobServer installation will need to be able to 
    reference the database. The following database properties will need to be defined in the 
    Jobserver "jobserver/conf/system-config.properties" file: 

        dbDriverType=ORACLE        /* Identify that the database used by JobServer as ORACLE    */
        dbHost=localhost           /* Hostname where database is installed                      */
        dbName=somename            /* Name of the database used                                 */
        dbPort=1521                /* Port number to use to connect to database via JDBC        */ 
        dbUsername=jsuser          /* Oracle user used by JobServer apps to access the database.*/
        dbPassword=changeme        /* Password corresponding to jsuser username.                */


 3) Configure the database properties discussed above in the 
    "system-config.properties". Now with the database up and running, check 
    if the database is properly configured and can be accessed by JobServer.
    Run the following command:
    
        > jobserver/bin/jsmonitor.sh  (unix)
        > jobserver/bin/jsmonitor.bat (windows)

    This will tell you if JobServer can detect and connect to the database. 
    If the database status is not shown as "Active" recheck the installation 
    steps to make sure you have configured the database and JobServer properly.
    

 4) JDBC Driver Support:
    Check with your DBA to verify the exact version of Oracle you are using (9, 10g,
    11i...etc). JobServer comes bundled with a version of the 11i JDBC driver. You can
    if necessary replace this driver with one that is more appropriate for your particular
    version of the Oracle server you are using. Contact us for questions or assistance.

   
                       [Database Maintenance]

Tuning
-------
Updating database statistics should be run on a periodic basis to keep the database well 
tuned. Table sizes and fragmentation should be monitored and kept optimal. The tables 
that grow most rapidly will need the most attention. These include the tables 
"js_wbstatus", "js_opstatus", and "js_taudit".


Table Trimming
--------------
The JobServer database tables should normally be trimmed on a periodic basis, to avoid
accumulating excessive amounts of outdated historical data. To do this,  
create a job using the pre-built TaskBean called "JobServerCleanup" that comes with 
the installation. Create, configure, and schedule this Job. Run this job on a regular
basis (for example weekly, monthly, or as needed). It will keep the database tables trimmed
to your specifications by removing outdated historical records from the various database
tables including outdated log files from the filesystem.


Backup
------
Backup your database as necessary. Incremental backup is recommended with periodic 
full backup.