Version 4.1.2

New and Changed Features for the Data Studio, Version 4.1.2

All Data Studio APAR fixes included in DS v4.1.1 APAR 1 - 5 are included in Data Studio v4.1.2.

Enhanced support for BigSQL including: connection support, and parser support with specific BigSQL grammar.

Deployment Manager enhanced to make enterprise deployment easier.

Data Studio can be used with DB2 10.5 FP5 and later databases that employ Native Encryption.

Includes all the cumulative fixes from previous fix packs.

Version 4.1.1

New and Changed Features for the Data Studio, Version 4.1.1

Support for Fix Pack 4 for DB2 for Linux, UNIX and Windows v10.5. This includes:

The latest BLU capabilities, such as the ability to alter BLU tables by adding columns

Columnar materialized query tables (MQT)

Support for Shadow tables (Replication-maintained MQTs)

Enhancements for DB2 for z/OS:

More objects (such as related views) are created when you generate DDL for databases or tablespaces.

When you deploy Java stored procedures, you can decide whether or not to refresh Workload Manager (WLM) immediately. This is useful if you have multiple Java stored procedures and only want to refresh WLM once.

Navigation improvements:

This release adds more navigational capabilities to the Data Studio client so that database administrators can find relevant database objects more quickly. When you select an object and open the Show menu for it, new options are available.

Data object

New menu options under "Show"

Table

Show Triggers

Show Views

Show UDTs

Show UDFs

Show Packages

View

Show Triggers

Show UDFs

Show Views

Alias

Show Views

Trigger

Show Views

Show UDFs

Index *

Show Tablespaces

Table Space *

Show Indexes

UDF

Show Packages

Stored Procedure

Show Packages

* = only for DB2 for Linux, UNIX, and Windows

DB2 client command line processor added to SQL editor:

In the SQL editor, a new run method was added, called "DB2 Client Command Line Processor”, for DB2 for Linux, UNIX, and Windows. Now data administrators can run DB2 commands and command scripts on remote data sources using a locally installed DB2 Client. You can also move data between the remote system and local storage.

Running SQL scripts is now easier:

A database administrator can now persist and retrieve SQL scripts to and from the file system without creating a new Eclipse project.

SQL results can be examined in either a split pane view, which includes both the history and results, or single pane, which includes only the history or the results.

Generating scripts for object changes is more reliable

The interface for object change management has been streamlined to save time generating change scripts. For example, if you generate DDL and any affected data objects have errors, the errors are displayed so you can fix them.

When a table is dropped and recreated, your RUNSTATS profile is preserved.

You can now use the following Create or Replace statements

CREATE OR REPLACE ALIAS

CREATE OR REPLACE VIEW

CREATE OR REPLACE NICKNAME

CREATE OR REPLACE SEQUENCE

CREATE OR REPLACE VARIABLE

CREATE OR REPLACE TRIGGER

CREATE OR REPLACE MASK

CREATE OR REPLACE PERMISSION

CREATE OR REPLACE TYPE

You can use the following Alter DDL statements:

ALTER TABLE ADD PARTITION

ALTER BUFFERPOOL

ALTER TABLE ALTER COLUMN DROP GENERATED

ALTER TABLE ALTER COLUMN SET NOT HIDDEN/IMPLICITLY HIDDEN

Big SQL for Infosphere Big Insights is now supported: You can:

Browse and navigate Hadoop tables and show table contents, and create and alter Hadoop tables, views, and constraints

You can now change the default statement terminator or specify a different statement terminator for the SQL statements in a script that you create in the SQL and XQuery editor using the --#SET TERMINATOR x command.

The Object List editor displays more information about data objects

The Object List editor now displays the following information about data objects:

Data object

New information displayed

Table Space

Managed By *

Percentage *

Data Pages *

Table

Statistics Time

MQT

Regular Table Space *

Index Table Space *

Large Table Space *

Table Space **

Maintained by **

Cardinality

Statistics Time

Stored Procedure

Specific Name

Fenced *

Language

Package

Binder *

Cursor *

Number of sections *

Optimization class *

Explain snapshot *

Collection ID **

* = only for DB2 for Linux, UNIX, and Windows

** = only for DB2 for z/OS

Diagnosing problems is easier in the routine debugger

One common problem that can occur during debugging is that the system cannot connect to the DB2 server. There are a variety of reasons this can happen, such as a firewall, incorrect configuration of the network, an incorrect network adaptor, and so on. The routine debugger can now diagnose the root cause of the problem and suggest actions to fix it.

Version 4.1.0.1

New and Changed Features for the Data Studio Client, Version 4.1.0.1

Data Studio Version 4.1.0.1 includes enhancements or additional support in the Data Studio client.

Tip: For details about which features exist in each component of Data Studio, see the Data Studio features document on the support site.

The routine editor now supports and stores routines with a file extension of .*sql, such as .spsql/.udfsql/.pkgsql/.javaspsql, to improve persistence and reduce dependence on the Eclipse Modeling Framework. The routine editor is still compatible with existing files that use the .xmi file extension but no longer stores new files with that file extension.

You can now export the entire set of results or cut and paste individual rows to a Microsoft Excel spreadsheet.

In the Export wizard, there are two new options: Open export file and Include column header.

A new export option, Export > Selected Rows, has been added.

More information: For information about the SQL results view, see SQL Results view.

Administration enhancements:

Improved data partition support The EVERY clause of the CREATE TABLE statement is now supported and allows you to specify the width of each data partition range in tables, including support for calendar and time based partition ranges.

Improved integration with IBM InfoSphere Optim Query Workload Tuner If you have InfoSphere Optim Query Workload Tuner, you can now open the Convert to Column Organization task assistant directly from the Workload Table Organization Advisor.

Support has been added to the Import, Load, and Export task assistants for choosing to run commands with the IBM DB2 client command line processor (CLP). In addition to choosing to run commands with JDBC or the IBM DB2 server CLP, you can now choose to run commands with the IBM DB2 client CLP if you have an instance installed.

A new filter has been added to the Restore task assistant to improve managing the list of backup images. In the Backup image section of the Restore task assistant, you can now enter keywords to filter out and locate specific backup images when you are working with a large number of images.

Enhancement for DB2 for z/OS:When examining data objects from DB2 for z/OS, it is now easier to find objects that have been recently edited by using the new Changed Object bar.

User interface simplification and usability enhancements:

User interface simplification:

You can now turn off functions you do not use, which removes them from the workspace and then restore them later.

In the Data Source Explorer, the context menus have been reorganized to be similar to those in the Administration Explorer.

The Review and deploy wizard has been revised to improve usability The layout, labels, descriptions, and help have been updated to improve the experience of reviewing commands, choosing advanced options, specifying the column mapping, and then choosing to edit, create a job, or deploy the commands to the database.

The SQL and XQuery editor has been updated to include a tabbed view The command pane has been replace with tabs that appear at the bottom of the SQL and XQuery editor to allow you to quickly switch between each tab.

When editing a table in the Administration Explorer, a new column has been added to identify which line you are working on. The new column is persistent, and is useful if the record has many columns that you must scroll to display.

The folders in the Administration Explorer have been reordered to display in the order of most commonly used and no longer display alphabetically.

InfoSphere Optim Query Workload Tuner was able to extract from compiled SQL triggers statements marked as 'Y' in the EXPLAINABLE column of SYSIBM.SYSPACKSTMT table. However, InfoSphere Query Workload Tuner was not able to explain extracted statements that contained native trigger syntax, such as NEW, OLD, NEW_TABLE, and OLD_TABLE.

In the new fix pack, InfoSphere Optim Query Workload Tuner is able to explain such statements. You can then generate an access plan graph or get recommendations for improving statistics for objects that a statement references.

New filter, stmt_type_id, for capturing from package caches

If you are using an event monitor, you can now use the statement type identifier monitor element in the filters that you create for capturing SQL statement from a package cache.

New SQL Execution option in the installer Select the SQL Execution option alone to install a simplified version of Data Studio that includes tools for writing and running SQL and XQuery statements. The SQL and XQuery editor and SQL results view are included in the default layout, and most of the unrelated Data Studio features are not installed. You can choose to install the simplified version of Data Studio to install only the tools you need to efficiently run SQL.

Enterprise deployment has been improved After installing the product on your base computer, and configuring settings and preferences such as database connections, you can now deploy those settings and preferences to other computers in your enterprise.

Simplified product packaging Product packaging has been reduced down to two images that each support both 32-bit and 64-bit operation systems. You can now download a single product image for the supported versions of Microsoft Windows or Linux and it will either install a compatible version of IBM Installation Manager or upgrade your existing version.

Support for excluding null keys from indexes. Indexes that are created with the EXCLUDE NULL KEYS clause do not insert a key into the index object when all the columns in the key are null. Excluding null keys can improve storage and performance optimization when you do not want queries to access data associated with null keys.

Support for expression-based indexes. Index on expression creates an index where the results of an expression are kept in the index. This speeds execution of an SQL statement containing a predicate that includes the expression.

Support for managing column-organized tables (support for BLU acceleration):

Create column-organized tables.

Set the default table organization of new tables to column-organized with the DFT_TABLE_ORGANIZE parameter.

Convert row-organized tables to column organization with recoverable and non-recoverable options by using the new Convert to Column Organization task assistant.

Create only check and foreign key constraints as informational constraints by using the NOT ENFORCED option.

Support for multiple standby databases for High availability disaster recovery (HADR). With the task assistants for setting up and managing HADR, you can now manage the primary and standby databases. The task assistants also include improved status information about each standby database.

CLP support in task assistants:Support for specifying the Command Line Processor (CLP) as the run method in task assistants. You previously had to launch an SSH shell to run task assistants to generate CLP commands. With the new support, you can now generate CLP commands and run them with SSH or, when applicable, use the ADMIN_CMD procedure to run commands with JDBC.

Breakpoints enhancement in the routine debugger: Support for specifying breakpoints before routines are deployed. You can now set breakpoints in a new line of code while you are editing the routine and are no longer required to first deploy the routine in debug mode. When you deploy a routine in debug mode, the server removes any improperly set breakpoints and deploys the routine with the breakpoints enabled.

New checkpoint feature in the SQL and XQuery Editor. Use checkpoints to run only portions of your SQL statements at a time. Add one or more checkpoints to your SQL statements in the SQL editor and when you run your SQL statements, they will run only the SQL statements up to each checkpoint.

You can now alter the script in the editor, run one or more statements to view information, and restart the script at any point. This is useful for evaluating the results of the script before continuing.

The run actions have been enhanced. In the SQL and XQuery editor, you can now run the full script, start running statements from the location of the cursor, or select a section of the script to run. If you select part of a particular statement, the full statement will run from the begining.

There is now a new toolbar to enhance the usability around switching perspectives. Use the toolbar to manage your home activity, reset your views and editors back to their default settings, re-open the task launcher, and switch to other activities from a single menu.

A number of changes were made to the user interface to make it cleaner and easier to use. Some views were removed from the default layouts of perspectives, and only the most frequently used views were retained. You can add back any of the removed views with the Window > Show View window. If you are using an existing workspace, you must reset your perspective to change your layout. Buttons were also added in the Administration Explorer and Data Source Explorer for many common actions.

New wizard for finding and creating nicknames for tables in Federated databases (InfoSphere Federation Server). The new wizard connects to remote servers, searches and discovers remote tables, and then creates nicknames for the tables you select so that you create multiple nicknames instead of creating them individually.

New annotation in the query formatter to indicate whether a table is row-organized or column-organized.

To run the query formatter, capture a statement and click the Invoke Tools and Advisors button. Alternatively, in a query workload, right-click a statement and select Run Single-Query Tools and Advisors.

In the Invoke section, click Select What to Run. In the window that appears, select Format and annotate SQL statement. Then, click OK.

Support in access plan graphs for the new CTQ operator.

The CTQ operator represents a boundary within the DB2 query engine, in which operators that appear below the boundary process data as compressed column-organized vectors and tuples, whereas operators that are above the boundary operate on tuples that are not encoded.

Support for the IUDM counter.

The preference Check for obsolete statistics for the Statistics Advisor and Workload Statistics Advisor is modified to read the new STATS_ROWS_MODIFIED column of the SYSIBMADM.ADMINTABINFO view. This column gives the count of modifications to a table since RUNSTATS was last run on that table.

If your user ID has the SELECT privilege on this view and you select the Check for obsolete statistics preference, an algorithm determines whether statistics for the tables that are referenced by your SQL statement or query workload are obsolete.

Query tuning enhancements for DB2 11 for z/OS:

Support for temporal tables.

Support for two new special registers:

Current Temporal SYSTEM_TIME

Current Temporal BUSINESS_TIME

Support for the new global variable GET_ARCHIVE:

The addition of the EXPANSION_REASON column in the list of SQL statements that are captured from packages.

The ability to set these two special registers and the global variable before explaining SQL statements.

The new column Expansion Plan is added to the View Comparison Results by Workload EXPLAIN snapshots and SQL Statements panel. The value Y indicates the comparison is between two expansion access plans, and N indicates the comparison is between two normal access plans.

New report on the configuration of subsystems for use with the no-charge tuning features. Use this report to find out which tables, views, packages, and stored procedures are ready for the product to use and which are missing, at an out-of-date version, or in an unknown format. The report provides DDL for migrating tables and views to the latest versions, and DDL for creating missing tables and views. For all other problems, the report links to troubleshooting topics in the products information center (not available until the product release date when the information center is published).

SQL actions for editors in IBM Rational Developer for DB2 for z/OS: There are now more integration points when Data Studio 4.1 shell-shares with Rational Developer for System z (RDz). You can run SQL from a source editor by highlighting the SQL statement and selecting Run SQL. The results are displayed in the SQL Results view. Selecting Tune SQL is also a new option in the source editors, which allows you to open the selected SQL in IBM InfoSphere Optim Query Workload Tuner.

SQL statements in PL1, Cobol, and C++ data sources are now displayed in the SQL Outline similar to how they are displayed in Java. You can now use the from the SQL Outline view to view the dependent tables and columns for those SQL statements or launch IBM InfoSphere Optim Query Workload Tuner or Visual Explain to tune the statements. You can also jump from the SQL Outline to the editor directly by double-clicking the SQL statements.

More information: For details about the SQL Results view, see the SQL Results view.

Installation Manager, Eclipse platform, and Java enhancements:

Version 1.7 of IBM Installation Manager is now supported, including the 64-bit versions for Windows and Linux.

Data Studio Web Console, Version 4.1

The Data Studio web console includes the following feature enhancements:

Support for running jobs on another member if the connection to a member of a DB2 pureScale environment fails.

Support for generating alerts for when the percentage of the File System Utilization exceeds the threshold.

Support for viewing the Database activation state in the Data Server Status Alert details.

Support for viewing the db2diag.log file from within the Data Studio web console user interface. Since the PD_GET_HIST() stored procedure is used, no access to the log directory is required. You can specify the number of entries to list and a date and time for the start and stop.

The following features have been deprecated and will be removed in a future release:

The DB2 Administration Server (DAS) has been deprecated and is no longer supported for running SQL statements, utilities, and commands, or to browse and access files on remote servers. For more information, see Configuring for remote operations.

The Microsoft Server Message Broker (SMB) protocol has been deprecated starting with Data Studio Version 3.1.1 and is no longer supported for running SQL statements, utilities, and commands, or to browse and access files on remote servers.