13 Common Tasks Performed in the DAC

This chapter contains the following topics:

• Accessing the DAC Server Using the Command Line

• DAC Repository Command Line Parameters

• Running the DAC Server Automatically (Standalone Mode)

• Running Two DAC Servers on the Same Machine

• Accessing Multiple DAC Servers Using One DAC Client

• Pointing Multiple Informatica Integration Services to a Single Informatica Repository

• Resetting the Data Warehouse

• Viewing DAC Metrics Using Fusion Middleware Control MBean Browser

• Monitoring the DAC Server Using WebLogic Server

Accessing the DAC Server Using the Command Line

You can access the DAC Server through a command line to start and stop execution plans and to get status information for servers, databases, and execution plans. This feature enables you to access the DAC Server using third-party administration or management tools, without using the DAC Client. Refer to the file dacCmdLine.bat/.sh for usage.

This section includes the following topics:

• Setting Up Command Line Access to the DAC Server

• Using the Command Line to Access the DAC Server

• Command Line Status Monitoring Queries

Setting Up Command Line Access to the DAC Server

The Command Line utility enables you to invoke commands on a DAC Server running on a remote or local machine.

To set up command line access to the DAC Server

1. Make sure you have installed the supported version of the Java SDK.

2. Copy the following files from the <Domain_Home>\dac directory to a local directory:

   • DAWSystem.jar

   • dac.properties

   • dacCmdLine.bat or dacCmdLine.sh

3. In the dacCmdLine.bat file, do the following:

   1. Edit the JAVA_HOME variable to point to the directory where the Java SDK is installed.

      Make sure there are no spaces in the path reference.

   2. Edit the DAC_HOME variable to point to the directory where the DAC is installed.

4. In the dac.properties file, edit the following parameter values.

   Parameter	Value
   ServerHost=	Host name of the DAC Server.
   ServerPort=	Port of the DAC Server. The default is 3141.
   RepositoryStampVal=	Repository stamp that appears in the DAC Client Login Details screen. To find this value, in the DAC Client navigate to Help, then select Login Details.

   
   

   The dac.properties file should look similar to the following:

   ServerHost=<host name> ServerPort=3141 RepositoryStampVal=851E0677D5E1F6335242B49FCCd6519
   

Using the Command Line to Access the DAC Server

Follow this procedure to use the command line to access the DAC Server.

To use the command line to access the DAC Server

• At the command prompt, enter the following:

  dacCmdLine <method name> <optional execution plan name>
  

  where method name is one of the following:

  Method Name	Description
  StartServer	Starts the DAC Server (Web mode only).
  StopServer	Stops the DAC Server (Web mode only).
  StartETL	Starts an execution plan. You must specify an execution plan name. -wait option lets you start the execution plan in synchronous mode.
  StopETL	Stops the operation of an execution plan. You must specify an execution plan name.
  ETLStatus	If you do not specify an execution plan name, the status of the execution plan that last ran is returned. If you specify an execution plan name, the status of the specified execution plan is returned.
  DatabaseStatus	Verifies whether the DAC Server can connect to all active database connections. You do not need to specify an execution plan name.
  InformaticaStatus	Verifies whether the DAC Server is able to ping all active Informatica PowerCenter Services machines.

  
  

  Note:

  The method names are case insensitive. Execution plan names are case sensitive. Also, if the execution plan name contains spaces, place beginning and ending double quotes around the name.

  For example:

  Command Line	Description
  dacCmdLine EtlStatus	Returns the status of the execution plan that last ran.
  dacCmdLine EtlStatus Forecast	Returns the status of the last instance of the Forecast execution plan.
  dacCmdLine StopEtl Forecast	If the execution plan currently running is Forecast, the operation will be terminated. Otherwise, the request is ignored.
  dacCmdLine databasestatus	Returns the health status of all the database connections as defined in the DAC repository from the DAC Server.
  dacCmdLine InformaticaStatus	Returns the health status of all the Informatica PowerCenter Services connections as defined in the DAC Client on the Informatica Services tab.

  
  

Command Line Status Monitoring Queries

The command line feature enables you to get the following status information:

• Summary of the requested execution plan. If there are multiple instances of the same execution plan, a summary of the instance that last ran is returned. Below is an example of the information contained in the summary.

  (c) 2009 Oracle
  Oracle DAC Server comprising the etl execution-management,  scheduler, logger, and network server.
  ETL details for the last run:
  ETL Process Id : 255 ETL Name : Complete ETL Run Name : DRY RUN OF Complete ETL: ETL Run - 2004-06-17 18:30:13.201 DAC Server : (<host name>) DAC Port : 3141 Status: Stopped Log File Name: Complete_ETL.255.log Database Connection(s) Used : 
  
  
  OLTP jdbc:microsoft:sqlserver://<host name>:1433;DatabaseName=OLTP Data Warehouse jdbc:microsoft:sqlserver://<host name>:1433;DatabaseName=olap
  
  Informatica Server(s) Used  : 
  
  InformaticaServer4-<host name>:(4)  InformaticaServer2-<host name>:(2)  InformaticaServer3-<host name>:(3)  InformaticaServer1-<host name>:(10) 
  
  Start Time: 2004-06-17 19:00:06.885 Message: ETL was interrupted Actual Start Time: 2004-06-17 18:30:13.357 End Time: 2004-06-17 19:05:56.781 Total Time Taken: 35 Minutes
  Start Time For This Run: 2004-06-17 19:00:06.885 Total Time Taken For This Run: 5 Minutes
  Total steps: 212 Running steps: 0 Complete steps: 142 Failed/Stopped steps:70
  

• Summary of connection status to all active databases and Informatica servers.

DAC Repository Command Line Parameters

This section describes the DAC repository command line properties that are exposed by the AutomationUtils.bat file, which is located in the <Domain_Home>\dac directory.

Note: Before you can use the command line properties, you must first configure DAC repository database and authentication properties in the automationUtils.properties file, which is located in the <Domain_Home>\dac directory. Alternatively, you can create your own connection file that contains the repository database and authentication properties. Many command line properties discussed in this section require the path to the connection file (either automationUtils.properties or a user-defined connection file). The path to the connection file must include the file name.

The following DAC repository command line parameters are available in the AutomationUtils.bat file:

• Analyze DAC Schema

• Apply Distributed Dev Patch

• Assemble Subject Area

• Build Execution Plan

• Change Encryption Key

• Clear Encrypted Data

• Command Credentials

• Create DAC Schema

• Create DAC User

• Create Data Warehouse Schema

• Create Patch of Objects Between Time Stamps

• Database Credentials

• Delete Objects Execution Plan

• Delete Objects Subject Area

• Drop DAC Schema

• Drop Data Warehouse Schema Using Schema Definition File

• Export DAC Metadata by Application

• Export DAC Metadata by Categories

• Export Patch

• JKS Password

• Generate DW Schema Definition File

• Import DAC Metadata by Application

• Import DAC Metadata by Categories

• Repository XML Snapshot

• Server Setup

• Set Password

• Upgrade DAC Schema

• Upgrade Data Warehouse Schema Using Schema Definition File

Analyze DAC Schema

The analyzeDACSchema property analyzes the DAC repository tables.

Syntax:

<path to connection file> analyzeDACSchema

Apply Distributed Dev Patch

The applyDistributedDevPatch property applies a patch to the DAC repository.

Syntax:

<path to connection file> applyDistributedDevPatch <file name>

where:

Parameter	Description
file name	Name of the XML file.

Assemble Subject Area

The assembleSubjectArea property assembles a subject areas.

Syntax:

<path to connection file> assembleSubjectArea <container name> <subject area name>

Build Execution Plan

The buildExecutionPlan property builds an execution plan.

Syntax:

<path to connection file> buildExecutionPlan <execution plan name>

Change Encryption Key

The changeEncryptionKey property changes the encryption key in the credentials file and re-encrypts all encrypted information in the DAC repository.

Syntax to change encryption key with a randomly generated key:

<path to connection file> changeEncryptionKey -randomkey

Syntax to change encryption key by explicitly specifying the key from command line input:

<path to connection file> changeEncryptionKey

Clear Encrypted Data

The clearEncryptedData property removes all encrypted data from the DAC repository.

Syntax:

<path to connection file> clearEncryptedData <schema owner>

Note: An authentication file is not required for this operation. The repository password will be taken from command line input.

Caution:

This operation will clear all database, Informatica, and external executor passwords as well as all DAC user data. Use this command only when the authentication file with the encryption key was lost and cannot be recovered. After removing the encrypted data from the DAc repository, generate the authentication file with a new encryption key, and log into the DAC repository using the DAC Client. When prompted to add the encryption key, update all passwords in the DAC repository and distribute the new authentication file to all required DAC users.

Command Credentials

The cmdCredentials parameter stores credentials for the DAC command line utility for both the Fusion Middleware and DAC standalone authentication modes.

Syntax:

cmdCredentials <path to cwallet.sso file> <user name>

Note: The password will be taken from command line input. Also, make sure to include the cwallet.sso file name in the path.

Create DAC Schema

The createDACSchema property creates the schema of a new DAC repository.

Syntax:

<path to connection file> createDACSchema <unicode> <tablespace>

where:

Parameter	Description
unicode	Specifies whether the schema is created as unicode. Possible values are true and false.
tablespace	The name of the default tablespace in which the schema is created. Surround the name with double quotation marks. Note: If the value for the AuthenticationType property in the connection file is FMW, then user credentials will be used for authentication.

Create DAC User

The createDACUser property creates a DAC schema user.

Syntax for FMW authentication:

<path to connection file> createDACUser <user name> [role1] [role2] ...

Syntax for database authentication:

<path to connection file> createDACUser <user name> [role1] [role2] ... [-dbVerification <schema owner>]

Note: The user password will be taken from command line input.

Create Data Warehouse Schema

The createDWSchema property creates the data warehouse schema using the schema definition file.

Syntax:

<path to connection file> createDWSchema <dwSchemaDefinitionFileName> <unicode> <tablespace>

where:

Parameter	Description
dwSchemaDefinitionaFileName	Specifies the data warehouse schema definition file location and name. This file is generated by the generateDWSchemaDefinitionFile command line parameter.
tablespace	The name of the default tablespace in which the schema is created. Surround the name with double quotation marks.
unicode	Specifies whether the schema is created as unicode. Possible values are true and false.

Note: The file create.sql is generated and stored in the <Domain_Home>/dac/conf/sqlgen/sql/oracle directory of an enterprise installation, and the dac/conf/sqlgen/sql/oracle directory in a standalone or client installation. User authentication is not required for this command.

Create Patch of Objects Between Time Stamps

The createPatchOfObjectsBetweenTimeStamps property creates a DAC repository metadata patch containing all eligible objects that are new or changed between two timestamps.

Syntax:

<path to connection file> createPatchOfObjectsBetweenTimeStamps <patch name> <from timestamp> <to timestamp>

where:

Parameter	Description
from timestamp	The beginning timestamp for the timestamp range in format yyyymmdd-hh:mm:ss:ss. The hh value must be between 00-23.
to timestamp	The ending timestamp for the timestamp range. The format is the same as that of the "from timestamp."
-now	A special string to specify the current timestamp.

Database Credentials

The dbCredentials property generates an authentication file (cwallet.sso) with the DAC repository credentials and encryption key.

Syntax for explicit key and credentials:

dbCredentials <path to cwallet.sso file> <user name> -withKey

Syntax for randomly generated encryption key and credentials:

dbCredentials <path to cwallet.sso file> <user name> -randomKey

Syntax to update credentials in existing file without modifying the encryption key:

dbCredentials <path to cwallet.sso file> <user name> -noKey

Note: The user password will be taken from command line input.

Delete Objects Execution Plan

The deleteObjects ExecutionPlan property deletes executions plan from the DAC repository.

Syntax:

<path to connection file> deleteObjects ExecutionPlan <name 1> [name 2] ...

Delete Objects Subject Area

The deleteObjects SubjectArea property deletes subject areas from the DAC repository.

Syntax:

<path to connection file> deleteObjects SubjectArea <container name> <subject area name 1> [subject area name 2] ...

Drop DAC Schema

The dropDACSchema property drops the schema of the DAC repository.

Syntax:

<path to connection file> dropDACSchema

Drop Data Warehouse Schema Using Schema Definition File

The dropDWSchemaFromSchemaDefinitionFile property drops the data warehouse schema using the schema definition file.

Syntax:

<path to connection file> dropDWSchemaFromSchemaDefinitionFile <dwSchemaDefinitionFileName> <execute>

where:

Parameter	Description
dwSchemaDefinitionaFileName	Specifies the data warehouse schema definition file location and name. This file is generated by the generateDWSchemaDefinitionFile command line parameter.
execute	Possible values are true and false. If set to true DAC will generate and execute the SQL statements that drop the data warehouse schema. If set to false, the SQL statements will be generated but not executed.

Note: The file drop.sql is generated and stored in the <Domain_Home>/dac/conf/sqlgen/sql/oracle directory of an enterprise installation, and the dac/conf/sqlgen/sql/oracle directory in a standalone or client installation. User authentication is not required for this command.

Export DAC Metadata by Application

The Export property exports DAC metadata from the DAC repository for specified source system containers.

Syntax:

<path to connection file> export <folder name> <container name 1> <container name 2> ...

where:

Parameter	Description
folder name	Full path to the location of the export file structure.
container name	(Optional) Name of the source system container for which you want to export DAC metadata. If no container is named, all containers that are found in the file structure will be exported.

Export DAC Metadata by Categories

The exportCategory property exports DAC metadata from the DAC repository based on the Logical, Run Time, or System categories.

Syntax:

<path to connection file> exportCategory <folder name> <category>

where:

Parameter	Description
folder name	Full path to the root of the export file structure.
category	Possible values are the following: logical - Exports all data categorized as logical (information contained in the DAC Design view). runtime - Exports all data categorized as run time (information contained in the DAC Execute view). system - Exports all data categorized as run time (information contained in the DAC Setup view).

Export Patch

The exportPatch property exports a DAC metadata repository patch in XML format.

Syntax:

<path to connection file> exportPatch <file name> <patch name>

where:

Parameter	Description
file name	Name of the XML file.

JKS Password

The jksPassword parameter stores the password for the Java key store.

Syntax:

jksPassword <path to cwallet.sso file>

Note: The password will be taken from command line input. Also, make sure to include the cwallet.sso file name in the path.

Generate DW Schema Definition File

The generateDWSchemaDefinitionFile generates a data warehouse schema definition file.

Syntax:

<path to connection file> generateDWSchemaDefinitionfile <dwSchemaDefinitionFileName> <container 1> <container 2> ...

Note: The definition file will be generated for the specified containers. If you do not specify a container, the file will be generated for all containers.

Import DAC Metadata by Application

The Import property imports DAC metadata into the DAC repository for specified source system containers.

Syntax:

<path to connection file> import <folder name> [-noTruncate] [-noUpdate] <container name 1> <container name 2> ...

where:

Parameter	Description
folder name	Full path to the root of the import file structure.
container name	(Optional) Name of the source system container for which you want to import DAC metadata. If no container is named, all containers that are found in the file structure will be imported.
-noTruncate	By default the repository will be truncated upon import. Use the -noTruncate option for incremental import.
-noUpdate	By default existing records will be updated during incremental import. Use the -noUpdate option if you only want to insert new records.

Import DAC Metadata by Categories

The importCategory property imports DAC metadata from the DAC repository based on the Logical, Run Time, or System categories.

Syntax:

<path to connection file> exportCategory <folder name> <category>

where:

Parameter	Description
folder name	Full path to the root of the import file structure.
category	Possible values are the following: logical - Imports all data categorized as logical (information contained in the DAC Design view). runtime - Imports all data categorized as run time (information contained in the DAC Execute view). system - Imports all data categorized as run time (information contained in the DAC Setup view).
-noTruncate	By default the repository will be truncated upon import. Use the -noTruncate option for incremental import.
-noUpdate	By default existing records will be updated during incremental import. Use the -noUpdate option if you only want to insert new records.

Repository XML Snapshot

The repositoryXMLSnapshot property exports a snapshot of the DAC repository in XML format.

Syntax:

<path to connection file> repositoryXMLSnapshot <file name>

where:

Parameter	Description
file name	Name of the XML file.

Server Setup

The serverSetup property sets up the DAC Server connection to the DAC repository.

Syntax:

<path to connection file> serverSetup <database type> <database name/instance name/tns> <host> <port>

Note: If Fusion Middleware authentication is specified in the connection file, the DAC Server URL should be specified in the properties file and the DAC Server will be set to where the URL points (location can be a remote server). If DAC standalone authentication is specified in the connection file, you must configure the repository connection properties in the properties file. The DAC Server will be set up in the local installation. The user name, password, and encryption key will be taken from the database credentials (cwallet.sso) file.

Set Password

The setPassword parameter sets the password for the Informatica Integration Service, Informatica Repository Service, and physical data sources in the DAC repository.

Syntax:

<path to connection file> setPassword <type> <logical name>

where:

Parameter	Description
type	Possible values are the following: server - use for Informatica Integration Service and Repository Service. dbconn - use for physical data sources.
logical name	Logical name of the server or physical data source record in DAC.

Note: The password will be taken from command line input. If the logical name or password contains spaces, surround the entry in double quotes.

Upgrade DAC Schema

The upgradeDACSchema parameter upgrades the DAC repository.

Syntax:

<path to connection file> upgradeDACSchema

Upgrade Data Warehouse Schema Using Schema Definition File

The upgradeDWSchemaFromSchemaDefinitionFile property upgrades the data warehouse schema using the schema definition file.

Syntax:

<path to connection file> upgradeDWSchemaFromSchemaDefinitionFile <dwSchemaDefinitionFileName> <unicode> <execute>

where:

Parameter	Description
dwSchemaDefinitionaFileName	Specifies the data warehouse schema definition file location and name. This file is generated by the generateDWSchemaDefinitionFile command line parameter.
unicode	Specifies whether the schema is created as unicode. Possible values are true and false.
execute	Possible values are true and false. If set to true DAC will generate and execute the SQL statements that drop the data warehouse schema. If set to false, the SQL statements will be generated but not executed.

Note: User authentication is not required for this command.

Running the DAC Server Automatically (Standalone Mode)

Follow this procedure to set up the DAC Server to be run automatically when your machine reboots.

Note: This procedure applies to the DAC Server in standalone mode only.

To set up the DAC Server to run automatically upon rebooting the machine

1. From the Start menu, select Programs, Accessories, System Tools, Scheduled Tasks.

2. Double-click Add Scheduled Task.

3. In the Scheduled Task Wizard, browse to startserver.bat, and click Open.

4. Select the option When my computer starts, and click Next.

5. Enter the domain user account to start the DAC Server and a password, and click Finish.

   The startserver task is displayed in the Scheduled Task window.

6. Right-click the task and select Properties.

7. In the Settings tab, deselect the Stop the task if it runs for 72 hours check box.

To start the DAC Server as a scheduled task

1. From the Programs menu, select Accessories, System Tools, Scheduled Tasks.

2. Right-click startserver, and then click Run.

To stop the DAC Server as a scheduled task

1. From the Programs menu, select Accessories, System Tools, Scheduled Tasks.

2. Right-click startserver, and then click End Task.

To check if the DAC Server is running

1. From the Programs menu, select Accessories, System Tools, Scheduled Tasks.

2. Select the startserver task.

3. In the Windows menu bar, select View, Details.

Running Two DAC Servers on the Same Machine

In the DAC standalone mode, you can run two DAC Servers on the same machine as long as they are listening on different ports and pointing to two different repositories.

Note: This procedure does not apply when the DAC Server is running as a service of the DACServer application on WebLogic Server.

To run two DAC Servers on the same machine

1. Copy the <Domain_Home>\dac directory to a different directory on the same machine.

   For example, you might copy the C:\<Domain_Home>\dac directory to C:\<Domain_Home>\DAC_SERVER2\dac.

2. Edit the config.bat file to set the DAC_HOME variable appropriately for each instance.

   For example if you copy the C:\<Domain_Home>\dac directory to C:\<Domain_Home>\DAC_SERVER2\dac, make sure that the C:\<Domain_Home>\DAC_SERVER2\dac config.bat file is configured correctly.

3. Launch each of the DAC Clients by navigating to the DAC directories and double-clicking the startclient.bat file.

4. For each instance, configure the DAC Server connection.

   1. Navigate to Tools, DAC Server Management, DAC Server Setup.

      A message dialog states this operation should be performed on the machine running the DAC Server. It asks whether you want to continue.

   2. Click Yes.

   3. In the Repository Connection Information tab, enter the appropriate information for each instance. The Database Host should be the same for each instance, and the Database Port should be different.

   4. Click Save.

5. For each instance, configure the DAC repository connection.

   1. Navigate to Tools, DAC Server Management, Repository Configuration.

   2. In the Repository Configuration dialog, enter the following information:

      Field	Description
      Mode	Select Standalone.
      Standalone Mode Configuration:	If you are using standalone mode configuration, enter the following information:
      Host	Name of the host machine where the DAC Server resides.
      Alternative Hosts	(Optional) Names of additional machines hosting DAC Servers that can be used to connect to the DAC repository if the main DAC Server host machine fails. Separate multiple hosts with commas.
      Port	DAC Server port.

      
      

6. Start each DAC Server from its directory.

Accessing Multiple DAC Servers Using One DAC Client

You can use one DAC Client to access multiple DAC Servers. The DAC Servers can reside on the same machine, or they can each reside on a different machine. Each DAC Server must listen on its own port and point to a single DAC repository.

Pointing Multiple Informatica Integration Services to a Single Informatica Repository

You can install multiple Informatica Integration Services and point them to a single Informatica repository. You need to register each instance of the Integration Service in DAC and specify a unique machine name and service name. For instructions on registering Integration Services, see "Registering Informatica Services in DAC".

Resetting the Data Warehouse

When you reset a data warehouse all tables are truncated and refresh dates are deleted. This allows you to perform a full load of data into the data warehouse.

To reset the data warehouse

1. On the Tools menu, select ETL Management, and then select Reset Data Warehouse.

   The Reset Data Warehouse dialog is displayed and requests you to confirm you want to reset the data warehouse.

2. Enter the confirmation text in the text box.

3. Click Yes.

Viewing DAC Metrics Using Fusion Middleware Control MBean Browser

Fusion Middleware Control MBean Browser is an Oracle Web application (based on JMX MBean containers) that you can use to view information about running, failed and queued execution plans and the status of the DAC Server.

Information about execution plans is available through the MBean Browser only if the DAC Server is running.

To display DAC metrics using Fusion Middleware Control MBean Browser

1. Display Fusion Middleware Control.

2. In the Navigator window, expand the WebLogic Domain folder and the bifoundation_domain node, and select the AdminServer node.

3. Display the WebLogic Server menu and select System MBean Browser from the menu to display the Fusion Middleware Control System MBean Browser.

4. Display the DACMetrics MBean by selecting the following: ApplicationDefinedMBeans, com.oracle.dac, Server:<server name>, Application: DACServer, EMIntegration, and DACMetrics MBean.

   The attributes and operations that are available to be invoked are displayed.

Monitoring the DAC Server Using WebLogic Server

The DAC Server runs as a service of the DACServer application on WebLogic Server. The WebLogic Server Administration Console enables you to monitor the status of the DAC Server.

Note: The DAC Server can also run in standalone mode. For information about the differences between the standalone and WebLogic Server modes, se

To monitor the DAC Server using the WebLogic Server Administration Console

1. Display the WebLogic Server Administration Console in one of two ways:

   • On the Overview page in Fusion Middleware Control, expand the WebLogic Domain node and select the bifoundation_domain. Locate the WebLogic Server Administration Console link in the Summary region.

   • Enter the following URL into a Web browser:

     http://<host>:<port>/console
     

     where host is the DNS name or IP address of the DAC Server and port is the listen port on which the WebLogic Server is listening for requests. The default port is 7001.

2. Enter the system administrator user name and password and click Login.

   This system wide administration user name and password was specified during the installation process, and you can use it to log in to WebLogic Server Administration Console and Fusion Middleware Control. Alternatively, enter a user name that belongs to one of the following security groups:

   • Administrators

   • Operators

   • Deployers

   • Monitors

3. Click on the Deployments link in the navigation tree on the left to display the list of deployed applications.

4. Select the DACServer application.

   The DAC Server status is displayed.