 | Level: Introductory Marina Greenstein (greenstm@us.ibm.com), Certified Consulting I/T Specialist - Data Management, IBM
Arthur Sammartino (asamma@us.ibm.com), Certified Consulting I/T Specialist - Data Management, IBM
Knut Stolze (stolze@de.ibm.com), Information Integration Development, IBM
04 Feb 2004 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.
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
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 |
About the authors | | |
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. |
| | | 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 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. |
Rate this page
| |
