Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
Database Tools
SQL Scripts & Samples
» Database Forum
» Sitemap
Free Newsletters:
News Via RSS Feed

follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Mar 19, 2008

DB2 9 for Linux, UNIX, and Windows: DBA Guide, Reference, and Exam Prep (6th Edition) - Page 6

By Staff

DB2 Extenders

DB2 Extenders offer the ability to manipulate data outside of conventional rows and columns to include the manipulation of special data types (for example, spatial types that have associated LAT/LONG coordinates and SQL-based functions to operate on them), searching services, and more. The purpose of the DB2 Extenders is to provide for the management of this data through the familiar DB2 SQL API.

The DB2 Extenders encapsulate the attributes, structure, and behavior of these unstructured data types and stores this information in DB2. From the developer's perspective, the DB2 Extenders appear as seamless extensions to the database and enable the development of multimedia-based applications. In other words, a spatial data type is no different than a built-in data type that they may be accustomed to. This section briefly details the DB2 Extenders that are provided by IBM.

DB2 Spatial Extender

The DB2 Spatial Extender (DB2 SE) provides the ability to create spatially aware data objects and store them within your DB2 database, along with other spatially related objects like (LAT/LONG) coordinates and more. Almost all industries could benefit from this free technology in DB2. For example, the banking and finance industry could visually envelope customer segments for brand location identification. Municipal governments could use this technology for flood plain identification, the retail industry for billboard locations, and more. This seems apparent when you consider that almost all data has some sort of spatial component to it: we all have an address, merchandise in a warehouse has a stock location, and so on.

The business benefit of the DB2 SE lies in the notion that it's a lot easier to spot visually represented information than data reported in rows and columns.

When you enable your DB2 database for the DB2 SE, you can interact with your data using SQL or specialized spatial tools from other vendors. The point is that with the DB2 SE, DB2 understands the spatial "dialect" and the operations that you want to perform with it.

For example, a telematics application on a PDA may provide its users with a list of nearby Chinese restaurants that serve Peking Duck based on the dynamic request of this user. In this case, after the client's PDA creates a location box using Global Positioning System (GPS) coordinates, it could generate SQL statements similar to the following:

  OVERLAPS (location, box(getGPS(),2000,2000))
  AND category = 'chinese'
  AND doc Contains(menu,'Peking duck');

OVERLAPS is a spatial function that shows interested data in a binding box defined by the OVERLAPS boundary specification; there are many other spatial functions, including INTERSECTS, WITHIN, BUFFERS, and so on.

DB2 Geodetic Extender

The DB2 Geodetic Extender builds upon capabilities available in the DB2 Spatial Extender and adds compensation for real-world objects like the curvature of the earth's surface. The algorithms in this extender seek to remove the inaccuracies introduced by projections and so on. This extender is available only for DB2 Enterprise as part of the Data Geodetic Management feature.

DB2 Net Search Extender

The DB2 Net Search Extender (DB2 NSE) combines in-memory database technology with text search semantics for high-speed text search in DB2 databases. Searching with it can be particularly advantageous in Internet applications where performance is an important factor. The DB2 NSE can add the power of fast full-text retrieval to your DB2 applications. Its features let you store unstructured text documents of up to 2 GB in databases. It offers application developers a fast, versatile, and intelligent method of searching through such documents.

Additionally, the DB2 NSE provides a rich set of XML searching capabilities with advanced search features like sounds-like, stemming, and so on. It is shipped free in DB2 9 (it was a chargeable extender in DB2 8) to facilitate non-XML index searching of XML data stored in pureXML columns.

DB2 XML Extender

The DB2 XML Extender is provided with DB2 and allows you to store XML documents in DB2; it also gives you the ability to shred and store XML in its component parts as columns in multiple tables. In either case, indexes can be defined over the elements or attributes of an XML document for fast retrieval. Furthermore, text and fragment search can be enabled on the XML column or its decomposed parts via the DB2 Net Search Extender. The DB2 XML Extender can also help you formulate an XML document from existing DB2 tables for data interchange in business-to-business environments.

You may recall that the pureXML add-on feature pack is available for all DB2 9 data servers. Indeed, this can cause confusion since the DB2 XML Extender is shipped for free in DB2 9. You should consider the DB2 XML Extender as stabilized technology. In other words, it is no longer being enhanced and shouldn't be considered for most XML applications. The DB2 XML Extender's approach to storing XML is to shred the XML to relational tables or stuff it into a large object. When you use this technology to persist XML data, you have to make serious trade-offs with respect to performance, flexibility, and so on. In addition, you have to use specialized functions to implement Spathe searches, and data types are abstracted from base DB2 data types. Quite simply, the way you interact with the DB2 XML Extender isn't natural for XML programmers and DBAs alike.

In contrast, the pureXML feature in DB2 9 provides services such that no compromises between flexibility (what XML was designed for) and performance (one of the reasons why you want the data server to store your XML) need to be made when storing your XML data. For example, to generate XML documents from relational tables, you simple use the SQL/XML API instead of the cumbersome DB2 XML Extender functions. You can validate XML documents against Sods instead of only document type definitions (Ds) as is the case with the DB2 XML Extender, and more. We strongly recommend this feature for most of your XML-based applications.

DB2 Administration

DB2 DBAs have a number of graphical-based tools they can use to manage and administer DB2 data servers. Alternatively, a DBA can also use a script-based approach to administer the data environment using the DB2 tools to create and schedule the scripts. This section briefly details the main graphical tools available with DB2.

Control Center

The Control Center is the central point of administration for DB2. The Control Center provides DBAs with the tools necessary to perform typical database administration tasks. It allows easy access to other server administration tools, gives a clear overview of the entire system, enables remote database management, and provides step-by-step assistance for complex tasks.

Figure 1–17
The DB2 Control Center

The All Systems object represents both local and remote data servers. To display all the DB2 systems that your system knows about, expand the object tree by clicking on the plus sign (+) next to All Systems. In Figure 1–17, you can see a DB2 data server called PAULZ contains a DB2 instance called DB2, in which the database TEST is located.

When you highlight an object, details about that object are shown in the Contents Pane.

The main components of the Control Center are:

  • Menu Bar — Used to access Control Center functions and online help.

  • Tool Bar — Used to access other DB2 administration tools, such as the Command Editor, Task Center, and more.

  • Objects Pane — This is shown on the left side of the Control Center window. It contains all the objects that can be managed from the Control Center as well as their relationship to each other.

  • Contents Pane — This is found on the right side of the Control Center window and contains the objects that belong or correspond to the object selected in the Objects Pane.

  • Contents Pane Toolbar — These icons are used to tailor the view of the objects and information in the Contents pane. These functions can also be selected in the View menu.

  • Task Window — Lists the most common tasks associated with the selected object in the Object Pane. In Figure 1–17 you can see that since a database is highlighted, common tasks and administrative functions related to it are in this window.

  • Hover Help — Provides a short description for each icon on the toolbar as you move the mouse pointer over the icon.

The Control Center also comes with personality control that you can use to adjust the view and functions available from the Control Center's tree view of your data server. For example, you can limit the Object Pain view to show just Tables or Views, as well as limit the actions you can perform from the context-sensitive right-click menu options. You can customize your Control Center personalities using Tools→Tools Settings→Customize Control Center.

Note - The facility to define a Control Center personality by defaults pops up each and every time you start the Control Center. You can turn off this option by deselecting the Show this window at startup time checkbox.

DB2 Replication Center

The DB2 Replication Center is a graphical tool that allows DBAs to quickly set up and administer all forms of data replication, including the options offered by WebSphere Replication Server. The main functions in setting up a replication environment can be performed with this tool, including:

  • Registering replication sources

  • Monitoring the replication process

  • Operating the CAPTURE and APPLY programs

  • Defining alerts

You can use the Replication Center to set up all kinds of DB2 replications, as shown in Figure 1–18.

Figure 1–18
The DB2 Replication Center

DB2 Archives

Comment and Contribute


(Maximum characters: 1200). You have characters left.