:meta-keywords: cubrid create, cubrid add volume, cubrid backup, cubrid online backup, cubrid restore, cubrid unload, cubrid load, cubrid space, cubrid compact, cubrid optimize, cubrid plan dump, cubrid stat dump, cubrid check, cubrid diag, cubrid commands :meta-description: CUBRID comprehensive list of utilities and commands. Utilities: createdb, deletedb, backupdb, restoredb, compactdb, statdump, diagdb, checkdb, genlocale, gen_tz and many others. CUBRID commands for HA, Locale and Timezone. .. _cubrid-utilities: cubrid Utilities ================ The following shows how to use the cubrid management utilities. :: cubrid utility_name utility_name: createdb [option] --- Creating a database deletedb [option] --- Deleting a database installdb [option] --- Installing a database renamedb [option] --- Renaming a database copydb [option] --- Copying a database backupdb [option] --- Backing up a database restoredb [option] --- Restoring a database addvoldb [option] --- Adding a database volume file spacedb [option] --- Displaying details of database space lockdb [option] --- Displaying details of database lock tranlist [option] --- Checking transactions killtran [option] --- Removing transactions optimizedb [option] --- Updating database statistics statdump [option] --- Dumping statistic information of database server execution compactdb [option] --- Optimizing space by freeing unused space diagdb [option] --- Displaying internal information checkdb [option] --- Checking database consistency alterdbhost [option] --- Altering database host plandump [option] --- Displaying details of the query plan loaddb [option] --- Loading data and schema unloaddb [option] --- Unloading data and schema paramdump [option] --- Checking out the parameter values configured in a database changemode [option] --- Displaying or changing the server HA mode applyinfo [option] --- Displaying the status of being applied transaction log to the other node in HA replication environment synccolldb [option] --- Synchronizing the DB collation with the system collation genlocale [option] --- Compiling the locale information to use dumplocale [option] --- Printing human readable text for the compiled binary locale information gen_tz [option] [] --- Generates C source file containing timezone data ready to be compiled into a shared library dump_tz [option] --- Displaying timezone related information tde [option] --- Managing Transparent Data Encryption (TDE) vacuumdb [option] --- Vacuuming deleted records or unnecessary mvcc related information from records in the database flashback [option] --- Provides statements to rewind a specific transaction. cubrid Utility Logging ---------------------- CUBRID supports logging feature for the execution result of **cubrid** utilities; for details, see :ref:`cubrid-utility-logging`. .. _creating-database: .. _createdb: createdb -------- The **cubrid createdb** utility creates databases and initializes them with the built-in CUBRID system tables. It can also define initial users to be authorized in the database and specify the locations of the logs and databases. In general, the **cubrid createdb** utility is used only by DBA. .. warning:: When you create database, a locale name and a charset name after a DB name must be specified(e.g. ko_KR.utf8). It affects the length of string type, string comparison operation, etc. The specified charset when creating database cannot be changed later, so you should be careful when specifying it. For charset, locale and collation setting, see :doc:`/sql/i18n`. :: cubrid createdb [options] database_name locale_name.charset * **cubrid**: An integrated utility for the CUBRID service and database management. * **createdb**: A command used to create a new database. * *database_name*: Specifies a unique name for the database to be created, without including the path name to the directory where the database will be created. If the specified database name is the same as that of an existing database name, CUBRID halts creation of the database to protect existing files. * *locale_name*: A locale name to use in the database should be input. For a locale name which can be used in CUBRID, refer to :ref:`locale-selection`. * *charset*: A characterset to use in the database should be input. A character set which can be used in CUBRID is iso88591, euckr or utf8. * If *locale_name* is en_US and *charset* is omitted, a character set will be iso88591. * If *locale_name* is ko_KR and *charset* is omitted, a character set will be utf8. * All locale names except en_US and ko_KR cannot omit *charset*, and a *charset* can be specified only with utf8. The maximum length of database name is 17 in English. The following shows [options] available with the **cubrid** **createdb** utility. .. program:: createdb .. option:: --db-volume-size=SIZE This option specifies the size of the database volume that will be created first. The default value is the value of the system parameter **db_volume_size**. You can set units as K, M, G and T, which stand for kilobytes (KB), megabytes (MB), gigabytes (GB), and terabytes (TB) respectively; if you omit the unit, bytes will be applied. The size of the database is always rounded up to 64 disk sectors, which depends on the size of a page and can be 16M, 32M or 64M for page size 4k, 8k and 16k respectively. The following example shows how to create a database named *testdb* and assign 512 MB to its first volume. :: cubrid createdb --db-volume-size=512M testdb en_US .. option:: --db-page-size=SIZE This option specifies the size of the database page; the minimum value is 4K and the maximum value is **16K** (default). K stands for kilobytes (KB). The value of page size is one of the following: 4K, 8K, or 16K. If a value between 4K and 16K is specified, system rounds up the number. If a value greater than 16K or less than 4K, the specified number is used. The following example shows how to create a database named *testdb* and configure its page size 16K. :: cubrid createdb --db-page-size=16K testdb en_US .. option:: --log-volume-size=SIZE This option specifies the size of the database log volume. The default value is the same as database volume size, and the minimum value is 20M. You can set units as K, M, G and T, which stand for kilobytes (KB), megabytes (MB), gigabytes (GB), and terabytes (TB) respectively. If you omit the unit, bytes will be applied. The following example shows how to create a database named *testdb* and assign 256 MB to its log volume. :: cubrid createdb --log-volume-size=256M testdb en_US .. option:: --log-page-size=SIZE This option specifies the size of the log volume page. The default value is the same as data page size. The minimum value is 4K and the maximum value is 16K. K stands for kilobytes (KB). The value of page size is one of the following: 4K, 8K, or 16K. If a value between 4K and 16K is specified, system rounds up the number. If a value greater than 16K or less than 4K, the specified number is used. The following example shows how to create a database named *testdb* and configure its log volume page size 8K. :: cubrid createdb --log-page-size=8K testdb en_US .. option:: --comment=COMMENT This option specifies a comment to be included in the database volume header. If the character string contains spaces, the comment must be enclosed in double quotes. The following example shows how to create a database named *testdb* and add a comment to the database volume. :: cubrid createdb --comment "a new database for study" testdb en_US .. option:: -F, --file-path=PATH The **-F** option specifies an absolute path to a directory where the new database will be created. If the **-F** option is not specified, the new database is created in the current working directory. The following example shows how to create a database named *testdb* in the directory /dbtemp/new_db. :: cubrid createdb -F "/dbtemp/new_db/" testdb en_US .. option:: -L, --log-path=PATH The **-L** option specifies an absolute path to the directory where database log files are created. If the **-L** option is not specified, log files are created in the directory specified by the **-F** option. If neither **-F** nor **-L** option is specified, database log files are created in the current working directory. The following example shows how to create a database named *testdb* in the directory /dbtemp/newdb and log files in the directory /dbtemp/db_log. :: cubrid createdb -F "/dbtemp/new_db/" -L "/dbtemp/db_log/" testdb en_US .. option:: -B, --lob-base-path=PATH This option specifies a directory where **LOB** data files are stored when **BLOB/CLOB** data is used. If the **\-\-lob-base-path** option is not specified, LOB data files are store in <*location of database volumes created*>/**lob** directory. The following example shows how to create a database named *testdb* in the working directory and specify /home/data1 of local file system as a location of LOB data files. :: cubrid createdb --lob-base-path "file:/home1/data1" testdb en_US .. option:: --server-name=HOST This option enables the server of a specific database to run in the specified host when CUBRID client/server is used. The information of a host specified is stored in the **databases.txt** file. If this option is not specified, the current localhost is specified by default. The following example shows how to create a database named *testdb* and register it on the host *aa_host*. :: cubrid createdb --server-name aa_host testdb en_US .. option:: -r, --replace This option creates a new database and overwrites an existing database if one with the same name exists. The following example shows how to create a new database named *testdb* and overwrite the existing database with the same name. :: cubrid createdb -r testdb en_US .. option:: --more-volume-file=FILE This option creates an additional volume based on the specification contained in the file specified by the option. The volume is created in the same directory where the database is created. Instead of using this option, you can add a volume by using the **cubrid addvoldb** utility. The following example shows how to create a database named *testdb* as well as an additional volume based on the specification stored in the **vol_info.txt** file. :: cubrid createdb --more-volume-file vol_info.txt testdb en_US The following is a specification of the additional volume contained in the **vol_info.txt** file. The specification of each volume must be written on a single line. :: #xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx # NAME volname COMMENTS volcmnts PURPOSE volpurp NPAGES volnpgs NAME data_v1 COMMENTS "data information volume" PURPOSE data NPAGES 1000 NAME data_v2 COMMENTS "data information volume" NPAGES 1000 NAME temp_v1 COMMENTS "temporary information volume" PURPOSE temp NPAGES 500 #xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx As shown in the example, the specification of each volume consists following. :: [NAME volname] [COMMENTS volcmnts] [PURPOSE volpurp] NPAGES volnpgs * *volname*: The name of the volume to be created. It must follow the UNIX file name conventions and be a simple name not including the directory path. The specification of a volume name can be omitted. If it is, the "database name to be created by the system_volume identifier" becomes the volume name. * *volcmnts*: Comment to be written in the volume header. It contains information on the additional volume to be created. The specification of the comment on a volume can also be omitted. * *volpurp*: The purpose for which the volume will be used. It can be either permanent data (default option) or temporary. .. note:: For backward compatibility, all old keywords, **data**, **index**, **temp**, or **generic** are accepted. **temp** stands for temporary data purpose, while the rest stand for permanent data purpose. * *volnpgs*: The number of pages of the additional volume to be created. The specification of the number of pages of the volume cannot be omitted; it must be specified. The actual volume size is rounded up to the next multiple of **64 sectors**. .. option:: --user-definition-file=FILE This option adds users who have access to the database to be created. It adds a user based on the specification contained in the user information file specified by the parameter. Instead of using the **\-\-user-definition-file** option, you can add a user by using the **CREATE USER** statement (for details, see :ref:`create-user`). The following example shows how to create a database named *testdb* and add users to *testdb* based on the user information defined in the **user_info.txt** file. :: cubrid createdb --user-definition-file=user_info.txt testdb en_US The syntax of a user information file is as follows: :: USER user_name [ | ] : [ GROUPS [ { }... ] ] : [ MEMBERS [ { }... ] ] * The *user_name* is the name of the user who has access to the database. It must not include spaces. * The **GROUPS** clause is optional. The *group_name* is the upper level group that contains the *user_name* . Here, the *group_name* can be multiply specified and must be defined as **USER** in advance. * The **MEMBERS** clause is optional. The *member_name* is the name of the lower level member that belongs to the *user_name* . Here, the *member_name* can be multiply specified and must be defined as **USER** in advance. Comments can be used in a user information file. A comment line must begin with a consecutive hyphen lines (\-\-). Blank lines are ignored. The following example shows a user information in which *grandeur* and *sonata* are included in *sedan* group, *tuscan* is included in *suv* group, and *i30* is included in *hatchback* group. The name of the user information file is **user_info.txt**. :: -- -- Example 1 of a user information file -- USER sedan USER suv USER hatchback USER grandeur GROUPS sedan USER sonata GROUPS sedan USER tuscan GROUPS suv USER i30 GROUPS hatchback The following example shows a file that has the same user relationship information as the file above. The difference is that the **MEMBERS** statement is used in the file below. :: -- -- Example 2 of a user information file -- USER grandeur USER sonata USER tuscan USER i30 USER sedan MEMBERS sonata grandeur USER suv MEMBERS tuscan USER hatchback MEMBERS i30 .. option:: --csql-initialization-file=FILE This option executes an SQL statement on the database to be created by using the CSQL Interpreter. A schema can be created based on the SQL statement contained in the file specified by the parameter. The following example shows how to create a database named *testdb* and execute the SQL statement defined in table_schema.sql through the CSQL Interpreter. :: cubrid createdb --csql-initialization-file table_schema.sql testdb en_US .. option:: -o, --output-file=FILE This option stores messages related to the database creation to the file given as a parameter. The file is created in the same directory where the database was created. If the **-o** option is not specified, messages are displayed on the console screen. The **-o** option allows you to use information on the creation of a certain database by storing messages, generated during the database creation, to a specified file. The following example shows how to create a database named *testdb* and store the output of the utility to the **db_output** file instead of displaying it on the console screen. :: cubrid createdb -o db_output testdb en_US .. option:: -v, --verbose This option displays all information on the database creation operation onto the screen. Like the **-o** option, this option is useful in checking information related to the creation of a specific database. Therefore, if you specify the **-v** option together with the **-o** option, you can store the output messages in the file given as a parameter; the messages contain the operation information about the **cubrid createdb** utility and database creation process. The following example shows how to create a database named *testdb* and display detailed information on the operation onto the screen. :: cubrid createdb -v testdb en_US .. note:: * **temp_file_max_size_in_pages** is a parameter used to configure the maximum size of temporary volumes - used for complicated queries or storing arrays - on the disk. With the default value **-1**, the temporary volumes size is only limited by the capacity of the disk specified by the **temp_volume_path** parameter. If the value is 0, no temporary volumes can be created. In this case, a permanent volume with temporary data purpose should be added by using the :ref:`cubrid addvoldb ` utility. For an efficient storage management, it is recommended to use the latter approach. * By using the :ref:`cubrid spacedb ` utility, you can check the remaining space of each volume. By using the :ref:`cubrid addvoldb ` utility, you can add more volumes as needed while managing the database. You are advised to add more volumes when there is less system load. When all preassigned volumes are completely in use, the database system automatically creates new volumes. The following example shows how to create a database, with additional volumes, including one for temporary data purpose. :: cubrid createdb --db-volume-size=512M --log-volume-size=256M cubriddb en_US cubrid addvoldb -S -n cubriddb_DATA01 --db-volume-size=512M cubriddb cubrid addvoldb -S -p temp -n cubriddb_TEMP01 --db-volume-size=512M cubriddb .. note:: **Creating a database using an existing key file** When the database is created, a key file is created together by default. If you want to use an existing key file when creating a database: #. Copy the key file with the name **_keys**. #. Specify the directory path of the copied key file by the system parameter **tde_keys_file_path**. #. Create a database by using the **createdb utility**. For more information on the TDE key file, see :ref:`tde-file-based-key`. .. _adding-database-volume: .. _addvoldb: addvoldb -------- If you want to micromanage CUBRID storage volumes, addvoldb is the tool for you. You can finely tune each file name, path, purpose, and size. The database system can handle all storage by itself, but it uses default values to configure each new volume. The command for manually adding a database volume is as follows. :: cubrid addvoldb [options] database_name * **cubrid**: An integrated utility for CUBRID service and database management. * **addvoldb**: A command that adds a specified number of pages of the new volume to a specified database. * *database_name*: Specifies the name of the database to which a volume is to be added without including the path name to the directory where the database is to be created. The following example shows how to create a database, with additional multi-purpose volumes. :: cubrid createdb --db-volume-size=512M --log-volume-size=256M cubriddb en_US cubrid addvoldb -S -n cubriddb_DATA01 --db-volume-size=512M cubriddb cubrid addvoldb -S -p temp -n cubriddb_TEMP01 --db-volume-size=512M cubriddb The following shows [options] available with the **cubrid addvoldb** utility. .. program:: addvoldb .. option:: --db-volume-size=SIZE **\-\-db-volume-size** is an option that specifies the size of the volume to be added to a specified database. If the **\-\-db-volume-size** option is omitted, the value of the system parameter **db_volume_size** is used by default. You can set units as K, M, G and T, which stand for kilobytes (KB), megabytes (MB), gigabytes (GB), and terabytes (TB) respectively. If you omit the unit, bytes will be applied. The size of the database is always rounded up to 64 disk sectors, which depends on the size of a page and can be 16M, 32M or 64M for page size 4k, 8k and 16k respectively. The following example shows how to add a volume for which 256 MB are assigned to the *testdb* database. :: cubrid addvoldb --db-volume-size=256M testdb .. option:: -n, --volume-name=NAME This option specifies the name of the volume to be added to a specified database. The volume name must follow the file name protocol of the operating system and be a simple one without including the directory path or spaces. If the **-n** option is omitted, the name of the volume to be added is configured by the system automatically as "database name_volume identifier". For example, if the database name is *testdb*, the volume name *testdb_x001* is automatically configured. The following example shows how to specify a different name, *testdb_v1*, to newly added volume. :: cubrid addvoldb -n testdb_v1 testdb .. option:: -F, --file-path=PATH This option specifies the directory path where the volume to be added will be stored. If the **-F** option is omitted, the value of the system parameter **volume_extension_path** is used by default. The following example shows how to add a volume in the */dbtemp/addvol* directory. Since the **-n** option is not specified for the volume name, the volume name *testdb_x001* will be created. :: cubrid addvoldb -F /dbtemp/addvol/ testdb .. option:: --comment COMMENT This option facilitates to retrieve information on the added volume by adding such information in the form of comments. It is recommended that the contents of a comment include the name of **DBA** who adds the volume, or the purpose of adding the volume. The comment must be enclosed in double quotes. The following example shows how to add a volume and inserts a comment with additional information. :: cubrid addvoldb --comment "Data volume added by cheolsoo kim because permanent data space was almost depleted." testdb .. option:: -p, --purpose=PURPOSE This option specifies the purpose of the volume to be added. The purpose defines the type of files that will be stored in added volume: * **PERMANENT DATA** to store table rows, indexes and system files. * **TEMPORARY DATA** to store intermediate and final results of query processing and sorting. If not specified, the purpose of the volume is by default considered **PERMANENT DATA**. The following example shows how to change it to temporary. :: cubrid addvoldb -p temp testdb .. note:: PERMANENT DATA volumes used to be classified as generic, data and index. The design of volumes has been changed, and since then the classification no longer exists. In order to avoid invalidating your old scripts, we chose to keep the keywords as valid options, but their effect will be the same. The only remaining option value with a real effect is temp. For detailed information on each purpose, see :ref:`database-volume-structure`. .. option:: -S, --SA-mode This option accesses the database in standalone mode without running the server process. This option has no parameter. If the **-S** option is not specified, the system assumes to be in client/server mode. :: cubrid addvoldb -S --db-volume-size=256M testdb .. option:: -C, --CS-mode This option accesses the database in client/server mode by running the server and the client separately. There is no parameter. Even when the **-C** option is not specified, the system assumes to be in client/server mode by default. :: cubrid addvoldb -C --db-volume-size=256M testdb .. option:: --max-writesize-in-sec=SIZE The **\-\-max-writesize-in-sec** is used to limit the impact of system operating when you add a volume to the database. This can limit the maximum writing size per second. The unit of this option is K(kilobytes) and M(megabytes). The minimum value is 160K. If you set this value as less than 160K, it is changed as 160K. It can be used only in client/server mode. The below is an example to limit the writing size of the 2GB volume as 1MB. Consuming time will be about 35 minutes(= (2048MB/1MB) /60 sec.). :: cubrid addvoldb -C --db-volume-size=2G --max-writesize-in-sec=1M testdb .. _deleting-database: .. _deletedb: deletedb -------- The **cubrid deletedb** utility is used to delete a database. You must use the **cubrid deletedb** utility to delete a database, instead of using the file deletion commands of the operating system; a database consists of a few interdependent files. The **cubrid deletedb** utility also deletes the information on the database from the database location file (**databases.txt**). The **cubrid deletedb** utility must be run offline, that is, in standalone mode when nobody is using the database. :: cubrid deletedb [options] database_name * **cubrid**: An integrated utility for the CUBRID service and database management. * **deletedb**: A command to delete a database, its related data, logs and all backup files. It can be executed successfully only when the database is in a stopped state. * *database_name*: Specifies the name of the database to be deleted without including the path name. The following shows [options] available with the **cubrid deletedb** utility. .. program:: deletedb .. option:: -o, --output-file=FILE This option specifies the file name for writing messages:: cubrid deletedb -o deleted_db.out testdb The **cubrid** **deletedb** utility also deletes the database information contained in the database location file (**databases.txt**). The following message is returned if you enter a utility that tries to delete a non-existing database. :: cubrid deletedb testdb Database "testdb" is unknown, or the file "databases.txt" cannot be accessed. .. option:: -d, --delete-backup This option deletes database volumes, backup volumes and backup information files simultaneously. If the -**d** option is not specified, backup volume and backup information files are not deleted. :: cubrid deletedb -d testdb .. _renamedb: renamedb -------- The **cubrid renamedb** utility renames a database. The names of information volumes, log volumes and control files are also renamed to conform to the new database one. In contrast, the **cubrid alterdbhost** utility configures or changes the host name of the specified database. In other words, it changes the host name configuration in the **databases.txt** file. :: cubrid renamedb [options] src_database_name dest_database_name * **cubrid**: An integrated utility for the CUBRID service and database management. * **renamedb**: A command that changes the existing name of a database to a new one. It executes successfully only when the database is in a stopped state. The names of related information volumes, log volumes and control files are also changed to new ones accordingly. * *src_database_name*: The name of the existing database to be renamed. The path name to the directory where the database is to be created must not be included. * *dest_database_name*: The new name of the database. It must not be the same as that of an existing database. The path name to the directory where the database is to be created must not be included. The following shows [options] available with the **cubrid renamedb** utility. .. program:: renamedb .. option:: -E, --extended-volume-path=PATH This option renames an extended volume created in a specific directory path (e.g. /dbtemp/addvol/), and then moves the volume to a new directory. This specifies a new directory path (e.g. /dbtemp/newaddvols/) where the renamed extended volume will be moved. If it is not specified, the extended volume is only renamed in the existing path without being moved. If a directory path outside the disk partition of the existing database volume or an invalid one is specified, the rename operation is not executed. This option cannot be used together with the **-i** option. :: cubrid renamedb -E /dbtemp/newaddvols/ testdb testdb_1 .. option:: -i, --control-file=FILE The option specifies an input file in which directory information is stored to change all database name of volumes or files and assign different directory at once. To perform this work, the **-i** option is used. The **-i** option cannot be used together with the **-E** option. :: cubrid renamedb -i rename_path testdb testdb_1 The following are the syntax and example of a file that contains the name of each volume, the current directory path and the directory path where renamed volumes will be stored. :: volid source_fullvolname dest_fullvolname * *volid*: An integer that is used to identify each volume. It can be checked in the database volume control file (database_name_vinf). * *source_fullvolname*: The current directory path to each volume. * *dest_fullvolname*: The target directory path where renamed volumes will be moved. If the target directory path is invalid, the database rename operation is not executed. :: -5 /home1/user/testdb_vinf /home1/CUBRID/databases/testdb_1_vinf -4 /home1/user/testdb_lginf /home1/CUBRID/databases/testdb_1_lginf -3 /home1/user/testdb_bkvinf /home1/CUBRID/databases/testdb_1_bkvinf -2 /home1/user/testdb_lgat /home1/CUBRID/databases/testdb_1_lgat 0 /home1/user/testdb /home1/CUBRID/databases/testdb_1 1 /home1/user/backup/testdb_x001/home1/CUBRID/databases/backup/testdb_1_x001 .. option:: -d, --delete-backup This option renames the *testdb* database and at once forcefully delete all backup volumes and backup information files that are in the same location as *testdb*. Note that you cannot use the backup files with the old names once the database is renamed. If the **-d** option is not specified, backup volumes and backup information files are not deleted. :: cubrid renamedb -d testdb testdb_1 .. _alterdbhost: alterdbhost ----------- The **cubrid alterdbhost** utility sets or changes the host name of the specified database. It changes the host name set in the **databases.txt** file. :: cubrid alterdbhost [option] database_name * **cubrid**: An integrated utility for the CUBRID service and database management * **alterdbhost**: A command used to change the host name of the current database The following shows the option available with the **cubrid alterdbhost** utility. .. program:: alterdbhost .. option:: -h, --host=HOST The *-h* option specifies the host name to be changed. When this option is omitted, specifies the host name to localhost. .. _copydb: copydb ------ The **cubrid copydb** utility copy or move a database to another location. As arguments, source and target name of database must be given. A target database name must be different from a source database name. When the target name argument is specified, the location of target database name is registered in the **databases.txt** file. The **cubrid copydb** utility can be executed only offline (that is, state of a source database stop). :: cubrid copydb [options] src-database-name dest-database-name * **cubrid**: An integrated utility for the CUBRID service and database management. * **copydb**: A command that copy or move a database from one to another location. * *src-database-name*: The names of source and target databases to be copied or moved. * *dest-database-name*: A new (target) database name. If options are omitted, a target database is copied into the same directory of a source database. The following shows [options] available with the **cubrid copydb** utility. .. program:: copydb .. option:: --server-name=HOST The **\-\-server-name** option specifies a host name of new database. The host name is registered in the **databases.txt** file. If this option is omitted, a local host is registered. :: cubrid copydb --server-name=cub_server1 demodb new_demodb .. option:: -F, --file-path=PATH The *-F* option specifies a specific directory path where a new database volume is stored with an **-F** option. It represents specifying an absolute path. If the specified directory does not exist, an error is displayed. If this option is omitted, a new database volume is created in the current working directory. And this information is specified in **vol-path** of the **databases.txt** file. :: cubrid copydb -F /home/usr/CUBRID/databases demodb new_demodb .. option:: -L, --log-path=PATH The *-L* option specifies a specific directory path where a new database volume is stored with an **-L** option. It represents specifying an absolute path. If the specified directory does not exist, an error is displayed. If this option is omitted, a new database volume is created in the current working directory. And this information is specified in **log-path** of the **databases.txt** file. :: cubrid copydb -L /home/usr/CUBRID/databases/logs demodb new_demodb .. option:: -E, --extended-volume-path=PATH The *-E* option specifies a specific directory path where a new database extended volume is stored with an **-E**. If this option is omitted, a new database extended volume is created in the location of a new database volume or in the registered path of controlling file. The **-i** option cannot be used with this option. :: cubrid copydb -E home/usr/CUBRID/databases/extvols demodb new_demodb .. option:: -i, --control-file=FILE The **-i** option specifies an input file where a new directory path information and a source volume are stored to copy or move multiple volumes into a different directory, respectively. This option cannot be used with the **-E** option. An input file named copy_path is specified in the example below. :: cubrid copydb -i copy_path demodb new_demodb The following is an example of input file that contains each volume name, current directory path, and new directory and volume names. :: # volid source_fullvolname dest_fullvolname 0 /usr/databases/demodb /drive1/usr/databases/new_demodb 1 /usr/databases/demodb_data1 /drive1/usr/databases/new_demodb_data1 2 /usr/databases/ext/demodb_ext1 /drive2//usr/databases/new_demodb_ext1 3 /usr/databases/ext/demodb_ext2 /drive2/usr/databases/new_demodb_ext2 * *volid*: An integer that is used to identify each volume. It can be checked in the database volume control file (**database_name_vinf**). * *source_fullvolname*: The current directory path to each source database volume. * *dest_fullvolname*: The target directory path where new volumes will be stored. You should specify a valid path. .. option:: -r, --replace If the **-r** option is specified, a new database name overwrites the existing database name if it is identical, instead of outputting an error. :: cubrid copydb -r -F /home/usr/CUBRID/databases demodb new_demodb .. option:: -d, --delete-source If the **-d** option is specified, a source database is deleted after the database is copied. This execution brings the same the result as executing **cubrid deletedb** utility after copying a database. Note that if a source database contains LOB data, LOB file directory path of a source database is copied into a new database and it is registered in the **lob-base-path** of the **databases.txt** file. :: cubrid copydb -d -F /home/usr/CUBRID/databases demodb new_demodb .. option:: --copy-lob-path=PATH If the **\-\-copy-lob-path** option is specified, a new directory path for LOB files is created and a source database is copied into a new directory path. If this option is omitted, the directory path is not created. Therefore, the **lob-base-path** of the **databases.txt** file should be modified separately. This option cannot be used with the **-B** option. :: cubrid copydb --copy-lob-path demodb new_demodb .. option:: -B, --lob-base-path=PATH If the **-B** option is specified, a specified directory is specified as for LOB files of a new database and a source database is copied. This option cannot be used with the **\-\-copy-lob-path** option. :: cubrid copydb -B /home/usr/CUBRID/databases/new_lob demodb new_demodb .. _installdb: installdb --------- The **cubrid installdb** utility is used to register the information of a newly installed database to **databases.txt**, which stores database location information. The execution of this utility does not affect the operation of the database to be registered. :: cubrid installdb [options] database_name * **cubrid**: An integrated utility for the CUBRID service and database management. * **installdb**: A command that registers the information of a moved or copied database to **databases.txt**. * *database_name*: The name of database to be registered to **databases.txt**. If no [options] are used, the command must be executed in the directory where the corresponding database exists. The following shows [options] available with the **cubrid installdb** utility. .. program:: installdb .. option:: --server-name=HOST This option registers the server host information of a database to **databases.txt** with a specific host name. If this is not specified, the current host information is registered. :: cubrid installdb --server-name=cub_server1 testdb .. option::-F, --file-path=PATH This option registers the absolute directory path of a database volume to **databases.txt** by using the **-F** option. If this option is not specified, the path of a current directory is registered as default. :: cubrid installdb -F /home/cubrid/CUBRID/databases/testdb testdb .. option:: -L, --log-path=PATH This option registers the absolute directory path of a database log volume to **databases.txt** by using the **-L** option. If this option is not specified, the directory path of a volume is registered. :: cubrid installdb -L /home/cubrid/CUBRID/databases/logs/testdb testdb .. include:: backup.inc .. include:: migration.inc .. _spacedb: spacedb ------- The **cubrid spacedb** utility is used to check how much space of database volumes is being used. The tool can show brief aggregated information on database space usage, or detailed descriptions of all volumes and files in use, based on its options. Information returned by the **cubrid spacedb** utility includes the volume ID's, names, purpose and total/free space of each volume. :: cubrid spacedb [options] database_name * **cubrid** : An integrated utility for the CUBRID service and database management. * **spacedb** : A command that checks the space in the database. It executes successfully only when the database is in a stopped state. * *database_name* : The name of the database whose space is to be checked. The path-name to the directory where the database is to be created must not be included. The following shows [options] available with the **cubrid spacedb** utility. .. program:: spacedb .. option:: -o FILE This option stores the result of checking the space information of *testdb* to a file named *db_output*. :: cubrid spacedb -o db_output testdb .. option:: -S, --SA-mode This option is used to access a database in standalone, which means it works without processing server; it does not have an argument. If **-S** is not specified, the system recognizes that a database is running in client/server mode. :: cubrid spacedb --SA-mode testdb .. option:: -C, --CS-mode This option is used to access a database in client/server mode, which means it works in client/server process respectively; it does not have an argument. If **-C** is not specified, the system recognize that a database is running in client/server mode by default. :: cubrid spacedb --CS-mode testdb .. option:: --size-unit={PAGE|M|G|T|H} This option specifies the size unit of the space information of the database to be one of PAGE, M(MB), G(GB), T(TB), H(print-friendly). The default value is **H**. If you set the value to H, the unit is automatically determined as follows: M if 1 MB = DB size < 1024 MB, G if 1 GB = DB size < 1024 GB. :: $ cubrid spacedb --size-unit=H testdb Space description for database 'testdb' with pagesize 16.0K. (log pagesize: 16.0K) type purpose volume_count used_size free_size total_size PERMANENT PERMANENT DATA 2 61.0 M 963.0 M 1.0 G PERMANENT TEMPORARY DATA 1 12.0 M 500.0 M 512.0 M TEMPORARY TEMPORARY DATA 1 40.0 M 88.0 M 128.0 M - - 4 113.0 M 1.5 G 1.6 G Space description for all volumes: volid type purpose used_size free_size total_size volume_name 0 PERMANENT PERMANENT DATA 60.0 M 452.0 M 512.0 M /home1/cubrid/testdb 1 PERMANENT PERMANENT DATA 1.0 M 511.0 M 512.0 M /home1/cubrid/testdb_x001 2 PERMANENT TEMPORARY DATA 12.0 M 500.0 M 512.0 M /home1/cubrid/testdb_x002 32766 TEMPORARY TEMPORARY DATA 40.0 M 88.0 M 128.0 M /home1/cubrid/testdb_t32766 LOB space description file:/home1/cubrid/lob .. option:: -s, --summarize This option aggregates volume count, used size, free size and total size by volume types and purposes. There are three classes of volumes: permanent volumes with permanent data, permanent volumes with temporary data and temporary volume with temporary data; no temporary volumes with permanent data. Last row shows the total values for all types of volumes. :: $ cubrid spacedb -s testdb Space description for database 'testdb' with pagesize 16.0K. (log pagesize: 16.0K) type purpose volume_count used_size free_size total_size PERMANENT PERMANENT DATA 2 61.0 M 963.0 M 1.0 G PERMANENT TEMPORARY DATA 1 12.0 M 500.0 M 512.0 M TEMPORARY TEMPORARY DATA 1 40.0 M 88.0 M 128.0 M - - 4 113.0 M 1.5 G 1.6 G .. option:: -p, --purpose This option shows detailed information on the purpose of stored data. The information includes number of files, used size, size of file tables, reserved sectors size and total size. :: Space description for database 'testdb' with pagesize 16.0K. (log pagesize: 16.0K) Detailed space description for files: data_type file_count used_size file_table_size reserved_size total_size INDEX 17 0.3 M 0.3 M 16.5 M 17.0 M HEAP 28 7.6 M 0.4 M 26.0 M 34.0 M SYSTEM 8 0.4 M 0.1 M 7.5 M 8.0 M TEMP 10 0.0 M 0.2 M 49.8 M 50.0 M - 63 8.2 M 1.0 M 99.8 M 109.0 M .. _compactdb: compactdb --------- The **cubrid compactdb** utility is used to secure unused space of the database volume. In case the database server is not running (offline), you can perform the job in standalone mode. In case the database server is running, you can perform it in client-server mode. .. note:: The **cubrid compactdb** utility secures the space being taken by OIDs of deleted objects and by class changes. When an object is deleted, the space taken by its OID is not immediately freed because there might be other objects that refer to the deleted one. Therefore, when you make a table to reuse OIDs, it is recommended to use a REUSE_OID option as below. .. code-block:: sql CREATE TABLE tbl REUSE_OID ( id INT PRIMARY KEY, b VARCHAR ); However, a table with a REUSE_OID option cannot be referred by the other table. That is, this table cannot be used as a type of the other table. .. code-block:: sql CREATE TABLE reuse_tbl (a INT PRIMARY KEY) REUSE_OID; CREATE TABLE tbl_1 ( a reuse_tbl); :: ERROR: The class 'reuse_tbl' is marked as REUSE_OID and is non-referable. Non-referable classes can't be the domain of an attribute and their instances' OIDs cannot be returned. To see details of REUSE_OID, please refer to :ref:`reuse-oid`. Reference to the object deleted during compacting is displayed as **NULL**, which means this can be reused by OIDs. :: cubrid compactdb [options] database_name [schema_name.class_name [{, schema_name.class_name}]] * **cubrid**: An integrated utility for the CUBRID service and database management. * **compactdb**: A command that compacts the space of the database so that OIDs assigned to deleted data can be reused. * *database_name*: The name of the database whose space is to be compacted. The path name to the directory where the database is to be created must not be included. * *class_name_list*: You can specify the list of tables names that you want to compact space after a database name; the **-i** option cannot be used together. The table name must be prefixed with the schema name. If you use the lists on client/server mode, it skips securing space taken by objects such as catalog, delete files and tracker, etc. **-I**, **-c**, **-d**, **-p** options are applied in client/server mode only. The following shows [options] available with the **cubrid compactdb** utility. .. program:: compactdb .. option:: -v, --verbose You can output messages that shows which class is currently being compacted and how many instances have been processed for the class by using the **-v** option. :: cubrid compactdb -v testdb .. option:: -S, --SA-mode This option specifies to compact used space in standalone mode while database server is not running; no argument is specified. If the **-S** option is not specified, system recognizes that the job is executed in client/server mode. :: cubrid compactdb --SA-mode testdb .. option:: -C, --CS-mode This option specifies to compact used space in client/server mode while database server is running; no argument is specified. Even though this option is omitted, system recognizes that the job is executed in client/server mode. .. option:: -i, --input-class-file=FILE You can specify an input file name that contains the table name with this option. Write one table name in a single line; invalid table name is ignored. The table name must be prefixed with the schema name. Note that you cannot specify the list of the table names after a database name in case of you use this option. If you use this option on client/server mode, it skips securing space taken by objects such as catalog, delete files and tracker, etc. The following options can be used in client/server mode only. .. option:: -p, --pages-commited-once=NUMBER You can specify the number of maximum pages that can be committed once with this option. The default value is 10, the minimum value is 1, and the maximum value is 10. The less option value is specified, the more concurrency is enhanced because the value for class/instance lock is small; however, it causes slowdown on operation, and vice versa. :: cubrid compactdb --CS-mode -p 10 testdb public.tbl1, public.tbl2, public.tbl5 .. option:: -d, --delete-old-repr You can delete an existing table representation (schema structure) from catalog with this option. Generally you'd better keep the existing table representation because schema updating cost will be saved when you keep the status as referring to the past schema for the old records. .. option:: -I, --Instance-lock-timeout=NUMBER You can specify a value of instance lock timeout with this option. The default value is 2 (seconds), the minimum value is 1, and the maximum value is 10. The less option value is specified, the more operation speeds up. However, the number of instances that can be processed becomes smaller, and vice versa. .. option:: -c, --class-lock-timeout=NUMBER You can specify a value of instance lock timeout with this option. The default value is 10 (seconds), the minimum value is 1, and the maximum value is 10. The less option value is specified, the more operation speeds up. However, the number of tables that can be processed becomes smaller, and vice versa. .. _optimizedb: optimizedb ---------- Updates statistical information such as the number of objects, the number of pages to access, and the distribution of attribute values. :: cubrid optimizedb [