Relational database tools - release notes

1.0 Introduction
2.0 Supported software and specifications
3.0 Changes from the previous release
4.0 Known problems and limitations
   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 Empty schemas not loaded for DB2 connections
   4.8 Data Export
   4.9 Renaming folders in database project
   4.10 SQL Comments
   4.11 Check in and check out SQL Statements before opening them in the SQL Builder
   4.12 Do not immediately add newly created SQL Statements to version control
   4.13 Increasing the DB2 application heap size if DB2 routines are not loaded
   4.14 Editing Java stored procedures
   4.15 DBCS data gets truncated when connecting to DB2/390 (CP930)
   4.16 Changes lost when closing editor of read-only document
   4.17 Schema object identifiers missing quotes for non-english locales
   4.18 Which JDBC driver to use when connecting to a DB2 server
   4.19 DB2 stored procedure and UDF support for DB2 Universal Database for iSeries
   4.20 Dropping Java stored procedures from DB2 Version 8 is not supported when using DB2 Version 7 client
   4.21 Manually changing the returned SQL data types for DB2 table UDFs
   4.22 Problems with Sequelink 5.1 shipped with WebSphere Studio
   4.23 DataDirect Sequelink 5.3 Driver
   4.24 Database editor, schema editor, or table editor NullPointerException error
   4.25 4.20 Dropping Java stored procedures from DB2 V8 is not supported when using DB2 V7 client
   4.26 Updates to "Creating a JDBC connection using a Cloudscape driver"
   4.27 SQL Wizard only support Web Projects using default content location

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:

3.0 Changes from the previous release

4.0 Known problems and limitations

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 exmaple "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 Empty schemas not loaded for DB2 connections

The IBM DB2 Version 7 JDBC Drivers, APP and NET, do not load empty schemas. A schema is considered empty if it does not contain schema objects, for example, tables, views, and indexes. Application objects, for example stored procedures and UDF's, are not considered schema objects.

4.8 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.9 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.10 SQL Comments

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

4.11 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.12 Do not immediately add newly created SQL Statements to version control

If you create a new SQL Statment 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.13 Increasing the DB2 application heap size if DB2 routines are not loaded

When you connect to a DB2 database from the DB Servers view, you might need to increase the number of pages allocated for application heap in DB2. The workbench uses an SQL query to obtain the list of routines from DB2. Depending on the database and the specified filter, DB2 may require a bigger application heap size to evaluate the SQL statement. Contact your DBA, use the DB2 Control Center, or enter the following command to increase the application heap size variable (applheapsz):

db2 update db cfg for db alias using applheapsz 256

This problem is more likely to occur if you are using DB2 Version 7. The default application heap size for DB2 Version 7 is 128 pages (4 KB each). The default heap size for DB2 Version 8 is 256 pages.

4.14 Editing Java stored procedures

4.15 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.16 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.17 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.18 Which JDBC driver to use when connecting to a DB2 server

4.19 DB2 stored procedure and UDF support for DB2 Universal Database for iSeries

4.20 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.21 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.22 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.23 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.24 Database editor, schema editor, or table editor NullPointerException error

If you experience a NullPointerException error message while modifying database resources using the database editor, schema editor, or table editor, you should shut down and restart the workbench. No data will be lost.

4.25 4.20 Dropping Java stored procedures from DB2 V8 is not supported when using DB2 V7 client

Secion 4.20 for WSAD, Section 4.19 for WSSD/WAS Express

As Defect 241987 both UDF, and Stored Procedure cannot be dropped when using DB2 V7 client to DB2 V8.0. Unsupported statement for UDF should be also included.

"4.20 Dropping Java stored procedures from DB2 V8 is not supported when using DB2 V7 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.26 Updates to "Creating a JDBC connection using a Cloudscape driver"

Steps 7 to 9 in the relational database tools help topic called "Creating a JDBC connection using a Cloudscape driver" should read:

  1. If connecting to an existing Cloudscape database, type the fully qualified location of the Cloudscape database in the Database location field. To create an empty Cloudscape database used to test your applications, type:
    <Cloudscape_dbdir>\<database_name>;create=true
    (where <Cloudscape_dbdir> is the directory on your file system where you want the Cloudscape database to be located, <database_name> is the name of the database that you provided in step 2, and "create=true" creates the Cloudscape database).
  2. Type the class location containing the JDBC driver in the Class location field. For example, <WS_instdir>\runtimes\base_v5\lib\db2j.jar (where <WS_instdir> is the path where you have installed WebSphere Studio). If Other Driver was selected from JDBC driver pull-down list, then the Class location field is enabled allowing you to either type or select the appropriate driver class location.
  3. The Connection URL field is pre-filled and disabled with the URL connection. This URL is in the form of jdbc:db2j:<subsubprotocol><database_name> (where <database_name> is the name of database that you provided in step 2 and <subsubprotocol> is the location where Cloudscape looks for a database, such as in a directory, in a class path, or in a jar file). Refer to the Cloudscape Developer's Guide (located at www.ibm.com/software/data/cloudscape/pubs/collateral.html) for examples of database connection URLs. However, if Other Driver was selected from JDBC driver pull-down list, then the Connection URL field is enabled allowing you to either type or select the appropriate URL connection.

4.27 SQL Wizard only support Web Projects using default content location

In this release, you must use the default Web Project content location (Web Content) in order to use the SQL Wizard. You will encounter errors otherwise.

Return to the main readme file