DB2 Connect lets an application program access data in DB2 databases on System/390 and AS/400 servers. For example, an application running on Windows can access data in a DB2 Universal Database for OS/390 database. You can create new applications, or modify existing applications to run in a host or AS/400 environment. You can also develop applications in one environment and port them to another.
DB2 Connect enables you to use the following APIs with host database products such as DB2 Universal Database 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 SQL statements that are:
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 host or AS/400 server. For information on limits on different platforms, such as the maximum column length, refer to 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.
When you program in a host or AS/400 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 host or AS/400 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 host or AS/400 server when you precompile to a host or AS/400 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 Universal Database 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.
When numeric data is transferred to DB2 Universal Database, the data type may change. Numeric and zoned decimal SQLTYPEs (supported by DB2 Universal Database 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 host or AS/400 server may support only a subset of scalar functions for long fields; for example, DB2 Universal Database for OS/390 allows only the LENGTH and SUBSTR functions for long fields. Also, a host or AS/400 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 supported by DB2 Connect.
Only User Defined Distinct Types are supported by DB2 Connect. Abstract Data Types are not.
The ROWID data type is handled by DB2 Connect as VARCHAR for bit data.
Eight byte (64-bit) integers are supported by DB2 Connect. The BIGINT internal data type is used to provide support for the cardinality of very large databases, while retaining data precision.
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).
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 host or AS/400 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 host or AS/400 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 DB2 Connect support 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 host or AS/400 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 Administrative API Reference and Command Reference.
A package has the following attributes:
Each host or AS/400 server system has limitations on the use of these attributes:
| Note: | DB2 Connect provides support for the SET CURRENT PACKAGESET command for DB2 Universal Database 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.
For a description of how null-terminated strings are handled when prepared with the LANGLEVEL option set to MIA or SAA1, refer to Application Development Guide.
By default, CNULREQD is set to YES. This causes null-terminated strings to be interpreted according to MIA standards. If connecting to a DB2 Universal Database for OS/390 server it is strongly recommended to set CNULREQD to YES. 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, refer to the Application Development Guide, the Administrative API Reference and the Command Reference.
| Note: | Database tables can now be stored on DB2 Universal Database for OS/390 in ASCII format. This permits faster exchange of data between DB2 Connect and DB2 Universal Database 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 Universal Database 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 host or AS/400 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 database server that does not have SQLCODE mapping (a non-IBM database server). You can also turn off SQLCODE mapping.
For more information, see 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.
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 Universal Database 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 Universal Database 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 host or AS/400 server does not support the isolation level that you specify, the next higher supported level is used.
Table 2 shows the result of each isolation level on each host or
AS/400 application server.
| DB2 Connect | DB2 Universal Database for OS/390 | DB2 for VSE & VM | DB2 Universal Database 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 |
|
Notes:
| ||||
With DB2 Universal Database 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, refer to the SQL Reference. For information on how to use stored procedures when writing application programs, refer to the Application Development Guide.
You can invoke the server program on DB2 Universal Database with the same parameter convention that server programs use on DB2 Universal Database for OS/390, DB2 Universal Database for AS/400, or DB2 for VSE & VM. For more information on invoking DB2 Universal Database stored procedures, refer to the Application Development Guide. For more information on the parameter convention on other platforms, 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 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 Universal Database for OS/390 or DB2 Universal Database for AS/400 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.
DB2 Stored Procedure Builder provides an easy-to-use development environment for creating, installing, and testing stored procedures. It allows you to focus on creating your stored procedure logic rather than the details of registering, building, and installing stored procedures on a DB2 server. Additionally, with Stored Procedure Builder, you can develop stored procedures on one operating system and build them on other server operating systems.
Stored Procedure Builder is a graphical application that supports rapid development. Using Stored Procedure Builder, you can perform the following tasks:
You can launch Stored Procedure Builder as a separate application from the DB2 Universal Database program group, or you can launch Stored Procedure Builder from any of the following development applications:
You can also launch Stored Procedure Builder from Control Center for DB2 for OS/390. You can start Stored Procedure Builder as a separate process from the Control Center Tools menu, toolbar, or Stored Procedures folder. In addition, from the Stored Procedure Builder Project window, you can export one or more selected SQL stored procedures built to a DB2 for OS/390 server to a specified file capable of running within the Command Line Processor (CLP).
Stored Procedure Builder manages your work by using projects. Each Stored Procedure Builder project saves your connections to specific databases, such as DB2 for OS/390 servers. In addition, you can create filters to display subsets of the stored procedures on each database. When opening a new or existing Stored Procedure Builder project, you can filter stored procedures so that you view stored procedures based on their name, schema, language, or collection ID (for OS/390 only).
Connection information is saved in a Stored Procedure Builder project; therefore, when you open an existing project, you are automatically prompted to enter your user id and password for the database. Using the Inserting SQL Stored Procedure wizard, you can build SQL stored procedures on a DB2 for OS/390 server. For a SQL stored procedure built to a DB2 for OS/390 server, you can set specific compile, pre-link, link, bind, runtime, WLM environment, and external security options.
Additionally, you can obtain SQL costing information about the SQL stored procedure, including information about CPU time and other DB2 costing information for the thread on which the SQL stored procedure is running. In particular, you can obtain costing information about latch/lock contention wait time, the number of getpages, the number of read I/Os, and the number of write I/Os.
To obtain costing information, Stored Procedure Builder connects to a DB2 for OS/390 server, executes the SQL statement, and calls a stored procedure (DSNWSPM) to find out how much CPU time the SQL stored procedure used.
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 host or AS/400 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, refer to the SQL Reference.
DB2 Connect allows you to perform a multisite update, also known as two-phase commit. A multisite update is an update of multiple databases within a single distributed unit of work (DUOW). Whether you can use this capability depends on several factors:
The above is true for native DB2 UDB applications and applications coordinated by an external Transaction Processing (TP) Monitor such as IBM TXSeries, CICS for Open Systems, BEA Tuxedo, Encina Monitor, and Microsoft Transaction Server.
| Note: | For more information on BEA Tuxedo, see Using DB2 Connect with Transaction Processing Monitors.For more information on the XA concentrator, see DB2 Connect Connection Concentrator. |
If a common DB2 Connect Enterprise Edition server is used by both native DB2 applications and TP monitor applications to access host data over TCP/IP connections then the sync point manager must be used.
If a single DB2 Connect Enterprise Edition server is used to access host data using both SNA and TCP/IP network protocols and two phase commit is required, then the sync point manager must be used. This is true for both DB2 applications and TP monitor applications.
The following statements compile successfully for host or AS/400 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 host or AS/400 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.