Does DB2 provides access to the administrative APIs through some Java or SQL functionality? Recent versions of DB2 UDB for Linux™, UNIX®, and Windows® have added several built-in functions to make, for example, snapshot functionality available through user-defined functions (UDFs). However, not all APIs can be accessed that way yet. Some APIs can only be invoked from C code, if necessary, through the use of JNI or other coupling facilities. In this article, you'll see an easy and generic way to make the APIs you need available to all your applications.
The mechanism is based on stored procedures that are implemented in C. Each API call is encapsulated in a dedicated procedure. Such a procedure takes the necessary parameters -- like the name of the table that should be handled or the file from which data shall be loaded -- as input parameters, prepares the required data structures, and calls the API.
The following sections are organized as follows:
- Description of the
RUNSTATScommand procedure - Explanation of how to implement a LOAD procedure
- Examination of building and installing the procedures
The first stored procedure simply wraps the db2Runstats API to offer a way to call the same functionality as the DB2 command RUNSTATS. (For more information on the db2Runstats API and the RUNSTATS command, see Resources.) The procedure takes the fully-qualified name of a table as input parameter and sets up the necessary data structures that are passed to the API function. Please note that the requirement to supply a table name qualified by the name of the schema where that table was created is inheritted from the db2Runstats API and, thus, also applies to the parameter of the procedure.
The statistics are collected for all columns (with distributions) and all indexes (with extended statistics). Read access is allowed while the operation is performed. The functionality implemented in the stored procedure is equivalent to the following command:
RUNSTATS ON TABLE <table-name>
ON ALL COLUMNS WITH DISTRIBUTION AND
DETAILED INDEXES ALL
ALLOW READ ACCESS
|
The procedure does not perform any action if the given table name is NULL. If the db2Runstats API encounters any error (for example, the specified table does not exist or the user who called the procedure does not have the necessary privileges) the error is returned to the caller using SQLSTATE "38RS1". You can see the details of the procedure in Listing 1 below. Note that the call to the API where the actual work is done is set in italics. As you can see, the invocation itself is actually the smallest part of the implementation. Before that, though, comes the (rather long) preparation of the data structures.
Listing 1. Stored procedure code to invoke db2Runstats
#include <sql.h>
#include <sqludf.h>
#include <db2ApiDf.h>
int SQL_API_FN runstats_proc(
SQLUDF_VARCHAR *tableName,
SQLUDF_NULLIND *tableName_ind,
SQLUDF_TRAIL_ARGS)
{
SQL_API_RC rc = SQL_RC_OK;
db2RunstatsData data;
struct sqlca sqlca;
if (SQLUDF_NULL(tableName_ind)) {
goto cleanup;
}
/* initialize data structures */
data.iSamplingOption = 0;
data.piTablename = (unsigned char *)tableName;
data.piColumnList = NULL;
data.piColumnDistributionList = NULL;
data.piColumnGroupList = NULL;
data.piIndexList = NULL;
data.iRunstatsFlags = DB2RUNSTATS_ALL_COLUMNS | DB2RUNSTATS_DISTRIBUTION |
DB2RUNSTATS_ALL_INDEXES | DB2RUNSTATS_EXT_INDEX |
DB2RUNSTATS_ALLOW_READ;
data.iNumColumns = 0;
data.iNumColdist = 0;
data.iNumColGroups = 0;
data.iNumIndexes = 0;
data.iParallelismOption = 0;
data.iTableDefaultFreqValues = -1; /* use default */
data.iTableDefaultQuantiles = -1; /* use default */
data.iUtilImpactPriority = 0;
data.iSamplingRepeatable = 0; /* unused */
memset(&sqlca, 0x00, sizeof sqlca);
rc = db2Runstats(db2Version820, &data, &sqlca);
if (rc != SQL_RC_OK || SQLCODE != SQL_RC_OK) {
memcpy(SQLUDF_STATE, "38RS1", SQLUDF_SQLSTATE_LEN);
sprintf(SQLUDF_MSGTX, "Error %d returned by db2Runstats.",
(int)(rc == SQL_RC_OK ? SQLCODE : rc));
goto cleanup;
}
cleanup:
return SQLZ_DISCONNECT_PROC;
}
|
In order to use the procedure, it must be registered in the database with the following SQL statement.
Note: We use the character '@' as statement terminator throughout the article; it is not part of the statement itself.
Listing 2. SQL statement
CREATE PROCEDURE runstats(IN tableName VARCHAR(256))
SPECIFIC runstats
DYNAMIC RESULT SETS 0
MODIFIES SQL DATA
NOT DETERMINISTIC
CALLED ON NULL INPUT
LANGUAGE C
EXTERNAL NAME 'db2_api!runstats_proc'
FENCED THREADSAFE
NO EXTERNAL ACTION
PARAMETER STYLE SQL@
|
Increasing control over the internal operation
Of course, it is possible to allow the caller of the stored procedure to further control the execution. For example, you can influence which indexes or columns are affected by the processing. To that end, you should change the CREATE PROCEDURE statement and the C code to allow for additional parameters. Depending on the values of those parameters, you could now set the options for the data structures any way you like (for example, allowing write access during the operation).
After you compile the source code, build the shared library, and register the procedure in the database, you can verify the proper functioning of your work so far. Listing 3 demonstrates a few examples. A table is created and some data is inserted before the procedure is called. The results of the call can be verified in the catalog tables in the SYSSTAT schema. The following scenario shows collected statistics on a table of the DB2 catalog itself, the table SYSIBM.SYSTABLES.
Listing 3. Testing the RUNSTATS procedure
$ db2 -td@
db2 => CREATE TABLE stolze.t ( a INT NOT NULL, b VARCHAR(100),
db2 (cont.) => PRIMARY KEY(a) )@
db2 => INSERT INTO stolze.t VALUES (1, 'text 1'),
db2 (cont.) => (3, 'another text'), (5, 'sample text')@
db2 => SELECT colname, colcard, avgcollen FROM sysstat.columns
db2 (cont.) => WHERE ( tabschema, tabname ) = ( 'STOLZE', 'T' )@
COLNAME COLCARD AVGCOLLEN
---------- -------------------- -----------
A -1 -1
B -1 -1
2 record(s) selected.
db2 => CALL runstats('stolze.t')@
Return Status = 0
db2 => SELECT colname, colcard, avgcollen FROM sysstat.columns
db2 (cont.) => WHERE ( tabschema, tabname ) = ( 'STOLZE', 'T' )@
COLNAME COLCARD AVGCOLLEN
---------- -------------------- -----------
A 3 4
B 3 15
2 record(s) selected.
db2 => SELECT colname, colcard, avgcollen FROM sysstat.columns
db2 (cont.) => WHERE ( tabschema, tabname ) = ( 'SYSIBM', 'SYSTABLES' )
db2 (cont.) => FETCH FIRST 10 ROWS ONLY@
COLNAME COLCARD AVGCOLLEN
------------------ -------------------- -----------
ACCESS_MODE -1 -1
ACTIVE_BLOCKS -1 -1
APPEND_MODE -1 -1
AST_DESC -1 -1
BASE_NAME -1 -1
BASE_SCHEMA -1 -1
CARD -1 -1
CHECKCOUNT -1 -1
CHECK_DESC -1 -1
CHILDREN -1 -1
10 record(s) selected.
db2 => CALL runstats('sysibm.systables')@
db2 => SELECT colname, colcard, avgcollen FROM sysstat.columns
db2 (cont.) => WHERE ( tabschema, tabname ) = ( 'SYSIBM', 'SYSTABLES' )
db2 (cont.) => FETCH FIRST 10 ROWS ONLY@
COLNAME COLCARD AVGCOLLEN
------------------ -------------------- -----------
ACCESS_MODE 1 1
ACTIVE_BLOCKS 2 8
APPEND_MODE 1 1
AST_DESC -1 -1
BASE_NAME 1 5
BASE_SCHEMA 1 5
CARD 2 8
CHECKCOUNT 1 2
CHECK_DESC -1 -1
CHILDREN 1 3
10 record(s) selected.
db2 => CALL runstats('unknown.table')@
SQL0443N Routine "STOLZE.RUNSTATS" (specific name "RUNSTATS") has returned an
error SQLSTATE with diagnostic text "Error -2306 returned by db2Runstats.".
SQLSTATE=38RS1
db2 => ? sql2306@
SQL2306N The table or index "<name>" does not exist.
|
As you can see from the above examples, the procedure is working quite well and causes the statistics for the given tables to be updated. Additionally, the last test demonstrated that errors encountered during the processing are properly returned to the caller, and you, from the error message, can derive more information of the underlying problem. (In our case, we specified the name of a non-existing table.)
LOAD is a very fast and direct way to get data from a file residing in the file system into a table in your database. LOAD does not use SQL INSERT statements, as does IMPORT. Instead, it writes the data directly to the data pages in the tablespace. Additionally, less logging occurs to further improve performance of the load process. (For more information on the LOAD command, see Resources.)
Due to the non-SQL nature of the command, it is not directly available in a unit of work (transaction) of the database system. Nevertheless, it is sometimes desirable to start a load process from a client application that only talks to the database server using SQL. The implementation of the stored procedure described in this section addresses those needs because CALL is a SQL statement of its own.
The procedure shown in Listing 4 takes the necessary input parameters -- the name and type of the file and the fully qualified name of the table into which the data from the file is to be loaded. It sets up the data structures required by the function db2Load and calls that function. The function call is set again in italics. Again you will notice that the preparation steps are more complex than the call itself. This is due to the many parameters that LOAD supports, and all of them need to be initialized.
Listing 4. Stored procedure code to invoke db2Load
#include <sql.h>
#include <sqludf.h>
#include <db2ApiDf.h>
int SQL_API_FN load_proc(
SQLUDF_VARCHAR *fileName,
SQLUDF_VARCHAR *fileType,
SQLUDF_VARCHAR *tableName,
SQLUDF_NULLIND *fileName_ind,
SQLUDF_NULLIND *fileType_ind,
SQLUDF_NULLIND *tableName_ind,
SQLUDF_TRAIL_ARGS)
{
SQL_API_RC rc = SQL_RC_OK;
db2LoadStruct data;
struct sqlu_media_list fileSources;
struct sqlu_location_entry fileLocation;
struct sqlca sqlca;
data.piActionString = NULL;
if (SQLUDF_NULL(fileName_ind) || SQLUDF_NULL(fileType_ind) ||
SQLUDF_NULL(tableName_ind)) {
memcpy(SQLUDF_STATE, "38LD1", SQLUDF_SQLSTATE_LEN);
memcpy(SQLUDF_MSGTX, "No file, file type, or table name specified.",
SQLUDF_MSGTEXT_LEN);
goto cleanup;
}
/*
* initialize data structures
*/
/* setup sources */
data.piSourceList = &fileSources;
fileSources.media_type = SQLU_SERVER_LOCATION;
fileSources.sessions = 1;
fileSources.target.location = &fileLocation;
if (strlen(fileName) > SQLU_MEDIA_LOCATION_LEN) {
memcpy(SQLUDF_STATE, "38LD2", SQLUDF_SQLSTATE_LEN);
memcpy(SQLUDF_MSGTX, "The file name is too long.",
SQLUDF_MSGTEXT_LEN);
goto cleanup;
}
fileLocation.reserve_len = strlen(fileName);
memcpy(fileLocation.location_entry, fileName,
fileLocation.reserve_len);
/* no lob support */
data.piLobPathList = NULL;
/* load all columns */
data.piDataDescriptor = NULL;
/* action: INSERT */
{
struct sqlchar *action = (struct sqlchar *)malloc(
sizeof(struct sqlchar) + 12 + strlen(tableName));
if (!action) {
memcpy(SQLUDF_STATE, "38LD3", SQLUDF_SQLSTATE_LEN);
memcpy(SQLUDF_MSGTX, "Memory allocation failed.",
SQLUDF_MSGTEXT_LEN);
goto cleanup;
}
action->length = sprintf(action->data,
"INSERT INTO %s", tableName);
data.piActionString = action;
}
/* file type is provided by the caller */
data.piFileType = fileType;
/* MODIFIED BY clause not supported */
data.piFileTypeMod = NULL;
/* all messages are discarded */
#if defined(SQLWINT)
data.piLocalMsgFileName = "NUL";
#else /* SQLWINT */
data.piLocalMsgFileName = "/dev/null";
#endif /* SQLWINT */
/* system defaults are used for the remaining parameters */
data.piTempFilesPath = NULL;
data.piVendorSortWorkPaths = NULL;
data.piCopyTargetList = NULL; /* no copies are created */
data.piNullIndicators = NULL; /* no null indicators for ASC files */
data.piLoadInfoIn = NULL;
data.poLoadInfoOut = NULL;
data.piPartLoadInfoIn = NULL;
data.poPartLoadInfoOut = NULL;
/* start the LOAD process */
data.iCallerAction = SQLU_INITIAL;
/* call the API */
rc = db2Load(db2Version820, &data, &sqlca);
if (rc != SQL_RC_OK || SQLCODE != SQL_RC_OK) {
memcpy(SQLUDF_STATE, "38XXX", SQLUDF_SQLSTATE_LEN);
sprintf(SQLUDF_MSGTX, "Error %d returned by db2Runstats.",
(int)(rc == SQL_RC_OK ? SQLCODE : rc));
goto cleanup;
}
cleanup:
if (data.piActionString != NULL) {
free(data.piActionString);
}
return SQLZ_DISCONNECT_PROC;
}
|
The procedure needs to be registered in each database where you want to use it. The CREATE PROCEDURE statement shown below (Listing 5) must be aligned with the signature of the C code function so that DB2 can pass all the values correctly through the program stack. In particular, the number and data types of the parameters, as well as null indicators and additional parameters, must be consistent with the parameter style declared in the CREATE PROCEDURE statement.
Listing 5. CREATE PROCEDURE statement
CREATE PROCEDURE load(IN fileName VARCHAR(256),
IN fileType VARCHAR(3), IN tableName VARCHAR(256))
SPECIFIC load
DYNAMIC RESULT SETS 0
MODIFIES SQL DATA
NOT DETERMINISTIC
CALLED ON NULL INPUT
LANGUAGE C
EXTERNAL NAME 'db2_api!load_proc'
FENCED THREADSAFE
NO EXTERNAL ACTION
PARAMETER STYLE SQL@
|
The first step in testing the procedure is to prepare a file that can be loaded in a table. The file has to reside on the server side of the database system because the procedure will be executed on the database server and has only access to that machine. (We, therefore, run our samples directly on the server itself.) Once the file is created using DB2's EXPORT functionality, you can use it to test the procedure. This and some further steps are illustrated in Listing 6.
Listing 6. Testing the LOAD procedure
$ db2 -td@
db2 => CREATE TABLE stolze.t ( a INT NOT NULL, b VARCHAR(100), PRIMARY KEY(a) )@
db2 => INSERT INTO stolze.t VALUES (1, '2'), (3, '4'), (5, '6')@
db2 => EXPORT TO /home/stolze/testdata.ixf OF IXF SELECT * FROM stolze.t@
SQL3104N The Export utility is beginning to export data to file
"/home/stolze/testdata.ixf".
SQL3105N The Export utility has finished exporting "3" rows.
Number of rows exported: 3
db2 => CREATE TABLE stolze.target LIKE stolze.t@
db2 => CALL load('/home/stolze/testdata.ixf', 'IXF', 'stolze.target')@
Return Status = 0
db2 => SELECT * FROM stolze.target@
A B
----------- -------------------
1 2
3 4
5 6
3 record(s) selected.
db2 => CREATE TABLE stolze.t2 ( a INT NOT NULL )@
db2 => CALL load('/home/stolze/testdata.ixf', 'IXF', 'stolze.t2')@
Return Status = 0
db2 => SELECT * FROM stolze.t2@
A
-----------
1
3
5
3 record(s) selected.
db2 => CALL load('/home/stolze/testdata.ixf', 'IXF', 'unknown.table')@
SQL0443N Routine "STOLZE.LOAD" (specific name "LOAD") has returned an error
SQLSTATE with diagnostic text "Error -3304 returned by db2Runstats.".
SQLSTATE=38LD4
db2 => ? sql3304@
SQL3304N The table does not exist.
Explanation:
The parameters specified on the command require that the table
exists.
|
The tests have shown that the procedure works as expected. The LOAD is performed successfully and the data is populated into the respective tables. When using this procedure, keep the behavior of LOAD itself in mind. For example, if there are any dependent tables (tables that have a foreign key referencing the table into which data is to be loaded, for example), they might be placed in the CHECK PENDING state. Additionally, further constraints could also result in the table not being available right after the LOAD operation, requiring you to run the SET INTEGRITY SQL statement first.
Building and installing the procedures
The source code for both stored procedures presented above can be found under Download. In order to build the procedures, you might want to use the bldrtn script that comes with the C code samples of DB2 and can be found in the sqllib/samples/c/ directory of the DB2 instance.
The bldrtn script takes care of all the necessary steps. First, it compiles the C source code into an object file and subsequently creates the shared library that will be loaded by DB2 at runtime when one of the procedures is called. The library will be copied to the sqllib/function/ directory in the final step. Listing 7 demonstrates how you use the script with the db2_api.c source file, which contains the implementation for both procedures discussed above.
Listing 7. Building the procedures and verifying the installation
$ cp ~/sqllib/samples/c/bldrtn . $ ./bldrtn db2_api $ ls -sh ~/sqllib/function/db2_api 24K /home/stolze/sqllib/function/db2_api |
Once you have your own implementation of the procedures running and tested to your satisfaction, you can apply it to your production system. Note that you do not have to install a C compiler or the DB2 samples on the production system. You merely have to copy the shared library db2_api to the target system and place it in the sqllib/function/ directory. Of course, you need to register the procedures in the databases of the target system, and you are all set up.
A final word should be said about authorization inside the procedures. From the API documentations listed under Resources, you can derive that the DB2 APIs perform their own authorization checking. You only need to be aware that the APIs are called implicitly with the authorization of the user who called the stored procedure; usually that's also the user who connected to the database.
Many of the DB2 commands are only available using the DB2 command line or a C API. In this article we demonstrated how you can easily make almost any DB2 command available for any application by wrapping it into a stored procedure. This approach allows the application to be implemented in any language, like Java or PHP. By means of example, we used the functionality of the RUNSTATS and LOAD commands, but of course the mechanism can be applied to any other command that is available with a DB2 API.
| Description | Name | Size | Download method |
|---|---|---|---|
| C source code and SQL scripts | db2_api.zip | 5 KB |
FTP
 |
HTTP
|
Information about download methods
- Specification of the
db2Runstatsadministrative API. - Description of the
RUNSTATScommand. - Description of the
LOADcommand. - Specification of the
db2Loadadministrative API. - Read "An introduction to the DB2 UDB SQL administrative routines" (developerWorks, May 2005) to learn how to use some of these routines, which can be invoked from a DB2 command line, a command script, or an application containing embedded SQL.
- The article "Using DB2 routines to ease migration" (developerWorks, September 2004) shows how to migrate your database and applications from database products to the DB2 UDB.
Knut Stolze started his work with DB2 when he joined IBM as a visiting scientist at the Silicon Valley Lab where he worked on the DB2 Image Extender. For the following two years he moved on to the DB2 Spatial Extender team and was responsible for several enhancements to improve the usability, the performance, and the standard-conformance of the Extender. Currently, he works as a teaching assistant at the University of Jena, Germany, and continues his work for IBM in the area of the Information Integrator development. He can be reached through the newsgroups comp.databases.ibm-db2 and ibm.software.db2.udb.spatial or via stolze@de.ibm.com.
Comments (Undergoing maintenance)
Table of contents
Next steps from IBM
My developerWorks community
Tags
Use the slider bar to see more or fewer tags.
Popular tags shows the top tags for this particular content zone (for example, Java technology, Linux, WebSphere).
My tags shows your tags for this particular content zone (for example, Java technology, Linux, WebSphere).
Popular article tags |
My article tagsSkip to tags list
Popular article tags |
My article tags
