Download (direct link):
CLIv2 JDBC ODBC Teradata SQL Assistant, (formerly known as Queryman) CREATE PROCEDURE/ REPLACE PROCEDURE SQL statements
2 - 76
Teradata RDBMS Database Administration 2 - 75Chapter 2: Building the Teradata RDBMS
The named set of compiled SQL constructs is stored in permanent table space.
Utilities such as CheckTable, Table Rebuild, Reconfig, and ARC DUMP and RESTORE operate on stored procedures as on any permanent data table.
Note: ARC archives procedures as part of a database-level archive and restore, not by individual procedure.
The ALTER PROCEDURE statement allows you to recompile stored procedures without the need for SHOW followed by REPLACE. This is useful for bulk recompilation of stored procedures during a Teradata RDBMS upgrade or migration, or cross-platform copy/restore of individual stored procedures.
To use this feature, you or your administrative user (for example, DBAdmin) needs the following privileges:
IF you want to . THEN you must .
recompile individual stored procedures • Be GRANTed the ALTER PROCEDURE privilege • Have or be GRANTed the DROP PROCEDURE privilege. (DROP PROCEDURE is automatically granted to users on any procedures they create in their own database. Otherwise, it must be explicitly granted by the owning user.)
recompile stored procedures at the database level either: • Have or be GRANTed the DROP PROCEDURE privilege • Be GRANTed the ALTER PROCEDURE privilege on either: • The named procedure • The database containing the procedure
For more information on:
• Using stored procedures to control user access to data, see ""Limiting Data Access with Stored Procedures" on page 6-44
• Defining and applying stored procedures, see Teradata RDBMS SQL Reference, Volume 6
• Performance considerations, see Teradata RDBMS Performance Optimization
Teradata RDBMS Database Administration
2 - 75Chapter 2: Building the Teradata RDBMS
The purpose of a permanent journal is to maintain a sequential history of all changes made to the rows of one or more tables. Permanent journals help protect user data when users commit, uncommit, or abort transactions. A permanent journal can capture a snapshot of rows before a change, after a change, or both.
You use permanent journaling to protect data. Unlike the automatic journal, the contents of a permanent journal remain until you drop them. When you create a new journal table, you can use several options to control the type of information to be captured.
A permanent journal provides the following options:
Single Image Captures/stores one copy of the data.
Dual Image Captures/stores two separate copies of data: one copy on the primary AMP and one on the fallback AMP.
Before Image Captures/stores row values before a change occurs.
After Image Captures/stores row values after a change occurs.
Use permanent journal tables to protect against:
• Loss of data caused by a disk failure in a table that is not fallback or RAID protected
• Loss of data if two or more AMP vprocs fail in the same cluster. This would mean the loss of two disks in a rank per failed AMP vproc. (To see how rows are distributed among AMPs in a cluster, see "AMP Clustering and Fallback" on page 7-32.)
• Incorrect operation of a batch or application program
• Disaster recovery of an entire system
• Loss of changes made after a data table is archived
• Loss of one copy of the journal table (with dual journaling)
Journal tables are allocated permanent space and reside within a database or
user space. Each database or user can contain only one journal table.
You create permanent journals with a CREATE USER/DATABASE or MODIFY
2 - 78
Teradata RDBMS Database AdministrationChapter 2: Building the Teradata RDBMS
Data tables can write to a journal in their owning database/user, or to a journal located in another database or user.
A journal in a database/user is the default journal for data tables in the same database/user, but you can specify that a data table write to a different journal in the CREATE TABLE or ALTER TABLE statement.
Rollback with Before-Image Journals
Before Images are used for ROLLBACK recovery. Once a before-image journal is created, a snapshot of an existing row is stored in the journal table before any data is modified.
In the event of a software failure, the before-image journal can roll back any unwanted changes in the data tables that write to that journal.
Permanent journals roll back all transactions from a table to a checkpoint. They may not be used to roll back specific transactions.
Rollforward with After-Image Journals
An after-image journal stores a snapshot of a row value after a change has been committed. Then if a hardware failure occurs, you can use the after-image journal to roll forward any changes made to the data tables that write to that journal since the last full system backup.