business objects xir2 data access guide en

Data Access Guide

BusinessObjects XI Release 2 Update, March 2006

Windows and UNIX

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

Trademarks Business Objects, the Business Objects logo, Crystal Reports, and Crystal Enterprise are trademarks or registered trademarks of Business Objects SA or its affiliated companies in the United States and other countries. All other names mentioned herein may be trademarks of their respective owners.

Copyright Copyright © 2006 Business Objects. All rights reserved.Last update: March 2006

Part Number 320-50-650-0

Contents
Chapter 1 Data access basics 5
Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6Introducing Connection Server and data access drivers . . . . . . . . . . . . . . . 6Connecting to your data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8Data access drivers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10What data access files are installed? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10

Chapter 2 Creating connections 15Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16Creating a connection with the New Connection wizard . . . . . . . . . . . . . . 16

Chapter 3 Managing data access 29Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30Setting dynamic SQL parameters in a universe . . . . . . . . . . . . . . . . . . . . . 30Optimizing data access by editing data access parameter files . . . . . . . . . 32

Chapter 4 Data type conversion reference 37Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38IBM DB2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38Informix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39Microsoft SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40Red Brick . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41Sybase . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42Teradata . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43

Chapter 5 Setting universe dynamic SQL parameters 45Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46Dynamic SQL parameters in a universe . . . . . . . . . . . . . . . . . . . . . . . . . . 46

Data Access Guide 3

Contents

Chapter 6 Setting data access SQL parameters (PRM) 59Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60Data access driver SQL parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 60PRM file Configuration reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64

Chapter 7 Setting data access driver configuration parameters (SBO) 85Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86Data access driver configuration parameters . . . . . . . . . . . . . . . . . . . . . . . 86SBO file reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 89

Chapter 8 Setting data access general configuration parameters (CFG) 99Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100Data access general configuration file parameters . . . . . . . . . . . . . . . . . . 100Settings reference . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103

Appendix A Business Objects information resources 107Documentation and information services . . . . . . . . . . . . . . . . . . . . . . . . . 108Documentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108Customer support, consulting and training . . . . . . . . . . . . . . . . . . . . . . . . 109Useful addresses at a glance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 110

Index 113

4 Data Access Guide

Data access basics

chapter

Data access basicsOverview1
Overview
Data access for Web Intelligence, Desktop Intelligence, and Designer is managed by Connection Server. Connection Server is the Business Objects software layer that creates and manages connections used by universes between BusinessObjects data access drivers and target RDBMS middleware. Connection Server and the required data access components are automatically installed when you install , Desktop Intelligence, or Designer.This chapter introduces Connection Server. The following topics are described:• Introducing Connection Server and data access drivers• Connecting to your data• Data access drivers• What data access files are installed?Note: $INSTALLDIR variable in this guideIn this guide the variable $INSTALLDIR is the install root path for the data access files used by Designer and Web Intelligence. This is the Business Objects installation path with the operating system sub directory that contains the Designer executable and the data access drivers.Under Windows$INSTALLDIR = \\...\Business Objects\BusinessObjects Enterprise 11.5\win32_x86.For example C:\Program Files\Business Objects\BusinessObjects Enterprise 11.5\win32_x86.

Introducing Connection Server and data access drivers

Universes access data in relational databases by using a connection to the data source middleware.A connection is a defined set of parameters between a BusinessObjects data access driver and a target middleware.A data access driver is the software layer that maps the SQL definitions and functions defined in the middleware to equivalent structures and parameters in a universe.

6 Data Access Guide

Data access basicsIntroducing Connection Server and data access drivers 1

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

What is Connection Server?Connection Server is the BusinessObjects data access management software that is installed automatically with one or more data access drivers for your RDBMS. Connection Server is installed by default with WebIntelligence and Designer.Connection Server allows InfoView and WebIntelligence users to connect to and run queries against a relational data source, whether it is a corporate database or a repository.

How do you access Connection Server features?Connection Server does not have a user interface. You create and administer connections from the user interface of Designer.

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

Optimizing data accessYou can optimize the way that data is passed through Connection Server by modifying SQL and data access parameter files. These are files in XML format that are installed with Connection Server. You can edit these files to optimize values for parameters specific to a RDBMS, or general parameters that apply to all installed data access drivers.For information on data access drivers, see the section “Data access drivers” on page 10.For information on editing data access parameters files to optimize data access, see the sections:• “Optimizing data access by editing data access parameter files” on

page 32• “Setting data access SQL parameters (PRM)” on page 59• “Setting data access driver configuration parameters (SBO)” on page 85

Data Access Guide 7

Data access basicsConnecting to your data1

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

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

Connecting to your dataA connection allows WebIntelligence users to access data in a target RDBMS.

What is a connection?A connection is a named set of parameters that defines how Desktop Intelligence, Web Intelligence, and Designer access data in a database file. It is the link between a universe, a data access driver, your middleware, and your database.You must have a valid connection between your target universe and your database in order to access your data.A connection is made up of the following elements:

Connection element Description

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

Connection and login parameters

Connection parameters that allow the data access driver to connect to the target database middleware. These parameters include Connection user name, password, and data source name.

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

8 Data Access Guide

Data access basicsConnecting to your data 1

Pre-requisites to creating a connection?To create a connection you must have the following:• middleware• information about your database server• Designer installed• BusinessObjects data access driver

Connection workflow overviewThe following is an overview of the process that you follow to create a connection. Creating connections is fully described in the chapter “Creating connections” on page 15.• Ensure that you have all the information necessary to access your

database.• Install middleware so that you can access your database through either

your computer or a server.• Install Business Objects products and the appropriate data access driver.• Using the New Connection wizard accessible from Designer, you connect

your middleware to your data access driver.• Make any necessary modifications to optimize the connection

parameters. You can customize certain parameters from the Connection Wizard.Other parameters can be customized in the SQL parameter file associated with the data access driver. This file is called the <driver>.prm file. See the chapter “Setting data access SQL parameters (PRM)” on page 59 for information on editing the PRM file for your data access driver.Many SQL generation parameters can also be set at the universe level from Designer. See the chapter “Setting universe dynamic SQL parameters” on page 45 for information.

Data Access Guide 9

Data access basicsData access drivers1
Data access drivers
A data access driver is the BusinessObjects software layer that connects a universe to your middleware.Data access drivers are shipped with Business Objects products. There is a data access driver for each supported middleware. When you install Business Objects products, your data access key determines which data access drivers are installed.When you create a new connection, you select the appropriate data access driver for the RDBMS middleware that you use to connect to the target RDBMS.For example, if you access an Oracle 8i database, you must install the appropriate middleware (Oracle 8i Client), then the BusinessObjects Oracle data access driver.The types of databases supported through data access drivers are listed below.• IBM DB2• Informix• Microsoft SQL Server and Analysis Services• Oracle• Red Brick• Sybase• Teradata• MySQL• SAP Business Warehouse

What data access files are installed?This section describes the data access files that are installed when you install Business Objects products. Data access files can be divided into two levels:• Global level. These are files that apply to all installed data access drivers. • Driver level. These are data access driver specific files.Note: Not all the files installed by Connection Server are listed. Most of the files described in this section contain parameters that can be modified to optimize or customize data access. Certain files are listed to explain their function. These and other files in these directories must not be modified. They are required for full functionality, so do not remove them.

10 Data Access Guide

Data access basicsWhat data access files are installed? 1

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

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

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

$INSTALLDIR\dataAccess\RDBMS\connectionServer

Installed file Edit this file?

Description

cs.cfg Yes Stores all configuration parameters for the global configuration of Connection Server.See “Setting data access general configuration parameters (CFG)” on page 99

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

charsets.txt No List of the charset supported by BusinessObjects.Warning: Do not modify this file.

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

cschecklocales.xml No Contains locale information for each language used by the cscheck tool when verifying connection configuration.Warning: Do not modify this file.

Data Access Guide 11

Data access basicsWhat data access files are installed?1

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

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

strategy_localization.dtdstrategy.dtdsbo.dtddbparameters_localization.dtddbparameters.dtdcscheckresults.dtdcschecklocales.dtdcs.dtd

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

<driver><language>.cod

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

Installed file Edit this file?

Description

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


Data access basicsWhat data access files are installed? 1

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

Driver specific file Can be edited?

Description Example

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

oracleen.stg

<driver>.stg Yes Contains the external strategies available to the data access driver.See the Designer’s Guide for information on using external strategies.

oracle.stg

<driver>.sbo Yes Defines all databases supported and the specific connectivity configuration for each database.See the sections:“Data access driver general configuration (<driver>.SBO)” on page 34“Setting data access driver configuration parameters (SBO)” on page 85

oracle.sbo

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

oracleen.prm


Data access basicsWhat data access files are installed?1

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

by editing data access parameter files” on page 32

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

oracle.prm

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

oracleen.cod

Driver specific file Can be edited?

Description Example


Creating connections

chapter

Creating connectionsOverview2
Overview
This chapter provides information about creating connections to database middleware.It describes how you create connections using the New Connection wizard, set login parameters, and optimize certain connection settings. Connection issues that are specific to RDBMS are also covered.This chapter covers the following topics:• Creating a connection with the New Connection wizard• Setting login parameters• Setting advanced parameters• Setting custom parameters

Creating a connection with the New Connection wizard

You create a connection using the New Connection Wizard. This is a connection wizard that is accessible from Designer. The wizard allows you to select a target database and middleware, then configure the data access driver that you use to access the middleware.

What connection parameters are set in the New Connection wizard?

You use the wizard to set the following parameters:

Connection wizard stage Description

Login parameters.See “Setting login parameters” on page 22 for more information.

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


Creating connectionsCreating a connection with the New Connection wizard 2

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

Using the New Connection wizardYou create a connection using the New Connection wizard.

Accessing the New Connection wizardYou access the New Connection in Designer as follows:

From the Universe parameters dialog box1. Select File > Parameters2. Click the New button.

From the Connections list1. Select Tools > Connections.2. Click AddThe Welcome page of the New Connection wizard appears.

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

The wizard welcome page appears.2. Click Next.

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

3. Do the following from the Database middleware selection page:• Expand a database node.

Advanced parametersSee “Setting advanced parameters” on page 24 for more information.

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

Custom parametersSee “Setting custom parameters” on page 26 for more information.

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

Connection wizard stage Description


Creating connectionsCreating a connection with the New Connection wizard2

The middleware available for the database appear under the node.• Expand a middleware node.The data access driver for the middleware appears under the node.• Click the data access driver name.The data access driver for Oracle 9 is shown below.

• Click Next.The Login parameters page appears. It lists login and connection identifying parameters for the data access driver. Login parameters are fully described in the section “Setting login parameters” on page 22.

4. Do the following from the Login parameters page:• Select a connection type.• Type connection name, user name, and database password.• Select a data source from the Data source or Service drop down list

boxOrType the name for the database or data source that you want to access.



A Login Parameters page with parameters completed appears below.

• Click Next.Note: If you want users to access the database with their CMS (repository) login, select the Use Single Sign-On when viewing reports check box. To use this feature, the CMS login must be the same as the login to the database.The Perform a test page appears. It summarizes the connection information and allows you to test the connection.

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



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

6. Select values for advanced connection parameters then click Next.OrClick Next directly if you don’t want to set advanced parameters.



The Custom Parameters page appears. It allows you to manually set certain parameters. Custom parameters are fully described in the section “Setting custom parameters” on page 26.

7. If you do not want to modify a listed parameter, click Finish.OrIf you want to modify a default parameter value do the following:• Select a parameter in the list.• Type a value in the text box under the parameter list and click Set.• If you want to remove the value, click Remove.



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

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

Setting login parametersThe Login Parameters page of the New Connection wizard contains the following parameters:

Parameter Description

Type Connection type. You can create one of three connection types:• Personal• Shared• SecuredSee the section “Connection type” on page 23 for more information on the types of connections that you can create.

Name Identifying name for the connection.User name Your database user name. This is normally

assigned to you by the database administrator.



Connection typeThe type of connection determines who can use the connection to access data. Designer automatically stores all the connections that you create during a work session. The next time you launch a session, these connections will be available to you. You can create three types of connections with Designer:• Personal• Shared• SecuredEach connection type is described as follows:

Personal connectionsRestricts access to data to the universe creator and the computer on which it was created.Connection parameters are stored in the PDAC.LSI file located in the LSI folder in the Business Objects 11.5 folder in you user profile directory, for example:C:\Documents and Settings\<user name>\Application Data\Business Objects\Business Objects 11.5\lsiThese parameters are static and cannot be updated.

Use Single Sign-On when refreshing reports at view time

When selected, the user name and password used to access the CMS are automatically used as database login parameters. See the BusinessObjects Enterprise Administrator’s Guide for information on setting up Single Sign-On.

Use database credentials associated with BusinessObjects user account

When selected the user is forced to enter 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 BusinessObjects Enterprise Administrator’s Guide for information on setting up this option.

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

Data source or Service

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




Personal connections are unsecured in terms of Business Objects products security.You do not use personal connections to distribute universes. You could use personal connections to access personal data on a local machine.

Shared connectionsAllows access to data for all users. These connections are unsecured in terms of Business Objects products security. Connection parameters are stored in the SDAC.LSI file located in the LSI folder in the Business Objects 11.5 folder in you user profile directory, for example:C:\Documents and Settings\<user name>\Application Data\Business Objects\Business Objects 11.5\lsi

Secured connections• Centralizes and controls access to data. It is the safest type of

connection, and should used be to protect access to sensitive data.• You can create secured connections with Designer.• You must use secured connections if you want to distribute universes

through the CMS.• Secured connections can be used and updated at any time.

Setting advanced parametersThe Advanced page contains parameters used to over ride the following common configuration options:• Defining the duration of a connection into a pool• Defining how Business Objects products respond when database

resources are not available• Defining the size of the array fetch



You can set the following advanced parameters:


Disconnect after each transaction

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

Keep the connection active for x minutes

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

Keep the connection active during the whole session (local mode only)

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

Array fetch size Enter the maximum number of rows authorized with each fetch. If you enter 20, and your query retrieves 100 rows, the connection executes 5 fetches to retrieve your data. Five fetches of 20 rows each equals 100 rows of data.If you enter 1, the array fetch is deactivated and data is retrieved row by row.Note: Deactivating array fetch is the safest way of retrieving your data but row-by-row retrieval slows down server performance. The greater the value in the Array fetch size option, the faster your rows are retrieved; you must, however, ensure you have adequate client system memory.Note: If a query retrieves data containing blob or long text types, the array fetch is automatically reset to 1.The default value is 20.



Setting custom parametersValues on the Custom page should only be modified by an advanced user, DBA, or BusinessObjects administrator. It contains certain parameters that can be used to override connection settings. The following parameters are available• Binary Slice Size. This appears by default in the Advanced page for all

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

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

Using Hints (Oracle only)A Hint is commented information that is embedded in a query, and used by the Oracle optimizer to choose an execution plan. You should consult your Oracle documentation for full information on the Hints that can be used, and how they can be used to optimize queries.You enter the value for a Hint as a comment. A plus sign is used at the beginning, immediately after the comment delimiter, with no space between the comment delimiter and the plus sign:/*+ <hint> */

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

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




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

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

Hint What it does...

FIRST_ROWS Optimizes response time.RULE Use rule-based optimization and not cost.FULL Does a full table scan on the table.ROWID Scans table by rowid.INDEX_FFS Perform a fast full scan on the index rather than

on the table.


Managing data access

chapter

Managing data accessOverview3
Overview
This chapter describes how you can use parameter files to optimize your data access driver. The following areas are described:• Setting dynamic SQL parameters in a universe• Optimizing data access by editing data access parameter filesNote: Other tools in the readme: You can use command line utilities to check your RDBMS and data access driver configuration, and to create log files that trace Web Intelligence server activity. Refer to the readme for this release for instructions on the use of these utilities.

Setting dynamic SQL parameters in a universe

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

Using parameter (PRM) files in previous versions of DesignerIn previous versions of Designer, the SQL generation parameters used by a universe were maintained and edited in a separate file called a parameters (PRM) file. The values set in the PRM file applied to all universes using the associated data access driver defined for a connection.Many of the SQL parameters that are used to optimize query generation are now controlled within an individual universe file. The PRM file is now no longer used for the query generation parameters that you can set in Designer. PRM files are still used for parameters that are database specific.Note: See the chapter Setting universe dynamic SQL parameters for a complete reference on the universe SQL parameters available in Designer.

Setting the SQL parameters dynamically in DesignerMany 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.


Managing data accessSetting dynamic SQL parameters in a universe 3

Editing SQL generation parametersYou can modify the values for SQL parameters that determine SQL generation in products using the universe.See the chapter Setting universe dynamic SQL parameters for a complete reference on the universe SQL parameters available in Designer.To edit SQL generation parameters:1. Select File > Parameters.

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

The Parameter page appears.


Managing data accessOptimizing data access by editing data access parameter files3

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

4. Click OK.Note: The SQL generation parameter values that you set in a universe, are only available to products using that universe.

Optimizing data access by editing data access parameter files

Certain data access parameters can be modified to optimize BusinessObjects and WebIntelligence access to data for all installed data access drivers, a specific data access driver, or the SQL generation parameters for a specific data access driver.Data access parameters are listed in three XML files:• General configuration for all data access drivers (CS.CFG)• Data access driver general configuration (<driver>.SBO)• Data access driver SQL parameters (<driver>.PRM)These files are all in XML format. You should use an XML editor, for example XML Spy to edit these files.

To... Then do the following

Add a new parameter 1. Click any parameter in the list.2. Type a name in the Name box3. Type a value in the Value box.4. Click Add.The new value appears at the bottom of the list

Change name or value 1. Click a parameter in the list.2. Type a new name in the Name box3. Type a new value in the Value box.4. Click Replace.The value is replaced by the new definition.

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

2. Click Delete.


Managing data accessOptimizing data access by editing data access parameter files 3

Each of these files is described briefly below. Full descriptions and editing procedures are described in the reference chapter for each file at the end of the book.Note: Certain SQL generation parameters that were set in the PRM file in previous versions of BusinessObjects products, are now available at the universe level. These parameters are edited in a universe and are available to all reporting products using the universe. See the section “Setting dynamic SQL parameters in a universe” on page 30 for more information.

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

access drivers.• SBO: Default connection and driver settings that are driver specific. these

default values apply only to the target data access driver.• PRM: SQL operators and functions that are available to the target data

access driver. The values set for these SQL operators and functions can be modified to optimize the SQL generated in Business Objects products.

Each of these files is discussed briefly below. A list of all parameters available in each file, and the methods for viewing and editing the files are available in the reference chapters that correspond to each file at the back of this manual.

General configuration for all data access drivers (CS.CFG)The general configuration for all data access drivers (CS.CFG) file contains parameters for the following uses:• Char set for each locale• List of all files used by data access drivers to create and configure

connections• Driver defaults that apply to all drivers, for example array fetch size and

array bind size.• Activating traces for connection activity.



Values set in the CS.CFG file apply to all data access drivers listed in the DRIVER DEFAULTS section of the file.The CS.CFG file is found here:

The file uses the cs.dtd in the same directory.A description of each parameter in the CS.CFG file, and the procedure for editing the file are documented in the chapter “Setting data access general configuration parameters (CFG)” on page 99.

Data access driver general configuration (<driver>.SBO)The data access driver general configuration (.SBO) file contains parameters for the following uses:• List of files used by a specific data access driver to manage connections• Driver defaults that apply to a specific data access driver.• List of database aliases used for connections defined with older database

engines.Values set in the <driver>.sbo file apply to the specific <driver> data access driver.The <driver>.sbo file is found here:

The file uses the sbo.dtdin the directory $INSTALLDIR \dataAccess\RDBMS\connectionServer.

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

Data access driver SQL parameters (<driver>.PRM)The data access driver SQL parameters (.PRM) file contains parameters that have default values set for SQL generation parameters that apply to a specific data access driver.The data access SQL parameter (.PRM) file contains parameters for the following uses:• Configuration parameters that specify how certain database operations

are handled by the data access driver.

$INSTALLDIR\dataAccess\RDBMS\connectionServer\

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


Managing data accessOptimizing data access by editing data access parameter files 3

• Date operators.• A list of the SQl operators supported by the data access driver.• A list of the database functions supported by the data access driver.

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

A description of each parameter in a PRM file, and the procedure for editing the file are documented in the chapter “Setting data access SQL parameters (PRM)” on page 59.Any modification to this file applies only to the specified data access driver. The <driver>.prm file is found here:

The file uses the dbparameter.dtd in the directory$INSTALLDIR \dataAccess\RDBMS\connectionServer.

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

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


Data type conversion reference

chapter

Data type conversion referenceOverview4
Overview
This chapter contains data conversion tables for each supported RDBMS. Business Objects supports four datatypes:• date• character• number• long textData conversion tables are available for the following RDBMS:• IBM DB2• Informix• Microsoft SQL Server• Oracle• Red Brick• Sybase• TeradataNote: Data conversion tables for other supported RDBMS will be available in future updates of the Data Access guide. The Data Access guide and other Business Objects documentation is available from the product documentation page of the Business Objects Customer Support site:http://support.businessobjects.com/documentation

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

IBM/DB2 datatype BusinessObjects datatypeCLOB LONG TEXTCHARACTER CHARACTERDATE DATEDECIMAL NUMBERDOUBLE NUMBERFLOAT NUMBERINTEGER NUMBERLONG VARCHAR LONG TEXTNUMERIC NUMBER


Data type conversion referenceInformix 4

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

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

SMALLINT NUMBERTIME DATETIMESTAMP DATEVARCHAR CHARACTER

IBM/DB2 datatype BusinessObjects datatype

Informix datatype BusinessObjects datatypeCHAR CHARACTERDATE DATEDATETIME DATEDECIMAL NUMBERFLOAT NUMBERINTEGER NUMBERMONEY NUMBERNCHAR CHARACTERNVARCHAR CHARACTERSERIAL NUMBERSMALLINT NUMBERSMALLFLOAT NUMBERTEXT LONG TEXTVARCHAR CHARACTER

Microsoft SQL Server datatype BusinessObjects datatypeBIT NUMBERBOOLEAN NUMBER


Data type conversion referenceOracle4

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

* indicates Oracle 9i datatype

CHAR CHARACTERDATETIME DATEDECIMAL NUMBERFLOAT NUMBERINT NUMBERMONEY NUMBERNUMERIC NUMBERREAL NUMBERSMALLDATETIME DATESMALLINT NUMBERSMALLMONEY NUMBERTEXT CHARACTERTINYINT NUMBERVARCHAR CHARACTER

Microsoft SQL Server datatype BusinessObjects datatype

Oracle datatype BusinessObjects datatypeCHAR CHARACTERCLOB LONG TEXTDATE DATEFLOAT NUMBERINTEGER NUMBERLONG LONG TEXTNUMBER NUMBERVARCHAR CHARACTERVARCHAR2 CHARACTER


Data type conversion referenceRed Brick 4

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

TIME and TIMESTAMP support in a WHERE clauseObjects that use the TIME and TIMESTAMP datatype are not supported in a WHERE clause for queries run against a Red Brick database. You can set the properties of an object in a universe to support the use of TIME and TIMESTAMP in the WHERE clause as follows:To support TIME or TIMESTAMP datatype object in WHERE clause:1. From Designer, double-click an object in the Universe pane.

The Edit Properties of Object dialog box appears.2. Click the Definition tab.3. Select Date from the Type drop down list.4. Click the Advanced tab.5. In the Database Format textbox type the following

6. Click OK.

Red Brick datatype BusinessObjects datatype

CHAR CHARACTERDATE DATEDECIMAL NUMBERFLOAT NUMBERINTEGER NUMBERNUMERIC NUMBERSMALLINT NUMBERTIME DATETIMESTAMP DATE

For... Type

TIME support HH:MM:SSTIMESTAMP support MM/DD/YYYY HH:MM:SS


Data type conversion referenceSybase4
Sybase
The following table lists the Sybase internal datatypes and their equivalent in Business Objects products

Sybase datatype BusinessObjects datatypeBIT NUMBERCHAR CHARACTERDATETIME DATEDECIMAL NUMBERFLOAT NUMBERINT NUMBERMONEY NUMBERNUMERIC NUMBERREAL NUMBERSMALLDATETIME DATESMALLINT NUMBER SMALLMONEY NUMBER TINYINT NUMBER VARCHAR CHARACTER


Data type conversion referenceTeradata 4

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

Teradata datatype BusinessObjects datatype

DATE DATEDECIMAL NUMBERFLOAT NUMBERINTEGER NUMBERSMALLINT NUMBERBYTEINT NUMBERDATETIME DATEVARCHAR CHARACTER


Data type conversion referenceTeradata4


Setting universe dynamic SQL parameters

chapter

Setting universe dynamic SQL parametersOverview5
Overview
This chapter provides an alphabetical reference for the SQL generation parameters listed in the Parameter page of the Universe Parameters dialog box in Designer. These are SQL parameters that are common to most data access drivers. Each parameter is valid for the universe in which it is set. Other RDBMS specific and connection parameters are listed in the data access parameter (PRM) file for the target data access driver.Refer to the section “Setting dynamic SQL parameters in a universe” on page 30 for information on how to set the dynamic SQL parameters in Designer. Other RDBMS specific SQL parameters are described in the chapter “Setting universe dynamic SQL parameters” on page 45.

Dynamic SQL parameters in a universeParameters used for SQL generation can be set for a universe. In previous versions of Business Objects products, all SQL parameters were set in the <RDBMS>.PRM file. This file was a static text file that applied to all universes using the data access driver.The PRM file is still used for many RDBMS specific SQL and connection parameters, however, SQL parameters common across RDBMS are now modified within an individual universe. These parameters apply only to the current universe, so universes using the same data access driver, can have different SQL generation behavior.These dynamic SQL parameters are saved in the .UNV file, and are accessible only from the universe.This section lists and describes the dynamic SQL generation parameters available to universes. Refer to the section Setting dynamic SQL parameters in a universe in the Managing Connections chapter for information on accessing and modifying these parameters. This parameter reference is also available in the Designer’s Guide.


Setting universe dynamic SQL parametersDynamic SQL parameters in a universe 5

ANSI92ANSI92 = Yes|No

AUTO_UPDATE_QUERYAUTO_UPDATE_QUERY = Yes|No

BLOB_COMPARISONBLOB_COMPARISON = Yes|No

Values Yes|NoDefault NoDescription Specifies whether the SQL generated complies to the

ANSI92 standard.Yes: Enables the SQL generation compliant to ANSI92 standard.No: SQL generation behaves according to the PRM parameter OUTER_JOIN_GENERATION.

Values Yes|NoDefault YesDescription Determines what happens when an object in a query is

not available to a user profile.Yes: Query is updated and the object is removed from the query.No: Object is kept in the query.

Values Yes|NoDefault NoCan be edited? NoDescription Species if a query can be generated with a DISTINCT

statement when a BLOB file is used in the SELECT statement. It is related to the setting "No Duplicate Row" in the query properties.Yes: The DISTINCT statement can be used within the query. No: The DISTINCT statement cannot be used within the query even if the query setting "No Duplicate Row" is on.


Setting universe dynamic SQL parametersDynamic SQL parameters in a universe5
BOUNDARY_WEIGHT_TABLE
BOUNDARY_WEIGHT_TABLE = Integer 32bits [0-9]

COLUMNS_SORTCOLUMNS_SORT=Yes|No

Values Integer 32bits [0-9]Default -1Description Allows you to optimize the FROM clause when tables

have many rows.If the table size is greater than the entered value, the table is declared as a subquery:FROM (SELECT col1, col2,...., coln FROM

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

Limitations Optimization is not implemented when:• the operator OR is in the query condition• only one table is involved in the SQL• the query contains an outer join• no condition is defined on the table that is being

optimized• the table being optimized is a derived table.

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

Default NoDescription Determines the order that columns are displayed in

tables in the Structure pane.



COMBINE_WITHOUT_PARENTHESISCOMBINE_WITHOUT_PARENTHESIS=Yes|No

COMBINED_WITH_SYNCHROCOMBINED_WITH_SYNCHRO = Y|N

Values YES Removes the parentheses.NO Leaves the parentheses.

Default NoDescription Specifies whether or not to encapsulate a query with

parentheses when it contains UNION, INTERSECT or MINUS operators. Used with RedBrick.

Values Yes|NoDefault NoDescription Specifies whether to allow a query to execute that

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


CORE_ORDER_PRIORITY
CORE_ORDER_PRIORITY = Yes|No

CORRECT_AGGREGATED_CONDITIONS_IF_DRILLCORRECT_AGGREGATED_CONDITIONS_IF_DRILL = Yes|No

Values Yes|NoDefault YesDescription Specifies in which order you want classes and objects to

be organized once two or more universes are linked in Designer.Yes: Specifies that classes and objects follow the order defined in the kernel universe.No: Specifies that classes and objects follow the order defined in the derived universe. This is the default value.

Values Yes|NoDefault NoDescription Specifies whether BusinessObjects can aggregate

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



CUMULATIVE_OBJECT_WHERECUMULATIVE_OBJECT_WHERE = Y|N

DECIMAL_COMMADECIMAL_COMMA = Yes|No

Values Yes|NoDefault NoDescription 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’

Values Yes|NoDefault YesDescription 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.


DISTINCT_VALUES_
DISTINCT_VALUES = GROUPBY|DISTINCT

END_SQL END_SQL = String

Values GROUPBY|DISTINCTDefault DISTINCTDescription Specifies whether SQL is generated with a DISTINCT or

GROUPBY clause in a list of values and query panel when the option “Do not retrieve duplicate rows” is active.DISTINCT: The SQL is generated with a DISTINCT clause, for example;SELECT DISTINCTcust_nameFROMCustomersGROUPBY: The SQL is generated with a GROUP BY clause, for example;SELECTcust_nameFROMCustomersGROUPBY cust_name

Values StringDefault <empty string>Description The statement specified in this parameter is added at the

end of each SQL statement.



EVAL_WITHOUT_PARENTHESISEVAL_WITHOUT_PARENTHESIS = Yes|No

FILTER_IN_FROMFILTER_IN_FROM = Yes|No

Values Yes|NoDefault NoDescription By default, the function @Select(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(objet1) = A-B and SQL(objet2) =C,then the operation is ( A-B ) * ( C ).You avoid the default adding of brackets by setting EVAL_WITHOUT_PARENTHESIS = Yes. The operation is then A - B * C.Yes: Brackets are removed from the Select statement for a function @Select(Class\object)No: Brackets are added around the Select statement for the function @Select(Class\object).

Values Yes|NoDefault NoDescription 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 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 default 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
FIRST_LOCAL_CLASS_PRIORITY = Yes|No

FORCE_SORTED_LOV FORCE_SORTED_LOV = Yes|No

MAX_INLIST_VALUESMAX_INLIST_VALUES = 99]

Values Yes|No Default NoDescription Only taken into account when

CORE_ORDER_PRIORITY=Yes.Yes: Classes in derived universe are placed first.No: Objects and sub classes from derived universe appear after those of the core universe.

Values Yes|NoDefault NoDescription 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.

Values Integer: min 0, max 256Default 99Description Allows you to increase to 256 the 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.256: Specifies that you may enter up to 256 values when you create a condition using the IN LIST operator. 256 is the maximum authorized value you may enter.



PATH_FINDER_OFFParameter is not listed by default. You must add the parameter manually to the list and set a value. See Setting dynamic SQL parameters in a universe for information on editing parameters.

PATH_FINDER_OFF= Y|N

REPLACE_COMMA_BY_CONCATREPLACE_COMMA_BY_SEPARATOR= Yes|No

Values Y|NDefault No default. You must manually enter the parameter.Description Used for HPIW because the join generation is done by

the database.Y: Joins are NOT generated in the query.N: Joins are generated in the query. This is the default behaviour.

Values Yes|NoDefault YesDescription 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_COMMA_BY_SEPARATOR to No to keep this behavior. In the current version of Designer, this parameter is set to Yes by default, so that a expressions using a comma in this way are automatically changed to use concatenation syntax.Yes: Comma is replaced by the concatenation expression when multi field object is found.No: Keep the comma as it is.


SHORTCUT_BEHAVIOR
SHORTCUT_BEHAVIOUR = Global|Successive

THOROUGH_PARSETHOROUGH_PARSE = Yes|No

Values Global|SuccessiveDefault SuccessiveDescription Specifies how shortcut joins are applied. This parameter

was formerly listed as GLOBAL_SHORTCUTS in the PRM files. The values have been changed to Global for Yes, and Successive for No.Global: Specifies that a shortcut joins are considered one by one. A shortcut join is applied only if it really bypasses one or more tables, and if it does not remove a table from the join path used by a following shortcut join.Successive: Specifies that all shortcut joins are applied. Note: If it generates a Cartesian product, no shortcut joins are applied.

Values Yes|NoDefault NoDescription Specifies the methodology used for default Parsing in the

Query panel and individual object parsing.Yes: PREPARE, DESCRIBE, and EXECUTE statements are used to parse SQL for objects.Prepare+DescribeCol+ExecuteNo: PREPARE and DESCRIBE statements are used to parse SQL for objects.



TRUST_CARDINALITIESTRUST_CARDINALITIES = Yes|No

Values Yes|NoDefault NoDescription Allows you to optimize the SQL in a case of inflated

results.Yes: For queries that include a measure, all conditions that inflate the measure and do not appear in the Result Objects, are transformed to sub queries to ensure that tables that may return false results for the measure are not included in the query.No: No optimization is implemented.


UNICODE_STRINGS
UNICODE_STRINGS = Yes|No

Values Yes|NoDefault NoDescription Specifies whether the current universe can manipulate

Unicode strings or not. Only applies to Microsoft SQL Server and Oracle 9. If the database character set in the SBO file is set as Unicode, then it is necessary to modify the SQL generation to handle specific Unicode column types like NCHAR and NVARCHAR. Yes: Conditions based on strings are formatted in the SQL according to the value for a parameter UNICODE_PATTERN in the PRM file, for example for MS SQL Server (sqlsrv.prm): UNICODE_PATTERN=N$The condition Customer_name='Arai ' becomesCustomer_name=N'Arai'.Note: When you create a prompt with @Prompt syntax based on Unicode value, the datatype should be 'U' not 'C'.No: All conditions based on strings are formatted in the standard SQL. For example the condition Customer_name='Arai ' remains Customer_name='Arai'


Setting data access SQL parameters (PRM)

chapter

Setting data access SQL parameters (PRM)Overview6
Overview
This chapter provides an alphabetical reference for all SQL configuration parameters listed in <driver>.prm files. There is a <driver>.prm file for each supported RDBMS.This reference describes the SQL parameters in the Configuration section. These are the parameters you use to optimize your connection. They are described here with information on how to use these parameters to optimize your data access.You can see the date operators, other operators, and functions available for your data access driver by opening the <driver>.prm file in an XML editor.Note: Certain parameters can be set in the Parameter page of the Universe Parameters dialog box in Designer (File > Parameters > Parameter). If you modify a parameter in Designer, the new value over rides any modification that you have made to the parameter value in the PRM file. The changes made in Designer apply only to the universe they are saved in, and the value in the PRM remains unchanged. See the Designer’s Guide for more information.

Data access driver SQL parametersEach data access driver is associated with a SQL parameters file (<driver>.prm). A data access SQL parameters file is a XML file that contains default SQL parameters used for query generation, date and other operators, and functions.<driver>.prm files are found in the directory \\<INSTALLDIR>\dataAccess\RDBMS\connectionServer\<rdbms>\.The following <driver>.prm files are available:

Data access driver PRM files PRM Help text files (English)

IBM DB2 db2udb.prmdb2mvs.prmdb2iseries.prm

db2udben.prmdb2mvsen.prmdb2iseriesen.prm

Informix informix.prm informixen.prmMicrosoft SQL Server sqlsrv.prm

sqlsrv_as.prmsqlsrven.prmsqlsrv_asen.prm

MySQL mysql.prm mysqlen.prm


Setting data access SQL parameters (PRM)Data access driver SQL parameters 6

The <driver>.prm files are in XML format. You should use a XML editor to view and modify values in these files if necessary.

Parameter file structureThere is a <driver>.prm file for each supported RDBMS middleware. Each <driver>.prm file is divided into the following sections. Each section contains parameters that have default values set:

ODBC odbc.prmaccess.prm

odbcen.prmaccessen.prm

Oracle oracle.prm oracleen.prmRed Brick redbrick.prm redbricken.prmSybase asiq.prm

sybase11.prmsybase.prm

asiqen.prmsybase11en.prmsybaseen.prm

SAP sap.prm sapen.prmTeradata teradata.prm teradataen.prm

Data access driver PRM files PRM Help text files (English)

File section Description

Configuration SQL parameters used to create and optimize a universe, for example, COMMA, OUTERJOINS_GENERATION, REVERSE_TABLE_WEIGHT. These parameters are not directly available to any Business Objects product. They are described in this chapter.

DateOperations Date operators available to Designer, Desktop Intelligence, and WebIntelligence, for example YEAR, QUARTER, MONTH.

Operators Operators available to Designer, Desktop Intelligence, and WebIntelligence, for example ADD, SUBSTRACT, MULTIPLY.


Setting data access SQL parameters (PRM)Data access driver SQL parameters6

Note: Only the Configuration section is documented here. These parameters can be edited to optimize queries run against universes using the target data access driver.

Viewing and editing PRM file parametersYou can view, edit, and add parameters to a PRM file as follows:1. Browse to the directory that stores the PRM file for your target data

access driver. PRM files are stored here:<INSTALDIR>\dataAccess\RDBMS\connectionServer\<rdbms>\<driver>.prm

2. Open a <driver>.prm file in a XML editor.3. Expand sections as required.4. Set values by entering the value in the appropriate tag.5. Save and close the file.

Functions Functions available to Designer, Desktop Intelligence, and WebIntelligence, for example Average, Sum, Variance.Help text that appears when functions in this section are selected in Designer, Desktop Intelligence, and WebIntelligence is listed in the file <driver><language>.prm, for example, oracleen.prm. This file is found in the same directory as the <driver>.prm file. You can open it to view descriptions of all the functions available in the <driver>.prm file. Viewing and editing function Help text is described in the section “Viewing and editing function help text” on page 63.The Function section has the following subsections:• Group: • ID: Name of the function. This is the name that appears in the function

list in Designer.• InMacro: Specifies if the function appears in the function list in an

object’s Edit Select box in Designer. When value is True, the function is listed, when value is False, the function is not listed. If you set the value to True, it will be listed the next time Designer is started.

• Type: Function data type.• Arguments: Arguments accepted by the function.• SQL: The SQL syntax for the function.



Setting data access SQL parameters (PRM)Data access driver SQL parameters 6

Viewing and editing function help textThe Help text that appears under each function when selected in Designer is maintained in a separate XML file. In previous versions of Business Objects products this text was maintained in the same file as the PRM parameters. You can edit and add text to describe a function by editing the file <driver>.<language>.prm. There is a Help text file for each language version of Business Objects products installed.The Help text appears as shown below when a function that is stored in the PRM file is selected in Designer:

When you add a function to the PRM file, you need to add the Help text for the new function to the appropriate <driver>.<language>.prm file, for example, if you add a function to the oracle.prm file, then you also add the function name and the Help text for the function to the oracleen.prm file, of you are working with the English version of Designer.

Help text appears here

function selected


Setting data access SQL parameters (PRM)PRM file Configuration reference6
Editing Help text for a PRM file function
You can view, edit, and add Help text for a function listed in a PRM file as follows:1. Browse to the directory that stores the PRM language file for your target

data access driver. PRM language files are stored here:$INSTALLDIR\dataAccess\RDBMS\connectionServer\<rdbms>\<driver><language>.prm

For example, oracleen.prm.2. Open a <driver><language>.prm file in a XML editor.3. Expand the Messages section.4. To add Help for a new function do the following:

• Add a new section for a function. The easiest way to do this is to copy an existing function entry and copy it into the Function section. You then edit the new function text.

• Enter Help text for the function.5. To view or edit existing function Help text, do the following:

• Expand the Function section.• Expand the Message section for a function.• Edit Help text as required.

6. Save and close the file.

PRM file Configuration referenceThe Configuration SQL parameters are listed alphabetically. To view functions, date operators, and other operators available, open a <driver>.prm file in a XML editor, 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 attributed to the parameter.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


Setting data access SQL parameters (PRM)PRM file Configuration reference 6

Note: Certain configuration parameters must not be edited. These parameters have values set for use internally within a Business Objects product. These parameters are described in this section but contain a warning not to edit the value. You must not edit these parameters. Before editing any other PRM file parameter, you should make a backup copy of the PRM file.

BACK_QUOTE_SUPPORTED<Parameter Name="BACK_QUOTE_SUPPORTED">Y</Parameter>

CASE_SENSITIVE<Parameter Name="CASE_SENSITIVE">N</Parameter>

CHECK_OWNER_STATE<Parameter Name="CHECK_OWNER_STATE">N</Parameter>

Description Specifies whether or not to enclose table or column names containing spaces or special characters with backquotes.

Values Y: Encloses table and column name with backquotes.N: Does not insert backquotes around table and column names.

Default YResult Table name=`My Table`

Description Specifies if the database is case sensitive. Parameter used with Oracle.

Values Y: Specifies that the database is case sensitive.N: Specifies that the database is not case sensitive.

Default N

Description Specifies whether or not there is a check if the database supports table classification by owner name.

Values Y: Checks if the database supports table classification by owner name.N: Does not check if the database supports table classification by owner name.

Default Y


CHECK_QUALIFIER_STATE
<Parameter Name="CHECK_QUALIFIER_STATE">N</Parameter>

COMMA<Parameter Name="COMMA">||' '||</Parameter>

CONCAT<Parameter Name="CONCAT">||</Parameter>

Description Specifies whether or not there is a check if the database supports table classification by qualifier.

Values Y: Checks if the database supports table classification by qualifier.N: Does not check if the database supports table classification by qualifier.

Default Y

Description Specifies what database concatenation operator should be used to replaces a comma for objects that have the following syntax:Tab.Col1, Tab.Col2.Parameter is used with all data access drivers.

Values ||' '||+' '+

Default ||' '||Result Tab.Col1||' '||Tab.Col2

Description Specifies the concatenation operator. Parameter is used with all data access drivers.

Values ||+

Default ||



DATABASE_DATE_FORMAT<Parameter Name="DATABASE_DATE_FORMAT">DD-MM-YYYY

HH24:MI:SS</Parameter>

DATATYPE_BLOBName="Datatype_Blob">LONGVARCHAR</Parameter>

DATATYPE_DOUBLE<Parameter Name="Datatype_Double">DATE</Parameter>

DATATYPE_DTM<Parameter Name="Datatype_Dtm">DATE</Parameter>

Description Specifies the default date and hour formats stored on the server.

Values 'DD-MM-YYYY HH24:MI:SS'Default See values above.

Description Do not edit this parameter. Column database datatype for Blob object. This parameter is not implemented for this release.

Default RDBMS specific. Oracle default is LONGVARCHAR

Description Do not edit this parameter. Column database datatype for Date object (CREATE table command)

Default RDBMS specific. Oracle default is DATE

Description Do not edit this parameter. Column database datatype for Date object (CREATE table command)

Default RDBMS specific. Oracle default is DATE


DATATYPE_INT
<Parameter Name="Datatype_Int">NUMBER</Parameter>

DATATYPE_NULL<Parameter Name="Datatype_Null"/>

DATATYPE_STRING<Parameter Name="Datatype_String">VARCHAR2</Parameter>

DATE_WITHOUT_QUOTE<Parameter Name="DATE_WITHOUT_QUOTE">Y</Parameter>

Description Do not edit this parameter. Column database datatype for Numeric object (CREATE table command)

Default RDBMS specific. Oracle default is NUMBER

Description Do not edit this parameter. Database SQL syntax for Null values (INSERT command)

Default RDBMS specific. Oracle default is no value.

Description Do not edit this parameter. Column database datatype for Character and Long object (CREATE table command).

Default VARCHAR2

Description Specifies support for dates without single-quotes in the SQL syntax.Parameter is used with MS Access.

Values Y: Dates are not surrounded by single-quotes.N: Dates are surrounded by single-quotes.

Default Y



EXT_JOIN<Parameter Name="EXT_JOIN">YES</Parameter>

EXT_JOIN_INVERT<Parameter Name="EXT_JOIN_INVERT">YES</Parameter>

EXTERN_SORT_EXCLUDE_DISTINCT<Parameter Name="EXTERN_SORT_EXCLUDE_DISTINCT">Y</Parameter>

Description Specifies whether or not outer joins are supported.Parameter is used by all data access drivers.

Values YES: Specifies that the database supports outer joins.NO: Specifies that the database does not support outer joins. The Outer join check boxes in the Edit Join dialog box of Designer are dimmed.

Default YES

Description Specifies how to display an outer join symbol in a join expression.Parameter is used with IBM DB2, Informix, Oracle, and Teradata.

Values YES: When you click an Outer join check box in the Edit Join dialog box of Designer, the outer join symbol appears reversed in position in a join expression.NO: When you click an Outer join check box in the Edit Join dialog box of Designer, the outer join symbol appears on the same side on which you created the outer join.

Default YES

Description Specifies whether or not to generate a SELECT DISTINCT when a query contains an ORDER BY.Parameter is used with Oracle.

Values Y: Specifies that a SELECT DISTINCT is not generated as the query contains an ORDER BY.N: Specifies that a DISTINCT is generated even though the query contains an ORDER BY.

Default Y


GROUPBY_EXCLUDE_COMPLEX
Parameter Name="GROUPBY_EXCLUDE_COMPLEX">N</Parameter>

GROUPBY_WITH_ALIAS<Parameter Name="GROUPBY_WITH_ALIAS">Y</Parameter>

Description Specifies whether the database allows you to enter formulas, aliases or indexes in GROUP BY clauses.Parameter is used with IBM DB2.

Values Y: Specifies that the database does not allow you to enter formulas, aliases or indexes in GROUP BY clauses. If you run a query containing measure objects and complex objects (e.g. with the substring function or the concatenation operator), your Business Objects product displays the following error message: “Your database does not allow you to do aggregations with the <object name> object”.N: Specifies that the database does allow you to enter formulas, aliases or indexes in GROUP BY clauses.

Default N

Description Specifies whether the database can create a GROUP BY clause in the SELECT statement.Parameter is used with Red Brick.

Values Y: Allows you to create a GROUP BY clause in the SELECT statement. This is possible using an internal alias which references a concatenated T1.col+T2.col+...+Tn.col statement.N: Does not let you to create a GROUP BY clause in the SELECT statement.

Default Y



GROUPBY_WITHOUT_CONSTANT<Parameter Name="GROUPBY_WITHOUT_CONSTANT">Y</Parameter>

GROUPBYCOL<Parameter Name="GROUPBYCOL">NO</Parameter>

INTERSECT<Parameter Name="INTERSECT">INTERSECT</Parameter>t

Description Specifies whether or not you authorize the addition of objects whose SQL definition is a constant in a GROUP BY clause. Parameter is used by IBM DB2 and Microsoft SQL Server

Values Y: Specifies that you can add any constant object to the query but it will not be present in the GROUP BY clause.N: Specifies that you can insert all the objects of a query (i.e. without aggregate functions) in a GROUP BY clause.

Default Y

Description Specifies whether a GROUP BY clause accepts integers for column names.

Values YES: Specifies that a GROUP BY clause followed by an integer is supported by the database.NO: Specifies that a GROUP BY clause followed by an integer is not supported by the database.

Default NO

Description Specifies if the database supports the INTERSECT set operator.

Values INTERSECT: The database supports the INTERSECT set operator.no value: The database does not support the INTERSECT set operator. In such a case, two queries are generated.

Default INTERSECT


KEY_INFO_SUPPORTED
<Parameter Name="KEY_INFO_SUPPORTED">Y</Parameter>

LEFT_OUTER<Parameter Name="LEFT_OUTER">$(+)</Parameter><Parameter Name="LEFT_OUTER">$*</Parameter>

LENMAXFORCOLUMNNAME<Parameter Name="LenMaxForColumnName">30</Parameter>

Description Specifies if you can retrieve primary and secondary key definitions from the database account.

Values Y: Specifies that the database lets you retrieve primary and secondary key definitions from the database account. This parameter enables Designer to display the keys in the Structure window.N: Specifies that the database does not let you retrieve primary and secondary key definitions from the database account.

Default Y

Description Specifies the left outer join syntax.Values $(+) This syntax is used with Oracle. $ represents a join

expression.$* This syntax is used with Sybase, MS SQL Server and Red Brick. $ represents a join expression.

Default See values above.

Description Do not edit this parameter. Maximum length for column name (by default the object name is proposed) (CREATE table command)

Default RDBMS specific. Oracle default is 30



LENMAXFORTABLENAME<Parameter Name="LenMaxForTableName">30</Parameter>

LENMAXFORVARCHAR<Parameter Name="LenMaxForVarchar">254</Parameter>

MINUS<Parameter Name="MINUS">MINUS</Parameter>

Description Do not edit this parameter. Maximum length for VARCHAR column datatype (CREATE table command).


Description Do not edit this parameter. Maximum length for VARCHAR column datatype (CREATE table command).


Description Specifies if the database supports the MINUS set operator. Values MINUS Specifies that the database supports the MINUS set

operator.EXCEPT Specifies that the database supports the MINUS set operator.no value Specifies that the database does not support the MINUS set operator. In such a case, two queries are generated.

Default MINUS


NO_DISTINCT
<Parameter Name="NO_DISTINCT">Y</Parameter>

OLAP_CLAUSE<Parameter Name="OLAP_CLAUSE">WHEN</Parameter>

Description Specifies if the database supports the DISTINCT keyword.Parameter is used with MS Access.

Values Y: Specifies that the database does not support the DISTINCT keyword. This behavior disables:The Distinct Values option that appears when you click the View Values button in the Quick Design wizardThe Countdistinct function that appears when you create a condition with the Calculation operand in the Query PanelN: Specifies that the database does support the DISTINCT keyword.

Default Y

Description Specifies whether Business Objects products generate a WHEN or QUALIFY clause if a function listed in the RISQL_Functions parameter is used in a condition. This parameter must be used with the GROUPBY clause.

Values WHEN: Generates a WHEN clause if a function listed in the RISQL_Functions parameter is used in a condition. This is the default value for Red Brick databases.QUALIFY: Generates a QUALIFY clause if a function listed in the RISQL_Functions parameter is used in a condition. This is the default value for Teradata databases.




OUTERJOINS_GENERATIONThis parameter is only used for compatibility with previous versions of Designer. the ability to create outer joins that conform to the ANSI 92 standard can be set at the universe level by setting the universe parameter ANSI92 to Yes. See the section “ANSI92” on page 47 for more information.<Parameter Name="OUTERJOINS_GENERATION">USUAL</Parameter>

Results of values applied for OUTERJOINS parameters aboveValue = USUALFROM T1, T2WHERE T1.col1(+) = T2.col2DB2:FROM T2 LEFT OUTER JOIN T1 ON T1.col1 = T2.col2ODBC:FROM {oj T1 LEFT OUTER JOIN T2 ON T1.col1=T2.col2}INFORMIX:FROM T2OUTER T1

Description Specifies the SQL syntax for outer joins.The value ANSI 92 generates an outer join in the FROM clause. Other values generate the outer join in the WHERE clause. When you modify this setting, you should check join properties to verify that the outer join expression is valid, and that the cardinalities are correct.MS SQL Server database engine, the sqlsrv.prm file uses standard outer join generation (in WHERE clause). To use ANSI 92 outer join generation with MS SQL Server, modify the sqlsrv.prm file as follows:EXT_JOIN_INVERT=YESMODIFY: LEFT_OUTER=RIGHT_OUTER=OUTERJOINS_GENERATION=ANSI_92

Values USUAL: Default value with Oracle, Sybase 11, and Red Brick.DB2: Default value for IBM DB2. ODBC: Default value for ODBC. FULL_ODBC: Can be used with MS SQL ServerINFORMIX Default value for Informix. NO: Outer joins are not supported.ANSI_92: Generates the ANSI92 syntax. Default value for Sybase.

Default See values above



WHERE T1.col1=T2.col2FULL_ODBC:FROM {oj T1 RIGHT OUTER JOIN T2 ON T2.col2=T1.col1}Value = ANSI_92:SELECT DISTINCT t1.col1, t2.col2FROM

(t1 RIGHT OUTER JOIN t2 ON (t1.col1=t2.col2) )

OVER_CLAUSE<Parameter Name="OVER_CLAUSE">Y</Parameter>

OWNER<Parameter Name="OWNER">Y</Parameter>

Description Allows Business Objects products to include RISQL functions when generating SQL. The supported RISQL functions for the database are listed in the RISQL_FUNCTIONS parameter.

Values Y: BusinessObjects products can include RISQL functions when generating SQL.N: BusinessObjects products cannot include RISQL functions when generating SQL.

Default Y

Description Specifies if the database authorizes to prefix tables by the owner name.

Values Y: Specifies that the database does support prefixing tables by the owner name. N: Specifies that the database does not support prefixing tables by the owner name.

Default Y



PATH_FINDER_4xThis parameter is not in any <driver>.prm file by default. To use it, you must add it as a parameter in the Configuration section.

PREFIX_SYS_TABLE<Parameter Name="PREFIX_SYS_TABLE">RBW_</Parameter><Parameter Name="PREFIX_SYS_TABLE">MSys</Parameter>

QUALIFIER<Parameter Name="QUALIFIER">N</Parameter>

Description Specifies if users can choose a context if all the proposed contexts use the same joins.

Values Y: Specifies that users can choose a context even if all the proposed contexts use the same joins.N: Specifies that users cannot choose a context if all the proposed contexts use the same joins.

Default No default value.

Description Specifies if the system tables are displayed in Designer. This parameter is found in the access.prm file and redbrick.prm file.

Values MSys: Specifies that the MS Access system tables are hidden in the Designer table browser. Default value for MS Access.RBW_ :Specifies that the Red Brick system tables are hidden in the Designer table browser. Default value for Red Brick.no value Specifies that the database system tables are displayed in the Designer table browser.


Description Specifies whether the database authorizes to prefix tables by the qualifier name

Values Y: Specifies that the database does support prefixing tables by the qualifier name.N: Specifies that the database does not support prefixing tables by the qualifier name.

Default RDBMS dependant.


QUOTE_OWNER
<Parameter Name="QUOTE_OWNER">Y</Parameter>

REFRESH_COLUMNS_TYPE<Parameter Name="REFRESH_COLUMNS_TYPE">O</Parameter>

Description Specifies whether or not an owner name should be in single quotes. Used by Informix only.

Values Y: Specifies that table names are prefixed by an owner name in single quotes. This is mandatory for an ANSI compliant Informix database. If not, Informix converts the owner name in upper case characters.N: Specifies that table names are not prefixed by an owner name in single quotes.

Default YResult SELECT Alias.col (<Alias> is a local Alias)

FROM 'Owner'.table.col Alias

Description Indicates how columns are refreshed.Values O: Columns are refreshed by owner name. This is the

default value with Oracle.Q: Columns are refreshed by qualifier name. This is the default value with Red Brick, Sybase, MS SQL Server and MS Access.T: Columns are refreshed by table name.

Default See Values above.



REVERSE_TABLE_WEIGHT<Parameter Name="REVERSE_TABLE_WEIGHT">Y</Parameter>

RIGHT_OUTER<Parameter Name="RIGHT_OUTER">$(+)</Parameter><Parameter Name="RIGHT_OUTER">*$</Parameter>

Description Specifies in which order tables should be generated. This parameter is used with Oracle. This parameter can also be used with some other databases, possibly with the Y and N reversed.This parameter is not supported for Teradata.

Values N: Specifies that tables are generated from the largest to the smallest. Y: Specifies that tables are generated from the smallest to the largest.

Default Y

Description Specifies the right outer join syntax.Values $(+): Used with Oracle. $ represents a join expression.

*$: Used with Sybase, MS SQL Server and Red Brick. $ represents a join expression.



RISQL_FUNCTIONS
<Parameter Name="RISQL_FUNCTIONS">RANK,SUM,AVG,COUNT,MIN,MAX</Parameter>

SHORTCUTS_4XThis parameter is not in any <driver>.prm file by default. To use it, you must enter it in the [Parameters] section under DBParameters and Configuration in the XML file.

Description Lists the RISQL functions supported by the database.Values Oracle default list:

RANK,SUM,AVG,COUNT,MIN,MAX,RATIO_TO_REPORT,CUME_DIST,CORR,DENSE_RANK,FIRST_VALUE,LAST_VALUE,LAG,LEAD,NTILE,PERCENT_RANK,PERCENTILE_CONT,PERCENTILE_DISC,ROW_NUMBER,CORR,COVAR_POP,COVAR_SAMP,REGR_SLOPE,REGR_INTERCEPT,REGR_COUNT,REGR_R2,REGR_AVGX,REGR_AVGY,REGR_SXX,REGR_SYY,REGR_SXY,STDDEV,STDDEV_POP,STDDEV_SAMP,VAR_POP,VAR_SAMP,VARIANCEIBM DB2 default list:RANK,SUM,AVG,COUNT,MIN,MAXTeradata default list:CSUM,MAVG,MDIFF,MLINREG,MSUM,RANK,QUANTILERed Brick default list:CUME,MOVINGAVG,MOVINGSUM,RANK,RATIOTOREPORT,TERTILE.


Description Specifies whether shortcuts are applied according to the number of paths they resolve.

Values N: Specifies that the shortcut join that resolves the most number of paths is applied first.O: Specifies that the shortcut joins are not applied in a set order.

Default No default value.



SORT_BY_NO<Parameter Name="SORT_BY_NO">NO</Parameter>

TABLE_DELIMITER<Parameter Name="TABLE_DELIMITER">"</Parameter>

Description Specifies if users are authorized to sort on columns (represented as objects in the universe) that are not included in the SELECT statement.

Values YES: Specifies that users are not authorized to sort on columns if they are not included in the SELECT statement. When the parameter is set to YES, the Manage Sorts button is dimmed in the Query Panel.NO: Specifies that you are authorized to sort on columns even if they are not included in the SELECT statement.

Default NO

Description Specifies that table or column names that contain spaces or special characters are enclosed within quotation marks if the parameter BACK_QUOTE_SUPPORTED is activated.To use this parameter, BACK_QUOTE_SUPPORTED must be set to BACK_QUOTE_SUPPORTED=Y. This is the default value.

Values “: Table or column names that contain spaces or special characters are enclosed in double quotation marks.‘: Table or column names that contain spaces or special characters are enclosed in single quotation marks. This value can be used only with Microsoft Access.

Default ”Result Table name=“My Table”


UNICODE_PATTERN
<Parameter Name="UNICODE_PATTERN">UNISTR($)</Parameter>

UNION<Parameter Name="UNION">UNION</Parameter>

Description SQL Server and Oracle only.Only applies when the universe SQL generation parameter UNICODE_STRINGS is set to Y. All conditions based on strings are then formatted with this string value.See the section “UNICODE_STRINGS” on page 58 for information on setting the UNICODE_STRINGS parameter

Default Microsoft SQL Server: N$Oracle: UNISTR($)

Example UNICODE_PATTERN=N* COL1 = N'abcd'

Description Specifies if the database supports the UNION set operator.Values UNION: The database supports the UNION set operator.

no value: The database does not support the UNION set operator. In such a case, two queries are generated.

Default UNION



USER_INPUT_DATE_FORMAT<Parameter Name="USER_INPUT_DATE_FORMAT">'dd-MM-yyyy

HH:mm:ss'</Parameter>

USER_INPUT_NUMERIC_SEPARATOR<Parameter Name="USER_INPUT_NUMERIC_SEPARATOR">.</Parameter>

Description Specifies the default date and hour formats generated in the WHERE clause of a SQL script.

Values {\d 'yyyy-mm-dd'} This is the default date format with ODBC.'DD-MM-YYYY HH:MM:SS' This is the default date and hour formats with Oracle.'YYYY-MM-DD HH:MM:SS' This is the default date and hour formats with Informix.'yyyy-mm-dd HH:mm:ss' This is the default date and hour formats with MS SQL Server and for most IBM DB2 servers.'mm/dd/yyyy hh:m:s am/pm' This is the default date and hour formats with Sybase.'yyyy-mm-dd' This is the default date format with a Sybase gateway.Note: f you need to use time or timestamp variables with ODBC, you must replace the default date format value with: {\t 'hh:mm:ss'} or {\t\s 'yyyy-mm-dd hh:mm:ss'} in the odbc.sbo file.


Description Specifies the default decimal separator stored on the server. Values '.'Default '.'


Setting data access driver configuration parameters (SBO)

chapter

Setting data access driver configuration parameters (SBO)Overview7
Overview
This chapter provides a reference for all default configuration parameters in <driver>.sbo files for the supported RDBMS. It also describes how a data access driver configuration file is structured, and how you edit the SBO file to set new values for the driver parameters.

Data access driver configuration parametersA data access driver configuration file (<driver>.sbo) is a file in XML format that contains default configuration parameters for a specific data access driver. There is a configuration file for each data access driver.Note: Driver defaults in SBO over-ride same driver defaults in CFG fileCertain parameters that appear in the Driver Defaults section of the cs.cfg file also appear in the Driver Configuration defaults section of the SBO file. The values in the CFG file apply to all drivers loaded by Connection Server. The value set in a <driver>.sbo file over rides that set in the CFG file. This allows you to set driver defaults that apply to an individual data access driver, while leaving a default setting to apply to other drivers.A data access configuration file (SBO) contains the following:• Default parameters that apply to all connections using the data access

driver.• A list of database middleware that the driver supports. This list can also

contain aliases for middleware versions that are no longer supported, but which are still used by connections defined with the data access driver. Each middleware entry can have default parameters set that apply only to that database middleware.

Data access configuration files (<driver>.sbo) are found in the directory

The following <driver>.sbo files are available

\\<INSTALDIR>\dataAccess\RDBMS\connectionServer\<rdbms>\

Data access driver SBO file

IBM DB2 db2.sboiseries.sbo

Informix informix.sboMicrosoft SQL ServerMicrosoft Analysis Services

odbc.sbooledb_olap.sbo


Setting data access driver configuration parameters (SBO)Data access driver configuration parameters 7

Note: The <driver>.sbo files are in XML format. You should use a XML editor to view and modify values in these files if necessary.

MySQL odbc.sboODBC odbc.sboOracle oracle.sboRed Brick odbc.sboSAP sap.sboSybase sybase.sboTeradata teradata.sbo

Data access driver SBO file


Setting data access driver configuration parameters (SBO)Data access driver configuration parameters7
Configuration file structure
There is a <driver>.sbo file for each supported RDBMS. Each <driver>.sbo file is divided into the following sections:


Defaults Default configuration parameters that apply to all database middleware using the data access driver. These are listed in the section Database described below. Certain of these parameters can also be set for a specific middleware. In this case, the value set for the target middleware in the Databases section, over rides the default value set in the Defaults section.

Databases Database middleware supported by the data access driver. Each middleware is listed with the following parameters:• Active Specifies if middleware support is activated

or not. Values are YES or NO.• 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 used by existing connections. You can add an alias parameter for an older middleware using the current data access driver, and set configuration parameters specific to the middleware as parameters of the new alias. Connections can still be created and used using the alias specified.

• Parameters Configuration parameters with values that apply specifically to a middleware. Values set for parameters listed here, over ride the values set for the same parameters in the Defaults section.


Setting data access driver configuration parameters (SBO)SBO file reference 7

Viewing and editing a SBO fileYou can view and edit parameters in a SBO file as follows:1. Browse to the directory that stores the SBO file for your target data

access driver. SBO files are stored here:<INSTALDIR>\dataAccess\RDBMS\connectionServer\<rdbms>\<driver>.sbo.

2. Open a <driver>.sbo file in a XML editor.3. Expand sections as required.4. Set values by entering the value in the appropriate tag.5. Save and close the file.

SBO file referenceThe SBO parameters are listed alphabetically. To view parameters available, open a <driver>.sbo file in a XML editor. 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 attributed to the parameter.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

Parameters can be set at the Defaults or Databases sectionConfiguration parameters can be set in the Defaults or Databases sections of the SBO file. If they are set for a specific middleware in the Databases section, then for the specific middleware, this value over rides the value set at the Defaults level.Note: Certain configuration parameters must not be edited. These parameters have values set for use internally within a Business Objects product. These parameters are described in this section but contain a warning not to edit the value. You must not edit these parameters. Before editing any other SBO file parameter, you should make a backup copy of the SBO file.


Setting data access driver configuration parameters (SBO)SBO file reference7
Array Bind Available
<Parameter Name="Array Bind Available">True</Parameter>

Array Bind Size<Parameter Name="Array Bind Size">5</Parameter>

Array Fetch Size<Parameter Name="Array Fetch Size">10</Parameter>

Array Fetch Available<Parameter Name="Array Fetch Available">True</Parameter>

Description Specifies if the database supports this methodValues True

FalseDefault True

Description Specifies the number of rows exported with each INSERT command. Used with all Data Access drivers, default value set in cs.cfg

Values n Specifies that n number of rows are exported with each INSERT.

Default Value set in the cs.cfg file.

Description Specifies the number of rows retrieved with each fetch. Used with all data access drivers and is available in all <driver>.sbo files.

Values n Specifies that n number of rows are retrieved with each fetch.1 Specifies that the array fetch is deactivated.

Default Value set in the cs.cfg file.

Description Specifies whether the Array Fetch is natively supported.Values True

FalseDefault Value set in the cs.cfg file.



Binary Slice Size<Parameter Name="Binary Slice Size">32000</Parameter>

CharSet Table<Parameter Name="CharSet Table">oracle</Parameter>

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

Description Specifies the number of bytes of the slice that stores the document in OBJ_X_DOCUMENTS. This is the only table in the document domain. It stores the binary content of all documents sent to the repository. These are stored as BLOBs (Binary Large OBjects) stored in slices.

Values n Specifies that slice size is n bytes.Default Oracle, Sybase: 32000

Informix: 30000Teradata: 20480

Description Specifies the Charset file (.CRS) for the data access driver with no extension. This setting is specific to each .sbo file. The CRS file is in the same directory as the SBO file.

Values oracle, db2, sybase, iseries, informix, odbc, teradata.Default Data Access driver dependant. See the Values above.

Description Informix only. Specifies if the database supports the DescribeParam method for Stored Procedures. This parameter is set in the Parameter section of the Databases section of the SBO file.

Values TrueFalse

Default False.


Description File
Do not edit this parameter<Parameter Name="Description File">oracle</Parameter>

DriverLevel<Parameter Name="Driver Level">31</Parameter>l

Description Do not edit this parameter. It specifies the name of the description file (<driver><language>.cod) with no extension. This file contains the labels of the connection dialog boxes used in the new connection wizard. The COD file is in the same directory as the SBO file.

Values oracle, db2, sybase, iseries, informix, odbc, teradataDefault See Values above.

Description Specifies the limitations of each driver. This setting is specific to each <driver>.sbo file

Values 1 Specifies that you can only use the driver to create and execute queries.13 Specifies that you can use the driver to create and execute queries, and to create universe and security domains.15 Specifies that you can use the driver to create the Business Objects repository, and to create and execute queries.31 Specifies that you can use the driver to create the Business Objects repository, to create and execute queries, and to access stored procedures.

Default Default values are data access driver specific.



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

FamilyDo not edit this parameter<Parameter Name="Family">Sybase</Parameter>

Description Specifies that certain functions, for example SQL Tables, receive an empty string or a null pointer to replace missing parameters. Used by all ODBC data access drivers.

Values EmptyString Missing parameters are replaced by an empty string.no value Missing parameters are not replaced.

Default EmptyString

Description Specifies the family of database engine to be displayed in the Database Middleware Selection page of the new connection wizard. The middleware that correspond to your data access key are displayed on this page in a tree view.

Values Oracle for OracleIBM for IBM databasesInformix for InformixSybase for SybaseGeneric for ODBC, ODBC3, and Red Brick ODBCNCR for TeradataMicrosoft for Microsoft SQL Server and Access.Microsoft is set in the MS SQL Server section of the Databases section.



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

ODBC Cursors<Parameter Name="ODBC Cursors">No</Parameter>

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

Description Specifies the availability of native Int64 support. Parameter is available to all ODBC3 and Red Brick data access drivers. The parameter is available for a specified data access driver in the Database section of the SBO file.

Values False Specifies that Int64 is not supported, so Connection Server can emulate Int64 methods.True Specifies that native Int64 support is available so integers are coded in 64 bits.

Default False

Description Specifies availability of the cursor library by the data access driver. Applies to all ODBC drivers. This parameter is not listed by default in the SBO file. The default value is set in the cs.cfg file. You can add it to the SBO file. The value set in the SBO file over rides the value in the cs.cfg file. Do not change the default value in the CFG file.

Values NoYes

Default Set in the cs.cfg file.

Description Specifies the optimization method for executing queries. Values True Specifies that SQLExecDirect is performed.

False SQLPrepare+SQLExecute is performed.Default False



Password_Encryption<Parameter Name="Password Encryption">True</Parameter>

Provider CLSIDDo not edit this parameter<Parameter Name="Provider CLSID">MSDASQL</Parameter>

Description Specifies whether or not to use the encryption password mechanism of the middleware for the password entered in the connection dialog box. This parameter is used with Sybase.

Values 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.

Default True

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

ValuesDefault MSDASQL


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

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

Description SQL Parameter File indicates the name of the file which stores database parameters. The extension of this file is .prm. You must ensure this file is located in the same directory as the data configuration file (.sbo file).

Values db2iseries for IBM DB2 iSeriesdb2udb for IBM DB2informix for Informixsqlsrv for Microsoft SQL Server 2000oracle for Oracleredbrick for Red Bricksybase for Sybaseasiq for Sybase ASIQteradata for Teradata


Description Specifies the name with no extension of the Strategy file (.STG). This is the file that contains external strategies that are available for Designer for automatic universe creation. Strategy files are stored in the same directory as the SBO file.

Values db2 for DB2 data access drivers.informix for Informxioracle for Oraclesybase for Sybaseteradata for Teradata




Transaction Available<Parameter Name="Transactional Available">Yes</Parameter>

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

Description Specifies if operations run against the database occur within transactions. 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. You would do this if your data access driver does not support transaction mode. In this case, Business Objects tries to set off the autocommit which is not supported by certain drivers. See the note below concerning the value No for this parameter.

Values Yes Specifies that operations against the database are run as transactions and are run as a block when committed.No Specifies that each SQL statement is immediately committedNote: When you set the value to No, ensure that you use this driver to access the data account only. Do not use this driver (with Transactional Available=No) to access the Business Objects repository.

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

Description 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 UTF8CharSetUCS2

Default This is set in the cs.cfg file.


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

Description Specifies the conversion rule from Informix Connect to Informix ODBC. Found in Informix.sbo. The value of this parameter determines which Informix Driver is used to define the ODBC DSN less connection string.The value of this parameter must be the exact name of the Informix driver installed on the machine.

Values {Informix 3.34 32 BIT}Default This is set in the cs.cfg file.


Setting data access general configuration parameters (CFG)

chapter

Setting data access general configuration parameters (CFG)Overview8
Overview
This chapter provides a reference for all default parameters in the Settings section of the data access general configuration file cs.cfg. It also describes how the CFG file is structured, and how you can edit the file to set new values for the parameters listed.

Data access general configuration file parameters

A data access general configuration file (cs.cfg) is a file in XML format that contains default configuration parameters that apply to all data access drivers. Certain default parameters can be over-ridden for a specific data access driver when the same parameter is set to a different value in the data access configuring file (<driver>.sbo).The cs.cfg file is an XML file. The DTD for the file is in the same directory. Make sure that, when modifying the XML file, the cs.dtd is accessible. The CFG file is stored here:

General configuration file structureThere are five sections in the cs.cfg XML file:• Locales• Settings• Distribution• Driver defaults• TracesEach of these sections is described briefly as follows:

LocalesDefines the usual operating system charset for a given language. BusinessObjects recommends that you do not modify these values.Example:<Locales><Locale><Language>en</Language>

$INSTALLDIR \dataAccess\RDBMS\connectionServer


Setting data access general configuration parameters (CFG)Data access general configuration file parameters 8

<CharSet Platform="MSWindows">CP1252</CharSet><CharSet Platform="Unix">ISO-8859-1</CharSet></Locale>

</Locales>

SettingsDefines parameters that apply to all data access drivers, for example the file extensions of files used by Connection Server to manage data access.Some settings can be defined either for library version or server version of Connection Server.These parameters are described in the section “Settings reference” on page 103.

DistributionParameters used when Connection Server is used in a stand alone server mode. See the following guides for information on installing and using Connection Server as a stand alone server:• Deploying the Business Objects System• Installation and Configuration GuideThis section contains the following sub sections:• Protocols• Lookup

ProtocolsContains the default values that Connection Server uses to process requests coming from CORBA clients or HTTP clients, for example;

<Protocol Name="HTTP" Active="No"/>

LookupAllows you to expose the target RDBMS that corresponds to a specific ODBC network layer to Connection Server. This applies when Connection Server is using two different data access drivers, for example Microsoft SQL Server and Microsoft Access, through a common ODBC network layer.By default, Lookup is set to No, so Connection Server associates a target RDBMS to its ODBC network layer. However, if Connection Server is being used as a standalone server, and has two databases that are accessed by the same ODBC network layer, the specific target database information must be exposed to Connection Server to allow queries to be routed through ODBC to the correct data account.


Setting data access general configuration parameters (CFG)Data access general configuration file parameters8

You expose the database information to Connection Server by setting the Lookup parameter to Yes. This ensures that the target database information for queries using ODBC are routed to the correct server, for example ODBC_SQLSERVER and ODBC_ACCESS.You only set Lookup to Yes when Connection Server is being used as a stand alone server, dedicated to connection activity only.Example: Lookup set to Yes to expose two RDBMS using the same ODBC network layer<LookUp Active="Yes"> <NetworkLayer Name="ODBC"> <Property Name="ODBC_SQLServer"> <DataBase Pattern="^MS SQL Server.*$"/> </Property> <Property Name="ODBC_Access"> <DataBase Pattern="^MS Access.*$"/> </Property>

Driver defaultsData access driver parameters that are also found in driver specific SBO files. The values set for these parameters apply to all data access drivers, but these can be set in a <driver>.sbo file to over-ride the default value.See the chapter “Setting data access driver configuration parameters (SBO)” on page 85 for information in the parameters listed here.

TracesYou 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.

Viewing and editing a cs.cfg fileYou can view and edit parameters in a CFG file as follows:1. Browse to the directory that stores the cs.cfg file:

$INSTALLDIR\dataAccess\RDBMS\connectionServer\cs.cfg2. Open the cs.cfg file in a XML editor.3. Expand sections as required.4. Set values by entering the value in the appropriate tag.5. Save and close the file.


Setting data access general configuration parameters (CFG)Settings reference 8

Settings referenceThe CFG setting parameters are listed alphabetically. To view parameters available, open the CFG file in a 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 attributed to the parameter.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

Parameters can be set at the library or server levelCFG settings parameters can be set at the following data access levels:

Setting set at this level

Description

library Parameters apply to a standard Connection Server installation. It is installed with other BusinessObjects desktop or server software. This is the default mode when you install a BusinessObjects product that is installed automatically with Connection Server.

server Parameters apply to a Connection Server stand alone server installation. It is installed alone with no other BusinessObjects desktop or server product. You select this mode in the choice of custom installation options when you install BusinessObjects 6.5. You set parameters in this mode when you are using a node for connection management only.


Setting data access general configuration parameters (CFG)Settings reference8
Load Drivers On Startup
Load Drivers On startup is available in library and server mode.

Library<Settings><Parameter Name="Load Drivers On Startup">No</Parameter>...<Library/>

Server<Server><Parameter Name="Load Drivers On Startup">Yes</Parameter>...</Server>

Both are described below.

Enable Failed Load<Parameter Name="Enable Failed Load">Yes</Parameter>

Description Determines loading of driver libraries. You can set values for Load Drivers On Startup 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.

Values Yes All installed drivers are loaded during the initialization phase.No Drivers are loaded on demand.

Default Library mode: NoServer mode: Yes

Description Determines the level of error incurred by a failed driver load.Values Yes If a driver fails to load, it is not a fatal error.

No If a driver fails to load, it is a fatal error.Default Yes


Setting data access general configuration parameters (CFG)Settings reference 8

Config File Extension<Parameter Name="Config File Extension">sbo</Parameter>

SQL Parameter Extension<Parameter Name="SQL Parameter Extension">prm</Parameter>

SQL External Extension<Parameter Name="SQL External Extension">rss</Parameter>

Strategies Extension<Parameter Name="Strategies Extension">stg</Parameter>

Description Extension<Parameter Name="Description Extension">cod</Parameter>

Description Determines the file extension for general configuration files.Default sbo

Description Determines the file extension for SQL parameter files.Default prm

Description Determines the file extension for external SQL files.Default rss

Description Determines the extension for strategy files.Default stg

Description Determines the extension for the connection description files.Values Do not change the default value for this parameter.Default cod


Setting data access general configuration parameters (CFG)Settings reference8
Charset List Extension
<Parameter Name="CharSet List Extension">crs</Parameter>

Max Pool TimeMax Pool Time is available in library and server mode.

Library<Settings><Parameter Name="Max Pool Time">-1</Parameter>...<Library/>

Server<Server><Parameter Name="Max Pool Time">60</Parameter>...</Server><Parameter Name="Max Pool Time">-1</Parameter>

Description Determines the extension for character set files.Default crs

Description 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 Library mode: -1Server: 60

Values -1 No timeout, keep alive for the whole session.0 Connection not managed by the pool.>0 Idle lifetime (in minutes).


Business Objectsinformation resources

appendix

Business Objects information resourcesDocumentation and information servicesA
Documentation and information services
Business Objects offers a full documentation set covering its products and their deployment. Additional support and services are also available to help maximize the return on your business intelligence investment. The following sections detail where to get Business Objects documentation and how to use the resources at Business Objects to meet your needs for technical support, education, and consulting.

DocumentationYou can find answers to your questions on how to install, configure, deploy, and use Business Objects products from the documentation.

What’s in the documentation set?View or download the Business Objects Documentation Roadmap, available with the product documentation at http://www.businessobjects.com/support/.The Documentation Roadmap references all Business Objects guides and lets you see at a glance what information is available, from where, and in what format.

Where is the documentation?You can access electronic documentation at any time from the product interface, the web, or from your product CD.

Documentation from the productsOnline help and guides in Adobe PDF format are available from the product Help menus. Where only online help is provided, the online help file contains the entire contents of the PDF version of the guide.

Documentation on the webThe full electronic documentation set is available to customers on the web from support website at: http://www.businessobjects.com/support/.

Documentation on the product CDLook in the docs directory of your product CD for versions of guides in Adobe PDF format.


http://www.businessobjects.com/support/


http://www.businessbojects.com/support/

http://www.businessbojects.com/support/

Business Objects information resourcesCustomer support, consulting and training A

Send us your feedbackDo you have a suggestion on how we can improve our documentation? Is there something you particularly like or have found useful? Drop us a line, and we will do our best to ensure that your suggestion is included in the next release of our documentation: [email protected]: If your issue concerns a Business Objects product and not the documentation, please contact our Customer Support experts. For information about Customer Support visit: http://www.businessobjects.com/support.

Customer support, consulting and trainingA global network of Business Objects technology experts provides customer support, education, and consulting to ensure maximum business intelligence benefit to your business.

How can we support you?Business Objects offers customer support plans to best suit the size and requirements of your deployment. We operate customer support centers in the following countries:• USA• Australia• Canada• United Kingdom• Japan

Online Customer SupportThe Business Objects Customer Support website contains information about Customer Support programs and services. It also has links to a wide range of technical information including knowledgebase articles, downloads, and support forums.http://www.businessobjects.com/support/



mailto:[email protected]


http://businessobjects.com/support/

http://businessobjects.com/support/

Business Objects information resourcesUseful addresses at a glanceA
Looking for the best deployment solution for your company?
Business Objects consultants can accompany you from the initial analysis stage to the delivery of your deployment project. Expertise is available in relational and multidimensional databases, in connectivities, database design tools, customized embedding technology, and more.For more information, contact your local sales office, or contact us at:http://www.businessobjects.com/services/consulting/

Looking for training options?From traditional classroom learning to targeted e-learning seminars, we can offer a training package to suit your learning needs and preferred learning style. Find more information on the Business Objects Education website:http://www.businessobjects.com/services/training

Useful addresses at a glance

Address Content

Business Objects product informationhttp://www.businessobjects.com

Information about the full range of Business Objects products.

Product documentationhttp://www.businessobjects.com/support

Business Objects product documentation, including the Business Objects Documentation Roadmap.

Business Objects Documentation [email protected]

Send us feedback or questions about documentation.

Online Customer Supporthttp://www.businessobjects.com/support/

Information on Customer Support programs, as well as links to technical articles, downloads, and online forums.


www.businessobjects.com/services/consulting.htm

www.businessobjects.com/services/education.htm

http://www.businessobjects.com

http://www.businessobjects.com/support

http://www.businessobjects.com/support




http://www.businessobjects.com/forms/tipsandtricks_login.asp

http://www.techsupport.businessobjects.com/

http://www.techsupport.businessobjects.com/

www.businessobjects.com/services/consulting.htm

www.businessobjects.com/services/education.htm

Business Objects information resourcesUseful addresses at a glance A

Business Objects Consulting Serviceshttp://www.businessobjects.com/services/consulting/

Information on how Business Objects can help maximize your business intelligence investment.

Business Objects Education Serviceshttp://www.businessobjects.com/services/training

Information on Business Objects training options and modules.

Address Content


http://businessobjects.com/services/consulting/

http://businessobjects.com/services/consulting/

http://www.businessobjects.com/services/training

http://www.businessobjects.com/services/training

Business Objects information resourcesUseful addresses at a glanceA


Index

Symbols$INSTALLDIR

use of variable in guide 6

Aaccess

Connection Server 7New Connection wizard 17

advanced parametersconnection 24

ANSI92universe parameter 47, 55

Array Bind Available 90Array Bind Size 90, 90Array Fetch Available 90Array Fetch Size 90AUTO_UPDATE_QUERY

universe parameter 47, 47

BBACK_QUOTE_SUPPORTED 65Bca_Bursting_Size 91BeginTranSyntax 91Binary Slice Size 91BLOB_COMPARISON

universe parameter 47Blob_Comparison 65BOUNDARY_WEIGHT_TABLE

universe parameter 48Boundary_Weight_Table 65, 65Business Objects

consulting services 110, 111support services 109training services 110, 111

CCASE_SENSITIVE 65

Case_Sensitive 65CFG file

Charset List Extension 106Config File Extension 105Description Extension 105Distribution section 101Driver Default parameters 100Driver defaults section 102editing 102Enable Failed Load 104Load Drivers On Startup 104Locales parameters 100Locales section 100Lookup parameters 101Max Pool Time 106Protocols parameters 101settings parameters reference 103Settings section 101SQL External Extension 105SQL Parameter Extension 105Strategies Extension 105structure 100Traces section 102viewing 102

cfg filedescription 33

Charset List ExtensionCFG file 106

CharSet Table 91CHECK_OWNER_STATE 65CHECK_QUALIFIER_STATE 66COLUMNS_SORT

universe parameter 48COMBINED_WITH_SYNCHRO

universe parameter 49COMBINED_WITHOUT_PARENTHESIS

universe parameter 49COMMA 66CONCAT 66


Index

Config File ExtensionCFG file 105

connectionadvanced parameters 24create new 17creating 16creation pre-requisites 9creation process overview 9custom parameters 26definition 8management tools 8parameters overview 16personal 23secured 23shared 23start creating 17using wizard 17utilities 8

Connection Serveraccessing 7creating connections 7definition 7management tools 8optimize data access 7

consultants, Business Objects 110CORE_ORDER_PRIORITY

universe parameter 50CORRECT_AGGREGATED_CONDITIONS_IF_D

RILLuniverse parameter 50

Cost Estimate Unit 68create

connection overview 9connection pre-requisites 9connection with wizard 16new connection 17

CUMULATIVE_OBJECT_WHEREuniverse parameter 51

Cumulative_Object_Where 68, 92CursorForward 68custom parameter

Hint 26custom parameters

connection 26customer support 109

Ddata access

driver level files 12files installed 10global files 11installed files 10optimizing 7

data access driverdefinition 10files used by 12

DATABASE_DATE_FORMAT 67datatype

IBM DB2 38Informix 39Oracle 40Red Brick 41SQL Server 39Sybase 42Teradata 43

DATATYPE_BLOB 67DATATYPE_DOUBLE 67DATATYPE_DTM 67DATATYPE_INT 68DATATYPE_NULL 68DATATYPE_STRING 68DATE_WITHOUT_QUOTE 68Date_Without_Quote 68DECIMAL_COMMA

universe parameter 51define

new connection 17Describe Param Available 91DescribeParam Available 91Description Extension

CFG file 105Description File 92DISTINCT_VALUES

universe parameter 52Distribution

CFG file 101documentation

feedback on 109on product CD 108on the web 108roadmap 108


Index

Driver defaultsCFG file 102

driveren.prm parametersBlob_Comparison 65Boundary_Weight_Table 65, 65Case_Sensitive 65Cumulative_Object_Where 68, 92Ext_Join 69Intersect 71Left_Outer 72Max_Inlist_Values 73Minus 73OLAP_Clause 74Quote_Owner 78Refresh_Columns_Type 78

DriverLevel 92driver.sbo parameters

Bca_Bursting_Size 91BeginTranSyntax 91Cost Estimate Unit 68CursorForward 68Date_Without_Quote 68Lock Mode 94LongVarcharNotSupported 94Shared 80Shortcuts_4x 80

dynamicSQL parameters 31

dynamic SQLsetting parameters in universe 30

Eedit

dynamic SQL parameters 31editing 32editing data access files 32education. See trainingEmpty String 93, 93Enable Failed Load

CFG file 104END_SQL

universe parameter 52EVAL_WITHOUT_PARENTHESIS

universe parameter 53EXT_JOIN 69

Ext_Join 69EXT_JOIN_INVERT 69EXTERN_SORT_EXCLUDE_DISTINCT 69

FFamily 93feedback, on documentation 109FILTER_IN_FROM

universe parameter 53, 58FIRST_LOCAL_CLASS_PRIORITY

universe parameter 54FORCE_SORTED_LOV

universe parameter 54

Gglobal

data access files 11GROUPBY_EXCLUDE_COMPLEX 70GROUPBY_WITH_ALIAS 70GROUPBY_WITHOUT_CONSTANT 71GROUPBYCOL 71

HHints

custom parameter Oracle 26

IIBM DB2

datatypes 38information resources 108Informix

datatypes 39installation

files installed 10INTERSECT 71Intersect 71

KKEY_INFO_SUPPORTED 72

LLEFT_OUTER 72


Index

Left_Outer 72LENMAXFORCOLUMNNAME 72LENMAXFORTABLENAME 73LENMAXFORVARCHAR 73Load Drivers On Startup

CFG file 104Locales

CFG file 100Lock Mode 94login

setting parameters 22login parameters 22LongVarcharNotSupported 94Lookup

CFG file 101

MMax Pool Time

CFG file 106MAX_INLIST_VALUES

universe parameter 54Max_Inlist_Values 73MINUS 73Minus 73

NNative Int64 Available 94new connection

create 17New Connection wizard 16

accessing 17using 17

niverse parameterPATH_FINDER_OFF 55

NO_DISTINCT 74

OODBC Cursors 94OLAP_CLAUSE 74OLAP_Clause 74Online Customer Support 109optimize

data access 7, 32Optimize Execute 94

Oracledatatypes 40

OUTERJOINS_GENERATION 75OVER_CLAUSE 76overview

connection parameters 16creating a connection 9

OWNER 76

Pparameter files 32Password_Encryption 95, 95PATH_FINDER_4x 77PATH_FINDER_OFF

universe parameter 55personal

connection 23PREFIX_SYS_TABLE 77PRM file

BACK_QUOTE_SUPPORTED 65CASE_SENSITIVE 65CHECK_OWNER_STATE 65CHECK_QUALIFIER_STATE 66COMMA 66CONCAT 66DATABASE_DATE_FORMAT 67DATATYPE_BLOB 67DATATYPE_DOUBLE 67DATATYPE_DTM 67DATATYPE_INT 68DATATYPE_NULL 68DATATYPE_STRING 68DATE_WITHOUT_QUOTE 68edit parameters 62EXT_JOIN 69EXT_JOIN_INVERT 69EXTERN_SORT_EXCLUDE_DISTINCT 69GROUPBY_EXCLUDE_COMPLEX 70GROUPBY_WITH_ALIAS 70GROUPBY_WITHOUT_CONSTANT 71GROUPBYCOL 71INTERSECT 71KEY_INFO_SUPPORTED 72LEFT_OUTER 72LENMAXFORCOLUMNNAME 72


Index

LENMAXFORTABLENAME 73LENMAXFORVARCHAR 73list of files 60MINUS 73NO_DISTINCT 74OLAP_CLAUSE 74OUTERJOINS_GENERATION 75OVER_CLAUSE 76OWNER 76PATH_FINDER_4x 77PREFIX_SYS_TABLE 77QUALIFIER 77QUOTE_OWNER 78reference 60reference list 64REFRESH_COLUMNS_TYPE 78REVERSE_TABLE_WEIGHT 79RIGHT_OUTER=$(+) 79RISQL_FUNCTIONS 80SHORTCUTS_4X 80SORT_BY_NO 81TABLE_DELIMITER 81UNICODE_PATTERN 82UNION 82USER_INPUT_DATE_FORMAT 83USER_INPUT_NUMERIC_SEPARATOR 83view parameters 62

prm filedescription 34

PRM language fileedit function Help text 63view function Help text 63

ProtocolsCFG file 101

Provider CLSID 95

QQUALIFIER 77QUOTE_OWNER 78Quote_Owner 78

RRed Brick

datatypes 41

TIME support in WHERE 41TIMESTAMP support in WHERE 41

REFRESH_COLUMNS_TYPE 78Refresh_Columns_Type 78REPLACE_COMMA_BY_CONCAT

universe parameter 55, 55resources 108REVERSE_TABLE_WEIGHT 79RIGHT_OUTER=$(+) 79RISQL_FUNCTIONS 80

SSBO file 90, 93

Array Bind Available 90Array Fetch Available 90Array Fetch Size 90Binary Slice Size 91CharSet Table 91Databases parameters 88Defaults parameters 88Describe Param Available 91DescribeParam Available 91Description File 92DriverLevel 92editing 89Family 93Native Int64 Available 94ODBC Cursors 94Optimize Execute 94Password_Encryption 95, 95Provider CLSID 95SQL Parameter File 96Strategies File 96structure 88Transaction Available 97Unicode 97V5toV6DriverName 98viewing 89

sbo filedescription 34

securedconnection 23

settinglogin parameters 22

Settings


Index

CFG file 101settings

CFG parameter reference 103Shared 80shared

connection 23SHORTCUT_BEHAVIOR

universe parameter 56SHORTCUTS_4X 80Shortcuts_4x 80SORT_BY_NO 81SQL External Extension

CGF file 105SQL parameter

PRM file list 64SQL Parameter Extension

CFG file 105SQL Parameter File 96SQL parameters

setting in universe 30SQL Server

datatypes 39start

creating a connection 17Strategies Extension

CFG file 105Strategies File 96support

customer 109locations 109technical 109web site 109

Sybasedatatypes 42

TTABLE_DELIMITER 81technical support 109Teradata

datatypes 43THOROUGH_PARSE

universe parameter 56, 56traces

CFG file 102training, on Business Objects products 110

Transaction Available 97TRUST_CARDINALITIES

universe parameter 57

UUnicode

SBO file 97UNICODE_PATTERN 82UNICODE_STRINGS

universe parameter 58UNION 82universe

setting SQL parameters 30universe parameter

AUTO_UPDATE_QUERY 47BLOB_COMPARISON 47BOUNDARY_WEIGHT_TABLE 48COLUMNS_SORT 48COMBINED_WITH_SYNCHRO 49COMBINED_WITHOUT_PARENTHESIS 49CORE_ORDER_PRIORITY 50CORRECT_AGGREGATED_CONDITIONS_I

F_DRILL 50CUMULATIVE_OBJECT_WHERE 51DECIMAL_COMMA 51DISTINCT_VALUES 52END_SQL 52EVAL_WITHOUT_PARENTHESIS 53FILTER_IN_FROM 53FIRST_LOCAL_CLASS_PRIORITY 54FORCE_SORTED_LOV 54MAX_INLIST_VALUES 54REPLACE_COMMA_BY_CONCAT 55SHORTCUT_BEHAVIOR 56THOROUGH_PARSE 56TRUST_CARDINALITIES 57UNICODE_STRINGS 58

USER_INPUT_DATE_FORMAT 83USER_INPUT_NUMERIC_SEPARATOR 83

VV5toV6DriverName 98


Index

Wweb

customer support 109getting documentation via 108useful addresses 110

web sitessupport 109training 110


Index
