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.
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