DB2 Connect lets an application program access data in any DRDA server database. For example, an application running on OS/2 can access data in a DB2 for OS/390 database. You can create new applications, or modify existing applications to run in a DRDA environment. You can also develop applications in one environment and port them to another.
DB2 Connect enables you to use the following items with host database products such as DB2 for OS/390, as long as the item is supported by the host database product:
Some SQL statements differ among relational database products. You may encounter the following situations:
SQL statements in the first two categories are highly portable, but those in the third category will first require changes.
In general, SQL statements in Data Definition Language (DDL) are not as portable as those in Data Manipulation Language (DML).
DB2 Connect accepts some SQL statements that are not supported by DB2 Universal Database. DB2 Connect passes these statements on to the DRDA server.
For information on limits on different platforms, such as the maximum column length, see the sections on SQL Limits and Considerations for Using in the SQL Reference.
If you move a CICS application from OS/390 or VSE to run under another CICS product (for example, CICS for AIX), it can also access the OS/390 or VSE database using DB2 Connect. Refer to the CICS/6000 Application Programming Guide and the CICS Customization and Operation manual for more details.
| Note: | You can use DB2 Connect with a DB2 Universal Database Version 5 database using DRDA, although it would be more efficient to use the DB2 private protocol without DB2 Connect. Most of the incompatibility issues listed in the following sections will not apply if you are using DB2 Connect against a DB2 Universal Database Version 5 database, except in cases where a restriction is due to a limitation of DRDA itself, for example, the non-support of large object (LOB) data types. |
When you program in a DRDA environment, you should consider the following specific factors:
DDL statements differ among the IBM database products because storage is handled differently on different systems. On DRDA server systems, there can be several steps between designing a database and issuing a CREATE TABLE statement. For example, a series of statements may translate the design of logical objects into the physical representation of those objects in storage.
The precompiler passes many such DDL statements to the DRDA server when you precompile to a DRDA server database. The same statements would not precompile against a database on the system where the application is running. For example, in an OS/2 application the CREATE STORGROUP statement will precompile successfully to a DB2 for OS/390 database, but not to a DB2 for OS/2 database.
In general, DML statements are highly portable. SELECT, INSERT, UPDATE, and DELETE statements are similar across the IBM relational database products. Most applications primarily use DML SQL statements, which are supported by the DB2 Connect program and DRDA protocol.
When numeric data is transferred to DB2 Universal Database, the data type may change. Numeric and zoned decimal SQLTYPEs (supported by DB2 for AS/400) are converted to fixed (packed) decimal SQLTYPEs.
Mixed-byte data can consist of characters from an extended UNIX code (EUC) character set, a double-byte character set (DBCS) and a single-byte character set (SBCS) in the same column. On systems that store data in EBCDIC (OS/390, OS/400, VSE, and VM), shift-out and shift-in characters mark the start and end of double-byte data. On systems that store data in ASCII (such as OS/2 and UNIX), shift-in and shift-out characters are not required.
If your application transfers mixed-byte data from an ASCII system to an EBCDIC system, be sure to allow enough room for the shift characters. For each switch from SBCS to DBCS data, add 2 bytes to your data length. For better portability, use variable-length strings in applications that use mixed-byte data.
Long fields (strings longer than 254 characters) are handled differently on different systems. A DRDA server may support only a subset of scalar functions for long fields; for example, DB2 for MVS/ESA allows only the LENGTH and SUBSTR functions for long fields. Also, a DRDA server may require different handling for certain SQL statements; for example, DB2 for VSE & VM requires that with the INSERT statement, only a host variable, the SQLDA, or a NULL value be used.
The LOB data type is not supported by the DRDA architecture. DB2 Connect cannot send data with this data type.
Each IBM relational database management system provides different levels of granularity for the GRANT and REVOKE SQL statements. Check the product-specific publications to verify the appropriate SQL statements to use for each database management system.
DB2 Connect supports the CONNECT TO and CONNECT RESET versions of the CONNECT statement, as well as CONNECT with no parameters. If an application calls an SQL statement without first performing an explicit CONNECT TO statement, an implicit connect is performed to the default application server (if one is defined).
When you connect to a database, information identifying the relational database management system is returned in the SQLERRP field of the SQLCA. If the application server is an IBM relational database, the first three bytes of SQLERRP contain one of the following:
If you issue a CONNECT TO or null CONNECT statement while using DB2 Connect, the country code or territory token in the SQLERRMC field of the SQLCA is returned as blanks; the CCSID of the application server is returned in the code page or code set token.
You can explicitly disconnect by using the CONNECT RESET statement (for type 1 connect), the RELEASE and COMMIT statements (for type 2 connect), or the DISCONNECT statement (either type of connect, but not in a TP monitor environment). Type 2 connect is used for distributed unit of work, as described in "Distributed Unit of Work".
If a connection is not explicitly disconnected and the application ends normally, DB2 Connect commits the resulting data implicitly.
| Note: | An application can receive SQLCODEs indicating errors and still end normally; DB2 Connect commits the data in this case. If you do not want the data to be committed, you must issue a ROLLBACK command. |
The FORCE command lets you disconnect selected users or all users from the database. This is supported for DRDA server databases; the user can be forced off the DB2 Connect workstation.
There are some differences in the precompilers for different IBM relational database systems. The precompiler for DB2 Universal Database differs from the DRDA server precompilers in the following ways:
The DB2 Connect program supports the DB2 database manager blocking bind options:
The DB2 Connect program uses the block size defined in the DB2 database manager configuration file for the RQRIOBLK field. Current versions of DRDA supports block sizes up to 32 767. If larger values are specified in the DB2 database manager configuration file, DB2 Connect uses a value of 32 767 but does not reset the DB2 database manager configuration file. Blocking is handled the same way using the same block size for dynamic and static SQL.
| Note: | Most DRDA server systems consider dynamic cursors ambiguous, but DB2 Universal Database systems consider some dynamic cursors unambiguous. To avoid confusion, you can specify BLOCKING ALL with DB2 Connect. |
Specify the block size in the DB2 database manager configuration file by using the CLP, the Control Center, or an API, as listed in the API Reference and Command Reference.
A package has the following attributes:
Each DRDA server system has limitations on the use of these attributes:
| Note: | DB2 Connect provides support for the SET CURRENT PACKAGESET command for DB2 for MVS/ESA, DB2 for OS/390, and DB2 Universal Database. |
The CNULREQD bind option overrides the handling of null-terminated strings that are specified using the LANGLEVEL option. Refer to Embedded SQL Programming Guide for a description of how null-terminated strings are handled when prepared with the LANGLEVEL option set to MIA or SAA1. By default, CNULREQD is set to YES. This causes null-terminated strings to be interpreted according to MIA standards. If connecting to a DB2 for OS/390 server it is strongly recommended to set CNULREQD to YES. DB2 Connect will use CNULREQD as a default. You need to bind applications coded to SAA1 standards (with respect to null-terminated strings) with the CNULREQD option set to NO. Otherwise, null-terminated strings will be interpreted according to MIA standards, even if they are prepared using LANGLEVEL set to SAA1.
Standalone SQLCODE and SQLSTATE variables, as defined in ISO/ANS SQL92, are supported through the LANGLEVEL SQL92E precompile option. An SQL0020W warning will be issued at precompile time, indicating that LANGLEVEL is not supported. This warning applies only to the features listed under LANGLEVEL MIA in the Command Reference, which is a subset of LANGLEVEL SQL92E.
The differences between EBCDIC and ASCII cause differences in sort orders in the various database products, and also affect ORDER BY and GROUP BY clauses. One way to minimize these differences is to create a user-defined collating sequence that mimics the EBCDIC sort order. You can specify a collating sequence only when you create a new database. For more information, see the Embedded SQL Programming Guide, the API Reference and the Command Reference.
| Note: | Database tables can now be stored on DB2 for OS/390 in ASCII format. This permits faster exchange of data between DB2 Connect and DB2 for OS/390, and removes the need to provide field procedures which must otherwise be used to convert data and resequence it. |
Different systems handle referential constraints differently:
Other rules vary concerning levels of cascade.
The way in which the database server performs locking can affect some applications. For example, applications designed around row-level locking and the isolation level of cursor stability are not directly portable to systems that perform page-level locking. Because of these underlying differences, applications may need to be adjusted.
The DB2 for OS/390 and DB2 Universal Database products have the ability to time-out a lock and send an error return code to waiting applications.
Different IBM relational database products do not always produce the same SQLCODEs for similar errors. You can handle this problem in either of two ways:
SQLSTATEs have approximately the same meaning across the database products, and the products produce SQLSTATEs that correspond to the SQLCODEs.
By default, DB2 Connect maps SQLCODEs and tokens from each IBM DRDA server system to your DB2 Universal Database system. You can specify your own SQLCODE mapping file if you want to override the default mapping or you are using a DRDA server that does not have SQLCODE mapping (a non-IBM DRDA server). You can also turn off SQLCODE mapping. For more information, see Chapter 8. "SQLCODE Mapping".
The system catalogs vary across the IBM database products. Many differences can be masked by the use of views. For information, see the documentation for the database server that you are using.
The catalog functions in CLI get around this problem by presenting support of the same API and result sets for catalog queries across the DB2 family (including DRDA).
Numeric conversion overflows on retrieval assignments may be handled differently by different IBM relational database products. For example, consider fetching a float column into an integer host variable from DB2 for OS/390 and from DB2 Universal Database. When converting the float value to an integer value, a conversion overflow may occur. By default, DB2 for OS/390 will return a warning sqlcode and a null value to the application. In contrast, DB2 Universal Database will return a conversion overflow error. It is recommended that applications avoid numeric conversion overflows on retrieval assignments by fetching into appropriately sized host variables.
DB2 Connect accepts the following isolation levels when you prep or bind an application:
The isolation levels are listed in order from most protection to least protection. If the DRDA server does not support the isolation level that you specify, the next higher supported level is used.
Table 4 shows the result of each isolation level on each DRDA application server.
| DB2 Connect | DB2 for MVS/ESA or DB2 for OS/390 | DB2 for VSE & VM | DB2 for AS/400 | DB2 Universal Database | ||
|---|---|---|---|---|---|---|
| RR | RR | RR | note 1 | RR | ||
| RS | note 2 | RR | COMMIT(*ALL) | RS | ||
| CS | CS | CS | COMMIT(*CS) | CS | ||
| UR | note 3 | CS | COMMIT(*CHG) | UR | ||
| NC | note 4 | note 5 | COMMIT(*NONE) | UR | ||
| ||||||
With DB2 for AS/400, you can access an unjournalled table if an application is bound with an isolation level of UR and blocking set to ALL, or if the isolation level is set to NC.
A client program can invoke a server program by issuing an SQL CALL statement. Each server works a little differently to the other servers in this case.
All CALL statements to DB2 for AS/400 from REXX/SQL must be dynamically prepared and executed by the application as the CALL statement implemented in REXX/SQL maps to CALL USING DESCRIPTOR.
For the syntax of the SQL CALL statement, see the SQL Reference. For information on how to use stored procedures when writing application programs, see the Embedded SQL Programming Guide.
The server program on DB2 Universal Database is invoked with a different parameter convention than a server program on DB2 for MVS/ESA, DB2 for OS/390, or DB2 for AS/400. For more information on the parameter convention for a specific platform, refer to the DB2 product documentation for that platform.
All the SQL statements in a stored procedure are executed as part of the SQL unit of work started by the client SQL program.
Between DB2 Universal Database, the systems pass whatever you put into the indicator variables. However, when using the DRDA protocol (such as through DB2 Connect), you can only pass 0, -1, and -128 in the indicator variables.
A server program on DB2 Universal Database can update the SQLCA to return any error or warning, but a stored procedure on DB2 for OS/390, DB2 for AS/400, or DB2 for MVS/ESA has no such support. If you want to return an error code from your stored procedure, you must pass it as a parameter. The SQLCODE and SQLCA is only set by the server for system detected errors.
Compound SQL allows multiple SQL statements to be grouped into a single executable block. This may reduce network overhead and improve response time.
DB2 Connect supports NOT ATOMIC compound SQL. This means that processing of compound SQL continues following an error. (With ATOMIC compound SQL, which is not supported by DB2 Connect, an error would roll back the entire group of compound SQL.)
Statements will continue execution until terminated by the application server. In general, execution of the compound SQL statement will be stopped only in the case of serious errors.
NOT ATOMIC compound SQL can be used with all of the supported DRDA application servers.
If multiple SQL errors occur, the SQLSTATEs of the first seven failing statements are returned in the SQLERRMC field of the SQLCA with a message that multiple errors occurred. For more information, see the SQL Reference.
DB2 Connect allows you to update multiple databases within a single distributed unit of work (DUOW). Whether you can use this capability depends on several factors:
Additionally, the DB2 Connect Enterprise Edition Version 5 Syncpoint Manager continues to allow DB2 MVS Version 3.1 databases to participate in two-phase commit, but not to act as the TM_DATABASE for such transactions.
Notes:
We strongly recommend that all clients accessing DB2 Universal Database Version 5 and DB2 for OS/390 Version 5 databases be at DB2 Universal Database Version 5 level. DB2 Version 2.1 clients cannot initiate two-phase commit transactions if DB2 for OS/390 Version 5 databases participate in the transaction.
The following statements compile successfully for DRDA server processing but not for processing with DB2 Universal Database systems:
These statements are also supported by the command line processor.
The following statements are supported for DRDA server processing but are not added to the bind file or the package and are not supported by the command line processor:
The precompiler makes the following assumptions:
The following SQL statements are not supported by DB2 Connect and not supported by the command line processor:
DB2 for VSE & VM extended dynamic SQL statements are rejected with -104 and syntax error SQLCODEs.