Licensed Materials - Property of IBM

IBM Cognos Products: DOCS

(C) Copyright IBM Corp. 2013

US Government Users Restricted Rights - Use, duplication or disclosure restricted by GSA ADP Schedule Contract with
IBM Corp.


 STEPS

1. Drop this udf.jar file into the c10_location\v5dataserver\lib\ext directory of your Cognos BI server

2. Restart the query service.

3. Create your own model or use this example FM model which is based on GOSALES in DB2

 

DESCRIPTION

What you have are some very simple examples of locally processed functions that perform useful operations like the following:
        - find the Nth instance of a string in a string
        - find the first instance of a string starting from the end not the start the string
        - find the 'nth' word in a string (where space is the delimiter)
        - aggregate to compute the geometric mean (you can do this in SQL exp(sum(log(n))/count))
        - simple 'templated/parameterized' scalar UDF which RQP does not allow today
        - table function to return the data from an RSS feed such as:
                BBC: 'http://feeds.bbci.co.uk/news/rss.xml'
                CNN: 'http://rss.cnn.com/rss/edition_world.rss'
                CBC: 'http://rss.cbc.ca/lineup/topstories.xml'

++++++++++++++++++++++++++

SQLActions[] = {

"BEGIN INSTALL
    
CREATE AGGREGATE DQM_GEOMETRICMEAN (  p0 SMALLINT )
RETURNS DOUBLE PRECISION
LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'thisjar:udf.GeometricMean';

CREATE AGGREGATE DQM_GEOMETRICMEAN (  p0 INTEGER )
RETURNS DOUBLE PRECISION
LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'thisjar:udf.GeometricMean';
    
CREATE AGGREGATE DQM_GEOMETRICMEAN (  p0 BIGINT )
RETURNS DOUBLE PRECISION
LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'thisjar:udf.GeometricMean';
    
CREATE AGGREGATE DQM_GEOMETRICMEAN (  p0 REAL )
RETURNS DOUBLE PRECISION
LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'thisjar:udf.GeometricMean';

CREATE AGGREGATE DQM_GEOMETRICMEAN (  p0 FLOAT )
RETURNS DOUBLE PRECISION
LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'thisjar:udf.GeometricMean';

CREATE AGGREGATE DQM_GEOMETRICMEAN (  p0 DOUBLE PRECISION )
RETURNS DOUBLE PRECISION
LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'thisjar:udf.GeometricMean';

CREATE FUNCTION DQM_POSITION_NTH (  p0 CHAR(128), p1 CHAR(128), p2 INTEGER )
RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'thisjar:udf.StringUtils.positionN';

CREATE FUNCTION DQM_POSITION_NTH (  p0 VARCHAR(128), p1 CHAR(128), p2 INTEGER )
RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'thisjar:udf.StringUtils.positionN';

CREATE FUNCTION DQM_POSITION_LAST (  p0 CHAR (128) )
RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'thisjar:udf.StringUtils.positionLast';

CREATE FUNCTION DQM_POSITION_LAST (  p0 VARCHAR (128) )
RETURNS INTEGER
LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'thisjar:udf.StringUtils.positionLast';

CREATE FUNCTION DQM_NTH_WORD (  p0 VARCHAR (128), p1 INTEGER )
RETURNS VARCHAR(32)
LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'thisjar:udf.StringUtils.nthWord';

CREATE FUNCTION DQM_RSSFEEDS (  p0 CHAR (128) )
RETURNS TABLE (  "title" VARCHAR(256), "description" VARCHAR(512),
                "link" VARCHAR(256), "guid" VARCHAR(128), "pubDate" VARCHAR(32))
LANGUAGE JAVA PARAMETER STYLE JAVA EXTERNAL NAME 'thisjar:udf.RssFeed.rssFeed';

CREATE FUNCTION DQM_MY_CONCAT(p0 VARCHAR(32), p1 VARCHAR(32))
RETURNS VARCHAR(72)
LANGUAGE SQL
PARAMETER STYLE SQL
READS SQL DATA
DETERMINISTIC
        RETURN p0 || '...' || p1;

END INSTALL"

}