Understanding the Data

Application integration using data sources may sound simple, but first impressions can be deceiving. To use data sources effectively for integration, it is essential that EAI architects, developers, and administrators understand the database metadata and structures used by the underlying applications and how data flows. The metadata for the data sources must be well understood so that the appropriate fields are moved, transformed (when necessary), and interpreted correctly.

In the proposed scenario, the source of the data will be coming from DB2 tables on the AS/400. The metadata for the DB2 tables must be understood to identify the relevant fields used in analysis. It is only necessary to move data fields that are needed by the analysis and reporting applications on the desktop. The fewer database tables and columns of data from tables that need to be moved and transformed, the faster the process for data movement and transformation can proceed. The time required for this data movement and transformation may well limit the overall options for deployment and how frequently data can realistically be transferred.

Some of the data types supported by IBM DB2 are extensions to the SQL-92 standard (sometimes referred to as SQL-2) that most major database systems support. If these data types are used by the business applications in the DB2 tables, then columns in the tables will need to be mapped and transformed to appropriate data types supported by the destination data source, SQL Server 2000 in our example. For example, IBM DB2 supports several SQL data types listed below that may require mapping when transformed to other database systems such as SQL Server:

BLOB - a binary large object
CLOB - a large object containing single-byte character data
DATE - Year, month, and day (the format for this data type in an RDBMS is often different)
DBCLOB - a large object containing double-byte character data
DECIMAL - a decimal number with fixed values for precision and scale.
GRAPHIC - a fixed-length string of double-byte characters (used to represent characters in some Asian languages)
TIME - Hour, minute, and second (the format for this data type in an RDBMS is often different)
VARGRAPHIC - a varying-length string of double-byte characters (used to represent characters in some Asian languages)

The GRAPHIC and VARGRAPHIC data types are specific to IBM DB2 and are for storing strings of double-byte characters used in several Asian languages. The large object types are not a part of the original SQL-2 standard. The BLOB and CLOB data types are included in the later SQL standards (often referred to as SQL-3) adopted by ISO in 1999 through 2001, but most database products are not yet conforming to these standards. Also, many database vendors used their own interpretation for the DATE and TIME SQL data types.

Previous Next