Data Access Guide. BusinessObjects 11. Windows and UNIX

(1)

(2)

1

If you find any problems with this documentation, please report them to Business Objects in writing at [email protected]. Trademarks Business Objects, the Business Objects logo, Crystal Reports, and Crystal

Enterprise are trademarks or registered trademarks of Business Objects SA or its affiliated companies in the United States and other countries. All other names mentioned herein may be trademarks of their respective owners. Contains IBM Runtime Environment for AIX(R), Java(TM) 2 Technology Edition Runtime Modules (c) Copyright IBM Corporation 1999, 2000. All Rights Reserved.

This product includes code licensed from RSA Security, Inc. Some portions licensed from IBM are available at http://oss.software.ibm.com/icu4j. Use restrictions This software and documentation is commercial computer software under

Federal Acquisition regulations, and is provided only under the Restricted Rights of the Federal Acquisition Regulations applicable to commercial computer software provided at private expense. The use, duplication, or disclosure by the U.S. Government is subject to restrictions set forth in subdivision (c)(1)(ii) of the Rights in Technical Data and Computer Software clause at 252.227-7013.

Patents Business Objects owns the following U.S. patents, which may cover products that are offered and sold by Business Objects: 5,555,403, 6,247,008 B1, 6,578,027 B2, 6,490,593 and 6,289,352.

(3)

Chapter 1 Data access basics 3

Overview . . . 4

Introducing Connection Server and data access drivers . . . 4

Connecting to your data . . . 6

Data access drivers . . . 7

What data access files are installed? . . . 8

Chapter 2 Creating connections 13 Overview . . . 14

Creating a connection with the New Connection wizard . . . 14

Chapter 3 Managing data access 27 Overview . . . 28

Configuring unicode data access . . . 28

Setting dynamic SQL parameters in a universe . . . 36

Optimizing data access by editing data access parameter files . . . 38

Chapter 4 Configuring specific data access drivers 43 Overview . . . 44

Oracle . . . 44

Teradata . . . 45

Chapter 5 Data type conversion reference 47 Overview . . . 48 IBM DB2 . . . 48 Informix . . . 49 Microsoft SQL Server . . . 49 Oracle . . . 50 Red Brick . . . 51

(4)

Contents

Sybase . . . 52

Teradata . . . 52

Chapter 6 Setting universe dynamic SQL parameters 55 Overview . . . 56

Dynamic SQL parameters in a universe . . . 56

Chapter 7 Setting data access SQL parameters (PRM) 69 Overview . . . 70

Data access driver SQL parameters . . . 70

PRM file Configuration reference . . . 74

Chapter 8 Setting data access driver configuration parameters (SBO) 95 Overview . . . 96

Data access driver configuration parameters . . . 96

SBO file reference . . . 99

Chapter 9 Setting data access general configuration parameters (CFG) 109 Overview . . . 110

Data access general configuration file parameters . . . 110

Settings reference . . . 113

Appendix A Business Objects information resources 117 Documentation and information services . . . 118

Documentation . . . 118

Customer support, consulting and training . . . 119

Useful addresses at a glance . . . 121 Index 123

(5)

(6)

Data access basics Overview

1 Overview

Data access for Web Intelligence is managed by Connection Server. Connection Server is the Business Objects software layer that creates and manages connections used by universes between BusinessObjects data access drivers and target RDBMS middleware.

Connection Server and the required data access components are automatically installed when you install Web Intelligence or Designer. This chapter introduces Connection Server. The following topics are described:

•

Introducing Connection Server and data access drivers

•

Connecting to your data

•

Data access drivers

•

What data access files are installed?

Note: $INSTALLDIR variable in this guide

In this guide the variable $INSTALLDIR is the install root path for the data access files used by Designer and Web Intelligence. This is the Business Objects installation path with the operating system sub directory that contains the Designer executable and the data access drivers.

Under Windows$INSTALLDIR = \\...\Business Objects\BusinessObjects Enterprise 11\win32_x86.

For example C:\Program Files\Business Objects\BusinessObjects Enterprise 11\win32_x86.

Introducing Connection Server and data

access drivers

Universes access data in relational databases by using a connection to the data source middleware.

A connection is a defined set of parameters between a BusinessObjects data access driver and a target middleware.

A data access driver is the software layer that maps the SQL definitions and functions defined in the middleware to equivalent structures and parameters in a universe.

(7)

Each connection between a data access driver and a RDBMS middleware is managed and controlled by a Business Objects data access management tool called Connection Server.

What is Connection Server?

Connection Server is the BusinessObjects data access management software that is installed automatically with one or more data access drivers for your RDBMS. Connection Server is installed by default with

WebIntelligence and Designer.

Connection Server allows InfoView and WebIntelligence users to connect to and run queries against a relational data source, whether it is a corporate database or a repository.

How do you access Connection Server features?

Connection Server does not have a user interface. You create and administer connections from the user interface of Designer.

Creating connections

You create connections using a connection wizard available from Designer. You can administer certain connection parameters from the Connection dialog box (Tools > Connections). See the chapter “Creating connections” on page 13 for more information on creating connections.

Administering connection deployment

In a WebIntelligence deployment, Connection Server is installed

automatically with both these products. Each node in a cluster that has either of these products installed, also includes a Connection Server layer.

Optimizing data access

You can optimize the way that data is passed through Connection Server by modifying SQL and data access parameter files. These are files in XML format that are installed with Connection Server. You can edit these files to optimize values for parameters specific to a RDBMS, or general parameters that apply to all installed data access drivers.

For information on data access drivers, see the section “Data access drivers”

(8)

Data access basics Connecting to your data

1

For information on editing data access parameters files to optimize data access, see the sections:

•

“Optimizing data access by editing data access parameter files” on page 38

•

“Setting data access SQL parameters (PRM)” on page 69

•

“Setting data access driver configuration parameters (SBO)” on page 95

•

“Setting data access general configuration parameters (CFG)” on page 109.

Using connection management tools with Connection Server

Connection management utilities that allow you to check and trace connection processes are installed with Connection Server. You run these applications from a command line. Using these utilities is described in the chapter “Managing data access” on page 27.

Connecting to your data

A connection allows WebIntelligence users to access data in a target RDBMS.

What is a connection?

A connection is a named set of parameters that defines how Web Intelligence accesses data in a database file. It is the link between a universe, a data access driver, your middleware, and your database.

You must have a valid connection between your target universe and your database in order to access your data.

A connection is made up of the following elements: Connection element Description

Data access driver BusinessObjects software layer that connects a universe to your middleware.

Connection and login

parameters Connection parameters that allow the data access driver to connect to the target database middleware. These parameters include

Connection user name, password, and data source name.

Connection type Type of connection that determines who can use it. A connection can be personal, shared, or secured.

(9)

Pre-requisites to creating a connection?

To create a connection you must have the following:

•

middleware

•

information about your database server

•

Designer installed

•

BusinessObjects data access driver

Connection workflow overview

The following is an overview of the process that you follow to create a connection. Creating connections is fully described in the chapter “Creating connections” on page 13.

•

Ensure that you have all the information necessary to access your database.

•

Install middleware so that you can access your database through either your computer or a server.

•

Install Business Objects products and the appropriate data access driver.

•

Using the New Connection wizard accessible from Designer, you connect your middleware to your data access driver.

•

Make any necessary modifications to optimize the connection

parameters. You can customize certain parameters from the Connection Wizard.

Other parameters can be customized in the SQL parameter file

associated with the data access driver. This file is called the <driver>.prm file. See the chapter “Setting data access SQL parameters (PRM)” on page 69 for information on editing the PRM file for your data access driver.

Many SQL generation parameters can also be set at the universe level from Designer. See the chapter “Setting universe dynamic SQL parameters” on page 55 for information.

Data access drivers

A data access driver is the BusinessObjects software layer that connects a universe to your middleware.

(10)

Data access basics

What data access files are installed?

1

Data access drivers are shipped with Business Objects products. There is a data access driver for each supported middleware. When you install Business Objects products, your data access key determines which data access drivers are installed.

When you create a new connection, you select the appropriate data access driver for the RDBMS middleware that you use to connect to the target RDBMS.

For example, if you access an Oracle 8i database, you must install the appropriate middleware (Oracle 8i Client), then the BusinessObjects Oracle data access driver.

The types of databases supported through data access drivers are listed below. Each data access driver is described in its respective chapter in this guide.and the chapters associated with the connection:

•

IBM DB2

•

Informix

•

Microsoft SQL Server

•

Oracle

•

Red Brick

•

Sybase

•

Teradata

What data access files are installed?

This section describes the data access files that are installed when you install Business Objects products. Data access files can be divided into two levels:

•

Global level. These are files that apply to all installed data access drivers.

•

Driver level. These are data access driver specific files. Note:

Not all the files installed by Connection Server are listed. Most of the files described in this section contain parameters that can be modified to optimize or customize data access. Certain files are listed to explain their function. These and other files in these directories must not be modified. They are required for full functionality, so do not remove them.

The global and driver level files are described in their respective sections below.

(11)

Global level data access files

Global files used by all data access drivers are installed here:

The files listed below either have parameters that apply to all installed data access drivers, or are executable utilities that are used for all data access drivers.

$INSTALLDIR\dataAccess\RDBMS\connectionServer

Installed file Edit this file?

Description

cs.cfg Yes Stores all configuration parameters for the global configuration of Connection Server.

See “Setting data access general configuration parameters (CFG)” on page 109

platform.crs No Maps the charset for each RDBMS to the charset used by BusinessObjects. Warning: Do not modify this file. charsets.txt No List of the charset supported by

BusinessObjects.

Warning: Do not modify this file. cscheck.xml No Contains the information needed by the

cscheck tool to verify connection configuration. See the readme for information on using this tool. Warning: Do not modify this file. cschecklocales.xml No Contains locale information for each

language used by the cscheck tool when verifying connection configuration. Warning: Do not modify this file.

(12)

Data access basics

1 Driver level data access files

Driver level files used by specific data access drivers are installed here:

There are other files in these directories but they not currently modifiable. They are required for full functionality, so do not remove them.

strategy_localization. dtd strategy.dtd sbo.dtd dbparameters_localiz ation.dtd dbparameters.dtd cscheckresults.dtd cschecklocales.dtd cs.dtd

No DTD files for the corresponding XML files in the same directory.

<driver><language>.

cod No Lists options that appear in the RDBMS page of the Connection wizard. Warning: Do not modify this file. Installed file Edit this

file?

Description

(13)

The files listed below have parameters that apply to installed data access drivers.

Driver specific file Can be edited?

Description Example

<driver><language>.stg Yes Contains the Help text for each external strategy defined in the <driver>.stg file. This text appears when the strategy is selected in Designer.

oracleen.stg

<driver>.stg Yes Contains the external strategies available to the data access driver.

See the Designer’s Guide for information on using external strategies.

oracle.stg

<driver>.sbo Yes Defines all databases supported and the specific connectivity configuration for each database.

See the sections:

“Data access driver general configuration (<driver>.SBO)”

on page 39

“Setting data access driver configuration parameters (SBO)” on page 95

oracle.sbo

<driver><language>.prm Yes Contains the Help text for functions that are listed in the <driver>.prm file. This text appears when the function is selected in Designer.

(14)

Data access basics

1

<driver>.prm Yes Defines the information— parameters and functions— related to database. For more information, see the sections:

•

“Optimizing data access by editing data access parameter files” on page 38

•

“Setting data access SQL parameters (PRM)” on page 69

oracle.prm

<driver><language>.cod No Stores information related to connection definitions. Warning: Do not modify this file.

oracleen.cod Driver specific file Can be

edited?

(15)

(16)

Creating connections Overview

2 Overview

This chapter provides information about creating connections to database middleware.

It describes how you create connections using the New Connection wizard, set login parameters, and optimize certain connection settings. Connection issues that are specific to RDBMS are also covered.

This chapter covers the following topics:

•

Creating a connection with the New Connection wizard

•

Setting login parameters

•

Setting advanced parameters

•

Setting custom parameters

Creating a connection with the New

Connection wizard

You create a connection using the New Connection Wizard. This is a

connection wizard that is accessible from Designer. The wizard allows you to select a target database and middleware, then configure the data access driver that you use to access the middleware.

What connection parameters are set in the New

Connection wizard?

You use the wizard to set the following parameters: Connection wizard stage Description

Login parameters. See “Setting login

parameters” on page 20 for more information.

Identify the connection and allow you to connect to the middleware. Parameters include Type, Connection name, User name, Password, and Data source or service.

(17)

The procedure for starting and following the New Connection wizard appears below. The parameters that you can set on each page are fully described in their respective section.

Using the New Connection wizard

You create a connection using the New Connection wizard.

Accessing the New Connection wizard

You access the New Connection in Designer as follows: From the Universe parameters dialog box

1. Select File > Parameters 2. Click the New button. From the Connections list 1. Select Tools > Connections. 2. Click Add

The Welcome page of the New Connection wizard appears.

Creating a connection with the New Connection wizard

To create a connection with the New Connection wizard: 1. Start the New Connection wizard.

The wizard welcome page appears. 2. Click Next.

The Database middleware selection page appears. It lists the database and middleware that correspond to your data access licence key. 3. Do the following from the Database middleware selection page:

Advanced parameters See “Setting advanced parameters” on page 22 for more information.

Allow you to manage certain connection parameters. These include connection length, array fetch and bind sizes, and login time-out. Custom parameters

See “Setting custom parameters” on page 24 for more information.

Parameters that you can add and customize to optimize your connection.

(18)

Creating connections

Creating a connection with the New Connection wizard

2

•

Expand a database node.

The middleware available for the database appear under the node.

•

Expand a middleware node.

The data access driver for the middleware appears under the node.

•

Click the data access driver name.

The data access driver for Oracle 9 is shown below.

•

Click Next.

The Login parameters page appears. It lists login and connection identifying parameters for the data access driver. Login parameters are fully described in the section “Setting login parameters” on page 20. 4. Do the following from the Login parameters page:

•

Select a connection type.

(19)

•

Select a data source from the Data source or Service drop down list box

Or

Type the name for the database or data source that you want to access. A Login Parameters page with parameters completed appears below..

•

Click Next. Note:

If you want users to access the database with their CMS (repository) login, select the Use Single Sign-On when viewing reports check box. To use this feature, the CMS login must be the same as the login to the database.

The Perform a test page appears. It summarizes the connection information and allows you to test the connection.

1. Click the Test Connection button.

If the connection is valid then “Server is responding” appears under the Test Data Source button.

If the connection is not valid then “Server is not responding” appears under the Test Data Source button. Information describing the failed connection appears in the zone to the left of the Test Data Source button.

(20)

2

•

Click Next.

The Advanced Parameters page appears. It lists certain connection parameters that you can set to optimize your connection. Advanced parameters are fully described in the section “Setting advanced parameters” on page 22.

(21)

2. Select values for advanced connection parameters then click Next. Or

Click Next directly if you don’t want to set advanced parameters. The Custom Parameters page appears. It allows you to manually set certain parameters. Custom parameters are fully described in the section

“Setting custom parameters” on page 24.

3. If you do not want to modify a listed parameter, click Finish. Or

If you want to modify a default parameter value do the following:

•

Select a parameter in the list.

•

Type a value in the text box under the parameter list and click Set.

(22)

2

•

Click Finish.

The Connections list appears. The new connection is now included in the list.

4. Click Finish to close the Connections list. The connection is now available to the universe.

Setting login parameters

The Login Parameters page of the New Connection wizard contains the following parameters:

Parameter Description

Type Connection type. You can create one of three connection types:

•

Personal

•

Shared

•

Secured

See the section “Connection type” on page 21 for more information on the types of connections that you can create.

(23)

Connection type

The type of connection determines who can use the connection to access data. Designer automatically stores all the connections that you create during a work session. The next time you launch a session, these connections will be available to you.

You can create three types of connections with Designer:

•

Personal

•

Shared

•

Secured

Each connection type is described as follows: Personal connections

Restricts access to data to the universe creator and the computer on which it was created. Connection parameters are stored in the PDAC.LSI file located in the LSI folder in the Business Objects installation path. An example of this path is shown below.

C:\Program Files\Business Objects\BusinessObjects Enterprise 11\win32_x86\pdac.lsi

These parameters are static and cannot be updated.

Personal connections are unsecured in terms of Business Objects products security.

You do not use personal connections to distribute universes. You could use personal connections to access personal data on a local machine.

User name Your database user name. This is normally assigned to you by the database administrator. Use Single Sign-On

when viewing reports When selected, the user name and password used to access the CMS are automatically used as database login parameters. See the

BusinessObjects Enterprise™ 11 Administrator’s Guide for information on setting up Single Sign-On. Password Your database password. This is normally assigned

to you by the database administrator. Data source or

Service Data source or database name. If you are using an ODBC driver the data source name identifies the target database. If you are using a native driver, the database name identifies the target database.

(24)

2

Shared connections

Allows access to data for all users. These connections are unsecured in terms of Business Objects products security.

Connection parameters are stored in the SDAC.LSI file located in the LSI folder in the Business Objects installation path. An example of this path is shown below.

C:\Program Files\Business Objects\BusinessObjects Enterprise 11\win32_x86\sdac.lsi

If the SDAC.SSI file is stored locally, only users having access to the local machine (through a mapped drive), can use the shared connections. Shared connections can be useful in a universe testing environment. Secured connections

•

Centralizes and controls access to data. It is the safest type of connection, and should used be to protect access to sensitive data.

•

You can create secured connections with Designer.

•

You must use secured connections if you want to distribute universes through the CMS.

•

Secured connections can be used and updated at any time.

Setting advanced parameters

The Advanced page contains parameters used to over ride the following common configuration options:

•

Defining the duration of a connection into a pool

•

Defining how Business Objects products respond when database resources are not available

(25)

You can set the following advanced parameters:

Parameter Description

Disconnect after each

transaction Click if you want your connection to disconnect immediately after the transaction is completed. Users will have to reconnect each time they want to access data.

Keep the connection active

for x minutes Click if you want your connection to stay active for a defined period of time. You must also enter a value for the number of minutes. This is the default Connection Property. Keep the connection active

during the whole session (local mode only)

Click if you want your connection to stay active during the entire session of the product. The connection ends when the user exits the application.

Get error message and stop Click if you want your connection to disconnect and notify the user that the resource is locked when a user receives an error message. This is the default Locked Resources option. Wait for resources Click if you want your connection to remain

active without notifying users.

Array fetch size Enter the maximum number of rows authorized with each fetch.

If you enter 20, and your query retrieves 100 rows, the connection executes 5 fetches to retrieve your data. Five fetches of 20 rows each equals 100 rows of data.

If you enter 1, the array fetch is deactivated and data is retrieved row by row.

Note: Deactivating array fetch is the safest way of retrieving your data but row-by-row retrieval slows down server performance. The greater the value in the Array fetch size option, the faster your rows are retrieved; you must, however, ensure you have adequate client system memory.

Note: If a query retrieves data containing blob or long text types, the array fetch is

automatically reset to 1. The default value is 20.

(26)

2 Setting custom parameters

Values on the Custom page should only be modified by an advanced user, DBA, or BusinessObjects administrator. It contains certain parameters that can be used to override connection settings. The following parameters are available

•

Binary Slice Size. This appears by default in the Advanced page for all data access drivers. This parameter specifies the size of the slice used to store the binary content of all documents sent to the repository. It is described in the section “Binary Slice Size” on page 101.

•

Hint (Oracle only). This parameter is described below.

Using Hints (Oracle only)

A Hint is commented information that is embedded in a query, and used by the Oracle optimizer to choose an execution plan. You should consult your Oracle documentation for full information on the Hints that can be used, and how they can be used to optimize queries.

You enter the value for a Hint as a comment. A plus sign is used at the beginning, immediately after the comment delimiter, with no space between the comment delimiter and the plus sign:

/*+ <hint> */

Array bind size Enter the size of the bind array that Connection Server uses before sending to the repository. The bind array is the area in memory that Connection Server stores a batch of data to be loaded (sent to the repository). When the bind array fills, it is transmitted to the database. Generally, the larger the bind array, the more rows (n) can be loaded in one operation, and the better your performance.

Login timeout Specifies the number of seconds that must be spent to establish a connection before an error message is displayed.

(27)

Some useful Hints that you can use are as follows. Consult your Oracle documentation for a full list:

Example: FIRST_ROWS

On the Custom page, click Hints and type /* FIRST_ROWS */ in the values box under the parameter list, and click on Set. The SQL is:

SELECT /* FIRST_ROWS */ RESERVATIONS.CUST_ID FROM

RESERVATIONS

Hint What it does...

FIRST_ROWS Optimizes response time.

RULE Use rule-based optimization and not cost. FULL Does a full table scan on the table.

ROWID Scans table by rowid.

INDEX_FFS Perform a fast full scan on the index rather than on the table.

(28)

2

(29)

(30)

Managing data access Overview

3 Overview

This chapter describes how you can use parameter files to optimize your data access driver. The following areas are described:

•

Configuring unicode data access

•

Setting dynamic SQL parameters in a universe

•

Optimizing data access by editing data access parameter files

Note: Other tools in the readme:You can use command line utilities to check your RDBMS and data access driver configuration, and to create log filies that trace Web Intelligence server activity. Refer to the readme for this release for instructions on the use of these utilities.

Configuring unicode data access

WebIntelligence users can run queries that return data containing characters that confirm to the Unicode Standard UTF-8, or the Universal Character Set (UCS) standard.

Note:

Unicode characters are not supported in table or object names in a universe. Unicode data access is available to WebIntelligence when the following conditions are met:

•

Target middleware supports unicode.

•

The BusinessObjects data access driver for the target middleware supports Unicode.

•

Unicode parameters have been set in the General data access (SBO) file for the appropriate data access drivers, and in the dynamic SQL

parameters for the target universe.

When these conditions have been met, WebIntelligence users can run queries returning data sets that contain unicode characters.

What RDBMS are supported for Unicode data access?

Unicode data access is supported for the following RDBMS:

•

Oracle

•

(31)

•

Teradata

Setting up unicode data access

An overview of the steps you need to follow to set up unicode data access are as follows:

1. Set a unicode parameter in the <RDBMS>.sbo file for the data access driver.

Or

Set a default unicode parameter for all data access drivers using the general configuration file (cs.cfg).

2. Set an environment variable to a unicode value specific for the target RDBMS.

Setting the SBO and environment variable values are described in sections for the following RDBMS:

•

“Setting up unicode data access for Oracle” on page 29

•

“IBM DB2 UDB” on page 32

•

“Microsoft SQL Server 2000” on page 33

•

“Teradata V2R5” on page 34

3. For the following RDBMS, you must set a SQL parameter for a target universe in Designer to allow queries return unicode characters.

•

Oracle with local charset

•

This is described in the section “Activating unicode support in a universe (SQL Server and Oracle special case)” on page 35.

Setting up unicode data access is described for each supported middleware.

Setting up unicode data access for Oracle

You can set unicode support for a particular Oracle target data access driver, or as a default setting for all Oracle data access drivers. You do this in two stages:

•

Set a parameter in the oracle.sbo file to activate unicode support.

•

Set the environment variable NLS_LANG parameter to activate unicode support. This is done in the Windows registry for Windows, and from the command line for UNIX.

(32)

Managing data access

Configuring unicode data access

3

Setting unicode support for a Oracle data access driver To set unicode support for a data access driver:

1. Open the oracle.sbo file in a XML file editor.

General data access configuration (SBO) files are installed by default in the following directory:

2. Go to the section Databases.

3. Go to section Parameters for your target database version. 4. Add the following parameter and value:

<Parameter Name="Unicode">UTF8</Parameter> The Database section should appear as follows:

</DataBase>

Note:

You can set the unicode parameter for all Oracle RDBMS using the oracle.sbo file by adding the Unicode parameter with value UTF8 to the Defaults section of the file (<Parameter Name="Unicode">UTF8</ Parameter>). This applies for all target Oracle middleware.

1. Save and close the file.

Setting the NLS_LANG variable for Windows

You set the NLS_LANG variable for a Windows environment in the registry. Note:

Don’t take risks when editing the registry. A mistake can cause one or more applications to not function correctly. You should create a backup copy of your registry before making modifications. As an extra security measure, before modifying a key value, you can also copy the value of the original key to a new temporary key, or save it in a backup text file until you are sure the new value functions correctly.

To set NLS_LANG in the Windows registry: 1. Select Start > Run.

Type regedit in the Run text box and click OK. The Registry Editor appears.

(33)

2. Browse to the following key:

HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEO 3. Select the NLS_LANG value.

4. Select Edit > Modify.

5. Replace the file extension with UTF8.

For example, the default value for American Engish is: AMERICAN_AMERICA.WE81SO8859P1

Replace the file extension as follows: AMERICAN_AMERICA.UTF8 6. Save and close the registry editor. Setting the NLS_LANG variable for UNIX

The NLS_LANG variable for the environment language must be set to the value UTF8. You set the NLS_LANG variable from the command line in UNIX. Modifying the NLS_LANG variable for UNIX:

1. Type the following command to verify your current set up:

echo $NLS_LANG

The current value of your system is returned. It appears as follows: <LANGUAGE>.<extension>

for example: ENGLISH_UNITED KINGDOM.WE9ISO8859P 15. To activate unicode, you must replace the <extension> with UTF8. 2. Depending on the shell, use the following command to set the variable to

<value>, where <value> is <LANGUAGE>.UTF8.

•

tcsh or korn shell

SETENV NLS_LANG <value>

•

bash or cshell

EXPORT NLS_LANG = <value>

For example, ENGLISH_UNITED KINGDOM.WE9ISO8859P 15 is set to ENGLISH_UNITED KINGDOM.UTF8

Setting up unicode support for Oracle using a local charset

When the Oracle database charset is set as a local charset, you also need to set the unicode universe parameter for each universe that access the unicode data. This parameter generates specific SQL syntax to specify that a UTF8 string is passed as condition. The database being set as local charset usually

(34)

3

handles local charset strings.This is described in the section “Activating unicode support in a universe (SQL Server and Oracle special case)” on page 35.

IBM DB2 UDB

You can set unicode support for a particular IBM DB2 target data access driver, or as a default setting for all IBM DB2 data access drivers. You do this in two stages:

•

Set a parameter in the db2.sbo file to activate unicode support.

•

Set the environment variable NLS_LANG parameter to activate unicode support.

Each stage is described below.

Setting unicode support for a IBM DB2 data access driver To set unicode support for a data access driver:

1. Open the db2.sbo file in a XML file editor.

</DataBase>

</DataBase>

Note:

You can set the unicode parameter for all IBM DB2 RDBMS using the db2.sbo file by adding the Unicode parameter with value UTF8 to the Defaults section of the file (<Parameter Name="Unicode">UTF8</

Parameter>). This applies for all target IBM DB2 middleware.

(35)

Setting the variable DB2CODEPAGE

You now must set the DB2CODEPAGE variable for the environment variable to the value 1208.

Microsoft SQL Server 2000

You can set unicode support for a Microsoft SQL Server 2000 target data access driver, or as a default setting for all ODBC data access drivers. You do this by setting a parameter in the odbc.sbo file to activate unicode support. Setting unicode support for a Microsoft SQL Server 2000 data access driver

To set unicode support for Microsoft SQL Server 2000: 1. Open the odbc.sbo file in a XML file editor.

2. Go to the section Databases for MS SQL Server 2000. 3. Go to section Parameters for MS SQL Server 2000. 4. Add the following parameter and value:

5. Verify that the Library parameter has the correct Unicode library name. For example:

<Parameter Name="Library">dbd_wmssql</Parameter> The Database section should appear as follows:

<DataBase Active="Yes" Name="MS SQL Server 2000"> <Parameter Name="Family">Microsoft</Parameter>

<Parameter Name="Version">rdbms_mssqlserverodbc.txt</ Parameter>

<Parameter Name="SQL External File">sqlsrv</ Parameter>

<Parameter Name="SQL Parameter File">sqlsrv</ Parameter>

<Parameter Name="Library">dbd_wmssql</Parameter> <Parameter Name="Unicode">UCS2</Parameter>

Note:

(36)

3

You can set the unicode parameter for all data access drivers using the odbc.sbo file by adding the Unicode parameter with value UCS2 to the Defaults section of the file (<Parameter Name="Unicode">UCS2</ Parameter>).

Setting up unicode support in a universe

For Microsoft SQL Server, once you have set up the data access driver for unicode support, you now need to set the unicode universe parameter for each universe that access the unicode data. This is described in the section

“Activating unicode support in a universe (SQL Server and Oracle special case)” on page 35.

Teradata V2R5

You can set unicode support for a Teradata V2R5 target data access driver, or as a default setting for all Teradata data access drivers. You do this by setting a parameter in the teradata.sbo file to activate unicode support.

Setting unicode support for a Teradata data access driver To set unicode support for a data access driver:

1. Open the teardata.sbo file in a XML file editor.

<Parameter Name="Unicode">UTF8</Parameter>

</DataBase>

Note:

You can set the unicode parameter for all Teradata data access drivers using the teradata.sbo file by adding the Unicode parameter with value UTF8 to the Defaults section of the file (<Parameter Name="Unicode">UTF8</ Parameter>). This applies for all target Teradata middleware.

(37)

Activating unicode support in a universe (SQL Server

and Oracle special case)

For Microsoft SQL Server, and the case when an Oracle database charset is set as a local charset, once you have set unicode support in the SBO file for the target data access driver, you must activate the SQL parameter

UNICODE in each universe that uses the data access driver to access unicode data.

The setting UNICODE_STRINGS in Designer for SQL generation indicates that specific SQL generation is required because of NVARCH, NCHAR columns in the universe. The UNICODE_STRINGS parameter ensures that specific SQL syntax is generated to specify that a UTF8 string is passed as condition.

The string is specified by another parameter, the UNICODE_PATTERN parameter in the PRM file. Once you have set UNICODE_STRINGS to Yes, you then set the string condition in for the UNICODE_PATTERN parameter in the PRM file.

The database being set as local charset usually handles local charset strings. You set the UNICODE_STRINGS parameter in Designer. See the section

“Setting dynamic SQL parameters in a universe” on page 36 for complete information on setting dynamic SQL parameters for a universe in Designer. You set the UNICODE_PATTERN parameter in the PRM file for the target RDBMS. See the chapter “Setting data access SQL parameters (PRM)” on page 69 for information on setting PRM parameters.

Setting unicode support for a universe in Designer

To set unicode support for a universe in Designer: 1. Start Designer.

2. Open the universe that you want to access unicode enabled middleware. 3. Select File > Parameters.

4. Click the Parameter tab.

5. Scroll down the list of SQL parameters to UNICODE_STRINGS parameter.

6. Click UNICODE_STRINGS.

7. Remove No in the Value box, and type Yes. 8. Click the Replace button.

(38)

Setting dynamic SQL parameters in a universe

3

10. Save and export the universe.

Setting dynamic SQL parameters in a

universe

In Designer, you can dynamically configure certain SQL parameters that are common to most RDBMS to optimize the SQL generated in BusinessObjects and WebIntelligence products using the universe.

Using parameter (PRM) files in previous versions of Designer

In previous versions of Designer, the SQL generation parameters used by a universe were maintained and edited in a separate file called a parameters (PRM) file. The values set in the PRM file applied to all universes using the associated data access driver defined for a connection.

Many of the SQL parameters that are used to optimize query generation are now controlled within an individual universe file. The PRM file is now no longer used for the query generation parameters that you can set in Designer. PRM files are still used for parameters that are database specific.

Note:

See the chapter Setting universe dynamic SQL parameters for a complete reference on the universe SQL parameters available in Designer.

Setting the SQL parameters dynamically in Designer

Many of the parameters common to most supported RDBMS middleware are available for editing in the Parameters tab in the universe parameters dialog box (File > Parameters > Parameter).

These parameters apply only to the active universe, and are saved in the UNV file. When you modify an SQL parameter for a universe in Designer, the value defined in Designer is used, and not the value defined in the PRM file associated with the data access driver for the connection.

Editing SQL generation parameters

You can modify the values for SQL parameters that determine SQL generation in products using the universe.

See the chapter Setting universe dynamic SQL parameters for a complete reference on the universe SQL parameters available in Designer.

(39)

1. Select File > Parameters.

The Parameters dialog box appears. 2. Click the Parameter tab.

The Parameter page appears.

3. Edit, add, or remove parameters as follows:

To... Then do the following

Add a new parameter 1. Click any parameter in the list. 2. Type a name in the Name box 3. Type a value in the Value box. 4. Click Add.

The new value appears at the bottom of the list

Change name or value 1. Click a parameter in the list. 2. Type a new name in the Name box 3. Type a new value in the Value box. 4. Click Replace.

The value is replaced by the new definition.

Delete a parameter 1. Click the parameter that you want to remove from the list.

(40)

Optimizing data access by editing data access parameter files

3

4. Click OK. Note:

The SQL generation parameter values that you set in a universe, are only available to products using that universe.

Optimizing data access by editing data access

parameter files

Certain data access parameters can be modified to optimize BusinessObjects and WebIntelligence access to data for all installed data access drivers, a specific data access driver, or the SQL generation parameters for a specific data access driver.

Data access parameters are listed in three XML files:

•

General configuration for all data access drivers (CS.CFG)

•

Data access driver general configuration (<driver>.SBO)

•

Data access driver SQL parameters (<driver>.PRM)

These files are all in XML format. You should use an XML editor, for example XML Spy to edit these files.

Each of these files is described briefly below. Full descriptions and editing procedures are described in the reference chapter for each file at the end of the book.

Note:

Certain SQL generation parameters that were set in the PRM file in previous versions of BusinessObjects products, are now available at the universe level. These parameters are edited in a universe and are available to all reporting products using the universe. See the section “Setting dynamic SQL parameters in a universe” on page 36 for more information.

How are the data access files used by a connection?

Connection Server uses the CFG, SBO, and PRM data access files to manage the parameters of a connection between a data access driver and the target middleware. The default parameters used by a connection are defined in these three files. Some of these parameters can be modified in the CFG file to apply to all data access drivers, or set at the driver level (SBO) to over-ride the general default value. Parameters in the PRM file can be modified to optimize the SQL generation for products using the connection.

(41)

•

CS.CFG: Default connection and driver settings that apply to all data access drivers.

•

SBO: Default connection and driver settings that are driver specific. these default values apply only to the target data access driver.

•

PRM: SQL operators and functions that are available to the target data access driver. The values set for these SQL operators and functions can be modified to optimize the SQL generated in Business Objects products. Each of these files is discussed briefly below. A list of all parameters available in each file, and the methods for viewing and editing the files are available in the reference chapters that correspond to each file at the back of this manual.

General configuration for all data access drivers

(CS.CFG)

The general configuration for all data access drivers (CS.CFG) file contains parameters for the following uses:

•

Char set for each locale

•

List of all files used by data access drivers to create and configure connections

•

Driver defaults that apply to all drivers, for example array fetch size and array bind size.

•

Activating traces for connection activity.

Values set in the CS.CFG file apply to all data access drivers listed in the DRIVER DEFAULTS section of the file.

The CS.CFG file is found here:

The file uses the cs.dtd in the same directory.

A description of each parameter in the CS.CFG file, and the procedure for editing the file are documented in the chapter “Setting data access general configuration parameters (CFG)” on page 109.

Data access driver general configuration

(<driver>.SBO)

The data access driver general configuration (.SBO) file contains parameters for the following uses:

(42)

3

•

List of files used by a specific data access driver to manage connections

•

Driver defaults that apply to a specific data access driver.

•

List of database aliases used for connections defined with older database engines.

Values set in the <driver>.sbo file apply to the specific <driver> data access driver.

The <driver>.sbo file is found here:

The file uses the sbo.dtdin the directory

$INSTALLDIR \dataAccess\RDBMS\connectionServer.

A description of each parameter in a SBO file, and the procedure for editing the file are documented in the chapter “Setting data access driver

configuration parameters (SBO)” on page 95.

Data access driver SQL parameters (<driver>.PRM)

The data access driver SQL parameters (.PRM) file contains parameters that have default values set for SQL generation parameters that apply to a specific data access driver.

The data access SQL parameter (.PRM) file contains parameters for the following uses:

•

Configuration parameters that specify how certain database operations are handled by the data access driver.

•

Date operators.

•

A list of the SQl operators supported by the data access driver.

•

A list of the database functions supported by the data access driver. These functions are available in the Functions pane in the Edit Select or Where statement dialog box for an object in Designer. You can add finctions to this list in the PRM file, if the target RDBMS supports the function and it is not available by default in the PRM file.

A description of each parameter in a PRM file, and the procedure for editing the file are documented in the chapter “Setting data access SQL parameters (PRM)” on page 69.

Any modification to this file applies only to the specified data access driver. The <driver>.prm file is found here:

$INSTALLDIR \dataAccess\RDBMS\connectionServer\<RDBMS>\

(43)

The file uses the dbparameter.dtd in the directory

$INSTALLDIR \dataAccess\RDBMS\connectionServer.

Certain SQL parameters can be set from only the universe itself. These dynamic SQl parameters can be editied from Designer (File > Parameters > Parameter). The SQL generation parameters that can be set for a universe are described in the section “Setting dynamic SQL parameters in a universe”

(44)

3

(45)

(46)

Configuring specific data access drivers Overview

4 Overview

This chapter contains data access information specific to each supported RDBMS. Consult the BusinessObjects 11 readme for the latest release issues.

Data access driver specific information is available for the following RDBMS:

•

Oracle

•

Teradata

Oracle

This section lists connection and data access issues that are specific to Oracle.

WebIntelligence Web Intelligence

Oracle connection configuration

The default Oracle middleware to server configuration is as follows:

•

Oracle client middleware version 8.1.x to Oracle DB Server 8i.

(47)

If your client middleware to Oracle server is not as listed above; for example, if you are using client middleware version 8 and accessing an Oracle server 9, then depending on your configuration, you must perform the following steps to ensure that your connection to an Oracle RDBMS can function:

Teradata

This section lists connection and data access issues that are specific to Teradata.

If your configuration is...

Then you must... Oracle client middleware

version 8.1.x to Oracle DB Server 9i

1. Open the oracle.sbo in a text or xml editor. This file is here: $INSTALLDIR /dataAccess/ RDBMS/connectionServer/oracle/oracle.sbo 2. Go to the section Databases.

3. Set The your connection database engine to "Oracle 9" and modify the .sbo parameter file as follows:

4.

Comment out the Library line. [Oracle 9]

; Library=dbd_oci9

The .sbo file is found in the directory $INSTALLDIR /dataAccess/RDBMS/ connectionServer/oracle/oracle.sbo Oracle client middleware

version 9x to Oracle DB Server 8i

Set your connection database engine to "Oracle 8.1" and modify the .sbo parameter file as follows: Add a Library parameter and set it as follows: [Oracle 8.1]

Library=dbd_oci9

The .sbo file is found in the directory $INSTALLDIR/dataAccess/RDBMS/ connectionServer/oracle/oracle.sbo

(48)

Configuring specific data access drivers Teradata

4 Using Run in Quiet mode to avoid query freeze up on

client

Queries run and then cancelled against a Teradata source can become blocked due to a Cancel query message that appears at the server end, and blocks the query until the message is cleared.

To ensure that an end user does not receive a Cancel Query message when they cancel a query from a browser, you should ensure that the option “Run in quiet mode” is selected in the the Advanced tab of the Teradata ODBC Driver dialog box.

If “Run in quiet mode” is not selected, then the Cancel query message appears at the Web Intelligence server end, and the query remains blocked until OK is clicked at the server end. This option is not selected by default.

(49)

(50)

Data type conversion reference Overview

5 Overview

This chapter contains data conversion tables for each supported RDBMS. Business Objects supports four datatypes:

•

date

•

character

•

number

•

long text

Data conversion tables are available for the following RDBMS:

•

IBM DB2

•

Informix

•

Oracle

•

Red Brick

•

Sybase

•

Teradata

IBM DB2

The following table lists the IBM DB2 internal datatypes and their equivalent in Business Objects products:

IBM/DB2 datatype BusinessObjects datatype

CLOB LONG TEXT

CHARACTER CHARACTER DATE DATE DECIMAL NUMBER DOUBLE NUMBER FLOAT NUMBER INTEGER NUMBER

LONG VARCHAR LONG TEXT

NUMERIC NUMBER

SMALLINT NUMBER

(51)

Informix

The following table lists the Informix internal datatypes and the equivalent in Business Objects products:

Microsoft SQL Server

The following table lists the Microsoft SQL Server internal datatypes and their BusinessObjects equivalent

TIMESTAMP DATE

VARCHAR CHARACTER

IBM/DB2 datatype BusinessObjects datatype

Informix datatype BusinessObjects datatype

CHAR CHARACTER DATE DATE DATETIME DATE DECIMAL NUMBER FLOAT NUMBER INTEGER NUMBER MONEY NUMBER NCHAR CHARACTER NVARCHAR CHARACTER SERIAL NUMBER SMALLINT NUMBER SMALLFLOAT NUMBER

TEXT LONG TEXT

VARCHAR CHARACTER

Microsoft SQL Server datatype BusinessObjects datatype

BIT NUMBER

BOOLEAN NUMBER

(52)

Data type conversion reference Oracle

5 Oracle

The following table lists the Oracle internal datatypes and their equivalent in Business Objects products:

* indicates Oracle 9i datatype

DECIMAL NUMBER FLOAT NUMBER INT NUMBER MONEY NUMBER NUMERIC NUMBER REAL NUMBER SMALLDATETIME DATE SMALLINT NUMBER SMALLMONEY NUMBER TEXT CHARACTER TINYINT NUMBER VARCHAR CHARACTER

Microsoft SQL Server datatype BusinessObjects datatype

Oracle datatype BusinessObjects datatype

CHAR CHARACTER

CLOB LONG TEXT

DATE DATE

FLOAT NUMBER

INTEGER NUMBER

LONG LONG TEXT

NUMBER NUMBER

VARCHAR CHARACTER

(53)

Red Brick

The following table lists the Red Brick internal datatypes and their BusinessObjects equivalent

TIME and TIMESTAMP support in a WHERE clause

Objects that use the TIME and TIMESTAMP datatype are not supported in a WHERE clause for queries run against a Red Brick database. You can set the properties of an object in a universe to support the use of TIME and

TIMESTAMP in the WHERE clause as follows:

To support TIME or TIMESTAMP datatype object in WHERE clause: 1. From Designer, double-click an object in the Universe pane.

The Edit Properties of Object dialog box appears. 2. Click the Definition tab.

3. Select Date from the Type drop down list. 4. Click the Advanced tab.

5. In the Database Format textbox type the following

6. Click OK.

Red Brick datatype BusinessObjects datatype

CHAR CHARACTER DATE DATE DECIMAL NUMBER FLOAT NUMBER INTEGER NUMBER NUMERIC NUMBER SMALLINT NUMBER TIME DATE TIMESTAMP DATE For... Type TIME support HH:MM:SS

(54)

Data type conversion reference Sybase

5 Sybase

The following table lists the Sybase internal datatypes and their equivalent in Business Objects products

Teradata

The following table lists the Teradata internal datatypes and their equivalent in Business Objects products:

Sybase datatype BusinessObjects datatype

BIT NUMBER CHAR CHARACTER DATETIME DATE DECIMAL NUMBER FLOAT NUMBER INT NUMBER MONEY NUMBER NUMERIC NUMBER REAL NUMBER SMALLDATETIME DATE SMALLINT NUMBER SMALLMONEY NUMBER TINYINT NUMBER VARCHAR CHARACTER

Teradata datatype BusinessObjects datatype

DATE DATE DECIMAL NUMBER FLOAT NUMBER INTEGER NUMBER SMALLINT NUMBER BYTEINT NUMBER

(55)

DATETIME DATE

VARCHAR CHARACTER

(56)

Data type conversion reference Teradata

(57)

(58)

Setting universe dynamic SQL parameters Overview

6 Overview

This chapter provides an alphabetical reference for the SQL generation parameters listed in the Parameter page of the Universe Parameters dialog box in Designer. These are SQL parameters that are common to most data access drivers. Each parameter is valid for the universe in which it is set. Other RDBMS specific and connection parameters are listed in the data access parameter (PRM) file for the target data access driver.

Refer to the section “Setting dynamic SQL parameters in a universe” on page 36 for information on how to set the dynamic SQL parameters in Designer. Other RDBMS specific SQL parameters are described in the chapter “Setting universe dynamic SQL parameters” on page 55.

Dynamic SQL parameters in a universe

Parameters used for SQL generation can be set for a universe. In previous versions of Business Objects products, all SQL parameters were set in the <RDBMS>.PRM file. This file was a static text file that applied to all universes using the data access driver.

The PRM file is still used for many RDBMS specific SQL and connection parameters, however, SQL parameters common across RDBMS are now modified within an individual universe. These parameters apply only to the current universe, so universes using the same data access driver, can have different SQL generation behavior.

These dynamic SQL parameters are saved in the .UNV file, and are accessible only from the universe.

This section lists and describes the dynamic SQL generation parameters available to universes. Refer to the section Setting dynamic SQL parameters in a universe in the Managing Connections chapter for information on accessing and modifying these parameters. This parameter reference is also available in the Designer’s Guide.

ANSI92

ANSI92 = Yes|No

(59)

AUTO_UPDATE_QUERY

AUTO_UPDATE_QUERY = Yes|No

BLOB_COMPARISON

BLOB_COMPARISON = Yes|No

Default No

Description Specifies whether the SQL generated complies to the ANSI92 standard.

Yes: Enables the SQL generation compliant to ANSI92 standard.

No: SQL generation behaves according to the PRM parameter OUTER_JOIN_GENERATION.

Values Yes|No

Default Yes

Description Determines what happens when an object in a query is not available to a user profile.

Yes: Query is updated and the object is removed from the query.

No: Object is kept in the query.

Values Yes|No

Default No

Can be edited? No

Description Species if a query can be generated with a DISTINCT statement when a BLOB file is used in the SELECT statement. It is related to the setting "No Duplicate Row" in the query properties.

Yes: The DISTINCT statement can be used within the query.

No: The DISTINCT statement cannot be used within the query even if the query setting "No Duplicate Row" is on.

(60)

Setting universe dynamic SQL parameters Dynamic SQL parameters in a universe

6 BOUNDARY_WEIGHT_TABLE

BOUNDARY_WEIGHT_TABLE = Integer 32bits [0-9]

COLUMNS_SORT

COLUMNS_SORT=Yes|No

Values Integer 32bits [0-9]

Default -1

Description Allows you to optimize the FROM clause when tables have many rows.

If the table size is greater than the entered value, the table is declared as a subquery:

FROM (SELECT col1, col2,...., coln FROM Table_Name WHERE simple condition).

A simple condition is defined as not having a subquery, and not having EXCEPT or BOTH operators.

Limitations Optimization is not implemented when:

•

the operator OR is in the query condition

•

only one table is involved in the SQL

•

the query contains an outer join

•

no condition is defined on the table that is being optimized

•

the table being optimized is a derived table.

Values YES Columns are displayed in alphabetical order NO Columns are displayed in the order they were retrieved from the database

Default No

Description Determines the order that columns are displayed in tables in the Structure pane.

(61)

COMBINE_WITHOUT_PARENTHESIS

COMBINE_WITHOUT_PARENTHESIS=No

COMBINED_WITH_SYNCHRO

COMBINED_WITH_SYNCHRO = Y|N

CORE_ORDER_PRIORITY

CORE_ORDER_PRIORITY = Yes|No

Values YES Removes the parentheses. NO Leaves the parentheses.

Default No

Description Specifies whether or not to encapsulate a query with parentheses when it contains UNION, INTERSECT or MINUS operators. Used with RedBrick.

Values Yes|No

Default No

Description Specifies whether to allow a query to execute that contains UNION, INTERSECTION, or EXCEPT operators, and whose objects in each subquery are incompatible.

Yes: Specifies that you do allow a query to execute that contains UNION, INTERSECTION and EXCEPT operators, and whose objects in each subquery are incompatible. This type of query generates

synchronization (two blocks in the report).

No: Specifies that you do not allow a query to execute that contains UNION, INTERSECTION and EXCEPT operators, and whose objects in each subquery are incompatible. When the query is executed the following error message is displayed: “This query is too complex. One of the subqueries contains incompatible objects.” This is the default value.

(62)

Setting universe dynamic SQL parameters Dynamic SQL parameters in a universe

6 CORRECT_AGGREGATED_CONDITIONS_IF_DRI

LL

CORRECT_AGGREGATED_CONDITIONS_IF_DRILL = Yes|No

CUMULATIVE_OBJECT_WHERE

CUMULATIVE_OBJECT_WHERE = Y|N Default Yes

Description Specifies in which order you want classes and objects to be organized once two or more universes are linked in Designer.

Yes: Specifies that classes and objects follow the order defined in the kernel universe.

No: Specifies that classes and objects follow the order defined in the derived universe. This is the default value.

Values Yes|No

Default No

Description Specifies whether BusinessObjects can aggregate measures in queries and conditions.

Yes: BusinessObjects can aggregate measures separately in the main query and the condition, if the query is drill enabled.

No: BusinessObjects cannot aggregate measures separately in the main query and the condition, if the query is drill enabled.