Relational database tools - release notes

1.0 Introduction
2.0 Supported software and specifications
3.0 Limitations
   3.1 Must be online to generate Java Bean or EJB method from a stored procedure
4.0 Known problems
   4.1 Database models
   4.2 SQL queries
   4.3 Database connections
   4.4 SQL DDL
   4.5 Error reporting
   4.6 Running sql statements creates incorrect tables in Oracle
   4.7 Data Export
   4.8 Renaming folders in database project
   4.9 SQL Comments
   4.10 Check in and check out SQL Statements before opening them in the SQL Builder
   4.11 Do not immediately add newly created SQL Statements to version control
   4.12 Editing Java stored procedures
   4.13 DBCS data gets truncated when connecting to DB2/390 (CP930)
   4.14 Changes lost when closing editor of read-only document
   4.15 Schema object identifiers missing quotes for non-english locales
   4.16 Connecting to a DB2 server
   4.17 Dropping Java stored procedures from DB2 Version 8 is not supported when using DB2 Version 7 client
   4.18 Manually changing the returned SQL data types for DB2 table UDFs
   4.19 Problems with Sequelink 5.1 shipped with WebSphere Studio
   4.20 DataDirect Sequelink 5.3 Driver
   4.21 WebSphere Application Server - Express examples SQL files may contain errors but can be executed successfully
   4.22 Problems retrieving tables from Microsoft SQL Server
   4.23 Problems connecting to Cloudscape 5.1
   4.24 Error message while moving/renaming/deleting JDBC jar file
   4.25 Duplicate schema nodes will appear when importing, deleting, or re-importing EJB JAR files
   4.26 Corrupted Arabic error messages during relational data export

1.0 Introduction

The relational database tools in WebSphere Studio allow you to connect to databases, browse or import database designs, design new databases using wizards, export new designs to your database, create and execute SQL queries using the SQL Builder, create and execute DB2 stored procedures as well as DB2 user-defined functions. The relational database tools are accessed through the Data perspective Data Definition and DB Servers views.

2.0 Supported software and specifications

The relational database tools support connecting to and importing from the following database types:

SQL support in the SQL wizard and SQL query builder is dependent on the level of support provided by your database vendor.

Fullselect support:

Joins support: Grouping query results support: Cast expression support:

On Linux, you may experience a crash when connecting to DB2 Universal Database for iSeries V5R1 using the AS/400 Toolbox for Java JDBC Driver. The problem occurs when you enter an incorrect password or leave the password field blank. Should this happen, restart the product and connect again using the correct password.

3.0 Limitations

3.1 Must be online to generate Java Bean or EJB method from a stored procedure

To use the wizards that enable you to generate a Java Bean or generate an EJB method from a Stored Procedure, you must be working online.

4.0 Known problems

4.1 Database models

  1. There is currently no support for modeling indexes, check and unique constraints, triggers, structured types, or identity columns.
  2. The relational database tools do not support check options on views.

4.2 SQL queries

  1. Unnecessary brackets in query statements will cause parser errors.
  2. The use of parentheses in query WHERE condition clauses is currently unsupported.
  3. Large SQL files may slow down the editor.
  4. For Oracle, update statements must assign values to columns individually without the use of bracketed groups. Add columns individually and to add a value using a subquery, use the "Build Expression" option in the Value table cell combobox to create a query expression for the column.
  5. Modifications to database objects used by a statement may cause the SQL Builder to fail because it can no longer parse the statement successfully against the local data model.
  6. Editing a view and then opening a statement which contains the view may cause unexpected workbench shutdown.
  7. If a database connection is forced off outside of WebSphere Studio connection will not be prompted on statement execution. Re-import the database to the project from the DB Servers view to restore the connection.
  8. A query cannot be created using a view that is derived from a user-defined function.
  9. Drag and drop of database objects is only supported from the Data Definition view not the DB Servers view.

4.3 Database connections

  1. If you import a DB2 UDB or DB2 for iSeries table with a column of type DATALINK, the link control options for the column will not be imported. The DATALINK length will default to 200, and the link options will default to NO LINK CONTROL. You can update these properties in the table editor after importing the table to the Data View.
  2. Removing multiple connections in the DB Servers view does not always work. If you encounter such a problem, you can either keep your connections or proceed to remove them one at a time.
  3. Character string data types may not be imported properly for connections to iSeries servers. The types CHAR FOR BIT DATA, VARCHAR FOR BIT DATA, and LONG VARCHAR FOR BIT DATA may be imported without the FOR BIT DATA options. In these cases, use the table editor in the Data perspective to edit the affected columns.
  4. Schema object names that contain DBCS characters may not be imported properly from iSeries. Delimited identifiers may be missing quotes. In these cases, use the table editor to edit the column and constraint names.
  5. Identifiers for primary key constraints are not imported for OS/390 connections.
  6. Column default values that are stored procedures, functions, or defined with an identity clause are not imported properly and can result in non-ASCII characters in a table document. The non-ASCII characters cause problems while loading the document. When this happens it is necessary to hand edit the problem tblxmi document and remove the default tag and value.
  7. When using the WebSphere Connect JDBC driver for connections to a SQL Server database, you typically need 3 jar files to specify the class location. We recommend that you combine these jar files into a single jar file. Otherwise, when the wizard is reopened after making a connection, it will be abnormally large. This is due to the length of the Driver location field.
  8. When creating a new database connection from a DB2 Version 8 client, if you want to use the IBM DB2 NET DRIVER JDBC Driver, you must add these two JAR files to the Class location field: <DB2HOME>/java/db2jcc.jar and <DB2HOME>/java/common.jar (where <DB2HOME> is the directory where you have installed DB2). If you do not do so, you will get an error. Note: If you use the New Database Connection Wizard without adding these JAR files to your class location, the stored procedures will not be loaded. You must bring up the connection wizard again to enter the files in the Class location field.
  9. Schema and table meta-data may not be imported when using a DB2 UDB Version 8 client to connect to a Version 7 or earlier of DB2 on z/OS. To fix the problem, install DB2 for z/OS PTF UQ72081.

4.4 SQL DDL

  1. You may encounter problems with importing or editing involving the Oracle long datatype.
  2. Extremely large SQL DDL files may slow down the editor.
  3. Extremely large SQL DDL files may cause out of memory exceptions during parsing or execution.
  4. Editing of Oracle scripts may display errors in the outline view of the editor. For example "CREATE SEQUENCE", "CREATE OR REPLACE TRIGGER", and "END" statements will show up as erroneous. However, you can still proceed to export these items to an Oracle server.

4.5 Error reporting

When you encounter an error during parsing of an SQL file, generally the problem is with the next token after the one reported by the error message. The error message is also displayed in the task list.

4.6 Running sql statements creates incorrect tables in Oracle

When a sql file is run on the Oracle database server, it is necessary to initially select to create the tables sequences and triggers. This is because WebSphere incorrectly interacts with Oracle by inserting extra spaces. Once the tables have been created, go to the Oracle server machine and edit the statement to take out the inserted spaces and add the command 'end;'. Then go back to WebSphere and run the sql statement on the server again, this time selecting the Alter statements and the insert statements.

4.7 Data Export

  1. You can only drop existing artifacts in the back-end database/catalog by running Run on Server on SQL DDL scripts. On Data objects (for example, database, schema, table) if you wish to drop the existing objects, you should select Generate DDL from the pop-up menu, specifying to drop existing statements. On the generated SQL DDL script, select Run on Server from the pop-up menu. If the objects do not currently exist on the back-end database/catalog, choosing Export to Server on the data objects will be sufficient.
  2. Exporting certain statements to Oracle server may cause spacing in identifiers on the server. For example, a statement like so: CREATE OR REPLACE TRIGGER SET_TRANSACTIONS_TRANID BEFORE INSERT ON TRANSACTIONS FOR EACH ROW WHEN (NEW.TRANID IS NULL) BEGIN SELECT TRANSACTIONS_TRANID_SQ.NEXTVAL INTO :NEW.TRANID FROM DUAL; END;

    After execution of this script against the server, the identifiers specified in the SELECT clause may appear differently, for example "TRANSACTION_TRANID_SQ.NEXTVAL" may appear like so "TRANSACTIONS_TRANID_SQ . NEXTVAL"

4.8 Renaming folders in database project

Changing the name of a folder within a project containing a database will break URI references in the database related files in the project and cause errors. If a folder must be renamed, all related references in the database related files must be changed. For example, the following reference exists in a table object file (with extension .tblxmi) and the database in the project is contained in the a folder called folder1:

<database href="folder1/Con1_VIDEOS.dbxmi#RDBDatabase_1"/>

If the folder name is changed to folder2 then this line must be altered to reflect this change:

<database href="folder2/Con1_VIDEOS.dbxmi#RDBDatabase_1"/>

4.9 SQL Comments

SQL comments (lines preceded by a double dash: --) are not supported in the SQL builder.

4.10 Check in and check out SQL Statements before opening them in the SQL Builder

If you are editing any SQL Statements (.sqx files) that are under version control, ensure that the statement is not open in the SQL Builder when you are checking them in or out. Check the file out (in the Navigator view) and then edit the statement. After you have finished editing the statement, save your changes, close the SQL Builder and check the file back in.

4.11 Do not immediately add newly created SQL Statements to version control

If you create a new SQL Statement and are prompted to add it to version control,do not do so. Make any changes necessary to the file using the SQL Builder, close the file, then add it to version control.

4.12 Editing Java stored procedures

4.13 DBCS data gets truncated when connecting to DB2/390 (CP930)

Due to a JDBC limitation, when connecting to DB2/390 (CP930), some DBCS data gets truncated based on the data length of UTF-8 encoding. 10 SBCS Katakana characters can be received as 3 Katakana characters by a "SELECT" statement. The actual data stored in a table in OS/390 is '8182838485868788898A' in hexadecimal which is 10 Japanese Katakana characters (1 byte for 1 character). However, when using the JDBC driver with the default setting, the received query data shows only 3 characters ('efbdb1efbdb2efbdb3') as the valid 9 bytes string in UTF-8, which would be cut off from 'efbdb1efbdb2efbdb3efbdb4efbdb5efbdb6efbdb7efbdb8efbdb9efbdba' in the JDBC driver.

4.14 Changes lost when closing editor of read-only document

If a document is checked out, ie marked read only, while an editor is open on the document all pending changes will be lost when the editor is closed. Ensure a document is checked out before it is opened in an editor.

4.15 Schema object identifiers missing quotes for non-english locales

The JDBC catalog loader cannot determine whether a schema object identifier should be delimited for locales other than english. For identifiers that should be delimited, after import open the schema/table editor to rename the schema object. Refer to your database server documentation to determine when an identifier should be delimited.

4.16 Connecting to a DB2 server

For other DB2 servers, see the online help for selecting JDBC drivers when making database connections.

4.17 Dropping Java stored procedures from DB2 Version 8 is not supported when using DB2 Version 7 client

If you are working with DB2 Universal Database Version 7 on your client workstation and connecting to DB2 Universal Database Version 8 on the server, you will be unable to drop Java stored procedures from the DB2 Version 8 server.

4.18 Manually changing the returned SQL data types for DB2 table UDFs

When you use the New SQL User-Defined Function wizard to create a DB2 table UDF, the wizard recommends SQL data types for the data returned for each column.

If the wizard is unable to map a corresponding data type for a column, or if you want to use a different returned data type for a column, open the generated UDF in the editor and change the data type manually.

4.19 Problems with Sequelink 5.1 shipped with WebSphere Studio

WebSphere Studio includes a WebSphere branded version of the Sequelink 5.1 JDBC client in WS_installdir/runtimes/aes_v4/lib/sljc.jar. The WebSphere Studio relational database tools are not enabled to use this version of the client for JDBC connection. You may receive the error message:

IWAS0126E Problems encountered while trying to establish connection connection_name Reason: Connection was not established because the selected JAR file is not a WebSphere branded DataDirect client. Ensure the selected JAR comes from a WebSphere install.

when this JAR is specified in the JDBC class location field of the Connection wizard. A version of the client that the relational database tools are enabled to use is available from IBM Support.

4.20 DataDirect Sequelink 5.3 Driver

WebSphere Studio Version 5.x includes a new version of the DataDirect Sequelink JDBC client. The DataDirect Sequelink 5.3 JDBC client can be found in the WS_installdir/runtimes/base_v5/lib/sljc.jar archive. Refer to the relational database tools documentation on how to connect to a database using the Sequelink 5.1 JDBC driver.

4.21 WebSphere Application Server - Express examples SQL files may contain errors but can be executed successfully

The SQL files in each WebSphere Application Server - Express example's Setup/databases project folder may contain erroneous parsing errors shown in the Outline view of the Data Perspective. However, The SQL files execute successfully using the "Run on database server" action as described in the examples README.html file.

4.22 Problems retrieving tables from Microsoft SQL Server

4.23 Problems connecting to Cloudscape 5.1

Multiple connect and disconnect actions using Cloudscape 5.1 may result in tables without columns in the DB Servers pane. Should you encounter this problem, restart WebSphere Studio.

4.24 Error message while moving/renaming/deleting JDBC jar file

JDBC jar files, ie the jar files specified in the class location field of the Connection Wizard, are locked for use in the current JVM session after the first time a connection is established. Attempts to rename/move/delete the jar files will result in the following error message: 'The process cannot access the file because it is being used by another process.' The file will be unlocked when the workbench shuts down.

4.25 Duplicate schema nodes will appear when importing, deleting, or re-importing EJB JAR files

Importing, deleting, and re-importing EJB JAR files (samples or otherwise) with either the Data Perspective or the J2EE Perspective open, or both, occasionally causes duplicate schema nodes to appear under the backend databases for that EJB JAR file. The entries are harmless, however, it is recommended that you shutdown and restart the perspective to remove the duplicate entries.

4.26 Corrupted Arabic error messages during relational data export

When exporting relational data to a UTF-8 UDB DB2 V7 database, using Arabic locale, you may experience corrupted error messages should errors occur. There is no workaround to this problem.

Return to the main readme file

(C) Copyright IBM Corporation 2000, 2003. All Rights Reserved.