26. Advanced configuration options

26.1. Database Identifier
26.2. DBID
26.3. GUI related settings
26.4. Editor related settings
26.5. DbExplorer Settings
26.6. General configuration settings
26.7. Database related settings
26.8. DBMS specific settings
26.9. SQL Execution related settings
26.10. Default settings for Export/Import
26.11. Controlling the log file
26.12. Configure Log4J logging
26.13. Settings related to SQL statement generation
26.14. Customize table source retrieval
26.15. Filter settings

This section describes the additional options for SQL Workbench/J which are not (yet) available in the options dialog.

The name of the setting refers to the entry in the file workbench.settings which is located in the configuration directory. Not all listed properties will be present in workbench.settings. In this case, simply create a new line with the property name and the value as described here. The position where you add this entry does not matter.

You can also change the values for these properties while the application is running by using the command WbSetConfig.

[Note]

Every property can also be specified on the commandline when starting SQL Workbench/J by setting a system property with that name using the -Dworkbench.property=value switch when starting the application using the java command directly.

You can edit the file using a text editor. In that case you must close the application before editing the file, otherwise your changes will be overwritten when the application is closed.

You can also change any property using the SQL Workbench/J command WbSetConfig. For most of the parameters the change will be in effect immediately. For some you will still need to restart the application or at least re-connect to the database.

26.1. Database Identifier

Some parameters are used such that a list of "Database Identifiers" is expected. The identifier that needs to be put there can be obtained by right clicking on the JDBC URL in the main window and then selecting "Connection Info" to show details about the current connection

The "Database Identifier is also reported in the logfile. After a successful connect to a database, there will be an entry in the log file similar to this:

INFO  15.08.2004 10:24:42 Connected to: [HSQL Database Engine]

If the description for a property in this chapter refers to a "Database Identifier", the text between (but not including) the square brackets has to be used.

26.2. DBID

For some settings, where the ID is part of the property's key, a "clean" version of the Database Identifer, called the DBID, is used. This DBID is displayed in the connection info dialog (right click on the connection URL in the main window, then choose "Connection Info").

The DBID is also reported in the log file:

INFO  15.08.2004 10:24:42 Using DBID=hsql_database_engine

If the description for a property in this chapter refers to the "DBID", then this value has to be used.

If the DBID is part of a property key this will be referred to as [dbid] in this chapter.

26.3. GUI related settings

Showing accelerator in menu

Property: workbench.gui.showmnemonics

Possible values: true, false

Usually the mnemonic (aka. Accelerator) for a menu item is not shown under Windows 2000 or later. It will only be shown, when you press the ALT key. With this settings, this JDK behaviour can be controlled.

Default: true

Controlling the type of print dialog

Property: workbench.print.nativepagedialog

Possible values: true, false

When printing the contents of a table, this settings controls the type of print dialog to be used. The default setting will open the native print dialog of the operating system. If you experience problems when trying to print, set this property to false. SQL Workbench/J will then open a cross-platform print dialog.

Default value: true

26.4. Editor related settings

Define the default name for new tabs

Property: workbench.gui.tabs.defaultlabel

When adding a new editor tab, the value of this property will be used to set the new tab's title.

Include Oracle public synonyms in auto-completion of tables

Property: workbench.editor.autocompletion.oracle.public_synonyms

Possible values: true, false

When using auto completion for table columns and table names, Oracle's public synonyms are not included by default. This has two reasons: first, the author believes that public synonyms shouldn't be used (it's just as bad as global variables in programming) and second, Oracle defines a huge number of public synonyms that would make the popup with all available tables very long and hard to use. Setting this property to true, will include public synonyms in the popup. Please refer to filtering synonyms for details on how to filter out unwanted synonyms from this list.

Default value: false

Set the modifier key for rectangular selections in the edior

Property: workbench.editor.rectselection.modifier

These properties control the modifier key that needs to be pressed to enable rectangular selections in the editor. Possible values are alt for setting the Alt key as the modifier, or ctrl for setting the Ctrl key as the modifier.

Default value: alt

Default file encoding

Property: workbench.file.encoding

Several internal commands use an encoding when writing external text files (e.g. WbExport). If no encoding is specified for those commands, the default platform encoding as reported by the Java runtime system is used. You can overwrite the default encoding that Java assumes by setting this property.

Default value: empty, the Java runtime default is used

Limitting size of the text put into the history

Property: workbench.sql.history.maxtextlength

When you execute a SQL statement in the editor, the current content of the editor is put into the history buffer. If you are editing large scripts, this can lead to memory problems. This property controls the max. size of the editor text that is put into the history.

If the current editor text is bigger than the size defined in this property the text is not put into the history.

Default value: 10485760 (10MB)

Controlling newlines in code snippets

Property: workbench.clipcreate.includenewline

Possible values: true, false

When creating a Java code snippet, the newlines inside the editor are preserved by putting a \n character into the String declaration. Setting this property to false, will tell SQL Workbench/J not put any \n characters into the Java string.

Default: true

Controlling the concatenation character for code snippets

Property: workbench.clipcreate.concat

When creating a Java code snippet, each line is concatenated using the standard + operator. If your programming language uses a different concatenation character (e.g. &), this can be changed with this property.

Default: +

Controlling the prefix for code snippets

Property: workbench.clipcreate.codeprefix

When creating a Java code snippet, this is prefixed with String sql = . With this property you can adjust this prefix.

Default: String sql =

26.5. DbExplorer Settings

Switching the current database in the DbExplorer

Property: workbench.dbexplorer.switchcatalog

When connected to a DBMS that supports multiple databases (catalogs) for the same connection, the DbExplorer displays a dropdown list with the available databases. Switching the selected catalog in the dropdown will trigger a switch of the current catalog/database if the DbExplorer uses its own connection. If you do not want to switch the database, but merely apply the new selection as a filter (which is always done, if the DbExplorer shares the connection with the other SQL panels) set this property to false.

Default: true

Controlling data display in the DbExplorer

Property: workbench.db.objecttype.selectable.[dbid]=value1,value2,...

The DbExplorer makes the "data" tab available based on the type of the selected object in the object list (second column). If the type returned by the JDBC driver is one of the types listed in this property, SQL Workbench/J assumes that it can issue a SELECT * FROM to retrieve data from that object.

Default values:

.defaultt=view,table,system view,system table
.postgresql=view,table,system view,system table,sequence
.rdb=view,table,system,system view

The values in this property are not case-sensitiv (TABLE is the same as table)

Customizing the SELECT to be used for the data tab

You can customize the generated SELECT that is used to display the table data depending on the column type. Please refer to the DbExplorer chapter for details.

Customizing columns that can be searched

Property: workbench.db.[dbid].datatypes.searchable

DbExplorer's "Search table data" feature only includes columns with the datatypes CHAR and VARCHAR into the WHERE clause for searching.

Some database systems allow CLOB columns to be searched using a LIKE expression as well. This property can be used to list all datatypes that can be used in a LIKE condition.

Default values:

For PostgreSQL: text
For MySQL: longtext,tinytext,mediumtext

Microsoft SQL Server extended property for remarks

Property: workbench.db.microsoft_sql_server.remarks.propertyname

Defines the name of the extended property that is queried in order to retrieve table or column remarks for SQL Server.

SQL Workbench/J will use the table function fn_listextendedproperty to retrieve the extended property defined by this configuration setting to retrieve remarks.

Default value: MS_DESCRIPTION

Retrieving remarks for Microsoft SQL Server

Property:

workbench.db.microsoft_sql_server.remarks.object.retrieve
workbench.db.microsoft_sql_server.remarks.column.retrieve

Enables/disables the retrieval of extended properties as a replacement for the standard SQL COMMENT ON ... capability.

SQL Workbench/J will use SQL Server's fn_listextendedproperty table function to retrieve table or column remarks. As this can have a performance impact on the retrieval of tables or columns, this retrieval can be disabled using this configuration setting.

The name of the extended property can be configured using workbench.db.microsoft_sql_server.remarks.propertyname

Enabling these options is also necessary in order to get comments in a WbSchemaReport output

Default value: true for both properties

Changing the isolation level for the table list retrieval

Property: workbench.db.[dbid].dbexplorer.use.read_uncommitted

To avoid blocking of the table list retrieval, the isolation level used in the DbExplorer can be switched to READ_UNCOMMITTED for DBMS that support this. This is e.g. necessary for Microsoft SQL Server as an uncommitted DDL statement from a different connection can block the SELECT statement that retrieves the table information.

The isolation level will only be changed if Separate connection per tab is enabled.

For Microsoft SQL Server the timeout waiting for such a lock can be configured as an alternative.

Default values:

For Microsoft SQL Server: true

26.6. General configuration settings

Defining a base directory for JDBC libraries

Property: workbench.libdir

A directory that contains the .jar files for the JDBC drivers. The value of this property can be referenced using %LibDir% in the driver's definition. The value for this can also be specified on the commandline.

No default

26.7. Database related settings

Include dependency information for "Object info"

Property:workbench.db.objectinfo.includedeps
workbench.db.[dbid].objectinfo.includedeps

If Object info is invoked, this setting controls if dependent objects (indexes, triggers) are also displayed for tables. This setting serves as a default for all DBMS. Displaying dependent objects can also be controlled on per DBMS by adding the DBID to the property key. The value without the DBID serves as a default setting for all DBMS.

Default: false

Include foreign key constraint information for "Object info"

Property:workbench.db.objectinfo.includefk
workbench.db.[dbid].objectinfo.includefk

If Object info is invoked, this setting controls if foreign key constraints are also displayed when dependent objects are displayed for tables. This setting serves as a default for all DBMS. When adding the DBID to the property key this is controlled on a per DBMS level.

Default: false

Automatically connect the DataPumper

Property: workbench.datapumper.autoconnect

When opening the DataPumper as a separate window it will connect to the current profile as the source connection. If you do not want the DataPumper to connect automatically set this property to false

Default: true

Controlling COMMIT for DDL statements

Property workbench.db.[dbid].ddlneedscommit

Possible values: true, false

Defines if the DBMS supports transactional DDL (CREATE TABLE, DROP TABLE, ...)

Default: false

COMMIT/ROLLBACK behaviour

Property: workbench.db.[dbid].usejdbccommit

Possible values: true, false

Some DBMS return an error when COMMIT or ROLLBACK is sent as a regular command through the JDBC interface. If the DBMS is listed here, the JDBC functions commit() or rollback() will be used instead.

Default: false

Generating constraints for SQL source

Property: workbench.db.inlineconstraints

This setting controls the generation of the CREATE TABLE source in the DbExplorer. This is a comma separated list of Database Identifiers that only support defining primary and foreign keys inside the CREATE TABLE statement.

If a DBMS is not listed here, the table constraints will be re-created using ALTER TABLE.

Default: FirstSQL/J

Case sensitivity when comparing values

Property workbench.db.[dbid].casesensitive

Possible values: true, false

The search panel of the DbExplorer highlights matching values in the result tables. When using the "Server Side Search", the highlighter needs to know whether string comparisons in the database are case sensitive in order to highlight the correct values.

Default: false

Definining SQL commands that may change the database

Property: workbench.db.updatingcommands for general SQL statements

Property: workbench.db.[dbid].updatingcommands for DBMS specific update statements

When enabling the read only or confirm update option in a connection profile, SQL Workbench/J assumes a default set of SQL commands that will change the database. With this property you can add additional keywords that should be considered as "updating commands". This is a comma separated list of keywords. The keywords may not contain whitespace.

No default

Databases supporting the check for uncommitted changes

Property: workbench.db.drivers.opentransaction.check

A list of JDBC driver classnames that map to databases that support checking for uncommitted changes. If one of these drivers is selected in a connection profile, the option Check for uncomitted changes will be visible in the connection dialog.

To make this option work, a query that counts the number of uncommitted changes needs to be configured as well.

Default: oracle.jdbc.OracleDriver,org.postgresql.Driver

Query to check for uncommitted changes

Property: workbench.db.[dbid].opentransaction.query

A query that can be used to check if the current connection has any uncommitted transactions. The query is expected to return a single row with a single numeric column. If the value is zero, no uncommitted changes are detected. Any number greater than zero means that there is an uncommitted change.

Default: For Oracle and PostgreSQL, the corresponding queries are configured

URL for online manual

Property: workbench.db.[dbid].manual

This defines the URL of the online manual for that DBMS. This URL is shown in the browser when using the menu item: HelpDBMS Manual will display the

You can append a version number after the DBID in the property key, to define different URLs for different DBMS versions. The key workbench.db.microsoft_sql_server.8.manual defines the URL for SQL Server 2000, whereas workbench.db.microsoft_sql_server.10.5.manual defines the URL for SQL Server 2008 R2. The numbers have to be majorversion.minorversion as shown in the "Connection Info" dialog

If the online manuals always have the version information at the same place of the URL, placeholders can be used, and only a single URL is necessary. For PostgreSQL, the following URL is used: workbench.db.postgresql.manual=http://www.postgresql.org/docs/{0}.{1}/static/index.html

Where {0} is replaced with the major version number and the {0} is replaced with the minor version number.

Filtering synonyms

Property: workbench.db.[dbid].exclude.synonyms

The database explorer and the auto completion can display (Oracle public) synonyms. Some of these are usually not of interest to the end user. Therefor the list of displayed synonyms can be controlled. This property defines a regular expression. Each synonym that matches this regular expression, will be excluded from the list presented in the GUI.

Default value (for Oracle): ^AQ\\$.*|^MGMT\\$.*|^GV\\$.*|^EXF\\$.*|^KU\\$_.*|^WM\\$.*|^MRV_.*|^CWM_.*|^CWM2_.*|^WK\\$_.*|^CTX_.*

Note that you need to use two backslashes in the RegeEx.

Defining keywords for date or timestamp input

Property: workbench.db.keyword.current_date

The "literals" that are accepted for DATE columns to identify the current date. Default values are current_date, today

Property: workbench.db.keyword.current_timestamp

The "literals" that are accepted for TIMESTAMP columns to identify the current date/time. Default values are current_timestamp,sysdate,systimestamp

Property: workbench.db.keyword.current_time

The "literals" that are accepted for TIME columns to identify the current time. Default values are current_time, now

Use Savepoints to guard DML statement execution

Property: workbench.db.[dbid].sql.usesavepoint

Possible values: true, false

Some DBMS (such as PostgreSQL) cannot continue inside a transaction when an error occurs. A script with multiple DML statements can therefor not run completely if one statement fails, even if you choose to ignore the error. If this property is set to true, SQL Workbench/J will set a savepoint before executing a DML statement (SELECT, INSERT. In case of an error the savepoint will be rolled back and the transaction can continue.

Default value: false

Use Savepoints to guard DDL statement execution

Property: workbench.db.[dbid].ddl.usesavepoint

Possible values: true, false

Some DBMS (such as PostgreSQL) cannot continue inside a transaction when an error occurs. A script with multiple DDL statements can therefor not run completely if one statement fails, even if you choose to ignore the error. If this property is set to true, SQL Workbench/J will set a savepoint before executing a DDL statement. In case of an error the savepoint will be rolled back and the transaction can continue.

Default value: false

Use Savepoints for update/insert mode for WbImport

Property: workbench.db.[dbid].import.usesavepoint

Possible values: true, false

Some DBMS (such as PostgreSQL) cannot continue inside a transaction when an error occurs. When running WbImport in update,insert or insert,update mode, the first of the two statements needs to be rolled back in order to be able to continue the import. If this property is set to true, SQL Workbench/J will set a savepoint before executing the first (insert or update) statement. In case of an error the savepoint will be rolledback and WbImport will try to execute the second statement.

Note that enabling savepoints can drastically reduce the performance of the import.

Default value: false

Ignore errors during data retrieval

Property: workbench.db.ignore.readerror

Possible values: true, false

When retrieving data (e.g. using a SELECT statement) errors that are reported by the driver will be displayed to the user. The retrieval will be terminated. If you want to ignore errors and replace the data that could not be retrieved with a NULL value, set this property to true.

Using this parameter is not recommended as it might produce results that do not reflect the data as it is stored in the database.

Default value: false

Check read only columns

Property: workbench.db.[dbid]resultset.columns.check.readonly

Possible values: true, false

If this property is enabled, columns in result sets will be checked whether they are marked as read only by the JDBC driver. Read-only columns will not be included in generated DML statements when editing data. If the driver incorrectly reports columns that can be changed as read-only, setting this property to false will enable editing those columns.

Default value: true

Customizing data type mapping

Property: workbench.db.[dbid].typemap

When using the -createTarget parameter for WbCopy, the type mapping from the JDBC driver might not be sufficient or correct. With this setting you can define your own type mapping for a specific dbms. The entry is a list of mappings that map the numeric value of a JDBC datatype (as defined in java.sql.Types) to a real data type name for the target DBMS. The numeric JDBC datatype value and the DBMS specific datatype name are separated with a colon. Each pair is separated by a semicolon.

The following entry maps the JDBC datatype with the value 3 (DECIMAL) to the target datatype double and the value 2 (BIGINT) to the target type NUMBER. The NUMBER datatypes needs uses two parameter placeholders $size and $digits. The last mapping maps the JDBC value -1 (LONGVARCHAR) to the DBMS type VARCHAR using only the $size parameter

workbench.db.some_dbid.typemap=3:DOUBLE;2:NUMBER($size,$digits);-1:VARCHAR($size)

JDBC 4.0 defines the following constants:

  • BIGINT = -5
  • BINARY = -2
  • BIT = -7
  • BLOB = 2004
  • BOOLEAN = 16
  • CHAR = 1
  • NCHAR = -15
  • CLOB = 2005
  • NCLOB = 2011
  • DATE = 91
  • DECIMAL = 3
  • DOUBLE = 8
  • FLOAT = 6
  • INTEGER = 4
  • LONGVARBINARY = -4
  • LONGVARCHAR = -1
  • LONGNVARCHAR = -16
  • NUMERIC = 2
  • REAL = 7
  • SMALLINT = 5
  • TIME = 92
  • TIMESTAMP = 93
  • TINYINT = -6
  • VARBINARY = -3
  • VARCHAR = 12
  • NVARCHAR = -9
  • ROWID = -8
  • SQLXML = 2009

26.8. DBMS specific settings

26.8.1. Oracle specific settings

Support for Oracle materialized views

Property: workbench.db.oracle.detectsnapshots

When displaying the list of tables in the database explorer Oracle materialized views (snapshots) are identified as tables by the Oracle JDBC driver. To identify a specific "table" as a materialized view, a second request to the database is necessary (accessing the system view ALL_MVIEWS). As this request can slow down the retrieval performance, this feature can be turned off. If for any reason the ALL_MVIEWS view cannot be accessed, this feature will be turned off until you re-connect to the database.

Default value: true

Fix type display for VARCHAR columns in Oracle

Property: workbench.db.oracle.fixcharsemantics

The Oracle driver does not report the size of VARCHAR2 columns correctly if the character semantic has been set to "char". The JDBC driver always returns the length in bytes. When this property is set to true, the length for those columns will be displayed correctly in the DbExplorer. As this means SQL Workbench/J is using it's own query to retrieve the table definition, this might not always yield the same results as the original statement from the Oracle driver. If your table definitions are not displayed correcly, set this value to false so that the original driver methods are used. The statement used by SQL Workbench/J is a bit faster then then original Oracle statement, as it does not use a LIKE predicate (which is required to comply with the JDBC specs).

Default value: true

Fix type display for NVARCHAR2 columns in Oracle

Property: workbench.db.oracle.fixnvarchartype

The Oracle driver does not report the type of NVARCHAR2 columns correctly. They are returned as Types.OTHER. If this property is enabled, than SQL Workbench/J is also using it's own SELECT statement to retrieve the table definition.

Default value: true

Include tablespace information in the generated SQL source

Property: workbench.db.oracle.retrieve_tablespace

Possible values: true, false

If this is enabled, the generated SQL source for tables and indexes will contain the corresponding TABLESPACE xxx option to reflect the way the table was created. If this option should not be included in the SQL, set this parameter to false.

Default value: true

Check for the user's default tablespace

Property: workbench.db.oracle.check_default_tablespace

Possible values: true, false

When including the tablespace for an index or table, and this option is enabled, the tablespace for tables and indexes owned by the current user is only displayed if it is different from the default tablespace. For tables and indexes owned by other users, the tablespace will still be displayed even if it's the default tablespace of the owner.

Default value: false

26.8.2. Microsoft SQL Server specific settings

Define a lock timeout for the DbExplorer

Property: workbench.db.microsoft_sql_server.dbexplorer.locktimeout

Possible values: positive integer value. (Timeout in milliseconds)

This defines timeout that limits the time the driver should wait when hitting a read lock during retrieval of the table information. The timeout will be changed by running SET LOCK_TIMEOUT ... after the DbExplorer is opened.

The timeout will only be changed if Separate connection per tab is enabled.

As an alternative, the DbExplorer can be configured to change the isolation level to READ UNCOMMITTED to avoid the locks alltogether (but display pontially wrong information).

Default value: 2500

26.9. SQL Execution related settings

Maximum script size for in-memory script execution

Property: workbench.sql.script.inmemory.maxsize

This setting controls the size up to which files that are executed in batch mode or via the WbInclude command are read into memory. Files exceeding this size are not read into memory but processed statement by statement. When a file is not read into memory the automatic detection of the alternate delimiter does not work any longer. The size is given in bytes.

Default: 1048576

Ignoring certain SQL commands

Property: workbench.db.ignore.[dbid]

For a DBMS identifier you can define a list of commands that are simply ignored by SQL Workbench/J. This is useful e.g. for Oracle, when you want to run scripts that are intended for SQL*Plus. If those scripts contain special SQL*Plus commands (that are not understood by the Oracle server as SQL*Plus executes these commands directly) they would fail in SQL Workbench/J. If those commands are simply ignored and not send to the server, the scripts can run without modification.

Default: workbench.db.ignore.oracle=prompt,exit,whenever

Enabling short WbInclude

Property: workbench.db.supportshortinclude

By default the WbInclude command can be shortened using the @ sign. This behaviour is disabled for MS SQL to avoid conflicts with parameter definitions in stored procedures. This property contains a list of DBIDs for which this should be enabled. To enable this for all DBMS, simply use * as the value for this property.

Default: oracle, rdb, hsqldb, postgresql, mysql, adaptive_server_anywhere, cloudscape, apache_derby

Check for single line commands without delimiter

Property: workbench.db.checksinglelinecmd

When parsing a SQL script, SQL Workbench/J supports statements that are put into a single line without a delimiter. This is primarily intended for compatibility with Oracle's SQL*Plus and is not enabled for other database systems.

Default: oracle

26.10. Default settings for Export/Import

For some switches of the WbExport and WbImport command, you can override the default values used by SQL Workbench/J in case you do not provide the parameter. The default values mentioned in this chapter apply, if no property is defined in the workbench.settings file. The current default for these properties is displayed in the help message when you run the corresponding command without any parameters.

Controlling header lines in text exports

Property: workbench.export.text.default.header

Possible values: true, false

This property controls whether default value for the -header parameter of the WbExport command.

Default: false

Controlling XML export format

Property: workbench.export.xml.default.verbose

Possible values: true, false

This property controls whether XML exports are done using verbose XML or short tags and only basic formatting. This property sets the default value of the -verbosexml parameter for the WbExport command.

Default: true

Setting default for WbImport's -continueOnError parameter

Property: workbench.import.default.continue

Possible values: true, false

This property controls the default value for the parameter -continueOnError of the WbImport command.

Default: false

Setting a default for WbImport's -header parameter

Property: workbench.import.default.header

Possible values: true, false

This property controls the default value for the parameter -header of the WbImport command.

Default: true

Setting a default for WbImport's -multiLine parameter

Property: workbench.import.default.multilinerecord

Possible values: true, false

This property controls the default value for the parameter -multiLine of the WbImport command.

Default: false

Setting a default for WbImport's -trimValues parameter

Property: workbench.import.default.trimvalues

Possible values: true, false

This property controls the default value for the parameter -trimValues of the WbImport command.

Default: false

26.11. Controlling the log file

When SQL Workbench/J initializes the logging environment, it also adds two system property that can be used to define the logfile relative to the configuration or the installation directory:

  • workbench.config.dir contains the full path to the configuration directory
  • workbench.install.dir contains the full path to the directory where sqlworkbench.jar is located

These properties can be used to put the logfile into the directory relative to the config or installation directory without the need to hardcode the directory name.

26.11.1. Configure internal logging

Log file location

Property: workbench.log.file

Defines the location of the logfile. By default, the file will be named workbench.log and will be written into the configuration directory.

Log level

Property: workbench.log.level

Set the log level for the log file. Valid values are

  • DEBUG
  • INFO
  • WARN
  • ERROR

Default: INFO

Log format

Property: workbench.log.format

Define the elements that are included in log messages. The following placeholders are supported:

  • {type}
  • {timestamp}
  • {message}
  • {error}
  • {source}
  • {stacktrace}

This property does not define the layout of the message, only the elements that are logged.

If the log level is set to DEBUG, the stacktrace will always be displayed even if it is not included in the format string.

If you want more control over the log file and the format of the message, please switch the logging to use Log4J.

Default: {type} {timestamp} {message} {error}

Logging to the console

Property: workbench.log.console

Defines whether SQL Workbench/J logs messages additionally to the standard error output

Default: false

Logging SQL used for retrieving metadata

Property: workbench.dbmetadata.logsql

If this is set to true the SQL queries used to retrieve DBMS specific meta data (such as view/procedure/trigger source, defined triggers/views) will be logged with level INFO.

This can be used to debug customized SQL statements for DBMS's which are not (yet) preconfigured.

Default: false

26.12. Configure Log4J logging

26.12.1. Turn on Log4J logging

Property: workbench.log.log4j

If you need more control over the logfile (e.g. for batch processing) you can delegate logging to Log4j. You can turn on Log4j logging in two different ways:

  • The value of the property is true
  • The value of the property points to an existing file

If you just pass true as the value for this property, the Log4j configuration file must be accessible to Log4j through the usual ways (please refer to the Log4j manual for details). If you specify a configuration file, this will be "passed" to Log4j by setting the system property log4j.configuration to contain the correct "file URL" needed by Log4j.

When passing a configuration file through this property, you can use a system property as part of the filename (e.g. ${user.home}/sqlworkbench.log). If the filename denotes a relative filename (e.g. log4j.xml without any path information), then it is assumed to be relative to the configuration directory.

When you turn on Log4J logging, you must copy copy the Logg4J library as log4j.jar into the directory where sqlworbkench.jar is located. Do not include the version number in the filename.

[Important]

The jar file must be named log4j.jar

If the Log4J classes are not found, the built-in logging will be used (see above)

When Log4J logging is enabled, none of the logging properties described in the previous section will be used. You have to configure everything through log4j.xml.

When using HelpShow log file with Log4J enabled, and you have configured Log4J to write to multiple files, only the first file will be shown.

When SQL Workbench/J initializes the logging environment, it also adds two system property that can be used to define the logfile relative to the configuration or the installation directory:

  • workbench.config.dir contains the full path to the configuration directory
  • workbench.install.dir contains the full path to the directory where sqlworkbench.jar is located

These properties can be used to put the logfile into the directory relative to the config or installation directory without the need to hardcode the directory name in log4j.xml

A sample log4j.xml can be found in the scripts directory of the SQL Workbench/J distribution.

The system properties that are set by SQL Workbench/J to point to the configuration and installation directory (see above) can also be used in the log4j.xml file.

26.13. Settings related to SQL statement generation

Controlling schema usage in generated SQL statements

Property: workbench.sql.ignoreschema.[dbid]=schema1,...

Define a list of schemas that should be ignored for the DB ID When SQL Workbench/J creates DML statements and the current table is reported to belong to any of the schemas listed in this property, the schema will not be used to qualify the table. To ignore all schemas use a *, e.g. workbench.sql.ignoreschema.rdb=*. In this case, table names will never be prefixed with the schema name reported by the JDBC driver. The values specified in this property are case sensitiv.

Note that for Oracle, tables that are owned by the current user will never be prefixed with the owner.

Default values:

.oracle=PUBLIC
.postgresql=public
.rdb=*

Defining CREATE TABLE templates for WbCopy

Property: workbench.db.[dbid].create.table.[typename]

This defines a complete CREATE TABLE statement that is used by WbCopy to create the target table. The typename value is the value that has to be used for the -tableType parameter of the WbCopy command.

The following placeholders are supported in the template

%fq_table_name% replaced with the fully qualified table name
%table_name% replaced with the specified table name (without schema or catalog)
%columnlist% replaced with the column definitions (for all columns)
%pk_definition% replaced with the primary key definition.

The placeholder %pk_definition% can be used if the DBMS does not support defining a primary key using an ALTER TABLE on the created table. If this placeholder is present in the template and the table has a primary key, the placeholder will replaced with an approriate PRIMARY KEY (col1, ...) expression. Note that the template must not contain the needed comma for the PRIMARY KEY. The comma will be added by SQL Workbench/J if a primary key is defined. If the table has no primary key, the placeholder will automatically be removed.

Default values:

.postgresql.create.table.temp=CREATE LOCAL TEMPORARY TABLE %fq_table_name% ( %columnlist% ) ON COMMIT DROP
.oracle.create.table.globaltemp=CREATE GLOBAL TEMPORARY TABLE %fq_table_name% ( %columnlist% ) ON COMMIT DELETE ROWS
.h2.create.table.temp=CREATE LOCAL TEMPORARY TABLE %fq_table_name% ( %columnlist% )
.informix_dynamic_server.create.table.temp_nolog=CREATE TEMP TABLE %fq_table_name% ( %columnlist% %pk_definition% ) WITH NO LOG

System generated names for contraints

Property: workbench.db.[dbid].constraints.systemname

Defines a regular expression to identify system generated constraint names. If a constraint name is identified as beeing system generated, it is treated as if no name was defined, when e.g. creating the SQL for a table. Whether or not SQL Workbench/J then generates a name for the constraint can be controlled in the options for the DbExplorer.

Default values:

oracle: ^SYS_.*
mysql: PRIMARY

Controlling the chunk size for WbDataDiff

Property: workbench.sql.sync.chunksize

Controls the number of rows that are retrieved from the target table when running WbDataDiff or WbCopy with the -syncDelete=true parameter.

Default value: 25

26.14. Customize table source retrieval

SQL Workbench/J re-generates the source of a table based on the information about the table's metadata returned by the driver. In some cases the driver might not return the correct information, or not all the information that is necessary to build the correct syntax for the DBMS. In those cases, a SQL query can be configured that can use the built-in functionality of the DBMS to return a table's definition.

This DBMS specific retrieval of the table source is defined by two properties in workbench.settings.

Defining the SQL statement

Property: workbench.db.[dbid].retrieve.create.table.query

This property defines the SQL query that should be executed. It must be a statement that returns a result set. The statement may contain three placeholders: %catalog%, %schema% and %table_name% that are replaced with the values of the actual table before running the statement.

Defining the result column

Property: workbench.db.[dbid].retrieve.create.table.sourcecol

By default the source code is assumed to be in the first column of the result. If that is not the case this property can be used to define the column index in which the table's source is available. The first column has the index 1.

The following example configures a SQL statement to retrieve the table source using MySQL's SHOW CREATE TABLE:

workbench.db.mysql.retrieve.create.table.query=show create table %catalog%.%table_name%
workbench.db.mysql.retrieve.create.table.sourcecol=2

The following example configures a SQL statement to retrieve the table source using Oracle's DBMS_METADATA package:

workbench.db.oracle.retrieve.create.table.query=select dbms_metadata.get_ddl('TABLE', '%table_name%' ,'%schema%') from dual

If an error occurs during retrieval, SQL Workbench/J will revert to the built-in table source generation.

26.15. Filter settings

Controlling the number of items in the pick list

Property: workbench.gui.filter.mru.maxsize

When saving a filter to an external file, the pick list next to the filter icon will offer a drop down that contains the most recently used filter definitions. This setting will control the maximum size of that dropdown.

Default value: 15