Bind, rebind, and db2rbind of DB2 package

We usually know that the DB2 package needs to be rebound after the statistical information or dependent object changes. Otherwise, the DB2 optimizer will run the package using an execution plan that is less efficient or wrong, the execution performance may be seriously degraded, or the SP may remain stuck. Therefore, the SP rebinding operation is performed on a regular basis, which greatly improves the overall performance and efficiency of the database. However, check that the SP rebinding of DB2 provides three commands: many people cannot tell the difference between db2rbind, rebind, and bind. Therefore, this article focuses on the usage of the three commands.

In fact, all three provide SP re-binding, but the input parameters are different, bind needs to be included. bnd files, under the condition of defects, when the SP written in C or embedded SQL is precompile,. bnd end file, the database uses the same reason for compiling the package and the application, that is, to improve performance and compression, in the package build process rather than the execution process, the related syntax and semantics in SP are analyzed, and the package stores operation character groups with higher interpretation efficiency. The server manager calls the pre-compiled code, you only need to provide the corresponding input variables for execution, improving the efficiency. In addition, pre-compilation is only applicable to static SQL, but not for dynamic SQL statements such as prepare execute and execute immediate. These dynamic SQL statements still need to be re-compiled and explained. Db2 provides the BIND file description tool db2bfd to view the bnd file. The usage is as follows:

C: \ Program Files (x86) \ IBM \ SQLLIB \ bnd> db2bfd

Usage: db2bfd [[-B] [-h] [-s] [-v] <filespec>

Where: <filespec> is at least a V7 bind file

Options:-B = display bind file header
-H = display this information
-S = display SQL statements
-V = display host variable declarations

C: \ Program Files (x86) \ IBM \ SQLLIB \ bnd> db2bfd-B db2sampl_ SQL .bnd

Db2sampl_ SQL .bnd: Header Contents

Header Fields:

Field Value
----------
ReleaseNum 0x800
Endian 0x4c
NumHvars 16
MaxSect 14
NumStmt 26
OptInternalCnt 4
OptCount 10

Name Value
-----------------------
Isolation Level Cursor Stability
Creator "NULLID"
App Name "SQLE6H02"
Timestamp "AAAAABIU: 2004/08/01 00: 00: 00: 00"
Cnulreqd Yes
SQL Error No package
Block All
Validate Bind
Date Default/local
Time Default/local

*** All other options are using default settings as specified by the server ***

C: \ Program Files (x86) \ IBM \ SQLLIB \ bnd> db2bfd-s db2sampl_ SQL .bnd

Db2sampl_ SQL .bnd: SQL Statements = 26

Line Sec Typ Var Len SQL statement text
-------------------------------------------------------------------------
55 0 5 0 21 BEGIN DECLARE SECTION
76 0 2 0 19 END DECLARE SECTION
275 0 14 0 32 WHENEVER SQLERROR GOTO ENDOFPROC
3366 1 9 1 26 execute immediate: H00001
3416 2 0 1 40 values current sqlid into: hsf-8
3418 3 0 1 32 set current sqlid =: hsf-9
3437 4 0 1 34 set current sqlid =: hsf-8
3459 5 0 1 40 values current sqlid into: hsf-8
3461 6 0 1 32 set current sqlid =: hsf-9
3547 7 11 1 24 PREPARE S1 FROM: H00001
3582 7 11 1 24 PREPARE S1 FROM: H00001
3598 8 0 1 34 set current sqlid =: hsf-8
3616 0 5 0 21 BEGIN DECLARE SECTION
3619 0 2 0 19 END DECLARE SECTION
3652 7 9 4 56 EXECUTE S1 USING: H00004,: H00015,: H00005,: H00006
3674 7 9 4 56 EXECUTE S1 USING: H00004,: H00016,: H00005,: H00006
3720 9 0 1 41 values current schema into: hsf-8
3774 10 0 1 33 set current schema =: hsf-9
3977 11 11 1 32 prepare blobupdate from: H00001
4005 12 11 1 32 prepare clobupdate from: H00001
4248 13 0 1 86 SELECT NUM_DB_STORAGE_PATHS INTO: H00014 FR
Om table (SNAP_GET_DB ('',-1 ))
4416 14 0 1 35 set current schema =: hsf-8
4475 11 9 3 58 execute blobupdate using: H00010,: H00005,: H00012

4492 11 9 3 58 execute blobupdate using: H00010,: H00005,: H00012

4551 12 9 3 59 execute clobupdate using: H00011,: H00005,: H00013

4573 12 9 3 59 execute clobupdate using: H00011,: H00005,: H00013

Specifically, the package is developed and compiled using embedded languages such as C. Therefore, bind is usually required for binding. Because the bind Command needs to input the bnd parameter file name, for example:

C: \ Program Files (x86) \ IBM \ SQLLIB \ bnd> db2 bind db2sampl_ SQL .bnd

Line messages for db2sampl_ SQL .bnd
--------------------------------------------------------------------------
SQL0061W The binder is in progress.
SQL0091N Binding was ended with "0" errors and "0" warnings.
Bind the package to the current database. Of course, there are other parameters. A package will be created for each separately pre-compiled source code module. If the application has five source files, three of which need to be pre-compiled, three packages or bind files will be created. By default, the name specified for each package is the same as the name of the source module based on the. bnd file, but this name will be truncated to 8 characters. To explicitly specify another PACKAGE name, you must use the package using option in the PREP command. The VERSION of the package is specified by the VERSION pre-compilation option, and the default value is null. If the name and mode of the newly created package are the same as the existing package in the target database but the version ID is different, the new package is created and the previous package is retained. However, if a package that matches the name, mode, and version of the bound package exists, the package will be deleted and replaced with the new package being bound. If action add is specified during binding, this condition is not allowed, but an error is returned (SQL0719 ).

DB2 database performance adjustment and optimization (1st and 2) PDF

DB2 database performance optimization

DB2 9.7 for Linux 5.4 Installation Steps

For more details, please continue to read the highlights on the next page: