Application development for DRDA

Programmers can write high-level language programs that use SQL statements for IBM® i distributed application programs.

The main differences from programs written for local processing only are the ability to connect to remote databases and to create SQL packages. The CONNECT SQL statement can be used to explicitly connect a client to a server, or the name of the relational database can be specified when the program is created to allow an implicit connection to occur. Also, the SET CONNECTION, RELEASE, and DISCONNECT statements can be used to manage connections for applications that use distributed unit of work.

An SQL package is an IBM i object used only for distributed relational databases. It can be created as a result of the precompile process of SQL or can be created from a compiled program object. An SQL package resides on the server. It contains SQL statements, host variable attributes, and access plans that the server uses to process a client's request.

Because application programs can connect to many different systems, programmers might need to pay more attention to data conversion between systems. The IBM i operating system provides for conversion of various types of data, including coded character set identifier (CCSID) support for the management of character information.

You can create and maintain programs for a distributed relational database on the system using the SQL language the same way you use it for local-processing applications. You can embed static and dynamic Structured Query Language (SQL) statements with any one or more of the following high-level languages:

  • PL/I PRPQ
  • ILE C
  • COBOL/400
  • ILE COBOL
  • FORTRAN/400
  • RPG/400®
  • ILE RPG

The process of developing distributed applications is similar to that of developing SQL applications for local processing. The difference is that the application for distributed processing must specify the name of the relational database to which it connects. This might be done when you precompile the program or within the application.

The same SQL objects are used for both local and distributed applications, except that one object, the SQL package, is used exclusively for distributed relational database support. You create the program using the Create SQL program (CRTSQLxxx) command. The xxx in this command refers to the host language CI, CBL, CBLI, FTN, PLI, RPG, or RPGI. The SQL package might be a product of the precompile in this process. The Create Structured Query Language Package (CRTSQLPKG) command creates SQL packages for existing distributed SQL programs.

You must have the IBM DB2® Query Manager and SQL Development Kit for i licensed program installed to precompile programs with SQL statements. However, you can create SQL packages from existing distributed SQL programs with only the compiled program installed on your system. The IBM DB2 Query Manager and SQL Development Kit for i licensed program also allows you to use interactive SQL to access a distributed relational database. This is helpful when you are debugging programs because it allows you to test SQL statements without having to precompile and compile a program.