The source code is created in a standard ASCII file, called a source file, using a text editor. The source file must have the proper extension for the host language in which you write your code. See Table 23 to find out the required file extension for the host language you are using.
| Note: | Not all platforms support all host languages. See your DB2 SDK Building Applications for specific information. |
You can find the details of how to write the code for a DB2 program in "Coding a DB2 Application", but for this discussion, assume that you have already written the source code.
If you have written your application using a compiled host language, there are additional steps required to build your application. Along with compiling and linking your program, you must precompile and bind it.
Simply stated, precompiling is the conversion of embedded SQL statements into DB2 run-time API calls that a host compiler can process, and the creation of a bind file. The bind file, which contains information on the SQL statements in the application program, is used to create a package in the database by using the BIND command. Optionally, the precompiler can perform the bind step at precompile time.
Binding is the process of creating a package from a bind file and storing it in a database. If your application accesses more than one database, a package must be created for each database.
Figure 4 shows the order of these steps, along with the various modules of a typical compiled DB2 application. You may wish to refer to it as you read through the following sections about what happens at each stage of program preparation.
Figure 4. Preparing Programs Written in Compiled Host Languages
To run applications written in compiled host languages, you must create the packages needed by the database manager at execution time. This involves the following steps:
Other topics discussed in this section include:
Since REXX is an interpreted language, applications written in this language are not precompiled, compiled, linked, or bound. Applications written in REXX connect to a database but do not bind to it. See Chapter 14. "Programming in REXX" for more information.
After the source files are created, each file containing SQL statements must be precompiled with the PREP command. The precompiler converts SQL statements contained in the source file to comments, and generates the DB2 run-time API calls for those statements.
Before precompiling an application you must connect to a server, either implicitly or explicitly. Although application programs are precompiled at the client workstation and the modified source and messages are generated on the client, the server connection is needed because some of the validation is done on the server.
The precompiler also creates the information the database manager needs to process the SQL statements against a database. This information is stored in a package, in a bind file, or in both, depending on the precompiler options selected.
The precompiler generates up to four types of output:
| Note: | With the PACKAGE option, the database used during the precompile process must contain all of the database objects referenced by the static SQL statements in the source file. For example, a SELECT statement cannot be precompiled unless the table it references exists in the database. |
If you request a bind file at precompile time but do not specify the PACKAGE, that is you do not create a package, certain object existence and authorization SQLCODES are treated as warnings instead of errors. This enables you to precompile a program and create a bind file without requiring that the referenced objects be present, or requiring that you possess the authority to execute the SQL statements being precompiled. For a list of the specific SQLCODES that are treated as warnings instead of errors see the Command Reference.
A source file must always be precompiled against a specific database, even if that is not the database eventually used with the application. In practice, a test database can be used for development, and after the application is fully tested, its bind file can be bound to one or more production databases. See "Advantages of Deferred Binding" for information about how else this can be useful.
If the code page your application uses is not the same as your database code page, you need to consider which code page to use when precompiling. See "Conversion Between Different Code Pages".
If your application uses user-defined functions (UDFs) or user-defined distinct types (UDTs), you may need to use the FUNCPATH option when you precompile your application. This option specifies the function path that is used to resolve UDFs and UDTs for applications containing static SQL. If FUNCPATH is not specified, the default function path is "SYSIBM","SYSFUN", "USER", where "USER" refers to the current user ID. For more information on bind options see the Command Reference.
To precompile an application program that accesses more than one server, you can do one of the following:
The same procedures apply if your application will access a DRDA application server through DB2 Connect. Precompile it against the server to which it will be connecting, using the PREP options available for that server.
If you are precompiling an application that will run on DB2 for MVS/ESA or OS/390, consider using the flagger facility to check the syntax of the SQL statements. The flagger indicates SQL syntax that is supported by DB2 Universal Database, but not supported by DB2 for MVS/ESA or OS/390. You can also use the flagger to check that your SQL syntax conforms to the SQL92 Entry Level syntax. You can use the SQLFLAG option on the PREP command to invoke it and to specify the version of DB2 for MVS/ESA or OS390 SQL syntax to be used for comparison. The flagger facility will not enforce any changes in SQL use; it only issues informational and warning messages regarding syntax incompatibilities, and preprocessing is not terminated abnormally.
For details about the PREP command, see the Command Reference.
Compile the modified source files and any additional source files that do not contain SQL statements using the appropriate host language compiler. The language compiler converts each modified source file into an object module.
See the DB2 SDK Building Applications book or other programming documentation for your operating platform for any exceptions to the default compile options. See your compiler's documentation for a complete description of available compile options.
The host language linker creates an executable application. For example:
| Note: | Although applications can be DLLs on Windows platforms, for the Windows 3.1, Windows 95, or the Windows NT operating systems, the DLLs are loaded directly by the application and not by the DB2 database manager. On the Windows 95 and Windows NT operating systems, DLLs can be loaded by the database manager. Stored procedures, which are normally built as DLLs or shared libraries are unsupported on the Windows 3.1 operating system. For information on using stored procedures, see Chapter 5. "Writing Stored Procedures". |
For information on creating executable files on other platforms supported by DB2, see the DB2 SDK Building Applications for those platforms.
The executable file is created by linking the following:
Binding is the process that creates the package the database manager needs in order to access the database when the application is executed. Binding can be done implicitly by specifying the PACKAGE option during precompilation, or explicitly by using the BIND command against the bind file created during precompilation.
One package is created for each separately precompiled source code module. If an application has five source files, of which three require precompilation, three packages or bind files are created. By default, each package is given a name that is the same as the name of the source module from which the .bnd file originated, but truncated to 8 characters. If the name of this newly created package is the same as a package that currently exists in the target database, the new package replaces the previously existing package. To explicitly specify a different package name, you must use the PACKAGE USING option on the PREP command. See the Command Reference for details.
When creating multiple versions of an application, you should avoid conflicting names by renaming your package. For example, if you have an application called foo (compiled from foo.sqc), you precompile it and send it to all the users of your application. The users bind the application to the database, and then run the application. Subsequently, if changes are needed, you create a new version of foo and send this application and its bind file to the users that require the new version. The new users bind foo.bnd and the new application runs without any problem. However, when users attempt to run the old version of the application, they receive a timestamp conflict on the FOO package (which indicates that the package in the database does not match the application being run) so they rebind the client. (See "Timestamps" for more information on package timestamps.) Now the users of the new application receive a timestamp conflict. This problem is caused because both applications use packages with the same name.
The solution is to use package renaming. When you build the first version of FOO, you precompile it with the command:
DB2 PREP FOO.SQC BINDFILE PACKAGE USING FOO1
After you distribute this application, users can bind and run it without any problem. When you build the new version, you precompile it with the command:
DB2 PREP FOO.SQC BINDFILE PACKAGE USING FOO2
After you distribute the new application, it will also bind and run without any problem. Since the package name for the new version is FOO2 and the package name for the first version is FOO1, there is no naming conflict and both versions of the application can be used.
For dynamically prepared statements, the statement compilation environment is determined by a number of special registers. The optimization class used is determined by the value of the CURRENT QUERY OPTIMIZATION special register. The function path used for UDF and UDT resolution is obtained from the value of the CURRENT FUNCTION PATH special register. Similarly, the value of the CURRENT EXPLAIN SNAPSHOT register determines whether explain snapshot information is captured, and the value of the CURRENT EXPLAIN MODE register determines whether explain table information is captured, for any eligible dynamic SQL statement. The default values for these special registers are the same defaults as are used for the related bind options. For information on special registers and their interaction with BIND options, refer to the appendix of the SQL Reference.
You can handle unqualified table names in your application by using one of the following methods:
CONNECT TO db_name USER user_name BIND file_name COLLECTION schema_name
In the above example, db_name is the name of the database, user_name is the name of the user, and file_name is the name of the application that will be bound. Note that db_name and schema_name are usually the same value. Then use the SET CURRENT PACKAGESET statement to specify which package to use, and therefore, which qualifiers will be used. The qualifier is always the authorization identifier that is used when binding the package. For an example of how to use the SET CURRENT PACKAGESET statement, refer to the SQL Reference.
If your application code page is not the same as your database code page, you may need to consider which code page to use when binding. See "Conversion Between Different Code Pages".
If your application issues calls to any of the database manager utility APIs, such as IMPORT or EXPORT, the supplied utility bind files must be bound to the database. For details, see the Quick Beginnings for your platform.
You can use bind options to control certain operations that occur during binding. For example, the QUERYOPT bind option can be used to take advantage of a specific optimization class when binding, the EXPLSNAP bind option can be used to store Explain Snapshot information for eligible SQL statements in the Explain tables, and the FUNCPATH bind option can be used to properly resolve user-defined distinct types and user-defined functions in static SQL.
For information on bind options, refer to the section on the BIND command in the Command Reference.
If the bind process starts but never returns, it may be that other applications are connected to the database and are holding locks that you require. In this case, ensure that no applications are connected to the database. If they are, disconnect all applications on the server and the bind process will continue.
If your application will access a DRDA server, you can use the BIND options available for that server. For details on the BIND command and its options, see the Command Reference.
Precompiling with binding enabled allows an application to access only the database used during the precompile process. Precompiling with binding deferred, however, allows an application to access many databases, because you can bind the BIND file against each one. This method of application development is inherently more flexible in that applications are precompiled only once, but the application can be bound to a database at any time.
Using the BIND API during execution allows an application to bind itself, perhaps as part of an installation procedure or before an associated module is executed. For example, an application can perform several tasks, only one of which requires the use of SQL statements. The application can be designed to bind itself to a database only when the task requiring SQL statements is called, and only if an associated package does not already exist.
Another advantage of the deferred binding method is that it lets you create packages without providing source code to end users. The associated bind files can be shipped with the application.
With the DB2 Bind File Dump (db2bfd) tool, you can easily display the contents of a Version 3 bind file to examine and verify the SQL statements within it, as well as display the precompile options used to create the bind file. This may be useful in problem determination related to your application's bind file.
The db2bfd tool is located in the misc subdirectory of the sqllib directory of the instance on AIX and UNIX-based platforms. It is located in the bin subdirectory of the sqllib directory of the instance on OS/2 and Windows platforms.
Its syntax is:
<---------<
(1) (5)
>>--db2bfd---.--h----.---filespec--------------------------------------><
| (2)|
|--b----|
| (3)|
|--s----|
| (4)|
'--v----'
NOTES:
(1) Display the help information.
(2) Display bind file header.
(3) Display SQL statements.
(4) Display host variable declarations
(5) The name of the bind file.
A package is an object stored in the database that includes information needed to execute specific SQL statements in a single source file. A database manager application uses one package for every precompiled source file used to build the application. Each package is a separate entity, and has no relationship to any other packages used by the same or other applications. Packages are created by running the precompiler against a source file with binding enabled, or by running the binder at a later time with one or more bind files.
Database manager applications use packages for some of the same reasons that applications are compiled: improved performance and compactness. By precompiling an SQL statement, the statement is compiled into the package when the application is built, instead of at run time. Each statement is parsed, and a more efficiently interpreted operand string is stored in the package. At run time, the code generated by the precompiler calls run-time services database manager APIs with any variable information required for input or output data, and the information stored in the package is executed.
The advantages of precompilation apply only to static SQL statements. SQL statements that are executed dynamically (using PREPARE and EXECUTE or EXECUTE IMMEDIATE) are not precompiled; therefore, they must go through the entire set of processing steps at run time.
| Note: | Do not assume that a static version of an SQL statement automatically executes faster than the same statement processed dynamically. In some cases, this is true because of the overhead required to prepare the dynamic statement. In other cases, the same statement prepared dynamically executes faster, because the optimizer can make use of current database statistics, rather than the database statistics available at an earlier bind time. Note that if your transaction takes less than a couple of seconds to complete, static SQL will generally be faster. To choose which method to use, you should prototype both forms of binding. For a detailed comparison of static and dynamic SQL, see "Comparing Dynamic SQL with Static SQL". |
When generating a package or a bind file, the precompiler generates a timestamp. The timestamp is stored in the bind file or package and in the modified source file.
When an application is precompiled with binding enabled, the package and modified source file are generated with timestamps that match. When the application is run, the timestamps are checked for equality. An application and an associated package must have matching timestamps for the application to run, or an SQL0818N error is returned to the application.
Remember that when you bind an application to a database, the first eight characters of the application name are used as the package name unless you override the default by using the PACKAGE USING option on the PREP command. This means that if you precompile and bind two programs using the same name, the second will override the package of the first. When you run the first program, you will get a timestamp error because the timestamp for the modified source file no longer matches that of the package in the database.
When an application is precompiled with binding deferred, one or more bind files and modified source files are generated with matching timestamps. To run the application, the bind files produced by the application modules can execute. The binding process must be done for each bind file as discussed in "Binding".
The application and package timestamps match because the bind file contains the same timestamp as the one that was stored in the modified source file during precompilation.
Rebinding is the process of recreating a package for an application program that was previously bound. You must rebind packages if they have been marked invalid or inoperative. In some situations, however, you may want to rebind packages that are valid. For example, you may want to take advantage of a newly created index, or make use of updated statistics after executing the RUNSTATS command.
Packages can be dependent on certain types of database objects such as tables, views, aliases, indexes, triggers, referential constraints and table check constraints. If a package is dependent on a database object (such as a table, view, trigger, and so on), and that object is dropped, the package is placed into an invalid state. If the object that is dropped is a UDF, the package is placed into an inoperative state. For more information, see the Package Dependencies section in the Administration Guide.
Invalid packages are implicitly (or automatically) rebound by the database manager when they are executed. Inoperative packages must be explicitly rebound by executing either the BIND command or the REBIND command. Note that implicit rebinding can cause unexpected errors if the implicit rebind fails. That is, the implicit rebind error is returned on the statement being executed which may not be the statement that is actually in error and if an attempt is made to execute an inoperative package, an error occurs. You may decide to explicitly rebind invalid packages rather than have the system automatically rebind them. This enables you to control when the rebinding occurs.
The choice of which command to use to explicitly rebind a package depends on the circumstances. You must use the BIND command to rebind a package for a program which has been modified to include more, fewer, or changed SQL statements. You must also use the BIND command if you need to change any bind options from the values with which the package was originally bound. In all other cases, use either the BIND or REBIND command. You should use REBIND whenever your situation does not specifically require the use of BIND, as the performance of REBIND is significantly better than that of BIND.
For details on the REBIND command, see the Command Reference.