An alias is similar to a pseudonym, where an author publishes work under a different name. In database terms, the alias simply is an alternative name for a table or view. The motivation to use an alias is similar to why you would create a view. They both provide a means of implementing object-level database abstraction by hiding the physical location and representation of data from the application and, ultimately, the users.
Why would you want to spend time hiding such details? The answer is simple but powerful because it has a direct relation to data center cost, efficiency, and flexibility. When SQL is directed at views and aliases instead of tables, you have improved your ability to extend, transform, or move the location of the physical data. Where a view can combine pieces of different tables, transform data, and hide sensitive columns, aliases let applications code to well-defined object names that can be changed outside of the application set to refer to different tables.
The exciting enhancement in this area on IBM i 7.1 is that aliases can substitute a table reference with an object that resides on a different relational database. To refer to a table or view residing on a different database, the alias simply must indicate a three-part name, and DB2 for i will manage a temporary connection to the target database whenever the alias is used within SQL.
Before we delve into acronyms and techie details, let's review the basic concepts and terminology involved in this topic.
- ALIAS is an alternative name for a view, table, or member within a table accessed using SQL. On DB2 for i, the physical form of an alias is a DDM file.
- AR, the Application Requester (or client) side of a DRDA connection, is the application that issued the CONNECT and is requesting data or permission to execute statements on the other database. The AR is also the application using SQL with three-part name table references. You can specify the three-part name references as explicit in the SQL or as implicit via an alias.
- AS, the Application Server (or server) side of a DRDA connection, is the target database found using the Relational Database Directory entry on the AR. When you use DB2 for i as the AS, the job name when the CONNECT is issued is QRWTSRVR in the QUSRWRK subsystem.
- Database transparency is a technique of abstracting (hiding) the physical location of data from applications and users. Transparency usually means that others can clearly observe the true nature of something. For a database, transparency is similar to Cloud technologies in that users simply want the database to work and don't care about data representation and location.
- DDM, Distributed Data Management, lets application programs or users access data files that reside on remote systems. When DDM is in use, the application accesses a file as it normally would, and the database engine establishes and uses a connection to the remote database to satisfy the request.
- DRDA, IBM Distributed Relational Database Architecture, is an industry-standard-based protocol used to connect two databases. DRDA support provides distributed relational database management in both IBM and non-IBM environments and is an extension of the DDM architecture. However, DRDA and DDM access data differently. DRDA is an extension of SQL, whereas DDM is an extension of native record-level access I/O.
- Nonlocal three-part name is a three-part name with a <database-name> that refers to a remote database.
- Three-part name is an SQL identifier that specifies the relational database, the schema/library name, and the name of the object for use in an SQL statement (e.g., <database-name>.<schema-name>.<object-name> for SQL naming and <database-name>/<schema-name>/<object-name> for system naming).
Examples in This Article
I've based the examples in this article on the DB2 sample corporate database. To set up the database in a caller-supplied schema name, execute the following procedure call. Because this article focuses on distributed database work via SQL, I executed this CALL statement on my AR (RCHAPTF3) and AS (LP13UT23) machines:
What's in a Name?
DB2 for i has supported three-part names for many releases; however, before 7.1 the database-name portion of the three-part name had to match the current server. If you attempted to use a different database name, the statement would fail with a syntax error (SQL0114).
With 7.1, the database name can refer to a different relational database name, including an independent application storage pool (IASP). When the database name refers to a different database, a remote connection is established and used for the scope of the SQL statement. The user doesn't need to manage the connection and doesn't even realize it is a reference to a remote database!
A traditional approach for working remotely using SQL included build-time controls for embedded SQL, where the relational database name (RDB parameter) designated the AS used along with the CRTSQLPKG command on the AS. Three-part name support provides an easier approach at hand. Simply use a three-part name in your SQL, build your SQL programs with RDB(*LOCAL), and let the database manage any remote connections.
To implement an alias, you use a DDM file. The alias will still appear within the QSYS2/SYSTABLES catalog, but the TABLE_TYPE column will indicate 'A' for alias instead of 'T' or 'V' for table or view, respectively. When an SQL statement contains a reference to either an explicit or implicit nonlocal three-part name, DB2 for i attempts to establish a temporary connection to the remote machine, executes the statement, and returns the application state to its previous connection.
Example 1 below shows the execution of a searched UPDATE SQL statement initiated on the AR and executed against a table on the AS. Example 2 requires some extra setup. First, you must drop or rename the table of the same name. The second step is to create a nonlocal three-part name alias. Finally, the update is executed. If you currently have multiple copies of the same table being copied to different databases, consider using the approach in Example 2 to simplify operations and to eliminate redundant (and possibly stale) tables.
Example 1: Explicit nonlocal three-part name use:
SET MGRNO = 'F22' WHERE DEPTNO='000070'
Example 2: Implicit nonlocal three-part name use:
CREATE ALIAS CORPDB.DEPARTMENT FOR
UPDATE CORPDB.DEPARTMENT SET MGRNO = 'F22'
Rules of the Game
Let's pull together the most important aspects of what you can or cannot do with nonlocal three-part names. Here's what you can do:
- use three-part names explicitly within SQL statements where a table reference is allowed (i.e., skip the alias and hard-code directly to use a remote database)
- use three-part names implicitly within SQL statements where a table reference is allowed via the CREATE ALIAS statement; this approach isolates the database location from the application SQL
- replace tables and views with three-part name aliases without having to recompile or rewrite SQL-based applications; the database engine provides seamless access to remote data
And here's what you can't do:
- reference more than one database in a specific SQL statement; if the SQL statement fails with SQL0512, you've violated this rule (use of a user-defined table function lets you skirt this rule—for details, refer to the Wrapper a Remote Alias with a UDTF section of this article)
- create an alias that refers to an alias; an alias must refer to a table or view
- use a three-part name alias within a program, procedure, or function if the AR alias library doesn't exist on the AS (Example 3 illustrates this point: The topSalesPerson procedure returns the name of the sales person with the most sales); using a three-part name alias, you can redirect the procedure to different databases by dropping and re-creating the alias
If you create the alias in a library that doesn't exist on the AS, the call to the procedure will fail. In this case, library SHOW123 doesn't exist on the AS. If you receive the SQL0805 error when executing SQL-code bodies that contain three-part names, examine the spool file of the user on the AS to understand the nature of the SQL0805 failure.
One other note before we continue: It is of the utmost importance to ensure that you always apply the most current Database Group PTFs. For an easy way to schedule PTFs, see "Service-level Details," below.
Example 3: Using a three-part alias within an SQL procedure:
This example shows how to build application logic to avoid database location specifics. In step A, we create a schema and procedure on the AR. The procedure will query SalesTable, tally the sales figures for each salesperson, and order the salesperson names from most to least total sales. In step B, we create an alias and supply the database with the target database name (LP13UT23), schema (CORPDB), and table (SALES). In this example, the table (SHOW123.SalesTable) doesn't exist at the time the procedure is built. Reversing steps A and B in the example will produce the same results.
During step C, we see that the first attempt to call the procedure fails because, in this case, the database manager couldn't create an SQL package (*SQLPKG) for the procedure on the remote machine. If the package does not already exist in the application schema on the AS, the database manager will attempt to create it. The package contains information about all the SQL statements within the procedure and must exist on the AS for the procedure call to be successful. Here, the package creation failed because the SHOW123 schema simply did not exist on the AS. Figure 1 displays an image at the application level when the database can't create the SQL package for the nonlocal three-part name use within a SQL procedure.
Step D includes a CREATE SCHEMA SQL statement that creates SHOW123 on the AS. This is a special form of the three-part name support. The CREATE SCHEMA statement is passed the schema-name that must be created. With the DB2 for i support on 7.1, we can prefix the schema name with a database name. This aspect alone can help simplify and consolidate build scripts that previously required separate execution.
Once the schema exists on the AS, the procedure call is successful, and we see that LEE is the top salesperson. The SQL package is created on the AS when the procedure is called, if the package does not already exist.
Step A: Create the application schema and procedure
CREATE PROCEDURE show123.topSalesPerson(
SELECT SALES_PERSON INTO top_sales_person
GROUP BY SALES_PERSON
ORDER BY SUM(SALES) DESC
FETCH FIRST 1 ROWS ONLY;
Step B: Create a nonlocal three-part name alias
Step C: Invoke the procedure on the AR
SQL State: 51002
Vendor Code: -805
Message: [SQL0805] SQL package TOPSA00001 in
SHOW123 not found at DRDA Server.
Step D: Create the application schema on the AS and retry the procedure invocation
Returns - <strong>Output Parameter #1 = LEE</strong>
Wrapper a Remote Alias with a UDTF
With three-part names, we can easily collect data from different sources. To understand the concept and steps involved, the following builds upon the examples laid out so far, with the sample corporate database existing on two machines. We'll continue to use RCHAPTF3 as our AR and LP13UT23 as the AS.
Example 4: Avoiding the "single database reference in a statement" rule
On the AR, we create an alias called remoteSales, which points to the SALES table on the AS.
Also on the AR, we create a user-defined table function (UDTF), which simply queries the alias and returns the results in the form of a table. Why bother? The next steps of the example make the benefit of the UDTF apparent.
RETURNS TABLE (DATABASE_NAME VARCHAR(18),
SALES_DATE DATE, SALES_PERSON VARCHAR(15),
REGION VARCHAR(15), SALES INTEGER)
READS SQL DATA
SELECT CURRENT SERVER,
Next, we define a temporary table to contain the accumulated sales results across all databases. The sample database is small, making a temporary table a reasonable destination for the combined data. Note that in Figure 2, I added two columns to my destination table:
- DATABASE_NAME—the current server SQL special register value will show the database name responsible for every row in the combined result table
- RRN_ROW—a unique counter incrementing from 1 as rows are inserted
With the setup complete after just a few SQL statements, we're ready to move the data. We can execute these SQL statements with static or embedded SQL in a program/procedure or dynamically using Java, ODBC, or any other SQL interface. The first INSERT statement fetches the rows from the AS and inserts them into a table on the AR within a single SQL statement. As previously mentioned, this is the one exception to the SQL0512 multiple database reference restriction.
SALES_DATE, SALES_PERSON, REGION, SALES)
SELECT DATABASE_NAME, SALES_DATE, SALES_PERSON,
REGION, SALES FROM TABLE
(CORPDB.remoteSales_udtf()) AS X
The second statement simply tacks on the local sales results. Hopefully, you see how you could extend this example to work with many databases.
SALES_DATE, SALES_PERSON, REGION, SALES)
SELECT CURRENT SERVER,
REGION, SALES FROM CORPDB.SALES
When we review the results, we see the database name change. The example inserted 41 rows from the CORPDB.SALES table on two machines. Figure 3 shows the output from a query of totalSALES. Note that the DATABASE_NAME value is different, showing the source of the data.
ORDER BY RRN_row
The ease of use that a three-part named alias provides relies on two DDM/DRDA configuration elements:
- The relational database directory must contain an entry for the AS. If the SQL statements are failing with SQL0950, we need to adjust the RDB directory. For example, SELECT CURRENT SERVER, A.* FROM
In Figure 4, you can see that the relational DB was excluded in the relational database directory.
To establish the entry for the AS, use the Add Relational Database Directory Entry (ADDRDBDIRE) command as follows:ADDRDBDIRE RDB(LP13UT23 *NONE)
RMTLOCNAME(LP13UT23 *IP) PORT(*DRDA)
- Now that we can find the database, our simple query might fail with SQ30082, depending on the authentication configuration. For example, Figure 5 shows the authorization failure on the distributed database connection attempt (reason code 17).SELECT CURRENT SERVER, A.* FROM
Now we have to make an important security decision. Will we downgrade the security requirements of the AS (LP13UT23) or configure the user for remote authority to the AS (L13UT23) on the AR (RCHAPTF3)? I cover both options here, but of course, the right approach depends on your environment's security policy.
To downgrade the security requirements for DDM/DRDA connections on the AS, use the Change DDM TCP/IP Attributes (CHGDDMTCPA) command. The password-required parameter can indicate that passwords are not required and ignored (*USRID) or not required but validated if passed (*VLDONLY).
In the above command, *USRID specifies not to require a password on a DDM connection request. If a password is sent, it is ignored. The *VLDONLY says not to require a password on a DDM connection request. If a password is sent, however, it must be valid for the associated userid. And finally, *NO indicates not to require a password on a DDM connection request. If a password is sent, it is ignored. (Also see the *VLDONLY description.) The current recommendation is to use value *USRID in place of value *NO.
If we choose a PWDRQD value that directs the database to require a password, we can grant the user authority by changing the user's authentication information on the AR. The Add Server Authentication Entry (ADDSVRAUTE) command saves the user identification and password in a secure, internal location, which is accessed and used whenever the user needs to establish an implicit remote connection. Note that the SERVER parameter name is case-sensitive and must exactly match the server name in the RDB directory. Also, the USRPRF and USRID names need not be the same name, but in many cases, they are identical.
Regardless of which approach you take, the example query is finally successful. Notice the use of the CURRENT SERVER SQL special register confirms the remote access of data.
Figure 6 shows the successful query to the AS using a three-part name.
Recently, IBM shipped an improvement for DDM/DRDA authentication. You can now configure a user to work with all DDM/DRDA connections to any system in the TCP/IP network using just one userid and password. For more details, see "Hot Off the Press," below.
Database File Overrides
The OVRDBF command is an alternative technique to replace a file reference with a different name. You can use this command against SQL tables (physical files), SQL views (logical files), and DDM files. Database file overrides include a scope for the override, all of which are temporary. Overrides are always scoped to the job.
While you'll find OVRDBF useful for temporarily changing a file's characteristics, an SQL alias is generally the preferred technique when deploying database transparency. An alias is easier to create and manage and typically performs better than file overrides.
The word alias brings to mind thoughts of subterfuge and danger because it's unnatural to obscure or hide the true nature of something or someone. However, in a relational database such as DB2 for i, an alias is a positive and powerful construct to keep users and applications at a comfortable distance from the physical specifics of data.
On DB2 for i 7.1, you can use nonlocal three-part names to improve operations, administration, and applications. Consider using both explicit three-part names and implicit three-part names (via an alias) to realize improvements in the data center today and to position the data center for efficient transformation. (To learn more about the topics discussed here, see the references listed in Find Out More, below.)
Nonlocal three-part ALIAS support is part of IBM i 7.1. As a database administrator, you should always apply the most recent Database Group PTF.
Speaking of Database Group PTFs, the IBM i Technology Updates wiki on developerWorks now includes a page devoted to the DB2 for i Group PTF schedule! You can find the wiki page at ibm.com/developerworks/ibmi/techupdates/db2/groupptf.
This wiki page provides several conveniences:
- It lets you see the cadence and planned availability dates of future DB2 Group PTFs. Use this information to construct better maintenance plans within your data center.
- It lets you subscribe to the page to receive emails when IBM changes the page. This realtime notification of status or plan changes will arm you with valuable information about the database.
- It lets you compare the DB2 for IBM i Group PTF version level with enhancements described in the Technology Updates wiki.
Hot Off the Press
IBM has shipped a DDM/DRDA authentication improvement to DB2 for i on V5R4M0, 6.1, and 7.1. The enhancement adds a special value QDDMDRDASERVER to the ADDSVRAUTE command SERVER parameter for DDM and DRDA connections. This special value lets an administrator configure a user to work with all possible DDM or DRDA connections to any system in the TCP/IP network via a common userid and password. Once the configuration for a specific user is complete, you need not make additional changes for that user, as systems are added to the relational database directory.
A simple revision to the ADDSVRAUTE example in the Authentication Options section of the main article shows how to use the special value in the SERVER parameter to configure the user once on the AR. This revised configuration lets the user access and use any AS configured now or added to the network in the future.
For additional details about this enhancement, refer to this fact page on the DB2 for IBM i Technology Updates wiki: developerWorks—Add QDDMDRDASERVER server authentication entry special value.
Find Out More
The following IBM i 7.1 books and web pages contain information related to this topic: