 | Level: Intermediate Knut Stolze (stolze@de.ibm.com), Information Integration Development, IBM Germany
19 Feb 2004 Author Knut Stolze describes an approach for using the existing replication facilities in DB2 UDB to replicate spatial data by transforming it to an external representation based on LOBs, and replicating those LOBs. Sample setup program included.
Introduction
IBM offers the DB2 Spatial Extender to store, manage, and analyze spatial data in DB2® Universal DatabaseTM relational databases. Spatial data is stored in the database using structured types in order to model its complex properties appropriately and to exploit specialized multi-dimensional indexing methods in an easier and more reliable way.
Another key component of the DB2 Information Management product family is the DB2 Replication suite. It allows you to maintain selected sets of data in multiple databases. Changes to the data are copied from a table in one database to another table in a different database. DB2 replication is an integrated feature of DB2 Universal Database version 8. It's replication capabilities are tailored to handle the DB2 built-in data types such as INTEGER, DOUBLE, or VARCHAR. Currently, there is no native support in replication to take advantage of existing spatial data and to replicate it as well to other databases if the user wishes to do so.
This paper describes an approach for using the existing replication facilities in DB2 to replicate spatial data by transforming it to an external representation based on LOBs, and replicating those LOBs. At the target database, the reverse transformation is applied to construct the geometries understood by the DB2 Spatial Extender again. This approach requires some slight modifications in the replication setup. In this article I'll explains the necessary setup step-by-step, so that you can easily understand and adapt it for your own environment.
Understanding spatial data and replication
Spatial data is used to represent complex spatial features as scalar values in object-relational database systems. Spatial features are points, lines, polygons, or collections of those features. With the DB2 Spatial Extender, you get the necessary types and functions to manage spatial data in a DB2 database. Once you have spatially enabled your database, you can then answer questions like "How far away from my stores do all customers live who have a certain income?" or "Do I have several construction areas planned in the same vicinity in a certain time interval, and could I consolidate them?" The DB2 Spatial Extender defines a set of data types and functions to manage spatial data (geometries) and to help you leverage the spatial aspect that exists in your data.
The spatial data types are arranged in a type hierarchy to provide you some more abstraction and better type safety. Loosely speaking, a type hierarchy in SQL is the same as a class hierarchy in object-oriented programming languages. Figure 1 shows you the type spatial hierarchy. The types depicted with the blue background color identify the non-instantiable types. Those types are like an abstract class in the Java or C++ programming language. All other types are instantiable.
Figure 1. DB2 Spatial Extender type hierarchy.
The DB2 Spatial Extender comes with well over 100 different functions for working with spatial data. For example, you can test whether or not one geometry is within another, or you can calculate the distance between two geometries. Describing all those functions goes way beyond the scope of this paper. Refer to the DB2 Spatial Extender User's Guide and Reference for more information.
Data replication is used in many different customer scenarios. For example, it can permit different groups to work independently on common data while still keeping the replicated portion synchronized. It also offers a way to implement a high-availability scenario.
Figure 2 shows a basic overview how DB2 replication works. The user's application issues queries (SELECT) and data modification statements (INSERT, UPDATE, DELETE) against a source table. The data in that table is to be replicated to another table in a different database, the target table. The data modifications in the source table cause DB2 log records to be written. The asynchronous Capture process reads the log records, and when it detects that changes were made to the source table, it extracts the change data from the log records and places it into a staging table, the change-data table (CD table). Another process, the Apply process, periodically scans the CD table and if new information appears there, it reads it and publishes it to the target table. The Apply process can handle multiple target tables. Thus, the architecture of DB2 replication follows the classic publish/subscribe mechanism.
Figure 2. DB2 replication overview.
In the next section, I'll describe an approach to replicating spatial data using several features that are available in DB2 and the replication tools. One concept, the transform functions and transform groups, is more important for spatial data replication and it is explained in more detail in the section Transform functions for structured types. The section Capturing changes to spatial data presents the setup for capturing changes to spatial (and other) data on the source (capture) database. It shows how the change-data tables and views have to be defined and explains how the Capture process will take place. In the section Preparing the target database I'll explain how to prepare the target database in such a way that the spatial data is stored directly in the respective table by the Apply process. To further simplify the setup procedures for the source and target databases, we implemented a command line tool, available in the download section of this article. The details of the tool are described in the section Setup tool "db2se repl". The article concludes with some notes on DB2, spatial, and replication features.
 |
The big picture
The replication components -- Capture, Apply, Monitor, and the Replication Center administration interface -- are not configured to work with structured types in general, and spatial types in particular. The Capture program cannot interpret spatial data that is stored in DB2 log records, and therefore a set of workarounds must be employed to overcome this limitation. There are several capabilities built into DB2 and the replication tools that can be used for implementing these workarounds. Let's start with a high-level overview.
When replicating spatial data, we transfer it as LOB data, to be more specific, as binary large objects (BLOBs). That way, the replication tools deal with a data type that is supported, and the tools are not aware that the BLOB data is actually spatial data. Views can be leveraged to implement such a transformation. Views can be defined in replication such that the Apply program fetches data through a view from the source table during full refresh of the target, and through a view of the CD table for differential refreshes. Views allow for a "logical" expansion and/or interpretation of the source data, such that data is transformed or expanded during the fetch of the data by the Apply program. In this implementation for the spatial replication, a view over the source table is defined in such a way that it includes again the spatial data in form of BLOBs. Figure 3 illustrates the architecture.
Figure 3. Replicating spatial data as BLOBs.
The Apply process queries the data from the source view. There, it sees the BLOB column instead of the Spatial column, which is actually the transformed spatial data from the source table. The other columns -- the key column "k" and the data column "d1" -- are treated as usual by DB2 replication and no special considerations are necessary. The data is inserted (eventually across system boundaries as marked by the dotted line) into the target view by the Apply process. The definition of the target view matches the definition of the source view, which means that the data type for the spatial data is also a BLOB. In addition to the target view, we define three INSTEAD OF triggers that implement the reverse transformation during an INSERT, UPDATE, or DELETE operation. The reverse transformation is the construction of the spatial data from the binary data that was replicated by the Apply process.
This logic is also used during a full refresh, that is, when all the data from the source is to be replicated to the target. The basic principle is the same for a differential refresh where only the changes since the last Apply cycle are replicated. For differential refresh, we have to take the change data tables and view (CD tables and CD views) into consideration. The CD tables are maintained by the Capture process, which scans the DB2 logs and extracts the information on which data changed. These data changes are collected in the CD tables. CD views are similar to other views and present a different view on the data in the CD tables. Those views are derived from the actual views that are to be replicated. Figure 4 shows the specifics of the source database including the CD table and the CD view.
Figure 4. Source table and view with CD table and CD view.
We have a two-fold problem now. First, the Capture logic does not understand spatial types and, thus, will not collect the changed data information in the CD table. The second issue is introduced by the transformation to LOBS that takes place in the source view as described above. But those issues can easily be resolved if you consider the following DB2 and DB2 replication capabilities:
-
Replicating a subset of columns
Replication of data from a table does not require that all columns of the table be replicated. A subset of columns can be defined for a replication source, such that certain columns in the source table can be ignored completely. This feature is used to leave out the spatial columns from the registration of the source table. Thus, we are not limited by the Capture logic and the spatial data type.
-
and replicating LOBs
The Capture program never captures changes to LOB data from the DB2 log. When a "native" LOB column is registered for replication, the CD table is built to include an "update indicator" column that will be maintained by the Capture process as it reads the DB2 log records for the source table. When a log record is read which indicates that a LOB column has been updated, the update indicator is set to the value 'U'; otherwise set to NULL. On the Apply side, when the source table of a subscription contains a LOB column, the Apply program looks for the matching update indicator column in the CD table to detect whether LOB updates have occurred. The matching update indicator column in the CD table has the same name as the LOB column in the source table. If a LOB was updated, the Apply program fetches the LOB data directly from the source table and applies this data to the target table.
In this implementation, since the spatial data is being fetched as a binary data type using a view, the same logic needs to be set up for the CD view. However, the capture program is not aware that the spatial column is to be treated like any other LOB column. This requires that we need to mimic the update indicator column. We add another column to the source table and define two triggers to maintain this column in the same way the Capture process maintains update indicators for LOB columns. This additional column is registered with DB2 Replication, and any changes to it are captured and stored in the CD table. The CD table and view include this update indicator.
The described functionality is now exploited. The update indicator column is marked with an "@" in figure 4. Triggers are used to set its value to 'U' if and only if the spatial column was modified. The update indicator is also part of the CD table. Thus, the CD table keeps the information if the values in spatial column were changed. The definition of the CD view maps all columns from the CD table as they are, except the update indicator, which is renamed to the name of the transformed spatial column. The result is that the Apply process finds a BLOB column in the source view and an update indicator column matching with the BLOB column's name in the CD view. The non-spatial data is fetched from the CD table. The spatial data is fetched as BLOB from the source view either if an INSERT operation occurred or if an UPDATE occurred and the update indicator in the CD table is set to 'U' indicating that the spatial data was modified. Figure 5 illustrates the logic flow involving all the pieces presented before.
Figure 5. Overview of spatial data replication.
The following sections explain in more detail the necessity to transform the spatial data to a BLOB and how the source and target tables need to be set up. Then an example guides you through performing the various required steps in the Replication Center and helps you to gain a complete understanding of this approach to replicating spatial data.
Transform functions for structured types
Now let's have a look at the transfer of spatial data between the database server and the client. Spatial data is represented using structured types. As in any other programming language, structured data cannot simply be transferred as-is. It needs to be linearized or transformed to some form of a byte stream. This byte stream is then transferred from the client to the server or vice versa.
DB2 (and the SQL standard) uses so-called "transform functions" that perform the linearization and transformation of the DB2 internal structured data to an external representation and back. The external representation is a single scalar value, which is based on built-in data types. The built-in data types could be VARCHAR, INTEGER, BLOB or CLOB, or any other data type available in DB2. It depends on the specific transformation which result data type is required. In the case of the DB2 Spatial Extender, we use BLOBs for binary representations and CLOBs for textual representations of spatial data in an external format.
For communication between the DB2 client and the DB2 server, we need two different transform functions. One function converts the spatial data from the SQL representation to the external data format, and the other function converts from the external data format to the SQL representation. To simplify the usage of both transform functions you use a "transform group". A transform group contains a FROM SQL function that is responsible for the conversion from the structured data to the external format, and a TO SQL function that constructs a structured value in the SQL database from data in the external format.
Transform groups
Let's look at the concept of transform functions and groups and their application in an example. The DB2 Spatial Extender provides four transform groups. Each group contains one function to convert a geometry to an external representation and one function to convert from the external representation to a geometry. The four transform groups support the following external data formats:
-
ST_WellKnownText
Transform geometries from and to the corresponding well-known text representation (WKT). The WKT is stored as textual data in a CLOB with a maximum size of 2GB. The method ST_AsText is used to convert a geometry to the WKT.
-
ST_WellKnownBinary
Transform geometries from and to the corresponding well-known binary representation (WKB). The WKB is stored as binary data in a BLOB with a maximum size of 2GB. The method ST_AsBinary is used to convert a geometry to the WKB.
-
ST_GML
Transform geometries from and to the corresponding representation in the Geography Markup Language (GML). The GML is stored as textual data in a CLOB with a maximum size of 2GB. The method ST_AsGML is used to convert a geometry to the GML.
-
ST_Shape
Transform geometries from and to the corresponding ESRI shape representation (Shape). The Shape is stored as binary data in a BLOB with a maximum size of 2GB. The method ST_AsShape is used to convert a geometry to the Shape.
The conversion from the external representation (WKT, WKB, GML, and Shape) to the corresponding geometry values in the SQL database is implemented with the constructor functions ST_Geometry, ST_Point, ST_LineString, ST_Polygon, ST_GeomCollection, ST_MultiPoint, ST_MultiLineString, and ST_MultiPolygon. Each of the constructors is overloaded to take a BLOB as well as a CLOB as input parameter. Depending on the input data, the constructor functions can determine which format was provided and decode the data appropriately.
The usage of the transform groups can easily be illustrated with the SQL code shown in listing 1.
Listing 1. Using transform groups
CREATE TABLE t (
id INTEGER NOT NULL PRIMARY KEY,
g db2gse.ST_Point )@
INSERT
INTO t
VALUES ( 1, db2gse.ST_Point(10, 20, 0) ),
( 2, db2gse.ST_Point(110, 100, 0) )@
-- use the ST_WellKnownText transform group for bind-out
SET CURRENT DEFAULT TRANSFORM GROUP = ST_WellKnownText@
SELECT * FROM t;
ID G
----------- -------------------------------------------
1 POINT(10 20)
2 POINT(110 100)
2 record(s) selected.
DESCRIBE SELECT * FROM t@
SQLDA Information
sqldaid : SQLDA sqldabc: 896 sqln: 20 sqld: 2
Column Information
sqltype sqllen sqlname sqllonglen sqldatatype_name
----------- ------ ------- ---------- ------------------
496 INTEGER 4 ID 0 SYSIBM .INTEGER
409 CLOB 0 G 2147483647 DB2GSE .ST_POINT
-- use the ST_GML transform group for bind-out
SET CURRENT DEFAULT TRANSFORM GROUP = ST_GML@
SELECT * FROM t@
ID G
----------- -------------------------------------------
1 <gml:Point srsName="UNSPECIFIED"><gml:coord>
<gml:X>10</gml:X><gml:Y>20</gml:Y>
</gml:coord></gml:Point>
2 <gml:Point srsName="UNSPECIFIED"><gml:coord>
<gml:X>110</gml:X><gml:Y>100</gml:Y>
</gml:coord></gml:Point>
2 record(s) selected.
|
As you can see, the SELECT statement retrieves all columns from table T. The column G has a declared type of ST_Point, which is a structured type. The need to convert the structured data stored in this column when the data is retrieved is evident. The query did not involve any explicit calls to functions that would do the conversion, but DB2 recognizes the structured data as such and determines, based setting of the CURRENT DEFAULT TRANSFORM GROUP special register, which transform group and transform function needs to be used. For the first query, DB2 finds that ST_WellKnownText is specified for the transform group. The FROM SQL transform function in that group is ST_AsText(). Thus, DB2 will call the ST_AsText() function for all the structured values in the column G and convert it to CLOBs. The output of the DESCRIBE statement illustrates this. Those CLOB values -- together with the values for the ID column -- are now bound-out when the data is fetched.
Concluding, the concept of transform needs to be applied to spatial data replication because we effectively transfer the structured data from one database to another one. However, the replication tools do not natively support transform groups and functions. Therefore, we use a more manual approach. We perform the transformation from the structured data to the external representation with the help of views, and the reverse-transformation with views, combined with INSTEAD OF triggers. The following two sections give you all the details for that.
Capturing changes to spatial data
DB2 Replication supports the replication of LOBs 1, and spatial data can be converted to LOBs using the same functions that are used as transform functions in the transform groups. Those features can be combined to directly replicate spatial data. In this section, we explain the setup on the source table to perform the conversion of the spatial data to LOBs and the functioning of the Capture process. The procedure needed for the target table and the Apply process is covered in the next section.
The concept of transform groups is usually used to transfer data between the DB2 database server and the DB2 client. When replicating spatial data, the same concept is employed to transfer the data between two databases. Now, the source database becomes the database client providing the data to be stored at the target database, i.e. the database server. From that perspective, a classical client/server environment exists.
Choosing a transformation
As already presented, we have different choices for the external data format to be used during the replication of the spatial data. To determine the correct transformation function, we will set the following criteria:
- No or only very little additional disk storage should be needed in the source or target database.
- The conversion should be as fast as possible to avoid impacting the overall performance of the replication process.
To support both, we can either implement our own transform group or use one of the groups already supplied by the DB2 Spatial Extender. I've chosen to use the existing transform group ST_WellKnownBinary because using a pre-defined transform group rather than implementing one gives us well-tested functionality and reduces the implementation costs.
Choosing between a textual and a binary representation is equally simple. A textual representation is usually longer than a binary representation because more characters are needed for each floating point number. Another advantage of binary data is the higher accuracy. The points in spatial data are stored as floating point numbers using the IEEE 754 standard. Representing the binary data as textual data involves a conversion that introduces rounding issues. So we would reduce the accuracy of the data already at the source of the replication, and the reconstruction of the binary data at the target could further decrease the accuracy. Directly transferring the binary data avoids the second issue.
The previous observations leave us with the transform groups ST_WellKnownBinary and ST_Shape as possible options. A close observation of the ESRI shape format [6] reveals that the shape format cannot make any distinction between multi-part geometries with only a single part, for example multipoint(10 10), and pure single-part geometries, for example point(10 10). Thus, the specific type information could be lost during the replication process if any type of geometry is stored in the table to be replicated. However, the argument does not apply if we know that the spatial data has a certain more specific type. Using the proper constructor function on the target table will ensure that the correct specific type is reconstructed.
Another advantage of the well-known binary format exists on big endian systems. The format supports little as well as big endian encoding. The shape format stores all information in little endian (except the header for each record) and, thus requires the conversion of each floating point number from big to little endian.
On the other hand, the well-known binary format has the disadvantage that it is slightly more verbose than the shape format. It requires a few more bytes to represent a geometry. So preferring the group ST_WellKnownBinary over ST_Shape is not mandatory because both groups serve the same purpose sufficiently.
Unfortunately, DB2 replication does not natively support DB2's transform groups mechanism. It does not allow the user to specify a certain group to be used when fetching the data from the source table or when the data is inserted into the target table. The replication tools will recognize spatial columns only as columns of an unsupported type. The consequence is that we have to transform the spatial data manually before the replication processes start to deal with it.
This limitation introduces some inconveniences, but it does not prevent us from replicating spatial data. We will define a view over the source table in such a way that the transformation of the spatial data to an external format happens in the view. The view so created is now used as the replication source in the subscription sets.
When replicating LOB data, the LOBs will not be stored in the CD tables or views. Instead, only an update indicator will be maintained there. The update indicator will be used by the Apply process to determine whether an update was performed on a LOB value or not. If there was no update, then there is no need to copy the LOB value from the source to the target table. This approach avoids unnecessary data movement.
Please note that insert operations will always copy the LOB data because it is part of a new record and does not yet exist in the target table. Due to the manual transformation we will implement in the view, we do not have an update indicator for the CD table that corresponds to our source table. As mentioned before, the replication tools will not recognize the spatial column as being a column that could be replicated. The consequence is that we have to provide and maintain such an indicator column ourselves.
The definition of the view and the addition of the update indicator column lead to a 4-step approach that we will outline now as an example. Let's assume we want to replicate all the data, including the spatial data, from the table defined in listing 2.
Listing 2. Defining the source table to be replicated.
CREATE TABLE streets (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
track db2gse.ST_LineString
)@
|
Defining the update indicator
The first step is to add a column for the update indicator along with 2 triggers to maintain it. One trigger is used to reset the indicator every time an update on the row occurs. The second trigger is responsible for setting the indicator if an update to the geometry column -- only the geometry column -- occurs. Triggers fire in the order of their definition, that is, the trigger with the earlier CREATE_TIME will fire before a trigger defined after it. Because we want to reset the indicator and set it again if the geometry is updated, we have to pay attention to the order of the trigger execution. The trigger to reset the indicator must be created first, and the trigger to set the update indicator has to be created after it. The SQL statements in listing 3 show the necessary modifications and trigger definitions in the correct sequence.
Listing 3. Adding the update indicator for the spatial column.
ALTER TABLE streets
ADD COLUMN track_update CHAR(1)@
CREATE TRIGGER streets_update NO CASCADE BEFORE
UPDATE ON streets
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
SET n.track_update = NULL@
CREATE TRIGGER streets_track_upd NO CASCADE BEFORE
UPDATE OF track ON streets
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
SET n.track_update = 'U'@
|
Creating the view over the source table
To have all the data in the correct format for the replication, we need to define a view over the source table. The view definition will refer to all columns from the source table that are to be replicated. There are two spatial columns. The spatial column needs to be converted to the well-known binary format as described above. The ST_AsBinary function is used to do the conversion. The update indicator is the other column that does not appear in the view definition. We do not want to replicate the update indicator; we just need it for the Apply process to determine whether data from the spatial column is to be replicated. So we use the SQL statement in listing 4 to create the view over our replication source table.
Listing 4. Creating the view to convert the spatial data to BLOBs.
CREATE VIEW streets_repl_view(id, name, wkb) AS
SELECT t.id, t.name, db2gse.ST_AsBinary(t.track)
FROM stolze.streets AS t@
|
Please note that you should always specify the fully qualified table names and use correlation names in the view definition so that the replication tool can successfully parse the definition and extract the relevant information needed later on.
The order of creating the view and adding the update indicator to the source table is irrelevant. Both steps can be executed completely independently. However, the subsequent steps require that the previous setup be already completed.
Registering the source table as replication source
Now that all the preparation is completed, we can begin the setup procedure for the actual replication. When replicating from a view, DB2 replication requires that the tables on which the view is based are also registered as replication source tables. Otherwise, we could not take advantage of the Capture process to scan the log files and find all the rows that are modified and need to be replicated. Such a restriction would imply that we could only do a full refresh for the replicated table in the target database. This is clearly not an option in many situations.
Below, we assume that you already created the capture control tables and enabled the database for replication. The enabling requires that the database configuration parameter LOGRETAIN is set to YES. Please note that you have to backup the database once you modified the LOGRETAIN parameter. Please refer to [4] for more details to set up replication for your database.
Registering the source table is straightforward. Changes on all columns including the update indicator but excluding the spatial column need to be captured. Figure 6 shows how this is done from the DB2 Replication Center. As you can see in this figure, no changes on the column TRACK can be captured because it is a column with a structured type.
Figure 6. Registering the table STREETS as replication source.
The registration process creates a change data table (CD table) that is used by the Capture process to keep track of any changes on the table STREETS extracted from the DB2 log. Let's quickly verify this. Listing 5 shows several SQL statements that modify data in the STREETS table and then query the CDSTREETS table to see if the correct data is captured and populated. Please note that you might have to wait a few moments before the Capture process extracts the data from the log and applies it to the CD table.
Listing 5. Testing the Capture setup.
INSERT
INTO streets(id, name, track)
VALUES (1, 'my first street',
db2gse.ST_LineString ('linestring(10 10, 20 20)', 1))@
SELECT id, VARCHAR(name, 20), wkb FROM cdstreets_repl_view@
ID 2 WKB
----------- -------------------- ---
1 my first street -
1 record(s) selected.
UPDATE streets
SET name = 'different street'
WHERE id = 1@
SELECT id, VARCHAR(name, 20), track_update FROM cdstreets@
ID 2 TRACK_UPDATE
----------- -------------------- ------------
1 my first street -
1 different street -
2 record(s) selected.
UPDATE streets
SET (id, track) = ( 2, db2gse.ST_LineString(
'linestring (5 10, 5 20)', 1) )
WHERE id = 1@
SELECT id, VARCHAR(name, 20), track_update FROM cdstreets@
ID 2 TRACK_UPDATE
----------- -------------------- ------------
1 my first street -
1 different street -
2 different street U
3 record(s) selected.
|
Registering the view as replication source
The last step before we can start the capture process is to register the view STREETS_REPL_VIEW as replication source. The view can then be used in the subscription sets and contains the spatial data encoded in well-known binary format.
Unfortunately, the view cannot be registered as it is using the DB2 Replication Center. The replication tool wants to create a change-data view (CD view) that is based on the CD tables for all the tables on which the view is created. All references in the CD view must find corresponding targets in the CD table. In our situation, this means the following. The CD table contains the columns ID, NAME, and TRACK_UPDATE. It does not contain the column TRACK because that column could not be registered as explained above. The view STREET_REPL_VIEW refers to the columns ID, NAME, and TRACK from the table STREETS. The corresponding CD view would have to refer to the same columns in the CD table. Figure 7 depicts these dependencies.
Figure 7. Dependencies between views, tables, CD views, and CD tables.
The replication tools analyze the view definition of STREETS_REPL_VIEW and find the column TRACK as input for the ST_AsBinary() function. But there is no corresponding column TRACK in the CD table and, therefore, no CD view could be created. The error message you see in figure 8 is raised instead.
Figure 8. Error message when registering STREETS_REPL_SOURCE as replication source.
Besides the problem I've described of the non-existence of the column TRACK in the CD table, we have another issue to deal with. The resulting CD view should not contain the BLOB column, that is the result of the function call "ST_AsBinary(TRACK)", but rather the update indicator column TRACK_UPDATE. The update indicator is already available in the CD table, so the dependency can be satisfied quite easily.
In order to create the required CD view, we will now use a dummy view over the base table STREETS. The view is created in such a way that it contains the TRACK_UPDATE column instead of the TRACK column, and it will have the same name as the transformed TRACK column in the STREETS_REPL_VIEW, that is WKB. The corresponding CREATE VIEW statement can be found in listing 6.
Listing 6. Creating a dummy view with the update indicator.
CREATE VIEW spatial_replication_dummy(id, name, wkb) AS
SELECT t.id, t.name, t.track_update
FROM stolze.streets AS t@
|
Beginning the registration of this dummy view is straightforward as Figure 9 illustrates. During the registration, we also give the CD view a more appropriate name.
Figure 9. Registering the dummy view
Generating the SQL statements in this way is straightforward and does not produce any errors or warnings. The SQL statements can also be executed as they are. But the dummy view is not the view which we really want to replicate. Thus, one modification to correct the view name is necessary in the replication catalog tables. The catalog table IBMSNAP_REGISTER contains the fully qualified table name in the columns SOURCE_OWNER and SOURCE_TABLE. That data needs to be adjusted to refer to the view STREETS_REPL_VIEW. Listing 7 shows the content of the IBMSNAP_REGISTER table after the dummy view was registered as replication source.
Listing 7. Content of the IBMSNAP_REGISTER catalog table.
SELECT source_owner, source_table FROM asn.ibmsnap_register@
SOURCE_OWNER SOURCE_TABLE
--------------- -----------------------------
STOLZE SPATIAL_REPLICATION_DUMMY
STOLZE STREETS
|
Correcting this entry is straightforward. A single UPDATE statement can be used to achieve the necessary correction. After you make the change, you can drop the dummy view because it is no longer needed. Both steps are illustrated in listing 8.
Listing 8. Correcting the replication catalog information and cleaning up.
UPDATE asn.ibmsnap_register
SET ( source_owner, source_table ) =
( 'STOLZE', 'STREETS_REPL_VIEW' )
WHERE source_table = 'SPATIAL_REPLICATION_DUMMY'@
DROP VIEW spatial_replication_dummy@
|
As a final step, you can double-check the correct registration of the view in the DB2 Replication Center. Selecting the "Registered Views" shows you the STREETS_REPL_VIEW as you can see in Figure 10.
Figure 10. Verifying the registered views.
With the successful registration of the view, you can now define subscription members that reference the view as replication source and replicate the data accessible through the view to target tables in different databases. The setup of the target tables is explained in detail in the following section.
Preparing the target database
When defining a replication source to target mapping, the target is typically a base table. But in some cases a view can be substituted as is done in our spatial replication scenario. If using a target view, the target table and view over this target table must be defined prior to defining the mapping using the Replication Center. In the past, target views were typically used for consolidation, union, or outer join scenarios. Now with the introduction of INSTEAD OF triggers in DB2 UDB Version 8.1, the target view has a completely new potential application. The triggers allow you to define a different logic that will be driven when an INSERT, UPDATE, or DELETE statement is issued against the view. Therefore, using a target view coupled with such INSTEAD OF triggers expands the possibilities for data transformation during the Apply process in general.
After we provided the source view which performs the transformation of the spatial data to the external data format, and registered this view as a replication source, we can now use this view in a subscription member. The BLOB in which the spatial data was converted is not directly supported by the DB2 Spatial Extender. In particular, a BLOB is not an ST_Geometry value and, thus, cannot be used as input for the wide variety of spatial functions defined by the extender. First it is necessary to perform the reverse transformation from the BLOB to the geometry types. This section gives a step-by-step explanation of the setup needed on the target database.
Overview
Due to the spatial data, we have some additional work to be done for the target tables. Again, the replication tools are not aware of the spatial data types (or any other structured types), and the tools also do not know that we transfer the spatial data in the well-known binary format. We have to implement a mechanism that takes the binary data and converts it to the corresponding geometries on the fly, that is, when the Apply process inserts the data into the target table.
A very simply solution can be implemented using INSTEAD OF triggers which are available in DB2 UDB Version 8.1 and later. We create the target table ourselves, add a view on top of it and supply 3 INSTEAD OF triggers on the view which will perform the transformation from the external data format to the geometries and then insert the data into the target table. Figure 11 illustrates this in more detail. Please note that the additional processing prevents us from using the target tables that would be generated by the replication tools. Instead, we need to supply the tables ourselves.
Figure 11. Processing at the target database.
Creating the base table and view
The first step is the creation of the base table and a view on top of the base table together with the set of INSTEAD OF triggers. The definition of the base table and the view is straightforward because it can simply be copied from the source database, but we do not need to add the update indicator column for the target table. Listing 9 repeats those steps. We could choose the same table and view names if the target table is in another database or another schema. However, for the example I used the prefix "TG_" to clarify which tables I'm referring to.
Listing 9. Creating the target table and a view on top of it.
CREATE TABLE tg_streets (
id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(200) NOT NULL,
track db2gse.ST_LineString
)@
CREATE VIEW tg_streets_repl_view(id, name, wkb) AS
SELECT t.id, t.name, db2gse.ST_AsBinary(t.track)
FROM stolze.tg_streets AS t@
|
With the table and view in place, you can create three INSTEAD OF triggers. One trigger takes care of INSERT operations that happen when new data is replicated from the source to the target table. The second trigger applies to UPDATEs, and it is necessary when data that exists in the source and the target tables was modified at the source. And the third trigger will remove data from the target table if a DELETE operation was performed on the corresponding rows in the source table. Listing 10 shows the definitions for the three triggers on the view TG_STREETS_REPL_VIEW.
Listing 10. Defining the INSTEAD OF triggers on the view over the target table.
CREATE TRIGGER tg_streets_repl_i INSTEAD OF
INSERT ON tg_streets_repl_view
REFERENCING NEW AS n
FOR EACH ROW
MODE DB2SQL
INSERT
INTO tg_streets(id, name, track)
VALUES ( n.id, n.name, db2gse.ST_LineString(n.wkb))@
CREATE TRIGGER tg_streets_repl_u INSTEAD OF
UPDATE ON tg_streets_repl_view
REFERENCING NEW AS n OLD AS o
FOR EACH ROW
MODE DB2SQL
UPDATE tg_streets
SET ( id, name, track ) = ( n.id, n.name,
db2gse.ST_LineString(n.wkb, 1) )
WHERE id = o.id@
CREATE TRIGGER tg_streets_repl_d INSTEAD OF
DELETE ON tg_streets_repl_view
REFERENCING OLD AS o
FOR EACH ROW
MODE DB2SQL
DELETE
FROM tg_streets
WHERE id = o.id@
|
Now that everything is properly set up, you can define the subscription set and its members.
Creating a subscription set
The first step to actually replicate spatial data is to create a subscription set if you do not have one already. A subscription set usually groups several source table/target table mappings that specify from which source table you want to replicate data to which target tables. The data in all the mappings in a single subscription set is replicated together. You can also define several subscription sets, all of which are independent of each other.
Here, we create an empty subscription set. This set is used in the subsequent steps where the subscription members are added to the set. Figure 12 shows how we define the subscription set named SPATIAL. We use the capture server SPATIAL as the database where the source tables are residing, and the apply control server is named REPL, which is the database where the target tables will be kept.
Figure 12. Creating a new subscription set.
Of course, you could already add some or all members when you create a subscription set. This is done on the "Source-to-Target Mapping" pane in the "Create Subscription Set" window. We won't define the members right away but will use a separate step instead. That way, the adding of a new member is easier to repeat.
For more details on the creation of subscription sets, please refer to the DB2 Replication User's Guide and Reference manual [4].
Adding subscription members for the spatial data
With the setup of the source and target tables, we can now define the subscription members. This is the final step to complete the administration tasks for spatial replication. The definition of the subscription members is a straightforward matter and the DB2 Replication Center is used to illustrate each step.
First, open the "Add Member to Subscription Set" window by choosing the "Add Member..." option from the context menu on the subscription set name. On the "Member Information" pane, add the registered source STREETS_REPL_VIEW. The DB2 Replication Center will generate a name for a new target table. You must correct that name to point to your existing view over the actual target table as shown in figure 13.
Figure 13. Adding the member to the subscription set.
A few more details need to be corrected, so select the Details... button and proceed in the window that opens. Of course, we also want to replicate the data in the WKB column. So we add it to the list of selected columns as is shown in figure 14. The second screen shot in figure 14 also shows how the column selected column WKB is mapped to the target column also named WKB. You drag an arrow from the blue button on the left to the red button on the right to establish the relationship between the two items.
Figure 14. Adding the column WKB to the list of selected columns.
Specifying that there is an index on the ID column in the next step is more for informational purposes. We know that such an index was created as part of the primary key on the underlying base table TG_STREETS. This step is necessary to complete the member properties. Figure 15 shows the details.
Figure 15. Specifying an index for the target table.
We do not want to specify any additional row filter, so we are done and select the OK button in the "Member Properties" window and in the "Add Members to Subscription Sets" window. The generated SQL statements for the source and target databases are executed and, thus, the definition of the member is completed.
After starting the Capture and Apply control servers, all spatial data is now replicated properly. Some small tests in listing 11 are used for verification. Please note that the SQL statements set in normal text, to be executed against the table STREETS, must be run on the source table in the source database. The queries set in bold text retrieve the results from the target table TG_STREETS in the target database. Also, there is a delay between the operations on the source database and the propagation of the data to the target table. The delay depends on the schedule you defined for the subscription set. You will have to wait with the execution of the statements on the target database until the changed data is replicated before you can see the results presented below.
Listing 11. Verifying the complete setup.
INSERT
INTO streets(id, name, track)
VALUES ( 1, 'first street',
db2gse.ST_LineString('linestring(10 10, 20 20)', 1) )@
SELECT id, VARCHAR(name, 20),
VARCHAR(track..ST_AsText(), 50)
FROM tg_streets@
ID 2 3
----------- -------------------- ------------------------------------------------------------
1 first street LINESTRING(10.000000 10.000000, 20.000000 20.000000)
1 record(s) selected.
INSERT
INTO streets(id, name, track)
VALUES ( 2, 'second street',
db2gse.ST_LineString('linestring(10 15, 20 15)', 1) )@
SELECT id, VARCHAR(name, 20),
VARCHAR(track..ST_AsText(), 50)
FROM tg_streets@
ID 2 3
----------- -------------------- ------------------------------------------------------------
1 first street LINESTRING(10.000000 10.000000, 20.000000 20.000000)
2 second street LINESTRING(10.000000 15.000000, 20.000000 15.000000)
2 record(s) selected.
DELETE
FROM streets
WHERE id = 1@
SELECT id, VARCHAR(name, 20),
VARCHAR(track..ST_AsText(), 50)
FROM tg_streets@
ID 2 3
----------- -------------------- ------------------------------------------------------------
2 second street LINESTRING(10.000000 15.000000, 20.000000 15.000000)
1 record(s) selected.
UPDATE streets
SET name = 'another name'
WHERE id = 2@
SELECT id, VARCHAR(name, 20),
VARCHAR(track..ST_AsText(), 50)
FROM tg_streets@
ID 2 3
----------- -------------------- ------------------------------------------------------------
2 another name LINESTRING(10.000000 15.000000, 20.000000 15.000000)
1 record(s) selected.
UPDATE streets
SET (name, track) = ( 'yet another name',
db2gse.ST_LineString('linestring(5 5, 1 1)', 1) )
WHERE ID = 2@
SELECT id, VARCHAR(name, 20),
VARCHAR(track..ST_AsText(), 50)
FROM tg_streets@
ID 2 3
----------- -------------------- ------------------------------------------------------------
2 yet another name LINESTRING(5.000000 5.000000, 1.000000 1.000000)
1 record(s) selected.
|
The examples above did not include any UPDATE operations on the ID column. If you intend to apply changes to the data in the primary key columns - or, to be more specific, the target key columns, - then you need to capture the before images for the columns from which the target key is derived. Otherwise, the Apply process will not be able to match the rows that were updated in the source table with the corresponding rows in target table. Please refer to the DB2 Replication User's Guide and Reference [4] for more details.
Setup tool "db2se_repl"
We looked at a detailed description of the necessary setup steps in the sections Capturing changes to spatial data and Preparing the target database. To simplify the administration tasks even further, we implemented a tool named "db2se_repl" using the Java programming language [7, 8]. The tool allows you to specify which source table, identified by its fully qualified name and the database where it resides, and the target table where you want to replicate the data.
Syntax
The specifications for the tool are syntax based, and below you can see the details of that syntax together with an explanation for the various options and keywords. The style for the syntax diagram follows the style used in the DB2 SQL Reference.
>>--SETUP SPATIAL REPLICATION------------------------------>
>----| source |--| target |--+------------------------+---><
‘--VERBOSE--+---------+--’
‘--TRACE--’
source:
|--REPLICATE TABLE--+-----------------+--table_name-------->
‘--schema_name--.-’
>-----+---------------------------+------------------------>
| .--,------------. |
| V | |
‘--(-----column_name--+--)--’
>-----FROM CAPTURE SERVER--database_url-------------------->
>-----USER--user_name--PASSWORD--password------------------>
>-----+-----------------------------------------------+----|
‘--USING--(--CAPTURE SCHEMA--capture_schema--)--’
target:
|--TO TARGET TABLE--+-----------------+--table_name-------->
‘--schema_name--.-’
>-----+---------------------------+------------------------>
| .--,------------. |
| V | |
‘--(-----column_name--+--)--’
>-----ON APPLY SERVER--database_url------------------------>
>-----+---------------------------------------+------------>
‘--USER--user_name--PASSWORD--password--’
>-----USING--(--*---SUBSCRIPTION SET--set_name--*--,------->
>-----*--APPLY_QUALIFIER--apply_qualifier--*--,------------>
>-----*--SPATIAL REFERENCE SYSTEM IDENTIFIER--srs_id--*--)-|
|
REPLICATE TABLE schema_name.table_name ( column_name, & )
Identify the table and its columns that is the source for the replication. If no column names are specified, then all columns of the table are to be replicated. Among those columns must be exactly one spatial column. The list of columns must contain the primary key of the source table.
FROM CAPTURE SERVER database_url
Specify the name and location of the database where the source table can be found. The database URL must have the following form:
//host-name:port/database-name
This information is used to connect to the source database using a Type 4 JDBC driver. That implies you do not have to catalog the database locally.
CAPTURE SCHEMA capture_schema
Specify the name of the capture schema in the source database. If this parameter is not specified, then the schema named "ASN" is used per default.
TO TARGET TABLE schema_name.table_name (column_name, … )
Use the identified table as the target table for the replication. If the table does not yet exist, then it will be created in the target database based on the definition of the source table. The column names are not specified here; they are derived from the source table.
If the target table already exists, it is reused and the specified column names (if any) must identify the columns in the existing table.
ON APPLY SERVER database_url
Specify the name and location of the database where the target table will reside. The database URL must have the following form:
//host-name:port/database-name
This information is used to connect to the source database using a Type 4 JDBC driver. That implies you do not have to catalog the database locally.
SUBSCRIPTION SET set_name
Identify the subscription set to which the source to target mapping will be added as a new member.
APPLY QUALIFIER apply_qualifier
Identify the apply qualifier that is used for this subscription set.
SPATIAL REFERENCE SYSTEM IDENTIFIER srs_id
Specify the numerical identifier for the spatial reference system that is needed for the reverse transformation of the well-known binary data to the spatial data. The spatial reference system must exist in the target database.
It is recommended that this spatial reference system (SRS) is the same as the SRS that is used for the data in the source database.
As a minimal requirement, you have to ensure that the source and target SRS are both based on the same coordinate system. Otherwise, your data will be represented in a different coordinate system, essentially giving you different data.
USER user_name PASSWORD password
Specify the user name and password that is to be used when connecting to the source or target database. If no user name and password were specified for the target database, then the user name and password provided for the source database will be used for the target database.
VERBOSE
Print all messages generated by the "asnclp" tool to the standard output. The default is to suppress those messages.
TRACE
Turn on the trace facility of the "asnclp" tool. The trace is written to the standard output stream.
Processing logic
The logic of the tool follows the description given in sections Capturing changes to spatial data and Preparing the target database. The setup of the source table is done only if this table was not yet registered for replication. If the registration was already completed, the previous definition is simply reused. Otherwise, the update indicator column is added to the source table, the source view is created, and the source table and source view are registered, leading to the creation of the CD table and CD view.
On the target side, the tool also tolerates an existing target tables. But it does not verify that the columns and data types match. If the table does not exist, it will be created. During that phase, we use the definition of the primary key on the source table also for the target table. This primary key is needed for a proper functioning of the INSTEAD OF triggers. The target view with those triggers is created before the subscription set member is defined.
The schema named DB2SE_REPL is used to create the views and triggers in. Please note that the unqualified names of the view are the same as the unqualified names for the tables. This implies that all tables to be set up this way must have unique unqualified names; that is, you cannot have two tables named TAB in two different schemas and successfully set them up for replication using the "db2se_repl" tool.
All replication specific commands are performed with the "asnclp" tool [5]. Therefore, no special logic beyond the handling for the spatial data is implemented in the tool. Please be aware that not all possible options from "asnclp" are promoted to the interface of "db2se_repl". We focused on keeping the interface simple and not on a rich feature set. For example, if you want to adjust the schedule for the subscription set or its type (default is to define a read-only set), then you should either create the subscription set yourself or modify it appropriately in the DB2 Replication Center once "db2se_repl" created it.
Sample output
The sections Capturing changes to spatial data and Preparing the target database used the table STREETS to be set up for replication. The same steps that were described above can be executed with the tool, and listing 12 shows you how this is done. For our example, we use the database named SRC_DB as the source database and TGT_DB as the target database. The table STREETS is placed in the schema S in the source database and the schema T in the target database. The output gives you a short description of the progress of the setup.
Listing 12. Using "db2se_repl" to set up STREETS for replication.
db2se_repl "SETUP SPATIAL REPLICATION
REPLICATE TABLE s.streets(id, name, track)
FROM CAPTURE SERVER //localhost:50000/src_db
USER db2admin PASSWORD ibmdb2
TO TARGET TABLE t.streets
ON APPLY SERVER //localhost:50000/tgt_db
USER db2admin PASSWORD ibmdb2
USING ( SUBSCRIPTION SET sp_set, APPLY QUALIFIER
apply_spatial, SRS ID 1 )"
Connecting to databases and checking setup...done.
Collecting defaults from database...done.
Extending source table with indicator column...done.
Creating replication view 'DB2SE_REPL.STREETS'...done.
Creating replication dummy view...done.
Registering source table and dummy view for replication...done.
Updating replication catalog...done.
Dropping dummy view...done.
Creating target table...done.
Creating replication view 'DB2SE_REPL.STREETS'...done.
Creating triggers on target view...done.
Creating subscription set 'SP_SET'...done.
Adding target view to subscription set 'SP_SET'...done.
Disconnecting from databases...done.
SER0000I The operation was completed successfully.
|
With the successful completion of the setup, you can start the capture and apply servers and all changes to the data, spatial or otherwise, is captured at the source table S.STREETS and applied to the target table T.STREETS or any other target database you might have defined. You can now run the same tests that were shown in listing 11 to verify the proper functioning of the spatial replication setup.
Side notes
The above described approach to replicate spatial data does not address all the different details that are applicable to the DB2 Spatial Extender or to DB2 Replication. Let's look at some of the issues now.
Not replicating -- geocoding
It is important to mention that you might not have to replicate the spatial data as described above in all cases. If you use a geocoder (as described in my previous article, [3]) on the source and target tables, then you can generate the spatial data again from other columns in the table, such as the address columns. You would replicate the address columns and have all the necessary information to geocode the address again to its corresponding spatial value.
The process to generate spatial data from address data is called geocoding. A geocoder is the function implementing the geocoding algorithm. The DB2 Spatial Extender User's Guide and Reference has an architecture to support many different geocoders. You can write your own user-defined function (UDF), which takes the address data as input parameter and converts it to spatial data. The address data can be any non-spatial data; it does not have to be a real-world address such as "555 Bailey Ave. San Jose, CA 95141".
If you use a geocoder together with your replication scenario so that you actually do not replicate the spatial data itself, then you will use a configuration as follows in Figure 16. There is a geocoder on the source table to generate the spatial data from the address data in the same table (1). The source table contains the spatial data and applications accessing the source table can also work with the spatial data. When replicating the information in the source table to a target table, you will replicate the columns that contain the address data, but you will not replicate the spatial column (2). In the target database, you use again a geocoder to convert the address data to the spatial data (3). Consequentially, the geocoding occurs two times for each row: once on the source table and once on the target table. Figure 16 summarizes the described configuration. The spatial column is marked blue, and the three different steps of geocoding on the source table, the replication, and the geocoding of the address data are numbered.
Figure 16. Geocoding replicated address data.
The additional geocoding of the address data on the target table might not be desired in many situations. For example, if you use a geocoder, you will have to accept the performance penalty for running the geocoder twice. Another consideration should be the possibility that the geocoder cannot geocode all your addresses. You could have manually set the coordinates for some addresses that could not be handled by the geocoder. This manual step would have to be repeated on the replicated data. Additionally, using a Web service based as in [3] imposes financial charges for the usage of the geocoder itself. Repeating the geocoding for the same data after it was replicated increases the fees for a single address. In such cases, the technique presented in this paper might be a viable option for you, despite its higher administration and setup requirements.
Please note that the geocoder-like approach could also be used if you employ an additional column and triggers to convert the spatial data to LOBs. The triggers will regenerate the value for the additional column (let's call the column SPATIAL_LOB). When replicating the data in the source table, you will also replicate the data in the SPATIAL_LOB column. At the target table, you will then use the same approach and store the replicated data in a separate column. Again triggers can be used to generate the spatial data from the replicated LOBs. Because the reconstruction is done using the spatial functions, you can simply consider those functions as "geocoder" and we have again the situation already explained above. The drawback is clearly the additional disk space that would be required and the need to perform the transformation to a LOB inside the user's transactions on the source table. Our technique requires only minimal disk space (a CHAR(1) column needs to be added), and the transformation workload is completely handled by the Apply process.
INLINE LENGTH for spatial columns
The aspect of the INLINE LENGTH was ignored throughout the paper. When creating tables you should consider the specification of the INLINE LENGTH parameter for the spatial columns. The INLINE LENGTH defines how many bytes a structured value can occupy at most to still be stored like a VARCHAR FOR BIT DATA together with the other data in the row. If the structured value is longer, it will be stored like a BLOB in the long tablespace associated with the table. A larger INLINE LENGTH allows for more values (spatial data) to be stored with the rest of the table's data.
The benefit of storing structured values as VARCHAR FOR BIT DATA is clearly that those values are cached in the DB2 buffer pool, whereas LOBs are directly read from and written to the disk, leading to higher I/O.
Replication options
DB2 replication allows many other options to control the replication mechanisms. For example, you can change the schedule, define additional SQL statements to be executed at certain points during the replication process, group subscription sets to different apply qualifiers, create indexes, filter rows, specify tablespaces, and so on. All those options are (mostly) orthogonal to the spatial replication described in this paper. Please refer to the DB2 Replication User's Guide and Reference to learn more about them.
Replicating LOBs
The spatial data is transformed to LOBs, the LOBs are replicated, and then transformed back to spatial data for the target table. The approach of using LOBs implies that the special considerations for replicating LOB data is implicitly applicable to spatial data as well. For example, the Apply process will pick the latest version of the spatial data from the source table. That version might be different than the version of the other data in the rows retrieved from the CD table. Such an inconsistency will be corrected with the next Apply cycle, but you should be aware of this possibility.
Summary
This article has shown you an approach to replicating spatial data using DB2 UDB Version 8.1 and DB2 Replication. We followed the well-established technique of converting the spatial data to an external data format (binary in our case) using a view over the source table. The corresponding CD table and CD view need to be slightly adapted to fit our needs. With the proper setup on the source tables, the Apply process captures all changes on the spatial data in form of BLOBs and applies it to a target view. Together with three INSTEAD OF triggers, the view defines the interface for the Apply process to work with. The triggers specify the reverse transformation and construct the spatial data again exactly as it existed in the source table. The result is a seamless replication of the spatial data. We looked at a detailed description of how to set up the source table and view as well as the target table with its view and triggers.
To further simplify the setup tasks, you can use the tool "db2se_repl". The tool executes the necessary SQL statements and replication commands to create the tables, views, and triggers, and to issue the necessary replication commands using the "asnclp" command line interface for the replication functionality.
Acknowledgments
The author wishes to thank Beth Hamel from the DB2 Replication team for her continued support in writing this paper and providing much information for the understanding of replication and its workings. Also, Serge Rielau and his team from the DB2 UDB SQL Compiler Development prepared the way for the presented technique by providing INSTEAD OF triggers in DB2.
Footnote
1Please refer to the Chapter “Replicating special data types", section “Replicating large objects" in the DB2 Replication Guide and Reference manual regarding the considerations when replicating LOB data.
References
[1]DB2 Spatial Extender User's Guide and Reference
[2]DB2 SQL Reference
[3] Integrating custom geocoders with the DB2 Spatial Extender, (developerWorks, May 2003)
[4]DB2 Replication User's Guide and Reference
[5]DB2 Replication "asnclp" program at http://www.ibm.com/software/data/dpropr/asnclp/
[6] ESRI Shapefile whitepaper at http://www.esri.com/library/whitepapers/pdfs/shapefile.pdf
[7] DB2 Application Development Guide
[8]Optimal DB2 performance with SQLJ and JDBC, (developerWorks), at https://www6.software.ibm.com/reg/devworks/dw-db2sqlj-i?S_TACT=102B7W81&S_CMP=DB2DD
Download | Name | Size | Download method |
|---|
| db2se_repl.zip | 8.47 KB | FTP|HTTP |
About the author | 
| |
Knut Stolze started his work with DB2 when he joined IBM as a visiting scientist at the Silicon Valley Lab where he worked on the DB2 Image Extender. He moved on to the DB2 Spatial Extender Version 8 and was responsible for several enhancements to improve the usability, the performance, and the standard-conformance of the Extender for more than two years. Currently, he works as a teaching assistant at the University of Jena, Germany, and continues his work for IBM in the area of federated databases. He can be reached through the newsgroups comp.databases.ibm-db2 and ibm.software.db2.udb.spatial or at stolze@de.ibm.com. |
Rate this page
| |
