With the release of V4R4 of OS/400 earlier this year, IBM introduced us to DB2 Universal Database (DB2 UDB) for AS/400. Because DB2/400 is integrated with the AS/400, those not working with the AS/400 on a daily basis have sometimes mistakenly labeled DB2/400 as proprietary in nature and thus unable to communicate with the rest of the computing world. Heck, some AS/400 professionals even think that way. DB2/400 is far from proprietary, however. Even before the release of DB2 UDB for AS/400, DB2/400 was more than a reliable, relational database for the AS/400. Because of its ties to SQL, DB2/400 was universally accessible. This means that applications could be written outside the AS/400 to access and manage DB2/400 data. Because of this functionality, in addition to the reliability and scalability, DB2/400 could be considered a universal database even before the enhancements gained in V4R4. Now, with the AS/400 becoming more involved with the Internet and e-business, its role as a universal database server is becoming increasingly important.

So what’s new and improved with DB2 UDB for the AS400? Well, there have been improvements made in the areas of scalability and database management. The new release includes derived tables, Operations Navigator SQL scripting functions with Client Access, and query enhancements. The most dramatic change to DB2/400 is in the area of extensibility, which includes support for large objects (LOBs), DataLinks, user-defined data types (UDTs), and user-defined functions (UDFs). In this article, I focus on the extensibility enhancements. I discuss each enhancement and demonstrate how to use UDTs, UDFs, and LOBs to better store and manipulate objects in your application.

Why Database Extensions?

One of the most important developments in programming language evolution is object- oriented technology. This technology allows entities in your application to be modeled as independent objects and related to one another in a classlike environment. You can capture the similarities and differences among objects and group those objects together into their related types. Objects of the same type behave in the same way because they share the same set of type-specific functions. These functions reflect the behavior of the objects in your application. Until recently, AS/400 programmers were immune to object-oriented technology. However, with the introduction of ILE and Java to the AS/400, the AS/400 is

now becoming a primary player in the object-oriented arena. As the AS/400 moves toward object-oriented technology, it follows that its database should, too.

The object extensions of DB2 UDB allow you to realize the benefits of object- oriented technology while building on the strengths of relational technology. In your traditional, relational database application, data types describe the data stored in your tables. The instances (or objects) of these data types are the stored data. For example, say you have a table with a 20-byte character column called NAME. That’s your data type. If you place a name in that column, say KEVIN, you have now created an instance (or object) of that data type. Programs, procedures, or functions such as SQL, RPG, or CL support operations on these instances. The DB2 UDB approach to support object extensions fits exactly into that relational design. UDTs are data types that you define. UDTs, like built-in types, can be used to describe the data stored in tables. UDFs are functions that you define. UDFs, like built-in functions, programs, or procedures, support the manipulation of UDT instances. Thus, UDT instances are stored in tables and manipulated by UDFs in SQL queries. UDTs can be internally represented in different ways. LOBs are just one example of this. Let’s take a closer look at each extension.

Large and In Charge

Until V4R4, DB2/400 could store text data in data types up to 32 KB in length. With the need to store larger text data as well as a wider variety of data, such as audio, video, drawings, graphics, and images, the implementation of LOB data types was required. DB2 UDB provides three new data types to store these larger data objects as strings of up to 15 MB in size. The three data types are binary large objects (BLOBs), single-byte character large objects (CLOBs), and double-byte character large objects (DBCLOBs). You can use BLOBs to store photos and images, which are stored in binary format. CLOBs and DBCLOBs allow you to store large text objects, such as proposals or resumes. The following is a typical SQL statement that creates a table using the new data types:

Create Table MyTable (

IDNumber Integer,

Name VarChar (50),

Resume CLOB (1M),

Photo BLOB (8M),

Handbook DBCOB (6M))

Actually, the 15-MB limit is not imposed at the column level; it is imposed at the row level. A single row containing one or more LOB values cannot exceed 15 MB of LOB data; a table may contain nearly 256 GB of LOB data. You can refer to and manipulate LOBs using variables just as you would any other data type. However, variables use the memory buffer, which may not be large enough to hold LOB data. SQL provides a locator (or handle) for referring to or manipulating LOB data. Locators let you get at your LOB data without moving that data into memory.

A LOB locator is a 4-byte value stored in a variable that your program uses to refer to a LOB value. By using a LOB locator, your program can manipulate the LOB value as if the LOB value itself were stored in the variable. The LOB locator is associated with a LOB value, not a row in the database. Therefore, after you’ve referenced a LOB value in your program, no action can take place against a row or table that will have an effect on the locator value. Think of the LOB locator as you would a record lock in DB2/400. When you access a record for update in an RPG program, nothing can change that record until you release the lock. The locator value is valid until the job containing it ends or the locator is explicitly freed. The Free Locator statement releases a specific locator from its associated value. A commit or rollback operation frees all LOB locators.

You don’t have to select a LOB value into a LOB locator. You can select a LOB value directly into a variable, as you’re accustomed to doing with traditional data types, or

you can select the LOB value into a reference file variable that will exist as a file in the AS/400 Integrated File System (AS/400 IFS). Reference file variables are used like host variables, except that they transfer data to and from the AS/400 IFS instead of to and from memory as host variables do. There is no hard-and-fast rule as to which method to use. If the LOB value is particularly large and is needed only as an input value, you may want to use a locator. If your program needs the entire LOB value regardless of size, then you must move it to a variable. Even in the latter case, you might be able to use a locator and move the value a little at a time into your variable.

DataLinks

Another data type included with DB2 UDB is the DataLink. This is a great way to extend the data types that a database can store. DataLinks are also valuable when you need to store data that exceeds the limits imposed by DB2 UDB. The reason you can get away with such things is that the data stored in DataLinks are really pointers to data in other file systems, possibly on other machines. The DataLink pointer is actually in the form of a URL. DataLinks come in handy when, for example, a user has thousands of video clips stored in the AS/400 IFS. The user may want to create a DB2 UDB table to contain information about the video clips, but because the data already exists in directories on the AS/400 IFS, there is no need to duplicate it in a DB2 table. Besides, some of the video clips might be larger than 15 MB, and therefore, you cannot store them in a BLOB.

The user can create or modify a table to reference the objects on the AS/400 IFS using DataLinks. The DB2 table can use traditional data types to store information about each clip, such as title, length, and date. However, the clip itself would be referenced by using a URL in a DataLink column. The advantages to using this technique are that the data stored on the AS/400 IFS can be in any type of stream file and can exceed the storage limits of DB2 UDB LOBs. You are also able to take advantage of the strengths of each file structure. You can use DB2 for its query and reporting strengths and the AS/400 IFS for its file streaming capabilities. The following is how you would define a DataLink column in a DB2 table:

Create Table Rental (

Cust_ID Integer.

Name VarChar (50),

Video_ID Integer,

Description VarChar (50),

Length Integer

Clip Datalink (40))

Those of you aware of DB2’s excellent built-in data integrity and security capabilities may be wondering how DataLinks are handled, if at all. The good news is that you can apply the same integrity and security rules that you would any other data type that is local to your database. You can define constraints to your DataLink column to stop someone from deleting or updating the linked file. In addition, you can ensure that the URL is valid before attempting to link to an external file. To show you what you can do, I’m going to add some constraints to the CREATE command:

Create Table Rental (

Cust_ID Integer.

Name VarChar (50),

Video_ID Integer,

Description VarChar (50),

Length Integer

Clip Datalink (40)

File Link Control

Integrity All

Write Permission Blocked )

Take a look at the File link control parameter. In the first example, I did not specify this parameter, so the URL is checked for its syntax only. There is no guarantee that the URL is valid, because is not checked. In my second example, the File link control parameter tells the system to verify that the DataLink value is a valid URL, with a valid server name and file name. The URL link must exist at the time that row is inserted into the table. When the object is found, it is then marked as linked. The linked object cannot be moved, deleted, or renamed during the time that it is linked. If you decide to use the File link control parameter for a link to a remote system, that system must be running OS/400 V4R4 or Advanced Interactive eXecutive (AIX) with DB2 UDB. The Integrity all

parameter stops the link from being deleted. To delete the link, first delete the row in the DB2 table that refers to the link. The Write permission blocked parameter blocks all direct updates to the linked object. If users want to make changes, they must first copy the linked object, make the changes, and decide if they want the DB2 table to link to the new object.

If BLOBs, CLOBs, DBCLOBs, and DataLinks aren’t enough to satisfy your hunger for data types, there is another, introduced with DB2 UDB, called UDT. With UDTs, you can further define your database to more closely resemble the data in your specific application. The most common example I see is with currency. If you deal in multiple currencies, you probably have a data type defined as Decimal (11, 2) and store all the currencies, regardless of origin, in that column or field. You probably use some other method to determine if the data in that decimal field is in U.S. dollars, Canadian dollars, or French francs. With UDTs, you can break down your data types to better represent your data. You can define three data types called USDollars, CanadaDollars, and FrenchFrancs, all Decimal (11, 2). These data types provide a better representation of your data and remove the need to further define a record type as U.S., Canadian, or French. Now that you have distinct data types, you can perform meaningful currency comparisons and arithmetic operations.

To create a new data type, you use the CREATE command. You must base a UDT on an internal DB2 data type. You cannot create a UDT based on another UDT. The example below shows how to create my three distinct currency data types:

Create Distinct Type USDollars

As Decimal (11, 2)
Create Distinct Type CanadaDollars

As Decimal (11,2)
Create Distinct Type FrenchFrancs

As Decimal (11,2)

You can also use the new LOB data types as references for UDTs, especially if you are going to use functions to extract data from those forms. You would define them in the same manner as the currency data types:

Create Distinct Type DetailSpecification

As CLOB (1M)

After the UDTs are created, you can use them as you would any data type, as shown below:

Create Table MyTable (

Item_Number Integer,

Item_Desc VarChar (40),

US_Price USDollars,

UDT

US_Cost USDollars,

Canada_Price CanadaDollars,

Canada_Cost CanadaDollars,

French_Price FrenchFrancs,

French_Cost FrenchFrancs )

As a part of the UDT creation process, DB2 creates a casting function to help you convert values to and from the new type. For example, by creating a UDT called USDollars, DB2 created a function called USDollars that converts the base data type, Decimal, to the new UDT, USDollars. In turn, DB2 created a similar function called Decimal to convert USDollars back to its base type, Decimal. Users can use these functions to perform comparisons and queries. If I want to find all the rows in MyTable where the USPrice is equal to 500, I cannot do it as simply as I did without UDTs. The following SQL would not work:

Select * From MyTable

Where US_Price = 500.00

The preceding SQL statement is invalid because US_Price and 500 are different types and thus cannot be compared. However, by using the casting functions created by DB2 when the UDT was created, you could perform the comparison using one of the following SQL statements:

Select * From MyTable

Where US_Price = USDollars (500.00)

or

Select * From MyTable

Where US_Price = Cast

(500.00 As USDollars)

UDF

You have seen new ways to store and define data in DB2 UDB; now, take a look at new ways to manipulate that data. UDFs also allow you to write extensions to SQL. DB2 UDB includes many built-in functions such as mathematical expressions and string manipulation functions; however, specific business logic in your application may require specific functions not already included with DB2. UDFs open up functions in ILE languages. You can create a UDF with SQL or any of the ILE languages (e.g., RPG, C, and CL). If you decide to take advantage of UDTs, you will need UDFs to manipulate them because the built-in data types do not support the UDTs. Remember, functions and operations are data- type specific, so a function that works on decimal-type data will not work on my USDollars data type. I need to create a UDF to work with the USDollars UDT. First, take a look at how to create UDFs in SQL. You must define, write, and register a UDF before you can use it. This is all done with the SQL CREATE command. Because we are writing this UDF in SQL, the actual UDF and its definition are contained in one CREATE statement. Take a look:

Create Function Function name

(parameters separated by commas)

Returns return value

Language SQL

Begin

SQL statements for function

End

I’m going to use this structure to create a real function by passing the function one integers parameter and by returning that number squared.

Create Function Square (Number int)

Returns int

Language SQL

Begin

Return(Number * Number)

End

If the Square function were written in ILE RPG, you might use a statement like the following to define and register it:

Create Function Square (Number int)

Returns int

External Name ‘Library.Program’

Language RPG

As I mentioned earlier, SQL built-in functions don’t work with UDTs. If you want to use a UDT in a function, that function must be a UDF. However, it is simple to derive UDFs from system functions to work with your UDTs. For example, my UDT USDollars cannot have mathematical expressions performed against it because the system functions of plus (+) and minus (-) don’t know how to work with the USDollars data type. The plus (+) and minus (-) do know how to work with the Decimal data type that was the basis for USDollars, so I can inherit those operations to work with the USDollars data type with the following statements:

Create Function “+” (USDollars,

USDollars) Returns USDollars

Source “+” (Decimal(), Decimal());

Create Function “-” (USDollars,

USDollars) Returns USDollars

Source “-” (Decimal(), Decimal());

What I’ve done is overload the operators and function names. Overloading is an important capability of object-oriented technology. With function overloading, you can have several functions with the same name perform different logic depending on the data type they are working with. In my example, I could also create plus (+) and minus (-) functions for the CanadaDollars and FrenchFrancs data types and perform those mathematical operations on them. DB2 will automatically figure which data type I’m attempting to perform against and use the appropriate plus (+) and minus (-) functions.

Extended Forecast

You can see how the new DB2 UDB extensions expand the AS/400 integrated database toward object-oriented technology. With LOBs, you can now store traditional data such as text and numeric information along with nontraditional data such as video, audio, and large text documents. DataLinks allow you to add the benefits of a hierarchical file structure to your database by referencing files in other databases or on remote machines with URL pointers. UDTs allow you to tailor your database more closely to your application by expanding the system data types to better represent your data. Finally, UDFs enable you to define new functions using SQL or ILE languages and expand the built-in system functions to work with your UDTs. As the AS/400 programming environment moves more toward

object-oriented technology, you will begin to fully appreciate the benefits afforded us by the additions of database extensions in DB2 UDB.

REFERENCE

DB2 UDB for AS/400 SQL Programming (QB3AQ803 SC41-5611-03)

Also Read

TechTip: Fine-Tuning Db2 Symmetric Multiprocessing (SMP) to Improve Performance for Long-Running Queries

Celebrating 40 Years of Db2: Running the World’s Mission Critical Workloads

TechTip: Data Source Base Project, Part 3 - SSIS Code Structure