Other tips for interoperating in unlike environments

This topic collection provides additional information for using DB2® for IBM® i with DB2 for Linux®, UNIX, and Windows.

DB2 Connect as opposed to DB2 for Linux, UNIX, and Windows

Users are sometimes confused over what products are needed to perform the DRDA application server function as opposed to the application requester (client) function. The AR is sometimes referred to as DB2 Connect; and both the AR and AS as DB2 for Linux, UNIX, and Windows. DB2 refers to the following products:

  • DB2 for AIX®
  • DB2 for HP-UX
  • DB2 for Linux
  • DB2 for Sun Solaris
  • DB2 for Windows

Proper configuration and maintenance level

Be sure to follow the installation and configuration instructions given in the product manuals carefully. Make sure that you have the most current level of the products. Apply the appropriate fix packs if not.

Table and collection naming

SQL tables accessed by DRDA applications have three-part names: the first part is the database name, the second part is a collection ID, and the third part is the base table name. The first two parts are optional. Db2 for i qualifies table names at the second level by a collection (or library) name. Tables reside in the Db2 for i database.

In DB2, tables are qualified by a user ID (that of the creator of the table), and reside in one of possibly multiple databases on the platform. DB2 for Linux, UNIX, and Windows has the same notion of using the user ID for the collection ID.

In a dynamic query from DB2 for Linux, UNIX, and Windows to Db2 for i, if the name of the queried table is specified without a collection name, the query uses the user ID of the target side job (on the IBM i operating system) for the default collection name. This might not be what is expected by the user and can cause the table to not be found.

A dynamic query from Db2 for i to DB2 would have an implied table qualifier if it is not specified in the query in the form qualifier.table-name. The second-level DB2 table qualifier defaults to the user ID of the user making the query.

You might want to create the DB2 databases and tables with a common user ID. Remember, for DB2 there are no physical collections as there are in Db2 for i. There is only a table qualifier, which is the user ID of the creator.

APPC communications setup

IBM i communications must be configured properly, with a controller and device created for the workstation when you use APPC with either DB2 for Linux, UNIX, and Windows as an AR, or with DB2 as an AS.

Setting up the RDB directory

When adding an entry in the RDB directory for each DB2 database that a IBM i product will connect to, use the Add Relational Database Directory Entry (ADDRDBDIRE) command. The RDB name is the DB2 database name.

When using APPC communications, the remote location name is the name of the workstation.

When using TCP/IP, the remote location name is the domain name of the workstation, or its IP address. The port used by the DRDA server is typically not 446, the well-known DRDA port that the IBM i operating system uses.

Consult the DB2 product documentation to determine the port number. A common value used is 50000. An example DSPRDBDIRE display screen showing a properly configured RDB entry for a DB2 server follows. 

Display Relational Database Detail
      Relational database  . . . . . . :   SAMPLE
      Remote location:
        Remote location  . . . . . . . :   9.5.36.17
          Type . . . . . . . . . . . . :   *IP
        Port number or service name  . :   50000
      Text . . . . . . . . . . . . . . :   My DB2 server

How do I create the NULLID packages used by DB2 for Linux, UNIX, and Windows and IBM DB2 Universal Driver for SQLJ and JDBC?

Before using DB2 for Linux, UNIX, and Windows to access data on Db2 for i, you must create IBM i SQL packages for application programs and for the DB2 for Linux, UNIX, and Windows utilities.

The DB2 (PREP) command can be used to process an application program source file with embedded SQL. This processing will create a modified source file containing host language calls for the SQL statements and it will, by default, create an SQL package in the database you are currently connected to.

To bind DB2 for Linux, UNIX, and Windows to a Db2 for i server, follow these steps:

  1. CONNECT TO rdbname
  2. Bind path@ddcs400.lst BLOCKING ALL SQLERROR CONTINUE MESSAGES DDCS400.MGS GRANT PUBLIC

    Replace 'path' in the path@ddcs400.lst parameter above with the default path C:\SQLLIB\BND\ (c:/sqllib/bin/ on non-INTEL platforms), or with your value if you did not install to the default directory.

  3. CONNECT RESET

How do I set up the interactive SQL packages?

To use interactive SQL, you need the IBM Db2 Query Manager and SQL Development Kit for i product installed on IBM i. To access data on DB2, do the following:

  1. When starting a session with STRSQL, use session attributes of NAMING(*SQL), DATFMT(*ISO), and TIMFMT(*ISO). Other formats besides *ISO work, but not all, and what is used for the date format (DATFMT) must also be used for the time format (TIMFMT).
  2. Note the correspondence between schemas on the IBM i operating system and table qualifier (the creator's user ID) for DB2.
  3. For the first interactive session, you must do this sequence of SQL statements to get a package created on DB2: (1) RELEASE ALL, (2) COMMIT, and (3) CONNECT TO rdbname (where 'rdbname' is replaced with a particular database).

As part of your setup for the use of interactive SQL, you might also want to use the statement GRANT EXECUTE ON PACKAGE QSQL400.QSQLabcd TO PUBLIC (or to specific users), so that others can use the SQL PKG created on the PC for interactive SQL. The actual value for abcd in the following GRANT statement can be determined from the following table, which gives the package names for various sets of options that are in effect when the package is created. For example, you would use the statement GRANT EXECUTE ON PACKAGE QSQL400.QSQL0200 TO some-user if the following options were in use when you created the package: *ISO for date, *ISO for time, *CS for commitment control, a single quotation mark for string delimiter, and single byte for character subtype.

Position Option Value
a Date Format 0 = ISO, JIS date format 1 = USA date format 2 = EUR date format
b Time Format 0 = JIS time format 1 = USA time format 2 = EUR, ISO time format
c Commitment Control Decimal Delimiter 0 = *CS commitment control period decimal delimiter 1 = *CS commitment control comma decimal delimiter 2 = *RR commitment control period decimal delimiter 3 = *RR commitment control comma decimal delimiter
d String Delimiter Default Character Subtype 0 = single quotation mark string delimiter, single byte character subtype 1 = single quotation mark string delimiter, double byte character subtype 2 = quotation marks string delimiter, single byte character subtype 3 = quotation marks string delimiter, double byte character subtype

Close of queries

DB2 for Linux, UNIX, and Windows provides an option to request that read locks be released when queries are closed either implicitly or explicitly. It is not considered an error if the system does not honor the request, which is the case for IBM i products. DB2 for Linux, UNIX, and Windows provides another option to specify whether the system should close the query implicitly for a nonscrollable cursor when there are no more rows to read. Previously, the system made this decision. The IBM i AS supports this feature.

What is the maximum length of user IDs and passwords in a heterogeneous environment?

Db2 for i running as the application requester (AR) allows user IDs and passwords longer than ten characters when running to an unlike application server (AS). The exact limits are specified in the description of the specific interface being used. For example, see the DB2 for IBM i SQL reference topic for limits on the SQL CONNECT statement.

Creating interactive SQL packages on DB2 Server for VM

On DB2 Server for VM, a collection name is synonymous with a user ID. To create packages to be used with interactive SQL or Db2 for i Query Manager on an DB2 Server for VM application server, create a user ID of QSQL400 on the IBM i operating system. This user ID can be used to create all the necessary packages on the DB2 Server for VM application server. Users can then use their own user IDs to access Server for VM through interactive SQL or DB2 Query Manager on IBM i.