Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

cm_manual_objects


CUBRID Manager :: Database objects


6. Database objects



6.1. User


You can add, edit or drop users by right-clicking Users in the navigation tree after login to the database. All users of the current database are displayed in the sub-node of Users.


Create User

You can add a user by right-clicking Users in the navigation tree and then selecting Create User or by clicking Create User from the toolbar menu.


General User Information

  • User name/Password : The name and password of the user to be added. The maximum length of the user name is 32 characters.
  • All users : Lists the list of users that can be selected as the group for the user to be added.
  • Authorization of this user : Displays the list of groups of users to be added. You can specify a group with which users to add from the All users list will join by using the arrow button. However, you cannot modify the authorization of dba and public accounts.
  • Users that have this user's authorization : Displays the list of users that have this user's authorization.

User Authorization Information

In the User authorization information tab, you can grant or revoke authorization for each table. You can sort authorization grantees for each column and select multiple tables to grant authorization.


Edit User

You can edit the settings entered in the Add User dialog box. However, the user name cannot be modified.


Drop User

You can drop the selected database user account.



6.2. Table


When you log in to the database, you can see accessible tables and system tables and columns and index in the navigation tree.

If you right-click the Table in the navigation tree, you can view the Create Table, Create Like Table, and Refresh menus.


Create Table

Right-click Tables in the navigation tree and then select Create Table or click Create Table from the toolbar. Then, a wizard which makes you create a new table will appear.

  • General tab : You can define the name of the table to be added and add, edit and drop columns. You can also set the primary key (PK) and, before the table is created, adjust the position of the selected column by using and .
    Using the add column wizard, you can set the name, type, default value and constraints of the column to be added. You can also set to display a warning message when you enter something that is not grammatically correct, or to disable the selection of a grammatically incorrect entry.
    • Reuse_OID : If this option is selected, a table is created with the option REUSE_OID applied. For this kind of table, using with OID is restricted. For more information on OID reuse table, see "Table Options (REUSE_OID)" in the CUBRID online manual.
  • FK/Indexes tab : You can set foreign keys and indexes.
    • Add Foreign Key : Using the add foreign key wizard, you can set the name of the foreign key, the name and primary key of the reference table and the trigger actions to maintain referential integrity. The ON UPDATE, ON DELETE and ON CACHE OBJECT options are provided.
    • Add Index : You can set index name, index type, a column to be indexed. Ascending (asc) and descending (desc) sorting can be selected within the supported range. For REVERSE indexes, only descending sorting is supported.
  • Partition Tab : Supports partitioning setting and modification of the given table.
    • Add Partition : Using the add partition wizard, you can set the partition type and expression. The RANGE, LIST and HASH partitions are supported.
    • Edit/Delete Partition : You can edit or delete partition.
  • SQL Statement Tab : You can check and copy SQL statements created according to the settings in General, Foreign Key/Index and Partition tabs.


Adding Object Oriented Tables

To add a table with object-oriented properties, select Show object oriented related properties. When you select Show object oriented related properties, the Inheritance tab is added.

  • General tab : When you select Show object oriented related properties, Shared, Inheritance and Table column are added as a column in the table. When adding or editing columns, you can choose the column type as shown in the figure below. You can choose OBJECT or a table in the database as the data type.
  • Inheritance tab : You can define the super table to inherit from. If a column name conflict occurs, it can be adjusted.


Select All


Right-click a table in the navigation tree and select Select All. Or you can drag and drop the table into the editor results pane of the Query editor when it is open. Then, a new Query editor opens and retrieves the entire data.


Select Count

Retrieves a total data count of the table and performs the same functionality as the following syntax.

SELECT COUNT(*) FROM table_name



DELETE ALL

Deletes all records from the table and performs the same functionality as the following syntax.

DELETE FROM table_name



TRUNCATE TABLE

Deletes entire data in the table. It can delete all records including indexes and constraints in a table, so it is faster than DELETE ALL. The ON DELETE trigger is not activated when you use the TRUNCATE TABLE. It can perform the same functionality as the following syntax.

TRUNCATE TABLE table_name




Insert Records

You can insert values for each column while checking its type and constraints.

  • When you add more than one instance, you can separate them by adding a line break character between each query and its result.
  • You can move the cursor to the next field by pressing the Enter key when you enter a value for each field.
  • When you click the Clear button, the value in the input box and the execution history are initialized.
  • The execution history pane cannot be edited.
  • For DATE, TIME, TIMESTAMP and DATETIME data types, you can enter different data for each type. For example, for a DATE type, you can enter data such as SYSDATE, SYS_DATE, CURRENT_DATE and DATE'2009-07-05'.


Import Data

You can import data from an Excel or CSV file into the database. You can use File charset to get data from a file, and use JDBC charset to specify charset for data to be stored in the database. To change the value for JDBC charset, right-click the mouse and then select Properties.

By selecting Ignore or Break, you can change the way when an error occurs in the process of import.


Export Data

You can export data (usually in one or more tables) in Excel (.xls), CSV, SQL, or CUBRID load (.obs) format. A file name is created with a table name and it is saved in the path specified in File path. You can choose File charset when exporting data.


Drop Table

Drops the selected table. This is the same as the DROP TABLE statement.


Rename Table

You can change the name of the current table. It is the same as RENAME TABLE statement.


Create Like Table

Creates an empty table which has the same schema structure with an existing one. It works the same as CREATE TABLE LIKE. For more information, see "CREATE TABLE LIKE" in the CUBRID online manual.


Edit Table

You can use all functionalities of Create Table in Edit Table as well. However, you cannot adjust the order of columns in the table.


Table Information

You can check the schema information of the table by double-clicking it.


Execute Defined SQL

With Execute Defined SQL menu, you can execute "prepared statement," pre-saving a specific query statement and specifying a new parameter value for the statement whenever the query is executed.

You can execute the SELECT statement which meets specified conditions by entering a parameter value of defined "prepared statement" by using Select by input value.

You can execute the INSERT statement by entering a parameter value of defined "prepared statement" by using Insert by input value.

You can use Select by read file when repeating the execution of SELECT statement by inputting several parameter values in the defined "prepared statement." The data is saved in Excel (.xls) or CSV format. You can configure a  file charset where parameter values are saved, the number of concurrent threads to be executed, and commit cycle. To change the value for JDBC charset, right-click the mouse in Explorer and then select Properties.

You can use Insert by read file when repeating the execution of INSERT statement by inputting several parameter values in the defined "prepared statement." The data is saved in Excel (.xls) or CSV format. You can configure a  file charset where parameter values are saved, the number of concurrent threads to be executed, and commit cycle. To change the value for JDBC charset, right-click the mouse in Explorer and then select Properties.


Copy SQL to Editor

A convenient function is provided that allows you to copy DDL and DML in a selected table to the current editor. If there is no editor opened, a new editor will opened and show the DDL or DML in it. The text in menu item gives the format of the statement. You can modify it based upon your requirements. INSERT, SELECT, CREATE, UPDATE, DELETE and GRANT statements are included.


Copy SQL to Clipboard

Like Copy SQL to Editor, you can copy DDL and DML in a selected table. But the difference is the object address is clipboard.


Copy SQL to File

You can copy DDL and DML in a selected table to a given file.



6.3. View


When you login to the database, you can see accessible views and system views in the navigation tree.

Right-click Views in the navigation tree and then select Create View.

Specify the view name and the owner, click the Add button, and then enter the query for the view to be created. You can check the contents entered in the Create View wizard from the SQL statement in the SQL Script tab.



6.4. Trigger


When you login to the database, you can view accessible triggers in the navigation tree.

Right-click Triggers in the navigation tree and then select Create Trigger.

  • Trigger name : Enter the name of the trigger to be added.
  • Target table/Column : Enter the target table and column information.
  • Event Time : Select the point of time when the condition of the trigger is to be evaluated. You can select from BEFORE, AFTER, and DEFERRED.
  • Event Type : Select the type of the event to be occurred. Event types are INSERT, DELETE, UPDATE, STATEMENT INSERT, STATEMENT DELETE, STATEMENT UPDATE, COMMIT and ROLLBACK.
  • Event Condition : Enter the condition for the trigger action.
  • Execution Time : Specify the point of time when the trigger is to be executed. If default is selected, the trigger fires based on the point of time when its condition is validated.
  • Action Type : Select the type of the trigger action. If the type of the trigger action is PRINT, OTHER or STATEMENT, you can enter additional information in the SQL statements or print messages below.
  • Trigger Status : You can specify whether to activate or deactivate the trigger to be added.
  • Trigger Priority : You can set the priority of the trigger. The priority value is a FLOAT and can be between 00.00 and 9999.99.



6.5. Serial


When you login to the database, you can view accessible serials in the navigation tree.

Right-click Serials in the navigation tree and then select Create Serial.

The name of the serial must be unique in the database.



6.6. Stored Procedure


When you login to the database, you can view accessible stored procedures.

A feature that registers a function or procedure written in Java in the database server with the loadjava command and adds a database function or procedure in order to use the given Java function.

Right-click Stored procedure in the navigation tree and then select Create Function or Create Procedure.

Only Java types compatible with the selected SQL type are displayed so that the user does not make a mistake with type mapping.

For more information, see "Java Stored Function/Procedure" in the CUBRID online manual.



6.7. Automation


Backup Automation

When you login to the database, you can view Backup plan under Job automation in the navigation tree. The Add Backup Plan and Auto Backup Logs menus will appear when you right-click Backup plan.

If you want to execute a backup periodically with the CUBRID Manager, configure the values of Add Backup Plan. The DBA can configure the backup automation while the Manager server is running. It is not affected by whether the database is running or not. That is, backup automation is executed only when the Manager server is running.

  • Backup ID : Enter the name of the backup job. The backup ID must be unique in the database because multiple backup plans may exist in the same database.
  • Backup level : You can choose from 0, 1, and 2. Level0 is a full backup. Level1 is the first incremental backup that backs up changes made only after Level0 backup. Level2 is the second incremental backup that backs up changes made only after the Level1 backup.
  • Backup path : Specifies the directory of the backup volume.
  • Period type : You can select a backup period from options of Monthly, Weekly, Daily and A specific day.
  • Period detail : You can set details for the period type you selected.
  • Backup hour : Enter the time when the automated backup is to be executed. You must enter the time in hour and minute.
  • Options : You can specify an option for backup. For more information, see "Database Backup" in the CUBRID online manual.
    • Store old backup file : This option saves the current original backup volume file of the database in database_directory/backupold directory.
    • Delete archive volumes : An option that deletes archive log volumes after backup. When this option is selected while the database is set to master server in the replication environment, volumes do not affect the replication will be deleted automatically.
    • Update statistics information : Updates statistics information after backup.
    • Checking database consistency : Checks database consistency during backup.
    • Use compress : Uses compression during backup.
    • Number thread : Specifies the number of threads to be used concurrently during backup. It is recommended to configure the maximum number of threads to be the same as the number of CPUs. The default value is 0. If it is set to the default value, the number of threads is determined automatically by the system.
  • Online backup : Automated backup is executed only when the database is running. If the database stopped, only error logs are recorded without backing up the database.
  • Offline backup : Automated backup is executed only when the database stopped running. Forces the database to shut down if it is currently running, performs an automated backup, and then restarts the database.


Auto Backup Log

Provides the error logs created during backup automation.


Query Automation

When you login to the database, you can view Query plan under Job automation in the navigation tree. The Add Query Plan and Auto Query Logs menus will appear when you right-click Query plan.

If you want to execute a backup periodically with the CUBRID Manager, configure the values of Add Query Plan. The DBA can configure the backup automation while the Manager server is running. It is not affected by whether the database is running or not. That is, backup automation is executed only when the Manager server is running.

  • Query Plan ID : Enter the name of the query job. The query plan ID must be unique in the database because multiple query plans may exist in the same database.
  • User name/password : Enter the user name, which will execute the registered query automatically,  and its password. If you enter wrong information, the query is not automatically executed. Therefore, you must change the user information in the Edit Query Plan in case of password change.
  • Period type : You can select a query automation period from options of Monthly, Weekly, Daily and A specific day.
  • Period detail : You can set details for the period type you selected.
  • Query hour : Enter the time when the query is to be executed automatically. You must enter the time in hour and minute.
  • Query Statement : Enter the query statement to be executed automatically. Note that the registered query is executed automatically at the specified time, but the execution results are not recorded.
  • Check Query : Check query errors before registering the auto-executed query statement. It determines whether it is an error or not by creating query plan it does not actually execute the query. It is working like executing optimization level 514 in the CSQL Interpreter.


Auto Query Log

You can view a log about queries that are automatically executed in the Auto Query Logs. Information such as a database, user ID, query ID, query hour, and error code (success : 0, failure : -1) is recorded.



6.8. Database Space


View Database

When you login to the database, you can view Database space in the navigation tree. With this option, you can select shortcut menus, such as View Database, Set Auto Add Volume, or Add Volume.

If you double-click a sub-node of Database space in the navigation tree, the volume information appears.


Set Auto Add Volume

  • Volume purpose : The automatic addition functionality can be used for a data or an index volume.
  • Using automatic volume addition : Use the automatic volume addition functionality for the selected volume type.
  • Out of Space warning rate : A volume is added automatically when the remaining volume equals to the value set by Out of Space warning rate. For example, if this value is set to 15 % and the remaining space of the volume is 15 %, a data volume is added automatically. The minimum value is 15, and the maximum is 30.
  • Volume size : Enter the size of the volume to be added automatically.

Add Volume

  • Path : Enter the directory where the added volume is to be saved. The default value is the directory where the database volume is created.
  • Purpose : Specify the type of the volume to be added. You can select from data, generic, index and temp.
  • Volume size : Enter the size of the volume to be added. Its unit is MB.
  • Pages : When the size is entered in Volume size, the number of pages for the volume is automatically calculated and displayed.





comments powered by Disqus
Page info
viewed 424 times
translations en
Author
posted 2 years ago by
newpcraft
Contributors
updated 2 years ago by
View revisions
tagged
Share this article