DBMS Administration and Management
Database Backups
Hints & Tips
Focus

DBMS Administration and Management

A)Who administers application database systems and in what environments?

B) Who administers the database management systems?

C) What is a DB2 Subsystem?

A "subsystem" is considered an environment. DB2P is considered the "production" environment, and DB2U the "user test and acceptance" environment.

Top

Database Backups

D) What kind of database backups are performed?

Complete "offline" backups are performed on databases DBA administers. Complete "offline" backups are performed for several reasons:

1. They are inherently more reliable since they are performed during a "point-of-consistency". This means the data is static when the backup is being performed and no transactions are occurring.
2. Complete offline backups are more reliable and less complex to develop, implement, maintain, and test.

E) Are databases available during backups?

Unfortunately, not at this time. Databases are placed in a "restricted" mode when backups are created to ensure data integrity (point-of-consistency) for restore purposes.

F) When are database backups performed?

Please note that days and times are subject to change. For production system availability, please check the UCC production calendar - http://www.nmsu.edu/cgi-bin/candn/Calendar/calendar.cgi?calendar=opc

1. SUPRA
   

   Production backups are performed Monday through Friday evening prior to nightly batch processing (approximately 18:00). For actual start times, please contact UCC. Backups are also performed each Friday morning prior to online activity.

2. DB2
   

   Production backups are performed Monday through Friday evening prior to nightly batch processing (approximately 18:00). For actual start times, please contact UCC.

3. Oracle
   

   Production and Devleopment backups are performed:

1. Monday through Saturday at 22:00.
2. Saturday and Sunday starting at 17:00.


4. SQL Server
   

   Production and Development backups are performed:
   

1. Monday through Saturday starting at 21:00.

G) How may database backups are kept for restoration?

1. SUPRA

   Production - 10 Generations prior to "batch" processing (approximately 2 weeks).
   Production - 12 Generations post "batch" processing on Friday mornings (approximately 12 weeks)
   Development - No Generations are created..

2. DB2

   Production(DB2P) - 35 Generations (approximately 7 weeks), prior to batch processing.
   Development (DB2D) - 05 Generations (approximately 1 week), prior to batch processing.
   User (DB2U) - 05 Generations (approximately 1 week), prior to batch processing.

3. Oracle

   Production and Development - 10 Cycles (approximately 2 weeks).

4. SQL Server

   Production - 12 Cycles (approximately 2 weeks).
   Development - 6 Cycles (approximately 1 week).

H) What are the database backups used for?

(a) Primary Purpose:
      (1) Restoring from server failure (disk, cpu, memory, etc.)
(b) Secondary Purpose:
      (1) Restoration of Production to Development environment to test restore            procedures.
(c) Collateral Purposes:
      (1) To facilitate university software application group development testing.
      (2) To facilitate university user testing.

I) What kind of database restores are supported by DBA?

1. SUPRA
2. DB2

   (a) "Full Image Copy" restore
   (b) "Quiesce Point" restore
   

3. Oracle

   (a) Nightly backup restore (physical)
   (b) Export to Import restore (physical)
   

4. SQL Server

   (a) Nightly backup restore
   

J) How do I request a "production" to development" database clone/restore?

Please send an email to ict_dba@nmsu.edu.

Q: What restore options are available?

A: There are several different options for restoring data or a database. Several questions must be answered before an option is selected. "What happened?" "Is the data or database really corrupt?" "What caused the data or database to become corrupt?"

A. Application Program Errors
There are three options to consider if data becomes corrupted in a database because of an application program:

1. Manual Restore
The affected transactions are restored manually through data entry. This option is appropriate when a limited number of transactions are corrupted by the error. This option is commonly chosen since only the corrupted transactions are affected and fixed. The database can be available if this restore option is chosen. The DBA group can provide technical assistance.

2. Programmatic Restore
The affected transactions are restored programmatically by a program. This option is appropriate when a larger number of transactions are corrupted by the error . This option is commonly chosen since only the corrupted transactions are affected and fixed. The database can be available if this restore option is chosen. The DBA group can provide technical assistance.

3. Database Restore
All transactions in a database are restored either from the last known good backup and/or to a known point of data consistency. The DBA group can perform this operation. This option is appropriate when all transactions have been corrupted by the error. This is the "All or Nothing" option since bad transactions cannot be restored independently of good ones. This option is not commonly chosen since good transactions as well as corrupted ones are lost. The database is not normally available during this restore operation if this option is chosen. The actual details and procedures vary dependent upon each Database Management System.

B. Hardware Failure Errors
Recovery from these types of errors are commonly handled by the DBMS.

Top

Hints & Tips

Q: Our application was running fine last month but now it is running slow. What is happening?

A: There could be a variety of causes. The most effective method to prevent a performance issue is to analyze each and every SQL statement in the application using a utility that is currently available called "explain."

By using "explain" in a development environment with enough test data, the poorly performing SQL statement can be identified. Explain will identify if a SQL statement may need to be rewritten, if an index isn't being used properly, or if an index isn't being used at all. Once the problem is correctly identified, several possible solutions are available.

Analyzing the application code in development prevents reactive solutions from occurring in production. Please contact us for more information on "explain" for each of the database management systems we administer and we will be happy to provide more information on what is available for you and your environment.

Top

FOCUS

K) My FOCUS password doesn't work anymore. Can you reset it?

The Customer Service Center (CSC) at 646-1840 can reset your FOCUS account password. For security reasons, a password cannot be reset to an account that is not in your name. Contact the CSC and request that your "Netview Access" account password be reset.

L) Can you create a FOCUS account for me?

Yes. FOCUS accounts are created when we receive an email request from the data custodian of the database. Due to security procedures, we do not accept requests from anyone else. When we receive an approved request and any other required information, we generally can create the FOCUS account that day and notify you.

M) How do I fix my FOCUS report?

Unfortunately, Database Administration only administers FOCUS and does not provide any training, application development, or local setup assistance. Our support for FOCUS is limited to software installation, patch installations, and functionality problems that may be bug related.

N) Can I use a reporting package other than FOCUS?

Focus was originally obtained to connect to the M204 database, SUPRA databases, and VSAM files. M204 is no longer used and SUPRA and VSAM (FRS) are also very close to being replaced on other platforms. Other packages such as Crystal Reports, MS Access, MS Excel, and a host of other third-party report packages are commercially available that provide access to DB2, Oracle, and SQL Server. Please contact your supervisor for more information regarding purchases and configuration support in your department.

Top

Computing Help:

help@nmsu.edu

ICT DBA:

ict_dba@nmsu.edu