Binding an application that uses SQL

Before you can run your application program, a relationship between the program and any specified tables and views must be established. This process is called binding. The result of binding is an access plan.

The access plan is a control structure that describes the actions necessary to satisfy each SQL request. An access plan contains information about the program and about the data the program intends to use.

For a nondistributed SQL program, the access plan is stored in the program. For a distributed SQL program (where the RDB parameter is specified on the CRTSQLxxx command), the access plan is stored in the SQL package at the specified relational database.

SQL automatically attempts to bind and create access plans when the program object is created. For non-ILE compilations, this occurs as the result of running a successful CRTxxxPGM command. For ILE compilations, this occurs as the result of running a successful CRTBNDxxx, CRTPGM, or CRTSRVPGM command. If Db2® for i detects at run time that an access plan is not valid (for example, the referenced tables are in a different library) or detects that changes have occurred to the database that might improve performance (for example, the addition of indexes), a new access plan is automatically created. Binding does the following things:

  1. It revalidates the SQL statements using the description in the database. During the bind process, the SQL statements are checked for valid table, column, and other object names. If a specified table or object does not exist at the time of the precompile or compile, the validation is done at run time. If the table or object does not exist at run time, a negative SQLCODE is returned.
  2. It selects the index needed to access the data your program wants to process. In selecting an index, table sizes, and other factors are considered. It considers all indexes available to access the data and decides which ones (if any) to use when selecting a path to the data.
  3. It attempts to build access plans. For each SQL statement that is valid, the bind process builds and stores an access plan in the program.

If the characteristics of a table or view your program accesses have changed, the access plan may no longer be valid. When you attempt to run a program that contains an access plan that is not valid, the system automatically attempts to rebuild the access plan. If the access plan cannot be rebuilt, a negative SQLCODE is returned. In this case, you might have to change the program's SQL statements and reissue the CRTSQLxxx command to correct the situation.

Assume that a program contains an SQL statement that refers to COLUMNA in TABLEA and the user deletes and re-creates TABLEA so that COLUMNA no longer exists. When you call the program, the automatic rebind will be unsuccessful because COLUMNA no longer exists. In this case you must change the program source and reissue the CRTSQLxxx command.