Welcome to Adventure Works, DB2 for i!

The DB2 for i database offers little in the realm of test data.  There is the ever popular QIWS.QCUSTCDT sample customer table and the CORPDATA sample database.   The CORPDATA database is decent in terms of design but the volume of data is negligible. In contrast, Microsoft SQL Server users have for many years enjoyed the AdventureWorks sample data; an option that is now available to DB2 for i.

What purpose does sample data serve?

A sample database can be useful to database developers for many reasons:

• Training Developers
• Performance testing (if enough data is available)
• Exploring new or unfamiliar features
• If the data is available to all, ideas can be shared and demonstrated with other DB2 for i professionals

The AdventureWorks sample data is great because it is freely available under the Microsoft Public License.   Though designed for SQL Server, ported the database structure and data has been ported to DB2 for i. However, not all of the SQL Server features are implemented.

Why would you want to use the AdventureWorks database?   DB2 for i has a host of relatively new features that you may not have in your legacy database including large object (LOB) columns (XML/image/document), user-defined distinct types, check constraints, foreign key constraints, journaling, etc.  All of these things can be investigated with AdventureWorks.

The DB2 for i Data

The list of the AdventureWorks 2012 tables and the row counts are shown below (note the DatabaseLog table was not migrated from SQL Server to DB2 for i):

Differences with SQL Server version

Since SQL Server and DB2 for i support different features, some of the AdventureWorks features were dropped or modified when brought to DB2 for i.

The differences include:

• The SQL Server schemas in AdventureWorks are not preserved.  The closest analog to SQL Server’s “schema” in DB2 for i is the concept of the object owner and it is only significant when using the SQL naming convention.   All tables are placed in one DB2 for i schema (aka library) called ADVWORKS12.
• The hierarchy data type is not present in DB2 for i so it was simply changed to VARBINARY(32).
• XML schema (XSD) definitions were ignored.
• XML indexes are unsupported and ignored.
• Computed columns are unsupported in DB2 for i and were ignored.
• GUID row ids were migrated as BINARY(16).  DB2 for i doesn’t not support the UNIQUEIDENTIFER (aka GUID/UUID) data type.
• The geo-spatial CLR data types (.NET complex objects) are unsupported.  However, the Latitude and Longitude values stored within this column in the Address table were migrated to two new columns named LATITUDE and LONGITUDE in the DB2 for i version of the Address table (these columns are not present in the SQL Server version.) 
• Various column data type changes were made: MONEY and SMALLMONEY were changed to DECIMAL.  The BIT data type was changed to SMALLINT. The DATETIME columns were changed to either DATE or TIMESTAMP (or TIMESTAMP(3) in the IBM i 7.2 version)
• The column name PRIMARY in table ProductProductPhoto is required to be quoted because PRIMARY is a reserved word.
• All column and table names in DB2 for i are converted to upper case.
• The “extended properties” (aka table, constraint and column descriptions) were converted to labels in DB2 for i.  However, the text is truncated at 50 characters.
• Table DatabaseLog was deemed unnecessary and therefore it was not migrated.
• Various CHECK constraints were eliminated because preserving their criteria involved using DB2 for i special registers which is not allowed by DB2.
• Check constraint CK_ProductInventory_Shelf involved a complex LIKE predicate that is not supported.   However, this constraint can be implemented using REGEXP_LIKE if your DB2 for i version (7.1-TR9, 7.2-TR1) supports the new regular expression functionality.

Because of these structure changes (like missing computed columns) and some of the differences SQL dialects, I didn’t port any of the T-SQL code to DB2 for i.

Likewise, clustered indexed views & XML indexes are not supported. However, an MQT could possibly serve as a candidate for replacing a clustered index view, though the MQT refresh would not be done in real time.

Some features that were implemented in the migration are:

• Many CHECK constraints
• FOREIGN KEY constraints
• User-Defined Types

The DB2 for i source is found here.  There are two save file versions available: V7R1 and V7R2.  The main difference between the two versions is that the V7R2 version has all of its TIMESTAMP columns defined as TIMESTAMP(3); which is the closest DB2 for i 7.2 equivalent to SQL Server’s DATETIME data type.

Happy testing!