DB2 packages: Concepts, examples, and common problems
来源：互联网 发布：淘宝刷流量软件哪个好 编辑：程序博客网 时间：2024/05/21 18:39
Understanding DB2 system and user application packages
Summary: Have you ever asked yourself why you have a package and application mismatch being reported while developing applications? Have you ever wondered how many DB2® system packages are created by default? Or when updated DB2 system package versions are bound to your database as new DB2 client levels and are brought on-line? Ever wonder what was inside a package? If you answered yes to any of these questions, you've come to the right place. In this article, find a detailed discussion of DB2 packages, both system generated and user generated. Get an introduction to many concepts that are important to understanding packages with detailed examples. And review a select subset of common problems that users and DBAs often encounter.
Tags for this article: about_the_product, access, apis, bind, binding,configuration_and_administration, data_access, databases_and_data_management, db2,db2_for_linux_and_unix_and_windows... more tags
Date: 01 Jun 2006
Level: Intermediate
Activity: 45490 views
Comments: 5 (View | Add comment - Sign in)
Average rating (46 votes)Rate this article
What are packages?
Packages in DB2 are control-structure database objects that contain executable forms of SQL statements or placement holders for executable forms. In DB2 for Linux®, UNIX®, and Windows®, packages may also be referred to as access plans. Packages are stored in the database system catalog tables.
If an application intends to access a database using static SQL, the application developer must embed the appropriate SQL statements in the program source code. When the program source code is converted to an executable object (static SQL) or executed (dynamic SQL), the strategy for executing each embedded SQL statement is stored in a package as a single section. Each section is a bound form of the embedded SQL statement, and this form contains information such as which index to use and how to use the index (basically an access plan).
DB2 packages are used to execute SQL statements on the database server. They play a crucial role in how a DB2 client application interacts with the DB2 server.
Back to top
Common use of packages in DB2
The general use of packages in DB2 can be categorized into two areas:
- DB2 system packages: This set of packages is supplied with all DB2 products (with the exception of the run time client). It consists of utilities and drivers that are required by DB2 clients to connect and perform tasks on a database server.
- Embedded SQL packages: The second set includes packages that are created by the user in the form of embedded applications or stored procedures.
The following sections discuss the use of both kinds of packages in detail.
Back to top
DB2 system packages
The following are list files (*.lst) of packages that are required to be bound to the database from an administrative client or full DB2 product. Binding must be done once per db2level against every remote DB2 server the given installed code connects to.
Here are the typical commands used to bind system packages from the command line against DB2 Linux, UNIX, and Windows databases:
Listing 1. Commands to bind system packages
bind BNDPATH/@db2ubind.lst blocking all sqlerror continue messages bind.msg grant publicbind BNDPATH/@db2cli.lst blocking all sqlerror continue messages bind.msg grant public
All host database systems have their own *.lst files for binding purposes:.
ddcsmvs.lst - for DB2 for z/OS® and OS/390®
ddcsvm.lst - for DB2 for VM®
ddcsvse.lst - for DB2 for VSE®
ddcs400.lst - for DB2 for AS/400® and iSeries™
The *.lst files are simple text files that contain a list of bind (*.bnd ) files. When binding the list file, you must specify the '@' character (at sign) and qualify the location of the *.lst file for the bind to be successful.
Among the various system packages, this article concentrates on the CLI packages (db2cli.lst).
Packages for each db2level are unique. They are specific to each DB2 fix pack level and platform, hence the need to bind packages from all unique levels of DB2 in your environment (at least once at install time).
Every new DB2 fix pack may have changes built into system packages. These changes result from APARs (defects) or feature additions. Changes to system packages will result in a change to the package names. This change to package naming is done to ensure that the new package does not interfere with another existing client that may be running at an earlier fix pack release. The existing client would require previously bound packages (bound at install time) to run applications at its given level. If the names were not changed as required, there would be no way to easily identify which package that existing client should use should another newer package be bound after a change has taken place.
Historically, DB2 would always have a unique package name depending on the platform and db2level for the client from which the package originated. This design resulted in many unnecessary packages on the server over time, as clients were cycled out of use as they aged. In DB2 UDB Version 7.2 and 8.x, the package name will only change if code changes demand an update to the DB2 packages.
With the installation of any new fix pack, a user with BINDADD authority on the database server is required to bind the necessary system bind files that exist in the sqllib/bnd directory.
DB2 Call Level Interface (CLI) packages
The DB2 Call Level Interface (DB2 CLI) is a callable SQL interface to the DB2 family of database servers. A callable SQL interface is an application program interface (API) for database access, which uses function calls to invoke dynamic SQL statements. It is an alternative to embedded dynamic SQL, but unlike embedded SQL, it does not require precompiling or binding for each application.
DB2 CLI is based on the Microsoft® Open Database Connectivity (ODBC) specification and the X/Open specification.
The DB2 CLI driver automatically creates DB2 packages required at connection time if packages are not found on the server. This is known as autobind. If a package already exists, the driver uses the existing package.
These packages are also required to be bound when fix packs are upgraded within the environment in question. By default, DB2 packages created by the DB2 driver are created in the NULLID collection (or library).
A dynamic section is the actual executable object that contains the logic needed to satisfy a dynamic SQL request. Each statement handle allocated in a CLI application will occupy one section within a CLI package.
In some cases, you may need to create additional DB2 CLI packages that in turn will contain more than the default number of dynamic sections. Should your application require more than the default number of sections (if you have an application that under heavy load has allocated more statement handles than there are sections, for example), you will fall into this scenario. The CLIPKG option discussed below demonstrates how to create more CLI packages.
CLI package naming convention
For each CLI bind file, generate multiple packages with different package names. The number of packages generated is equal to the number of isolation levels TIMES the value of the CLIPKG bind option. The default is three for the CLIPKG option. Theoretically, CLIPKG can be a maximum of 255 (ff). That is the limit due to our naming convention.
Figure 1. Naming convention for CLI packages
The cursors in the packages follow the naming convention in Listing 2:
Listing 2. Naming convention for cursors
Example: SQL_CURLH100C384 SQL : constant for all cursors L or S: package size H or N: hold or nohold 0,1,2,3,4 : isolation defined in sqllib\include\sqlmon.h Cursor : constant for all cursors 1 to 64 sections in small pkgs and 1 to 384 sections in large packages
Table 1. DB2 CLI bind files and package names
Notes for Table 1:
- 'S' represents a small package, and 'L' represents a large package
- 'H' represents WITH HOLD, and 'N' represents NOT WITH HOLD
- 'x' is the isolation level: 0=NC, 1=UR, 2=CS, 3=RS, 4=RR
- 'yy' is the package iteration 00 through FF
- 'zz' is unique for each platform
For example, for the dynamic packages:
- SYSSN100 - A small package (65 sections), where all cursor declarations are for non-held cursors. Bound with isolation level UR. This is the first iteration of that package.
- SYSLH401 - A large package (385 sections), where all cursor declarations are for held cursors. Bound with isolation level RS. This is the second iteration of that package.
Bind options used for CLI Packages
CLI packages are automatically bound with ACTION ADD. Therefore, once the packages are created, any subsequent attempt at binding them will return an SQLCODE of -719 or -721 before commencing the whole bind process. The binder turns off this -719/-721 error and does not show it to the user. However, if a DB2 trace is taken, the -719 / -721 is logged. The ACTION ADD speeds up the binding process by only creating the package if it does NOT exist. Should the package already exist, under the covers, you would see the SQLCODEs denoted above and binding would stop. ACTION ADD eliminates lock contention for replacing already existing packages.
ACTION ADD is only sent to Linux, UNIX, and Windows and z/OS. Do not send it to other servers (for example, VSE, VM, OS400), as it was not supported at the time of this implementation.
When binding CLI, ignore all user-specified options, except ACTION, REPLVER, COLLECTION, and CLIPKG (other than options specified in the bind file). However, if COLLECTION has been specified, then all options are processed.
Package sections and their impact on CLI
The use of packages poses a theoretical limit on the number of handles any application (which utilizes CLI) can allocate. Version 7 had a maximum limit of 512 handles per application. Version 8 has a maximum limit of 16384 per application.
It is possible to allocate up to 16,384 statement handles, but the application would have to use multiple isolation levels and hold cursor combinations since there are limits based on the package information.
Note that in addition to a theoretical limit, physical resource limitations, like memory, restrict actual statement handles any application can allocate.
The maximum theoretical number of sections that may be used at one time within a CLI application is 11,712. By default, three sections from each package (both large and small) are reserved for positioned update and delete statements. This is done since an UPDATE/DELETE WHERE CURRENT OF needs to be driven in the same package from which the cursor that executes the UPDATE/DELETE originated. Since there are a maximum of 33 packages (three small and up to 30 large ), reserve 99 (=33X3) sections in total for positioned update and deletes. That means real available sections or handles are 11,712 - 99 = 11,613.
So an application working with only CS isolation and HOLD cursors would effectively only be able to use about 11,613 statements (assuming no positioned updates or deletes). If, however, the application used a different isolation level or WITHOUT HOLD cursors as well as CS and HOLD CURSORs, it is possible to hit the maximum statement limit of 16,384.
Package associated CLI keywords
CLI keywords are set in the db2cli.ini file. An application will load and read the db2cli.ini file on the same machine on which it is executed. You will find a copy of the db2cli.ini file on all installed instances of DB2. So ensure that any application-driven changes to the db2cli.ini file occurs on the proper version of the file and in the correct location,which in most cases is wherever the application is run.
Keywords are only read from the db2cli.ini configuration file at the time an application is initialized. You can find a list of CLI keywords in Table 2.
JDBC packages
Both the Legacy CLI-based JDBC and the Universal JDBC T2/T4 drivers use the same packages as the CLI driver discussed above. This piece of information is very handy in that if your target database already had CLI packages bound to it for the level of code your JDBC driver is based on, you do not need to bind the packages again. You are free to use them. The reverse is also true -- should you have the need to perform a bind against a newly created database with the JDBC driver, once the packages are created, all JDBC and CLI applications are free to use. Any CLI application does not need to perform a bind to run against that target database.
Note: Again, remember packages may be different for every fix pack released by DB2. They are level dependent, and each fix pack should have its set of packages bound to any target database that it plans on connecting to and running applications against.
Should a newly installed JDBC client require its packages bound to an existing target database, that client may use thedb2jdbcbind command to achieve this bind.
db2jdbcbind - DB2 JDBC package binder command
db2jdbcbind is used to bind or rebind the JDBC packages to a DB2 database. DB2 V8 databases already have the JDBC packages preinstalled, thus db2jdbcbind usually only needs to be run usually downlevel servers.
The authorization required to run db2jdbcbind is one of the following:
- sysadm
- dbadm
- BINDADD privilege if a package does not exist, and one of:
- IMPLICIT_SCHEMA authority on the database if the schema name of the package does not exist
- CREATEIN privilege on the schema if the schema name of the package exists
- ALTERIN privilege on the schema if the package exists
- BIND privilege on the package if it exists. This command establishes a database connection, so no previous connection is necessary.
Listing 3. Command syntax - db2jdbcbind
>>-db2jdbcbind--+------+-- -url jdbc:db2://server:port/dbname---> '-help-'>-- -user--username-- -password--password----------------------->>--+-----------------------------+------------------------------> '- -collection--collection ID-'>--+----------------------------+-------------------------------> '- -size--number of packages-' .-,------------------------------. V |>-- -tracelevel----+-TRACE_ALL------------------+-+------------->< +-TRACE_CONNECTION_CALLS-----+ +-TRACE_CONNECTS-------------+ +-TRACE_DIAGNOSTICS----------+ +-TRACE_DRDA_FLOWS-----------+ +-TRACE_DRIVER_CONFIGURATION-+ +-TRACE_NONE-----------------+ +-TRACE_PARAMETER_META_DATA--+ +-TRACE_RESULT_SET_CALLS-----+ +-TRACE_RESULT_SET_META_DATA-+ '-TRACE_STATEMENT_CALLS------'
Command parameters:
- help - Displays the help information for the command; any other parameter or option will be ignored when this is used.
- url jdbc:db2://(server:port/dbname) - A standard JDBC URL. Used for establishing the database connection. The DB2 JDBC type 4 driver is used to establish the connection, hence the use of the server and port parameters.
- user (username) - Specifies the userid used when connecting to the database the command is run against.
- password (password) - Specifies the password for the userid above.
- collection (collection ID) - The collection ID used for the packages created (CURRENT PACKAGESET). Default is NULLID. Use this parameter to create multiple copies of the package set. This option may only be used with the Connection or DataSource property currentPackageSet.
- size (number of packages) - The number of internal packages to bind for each isolation level and cursor holdability setting. The default is three. Since there are four isolation levels and two cursor holdability settings, there will be (4x2=8) eight times as many dynamic packages bound as are specified by this option. A sole static package is always bound for internal use.
- tracelevel - Only required for troubleshooting bind issues with the
db2jdbcbindcommand. Options are listed above in the command diagram (Listing 3).
Back to top
Embedded SQL
Up to this point, this article has discussed all packages that are associated with the CLI and JDBC drivers that ship with DB2. Applications built to use these drivers may use previously bound packages to execute their SQL statements against any target database that has these requisite packages bound.
Embedded SQL is another type of application that will need package(s) bound against a database so that it can execute its SQL. What differentiates these applications from the applications discussed thus far is that all embedded SQL applications have their own packages created and bound against any database it is designed to connect to and run against. These packages for embedded SQL type applications are used ONLY by the application that the bind file was created for. They are not shared with other applications, as seen above with the dynamic CLI and JDBC packages. Both static and dynamic statements may be run within these packages and associated sections as well. The benefits of a static execution (in other words, using packages bound solely for the use of its partner program) are reduced resource consumption, improved diagnostics, improved security, and greater repeatability of SQL performance due to static access paths and plans. Everything you need to get from the data is already in the package bound at bind time.
Embedded applications can be written in a number of host languages. They include C/C++, Java™ (SQLJ), COBOL, and FORTRAN. It should be noted that SQL statements placed in an application are not specific to the host languages. However, the method in which the database manager converts the SQL syntax for processing by the host languages is different. This processing difference arises because Java (SQLJ) requires different commands and steps from the other languages.
Non-Java embedded applications
These include the C/C++, COBOL and FORTRAN languages. For these host programming languages, SQL syntax processing is handled by the DB2 precompiler (invoked using the PREP command), which converts embedded SQL statements into DB2 run time service API calls.
Package creation for embedded SQL
To run applications written in compiled host languages, you must create the packages needed by the database manager at execution time. This involves the steps, as shown in the Figure 2:
Figure 2. Embedded SQL creation overview
PRECOMPILE, BIND, REBIND and DB2RBIND commands
The PRECOMPILE, BIND, and DB2RBIND commands require you to already be connected to the target database. You need to have connected with a userid that has sufficient authority to issue the commands listed above.
PRECOMPILE command
PRECOMPILE is an application process that modifies source files containing embedded SQL statements (*.sqc) and yields host language calls consisting of a source file(s) (*.c) and a package.
Without the any bind option, no bind file is created, but the package is created automatically at precompile time and bound to the database. Precompiling with the bindfile option provides a means to defer binding to some later point in time (rather than at precompile time). This deferred binding allows the same bind file package to be bound against multiple databases, thus allowing easy and consistent deployment across multiple database servers. It also lets the user create packages without access to the source code.
For the following set of examples, the sample.sqc application will be used:
Listing 4. sample.sqc
/*--------------------------------------------------------------Sample developerworks embedded SQL C Program for DB2.This sample application utilizes the DB2 sample databasewhich can be created using the "db2sampl" command.You need to connect to the sample database then issue db2 prepin order to generate a bind file. db2 prep sample.sqc bindfile cl -Zi -Od -c -W2 -DWIN32 sample.c link -debug -out:sample.exe sample.obj db2api.libThis sample application takes one argument on the command line,a DEPT (department id) from staff table.It then finds the total number of staff registered with thegiven DEPT ID.--------------------------------------------------------------*/#include <stdio.h>#include <stdlib.h>#include <string.h>#include <sqlcodes.>#include <sqlenv.h>#define DONE 0/* Internal variables */char msg[1025];int rc;int errcount;/* Declare the SQL interface variables */EXEC SQL BEGIN DECLARE SECTION ; short total; short udept;EXEC SQL END DECLARE SECTION ;EXEC SQL INCLUDE SQLCA ;/*Outputs SQLCA*/OUTPUT_MESSG() { if (rc == 0 && sqlca.sqlcode != 0) { sqlaintp(msg, 1024, 0, &sqlca); printf("SQLCA Message: %s\n",msg); } }/* This macro prints out all fields in the SQLCA */PRINT_SQLCA() \ { \ printf("The SQLCA Structure\n"); \ printf("SQLCAID: %s\n", sqlca.sqlcaid); \ printf("SQLCABC: %d\n", sqlca.sqlcabc); \ printf("SQLCODE: %d\n", sqlca.sqlcode); \ printf("SQLERRML: %d\n", sqlca.sqlerrml); \ printf("SQLERRMC: %s\n", sqlca.sqlerrmc); \ printf("SQLERRP: %s\n", sqlca.sqlerrp); \ printf("SQLERRD[0]: %d\n", sqlca.sqlerrd[0]); \ printf("SQLERRD[1]: %d\n", sqlca.sqlerrd[1]); \ printf("SQLERRD[2]: %d\n", sqlca.sqlerrd[2]); \ printf("SQLERRD[3]: %d\n", sqlca.sqlerrd[3]); \ printf("SQLERRD[4]: %d\n", sqlca.sqlerrd[4]); \ printf("SQLERRD[5]: %d\n", sqlca.sqlerrd[5]); \ printf("SQLWARN: %s\n", sqlca.sqlwarn); \ printf("SQLSTATE: %s\n", sqlca.sqlstate); \ }/* Prints the message in the SQLCA */STATUS(code,text_string,eExit) \ { \ OUTPUT_MESSG(); \ if (sqlca.sqlcode != code || rc != 0 ) { \ printf("%s\n",text_string); \ printf("Expected code = %d\n",code); \ if (rc == 0) { \ PRINT_SQLCA(); \ } \ else printf("RC: %d\n",rc); \ errcount += 1; \ if (eExit == DONE) { EXEC SQL CONNECT RESET; } \ } \ }/*----------------------------------------------------------------------------Main section----------------------------------------------------------------------------*/main (int argc, char *argv[]){ /* Grab the first command argument. This is the DEPT. */ if (argc > 1) { udept = atoi(argv[1]); printf("Total personnel was requested for department : %d.\n", udept); /* If there is no argument, exit. */ } else { printf("Which DEPT would you like to enquire?\n"); exit(0); } EXEC SQL CONNECT TO SAMPLE; STATUS(0, "Connect error", DONE); /* Find the total number of personnel for given dept. */ EXEC SQL SELECT COUNT(ID) into :total FROM STAFF WHERE DEPT = :udept; STATUS(0, "Query error ", DONE); /* Report total number of personnel. */ printf("Total number of personnel in the department %d is %d.\n", udept, total);}Listing 5. DB2 PREP command output -- prep sample.sqc
D:\Sample\test>db2 connect to sample Database Connection information Database server = DB2/NT 8.2.0 SQL authorization ID = CHUNJ Local database alias = SAMPLE D:\Sample\test>db2 prep sample.sqc bindfile LINE MESSAGE FOR sample.sqc ------ ------------------------------------------------------------ SQL0060W The "C" precompiler is in progress. SQL0091W Precompilation or binding was ended with "0" errors and "0" warnings. D:\Sample\test>
The PRECOMPILE step results in a modified source file. In this case, it is called sample.c
It is at this precompile time that the TIMESTAMP, which is also known as the UNIQUE ID or CONSISTENCY TOKEN, is generated and is associated with the package through the bind file and modified source code. This will be discussed further on in a later section.
Listing 6. sample.c
static char sqla_program_id[162] ={ 42,0,65,68,65,75,65,73,83,65,77,80,76,69,32,32,110,65,111,67, 78,89,69,87,48,49,49,49,49,32,50,32,8,0,67,72,85,78,74,32, 32,32,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, 0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0, 0,0};#include "sqladef.h"static struct sqla_runtime_info sqla_rtinfo ={{'S','Q','L','A','R','T','I','N'}, sizeof(wchar_t), 0, {' ',' ',' ',' '}};static const short sqlIsLiteral = SQL_IS_LITERAL;static const short sqlIsInputHvar = SQL_IS_INPUT_HVAR;#line 1 "sample.sqc"/*--------------------------------------------------------------------Sample developerworks embedded SQL C Program for DB2.This sample application utilizes the DB2 sample databasewhich can be created using the "db2sampl" command.You need to connect to the sample database then issue db2 prepin order to generate a bind file. db2 prep sample.sqc bindfile cl -Zi -Od -c -W2 -DWIN32 sample.c link -debug -out:sample.exe sample.obj db2api.libThis sample application takes one argument on the command line,a DEPT (department id) from staff table.It then finds the total number of staff registered with thegiven DEPT ID.---------------------------------------------------------------------*/#include <stdio.h>#include <stdlib.h>#include <string.h>#include <sqlcodes.h>#include <sqlenv.h>#define DONE 0/* Internal variables */char msg[1025];int rc;int errcount;/* Declare the SQL interface variables *//*EXEC SQL BEGIN DECLARE SECTION ;*/#line 33 "sample.sqc" short total; short udept;/*EXEC SQL END DECLARE SECTION ;*/#line 37 "sample.sqc"/*EXEC SQL INCLUDE SQLCA ;*//* SQL Communication Area - SQLCA - structures and constants */#include "sqlca.h"struct sqlca sqlca;#line 39 "sample.sqc"/*Outputs SQLCA*/OUTPUT_MESSG() { if (rc == 0 && sqlca.sqlcode != 0) { sqlaintp(msg, 1024, 0, &sqlca); printf("SQLCA Message: %s\n",msg); } }/* This macro prints out all fields in the SQLCA */PRINT_SQLCA() \ { \ printf("The SQLCA Structure\n"); \ printf("SQLCAID: %s\n", sqlca.sqlcaid); \ printf("SQLCABC: %d\n", sqlca.sqlcabc); \ printf("SQLCODE: %d\n", sqlca.sqlcode); \ printf("SQLERRML: %d\n", sqlca.sqlerrml); \ printf("SQLERRMC: %s\n", sqlca.sqlerrmc); \ printf("SQLERRP: %s\n", sqlca.sqlerrp); \ printf("SQLERRD[0]: %d\n", sqlca.sqlerrd[0]); \ printf("SQLERRD[1]: %d\n", sqlca.sqlerrd[1]); \ printf("SQLERRD[2]: %d\n", sqlca.sqlerrd[2]); \ printf("SQLERRD[3]: %d\n", sqlca.sqlerrd[3]); \ printf("SQLERRD[4]: %d\n", sqlca.sqlerrd[4]); \ printf("SQLERRD[5]: %d\n", sqlca.sqlerrd[5]); \ printf("SQLWARN: %s\n", sqlca.sqlwarn); \ printf("SQLSTATE: %s\n", sqlca.sqlstate); \ }/* Prints the message in the SQLCA */STATUS(code,text_string,eExit) \ { \ OUTPUT_MESSG(); \ if (sqlca.sqlcode != code || rc != 0 ) { \ printf("%s\n",text_string); \ printf("Expected code = %d\n",code); \ if (rc == 0) { \ PRINT_SQLCA(); \ } \ else printf("RC: %d\n",rc); \ errcount += 1; \ if (eExit == DONE) {/*EXEC SQL CONNECT RESET;*/{#line 88 "sample.sqc" sqlastrt(sqla_program_id, &sqla_rtinfo, &sqlca);#line 88 "sample.sqc" sqlacall((unsigned short)29,3,0,0,0L);#line 88 "sample.sqc" sqlastop(0L);}#line 88 "sample.sqc" } \ } \ }/*----------------------------------------------------------------------------Main section----------------------------------------------------------------------------*/main (int argc, char *argv[]){ /* Grab the first command argument. This is the DEPT. */ if (argc > 1) { udept = atoi(argv[1]); printf("Total personnel was requested for department : %d.\n", udept); /* If there is no argument, exit. */ } else { printf("Which DEPT would you like to enquire?\n"); exit(0); }/*EXEC SQL CONNECT TO SAMPLE;*/{#line 108 "sample.sqc" sqlastrt(sqla_program_id, &sqla_rtinfo, &sqlca);#line 108 "sample.sqc" sqlaaloc(2,1,1,0L); { struct sqla_setdata_list sql_setdlist[1];#line 108 "sample.sqc" sql_setdlist[0].sqltype = 460; sql_setdlist[0].sqllen = 7;#line 108 "sample.sqc" sql_setdlist[0].sqldata = (void*)"SAMPLE";#line 108 "sample.sqc" sql_setdlist[0].sqlind = 0L;#line 108 "sample.sqc" sqlasetdata(2,0,1,sql_setdlist,NULL,0L); }#line 108 "sample.sqc" sqlacall((unsigned short)29,4,2,0,0L);#line 108 "sample.sqc" sqlastop(0L);}#line 108 "sample.sqc" STATUS(0, "Connect error", DONE); /* Find the total number of personnel for given dept. *//*EXEC SQL SELECT COUNT(ID) into :total FROM STAFF WHERE DEPT = :udept;*/{#line 114 "sample.sqc" sqlastrt(sqla_program_id, &sqla_rtinfo, &sqlca);#line 114 "sample.sqc" sqlaaloc(2,1,2,0L); { struct sqla_setdata_list sql_setdlist[1];#line 114 "sample.sqc" sql_setdlist[0].sqltype = 500; sql_setdlist[0].sqllen = 2;#line 114 "sample.sqc" sql_setdlist[0].sqldata = (void*)&udept;#line 114 "sample.sqc" sql_setdlist[0].sqlind = 0L;#line 114 "sample.sqc" sqlasetdata(2,0,1,sql_setdlist,NULL,0L); }#line 114 "sample.sqc" sqlaaloc(3,1,3,0L); { struct sqla_setdata_list sql_setdlist[1];#line 114 "sample.sqc" sql_setdlist[0].sqltype = 500; sql_setdlist[0].sqllen = 2;#line 114 "sample.sqc" sql_setdlist[0].sqldata = (void*)&total;#line 114 "sample.sqc" sql_setdlist[0].sqlind = 0L;#line 114 "sample.sqc" sqlasetdata(3,0,1,sql_setdlist,NULL,0L); }#line 114 "sample.sqc" sqlacall((unsigned short)24,1,2,3,0L);#line 114 "sample.sqc" sqlastop(0L);}#line 114 "sample.sqc" STATUS(0, "Query error ", DONE); /* Report total number of personnel. */ printf("Total number of personnel in the department %d is %d.\n", udept, total);} BIND command
The BIND command invokes the bind utility. It prepares SQL statements stored in the bind file generated by the precompiler and creates a package that is stored in the database.
After sample.sqc was processed using the PREP command (above), the sample.c and sample.bnd files were produced. (sample.bnd file will only be produced if the bindfile option is specified; otherwise sample.bnd would be implicitly bound and no bind file will be produced).
The BIND command can be used to bind the sample.bnd file against the target database.
Listing 7. DB2 BIND command output -- bind sampl.bnd
D:\Sample\test>db2 connect to sample Database Connection information Database server = DB2/NT 8.2.0 SQL authorization ID = CHUNJ Local database alias = SAMPLE D:\Sample\test>db2 bind sample.bnd LINE MESSAGE FOR sample.bnd ------ ------------------------------------------------------------ SQL0061W The binder is in progress. SQL0091W Binding was ended with "0" errors and "0" warnings. D:\Sample\test>
The bound package information can now be verified by querying the SYSCAT.PACKAGES table:
Listing 8. SYSCAT.PACKAGES content
PKGSCHEMA PKGNAME BOUNDBY DEFINER DEFAULT_SCHEMA VALID UNIQUE_ID ... --------- ------- ------- ------- -------------- ----- --------- ......CHUNJ SAMPLE CHUNJ CHUNJ CHUNJ Y qAMUQUEW ......EXPLICIT_BIND_TIME LAST_BIND_TIME CODEPAGE ...-------------------------- -------------------------- --------2006-04-20-16.36.35.973001 2006-04-20-16.36.35.973001 1252 ...
The resulting sample.c source can be compiled using any of the supported C/C++ compilers. For this example, we used MS Visual Studio C/C++ compiler (cl).The command cl -Zi -Od -c -W2 -DWIN32 sample.c produces the sample.obj file:
Listing 9. Source file compilation on Windows
D:\Sample\test>cl -Zi -Od -c -W2 -DWIN32 sample.c Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 12.00.8804 for 80X86 Copyright (C) Microsoft Corp 1984-1998. All rights reserved. sample.c D:\Sample\test>
The sample.obj file can now be used to create the executable using the link -debug -out:sample.exe sample.obj db2api.libcommand:
Listing 10. Linking object file on Windows
D:\Sample\test>link -debug -out:sample.exe sample.obj db2api.lib Microsoft (R) Incremental Linker Version 6.00.8447 Copyright (C) Microsoft Corp 1984-1998. All rights reserved. D:\Sample\test>
Now that the C-embedded application is compiled and linked to produce an executable, sample.exe, it can be executed. The sample.exe application, in this case, accepts a single parameter that represents the department ID (DEPT) in the database table staff, which exists in the sample database. It returns the total personnel in the staff table that has same DEPT value fed into the application.
Listing 11. Execution of sample embedded application
D:\Sample\test>sample 20 Total personnel was requested for department : 20. Total number of personnel in the department 20 is 4. D:\Sample\test>
Common BIND options:
BLOCKING: Specifies if a cursor will be blocked (in other words, if data will be sent from the database in block sizes (RQRIOBLK) or not)COLLECTION<schema-name>: New schema name for the given package (30 character limit)DATETIME: Specifies default format for date/time returned to the callerDYNAMICRULES: Authorization ID that is used to resolve unqualified object in Dynamic SQLFUNCPATH: Specifies default function path used to resolve unqualified UDFs and stored proceduresGRANT: Explicitly grants EXECUTE privilege on the package to a group and/or userISOLATION: Specifies the default isolation level for the embedded SQL applicationOWNER authorization-ID: Changes the owner of package to a user other than the binder of the packageQUALIFIER qualifier-name: Specifies qualifier for unqualified database objectsQUERYOPT optimization-level: Specifies default query optimization for SQL within the embedded SQL applicationVALIDATE: Specifies when to check for authorization errors (in other words, bind or run time)
The following are specific to DB2 or DB2 add-on product packages:
CLIPKG: Creates more CLI large packages; only applicable for CLI bind filesFEDERATED: Specifies whether static SQL references a federated nickname/view or not
REBIND command
The REBIND command recreates package(s) for an application program that has previously been bound. The authorization ID logged in the BOUNDBY column of the SYSCAT.PACKAGES system catalog table is used as the binder authorization ID for the rebind and for the default schema for the table references in the package.
Listing 12. SYSCAT.PACKAGES output -- SYSCAT.PACKAGES2
PKGSCHEMA PKGNAME BOUNDBY DEFINER DEFAULT_SCHEMA VALID UNIQUE_ID ... --------- ------- ------- ------- -------------- ----- --------- ......CHUNJ SAMPLE CHUNJ CHUNJ CHUNJ Y qAMUQUEW ......EXPLICIT_BIND_TIME LAST_BIND_TIME CODEPAGE ...-------------------------- -------------------------- --------2006-04-20-16.36.35.973001 2006-04-20-16.36.35.973001 1252 ...
One must REBIND a package if the existing bound package has been marked invalid or inoperative.
Invalidated packages will be marked inoperative in the SYSCAT.PACKAGES system catalog table through the VALID column if a function, or object, it depends on is dropped.
Listing 13. SYSCAT.PACKAGES output -- SYSCAT.PACKAGES3
PKGSCHEMA PKGNAME BOUNDBY DEFINER DEFAULT_SCHEMA VALID UNIQUE_ID ... --------- ------- ------- ------- -------------- ----- --------- ......CHUNJ SAMPLE CHUNJ CHUNJ CHUNJ Y qAMUQUEW ......EXPLICIT_BIND_TIME LAST_BIND_TIME CODEPAGE ...-------------------------- -------------------------- --------2006-04-20-16.36.35.973001 2006-04-20-16.36.35.973001 1252 ...
Also, performing a REBIND should be considered when you wish to take advantage of new statistics on a table or after you add a new index to a table involved in the SQL statement in the package.
In the example, if we were to have altered an index on the sample table STAFF, we can issue the REBIND command in Listing 14against the 'sample' package.
Note that for the REBIND command, you specify the package name, not the bind file name. Also, REBIND does not automatically commit the transaction following a successful rebind, so you need to issue an explicit commit.
Listing 14. DB2 REBIND output -- rebind sampl.bnd
D:\Sample\test>db2 connect to sample Database Connection information Database server = DB2/NT 8.2.0 SQL authorization ID = CHUNJ Local database alias = SAMPLE D:\Sample\test>db2 rebind sample.bnd DB20000I The REBIND PACKAGE command completed successfully. D:\Sample\test>db2 commit DB20000I The SQL command completed successfully. D:\Sample\test>
After the rebind, EXPLICIT_BIND_TIME and LAST_BIND_TIME will be changed in the SYSCAT.PACKAGES catalog view.
Listing 15. SYSCAT.PACKAGES output -- SYSCAT.PACKAGES4
PKGSCHEMA PKGNAME BOUNDBY ... UNIQUE_ID ... EXPLICIT_BIND_TIME ...--------- ------- ------- ... --------- ... ------------------------------ ......CHUNJ SAMPLE CHUNJ ... qAMUQUEW ... 2006-04-22-10.16.55.258001 ......LAST_BIND_TIME CODEPAGE ...------------------------------- --------2006-04-22-10.16.55.258001 1252 ...
DB2RBIND command
Rebinding is the process of recreating a package for an application program that was previously bound.
If the package that has been previously bound becomes invalid or inoperative, you can either drop and bind or rebind the invalid package.
Rebind is useful in cases where a new index was created or statistics have been updated after execution of the RUNSTATScommand.
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.
Listing 16. DB2RBIND command output
C:\SQLLIB\bnd>db2rbind sample /l logfile.txt Rebind done successfully for database 'SAMPLE' C:\SQLLIB\bnd>
Java-embedded application (SQLJ)
Along with host language embedded SQL type applications, there are also embedded Java applications, better known as SQLJ programs. SQLJ is a method for accessing DB2 from a Java application that supports static execution. Again, the benefits of a static execution are reduced resource consumption, improved diagnostics, improved security, and greater repeatability of SQL performance due to static access paths and plans. Everything you need to get from the data is already in the package bound at bind time.
Listing 17 shows an example SQLJ program. It uses the same SQL shown in the previous example above written in embedded C.
Listing 17. DW2.sqlj
//***************************************************************************//// SOURCE FILE NAME: DW2.sqlj//// Sample developerworks embedded SQLJ Program for DB2.// This sample application utilizes the DB2 Sample database// which can be created using the "db2sampl" command.//// You need to run this through the SQLJ Translator then run the// db2sqljcustomize command against the sample db using the .ser file// created from the SQLJ step.// Of course you must also compile the resultant DW2.java code into// bytecode using the javac command.//// This sample application takes one argument on the command line,// a DEPT (department id) from staff table.// eg. java DW2 20//// It then finds the total number of staff registered within that DEPT ID// in the STAFF table under the SAMPLE database.////***************************************************************************import java.sql.*;import java.io.*;import sqlj.runtime.*;import sqlj.runtime.ref.*;//====================================================================// MAIN//====================================================================class DW2{ static { try { Class.forName ("com.ibm.db2.jcc.DB2Driver").newInstance (); } catch (Exception e) { System.out.println ("\n Error loading Driver \n"); System.exit(1); } } public static void main(String argv[]) { try { String url = "jdbc:db2:sample"; Connection con = null; // Connect with implicit userid and pw con = DriverManager.getConnection(url); // Set the default context DefaultContext ctx = new DefaultContext(con); DefaultContext.setDefaultContext(ctx); // Read in the query argument off the command line. int udept = 0; if (argv.length > 0) { udept = Integer.parseInt(argv[0]); System.out.println ("Total personnel was requested for department : " +udept); } else { throw new Exception("Which department?\n"); } short total = 0; // Find the number of folks in the Department specified. #sql { SELECT COUNT(ID) into :total FROM STAFF WHERE DEPT = :udept } ; System.out.println ("Total number of personnel in dept " +udept+ " is " +total+ "."); System.out.println ("Goodbye"); } catch( Exception e ) { System.out.println (e); } }} Commands covered in this section include:
- sqlj:
sqlj is the translator that will take an embedded SQLJ program and create a .ser file used for binding and a .java file that will also be compiled into byte code as typical Java programs are compiled.
- db2sqljcustomize:
This command will take the .ser file from the sqlj step, connect to the database against which the application will be run, and bind four bind files for this application, all with different isolation levels
- db2sqljbind:
This command can be used to rebind this application against other databases; for example, it can be used for moving the application from the test to the production database.
So the first thing to do with an SQLJ program is run it through the translator.
Listing 18. SQLJ command output
(pcirone@bugdbug) /home/pcirone/sqlj $ sqlj DW2.sqlj (pcirone@bugdbug) /home/pcirone/sqlj $ llection PAOLO DW2_SJProfile0.ser < [ibm][db2][jcc][sqlj] [ibm][db2][jcc][sqlj] Begin Customization [ibm][db2][jcc][sqlj] Loading profile: DW2_SJProfile0 [ibm][db2][jcc][sqlj] Customization complete for profile DW2_SJProfile0.ser [ibm][db2][jcc][sqlj] Begin Bind [ibm][db2][jcc][sqlj] Loading profile: DW2_SJProfile0 [ibm][db2][jcc][sqlj] Driver defaults(user may override): BLOCKING ALL VALIDATE BIND STATICREADONLY YES [ibm][db2][jcc][sqlj] Fixed driver options: DATETIME ISO DYNAMICRULES BIND [ibm][db2][jcc][sqlj] Binding package DW201 at isolation level UR [ibm][db2][jcc][sqlj] Binding package DW202 at isolation level CS [ibm][db2][jcc][sqlj] Binding package DW203 at isolation level RS [ibm][db2][jcc][sqlj] Binding package DW204 at isolation level RR [ibm][db2][jcc][sqlj] Bind complete for DW2_SJProfile0 (pcirone@bugdbug) /home/pcirone/sqlj $
The SQLJ step above produced a number of files, namely, DW2.java, DW2.class, DW2_SJProfile0.ser, DW2_SJProfileKeys.class.
After this is done, you can run the DW2_SJProfile0.ser file through the db2sqljcustomize command. This will process the SQLJ profile containing embedded SQL statements. The SQLJ customization process binds four packages to the DB2 server (one for each isolation level). The isolation level, which is set as part of the DataSource or dynamically within the application, will be implemented at execution time by invoking one of these four packages. These packages, by default, have seven character root names followed by single digit (1 to 4) depending upon the isolation level.
This command augments the profile with DB2-specific information that will be used at runtime. It should be run AFTER the SQLJ program has been run through the translator, but BEFORE it is executed for the first time. See the Command Reference for more details on the command itself.
In Listing 18, you see the command run and what the output looks like. You also see the packages created. Here is the entire command entered above:
Listing 19. Entire command entered for Listing 18
db2sqljcustomize -user pcirone -password ******** -url jdbc:db2://bugdbug:22906/sample-collection PAOLO -DW2_SJProfile0.ser
Note: When running the db2sqljcustomize command against a DB2 database on Z/OS, ensure that the collection ID used is in all uppercase. (That is a restriction by the server, not the client.) Against any Linux, UNIX, Windows database on the workstation, case does not matter; what you specify is what is used for the collection ID in the system catalog tables.
In Listing 20, you see a part of the packages created by the db2sqljcustomize utility:
Listing 20. SYSCAT.PACKAGES output -- SYSCAT.PACKAGES5
PKGSCHEMA PKGNAME BOUNDBY DEFINER DEFAULT_SCHEMA VALID UNIQUE_ID--------- ------- ------- ------- -------------- ----- ---------...PAOLO DW201 PCIRONE PCIRONE PCIRONE Y jBCMXGDsPAOLO DW202 PCIRONE PCIRONE PCIRONE Y jBCMXGDsPAOLO DW203 PCIRONE PCIRONE PCIRONE Y jBCMXGDsPAOLO DW204 PCIRONE PCIRONE PCIRONE Y jBCMXGDs...TOTAL_SECT FORMAT ISOLATION BLOCKING---------- ------ --------- --------1 3 UR B1 3 CS B1 3 RS B1 3 RR B
The execution and output of the program:
Listing 21. Execution of SQLJ sample application -- Java DW2 20
(pcirone@bugdbug) /home/pcirone/sqlj $ java DW2 20 Total personnel was requested for department :20 Total number of personnel in dept 20 is 4. Goodbye (pcirone@bugdbug) /home/pcirone/sqlj $ _
Back to top
Packages and SQL routines
SQL Procedural Language (SQL PL) is a subset of SQL that provides procedural constructs in functions and procedures that can be used to implement logic around traditional SQL. It is a high-level language with simple syntax and command program control statements. It is a very powerful tool for application developers. One major advantage is it helps to reduce network traffic when running SQL statements against the server by doing the bulk of the work locally on the server itself.
As routines incorporate SQL, with which package is the SQL executed? Where is the access plan held? The answer is SQL routines have their own package associated with each and every routine (just as you would see in an embedded SQL program scenario).
Let's take the following example: It is an SQL procedure that will update the EMPLOYEE table in the SAMPLE database. The basic logic is used to determine if an adjustment is needed in an employee's salary according to their performance on a scale of 100. If the employee is a top performer with a rating of 90% or better, they qualify for a year over year raise of 15%.
Listing 22. Sample CREATE statement
CREATE PROCEDURE PAOLO.RAISE (IN empnum CHAR(6), IN perfrating SMALLINT) LANGUAGE SQL BEGIN IF perfrating >= 90 THEN UPDATE employee SET salary = salary * 1.15 WHERE empno = empnum; END IF;END
Once you have created this procedure, various system catalog tables will be updated to hold the information for the said procedure.
One important table is the SYSCAT.PROCEDURES table. If you query this table after creating the PAOLO.RAISE procedure, you will see the following entry for the procedure:
Listing 23. SYSCAT.PROCEDURES output
PROCSCHEMA PROCNAME SPECIFICNAME PROCEDURE_ID CREATE_TIME------------- ----------- --------------------- ------------ ----------------------------PAOLO RAISE SQL060423221235152 65752 2006-04-23-22.12.35.221806
Notice the SPECIFICNAME column entry. This will come in handy when you look at SYSCAT.ROUTINEDEP. This table holds routine dependencies, including a column called BNAME. The value for BNAME for the procedure will be important when you look at the next table. Notice that we matched the SPECIFICNAME from the first table with the ROUTINENAME of the second table (SYSCAT.ROUTINEDEP).
Note: SYSCAT.ROUTINEDEP supercedes SYSCAT.FUNCDEP in V810 and beyond. However, FUNCDEP will still remain as it was in V710.
Listing 24. SYSCAT.ROUTINEDEP output
PROCSCHEMA ROUTINENAME BTYPE BSCHEMA BNAME------------- --------------------- ----- ------- ------------PAOLO SQL060423221235152 K PAOLO P2123518
The next step is to go to the SYSCAT.PACKAGES table and use the info on hand to get the final piece of the puzzle.
Listing 25. SYSCAT.PACKAGES output
PKGSCHEMA PKGNAME BOUNDBY DEFINER DEFAULT_SCHEMA VALID UNIQUE_ID------------ ---------- --------- ------- -------------- ----- ---------PAOLO P2123518 PCIRONE PCIRONE PCIRONE Y XAjMWXEWTOTAL_SECT FORMAT ISOLATION BLOCKING INSERT_BUF---------- ------ --------- -------- ----------1 0 CS U N
So take BNAME, go to the SYSCAT.PACKAGES table, and see that it corresponds to the value in the PKGNAME column. BNAME in the previous table is the actual package name for this stored procedure. You will see other information on this package, as well, here. Things like the UNIQUE_ID (consistency token), isolation level for the package and SQL, and so on.
Using this information, it is simple to find the package from the function name and find the function name from the package name. This should help in various troubleshooting situations when trying to pull all the pieces together with routines and packages.
An additional procedure is made available to customers if they wish to rebind an SQL procedure's package without having to know the package name itself. REBIND_ROUTINE_PACKAGE is a stored procedure that will take three parameters, one of which is the procedure name you wish to rebind. Check the Information Center for more details on this method to rebind your procedures.
Back to top
Authorizations and binding of routines
There are several roles related to routines and packages. Roles can be defined as package owner, routine definer, and routine/package invoker.
Package owner
The package owner participates in the implementation of the routine through the execution of the precompile and binding of the package to the database.
It should be noted that the package owner can override the ownership through the precompile/BIND option and set it to an alternate user.
The relationship between bind file and package is one to many. Therefore, a routine library or executable can be comprised of multiple packages, resulting in multiple package owners (one for each package).
Routine definer
The routine definer is someone who issues the CREATE statement to register a routine. This is in the case of SQL routines (stored procedures or UDFs), which require a CREATE statement to create/bind and register the underlying routine package, as shown above.
This role is usually associated with the DBA or package owner. At the time of routine invocation (when the package is being loaded), authentication is performed using the routine definer ID and not the routine invoker ID.
In order to successfully execute the routine, the routine definer must have one of either:
- EXECUTE privilege on the package or packages of the routine and EXECUTE privilege on the routine itself
- SYSADM or DBADM authority
If the routine definer and the routine package owner are the same user, then the routine definer will have the required EXECUTE privileges on the packages. If the definer is not the package owner, the definer must be explicitly granted EXECUTE privilege on the packages by the package owner or any user with SYSADM or DBADM authority.
For SQL routines, the routine definer is also implicitly the package owner. Therefore, the definer will have EXECUTE WITH GRANToption on both the routine and on the routine package upon execution of the CREATE statement for the routine.
Routine invoker
The routine invoker is someone who invokes the routine.
Routines can be invoked from a command window or from within an embedded SQL application.
For methods and UDFs, they will not be invoked on their own but through another SQL statement in which they are embedded, while procedures will be invoked using the CALL statement.
In the case of dynamic SQL in an application, the invoker is the runtime authorization ID of the immediately higher-level routine or application containing the routine invocation. (But this ID can also depend on the DYNAMICRULES option, to which a higher level routine or application was bound).
For static SQL, the invoker is the value of the OWNER precompile/BIND option of the package that contains the reference to the routine.
In order to successfully invoke the routine, users will require execute privilege on the routine (granted to user with EXECUTE WITH GRANT option privilege on the routine).
If the application containing dynamic SQL was bound with DYNAMICRULES BIND, then its runtime authorization ID will be its package owner and not the person invoking the package.
General syntax for GRANT statements:
db2 GRANT EXECUTE ON FUNCTION <function name> to <user>db2 GRANT EXECUTE ON PROCEDURE <procedure name> to <user>db2 GRANT EXECUTE ON METHOD <method name> to <user>db2 GRANT EXECUTE ON PACKAGE <package-schema>.<package-id> to <user>
Note: When the BIND statement is issued with a GRANT clause, a binder package that grants the permissions is required. This binder package uses a NULLID collection to grant privileges to each package. This means that even when system packages like CLI packages are bound using the COLLECTION bind option to OS/390, there will always be a package that has a NULLID collection identifier when GRANT is used.
Back to top
Package versioning and other information
Versioning
DB2 provides a versioning feature where one can create multiple versions of an application using the VERSION option in thePRECOMPILE command.
This option allows multiple versions of the same package name (package and creator name) to coexist.
As an example, one can first precompile using the VERSION option, as seen in Listing 26:
Listing 26. Sample PREP statement with version option 1.0
DB2 PREP TEST.SQC VERSION V1.0
This first 1.0 Version of the program may now be run. When you build the new version of TEST, you can change the version to 1.1 using the following precompile command:
Listing 27. Sample PREP statement with version option 1.1
DB2 PREP TEST.SQC VERSION V1.1
This allows the new version of the application to exist in conjunction with first initial version of the application.
Precompile timestamp/Consistency token/Unique ID
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. The timestamp is also known as the consistency token or unique ID. It is implemented as an eight-character value.
When an application is precompiled with binding enabled, the package and modified source file are generated with timestamps that match. If you use the PRECOMPILE VERSION option, and multiple versions of a package exist, each version will have with it an associated timestamp. When the application is run, the package name, creator, and timestamp are sent to the database manager, which checks for a package whose name, creator, and timestamp match that sent by the application. If such a match does not exist, one of the two following SQL error codes is returned to the application:
- SQL0818N (timestamp conflict): This error is returned if a single package is found that matches the name and creator (but not the consistency token), and the package has a version of "" (an empty string)
- SQL0805N (package not found): This error is returned in all other situations
The consistency token is stored as an eight-character value representing the timestamp for that package. Here is how you can decode the consistency token back to the timestamp value:
Figure 3. Translating consistency token to timestamp value
This information can be retrieved using the db2bfd -b command against the bind (*.bnd) file and can also be found in the SYSCAT.PACKAGES view.
Catalog views
DB2 provides several views in each database to display information regarding various packages that were bound to that database:
Table 3. SYSCAT.PACKAGES catalog view
This topic can be found in SQL Reference, Volume 1.
SYSCAT.PACKAGEDEP catalog view contains a row for each dependency that packages have on indexes, tables, views, triggers, functions, aliases, types, and hierarchies.
Table 4. SYSCAT.PACKAGEDEP catalog view
This topic can be found in SQL Reference, Volume 1.
SYSCAT.PACKAGEAUTH catalog view contains a row for every privilege held on a package.
Table 5. SYSCAT.PACKAGEAUTH catalog view
This topic can be found in SQL Reference, Volume 1.
In addition to these catalog tables, DB2 has a LIST PACKAGES command to display all packages associated with the current database that the user is connected to.
Listing 28. LIST PACKAGES statement
>>-LIST--+-PACKAGES-+--+------------------------------+---------> '-TABLES---' | .-USER----------------. | '-FOR--+-ALL-----------------+-' +-SCHEMA--schema-name-+ '-SYSTEM--------------'>--+-------------+--------------------------------------------->< '-SHOW DETAIL-'
db2bfd - Bind file description tool command
The db2bfd command can be used to display the content of the bind file. This utility is particularly useful in examining the bind file and to help determine problems related to packages.
One of the most useful options of the db2bfd utility is the -b option. It displays the bind file header information.
Listing 29. db2bfd -b sample.bnd statement
D:\Sample\test>db2bfd -b sample.bndsample.bnd: Header ContentsHeader Fields:Field Value----- -----releaseNum 0x800Endian 0x4cnumHvars 2maxSect 1numStmt 6optInternalCnt 4optCount 9Name Value------------------ -----Isolation Level Cursor StabilityCreator "CHUNJ "App Name "SAMPLE "Timestamp "qAMUQUEW:2006/04/20 16:20:12:42"Cnulreqd YesSql Error No packageValidate BindDate Default/localTime Default/local*** All other options are using default settings as specified by the server ***D:\Sample\test>
As shown above, db2bfd with the -b option provides essential information like timestamp, app name, and creator.
Listing 30. db2bfd command syntax
.-,-------. V |>>-db2bfd----+- -h-+-+--filespec------------------------------->< +- -b-+ +- -s-+ '- -v-'
Command parameters:
- -h: Displays help information. When this option is specified, all other options are ignored, and only the help information is displayed.
- -b: Displays the bind file header
- -s: Displays the SQL statements
- -v: Displays the host variable declarations
- filespec: Name of the bind file whose contents are to be displayed.
This topic can be found in SQL Reference, Volume 1.
CURRENT PACKAGE PATH special register for package schemas
Packages can be logically grouped under package schemas. This means that packages with the same name can exist under multiple package schemas in the same database. (For example, dev.sample, test.sample, and prod.sample.)
When a package with multiple schemas exists, the database manager must determine in which schema to look for a specified package. This is where the CURRENT PACKAGE PATH special register comes into play. DB2 provides the CURRENT PACKAGE PATHspecial register, so that you can set the register to indicate the order of schema search for invoked packages.
This special register can be set in the db2cli.ini file or by using the SQLSetConnectAttr API.
In embedded SQL programs, you can set this register by including a SET CURRENT PACKAGE PATH statement in the source code.
You can also issue the SET CURRENT PACKAGE PATH statement at the beginning of the application to specify a list of schema names.
In DB2 OS/390 and z/OS, another similar special register called CURRENT PACKAGESET is available. It can be set explicitly to a single schema name using the SET CURRENT PACKAGESET statement. This is not available in any other platform other than OS/390 and z/OS.
Back to top
Common problems and considerations involving packages
When diagnosing bind/package-related issues, always attempt to simplify the scenario involved.
For diagnosing problems binding system packages, simplify the scenario and software layers involved by binding from the command line processor.
For package-related issues, information, again, is held in the SYSCAT.PACKAGES table. A db2 trace (db2trc) on the server side and db2bfd (with -b option) on the bind file are the most useful tools to investigate the cause of the issue.
In DB2 V8, a utility called db2pd was introduced. Running db2pd with the -static option will provide further information about the execution of static SQL and packages.
The following are some of the more common problematic scenarios you may encounter with packages:
Case 1: SQL0104N and SQL0440N messages on binding CLI packages
Prior to Fix Pack 10, CLI packages bound clean only to Linux, Unix and Windows. With other servers, you get errors that are treated as warnings -- you commonly see an SQL0104N and SQL0440N. These sqlcodes are expected.
Against VM and VSE, not create WITH HOLD cursor packages. The following is the expected sample output from a bind against non-Linux, UNIX, and Windows servers:
Listing 31. Example bind output:
LINE MESSAGES FOR db2clpcs.bnd------ --------------------------------------------------------------------197 SQL0440N No authorized routine named "PROCEDURE" of type"GET_ROUTINE_SAR" having compatible arguments was found.SQLSTATE=58020418 SQL0440N No authorized routine named "PROCEDURE" of type"PUT_ROUTINE_SAR" having compatible arguments was found.SQLSTATE=58020421 SQL0440N No authorized routine named "PROCEDURE" of type"PUT_ROUTINE_SAR" having compatible arguments was found.SQLSTATE=580201135 SQL0104N An unexpected token "P" was found following "".Expected tokens may include: "<END-OF-STATEMENT>".SQLSTATE=42601....more errors...LINE MESSAGES FOR ddcsmvs.lst--------------------------------------------------------------------------SQL0091N Binding was ended with "0" errors and "17" warnings.
The procedure listed in the messages above does not and cannot exist on the host. Thus, the resulting messages are expected. These listed procedures are for DB2 on Linux, UNIX, and Windows only.
A user/binder should specify the BLOCKING ALL and SQLERROR CONTINUE options.
This cosmetic issue has since been addressed through APAR JR20949 (V8, Fix Pack 10).
You can find more detail on the DB2 for Linux, UNIX, and Windows support page.
Case 2: SQL0805N for NULLID.SYSxyN03 (where "x", "y" and "N" may be of varying values).
As previously mentioned in the section "DB2 Call Level Interface (CLI) packages," the default number of CLI packages is three.
This type of error normally occurs on CLI packages. (You know this is a CLI package, as it has NULLID collection and SYSxyNzz naming convention.) When the number of statement handles exceed the maximum value available per CLI package section that were originally bound, you run into this issue.
The number of statement handles available to a CLI application depends on the number of large packages the application has defined and is limited by the overall system resources (Refer to previous section "Package sections and their impact on CLI").
By default, three small and three large packages are created. Each small package allows a maximum of 64 statement handles per connection, and each large package allows a maximum of 384 statements per connections, giving a total of 1,344 statement handles.
Should an application require more than 1,344 handles, first examine the application at hand to see whether it has closing statement handles that are not needed. Basically, is the application cleaning up after itself as efficiently as it can be? It is important to make efficient use of limited resources (memory) and to prevent application memory leaks.
The second thing to review is the necessity of more packages. Perhaps the application is indeed cleaning up after itself, but it is a heavily used application in a production environment. These additional packages take up space and resources in the database, but if they are required, they are required.
In the case above, you are looking for NULLID.SYSxyN03. Since, by default, DB2 creates only three sections, packages up to NULLID.SYSxyN02 would have only been created (00, 01, 02).
If it is deemed that the application does require a larger number of handles that exceeds the number made available by three sections, then you can increase the number of packages using one of following methods:
- Set the CLIPKG CLI keyword in db2cli.ini to a value of 30
- Bind with
CLIPKGoption
Listing 32. Example bind output:
db2 "bind @db2cli.lst blocking all sqlerror continue grant public CLIPKG X"
Where "X" is any number of large packages that can be created.
If the value is not an integer between three and 30, the default value will be used without error or warning.
Consider the following information if your application requires DB2 packages with a large number of dynamic sections:
Creating DB2 packages with a large number of dynamic sections may exhaust certain server resources. Many or all of these points may apply:
- You may need to increase the database parameter PCKCACHE_SZ to allow the larger packages to be created.
- The creation of more dynamic sections will slow down the initial creation of the DB2 package.
- Using DB2 packages with a large number of dynamic sections may impact application performance.
- If a small number of sections are in use at one time, there will be no impact on the application.
- If a large number of sections are in use at one time, the performance of the application may decrease because the database will expend resources to check all open sections for locks.
- As the number of open sections increases, so does the likelihood that a deadlock situation may occur.
- If your application is mostly executing select statements, it is best to operate in the default mode of automatically committing the database.
- Dynamic sections are not freed in the DB2 package until the database is committed, even if the statements are closed in the application.
- In this mode, the database will commit every time an SQL statement is executed and free all of the sections that were opened.
- If you need to operate in a manual commit mode, then it is advisable to commit to the database as often as possible to ensure that all server resources are freed in a timely manner.
- The DB2 server has a limit on dynamic sections.
Case 3: SQL0805N error invoking embedded application
If a package owner failed to bind the necessary bind file resulting from db2 precompiler processing (PREP), SQL0805N will result.
Using the previously introduced embedded C application sample, the following error will be returned upon execution if the application developer failed to bind the resulting sample.bnd file.
Listing 33. SQL805N error resulting from the sample application
$sample 20 Total personnel was requested for department : 20. SQL0805N Package "CHUNJ.SAMPLE 0X71414D5551554557" was not found. SQLSTATE=51002 The SELECT failed. Expected code = 0 The SQLCA Information SQLCAID: SQLCA SQLCABC: 136 SQLCODE: -805 SQLERRML: 31 SQLERRMC: CHUNJ.SAMPLE 0X71414D5551554557 SQLRACFIm SQLERRP: SQLRACFIm SQLERRD[0]: -2146303891 SQLERRD[1]: 0 SQLERRD[2]: 0 SQLERRD[3]: 0 SQLERRD[4]: 0 SQLERRD[5]: 0 SQLWARN: 51002 SQLSTATE: 51002
If a package owner issued the PREP command without the bindfile option, the bind file is automatically bound to the server.
Note: If you are receiving an -805 error on CLI packages, recall that by default, you only bind three small and three large packages (in other words, NULLID.SYSSH200, NULLID.SYSSH201, NULLID.SYSSH202, NULLID.SYSLH200, NULLID.SYSLH201, and NULLID.SYSLH202).
Case 4: SQL0818N error
The SQL0818N sqlcode indicates a timestamp conflict has occurred when invoking the package. The timestamp value is stored in both the package and in the system view table (SYSCAT.PACKAGES as UNIQUE_ID) as well as the object code calling on that package. If these do not match, you will receive this error upon execution of that code.
In order to investigate the SQL0818N error, note the package which resulted in the error.
The package name does not necessarily translate to a bind file name and thus you will need some idea of which packages are part of which bind file. Also, multiple packages may be associated with a single bind file.
This case study uses the same SAMPLE embedded application that you saw earlier.
Listing 34. SQL0818N error
$sample 20 Total personnel was requested for department : 20. SQL0818N A timestamp conflict occurred. SQLSTATE=51003 The SELECT failed. Expected code = 0 The SQLCA Information SQLCAID: SQLCA SQLCABC: 136 SQLCODE: -818 SQLERRML: 0 SQLERRMC: SQLRALDP SQLERRP: SQLRALDP SQLERRD[0]: -2146303891 SQLERRD[1]: 0 SQLERRD[2]: 0 SQLERRD[3]: 0 SQLERRD[4]: 0 SQLERRD[5]: 0 SQLWARN: 51003 SQLSTATE: 51003
Execution of the SAMPLE application resulted in the SQL0818N TIMESTAMP CONFLICT error.
To investigate the SQL0818N error, follow the basic information to get started:
- Information in the SYSCAT.PACKAGES catalog view:
You can obtain this information using following query:
SELECT PKGSCHEMA, PKGNAME, UNQUE_ID FROM SYSCAT.PACKAGES WHERE PKGNAME='SAMPLE'
Listing 35. SYSCAT.Packages outputPKGSCHEMA PKGNAME UNIQUE_ID --------- ------- --------- CHUNJ SAMPLE nAbMLWEW 1 record(s) selected.
- The
db2bfd -boutput of the bind file involved to verify the UNIQUE_ID associated with bind file at hand:
Listing 36. db2bfd -b outputHeader Fields: Field Value ----- ----- releaseNum 0x800 Endian 0x42 numHvars 2 maxSect 1 numStmt 6 optInternalCnt 4 optCount 9 Name Value ---- ----- Isolation Level Cursor Stability Creator "CHUNJ " App Name "SAMPLE " Timestamp "nAbMLWEW:2006/04/22 11:12:27:39" Cnulreqd Yes Sql Error No package Validate Bind Date Default/local Time Default/local
You can further diagnose the issue by utilizing a utility likeHEXDUMPto dissect the executable portion of the application to determine the UNIQUE_ID (TIMESTAMP/CONSISTENCY TOKEN) of the package it is referencing.
Figure 4. "hexdump -C sample" command output
Given these pieces of information alone, you know that there is a TIMESTAMP conflict, as the error message indicated.
The executable sample is referencing a timestamp ZAx4KWEW, while the bind file and SYSCAT.PACKAGES contains timestamp nAbMLWEW.
This evidence points to a bind file/executable mismatch.
The bind file does not appear to match the sample executable. The UNIQUE_ID present in SYSCAT.PACKAGES also does not match the value in the application.
- A DB2 trace (
db2trc) of the application execution that results in the SQL0818N error:Even without obtaining the HEXDUMP output of the executable binary, you can determine the nature of the error by taking a db2 trace.
Recommended DB2 trace instructions:
Listing 37. Recommended DB2 trace instructionsdbtrc on -f trace.tmp<Execute the application scenario that results in SQL0818N error.In this case "sample 20">db2trc offdb2trc flw trace.dmp trace.flwdb2trc fmt trace.dmp trace.fmt
The server-side DB2 trace is essential (where the database resides).
Figure 5 demonstrates a local execution of the embedded application.
Reviewing the trace showed the following:
A communication buffer to the server sent by the client requesting the execution of package SAMPLE with TIMESTAMP (UNIQUE ID/CONSISTENCY TOKEN) ZAx4KWEW.
This will only be shown on the client-side DB2 trace. This demonstration executes the embedded application locally to the database, so db2 trace will contain client-side information as well.
Figure 5. DB2 trace of client request
Communication buffer received by server regarding execution of SAMPLE package with TIMESTAMP (UNQIUE ID/CONSISTENCY TOKEN) ZAx4KWEW:
Figure 6. DB2 trace of server receiving the request
The above received communication is processed by the server to determine the request type, package name, and its consistency token.
Figure 7. DB2 trace of server processing the request
But this results in SQL0818N, as seen further down the trace. Trace also shows the consistency token involved.
Figure 8. DB2 trace showing SQL0818N error
Therefore, the trace captured the SQL0818N error, and you were looking for the sample package with a consistency token (or UNIQUE_ID/TIMESTAMP) of ZAx4KWEW.
To resolve this issue, a package with consistency token ZAx4JWEW needs to be bound. If the appropriate bind file cannot be located to bind the proper package, the application may need to be rebuilt using the steps specified above (precompile and bind, in other words).
Case 5: DATE/TIME format being returned from the Command Line Processor (CLP) is not in the desired format
To change the way the CLP displays data (when querying databases using SQL statements through the CLP), rebind the CLP bind files against the database being queried. For example, to display the date and time in ISO format, following these steps:
- Create a text file containing the names of the CLP bind files. This file is used as the list file for binding multiple files with one
BINDcommand. In this example, the file is named clp.lst, and its contents are:
Listing 38. Content of clp.ldb2clpcs.bnd + db2clprr.bnd + db2clpur.bnd + db2clprs.bnd + db2clpns.bnd
- Connect to the database.
- Issue the following command:
Listing 39. ISO bind commanddb2 bind @clp.lst collection nullid datetime iso
Back to top
Acknowledgements
Special thanks to Lorysa Bond, IBM DB2 RPD Advanced Support, for reviewing this article and providing valuable input.
Back to top
Further information
During application development, you may hit one or more of the problems described above involving packages. Our intention is to help you understand the concept of the package a little more thoroughly and to solve common problems. Please refer to theproduct manuals for any other questions you may have on application development and packages.
原文链接：http://www.ibm.com/developerworks/data/library/techarticle/dm-0606chun/
- DB2 packages: Concepts, examples, and common problems
- apache and php common tips and problems
- Common Problems (and Their Solutions) for java
- Lesson: Common Problems (and Their Solutions)
- Solve common problems with DB2 UDB Java stored procedures
- Geometry Shader Concepts & Examples
- Geometry Shader Concepts & Examples
- Geometry Shader Concepts & Examples
- Geometry Shader Concepts & Examples
- Geometry Shader Concepts & Examples
- Geometry Shader Concepts & Examples
- Geometry Shader Concepts & Examples
- Geometry Shader Concepts & Examples
- C++ Gotchas: Avoiding Common Problems in Coding and Design
- Answers to some common problems and questions about Xen
- Some common problems in RAD and their solutions/work arounds
- common concepts about Django
- Fix common PC problems
- JS 对 DropDownList 操作
- spring的作用
- 自定义UNIFORM的右键菜单
- linux下fork()函数用法
- WebService大讲堂之Axis2(8)：异步调用WebService
- DB2 packages: Concepts, examples, and common problems
- s3c2440串口通讯的相关配置
- 解决 event事件 Firefox "event is not defined" 而IE正常问题
- WebService大讲堂之Axis2(9)：编写Axis2模块（Module）
- JAVA验证码
- C#中Process类的使用，打造自己的任务管理器
- [.net] Swfupload配置示例 sufupload for .net
- 实例方法和类方法
- Session知识点介绍
