# 4. Database Administration

## 4.1 The Role of the Database Administrator (DBA)

The Database Administrator or DBA, is the individual or office
responsible for the installation, configuration, update, maintenance,
and overall performance and reliability of the SQL Server database. In
general, the DBA is concerned with the availability of the server, the
definition and management of resources allocated to the server, the
definition and management of databases and objects resident on the
server, and the relationship between the server and the operating
system.

Each section in this chapter provides necessary background information,
followed by step-by-step instructions and actual scripts where
applicable.

## 4.2 Conventions, SQL Server Nomenclature, and Directory Structure

In all cases throughout this chapter, when actual examples are provided,
those which reference UNIX commands will be preceded by a "%", and those
that reference SQL statements will be preceded by a number and a "\>"
(e.g. 1\>sp_help tablename).

The terms described in the following table will be used throughout the
rest of this chapter.

Table 4.2-1. SQL Server General Definitions

  -----------------------------------------------------------------------
  Term            Definition
  --------------- -------------------------------------------------------
  client          SYBASE Open Client software (version 11.1.0) located in
                  the /tools/sybOCv11.1.0 directory

  Backup Server   Similar to the dataserver, it uses a separate UNIX
                  process to off load the cycles associated with DUMP and
                  LOAD commands

  backups         The set of UNIX files containing full database dumps,
                  transaction log dumps, and dbcc output

  dbcc            Database Consistency Checker - a utility program
                  designed to check the logical and physical consistency
                  of a database

  sybase root     /usr/ecs/OPS/COTS/sybase, this is the home directory
  directory       for all SYBASE software and related products and is
                  reference both in UNIX and in the rest of this document
                  as **\$SYBASE**

  interfaces file Lists the names and access paths for all servers and
                  backup servers. This file is located in the
                  **\$SYBASE**

  sa              System Administrator login, this is the superuser of
                  the SQL Server

  scripts         UNIX script programs located in **\$SYBASE**/scripts
                  and related subdirectories

  showserver      A utility invoked at the UNIX command prompt to display
                  active server, located in **\$SYBASE**/install.

  SQL scripts     SQL and command statements located in
                  **\$SYBASE**/scripts and related subdirectories

  SQL             Structured Query Language
  -----------------------------------------------------------------------

The **sybase** directory structure is described in the following table.
Subdirectories under the **scripts** can contain template files with
easy to modify examples of SQL and SQL command syntax.

Table 4.2-2. SYBASE Directory Structure

+-------------------------+--------------------------------------------+
| Directory               | Contains                                   |
+-------------------------+--------------------------------------------+
| **\$SYBASE**/bin        | Utilities necessary to load, run, and      |
|                         | access the server                          |
+-------------------------+--------------------------------------------+
| **\$SYBASE** /install   | Files used to start and initialize         |
|                         | dataservers, backupserver and to record    |
|                         | server messages (errorlogs)                |
+-------------------------+--------------------------------------------+
| **\$SYBASE** /lib       | db-lib, ct-lib, and xa-lib client library  |
|                         | files used by applications to gain access  |
|                         | to the server (local to server)            |
|                         |                                            |
|                         | \***Applications use automounted           |
|                         | libraries**.                               |
+-------------------------+--------------------------------------------+
| **\$SYBASE** /scripts   | Root directory for all script files        |
|                         | executed on the server                     |
+-------------------------+--------------------------------------------+
| **\$SYBASE**            | Root directory that contains all backup    |
| /sybase_dumps           | subdirectories, it is recommended, but not |
|                         | required, that this directory be on a      |
|                         | separate physical disk.                    |
|                         |                                            |
|                         | \*\*Backups are stored on disk for two     |
|                         | week periods. The week2 data is moved to   |
|                         | tape. Week 1 becomes week2 data and the    |
|                         | new data is stored in week1.               |
+-------------------------+--------------------------------------------+
| backup subdirectories   | Created each evening by the RUN_sybcron    |
|                         | job, they are named week1 and week2; and   |
|                         | contain the following files:               |
|                         |                                            |
|                         | dbname.dat.MMDDHHMM.Z - full database      |
|                         | dumps                                      |
|                         |                                            |
|                         | servername_dbname_tx.dat - transaction     |
|                         | file dumps (where applicable)              |
|                         |                                            |
|                         | dbname_backup.log.                         |
|                         |                                            |
|                         | dbname_dbcc.log                            |
+-------------------------+--------------------------------------------+
| \*                      | \*\*xx are the 2 letter codes to identify  |
| \*[xx]{.underline}dmh02 | a DAAC site                                |
| serves as a remote      |                                            |
| Backup Server           | (i.e., g0 = Goodard)                       |
+-------------------------+--------------------------------------------+

**[Naming Conventions]{.underline}**

As one of the most important, yet least applied concepts, naming
conventions are presented in this chapter by examples according to the
following rules.

**[Rule1]{.underline}:** Regardless of the length of the name, it should
indicate the function and/or content of the object

**[Rule 2]{.underline}:** Only easily understandable abbreviations
should be used

**[Rule 3]{.underline}:** Parts of names are separated by underscores
"\_", only one optional suffix is permitted (appended to the name by a .
".")

**[Rule 4]{.underline}:** The full path of the object is considered to
be part of the name

**[Notes and Examples]{.underline}:**

The names of the databases and tables themselves may or may not follow
the above rules, these rules are specifically for the DBA to work with
SQL Server objects, and files in the UNIX environment.

All **COTS** software is installed in the /usr/ecs/OPS/COTS directory.

All **SYBASE** software is located in the sybase home directory
(**\$SYBASE**).

All backups are located in **\$SYBASE**/sybase_dumps directory, which
may or may not be on a separate physical disk.

The directories are kept for a period of 30 days and they are named as
follows:

dbname_dat_MMDDHHMM.Z

where MMDDHHMM is the "sortable" eight digit month, day, hour, and
minute. For example, on the date this chapter was written, a backup
directory called **backups_for_970423**.

All sql script files have the extension **.sql** as a suffix. Their
names reference the objects they create or functions they perform, and
are all located either in **\$SYBASE**/scripts or below.

## 4.3 SQL Server Installation

SYBASE SQL Server Version 11.1.0 has been installed and configured by
the ECS Installation Staff. Shared memory and disk resources have been
allocated and configured by the System Administrator, and both the
client and server portions have been set up by the DBA prior to
shipment. The following table describes many of the parameters used and
options chosen during installation.

Table 4.3-1. SQL Server Parameters and Options

+--------------------+-------------------------------------------------+
| Item               | Brief Explanation                               |
+====================+=================================================+
| Server Name        | The name of the database server for a specific  |
|                    | application                                     |
|                    |                                                 |
|                    | **EX. -** g0sps06_srvr - pdps application       |
|                    | database server                                 |
+--------------------+-------------------------------------------------+
| Port Numbers       | The port number to be utilized by the above     |
|                    | listed servers.                                 |
+--------------------+-------------------------------------------------+
| Release Directory  | \$SYBASE                                        |
+--------------------+-------------------------------------------------+
| Retry Count        | 5                                               |
+--------------------+-------------------------------------------------+
| Retry Delay        | 5                                               |
+--------------------+-------------------------------------------------+
| master device      | 25 Mb raw partition                             |
+--------------------+-------------------------------------------------+
| sybsystemprocs     | \$SYBASE/devices/sybprocs.dat, 12 Mb and on     |
|                    | it's own device                                 |
+--------------------+-------------------------------------------------+
| errorlog           | \$SYBASE/install/node**.**errorlog (**xxx**     |
|                    | indicates the application)                      |
+--------------------+-------------------------------------------------+
| Current default    | us_english                                      |
| language           |                                                 |
+--------------------+-------------------------------------------------+
| Current default    | iso_8859-1 (Latin-1)                            |
| character set      |                                                 |
+--------------------+-------------------------------------------------+
| Current sort order | Binary ordering, for the ISO 8859/1 or Latin-1  |
|                    |                                                 |
|                    | character set (iso_1).                          |
+--------------------+-------------------------------------------------+
| Internal auditing  | On                                              |
+--------------------+-------------------------------------------------+
| sybsecurity        | 175 Mb - Varies -- depends on disk allocations  |
| database size      |                                                 |
+--------------------+-------------------------------------------------+
| sybsecurity device | sybsecurity, positioned on a 175 Mb raw         |
|                    | partition                                       |
+--------------------+-------------------------------------------------+

The script files are located in the **\$SYBASE**/install directory. SQL
Server installation is performed by an authorized user with the
**sybinit** utility also located in the **\$SYBASE**/install directory.
See your UNIX System Administrator and the SYBASE SQL Server
Installation Guide.

## 4.4 DBA Functions

The following subsections detail the most common functions that a DBA
will perform.

### 4.4.1 Starting, Stopping, and Showing the Server(s)

Use **shutdown** to bring the server to a halt. This command can only be
issued by the System Administrator (sa).

Syntax: 1\> **shutdown** \[with nowait\]

2\> go

The "with nowait" option shuts down the SQL Server immediately without
waiting for currently executing statements to finish.

Use **startserver** to start an SQL Server and/or a Backup Server. This
command can only be issued by the **sybase** user.

Syntax: % **startserver** \[-f runserverfile\]

The "runserverfile" is contained in the **\$SYBASE**/install directory.

Use **showserver** to determine whether the SQL Server(s) and/or Backup
Server(s) are running.

Syntax: % **showserver**

**Example:** UNIX processes running the various servers:

UID PID PPID C STIME TTY TIME COMD

sybase 671 669 80 Apr 17 ? 80:05 /usr/ecs/OPS/COTS/sybase/bin/dataserver
-d /dev/rdsk/c1t0d0s1 -g0sps06_srvr

sybase 665 663 80 Apr 17 ? 50:02
/usr/ecs/OPS/COTS/sybase/bin/backupserver -g0sps06_backup -e/usr/ecs/OPS

### 4.4.2 Creating Logical Devices

A logical device is created when the UNIX System Administrator
determines that new disk space is available for use by SYBASE software,
databases, transaction logs, and/or backups. Either raw disk partitions
or UNIX filesystem partitions can be used to create a logical device.
The creation of a logical device is a mapping of physical space to a
logical name and virtual device number (**vdevno**) contained in the SQL
Server **master** database. The **disk init** command is used to
initialize this space. After the disk initialization is complete, the
space described by the physical address is available to SQL Server for
storage, and a row is added to the **sysdevices** table in the
**master** database. To display the device created, execute:

Syntax: 1\> **sp_helpdevice**

2\> go

**[Example of Creating a Logical Device]{.underline}**

A raw partition on a RAID device has been made available to SQL Server
by the UNIX System Administrator. Essentially, the actual name of the
raw device **c2t0d1s3** has had it's ownership changed to **sybase** and
it's group changed to **user**.

**1.** In **\$SYBASE**/scripts/create.devices, DBA makes a script file
from the template.

Syntax: % cd /usr/ecs/OPS/COTS/sybase/scripts/create.devices

> \% cp template.sql data_dev1.sql

**2.** Appropriate items are modified so that the script file resembles
the following:

1\> disk init

2\> name = "data_dev1",

3\> physname = "/dev/rdsk/c2t0d1s3",

4\> vdevno = 3,

5\> size = 128000

6\> go

7\> sp_helpdevice data_dev1

8\> go

**3.** DBA runs the script from the UNIX command prompt:

Syntax: % isql -Usa -S**servername** -idata_dev1.sql -odata_dev1.out

**4.** DBA checks the data_dev1.out file for success

### 4.4.3 Creating and Altering Databases

A user database is created by the DBA with a script containing the
**create database** command. A database is created on one or more
physical devices. Specifying the device is optional - but highly
recommended. When indicating the device, you use the logical name you
specified as part of a **disk init** (described above). Unlike the
**disk init** command, the size of the database data and log components
is specified in MB instead of 2K pages.

**[Example of Creating a Database]{.underline}**

The logical device **data­­\_dev1** has been created (as above) along with
another device called **tx_log1** (for transaction logging).

**1.** In **\$SYBASE**/scripts/create.databases directory, DBA makes a
script file from the template.

Syntax: % cd /usr/ecs/OPS/COTS/sybase/scripts/create.databases

\% cp template.sql userdb.sql

**2.** Appropriate items are modified so that the script file resembles
the following:

1\> create database UserDB on data_dev1 = 100 log on tx_log1 = 50 \[with
override\]

2\> go

3\> sp_helpdb UserDB

4\> go

**3** DBA runs the script from the UNIX command prompt:

Syntax: %isql -Usa -S**servername** -iuserdb.sql -ouserdb.out

**4** DBA checks the userdb.out file for success

**[Example of Altering a Database]{.underline}**

The user database **UserDB** has run out of space and it has been
determined that it should be increased by 50MB.

**1** In **\$SYBASE**/scripts/create.databases, DBA creates a script
file containing the ALTER DATABASE command (named alter_userdb.sql)

Syntax: Alter database UserDB on data_dev3 = 50

**2** DBA runs the script from the UNIX command prompt:

Syntax: % isql -Usa -S**servername** -ialter_userdb.sql
-oalter_userdb.out

**3** DBA checks the alter_userdb.out file for success

### 4.4.4 Data Placement - Segmentation

Segments are named subsets of the database devices available to a
particular SQL Server database. Segment names are used in **create
table** and **create index** commands to place tables or indexes on
specific database devices. Using segments allows the DBA to better
control the size of database objects and may improve performance by
spreading i/o more evenly across devices.

Once the database device exists and is available, the segment can be
defined with the system stored procedure **sp_addsegment**.

Syntax: sp_addsegment segname, dbname, devname

After the segment has been defined in the current database, the **create
table** or **create index** commands use the optional clause "on
segment_name" to place the object on a particular segment.

Syntax: create table table_name (column_name datatype \...) \[on
segment_name\]

create \[clustered \| nonclustered\] index index_name on table_name

Use **sp_helpdb** database_name to display the segments defined for that
database.

Use **sp_helpsegment** segment_name to list the objects on the segment
and show the mapped devices.

**[Example of Creating a Segment]{.underline}**

The DBA receives a request to create a segment for the storage of the
DATA_INFO table indexes in the pdps_db_ops database, on a separate
physical disk. Two devices **data_dev1** and **data_dev2** have already
been created and are located on different physical disks.

**1.** In **\$SYBASE**/scripts/create.segments directory, DBA makes a
script file from the template.

Syntax: % cd /usr/ecs/OPS/COTS/sybase/scripts/create.segments

\% cp template.sql segments_dev1.sql

**2.** The script file is modified so that it resembles the following:

1\> sp_addsegment seg1_dev1, pdps_db_ops, data_dev1

2\> sp_addsegment seg1_dev2, pdps_db_ops, data_dev2

3\> go

**3.** DBA runs the script from the UNIX command prompt:

Syntax: %isql -Usa -S**servername** -ipdps_db_ops_segments.sql \\

> -opdps_db_ops_segments.out

**4.** DBA checks the opdps_db_ops_segments.out file for success

**5.** When the table and indexes are created according to the
instructions in section 4.4.6, the

"on seg1_dev1" must be appended to the DATA_INFO.sql **create table**
statement,

and the "on seg1_dev2" must be appended to the DATA_INFO_indexes.sql
CREATE

INDEX statement.

Syntax: (example)

**create index** DATA_INFO_IDX on DATA_INFO (DI_ID) on SEG1_DEV2

### 4.4.5 Monitoring Space Usage

#### 4.4.5.1 Thresholds

Thresholds are defined on segments to provide a free space value at
which a procedure is executed to provide a warning or to take remedial
action.

Use **sp_addthreshold** to define your own thresholds:

**sp_addthreshold** database_name, segment_name, free_space,
procedure_name

where free_space is the number of free pages at which the threshold
procedure executes; procedure_name is the stored procedure which the
threshold manager executes when the number of free pages falls below the
free_space value. Please see the section on Auditing later in this
chapter for an example of Thresholds.

### 4.4.6 Creating Database Objects

For special cases, creation (and modification) scripts are stored in
**\$SYBASE**/scripts/create.db_objects. There should be a template for
each type of object to be created.

**[Example of Creating a User Table]{.underline}**

The DBA has received a request to authorize create a new table in the
pdps_db_ops database called **PGE_Statistics** which has three column,
pge_id, pge_statistic_type, and pge_statistic.

**1.** In the **\$SYBASE**/scripts/create.db_objects directory, DBA
creates a script file from the proper template.

Syntax: % cd /usr/ecs/OPS/COTS/sybase/scripts/create.db_objects

> \% cp table_template.sql PGE_Statistics_table.sql

**2.** Appropriate items are modified so that the script file resembles
the following:

1\> create table PGE_Statistics (

2\> pge_id int,

3\> pge_statistic_type int,

4\> pge_statistic float )

5\> go

6\> sp_help PGE_Statistic

7\> go

**3.** DBA runs the script from the UNIX command prompt:

Syntax: %isql -Usa -S**servername** -iPGE_Statistics_table.sql \\

> -oPGE_Statistics_table.out

**4** DBA checks the PGE_Statistics_table.out file for success

Other objects are created in like manner but are not included here due
to space considerations.

### 4.4.7 Creating and Managing Users Logins

In order to connect to a SQL Server a login must be created by the
System Administrator or a system security officer. Login details are
stored in the syslogins table in the **master** database.

The system stored procedure **sp_addlogin** adds new login names to the
server but does not grant access to any user database.

Syntax: **sp_addlogin** login_name, password, \[,default database
,language, fullname\]

In order to gain access to a database, the System Administrator, system
security officer, of the specific database owner must "add" the user
with the **sp_adduser** system stored procedure.

Syntax: 1\> **sp_adduser** jpublic, pdps_db_ops

2\> go

**[Example of Creating a Login and Granting Database
Access]{.underline}**

The DBA has received a request to authorize John Q. Public to the
pdps_db_ops database. \*It is a good practice to have a default_db, when
you create a user account.

**1.** In the **\$SYBASE**/scripts/create.users directory, DBA creates a
script file containing the sp_addlogin command (named public.sql)

Syntax: % cd /usr/ecs/OPS/COTS/sybase/scripts/create.users

\% cp template.sql public.sql

**2.** DBA modifies appropriate fields so that the script resembles the
following:

1\> sp_addlogin jpublic,jpublic, default_db

2\> go

3\> use pdps_db_ops

4\> go

5\> sp_adduser jpublic

6\> go

7\> sp_helpuser

8\> go

**3.** DBA runs the script from the UNIX command prompt:

Syntax: % isql -Usa -S**servername** - public.sql -opublic.out

**4.** DBA checks the public.out file for success

### 4.4.8 Permissions

Permissions are used to control access within a database. The DBA uses
the **grant** and **revoke** statements to accomplish this. There are
two types of permissions within a database, **Object** and **Command**.
In general, **Object** privileges control select, insert, update,
delete, and execute permissions on tables, views, and stored procedures.
**Command** permissions control access to the **create** statements for
databases, defaults, procedures, rules, tables, and views.

The syntax for the **grant** and **revoke** statements are quite
similar:

**grant** {all \[ privileges\] \| command_list }

to { public \| name_list \| role_name }

**revoke** {all \[ privileges\] \| command_list }

from { public \| name_list \| role_name }

**[Example of Granting Privileges to a Specific User]{.underline}**

The DBA receives a request that John Q. Public should be able to read
the DATA_INFO table and read and update the SUBSCRIPTION_NOTIFICATION
TABLE.

Syntax: 1\> **grant** select on DATA_INFO to jpublic

2\> **grant** all on SUBSCRIPTION_NOTIFICATION to jpublic

3.  go

Note: It is recommended that the DBA store these command in a ".sql"
file in the **\$SYBASE**/scripts/create.db_objects directory, along with
their results.

## 4.5 Backup and Recovery

Table 4.5-1. Backup and Recovery Definitions

+-------------------+--------------------------------------------------+
| Term              | Definition                                       |
+-------------------+--------------------------------------------------+
| Backup Script     | Located in the **\$SYBASE** directory, they      |
| Components        | include:                                         |
|                   |                                                  |
|                   | sybasedump, dmpdb_trns,                          |
|                   | copy_daily_dumps_to_week1,                       |
|                   | copy_daily_dumps_to_week2                        |
+-------------------+--------------------------------------------------+
| Backup files      | Defined in Table 4.2-2, the location of these    |
|                   | files has been determined during server setup    |
+-------------------+--------------------------------------------------+
| Backup Statements | Generated from the sql in sybasedump these       |
|                   | include calls to dbcc, Dump Database, and Dump   |
|                   | Transaction commands                             |
+-------------------+--------------------------------------------------+
| Backup            | The only directory level underneath of the       |
| Subdirectory      | Backup Directory, defined in Table 4.2-2.        |
+-------------------+--------------------------------------------------+
| Backup Summary    | An extraction of the successful Dump messages    |
|                   | along with any errors generated by the Backup    |
|                   | Statements stored in the Backup Subdirectory.    |
+-------------------+--------------------------------------------------+

### 4.5.1 Automatic Backups 

The following are the list of all procedures and scripts files that are
currently being used for Sybase backups. There are cron jobs running at
all sybase **accounts OR servers** that have SQL server installed. All
dump files are currently written to LOCAL machine. The site DBA is
responsible for configuring the backup dump to the REMOTE sybase
directory.

To check if the crontab is up and running, enter:

\> crontab -l

-   Example of the output:

-   012 \* \* 1,2,3,4,5,6 /usr/ecs/OPS/COTS/sybase/ dmpdb_trns

-   019 \* \* 1,2,3,4,5 /usr/ecs/OPS/COTS/sybase/sybasedump

-   021 \* \* 1,2,3,4,5
    /usr/ecs/OPS/COTS/sybase/copy_daily_dumps_to_week1

-   07 \* \* 2,4,6 /usr/ecs/OPS/COTS/sybase/ copy_daily_dumps_to_week2

NOTE:

If the crontab is not running enter:

\> crontab /usr/ecs/OPS/COTS/sybase/run_sybcron

[SCRIPTS]{.underline} [DESCRIPTIONS]{.underline}

dmpdb_trns

> This scripts runs everyday (Monday - Saturday at 12:00 noon). The
> purpose of this script is to recycle all committed transaction in
> SYSAUDIT table of all current databases. E-mail will be automatically
> sent to the DBA account and will show the status count of SYSLOG
> before/after result of transaction dump. Edit the sybasedump file, in
> order to get notified by e-mail. (WHERE TO EDIT)

sybasedump

> Runs everyday (Monday - Friday at 7:00 p.m. ). This is the actual
> database backup script that dump all the database to sybase_dumps
> directory. Once the dump is completed, it will send e-mail to DBA
> which notify him/her of the databases that were backup successfully.
> Backup files or Dump files are written in a compressed format. Use the
> uncompress command before restoring the database dump.

copy_daily_dumps_to_week1

> The purpose of this script is to move all daily dump files to week1
> directory to ensure that these files will not be overwritten every
> time the backup is executed.

copy_daily_dumps_to_week2

> This script moves all the save week1 dump files to week2 directory
> every other day (Tuesday, Thursday, and Saturday) and delete the
> entire week1 directory after all files have been transferred to week2.

All these scripts reside in "/usr/ecs/OPS/COTS/sybase" directory. The
assigned site DBA will be responsible for maintaining, modifying and
applying necessary changes that are applicable to their site as for
(security, and backup schedule).

SQL Server backups are performed nightly by a **cron** job which runs
the **run_sybcron** program located in the **\$SYBASE**/scripts/backups
directory. The following table of definitions will be used throughout
the rest of this section.

Table 4.5-2. Automatic Backup Components

+-----------------+----------------------------------------------------+
| Component Name  | Function(s)                                        |
+-----------------+----------------------------------------------------+
| run_sybcron     | File added with the crontab -e command, contains   |
|                 | the following:                                     |
|                 |                                                    |
|                 | 00 19 \* \* 1-6 /data1/COTS/sybase/sybasedump      |
+-----------------+----------------------------------------------------+
| sybasedump      | Controlling script that performs the following     |
|                 | functions:                                         |
|                 |                                                    |
|                 | run isql to create the Backup Statements           |
|                 |                                                    |
|                 | run isql to execute the Backup Statements          |
|                 |                                                    |
|                 | record the results of the Backup Statements in     |
|                 | Backup Files                                       |
|                 |                                                    |
|                 | copy the Backup Files to the Backup Subdirectory   |
|                 |                                                    |
|                 | create the Backup Summary                          |
+-----------------+----------------------------------------------------+
| run dbcc        | SQL statements that access the SQL Server master   |
|                 | database and create the Backup Statements          |
+-----------------+----------------------------------------------------+
| sp              | SQL Server password file                           |
+-----------------+----------------------------------------------------+
| what goes here  | UNIX shell script that "greps" successful Dump     |
|                 | statements along with any errors generated, sends  |
|                 | e-mail to the DBA and writes them to the           |
|                 | backup_summary file                                |
+-----------------+----------------------------------------------------+

No intervention in the Automatic Backup Process is required by the DBA,
though periodic checks of the Backup Subdirectories are recommended.

### 4.5.2 Manual Backups

Manual backups can be performed at any time by the System Administrator
and are recommended for the following situations:

1.  Any change to the **master** database - this includes new logins,
    devices, and databases

2.  Any major change to user databases - a large ingest or deletion of
    data, definition of indexes

3.  Other mission-critical activities - as defined by the DAAC
    Operations Controller

Both the **dump database** and **dump transaction** command processing
are off-loaded to the backup server, and will not affect normal
operations of the database. These commands are performed by the System
Administrator on appropriate databases as follows:

Syntax:

> 1\> dump database master to
> "/usr/ecs/OPS/COTS/sybase/sybase_dumps/dbname.dat.MMDDHHMM.Z"

2.  go

After dumping the database, compress the dump file by executing:

%compress usr/ecs/OPS/COTS/sybase/sybase_dumps/dbname.dat.MMDDHHMM.Z

1.  dump transaction pdps_db_ops to
    "/backup_directory/pdps_db_ops_tx.dat"

2.  go

### 4.5.3 Manual Recovery

Manual recovery of a user database is performed by the System
Administrator by the use of the **load database** and **load
transaction** commands. For issues concerning the **master** database,
please consult your System Administrator's Guide for assistance. It is
recommended that any user database to be recovered be dropped and
created with the **for load** option. In section 4.4.3, the
**databasename**.sql along with any **alter.databasename**.sql scripts
were saved, these need to be combined into one script which will
re-create the user database with the **for load** option. This will
insure the success of the **load database** and **load transaction**
commands. The following example illustrates these issues:

### 4.5.4 The BulkCopy Utility

The **bcp** utility is located in the **\$SYBASE**/bin directory and is
designed to copy data to and from SQL Server databases to operating
system files.

#### 4.5.4.1 Requirements for Using bcp

In general, you must supply the following information for transferring
data to and from SQL Server:

a.  Name of the database and table

b.  Name of the operating system file

c.  Direction of the transfer (in or out)

In order to use **bcp**, you must have a SQL Server account and the
appropriate permissions on the database tables and operating system
files that you will use. To copy data **in**to a table, you must have
**insert** permission on that table. To copy data **out** to an
operating system file, you must have select permission on the following
tables:

a.  The table being copied

b.  sysobjects

c.  syscolumns

d.  sysindexes

#### 4.5.4.2 bcp Syntax

bcp \[\[database_name\].owner.\]table_name {in \| out} datafile \[-e
errfile\] \[-n\] \[-c\]

\[-t field_terminator\] \[-r row_terminator\] \[-U username\] \[-P
password\] \[-S server\]

#### 4.5.4.3 bcp Scripts and Files

**[Example of User Database Recovery]{.underline}**

The database **UserDB** was created using the following script excerpt:
(stored in home/scripts/create.databases/userdb.sql)

create database UserDB on data_dev1 = 100 log on tx_log1 = 50 \[with
override\]

and was modified using the following script excerpt:
(home/scripts/create.databases/alteruserdb.sql)

Alter database **UserDB** on data_dev1=50

For the purposes of this example, the full database backup and
transaction log dumps were successful and located in
/usr/ecs/OPS/COTS/UserDB.dat and UserDB_tx.dat

**1.** In the **\$SYBASE**/scripts/create.databases directory, DBA makes
a script file from the template.

Syntax: % cd /usr/ecs/OPS/COTS/sybase/scripts/create.databases

\% cp template.sql userdb_for_load.sql

**2.** Appropriate items are modified so that the script file resembles
the following:

1\> create database UserDB on data_dev2=100 log on tx_log2=50 **for
load**

2\> go

3\> alter database UserDB on data_dev3=50

4\> go

**3.** DBA saves the script in
\$SYBASE/scripts/create.databases/userdb_for_load.sql

**4.** DBA runs the script from the UNIX command prompt.

Syntax: %isql -Usa -S**servername** -iuserdb_for_load.sql
-ouserdb_for_load.out

**5.** DBA checks the userdb_for_load.out file for success

**6.** DBA loads the database from the full backup.

Syntax: 1\> load database UserDB from

2.  "/usr/ecs/OPS/COTS/sybase/sybase_dumps/week1/dbname.dat.MMDDHHMM"

3.  go

**7.** DBA loads the transaction file from the transaction file dump.

Syntax: 1\> load transaction UserDB from

2.  "/usr/ecs/OPS/COTS/sybase/sybase_dumps/week1/dbname.dat.MMDDHHMM"

> 3\> go

## 4.6 Database Performance and Tuning

Once your application is up and running, the DBA monitors its
performance, and may want to customize and fine-tune it. Use the
following software tools provided by SQL Server:

a.  Setting query processing options with the **set** command

b.  Setting database options with **sp_dboption**

c.  Monitoring SQL Server activity with **sp_monitor**

d.  Using **update statistics** to ensure that SQL Server makes the best
    use of existing indexes

e.  Changing system variables using **sp_configure** and the
    **reconfigure** command

f.  Placing objects on segments to spread i/o, improve throughput, etc.
    as described in section 4.4.4

For a complete discussion of issues related to SQL Server performance
and tuning, refer to your SYBASE SQL Server System Administration Guide.

## 4.7 Installation of the Applications

DBA should have physical devices configured before installing either
autosys or remedy. Both applications use Sybase as their database.

### 4.7.1 Installation of the Application Database

The installation of the application databases has been automated using
ECS Assistant. The application databases are created using the DbBuild
script which can only be invoked through ECS Assistant. Other scripts
that ECS Assistant invokes are:

DbBuild - Create new empty database and loads with initial data

DbPatch - Upgrade to new schema while retaining existing data.

### 4.7.2 The AUTOSYS Application and other Configuration Issues

The AUTOSYS application works in tandem with pdps (which one of the
applications that comprise the ECS) to schedule the jobs that run on
Science Processor. Autosys installation is performed in
/usr/ecs/OPS/COTS by the auto install program located in the
autosys/install directory. The results of the installation are stored in
an autosys_install.scr file located in the AUTOSYS home directory
(/use/ecs/OPS/COTS/autosys). For pdps to run properly with AUTOSYS, the
following activities are completed:

a.  A user is defined named **autosys**

b.  **autosys** user is added to the pdps_db_ops database

c.  The autosys server is added to the sysservers table with
    **sp_addserver**

d.  The server is added to the sysservers table on the AUTOSYS server
    with **sp_addserver**

Further configuration and troubleshooting information is available from
the ECS Staff at the Goddard Space Flight Center.

### 4.7.3 Spatial Query Server (SQS)

SQS is a multi-threaded, Sybase Open Query database engine, which is
required by the Science Data Subsystem (SDSRV). This product allows
definition of spatial datatypes, spatial operators, and spatial
indexing. SQS communicates with Sybase SQL Server to process SDSRV
requests to push and pull metadata. SDSRV database server resides on an
SGI machine. SQS also, reside on the same machine as SDSRV Sybase SQL
Server.

-   Named X1acg01 - where X is the DAAC specific identifying character.

-   pathname - /usr/ecs/OPS/COTS/sqs222/bin/sqsserver

-   Should have one dedicated CPU per instance running. Defaults to one
    instance now, but may require additional instances later for
    performance reasons.

-   Requires one entry in the Sybase "interfaces" file per instance of
    the SQS server to be run.

-   Consult startup scripts in /etc/init.d/sybase and
    /etc/init.d/sqs_222

SQS requires a Sybase login with SA or sa_role and associated password
to start. SQS environment variables [requirements]{.underline}:

-   SYBASE = Location of the Sybase home directory. Example:
    /tools/sybOCv11.1.0

-   PATH = Must include in this order - /usr/bin; /usr/sbin;\$SYBASE/bin

-   DSQUERY = Name of SQL Server to which to connect. From the
    \$SYBASE/intefaces file. Examples - g0acg01_sqs222_srvr

-   DSLISTEN = Name of SQS server to use. Example - g0acg01_sqs222_srvr

-   SQSUSER = Name of the user (SA or sa_role) for system connection.

-   SQSPASSWORD = Password for the system connection login

The SQS startup script requires the following information:

SQSHOME = location of sqsserver binaries.

The following is a list of options that can be imbedded in the startup
script, these options are beneficial, but they are not required.

**STARTUP OPTIONS**:

-   -e path of the SQS server logfile. Example
    /usr/ecs/OPS/COTS/sqs222/sqs/bin/sqs_222.log

-   -u number of concurrent SQS connections. Recommend minimum of 125.
    Example -u 125

-   Usually started with a delay, after the SQL Server is started. This
    delay be sufficient for the SQL server to recover and come-up.

-   \$SQSHOME/bin/sqsserver -e \$SQSHOME/sqs_222.log -u \$USER &

SQS has dependencies on Sybase, such as:

-   Sybase must be running prior to starting SQS

-   SQS user id that starts SQS, which is different from the application
    user ID must have admin privileges

-   SQS opens a connection to Sybase\'s because it writes to the Sybase
    System tables

-   SQS server thread runs under the userid sa. In order to avoide
    confusion when monitoring this thread, it is best to:

-   create a separate login and userid specifically to monitor SQS

-   grant sa_role authority to the userid created to monitor SQS

-   EXAMPLE: 1\> **sp_adduser** sqs_mon

2.  **grant** sa_role to sqs_mon

3.  go