Database Administrator Guide > Database Administrator Guide > Introducing Database Administration
Was this helpful?
Introducing Database Administration
In This Guide
This guide provides Ingres database administrators with information about creating, maintaining, backing up, and recovering databases.
Audience
This guide is primarily intended for database administrators. In some cases, however, the responsibilities of the database administrator and the system administrator overlap. Therefore, some of the tasks and responsibilities described in this guide require permissions typically given to the system administrator, but not necessarily given to database administrators. In these cases, you must work with your system administrator to carry out these responsibilities.
Database Administrator Responsibilities
Anyone who creates a database becomes the database administrator (DBA) for that database. Furthermore, there is no limit on the number of DBAs that can exist at a site.
Note:  Before you can create a database, you must have the createdb privilege.
The DBA has permission to do the following:
Create and destroy databases
Manage public database objects
Maintain database and query performance
Monitor locking to maximize concurrency
Back up and recover the database
Authorize databases to use alternate locations
Manage user access to data through grants on tables, views, procedures, and other objects.
For information on managing user access, see the Security Guide.
Database Administration Summary
The following table is a quick reference to the statements, commands, and utilities used to perform database administration tasks.
For details, refer to the appropriate chapter in this guide, the SQL Reference Guide, Command Reference Guide, and Security Guide. Many of these tasks can be also performed using Actian Director or the Visual Tools on Windows.
Note:  UPPERCASE denotes SQL statement; lowercase denotes command or utility.
Task
Statement, Command, or Utility
Back up and recover a database
ckpdb
copydb
rollforwarddb
unloaddb
Change locations of a database
accessdb
extenddb
relocatedb
Configure installation parameters
cbf
Copy a BLOB to a table
blobstor
Copy data from a table into a file
xmlexport
COPY INTO
Copy data from a file into a table
xmlimport
COPY FROM
vwload
Duplicate a database
relocatedb
clonedb
Extend a database to use a location
accessdb
extenddb
Maintain a database
verifydb
Manage constraints
ALTER TABLE...ADD CONSTRAINT
ALTER TABLE...DROP CONSTRAINT
HELP CONSTRAINT
Manage current installation
GRANT...ON CURRENT INSTALLATION
Manage groups
CREATE GROUP
ALTER GROUP
DROP GROUP
Manage indexes
CREATE INDEX
DROP INDEX
HELP INDEX
MODIFY
Manage integrities
CREATE INTEGRITY
DROP INTEGRITY
HELP INTEGRITY
Manage locations
accessdb
extenddb
unextenddb
CREATE LOCATION
ALTER LOCATION
DROP LOCATION
SET WORK LOCATIONS
Manage databases
accessdb
alterdb
copydb
createdb
destroydb
relocatedb
unloaddb
GRANT...ON DATABASE
REVOKE
Manage database events
CREATE DBEVENT
DROP DBEVENT
GET DBEVENT
GRANT ... ON DBEVENT
HELP PERMIT ON DBEVENT
RAISE DBEVENT
REGISTER DBEVENT
REMOVE DBEVENT
Manage database objects
copyapp
copyform
copyrep
delobj
unloaddb
Manage database procedures
CREATE PROCEDURE
DROP PROCEDURE
EXECUTE PROCEDURE
GRANT...ON PROCEDURE
HELP PERMIT ON PROCEDURE
HELP PROCEDURE
REVOKE
Manage database rules
CREATE RULE
DROP RULE
HELP RULE
Manage locking strategies
SET LOCKMODE
SET SESSION ISOLATION LEVEL
SET TRANSACTION
SET TRANSACTION ISOLATION LEVEL
Manage optimization statistics
optimizedb
statdump
Manage profiles
CREATE PROFILE
ALTER PROFILE
DROP PROFILE
Manage roles
CREATE ROLE
ALTER ROLE
DROP ROLE
GRANT
REVOKE
Manage replication
dereplic
reconcil
repcat
repcfg
repdbcfg
repinst
repmod
repstat
rsstatd
Manage schemas
CREATE SCHEMA
Manage security alarms
CREATE SECURITY_ALARM
DROP SECURITY_ALARM
HELP SECURITY_ALARM
Manage security auditing
ALTER SECURITY_AUDIT
DISABLE SECURITY_AUDIT
ENABLE SECURITY_AUDIT
REGISTER TABLE
REMOVE TABLE
Manage sequences
CREATE SEQUENCE
ALTER SEQUENCE
DROP SEQUENCE
GRANT...ON SEQUENCE
REVOKE
Manage synonyms
CREATE SYNONYM
DROP SYNONYM
HELP SYNONYM
Manage tables
CREATE TABLE
CREATE TABLE AS
ALTER TABLE
DROP [TABLE]
COMMENT ON
COPY
COPY FROM/INTO
DECLARE GLOBAL TEMPORARY TABLE
DECLARE TABLE
GRANT...ON TABLE
HELP COMMENT
HELP DEFAULT
HELP PERMIT ON TABLE
HELP TABLE
MODIFY
REVOKE
copydb
blobstor
fastload
unloaddb
Manage users
CREATE USER
ALTER USER
ALTER PROFILE
DROP USER
accessdb
Manage views
CREATE VIEW
DROP VIEW
GRANT ... ON TABLE
HELP PERMIT ON VIEW
HELP VIEW
REVOKE
Monitor and tune a database
Various statements, commands, and utilities
Remove a database from a location
unextenddb
Set or change storage structure for a table or index
SET RESULT_STRUCTURE
CREATE TABLE AS
CREATE INDEX
MODIFY
Tidy user tables or indexes
MODIFY
usermod
Tidy system catalogs
sysmod
Unload data from a database to a flat file
copydb
fastload
unloaddb
View database characteristics
accessdb
catalogdb
infodb
vwinfo
What You Need to Know
This guide assumes that you are familiar with the windowing system on the target platform of the installation, including terminology, navigational techniques, and working with standard items, such as menus and dialogs.
Query Language Used in this Guide
The industry standard query language, SQL, is used as the standard query language throughout this guide. Ingres is compliant with ISO Entry SQL-92. For details about the settings required to operate in compliance with ISO Entry SQL-92, see the SQL Reference Guide.
System-specific Text in this Guide
Generally, Ingres operates the same way on all systems. When necessary, however, this guide provides information specific to your operating system. For example:
UNIX: Information is specific to the UNIX and Linux environments.
Windows: Information is specific to the Windows environment.
VMS: Information is specific to the VMS environment.
When necessary for clarity, the symbol is used to indicate the end of system-specific text.
For sections that pertain to one system only, the system is indicated in the section title.
Terminology Used in this Guide
This guide uses the following terminology:
A command is an operation that you execute at the operating system level.
A statement is an operation that you embed in a program or execute interactively from a terminal monitor.
Note:  A statement can be written in Ingres 4GL, a host programming language (such as C), or a database query language (SQL or QUEL).
Syntax Conventions Used in this Guide
This guide uses the following conventions to describe command and statement syntax:
Convention
Usage
Monospace
Indicates keywords, symbols, or punctuation that you must enter as shown.
Italics
Represent a variable name for which you must supply a value.
[ ] (brackets)
Indicate an optional item.
{ } (braces)
Indicate an optional item that you can repeat as many times as appropriate.
| (vertical bar)
Separates items in a list and indicates that you must choose one item.
 
Last modified date: 01/30/2023