Other tips for interoperating in unlike environments

 

This topic collection provides additional information for using DB2 Universal Database™ for iSeries™ with DB2® for Linux®, UNIX®, and Windows®. These tips were developed from experiences testing with the products on an OS/2® platform, but it is believed that they apply to all environments to which they have been ported.

 

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 UDB refers to the following products:

 

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 UDB for iSeries qualifies table names at the second level by a collection (or library) name. Tables reside in the DB2 UDB for iSeries database.

In DB2 UDB, 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 UDB for iSeries, 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 i5/OS® 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 UDB for iSeries to DB2 UDB would have an implied table qualifier if it is not specified in the query in the form qualifier.table-name. The second-level UDB table qualifier defaults to the user ID of the user making the query.

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

 

APPC communications setup

i5/OS 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 UDB as an AS.

 

Setting up the RDB directory

When adding an entry in the RDB directory for each DB2 UDB database that a System i™ product will connect to, use the Add Relational Database Directory Entry (ADDRDBDIRE) command. The RDB name is the UDB 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 UDB DRDA server is typically not 446, the well-known DRDA port that the i5/OS operating system uses.

Consult the UDB product documentation to determine the port number. A common value used is 50000. An example DSPRDBDIRE screen showing a properly configured RDB entry for a UDB 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 UDB 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 UDB for iSeries, create i5/OS 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 UDB for iSeries 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 DB2 UDB Query Manager and SQL Development Kit product installed on i5/OS. To access data on DB2 Universal Database:

  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 i5/OS operating system and table qualifier (the creator's user ID) for UDB.

  3. For the first interactive session, do this sequence of SQL statements to get a package created on UDB: (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 System 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 i5/OS AS supports this feature.

 

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

DB2 UDB for iSeries 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 SQL reference topic for limits on the SQL CONNECT statement.

 

Creating interactive SQL packages on DB2 UDB Server for VM

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

 

Parent topic:

User FAQs