10 Oct 2013 How to access the DAC server using the command line

One of the features that DAC has is the ability to remotely access it through the command line. This allows us to get information such as the status of an ETL execution or the status of the Informatica server. Moreover we can also start or stop execution plans and create shell scripts that can call the DAC server and programme actions depending on the status of a job. In this article we are going to see how to set up the command line access to the DAC server and how to use the commands available. Finally we will see how to create a script that automatically restarts an ETL execution if it has failed.

Setting up the access to the DAC server:

First of all make sure that you have installed in your local machine a supported version of JAVA SDK. Next go to the DAC server and navigate to the DAC folder (C:\OracleBI\DAC) and copy the following files in a new folder in your local machine:

• DAWSystem.jar: DAC executable Jar file
• dac.properties: File that contains the connection properties to access the DAC server
• dacCmdLine.bat: Batch file that contains the connection string to the DAC server

Edit the dac.properties file with your DAC server connection properties (syntax code for windows environment):

ServerHost=ETL_PROD_01
ServerPort=3141
RepositoryStampVal=B6DFA4EEF3EAA27298B5B5647AB61C77

You can find the RepositorySampVal in the DAC console by clicking on Help – Login details

Now edit the dacCmdLine.bat and set the JAVA_HOME and DAC_HOME (folder where you placed the DAC files from the server) path of your local machine.

set JAVA_HOME=C:\Java\jdk1.5.0_17
set DAC_HOME=C:\ DACRemote
set JAVA=%JAVA_HOME%\bin\java.exe

Make sure that your paths do not contain any space, as it would fail during execution. Now we are ready to access the DAC server from our local machine.

Using the command line to access to the DAC server:

Open a new command line window and from your local DAC folder execute the following command:
-> dacCmdLine EtlStatus

The command will return a summary of the last subject area executed in the server. There are five commands that we can use to interact with the DAC server:

• EtlStatus: Returns a summary of the last subject area executed in the server, also you can specify which subject area you want to see the status by adding the name of the subject area as a second parameter.
• StartEtl: Starts an execution plan. You have to specify the subject area as the second parameter.
• StopEtl: Stops an execution plan. You have to specify the subject area as the second parameter.
• Databasestatus: Checks if the DAC server can connect to all the databases.

• InformaticaStatus: Checks if the DAC Server can connect to the Informatica server.

Building a script to automatically restart a failed ETL execution:

Sometimes due to errors in the network the ETL execution fails as the communications between the source or target DB and the DAC server is lost. In these cases when there is nothing wrong in our mappings or DAC tasks we only need to restart the ETL to complete the execution successfully. To avoid restarting manually the ETL each time it fails we will schedule a task that checks the status of the ETL execution and restarts it if it has failed. Create a new batch file and name it ‘Restart_Failed_ETL.bat’, make sure that you place the file in a server where you can schedule batch tasks and also that has the remote DAC installation done (in case it is not the ETL server itself). Open the batch file and first set the paths to JAVA and DAC:

set JAVA_HOME=C:\Java\jdk1.5.0_17
set DAC_HOME=C:\ DACRemote
set JAVA=%JAVA_HOME%\bin\java.exe

Additionally create a new variable to set the name of the subject area we want to start:

set ETL_LOAD="CRM – Daily Load"

The next line will get the ETL status and save it in the variable RESULT. Notice that we look for the string message as it is after that when we get the status of the ETL.

for /f “tokens=2” %%i in (‘%JAVA% -cp %DAC_HOME%\DAWSystem.jar com.siebel.etl.net.DACCommandLine %DAC_HOME%\dac.properties ETLStatus ^| findstr Message:’) do SET RESULT=%%i

Now we will check if the status message contains the word ‘some’ as it is the first word of the message that the ETL returns if it has failed: Message: Some steps failed.

If it contains the word ‘some’ then it restarts the ETL again by executing the StartETL command:
IF %RESULT%==Some (%JAVA% -cp %DAC_HOME%\DAWSystem.jar com.siebel.etl.net.DACCommandLine %DAC_HOME%\dac.properties StartETL %ETL_LOAD%) ELSE ( ECHO ETL will not be restarted)

The final script should look like this:

ECHO off
set JAVA_HOME=E:\Java\jdk1.5.0_17
set DAC_HOME=E:\OracleBI\DAC
set JAVA=%JAVA_HOME%\bin\java.exe
set ETL_LOAD="LTA-CRM Load"
for /f "tokens=2" %%i in ('%JAVA% -cp %DAC_HOME%\DAWSystem.jar com.siebel.etl.net.DACCommandLine %DAC_HOME%\dac.properties ETLStatus ^| findstr Message:') do SET RESULT=%%i
IF  %RESULT%==Some (%JAVA% -cp %DAC_HOME%\DAWSystem.jar com.siebel.etl.net.DACCommandLine %DAC_HOME%\dac.properties StartETL %ETL_LOAD%) ELSE ( ECHO ETL will not be restarted)

Finally schedule the execution of the batch file to start at the same time that the ETL normally finishes. Now it will check every night if the ETL has completed or failed and then automatically restart it if it is the case. We hope you find this information useful, please leave a comment if you have any questions or thoughts.