Tool Properties

 DbVisualizer Logo
DbVisualizer 6.0
July 2007 		http://www.dbvis.com
support@dbvis.com

[ Master documentation index ]

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:

The user preferences (XML) file

All properties are saved in an XML file. The exact location of this file is platform dependent. The location on your system is listed in the first, General category, in the Tool Properties window. The XML file contains, in addition to all properties, also the information about drivers, database connections, bookmarks, etc. We recommend that you do not edit this file 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 file when the application is started. The location of this file is the same as for the standard XML file, but a .bak suffix is appended to the filename. The standard XML file might get broken for various reasons. If you see a a warning message that the XML file can not be read when you launch of 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.

Tip: the -up command line argument is used to identify the file name (and path) to an alternate XML file.

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

Property
 Description
Look and Feel
Controls which look and feel to use.
Note 1: You must restart DbVisualizer after you have selected a new look and feel.
Note 2: Some look and feels are platform specific and do not appear on all OS'es

Metal (Ocean)

Windows

Alloy

GTK+

Mac OS X
Icon Sizes
 The Menus, Main Tool Bars, Sub Tool Bars settings are used to control the size of the icons.
Show Tab Icons Specifies whether an icon will appear in the header of all object view tabs.

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/config/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.

Note: 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.

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

Database Connection

Property
 Description
Ask When Creating Database Connection If enabled, you will be asked if you want to use the Database Connection Wizard to create new connections.
Run "Connect All" at Startup If enabled, the Connect All operation is automatically run when you launch DbVisualizer, connecting all Database Connections marked as being included in the Connect All operation (see the Database properties further down for more on this).
Confirm "Disconnect All"
 If enabled, a dialog to be displayed before disconnecting all current database connections when using the Disconnect All operation.

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.

Note: The permission feature is part of DbVisualizer and does not replace the authorization system in the actual database.

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

Inline Editor Permissions

The permission types for the inline editor are:

Figure: Inline 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.

Data Formats

Property
 Description
Date Format
 Specifies the date format to use throughout the application (i.e., in grids, forms and during editing). More information below.
Time Format
 Specifies the time format to use throughout the application (i.e., in grids, forms and during editing). More information below.
Timestamp Format
 Specifies the timestamp format to use throughout the application (i.e., in grids, forms and during editing). More information below.
Numbers Format
 Specifies how numbers will be formatted.
Decimal Number Format Specifies how decimal numbers will be formatted.
Null String
 Specifies the string representation of the null value. This string is the readable form of null and appears in grids, forms, exports and during editing.

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

Property
 Description
Show Table Row Count
 Specifies if the number of rows in a table will be displayed in the header of the table in the Database Objects->Data tab. Enabling this property will cause an extra round trip to the database (i.e., a minor performance penalty)
Highlight Primary Key Columns
 Specifies if Primary Key columns will be indicated in the Database Objects->Data tab, Variable Substitution dialog, SQL Commander Result grids and in the References Graph.
Include Variables in SQL
 Specifies if the right-click menu operations in the Data tab will create SQL statements that include DbVisualizer variables or if the generated statements are plain SQL. Letting DbVisualizer generate statements with variables results in the variable substitution dialog being displayed when these statements are executed in the SQL Commander.
Max Rows at First Display
 Set the number of rows that will be fetched for a table in the Data tab when a table is first displayed.

Variables

Variables can be used in the SQL executed in the SQL Commander and in Connection details. 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.

Property
 Description
Variable Identifier
 The identifier for a variable. A variable starts and ends with this identifier. Default is "$$".
Variable Delimiter
 The delimiter used to identify the parts of a variable. Default is "||".

Transaction

Property
 Description
Pending Transactions at Disconnect
 Specifies what DbVisualizer does on exit from the application, when the auto commit setting is disabled.

Bookmarks

Property
 Description
Number of Bookmarks Limit
 Specifies the number of SQL bookmarks that the New and History bookmark objects may keep before the lists are truncated.

Monitor

Property
 Description
Start Monitors Automatically
 Check to enable start of monitors automatically when database connections are established.

Form Viewer

Property
 Description
Right Aligned Numbers
 If enabled, numbers are displayed as right-aligned in the Form Editor/Viewer.

Grid

Property
 Description
Auto Resize Column Widths
 If Auto Resize is enabled, DbVisualizer automatically sizes each grid column based on the widest cell value.  If Consider Column Header is also enabled, the header widths are also considered when calculating the column widths.
Show Grid Row Header
 If enabled,  a row header is shown also for read-only result set grids, such as monitoring result set grids.
Max Column Separator Width This setting is used only when Auto Resize Column Widths is enabled and specifies a maximum visual column width for grids. 
Image Thumbnail Size
 Specifies the size (in pixels) for the widest side of a thumbnail shown for image data in the Data Form Viewer.
Meaning of setting Max Chars
 The Max Chars property in the Database Objects Data tab and in the SQL Commander is used to control the max number of  characters for text values. If the number of characters for a text column is more than this setting, the column is colored in a light red color and the value is truncated as specified by this property:
  • Truncate Values
    Truncate the original value to be less then the setting of Max Chars.
    Note: this affects any subsequent edits and SQL operations that use the value since it's truncated. This setting is only useful to save memory when viewing very large text columns.
  • Truncate Values Visually
    Truncate the visible value only and leave the original value intact. This is the preferred setting since it will not harm the original value. The disadvantage is that more memory is needed when dealing with large text columns.

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.

Property
 Description
Column Delimiter
 Specifies the delimiter between columns in a multi column copy
End of Line Delimiter
 Specifies the new line control characters for multi row copy requests

Colors

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

Binary/BLOB and CLOB Data

Property
 Description
BLOB
 Specifies how BLOB and binary data values are represented in grids. Setting this property to By Value results in performance penalties and the memory consumption increases dramatically.
CLOB
 Specifies how BLOB and binary data values are represented in grids. Setting this property to By Value results in performance penalties and the memory consumption increases dramatically.

SQL Editor

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

Property
 Description
Tabs Specifies settings for the tab keyboard key: Tab Size (the number of characters a tab character corresponds to), Whitespace(s) per Tab (by how many characters to indent when the tab key is pressed), and Expand Tab to Whitespace (if enabled, always insert space characters when the tab key is pressed).

If Expand Tab to Whitespace is disabled, a tab character is inserted when the tab key has been clicked as many times as it takes to indent to the value specified by Tab Size, i.e., if Whitespace(s) per Tab is set to 4 and Tab Size is set to 8, clicking the tab key twice results in a tab character.
Recent Files Limit
 Specifies the max number of files listed in the File->Load Recent sub menu.
Confirm Close of Unsaved Editors If enabled, DbVisualizer asks you whether to save the text in an SQL editor with modified content (any editor; not only editors loaded from file) when you close the editor.
Set "Sticky" for SQL Editor(s) If enabled, the Sticky flag is automatically set for all new SQL Editors, which means that the database connection details only can be changed manually.

Statement Delimiters

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

Property
 Description
SQL Statement Delimiter 1
 Defines the character(s) used to delimit one SQL statement from another in a SQL script
SQL Statement Delimiter 2
 Defines the additional character(s) used to delimit one SQL statement from another in a SQL script. If there is no need for more then one SQL statement delimiter, set this one to the same as delimiter 1.
Allow "go" as Delimiter
 Specifies whether go as the first word on a single line should be interpreted as a statement delimiter.
Begin Identifier
Defines the character(s) that identifies the start of an anonymous SQL block
End Identifier
 Defines the character(s) that identifies the end of an anonymous SQL block

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.

Property
 Description
Sort Tables List
 Enable this to always present tables sorted in the auto completion popup
Sort Columns List Enable this to always present column names sorted in the auto completion popup
Display Automatically Enable this and the auto completion popup is automatically displayed whenever possible
Instant Substitution Enable this and the auto completion feature substitutes directly if there is only
one matching entry
Display Delay Specifies the time in milliseconds until the auto completion popup is displayed automatically

Comments

Property
 Description
Single Line Identifier 1
 Specifies the character(s) that identifies the beginning of a one line comment
Single Line Identifier 2
 Specifies the additional character(s) that identifies the beginning of a one line comment
Block Comment Begin Identifier
 Specifies the character(s) that identifies the start of a multi line comment block
End
 Specifies the character(s) that identifies the end of a multi line comment block

Debug

The Debug category holds properties that control the amount of output that is produced when setting various debug modes. Normally only error messages are displayed in the default debug destination, which is the Tools->Debug Window. The support team often refer to the debug properties when we want more information in a problem situation.

Property
 Description
Debug Output Destination
 Specifies the destination for all debug messages. It is not advisable to set this to Off, since this means that also error messages will be ignored. Standard Out is only useful if the debug mode of the DbVisualizer launcher is enabled.
Debug DbVisualizer
 Defines the amount of logging that will be produced, where the Debug setting results in the most amount of output and Error in the least amount. Setting Detail Level to Full produces the most detail, but it also consumes more resources.
Debug JDBC Drivers This property enables any debug output produced by a JDBC driver to be captured. The amount of output depends on the actual drivers.

Read more about using the debug output in the  Problem Resolution section.

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.

Property
 Description
Connection Mode
 Specifies the connection mode for the database connection: Development, Test or Production. Permissions are based on connection mode. For the Test and Production modes, DbVisualizer displays a border around areas where database content can be edited, to bring your attention to the fact that you are connected to a database where others may be affected by your changes.
Show only default Database or Schema
 Enable this if you only want the default database or schema listed in the database objects tree.
Connect when "Connect All" The Connect All feature allows you to connect to multiple database connections with a single click. Enable this property to include database connections of this type when using the Connect All feature.

Authentication

Property
 Description
Save Password
 If enabled, DbVisualizer saves the password for the database connection between invocations. (The password is saved encrypted)
Clear Password at Disconnect
 If enabled, the password is cleared at disconnect
Require Userid If enabled, you are asked to enter a userid whenever the database connection is established
Require Password If enabled, you are asked to enter a password whenever the database connection is established

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.

Property
 Description
Begin Identifier
 Defines the start character for a delimited identifier. Normally, this is a double quote (")
End Identifier
 Defines the end character for a delimited identifier. Normally, this is a double quote (")
Scripting Enable this to use delimited identifiers in the Scripting features
Auto Completion/Query Builder Enable this to use delimited identifiers in the auto completion and query builder features

Qualifiers

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

Property
 Description
Qualify with Schema/Database: Scripting
 Enable this to qualify object names with the schema/database in the Scripting features
Qualify with Schema/Database: Auto Completion/Query Builder Enable this to qualify object names with the schema/database in the auto completion and query builder features.
Qualify Columns: Auto Completion/Query Builder
 Enable this to qualify column names with the table name in the auto completion and query builder features.
Note: When you specify a table name alias, it is always used as a column name qualifier, regardless of this property setting.

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

Property
 Description
Auto Commit
 Defines if each executed SQL statement will be auto committed or not. This setting applies for all SQL statements that are executed in the SQL Commander. 
Ask when Auto Commit is Off If auto commit is off then this setting when enabled will show a confirmation dialog if there are uncommitted changes produced by the last execution in the SQL Commander.
Transaction Isolation
 Attempts to change the transaction isolation level for all database connections.
Note: If this property is changed during a transaction, the result is JDBC driver specific.
Commit Batch Size Specifies after how many rows DbVisualizer commits the transaction when saving a batch of changes in the table data editor.

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 in a SQL statement that is not valid 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:
Property
 Name
 Description
SQL Templates
 SELECT ALL
 Command used when selecting all rows for a table
SELECT ALL WHERE
 Command used when selecting some rows for a table
SELECT COUNT
 Command used to get the number of rows in a table
INSERT INTO
 Command used to insert a new row into a table
UPDATE WHERE
 Command used to update an existing row in a table
DELETE WHERE
 Command used to delete a specific row in a table
DROP TABLE
 Command used to drop a specific table
CREATE TABLE
 Command used to create a new table with an  optional primary key
CREATE INDEX
 Command used to create an index for a specific table
Monitor Row Count
 Command used to get the number of rows in a table and the current time stamp
Monitor Row Count Change
 Command used to get the row count difference in a table compared to the previous execution. The calculated row count and the current time stamp is returned

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.

Property
 Description
Run SQL at Connect
 Defines the SQL to be executed just after the connection has been established
Run SQL at Disconnect
 Defines the SQL to be executed just before the connection will be disconnected

Objects Tree

Property
 Description
Custom Object Tree Labels
 Here you can define custom tree labels for the data nodes in the database objects tree. The Object Type must match the corresponding type in the actual database profile, see more below.

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 Plug-in 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

Property
 Description
Remove New Line Characters
 Specifies whether any new line characters should be removed from any SQL statement executed in the SQL Commander, and in the implicit SQL execution functionality in DbVisualizer. Some drivers/databases such as DB2 require that no new line characters are part of any executed SQL.
Set Current Schema If enabled, changing the schema in the SQL Commander also changes the default schema for the database connection, so that unqualified table names in any SQL statement are associated with the selected schema. If this property is not enabled, changing the schema only affects the schemas used for auto-completion.

Note: Only a few databases supports setting the default schema for an opened connection. This property is only shown for database types that support it.

Query Builder

Property
 Description
Query Builder Auto-Join Properties
 With auto-join enabled, the Query Builder automatically joins tables as they are included in the query, based on the specified column matching rule: FK/PK declarations or columns with matching names in different tables.
Generate JOIN clauses in Query Builder Specifies whether the Query Builder generates joins as JOIN clauses or WHERE conditions.

JOIN clause:

SELECT     *
FROM       HR.EMPLOYEES emp
INNER JOIN HR.DEPARTMENTS dept
ON         (emp.DEPARTMENT_ID = dept.DEPARTMENT_ID)

WHERE condition:

SELECT *
FROM   HR.EMPLOYEES emp,
       HR.DEPARTMENTS dept
WHERE  (emp.DEPARTMENT_ID = dept.DEPARTMENT_ID)

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 automatically treat DATE as TIMESTAMP. 

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.

Property
 Description
Color Critical Nodes
 If enabled, critical nodes in the explain plan are highlighted.
Critical Threshold Specifies the threshold for when a node should be handled as critical
Warning Threshold Specifies the threshold for when a node should be handled as a warning

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.

System Tables (Oracle)

Select here whether the database profile for Oracle should retrieve database information from the DBA or ALL system tables.
Note: If choosing DBA, make sure the appropriate privileges are granted for the user you are connecting as. 

Copyright © 2007 Onseven Software AB. All rights reserved.