Creating Databases
Database File Locations
A database consists of a number of "locations" and within each location are files for either the Vector system catalog files or the Vector x100 files. There are two system databases, iidbdb and imadb. For details of the locations for the system databases, see the Ingres Database Administrator Guide.
The Vector databases are divided within each location between locations containing the Vector system catalog files and some "special case" user tables that are not of x100 format ('default') and the x100 wal directory (also known as the Write-Ahead Log) ('vectorwise').
Data location
Database 'default' location contains the following files:
• System catalog files created when a valid user creates a Vector database. These files have an extension starting with "t" followed by a number (for example, aaaabdef.t00), where the first extent is named t00, and succeeding extensions are named t01, t02, and so on.
• System catalog tables contain metadata (for example, descriptions of tables, columns, and views) about the database and are owned by the user $ingres. For a complete description of the system catalogs, see the appendix “System Catalogs” in the SQL Language Guide.
• Special case tables are generally created by Third-Party Applications that require OLTP capability to deliver an analytical or replication capability. These are known as Heap tables and may or may not have associated indexes and views.The naming convention is the same as for system catalog files.
Data tables contain the metadata and may be referred to as data files. For details, see the chapter "Managing Tables and Views."
Database 'vectorwise' location contains the following files:
• Files created when a valid user creates the first Vector database.
– vectorwise.conf – A default configuration file used for all databases unless a database specific file is defined.
• Folders and files created when a valid user creates a Vector database:
– CBM folder, which contains:
• authpass – Authorize key access file for security
• db_key – UUID for the database
• default – Folder under which all table files are created
• lock – Access file
• node_local – Distributed use only (Vector in Hadoop)
• profiles – Default folder used for the output of global profiling
– wal folder, which contains:
• location_map – Map of the Vector system locations and x100 locations
• main.wal – Write-Ahead Log used to record all activity in the Vector database carried out by the user and triggered by configurable parameters. This can include PDT data.
• pdts – Position Delta Trees containing updates, inserts, and deletes to the Vector tables. This excludes BULK and SET-WISE operations.
When tables are partitioned, the names of the files containing the data include partition divisions.
• Files created when a valid user creates a Vector database. These files have a name format that is descriptive, containing:
– Schema name with prefix '_' (underscore)
– Table name with prefix/separator 'S' (capital S)
– Column name with a double '_' prefix ('__')
– Unique hex ID with leading zero representation
_ingresSnoparrt__first_name_0000000000000088
_ingresSnoparrt__second_name_0000000000000089
When tables are partitioned the names of the files containing the data include partition divisions. The table name is postfixed with '@' and a decimal partition number starting with zero. Example:
_actianSfivepart@0__first_name_000000000000007e
_actianSfivepart@0__second_name_000000000000007f
_iactianSfivepart@4__first_name_0000000000000086
_actianSfivepart@4__second_name_0000000000000087
Checkpoint location
Contains a snapshot of your database, which is created when you take a checkpoint (ckpdb) of the database.
Journal location
Contains a record of the changes made to the database’s "special case" tables and/or system catalogs. Changes to tables are journalled only if journaling is switched on for the database and the table.
Dump
Contains a record of the changes to the database "special case" table data that occurred during an online checkpoint. Dump files are used during the recovery (rollforwarddb) of a database that was checkpointed online.
For additional information about checkpoint, journal, and dump files, see the chapter “Performing Backup and Recovery.”
Work location
Contains files used for system work, such as sorting and hash joins within a piece of SQL.