Connectivity Supplement

Chapter 4. Considerations between DB2 for AS/400 and DB2 Universal Database

This section lists some additional considerations that apply to SQL operations between DB2 for AS/400 and DB2 Client Server Version 2 or DB2 Universal Database Version 5. The rest of the discussion relates to DB2 for OS/2, but in most cases similar considerations apply to DB2 Client Server Version 2 and DB2 Universal Database Version 5 on other platforms, as follows:

On the AS/400, tables names are qualified by a collection (or library name) and reside in the DB2 for AS/400 database (one database per AS/400). However, on the PC, tables are qualified by a user ID (the creator of the table), and reside in a particular database (with multiple possible databases on a PC with DB2 for OS/2).
1. This means a query from DB2 for OS/2 (via DB2 Connect) to DB2 for AS/400 would use the user ID of the target side job (on the AS/400) for the (default) collection name, if the name of the queried table was specified without a collection name. Exercise caution, or the table may not be found.
2. This also means a query from DB2 for AS/400 to DB2 for OS/2 would have an implied table qualifier, if it's not specified in the query (in the form 'qualifier.table-name'). The DB2 for OS/2 table qualifier (specified as a collection or library by the AS/400 application requestor) defaults to the user ID of the user making this query. Again, exercise caution, or your query may not find the table.
3. You may want to create the DB2 for OS/2 databases and tables with a common user ID. For DB2 for OS/2 there are no physical collections as there are in DB2 for AS/400 but simply a table qualifier, which is the user ID of the creator.

For OS/400 DRDA application requesters to work against DB2 for OS/2 DRDA application servers, you may (depending on the release you are using) need some PTFs applied on the AS/400:

                                                                                                                                    
                   OS/400        OS/400        OS/400                                                                               
                    V2R3         V3R0.5         V3R1                                                                                
                    PTF           PTF           PTF                                                                                 
                   -------       -------       -------                                                                              
                   SF23100       SF23950       SF23270                                                                              
                   SF23205       SF23950       SF23277                                                                              
                   SF23101       SF23994       SF23271                                                                              
                   SF23722       SF23988       SF23721                                                                              
                   SF23987       SF23986       SF23985                                                                              
                   SF23990       SF23989       SF23960

For OS/400 V3R1 you may need PTF SF23634, if you will be using DDCS or DB2 Connect. See item 3 and item 7.b.2 below.

If you will be using the SQL CALL statement to call stored procedures from DB2 for AS/400 to DB2 for OS/2, you need one of the following PTFs:

 
        V3R1          V3R2          V3R6          V3R7                                                                              
       SF36701       SF36699       SF36700       SF33877

The symptom of this error is a "Distributed Relational Database Architecture (DRDA) protocol error" where the "Data descriptor did not match data" at the application requester (SQL code -30000, SQL state 58008).

For OS/400 V3R1 and V3R6 you will need to CALL DB2 for OS/2 procedures with the procedure name in a host variable as mentioned in item 13 (and item 12) below. For OS/400 V3R2 and V3R7 there are PTFs that allow you to embed the procedure name in the SQL statement without putting it into a host variable. These are:

              V3R2           V3R7                                                                                                   
             SF36535        SF35932

DB2 Connect (or DDCS) is needed if DB2 for OS/2 will be a client using the DRDA protocol. It is not needed if DB2 for OS/2 will be used only as a server.
It is very important to configure DB2 Connect properly:
1. Make sure you have the most current levels of DB2 for OS/2 and DB2 Connect. Apply any available FIX paks, if you do not.
2. Follow the installation and configuration instructions given in the manuals.
Of course, if you are using APPC, OS/400 communications must also be configured properly, with a controller and device created for the PC, when DB2 for OS/2 is used either as an application requester or as an application server. Furthermore, regardless of the communications protocol used, there needs to be an entry in the RDB directory for each DB2 for OS/2 database an AS/400 will connect to.
To set up for APPC communications, do the following:
1. You may manually create the device and controller descriptions. You may also let the system create them for you, if you have a token ring and the line description AUTOCRTCLT parameter says *YES. Use the WRKLIND command to look at the line description, using option 2 Change. Go down to the parameter 'Autocreate controller', and see what your AUTOCRTCLT value is.
  If your system will autocreate controllers, you can initiate the creation of the necessary controller descriptions. From the CM/2 folder on OS/2, Start Communications and run Subsystem Management. From Subsystem Management look at the details to the SNA subsystem. Here you can look at the Logical Links. Open that up and activate the link to the desired system to autocreate the controller there. The device description will automatically be created later.
2. The device and controller for the PC on the AS/400 needs to be ACTIVE for the network connection to work between systems. You could have the SWTDSC parameter set to *NO in the controller description so ACTIVE controllers will stay ACTIVE. You could also set the ONLINE parameter to *YES, so that on IPL the controller will go active. (The ONLINE parameter in the device description might also need to be set to *YES). Note that to change parameters on a controller description it must be VARIED OFF and the controller owner (CTLOWN parameter) needs to be set to *USER.
3. To add an entry in the RDB directory for each DB2 for OS/2 database an AS/400 will connect to, use the ADDRDBDIRE command: the RDB name is the DB2 for OS/2 database name and the remote location name is the name of the workstation.
The proper CCSID value (normally 37 for US customers) is needed for any tables (physical files) on the AS/400 used by DB2 for OS/2. You can view the CCSID value with DSPFD, and change the CCSID value for physical files with CHGPF. Furthermore, to successfully connect, you may need to change one of the following: the CCSID of the job, the CCSID of the user profile used, or the system CCSID value (QCCSID) if it is the default 65535. Normally the best place to make this change is in the user profile under which the server job will be running.
Before using DB2 Connect to interoperate with an AS/400 server, you must create SQL packages on the AS/400 for application programs and for DB2 Connect utilities.
1. 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 to which you are currently connected.
2. To bind the DB2 Connect utilities to any AS/400 DB2 server:
  1. ```
     CONNECT TO rdbname
```
2. ```
   BIND path@DDCS400.LST BLOCKING ALL SQLERROR CONTINUE
            MESSAGES DDCS400.MGS GRANT PUBLIC
```
    Replace path in path@DDCS400.LST above with the default path C:\SQLLIB\BND\, or with your local value if you did not install to the default location.
    Note: PTF SF23624 is needed for OS/400 V3R1 to avoid a -901 SQL code from the DB2 for AS/400 database on the third bind file in the list.
  3. ```
     CONNECT RESET
```
For interactive SQL from DB2 for AS/400 to DB2 for OS/2:
1. 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 COLLECTIONs on the AS/400, and table qualifier (the creator's user ID) for DB2 for OS/2. See item 1 in this list of considerations for SQL operations.
3. For the very first interactive session, you MUST also specify COMMIT(*CS) for commitment control; and then (1) RELEASE ALL, (2) COMMIT, and (3) CONNECT TO rdbname (where 'rdbname' is replaced with a particular database). You may also want, at this time, to GRANT EXECUTE ON PACKAGE QSQL400.QSQL0200 TO PUBLIC (or to specific users), so that others can use the SQL PKG created on the PC for interactive SQL.
For any programs created on an AS/400 accessing a DB2 for OS/2 database, remember to use the following DB2 for OS/2 commands:
1. ```
   GRANT ALL PRIVILEGES ON TABLE table-name TO user
```
2. ```
   GRANT EXECUTE ON PACKAGE package-name (usually the AS/400
     program name) TO user
```
  Possibly, specify 'PUBLIC' for user.
In the development of AS/400 applications accessing DB2 for OS/2 (V.2.1.1 or earlier) a message (SQL5057) was issued in response to the CRTSQLxxx command that says an SQL package had been created on the PC, even if the package had not been created. This is fixed in the latest level of DB2 for OS/2.
Furthermore, in older versions of DB2 for OS/2, SQL packages would not be created for OS/400 programs that had anything in the text field of its source member description.
C language stored procedures in DB2 for OS/2 cannot use argc and argv as parameters (cannot be of type main()). This differs from AS/400 stored procedures which must use use argc and argv. For DB2 for OS/2 stored procedures, see the examples in the \SQLLIB\SAMPLES subdirectory. Look for OUTSRV.SQC and OUTCLI.SQC in the C subdirectory.
For stored procedures in DB2 for OS/2, called by an AS/400, use upper case for the procedure name. The AS/400 currently folds procedure names to upper case. However, this means a procedure on the PC, having the same procedure name, but in lower case, will not be found. For stored procedures on an AS/400, remember the procedure names will be upper case.
Also without the appropriate PTF for embedded SQL, a CALL statement from an AS/400 to DB2 for OS/2 will work only if you put the procedure name in a host variable (CALL :host-procedure-name(...)). The V3R7 PTF to fix this is SF35932. The V3R2 PTF is SF36535.
Stored procedures on the AS/400 cannot include a COMMIT when they are created to run in the same activation group as the calling program (the proper way to create them). However, for DB2 for OS/2, a stored procedure is allowed to include a COMMIT, but the application designer should be aware that there is no knowledge on the part of DB2 for AS/400 that the commit occurred.

[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]

[ DB2 List of Books | Search the DB2 Books ]