Ready to port your app from Oracle or Sybase to DB2? This handy set of SQL UDFs can help make that move a little bit easier by providing the same or similar functionality in DB2 for certain functions available in Oracle or Sybase. Updated for DB2 UDB V8.

11 July 2005 (First published 01 June 2004)

Ready to port your app from Oracle or Sybase to DB2? This handy set of SQL UDFs can help make that move a little bit easier by providing the same or similar functionality in DB2 for certain functions available in Oracle or Sybase. Updated for DB2 UDB V8.

Introduction

Ready to port your app from Oracle or Sybase to DB2? This handy set of SQL UDFs can help make that move a little bit easier by providing the same or similar functionality in DB2 for certain functions available in Oracle or Sybase. These sample files work to help analyze information gathered from snapshot monitoring sample code files.

Many of the UDFs have been rewritten for DB2 UDB V8 by using SQL CONTROL statements. You'll see over 30 functions including ADD_MONTHS, BITAND, FORMAT, GREATEST, and many more. These samples are useful in learning design and coding techniques for DB2 SQL.

These UDFs are provided "as is" and were written and tested only in a DB2 for Linux, Unix® and Windows® environment by the author. Because your environment may be different, you are responsible for testing in your own environment.

About the sample code

The sample code is delivered in a zip (Windows) file approximately 40 KB in size.

The UDF samples for migration include:

  • FnOrAddM.txt (ADD_MONTHS) -- Add month to date.
  • FnBitAnd.txt (BITAND) -- Bit by bit AND.
  • FncTrign.txt (COSH, SINH, TANH, PI) -- Mathematical trigonometric functions.
  • FnGratst.txt (GREATEST) -- Greatest value of list of data.
  • FnLeastN.txt (LEAST) -- Least value of list of data.
  • FncHeX2I.txt (HEXTOINT) -- Convert hexadecimal to integer.
  • FnHx2Raw.txt (HEXTORAW) -- Convert hexadecimal to character string.
  • FncOrIcp.txt (INITCAP) -- Convert first character to upper case and other characters to lower case.
  • FncInStr.txt (INSTRB) -- Return the position of char2 in char1. Same function as DB2 LOCATE function with more functions.
  • FnLastDy.txt (LAST_DAY) -- Last day of month.
  • FnLengtO.txt (LENGTH_O) -- Count number of characters in a string. Each characters can be SBCS or DBCS, like Oracle length function.
  • FncOrPad.txt (LPAD) -- Pad to left with specified string.
  • (RPAD) -- Pad to right with specified string.
  • FnMBetwn.txt (MONTHS_BETWEEN) -- Months between D1 and D2. Fractionals are calculated considering as 31 days in a month.
  • FnNewTme.txt (NEW_TIME) -- Convert time of timezone Z1 to timezone Z2.
  • FnNextDy.txt (NEXT_DAY) -- Next day name of a week.
  • FnRevers.txt (REVERSE) -- Reverse order of character string.
  • FncStrip.txt (STRIP) -- Strip leading, trailing or both side characters.
  • FnSubstO.txt (SUBSTR_O) -- Substring of a string. Each characters can be SBCS or DBCS, like Oracle length function.
  • FncSysDt.txt (SYSDATE) -- Get current timestamp.
  • FncToChr.txt (TO_CHAR) -- Convert date, time or timestamp to character string according to specified format.
  • FncToDt2.txt (TO_DATE) -- Convert character string to timestamp according to specified format.
  • trunc.sql (TRUNC) -- Return truncated timestamp value according to specified format. (Author: Amyris V. Rada)
  • FncDUMPC.txt -- DUMP(char), DUMP(varchar)
  • FncDUMPn.txt -- DUMP(integer), DUMP(double), etc.
  • FncDUMPT.txt -- DUMP(date), DUMP(time), DUMP(timestsmp)
  • FncHEX2C.txt -- X2C (Used in DUMP UDF)
  • FncHEX2D.txt -- X2D (Used in DUMP UDF)
  • FncHEX2O.txt -- X2O (Used in DUMP UDF)

Download

DescriptionNameSize
Code sampleUDF4Migration.ZIP  ( HTTP | FTP )51 KB
static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=197323
SummaryTitle=Sample UDFs for Migration
publish-date=07112005