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 as shown in Figure 1:
Other topics discussed in this section include:
To create the packages needed by SQLJ applications, you need to use both the SQLJ translator and db2profc command. For more information on using the SQLJ translator, see SQLJ Programming.
After you create the source files, you must precompile each host language file containing SQL statements with the PREP command for host language source files. 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 you precompile application programs at the client workstation and the precompiler generates modified source and messages on the client, the precompiler uses the server connection to perform some of the validation.
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.
A typical example of using the precompiler follows. To precompile a C embedded SQL source file called filename.sqc, you can issue the following command to create a C source file with the default name filename.c and a bind file with the default name filename.bnd:
DB2 PREP filename.sqc BINDFILE
For detailed information on precompiler syntax and options, see the Command Reference.
The precompiler generates up to four types of output:
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, you cannot precompile a SELECT statement 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 refer to the Command Reference.
You must always precompile a source file against a specific database, even if eventually you do not use the database with the application. In practice, you can use a test database for development, and after you fully test the application, you can bind its bind file to one or more production databases. See Advantages of Deferred Binding for other ways to use this feature.
If your application uses a code page that 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 refer to 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 host or AS/400 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 Universal Database for 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 Universal Database for 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 Universal Database for OS/390 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 does not terminate preprocessing abnormally.
For details about the PREP command, refer to 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.
Refer to the Application Building Guide or other programming documentation for your operating platform for any exceptions to the default compile options. Refer to 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 32-bit operating systems, the DLLs are loaded directly by the application and not by the DB2 database manager. On Windows 32-bit operating systems, the database manager can load DLLs. Stored procedures are normally built as DLLs or shared libraries. For information on using stored procedures, see Stored Procedures. |
For information on creating executable files on other platforms supported by DB2, refer to the Application Building Guide.
To create the executable file, link 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.
A typical example of using the BIND command follows. To bind a bind file named filename.bnd to the database, you can issue the following command:
DB2 BIND filename.bnd
For detailed information on BIND command syntax and options, refer to the Command Reference.
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. To make subsequent changes, 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 values of a number of special registers determine the statement compilation environment:
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 user_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 default qualifier is 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 uses a different code page from 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, you must bind the supplied utility bind files to the database. For details, refer to the Quick Beginnings guide for your platform.
You can use bind options to control certain operations that occur during binding, as in the following examples:
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 connected to the database hold 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 server using DB2 Connect, you can use the BIND options available for that server. For details on the BIND command and its options, refer to the Command Reference.
Bind files are not backward compatible with previous versions of DB2 Universal Database. In mixed-level environments, DB2 can only use the functions available to the lowest level of the database environment. For example, if a V5.2 client connects to a V5.0 server, the client will only be able to use V5.0 functions. As bind files express the functionality of the database, they are subject to the mixed-level restriction.
If you need to rebind higher-level bind files on lower-level systems, you can:
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. You can design the application to bind itself to a database only when the application calls the task requiring SQL statements, 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. You can ship the associated bind files with the application.
With the DB2 Bind File Description (db2bfd) utility, you can easily display the contents of a 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 utility is located in the bin subdirectory of the sqllib directory of the instance.
Its syntax is:
.--------------.
V (1) | (5)
>>-db2bfd----+--h------+--+---filespec-------------------------><
| (2) |
+--b------+
| (3) |
+--s------+
| (4) |
'--v------'
Notes:
For more information on db2bfd, refer to the Command Reference.
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 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 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, static SQL is faster 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, refer to the Administration Guide: Planning.
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. 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, refer to the Command Reference.