The IBM^® Database Add-Ins for Visual Studio provide partial support for user-defined data types (UDTs) and spatial data types that are defined in DB2^® databases.

UDTs that are defined in a DB2 database and DB2 spatial data types are represented in the add-ins by their corresponding DB2 built-in data types.

Any information that the add-ins pass directly to the DB2 server can contain UDT and spatial-data-type data. However, any data on which the add-ins operate must be DB2 built-in data types. UDT or spatial-data-type data that is displayed in the IBM designers is shown by its corresponding DB2 built-in data types. A UDT or spatial data type can consist of multiple data elements. Data of such a data type is displayed in the add-ins as the individual elements and is treated and passed to the DB2 server as separate elements.

For example, consider a UDT with the name LOCALE that is created in a DB2 database. The LOCALE UDT consists of two elements: the first element, which represents the city, is of type VARCHAR, and the second element, which represents the state, is of type CHAR. If the INTERESTS table is defined in the DB2 database and one of the columns in INTERESTS is of type LOCALE, when you review the definition of INTERESTS in the IBM Table Designer, you see that LOCALE is represented by two separate columns, the first of type VARCHAR and the second of type CHAR.

Note:  In the following descriptions, wherever you can create or update the definition of a database object by using a script in the Visual Studio Code Editor, an IBM database project must be open in the Solution Explorer and the project must be connected to a DB2 data server.

Tables

The following information describes how you can work with a table that contains a column of UDT or spatial data type.

Create table

You cannot use the IBM Table Designer to create the table.

You can use the Visual Studio Code Editor to write an SQL script that, when built, creates the table.

Review table definition

You can use the IBM Table Designer only to review the table definition.

Update table definition

You cannot use the IBM Table Designer to update the table definition.

You can use the Visual Studio Code Editor to edit the script that defines the table, and then rebuild the table.

Load data

You can use the IBM Data Designer to load data in the table. You load data either by changing the table data or by importing data to the table. If check constraints are defined on the column of UDT or spatial data type, the DB2 data server might not perform integrity checking of the data that is passed through by the add-ins.

Views

The following information describes how you can work with a view that contains a column of UDT or spatial data type.

Create view

You can use the IBM View Designer to create the view, as long as the SQL SELECT statement that defines the view is described only in terms of manipulating values in SQL. For example, you could use a JOIN clause on two columns of UDT or spatial data type, but you could not compare such a column to the value of a host variable.

You can use the Visual Studio Code Editor to write an SQL script that, when built, creates the view.

Update view definition

You can use the IBM View Designer to update the view definition, as long as the SQL SELECT statement that alters the view is described only in terms of manipulating values in SQL.

You can use the Visual Studio Code Editor to edit the script that defines the view, and then rebuild the view.

Load data

You can use the IBM Data Designer to load data in the view. You load data either by changing the view data or by importing data to the view. If check constraints are defined on the column of UDT or spatial data type, the DB2 data server might not perform integrity checking of the data that is passed through by the add-ins.

Procedures and functions

The following information describes how you can work with a procedure or a function that contains a parameter of UDT or spatial data type.

Create procedure or function

You cannot use the IBM Procedure Designer to create the procedure or the IBM Function Designer to create the function.

You can use the Visual Studio Code Editor to write an SQL script that, when built, creates the procedure or the function.

Update procedure or function definition

You cannot use the IBM Procedure Designer to update the procedure definition or the IBM Function Designer to update the function definition.

You can use the Visual Studio Code Editor to edit the script that defines the procedure or the function, and then rebuild the procedure or the function.

Run procedure or function

The built procedure or function is shown under the data connection in the Server Explorer. You can run the procedure or the function by using the Run command on its shortcut menu.

You must specify a separate value for each element in an input parameter of a UDT or spatial data type.

If an output parameter is a UDT or spatial data type, the result set contains a separate value for each element in the parameter.

Applications

If you write a Windows^® or an ASP.NET application in Visual Studio that implements DB2 Spatial Extender, you must know how data of spatial data types is stored in a DB2 database so that you can programmatically manipulate the data by its separate elements.

For detailed information about DB2 spatial data types, refer to the About DB2 Spatial Extender section of the IBM DB2 Database for Linux^®, UNIX^®, and Windows Information Center.

Server Explorer

Database objects that contain data of UDT and spatial data types are displayed in the Server Explorer like objects with data of other data types.

You can drag objects with data of other data types from the Server Explorer to an application that you are developing. However, dragging objects with data of UDT or spatial data types to an application is not supported. Unpredictable results occur if you attempt drag operations on objects with data of UDT or spatial data types.

See Also

Creating Tables | Creating Views | Creating SQL and SPL Procedures | Creating SQL and SPL Functions | Editing the Source Code of Procedures, Functions, or Objects | Changing Table and View Data | Importing Data to Tables and Views | Running Procedures or Functions

.NET Development Forum   DB2 FAQs

© Copyright IBM Corporation 2002, 2019. All Rights Reserved.