Tool Properties

• Customizing DbVisualizer
  • The user preferences (XML) files
• Export Settings
• Import Settings
• General Settings
  • Appearance
    • Fonts
  • Key Bindings
  • Database Connection
  • Driver Manager
  • Permissions
    • SQL Commander Permissions
    • Table Data Editing Permissions
  • Time Zone
  • File Encoding
  • Data Formats
    • Date, Time and Timestamp formats
    • Number formats
  • Table Data
  • Transaction
  • Scripts
  • Monitor
  • Form Viewer
  • Grid
    • Copy
    • Colors
    • Binary/BLOB and CLOB Data
  • SQL Editor
    • Statement Delimiters
    • SQL Formatting
    • Auto Completion
    • Comments
    • Variables
  • SQL History
  • Proxy Settings
• Database Settings
  • Authentication
  • Delimited Identifiers
  • Qualifiers
  • Physical Connection
    • Transaction
  • SQL Statements
  • Connection Hooks
  • Objects Tree Labels
  • SQL Editor
  • Query Builder
  • Database Specific settings
    • Data Types (Oracle)
    • Data Types (DB2 and JavaDB/Derby)
    • Explain Plan (Oracle, SQL Server and DB2)
    • Explain Plan (Oracle)
    • Explain Plan (DB2)
    • Objects Tree (Oracle)

Customizing DbVisualizer

DbVisualizer is highly customizable. You can control formatting, layout and the way DbVisualizer interacts with databases. The default settings are good enough for normal use, but sometimes it is necessary to modify these properties. This chapter guides you through all the properties.

The Tool Properties window divides properties into two groups:

• General Settings
  These settings controls DbVisualizer in general, such as fonts, colors, data formats, etc.
• Database Settings
  These settings are per supported database type and defines properties that are used in database specific operations. When you set a database property in Tool Properties, it applies to all database connections defined for that database type. To set a property for one specific connection, use the Connection Properties, available in the Object Details area when you select a connection.

The user preferences (XML) files

All properties are saved in XML files. The exact location of these files is platform dependent. The location on your system is listed in the first, General category, in the Tool Properties window. These files contains, in addition to all properties, also the information about drivers, database connections, bookmarks, etc. We recommend that you do not edit these files manually; even though it is quite easy to do so, even a simple typo of an element name may cause problems. It's safer to edit all properties from the DbVisualizer GUI.

DbVisualizer automatically creates a backup copy of the XML files when the application is started. The location of these files is the same as for the standard XML files, but a .bak suffix is appended to the filename. The standard XML file might get broken for various reasons not in control by DbVisualizer. If you see a warning message that the XML file can not be read when you launch DbVisualizer, simply copy the backup file to the standard location and restart the application. If you move the XML file from its standard location, or if you remove it, DbVisualizer will automatically create a new one.

Export Settings

Sometimes it may be necessary to migrate all your settings for DbVisualizer and import them in second setup of DbVisualizer. This is very handy if you are migrating from one machine to another,  or if you want to setup an exact copy on your home computer, etc. Another key reason is for backup purposes. Loosing all database connection due to various reasons can be really frustrating. The Export Settings feature is available from the File->Export Settings main window menu choice.

Figure: Export User Settings window

The default layout of the Export Settings window is that all settings will be exported. Once you're done press OK and all settings will be saved in the specified file. The structure of this JAR file is the same as the content in your DbVisualizer settings directory.

The Relative File Paths option will transform all path definitions in the exported file relative to the DbVisualizer installation directory and your personal settings directory. This is useful if you will import the settings on another machine or share it with other users. Note that the DbVisualizer version importing relative file paths must be 7.1 or later to work properly (importing in earlier versions than 7.1 will not fail but path information will be erroneous for things such as drivers, favorites, etc.)

Import Settings

The Import Settings feature is used to import settings as previously exported via the Export Settings feature. Import will examine the content of the specified file and present the choices available. Consider the previous screenshot and that we export the settings for the Database Connections only. Here is how the Import Settings window will look:

Figure: Import User Settings window

Use the Target Location button to set where the imported database connections will appear in the objects tree.

General Settings

The General settings tab collects all categories that are used to control the general aspects of DbVisualizer.

Use the buttons at the bottom of the window when you have made some changes: Click OK to save the changes and close the window, the Apply button to save the changes but keep the window open, and the Cancel button to revert all changes. To reset the properties to the factory defaults, use the Defaults button.

Changes are tracked on a per category basis. If you have made changes and click on another category, you are asked whether the changes should be applied or not. When you click Defaults (for both the General and the Database properties), you can reset either all properties or just the properties for currently selected category.

This is a screenshot of the General category tree.

Figure: The Tool Properties window showing the tree with General categories

Appearance

Fonts

Individual fonts can be defined for SQL Editors, Grids and Text output data. The Application Font settings is used to control the font for all other components in the user interface, such as labels. Increasing the application font size is useful at demos or presentations. Anti-Aliased Fonts is supported by some look and feels and when enabled it gives a much smoother appearance of text in the application. Anti-Aliased font is not supported by the SQL editor.

Key Bindings

You can define key bindings for almost all operations and editor commands in DbVisualizer. Key bindings are grouped in Key Maps. DbVisualizer includes a set of predefined key maps targeted for the supported operating systems. These key maps cannot be deleted or modified. To customize key bindings, copy an existing key map and make your changes.

Figure: The key binding editor

All user defined key maps are stored in your $HOME/.dbvis/config70/keymaps directory. A key map file contain only the differences between the copied key map and the current.

To create a new key map, select the map you want to copy and click the Make Copy button. Set a name on the new key map and activate it with the Set Active button. The newly created key map now has the exact same key bindings as the parent key map.

Key maps must be uniquely named.

Figure: User defined key map

The action list is organized in folders. The Editor Commands folder lists all actions available in the SQL Commander editor and their current key bindings. The Main Menu folder contain sub folders, each representing a main window menu. The other folders group feature specific actions, such as actions to control the references graph, form editor, etc.

To modify the key bindings for an action, select the action from the action list. The current key bindings are listed in the Key Bindings list.

Figure: User defined key map

To add an additional key binding, press Add Key Binding or press Edit Key Binding to edit the selection.

Figure: Key stroke dialog

The key stroke dialog controls whether a key binding is already assigned somewhere else. If there is a conflict with another binding, the Conflicts are shows the names of the actions that are conflicting. The modifier keys Shift, Alt, Ctrl and Command can be used to form the final key binding.

Menu items and tooltips shows the first defined key binding in the list.

Database Connection

Driver Manager

The Driver Manager searches specified folders for JDBC drivers and helps you make them available for use by DbVisualizer, see the Load JDBC Driver and Get Connected section for details. In the Driver Manager properties category, you can specify if you want the Driver Manager to run automatically at start-up, when new files are discovered in the specified driver folders, or when driver related errors are encountered. You can also specify the folders to search and files to exclude, if any.

Permissions

The Permission functionality is a security mechanism, where you can specify that certain database operations must be confirmed. You configure permissions per connection mode (Development, Test and Production) for feature areas described in the following sections.

SQL Commander Permissions

For the SQL Commander, you can pick the permission type from a drop-down list for each SQL command:

Figure: SQL Commander Permissions

Table Data Editing Permissions

The permission types for the inline editor are:

Figure: Table Data Editor Permissions

Time Zone

In the Time Zone properties category, you can change the time zone for the DbVisualizer process, and thereby how date and time data is interpreted. DbVisualizer uses the OS time zone by default, which is usually what you want to use.

Changing the time zone is only of interest if you work with a database running with a different time zone than the time zone set on the client where you run DbVisualizer. One example is when working with a database that uses the UTC/GMT time zone to normalize all date/time data.

File Encoding

In the File Encoding category, you can set which file encoding DbVisualizer uses by default when reading and writing files, e.g., SQL scripts loaded into the SQL Commander or files with exported data. By default, DbVisualizer uses the default encoding for your operating system, and this is typically what you want. You only need to change this setting if you often work with files in another encoding, or if DbVisualizer can not find the default encoding for your operating system.

Data Formats

Date, Time and Timestamp formats

The lists for date, time and timestamp format contain collections of standard formats. If these formats are not suitable, you can enter your own format in the appropriate field. The tokens used to define the format are listed in the right-click menu when the field has focus.

Figure: The date and time right click menu

The complete documentation for these tokens is available at the following web page: SimpleDateFormat.

Number formats

The lists for number and decimal number contain collections of standard formats. If these formats are not suitable, you can enter your own format in the appropriate field. The tokens used to define the format are listed in the right-click menu when the field has focus, and complete documentation for these tokens is available at the following web page: DecimalFormat.

Table Data

Transaction

Scripts

Monitor

Form Viewer

Grid

Copy

The Copy category groups properties that control the result of using Copy Selection and Copy Selection (With Column Header) via the grid right-click menu, the corresponding key bindings, and drag and drop.

Colors

The Colors category is used to define alternatnve background, foreground and grid colors for grid components.

Binary/BLOB and CLOB Data

SQL Editor

The editor category controls various settings specific for the SQL Commander editor.

Statement Delimiters

Statement delimiters define how a script should be divided into specific SQL statements in the pre-processing phase.

SQL Formatting

The SQL formatting category groups properties to control the SQL formatting feature in the SQL Commander. To see the effect of each property, modify it, press Apply and format the SQL in the SQL Commander.

Auto Completion

These category is used to define the visual appearance of the auto completion popup in SQL Editors.

Comments

Variables

Variables can be used in the SQL executed in the SQL Commander. Before executing an SQL statement or connecting a database connection, a dialog is displayed, asking for replacement values.

These settings define a character sequence that identifies a variable and another sequence that delimits different parts of a variable.
Example: ${variable}$.

SQL History

Settings used to control the SQL history feature.

Proxy Settings

The Check for Updates feature requires HTTP access to the internet. If you access the internet through a proxy, you must specify the proxy settings in order to use this feature.

Database Settings

Database settings extends the General settings with properties that may have different values per supported database type. You specify the database type for a connection by choosing the appropriate type from the Database Type list in the Connection tab. If there is no matching entry, use the Generic database type.

The database type specific properties in the Tool Properties apply to all connections of the specific database type. You can also override these properties in the Connection Properties tab for a specific connection, in case you need to use different values for connections of the same database type.

Figure: The Tool Properties window showing the tree with Database categories

The following properties are displayed when selecting a database type in the tree.

Authentication

Delimited Identifiers

Delimited identifiers are identifiers which do not need to follow the rules of regular database object identifiers. Usually, delimited identifiers are used when you need to use SQL reserved words, spaces and mixed case sequences in an identifier.

Qualifiers

These properties control whether table and column names should be qualified when DbVisualizer generates SQL statement.

Physical Connection

The Physical Connection category controls whether DbVisualizer should use only one physical connection with the database server or if physical connections will be acquired when needed. The Use Single Shared Physical Database Connection is disabled by default. If enabled then briefly it means that whenever establishing a connection DbVisualizer will assign one physical database connection for the objects tree and one per every SQL editor in the SQL Commander. The physical connection for a SQL editor is not acquired directly when the editor is created but rather when doing the first execute in it.

If enabling Use Single Shared Physical Database Connection then only one physical connection will be used for that database. DbVisualizer will then share the physical connection among all features communicating with the database. If using a single physical connection and auto commit is off then a confirmation dialog may appear when launching features that require transaction control and if there are uncommitted changes in the database.

Transaction

SQL Statements

This category controls the SQL templates that DbVisualizer uses internally throughout the application. Each SQL template is composed of the standard SQL and variables. Variables are identified with ${...}$. DbVisualizer relies on a number of predefined variables, listed in the SQL Templates area right-click menu:

Figure: All predefined variables

A specific predefined variable can be used in one or more of the SQL templates. Using a variable that is not valid for a specific SQL statement will result in the variable appearing as-is when the statement is executed.

There is normally no reason to modify the SQL templates, nor the variable identifier or delimiter settings. There might however be circumstances when edits are needed, for instance to modify the appearance of the where clause or the list of columns.

Connection Hooks

Connection hooks defines optional SQL commands that are sent to the database at connect and just before disconnect. They are typically used to initialize the database session with custom settings and to clean up various resources at disconnect.

Objects Tree Labels

The label for a data node (e.g., a table or view node, as opposed to a node that just groups nodes, such as the Tables node) is typically the name of the database object the node represents, e.g., the table or view name. In some cases, you may want to extend the label to include other information, such as the name of the schema that the object belongs to. To do this, you can use a custom tree label, defined in the Objects Tree properties category.

You need two pieces of information to define a custom label: the Object Type name for the data node, and the names of the variables that hold the information you want to use in the label. You find this information in the <ObjectsTreeDef> element in the database profile XML file (described in detail in the Database Profile Framework section) for the database type you want to modify. Using the database profile for the JavaDB/Derby database type as an example, a stripped down version of the <ObjectsTreeDef> element looks like this:

  <ObjectsTreeDef id="derby">

      <GroupNode type="Schemas" label="Schemas">
         <DataNode type="Schema" label="${derby.getSchemas.Schema}">
            <SetVar name="schema" value="${derby.getSchemas.Schema}"/>
            <SetVar name="schemaId" value="${derby.getSchemas.Schema Id}"/>
            [...]

            <GroupNode type="Tables" label="Tables">
               <DataNode type="Table" label="${derby.getTables.Table Name}" isLeaf="true">
                  <SetVar name="objectname" value="${derby.getTables.Table Name}"/>
                  <SetVar name="rowcount" value="true"/>
                  <SetVar name="acceptInQB" value="true"/>
                  [...]
               </DataNode>
            </GroupNode>
            [...]
         </DataNode>
         [...]
      </GroupNode>
      [...]
  </ObjectsTreeDef>

In this example, there is one <DataNode> element with a type attribute set to Schema, with a nested <DataNode> element with a type attribute set to Table. These two elements represent data nodes, for the schema and table node, respectively, and the type attribute value is the Object Type name you need to bind the custom label to an object type.

Each <DataNode> element also has a number of nested <SetVar> elements, declaring the variables you can use in the custom label value. All variables declared for the object type node and those declared for a parent <DataNode> element can be used in the label. So, if you want the label for table nodes in the tree to show both the schema name and the table name, you add a custom label declaration like this:

Figure: Custom label declaration

SQL Editor

Query Builder

Database Specific settings

DbVisualizer provides more support for some databases than for others, and so requires extended configuration capabilities for these databases.

Data Types (Oracle)

With Oracle, the DATE data type should sometimes be handled as TIMESTAMP. Enable Handle DATE as TIMESTAMP and DbVisualizer will convert DATE into TIMESTAMP objects.

Data Types (DB2 and JavaDB/Derby)

DB2 and JavaDB/Derby supports a data type named CHAR FOR BIT DATA. If you want to see values of this type as text, enable this property.

Explain Plan (Oracle, SQL Server and DB2)

The explain plan feature supported for Oracle, SQL Server and DB2 can be configured to highlight certain threshold levels.

Explain Plan (Oracle)

The explain plan feature for Oracle can be configured to define the management of the underlying plan table in which the explain plan result is stored.

Explain Plan (DB2)

The explain plan feature for DB2 can be configured to define the management of the underlying plan tables in which the explain plan result is stored.

Objects Tree (Oracle)

---