Vertica and IBM Cognos: Tips and Techniques

Note This document pertains to Cognos versions 10.x and earlier. In later versions of Cognos, we recommend using JDBC instead of ODBC connections to Vertica.

About Vertica Tips and Techniques

Vertica develops Tips and Techniques to provide you with the information you need to use Vertica with software that our technology partners create. This document provides guidance using one specific version of Vertica and one version of the partner product. While other combinations are likely to work, other versions may not have been tested. This document provides guidance using the latest versions of Vertica and IBM Cognos as of January, 2016.

IBM Cognos Overview

IBM Cognos is a business intelligence tool that includes both client and server components. The client tool is a metadata modeling tool called Cognos Framework Manager. This document assumes readers are familiar with both IBM Cognos and Vertica.

IBM Cognos to Vertica Connection Options

You can deploy IBM Cognos BI applications in Windows, Linux, and UNIX operating systems, all of which support access to Vertica. IBM Cognos recommends that applications use the Dynamic Query engine to connect to Vertica using the JDBC driver. Applications that do not use the Dynamic Query engine must connect to Vertica using the ODBC driver.

For more information about which versions of IBM Cognos are compatible with Vertica, see the Cognos BI documentation.

Download and Install Vertica Client Drivers

Before you can connect to Vertica using IBM Cognos, you must download and install the Vertica client package. This package includes the ODBC and JDBC drivers that IBM Cognos uses to connect to Vertica.

Download the Vertica Client Driver

  1. Go to the Vertica Client Drivers page.
  2. Download the version of the Vertica client package that is compatible with the architecture of your operating system and Vertica server version. For a 32-bit operating system, download the 32-bit version and for a 64-bit operating system, download the 64-bit version.

    Note Vertica drivers are forward compatible, so you can connect to the Vertica server using previous versions of the client. For more information about client and server compatibility, see Client Driver and Server Version Compatibility in the Vertica documentation.

Install the Vertica Client Driver

Based on the client package you downloaded, follow the steps for installing the client drivers in the Vertica documentation

Connect IBM Cognos to Vertica

Create the connection from IBM Cognos to Vertica with the Cognos Framework Manager or Cognos Connection.

Note The OBDC driver only supports a 32-bit connection.

Connect Using the ODBC Driver

  1. Navigate to Start > Control Panel > Administrative Tools > Data Sources (ODBC)
  2. From the New Data Source wizard, choose ODBC from the Type list box.
  3. Click Next, and enter the data source name (DSN) for Vertica.
  4. Select the Unicode ODBC box.

For more information, see Creating an ODBC Data Source Name (DSN) in the Vertica documentation.

Connect Using the JDBC Driver

To use the JDBC driver, Cognos 10.2.1 must have Fix Pack version 1 or later installed. You can check the Cognos version and build in the cmplst.txt file. The following sample build line shows that the version is 10.2.

LICENSE_BI_UPDATER_version=LICENSE_BI_UPDATER-AW64-ML_RTM-
 10.2.5002.0-0 XQE_version=XQE-AW64-ML-RTM-10.2.5001.90-0

Follow these steps to connect to Cognos using the JDBC Driver:

  1. From the New Data Source wizard, choose JDBC from the Type list box.

ConnectUsingJDBC.png

  1. Click Next and enter your connection string and log-in information as shown in the following graphic

 Connectusingjdbcconfiguration.png

Vertica and the Cognos Expression Editors

The Cognos expression editors (Report Studio and Framework Manager) define calculations and filters and provide assistance to report authors. By default, Cognos provides a set of entries for some of the Vertica functions.

IBM Cognos BI provides scalar and aggregate expressions that Cognos automatically translates into applicable expressions that Vertica supports. While expressions can include references to Vertica-specific functions, IBM Cognos recommends using the Cognos expressions wherever possible.

The following graphic shows how you can add a new calculation to an expression.

Calculations.png

There are two ways a user could implement a function that adds months to a specific date. You can use the Cognos Out of Box (OOB) functions or the Vertica ADD_MONTHS function directly. The following is an example that uses the Cognos OOB function. Note that in this usage, an underscore precedes the function name:

_ADD_MONTHS ([Start_Date],4)

The following is an example that uses the Vertica syntax:

ADD_MONTHS ([Start_Date],4)

Depending on which function format you use, there are differences in the SQL generated. The following SQL shows both formats:

SELECT distinct "SFDC_CAMPAIGN_DIM3"."Start_Date" as "Start_Date" ,
CAST("SFDC_CAMPAIGN_DIM3"."Start_Date" + (INTERVAL '1' month *
FLOOR(4)) AS DATE) as "Cognos_Generic_add_mths" ,
add_months("SFDC_CAMPAIGN_DIM3"."Start_Date", 4) as
“Vertica_add_months_native”
from "VMart"."MARKETDB"."SFDC_CAMPAIGN_DIM" "SDFC_CAMPAIGN_DIM3

If the Cognos Server does not push the SQL to Vertica, use the Vertica native syntax to improve performance. You can check vertica.log to verify the server is pushing the SQL data to Vertica. At runtime, the Cognos engine attempts to send the expression to Vertica to resolve it, while the engine prepares a SQL statement.

You can use custom Vertica UDX functions in the Cognos Expression Editor. You can also update the functions listed in the dialog box by modifying various filelist*.xml files. See the Cognos Administration and Security Guide for more details.

Cognos Dynamic Query Mode for JDBC

Dynamic Query Mode is an enhanced Java-based query mode that offers capabilities that include query optimization and security-aware caching.

To switch the query mode for a package you publish to IBM Cognos, change the query mode setting and republish the package. Packages can only contain supported DQM data sources. If your package includes unsupported data sources, you receive an error message when you attempt to publish the package.

The following graphic shows what the connection looks like if you have published the package using DQM. To use DQM, you must change the package settings before you publish the model.

cognos6.png

The following graphic shows the settings of the Cognos Package if you have published the package supporting the DQM.

cognos7.png

Note Cognos DQM is one of the query engines Congos uses and customers should conduct proper testing of their packaging and reports before migrating an existing environment from ODBC to JDBC. You can use the Lifecycle Manager to migrate your environment.  

Known Issues and Limitations 

Upgrade Cognos Framework Manager Models: Vertica Does Not Exist Error

When upgrading Cognos FM models, you could see the following error when running a query subject:

UDA-SQL-0107 A general exception has occurred during the
 operation "prepare". ERROR: Database "Vertica" does not exist. RSV-SRV-0042

To correct this issue, change the FM package Query Processing from Database Only to Limited Local.

cognos8.png

For more information, see the IBM Knowledge Center.

If the error message continues to appear, remove the schema name as well, as shown in the following:

cognos9.png


The best practice for resolving this issue is to use the ColumnsAsChar = 1 setting.

Dynamic Query Mode: Could Not Load Driver Error

If you receive an error that DQM could not load the driver, there are two possibilities for such an error:

  1. The error indicates that Cognos BI cannot load the Vertica JDBC driver. To fix this issue, verify that you have placed a copy of the Vertica JDBC driver into the folder that IBM Cognos BI requires.

Or

  1. The error indicates that the version of Cognos BI you are using does not support Vertica. To fix this issue, upgrade to a more recent release of IBM Cognos that states it supports Vertica. 

National Character Literals Issue or Failure

  1. There are situations where Cognos cannot complete the Framework Manager model in a timely manner, causing the error:
  2. UDA-SQL-0458 PREPARE failed

    This section describes actions you can take to resolve the error if the FM model is not accessible. In the following sample Cognos report, a data item in the report studio contains a simple CASE statement.

    cognos14.jpg

    When you run the report, you receive the following error:

    UDA-SQL-0458 PREPARE failed because the query requires local processing of the data. The option to allow local processing has not been enabled.UDA-SQL-0477 A literal value is not supported by the database. This operation requires local processing of the data.RSV-SRV-0042 Trace back:RSReportService.cpp(722): QFException: CCL_CAUGHT …

    You can do one of the following as a workaround for this issue:

    1. Modify the report query property called Processing. Change the value to Limited Local so that Cognos processes the report on the Cognos server. Depending on the complexity of the query, and the amount of data returned, completion of the report could take longer than running the query only on Vertica.

      Note The SQL statement contains the N’Roamers’ syntax, which causes the Cognos server, not Vertica, to execute the SQL statement.

      => SELECT
      COUNTRY_NVARCHAR.COUNTRY_CODE_as COUNTRY_CODE,
             case when COUNTRY NVARCHAR.COUNTRY EN is NULL then N'Roamers'
      else COUNTRY_NVARCHAR.COUNTRY_DA 
             end_as Data_Item1, 
             COUNTRY_NVARCHAR.COUNTRY_EN_as COUNTRY_EN 
      from
             VMart_Demo.VMart.GOSALES.COUNTRY COUNTRY_NVARCHAR         
      			XX:NewSIZE=384m –XX:MaxPermSize=128m %DEBUG_OPTS%
    2. Or

    3. In the Properties section of the query, change the query to use Pass-Through mode.

  3. NChar data type is supported up to 40000 characters. If the NChar range exceeds 40000 characters, you receive the following error when you run the report:
  4. RQP-DEF-0177 An error occurred while performing operation 'sqlOpenResult' status='-9'

    If this error occurs, Vertica records the following message in the log:

    UDA-SQL-0107 A general exception has occurred during the operation "declare".[Microsoft][ODBC Driver Manager] Invalid string or buffer lengthRSV-SRV-0042

Appendix A: Non-Windows ODBC Configuration File Examples

Configuring ODBC on a non-Windows operating systems requires some environment variable settings particular to the operating system. This section identifies the most pertinent installation differences between Windows and non-Windows operating systems. Click here for an outline of requirements for setting the encoding.

Cognos 8.x and 10.x support unixODBC or iODBC as non-Windows ODBC driver managers. When @UNICODE is enabled, the query engine invokes the Windows ODBC functions, and passes strings encoded as follows:

  • unixODBC on Linux – UCS-2
  • iODBC on Solaris, Linux, HPUX – UTF-32
  • iODBC on AIX – UTF-16

You must verify that the driver is appropriately configured for encoding. The default driver manager is iODBC, which defaults to UTF-32 on Linux.

If you use unixODBC, you must configure the VERTICAINI global environment variable to tell the driver the format of the Unicode strings (UTF-16). The SQL utility is not impacted because it is not a Unicode application.

AIX Example

The AIX example is an iODBC example, so the vertica.ini file does not need to include a setting for the DriverManagerEncoding setting.

odbc.ini file:

[ODBC]
InstallDir=
Trace=0
TraceFile=/tmp/test_New5.1_unixODBCtrace.log
#TraceAutoStop=0

[ODBC Data Sources]
CognosCertDB_6.1.2 = Vertica ODBC Driver
[CognosCertDB_6.1.2]
Description      = Test VerticaSQL ODBC driver
Driver           =
/home/opt/cognos/c10.2.0_FP1/bin/verticaodbcw_6.1.3.so
Debug            = 0
Database         = CognosCertDB
Servername       = 
UserName         = username
Password         = mark
Port             = 5436
SuppressWarnings = 1
TraceFile=/tmp/test_unixODBCw.log
Trace            = 0
ColumnsAsChar=1

vertica.ini file:

[Driver]
ODBCInstLib=/home/opt/cognos/c10.2.1_FP1/bin/libiodbcinst.so
errorMessagesPath=/home/data/cognos_suite/downloads/drivers/Cognos_10.2.1_FP1/lib/ LogPath=/tmp/log
LogNameSpace=
logLevel=6

 

Path Settings:

Export
PATH=/home/opt/cognos/c10.2.1_FP1/bin64:/home/opt/cognos/c10.2.1_FP1/bin:/usr/java6_64/bin:$PATH
export JAVA_HOME=/usr/java6_64/
export ODBCINI=/home/opt/cognos/c10.2.1_FP1/bin/.odbc.ini
export ODBCINSTINI=/home/opt/cognos/c10.2.1_FP1/bin/.odbcinst.ini
export LC_ALL=en_US.UTF-8
export
LIBPATH=/home/opt/cognos/c10.2.1_FP1/bin:/home/opt/cognos/c10.2.1_FP1/bin64:$LIBPATH
export VERTICAINI=/home/opt/cognos/c10.2.1_FP1/bin/Cognos.ini

Solaris Example

The Solaris example is an iODBC example, so the vertica.ini file does not need to include a setting for the DriverManagerEncoding setting.

odbc.ini file:

[ODBC]
InstallDir=
Trace=1
Debug=1
TraceFile=/tmp/ODBCtrace 612.log
DebugFile=/tmp/ODBCdebug 419.log
#TraceAutoStop=0

[ODBC Data Sources]
CognosCertDB_6.1.0 = Vertica ODBC Driver
CognosCertDB_6.0.1 = Vertica ODBC Driver
CognosCertDB_6.1.2 = Vertica ODBC Driver
TechSupportDSN = Vertica ODBC Driver
LocalDSN = Vertica ODBC Driver

[CognosCertDB 6.1.2]
Description      = Test VerticaSQL ODBC driver
Driver           = /opt/cognos/c10.2.1 FP1/bin/verticaodbcw 6.1.3 so
Database         = CognosCertDB
Servername       = 
UserName         = username
Password         = mark
Port             = 5436
ColumnsAsChar=1

vertica.ini file:

[Driver]
ODBCInstLib=/iODBC DM/iODBC/lib/libiodbcinst.so
ErrorMessagesPath=/data/cognossuite/downloads/drivers/Cognos_10.2.1FP1/lib
LogPath=/tmp/log
logLevel=1

Path Settings:

JAVA_HOME=/Softwares/jrel.6.0 31; export JAVA HOME
PATH=/usr/bin:/opt/cognos/c10.2.1_FP1/bin64:$PATH:/opt/cognos/c10.2
.1 FP1/bin:$JAVA_HOME/bin:usr/X11/bin:.:/data/software/wget-
1.10.2/src/; export PATH
LD LIBRARY PATH-/opt/cognos/c10.2.1_FP1/bin64:/opt/cognos/c10.2.1_F
P1/bin:/opt/cognos/c10.2.1_FP1/cgi-
bin:/usr/lib:/usr/local/lib:/usr/lib64:/root/releases/lib:/Software
/DM iodbc/iodbc/lib:/usr/lib:/opt/local/lib:/usr/local; export
LD_LIBRARY_PATH
LC_ALL=en_US.UTF-8; export LC_ALL
ODBCINI=/opt/cognos/c10.2.1_FP1/bin/.odbc.ini; export ODBCINI
ODBCINSTINI=/opt/cognos/c10.2.1_FP1/bin/.odbcinst.ini;
ODBCINSTINI
VERTICAINI=/opt/cognos/c10.2.1_FP1/bin/Cognos.ini; export VERTICAINI
CLASSPATH=$CLASSPATH:/Softwares/jre1.6.0_31/lib/ext/bcprov-jdk14-145.jar

Linux Example

odbc.ini file:

[ODBC]
InstallDir=
Trace=1
Debug=1
TraceFile=/tmp/test_unixODBCtrace.log
#TraceAutoStop=0

[ODBC Data Sources]
CognosCertDB_6.1.0 = Vertica ODBC Driver
CognosCertDB_6.0.1 = Vertica ODBC Driver
CognosCertDB_6.1.2 = Vertica ODBC Driver
TechSupportDSN = Vertica ODBC Driver
LocalDSN = Vertica ODBC Driver

[CognosCertDB 6.1.2]
Description      = Test VerticaSQL ODBC driver
Driver           = /opt/cognos/c10.2.1 FP1/bin/verticaodbcw 6.1.2. so
Database         = CognosCertDB
Servername       = 
UserName         = username
Password         = mark
Port             = 5436
Trace=1
TraceFile=/tmp/testDSN unixODBCw.log
ColumnsAsChar=1

vertica.ini file:

[Driver]
ODBCInstLib=/root/releases/DriverMgr/unixODBC 2214 i386/lib/libodbcinst.so
ErrorMessagesPath=/data/cognossuite Linux Final/downloads/drivers/ Cognos_10.2.1FP1/lib
LogPath=/tmp/log
LogNameSpace=
logLevel=6
DriverManagerEncoding=UTF-16

Path Settings:

export JAVA_HOME=/usr/java/jdk1.7.0 45
export
PATH=/usr/bin:/opt/cognos/c10.2.1/bin:$PATH:/opt/cognos/c10.2.1/bin
64:$JAVA_HOME/bin:usr/X11/bin:.
LD LIBRARY PATH=/opt/cognos/c10.2.1/bin:/opt/cognos/c10.2.1/bin64:/
opt/cognos/c10.2.1/cgi-
bin:/usr/lib:/usr/local/lib:/usr/lib64:/root/releases/lib:/root/rel
/eases/DriverMgr/unixODBC 2214 i386/lib
export LD LIBRARY PATH
export ODBCINI=/opt/cognos/c10.2.1/bin/odbc.ini
export ODBCINSTINI=/opt/cognos/c10.2.1/bin/odbcinst.ini
export VERTICAINI-/opt/cognos/c10.2.1/bin/Cognos.ini

Appendix B: Cognos Unicode ODBC

Make sure the Unicode setting is enabled for the data source connection in the IBM Cognos Administration dialog box. The following is an example of an ODBC connect string:

;LOCAL;OD;DSN=partnerdb71;@ASYNC=0@0/0@UNICODE@COLSEQ=


If you have an ODBC DSN, you do not need an ODBC connect string.

cognos16.png

When configured properly, multilingual characters should also show up in the Framework Manager. For more information about some of the differences in data types between Oracle and Vertica, see Data Type Mappings in the Vertica documentation.

Appendix C: Cognos Transformer ODBC Setting

When using the Cognos Transformer to build cubes you may see an error that looks similar to the following:

EXIT SQLExecute with return code -1 (SQL_ERROR)
HSTMT 0x00B18960

DIAG [S1000] [Vertica][VerticaDSII] (20) An error occurred during query execution: out of memory for query result
(20)

WCHAR * 0x0018F55C [ 101] "[Vertica][VerticaDSII] (20) An error occurred during query execution: out of memory for query result\ a"

This happens when a large amount of data is brought back to the client.  When using the transformer, you need to increase the ResultsBufferSize setting to something that is larger than the default of 128KB and lower than 3GB, since the transformer is a 32-bit product.  Depending on the amount of memory on the transformer machine, consider setting ResultsBufferSize to 1,000,000.

For More Information

IBM Cognos

IBM Big Data and Analytics

Vertica Community Edition

Vertica Documentation

Vertica User Community