Contents
This release of IBM Data Studio Version 1.1.0 contains the functionality
that was included in DB2 Developer Workbench Version 9.1, plus some additional
key features. For information about DB2 Developer Workbench Version 9.1
features, refer to the Developer Workbench Overview topic in the DB2 Version 9 for Linux, UNIX, and Windows information center.
Data Studio Version 1.1.0 provides new key features in the following categories:
You can now use the workbench to create Web services that expose database
operations (SQL SELECT and DML statements, XQuery expressions, or calls
to stored procedures) to client applications. You create Web services in
a data development project and you can easily add stored procedures and
SQL statements by dragging and dropping them to a Web service or by using
a wizard. You can also use the workbench to deploy or undeploy the Web
services to a Web server.
pureQuery provides a common query API that lets you access relational databases
and in-memory Java collections with a single interface: standard SQL. Using
pureQuery functionality in a Java project, you can bridge the divide between
relational data and Java source code by:
- Editing and testing SQL in Java programs with the Java editor, including
SQL code assist
- Creating pureQuery code from Java classes, SQL statements, and database
tables in any combination from each other
- Creating pureQuery code from procedures
- Associating SQL with method invocations
- Passing Java objects as input to SQL
- Creating Java objects as output from SQL
- In-memory Java collections access with standard SQL
- You can use the Database Explorer toolbar buttons or the File > Export menu to export database connection information to database descriptor (XML) files. The information in these files can then be imported to another workspace. This allows team members to more easily share database connection information.
- You can connect to DB2 for Linux, UNIX, and Windows, or DB2 for z/OS, using
Kerberos or LDAP authentication. You must have your Kerberos and LDAP environment
configured on the server to use this connection option.
- You can set database connection preferences (for example, connection timeout)
on a new page in the Preferences window. To set connection preferences,
click Window > Preferences, and click Data.
- When you connect to DB2 databases, you no longer need to specify a database version number in the New Connection wizard. Select All Versions to connect to any supported version of DB2.
- You can select to generate trace files for JDBC connections. Specify tracing options on the Connection Parameters page of the New Connection wizard, on the Tracing Options tab. This feature is only supported for connections that use the IBM Data Server Driver for JDBC and SQLJ.
- The SQL editor has been enhanced to allow you to develop queries that work
with both relational and XML data. You can now use the editor to create
SQL and XQuery expressions. Editor features such as content assist, syntax
highlighting, and query parsing and validation are available for these
query types. In addition to the editor enhancements, there is a new preferences
page for the editor that allows you to create and use query templates.
To use a template, type a query type (for example,
SELECT
) in the SQL editor, then press Ctrl + Space and select a template from
the content assist window. To view available templates or add more templates,
click Windows > Preferences, expand Data and SQL Editor, and click Templates.
- Routine tooling:
- The routine editors (stored procedure and UDF) have been revised for better
usability. For Java stored procedures, you can click a link to the Java
source from the routine editor so that you can find and edit the Java source
code more easily.
- When you are deploying nested stored procedures, you can now see and select
stored procedures with nested dependencies for deployment.
- If you are working in a team environment, you can use UI customization templates to customize the New Stored Procedure wizard user interface, so that available options are filtered. UI customization templates options are available in the Preferences window. Click Windows > Preferences, expand Data, and click Administrative Options.
- XML tooling:
- You can use a wizard or the table data editor to insert XML data from XML
files that are stored in a data development project or on the file system
into an XML column.
- For DB2 for Linux, UNIX, and Windows servers, you can create a CLP script
to register an XML schema that is stored in a data development project.
You can run the CLP script outside of the workbench to perform the registration.
There are several new tasks that are available from the Database Explorer
that are typically performed by database administrators.
- Data object management
Use the Data Object editor to create and alter data objects. To create
an object, open the editor from a folder in the Database Explorer (for
example, right-click the Tables folder and select Create > Table). To alter an object, right-click the object and select Alter. Many of the DB2 and Informix Dynamic Server database objects can be created and altered.
- Privilege management
Use the Data Object editor to grant, revoke, and modify privileges for data objects or authorization IDs. To specify the privileges for a data object, right-click the object in the Database Explorer, select Alter, and click the Privileges tab. For databases supporting roles, use the Data Object editor to grant, revoke, and modify roles for authorization IDs. To specify the roles for an authorization id, right-click the authorization id in the Database Explorer, select Alter, and click the Roles tab.
- Visualization of data values and relationships
To visualize data object relationships, you can create an overview diagram from the Database Explorer. These diagrams can be saved or printed as image files. To view an overview diagram, right-click a data object (for example, a schema) and select Add to Overview Diagram.
To visualize data value distributions, you can create a graphical value distribution view from the Database Explorer. To view value distributions, right-click a data object (for example, a table or view) and select Value Distributions > Multivariate to display a graphical representation of the existing data values for each column. This feature can be used in to assist in query management and query analysis (Visual Explain) tasks.
- Statistics support
View and run statistics for data objects to help you improve application
performance. To view statistics, highlight a supported data object (for
example, a table) in the Database Explorer and open the Properties view.
Click the Statistics tab in the Properties view to view statistics for the object. To update
statistics, right-click a supported data object and select Update Statistics. Statistics are supported for tables, views, and columns for DB2 for Linux, UNIX, and Windows and DB2 for z/OS, and for table spaces for DB2 for z/OS.
There is new support for Informix Dynamic Server (IDS) in this release.
After you connect to an Informix database in the Database Explorer, you
can perform most database administration tasks in the Database Explorer
and you can use the connection information to create a data development
project that targets IDS. You can develop and store SQL statements in the
data development project; however, the wizards and editors that are available
for DB2 routines are not available for IDS. To create and deploy routines
for IDS, you can type the CREATE syntax in the SQL editor and run the SQL
statement from the editor.
The following table describes stored procedure parameter support by IDS
version and JDBC driver:
|
IDS Version 10 - Informix Driver |
IDS Version 11 - Informix Driver |
IDS Version 11 - IBM Data Server Driver for JDBC and SQLJ |
No parameters |
yes |
yes |
yes |
IN parameters |
no |
no |
yes |
1 OUT parameter |
yes |
yes |
yes |
several OUT parameters |
no |
yes |
yes |
INOUT parameters |
no |
no |
yes |
The following data servers are supported for connection in this release:
- DB2 for Linux, UNIX, and Windows Versions 8 and 9
- DB2 for iSeries V5R2, V5R3, and V5R4
- DB2 for OS390 or z/OS Versions 7, 8, and 9
- Informix Versions 9.2, 9.3, 9.4, 10.0, and 11.0
- Derby Versions 10.0 and 10.1
Not all data servers are supported for routine development. See the help
topic "Stored procedure and user-defined function support by server
type" for details.
Hardware
requirements
- Disk space:
- On Windows: 680MB (Installation Manager: 100MB, Data Studio Workbench: 580MB)
- On Linux: 620MB (Installation Manager: 100MB, Data Studio: 520MB)
- Memory: 512MB (1GB recommended)
The following operating systems are supported:
- Windows Vista Business, Enterprise, Ultimate
- Windows 2000 Professional x86-32 (SP3 & SP4)
- Windows 2000 SP4 Advanced Server x86-32
- Windows Server 2000 x86-32 (SP3 & SP4)
- Windows Server 2003 Enterprise Edition x86-32 (SP1)
- Windows Server 2003 Enterprise Edition x86-64 (Run in 32-bit mode) (SP1)
- Windows Server 2003 Standard Edition x86-32 (GA and SP1)
- Windows Server 2003 Standard Edition x86-64 (Run in 32-bit mode) (GA and
SP1)
- Windows XP Professional x86-32 (SP1 & SP2)
- Windows XP Professional x86-64 (GA, SP1 & SP2) (Run in 32-bit mode)
- Red Hat Desktop Linux 4.0 x86-32
- Red Hat Enterprise Linux (RHEL) 4.0 AS/ES x86-32
- SuSE Linux (SLES) 9.0 Enterprise Server (SP1 - SP4) x86-32
Data Studio is installed via a separate product called Installation Manager.
During the Data Studio installation process, Installation Manager will
be installed on your computer and updated to the correct version if you
do not already have it installed.
Data Studio Version 1.1.0 can coexist on your computer with Developer Workbench
Version 9.1. However, the installation mechanisms are different so you
cannot use the Developer Workbench Version 9.1 DWB Product Updater to upgrade
to Data Studio Version 1.1.0. You must complete the steps described in
the IBM Data Studio Version 1.1.0 Installation Guide.
If you have installed a previous beta version of Data Studio Version 1.1.0,
you must uninstall it and reinstall this version. There is no support for
updating beta versions using Installation Manager.
If you have any questions about the use of IBM Data Studio 1.1.0, please post in the forum: http://www.ibm.com/developerworks/forums/dw_forum.jsp?forum=1086&cat=19.
The steps for Installing Data Studio on a Windows Vista computer are essentially
the same as installing on other Windows computers. However, there are some
important differences that are summarized below.
- You must run Installation Manager as the administrator. (Right-click the
program shortcut and click Run as administrator.) It is not sufficient to run as a user in the administrator group.
- If you are starting the installation of Data Studio from the launchpad
program, then you must run the launchpad program as the administrator.
- Selecting installation directories within the Program Files directory (typically C:\Program Files) is not recommended.
- If the installation location or shared resources directory for Data Studio
is in a directory in the path C:\Program Files, then you must run Data
Studio as the administrator. To run as administrator, right-click the program
shortcut and click Run as administrator.
- After installing Data Studio on Vista, you might get an error message after you start Data Studio. To work around this problem:
- Log in as administrator, open Data Studio and specify a workspace directory
where the non-administrator user has write privilege.
- Exit Data Studio and log out.
- Log in as a non-administrator user.
- Start Data Studio, which will show the workspace specified by the administrator.
- To install Data Studio Version 1.1.0 on Windows or Linux systems:
-
- Uninstall previous beta versions of Data Studio Version 1.1.0. See the next section for details.
- Ensure that you have the correct authority for the machine on which you
are installing the product:
- On Windows: You must have administrator authority.
- On Linux: You must use the root user account.
- Follow the detailed instructions in the IBM Data Studio Version 1.1.0 Installation
Guide to install Data Studio using Installation Manager. The Installation
Guide is available from the Data Studio launchpad.
- Open Data Studio.
- On Windows: Click Start > All Programs > IBM Software Development Platform > IBM Data Studio > IBM Data Studio.
- On Linux: Click Programming > IBM Software Development Platform > IBM Data Studio or Development > IBM Software Development Platform > IBM Data Studio, depending on your Linux version.
Note: If you have problems launching Eclipse, try running eclipse.exe from a command line using the -clean
option. For example: eclipse -clean
.
- To uninstall previous versions of the Data Studio Version 1.1.0 Beta product:
-
- Ensure that you have the correct authority for the machine on which you
are installing the product:
- On Windows: You must have administrator authority.
- On Linux: You must use the root user account.
- Ensure that Data Studio is closed.
- Open Installation Manager.
- On Windows: Click Start > All Programs > IBM Installation Manager > IBM Installation
Manager.
- On Linux: Open a terminal window with root user privileges and run
/opt/IBM/InstallationManager/launcher_shortcut
.
- Click Uninstall Packages.
- In the Installation Packages list, select IBM Data Studio and select Version
1.1.0, and click Next.
- On the Summary page, view the uninstallation summary, then click Uninstall.
- After the Installation Manager finishes the uninstallation, click Finish.
- Delete the Data Studio product directory and the shared resources directory, if they are not automatically deleted by the Installation Manager.
- By default on Windows:
C:\Program Files\IBM\SDP70
and C:\Program Files\IBM\SDP70Shared
- By default on Linux:
/opt/IBM/SDP70
and /opt/IBM/SDP70Shared
- Optional: Uninstall IBM Installation Manager:
- On Windows:
- Click Start > Control Panel, then open Add or Remove Programs.
- Select IBM Installation Manager, and click Remove.
- On Linux: Run uninstaller.bin in the
/opt/IBM/InstallationManager/_uninst
directory.
This release contains the following known limitations and problems.
-
- General
-
- If you exported a connection using an earlier beta version of this product,
you must correct the JDBC driver path before you can import and use the
connection in this release.
- On Linux, you can only open Visual Explain from the routine editor or the
SQL editor.
- For JDBC testing, you must create a new connection with the New Connection
wizard, using the IBM Data Server Driver for JDBC and SQLJ. A connection
that is created automatically from database definitions on the local server
cannot be used.
- The table row count decorator in the Database Explorer indicates the number
of rows in the tables when the Tables folder was opened, not when the records are updated. If the row count
is obtainable through database statistics, the statistics row count will
be displayed with the statistics collection date prepended.
- To avoid performance problems, it is highly recommended to use data object
filtering in the Database Explorer when over 500 objects are likely to
be referenced.
- IBM Data Web Services
-
- If WASCE is already started before the workbench is started, you might
see an error informing you that the server port in in use when you start
or deploy a Web service to WASCE for the first time. This is because the
WASCE adapter takes some time to detect that WASCE is already running.
To work around this problem, wait a few seconds and you will see that the
state of the WASCE server changes to "start" automatically. You
can then deploy the Web service again.
- When you run an IDS stored procedures in a Web service, the procedure parameter
names will be listed as
parm1
, parm2
, and so on.
- The Informix parser does not parse the Call statement correctly, which
causes an error in the Operation wizard. Ignore the error and press the
Next button to proceed.
- The first time you deploy a Web service on Linux, a window opens prompting you to accept a WASCE license. If you see an error in the window that says that the license cannot be opened due to a Sun JDK issue, simply close the window. The deployment will continue.
- When you are adding a new WASCE server using the New Server wizard, make
sure you go through every page in the wizard by clicking Next, then click Finish to create the server.
- The Data Studio provides an option to install the WebSphere Application
Server Community Edition (WASCE) v1.1 install .exe. If you select this
install option, the WASCE install .exe will be located in the Data Studio
installation directory. You can also visit http://www-128.ibm.com/developerworks/downloads/ws/wasce/ to download WebSphere Application Server Community Edition (WASCE) v1.1.
- When you are installing the WASCE Eclipse plugin on Linux, you need to
start Data Studio as root.
- An exception occurs when you are generating a default XSD schema on an incorrect operation. To work around this problem, go back and use the Parse button to ensure that the operation is valid, and generate the XSD schema again. This is not done automatically.
- If an operation name under a Web service contains DBCS characters, or the
input value contains DBCS characters, you will see an error if you try
to run the operation in the Web service explorer, for POSTBINDING / GETBINDING
protocols. SOAP protocols are not affected. To work around this problem,
launch and run the Web service using an external browser (for example,
Firefox).
- Do not use the Browse button in the Web Services Explorer to load the .wsdl for the Web service.
Instead, right-click the Web services folder and select Launch Web Service Explorer.
- If you encounter an error while deploying a Web service and you are not
using the deployment option "Register database connection with Web
server", try the following tips to resolve the problem:
- When you are creating a Web service for a WebSphere Application Server
Community Edition v1.1 Web server, ensure that the "dataSourceArtifactId"
and "dataSourceGroupId" parameter values match (case-sensitive)
the parameter values that were used when you created the Web server's database
pool. By default, dataSourceArtifactId={database name}, dataSourceGroupId=console.dbpool.
- When you are creating a Web service for an Apache Tomcat v5.5 Web server,
ensure that the "DataSourceGlobalName" parameter value matches
(case-sensitive) the parameter value that was used when you defined the
database pool. By default, DataSourceGlobalName=jdbc/{database name}.
- If you encounter a problem deploying a Web service after having the workbench install the JDBC/JCC drivers into the Web server, manually stop and restart the Web server. The newly added JDBC/JCC drivers will then be available.
- If you encounter a problem while starting a Web server, try the following
tips to resolve the problem:
- Check for the
javaw.exe
process using the Task Manager. There should be one javaw.exe
process for Eclipse and one for each Web server. End any extra javaw.exe
processes (typically the ones with less memory usage). Then, start the
server using the Servers view in the workbench.
- Verify that you can start the Web server from outside of the workbench.
Delete all obsolete Data Server Web Services. Then stop the Web server
and start the Web server again from the Servers view in the workbench.
- If both of the above tips do not work, launch the Web servers administration
console and manually remove the deployed Web services. In addition, using
the Servers view in the workbench, you can delete the Web server instance
and create a new one.
- pureQuery
-
- Data object create and alter
-
- If you are adding a new column to a DB2 for iSeries table using the Create > Column menu option and using the Import Definition button to import from a primary key column, the DDL that is generated contains invalid clauses. To work around this problem, select Open with SQL editor, and modify and run the DDL from the SQL editor.
- When you create a new table, column, or index by importing the definition
from an existing table, column, or index, the statistics of the source
object do not copy over to the new object.
- DDL generation for altering a partition group on DB2 for Linux, UNIX, and Windows Version 9.1 using the Data Object editor fails. To work around this issue, click Open with SQL editor, and modify and run the DDL from the SQL editor.
- When you create synonym for DB2 for z/OS, you must select the Synonyms folder under the schema that is the same as the connection user id. If you select a Synonyms folder that is under a different schema, the synonym will still be created under the schema that is the same as the connection user id. You will need to manually refresh the Synonyms folder for it to appear after the creation.
- When generating DDL for an existing UDT on DB2 for z/OS or IDS, the GRANT
statements for any USAGE privileges do not have the correct syntax. To
work around this problem, open the DDL in the SQL Editor and add "TYPE
" immediately after "GRANT USAGE ON " in those GRANT statements.
- When you are modifying a table using the Data Object editor, use the Preview DDL button to preview the generated DDL. If there is generated DDL that backs up the existing table, you might need to modify the DDL in the SQL editor and run it from the SQL editor. You should examine the new table to see if data has been preserved. If the backup table is no longer needed, clean it up using the Drop menu option.
- If you need to assign a new regular table space while you are creating a new table on DB2 for Linux, UNIX, and Windows, you must first create a new table space by selecting the Create > Regular Table Space menu option from the Table Spaces folder in the Database Explorer. After the table space is created successfully in the database, you can then create your new table by selecting the Create > Table menu option and selecting a table space on the Table Spaces tab in the
Data Object editor.
- If you connect to a IDS 10 data server by using the IDS 11 selection in the Connection wizard and then try to create a synonym, you will get the following error:
java.lang.IllegalArgumentException: Argument cannot be null
. To work around this problem, recreate the connection by selecting IDS 10 as the data server.
- When creating a unique constraint, the name can exceed the limit set by
the database. To work around this problem, shorten the constraint name.
- After you update statistics for a data object, you must re-open the Statistics
tab in the Database Object Editor to see the updated values.
- The Create and Alter menu actions are not available for DB2 for Linux, UNIX, and Windows nicknames, or any elements contained within nicknames, from the Database Explorer. You can view nickname properties in read-only mode in the Properties view.
- When you add or modify a privilege in the Database Administration editor,
the change is immediately visible in the Database Explorer and the Properties
view. However, the actual change will be completed only after you click
Run DDL in the editor.
- If an authorization id (user, group, or role) is opened in the Data Object
editor to grant a new privilege on a database object, and the editor is
closed without executing the resultant DDL, then the same privilege, but
with an unspecified grantee, will subsequently be visible when viewing
that database object in the Properties view or in the Data Object editor.
Refreshing the associated database in the Database Explorer will result
in the incomplete privilege being disposed.
- Modification of a nullable column to a primary key column is not supported
in the Data Object editor.
- There is limited support for preserving data when altering data objects
with the Data Object editor. When you alter a table, the workbench will
determine whether the modifications can be performed using an ALTER TABLE
statement. If an ALTER TABLE statement cannot support the requested modifications,
then the generated DDL will create a backup of the original table before
creating the modified table. Additionally, when a backup is required, a
statement to copy the backed-up data to the modified table will be created.
This statement is intended as a template for the user to modify as required
to support the unique requirements of the existing data and table modifications.
- Database objects in the following states cause an exception when they are
expanded in the Data Project Explorer or the Database Explorer. You can
safely ignore the exceptions:
- If you define a table that contains XML data, but does not include a primary
key, updating the XML column will fail in the table data editor. You must
add a primary key or unique index to the table that contains the XML data.
- Routine and query development
-
- Problems running or debugging a nested Java stored procedure if the call
to the nested procedure is unqualified on DB2 for Linux, UNIX, and Windows:
The following scenario describes the problem: There are two procedures,
procA and procB, and both are deployed to a schema that is not the same
as SQLID. If procA calls procB and doesn't specify a qualifier schema,
then running or debugging procA will result in SQLCODE=-440 (No authorized
routine named routine-name of type routine-type having compatible arguments
was found) This is because the nested procedure procB could not be found
without a qualifier schema. To work around this problem, specify the schema
where procB is deployed in CURRENT PATH register. Select procA in the project
and select Run Settings. In the Run Settings window, click the Before Run tab and input this set statement in the text field (including the semicolon):
SET CURRENT PATH = schema_qualifier_for_procB;
Click OK. Now, run and debug procA, the nested procedure procB will get called and executed correctly.
- External stored procedures for DB2 for z/OS that were not created with
the workbench cannot be dropped using the workbench.
- You cannot enter a hex value for a BLOB input parameter when connected
to DB2 for Linux, UNIX, and Windows Version 9.
- For native stored procedures targeting DB2 for z/OS that have multiple
versions, the stored procedure versions are displayed twice in the Database
Explorer.
- The array type is currently only supported as the parameter type of a stored
procedure for DB2 for Linux, UNIX, and Windows Version 9.5. To work around
this problem:
- Use the SQL editor to create a new UDT and run the SQL to deploy it. For example:
CREATE TYPE nametype AS VARCHAR(10) ARRAY[2]
After the UDT is deployed, the UDT will be visible in the Database Explorer
but will not be accurately described in the Properties view.
- In the routine editor, you can reference the UDT in the parameter list of your stored procedure. You must do this on the Source page of the editor, because the new UDT will not be available as a parameter type in the New Stored Procedure Wizard. For example:
CREATE PROCEDURE getphones (IN name nametype)
- Deploy the stored procedure by right-clicking in the Source page of the routine editor and selecting Deploy Source.
- If you check out a data project from CVS into an existing project in your workspace, you cannot open the Java source for JDBC or SQLJ stored Java stored procedures. To work around this problem, ensure that the project name remains the same, or use the Bind with Java class option in the routine editor for JDBC stored procedures.
- Running an IDS stored procedure with a boolean type parameter fails.
- If logging is not enabled on an IDS server, you will see an error: 'transaction not supported" when you run a stored procedure on that server.
- Arrays are only supported as the parameter type of a stored procedure on
DB2 for Linux, UNIX, and Windows Version 9.5.
- CURRENT SCHEMA is not automatically appended to the CURRENT PATH. You can
set the CURRENT PATH in the following places:
Data server |
SQL stored procedures |
Java stored procedures |
DB2 for z/OS |
- External: In the Bind Options field on the Deploy Options page of the New Stored Procedure wizard, append the keyword PATH(identifier).
- Native: In the Procedure Options field on the Configuration page of the routine editor, append the keyword SQL PATH identifier.
|
In the Bind Options field on the Deploy Options page of the New Stored Procedure wizard, append the keyword PATH(identifier). |
DB2 for Linux, UNIX, and Windows |
In the Current Schema field on the Routine Options page of the New Stored
Procedure wizard, set to the schema of the nested unqualified stored procedure. |
In the Current Schema field on the Routine Options page of the New Stored Procedure wizard, set to the schema of the nested unqualified stored procedure. |
- Delimited schemas, names, version IDs, JAR IDs, specific names, and parameter
names for stored procedures might cause problems with deployment or other
database actions, such as dropping. To work around this problem, use ordinary
SQL identifiers.
- There is an issue with SQL stored procedure profiling when you attempt
to profile an SQL stored procedure in the same connection session in which
the procedure was deployed. This issue occurs with SQL stored procedures
that target DB2 for Linux, UNIX, and Windows Versions 9.1, 9.1 FP2, and
1.1. To work around this issue, disconnect and re-connect to the database
before invoking the Run Profiling action on the stored procedure.
- The new content assist feature in the SQL editor is available only if you
right-click in the editor and select Use Database Connection, then select a connection.
- The parser may occasionally flag errors in the routine editor that are not actually errors. Saving the changes and deploying the stored procedure to the server should work. To hide parser error markers, right-click in the editor and select Hide Source Error Markers
- The parser might not recognize all of the SQL syntax for every data server, and can report invalid errors. The database server should always be used as the final arbiter of correctness. To avoid seeing invalid errors, you can turn off syntax checking in the SQL editor.
- When a template is added to the SQL editor or routines editor, it might
contain one or more elements (words) with rectangles around them. These
rectangles indicate a special editing mode where changes in one element
are reflected in other linked elements. The special editing mode will be
ended and the rectangles will disappear if you press the Esc key.
- SQL stored procedures must have a space between the label and the BEGIN
statement. for example,
P1: BEGIN
. If no space exists, stored procedure import and deployment will fail.
- On Linux, parameters cannot be seen in the Configuration tab of the routine
editor for Java stored procedures. To work around this problem, minimize
the Parameters section, then expand it again to view the parameters.
- If you import an SQL stored procedure that contains a COMMENT ON PROCEDURE
statement, the stored procedure will not import. To work around this problem,
remove the COMMENT ON PROCEDURE statement before importing the stored procedure.
- DB2 package names must be ten characters or less for SQLJ stored procedures
that target iSeries. If the name is longer, you will see an error in the
New Stored Procedure wizard.
- You can save native SQL, external SQL, and Java stored procedures with
errors in the stored procedure editor as long as the procedure signature
is not changed, For SQL (non-native), and Java stored procedures. the signature
is the procedure name plus the number and type of parameters. For native
SQL stored procedures, the version number is part of the procedure signature.
If you save a stored procedure that contains errors, a red line marker
is shown in the source.
- User-defined types (UDTs) are not supported as parameters for routines.
- Before you delete a data development project, close the open routines and SQL editors that belong to the project. If you do not close the open routines and SQL editors, the project and its contents will still be deleted, but you will see error messages.
- Visual Explain is not available for user-defined functions that target
DB2 for z/OS databases. The workbench only supports expressions, not statements,
for z/OS user-defined functions.
- When you first create a routine in a data development project, Visual Explain
options might not be available in the New Stored Procedure wizard, the
New User-Defined Function wizard, or the routine editor. To work around
this problem, close and reopen the workspace and the Visual Explain options
are enabled.
- In DB2 for z/OS v9, you may encounter run problems when the stored procedure
schema or name is delimited, (for example, "a.b.c"). This will
be addressed in a forthcoming PTF for DB2 for z/OS v9.
- If you drag and drop a stored procedure or UDF between unlike servers (for
example,from a DB2 UDB for Linux, UNIX, and Windows server to a DB2
UDB for z/OS server), you will see a warning during the drag and drop
operation about certain incompatibilities between the two servers. If you
continue with the operation and then try to open the stored procedure or
UDF, you might see an error, or the stored procedure might not be
usable.
- Running SQL Profiling against a DB2 UDB for Linux, UNIX, and Windows V8.2 server
may cause a null pointer exception if the server is missing the prerequisite
stored procedure (SYSIBM.SQLCAMESSAGECCSID) that is required by the JCC driver
to retrieve error message text. To work around this issue, you can create a
connection to the server without the retrieveMessagesFromServerOnGetMessage=true
setting.
- During monitoring of the execution of SQL procedures, profiling events
are generated for DML statements such as INSERT, SELECT, DELETE, and UPDATE
that are issued in the procedure. However, events are not generated in
a deterministic fashion for procedural statements for variable assignments
and control structures such as WHILE or IF. Therefore, tuning data will
not be captured for these procedural statements.
- Java stored procedure development
- If you create multiple Java stored procedures within the same JAR file on DB2 for z/OS Version 9, you cannot drop the stored procedures using the workbench.
- Support for developing Java stored procedures with outside JAR dependencies is only available in DB2 for z/OS Version 9 in new-function mode, not compatibility mode.
- When you create a Java stored procedure and change the method name, right-clicking in the editor and clicking Save does not work. To save the updated stored procedure, click File > Save.
- You might see a
cannot load class
error when you deploy or run Java stored procedures. This can happen if
there is a mismatch in JDK version between Data Studio and the DB2 server,
if the DB2 server is on a down-level JDK. To prevent this error, you should
specify the "-source 1.4" option in the Compile
options field of the Deploy Routines wizard when you are deploying Java stored procedures against servers that use a JDK level of 1.4. (for example, a DB2 Universal Database for Linux, UNIX, and Windows V8.2 server). In general, use the appropriate compilation option "-source JDK level " to match the JDK level on the database server.
- General routine deployment:
- If you deploy a stored procedure to a new schema, you cannot run the stored
procedure until you close and re-open the Data perspective.
- In the Deploy wizard when the target database is DB2 for z/OS or DB2 for
iSeries, you must specify the database server JRE version if it is not
1.4, which is the default. For DB2 for Linux, UNIX, and Windows, the database
server is queried by the workbench server for this information, but for
DB2 for z/OS or iSeries, this query is not usually successful.
- Calls to an unqualified procedure do not get resolved during deployment
if the specified schema qualifier is not the SQLID.
- When a nested stored procedure belongs to a different data project than
the calling stored procedure, you must deploy the nested stored procedure
separately from the calling stored procedure.
- When deploying a stored procedure or a user-defined function using the
Ant deployment feature, the following message might appear if you do not
have the tools.jar file located in your classpath:
Unable to locate tools.jar. Expected to find it in F:\jre\1.4.2\lib\tools.jar
. Ignore this message. tools.jar is part of the Java Runtime Environment
(JRE), not part of the Ant deployment feature.
- Deploying Java stored procedures with long names that target DB2 for Linux,
UNIX, or Windows Version 9.1 might fail. To work around this issue, shorten
the stored procedure name.
- To deploy Java stored procedures that target DB2 UDB for iSeries from the file
system by using Ant deploy, you must ensure that you have the jt400.jar in your
system classpath.
- You cannot deploy SQLJ stored procedures that target DB2 for Linux, UNIX,
and Windows from the file system by using Ant deploy if DB2 for Linux,
UNIX, and Windows is not installed.
- If you attempt to deploy an exported stored procedure by using the instructions in DeployInstructions.txt, you might get an error message that says :
...[createsp] Could not connect to the target database. [createsp]
com.ibm.db2.jcc.DB2Driver...
To work around this issue, ensure that db2jcc.jar and the appropriate license files are in your system classpath.
- When falling back to DB2 for z/OS V9 compatibility mode*, native SQL stored
procedures are not deployable, although the Deploy button is enabled.
- When deploying a SQL stored procedure against DB2 for z/OS Version 8 (new-function mode) or DB2 for z/OS Version 9 (compatibility mode), you should upgrade to the DSNTPSMP 1.21 release, which is the latest level to support deploying SQL stored procedures to a schema other than its connection user ID. If you do not migrate, you might see an authorization error when deploying a SQL stored procedure to a different target schema:
user-id SPECIFIED IS NOT ONE OF THE VALID AUTHORIZATION IDS. SQLCODE=-553, SQLSTATE=42503, DRIVER=xxxx
. To upgrade your DSNTPSMP, apply PK49647.
- Binary stored procedure deployment, general limitations:
- Stored procedures that were deployed using binaries cannot be redeployed.
- Stored procedures that were deployed using binaries must be dropped with
the RESTRICT option.
- Binary deployment for DB2 for Linux, UNIX, and Windows stored procedures:
- This feature is supported for SQL, JDBC and SQLJ stored procedures, targeting
Version 8.2 or Version 9.1 only.
- This feature is only supported if the target server is the same or higher
level DB2 version (for example: source is Version 8 -> target is Version
9, or Version 8 to Version 8).
- The connection to both the source and the target server must use the IBM
Data Server Driver for JDBC and SQL.
- The client JDK level must be compatible with the JDK level of the target server.
- Binary deployment from a 64-bit server to a 32-bit server and vice versa
is not supported.
- When the target schema is different from the source schema, a full build
is done, rather than a binary deployment.
- The source operating system must be the same as the target operating system.
That is, you cannot do a binary deployment of a stored procedure created
in DB2 for Linux, UNIX, and Windows on a Windows operating system to a
DB2 for Linux, UNIX, and Windows on a Linux operating system.
- Binary deployment for DB2 for z/OS stored procedures:
- This feature is supported for external SQL, JDBC and SQLJ stored procedures,
targeting Version 8 (new-function mode) and Version 9 only. Binary deployment
for native SQL stored procedures is only supported for Version 9.
- The connection to both the source and the target server must use the IBM Data Server Driver for JDBC and SQL.
- Target Load Library must exist before you deploy external SQL stored procedures using binaries.
- The client JDK level must be compatible with the JDK level in the DB2 for
z/OS server. JDK 1.4.2 is supported. JDK 1.5 is not supported.
- XML tooling
-
- In data development projects that target Informix Dynamic Server (IDS),
there is an Open With > Annotated XML Schema Editor menu action available from XML schema files. However, this action is not supported for IDS.
- If you select an XML schema to register for DB2 for Linux, UNIX, and Windows
Version 9, and that schema has been registered before in the same project,
sometimes the XML Schema Registration wizard shows an additional temporary
copy of the same schema in the schema list. You must manually delete the
temporary copy before you can complete registration.
- On DB2 for z/OS, when you register XML schemas with multiple documents,
all of the documents must use the same target namespace and must be connected
to each other. The XML tooling in the workbench does not check for this.
The XML Schema Registration wizard will complete, but the registration
will fail on the server.
- When editing or validating a table that contains an XML column with no
primary key, the table data editor only supports a simple xml namespace.
For example, xmlns="http://www.w3schools.com" is supported and
xmlns="http://www.w3schools.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.w3schools.com note.xsd" is not
supported.
- Stored procedure debugger
-
- Java stored procedures that were dragged and dropped from the Database Explorer to a data development project cannot be debugged until you open them in the routine editor to force the required classpath to be set up correctly.
- You cannot debug a Java stored procedure if it is called from a Native
SQL stored procedure on DB2 for z/OS.
- When working offline, you cannot debug stored procedures, although the
context menu is enabled.
- You cannot debug a nested stored procedure that targets DB2 for Linux, UNIX, and Windows Version 1.1.
- When you debug a stored procedure, you must use the session manager that
is included with Data Studio. To start the session manager and set Data
Studio preferences:
- Open a command window and change to the Data Studio directory. By default, Data Studio is installed in the
C:\Program Files\IBM\SDP70
directory on Windows.
- Run
db2dbgm.bat
from the command window, and note the IP address and the port number for the session manager.
- Start DWB and modify preferences for the debugger to use the local session
manager:
- Click Window > Preferences, expand the Run/Debug node, and click DB2 Stored Procedure Debugger.
- In the Debugger pane, select Use already running session manager.
- In the Host field, specify the IP address of the machine. You can also obtain the IP address from the command or terminal window where the session manager is running.
- In the Port field, specify the port for the local session manager. By default, the port number is 4554. You can also obtain the port number from the command or terminal window where the session manager is running.
- The following scenario results in a stored procedure that cannot be debugged:
Import a stored procedure that was created for DB2 for z/OS prior to Version
9, without a version statement, then deploy it to a DB2 for z/OS Version
9 database. In this case you cannot debug the stored procedure. To work
around this problem, copy the stored procedure from the Database Explorer
to the project and then debug it.
- The debugger skips over SET statements for SQL stored procedures.
- When you are connected to a UNIX DB2 server, timeout exceptions can occur when
you are adding breakpoints or running in debug mode.
- The debugger does not run for a stored procedure whose name contains both
English and Chinese characters.
- Watch expressions are only supported for dynamic Java stored procedures. They
are not supported for SQL and SQLJ stored procedures.
- The debugger does not stop at a breakpoint if it is not positioned at the first
token of an executable statement, such as SET. In addition, it does not stop on
DECLARE CONTINUE, CLOSE CURSOR, or ROLLBACK.
- If you are debugging a Java stored procedure and you select a Terminate action,
it might take several minutes for the debug session to fully terminate. New
debug sessions that are started during this time may behave erratically.
- When you are debugging a Java stored procedure that is called from another SQL or Java stored procedure, the Step Return button is disabled. To work around this problem, you can add a breakpoint at the next line of the calling stored procedure and then click Resume to get back to the calling stored procedure.
- When a Java stored procedure calls another stored procedure and then you step back into the Java stored procedure, you might stop in some intermediate Java code. To work around this problem, use the Step Return button to return to the Java stored procedure stack frame.
- If you get a
Timeout occurred while waiting for packet
error while you are debugging a Java stored procedure, try increasing the Java timeout setting. To increase the Java timeout setting, click Window >
Preferences from the workbench menu bar. Expand the
Java node and click Debug. On the Debug
preferences page, increase the Debugger timeout(ms) value in
the Communication timeout section. It is recommended that you
at least double the default value.
- When you are debugging a Java stored procedure, if you use the Change
Value action to modify a variable that has an empty string value, the
OK button in the edit dialog might not become enabled. To
enable the button, select the Input an evaluation radio button,
set the value to a non-empty string (for example, 'a'), and then select the
Input literal text radio button. The OK button
will then be available.
- If you do not see local variables when you are debugging a Java stored
procedure, the stored procedure might have been deployed without the -g compiler
option. Ensure that you specify the -g compiler option when you deploy Java
stored procedures.
- If you see an 'invalid stack frame' message in the Variables view, go to the
Debug view and click on the thread object above the stack frame and then click
on the stack frame. This should refresh the Variables view and the error should
no longer appear.
- When you are debugging an SQLJ stored procedure that is running on DB2 UDB for
iSeries V5 R4, the current line that is being executed will not correspond to
the indicated SQLJ source line displayed in the Debug view unless you have
applied an iSeries PTF that updates the linemap to correspond to the SQLJ source
instead of the Java source.
- Debugger preferences for session manager timeout are not recognized. These
preferences are set as follows: Click Window >
Preferences, expand the Run/Debug node, and click
DB2 Stored Procedure Debugger. Modify the Session
manager timeout in minutes field.
- The debugger cannot process a stored procedure that has large number of
variables on DB2 for Linux, UNIX, and Windows. The maximum number of variables
is 200.
- Cursor movement in a debug session: In some cases, when there is more than one
variable declaration in a procedure, you must click Step Into
or Step Over more than once in order to move to the next line.
For example, you must click twice on this line: DECLARE v_dept, v_actdept
CHAR(3); and three times on this line: DECLARE v_bonus, v_deptbonus, v_newbonus
DECIMAL(9,2); You must click a number of times equal to the number of variable
declarations.
- If you start a debug session for a Java stored procedure and add breakpoints,
then disable the breakpoints, the breakpoints are still enabled. To work around
this issue, when you start a new debug session, you should first remove all of
the old breakpoints and then add new breakpoints.
- In some cases when you are working with multiple data development projects, you
might see an error when you attempt to debug a stored procedure that says
"Unable to locate stored procedure PROCNAME. Procedure may have been deleted
from workspace" or "Source not found".
- If you are debugging a SQL stored procedure right after you terminate a
debug session of a Java stored procedure, the debugger might show "User
defined function ... has been interrupted by the user."&; To work
around this issue, try debugging the SQL stored procedure again.
- If you have two stored procedures in a project with the same name but a
different number or types of parameters, it is possible that when you debug
a stored procedure from the Data Project Explorer, the wrong stored procedure
will debug. To work around this problem, you must specify specific names
for the stored procedures. You can specify a specific name in the Deploy
Options page of the New Stored Procedure wizard, or on the Source tab of
the routine editor. You can also create a specific name by deploying the
stored procedure, deleting it from your data project, then copying it back
from the Database Explorer to the data project.
- Documentation corrections
The following information is not included in the information center or
is not correct:
- Accessibility
-
- On the Windows > Preferences > Validation page, the check box status
cannot be edited from the keyboard. You can set the defaults for WSDL and
XML schema validation using the plugin_customization.ini file.
- Visual Explain is a separate product that is launched from the workbench,
and is not part of the Data Studio product. The Visual Explain interface
might not be accessible.
- To copy a routine into a data project, open the routine in the routine
editor, and then save it into an existing or new data project. To copy
the routine into a data project with a different database connection, you
must open the routine into a project with same database connection first,
then deploy it to a different database.
- Routine editor: On the Configuration page, read-only fields cannot be read
by a screen reader. To obtain this information with a screen reader, read
the CREATE PROCEDURE statement on the Source page.
- Data Object editor:
- In the Preview DDL section you must highlight the text before a screen reader can read it.
- For a table, the rows on the Columns page cannot be read by a screen reader.
To work around this problem, highlight the table in the Database Explorer
and obtain the information from the Properties view, or open the Data Object
editor for the column.
- The check boxes for GRANT and WITH GRANT OPTION on the Privileges page
cannot be read by a screen reader. To work around this problem, type the
GRANT or REVOKE statement in the SQL editor and run the statements from
the SQL editor.
- Web services:
- To navigate the Web service tree in the Web Services Explorer, use the Tab key instead of the arrow keys.
- The status area in the Web Services Explorer cannot be read by a screen
reader. To work around this, use an external Web browser.
- To add a stored procedure from the Database Explorer to a Web service:
- For DB2 procedures, right-click the procedure from the Database Explorer and select Open, then select a project to open into. After the procedure is added to the project, right-click it in the Data Project Explorer and select Add to Web service.
- For procedures that are not supported for development in a data project,
create an operation under the Web service and type in a valid CALL statement
for the procedure.
- On the Web service Build and Deploy page, the deploy parameters table cannot
be edited by using the keyboard. If you want to change the default values,
you must modify the config.xml file instead. The following example describes
how to complete this task assuming a data project called 'Project1' and
a Web service called 'WebService1':
- From your {workspace directory name}\{project directory name}\DataServerWebServices\{webservice directory name}\.metadata directory, there are 2 .xml files that you will need to edit: config.xml and generatorConfig.xml.
- Using an editor, edit each of the files and in the add the following lines
of text. If the <dsc:properties> tag already exists, just add the
<dsc:property line for each property.
<dsc:properties>
<dsc:property name="myParm" value="myValue"/> <-- repeat this for each property that you want to add -- eg: name='foo' value='bar'
</dsc:properties>
- After you edit the files, click Refresh on the Web service's node to reload the contents.
- Build and Deploy the Web service.
Notices
This information was developed for
products and services offered in the U.S.A. IBM may not offer the
products, services, or features discussed in this document in other
countries. Consult your local IBM representative for information on the
products and services currently available in your area. Any reference to
an IBM product, program, or service is not intended to state or imply
that only that IBM product, program, or service may be used. Any
functionally equivalent product, program, or service that does not
infringe any IBM intellectual property right may be used instead.
However, it is the user's responsibility to evaluate and verify the
operation of any non-IBM product, program, or service.
IBM may have patents or pending
patent applications covering subject matter described in this document.
The furnishing of this document does not give you any license to these
patents. You can send license inquiries, in writing, to: IBM Director of
Licensing IBM Corporation 500 Columbus Avenue Thornwood, NY 10594
U.S.A.
The following paragraph does not
apply to the United Kingdom or any other country where such provisions
are inconsistent with local law:
INTERNATIONAL BUSINESS MACHINES
CORPORATION PROVIDES THIS PUBLICATION "AS IS" WITHOUT WARRANTY OF ANY
KIND, EITHER EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE
IMPLIED WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS FOR A
PARTICULAR PURPOSE. Some states do not allow disclaimer of express or
implied warranties in certain transactions, therefore, this statement
may not apply to you.
This information could include
technical inaccuracies or typographical errors. Changes are periodically
made to the information herein; these changes will be incorporated in
new editions of the publication. IBM may make improvements and/or
changes in the product(s) and/or the program(s) described in this
publication at any time without notice.
Any references in this publication
to non-IBM Web sites are provided for convenience only and do not in any
manner serve as an endorsement of those Web sites. The materials at
those Web sites are not part of the materials for this IBM product and
use of those Web sites is as your own risk.
Licensees of this program who wish
to have information about it for the purpose of enabling: (i) the
exchange of information between independently created programs and other
programs (including this one) and (ii) the mutual use of the information
which has been exchanged, should contact:
IBM Corporation
J46A/G4
555 Bailey Avenue
San Jose, CA 95141-1003
U.S.A.
Such information may be available,
subject to appropriate terms and conditions, including in some cases,
payment of a fee.
The licensed program described in
this information and all licensed material available for it are provided
by IBM under terms of the IBM Customer Agreement, IBM International
Program License Agreement, or any equivalent agreement between us.
Information concerning non-IBM
products was obtained from the suppliers of those products, their
published announcements or other publicly available sources. IBM has not
tested those products and cannot confirm the accuracy of performance,
compatibility or any other claims related to non-IBM products. Questions
on the capabilities of non-IBM products should be addressed to the
suppliers of those products.
All statements regarding IBM's
future direction or intent are subject to change or withdrawal without
notice, and represent goals and objectives only.
This information contains examples
of data and reports used in daily business operations. To illustrate
them as completely as possible, the examples include the names of
individuals, companies, brands, and products. All of these names are
fictitious and any similarity to the names and addresses used by an
actual business enterprise is entirely coincidental.
Trademarks
IBM, Derby, Rational, DB2 Universal Database, and zSeries are trademarks
or registered trademarks of the IBM corporation in the United States, other
countries, or both.
Java and all Java-based trademarks
are trademarks of Sun Microsystems, Inc. in the United States, other
countries, or both.
Linux is a copyright of Linus
Torvalds in the United States, other countries, or both.
Microsoft, Windows 2000, Windows 2003, and Windows XP are trademarks of
Microsoft Corporation in the United States, other countries, or both.
Other company, product or service
names may be the trademarks or service marks of others.