Centerfield Technology, Inc. -- DB2 iSeries application performance tools

DECIMAL accessPathSize	(	LIB	CHAR(10),
		FILE	CHAR(10),
		MEMBER	CHAR(10)
	)

Returns the maximum access path size for a given table.

Parameters:

	LIB	- Library.
	FILE	- File.
	MEMBER	- Member.

Returns:: The access path size of the given table.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

overload: \ DECIMAL accessPathSize(VARCHAR, VARCHAR, VARCHAR)

Function Definition

        RETURNS DECIMAL(12,0)
        LANGUAGE SQL
        DETERMINISTIC
        RETURNS NULL ON NULL INPUT

Example Function Call:

STRSQL call
1. SELECT accessPathSize(CHAR('QSYS'), CHAR('QADBXREF'), CHAR('QADBXREF')) AS "Access Path size in bytes" FROM sysibm/sysdummy1

SPL Example

CREATE function testAccessPathSize()
RETURNS DECIMAL(12,0)
LANGUAGE SQL
p1: begin
DECLARE pathSize DECIMAL(12,0);
DECLARE c1 CURSOR FOR
  SELECT accessPathSize(CHAR('QSYS'), CHAR('QADBXREF'),
        CHAR('QADBXREF'))AS "Access Path size in bytes" FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 into pathSize;
  return pathSize;
CLOSE c1;
END p1

INTEGER activeJobsInSystem ( )

Returns an integer containing the number of active jobs on the system.

Parameters:

-

This function requires no parameters.

Returns:: The number of active jobs on the system.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS INT
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        NOT DETERMINISTIC
        NO SQL
        CALLED ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select activeJobsInSystem() from sysibm/sysdummy1;

SPL Example

CREATE function testActiveJobsInSystem()
RETURNS INT
LANGUAGE SQL
p1: BEGIN
DECLARE activeJobs INTEGER;
DECLARE c1 CURSOR FOR
   SELECT activeJobsInSystem() FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO activeJobs;
  RETURN activeJobs;
CLOSE c1;
END p1

INTEGER activeThreadsInSystem ( )

Returns an integer containing the number of active threads in the system.

Parameters:

-

This function requires no parameters.

Returns:: Number of active threads in the system.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS INT
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        NOT DETERMINISTIC
        NO SQL
        CALLED ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select activeThreadsInSystem() from sysibm/sysdummy1;

SPL Example

CREATE function testActiveThreadsInSystem()
RETURNS INT
LANGUAGE SQL
p1: begin
DECLARE activeThreads INTEGER;
DECLARE c1 CURSOR FOR
   SELECT activeThreadsInSystem() FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 into activeThreads;
  return activeThreads;
CLOSE c1;
END p1

INTEGER batchJobsInMessageWait ( )

Returns an integer containing the number of batch jobs currently in message wait on the system.

Parameters:

-

This function requires no parameters.

Returns:: Number of batch jobs currently in message wait on the system.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS INT
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        NOT DETERMINISTIC
        NO SQL
        CALLED ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select batchJobsInMessageWait() from sysibm/sysdummy1;

SPL Example

CREATE function testBatchJobsInMessageWait()
RETURNS INT
LANGUAGE SQL
p1: BEGIN
DECLARE batchJobs INTEGER;
DECLARE c1 CURSOR FOR
   SELECT batchJobsInMessageWait() FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO batchJobs;
  RETURN batchJobs;
CLOSE c1;
END p1

INTEGER batchJobsRunning ( )

Returns an integer containing the number of batch jobs that are currently running on the system.

Parameters:

-

This function requires no parameters.

Returns:: Number of batch jobs that are currently running on the system.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS INT
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        NOT DETERMINISTIC
        NO SQL
        CALLED ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select batchJobsRunning() from sysibm/sysdummy1;

SPL Example

CREATE function testBatchJobsRunning()
RETURNS INT
LANGUAGE SQL
p1: BEGIN
DECLARE batchJobs INTEGER;
DECLARE c1 CURSOR FOR
   SELECT batchJobsRunning() FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO batchJobs;
  RETURN batchJobs;
CLOSE c1;
END p1

INTEGER charindex	(	needle	VARCHAR(32739),
		haystack	VARCHAR(32739),
		startpos	INT
	)

Returns index of the first occurrence of a substring in a targetString.

Parameters:

	needle	The substring to be found.
	haystack	The target string to be scanned.
	startpos	The character position where to start searching in the target string. A value of 1 (or less) starts at the beginning of the target.

needle and haystack can be any of: char, varchar, clob, or blob. Any CHAR greater than 32739 will be truncated. See also charindexg and charindexgcc.

Returns:: An integer value of the starting index for the substring. The value is the position from the beginning of the string, not from 'start'. If the substring is not in the target string, the function returns 0.

Note:: This is a function created for convenience in porting from other database access languages. Using the iSeries native function of 'locate' will result in substantial performance improvements. For example: locate(needle,haystack,4)

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS INT
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        DETERMINISTIC
        NO SQL
        RETURNS NULL ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        ALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. SELECT charIndex('al','bob al and tom',3) FROM sysibm/sysdummy1;

SPL Example

CREATE function testCharIndex()
RETURNS INTEGER
LANGUAGE SQL
p1: BEGIN
DECLARE index INTEGER;
DECLARE c1 CURSOR FOR
   SELECT charIndex('al','bob al and tom',3) FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO index;
  RETURN index;
CLOSE c1;
END p1

INTEGER charindex	(	needle	VARCHAR(32739),
		haystack	VARCHAR(32739)
	)

Returns index of the first occurrence of a substring in a targetString.

Parameters:

	needle	The substring to be found.
	haystack	The target string to be scanned.

needle and haystack can be any of: char, varchar, clob, or blob. Any char greater than 32739 will be truncated to 32739. See also charindexg and charindexgcc.

Returns:: An integer value of the starting index for the substring. If the substring is not in the target string, the function returns 0.

Note:: This is a function created for convenience in porting from other database access languages. Using the iSeries native function of 'locate' will result in substantial performance improvements. For example: locate(needle,haystack)

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS INT
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        DETERMINISTIC
        NO SQL
        RETURNS NULL ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        ALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. SELECT charIndex('al','bob al and tom') FROM sysibm/sysdummy1;

SPL Example

CREATE function testCharIndex()
RETURNS INTEGER
LANGUAGE SQL
p1: BEGIN
DECLARE index INTEGER;
DECLARE c1 CURSOR FOR
   SELECT charIndex('al','bob al and tom') FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO index;
  RETURN index;
CLOSE c1;
END p1

DATE cvtdat	(	VARCHAR(10)	,
		VARCHAR(8)
	)

Returns an SQL date given a date value in character form.

Parameters:

	Character-form	date value.
	Format	of the character date value.

Valid inputs for Format

*MDY   The date has the month, day, year format, mmddyy.
*DMY   The date has the day, month, year format, ddmmyy.
*YMD   The date has the year, month, day format, yymmdd.
*JUL   The date has the Julian format, yyddd.
*JOB
*MDYY  The date has the month, day, year format, mmddyyyy.
*DMYY  The date has the day, month, year format, ddmmyyyy.
*YYMD  The date has the year, month, day format, yyyymmdd.
*CYMD  The date has the century, year, month, day format,
       cyymmdd, where c is 0 for years 1928 through 1999 and
       is 1 for years 2000 through 2071.
*ISO   The date has the International Organization for
       Standardization (ISO) date format, yyyy-mm-dd.

*USA   The date has the United States date format,
       mm/dd/yyyy.
*EUR   The date has the European date format, dd.mm.yyyy.
*JIS   The date has the Japanese Industrial Standard date
       format, yyyy-mm-dd.
*LONGJUL  The date has the long Julian format, yyyyddd.

Returns:: The SQL form of the character date.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

    Returns Date
    Language C
    Parameter Style DB2SQL
    Deterministic
    NO SQL
    RETURNS NULL ON NULL INPUT
    DBINFO
    NO EXTERNAL ACTION
    DISALLOW PARALLEL
    NO SCRATCHPAD
    NOT FENCED

Example Function Call:

STRSQL call
1. SELECT CVTDAT('060307', '*YMD') FROM SYSIBM/SYSDUMMY1;

SPL Example

CREATE function testcvtdat()
RETURNS DATE
LANGUAGE SQL
p1: BEGIN
DECLARE newdat DATE;
DECLARE c1 CURSOR FOR
   SELECT cvtdat('060307', '*YMD') FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO newdat;
  RETURN newdat;
CLOSE c1;
END p1

DATETIME dateAdd	(	datePart	VARCHAR(14),
		number	INT,
		inDateTime	TIMESTAMP
	)

Adds a number of dateParts (e.g., days, months) to a DATE, TIME, or TIMESTAMP. To subtract a number of dateparts using dateAdd(), use a negative number.

**IMPORTANT** - This function will accept a Date, Time, or Timestamp Value. If you pass a TIMESTAMP, it will return a TIMESTAMP, pass a DATE, it will return a DATE etc.

Parameters:

	datePart	Portion to change ('day','month','year').
	number	Number to add to a specified datepart.
	inDateTime	The date, time, or timestamp to be changed.

Valid inputs, abbreviations, and associations for datePart

year         yy, yyyy      valid on Date or Timestamp
quarter      qq, q         valid on Date or Timestamp
month        mm, m         valid on Date or Timestamp
dayofyear    dy, y         valid on Date or Timestamp - same as day
day          dd, d         valid on Date or Timestamp
week         wk, ww        valid on Date or Timestamp
week_iso     wi            valid on Date or Timestamp - same as week
weekday      dw, w         valid on Date or Timestamp - same as day
dayofweek_iso    dwi       valid on Date or Timestamp - same as day
weekday_iso                valid on Date or Timestamp - same as day
hour         hh, h         valid on Time or Timestamp
minute       mi, n         valid on Time or Timestamp
second       ss, sh        valid on Time or Timestamp
millisecond  ms            valid on Timestamp
microsecond  mu            valid on Timestamp

Returns:: The updated value with the same datatype as 'inDateTime'.

Exceptions:

	SQLSTATE	XC182 - The datepart does not match the operand.
	SQLSTATE	XC187 - The datepart is unrecognized.

Function Definition

      RETURNS DATE (or TIME or TIMESTAMP)
      LANGUAGE SQL
      DETERMINISTIC
      RETURNS NULL ON NULL INPUT

Example Function Call:

STRSQL call
1. select dateAdd('day',4,current date) from sysibm/sysdummy1;

SPL Example

CREATE function testDateAdd()
RETURNS DATE
LANGUAGE SQL
p1: BEGIN
DECLARE newDate DATE;
DECLARE c1 CURSOR FOR
   SELECT dateAdd('day',4,current date) FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 into newDate;
  return newDate;
CLOSE c1;
END p1

INTEGER dateDiff	(	datePart	VARCHAR(14),
		StrDateTime	TIMESTAMP,
		EndDateTime	TIMESTAMP
	)

Calculates the difference between two datetime values in the given units. For a positive result, EndDateTime should be later than StrDateTime.

**IMPORTANT** - This function will accept a Date, Time, or Timestamp Value. But both StrDateTime and EndDateTime must be of the same type, be it a Date, Time, or Timestamp.

Parameters:

	datePart	unit for the return value; the number of days, weeks, microseconds, etc., between the two values.
	StrDateTime	Starting date, time, or timestamp (to subtract)
	EndDateTime	Ending value (same type) (to be subtracted from)

Valid inputs, abbreviations, and associations for datePart

year         yy, yyyy      valid on Date or Timestamp
quarter      qq, q         valid on Date or Timestamp
month        mm, m         valid on Date or Timestamp
dayofyear    dy, y         valid on Date or Timestamp - same as day
day          dd, d         valid on Date or Timestamp
week         wk, ww        valid on Date or Timestamp
week_iso     wi            valid on Date or Timestamp - same as week
weekday      dw, w         valid on Date or Timestamp - same as day
dayofweek_iso    dwi       valid on Date or Timestamp - same as day
weekday_iso                valid on Date or Timestamp - same as day
hour         hh, h         valid on Time or Timestamp
minute       mi, n         valid on Time or Timestamp
second       ss, sh        valid on Time or Timestamp
millisecond  ms            valid on Timestamp
microsecond  mu            valid on Timestamp

Returns:: an integer with the difference between the two datetime values

Exceptions:

	SQLSTATE	XC182 - The datepart does not match the operand.
	SQLSTATE	XC187 - The datepart is unrecognized.

Function Definition

      RETURNS INTEGER
      LANGUAGE SQL
      DETERMINISTIC
      RETURNS NULL ON NULL INPUT

Example Function Call:

STRSQL call SELECT dateDiff('day', date('1982-03-20'), current date) FROM sysibm/sysdummy1;

SPL Example

CREATE function testDateDiff()
RETURNS INTEGER
LANGUAGE SQL
p1: BEGIN
DECLARE returnValue INTEGER;
DECLARE c1 CURSOR FOR
   SELECT dateDiff('hour', time('08:22:44'), current time)
             FROM sysibm/sysdummy1;
OPEN c1;
FETCH c1 INTO returnValue;
  RETURN returnValue;
CLOSE c1;
END p1

VARCHAR dateName	(	datePart	VARCHAR(14),
		inDateTime	DATETIME
	)

Returns the name of the given part of a DATE, TIME, or TIMESTAMP value.

**IMPORTANT** - This function will accept a Date, Time, or Timestamp Value.

Parameters:

	datePart	Portion of the Date/Time value to return.
	inDateTime	Original Date, Time, or Timestamp value.

Valid inputs, abbreviations, associations, and return examples

  Assuming an input timestamp of '2005-02-23-01.32.54.810606'
year         yy, yyyy      valid on Date or Timestamp      2005
quarter      qq, q         valid on Date or Timestamp      1
month        mm, m         valid on Date or Timestamp      February
dayofyear    dy, y         valid on Date or Timestamp      54
day          dd, d         valid on Date or Timestamp      23
week         wk, ww        valid on Date or Timestamp      9
week_iso     wi            valid on Date or Timestamp      8
weekday      dw, w         valid on Date or Timestamp      Wednesday
dayofweek_iso    dwi       valid on Date or Timestamp      Wednesday
weekday_iso                valid on Date or Timestamp      Wednesday
hour         hh, h         valid on Time or Timestamp      1
minute       mi, n         valid on Time or Timestamp      32
second       ss, sh        valid on Time or Timestamp      54
millisecond  ms            valid on Timestamp              810
microsecond  mu            valid on Timestamp              810606

Returns:: A VARCHAR(10) with the given part of the date/time value. For parts other than 'weekday' or 'month', this function will return in character form the same number returned from the datePart() function.

Exceptions:

	SQLSTATE	XC182 - The datepart does not match the operand.
	SQLSTATE	XC187 - The datepart is unrecognized.

Function Definition

      RETURNS VARCHAR(10)
      LANGUAGE SQL
      DETERMINISTIC
      RETURNS NULL ON NULL INPUT

Example Function Call:

STRSQL call
1. select dateName('weekday',current date) from sysibm/sysdummy1;

SPL Example

CREATE function testdateName()
RETURNS VARCHAR(10)
LANGUAGE SQL
p1: BEGIN
DECLARE WhichMonth VARCHAR(10);
DECLARE c1 CURSOR FOR
   SELECT dateName('Month',current timestamp) FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 into WhichQuarter;
  return WhichQuarter;
CLOSE c1;
END p1

INTEGER datePart	(	datePart	VARCHAR(14),
		inDateTime	TIMESTAMP
	)

Returns the given part of a DATE, TIME, or TIMESTAMP value.

**IMPORTANT** - This function will accept a Date, Time, or Timestamp Value.

Parameters:

	datePart	Portion of the Date/Time value to return.
	inDateTime	Original Date, Time, or Timestamp value.

Valid inputs, abbreviations, and associations for datePart

year         yy, yyyy      valid on Date or Timestamp
quarter      qq, q         valid on Date or Timestamp
month        mm, m         valid on Date or Timestamp
dayofyear    dy, y         valid on Date or Timestamp
day          dd, d         valid on Date or Timestamp
week         wk, ww        valid on Date or Timestamp - week starts with Sun,
                                                        Jan 1 is in week 1
week_iso     wi            valid on Date or Timestamp - week starts with Mon,
                                                        week 1 is first week
                                                        with a Thursday
weekday      dw, w         valid on Date or Timestamp - Sunday is 1, Sat 7
dayofweek_iso    dwi       valid on Date or Timestamp - Monday is 1, Sun 7
weekday_iso                valid on Date or Timestamp - Monday is 1, Sun 7
hour         hh, h         valid on Time or Timestamp
minute       mi, n         valid on Time or Timestamp
second       ss, sh        valid on Time or Timestamp
millisecond  ms            valid on Timestamp
microsecond  mu            valid on Timestamp

Returns:: An integer with the given part of the date/time value

Exceptions:

	SQLSTATE	XC182 - The datepart does not match the operand.
	SQLSTATE	XC187 - The datepart is unrecognized.

Function Definition

      RETURNS INTEGER
      LANGUAGE SQL
      DETERMINISTIC
      RETURNS NULL ON NULL INPUT

Example Function Call:

STRSQL call
1. select datePart('day',current date) from sysibm/sysdummy1;

SPL Example

CREATE function testdatePart()
RETURNS INTEGER
LANGUAGE SQL
p1: BEGIN
DECLARE WhichQuarter INTEGER;
DECLARE c1 CURSOR FOR
   SELECT datePart('Quarter',current timestamp) FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 into WhichQuarter;
  return WhichQuarter;
CLOSE c1;
END p1

INTEGER drop	(	type	VARCHAR(10),
		lib	VARCHAR(10),
		name	VARCHAR(10)
	)

A generic drop procedure that can be used to drop a wide variety of different objects (ie. Tables, Views, Procedures, Functions, etc).

Parameters:

	type	Type of item to drop (ie. table, procedure, function, views...).
	lib	Library Name.
	name	Name of Item to drop.

Returns:: A 1 if the drop is successful.

Exceptions:

	SQLSTATE	XC999 - An unexpected system exception has occurred.
	SQLSTATE	42704 - An undefined object or constraint name was detected. Function Definition RETURNS INT LANGUAGE C PARAMETER STYLE DB2SQL NOT DETERMINISTIC MODIFIES SQL DATA DBINFO EXTERNAL ACTION DISALLOW PARALLEL RETURNS NULL ON NULL INPUT NO SCRATCHPAD NO FINAL CALL NOT FENCED

Example Function Call:

STRSQL call
- select drop('table','myLib','myTable') from sysibm.sysdummy1;

SPL Example

CREATE function testDrop()
RETURNS INT
LANGUAGE SQL
MODIFIES SQL DATA
p1: BEGIN
DECLARE returnValue INTEGER;

CREATE TABLE QTEMP/someTable(x int, y int)

DECLARE c1 CURSOR FOR
   SELECT drop('table','QTEMP','someTable') FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO returnValue;
  RETURN returnValue;
CLOSE c1;
END p1

TIMESTAMP getDate ( )

Returns the current system date and time.

Parameters:

-

This function requires no parameters.

Returns:: The current timestamp value.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS TIMESTAMP
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        NOT DETERMINISTIC
        NO SQL
        CALLED ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select getDate() from sysibm/sysdummy1;

SPL Example

CREATE function testgetDate()
RETURNS INT
LANGUAGE SQL
p1: BEGIN
DECLARE sysTime INTEGER;
DECLARE c1 CURSOR FOR
   SELECT getDate() FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO sysTime;
  RETURN sysTime;
CLOSE c1;
END p1

Date getUTCDate ( )

Returns Universal Time Coordinate (UTC) date on the given system.

Parameters:

-

This function requires no parameters.

Returns:: The current UTC date.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

      RETURNS TIMESTAMP
      LANGUAGE SQL
      NOT DETERMINISTIC

Example Function Call:

STRSQL call
1. select getUTCDate() from sysibm/sysdummy1;

SPL Example

CREATE function testGetUTCDate()
RETURNS DATE
LANGUAGE SQL
p1: BEGIN
DECLARE utcDate DATE;
DECLARE c1 CURSOR FOR
   SELECT getUTCDate() FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 into utcDate;
  return utcDate;
CLOSE c1;
END p1

VARCHAR hexToRaw ( inputString VARCHAR(32739) )

Returns a string that converts a hexadecimal value to its raw character string equivalent.

Parameters:

inputString

The original hexadecimal string.

Can accept CHAR data type as well but will truncate to 32739

Returns:: The equivalent character string.

Exceptions:

	SQLSTATE	XC999 - An unexpected system exception has occurred.
	SQLSTATE	12500 - Invalid size of hexadecimal string, it must have an even number of characters.

Function Definition

        RETURNS VARCHAR(16369)
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        DETERMINISTIC
        NO SQL
        RETURNS NULL ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select hexToRaw('C38595A385998689859384') from sysibm/sysdummy1;

SPL Example

CREATE function testHexToRaw()
RETURNS VARCHAR(16369)
LANGUAGE SQL
p1: begin
DECLARE rawValue VARCHAR(16369);
DECLARE c1 CURSOR FOR
   SELECT hexToRaw('C38595A385998689859384') FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 into rawValue;
  return rawValue;
CLOSE c1;
END p1

VARCHAR initCap ( inputString VARCHAR(32739) )

Returns a string with the first letter of each word capitalized and all other letters are lowercase.

Parameters:

inputString

The original string value.

Returns:: The properly formatted string.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS VARCHAR(32739)
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        DETERMINISTIC
        NO SQL
        RETURNS NULL ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select initCap('cenTerFiELD tECHNOLogy') from sysibm/sysdummy1

SPL Example

CREATE function testInitCap()
RETURNS VARCHAR(32739)
LANGUAGE SQL
p1: BEGIN
DECLARE newString VARCHAR(32739);
DECLARE c1 CURSOR FOR
   SELECT initCap('cenTerFiELD tECHNOLogy') FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO newString;
  RETURN newString;
CLOSE c1;
END p1

INTEGER isDate ( inputDate VARCHAR(20) )

Validates if a character string can be converted to DATE.

Parameters:

inputDate

- A string value.

Returns:: A 1 if the inputString is a valid date, -1 if it is not.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS INT
        LANGUAGE SQL
        DETERMINISTIC
        RETURNS NULL ON NULL INPUT

Example Function Call:

STRSQL call
1. select isDate('12/12/2006') from sysibm/sysdummy1;

SPL Example

CREATE function testIsDate()
RETURNS INTEGER
LANGUAGE SQL
p1: BEGIN
DECLARE returnValue INTEGER;
DECLARE c1 CURSOR FOR
   SELECT isDate('22/12/2006') FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO returnValue;
  RETURN returnValue;
CLOSE c1;
END p1

INTEGER isNumeric ( inputString VARCHAR(100) )

Validates if a character string can be converted into a numeric value.

Parameters:

inputString

- A string value of a numeric value.

Can accept CHAR data type as well

Returns:: A 1 if the string is numeric, a -1 if the string is non-numeric.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS INT
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        DETERMINISTIC
        NO SQL
        RETURNS NULL ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select isNumeric('-23422.342') from sysibm/sysdummy1;

SPL Example

CREATE function testIsNumeric()
RETURNS INTEGER
LANGUAGE SQL
p1: BEGIN
DECLARE returnValue INTEGER;
DECLARE c1 CURSOR FOR
   SELECT isNumeric('-23422.342') FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO returnValue;
  RETURN returnValue;
CLOSE c1;
END p1

INTEGER jobsInSystem ( )

Returns an integer containing the number of jobs (active and inactive) on the system.

Parameters:

-

This function requires no parameters.

Returns:: The number of jobs on the system.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS INT
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        NOT DETERMINISTIC
        NO SQL
        CALLED ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select jobsInSystem() from sysibm/sysdummy1;

SPL Example

CREATE function testJobsInSystem()
RETURNS INT
LANGUAGE SQL
p1: BEGIN
DECLARE jobsInSystem INTEGER;
DECLARE c1 CURSOR FOR
   SELECT jobsInSystem() FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO jobsInSystem;
  RETURN jobsInSystem;
CLOSE c1;
END p1

INTEGER len ( inputString VARCHAR(32739) )

Returns the number of characters in the expression.

Parameters:

inputString

- The original character string.

Can accept CHAR data type as well but will truncate to 32739

Returns:: The number of characters in the expression.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS INT
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        DETERMINISTIC
        NO SQL
        RETURNS NULL ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select len('John Jacob Smith') from sysibm/sysdummy1;

SPL Example

CREATE function testLen()
RETURNS INTEGER
LANGUAGE SQL
p1: BEGIN
DECLARE length INTEGER;
DECLARE c1 CURSOR FOR
   SELECT len('John Jacob Smith') FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO length;
  RETURN length;
CLOSE c1;
END p1

VARCHAR lpad	(	inputString	VARCHAR(32739),
		pad	INT,
		padString	VARCHAR(32739)
	)

Returns the inputString, left-padded with the padString.

Parameters:

	inputString	The original character string.
	pad	The number of positions to pad.
	padString	The String to pad.

Can accept CHAR data type as well but will truncate to 32739

Returns:: The padded inputString.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

    RETURNS VARCHAR(32739)
    LANGUAGE C
    PARAMETER STYLE DB2SQL
    DETERMINISTIC
    NO SQL
    RETURNS NULL ON NULL INPUT
    DBINFO
    NO EXTERNAL ACTION
    DISALLOW PARALLEL
    NO SCRATCHPAD
    NOT FENCED

Example Function Call:

STRSQL call
1. select lpad('Brian Jones', 20, '*') from sysibm/sysdummy1;

SPL Example

CREATE function testLpad()
RETURNS VARCHAR(32739)
LANGUAGE SQL
p1: BEGIN
DECLARE newString INTEGER;
DECLARE c1 CURSOR FOR
   SELECT lpad('Brian Jones', 20, '*')  FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO newString;
  RETURN newString;
CLOSE c1;
END p1

VARCHAR lpad	(	inputString	VARCHAR(32739),
		pad	INT
	)

Returns the inputString, left-padded with a variable number of spaces.

Parameters:

	inputString	The original character string.
	pad	The number of positions to pad with spaces.

Can accept CHAR data type as well but will truncate to 32739

Returns:: The padded inputString.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

    RETURNS VARCHAR(32739)
    LANGUAGE C
    PARAMETER STYLE DB2SQL
    DETERMINISTIC
    NO SQL
    RETURNS NULL ON NULL INPUT
    DBINFO
    NO EXTERNAL ACTION
    DISALLOW PARALLEL
    NO SCRATCHPAD
    NOT FENCED

Example Function Call:

STRSQL call
1. select lpad('Brian Jones', 20) from sysibm/sysdummy1;

SPL Example

CREATE function testLpad()
RETURNS VARCHAR(32739)
LANGUAGE SQL
p1: BEGIN
DECLARE newString INTEGER;
DECLARE c1 CURSOR FOR
   SELECT lpad('Brian Jones', 20)  FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO newString;
  RETURN newString;
CLOSE c1;
END p1

DECIMAL months_between	(	date1	DATE,
		date2	DATE
	)

Calculates the number of months between two date objects. For a positive result, date1 should be later than date2. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an whole number. Any fraction of a month is considered with 31 days per month.

Parameters:

	date1	- The first date value.
	date2	- The second date value.

Returns:: A decimal value representing the number of months between two dates.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

      RETURNS DECIMAL(31,10)
      LANGUAGE SQL
      DETERMINISTIC
      RETURNS NULL ON NULL INPUT

Example Function Call:

STRSQL call
1. select months_between(date('01/01/2007'), date( '12/01/2006')) from sysibm/sysdummy1;

SPL Example

CREATE function testMonths_Between()
RETURNS DECIMAL(31,10)
LANGUAGE SQL
p1: BEGIN
DECLARE numOfMonths DOUBLE;
DECLARE c1 CURSOR FOR
   SELECT months_between(date('01/01/2007'), date( '12/01/2006'))
           FROM sysibm/sysdummy1;
OPEN c1;
FETCH c1 INTO numOfMonths;
  RETURN numOfMonths;
CLOSE c1;
END p1

VARCHAR nvl	(	expression1	VARCHAR(32739),
		expression2	VARCHAR(32739)
	)

Returns expression1 if expression1 is not null. If expression1 is null, the function returns expression2.

Parameters:

	expression1	First input string.
	string2	Second input string.

Returns:: Returns expression1 if expression1 is not null. If expression1 is null, the function returns expression2.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Example Function Call:

STRSQL call

select nvl('Tim Johnson', 'No Such Emp.') from sysibm/sysdummy1;

Function Definition

        RETURNS VARCHAR(32739)
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        DETERMINISTIC
        NO SQL
        CALLED ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

SPL Example

CREATE function testNvl()
RETURNS VARCHAR(32739)
LANGUAGE SQL
p1: BEGIN
DECLARE returnString VARCHAR(32739);
DECLARE c1 CURSOR FOR
    select nvl('Tim Johnson', 'No Such Emp.') from sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO returnString;
  RETURN returnString;
CLOSE c1;
END p1

DECIMAL processUnitUsed ( )

Returns a decimal value containing the percentage of the processor that is currently being used on the system.

Parameters:

-

This function requires no parameters.

Returns:: percentage of the processor that is currently being used on the system.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS DECIMAL(5,2)
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        NOT DETERMINISTIC
        NO SQL
        CALLED ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select processUnitUsed() from sysibm/sysdummy1;

SPL Example

CREATE function testProcessUnitUsed()
RETURNS DECIMAL(5,2)
LANGUAGE SQL
p1: BEGIN
DECLARE processUnitUsed INTEGER;
DECLARE c1 CURSOR FOR
   SELECT processUnitUsed() FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO processUnitUsed;
  RETURN processUnitUsed;
CLOSE c1;
END p1

VARCHAR rawToHex ( inputString VARCHAR(16369) )

Returns a string that converts a raw character value to its hex equivalent.

Parameters:

inputString

- The original String value.

Can accept CHAR data type as well but will truncate to 16369

Returns:: The equivalent hexadecimal string.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS VARCHAR(16369)
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        DETERMINISTIC
        NO SQL
        RETURNS NULL ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select rawToHex('Centerfield') from sysibm/sysdummy1;

SPL Example

CREATE function testRawToHex()
RETURNS VARCHAR(32739)
LANGUAGE SQL
p1: begin
DECLARE hexValue VARCHAR(32739);
DECLARE c1 CURSOR FOR
   SELECT rawToHex('Centerfield') FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 into hexValue;
  return hexValue;
CLOSE c1;
END p1

VARCHAR reverse ( str VARCHAR(32739) )

Returns the input string with the order of the characters reversed.

Parameters:

str

- The original string value.

Can accept CHAR data type as well but will truncate to 32739

Returns:: The reversed character string.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS VARCHAR(32739)
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        DETERMINISTIC
        NO SQL
        RETURNS NULL ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select reverse('Its like this string, backwards') from sysibm/sysdummy1;

SPL Example

CREATE function testReverse()
RETURNS INT
LANGUAGE SQL
p1: BEGIN
DECLARE reversedString INTEGER;
DECLARE c1 CURSOR FOR
   SELECT reverse('Its like this string, backwards')
         FROM sysibm/sysdummy1;
OPEN c1;
FETCH c1 INTO reversedString;
  RETURN reversedString;
CLOSE c1;
END p1

INTEGER reverseDigit ( inputInteger INTEGER )

Returns and integer with the digits in reverse order, ie. 123 becomes 321.

Parameters:

inputInteger

- The original integer value.

Returns:: The reversed integer value.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS INTEGER
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        DETERMINISTIC
        NO SQL
        RETURNS NULL ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select reverseDigit(123456) from sysibm/sysdummy1;

SPL Example

CREATE function testReverseDigit()
RETURNS INT
LANGUAGE SQL
p1: BEGIN
DECLARE reversedValue INTEGER;
DECLARE c1 CURSOR FOR
   SELECT reverseDigit(123456) FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO reverseDigit;
  RETURN reverseDigit;
CLOSE c1;
END p1

VARCHAR rpad	(	inputString	VARCHAR(32739),
		pad	INT,
		padString	VARCHAR(32739)
	)

Returns the inputString, right-padded with the padString.

Parameters:

	inputString	The original character string.
	pad	The number of positions to pad.
	padString	The String to pad.

Can accept CHAR data type as well but will truncate to 32739

Returns:: The padded inputString.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

    RETURNS VARCHAR(32739)
    LANGUAGE C
    PARAMETER STYLE DB2SQL
    DETERMINISTIC
    NO SQL
    RETURNS NULL ON NULL INPUT
    DBINFO
    NO EXTERNAL ACTION
    DISALLOW PARALLEL
    NO SCRATCHPAD
    NOT FENCED

Example Function Call:

STRSQL call
1. select rpad('Brian Jones', 20, '*') from sysibm/sysdummy1;

SPL Example

CREATE function testRpad()
RETURNS VARCHAR(32739)
LANGUAGE SQL
p1: BEGIN
DECLARE newString INTEGER;
DECLARE c1 CURSOR FOR
   SELECT rpad('Brian Jones', 20, '*')  FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO newString;
  RETURN newString;
CLOSE c1;
END p1

VARCHAR rpad	(	inputString	VARCHAR(32739),
		pad	INT
	)

Returns inputString, right-padded with a variable number of spaces.

Parameters:

	inputString	The original character string.
	pad	The number of positions to pad with spaces.

Can accept CHAR data type as well but will truncate to 32739

Returns:: The padded inputString.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS VARCHAR(32739)
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        DETERMINISTIC
        NO SQL
        RETURNS NULL ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select rpad('Brian Jones', 20) from sysibm/sysdummy1;

SPL Example

CREATE function testRpad()
RETURNS VARCHAR(32739)
LANGUAGE SQL
p1: BEGIN
DECLARE newString VARCHAR(32739);
DECLARE c1 CURSOR FOR
   SELECT rpad('Brian Jones', 20)  FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO newString;
  RETURN newString;
CLOSE c1;
END p1

INTEGER strcmp	(	string1	VARCHAR(32739),
		string2	VARCHAR(32739)
	)

returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise.

Parameters:

	string1	First input string.
	string2	Second input string.

Returns:: Returns 0 if the strings are the same, -1 if the first argument is smaller than the second, and 1 otherwise.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS INT
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        DETERMINISTIC
        NO SQL
        RETURNS NULL ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. SELECT strcmp('string1', 'string2') FROM sysibm.sysdummy1;

SPL Example

CREATE function testStrcmp()
RETURNS INT
LANGUAGE SQL
p1: BEGIN
DECLARE returnValue INTEGER;
DECLARE c1 CURSOR FOR
   SELECT strcmp('string1', 'string2') FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO returnValue;
  RETURN returnValue;
CLOSE c1;
END p1

TIMESTAMP sysDate ( )

Returns the current system date and time.

Parameters:

-

This function requires no parameters.

Returns:: The current timestamp value.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS TIMESTAMP
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        NOT DETERMINISTIC
        NO SQL
        CALLED ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select sysDate() from sysibm/sysdummy1;

SPL Example

CREATE function testSysDate()
RETURNS INT
LANGUAGE SQL
p1: BEGIN
DECLARE sysTime INTEGER;
DECLARE c1 CURSOR FOR
   SELECT sysDate() FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO sysTime;
  RETURN sysTime;
CLOSE c1;
END p1

VARCHAR sysUser ( )

Returns the current user name.

Parameters:

-

This function requires no parameters.

Returns:: The current user name.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

      RETURNS VARCHAR(10)
      LANGUAGE SQL
      NOT DETERMINISTIC
      CALLED ON NULL INPUT

Example Function Call:

STRSQL call
1. select sysUser() from sysibm/sysdummy1;

SPL Example

CREATE function testSysUser()
RETURNS VARCHAR(10)
LANGUAGE SQL
p1: BEGIN
DECLARE name VARCHAR(10);
DECLARE c1 CURSOR FOR
   SELECT sysUser() FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO name;
  RETURN name;
CLOSE c1;
END p1

INTEGER timeToSeconds ( inputTime TIME )

Converts a time object to its equivalent in seconds.

Parameters:

inputTime

Time object to be converted to seconds.

Returns:: A time object converted to seconds.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

      RETURNS INT
      LANGUAGE SQL
      DETERMINISTIC
      RETURNS NULL ON NULL INPUT

Example Function Call:

STRSQL call
1. select timeToSeconds(TIME('01:30:00')) from sysibm/sysdummy1;

SPL Example

CREATE function testTimeToSeconds()
RETURNS INT
LANGUAGE SQL
p1: begin
DECLARE numOfSeconds INTEGER;
DECLARE c1 CURSOR FOR
   SELECT timeToSeconds(TIME('01:30:00')) FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO numOfSeconds;
  RETURN numOfSeconds;
CLOSE c1;
END p1

INTEGER usersSignedOn ( )

Returns an integer containing the number of users currently signed on to the system.

Parameters:

-

This function requires no parameters.

Returns:: Number of users currently signed on to the system.

Exceptions:

SQLSTATE

XC999 - An unexpected system exception has occurred.

Function Definition

        RETURNS INT
        LANGUAGE C
        PARAMETER STYLE DB2SQL
        NOT DETERMINISTIC
        NO SQL
        CALLED ON NULL INPUT
        DBINFO
        NO EXTERNAL ACTION
        DISALLOW PARALLEL
        NO SCRATCHPAD
        NOT FENCED

Example Function Call:

STRSQL call
1. select usersSignedOn() from sysibm/sysdummy1;

SPL Example

CREATE function testusersSignedOn()
RETURNS INT
LANGUAGE SQL
p1: BEGIN
DECLARE usersSignedOn INTEGER;
DECLARE c1 CURSOR FOR
   SELECT usersSignedOn() FROM sysibm/sysdummy1;

OPEN c1;
FETCH c1 INTO usersSignedOn;
  RETURN usersSignedOn;
CLOSE c1;
END p1


Functions
FUNCTION	accessPathSize (LIB CHAR(10), FILE CHAR(10), MEMBER CHAR(10))
	Returns the maximum access path size for a given table.
FUNCTION	activeJobsInSystem ()
	Returns an integer containing the number of active jobs on the system.
FUNCTION	activeThreadsInSystem ()
	Returns an integer containing the number of active threads in the system.
FUNCTION	batchJobsRunning ()
	Returns an integer containing the number of batch jobs that are currently running on the system.
FUNCTION	batchJobsInMessageWait ()
	Returns an integer containing the number of batch jobs currently in message wait on the system.
FUNCTION	charindex (needle VARCHAR(32739), haystack VARCHAR(32739)) RETURNS INT LANGUAGE C PARAMETER STYLE DB2SQL DETERMINISTIC NO SQL RETURNS NULL ON NULL INPUT DBINFO NO EXTERNAL ACTION ALLOW PARALLEL NO SCRATCHPAD NOT FENCED EXTERNAL NAME 'XCUDFS/XCUDFS(CHARINDEX)'
	Returns index of the first occurrence of a substring in a targetString.
FUNCTION	charindexg (needle vargraphic(16369), haystack vargraphic(16369))
	Same as charindex, but needle and haystack can be any of graphic, vargraphic, or dbclob - but without a CCSID.
FUNCTION	charindexgcc (needle vargraphic(16369) ccsid 13488, haystack vargraphic(16369) ccsid 13488)
	Same as charindex, but needle and haystack can be any of graphic, vargraphic, or dbclob with a CCSID.
FUNCTION	charindex (needle VARCHAR(32739), haystack VARCHAR(32739), startpos INT)
	Returns index of the first occurrence of a substring in a targetString.
FUNCTION	charindexg (needle vargraphic(16369), haystack vargraphic(16369), startpos INT)
	Same as charindex, but needle and haystack can be any of graphic, vargraphic, or dbclob - but without a CCSID.
FUNCTION	charindexgcc (needle vargraphic(16369) ccsid 13488, haystack vargraphic(16369) ccsid 13488, startpos INT)
	Same as charindex, but needle and haystack can be any of graphic, vargraphic, or dbclob with a CCSID.
FUNCTION	cvtdat (VARCHAR(10), VARCHAR(8))
	Returns an SQL date given a date value in character form.
FUNCTION	dateAdd (datePart VARCHAR(14), number INT, inDateTime TIMESTAMP)
	Adds a number of dateParts (e.g., days, months) to a DATE, TIME, or TIMESTAMP. To subtract a number of dateparts using dateAdd(), use a negative number.
FUNCTION	dateDiff (datePart VARCHAR(14), StrDateTime TIMESTAMP, EndDateTime TIMESTAMP)
	Calculates the difference between two datetime values in the given units. For a positive result, EndDateTime should be later than StrDateTime.
FUNCTION	dateName (datePart VARCHAR(14), inDateTime TIMESTAMP)
	Returns the name of the given part of a DATE, TIME, or TIMESTAMP value.
FUNCTION	datePart (datePart VARCHAR(14), inDateTime TIMESTAMP)
	Returns the given part of a DATE, TIME, or TIMESTAMP value.
FUNCTION	drop (type VARCHAR(10), lib VARCHAR(10), name VARCHAR(10))
	A generic drop procedure that can be used to drop a wide variety of different objects (ie. Tables, Views, Procedures, Functions, etc).
FUNCTION	getUTCDate ()
	Returns Universal Time Coordinate (UTC) date on the given system.
FUNCTION	hexToRaw (inputString VARCHAR(32739))
	Returns a string that converts a hexadecimal value to its raw character string equivalent.
FUNCTION	initCap (inputString VARCHAR(32739))
	Returns a string with the first letter of each word capitalized and all other letters are lowercase.
FUNCTION	isDate (inputDate VARCHAR(20))
	Validates if a character string can be converted to DATE.
FUNCTION	isNumeric (inputString VARCHAR(100))
	Validates if a character string can be converted into a numeric value.
FUNCTION	jobsInSystem ()
	Returns an integer containing the number of jobs (active and inactive) on the system.
FUNCTION	len (inputString VARCHAR(32739))
	Returns the number of characters in the expression.
FUNCTION	lpad (inputString VARCHAR(32739), pad INT)
	Returns the inputString, left-padded with a variable number of spaces.
FUNCTION	lpad (inputString VARCHAR(32739), pad INT, padString VARCHAR(32739))
	Returns the inputString, left-padded with the padString.
FUNCTION	months_between (date1 DATE, date2 DATE)
	Calculates the number of months between two date objects. For a positive result, date1 should be later than date2. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an whole number. Any fraction of a month is considered with 31 days per month.
FUNCTION	nvl (expression1 VARCHAR(32739), expression2 VARCHAR(32739))
	Returns expression1 if expression1 is not null. If expression1 is null, the function returns expression2.
FUNCTION	processUnitUsed ()
	Returns a decimal value containing the percentage of the processor that is currently being used on the system.
FUNCTION	rawToHex (inputString VARCHAR(16369))
	Returns a string that converts a raw character value to its hex equivalent.
FUNCTION	reverseDigit (inputInteger INTEGER)
	Returns and integer with the digits in reverse order, ie. 123 becomes 321.
FUNCTION	reverse (str VARCHAR(32739))
	Returns the input string with the order of the characters reversed.
FUNCTION	rpad (inputString VARCHAR(32739), pad INT)
	Returns inputString, right-padded with a variable number of spaces.
FUNCTION	rpad (inputString VARCHAR(32739), pad INT, padString VARCHAR(32739))
	Returns the inputString, right-padded with the padString.
FUNCTION	strcmp (string1 VARCHAR(32739), string2 VARCHAR(32739))
	returns 0 if the strings are the same, -1 if the first argument is smaller than the second according to the current sort order, and 1 otherwise.
FUNCTION	sysDate ()
	Returns the current system date and time.
FUNCTION	getDate ()
	Returns the current system date and time.
FUNCTION	sysUser ()
	Returns the current user name.
FUNCTION	timeToSeconds (inputTime TIME)
	Converts a time object to its equivalent in seconds.
FUNCTION	usersSignedOn ()
	Returns an integer containing the number of users currently signed on to the system.

SQL UDFs File Reference

Functions

Detailed Description

Function Documentation