A Supporting Tasks for DAC and Informatica PowerCenter

This section contains additional tasks relating to the DAC Client and DAC Server, and Informatica PowerCenter Client Tools, and contains the following topics:

How to Log into DAC
How to Set DAC System Properties
How to Start and Stop the DAC Server
How to Log Into Informatica PowerCenter Administration Console
How to Start and Stop Informatica Services
How to Stop and Start the Informatica Repository Service (Optional)
About the DAC Server Scripts for Bash and C Shells

How to Log into DAC

Before you can log into the DAC, you need to have created a DAC connection, which is a set of stored login details. For information about creating a DAC connection, see Logging into DAC.

To log into DAC

Launch the DAC Client by double-clicking the DAC Client icon on your desktop or navigating to the OracleBI\DAC directory and double-clicking the startclient.bat file.

The Login... dialog is displayed.
In the Login... dialog, select a connection for the required DAC Repository from the Connection drop down list.
In the Table owner name field, enter the database user name for the DAC Repository database.
In the Password field, enter the database password for the DAC Repository database.
Click Login to launch the DAC.

The DAC Client launches and connects to the DAC Repository.

For more information about using the DAC Client, see Oracle Business Intelligence Data Warehouse Administration Console Guide.

How to Set DAC System Properties

This section provides instructions for setting the DAC System Properties.

To set DAC System Properties

Log in to DAC.

For more information about logging into DAC, see How to Log into DAC.).
From the Views menu, select Setup, then DAC System Properties tab.

Set the appropriate values for the following system properties:

Property	Value Required
Analyze Frequency (in days)	For DAC metadata tables, the frequency (in days) the DAC client automatically updates the table and index statistics for the DAC repository. The value must be numerical.
Auto Restart ETL	Possible values are True and False. When set to True: An ETL that is running when the DAC server abnormally terminates will continue running when the DAC server is restarted. When set to False: An ETL that is running when the DAC server abnormally terminates will not automatically restart when the DAC server restarts. The ETL status will be updated to Failed. An administrator will have to manually restart the ETL.
DAC Alternate Server Hosts	Host name of the machine where the alternate DAC server resides. The alternate DAC server is used for failover purposes. The DAC client cannot talk to the alternate server unless the main DAC server is not running.
DAC Server Hosts	Host name of the machine where the DAC server resides. You cannot use an IP address for this property. The DAC server and a given DAC repository have a one-to-one mapping. That is, you can only run one DAC server against any given DAC repository. Thus, in the repository you must specify the network host name of the machine where the DAC sever is to be run. This property also takes the value localhost. However, this value is provided for development and testing purposes and should not be used in a production environment.
DAC Server OS	Operating system of the machine where the DAC server resides. Possible values are Windows, Solaris, HP, or AIX. If you move the DAC server from another operating system to AIX, you need to do the following: change the DAC server host to the appropriate value; restart the DAC client; reenter all the password fields for the Informatica servers and database connections; and reconfigure the DAC server on the AIX machine by running serverSetupPrompt.sh.
DAC Server Port	Network port to which the DAC server binds in order to listen to client requests. The default value is 3141. If this port has been assigned to another process, you can enter any numerical port value greater than 1024.
Drop and Create Change Capture Views	Possible values are True and False. When set to True (the default value), the DAC server drops and creates change capture views every time it performs a change capture process, including for both full and incremental loads. Setting this property to True can create system catalog lock up for DB2-UDB and DB2-390 databases. Therefore, by setting the property to False, the DAC server will drop and create views selectively, using the following rules: In full mode: During the change capture phase, views will be dropped and created as full views. During the change capture sync process, incremental views will be generated. In incremental mode: If the view exists, it will not be dropped and created. If the view does not exist, the incremental view will be created.
Dryrun	Possible values are True and False. Indicates whether tasks are executed without invoking Informatica workflows. The following processes are executed: change capture, truncation of tables, drop and creation of indexes, and analyze statements. This option should be used for debugging purposes only and not used in a production environment.
Generic Task Concurrency LImit	Determines how many tasks with execution types other than Informatica can be run concurrently. The value must be numerical. To set this value, you should consider what the external tasks do. For example, if the tasks open connections to a database, you should consider how this would affect the preconfigured tasks.
HeartBeatInterval	Frequency (in seconds) the DAC server checks on the health of the database connections. The value must be numerical. For example, a value of 300 (the default value) indicates the system will perform subsystem diagnostics and recovery procedures every 300 seconds.
InformaticaFileParameterLocation	Directory where the Informatica parameter file is stored.
Output Redirect	Indicates whether logging information and standard output and errors are redirected to files in the log directory (when property is set to True). The file containing standard output starts with out_ and ends with the .log extension. The standard error messages are in the file starting with err_ and ending with the .log extension. If this property is set to False, the logging information is directed to the machine's standard output and error files, which typically defaults to the console from which the DAC server was launched if the server was launched in a visible console mode. If the server is launched as a Windows service, the logging information is directed to the service log. If the server is launched with the command shell not visible, all logging information is deleted
Repository DB Pool Size	Indicates the maximum number of connections to the DAC repository that the server will maintain.
Scheduler.Poll.Interval	Frequency (in seconds) the DAC server polls for changes in the schedule configuration.
Script After Every ETL	The name of the script or executable to be run after every execution plan. For more information, see the description of the property Script Before Every ETL.
Script Before Every ETL	The name of the script or executable to be run before every execution plan. For example, before running an execution plan, you might want to run a process or perform certain tasks. These can be contained in a script or executable. This file should be placed in the scripts subdirectory of the DAC server. The execution plan runs only after the external process has finished. Therefore, it is important that the script or executable does not fail.
Server Log Level	Output logging level. Possible values are Finest, Finer, Fine, Config, Info, Warning, and Severe. The Severe value produces minimal log details, and Finest produces the most extensive amount of reporting.
SQL Trace	Possible values are True and False. Indicates whether the SQL statements to the DAC repository and database connections are added to the log file. Possible values are True and False. The True value sends a hint to the database connectivity layer of the DAC server to enable SQL tracing; thus, every SQL statement that is run by the DAC server is spooled to the appropriate output log file.

How to Start and Stop the DAC Server

This section explains how to start and stop the DAC Server.

To start or stop the DAC Server on Windows

If you installed the DAC Server with the Oracle BI Applications installation, choose the Windows Start menu, then Programs, then Oracle Business Intelligence, then Oracle DAC, and then Start DAC Server or Stop DAC Server.
If you installed the DAC Server by copying the \DAC\ directory to a machine, run the \DAC\startserver.bat script or \DAC\stopserver.bat script.

To start the DAC Server on UNIX and Linux

In csh-related shells, issue the following command:
```
./startserver.csh
```
On AIX, use startserver_aix.csh.
In bash-related shells, issue the following command:
```
./startserver.sh
```
On AIX, use startserver_aix.sh.

To run the DAC Server in the background

In csh-related shells, issue the following command:
```
nohup startserver.csh 2>&1 &
```
In bash-related shells, issue the following command:
```
nohup startserver.sh 2>&1 &
```
The nohup command allows the DAC Server to run in the background on UNIX even if the DAC Client is disconnected. Note: To stop DAC Server running as a background process, use stopserver.sh or stopserver.csh.

To stop the DAC Server on UNIX and Linux

In csh-related shells, issue the following command:
```
./stopserver.csh
```
In bash-related shells, issue the following command:
```
./stopserver.sh
```
Note: When you execute ./stopserver.csh or ./stopserver.sh, the server will issue a warning about the shutdown request. When the server receives the request, it will shut down even if there is an ETL in progress. The statuses for the ETL run will not be set correctly. The next time the DAC Server starts, it will set the status to Failed for any uncompleted run.

Tip:

When you start the DAC Server, look at the DAC Server status icon in the DAC console to make sure that the DAC Server has started. The DAC Server status icon should either be orange (idle) or green (active). The screen shot below shows the DAC Server status icon highlighted.

This image is an example of the populated screen.

How to Log Into Informatica PowerCenter Administration Console

Informatica PowerCenter Administration Console is installed on the machine that hosts the gateway node for the PowerCenter domain. For a single machine install of PowerCenter Services as described in this chapter, PowerCenter Administration Console is installed along with PowerCenter Services.

To log into PowerCenter Administration Console

In Microsoft Internet Explorer or Mozilla Firefox, access the following URL:

http://<gateway host>:<domain port>

Where:
- <gateway host> is the name of the machine on which the gateway node has been configured; for a single-machine installation of PowerCenter Services it is the name of the machine on which PowerCenter Services has been installed.
- <domain port> is the port number for the gateway. The default port number is 6001.
In the login page, enter the domain username and password that was specified when you created the domain during installation of PowerCenter Services.

For more information about logging into PowerCenter Administration Console, refer to the Appendix in the Informatica PowerCenter Installation and Configuration Guide.

How to Start and Stop Informatica Services

After you have installed Informatica PowerCenter, you might need to start the Informatica services manually. The Informatica services must be running to enable you to access the Oracle Business Analytics Warehouse using the Informatica tools.

On Windows

Start and stop the Informatica Services from the Start Menu > Programs > Informatica PowerCenter 8.1.1 > Services > Start Informatica Services or Stop Informatica Services.

You can also start and stop Informatica Services from the Windows Services panel.

On UNIX or Linux

Use the infaservice command to start or stop Informatica Services, as follows:
```
Infaservice [startup | shutdown]
```
The infaservice command is located in $Informatica/PowerCenter8.1.1/server/tomcat/bin.

The Informatica Services may also be stopped on a node by shutting down the node using PowerCenter Administration Console.

For more information about stopping and starting Informatica PowerCenter Services, refer to the Appendix in the Informatica PowerCenter Installation and Configuration Guide.

How to Stop and Start the Informatica Repository Service (Optional)

This section explains how to stop and start the Informatica Repository Service. For example, you might need to re-start the service after making changes to the service properties.

To stop and start the Informatica Repository

In Informatica PowerCenter Administration Console, in the Domain tab, select the Repository Service (for example, Oracle_BI_DW_Base).
Do one of the following:
- To stop the Informatica Repository Service, click Disable.
- To start the Informatica Repository Service, click Enable.

About the DAC Server Scripts for Bash and C Shells

Shell scripts are provided in the *.sh and *.csh formats. You must use the appropriate scripts in the appropriate shells, because their syntaxes are different. Table A-1 lists the available shell scripts and their usage. These files contain comments that provide information about how to configure the scripts.

Table A-1 Oracle Business Analytics Warehouse Scripts for Bash and C Shells

Script	Purpose
config.sh config.csh	Configures the environment variables for DAC_HOME and JAVA_HOME.
dacCmdLine.sh dacCmdLine.csh	Invokes DAC commands on the DAC Server. See the file for usage information.
dac_env.sh dac_env.csh	Configures environment variables for the DAC Server.
serverSetupPrompt.sh serverSetupPrompt.csh	Configures DAC metadata repository connection information.
serverinfo.sh serverinfo.csh	Displays the DAC Server version.
startserver.sh startserver.csh	Starts the DAC Server on machines running Solaris or Linux, and HP.
startserver_aix.sh startserver_aix.csh	Starts the DAC Server on AIX machines.
stopserver.sh stopserver.csh	Shuts down the DAC Server.

Note:

The files listed in Table A-1 need to have read, write, and execute permissions. If the files do not have these permissions, modify them using the chmod command.

When you copy across these files, use a MS-DOS to UNIX conversion tool, convert the script files to UNIX format (that is, remove the carriage return and line feed characters). There are many MS-DOS to UNIX conversion tools that are freely available for download on the Internet. Alternatively, you can manually remove the carriage return and line feed characters from the script files.

On some shells, export commands in dac_env.sh and config.sh might not work correctly. In this case, try breaking the commands in two. For example, change the command from:

export JAVA_HOME=/opt/java1.5

To:

JAVA_HOME=/opt/java1.5
export JAVA_HOME

Follow these procedures to start and stop the DAC Server on UNIX. Before you can start the DAC Server, you must have already configured the config.csh or config.sh and dac_env.csh or dac_env.sh files.

To start the DAC Server

In csh-related shells, issue the following command:

./startserver.csh
In bash-related shells, issue the following command:

./startserver.sh

To run the DAC Server in the background

In csh-related shells, issue the following command:

nohup startserver.csh 2>&1 &
In bash-related shells, issue the following command:

nohup startserver.sh 2>&1 &

The nohup command allows the DAC Server to run in the background on UNIX even if the DAC Client is disconnected.

To stop the DAC Server

In csh related shells, issue the following command:

./stopserver.csh
In bash-related shells, issue the following command:

./stopserver.sh

Note:
When you execute ./stopserver.csh or ./stopserver.sh, the server will issue a warning about the shutdown request. When the server receives the request, it will shut down even if there is an ETL in progress. The statuses for the ETL run will not be set correctly. The next time the DAC Server starts, it will set the status to Failed for any uncompleted run.

How to Configure the DAC Metadata Repository

This section explains how to configure the DAC metadata repository, and includes the following topics:

How to Set Up DAC System Properties
How to Activate Join Indices for Teradata Databases

How to Set Up DAC System Properties

Follow this procedure to set up the DAC system properties, which determine the behavior of the DAC Server. If you do not change a property value, Oracle BI Applications uses the property value that is automatically set during the Oracle BI Applications installation.

To set up the DAC system properties

In DAC, display the Setup view.

For more information about logging into DAC, see How to Log into DAC).
Display the DAC System Properties tab.

If required, change the property values.

Note:

Possible values for all properties have to be keyed in exactly as described in the description tab (at the bottom) of each property. For example Auto Restart has possible values of false and true (case sensitive), and Server Log Level has the following possible values SEVERE, FINEST, FINER,FINE, INFO, WARNING (case sensitive).

Property	Description
Analyze Frequency (in days)	For DAC metadata tables, the frequency (in days) the DAC Client automatically updates the table and index statistics for the DAC repository. The value must be numerical.
Auto Restart ETL	Possible values are TRUE and FALSE. When set to TRUE: An ETL that is running when the DAC Server abnormally terminates will continue running when the DAC Server is restarted. When set to FALSE: An ETL that is running when the DAC Server abnormally terminates will not automatically restart when the DAC Server restarts. The ETL status will be updated to Failed. An administrator will have to manually restart the ETL.
CreateQueryIndexesAtTheEnd	Possible values are True and False. During the ETL process, the DAC Server automatically drops and creates indices. When set to True, this property groups all indices of the Query type and creates them after the ETL is complete. The DropAndCreateIndexes property takes precedence over this property. Therefore, if the DropAndCreateIndexes property is set to False, you cannot set the property CreateQueryIndexesAtTheEnd to True to have indices of the Query type created at the end of the ETL process. Also, be aware that when this property is set to True, tables will be analyzed twice, (once at the end of the ETL indexes and once at the end of query indexes). If any indices are marked as Query type indices, and are used by ETL processes, it can adversely affect the performance of the ETL process.
DAC Alternate Server Hosts	The failover server for the DAC.
DAC Server Host	Host name of the machine where the DAC Server resides. You cannot use an IP address for this property. Note: The DAC Server and a given DAC repository have a one-to-one mapping. That is, you can only run one DAC Server against any given DAC repository. Thus, in the repository you must specify the network host name of the machine where the DAC sever is to be run. Note: This property also takes the value localhost. However, this value is provided for development and testing purposes and should not be used in a production environment.
DAC Server OS	Operating system of the machine where the DAC Server resides. Possible values are Windows, Solaris, HP, AIX, Linux. Note: If you move the DAC Server from another operating system to AIX, you need to do the following: change the DAC Server host to the appropriate value; restart the DAC Client; re-enter all the password fields for the Informatica PowerCenter Integration Services and database connections; and reconfigure the DAC Server on the AIX machine by running serverSetupPrompt.sh.
DAC Server Port	Network port to which the DAC Server binds in order to listen to client requests. The default value is 3141. If this port has been assigned to another process, you can enter any numerical port value greater than 1024.
Drop and Create Change Capture Views Always	Possible values are True and False. When set to True (the default value), the DAC Server drops and creates change capture views every time it performs a change capture process, including for both full and incremental loads. Setting this property to True can create system catalog lock up for DB2-UDB and DB2-390 databases. Therefore, by setting the property to False, the DAC Server will drop and create views selectively. Note: This setting only applies to Siebel Adapters. Change Capture Views are built on the Siebel (CRM) or Oracle E-Business Suite OLTP database.
Dryrun	Possible values are True and False. Indicates whether tasks are executed without invoking Informatica workflows. The following processes are executed: change capture, truncation of tables, drop and creation of indices, and analyze statements. Possible values are True and False. Note: This option should be used for debugging purposes only and not used in a production environment.
Generic Task Concurrency Limit	Determines how many tasks with execution types other than Informatica can be run concurrently. The value must be numerical. To set this value, you should consider what the external tasks do. For example, if the tasks open connections to a database, you should consider how this would affect the preconfigured tasks.
HeartBeatInterval	Frequency (in seconds) the DAC Server checks on the health of the database connections. The value must be numerical. For example, a value of 300 (the default value) indicates the system will perform subsystem diagnostics and recovery procedures every 300 seconds.
InformaticaParameter FileLocation	This file location can be any file system location that is accessible to both the Informatica PowerCenter Integration Services service and the DAC Server. It is the same location that is entered as the Source File Directory in Informatica PowerCenter Workflow Manager. Note: This file location also should not have spaces.
Output Redirect	Indicates whether logging information and standard output and errors are redirected to files in the log directory (when property is set to True). The file containing standard output starts with out_ and ends with the .log extension. The standard error messages are in the file starting with err_ and ending with the .log extension. If this property is set to False, the logging information is directed to the machine's standard output and error files, which typically defaults to the console from which the DAC Server was launched if the server was launched in a visible console mode.
Repository DB Pool Size	Maximum number of connections to the DAC repository the DAC Server will maintain. The value must be numerical. Note: As the DAC Server runs, it needs to perform transactions and queries on the DAC repository. In order for it to do so efficiently, it internally maintains a pool of preexisting database connections. This pool of database connections reduces latency and leads to better use of resources through connection sharing. However, you need to specify the maximum size of the connection pool. This setting should be determined in consultation with the database administrator of the database where the DAC repository resides. Note: This property was deprecated as of version 7.7.1.5.
Repository Name	Unique name for the DAC repository (for example DAC).
Scheduler.Poll.Interval	Frequency (in seconds) the DAC Server polls for changes in the schedule configuration.
Script After Every ETL	The name of the script or executable to be run after every execution plan. For more information, see the description of the property Script Before Every ETL.
Script Before Every ETL	The name of the script or executable to be run before every execution plan. For example, before running an execution plan, you might want to run a process or perform certain tasks. These can be contained in a script or executable. This file should be placed in the scripts subdirectory of the DAC Server. The execution plan runs only after the external process has finished. Therefore, it is important that the script or executable does not fail.
Server Log Level	Output logging level. Possible values are Finest, Finer, Fine, Config, Info, Warning, and Severe. The Severe value produces minimal log details, and Finest produces the most extensive amount of reporting.
SQL Trace	Possible values are True and False. Indicates whether the SQL statements to the DAC repository and database connections are added to the log file. Possible values are True and False. The True value sends a hint to the database connectivity layer of the DAC Server to enable SQL tracing; thus, every SQL statement that is run by the DAC Server is spooled to the appropriate output log file. It is recommended that you set this property to False.
Verify and Create Non-Existing Indices	Possible values are True and False. Indicates whether indices defined in the DAC repository will be automatically created in the data warehouse database during an incremental load. Note: When this system property is set to True, the DAC Server verifies whether indices defined in the DAC repository are also defined in the data warehouse database. This verification process can delay the execution of an execution plan.

How to Activate Join Indices for Teradata Databases

For Teradata databases, the preconfigured tasks for creating and dropping join indices are inactive.

To activate join indices for Teradata databases

Create a new execution plan with the list of subject areas that you are interested for the ETL.
Query for all tasks whose name starts with 'Teradata Drop' and add them as preceding tasks.
Query for all tasks whose name start with 'Teradata Create' and add them as following tasks.
Assemble the execution plan parameters in the Parameters tab and configure the parameters.
Redesign the execution plans.