CREATE DATABASE Usage Notes - Teradata Vantage

Rules for Using CREATE DATABASE

PERMANENT, SPOOL, and TEMPORARY Space Supported by CREATE DATABASE

You can define the maximum disk space available for specific purposes for any database.

If necessary, the system changes the defined PERMANENT, SPOOL, or TEMPORARY disk space limits to the highest multiple of the number of AMPs on the system less than or equal to the requested space.

Global temporary tables require a minimum of 512 bytes from the PERM space of their containing database or user. This space is used for the table header of the table.

Limit the SPOOL space assigned to a database to a level appropriate for the workloads of its users. Minimize the allotment of spool for any database to a size that is large enough to permit result sets to be handled and space management routines such as MiniCylPack to run when disk space becomes low, but small enough to force spool overflow errors when runaway queries resulting from Cartesian products and other common SQL coding errors occur.

See Determining Available User Table Data Space and Managing Space: Operational DBAs for more information about user space.

Local Journaling in CREATE DATABASE

Teradata MultiLoad, Teradata Parallel Transporter, and Teradata FastLoad are impacted by the use of LOCAL journaling.

Journal Tables and Default Map

If the CREATE DATABASE statement specifies a journal table and a default map, the journal table is created using this default map. The journal table map must be a contiguous map with the same AMPs as TD_GlobalMap.

Activating Permanent Journaling (CREATE DATABASE)

If you specify only DEFAULT JOURNAL TABLE = table_name, then the system creates a journal table, but does not activate it.

To activate the permanent journal, you must also specify the AFTER JOURNAL journaling option or the BEFORE JOURNAL option.

This action causes permanent journaling to be activated for all tables created in this database afterward.

To determine which tables in your system are journal tables, use the following query:

SELECT DBC.dbase.databasename (FORMAT 'X(15)'),
DBC.tvm.tvmname (FORMAT 'X(25)')
FROM DBC.tvm,DBC.dbase
WHERE DBC.dbase.databaseid=DBC.tvm.databaseid
AND   DBC.tvm.tablekind='j'
ORDER BY 1,2;

To determine which databases and users in your system have a default journal table defined for them, use the following query:

SELECT d.databasename (TITLE'Database'),TRIM(dx.databasename)
||'.'||TRIM(t.tvmname)(TITLE 'Journal')
FROM DBC.dbase AS d,DBC.TVM AS t, DBC.dbase AS dx
WHERE d.journalid IS NOT NULL
AND   d.journalid <> '00'xb
AND   d.journalid = t.tvmid
AND   t.databaseid = dx.databaseid
ORDER BY 1;

To determine which tables in your system are being journaled, use the following query:

SELECT TRIM(Tables_DB)||'.'||TableName (TITLE 'Table',
CHARACTER(26)),'Assigned To' (TITLE ' '),TRIM(journals_db)
||'.'||JournalName (TITLE 'Journals', CHARACTER(26))
FROM DBC.journals
ORDER BY 1,2;

Alternatively:

SELECT TRIM(d.databasename)||'.'||TRIM(t.tvmname) (FORMAT
'x(45)',TITLE 'Table'),TRIM(dj.databasename)
||'.'||TRIM(tj.tvmname) (TITLE 'Journal')
FROM DBC.TVM  AS t, DBC.TVM  AS tj, DBC.dbase AS d, DBC.dbase AS dj
WHERE t.journalid IS NOT NULL
AND   t.journalid <> '00'xb
AND   t.journalid = tj.tvmid
AND   d.databaseid = t.databaseid
AND   dj.databaseid = tj.databaseid
ORDER BY 1;