Creating Databases
Database File Locations
A database consists of several “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 (VectorH)
• 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.
Commands for Working with a Database
You can perform the following basic operations on a database:
For details on these commands, see the Command Reference and the SQL Language Guide.
You can also perform many of these tasks using Actian Director.
The Master Database (iidbdb)
The master database, created when Vector is installed, named iidbdb, contains information about all other Vector databases in the installation, their locations, and the users who can access them.
The Management Database (imadb)
The management database, created when Vector is installed, named imadb, contains information about all activity that is current for a database server in the installation, the active connections, and current SQL, locks, and transaction states.
This database can be used to perform low level database actions, for example removing inactive sessions.
Rules for Naming Databases
Database names must follow these rules:
• They must be unique within an installation.
• They must begin with an alphabetic character (not the underscore).
• No special characters other than the underscore, dollar sign, and at symbol (@) are allowed for regular identifiers.
• The maximum length of a database name is 32 bytes. However, the name must be unique to 24 bytes (or the maximum file name length imposed by your operating system, if less than 24).
• Case significance (upper or lower) is determined by the settings for the database in which the object is created (Ingres or ANSI/ISO Entry SQL-92-compliant) and may differ for delimited and non-delimited identifiers).
For details about identifier syntax, see the SQL Language Guide.
Database Default Characteristics
By default, a database has the following characteristics when it is created:
• Public – All Vector users in the instance can connect to the database. You can use options to alter this behavior when creating the database.
• Unicode-enabled with Normalization Form C – The database can contain columns with Unicode data types such as NCHAR and NVARCHAR. Both datatypes can hold at least one two-byte UTF8 character.
• The only supported character encoding is UTF8 Basic. All character data types that have a length in bytes greater than one may contain a UTF8 character. VARCHAR or CHAR with length of exactly one is able to hold single character ASCII only.
How a Database Is Created
A database can be created using Vector commands and SQL statements or by using Actian Director.
The process for creating a database and its tables is as follows:
1. Create users.
2. Create a database.
3. Create base tables for the database.
4. Populate the tables with data.
Create a User
To create a user, you must have the required privilege, which is granted by default to the installation owner. You can create a user with the accessdb utility, through SQL by connecting to the iidbdb database, or through Actian Director.
To create a user with SQL
Issue this command at the command line:
sql iidbdb +U -u\$ingres
This directly accesses the system database where commands of the following form can be run to add a new user:
CREATE USER myuser WITH NOPROFILE, PRIVILEGES=(CREATEDB,CHANGE_PASSWORD);\go
where myuser is the name of the user to be created.
For details, see the SQL Language Guide.
Create a Database
To create a database, you must have the createdb privilege, which is granted by default to the installation owner. You can create a database through a command on the command line, or through Actian Director.
To create a database
Issue this command at the command line:
createdb dbname
where dbname is the name of the database.
The database is created in the default locations for the installation, uses the UTF8 character set, and is Unicode enabled with Normalization Form C (NFC).
To create a database with its data and checkpoint files on different devices
createdb bigdb -ddb_vw -cnewckp_vw
The bigdb database is created in a new default location db_vw; its checkpoint files are created in the newckp_vw location. The locations were defined previously with CREATE LOCATION statements.
How the Createdb Command Works
A database is created using the createdb system command (or the equivalent operation in Director or VDBA). When the createdb command is executed, the following occurs:
• The system catalogs in the master database (iidbdb) are updated.
• Subdirectories are created under the database location 'default' and 'vectorwise' for the database, with the name of the database.
• Similarly, directories are created under the work, journal, dump, and checkpoint locations for the database. These directories may not be created at the time the database is created, but at the time, for example, that a checkpoint is first run against the database.
• The configuration file (aaaaaaaa.cnf) and the core system catalogs (aaaaaaax.t00, x=b through e) are created in the new 'default' database directory.
• The DBMS system catalogs for the new database are created and modified.
• The standard catalog interface is created.
• The user interface system catalogs (restricted by any -f flag options) are created.
• Select permission for the system catalogs is granted to public.
• The folder structures 'wal' and 'CBM' are created in the new 'vectorwise' database directory. Within these are several subfolders and files that are maintained by the x100_server. The wal folder contains all the Write-Ahead Log information for a database. The CBM folder contains the actual data for the tables.
Note:
– Until data is inserted into a table there may not be any physical files associated with it.
– When data is inserted a few rows at a time (less than three) there may be no physical files created for the table until a threshold is reached that triggers propagating the PDT data to table files.
Show Information on Databases with infodb
The infodb utility shows administrative details about databases, such as the directory names of data, checkpoint, journal, dump, and work locations. These details are stored in the configuration file.
To get a list of databases, issue the following command at the operating system prompt:
infodb -databases
or
infodb -get_dbname
To see all information from all databases, including the system databases iidbdb and imadb:
infodb
Infodb prints a report for each database in the installation. To restrict the information to a specific database, specify:
infodb dbname
For more information, see the infodb command description.
Infodb can be used to access certain elements of the report:
infodb test2 -get_help
valid infodb -get_item flags:
-get_help -get_dbname
-get_dbowner -get_dbid
-get_dbid_hex -get_collation
. . .
-get_location_dump -get_location_work
-get_location_all
For example:
infodb test2 -get_dbowner -get_dbid
actian
1466958301
Show Information on Databases with vwinfo
The vwinfo utility shows administrative details about the x100 components and configuration of the Vector databases, such as the status of data in the PDT and current memory usage. Static parameters that are modified per installation are stored in the vectorwise.conf configuration file. There may be several of these files if individual configurations have been created for different databases in the installation: vectorwise.conf (default), vectorwise.dbname.conf, dbname/vectorwise.conf
To use vwinfo
To get a list of database configuration settings:
vwinfo dbname
The see valid options to vwinfo:
vwinfo -h
Remove a Database
To remove a database, use the destroydb command. You must be the DBA for the database or have the security privilege.
To remove a database
Issue the following command at the operating system prompt:
destroydb dbname
The following occurs:
• The database, checkpoint, journal, dump, and work directories for the database are deleted.
• All traces of the database are removed from the master database (iidbdb).
Caution! No prompt will ask you if you really want to destroy the database. Once you issue the destroydb command, the database is gone.