1. DBeaver

This chapter introduces the DBeaver overview, system requirements, installation and uninstallation methods, and how to connect to the Altibase database system.

Overview

DBeaver Community is a free database tool provided by DBeaver Corp. It enables connection to over 80 different database systems, offering functionalities such as data manipulation, database object management, SQL editor, SQL Plan viewer, session monitoring for database connections, etc.

The DBeaver package provided by Altibase provides the connection to Altibase and the diverse range of functionalities mentioned earlier, in addition to other compatible database systems.

For detailed information on DBeaver, refer to DBeaver Desktop Documentation.

System Requirements

Software Requirements

DBeaver Version

DBeaver versions compatible with Altibase Server are as follows:

• DBeaver 23.3.3 and higher

Altibase Server Version

Altibase server versions compatible with the DBeaver are as follows:

• Altibase 7.1.0 and higher

Installing and Uninstalling DBeaver

Users can download the DBeaver installation file from DBeaver Download. For detailed information about installation and uninstallation, refer to Installation.

Altibase Database System Connection

1. Connect to New Database

Click [ Database ] → [ New Database Connection ]

2. Select Altibase Database

Select Altibase in ALL or SQL tab, and click the [ Next > ].

3. Set the Altibase Connection Information

Configure Altibase connection settings. Host IP address, Port Number, Database/Schema, Username, and Password are required. Click the [ Finish ] after configuration.

Now users can find the Altibase database set above on the Database Navigator tab as follows:

4. Download JDBC Driver Files

To connect the Altibase database, Altibase JDBC driver file is required. If users can access the internet, please refer to '4.1 Download JDBC Driver Files'. If users can not access the internet or want to register JDBC driver files manually, please refer to '4.2 Register JDBC Driver Manually'.

4.1 Download JDBC Driver Files

Double click the Altibase connection information(mydb) on the Database Navigator tab. Then the 'Driver settings' window pops up to download Altibase JDBC driver files. Select a Altibase JDBC driver file and click [ Download ].

The database connection is processed after downloading. This only needs to be done the first connection.

4.2 Register JDBC Driver Manually

Users can manually register Altibase JDBC driver files. To do this, click on the [ Driver Settings ] located at the bottom of the 'Connect to a database' window.

Click [ Add File ] on the Libraries tab.

Select the Altibase JDBC driver file.

Click [ Classpath ].

Add the location of the JDBC driver file to be used in Global libraries and click [ Apply and Close ].

After all settings, click [ OK ] .

Click [ Finish ] to complete the configuration.

5. Complete Database Connection

Users can review the list of registered database connection information on the Database Navigator tab.

Double-clicking on the database connection information in the list attempts to establish a connection. A green check mark next to the icon signifies a successful connection has been established.

If needed, users can rename the database connection information to the desired name.

FAQ

Unable to Retrieve LOB Type Data.

DBeaver is configured with Auto-Commit as the default setting. To retrieve Altibase LOB type data, users need to change the commit mode from Auto-Commit to Manual Commit.

There are three methods to change the commit mode:

1. Click on the ▼ in the toolbar to switch to Manual Commit mode.

   

   Button Icon	Commit Mode
   	Auto-Commit
   	Manual Commit

2. Select "Manual Commit" from the list of commit modes.

   

3. Deselect the "Auto-commit" default setting option in the menu and reconnect to the database.

   [ Window ] → [ Preferences ]

   

   Click [ Connections ] → [ Connection Types ]

   Uncheck the "Auto-commit by default" check box, then click [ Apply and Close].

   Reconnect to the database.

   

Note

In Manual Commit mode, users must explicitly commit or roll back when modifying tables.

Reference: DBeaver Documentation-Auto and manual commit modes

The SYSTEM_ schema is not visible.

DBeaver, by default, does not display system objects. To view the SYSTEM_ schema, users need to set the "Show system objects" option.

Click on [ Connection View ] → [ Show system objects ] option.

After reconnecting to the database, users will be able to see the tables and views in the SYSTEM_ schema.

The DDL output for the object is not accurate.

To retrieve the accurate DDL statements of Altibase objects, it is essential to install the DBMS_METADATA package by SYS user.

To install the DBMS_METADATA package, connect to Altibase using the SYS account and execute the following statement:

$ cd $ALTIBASE_HOME
$ is -f ./packages/dbms_metadata.sql
$ is -f ./packages/dbms_metadata.plb

Example)

The following is an example syntax for creating the queue object Q1.

CREATE QUEUE q1(40) MAXROWS 100000;

After executing the above statement and attempting to retrieve the DDL for the Q1 object in DBeaver, the wrong output may be displayed as follows.

Install the DBMS_METADATA package:

$ cd $ALTIBASE_HOME
$ is -f ./packages/dbms_metadata.sql
$ is -f ./packages/dbms_metadata.plb

After installation, reconnect to the target Altibase in DBeaver. Users can retrieve accurate DDL statements for the Q1.

Where can I find the query execution plan for my queries?

The query execution plan can be viewed by entering a query in the SQL script window and then clicking on the "Explain Execution Plan" icon.

Click on [ SQL Editor ] of the connected database → [ New SQL script ]

In the SQL Script window, users can enter a query and then click on the "Explain Execution Plan" icon to view the query execution plan.

The default option value for the query execution plan is "Explain Plan Only."

I want to execute the query execution plan with EXPLAIN_PLAN = ON.

In the DBeaver Altibase Plugin, users can specify whether to use the Explain plan ONLY or ON for each connection. The default value is ONLY.

There is two methods to change the Explain plan setting:

1. Change the connected database configuration.

   Click on the connected database.

   

   Navigate to [ File ] → [ Properties ]

   

   Click on [ Altibase settings ] → Check "Datasource settings" → Click on "Explain Plan = ON"

   

2. Call Preferences from the script panel.

   Click the Preferences button beside the SQL script panel.

   

   Click on [ Altibase settings ] → Check "Datasource settings" → Click on "Explain Plan = ON"

   

I want to see the server output (PSM PRINTLN output).

To view the server output, users need to enable the "Enable DBMS Output" option for the connected database.

Click on the connected database.

Navigate to [ File ] → [ Properties ]

Click on [ Altibase settings ] → Check "Datasource settings" → Check the "Enable DBMS Output"

In the SQL script window of the database connection for which users changed the settings, click the "Show server output" button. After that, when users execute the SQL statement, the server output will be displayed in the Output tab.

Microseconds in DATE type are not visible.

DBeaver's timestamp output format is yyyy-MM-dd HH:mm:ss.SSS, displaying milliseconds (3 decimal places) in contrast to Altibase's DATE type, which can represent microseconds (6 decimal places).

The query results for DATE type data under the default settings of DBeaver are as follows.

To display the DATE type in microseconds, users need to change DBeaver's data type settings. The location for changing the settings is as follows.

Navigate to [ Windows ] → [ Preference ]

Navigate to [ Editors ] → [ Data Editor ] → [ Data Formats ] → Click on "Datasource settings"

Select the database connection and click [ Select ].

There are two methods to change the settings to view DATE type up to microseconds.

1. Change the data type Value Settings:

   Navigate to [ Datasource settings ]→ [ Format ], set the Type to Timestamp, change the Value to yyyy-MM-dd HH:mm.ss.SSSSSS, and then click [ Apply and Close ].

   

   When querying DATE type data, it displays a fixed six digits after the decimal point, including zero, as shown below.

   

2. Set the Data formats "Disable date/time formatting" option

   Click on [ Datasource settings ] → [ Settings ], check the "Disable date/time formatting" checkbox, then click on [ Apply and Close ].

   

   When querying DATE type data, it displays valid fixed six digits after the decimal point as shown below.

   

Reference: DBeaver Documentation-Managing data formats

"Invalid data type length" error message is displayed when modifying CHAR type.

DBeaver Data Editor defaults to executing INSERT statements when modifying data. Before saving the changes made with the Data Editor, ensure that the modified data does not exceed the column size.

I want to see Binary Data type as Hex.

DBeaver's default setting displays binary data as a String type. To view binary data as Hex, users need to change the setting.

Click on database connection.

Navigate to [ File ] → [ Properties ]

Navigate to [ Editors ] → [ Data Editor ] → [ Binary data ]

Change the Binary data formatter setting to Hex, then click on [ Apply and Close ].

Press the [ Refresh ] button at the bottom of the table, then the binary type data will be displayed in Hex format.

Reference: DBeaver Documentation-Data view and format

Unable to Modify Binary Type Data in DBeaver Data Editor

To modify binary type data in DBeaver, users need to enter the data to be modified in the "Value" field of the Data Editor. Data directly entered into the cell of the grid is converted to the binary value and stored.

How does DBeaver handle BIT, VARBIT, and NIBBLE of Binary Data Types?

In DBeaver, binary data is processed on a byte-unit basis. For binary data types such as BIT, VARBIT, and NIBBLE, which can be smaller than a byte, DBeaver handles them as numeric or character types inevitably.

How to completely delete DBeaver Settings and reinstall?

If users want to completely reset previous DBeaver settings and reinstall it, users need to completely remove the DBeaver application and user data before reinstalling.

Check the default DBeaver workspace folder location based on the operating system and remove all DBeaver data:

OS	Default location of DBeaver workspace
Windows	%APPDATE$\DBeaverData
MacOS	~/Library/DBeaverData/
Linux	$XDG_DATA_HOME/DBeaverData/ ($XDG_DATA_HOME=~/.local/share if not set)

Reference: DBeaver Documentation-Workspace location

In SQL Editor, I would like to set Auto commit off as default.

"Auto-commit" setting of DBeaver is on as default.

If users want to set "Auto commit" to off as default, please follow the below steps:

1. Right-click on the connection → [ Edit Connection ] or press the F4 to open the Configuration window.

   

2. Navigate to [ Connection settings ] → [ Initialization ] → [ Connection ] → Uncheck "Auto-commit"

   

If users want to temporarily change the auto-commit mode, press the transaction button, as shown in the capture below.