Business objects CONNECTION SERVER XI 3.0 User Manual

Data Access Guide

BusinessObjects Connection Server XI 3.0

© 2008 Business Objects. All rights reserved. Business Objects owns the following U.S. patents, which may cover products that are offered and licensed by Business Objects: 5,555,403; 5,857,205; 6,289,352; 6,247,008; 6,490,593; 6,578,027; 6,831,668; 6,768,986; 6,772,409; 6,882,998; 7,139,766; 7,299,419; 7,194,465; 7,222,130; 7,181,440 and 7,181,435. Business Objects and the Business Objects logo, BusinessObjects, Business Objects Crystal Vision, Business Process On Demand, BusinessQuery, Crystal Analysis, Crystal Applications, Crystal Decisions, Crystal Enterprise, Crystal Insider, Crystal Reports, Desktop Intelligence, Inxight, the Inxight Logo, LinguistX, Star Tree, Table Lens, ThingFinder, Timewall, Let there be light, Metify, NSite, Rapid Marts, RapidMarts, the Spectrum Design, Web Intelligence, Workmail and Xcelsius are trademarks or registered trademarks in the United States and/or other countries of Business Objects and/or affiliated companies. All other names mentioned herein may be trademarks of their respective owners.

Third-party Contributors

Business Objects products in this release may contain redistributions of software licensed from third-party contributors. Some of these individual components may also be available under alternative licenses. A partial listing of third-party contributors that have requested or permitted acknowledgments, as well as required notices, can be found at: http://www.businessobjects.com/thirdparty

Data access basics 9Chapter 1

About Connection Server...........................................................................10

Components of a connection.....................................................................10

About data access configuration files........................................................12

Creating a connection 15Chapter 2

Before you create a connection.................................................................16

Checking connection configuration: cscheck.............................................16

Accessing the New Connection wizard from the Designer Connections

list..............................................................................................................27

Accessing the New Connection wizard from the Parameters dialog box...28

Using the New Connection Wizard............................................................28

System architecture..............................................................................11

About data access drivers....................................................................11

Global configuration files......................................................................13

Driver configuration files.......................................................................13

Displaying help on the cscheck tool.....................................................17

Running the cscheck tool.....................................................................18

Check tool—function overview.............................................................18

Check tool—list....................................................................................19

Check tool—drivers search..................................................................20

Check tool—find...................................................................................21

Check tool—middleware......................................................................22

Check tool—accessdriver.....................................................................23

Check tool—connectivity......................................................................24

Check tool—ping .................................................................................25

Data Access Guide 3

Contents

Creating a new connection ..................................................................29

Database Middleware Selection dialog box ........................................30

Configuration parameters dialog box ..................................................34

Custom Parameters dialog box............................................................35

About JDBC connections ..........................................................................36

Creating a JDBC connection ....................................................................36

JDBC SBO example file structure........................................................37

About JavaBean connections....................................................................38

Creating a JavaBean connection...............................................................38

JavaBean SBO example file structure ................................................39

Configuring data access global parameters 41Chapter 3

About global parameters............................................................................42

About the cs.cfg global configuration file...................................................42

Viewing and editing cs.cfg.........................................................................43

Configuring the <driver defaults> parameters...........................................43

Configuring <Settings> parameters...........................................................44

Charset List Extension.........................................................................44

Config File Extension...........................................................................45

Description Extension...........................................................................45

Enable Failed Load..............................................................................45

Load Drivers On Startup.......................................................................46

Max Pool Time......................................................................................47

SQL External Extension.......................................................................48

SQL Parameter Extension....................................................................48

Strategies Extension............................................................................48

Configuring CORBA access......................................................................49

Configuring the Distribution section for CORBA access............................50

4 Data Access Guide

Contents

Configuring data access driver parameters 51Chapter 4

Configuring driver parameters...................................................................52

Data access configuration files.............................................................52

Installed SBO files................................................................................53

Editing an SBO file...............................................................................54

SBO file parameter reference 55Chapter 5

SBO parameter categories........................................................................56

SBO file structure.................................................................................56

Default SBO parameters......................................................................57

Informix SBO parameters.....................................................................75

JavaBean SBO parameters..................................................................77

JDBC SBO parameters........................................................................78

ODBC SBO parameters.......................................................................79

ODBC3 SBO parameters.....................................................................81

OLE DB SBO parameters....................................................................82

Sybase ASE/CTLIB..............................................................................83

Configuring SQL generation parameters for a universe 85Chapter 6

About SQL generation parameters for a universe.....................................86

Editing SQL generation parameters in a universe.....................................86

Universe SQL parameters reference...................................................87

Configuring SQL generation parameters for a database 105Chapter 7

About SQL generation parameters..........................................................106

About PRM files.......................................................................................106

Parameter file structure......................................................................108

Viewing and editing PRM file parameters..........................................109

Viewing and editing function help text................................................110

Data Access Guide 5

Contents

PRM file Configuration reference............................................................111

BACK_QUOTE_SUPPORTED..........................................................112

CASE_SENSITIVE.............................................................................112

CHECK_OWNER_STATE..................................................................112

CHECK_QUALIFIER_STATE.............................................................113

COMMA..............................................................................................113

CONCAT............................................................................................114

CONSTANT_SAMPLING_SUPPORTED...........................................114

DATABASE_DATE_FORMAT............................................................115

DATATYPE_BLOB..............................................................................115

DATATYPE_DOUBLE........................................................................115

DATATYPE_DTM...............................................................................116

DATATYPE_INT.................................................................................116

DATATYPE_NULL..............................................................................116

DATATYPE_STRING..........................................................................116

DATE_WITHOUT_QUOTE................................................................117

EXT_JOIN..........................................................................................117

EXT_JOIN_INVERT...........................................................................118

EXTERN_SORT_EXCLUDE_DISTINCT...........................................118

GROUPBY_EXCLUDE_COMPLEX...................................................119

GROUPBY_WITH_ALIAS..................................................................119

GROUPBY_WITHOUT_CONSTANT.................................................120

GROUPBYCOL..................................................................................120

IDENTIFIER_DELIMITER..................................................................120

IF_NULL.............................................................................................121

INTERSECT.......................................................................................121

KEY_INFO_SUPPORTED.................................................................122

LEFT_OUTER....................................................................................122

LENMAXFORCOLUMNNAME...........................................................123

LENMAXFORTABLENAME...............................................................123

LENMAXFORVARCHAR....................................................................123

6 Data Access Guide

Contents

MINUS................................................................................................124

NO_DISTINCT...................................................................................124

NULL_IN_SELECT_SUPPORTED....................................................125

OLAP_CLAUSE.................................................................................125

OUTERJOINS_GENERATION...........................................................126

OVER_CLAUSE.................................................................................129

OWNER..............................................................................................129

PERCENT_RANK_SUPPORTED......................................................130

PREFIX_SYS_TABLE........................................................................130

QUALIFIER........................................................................................131

QUOTE_OWNER...............................................................................131

RANK_SUPPORTED.........................................................................132

REFRESH_COLUMNS_TYPE...........................................................132

REVERSE_TABLE_WEIGHT.............................................................132

RIGHT_OUTER..................................................................................133

RISQL_FUNCTIONS..........................................................................133

SEED_SAMPLING_SUPPORTED.....................................................134

SORT_BY_NO...................................................................................135

UNICODE_PATTERN........................................................................135

UNION................................................................................................135

USER_INPUT_DATE_FORMAT........................................................136

USER_INPUT_NUMERIC_SEPARATOR..........................................137

Data type conversion reference 139Chapter 8

Data type conversion ..............................................................................140

IBM DB2.............................................................................................140

Informix...............................................................................................141

Microsoft SQL Server.........................................................................142

Oracle.................................................................................................143

Red Brick............................................................................................144

TIME and TIMESTAMP support in a WHERE clause........................145

Data Access Guide 7

Contents

Sybase...............................................................................................145

Teradata.............................................................................................146

Business Objects information resources 149Chapter 9

Documentation and support....................................................................150

Useful addresses at a glance..................................................................150

Documentation.........................................................................................151

What's in the documentation set?......................................................151

Where is the documentation?.............................................................151

Send us your feedback.......................................................................152

Customer support, consulting and training..............................................152

How can we support you?..................................................................152

Looking for the best deployment solution for your company?............153

Looking for training options?..............................................................153

Get More Help 155Appendix A

Index 159

8 Data Access Guide

Data access basics

About Connection Server

Connection Server is the Business Objects data access software that manages the connection between the application and the data source.

Connection Server allows Business Objects applications such as Designer and Web Intelligence users to connect to and run queries against a data source.

Connection Server does not have a user interface. You create and administer connections from the user interface of Business Objects applications such as Designer, or by editing the configuration files.

Creating connections: You create connections using a connection wizard

•

available from Business Objects applications such as Designer. You can modify some connection parameters from the application.

Optimizing data access: You can optimize the way that data is passed

•

through Connection Server by modifying data access parameter files and SQL generation files. These files are in XML format, and are installed with Connection Server. You can set parameter values for either a specific driver, or for all installed data access drivers.

Components of a connection

Connection Server manages the data access connection between a Business Objects application and a target database. A Business Objects data access connection consists of the following components:

Connection Server manages the connection between the Business Objects

•

application and the data source. For example, it handles requests for data from the application.

A data access driver is database-specific software that manages the

•

connection between Connection Server and the database middleware.

• Configuration files define parameters to configure the connection between:

the Business Objects application and Connection Server

•

the Business Objects application and the data access driver

•

Connection Server and the data access driver

•

10 Data Access Guide

System architecture

The diagram below details where Connection Server and data access drivers fit into a Business Objects configuration.

Data access basics

Components of a connection

About data access drivers

Data access drivers provide the connection between Connection Server and a data source. A database requires a data access driver in order that it can be accessed by a Business Objects application.

Business Objects applications include data access drivers that you can use to configure connections to your databases. The data access drivers that are included can depend on your licence. You can also develop your own data access drivers for use with non-standard databases.

Data Access Guide 11

Data access basics

About data access configuration files

To configure a database connection for which you do not have a driver, you need to obtain the required drivers before you can create the connection. The following options are available for obtaining a driver:

Contact your Business Objects representative to determine if there is a

•

driver available, and if you are licenced to use it.

Use the Driver Deveopment Kit (DDK) to develop a driver to use. Contact

•

your Business Objects representative for details.

When you create a new connection, you select the appropriate data access driver for the target datasource. For example, if you access an Oracle 10g database, you must install the appropriate middleware (Oracle 10g Client), then the Business Objects Oracle data access driver.

For an up-to-date list of supported data access drivers, check the Business Objects Support Site at www.support.businessobjects.com, or contact your BusinessObjects representative.

About data access configuration files

Data access configuration files are installed when you install Business Objects products. The configuration files can be divided into two levels:

Global level: These configuration files apply to all connections.

•

Driver level: These configuration files apply to specific drivers.

•

Of the files described in this section:

Some of the files contain parameters that you can modify to optimize or

•

customize data access.

• Other files listed must not be modified. They are described purely to

explain their function.

In addition to the configuration files that control a connection, each data access driver has a .prm configuration file. These files are used by Business Objects applications such as Designer. They control the way in which the Universe generates SQL. This functionality allows you to configure Universe-specific SQL generation properties.

Note: connectionserver-install-dir variable in this guide

In this guide, the variable connectionserver-install-dir is the install root path for the data access files used by Business Objects applications.

12 Data Access Guide

Under Windows, the derault connectionserver-install-dir = :C:\Program

Files\Business Objects\BusinessObjects Enterprise

12.0\win32_x86\dataAccess

Global configuration files

The cs.cfg global configuration file files used by all data access drivers is installed in the following location:

connectionserver-install-dir\connectionServer

•

The cs.cfg file contains parameters that apply to all installed data access drivers.

Related Topics

• About global parameters on page 42

Driver configuration files

Data access basics

About data access configuration files

The configuration files used by data access drivers are installed in the following path:

On a Windows system:

•

connectionserver-install-dir \connectionServer\RDBMS

On a Unix system:

•

connectionserver-install-dir /RDBMS/connectionServer

where RDBMS is the name of the database technology that uses the configuration file.

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

Data Access Guide 13

Data access basics

About data access configuration files

Driver specific file

<driver><lan guage>.cod

Yes<driver>.sbo

Yes<driver>.prm

There is a .sbo file for each supported network protocol, or database middleware used to connect to a database. Defines the specific connectivity configuration for each database.

Defines parameters that affect the way that a Business Objects application generates SQL.

Stores information related to connection definitions. Defines the fields that appear when you create a new connection.

ExampleDescriptionCan be edited?

oracle.sbo

oracle.prm

oracleen.cod

Related Topics

• SBO parameter categories on page 56

• PRM file Configuration reference on page 111

• About SQL generation parameters on page 106

14 Data Access Guide

Note: Do not

modify these files.

Creating a connection

Before you create a connection

This section lists the things you need to do before you can create a connection.

Ensure that your platform conforms to the platforms supported for

•

Business Objects connections.

Ensure that the database middleware is installed correctly, and that you

•

can access your database through either your computer or a server.

You can use the cscheck tool to check your configuration. See the

cscheck tool documentation for details.

Ensure that you have all the information necessary to access your

•

database, for example the database login name and password.

Install the Business Objects product that you will use, including the

•

appropriate data access driver.

Check that all of the Business Objects services have started successfully.

•

Refer to the Readme notice that comes with your Business Objects

•

product to check for any configuration changes that your environment and software might require.

Related Topics

• Checking connection configuration: cscheck on page 16

Checking connection configuration: cscheck

The Connection Server software includes a command line utility that you can use to check your datasource connection infrastructure. You can use the cscheck tool to check your client middleware and the installed Business Objects data access drivers at any time.

Note: The results of all checks apply to your local machine, from which you

run the tool.

The cscheck tool is installed in the following directory: connectionserver-in

stall-dir/connectionServer/tools

16 Data Access Guide

Creating a connection

Checking connection configuration: cscheck

You run the cscheck tool from a command console (DOS or shell). The output is displayed on the screen. You can specify that the output is generated as XML format, or you can suppress output, to use the tool in a script.

The cscheck tool can perform the following functions on your local machine:

Return details of all the connectivities, that is network layers and

•

databases, that the installation can support.

Return details of the data access drivers installed on your local machine.

•

Return details of the connectivities installed on your local machine.

•

Check for a valid middleware installation for a supplied network layer and

•

database client.

Check for a valid data access driver installation for a supplied network

•

layer and database client.

Check if a connection can be esbablished to a given database.

•

Related Topics

• Check tool—function overview on page 18

Displaying help on the cscheck tool

The cscheck tool provides functionality to:

Display general help on the cscheck utility.

•

Display help on each available cscheck function.

•

The help can be displayed in any language selected when you installed your Business Objects applications.

To display general help on cscheck, use the following syntax:

Figure 2-1: Command help syntax

cscheck --help|h --language|l { language }

To display help on a function, use the following syntax, where functionName is the name of the function for which you want help, and language is the language in which to display the help:

Figure 2-2: Function help syntax

cscheck --help|h { functionName }--language|l{ language }

Data Access Guide 17

Creating a connection

Checking connection configuration: cscheck

Example:

To display help in English on the cscheck tool, use the following command:

cscheck --help

To display help in French on the connectivity function, use the following command:

cscheck --language fr --help connectivity

Running the cscheck tool

You can run the cscheck tool at any time after you have installed your Business Objects application software.

1. Open a command console.

2. Change directory to the path where the tool is installed.

3. Enter cscheck with the correct parameters to find the information that

you want.

4. Review the returned information.

Check tool—function overview

From a command console, you use the cscheck command with the appropriate function and its arguments to return the results that you want.

cscheck commands have the following structure. Some of the parameters

are optional.

Figure 2-3: cscheck syntax

cscheck --language|l { output language }--xml|x--mute|m function namefunction options

The first part of the command controls the output format:

<output language> or l followed by the language specified in ISO-639

•

standard. This is optional. The default language is English.

--xml or x specifies that the output is in XML format. This is optional. The

•

default output is text displayed on the screen.

18 Data Access Guide

Creating a connection

Checking connection configuration: cscheck

--mute specifies that the output is not generated. You would use this

•

switch if you were using the tool in a script that checked the returned status. This is optional. The default is that output is generated.

The remaining part of the command consists of the function and its option arguments.

<function name> can take the following values. Each function has a short version that you can use in place of the full function name:

list, or lt

•

driverssearch, or ds

•

find, or fd

•

middleware, or mw

•

accessdriver, or ad

•

connectivity, or ct

•

ping, or pg

•

Related Topics

• Check tool—accessdriver on page 23

• Check tool—connectivity on page 24

• Check tool—drivers search on page 20

• Check tool—find on page 21

• Check tool—list on page 19

• Check tool—middleware on page 22

• Check tool—ping on page 25

Check tool—list

This function returns a list of the supported network layers and database engines. For example you could use it to determine the correct values to use with other check tool functions.

Data Access Guide 19

Creating a connection

Checking connection configuration: cscheck

Note: This function returns the full list of supported data access drivers and

middleware, including those that are not necessarily installed on your machine.

Figure 2-4: list syntax

cscheck |list||lt|

Example:

The following command lists all network layers and database engines supported by the Business Objects installation on the current machine.

cscheck list

Related Topics

• Check tool—function overview on page 18

• Displaying help on the cscheck tool on page 17

Check tool—drivers search

This function returns a list of the installed data access drivers.

Figure 2-5: driverssearch syntax

cscheck |driverssearch||ds|

Example:

The following command lists all data access drivers installed on the machine.

cscheck driverssearch

Related Topics

• Check tool—function overview on page 18

• Displaying help on the cscheck tool on page 17

20 Data Access Guide

Check tool—find

This function lists the available connectivity types, that is middleware and database clients, that are available from the local machine. This includes:

Connectivity types available on the local machine

•

Connectivity types available using the CORBA communication layer

•

Connectivity types available using the HTTP communication layer

•

Java connectivity types available on the local machine

•

Figure 2-6: find syntax

cscheck |find||fd| -m { Connection Server access mode }

Table 2-1: Function input parameters

Creating a connection

Checking connection configuration: cscheck

The mode in which the client application accesses Connection Server:

local: lists connectivity types

•

available on the local machine.

corba: lists connectivity types

•

available using CORBA.

Connection Server access mode (-m)

Example: Finding local connectivities

The following command returns a list of the data access drivers on the local machine that can be loaded by Connection Server.

cscheck find –m local

http:lists connectivity types avail-

•

able using HTTP.

java: lists Java connectivity types

•

available on the local machine.

extended: lists local, java, and

•

corba connectivity types.

Data Access Guide 21

Creating a connection

Checking connection configuration: cscheck

Example: Finding CORBA server connectivities

The following command returns a list of the data access drivers available from a CORBA server.

cscheck find –m corba

Related Topics

• Check tool—function overview on page 18

• Displaying help on the cscheck tool on page 17

Check tool—middleware

For a supplied network layer and database client, this function checks for a valid installation of the client middleware. To check both the middleware and data access driver for a supplied network layer and database client, you can use the connectivity function.

Figure 2-7: middleware syntax

cscheck |middleware||mw|-c { network layer }-d{ database client }

Table 2-2: Function input parameters

network layer (-c)

database client (-d)

Example:

The following command checks for a valid installation of the Oracle Client 9 middleware on the local machine. It creates an XML file of the output:

c:\result.xml

22 Data Access Guide

The network layer that the database middleware uses, as returned by the

find function.

The database to check, as returned by the find function.

cscheck --xml middleware -c "Oracle Client" -d "Oracle 9" > c:\result.xml

Related Topics

• Check tool—function overview on page 18

• Displaying help on the cscheck tool on page 17

• Check tool—connectivity on page 24

• Check tool—accessdriver on page 23

Check tool—accessdriver

For a supplied network layer and database client, this function checks for a valid data access driver installation. To check both the middleware and data access driver for a supplied network layer and database client, you can use the connectivity function.

Figure 2-8: accessdriver syntax

cscheck |accessdriver||ad|-c{ network layer }-d{ database client }

Creating a connection

Checking connection configuration: cscheck

Table 2-3: Function input parameters

The network layer that the database

network layer ( -c )

database client (-d)

Example:

The following command checks for a valid installation of an Oracle 9 data access driver, and displays the output in French:

cscheck -l fr accessdriver -c "Oracle Client" -d "Oracle 9"

middleware uses, as returned by the

find function.

The database to check, as returned by the find function.

Data Access Guide 23

Creating a connection

Checking connection configuration: cscheck

Related Topics

• Check tool—function overview on page 18

• Displaying help on the cscheck tool on page 17

• Check tool—list on page 19

Check tool—connectivity

For the supplied network layer and database client, this function checks that both the installed middleware and the data access driver are valid.

You can check each individually using the middleware and the accessdriver functions. You can use the ping function to check if you can connect to a specific database.

Figure 2-9: connectivity syntax

cscheck |connectivity||ct|-c{ network layer }-d{ database client }

Table 2-4: Function input parameters

network layer (-c)

database client (-d)

Example:

The following command checks the installed Oracle client middleware, and the Oracle 9 data access driver. The command writes the output to a text file: c:\result.txt .

cscheck -l en connectivity -c "Oracle Client" -d "Oracle 9">c:\result.txt

Related Topics

• Check tool—function overview on page 18

• Displaying help on the cscheck tool on page 17

24 Data Access Guide

The network layer that the database middleware uses, as returned by the

find function.

The database to check, as returned by the find function.

• Check tool—find on page 21

• Check tool—accessdriver on page 23

• Check tool—middleware on page 22

• Check tool—ping on page 25

Check tool—ping

This function attempts to access a given database using the supplied details.

Figure 2-10: ping syntax

cscheck ping|pg|-m{ Connection Server access mode }-c{ network layer }-d{ database client }-u{ user name }-p{ password }-s{ datasource }-t{

database }-r{ host name }-j{ PID }

Table 2-5: Function input parameters

Creating a connection

Checking connection configuration: cscheck

The mode in which the client application accesses Connection Server:

local: Connection Server is run-

•

ning on the local machine.

Connection Server access mode (-m)

network layer (-c)

database client (-d)

user name (-u)

corba: Connection Server is run-

•

ning on a CORBA server.

http:Connection Server is running

•

on a HTTP server.

java: Connection Server uses a

•

Java data access driver on the local machine.

The database middleware for the connection to check, as returned by the

find function.

The database type, as returned by the

find function.

A valid user name for the database.

Data Access Guide 25

Creating a connection

Checking connection configuration: cscheck

password (-p)

datasource (-s)

database (-t)

host name (-r)

PID (-i)

Example: Pinging an Oracle database

The following command checks access for:

Connection Server access mode: local, that is, the database runs on

•

the local machine.

Network layer: Oracle Client

•

Database: Oracle 8.1

•

Datasource: Harlaxton

•

User name: efashion

•

Password: X2345

•

The password for the user name.

The server on which the database is running.

The database server.

For CORBA mode, the computer hosting Connection Server.

For CORBA mode, the process number of the Connection Server to ping through.

cscheck ping -m local -c "Oracle Client" -d "Oracle 8.1" -u "efashion" -p "X2345" -s "Harlaxton"

Example: Pinging a Sybase database using CORBA

The following command checks access for:

Connection Server access mode: CORBA, that is, Connection Server

•

runs on a CORBA server.

Network layer: Sybase

•

User name: syadmin

•

26 Data Access Guide

Creating a connection

Accessing the New Connection wizard from the Designer Connections list

Password: password

•

Datasource: Sybase Adaptive Server 15

•

Database: SY1

•

Database host: sybasehost

•

Process ID: 456

•

cscheck ping -m corba -c "Sybase Open Client" -d syb15 -u "syadmin" -p "password" -s "Sybase Adaptive Server 15" -t "SY1" -r "sybasehost" -i 456

Related Topics

• Check tool—function overview on page 18

• Check tool—find on page 21

Accessing the New Connection wizard

from the Designer Connections list

You can access the New Connection Wizard from any Business Objects application that allows you to create a new connection. For example, to access the New Connection wizard from Designer:

1. From the Designer user interface, select Tools, Connections.

2. Click Add.

The New Connection wizard Welcome page appears.

Related Topics

• Accessing the New Connection wizard from the Parameters dialog box

on page 28

Data Access Guide 27

Creating a connection

Accessing the New Connection wizard from the Parameters dialog box

You can access the New Connection Wizard from any Business Objects application that allows you to create a new connection. For example, to access the Connections Wizard from the Universe parameters dialog box:

1. Open Designer and select a Universe to load.

The Universe appears in the Designer work area

2. From the File menu, select Parameters.

The Universe Parameters dialog box appears.

3. From the Universe Parameters dialog box, click the New button.

The New Connection wizard starts.

Related Topics

• Accessing the New Connection wizard from the Designer Connections

list on page 27

Using the New Connection Wizard

The New Connection wizard steps you through the process of creating a connection. Use it to select a target data source, and configure the data access driver.

Use the New Connection wizard to set the following parameters:

DescriptionConnection wizard stage

Locate the database middleware to

Database middleware

28 Data Access Guide

connect to, and assign a name for the connection.

Set the connection mode, and specify the login details for connecting to the middleware.

Creating a connection

Using the New Connection Wizard

DescriptionConnection wizard stage

Configure connection parameters, for example the connection timeout details,

Configuration parameters

Custom parameters

Note: The parameters that the New Connection wizard displays vary

depending on the options that you select, for example the database middleware, and the connection mode.

Related Topics

• Database Middleware Selection dialog box on page 30

• Login Parameters dialog box on page 32

• Configuration parameters dialog box on page 34

• Custom Parameters dialog box on page 35

array fetch and bind sizes, and login time-out. These vary depending on the database to which you are connecting.

Customize parameters to optimize your connection. These vary depending on the database to which you are connecting.

Creating a new connection

You use the New Connection wizard to create a data access connection. The wizard displays a series of screens that you use to enter your connection configuration details.

The choices that are available in each screen can vary depending on the Business Objects application that you are using, and the database to which you are connecting.

The Database middleware selection dialog box lists the available

•

database vendors, databases, and middleware. Locate the database you want, and expand the database node to locate the driver that you want to use.

Data Access Guide 29

Creating a connection

Using the New Connection Wizard

Use the Login parameters dialog box to enter the login and connection

•

information for the database. These details vary depending on the driver that you selected.

On this page you can also test the connection. If the test fails, check the credentials that you entered, or check with your database administrator.

Use the Configuration parameters dialog box to configure the

•

parameters that control the connection.

Use the Custom Parameters dialog box to configure any specific custom

•

parameters that apply to the connection. This page is available only for specific connections.

Values on the Custom Parameters dialog box should only be modified by an advanced user, database administrator, or Business Objects administrator. This page contains parameters that can be used to override the default connection settings. You would typically adjust these parameters to resolve performance issues.

The Connections List dialog box lists the available connections. New

•

connection that you define appear on this list . To test your connection, select the connection and click the Test button. A message appears, confirming that the connection is operating. If an error occurs, edit the connection to re-configure the parameters.

Related Topics

• Database Middleware Selection dialog box on page 30

• Login Parameters dialog box on page 32

• Configuration parameters dialog box on page 34

• Custom Parameters dialog box on page 35

Database Middleware Selection dialog box

The New Connection wizard's Database Middleware Selection dialog box contains the following settings:

Connection Type: determines who can use the connection to access

•

data. The options are explained in detail below.

Connection Name: enter a name for the connection.

•

30 Data Access Guide

Creating a connection

Using the New Connection Wizard

Filter Stored Procedures Network Layers: select this check box to

•

display only those datasources that support procedures stored in the database to deliver data. In order to use a JavaBean datasource, you must select this checkbox since a JavaBean datasource uses procedures stored in the JavaBean. If you do not select this checkbox, any installed JavaBean drivers do not appear in the selection list.

A list of the available data access drivers: you expand list items and select

•

the driver that you want.

You can create three types of connections with Designer:

Personal

•

Shared

•

Secured

•

Personal connections

This connection type restricts data access to the universe creator, and the computer on which it was created. You can use personal connections to access personal data on a local machine only. You cannot use personal connections to distribute universes.

Connection parameters are stored in the PDAC.LSI file located in the LSI folder in the Business Objects 12.0 folder in your user profile directory, for example:

C:\Documents and Settings\<user name>\Application Data\Business Objects\Business Objects 12.0\lsi

These parameters are static and cannot be updated. Personal connections are unsecured when used with Business Objects products security.

Shared connections

This connection type allows access to data for all users. These connections are unsecured when used with Business Objects products security.

Connection parameters are stored in the SDAC.LSI file located in the LSI folder in the Business Objects 12.0 folder in your user profile directory, for example:

Data Access Guide 31

Creating a connection

Using the New Connection Wizard

C:\Documents and Settings\<user name>\Application Data\Business Objects\Business Objects 12.0\lsi

Secured connections

This connection type centralizes and controls access to data. This is the safest type of connection, and should be used to control 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 connection details are stored in the CMS.

Secured connections can be used and updated at any time.

Login Parameters dialog box

The New Connection wizard's Login Parameters dialog box can contain the following parameters:

32 Data Access Guide

Authentication mode

Creating a connection

Using the New Connection Wizard

DescriptionParameter

Use specified user name and pass-

•

word: uses the login details as authentication.

Use Business Objects credential map-

•

ping: the user is prompted for a database user password associated with their BusinessObjects account to refresh a report. This is set using the parameters dbuser and dbpass. These are set at the administrative level. Refer to Business Objects Enterprise Administrator's Guide for information on setting up this option.

Use Single Sign On when refreshing

•

reports at View Time: When selected, the username and password used to access the CMS are automatically used as the database login parameters. See the Business Objects Enterprise Administration guide for informtaion on setting Single Sign-On (SSO).

User name

Password

Your database user name. This is normally assigned to you by the database administrator.

Your database password. This is normally assigned to you by the database administrator.

The data source details.Datasource (<Host>:<port>):

The database name.Database

Data Access Guide 33

Creating a connection

Using the New Connection Wizard

Configuration parameters dialog box

The Configuration Parameters dialog box contains parameters that you can set to override default configuration options. These configuration parameters override:

Any corresponding parameters set in the cs.cfg file

•

Any corresponding parameters set in the <driver>.sbo file.

•

You can set the following parameters:

From the drop down list, select the method to

Connection pool mode

Pool timeout

use to keep the connection active. Only necessary if using a connection pool.

If you select Keep the pool active for in the previous field, specifies the length of time to keep the connection open.

Array fetch size

34 Data Access Guide

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.

If you enter 1, 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.

The default value is 20.

Enter the size of the bind array that Connection Server uses before sending to the repository. The bind array is the area in memory where Connection Server stores a batch of data to be

Array bind size

Related Topics

• SBO parameter categories on page 56

• About global parameters on page 42

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.

Specifies the number of seconds before a connection attempt times out and an error message is displayed.

Creating a connection

Using the New Connection Wizard

Custom Parameters dialog box

Use this dialog box to configure any custom configurations that apply to the connection. The parameters that appear depend on the other parameters that you have set. The parameters set here override:

Any corresponding parameters set in the cs.cfg file

•

Any corresponding parameters set in the <driver>.sbo file.

•

For information on the parameters, refer to the SBO file parameter reference information.

Related Topics

• About global parameters on page 42

• SBO parameter categories on page 56

Data Access Guide 35

Creating a connection

About JDBC connections

A set of data access drivers are installed when you install your Business Objects application software. You can use these data access drivers to create connections to databases.

Business Objects software also includes configuration files for using JDBC drivers to access your databases. To use these drivers, you:

Obtain the java driver software from your database supplier.

•

Modify the supplied configuration files.

•

For an up-to-date list of supported JDBC drivers, check the Business Objects Support Site at www.businessobjects.com, or contact your BusinessObjects representative.

Creating a JDBC connection

In order to create a JDBC connection:

Obtain the necessary JDBC driver software for the database, and copy

•

the files to your system. The driver software consists typically of one or more jar files. Note the path details for these files.

Ensure that you have the database access details to hand, for example

•

the login and password details.

To create a JDBC connection, use the following procedure:

1. Check if there are any DLLs that the driver uses, and ensure that they

are accessible by the system. For example, the SQLServer 2005 JDBC driver uses DLLs. The DLL's directory needs to be included in the PATH environment variable.

2. Navigate to the directory that contains the jdbc.sbo file. For example,

on a Windows system, the configuration files are located in the following paths, where connectionserver-install-dir is the directory where the Connection Server software is installed: connectionserver-install-dir

\connectionServer\jdbc

3. Use an XML editor to open the jdbc.sbo file for editing.

4. Add the required .jar file details to the ClassPath area. Include the fully

qualified path names when specifying these files, for example:

36 Data Access Guide

Creating a connection

Creating a JDBC connection

<Path>C:\JDBC Drivers\MSSQLSERVER2000\ msutil.jar</Path>

Note: These files need to be installed on the machine running the

Business Objects application.

Refer to the information on the JDBC sbo example file structure for details.

5. Locate the Driver Capabilities parameter, and check that it is set to either

Procedure or Queries.

If it is not, the JDBC driver is unavailable from the New Connection wizard.

6. Save and close the .sbo file.

7. Run the Connection Wizard. The JDBC driver that you have configured

appears in the list of available connections. Select the JDBC driver and use the Wizard and configure the connection .

When you complete this task, the connection is available for use.

Related Topics

• JDBC SBO example file structure on page 37

• Before you create a connection on page 16

JDBC SBO example file structure

This lists an example of the section of the JDBC SBO file that you need to modify. This SBO file is for Microsoft SQLServer 2000 and Microsoft SQLServer 2005.

<Path>C:\JDBC Drivers\MSSQLSERVER2000\

msbase.jar</Path> <Path>C:\JDBC Drivers\MSSQLSERVER2000\ msutil.jar</Path> <Path>C:\JDBC Drivers\MSSQLSERVER2000\ mssqlserver.jar</Path>

</ClassPath> < . . . >

Data Access Guide 37

Creating a connection

About JavaBean connections

Developers can create JavaBeans that you can use as data sources.You can create connections using these JavaBeans Typically, these JavaBeans provide access to a datasource. In order to create a JavaBeans connection, the developers who create the JavaBean will supply:

The required .jar files.

•

Any other files that the JavaBean requires.

•

Any specific configuration details that the JavaBeans driver requires.

•

Within a JavaBeans driver, data-retrieval procedures are configured as stored procedures. When configuring a JavaBeans connection, on the New Connection wizard 's Database Middleware Selection screen, you must select the Filter Stored Procedures Network Layers check-box. If you do not, the New Connection wizard does not display the JavaBeans drivers that are available.

Related Topics

• Database Middleware Selection dialog box on page 30

Creating a JavaBean connection

To create a JavaBeans connection, use the following procedure:

1. Use an XML editor to open the javabeans.sbo file for editing. For exam

ple, on a Windows system, the configuration files are located in the follow ing paths, where connectionserver-install-dir is the directory where the Connection Server software is installed: connectionserver-install-

dir\connectionServer\javabean

2. Add the required .jar file details to the ClassPath area. Include the fully

qualified path names when specifying these files.

Note: These files need to be installed on the machine running the

Business Objects application.

Refer to the information on the JavaBean.sbo example file structure for details.

3. Save and close the .sbo file.

38 Data Access Guide

4. Perform any other configuration tasks specified by the JavaBeans

developer.

5. Run the Connection Wizard. The JavaBeans datasource that you have

configured should appear in the list of available connections. Select the JavaBeans datasource and use the Wizard to configure the connection.

When you complete this task, the connection is available for use with a Business Objects application.

Related Topics

• JavaBean SBO example file structure on page 39

• Database Middleware Selection dialog box on page 30

• PrimaryKey Available on page 79

JavaBean SBO example file structure

This section contains an example of a JavaBeans SBO file.

<Path>$ROOT$/beans/bean_excel.jar

</Path> </ClassPath> <Parameter Name="JavaBean Class">com. businessobjects.beans.excel.Excel </Parameter> <Parameter Name="URL Format">$DATASOURCE$ </Parameter>

</JavaBean> <Parameter Name="Family">Java Beans </Parameter>

<Parameter Name="Description File"> bean_excel</Parameter> <Parameter Name="Authentication Mode"> Bypass</Parameter> <Parameter Name="Extensions">bean_excel, javabean</Parameter>

</DataBase>

</DataBases>

Creating a connection

Creating a JavaBean connection

Data Access Guide 39

Creating a connection

Creating a JavaBean connection

40 Data Access Guide

Configuring data access global parameters

About global parameters

You can configure the global parameter values that apply to all connections. You can do this to improve performance, or to resolve issues with the connection that arise.

Data access global parameters are maintained in the cs.cfg file. This is an XML file that contains Connection Server configuration parameters, and default configuration parameters that apply to all data access drivers.

To override these global settings, you can configure settings in each driver's

.sbo file.

Related Topics

• Configuring driver parameters on page 52

About the cs.cfg global configuration file

On a Windows system, the cs.cfg file is stored in the following location:

connectionserver-install-dir\connectionServer

•

In the cs.cfg file, you can configure parameters in the following sections only:

Settings

•

This section defines Connection Server global configuration parameters, including client access types such as CORBA. These parameters can be overridden by corresponding settings in the data access driver configuration file: <driver>.sbo, where <driver> is the name of the data access driver to which the .sbo file relates.

DriverDefaults

•

These parameters apply to all data access drivers.

Distribution

•

You configure the settings in this section if you use CORBA.

Traces

•

42 Data Access Guide

Configuring data access global parameters

You can set trace parameters that allow the recording of connection activity through Connection Server in log files. Refer to the release notes for information on generating traces.

The remaining section, Locales, defines the operating system charset for each available language. The parameters in this section must not be modified.

Viewing and editing cs.cfg

You can view and edit parameters in cs.cfg as follows:

1. Browse to the directory that stores the cs.cfg file. For example, on a

Windows system:

connectionserver-install-dir\connectionServer\cs.cfg where

connectionserver-install-dir is the path where your Connection Server software is installed.

2. Open cs.cfg in an XML editor.

3. Expand sections as required.

4. Set parameters by either adding new parameters and values, or modifying

existing parameter values.

5. Check that the document is valid against the DTD, then save and close

the file.

Viewing and editing cs.cfg

Configuring the <driver defaults> parameters

The <driver defaults> section of the cs.cfg file contains the default values that apply to all data access drivers. These default values are overridden for a specific driver by corresponding values set in the <driver>.sbo file.

Refer to the SBO file parameter reference for information on the parameters that you can set.

Related Topics

• Data access configuration files on page 52

• Configuring driver parameters on page 52

• SBO parameter categories on page 56

Data Access Guide 43

Configuring data access global parameters

Configuring <Settings> parameters

The <Settings> section of cs.cfg defines settings that apply to all drivers, and cannot be customized for individual data access drivers. Some settings can be defined either for library version or server version of Connection Server:

Library: In this mode, Connection Server is included in the client process.

•

CORBA: In this mode, Connection Server is a CORBA server and is

•

accessed remotely. In this mode, Connection Server serves two different kinds of clients: HPPT and CORBA clients.

If your environment uses CORBA, you configure some parameters in the

<Server> section of cs.cfg.

The <Settings> parameters are listed alphabetically. To view or edit parameters, open cs.cfg in an XML editor, and go to the Settings section, then Parameters section. In the file, each parameter is defined in the following tag:

<Parameter Name="parameter">value</Parameter>

where parameter is the name of the parameter, and value is the value to which the parameter is set.

Each parameter is shown with the following information:

Example of how the parameter appears in the XML file. When the

•

parameter is available in library and server mode, an example of each is shown.

Description of the parameter

•

Possible values that can be set for the parameter (where applicable)

•

Default value for the parameter

•

Charset List Extension

44 Data Access Guide

Configuring data access global parameters

Configuring <Settings> parameters

Description

Config File Extension

Description

Description Extension

Note: Do not change this setting.

Sets the file extension for character set files.

crsDefault

Note: Do not change this setting.

Sets the file extension for general configuration files.

sboDefault

Description

Enable Failed Load

Note: Do not change this setting.

Sets the file extension for the connection description files.

codDefault

Data Access Guide 45

Configuring data access global parameters

Configuring <Settings> parameters

Description

Values

Related Topics

• Load Drivers On Startup on page 46

Load Drivers On Startup

Load Drivers On startup applies to both Library and Server modes.

Determines action taken when a driver fails to load. The

parameter lets you choose whether you want a usable

connection possibly without all drivers operating, or a fatal error and no functionality when a driver fails to load.

Yes: Connection Server generates a fatal error when a driver fails to load.

No: Connection Server generates a non-fatal error when a driver fails to load.

YesDefault

Library

Server

These are described below.

Values

46 Data Access Guide

Determines how driver libraries are loaded.Description

Yes: All installed drivers are loaded during the ini-

•

tialization phase.

No: Drivers are loaded on demand.

•

Configuring data access global parameters

Configuring <Settings> parameters

Default

Max Pool Time

Max Pool Time is available for Library mode and Server mode.

Library

Server

Description

Library mode: No

Server mode: Yes

Determines the maximum connection idle lifetime in the connection pool regardless of the value defined in the connection. You can set Max Pool Time for two types of Connection Server deployments:

Library: Value applies to nodes that have Connection Server installed with desktop or other server products.

Server: Value applies to Connection Server stand alone server installations. For information on deploying Connection Server on a dedicated node see the Deployment guide.

Default

Values

Library mode: -1

Server: 60

-1: No timeout, keep alive for the whole session.

0: Connection not managed by the pool.

>0: Idle lifetime (in minutes).

Data Access Guide 47

Configuring data access global parameters

Configuring <Settings> parameters

SQL External Extension

Description

Note: Do not change this setting.

Sets the file extension for external SQL files.

rssDefault

SQL Parameter Extension

Description

Note: Do not change this setting.

Sets the file extension for SQL parameter files.

prmDefault

Strategies Extension

Description

48 Data Access Guide

Note: Do not change this setting.

Sets the extension for strategy files.

stgDefault

Configuring data access global parameters

Configuring CORBA access

Connection Server can operate in the following two modes:

Library: In this mode, Connection Server is included in the client process.

•

CORBA: In this mode, Connection Server is a CORBA server and is

•

accessed remotely. In this mode, Connection Server serves two different kinds of clients: HTTP and CORBA clients.

In cs.cfg, parameters in the <Settings> section control the access method.

In the <Settings> section:

Parameters in the <Library> section control Library mode. Most Business

•

Objects products use Connection Server in Library mode.

Parameters in the <Server> section control CORBA access.

•

Parameters defined in <Library> or <Server> take precedence over any parameter definitions set in other parts of the <Settings> section. For example, with CORBA, parameters defined in the <Server> section can override parameters set in other areas for applications that use <Li

brary> access.

Configuring CORBA access

The settings that apply to CORBA access are:

Load Drivers On Startup

•

Max Pool Time

•

In addition to these settings, you must configure setttings in the Distribution section.

Related Topics

• Configuring the Distribution section for CORBA access on page 50

• Load Drivers On Startup on page 46

• Max Pool Time on page 47

Data Access Guide 49

Configuring data access global parameters

Configuring the Distribution section for CORBA access

You set the following parameters in the <Distribution> section of cs.cfg when using Connection Server with CORBA.

The <Protocols> section contains the default values that Connection Server uses to process requests coming from CORBA clients or HTTP clients. For CORBA access, configure the section as follows:

The <Lookup> section contains parameters for internal use, and must not be modified.

50 Data Access Guide

Configuring data access driver parameters

Configuring driver parameters

To configure data access for a particular data access driver, you can edit the driver's XML parameter files to adjust the parameter settings.

Note: For each Business Objects application that uses Connection Server,

the associated Readme file contains information on command line utilities that you can use to check your RDBMS and data access driver configuration. These utilities can create log files that trace Web Intelligence server activity. Refer to the readme for the release for instructions on the use of these utilities.

Related Topics

• About global parameters on page 42

Data access configuration files

The following configuration files control data access driver configurations for each defined connection:

The cs.cfg file defines global parameters that apply to all connections.

•

There is a separate configuration file for each data access driver. These

•

files are named <driver>.sbo, where <driver> is the database network layer to which the configuration file applies.

The parameters set in the Settings section of cs.cfg are overridden by corresponding settings in the data access driver configuration files: <driver>.sbo

The configuration files are located in the following paths, where connection server-install-dir is the directory where the Connection Server software is installed.

The cs.cfg file is in the following directory:

•

connectionserver-install-dir\connectionServer\

Each .sbo file is in a sub-directory of this directory, where the

•

sub-directory is named after the database network layer, for example for Oracle databases:

connectionserver-install-dir\connectionServer\oracle

52 Data Access Guide

Related Topics

• About global parameters on page 42

• Installed SBO files on page 53

• Configuring the <driver defaults> parameters on page 43

Installed SBO files

The following <driver>.sbo files are installed by default.

For an up-to-date list of supported drivers, check the Business Objects Support Site at www.businessobjects.com, or contact your BusinessObjects representative.

Configuring data access driver parameters

Configuring driver parameters

SBO fileData access driver

Essebase

IBM DB2

Informix

JDBC provides JDBC drivers for

databases. Check the Business Objects support website, or the jdbc.sbo file for details of the databases supported for JDBC connections.

Microsoft SQL Server

Microsoft Analysis Services

MySQL

ODBC

Oracle

Red Brick

essebase.sbo

db2.sbo

iseries.sbo

informix.sbo

jdbc.sbo

odbc.sbo

oledb_olap.sbo

odbc.sbo

oracle.sbo

odbc.sbo

Data Access Guide 53

Configuring data access driver parameters

Configuring driver parameters

SBO fileData access driver

SAP

Sybase

Teradata

Editing an SBO file

You can open an SBO file for viewing or editing as follows:

Note: Before opening an SBO file, make a backup copy of the file. Some

configuration parameters must not be edited. If you change or delete them it could affect the operation of your Business Objects applications.

1. Browse to the directory that stores the SBO file for your target data access

driver. SBO files are stored in the following location, where <driver> is the name of the database software that the connection uses:

\\<INSTALDIR>\win32_x86\dataAccess\connectionServer\\<driv

er>.sbo

2. Open the <driver>.sbo file in a XML editor.

3. Expand sections as required.

4. Locate the appropriate tag for the value to change, and change the value.

Parameters appear in the format: <Parameter Name="parameter">val

ue</Parameter> where parameter is the name of the parameter, and

value is the value attributed to the parameter.

sap.sbo

sybase.sbo

teradata.sbo

5. Check that the file is valid against the DTD, save and close the file.

54 Data Access Guide

SBO file parameter reference

SBO parameter categories

The configuration parameters in this guide are listed in the following categories. Each category represents a database that has a separate sbo file.

Defaults

•

Informix

•

JavaBean

•

JDBC

•

ODBC

•

ODBC3

•

OLE DB

•

Sybase ASE/CTLIB

•

Each parameter is shown with the following information:

Example of how the parameter appears in the XML file

•

Description of the parameter

•

Possible values that can be set for the parameter

•

Default value for the parameter

•

SBO file structure

There is a <driver>.sbo file for each supported database network layer. Each <driver>.sbo file is divided into the following sections:

Defaults

56 Data Access Guide

DescriptionFile section

This section contains the default configuration parameters that apply to all database middleware that uses the data access driver. These parameters override any corresponding values set in the database middleware.

Databases

SBO file parameter reference

SBO parameter categories

DescriptionFile section

This section contains a sub-section for each database middleware that is supported by the data access driver. The Active parameter specifies if middleware support is activated or not.

Values are YES or NO. Each middleware section can contain the following

parameters:

Name: Names of the middleware supported by

•

the data access driver. The middleware name values set here appear in the Database Middleware page of the new connection wizard.

Aliases: Names of older middleware versions

•

no longer officially supported by the data access driver, but that are still in use. You can add an alias parameter for an older middleware version so that existing connections use the current data access driver instead. You can set configuration parameters specific to the old middleware as parameters of the new alias. You can create new connections using the alias.

Default SBO parameters

These SBO parameters are defined in cs.cfg, or under the Defaults section of the SBO file.

Related Topics

• SBO parameter categories on page 56

Parameters: Configuration parameters with

•

values that apply specifically to a middleware. Values set for parameters listed here override the values set for the same parameters in the Defaults section.

Data Access Guide 57

SBO file parameter reference

SBO parameter categories

Array Bind Available

Description

Values

Array Bind Size

Description

Values

Specifies whether or not the database supports Array Binds.

True: the database supports Array Binds. False: the database does not support Array Binds.

FalseDefault

Specifies the number of rows exported with each INSERT command.

An integer that specifies the number of rows that are exported with each INSERT.

The value set in the cs.cfg file.Default

Array Fetch Available

Description

Values

58 Data Access Guide

Specifies whether or not the Array Fetch method is supported.

True: the Array Fetch method is supported. False: the Array Fetch method is not supported.

The value set in the cs.cfg file.Default

Array Fetch Size

Description

Values

SBO file parameter reference

SBO parameter categories

Specifies the number of rows of data retrieved with each Array Fetch method. The optimum number depends on your system's performance:

If the number is low, the system retrieves small

•

amounts of data many times. This can affect performance.

If the number is high, the system performs fewer

•

retrieval operations, but it requires more memory for each.

An integer that specifies the number of rows that are retrieved with each Array Fetch.

1 : specifies that Array Fetch is deactivated.

Catalog Name Max Size

Description

Values

Value set in the cs.cfg file.Default

Specifies the maximum length in characters of a database catelog name.

An integer that specifies the maximum database catalog name length in characters.

The value set in the database middleware.Default

Data Access Guide 59

SBO file parameter reference

SBO parameter categories

Catalog Separator

CharSet

Description

Default

Description

Values

Default

Specifies the separator character that is used between identifiers.

The separation character to use.Values

If not specified, Connection Server uses the separator specified in the database middleware.

Specifies the character set of the data returned by the database middleware.

UTF8: 8-bit UCS/Unicode Transformation Format UCS2: 2-byte Universal Character Set

If not specified, Connection Server uses the separator specified in the database middleware.

Column Name Max Size

Description

Values

60 Data Access Guide

Specifies the maximum length in characters of a database column name.

An integer that specifies the maximum database column name length in characters.

Connection Shareable

<Parameter Name="Connection Shareable">False</Parameter>

Description

Values

SBO file parameter reference

SBO parameter categories

The value set in the database middleware.Default

Specifies if the connection configuration is shareable between different connections. Operates in conjunction with the Shared Connection parameter.

True: the connection configuration can be shared between connections.

False: the connection configuration cannot be shared between connections

FalseDefault

Related Topics

• Shared Connection on page 70

Cost Estimate Available

<Parameter Name="Cost Estimate Available">False</Parameter>

Description

Values

Specifies if the database middleware supports cost estimation.

True: the middleware supports cost estimation. False: the middleware does not support cost estimation.

FalseDefault

Data Access Guide 61

SBO file parameter reference

SBO parameter categories

Description File

<Parameter Name="Description File">oracle</Parameter>

Driver Capabilities

<Parameter Name="Driver Capabilities">Procedures , Query</Pa rameter>

Description

Note: Do not edit this parameter.

Specifies the name of the file that holds the connection wizard input field labels.

The capabilities of the driver, that is whether it can access stored procedures and queries available in the database software. This parametery is set typically using the New Connection wizard. You can include both values in the parameter.

Note: This parameter must be set to Procedures for a

JavaBeans driver. The functionality of a JavaBeans driver is defined as stored procedures as far as Business Objects applications are concerned.

Values

Escape Character

62 Data Access Guide

Procedures: the driver can access the data retrieval procedures that are defined in the database software.

Query: the driver can access the data retrieval queries that are defined in the database software.

ProceduresDefault

SBO file parameter reference

SBO parameter categories

Extensions

Family

Description

Default

<Parameter Name="Family">Sybase</Parameter>

Specifies the character to use to escape strings of special characters, for example patterns.

The character to use as the escape character.Values

If not specified, Connection Server retrieves the value from the middleware.

Note: Do not modify the settings in this parameter.Description

Description

Field Size Factor

Note: Do not edit this parameter.

Specifies the family of the database engine that is displayed in the Database Middleware Selection page of the New Connection wizard. The set of middleware that corresponds to your license is displayed on this page in a tree view.

Data Access Guide 63

SBO file parameter reference

SBO parameter categories

Note: Do not change this value.

Description

Force Execute

<Parameter Name="Force Execute">Never</Parameter>

Description

Values

The value to use to compute the size of returned content expressed as field characters when the content is returned as bytes. This is used for DB2 only.

Specifies whether or not the SQL query is executed before retrieving results. Supported by the following:

• ODBC

• OLE

• DB

• JDBC

Never: The SQL query is never executed before retrieving results.

Procedures: Execute only for stored procedures. Always: The SQL query is always executed before re-

trieving the results.

Identifier Case

<Parameter Name="Identifier Case">LowerCase</Parameter>

Description

64 Data Access Guide

NeverDefault

Specifies how the database handles the case behavior of simple identifers.

Values

SBO file parameter reference

SBO parameter categories

LowerCase: identifiers must be in lower case. UpperCase: identifiers must be in upper case. MixedCase: identifiers can be in mixed case. SensitiveCase: identifiers are case sensitive.

Default

Identifier Quote String

Default

LIKE Escape Clause

Description

If this setting is not specified, Connection Server retrieves the information from the database middleware.

Specifies the character used to quote identifiers.Description

The character used to quote identifiers.Values

If this setting is not specified, Connection Server retrieves the information from the database middleware.

Specifies if escape character use is supported in the LIKE clause.

Values

Default

True: escape character use is supported in the LIKE clause.

False: escape character use is not supported in the LIKE clause.

If this setting is not specified, Connection Server retrieves the information from the database middleware.

Data Access Guide 65

SBO file parameter reference

SBO parameter categories

Locale

Values

Specifies the locale of the middleware.Description

The locale of the middleware.

• Language is specified in ISO-639 standard

• Country is specified in ISO-3166 standard

For example: en_US for English United States.

Default

Max Rows Available

Description

Values

Optimize Execute

<Parameter Name="Optimize Execute">False</Parameter>

If this setting is not specified, Connection Server retrieves the information from the database middleware.

Specifies if the driver supports the Max Rows function to limit the maximum number of rows that can be retrieved from a datasource.

True: The driver supports the Max Rows function . False: The driver does not support the Max Rows func-

tion.

FalseDefault

66 Data Access Guide

Description

Values

Owners Available

SBO file parameter reference

SBO parameter categories

Specifies whether or not Connection Server optimizes the execution of SQL queries. This parameter is supported by Oracle and ODBC drivers only.

True: specifies that where possible, SQL queries are optimized on execution.

False: SQL queries are not optimized for execution.

FalseDefault

Description

Values

Default

Password_Encryption

Description

Specifies whether or not Owners are supported by the target database.

True: Specifies that owners are supported by the target database.

False: Specifies that owners are not supported by the target database.

Not specified: value retrieved from the database middleware.

Specifies whether or not to use the encryption password mechanism specified in the middleware for the password entered in the Connection details dialog box. This parameter is used only with Sybase. It is included in the Defaults section for future compatibility.

Data Access Guide 67

SBO file parameter reference

SBO parameter categories

Values

Procedure Name Max Size

True: Specifies that the encryption password mechanism of the middleware is used.

False: Specifies that the encryption password mechanism of the middleware is not used.

TrueDefault

Description

Values

Specifies the maximum length in characters of a database procedure name.

An integer that specifies the maximum database procedure name length in characters.

The value set in the database middleware.Default

Procedure Parameter Name Max Size

Description

Values

Specifies the maximum length in characters of a database procedure parameter name.

An integer that specifies the maximum database procedure parameter name length in characters.

The value set in the database middleware.Default

68 Data Access Guide

Qualifiers Available

SBO file parameter reference

SBO parameter categories

Specifies whether or not Qualifiers are supported.Description

Values

Default

Query TimeOut Available

Description

Values

True: Specifies that Qualifiers are supported. False: Specifies that Qualifiers are not supported.

Not specified. Connection Server retrieves this information from the database middleware.

Specifies whether or not Query TimeOut is supported by the database middleware, that is if a query that is running can be cancelled after a time period has expired.

True: Specifies that Query TimeOut is supported by the database middleware .

False: Specifies that Query TimeOut is not supported by the database middleware .

FalseDefault

Quoted Identifier Case

Description

Specifies how the database treats the case of quoted identifiers.

Data Access Guide 69

SBO file parameter reference

SBO parameter categories

Values

LowerCase: quoted identifiers must be in lower case. UpperCase: quoted identifiers must be in upper case. MixedCase: quoted identifiers can be in mixed case. SensitiveCase: quoted identifiers are case sensitive.

Default

Schema Name Max Size

Description

Values

Shared Connection

Description

Not specified. Connection Server retrieves the information from the middleware.

Specifies the maximum length in characters of the database schema name.

An integer that specifies the maximum database schema name length in characters.

The value set in the database middleware.Default

Specifies whether or not the connection can be shared between different workflows. Operates in conjunction with the Connection Shareable parameter.

Values

70 Data Access Guide

True: Connections are shared if possible. False: Connections are not shared.

FalseDefault

Related Topics

• Connection Shareable on page 61

SQL External File

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

Description

SQL Parameter File

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

Description

SBO file parameter reference

SBO parameter categories

Note: Do not edit this value.

The SQL External file file holds configuration details used by the data access layer.

The name of the file that stores database parameters. The extension of this file is: .prm

You must ensure that this file is located in the same directory as the data configuration file (.sbo file).

Values

db2iseries for IBM DB2 iSeries db2udb for IBM DB2 informix for Informix sqlsrv for Microsoft SQL Server 2000 oracle for Oracle redbrick for Red Brick sybase for Sybase asiq for Sybase ASIQ teradata for Teradata

The listed values.Default

Data Access Guide 71

SBO file parameter reference

SBO parameter categories

SSO Available

<Parameter Name="SSO Available">False</Parameter>

Specifies whether or not Single Sign On is supported.Description

Values

Strategies File

<Parameter Name="Strategies File">oracle</Parameter>

Description

Values

True: Single Sign On is supported. False: Single Sign On is not supported .

FalseDefault

Specifies the name, with no extension, of the Strategy file (.stg). This file contains the external strategies that Designer uses for automatic universe creation. Strategy files are stored in the same directory as the .sbo file.

db2 for DB2 data access drivers. informix for Informxix. oracle for Oracle. sybase for Sybase. teradata for Teradata.

See values above.Default

Table Name Max Size

Description

72 Data Access Guide

Specifies the maximum length in characters of a database table name.

SBO file parameter reference

SBO parameter categories

Values

Transactional Available

Description

Values

An integer that specifies the maximum database table name length in characters.

The value set in the database middleware.Default

Specifies if SQL operations run against the database are run as block transactions or individually.

This parameter is not listed by default in the .sbo file. Add it to the .sbo file if your data access driver does not support transactional mode.

Yes: operations against the database are run as a block when committed.

No: each SQL statement is immediately committed. That is, Autocommit is de-activated.

Note: Do not use a driver with Transactional

Available=No to access the Business Objects reposi-

tory.

Transaction Mode

<Parameter Name="Transaction Mode">AutoCommit</Parameter>

Yes. This is set in the cs.cfg file.Default

Specifies the transaction mode that the database uses.Description

Data Access Guide 73

SBO file parameter reference

SBO parameter categories

Values

AutoCommit: statements are committed automatically on completion of a request.

Transactional: on competion, an explicit call either completes or rolls back the statement.

Type

Unicode

Default

<Parameter Name="Type">Relational</Parameter>

Description

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

Description

If not specified, the value is retrieved from the database middleware.

Specifies the Business Objects data source type.

Note: This parameter must not be modified.

Specifies if the access driver can benefit from the Unicode configuration of the client middleware. This parameter appears as a driver default in the cs.cfg file. Its value applies to all data access drivers. It is not listed by default in the SBO file. If you want to over-ride the default value you add it to the Defaults section of the SBO file for the target data access driver.

Values

74 Data Access Guide

UTF8: 8-bit UCS/Unicode Transformation Format coding. CharSet: Character Set coding. UCS2: 2-byte Universal Character Set coding

The value set in the cs.cfg file.Default

Version

<Parameter Name="Version">Relational</Parameter>

Description

XML Max Size

SBO file parameter reference

SBO parameter categories

Specifies the database version.

Note: This parameter must not be modified.

Specifies the maximum size allowed for XML data.Description

The maximum allowed XML size, in bytes.Values

This varies depending on the database.Default

Informix SBO parameters

These parameters apply to the Informix SBO file. They are used to define the connection to an Informix database.

These parameters are defined in the \\<INSTALDIR>\win32_x86\dataAc

cess\connectionServer\informix\informix.sbo file.

Note: In addition to the SBO parameters, for Informix, in a Unix environment,

you must modify the ODBC.INI file.

Related Topics

• V5toV6DriverName on page 76

• Modifying the Informix ODBC.INI file on page 76

Data Access Guide 75

SBO file parameter reference

SBO parameter categories

Modifying the Informix ODBC.INI file

In order to use the data access layer with an Informix database in a UNIX environment, you need to modify the unicode configuration. This configuration information can be in either of the following locations:

The .odbc.ini file located in your home directory.

•

The file specified by the ODBCINI environment variable.

•

1. Locate the ODBC.INI file or the file specified by the ODBCINI environment

variable, and open it in a text editor.

2. In the file, locate the [ODBC] section.

3. Add the following line to the [ODBC] section:

UNICODE=UTF-8

4. Close the file and save it.

Example: ODBC section with the configuration added

This code is an example of the ODBC section with the correct information added:

[ODBC]

UNICODE=UTF-8

V5toV6DriverName

<Parameter Name="V5toV6DriverName">{Informix 3.34 32 BIT}</Pa rameter>

Description

Values

76 Data Access Guide

Specifies the conversion rule from Informix Connect to Informix ODBC. The value of this parameter determines which Informix Driver is used to define the ODBC Data Source Name (DSN) without the connection string..

The exact name of the Informix driver installed on the machine.

The value set in the cs.cfg file.Default

JavaBean SBO parameters

These parameters apply to the JavaBean SBO file. They are used to define a JavaBean connection.

These parameters are defined in the \\<INSTALDIR>\win32_x86\dataAc

cess\connectionServer\javaean\javaean.sbo file.

JavaBean Class

<Parameter Name="JavaBean Class">string</Parameter>

Defines the entry point of the JavaBean that the Business

Description

Objects application uses. The entry point is the definition of a java class extending from the Bean interface specified through the com.businessobjects package.

A fully-qualified class JavaBean class name.Values

SBO file parameter reference

SBO parameter categories

URL Format

None.Default

<Parameter Name="URL Format ">string</Parameter>

Specifies the URL Format. The JDBC specification does not specify the format of

the connection string that it requires. Vendors use different kinds of URL format, for example:

MySQL vendor :

Description

•

jdbc:mysql://$DATASOURCE$/$DATABASE$

Oracle vendor :

•

jdbc:oracle:thin:@$DATASOURCE$:$DATABASE$

Data Access Guide 77

SBO file parameter reference

SBO parameter categories

JDBC SBO parameters

These parameters apply to the JDBC SBO file. They are used to define a JDBC connection.

These parameters are defined in the \\<INSTALDIR>\win32_x86\dataAc

cess\connectionServer\jdbc\jdbc.sbo file.

ForeignKeys Available

The URL FormatValues

NoneDefault

Description

Values

JDBC Class

<Parameter Name="JDBC Class">string</Parameter>

78 Data Access Guide

Specifies if ForeignKeys can be retrieved.

True: ForeignKeys can be retrieved. False: ForeignKeys cannot be re-

trieved.

TrueDefault

The JDBC driver's fully qualified Java class.Description

Values

PrimaryKey Available

SBO file parameter reference

SBO parameter categories

Depends on the vendor/datasource, for example :

oracle.jdbc: OracleDriver for Oracle

•

com.ibm.db2.jcc.DB2Driver: for DB2

•

None.Default

Description

Values

ODBC SBO parameters

These parameters apply to the ODBC SBO file. They are used to define an ODBC SBO connection.

These parameters are defined in the \\<INSTALDIR>\win32_x86\dataAc

cess\connectionServer\odbc\odbc.sbo file.

Empty String

<Parameter Name="Empty String">NullString</Parameter>

Description

Specifies whether or not the primary keys can be retrieved.

True: Primary keys can be retrieved. False: Primary keys cannot be retrieved.

TrueDefault

Specifies that certain functions, for example SQL tables, receive either an empty string or a null pointer to replace missing parameters.

Data Access Guide 79

SBO file parameter reference

SBO parameter categories

Values

Force Close Statement

Description

Values

ODBC Cursors

NullString: Specifies that a null string is used. EmptyString: Specifies that an empty string used.

EmptyStringDefault

Specifies that the SQL statement is closed after it completes execution.

True: the SQL statement is closed after it executes. False: the SQL statement is not closed after it executes.

Description

Values

Specifies if the ODBC cursor library is used by the data access driver.

Yes: the ODBC cursor library is used by the data access driver.

No: the ODBC cursor library is not used by the data access driver.

The value set in the cs.cfg file.Default

SQLDescribeParam Available

80 Data Access Guide

SBO file parameter reference

SBO parameter categories

Description

Values

SQLMoreResults Available

Description

Values

Specifies whether or not the SQLDescribeParam

Available mechanism is available.

True: Specifies that the SQLDescribeParam Available

mechanism is available. False: Specifies that the SQLDescribeParam Available

mechanism is not available.

The value set in the database middleware.Default

Specifies whether or not the SQLMoreResults Available mechanism is supported.

True: Specifies that the SQLMoreResults Available mechanism is supported.

False: Specifies that the SQLMoreResults Available Available mechanism is not supported.

The value set in the middleware.Default

ODBC3 SBO parameters

These parameters apply to the ODBC3 SBO file. They are used to define an ODBC3 connection.

These parameters are defined in the \\<INSTALDIR>\win32_x86\dataAc

cess\connectionServer\odbc3\odbc3sbo\odbc3.sbo file.

Connection Status Available

Data Access Guide 81

SBO file parameter reference

SBO parameter categories

Description

Values

Native Int64 Available

<Parameter Name="Native Int64 Available">False</Parameter>

Description

Values

Specifies whether or not the middleware can detect a bad connection.

True: The middleware can detect a bad connection. False: The middleware cannot detect a bad connection.

The value set in the middleware.Default

Indicates if 64 bit integers can be handled directly by middleware.

True: Specifies that 64-bit integers can be handled by the middleware.

False: The Business Objects Data Access Layer emulates the Int64 methods.

TrueDefault

OLE DB SBO parameters

These parameters apply to the OLE DB SBO file. They are used to define an OLE DB SBO connection.

These parameters are defined in the \\<INSTALDIR>\win32_x86\dataAc

cess\connectionServer\oledb\oledb.sbo file.

Provider CLSID

<Parameter Name="Provider CLSID">MSDASQL</Parameter>

82 Data Access Guide

Description

Sybase ASE/CTLIB

These parameters apply to the Sybase ASE/CTLIB SBO file. These parameters are used to define a Sybase ASE/CTLIB connection.

These parameters are defined in the \\<INSTALDIR>\win32_x86\dataAc

cess\connectionServer\sybase\sybase.sbo file.

Quoted Identifier

SBO file parameter reference

SBO parameter categories

Note: Do not edit this parameter.

Specifies name of OLEDB provider. This parameter is used with OLEDB only.

Values

Specifies whether or not quoted identifiers are supported.Description

True: quoted identifiers are supported. False: quoted identifiers are not supported.

The value set in the middleware.Default

Data Access Guide 83

SBO file parameter reference

SBO parameter categories

84 Data Access Guide

Configuring SQL generation parameters for a universe

About SQL generation parameters for a universe

SQL generation parameters control the SQL queries that Business Objects applications generate to retrieve the contents of a Universe.

You can set these SQL generation parameters to operate at :

• The Universe level: you set these parameters when you create or modify

a Universe. The database level: you set these parameters in the PRM file for the

•

database. These parameters are overriden by any corresponding Universe-level PRM file settings.

Editing SQL generation parameters in a universe

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.

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

To edit SQL generation parameters in Designer:

1. From the File menu, select Parameters.

The Universe Parameters dialog box appears.

2. Click the Parameter tab.

The Parameter page appears.

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

86 Data Access Guide

Add a new parameter

Change name or value

Configuring SQL generation parameters for a universe

Editing SQL generation parameters in a universe

Then do the followingTo...

Click any parameter in the list.

•

Type a name in the Name box

•

Type a value in the Value box.

•

Click Add.

•

The new value appears at the bottom of the list

Click a parameter in the list.

•

Type a new name in the Name box

•

Type a new value in the Value box.

•

Click Replace.

•

The value is replaced by the new definition.

Click the parameter that you want to

•

Delete a parameter

4. Click OK.

Note: The SQL generation parameter values that you set in a universe are

only available to products using that universe.

remove from the list.

Click Delete.

•

Universe SQL parameters reference

This section 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

Data Access Guide 87

Configuring SQL generation parameters for a universe

Editing SQL generation parameters in a universe

access parameter (PRM) file for the target data access driver. Refer to the Data Access guide for a reference to the parameters in the PRM file.

ANSI92

ANSI92 = Yes|No

Description

Yes|NoValues

NoDefault

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 OUT ER_JOIN_GENERATION.

AUTO_UPDATE_QUERY

AUTO_UPDATE_QUERY = Yes|No

Description

BLOB_COMPARISON

BLOB_COMPARISON = Yes|No

88 Data Access Guide

Yes|NoValues

YesDefault

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.

Description

Configuring SQL generation parameters for a universe

Editing SQL generation parameters in a universe

Yes|NoValues

NoDefault

NoCan be edited?

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 proper-

ties.

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.

BOUNDARY_WEIGHT_TABLE

BOUNDARY_WEIGHT_TABLE = Integer 32bits [0-9]

Integer 32bits [0-9]Values

-1Default

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:

Description

FROM (SELECT col1,

col2,......, coln, ,...., FROM

Table_Name WHERE simple condi tion).

A simple condition is defined as not having a subquery, and not having EX

CEPT or BOTH operators.

Data Access Guide 89

Configuring SQL generation parameters for a universe

Editing SQL generation parameters in a universe

Limitations

COLUMNS_SORT

COLUMNS_SORT = Yes|No

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 ta-

•

ble that is being optimized

the table being optimized is a de-

•

rived table.

Values

Default

Description

COMBINE_WITHOUT_PARENTHESIS

Yes/No

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

Yes: Columns are displayed in alpha-

betical order

No: Columns are displayed in the order

they were retrieved from the database

COMBINE_WITHOUT_PARENTHESIS = No

Values

Default

Yes/No

90 Data Access Guide

Configuring SQL generation parameters for a universe

Editing SQL generation parameters in a universe

Specifies whether or not to encapsulate a query with parentheses when it con-

Description

COMBINED_WITH_SYNCHRO

tains UNION, INTERSECT or MINUS operators. Used with RedBrick.

Yes Removes the parentheses.

No Leaves the parentheses.

COMBINED_WITH_SYNCHRO = Yes|No

Values

Default

Description

Yes|No

Specifies whether to allow a query to execute that contains UNION, INTER

SECTION, 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.

Data Access Guide 91

Configuring SQL generation parameters for a universe

Editing SQL generation parameters in a universe

COMPARE_CONTEXTS_WITH_JOINS

COMPARE_CONTEXTS_WITH_JOINS = Yes|No

Values

Default

Description

CORE_ORDER_PRIORITY

CORE_ORDER_PRIORITY = Yes|No

Values

Default

Description

Yes|No

Specifies how contexts are compared.

Yes: The system verifies that the con-

texts give the same joins.

No: The system verifies that the con-

texts give the same sets of tables. This is the default value.

Yes|No

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.

CORRECT_AGGREGATED_CONDITIONS_IF_DRILL

CORRECT_AGGREGATED_CONDITIONS_IF_DRILL = Yes|No

Values

92 Data Access Guide

Yes|No

Configuring SQL generation parameters for a universe

Editing SQL generation parameters in a universe

Default

Description

CUMULATIVE_OBJECT_WHERE

Specifies whether Web Intelligence can aggregate measures in queries and conditions.

Yes: Web Intelligence can aggregate

measures separately in the main query and the condition, if the query is drill enabled.

No: Web Intelligence cannot aggregate

measures separately in the main query and the condition, if the query is drill enabled.

CUMULATIVE_OBJECT_WHERE = Yes|No

Values

Default

Yes|No

Data Access Guide 93

Configuring SQL generation parameters for a universe

Editing SQL generation parameters in a universe

Description

Specifies the order of WHERE clauses that have the AND connective.

Yes: Specifies that WHERE clauses that

have the AND connective are set at the end of the condition.

No: Specifies that WHERE clauses follow standard SQL syntax.

Example:

If the condition is find all French clients different from John or American cities different from New York, the SQL is then:

WHERE

(customer.first_name <> 'John')

OR (city.city <> 'New York')

AND customer_country.country =

'France'

AND city_country.country = 'USA'

DECIMAL_COMMA

DECIMAL_COMMA = Yes|No

Values

Default

Description

94 Data Access Guide

Yes|No

Specifies that Business Objects products insert a comma as a decimal separator when necessary.

Yes: Business Objects products insert

a comma as a decimal separator when necessary.

No: Business Objects products do not

insert a comma as a decimal separator. This is the default value.

Configuring SQL generation parameters for a universe

Editing SQL generation parameters in a universe

DISTINCT_VALUES

DISTINCT_VALUES = GROUPBY|DISTINCT

Values

Default

Description

END_SQL

END_SQL = String

GROUPBY|DISTINCT

DISTINCT

Specifies whether SQL is generated with a DISTINCT or GROUP BY clause in a list of values and Query pane when the option "Do not retrieve duplicate rows" is active.

DISTINCT: The SQL is generated with

a DISTINCT clause, for example;

SELECT DISTINCT cust_name FROM Customers

GROUPBY: The SQL is generated with

a GROUP BY clause, for example;

SELECT cust_name FROM Customers GROUP BY cust_name

StringValues

Description

Example

<empty string>Default

The statement specified in this parameter is added at the end of each SQL statement.

For IBM DB2 databases, you can use the following:

END_SQL=FOR SELECT ONLY

The server will read blocks of data much faster.

Data Access Guide 95

Configuring SQL generation parameters for a universe

Editing SQL generation parameters in a universe

EVAL_WITHOUT_PARENTHESIS

EVAL_WITHOUT_PARENTHESIS = Yes|No

Values

Default

Description

Yes|No

By default, the function @Se

lect(Class\object) is replaced by the SELECT statement for the object

<Class\object> enclosed within brackets.

For example, when combining two

@Select statements, @Select(objet1)

*@select(objet2).

If the SQL(object1) = A-B and SQL(object2) =C,

then the operation is (A-B) * (C).

You avoid the default adding of brackets by setting EVAL_WITHOUT_PAREN

THESIS = Yes. The operation is then

A - B * C.

Yes: Brackets are removed from the SELECT statement for a function @Se lect(Class\object)

No: Brackets are added around the

Select statement for the function @Se

lect(Class\object).

FILTER_IN_FROM

FILTER_IN_FROM = Yes|No

Values

Default

96 Data Access Guide

Yes|No

Configuring SQL generation parameters for a universe

Editing SQL generation parameters in a universe

Determines if query conditions are included in the FROM Clause. This setting is only applicable if the other universe parameter setting ANSI92 is set to

Yes.

Yes: When editing an outer join, the

default behavior property selected in

Description

FIRST_LOCAL_CLASS_PRIORITY

the drop down list box of the Advanced Join properties dialog box in Designer, is set to "All objects in FROM".

No: When editing an outer join, the de-

fault behavior property selected in the drop down list box of the Advanced Join properties dialog box in Designer is set to "No object in FROM".

FIRST_LOCAL_CLASS_PRIORITY = Yes|No

Values

Default

Description

FORCE_SORTED_LOV

FORCE_SORTED_LOV = Yes|No

Values

Yes|No

Only taken into account when

CORE_ORDER_PRIORITY=Yes.

Yes: Classes in derived universe are

placed first.

No: Objects and sub classes from de-

rived universe appear after those of the core universe.

Yes|No

Data Access Guide 97

Configuring SQL generation parameters for a universe

Editing SQL generation parameters in a universe

Default

Description

INNERJOIN_IN_WHERE

INNERJOIN_IN_WHERE = Yes|No

Values

Default

Description

Retrieves a list of values that is sorted.

Yes: Specifies that the list of values is

sorted.

No: Specifies that the list of values is

not sorted.

Yes|No

You must manually enter the parameter to activate it.

Allows you to force the system to generate SQL syntax with all the inner joins in the WHERE clause when ANSI92 is set to yes . This is only possible if a query contains only inner joins (Does not contain FULL OUTER, RIGHT OUTER, or LEFT OUTER joins).

Yes: If ANSI92 is set to yes, the system

generates ANSI92 join syntax in the

FROM clause except when the query

contains only inner joins. In this case, the inner joins go into the WHERE clause.

No: If ANSI92 is set to Yes, the system

generates ANSI 92 join syntax in the

FROM clause.

JOIN_BY_SQL

JOIN_BY_SQL = Yes|No

98 Data Access Guide

Configuring SQL generation parameters for a universe

Editing SQL generation parameters in a universe

Values

Default

Description

MAX_INLIST_VALUES

MAX_INLIST_VALUES = 99]

Description

Yes|No

Specifies how multiple SQL statements are handled. Multiple statements can be combined (provided that the database permits this).

Yes: Specifies that multiple SQL state-

ments are combined.

No: Specifies that multiple SQL state-

ments are not combined. This is the default value.

Integer: min 0, max depends on DBValues

99Default

Allows you to set the maximum number of values you may enter in a condition when you use the IN LIST operator.

99: Specifies that you may enter up to 99 values when you create a condition using the IN LIST operator. This is the default value.

The maximum authorized value you may enter depends on your database.

PATH_FINDER_OFF

Parameter is not listed by default. You must add the parameter manually to the list and set a value.

PATH_FINDER_OFF= Y|N

Data Access Guide 99

Configuring SQL generation parameters for a universe

Editing SQL generation parameters in a universe

Values

Default

Description

REPLACE_COMMA_BY_CONCAT

Yes|No

No default. You must manually enter the parameter.

Used for HPIW because the join generation is done by the database.

Yes: Joins are NOT generated in the

query.

No: Joins are generated in the query.

This is the default behavior.

REPLACE_COMMA_BY_CONCAT= Yes|No

Values

Default

Description

Yes|No

Yes

In previous versions of Designer, a comma could be used to separate multiple fields in an object Select statement. The comma was treated as a concatenation operator. For universes that already use the comma in this way you can set REPLACE_COM

MA_BY_CONCAT to No to keep this be-

havior. In the current version of Designer, this parameter is set to Yes by default, so that any expressions using a comma in this way are automatically changed to use concatenation syntax.

Yes: Comma is replaced by the con-

catenation expression when multi field object is found.

No: Keep the comma as it is.

100 Data Access Guide

Business objects CONNECTION SERVER XI 3.0 User Manual

Specifications and Main Features

Frequently Asked Questions

User Manual

Contents

Data access basics

About Connection Server

Components of a connection

System architecture

About data access drivers

About data access configuration files

Global configuration files

Driver configuration files

Creating a connection

Before you create a connection

Checking connection configuration: cscheck

Displaying help on the cscheck tool

Running the cscheck tool

Check tool—function overview

Check tool—list

Check tool—drivers search

Check tool—find

Check tool—middleware

Check tool—accessdriver

Check tool—connectivity

Check tool—ping

Accessing the New Connection wizard from the Designer Connections list

Accessing the New Connection wizard from the Parameters dialog box

Using the New Connection Wizard

Creating a new connection

Database Middleware Selection dialog box

Login Parameters dialog box

Configuration parameters dialog box

Custom Parameters dialog box

About JDBC connections

Creating a JDBC connection

JDBC SBO example file structure

About JavaBean connections

Creating a JavaBean connection

JavaBean SBO example file structure

Configuring data access global parameters

About global parameters

About the cs.cfg global configuration file

Viewing and editing cs.cfg

Configuring the <driver defaults> parameters

Configuring <Settings> parameters

Charset List Extension

Config File Extension

Description Extension

Enable Failed Load

Load Drivers On Startup

Max Pool Time

SQL External Extension

SQL Parameter Extension

Strategies Extension

Configuring CORBA access

Configuring the Distribution section for CORBA access

Configuring data access driver parameters

Configuring driver parameters

Data access configuration files

Installed SBO files

Editing an SBO file

SBO file parameter reference

SBO parameter categories

SBO file structure

Default SBO parameters

Array Bind Available

Array Bind Size

Array Fetch Available

Array Fetch Size

Catalog Name Max Size

Catalog Separator

CharSet

Column Name Max Size

Connection Shareable

Cost Estimate Available

Description File

Driver Capabilities

Escape Character

Extensions