Using DB2 routines to ease migration

Truncating tables, getting the client's host name, and calling stored procedures from UDFs

Marina Greenstein (greenstm@us.ibm.com), Certified Consulting I/T Specialist - Data Management, IBM

Marina Greenstein is a Certified Technical Consultant with the DB2 Migration Team. She joined IBM at 1995 and is currently responsible for helping customers migrate from competitive DBMS to DB2 UDB. She presented migration methodology and various database migration topics at numerous DB2 Technical conferences and at SHARE.

Arthur Sammartino (asamma@us.ibm.com), Certified Consulting I/T Specialist - Data Management, IBM

Art Sammartino is a Certified Technical Consultant with the IBM Database Migration Team. He is an IBM Certified Solutions Expert who joined IBM with experience in Microsoft SQL Server, Sybase, and Oracle. In the 4+ years that Art has been with IBM, he has assisted more than 200 clients in their migrations from Microsoft SQL Server, Sybase, and Oracle databases to DB2 UDB. He is a co-author of the DB2 UDB V7.1 Porting Guide, SG24-6128, and the Oracle to DB2 UDB Conversion Guide SG24-7048. He can be reached at asamma@us.ibm.com.

Knut Stolze (stolze@de.ibm.com), Information Integration Development, IBM

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. He moved on to the DB2 Spatial Extender Version 8 and was responsible for several enhancements to improve the usability, the performance, and the standard-conformance of the Extender for more than two years. Currently, he works as a teaching assistant at the University of Jena, Germany, and continues his work for IBM in the area of federated databases. He can be reached through the newsgroups comp.databases.ibm-db2 and ibm.software.db2.udb.spatial or at stolze@de.ibm.com.

Summary: Migrating to DB2 from another relational database management system (RDBMS)? Here are some routines that will help you easily migrate some frequently-used functionality in other database products.

Tag this!

Date: 04 Feb 2004
Level: Introductory
Activity: 2619 views

Introduction

When working with various database systems from different vendors, users and database administrators will inevitably encounter features and functions that vary among the products. These differences can be found in such elements as

Varying syntax in supported SQL dialects
Database manager application interfaces, or
The varieties of administration tools and their usage.

In order to make it easier to migrate your database and applications from database products such as Oracle®, Sybase®, or Microsoft® SQL Server to IBM® DB2® Universal Database™ (UDB), this article will present some possible DB2 UDB implementations of functionality that are available in some of these other database systems. These implementations will involve the creation of stored procedures and user-defined functions (UDF) that implement some of these often requested features.

In the download section you will find the source code for the procedure and functions along with an SQL script that contains the CREATE PROCEDURE and CREATE FUNCTION statements. Please refer to the code if you are interested in the exact implementation details. Once you have compiled and linked the source code (or installed the precompiled library) and registered the procedure and functions in your database, you can use them as demonstrated in the examples shown in this article. Also, it is important to note that these procedures and functions can be used for DB2 UDB Version 7 and Version 8.

Truncate Table

A common issue encountered when migrating from Oracle to DB2 is the TRUNCATE command. When executed in Oracle, this command quickly removes all the content from a table without resorting to one or more DELETE operations, which would require extensive logging.

DB2's IMPORT functionality provides the means to achieve the same functionality if the REPLACE INTO clause is used, and an empty file is designated as the data source. In that case, all rows of the table are quickly removed using only a single log record, before the new data is imported from the given file. With an empty file, nothing gets imported so that the table is left truncated at the end of the operation.

To implement this functionality, we can make use of a DB2 defined C API function called sqluimpr() which enables the import of data into a table in the database in a programmatic way. We wrap this API into a stored procedure, and thus make it available for all applications, regardless of programming language, through the SQL interface. The stored procedure TRUNCATE has the signature that is shown in listing 1.

Listing 1. Signature of the procedure TRUNCATE

>>--TRUNCATE--(--schema_name--,--table_name--)--------><

The schema_name parameter of type VARCHAR(130) specifies the schema in which the table can be found. If the schema name is enclosed in double-quote characters, it is treated as a delimited name (mixed case and special characters). If NULL was given for the schema name, that is no schema was specified, then the CURRENT SCHEMA special register is consulted to determine the schema to be used. The table_name parameter of type VARCHAR(130) specifies the unqualified name of the table that is to be truncated. The table is uniquely identified together with the explicitly or implicitly defined schema name. If the table name is enclosed in double-quotes, it is treated as a delimited name (mixed case and special characters).

The logic of the procedure determines the default schema if the input parameter schema_name is NULL. Otherwise, existing double-quotes from the schema name are removed, or the unquoted schema name is converted to upper case. The same is done for the table name, i.e. eventually existing double-quotes around the table name are removed or the unquoted table name is converted to upper case. After that, we verify that the table exists, by querying the DB2 catalog view SYSCAT.TABLES. Now the import can be started. The necessary parameters are prepared where the file /dev/null (on Windows the NUL file) is used as it always exists and does not contain anything, i.e. is the empty file for the data source. Likewise, /dev/null (on Windows NUL) is used for the message file that is needed for the import. The import is started and if it succeeds, the procedure returns successfully. If any error is encountered, the procedure returns an SQLSTATE that indicates the error along with a message text. Listing 2 demonstrates the execution of the TRUNCATE procedure. The source code for this script, truncate_example.db2, may be found in the download section. Please note that the character ‘@’ is used as statement terminator here and throughout the remainder of this article.

Listing 2. Testing the procedure TRUNCATE

-- create and insert some values into the table tab1
CREATE TABLE tab1 ( col1  INTEGER  NOT NULL  PRIMARY KEY, col2  VARCHAR(15) )@
DB20000I  The SQL command completed successfully.

INSERT INTO tab1 VALUES ( 1, 'some data' ), ( 2, NULL )@
DB20000I  The SQL command completed successfully.

-- verify the current contents of table tab1
SELECT * FROM tab1 

COL1        COL2           
----------- ---------------
          1 some data      
          2 -              

  2 record(s) selected.


-- Call the truncate stored procedure for the DB2INST1 schema, and the table tab1
CALL truncate('DB2INST1', 'tab1')@

  Return Status = 0

-- Verify that the table contents have been truncated.
SELECT * FROM tab1@

COL1        COL2           
----------- ---------------

  0 record(s) selected.

-- Insert some new values into the tab1 table
INSERT INTO tab1 VALUES ( 2, 'some new data' ), ( 3, NULL )@
DB20000I  The SQL command completed successfully.

SELECT * FROM tab1 

COL1        COL2           
----------- ---------------
          2 some new data  
          3 -              

  2 record(s) selected.

-- Call the truncate procedure with a NULL schema
CALL truncate(NULL, 'tab1')@

  Return Status = 0

-- Verify that the table contents have been truncated.
SELECT * FROM tab1 

COL1        COL2           
----------- ---------------

  0 record(s) selected.

Sybase's host_name function

The host_name() function in a Sybase database returns the current host computer name of the client process (not the Adaptive Server process), that is, the host name of the computer on which the application is running and not the host name of the database server.

The user-defined function HOST_NAME has the signature shown in listing 3.

Listing 3. Signature of the user-defined function HOST_NAME

>>--HOST_NAME--( )-------------><

Note:

Due to DRDA requirements, the very first character of a non-local IP address is mapped to the letters 'G thru 'P' if it is originally '0' thru '9'. This mapping is reversed before the name-lookup takes place.

The function accesses the application ID stored in the DBINFO structure and decodes the client's IP address, which is part of the application ID. The name of that IP address is then resolved using the C library function "gethostbyaddr", which accesses name servers or other sources (like /etc/hosts) if necessary.

The IP address is encoded in the first 8 bytes of the application ID, or the string "*LOCAL" is used instead to denote local connections. For local connections, the host name of the IP address 127.0.0.1 is resolved.

Listing 4 demonstrates the execution of the HOST_NAME function. It shows the results for a local and a remote connection. Please note that name of the local AIX system (IP address 127.0.0.1) is defined as "demoaix" in the /etc/hosts file. The source code for this script, host_name_example.db2, may be found in the download section.

Listing 4. Testing the function HOST_NAME( )

-- connect to the local database
CONNECT TO sample@

   Database Connection Information

 Database server        = DB2/6000 8.1.2
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLE

-- execute the host_name function
VALUES host_name()@

1                                                                               
------------------------------------------------
demoaix                                                                         
                                                

  1 record(s) selected.                                                

-- the database samplaix is an alias for the SAMPLE database on AIX
-- we connect to that database from a Windows machine
CONNECT TO samplaix@ 

   Database Connection Information

 Database server        = DB2/6000 8.1.2
 SQL authorization ID   = DB2INST1
 Local database alias   = SAMPLAIX

-- execute the host_name UDF against the remote database – it returns
-- the name of the computer of the client connection, i.e. the name
-- of the Windows system as resolved by "gethostbyname"
VALUES host_name()

1                                              
------------------------------------------------
mycomputer

UDF to call a stored procedure from a trigger or user-defined function

Another common issue encountered when migrating to DB2 is the capability of other RDBMS to call stored procedures from triggers or functions. Although this feature is available in DB2 UDB Version 8.2, earlier versions require a work-around. We show how this feature may be implemented using the version 7 or 8.1 of DB2; that is, by creating a UDF that will issue a call to a stored procedure.

The user-defined function CALL_PROCEDURE that is used for this purpose has the signature shown in listing 5.

Listing 5. Signature of the user-defined function CALL_PROCEDURE

>>--CALL_PROCEDURE--(--procedure_name--,--parameter_list--,----->

>-----database_name--,--user_name--,--password--)-------------><

The procedure_name parameter of type VARCHAR(257) specifies the fully qualified name of the stored procedure that is to be called. The parameter_list parameter of type VARCHAR(30000) specifies the parameters that are to be passed to the stored procedure - when passing multiple parameters each must be delimited with a comma. This string is pasted into the CALL statement that is used to invoke the procedure so that its syntax needs to adhere to the requirements of the SQL CALL statement. The database_name parameter of type VARCHAR(8) specifies the alias of the database where the stored procedure is to be executed. The stored procedure does not have to reside in the same database. The user_name parameter of type VARCHAR(128) and the password parameter of type VARCHAR(200) are used to define the login that is used to connect to the database and execute the procedure.

This function calls a stored procedure in the current database. It establishes a new connection and then executes the CALL statement, combining the procedure name and the parameters that were provided as input parameter. The UDF returns 0 (zero) upon successful execution of the CALL statement (and the accompanying CONNECT and CONNECT RESET statements). Otherwise, the return code of the DB2 Command Line Processor (CLP) is returned along with an error message providing more information. Listing 6 demonstrates the execution of the CALL_PROCEDURE function. The procedure to be called takes a single parameter, and that parameters is provided by a trigger. In this sample we create two tables T1 and t2, a procedure ABC with one input parameter (p), and a trigger INS. When the trigger is executed it will call the procedure via the UDF. The procedure will then insert into table T1 with the new value of the column multiplied by the factor 2. This can be tested by performing an insert on table T2 to cause the trigger to fire and then issuing a select on table T1 to verify the contents of the table – and the successful execution of the procedure. The source code for this script, trig_calls_proc.db2, may be found in the download section.

Listing 6. Testing the function CREATE_PROCEDURE( )

CREATE TABLE t1 ( col1 INTEGER )@
DB20000I  The SQL command completed successfully.

CREATE TABLE t2 ( col1 INTEGER )@
DB20000I  The SQL command completed successfully.

CREATE PROCEDURE abc(IN p INTEGER) BEGIN INSERT INTO t1 VALUES(p); END@
DB20000I  The SQL command completed successfully.

CREATE TRIGGER ins AFTER INSERT ON t2
   REFERENCING NEW AS n
   FOR EACH ROW MODE DB2SQL
   BEGIN ATOMIC
      VALUES ( call_procedure('DB2INST1.ABC', char(new.col1 * 2),
         'SAMPLE', 'DB2INST1', 'db2inst1') );
   END@
DB20000I  The SQL command completed successfully.

INSERT INTO t2 VALUES ( 20 )@
DB20000I  The SQL command completed successfully.

-- validate that the trigger has fired - it should update t1
SELECT * FROM t1@

COL1       
-----------
         40

  1 record(s) selected.

The next example demonstrates the call to a stored procedure containing two parameters from a UDF. In this example we create a table C, a stored procedure ABC with two input parameters (p, p2), and a UDF UDF_WITHCALL with two parameters (parm1, parm2). When the UDF is executed it calls the stored procedure; the procedure then inserts into table C the values passed to it from the UDF. A select on table C verifies the contents of the table and, thus, the successful execution of the stored procedure. The source code for this script, udf_calls_proc.db2, may be found in the download section.

CREATE TABLE c ( a INTEGER CHECK (a <> 8), a1 INTEGER )@
DB20000I  The SQL command completed successfully.

CREATE PROCEDURE abc(IN p INTEGER, IN p2 INTEGER)
   BEGIN
      INSERT INTO c VALUES (p, p2);
   END@
DB20000I  The SQL command completed successfully.

CREATE FUNCTION udf_withcall ( parm1 INTEGER, parm2 INTEGER )
   RETURNS INTEGER
   LANGUAGE SQL
   NOT DETERMINISTTIC
   EXTERNAL ACTION
   RETURN call_procedure('DB2INST1.ABC', char(parm1) ||','|| char(parm2),
             'SAMPLE', 'DB2INST1', 'db2inst1'))@
DB20000I  The SQL command completed successfully.

SELECT udf_withcall(30, 40) FROM sysibm.sysdummy1@

1          
-----------
          0

  1 record(s) selected.

-- verify that the UDF has called the procedure and updated the table
SELECT * FROM c@

A           A1         
----------- -----------
         30          40

  1 record(s) selected.

Building the routines

In order to build C routines (UDFs or Stored Procedures), they must first be precompiled, compiled and linked. This process can be automated using the bldrtn (on UNIX/LINUX), or the bldrtn.bat (on Windows) batch file that is included as part of the samples installed with DB2. This file can be found in the <DB2PATH>/sqllib/samples/c directory on UNIX/LINUX or the <DB2PATH\sqllib\samples\c directory on Windows. The bldrtn file is invoked with the following syntax:

bldrtn  <file_name> [dbname userid password]

If the dbname is not supplied, the batch file will default the dbname to SAMPLE and the userid and password to the userid and password of the current session.

Listing 7 demonstrates building the routines in the functions.sqc file using the bldrtn batch file. Please be aware that DB2 Version 7 does not yet support the PARAMETER STYLE SQL. Therefore, you have to compile the file functions-v7.sqc instead.

Listing 7. Executing buildrtn on the functions file

bldrtn functions

It is important to state that the bldrtn batch file on the Windows platform may need to be edited to include the ws2_32.lib in the link instructions. The file should be changed as follows in listing 8.

Listing 8. Modifying the bldrtn.bat file on Windows

:link_step
rem Link the program.
link -debug -out:%1.dll -dll %1.obj db2api.lib 
					ws2_32.lib
				 -def:%1.def

Note:

Note: The export file <filename>.def (on WINDOWS) or <filename>.exp (on UNIX) are required for the build step. For your convenience, these files (functions.def and functions.exp) have been included in the download section.

After the succesfull build of a C routine, the shared library is automatically copied to the sqllib/function directory.

Registering the routines

Once the routines have been built they must be registered within the database. Listing 9 shows the contents of a script created to register these routines in the database.

Listing 9. A Script crested for registering routines in the database.

DROP SPECIFIC PROCEDURE truncate_table@
CREATE PROCEDURE truncate ( IN schemaName VARCHAR(130), IN tableName VARCHAR(130) )
   SPECIFIC truncate_table
   DYNAMIC RESULT SETS 0
   MODIFIES SQL DATA
   NOT DETERMINISTIC
   CALLED ON NULL INPUT
   LANGUAGE C
   EXTERNAL NAME 'functions!truncate_table'
   FENCED  THREADSAFE
   INHERIT SPECIAL REGISTERS
   PARAMETER STYLE SQL
   PROGRAM TYPE SUB
   NO DBINFO@


DROP SPECIFIC FUNCTION client_host_name@
CREATE FUNCTION host_name ( )
   RETURNS VARCHAR(128)
   SPECIFIC client_host_name
   EXTERNAL NAME 'functions!host_name'
   LANGUAGE C
   PARAMETER STYLE SQL
   DETERMINISTIC
   FENCED
   NOT THREADSAFE
   RETURNS NULL ON NULL INPUT
   NO SQL
   NO EXTERNAL ACTION
   NO SCRATCHPAD
   NO FINAL CALL
   ALLOW PARALLEL
   DBINFO@

DROP SPECIFIC FUNCTION call_stp@
CREATE FUNCTION call_procedure ( procedure VARCHAR(257),
      parameters VARCHAR(30000), databaseName VARCHAR(8),
      userName VARCHAR(128), password VARCHAR(200) )
   RETURNS INTEGER
   SPECIFIC call_stp
   EXTERNAL NAME 'functions!call_procedure'
   LANGUAGE C
   -- for version 7 use PARAMETER STYLE DB2SQL here
   PARAMETER STYLE SQL
   NOT DETERMINISTIC
   NOT FENCED
   THREADSAFE
   CALLED ON NULL INPUT
   NO SQL
   EXTERNAL ACTION
   NO SCRATCHPAD
   NO FINAL CALL
   DISALLOW PARALLEL
   NO DBINFO@

After the preceding script is created in a text editor, it is saved in a file called functions.db2, which may be found in the download section. The final step in this process is to execute the script. Listing 10 demonstrates the commands necessary to execute the script against the database.

Listing 10. Executing the functions.db2 script

/* connect to the database */
db2 connect to SAMPLE

/*  specify the terminating character (-td@), verbose output (-v), and the */
/* file name (f functions.db2) for script execution*/
db2 –td@ -vf  functions.db2

Summary

The combination of stored procedures, UDFs, and DB2 APIs shown in this article demonstrate the power of DB2 to enable creative, and viable, solutions to mapping functionality when migrating from competitive databases to DB2 UDB.

References

Download

Name	Size	Download method
db2migroutines.zip	9.12KB	FTP \| HTTP

Information about download methods

About the authors

Comments (Undergoing maintenance)

Trademarks | My developerWorks terms and conditions

Close [x]

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Close [x]

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/

SITE_ID=1

Zone=Information Management

ArticleID=14132

ArticleTitle=Using DB2 routines to ease migration

publish-date=02042004

author1-email=greenstm@us.ibm.com

author1-email-cc=

author2-email=asamma@us.ibm.com

author2-email-cc=