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
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.
The relational database tools support connecting to and importing from the following database types:
- SQL 92, 99
- Cloudscape 5.0.4
- DB2 UDB 6.1, 7.1, 7.2, 8.1
- DB2 UDB for iSeries 4.5, 5.1
- DB2 UDB for OS/390 6, 7
- Oracle 8i 8.1.7
- Oracle 9i 9.0.1
- SQL Server Enterprise 7.0 SP2
- SQL Server Enterprise 2000
- Sybase Adaptive Server Enterprise 11.9.2, 12, 12.5
- Informix Dynamic Server.2000 9.2
- Informix Dynamic Server 7.3, 9.3, 9.3.1
- MySQL 3.23
- InstantDB 3.26
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:
- Applicable for DB2, Oracle, Cloudscape and Sybase only.
- For DB2, the Union, Union All, Except, Except All, Intersect, and Intersect All operators as well as values clauses are supported.
- For Oracle, the Union, Union All, MINUS and Intersect operators are supported
- For Sybase, the Union and Union All operators are supported.
- For Cloudscape, the Union, Union All, as well as values clauses are supported.
Grouping query results support:
- DB2: inner joins, left outer joins, right outer joins, full outer joins
- Oracle: inner joins, left outer join and right outer joins. The syntax for left and right outer joins is consistent with the Oracle "+" syntax in the where clause.
- Sybase, Cloudscape and MySQL: inner joins, left outer join and right outer joins.
Cast expression support:
- DB2: Group By, Rollup, Cube and Grouping Sets
- Oracle: Group By, Rollup and Cube
- Sybase: Group By
- Cloudscape: Group By
- DB2 - CAST expression AS expression
- MS SQL Server - CAST expression AS data_type
- Oracle - TRANSLATE is unsupported
- New UDF Web Service wizard enables you to create UDFs to access the operations of Web services, eliminating the need to transfer data between Web services and the database, resulting in better performance.
- There is currently no support for modeling indexes, check and unique constraints, triggers, structured types, or identity columns.
- The relational database tools do not support check options on views.
- Unnecessary brackets in query statements will cause parser errors.
- The use of parentheses in query WHERE condition clauses is currently unsupported.
- Large SQL files may slow down the editor.
- 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.
- 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.
- Editing a view and then opening a statement which contains the view may cause unexpected workbench shutdown.
- 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.
- A query cannot be created using a view that is derived from a user-defined function.
- Drag and drop of database objects is only supported from the Data Definition view not the DB Servers view.
- 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.
- 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.
- 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.
- 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.
- Identifiers for primary key constraints are not imported for OS/390 connections.
- 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.
- 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.
- 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.
- 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.
- You may encounter problems with importing or editing involving the Oracle long datatype.
- Extremely large SQL DDL files may slow down the editor.
- Extremely large SQL DDL files may cause out of memory exceptions during parsing or execution.
- 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.
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.
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.
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.
- 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.
- 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"
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"/>
SQL comments (lines preceded by a double dash: --) are not supported 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.
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.
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.
- The editor shows a lightbulb icon in the marker bar at the first line of a Java stored procedure. You can ignore this marker. The marker will not cause problems when you build the Java stored procedure.
- The editor allows you to add breakpoints to the source code of a Java stored procedure. However, these breakpoints are ignored because the debugging of Java stored procedures is not yet supported.
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.
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.
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.
- DB2 Universal Database for iSeries: You must select the AS/400 Toolbox for Java JDBC Driver in the JDBC driver pull-down field of the New Database Connection wizard. For more information, see the help topic "Creating a JDBC connection using a DB2 Universal Database for iSeries driver," which includes information about obtaining and connecting using the AS/400 Toolbox for Java JDBC Driver.
- DB2 Universal Database for Linux, UNIX, and Windows:
- If you are working with DB2 stored procedures and UDFs, you should select the IBM DB2 APP DRIVER in the JDBC driver pull-down field of the New Database Connection wizard.
- For other DB2 servers, see the online help for selecting JDBC drivers when making database connections.
- The DB2 stored procedure and UDF builder supports DB2 Universal Database for iSeries V4R5, V5R1, and V5R2.
- When making a database connection to DB2 Universal Database for iSeries, you must use the AS/400 Toolbox for Java JDBC Driver.
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.
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.
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.
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.
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.
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. "
Steps 7 to 9 in the relational database tools help topic called "Creating a JDBC connection using a Cloudscape driver" should read:
- 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).- 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.
- 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.
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