Release Notes^® - IBM Data Studio Version 1.1.1

• General
• Data Web Services
• pureQuery development
• Routine development
• Query development
• XML tooling

General

Request for a context menu in the Database Explorer to show or hide table row count decorators.

Request for support for statistics for nicknames.

SELECT COUNT(*) is being issued periodically on connections, causing performance problems.

Table row count should not query target table.

Data Web Services

When copying the JCC driver to a web server, the timestamp of the web service version of the driver should be checked. If the product version is newer, the user should be prompted before the product JCC driver is copied to the web server.

Creating an operation that executes a UDF fails when using a VALUES expression.

pureQuery development

When support for pureQuery is added to a Java™ project, the workbench opens the Database Explorer view, if that view is not already open.

Various menu options were changed to match their respective functions better.

If you make changes to the SampleUtil.java file, the workbench preserves your changes.

When you generate an SQL bean, the bean uses the default schema for the Java project.

Connection URLs that pureQuery generates in annotated and inline methods are now correct when JCC tracing is enabled.

SMALLINT columns are now mapped by default to the Java short type, rather than to the Java int type.

Routine development

If a long root package name (8 or 10 chars) is specified on the config page of a Java stored procedure then there is a silent failure on deploy.

The product hangs if a stored procedure is run with profiling and the action takes a long time.

Request to be able to specify multiple fragment files on the Fragments page of the New Stored Procedure wizard.

The OK button is disabled on the Parameter page of the New Stored Procedure wizard for all data sources when the parameter name is over 18 characters. This behavior should only occur for DB2 for z/OS Version 7 stored procedures.

In the routine editor, the threadsafe option on the Configuration page is not reflected on the Source page.

Adding STAY RESIDENT YES to a stored procedure causes a deploy error.

Cannot save over (replace) an existing version of a stored procedure that is in a data development project.

Query development

The product crashes when working with a large number of tables in the SQL builder.

Request support for "?n" parameter marker in code assist.

Parser reports errors for DML OLAP expression on DB2 for z/OS.

XML tooling

The XML editor format action is disabled when the document has no project association.

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.

• Rational^® Data Architect Version 7.0.0.4
• Rational Application Developer Version 7.0.0.5
• Rational Software Architect Version 7.0.0.5

• 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.1 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 Version 1.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.

1. 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.)
2. On the Start page of Installation Manager, click the Update Packages button.
3. 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.
4. 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.
5. By default, recommended updates are displayed and selected on the Update Packages page. Ensure that you select Version 1.1.1 for Data Studio, then click Next.
6. 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.
   1. If you agree to the terms for all of the license agreements, click I accept the terms of the license agreements.
   2. Click Next to continue.
   3. On the Summary page, review the information displayed, and then click Update. A progress indicator shows the percentage of the installation completed.
7. 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.
8. For Windows Vista: Refer to Starting IBM Data Studio Version 1.1.1 on a Windows Vista computer.

1. Download the fix pack from http://download.boulder.ibm.com/ibmdl/pub/software/data/studio/unwarranted/11/111/zips/datastudio_unwarranted-1.1.1.zip.
2. Extract the compressed file in an appropriate directory. For example, extract the file to C:\temp.
3. Add the fix pack repository location in Installation Manager:
   1. Start Installation Manager.
   2. On the Start page of Installation Manager, click File > Preferences, and then click Repositories.

      The Repositories page opens.

   3. On the Repositories page, click Add Repository.
   4. In the Add repository window, browse to and enter the path to the repository.config file. For example, enter C:\temp\ds1fp1\repository.config and then click OK.
   5. Click OK to close the Preferences page.
4. 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 IBM Data Studio Version 1.1.0. If you installed the product from CDs or other media, they must be available when you use the update feature.

• 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 Rational Software Development Platform products (regardless of their installation location) and select shared resources directory and installation locations that are not in the path C:\Program Files.

• Installation
• General
• Data Web Services
• pureQuery
• Data object create and alter
• Routine development
• Query development
• Stored procedure debugger
• XML tooling
• Documentation corrections
• Accessibility

• 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, or 7.0.0.3 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.4, which is compatible with Data Studio.
• If you install Data Studio into a shared package group with Rational Application Developer Version 7.0.0.5 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 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 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.

• 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 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.

• If the number of columns in a table is greater than 255 and you want to insert a row by means of a pureQuery method, you must use a bean to pass the values for those columns. You cannot use parameters because of restrictions in the Java Virtual Machine.
• 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.
• If you are using the annotated method programming style, a generated implementation class does not compile if both of these conditions are true:
  • One or more of the annotated methods in your interface contain parameters that are subclasses of the Map interface.
  • Your interface does not import the Map interface.
  The workaround is to add the statement import java.util.Map; to your interface and generate the implementation class again.
• If you are using the annotated method programming style and none of the methods in an interface use annotations or if the interface contains no methods, the pureQuery Generator throws a null pointer exception when you try to generate an implementation of the interface. The workaround is to make sure you annotate at least one method in an interface.

• 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.
• 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.
• 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 Data Object 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.
• 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:
  • Incomplete
  • Check Pending
• 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.

• 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:
  1. 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.
  2. 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).
  3. 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 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.
  • 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.
  • 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.

• 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.

• 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:
  1. 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.
  2. Run db2dbgm.bat from the command window, and note the IP address and the port number for the session manager.
  3. Start the workbench and modify preferences for the debugger to use the local session manager:
     1. Click Window > Preferences, expand the Run/Debug node, and click DB2 Stored Procedure Debugger.
     2. In the Debugger pane, select Use already running session manager.
     3. 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.
     4. 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 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 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.

• 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 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.
• 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.

• 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 Value Distributions > Multivariate. 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.
• In addition to the data objects that are listed in the topic called: "Database statistics support", you can also update and view statistics for materialized query tables (MQTs) for DB2 for Linux, UNIX, and Windows, and DB2 for z/OS.
• 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.
• There is an undocumented Volatile check box on the General page in the Properties view for DB2 for z/OS tables. If this option is selected, index access will be used on this table whenever possible for SQL operations. Be aware that list prefetch and certain other optimization techniques are disabled when VOLATILE is used.
• 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.
• In the Deploy Routines wizard, the string that used to be named Current schema for unqualified data objects is now renamed to Target Schema for deploying an unqualified routine. The information center has references to the old string name.

• 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.
• 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':
    1. 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.
    2. 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>

    3. After you edit the files, click Refresh on the Web service's node to reload the contents.
    4. Build and deploy the Web service.

IBM Corporation
J46A/G4
555 Bailey Avenue
San Jose, CA 95141-1003
U.S.A.

Notes
IBM
DB2
z/OS
iSeries
Informix
Rational
WebSphere
DB2 Universal Database