New in this release
This
release adds support
in the pureQuery runtime from
annotated methods for the RowHandler interface. It also adds two new interfaces:
- ParameterHandler
- Use this interface to pass values into an SQL statement before that statement
is run.
- CallHandlerWithParameters
- Use this interface when you want to map the results of a call to an SQL
stored procedure to an object of type <T>. With this interface, you can
also update the values of the OUT and INOUT parameters in the pureQuery bean
or Map object that you used to pass values to the CALL statement.
This release provides
an integrated Visual Explain viewer that can be used to tune queries that
target the following new database servers:
- DB2® for Linux®, UNIX®,
and Windows® Version
9.5 Fixpack 1
- DB2 for z/OS® Version
8 (new-function mode)
There is new support for viewing
and updating statistics for nicknames. Refer to the product documentation for a list of other data objects
that are supported for statistics.
There is a new
toolbar icon that allows you to launch the Data Studio Administration Console
in a web browser. You must install the Data Studio Administration Console
before you can use this feature.
The Data Web Services tooling now supports
DB2 for z/OS Version 7 as a datasource for Web services.
Problems fixed in this release
- General
- Synonyms for Informix databases are not displayed in the Database Explorer.
- pureQuery development
- pureQuery incorrectly identified the isolation level for bound packages
- This problem caused an incorrect isolation level to be used, or a "Package
not found" condition. The latter usually appears as SQLCODE -805.
- pureQuery generated long package names by default for DB2 for z/OS Version
8 (Compatibility Mode) data servers
- For data servers that cannot accept names more than 8 characters long
for packages, the generator truncates the root package name that you specify
when you generate the implementation for an interface that defines annotated
methods. The generator was not truncating the root package name for DB2 for z/OS Version
8 (Compatibility Mode) data servers.
- Multi-row INSERT fails when run as static SQL against DB2 for z/OS
- When running an INSERT statement using an annotated method that takes
a collection of objects as input, pureQuery uses batch processing and, if
possible, the data server's capability for performing multi-row inserts. Even
if such an INSERT statement used the correct multi-row syntax when bound into
a package, pureQuery did not build the correct command sequence when running
the statement. This problem resulted in the underlying JDBC driver returning
ERRORCODE -4228.
- SQLCODE -104 error during the bind of a package containing a SELECT...INTO
statement with a column function
- When executing in static SQL, pureQuery uses a SELECT...INTO statement
(singleton SELECT) if it detects that the application can receive a result
set of only one row. Including column functions in the set of returned values
for the statement could cause an SQLCODE -104 error during the bind of the
package that contained the statement.
- Routine development
- Cannot create a new version of a native stored procedure from the Database
Explorer.
- Exception when trying to create new stored procedure from the Database
Explorer using the Routine editor.
- Tooling does not rebuild a routine that targets DB2 for z/OS with a new
a new build owner if the "REBUILD" (DSNTPSMP "REBUILD") function is invoked.
- Cannot redeploy a Java stored procedure to a DB2 for Linux, UNIX, and
Windows UTF-8 database after changes are made to the stored procedure.
- Query development
- Run SQL result in the Data Output view does not display BLOB data > 2000
bytes.
- XML tooling
- 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.
Hardware and software requirements
- Data server support
- DB2 for Linux, UNIX,
and Windows Versions
8, 9, and 9.5
- 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
- Hardware requirements
- Disk space
- Requirements vary depending on which optional features are installed.
- On Windows: 113MB
- On Linux:
112MB
- Memory
- 1GB (2GB recommended)
- Processor speed
- Recommended 2GHz or higher
- Software requirements
- Supported operating systems
- Windows Vista
Business, Enterprise, Ultimate (tested on Intel® Pentium® D CPU)
- Windows 2000
Professional x86-32 (SP3 and SP4)
- Windows 2000
SP4 Advanced Server x86-32
- Windows Server
2000 x86-32 (SP3 and SP4)
- Windows Server
2003 Enterprise Edition x86-32 (SP1) (tested on AMD Opteron Processor 246)
- Windows Server
2003 Enterprise Edition x86-64 (Run in 32-bit mode) (SP1) (tested on AMD Opteron
Processor 250)
- 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 and SP2) (tested on Intel Pentium M)
- Windows XP
Professional x86-64 (GA, SP1 and 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 (tested at Kernel level:
2.6.9-55.0.9.ELsmp #1 SMP i686 athlon i386 GNU/Linux)
- Red Hat Enterprise Linux (RHEL) 5.0 AS/ES x86-32
- SuSE Linux (SLES)
9.0 Enterprise Server (SP1 - SP4) x86-32 (tested at Kernel level: 2.6.5-7.286-bigsmp
#1 SMP i686 athlon i386 GNU/Linux)
- SuSE Linux (SLES)
10.0 Enterprise Server (running in 32-bit mode)
- JRE requirements
- If you are installing into an existing Eclipse 3.2.2 IDE, the Eclipse
IDE must use JRE 1.5. This JRE is bundled with the Eclipse 3.2.2 IDE that
ships with this product.
Compatibility
with other products
IBM Data Studio Version 1.1.2 can share a package group
with other compatible products that have been installed with IBM Installation Manager.
Before
you can share a package group with Rational Software Architect or Rational
Application Developer, you must upgrade to IBM Installation Manager Version
1.1.1. See the installation limitations for
additional information.
The following products have been tested with
IBM Data Studio Version
1.1.2.
Other products have not been tested and you should not attempt to install
IBM Data Studio Version
1.1.2 with
them in a shared package group.
- Rational® Data
Architect Version 7.0.0.5
- Rational Application
Developer Version 7.0.0.6
- Rational Software
Architect Version 7.0.0.6
For more information about shared package groups,
see the section called "Package groups and the shared resource directory"
in the IBM Data Studio Installation
Guide.
If you have installed other Eclipse 3.2.2 based products
using a mechanism other than IBM Installation Manager,
you can attempt to install IBM Data Studio Version 1.1.2 into the same Eclipse
IDE, using the Extend an existing Eclipse IDE option
in IBM Installation Manager. However,
be aware that this type of installation will fail if the Eclipse environment
is not compatible with IBM Data Studio Version 1.1.2.
See the IBM Data Studio support page for
additional technotes about extending an Eclipse 3.2.2 IDE.
Installing IBM Data Studio Version 1.1.2
If
you have any questions about the use of your Data Studio product, please post
in the forum: http://www.ibm.com/developerworks/forums/dw_forum.jsp?forum=1086&cat=19.
The following are the two common ways to install
this update:
- If IBM Data Studio Version 1.1.0 is already
installed on your system, then you can install this update by using the Update
Packages wizard in IBM Installation Manager.
For general instructions on installing an update, refer to the section "Updating Data Studio" in the Installation
Guide. For detailed instructions, see Installing IBM Data Studio Version 1.1.2 as an update.
- If you are installing IBM Data Studio Version 1.1.0 for the first
time or if you are installing an additional instance of it, then you can install
this update at the same time that you install IBM Data Studio Version1.1.0 by clicking Check
for updates on the Install page of the Installing Packages wizard
in IBM Installation Manager. For
general instructions on installing an update, refer to the section "Installing Data Studio using the IBM Installation Manager graphical interface"
in the Installation Guide.
By default, the update is installed directly
from the IBM update
repository; however, it is also available for download. For details, see the
specific installation instructions noted below.
Installing IBM Data Studio Version 1.1.2 as an update
Install
the fix pack with the same user account that installed the product.
Restriction: - IBM Data Studio Version 1.1.1 or 1.1.0 must be installed.
- The product cannot be open when you install this fix pack. Also, ensure
that all browsers and other applications opened by your Data Studio product
are closed.
To find and install
IBM Data Studio Version
1.1.2 as an update:
- Open IBM Installation Manager.
Note: On Windows Vista,
You must run Installation Manager as
administrator. (Right-click the program shortcut and click Run
as administrator.)
- On the Start page of Installation Manager,
click the Update Packages button.
- If a new version of Installation Manager is
found, you are prompted to confirm that you want to install it before you
can continue. Click OK to proceed. Installation Manager automatically
installs the new version, stops, restarts, and resumes.
- In the Update packages wizard, select the Installed Location for Data Studio and
click Next. Installation Manager searches
for updates in the Data Studio repository
on the Web, as well as any repository locations that you entered. A progress
indicator shows that the search is taking place.
- By default, recommended updates are displayed and selected on the Update
Packages page. Ensure that you select Version 1.1.2 for Data Studio , then click Next.
- On the Licenses page, read the license agreements for the update. On the
left side of the License page; click each item to display the license agreement
text.
- If you agree to the terms for all of the license agreements, click I
accept the terms of the license agreements.
- Click Next to continue.
- On the Summary page, review the information displayed, and then click Update.
A progress indicator shows the percentage of the installation completed.
- When the update process completes, a message that confirms the success
of the process is displayed near the top of the page. Click View
log file to open the log file for the current session in a new
window. You must close the Installation Log window to continue.
- For Windows Vista: Refer to Starting IBM Data Studio Version 1.1.2 on a Windows Vista computer.
Downloading the fix pack for local installations
You
can download a compressed file containing IBM Data Studio Version 1.1.2 update; the files that
you extract from the compressed file form a repository for the fix pack. You
can install the fix pack from the repository on your own computer, or you
can copy the repository to a shared drive or an HTTP or HTTPS server.
To
download and install the fix pack, complete the following steps:
- Download the fix pack from http://download.boulder.ibm.com/ibmdl/pub/software/data/studio/unwarranted/11/112/zips/datastudio_unwarranted-1.1.2.zip .
- Extract the compressed file in an appropriate directory. For example,
extract the file to C:\temp.
- Add the fix pack repository location in Installation Manager:
- Start Installation Manager.
- On the Start page of Installation Manager,
click , and then click Repositories.
The
Repositories page opens.
- On the Repositories page, click Add Repository.
- In the Add repository window, browse to and enter the path to the repository.config
file. For example, enter C:\temp\ds1fp2\repository.config and
then click OK.
- Click OK to close the Preferences page.
- Install the fix pack as described previously in this document.
Note: During
the process of updating from a local repository, Installation Manager might
prompt you for the location of the repository for previously installed versions
of IBM Data Studio . If you previously
installed the product from CDs or other media, they must be available when
you use the update feature.
Installing IBM Data Studio Version 1.1.2 for
the first time on a Windows Vista computer
The
steps for Installing IBM 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.
Note: - 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.
If
the launchpad program starts automatically (for example, if you are installing
from a CD), stop the launchpad program and then restart it using the Run
as administrator command; navigate to the CD or disk image, right-click
on setup.exe (at the top level of the CD or image) and click Run
as Administrator.
- Selecting installation directories within the Program Files directory
(typically C:\Program Files) is not recommended.
If you select either an
installation location or shared resources directory within the Program Files
directory, then the packages that you install must be run as administrator.
See Starting IBM Data Studio Version 1.1.2 on a Windows Vista computer for details.
Starting IBM Data Studio Version 1.1.2 on
a Windows Vista
computer
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.
On Windows Vista,
the Program Files directory is virtualized in order to
allow users who are not running as the administrator to have write access
to this protected directory. However, the virtualization workaround is not
compatible with Data Studio .
If
you selected an installation location or shared resources directory in the
path
C:\Program Files and you do not want to require
running
Data Studio as
administrator, then do one of the following steps:
- If you selected an installation location in a directory in the path C:\Program
Files, then reinstall Data Studio and
any other programs sharing the same installation location) and select an installation
location that is not in the path C:\Program Files.
- If you selected a shared resources directory in the path C:\Program
Files, then reinstall Data Studio and
all other products that were installed using Installation Manager (regardless
of their installation location) and select shared resources directory and
installation locations that are not in the path C:\Program Files.
Known problems
This product is
based on Eclipse 3.2.2. Refer to the Eclipse 3.2.2 release notes for information about
additional known problems with the Eclipse 3.2.2 IDE.
This
release contains the following known limitations and problems:
InstallationNote: For
additional information about known problems for Installation Manager, see
the Installation Manager release notes. To access the release notes for Windows,
click , and then click Release
Notes. For Linux, go to the documentation directory in your product
installation location, and open the file, readme.html.
- Do not attempt to install the product on operating systems other than
those that are listed. Installation Manager might run on other operating systems,
but the installation can fail. If installation fails, uninstallation sometimes
also fails.
- Installation Manager allows a shared installation with Rational Data
Architect Version 7.0, 7.0.0.1, 7.0.0.2, 7.0.0.3, or 7.0.0.4 using a shared
package group, but these Rational Data Architect Versions are not compatible
with Data Studio. If you attempt
this shared installation, your Rational Data Architect product can
become unusable. To work around this issue, install Rational Data Architect Version 7.0.0.5,
which is compatible with Data Studio.
- If you install Data Studio Version 1.1.2 into a shared package
group with Rational Application Developer Version 7.0.0.6 and
then uninstall (rollback) Data Studio and
Rational Application Developer, you might see errors with the uninstallation,
and some files might not be properly uninstalled. To work around this problem,
complete the IBM Installation Manager uninstallation
and then manually delete the shared installation directories (for example, C:\Program
Files\IBM\SDP70 and C:\Program Files\IBM\SDP70Shared).
- If you install Data Studio Version 1.1.2 into a shared package
group with Rational Software Architect Version 7.0.0.6 or Rational Application
Developer Version 7.0.0.6, you might not be able to roll back Data Studio Version 1.1.2 to a previous version.
To work around this problem, you can uninstall Data Studio Version 1.1.2.
- If you install more than one product into a shared package group, when
you launch the product, the splash screen of the first installed product is
always launched. However, after the splash screen, the correct product opens.
- You might experience problems installing Data Studio on
the Windows XP
Professional x86-64 (GA, SP1 & SP2) (Run in 32-bit mode) operating system
on the Intel Core
2 Quad processor.
General- 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 displays the date
at which statistics were last collected for a table, and the number of rows
in the table at that time. If statistics are not found for a table for which
the product supports statistics, a value of "-1" will be displayed instead.
For a table for which the product does not support statistics, no table row
count decorator is displayed.
- 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.
- The pre-populated DB2 alias connections that appear automatically in the
Database Explorer are not editable because the connection information is pulled
from the DB2 client
configuration. Only user created connections are editable.
Data
Web Services- Data Web Services supports the following Web servers:
- Apache Tomcat v5.5
- WebSphere® Application
Server (WAS) v6.nn
- WebSphere Application
Server Community Edition (WASCE) v1.1.0.2
Later versions of these Web servers might not work and are not officially
supported
- 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. For more information, refer to this technote: http://www-1.ibm.com/support/docview.wss?uid=swg21287541.
- If you are using WebSphere Application Server for Web services that
access Informix databases,
enable logging for those databases. Otherwise, your applications can receive
the following error message: Error 500: java.sql.SQLException: No Transaction
Isolation on non-logging db'sDSRA0010E: SQL State = IX000, Error Code = -79,746
- If WebSphere Application
Server Community Edition (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.
- 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.
- When you are installing the WASCE Eclipse plugin on Linux, you need
to start Data Studio as root.
- 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.
- An exception occurs when you are generating a default XSD schema on an
incorrect operation. To work around this problem, go back and click Parse 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 by 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- You cannot pass arguments to a JUnit test case.
Therefore, you must include in the test case the URL, user ID, and password
for connecting to the database. Workaround: After you generate JUnit
test cases, you must update them to include information that is required for
connecting to a database.
- If you did not select the Include connection information check
box when you generated the test case, the code for the test case contains
this line:
conn = SampleUtil.getConnection ("connectionUrl", "userID", "password");
Update
the arguments in the getConnection() method with the URL, user ID, and password
that you want to use.
- If you did select the Include connection information check
box when you generated the test case, the code for the test case contains
this line, with the URL and user ID matching those for your environment:
conn = SampleUtil.getConnection ("jdbc:db2://MYlocalhost:MYPORT/MYTESTDB", "MYUSERID", "password");
Supply the password in the getConnection() method.
You can now run the application.
Routine
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 Version
9 for Linux, UNIX,
and Windows.
- 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.
- 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.
- 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.
- 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.
- 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 9.5. To work around this issue, disconnect and re-connect
to the database before invoking the Run Profiling action
on the stored procedure.
- 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 .
- 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.
- 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.
- 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.
- 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 and target is Version 9, or source is Version
8 and target is Version 8).
- The connection to both the source and the target server must use the IBM Data
Server Driver for JDBC and SQLJ.
- 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 SQLJ.
- Target Load Library must exist before you deploy external SQL stored procedures
using binaries.
- You might see a "cannot load class" error when you binary deploy Java
stored procedures if there is a mismatch in JDK version between Data Studio
and the DB2 server, and 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 deploy 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.
Query
development- The 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 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, 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.
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
9.5.
- When you debug a stored procedure, you must use the session manager that
is included with the product. To start the session manager and set preferences:
- Open a command window and change to the product installation directory.
By default, the product 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 the workbench and modify preferences for the debugger to use the
local session manager:
- Click , 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 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.
- 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.
- 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.
- The debugger skips over SET statements for SQL stored procedures.
- 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.
- Watch expressions are only supported for dynamic Java stored
procedures. They are not supported for SQL and SQLJ stored procedures.
- 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 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 Input an evaluation, set
the value to a non-empty string (for example, 'a'), and then select Input
literal text. 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.
- 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.
- 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.
- 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.
- Debugger preferences for session manager timeout are not recognized.
- 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.
- 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 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.
XML
tooling- In data development projects that target Informix Dynamic Server (IDS), there
is an menu action available from XML schema
files. However, this action is not supported for IDS.
- If you insert XML documents larger than 100MB into an XML column from
the Data Project Explorer, you might experience performance problems or the
product might close.
- 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.
Documentation
corrections- There is no documentation for the multivariate value distributions feature.
With this feature you can visually explore value distributions and relationships
between the columns of a table. For each field in the table, a chart is displayed
that shows the distribution of the values in the chart. Additionally to the
chart, a table shows simple statistics, for example, the number of NULL values
or the number of different values. To use this feature, In the Database Explorer,
right-click the name of a table and select . The Multivariate Distributions
view is displayed, which shows a table that includes statistics for each field
of the database table being explored. Below the table, the appropriate value
distribution charts are displayed. Select different fields in the table to
display the value distributions of the selected fields in the charts below
the table.
- Creation of auxiliary tables is supported for DB2 for z/OS using the Data Object editor, but
auxiliary table properties are not documented in the information center. You
can modify the table space, base table and column for an auxiliary table.
You can specify the base table as a qualifier in the Column field.
- For IDS triggers, you can only specify one trigger event on the General
tab. The following fields are disabled for IDS triggers: Action
time and Granularity on the General page
and When clause on the Details page.
- There are references in the information center to the WITH OPTION field
in the Data Object editor for privilege management. This field has been changed
to WITH GRANT OPTION or WITH ADMIN OPTION, depending on your target data server.
Accessibility- On the 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.
- For data servers other than DB2 for z/OS V9, Visual Explain is a separate product
that is launched from the workbench, and is not part of the 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.
- Data 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 tag already exists, just add the <dsc: property> line
for each property:
<dsc:properties>
<dsc:property name="myParm" value="myValue"></dsc:property> <-- 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 grant you any license
to these patents. You can send license inquiries, in writing, to:IBM Director
of Licensing IBM Corporation
North Castle Drive Armonk, NY 10504-1785 U.S.A.
For license inquiries regarding double-byte (DBCS) information,
contact the IBM Intellectual
Property Department in your country or send inquiries, in writing, to:IBM
World Trade Asia Corporation Licensing 2-31 Roppongi 3-chome, Minato-ku Tokyo
106-0032, Japan
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 information 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 at your own risk.
IBM may
use or distribute any of the information you supply in any way it believes
appropriate without incurring any obligation to you.
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 document 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.
COPYRIGHT LICENSE:
This information contains sample application programs
in source language, which illustrate programming techniques on various operating
platforms. You may copy, modify, and distribute these sample programs in any
form without payment to IBM, for the purposes of developing, using, marketing
or distributing application programs conforming to the application programming
interface for the operating platform for which the sample programs are written.
These examples have not been thoroughly tested under all conditions. IBM, therefore,
cannot guarantee or imply reliability, serviceability, or function of these
programs.
Trademarks
The following terms are
trademarks of International Business Machines Corporation in the United States,
other countries, or both:
IBM
DB2
z/OS
iSeries
Informix
Rational
WebSphere
DB2 Universal Database
Intel, Intel logo, Intel Inside, Intel Inside logo, Intel Centrino, Intel
Centrino logo, Celeron, Intel Xeon, Intel SpeedStep, Itanium, and Pentium
are trademarks or registered trademarks of Intel Corporation or its subsidiaries
in the United States and other countries.
Microsoft,
Windows, Windows NT, and the Windows logo are trademarks of Microsoft 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 registered trademark of Linus Torvalds in the United States, other countries,
or both.
UNIX
is a registered trademark of The Open Group in the United States and other
countries.
Other company,
product, or service names may be trademarks or service marks of others.