Date: September 16, 2008

Abstract: The Teradata JDBC Driver Connection Pooling facility can greatly improve application performance. The steps used to enable it for the Teradata JDBC Driver will vary with the application server being used. This paper outlines the steps and parameters required to use the Teradata JDBC Driver with SAP Web Application Server and SAP BW 3.5 UD Connect Integration.

Table of Contents

1. Introduction

2. SAP Web Application Server 6.40

3. SAP BW 3.5 UD Connect Integration

4. Troubleshooting

Appendix A: SAP Web Application Server Code Samples

1. Introduction

1.1. Assumptions

1.2. Purpose and Organization

1.3. Version Information

The application server versions used are:

• SAP Web Application Server 6.40

Other tools covered are:

• SAP BW 3.5

1.4. Supported Platform Matrix

1.5. C/C++ and Java Application Sharing of TeraGSS Security Configuration

1.6. Overview - Data Source in SAP Web Application Server 6.40

This section provides a quick overview of Data Source configuration in SAP WAS 6.40. More details are provided in subsequent sections.

The Teradata JDBC driver can be used as an SAP WAS 6.40 data source. Within SAP WAS 6.40, the use of a DataSource object, instead of DriverManager, is the preferred means of connecting to a data source.

To use the DataSource interface within SAP WAS 6.40, the JDBC Connector driver definition and JDBC Data Source will need to be configured for Teradata JDBC type 4 driver.

When defining the JDBC Connector driver through SAP Visual Administrator, the following Teradata JDBC driver jar files are needed.

• terajdbc4.jar
• tdgssconfig.jar

When configuring the JDBC Connector Data Source through SAP Visual Administrator, the following Teradata-specific configuration parameters are needed.

1.7. Overview - Data Source in SAP BW 3.5 UDC Integration

To use Teradata JDBC driver with SAP BW 3.5 UDC intetgration, the Java Connectors will need to be configured through Connector Container section and RFC destination will need to be defined through JCo RFC Provider section in the Visual Administrator,

When configuring the Java Connectors in the Visual Administrator, the following Teradata-specific configuration parameters are needed:

2. SAP Web Application Server 6.40

The recommended way to create the data source within SAP is to use the graphical interface via SAP Visual Administrator.

2.1. Data Source via Visual Administrator

2.1.1. Prerequisites

Prior to define JDBC Connector driver in Visual Administrator, the following Teradata JDBC Driver jar files need to be available on the server:

• terajdbc4.jar
• tdgssconfig.jar

Some earlier Teradata JDBC driver versions contain the TeraGSS security library's user configuration file "TdgssUserConfigFile.xml" instead of the above tdgssconfig.jar file. When C/C++ and Java applications are deployed to the same physical machine, the Java applications can be configured to use this user configuration file. However, SAP Web Application Server 6.40 doesn't support the specification of a directory on the classpath, this TeraGSS user configuration file can only be shared by C/C++ and Java applications indirectly, and an extra step must be performed to enable this indirect sharing.

A jar "update" command must be executed to take the TeraGSS user configuration file from the TeraGSS directory and to put it into the tdgssconfig.jar file:

jar uvf tdgssconfig.jar TdgssUserConfigFile.xml

2.1.2. Start the Administration Tool

The Administration Tool of the SAP J2EE Engine (Visual Administrator) is a GUI interface that allows the users to adminstrater Java services within a cluster and provides remote monitoring and management of these services.

For the user who will run the administration tool (for example, <SAPSID>adm), make sure that SAPINST_JRE_HOME environment variable contains the path to the Java Development Kit (JDK) of the J2EE Engine.

Note: On UNIX, make sure that the DISPLAY environment variable is set to <host_name>:0.0, where <host_name> is the host on which the administration tool will be displayed.

Enter the following command to start the administration tool:
UNIX:
/usr/sap/<SAPSID>/<Instance_Name>/j2ee/admin/go
Windows:
<driver>:\usr\sap\<SAPSID>\<Instance_Name>\j2ee\admin\go.bat

The "J2EE Engine - Administration" screen with the dialog box "Connect to J2EE Enngine" appears.

For SAP J2EE standalone system, choose "Connect" to use the "Default" login and enter the password for the Administrator user of the J2EE engine.

For SAP J2EE add-in system, the "Default" login can not be used. The following steps need to be performed:

• Choose "New"
• Enter a display name and choose "Direct Connection to a dispatcher Node"
• Choose "Next"
• Enter the following information:
  • User Name: J2EE_ADMIN
  • Host: <host_name> of the J2EE Engine
  • Port: 5<Instance_Number_of_SAP_J2EE_Engine>04
• Choose "Save" and connect with the new login account by choosing "Connect"
• Enter password for the J2EE_ADMIN user and choose "Connect"

2.1.3. Define the JDBC Connector Driver

After connecting to the J2EE Engine, expand "Cluster", and then "Server", and then "Services".
Scroll down and click "JDBC Connector". On the right pane, click on "Drivers", and then click the paper icon to create a new driver definition.

In the dialog box, enter the name of the JDBC driver definition, and click "OK".

Navigate to the directory where you store the JDBC driver jar files, and select a .jar file, and click "OK".

A confirmation screen will appear asking "Do you want to select other files ?", click "Yes" to add more jar files.

All the Teradata JDBC driver jar files are needed for this step. Make sure you go back to add more jar files after each selected jar file. After finishing all the jar files, click "No". Now the new JDBC Connector driver is created with the specified jar files.

2.1.4. Configure the JDBC Connector DataSource

Now the JDBC Connector driver has been configured as shown below.

On the right pane of the "JDBC Connector" section in the Visual Administrator, click on "DataSources", and then click the paper icon to create a new DataSource definition.

The new DataSource definition template includes 4 tabs: "Main", "Additional", "DB Initialization", "Monitoring".

The following inputs are needed for the "Main" tab:

Some aliases can be added for referencing this DataSource.

On the "Additional" tab, make sure "Vendor SQL" is selected for "SQL Engine".

On the "DB Initialization" tab, SQL statements can be added and executed to verify the DataSource connection.

After the above configurations are done for the new DataSource, click the save icon to save the DataSource configuration. The "Application Name" on the "Main" tab will be automatically generated.

2.2. Accessing the DataSource via a Java Servlet

A Java servlet can access a data source to get pooled connections.

2.2.1. DataSource.getConnection() vs. DataSource.getConnection(username,password)

With SAP, a JDBC Connector driver is defined to specify information about the JDBC driver. After the JDBC Connector driver has been defined, then one or more DataSources can be configured for the JDBC driver. During the process of defining each DataSource, the driver access parameters can be configured for each DataSource.

Generally the user and password for the database need to be entered on the "Main" tab. The DataSource will use this default user/password pair to access the database via the driver when an application calls DataSource.getConnection(). The following cases illustrate how these default user/password pair is used:

• If "user" and "password" parameters are defined in the DataSource definition, then they serve as the default values when an application calls DataSource.getConnection().
• If "user" and "password" parameters are defined in the DataSource definition, then the application can override those default values by calling DataSource.getConnection(username,password).
• If "user" and "password" parameters are not defined completely (but "User" input can not be empty), then an application must call DataSource.getConnection(username,password) in order to connect to the database.

In other words, user and password values must be specified either as default parameters in the DataSource definition, or as arguments to DataSource.getConnection(username,password), or both; and when both are specified, then the arguments to DataSource.getConnection(username,password) override the "user" and "password" Custom Properties defined for a Data Source.

NOTE: the User parameter input in the DataSource definition can not be empty, otherwise the following exception will be generated:

java.rmi.RemoteException: Cannot deploy application sap.com/JDBCConnector_<DataSourceName>.xml

A SQLException will be thrown if either the password value is not specified in DataSource definition (user value can not be empty), or user and password values are not specified as arguments to DataSource.getConnection(username,password).

The exception will differ depending on the combination of Teradata JDBC Driver version and Teradata Database release.

• Message: [Teradata Database] : The UserId, Password or Account is invalid.
• SQLState: 08003
• Error code: 0

• Message: [Teradata Database] : The parcel stream is invalid.
• SQLState: 08003
• Error code: 0

A SQLException will be thrown if user and password values are specified, but the specified user or password are invalid. Note that invalid user and password values specified as arguments to DataSource.getConnection(username,password) will override correct user and password values specified in Custom Properties. When DataSource.getConnection(username,password) is called but authentication fails, SAP does not "fallback" to use the values specified in Custom Properties.

The exception will differ depending on the combination of Teradata JDBC Driver version and Teradata Database release.

• Message: [Teradata Database] : Invalid password.
• SQLState: 08003
• Error code: 0

• Message: [Teradata Database] : User identification is not authorized.
• SQLState: 08003
• Error code: 0

2.2.2. Using the DataSource

The servlet is accessed via a URL similar to the following:

In the example, the username, password and datasource name are all specified in the URL parameters:

Note that user-defined variables are in italics.

Accessing the URL Parameters

Creating the Initial Naming Context

Lookup the Data Source

Use the Data Source to get Connections

At this point, the connection obtained behaves the same way as one obtained from the Driver Manager.

See Appendix A for a complete Application.

2.3. Performance Parameters

The Connection Pooling properties can be configured through the "Additional" tab in the DataSource definition window. After DataSource is defined and saved, its parameters and properties can be modified and these new changes can be saved.

The following parameters are related to the DataSource performance:

Initial Connections - generally use the default value (0).

Maximum Connections - too small of a value here can cause users of the pool to waste time waiting for someone to release a connection.

Maximum Time to Wait for Connection - generally use the default value (60).

Expiration - specify if the connections in the pool will be expired and removed.

Connection Lifetime - specify how many seconds a connection may stay in pool before it is closed.

Cleanup Thread - schedule a thread that periodically cleans up unused connection objects at a regular interval that you set in seconds.

Generally the Java applications will close the connections and return the connections back to the connection pool after finishing all the queries. However, some applications and clients, such as Business Explorer, don't close the connections they have acquired and/or other resources such as ResultSets when returning the connections to connection pool, then sometimes issues will occur, such as exceptions like "[Teradata DBMS]: Response limit exceeded". This exception when more than 15 time queries are executed within the ResultSet in the connection, but the ResultSet is not closed when returning to connection pool. These connections that are not closed and/or have unreleased resources like ResulSets should be regularly cleaned up, and the three parameters "Expiration", "Connection Lifetime", and "Cleanup Thread" can be configured for this purpose.

3. SAP BW 3.5 UD Connect Integration

SAP BW 3.5 introduces BI Java Connector that is a resource adapter compliant with the Java Connector Architecture (JCA) and is deployed into SAP NetWeaver's J2EE server. After configuring the JDBC driver, Java Connector and RFC provider on the J2EE Visual Administrator, SAP BW system can access data from Teradata to: load Master Data directly (without having to land a flat file), extract data to build a Basic Cube, or query Teradata through queries written against a Remote InfoCube.

SAP BW 3.5 introduces BI Java Connector that is a resource adapter compliant with the Java Connector Architecture (JCA) and is deployed into SAP NetWeaver's J2EE server. After configuring the JDBC driver, Java Connector and RFC provider on the J2EE Visual Administrator, SAP BW system can access data from Teradata to: load Master Data directly (without having to land a flat file), extract data to build a Basic Cube, or query Teradata through queries written against a Remote InfoCube.

There are 4 main components in the SAP BW system architecture system:

1. Business Explorer:
The Business Explorer (BEx) front-end provides for the end-user experience of either launching pre-built BW reports/queries or allowing interactive ad-hoc query capabilities from the Query Designer. Any of the certified BW front-end tools can be leveraged in this solution. The BEx front-end interfaces to the next component - the BW Server.

2. SAP BW Server:
The SAP BW Server houses the BW OLAP processor which interfaces to BW InfoCubes. InfoCubes are built from BW InfoObjects (Characteristics & Key Figures). Mapping BW InfoObjects to Teradata relational objects, and creating a DataSource and InfoSource for BW targets (Remote InfoCubes or Master Data) are performed in the BW Administrator (a UD Connect component also resides on the BW Server - a generic DataSource generator with an object mapping GUI to assist in creating these objects). Once the DataSource is created, it can be used to connect a Remote Info-Cube or to enable loading Master Data directly from Teradata. The BW Server interfaces to the next component "the Java Service" via an RFC mechanism.

3. UC Connect Java Components:
The Java component of UD Connect resides in the SAP Web Application Server. It is responsible for the communication between Data Sources and SAP BW. Data Sources and the Source Objects (i.e., in Teradata) are addressable via the BI Java Connectors. Via the SAP Java Connector (JCo), UD Connect provides stateless Session Beans to enable this communication. The Session Beans call Function Modules in BW via an RFC connection between the J2EE Engine and the BW Server. The JDBC BI Connector interfaces to Teradata via the Teradata Driver for the JDBC Interface.

4. Teradata Data Warehouse:
The Teradata Data Warehouse processes the SQL request, utilizing Aggregate Join Indexes if possible. After execution of the SQL Statement, the answer set is streamed back to the J2EE Engine, and then rendered in the end-user front-end tool of choice (for a Remote InfoCube target), or loaded into BW Master Data repository.

3.1. Assumptions and Prerequisites

The following assumptions and prerequisites are required for configuring and using SAP BW UD Connect system:

1. SAP NewWeaver 4.0 ABAP and Java systems have been installed completely

2. SAP BW 3.5 system is available

3. SAP NetWeaver 04 system has been upgraded with Support Package Stack 13 or higher

4. Prior to define JDBC Connector driver in Visual Administrator, the following Teradata JDBC Driver jar files need to be available on the server:

• terajdbc4.jar
• tdgssconfig.jar

Some earlier Teradata JDBC driver versions contain the TeraGSS security library's user configuration file "TdgssUserConfigFile.xml" instead of the above tdgssconfig.jar file. When C/C++ and Java applications are deployed to the same physical machine, the Java applications can be configured to use this user configuration file. However, SAP Web Application Server 6.40 doesn't support the specification of a directory on the classpath, this TeraGSS user configuration file can only be shared by C/C++ and Java applications indirectly, and an extra step must be performed to enable this indirect sharing.

3.2. Teradata Data Modeling

Teradata V2R5.1 or greater is recommended due to potential leveraging of Aggregate Join Indexing to maximize the OLAP user experience. Additionally, it will be beneficial to utilize the Join Elimination feature due to UD Connect requirements to connect a BW DataSource to only one database object (table or view).

3.2.1. Database Modeling

Prior to configure and use SAP BW 3.5 UD Connect system, some basic BW data model will need to be prepared and documented to include the following items: dimensions, BW InfoObject technical names, types, datatypes, lengths, relationships of all objects to each other, etc.

Corresponding objects in Teradata data warehouse will need to be identified and modeled appropriately to map to each BW InfoObject. Completing the object mapping is highly recommended before proceeding.

The SAP BW UD Connect features limit the use of one database object (table or view) per BW DataSource. Therefore, creation of a set of database views on top of the Teradata relational datasource will be needed.

3.2.2. InfoCube DataSources

Since only one database object can be assigned to a BW DataSource, unless your database schema on Teradata is completely denormalized into one table, a view may be needed to pre-join any dimension tables to the fact table.

If dimension tables are not used to model hierarchical relationships, and only used to model descriptive information for key values, joining these in a view may not be necessary. You specific data model will dictate this decision.

A possible view to model would include all Characteristic InfoObjects (keys) from all dimensions plus all Key Figure InfoObjects included in the desired InfoCube. The name of this view or table will be selected for the "UD Connect Source Object" parameter used in DataSource creation.

Based on your particular model, it may be important to implement soft R.I. to enable the Join Elimination feature in Teradata, given the requirement to pre-join structures in a view for presentation to UD Connect.

An Aggregate Join Index (AJI) structure mirroring the denormalized view structure may be desireable. See section below on AJIs for more details

3.2.3. Master Data DataSources

It may be useful to construct a set of views of Master Data that mirrors the Communication Structure for the data target, and use this view in the "UD Connect Source Object" element when creating the DataSource. The required objects will be driven by the user's particular implementation, however, the following are recommended for Text Master Data and Attribute Master Data:

For Text Master Data, usually the format for the Communication Structure is the Characteristic key followed by one or more of the InfoObjects associated with Text Master Data descriptions. If your Characteristics are 'language-dependent' or time-dependent, you may require additional fields (Language Key, Date From/To) as well. Data lengths between the source object and Text Descriptions should be consistent.

Specifically for Text Master Data loading: Match the text description length in the Teradata table (possibly with a CAST statement in a view) with the corresponding BW Text field length definition(s).

InfoObject lengths for Text Master Data are as follows:

For Attribute Master Data, a possible format is the Characteristic InfoObject key followed by all assigned Attributes (keys). If you require 'time-dependent' Attributes, additional fields may be required. Examples for Texts and Attributes are included in the step-by-step instructions for Creating a DataSource.

3.2.4. Aggregate Join Indices

The mechanism for creating and deploying Aggregate Join Indices (AJIs) in dimensional schema environments often yields significant performance improvements, and should be considered when better performance is important in the system.

An aggregate join index is a database object created using the CREATE JOIN INDEX statement and specifying one or more columns that are derived from an aggregate expression. This feature is similar to other vendor's implementation of "Materialized Views"; however Teradata AJIs cannot be directly addressed by the end user. The Teradata Optimizer determines if it is a more cost-effective access path.

The primary function of an Aggregate Join Index is to provide the Teradata Optimizer with a performant, cost-effective means for satisfying any query that specifies a frequently made aggregation operation on one or more columns. In other words, Aggregate Join Indexes permit you to define a persistent summary table without violating the normalization of the database schema. This allows a join index to precompute an aggregate value that would otherwise potentially require a table scan and sort operation.

Aggregate join indexes can be especially helpful for queries that roll up values for dimensions other that the primary key dimension, which would otherwise require re-distribution. An aggregate join index can be used to cover aggregate queries that only consider a subset of groups contained in the join index or have more join tables than the join index. In order to allow the aggregate join index to be used in this way, its definition must satisfy the following conditions:

• The grouping clause must include all columns that are specified in the grouping clause of the query
• All columns in the query WHERE clause that join to tables not in the aggregate join index must be part of the join index definition

Note: Referential Integrity (can be Soft, Hard or Batch) may be required in your implementation to facilitate the Optimizer choosing the AJI, especially if Dimension Tables are included in the Join Index definition. Utilizing Soft RI enables the "Join Elimination" feature which allows a "Broad AJI" to cover queries that reflect a subset of the AJI.

Your DDL should contain a REFERENCES clause similar to this:
FOREIGN KEY ( SubClassID ) REFERENCES WITH NO CHECK OPTION RDBENCHMARK.SUBCLASS_V ( SubClassID ))
Also, be sure to make all relationship columns - both PK and FK columns in every table in the join path defined as NOT NULL.

Sample AJI Syntax (using the SNOWFLAKE Data Model example):

CREATE JOIN INDEX rdbenchmark.x_Broad2_attr_ji ,NO FALLBACK ,CHECKSUM = DEFAULT AS
SELECT COUNT(*)(FLOAT, NAMED CountStar ),rdbenchmark.item.ItemID ,
rdbenchmark.color_v.ColorID ,rdbenchmark.size_v.SizeID ,rdbenchmark.subclass_v.SubClassID ,
rdbenchmark.class_v.ClassID ,rdbenchmark.department.deptid ,
rdbenchmark.the_week_v.WeekID ,rdbenchmark.the_month_v.MonthID ,
rdbenchmark.the_quarter_v.QuarterID ,rdbenchmark.the_year_v.YearID ,
rdbenchmark.location.LocID ,rdbenchmark.district_v.DistID ,
SUM(rdbenchmark.sales_history.ItemQty )(FLOAT, NAMED ItemQty ),
SUM(rdbenchmark.sales_history.ItemRev )(FLOAT, NAMED ItemRev ),
SUM(rdbenchmark.sales_history.ItemCost )(FLOAT, NAMED ItemCost )
FROM rdbenchmark.size_v ,rdbenchmark.color_v ,rdbenchmark.sales_history ,
rdbenchmark.item ,rdbenchmark.subclass_v ,rdbenchmark.class_v ,
rdbenchmark.department ,rdbenchmark.the_day_v ,rdbenchmark.the_week_v ,
rdbenchmark.the_month_v ,rdbenchmark.the_quarter_v ,rdbenchmark.the_year_v ,
rdbenchmark.location ,rdbenchmark.district_v
WHERE ((((((((((((rdbenchmark.sales_history.ItemID = rdbenchmark.item.ItemID )
AND (rdbenchmark.item.ColorID = rdbenchmark.color_v.ColorID ))
AND (rdbenchmark.item.SizeID = rdbenchmark.size_v.SizeID ))
AND (rdbenchmark.item.SubClassID = rdbenchmark.subclass_v.SubClassID ))
AND (rdbenchmark.subclass_v.ClassID = rdbenchmark.class_v.ClassID ))
AND (rdbenchmark.class_v.DeptID = rdbenchmark.department.deptid ))
AND (rdbenchmark.sales_history.DayDT = rdbenchmark.the_day_v.DayDT ))
AND (rdbenchmark.the_day_v.WeekID = rdbenchmark.the_week_v.WeekID ))
AND (rdbenchmark.the_week_v.MonthID = rdbenchmark.the_month_v.MonthID ))
AND (rdbenchmark.the_month_v.QuarterID = rdbenchmark.the_quarter_v.QuarterID ))
AND (rdbenchmark.the_quarter_v.YearID = rdbenchmark.the_year_v.YearID ))
AND (rdbenchmark.sales_history.LocID = rdbenchmark.location.LocID ))
AND (rdbenchmark.location.DistID = rdbenchmark.district_v.DistID )
GROUP BY rdbenchmark.item.ItemID ,rdbenchmark.color_v.ColorID ,
rdbenchmark.size_v.SizeID ,rdbenchmark.subclass_v.SubClassID ,
rdbenchmark.class_v.ClassID ,rdbenchmark.department.deptid ,
rdbenchmark.the_week_v.WeekID ,rdbenchmark.the_month_v.MonthID ,
rdbenchmark.the_quarter_v.QuarterID ,rdbenchmark.the_year_v.YearID ,
rdbenchmark.location.LocID ,rdbenchmark.district_v.DistID
PRIMARY INDEX ( ItemID ,ColorID ,SizeID ,SubClassID ,ClassID ,
deptid ,WeekID ,MonthID ,QuarterID ,YearID ,LocID ,DistID );

If AJIs are utilized, Teradata V2R5.1 at a minimum is highly recommended (although not required), due to Optimizer improvements and exploitation of the AJI structures.

In addition to AJIs, one might want to consider Partitioned Primary Index implementations. For example, use AJI down to week-level aggregation, but partition the fact table on date to resolve date-based queries. This keeps the AJI lean while exploiting other Teradata features.

AJIs are not mandatory for this solution, but using AJIs can significantly improve performance.

3.3. Java Connector and RFC Provider via Visual Administrator

3.3.1. Start the Administration Tool

The Administration Tool of the SAP J2EE Engine (Visual Administrator) is a GUI interface that allows the users to adminstrater Java services within a cluster and provides remote monitoring and management of these services.

For the user who will run the administration tool (for example, <SAPSID>adm), make sure that SAPINST_JRE_HOME environment variable contains the path to the Java Development Kit (JDK) of the J2EE Engine.

Note: On UNIX, make sure that the DISPLAY environment variable is set to <host_name>:0.0, where <host_name> is the host on which the administration tool will be displayed.

Enter the following command to start the administration tool:
UNIX:
/usr/sap/<SAPSID>/<Instance_Name>/j2ee/admin/go
Windows:
<driver>:\usr\sap\<SAPSID>\<Instance_Name>\j2ee\admin\go.bat

The "J2EE Engine - Administration" screen with the dialog box "Connect to J2EE Enngine" appears.

For SAP J2EE standalone system, choose "Connect" to use the "Default" login and enter the password for the Administrator user of the J2EE engine.

For SAP J2EE add-in system, the "Default" login can not be used. The following steps need to be performed:

• Choose "New"
• Enter a display name and choose "Direct Connection to a dispatcher Node"
• Choose "Next"
• Enter the following information:
  • User Name: J2EE_ADMIN
  • Host: <host_name> of the J2EE Engine
  • Port: 5<Instance_Number_of_SAP_J2EE_Engine>04
• Choose "Save" and connect with the new login account by choosing "Connect"
• Enter password for the J2EE_ADMIN user and choose "Connect"

3.3.2. Define the JDBC Connector Driver

After connecting to the J2EE Engine, expand "Cluster", and then "Server", and then "Services".
Scroll down and click "JDBC Connector". On the right pane, click on "Drivers", and then click the paper icon to create a new driver definition. The following procedures are basically the same as previous section for defining JDBC drivers.

In the dialog box, enter the name of the JDBC driver definition, and click "OK".

Navigate to the directory where you store the JDBC driver jar files, and select a .jar file, and click "OK".

A confirmation screen will appear asking "Do you want to select other files ?", click "Yes" to add more jar files.

All the Teradata JDBC Driver jar files are needed for this step. Make sure you go back to add more jar files after each selected jar file. After finishing all the jar files, click "No". Now the new JDBC Connector driver is created with the specified jar files.

Now the JDBC Connector driver has been configured as shown below.

3.3.3. Configure the Java Connector

In the Visual Administrator window, expand "Cluster", and then "Server", and then "Services".
Scroll down and click "Connector Container".
On the right pane, under "Connectors" tree, locate "sap.com/com.sap.ip.bi.sdk.dac.connector.jdbc" and expand it,
Click on the default Java Connector "SDK_JDBC" (its name is "BI_JDBC_CONNECTOR") to bring up the Java Connector configuration window.

The default Java Connector configuration template includes 5 tabs: "General", "Resource Adapter", "Managed Connection Factory", "Security", and "Drivers".

In the "Resource Adapter" tab, click "Add" to add the previously defined JDBC Connector driver to the "Loader References", as shown below.

Note: after the string "library:", the Loader Reference name must match the defined JDBC driver name, such as "library:terajdbc". Click OK.

Now the new library has been added into the Loader References:

Once the JDBC driver has been added in the Loader References, click "Managed Connection Factory" tab.

Then click "Properties" tab to display the Teradata specific configuration properties screen. Highlight each configuration property and enter user-specified value for the property key, and then click "Add" to make the changes.

The following configuration properties are needed for the "Properties" tab:

When all the changes for the configuration properties are completed, click "save" button to save the configurations.

SAP Java Connector system provides a smoke test to validate if these configurations for UD Connect are correct. The smoke test will test this default Java Connector "BI_JDBC_CONNECTOR" to verify the connectivity to the specified Teradata database system in the properties tab by reading all the tables in that database.

To run the smoke test, execute the following URL in the browser (server and port are the placeholders for your SAP system):
http://<server>:<port>/TJdbc/servlet/TestJdbc

Note: port number is like 5<Instance_Number_of_SAP_J2EE_Engine>00, such as 50000 when Instance_Number_of_SAP_J2EE_Engine is 00.

The following is an example output of the smoke test:

After the smoke test has successfully validated the default Java Connector configuration, this default Java Connector needs to be cloned based on the following procedures to be accessible during the mapping process in SAP BW system.

To clone the default Java Connector, highlight the "SDK_JDBC" connector in the Connector Container window, click the double paper icon to clone this connector.

The following pop-up screen appears asking the "resource adapter jndi name" for the new Java Connector being cloned for:

Make sure the jndi name starts with "SDK_" in order for it to visible during the mapping process in SAP BW system, such as SDK_BI_JDBC_CONNECTOR.

Now the Java Connectors have been configured as shown below.

3.3.4. Configure the RFC Provider in Visual Administrator

After configuring the Java Connectors in the Visual Administrator, an RFC destination must be registered to enable the communication between SAP BW server and the SAP J2EE engine.

An RFC is a protocol used by SAP that allows communication from SAP BW system to external data sources. The RFC server you define here will be the same RFC destination that will be defined on the BW system side as well. This allows the RFC when started to register itself to the SAP BW 3.5 system. The following describes the procedures for registering the SAP BW system as an RFC destination in the Visual Administrator.

In the Visual Administrator window, expand "Cluster", and then "Server", and then "Services".
Scroll down and click "JCo RFC Provider".
On the right pane, click "Runtime" tab, and a new RFC destination template is displayed under "Bundles" tab.
Enter all the needed parameters for both the "RFC destination" and "Repository" sections.

The following configuration parameters are needed for the "RFC destination" section:

• Program ID: <user-defined JCo Server ID>
• Gateway host: <gateway_host_name>
• Gateway service: <gateway_server_name>
• Number of processes(1..100): <number_of_servers_running_simultaneously>

Note: by default the Number of processes will be set to 20. This number is configurable to a maximum value of 100. To change this number click on the "Properties" tab in the "JCo RFC Provider" window, and highlight the value field of MaxProcesses and enter the Number of processes you would like to set. Click "Update" to add the changes. Also the MaxConnections can be set to a maximum value of 100. The MaxProcesses and MaxConnections parameters should be configured based on the BW system environment configuration, for example, higher values are needed for complex multiple-provider BW systems.

The following configuration parameters are needed for the "Repository" section:

• Application server host: <application_server_host>
• System number: <sap_system_number>
• Client: <client_number>
• Language: <connection_language_code>
• User: <client_connection_user_name>
• Password: <client_connection_password>

If the SAP BW system is a Unicode system, make sure the "Unicode" box is checked.

When finishing all these parameters, click on "Set" to add the new RFC destination.

Note: after successful RFC destination configurations, a running man ico is displayed beside the RFC destination:

The same RFC destination configuration will need to be performed in SAP BW system environment through SAP GUI, This configuration procedures will be discussed in the following BW Administrator section.

3.4. BW DataSource via SAP BW Administrator

3.4.1. Start SAP Front End Logon GUI

Use the Desktop "Start" button as follows:

Start → Programs → SAP Front End → SAP Logon

The "SAP Logon 640" window appears. Then click "New Item" to create a new entry for the SAP system.

The following information is needed for creating the new entry:

• Description: the description name for the SAP system entry
• Application Server: <SAP_server_host_name>
• System ID: <SAP_system_id>
• System Number: <SAP_system_instance_number>
• SAP System: R/3

After the needed parameters are saved, click "Log on". On the Logon Window, the client number, user, and password are required to log on to the system.

3.4.2. Configure the RFC Provider in SAP GUI

The RFC destination configurations have been defined in Visual Administrator in previous section above. These RFC destinations are also required to be configured in SAP GUI so the communication between SAP BW system and J2EE engine can be enabled.

In SAP Logon GUI, use "RFC Destinations (Display/Maintain)" (transaction SM59) to enter the "Display and Maintain RFC Destinations" screen. Click "Create" to create a new RFC destination.

Enter RFC Destination name (for consistency it is recommended that the RFC destination name should be the same as the Program ID)
Enter Connection type as 'T'
Select radio button "Registered Server Program"
Enter Program ID name (this name is the RFC destination name defined in Visual Administrator)
Enter Gateway host and Server, when finished click Save

There are 2 test buttons that can be used to verify the RFC configurations. Click "Test Connection" will verify the communication between SAP BW system and J2EE engine, and "Unicode Test" button is used to verify if the SAP system is a Unicode system. The following screen shot displays a successful RFC connection.

3.4.3. Start BW Administrator Workbench

All the following procedures for creating BW DataSources, InfoObjects, and InfoCubes will be performed in SAP BW Administrator Workbench.

In the SAP Logon GUI, use "BW Adminstrator Workbench" (transaction RSA1) to start BW Administrator Workbench.

In BW Administrator Workbench, the following processes will be done for SAP BW UD Connect system configurations:

1. Creation of Generis DataSource
The following section illustrates the detailed procedures for the generic process of creating the DataSource. This process is similar regardless of what type of data target is desired (InfoCube, Text or Attribute Master Data), however the selection of InfoObjects to include in the DataSource will vary based on the intended data target.

2. Connecting DataSource to Data Target through InfoSource
Once the DataSource is successfully created, the procedures for connecting the Data Source to the target will vary based if the target object is Master Data or a Remote InfoCube. These procedures will be discussed in the section below.

3.4.4. Create BW Generic DataSource

SAP BW system can access data from Teradata database systems through UD Connect integration system. DataSources are responsible for extracting and staging data from source systems like Teradata database system. The DataSources subdivide the data that is provided by a source system into self-contained business areas.

There are 2 scenarios covering the data acess from BW system to Teradata database system: Master data load (Attributes or Text only with current UD Connect integration system for Teradata system) or Remote InfoCube.

Master data is data that remains unchanged over a long period of time. Master data contains information that is always needed in the same way. Characteristics can bear master data in BW (attributes or text only).

Remote InfoCube can be used as an InfoProvider in BEx Reporting. You can access the characteristics and key figures defined for an InfoCube in the Query Definition in the BEx Web or in the BEx Analyzer.

The following prerequisites are required for creating Generic DataSources:

• Teradata data modeling implementation described in earlier sections
• Complete configurations for Connector Container and JCo RFC Provider in Visual Administrator
  • RFC Destination: the RFC destination defined in Visual Adminstrator
  • UD Connect Java Source: the BI Java Connector defined in Visual Administrator
  • UD Connect Source Object: the relevant Teradata database table or view

The following sections describe the procedures for creating and activating BW DataSource.

3.4.4.1. Prepare Required InfoObjects

In the BW Administrator Workbench, highlight "InfoObjects" on the left pane under Modeling section, on the right pane, right click on the InfoObjects column and select "Create InfoArea..." to create an InfoArea for the target InfoObjects.

Highlight the InfoArea, and right click on it to select "Create InfoObject Catalog..." to create an InfoObject Catalog to hold the target InfoObjects. After the InfoObject Catalog is created, right click on it to create all required InfoObjects for the intended BW Generic DataSource.

The following configuration parameters are needed for creating a Characteristic InfoObject:

• Name: InfoObject Name
• Long description: the long description for the InfoObject
• Short description: the short description for the InfoObject
• General Tab
  • Data Type: NUMC - Character string
  • Length: length of the data
  • Convers. Rout.: leave blank
• Master data/texts Tab
  • Short text exists: checked if there existing short-length text data
  • Medium text exists: checked if there existing medium-length text data
  • Texts language dependent: unchecked
  • Character is data target/InfoProvider: checked
  • InfoArea: the InfoArea name

The Key Figure InfoObjects are used in the InfoCube implementation. To create a Key Figure InfoObject, the following configuration parameters are required:

• Name: InfoObject Name
• Long description: the long description for the InfoObject
• Short description: the short description for the InfoObject
• Type/unit Tab
  • Type/data Type: the data type for the Key Figure

After all the required configuration parameters are completed, save and activate the InfoObject.

3.4.4.2. Create and Configure InfoSource

In the BW Administrator Workbench, highlight "InfoSources" on the left pane under Modeling section, on the right pane, right click on the InfoSources column and select "Create application component..." to create an application for the target InfoSources and DataSources.

Highlight the application, and right click on it to select "Create InfoSource..." to create an InfoSource.

The "Create InfoSource" window appears. The following parameters are needed for this step:

• Flexible Update in any Data Target (Except Hierarchies: checked
• InfoSource: the InfoSource name
• Long description: the long description for the InfoSource

Confirm to create the InfoSource object.

From InfoSources Window, right click on the newly created InfoSource name and select Change

In the InfoSource Change Screen, add the required InfoObjects to the "Communication Structure". For example, for Text Master Data Load InfoSource, a Characteristic InfoObject and some Text InfoObjects (such as Short, Medium or Long Text) are needed to create a simple Text Master Data Load InfoSource; for Attribute Master Data Load InfoSource, a primary Characteristic InfoObject and some Attribute InfoObjects are used to create an Attribute Master Dara Load InfoSource; for a Remote InfoCube, all of the Characteristic InfoObjects (key fields) in the Dimensions and KeyFigure InfoObjects plus some Calendar InfoObjects can be used for constructing the InfoCube targets.

After all these configurations are done, save and activate the InfoSource object. Select yes on the dialog window for the question: "Do you want to activate all independent transfer programs ?".

3.4.4.3. Create and Activate BW DataSource

When the above InfoSource object has been created, the BW DataSource can be created through the InfoSource. The following describes the procedures for creating and activating the BW DataSource.

In the InfoSource change screen, select the top menu "Extras" and then click "Create BW DataSource with UD Connect".

The "Assigning UD Connect Source Object to BW DS and Extr.Gen." window appears. The following parameters are needed for this step:

• RFC Destination: the RFC Destination to the J2EE Java Service
• UD Connect Source: the BI Java Connector defined in J2EE Container COnnector Service
• UD Connect Source Obj.: the relevant Teradata database table or view

Click "Extract Source Object Elements" to populate the Source Object Elements. BW will attempt to heuristically map the objects. If the field mapping is not done by the system, highlight each object (one at a time) on the left hand side of the BW DataSource Fields and the Source Object Elements and then use the left arrow to transfer the mapping.

Once the field mapping is completed, select the "Generate Datasource (for UD Connect)" button. Select 'Yes' to the pop-up dialog windows if the proposals are acceptable.

You will be returned to the InfoSource (Communication Structure / Transfer Structure) screen. Click "Transfer_Structure/Transfer_Rules" to expand the section. Choose "IDoc" for "Transfer Method".

Save and activate the InfoSource object.

3.4.5. Connect DataSource to Data Target for Characteristic InfoObject

In the BW Administrator Workbench, highlight "InfoProvider" on the left pane under Modeling section, on the right pane, locate the InfoArea name that was created in the previous section. Right click on the InfoArea name and select "Insert Characteristic as a Data Target".

Enter the InfoObject name and click OK. If the InfoObject does not appear in the drop down, just type it in manually. The following screen shows the result after the above operation.

When you exit and return to the InfoProvide screen, the screen will be updated as follows:

Right click on the Characteristic Data Target and select "Create Update Rules". Select the correct InfoSource name defined previously in the above section, and press "Enter". Save and activate the Update Rules. The update rule appears under the Data Target name.

After the Update Rules are created, the procedures for creating InfoPackages will be performed. The following discusses the steps for creating InfoPackages.

In the BW Administrator Workbench, highlight "InfoSources" on the left pane under Modeling section, on the right pane, locate the DataSource name under the corresponding application and InfoSource names that have been defined in the above sections. Right click on the DataSource name and select "Create InfPackage".

Enter the InfoPackage description and click Save button to save the definition. The Scheduler (Maintain InfoPackage) screen appears.

The Scheduler (Maintain InfoPackage) screen includes 5 tabs: "Data Selection", "Processing", "Data Targets", "Update", and "Schedule".

In the "Data Selection" tab, highlight the InfoObject rows and click "Check".

In the "Processing" tab, configure the Update Data selection (use the default is recommended).

In the "Data Targets" tab, choose "Select Data Targets" and check the checkbox for the Data Target.

Click Save button to save the changes. In the "Schedule" tab, choose "Start Data Load Immediately" and click "Start" to run immediately, or choose "Start Later in Background" to schedule for future extraction.

Click Monitor icon to monitor the data load. The following screen shows a successful data load of the Text Master Data.

The following describes the way to view the loaded Master Data. In the BW Administrator Workbench, highlight "InfoObjects" on the left pane under Modeling section, on the right pane, locate the target InfoObject to enter the InfoObject screen. Click on the "Master data/texts" tab, double click on the Text table name in the box.

The Display Table screen appears. Click the menu button "Contents" to bring up the "Data Browser" window for the table, and then click the menu button "Execute" to display the data.

3.4.6. Connect DataSource to Remote InfoCube

In the BW Administrator Workbench, highlight "InfoProvider" on the left pane under Modeling section, on the right pane, locate the InfoArea name that was created in the previous section. Right click on the InfoArea name and select "Create InfoCube...".

Enter the technical name and description for the InfoCube, and select the corresponding InfoSource name under InfoCube Type section.

Click "paper" icon to create the cube.

The Edit InfoCube screen includes 3 tabs: "Characteristics", "Time characteristics", and "Key figures".

In the "Characteristics" tab, select "Dimensions..." tab to assign characteristics to dimensions. On the "Define Dimensions" screen, use the "Define" tab to enter descriptions for the dimensions. Use the "Assign" tab to assign the characteristics to dimensions, Click green mark when done.

Save and activate the InfoCube object.

The next step is to assign a source system to the InfoCube. On the "InfoProvider" screen, right click the Remote InfoCube and select "Assign Source Systems".

Highlight the needed source system and click save button.

Now the source system has been added to the Remote InfoCube.

3.5. Performance Parameters

When defining and configuring the Java Connectors through Connector Container service in the Visual Administrator, some configurations with the connections can be used to enhance performance and avoid run-time issues.

The Connection Definition properties for the Java Connectors can be configured through the "Connection Definition" tab under "Managed Connection Factory" tab in the "Connector Container" service in Visual Administrator.

The following parameters are related to the Connection run-time performance:

Expiration - specify if the connections in the pool will be expired and removed.

Connection Lifetime - specify how many seconds a connection may stay in pool before it is closed.

Cleanup Thread - schedule a thread that periodically cleans up unused connection objects at a regular interval that you set in seconds.

Maximum Connections - too small of a value here can cause users of the pool to waste time waiting for someone to release a connection.

Maximum Time to Wait for Connection - generally use the default value (120).

Generally the Java applications will close the connections and return the connections back to the connection pool after finishing all the queries. However, some applications and clients, such as Business Explorer, don't close the connections they have acquired and/or other resources such as ResultSets when returning the connections to connection pool, then sometimes issues will occur, such as exceptions like "[Teradata DBMS]: Response limit exceeded". This exception when more than 15 time queries are executed within the ResultSet in the connection, but the ResultSet is not closed when returning to connection pool. These connections that are not closed and/or have unreleased resources like ResulSets should be regularly cleaned up, and the three parameters "Expiration", "Connection Lifetime", and "Cleanup Thread" can be configured for this purpose.

4. Troubleshooting

4.1. JDBC Driver Debugging

Teradata JDBC driver can output the necessary detailed info and error messages for application debugging. To enable the debugging message availability, some configurations with Visual Administrator are required, which are described below.

In Visual Administrator, expand "Cluster", and then "Server", and then "Services".
Scroll down and click "Log Configurator". On the right pane, click on "Location" tab, and then expand System item. Click on "out" line to configure the System.out configurations. Use the drop-down menu to select "Debug". Click on Save and select "Apply to current node only".

To enable the JDBC debugging with Data Source definition with "JDBC Connector" service, follow the descriptions below.

On the right pane of the "JDBC Connector" section in the Visual Administrator, click on "DataSources", Expand the target Data Source definition and open the "Main" tab. The Database URL property needs to be added with the additional parameter "LOG=debug". And then save the changes.

The following procedures illustrate the approach to enable the JDBC debugging with Java Connector configurations with "Connector Container" service.

On the right pane of "Connector Container" section, locate "sap.com/com.sap.ip.bi.sdk.dac.connector.jdbc" and expand it to locate the target Java Connector. Open the "Managed Connection Factory" tab and click the "Properties" tab. Highlight the URL key to add the additional parameter "LOG=debug" in the URL string value. Click on the Save button to save the changes.

The debugging output messages will be stored in the following locations after the configurations are completed:

UNIX:
/usr/sap/<SAPSID>/<Instance_Name>/j2ee/cluster/server0/log
Windows: <driver>:\usr\sap\<SAPSID>\<Instance_Name>\j2ee\cluster\server0\log

4.2. SQL Trace

SQL tracing message can be captured on Teradata via standard Query Logging facilities (DBQL):

Rudimentary SQL logging syntax:
BEGIN QUERY LOGGING WITH SQL LIMIT SQLTEXT=10000 ON USERNAME;

When query capture is complete, end query logging with:
END QUERY LOGGING WITH SQL ON USERNAME;

Display the captured SQL with the following:
DATABASE DBC;
SEL USERID, STARTTIME,LOGONDATETIME, DATABASENAMEI, QUERYTEXT FROM QRYLOG Q, DBASE D
WHERE Q.USERID=D.DATABASEID
AND DATABASENAMEI='USERNAME'
ORDER BY STARTTIME DESC , LOGONDATETIME DESC

Cleanup the captured SQL logs with:
DEL FROM DBQLOGTBL WHERE
DBC.QRYLOG.USERID=DBC.DBASE.DATABASEID
AND DBASE.DATABASENAMEI='USERNAME'

4.3. BW System Trace

SAP BW system outputs an SDK_BI_Trace.log file that displays the SQL that was generated base on the query created by the end user. The log file also shows the database table name and all columns that were selected as well as the total time it took for query processing.

The BW system tracing output messages are located in the following locations:

UNIX:
/usr/sap/<SAPSID>/<Instance_Name>/j2ee/cluster/server0/BI_SDK_Trace.log
Windows:
<driver>:\usr\sap\<SAPSID>\<Instance_Name>\j2ee\cluster\server0\BI_SDK_Trace.log

Note: Please Refer to SAP BI UD Connect: Performance Issues guide for more information about the trace that is retrieved from this log file. The guide can be found at: http://help.sap.com/saphelp_nw04/helpdata/en/6c/7ffb3f6c78ee28e10000000a1550b0/frameset.htm Under Information Integration click on Business Intelligence (BI).

Appendix A: SAP Web Application Server Code Samples

Note: The creation of an SAP Web Application Server 6.40 datasource "ds1" creates a JNDI name of "jdbc/ds1" for the datasource. The name with the "jdbc" prefix must be used when accessing the following servlet.