Corporate Headquarters EMEA Headquarters Asia-Pacific Headquarters
100 California Street, 12th Floor
San Francisco, California 94111
York House
18 York Road
Maidenhead, Berkshire
SL6 1SF, United Kingdom
L7. 313 La Trobe Street
Melbourne VIC 3000
Australia
Product Documentation
DBArtisan
®
XE and DBArtisan
®
8.7.6
New Features Guide
© 2010 Embarcadero Technologies, Inc. Embarcadero, the Embarcadero Technologies logos,
and all other Embarcadero Technologies product or service names are trademarks or registered
trademarks of Embarcadero Technologies, Inc. All other trademarks are property of their
respective owners.
Embarcadero Technologies, Inc. is a leading provider of award-winning tools for application
developers and database professionals so they can design systems right, build them faster and
run them better, regardless of their platform or programming language. Ninety of the Fortune
100 and an active community of more than three million users worldwide rely on Embarcadero
products to increase productivity, reduce costs, simplify change management and compliance
and accelerate innovation. The company’s flagship tools include: Embarcadero
®
Change
Manager™, CodeGear™ RAD Studio, DBArtisan
®
, Delphi
®
, ER/Studio
®
, JBuilder
®
and Rapid
SQL
®
. Founded in 1993, Embarcadero is headquartered in San Francisco, with offices located
around the world. Embarcadero is online at www.embarcadero.com
.
Embarcadero Technologies 2
TABLE OF CONTENTS
New features summary ........................................................................................................... 4
Top new features ..................................................................................................................... 6
Database object management ........................................................................................... 6
Microsoft SQL Server object management enhancements ......................................... 6
IBM DB2 for Linux, Unix, and Windows object management enhancements ............ 8
IBM DB2 for z/OS object management enhancements ............................................. 10
Oracle object management enhancements ................................................................ 13
Revamped editors and wizards..................................................................................... 13
ISQL Editor ......................................................................................................................... 13
Automated error detection and coding assistance .................................................... 13
Query option enhancements ........................................................................................ 14
SQL Preprocessor (#define/#include) improvement ................................................... 15
Data editor ......................................................................................................................... 15
Improved record creation for tables with all-default column values ......................... 15
Default value handling .................................................................................................. 15
Datasource Explorer updates ........................................................................................... 15
New tree view with extended functionality ................................................................. 15
Data source filtering ...................................................................................................... 16
Additional currency or miscellaneous features ............................................................... 18
Additional resources ............................................................................................................. 20
Licensing your Embarcadero Technologies product ..................................................... 20
Embarcadero Technologies product support ................................................................. 20
Embarcadero Technologies technical support ............................................................... 20
Embarcadero Technologies on the Web ........................................................................ 20
Embarcadero Technologies 3
NEW FEATURES SUMMARY
Unicode support
DBArtisan now includes Unicode support throughout the application, allowing you to
work with data in different languages and display the text correctly on screen.
Datasource Explorer tree filtering
This release introduces a number of techniques for filtering the Datasource Explorer
view. On-the-fly filtering lets you filter tree nodes as you work. Object filters, defined
and enabled at either the DBMS or datasource level, let you filter display by object
name/schema. Node filtering lets you hide or display object types by DBMS platform.
ISQL Editor updates
The SQL parsing components have been upgraded for this release. The new
components provide the following benefits when creating or editing scripts:
• Increased performance – Parsing activity is much faster than in previous
releases.
• Platform concurrency – The ISQL editor now supports syntax and features of the
most recent DBMS versions supported by DBArtisan.
• Accuracy – Syntax issues in previous releases of DBArtisan have been corrected.
New editor features are also provided. The ISQL Editor has been enhanced with a set of
automated validation and code assistance features. On-the-fly syntax-checking locates
and diagnoses syntax errors as you type. Semantic validation eliminates typographical
errors and helps avoid outdated references in specified object names. Code assist
analyzes statements as you type and offers intelligent suggestions with regard to object
names
Query options customizing the execution environment can now be saved and loaded.
The frequency with which the options are sent to the server has been optimized.
Microsoft SQL Server exploitation
Index support in DBArtisan has been upgraded to account for newer Microsoft SQL
Server index functionality. You can now rebuild, reorganize, and disable indexes,
primary keys, and unique keys. As well, when creating, dropping, or rebuilding indexes,
primary keys, or unique keys, online options are available with these operations. This
release also offers sparse columns, MAXDOP support, and page and row locking.
IBM DB2 for Linux, Unix, and Windows exploitation
DBArtisan now supports the following recent upgrades to IBM DB2 for Linux, Unix, and
Windows functionality:
• IMMEDIATE and DEFERRED options when editing buffer pools
Embarcadero Technologies 4
• Encoding scheme options of ASCII, UNICODE, or NONE for character or graphic
string parameters in procedures and functions
• Object ownership transfer for object owners and users with SECADM authority
• Additional “history” deadlock event types for Event Monitors
• Inherit isolation level with or without lock request options on Structured Type
methods
IBM DB2 for z/OS exploitation
This release offers the following DB2 for z/OS upgrades:
• Extended Truncate table support
• Table cloning actions
• Updated calculation and display of catalog table statistics
• Long object names
• Column size and default value modification with simple ALTER TABLE
statements
• Compressed indexes, index key randomization, and expression-based indexes
• Universal tablespaces
Oracle exploitation
When creating or modifying tablespace datafiles, you can now set the availability of
datafiles to either ONLINE or OFFLINE.
User interface changes and related features
The Datasource Explorer tree features a new look for this release. Functionality
improvements include simpler connection, improved representation of
ownership/containment, and more object actions available directly from the tree.
Continuing from previous releases, this release features reworked object management
editors and wizards.
Additional currency or miscellaneous features
In response to customer requests, a number of new or enhanced features are now
available:
• Export and import of datasource definitions.
• Removal of reliance on proprietary stored procedures for IBM DB2 for z/OS.
• The ability to start a server, pause a server, and continue after pausing against
Microsoft SQL Server.
• A server shutdown Wait Time option against Sybase datasources.
Embarcadero Technologies 5
TOP NEW FEATURES
Top new features for this release fall into the following functional categories:
• Database object management
• ISQL Editor
• Data editor
• Datasource Explorer updates
• Additional or miscellaneous features
DATABASE OBJECT MANAGEMENT
MICROSOFT SQL SERVER OBJECT MANAGEMENT ENHANCEMENTS
MAXDOP support in SQL Server Indexes
The wizards/editors for indexes and primary/unique keys now include a MAXDOP index
operation property, limiting the number of processors used in parallel plan execution.
Rebuilding, Reorganizing, and Disabling Indexes, Primary Keys, and Unique Keys
This DBArtisan introduces the ability to rebuild, reorganize, and disable indexes. The
same operations are now available for primary keys and unique keys as well.
Embarcadero Technologies 6
Online options when creating, dropping or rebuilding indexes, primary keys, or
unique keys
DBArtisan now offers ONLINE=ON clause options for operations against clustered
indexes, primary keys, and unique keys. Create, drop, and rebuild operations can now
be specified as online operations.
Similarly, when dropping a clustered index, primary key, or unique key from a table, you
have the option to execute the drop as an online operation.
Sparse columns
This release introduces support for Sparse columns. Defining a column as sparse
optimizes storage of columns that allow NULL values. This property does not apply to
the following data types: text, ntext, image, timestamp, geometry, or geography types
Embarcadero Technologies 7
or to user-defined data types. Columns with default values, default or rule bindings,
cannot be defined as sparse. Computed columns cannot be defined as Sparse, but the
columns in the computed expression can be Sparse columns.
Page and row locking in SQL Server indexes
The wizards/editors for indexes, primary keys, and unique keys now let you enable
locking granularity at the page and row level.
IBM DB2 FOR LINUX, UNIX, AND WINDOWS OBJECT MANAGEMENT ENHANCEMENTS
Ownership transfer
DBArtisan now lets an object owner or a user with SECADM authority transfer
ownership of an object to another user. The new user is automatically granted the same
privileges as the former owner. Ownership can be transferred on an object-by-object
basis or you can transfer all objects currently owned by an individual user. You can
transfer ownership of most DB2 object types.
Embarcadero Technologies 8
Deferred option for Buffer Pools
Previously only available in the Create Buffer Pool wizard, a Deferred property is now
available in the Buffer Pool editor. It controls IMMEDIATE or DEFERRED update of the
buffer pool.
Encoding scheme options for procedures and functions
Parameter CCSID properties, available on the editors/wizards for procedures and
functions, let you select an encoding scheme of ASCII, UNICODE, or NONE for
character or graphic string parameters.
Isolation Level Lock Request options on Structured Type methods
For structured type methods, you can now specify whether the INHERIT clause is
specified as INHERIT ISOLATION LEVEL WITHOUT LOCK REQUEST or INHERIT
ISOLATION LEVEL WITH LOCK REQUEST. This setting is available for methods
specified with a Language of SQL.
Embarcadero Technologies 9
Additional deadlock event types for Event Monitors
The Event Monitor wizard now lets you build event monitors using additional event
types:
• DEADLOCKS WITH DETAILS HISTORY and DEADLOCKS WITH DETAILS
HISTORY VALUES (IBM DB2 for Linux, Unix, and Windows version 9.0 and higher)
• ACTIVITIES, THRESHOLD VIOLATIONS, and STATISTICS (IBM DB2 for Linux,
Unix, and Windows version 9.5)
IBM DB2 FOR Z/OS OBJECT MANAGEMENT ENHANCEMENTS
Extended Truncate table support
For this release, the truncate operation has been extended to control DROP
STORAGE/REUSE STORAGE, RESTRICT WHEN DELETE TRIGGERS/IGNORE DELETE
TRIGGERS, and IMMEDIATE clauses of the TRUNCATE TABLE statement.
Long object names
This release introduces support for DB2 Long Object Names, typically allowing
identifiers of up to 128 bytes.
Embarcadero Technologies 10
Table cloning actions
DBArtisan now supports object actions corresponding to CREATE CLONE and DROP
CLONE options for ALTER TABLE statements, functionality introduced in IBM DB2 for
z/OS version 9. EXCHANGE DATA statement support is also provided, letting you
quickly replace a table with its clone.
Universal tablespaces
Universal tablespaces combine the benefits of partitioned table spaces with segmented
table spaces. DBArtisan now offers Range-partitioned and Partition-by-growth
tablespace types. Other properties let you provide additional CREATE TABLESPACE
options required to implement these universal tablespaces.
Modifying column sizes and default values with a simple ALTER TABLE statement
Changing column sizes and default values can now be handled through a simple ALTER
when working against DB2 for z/OS version 8 and above. This eliminates the cost of
using an extended ALTER.
Embarcadero Technologies 11
Catalog table statistics
Calculation and display of certain index and table statistics have been revised to reflect
DB2 version 8.x updates to system catalogs. New statistics (and the relevant catalog
table column) for this release impacted by this change include:
• Index statistics: Data Blocks/Key (AVGKEYLEN) and DASD storage (SPACEF)
• Table statistics: Average Row Length (AVGROWLEN) - DASD storage (SPACEF)
Existing statistics impacted by this change include:
• Index statistics: Cluster Ratio (CLUSTERRATIOF)
• Table statistics: Number of rows (CARDF) and Number of pages (NPAGESF )
• Column statistics: Number of Distinct Values in the Column (COLCARDF)
Compressed indexes, index key randomization, and expression-based indexes
This DBArtisan release introduces support for these IBM DB2 for z/OS version 9
features.
• Compressed Indexes - Compressed indexes are useful when space is at a
premium, insertions are sequential, and deletions are few.
• Index Key Randomization – Entries are stored in random tree locations to avoid
page contention on insertion. Available for indexes created with the Padded
property selected, the RANDOM sort setting is available on the Columns panel.
• Expression-based Indexes – This feature is activated using the Index on
Expression property. The key-expression is provided on the Columns
panel/tab.
Embarcadero Technologies 12
ORACLE OBJECT MANAGEMENT ENHANCEMENTS
ONLINE/OFFLINE status of tablespace datafiles
When creating or modifying tablespace datafiles, you can now set the availability of the
datafile to either ONLINE or OFFLINE.
REVAMPED EDITORS AND WIZARDS
This release continues the reworking of object editors and wizards. Aimed at improving
workflow and the user interface experience, the new editors and wizards are being
reworked for consistency. The following object editors/wizards were upgraded for this
release:
• Microsoft SQL Server – Backup Devices, Linked Servers, Remote Servers, Roles, User
Messages
• DB2 for Linux, Unix, and Windows – Event Monitors, Structured Types
• DB2 for z/OS – Tablespaces
ISQL EDITOR
AUTOMATED ERROR DETECTION AND CODING ASSISTANCE
This release introduces a number of automated, as-you-type ISQL editor features.
While the existing debug and execution options provide assistance at the code-
completed or ready-for-testing stage, the new features optimize productivity as you
create or edit SQL. The new features minimize typographical errors, let you quickly
correct obsolete object name references, and save time and keystrokes.
Embarcadero Technologies 13
On-the-fly syntax checking
The ISQL editor now provides real-time, continuous syntax checking. With errors
persisting until corrected, this feature lets you quickly find and diagnose coding errors.
Semantic validation
Semantic validation ensures that the names of tables, columns, and views, are properly
specified. A semantic error is an indication that a name references an object not found
in the database.
Code Complete
Within SELECT, INSERT, UPDATE, DELETE, CALL, and EXEC statements, the Code
Complete feature offers intelligent suggestions at those points in the statement where
objects names are specified. A Code Complete suggestion list lets you choose from a
list of object names. This both eliminates keystrokes and minimizes errors. Suggestions
are offered for tables, columns, views, packages, procedures, functions, and synonyms.
QUERY OPTION ENHANCEMENTS
You can now save your Query Options settings for subsequent loading in another
session. You can also revert your current settings to the DBArtisan defaults.
Embarcadero Technologies 14
In addition, the frequency for which query options are sent to the server has been
optimized for locked ISQL sessions. Once locked, a session will only send query
options on the first execution. While the session remains locked, only options you
explicitly modify are sent to the server on the next execution.
SQL PREPROCESSOR (#DEFINE/#INCLUDE) IMPROVEMENT
Prior to this release, preprocessing a script made #define and #include substitutions in
the same ISQL editor window, losing the current state of your opened or edited script.
For this release, you now have the option of opening a preprocessed script in a new
ISQL editor session, unexecuted.
DATA EDITOR
IMPROVED RECORD CREATION FOR TABLES WITH ALL-DEFAULT COLUMN VALUES
For tables with all columns defined as having a default value, new rows can be added
using only the Insert New Record and Save Current Row buttons. This eases record
creation as no values need to be typed in column cells.
DEFAULT VALUE HANDLING
For tables with all columns defined as having default values, there is no longer a
requirement to provide a value in at least one column cell when adding records. You
can now add multiple records by repeatedly clicking the Insert New Record and Save
Current Row buttons.
DATASOURCE EXPLORER UPDATES
NEW TREE VIEW WITH EXTENDED FUNCTIONALITY
Tree display and related functionality of the Datasource Explorer have been reworked.
Connection has been simplified, tree organization better reflects
ownership/containment, and more right-click object actions are available directly from
the tree.
Embarcadero Technologies 15
DATASOURCE FILTERING
Existing filtering capabilities have been revised or enhanced for this release. In
addition, new, more powerful filtering capabilities are now available.
Simple, name-based filtering
The Filter box at the top of the Datasource Explorer lets you hide datasources based on
naming. When you type one or more characters, the tree is updated to show only those
nodes of connected datasources whose name contains the characters you typed.
Complex, user-defined object name filtering
Filters that hide system objects or that display only objects owned by the current
DBArtisan user were available in previous releases. Now you can create your own
custom filter definitions. Each named filter consists of one or more ANDed conditions,
based on schema or object names. You can also specify the object types to which your
object filter applies.
Embarcadero Technologies 16
Filters can be defined at the datasource level or at the DBMS level and are enabled and
disabled at the datasource level. When a filter is enabled, the tree display for that
datasource will include only those objects whose name, schema, and object type satisfy
the filter conditions.
Node filtering
Node filtering, defined at the DBMS platform level, lets you hide and show objects
based on object type.
Filters node
Each datasource now features a Filters node. In addition to any custom object filters
you create, this node provides access to two default filters: Ignore System Objects and
Show Only My Objects.
Embarcadero Technologies 17
The defaults replace functionality previously available in the Options editor. They can
be enabled and disabled at the datasource level but cannot be edited or deleted.
Custom filters can also be enabled and disabled but the Filters node also provides
access to creation and maintenance features.
ADDITIONAL OR MISCELLANEOUS FEATURES
In response to customer requests, a number of new or enhanced features are now
available. General enhancements for this release include:
• The ability to export and import datasource definitions. This can save time in
organizations with large numbers of datasources and multiple users. For
example, you can have one user walk through the process of registering each
datasource and then export those definitions. Other users can then import the
resulting datasource definition files.
IBM DB2 for Z/OS improvements include the following:
• Removal of reliance on proprietary stored procedures - For the past several
releases, DBArtisan relied on a set of Embarcadero-provided stored procedures
in supporting certain DB2 z/OS functionality. This functionality is now provided
through native IBM DB2 for z/OS stored procedures. In addition to simplifying
installation and setup, this better positions DBArtisan for further DB2 for z/OS
enhancements.
Microsoft SQL Server improvements include the following:
• DBArtisan now offers Datasource menu commands that let you start a server,
pause a server, and continue after pausing.
Embarcadero Technologies 18
Sybase improvements include the following:
• Server shutdown Wait Time option – This feature lets you enable a grace
period, an upper limit on how long to wait for normal operations to complete
before forcing a shutdown.
Embarcadero Technologies 19
Embarcadero Technologies 20
ADDITIONAL RESOURCES
LICENSING YOUR EMBARCADERO TECHNOLOGIES PRODUCT
All Embarcadero Technologies products include a 14-day trial period. To continue using
the product without interruption, we recommend that you license it as soon as possible.
To license your product, use the License Request Wizard found in the Help menu of
your respective product. If you have not yet purchased your Embarcadero Technologies
product, contact [email protected], or [email protected] for sales in
the EMEA region.
EMBARCADERO TECHNOLOGIES PRODUCT SUPPORT
The Embarcadero Technologies Web site is an excellent source for additional product
information, including white papers, articles, FAQs, and the developer network. Click
any of the links below, to find:
• Documentation
• Product Demos
• Technical Papers
• Developer Network
EMBARCADERO TECHNOLOGIES TECHNICAL SUPPORT
If you have a valid maintenance contract with Embarcadero Technologies, the
Embarcadero Technical Support team is available to assist you with any problems you
have with our applications. Our maintenance contract also entitles registered users of
Embarcadero Technologies products to download free software upgrades during the
active contract period. Evaluators receive free technical support for the term of their
evaluation (14 days).
We encourage you to open technical support cases via the Technical Support request
form at the Embarcadero Technologies Web site. For additional information about
Embarcadero Technologies Technical Support, go to the Support page on our Web
site.
EMBARCADERO TECHNOLOGIES ON THE WEB
To download evaluations of other Embarcadero Technologies products or to learn
more about our company and our products, visit us at www.embarcadero.com
.
