This chapter contains the following topics:
![]() Overview of Parameters
This section contains the following topics:
What Are Parameters and How Are They Used in DAC?
Jan 04, 2014 A parameter file is a list of parameters and variables and their associated values. These values are defined properties for a service, service process, workflow, worklet, or session. The Integration Service applies these values when you run a workflow or session that uses the parameter file.
In an ETL process, a parameter represents an attribute that is not hard coded or sourced from the transactional system. Parameters provide flexibility so that you can adapt ETL processes to fit your business requirements.
The following examples illustrate a few of the ways you can use parameters in ETL processes:
You can define static or runtime parameters in the DAC repository (using the DAC Client) to apply to source systems, tasks, or execution plans. You can also define parameters whose values come from outside of DAC, such as from external relational sources that DAC accesses through SQL interfaces, or from external technology stacks that DAC accesses through a Java API interface.
In addition, some parameters used by Oracle BI Applications are defined by the user in the Oracle BI Applications Configuration Manager. See Oracle Fusion Middleware Configuration Guide for Oracle Business Intelligence Applications for information about the parameters that are defined in the Configuration Manager. Also, depending on your environment, some predefined parameters used by Oracle BI Applications may be held in flat files named parameterfileDW.txt and parameterfileOLTP.txt, which are stored in the directory <Domain_Home>dacInformaticaparametersinput. Oracle recommends that you do not modify these parameter flat files. Instead, if you need to customize the value of the parameters, you can define new parameters in the DAC repository.
How DAC Handles Parameters at Runtime
During an ETL execution, DAC reads and evaluates all parameters associated with that ETL run, including static and runtime parameters defined in DAC, parameters held in flat files, and parameters defined externally to DAC. DAC consolidates all the parameters for the ETL run, deduplicates any redundant parameters, and then creates an individual parameter file for each Informatica session. This file contains the evaluated name-value pairs for all parameters, both static and runtime, for each workflow that DAC executes. The parameter file contains a section for each session under a workflow. DAC determines the sessions under a workflow during runtime by using the Informatica pmrep function ListObjectDependencies.
The naming convention for the parameter file is
<Informatica foldername>.<workflow instance name>.<primary source name>.<primary target name>.txt
DAC writes this file to a location specified in the DAC system property InformaticaParameterFileLocation. The location specified by the property InformaticaParameterFileLocation must be the same as the location specified by the Informatica parameter property $PMSourcefileDir.
Note:
Informatica Services must be configured to read parameter files from the location specified in the DAC system property InformaticaParameterFileLocation. You must set the Informatica parameter $PMSourceFileDir (accessible from Informatica Administrator) to match the location specified in InformaticaParameterFileLocation.
Types of Parameters Supported by DAC
DAC supports the following types of parameters:
Rules of Precedence
DAC evaluates the hierarchy of precedence for parameters as follows:
For example, if the same parameter is registered as a source system parameter and as a task parameter, DAC will evaluate the value of the task parameter. Similarly, if the same parameter is registered as a source system parameter and as a global external parameter, DAC will evaluate the value of the source system parameter.
Parameter Data Types
Parameters can have one of the following data types:
Text
Applies to source system and task parameters. The value for the parameter is defined as text. You can use the Text data type for both static and runtime parameters.
For instructions on defining a parameter using the Text data type, see 'Defining a Text Type Parameter'.
DB Specific Text
Applies to source system and task parameters. The value for the parameter is defined as database-specific text. This parameter should be used only if you have a heterogeneous database environment and the parameter value needs to be different for the different database types. DAC evaluates the text string based on the source or target database type. If you do not specify database-specific text, DAC returns the default value.
For instructions on defining a parameter using the DB Specific Text data type, see 'Defining a Database-Specific Text Type Parameter'.
Timestamp
![]()
Applies to source system and task parameters. The value for the parameter is defined as a timestamp. You can use the Timestamp data type for both static and runtime parameters. A static timestamp can be any time that is constant. A runtime timestamp parameter is a variable for which the value is supplied by DAC at runtime. You can define the timestamp in one of multiple formats or define a custom format. You can also use SQL to fetch any value that can be fired against the specified logical database connection. DAC executes the SQL against a data source that maps to the specified logical connection and then formats the resulting value in the specified format. A SQL statement specified for a given timestamp parameter can include nested DAC parameters. For information about nested parameters, see 'Nesting Parameters within Other Parameters'.
For instructions on defining a parameter using the Timestamp data type, see 'Defining a Timestamp Type Parameter'.
SQL
Applies to source system and task parameters. DAC retrieves the value for the parameter from a database using user-defined SQL.
For instructions on defining a parameter using the SQL data type, see 'Defining a SQL Type Parameter'.
JavaScript
Applies to source system and task parameters. DAC fetches the value for the parameter using user-defined JavaScript.
For instructions on defining a parameter using the JavaScript data type, see 'Defining a JavaScript Type Parameter'.
Multi-Parameter Text
Applies to source system and task parameters. The value for the parameter is defined as name-value pair strings using user-defined SQL.
For instructions on defining a parameter using the Multi-Parameter Text data type, see 'Defining a Multi-Parameter Type Parameter'.
Multi-Parameter Timestamp
Applies to source system and task parameters. The value for the parameter is defined as timestamps in name-value pairs using user-defined SQL.
For instructions on defining a parameter using the Multi-Parameter Timestamp data type, see 'Defining a Multi-Parameter Type Parameter'.
External-Parameter Text
Applies to source system parameters only. The value for the parameter is defined using a parameter producer Java API. The External-Parameter Text data type enables you to create a library and classes to implement the DACTextParameterProducer interface.
For instructions on defining a parameter using the External-Parameter Text data type, see 'Defining an External Type Parameter'.
External-Parameter Timestamp
Applies to source system parameters only. The value for the parameter is defined using a parameter producer Java interface. The External-Parameter Timestamp data type enables you to create a library and classes to implement the DACTimestampParameterProducer interface.
For instructions on defining a parameter using the External-Parameter Timestamp data type, see 'Defining an External Type Parameter'.
Global Multi-Parameter Text or Timestamp
Applies to global parameters only. The value for the parameter is defined as name-value pairs using user-defined SQL.
For instructions on defining a global parameter, see 'Defining a Global External Parameter'.
Static Versus Runtime Parameter Values
Source system and task parameters that have either a Text or Timestamp data type can be specified as either static or runtime. The values of static parameters are user defined and are not necessarily constant for all ETL processes. For example, static parameter values can be the result of a SQL query or the evaluation of a JavaScript. The values for runtime parameters are provided by DAC and pertain to runtime data. Such values are used in the ETL logic but cannot be predicted, such as process ID, last refresh timestamp, ETL start timestamp, and so on.
Predefined Parameters in Oracle BI Applications
Oracle BI Applications uses predefined parameters for Informatica tasks. The parameters are specific to Informatica sessions. Some of the predefined parameters are held in text files named parameterfileDW.txt and parameterfileOLTP.txt, which are stored in the directory <Domain_Home>dacInformaticaparametersinput. Other predefined parameters are held in DAC. The parameters held in DAC are specific to the different source system containers.
Oracle recommends that you do not modify the parameter text files. Instead, if you need to customize the value of the parameters, you can define parameters in the DAC repository at either the source system level or the task level.
Nesting Parameters within Other Parameters
You can nest any parameter definition within another parameter definition. For example, you could nest a runtime text parameter that returns the current run ID within a where clause of a SQL parameter, or you could use database specific text inside another parameter of the text or SQL data types. Parameters that are nested within other parameters must use the prefix @DAC_.
Below is an example of a text parameter that returns the current run ID placed in a where clause of a SQL parameter.
SELECT VALUE FROM PARAM_TEST WHERE ROW_ID=`@DAC_p1'
Note:
Avoid circular nesting, such as parameter A nested within parameter B, which is nested within parameter A. In such situations, DAC randomly picks one of the parameters in the circle and evaluates it as an empty string.
Parameter Load Type Property
The Load Type property enables you to define parameter values that are specific to the type of load the ETL process is carrying out, that is, a full load or an incremental load. This property can be useful for performance tuning. Note that this property does not apply to global external parameters.
About DAC Variables
DAC contains predefined variables that act as a mechanism to allow parameters to access various ETL-specific and task-specific information. These variables are available when you define a runtime parameter of the Text and Timestamp data types. When you define a runtime parameter and select a DAC variable, the variable gets wrapped inside the parameter and returns the information specified by the variable.
DAC Variables for the Text Data Type
The following variables are available when you define a runtime parameter of the Text data type.
DAC Variables for the Timestamp Data Type
The following variables are available when you define a runtime parameter of the Timestamp data type.
Defining Parameters
This section provides instructions for defining parameters based on the data type.
Defining a Text Type Parameter
Follow this procedure to define a parameter using the Text data type. This procedure applies to parameters defined at both the source system and task levels.
To define a Text data type parameter
Note: You can inactivate the parameter by selecting the Inactive check box.
Defining a Database-Specific Text Type Parameter
Follow this procedure to define a parameter using the DB Specific Text data type. This procedure applies to parameters defined at both the source system and task levels.
To define a database-specific text data type parameter
Note: You can inactivate the parameter by selecting the Inactive check box.
Defining a Timestamp Type Parameter
Follow this procedure to define a parameter using the Timestamp data type. This procedure applies to parameters defined at both the source system and task levels.
To define a Timestamp data type parameter
Note: You can inactivate the parameter by selecting the Inactive check box.
Defining a SQL Type Parameter
Follow this procedure to define a parameter using the SQL data type. This procedure applies to parameters defined at both the source system and task levels.
To define a SQL data type parameter
Note: You can inactivate the parameter by selecting the Inactive check box.
Defining an External Type Parameter
The External parameter data types enable you to define parameters using Java APIs to retrieve text or timestamp values from a source or target database. You implement the parameter producer interfaces by creating libraries and classes. During an ETL process, parameters received from the API are added to the DAC metadata.
Note: Producer code examples are available in the DACdocumentationpublic directory.
Text Interface
The External-Parameter Text data type uses the DACTextParameterProducer interface to return string values. The interface maps data source number IDs, database types, JDBC connections, and initialization parameters.
The DACTextParameterProducer interface is as follows:
Note: DAC forces DACTextParameterProducer to implement the finalize method.
Defining an External-Parameter Text Type Parameter
Follow this procedure to define an External-Parameter Text data type parameter.
Note: Producer code examples are available in the DACdocumentationpublic directory.
To define an External-Parameter Text data type parameter
Note: You can inactivate the parameter by selecting the Inactive check box.
Timestamp Interface
The External-Parameter Timestamp data type uses the DACTimestampParameterProducer interface to return timestamp values. DAC formats the timestamp during the ETL process according to your specifications.
The DACTimestampParameterProducer interface is as follows:
Note: DAC forces DACTimestampParameterProducer to implement the finalize method.
Defining an External-Parameter Timestamp Type Parameter
Follow this procedure to define an External-Parameter Timestamp type parameter.
Note: Producer code examples are available in the DACdocumentationpublic directory.
To define an External-Parameter Timestamp data type parameter
Note: You can inactivate the parameter by selecting the Inactive check box.
Defining a Multi-Parameter Type Parameter
The Multi-Parameter data types enable you to define parameters that return string or timestamp values as name-value pairs.
This procedure applies to parameters defined at both the source system and task levels.
To define a Multi-Parameter Text or Timestamp data type parameter
Note: You can inactivate the parameter by selecting the Inactive check box.
Defining a JavaScript Type Parameter
Follow this procedure to define a parameter using the JavaScript data type. This procedure applies to parameters defined at both the source system and task levels.
To define a JavaScript data type parameter
Note: You can inactivate the parameter by selecting the Inactive check box.
Defining a Global External Parameter
The Global External Multi-Parameter data types enable you to define parameters that return string or timestamp values as name-value pairs.
To define a parameter using the Global Multi-Parameter Text data type
![]()
A parameter file is a file that contains a list of initialization parameters and a value for each parameter. You specify initialization parameters in a parameter file that reflect your particular installation. Oracle supports the following two types of parameter files:
Server Parameter Files
A server parameter file is a binary file that acts as a repository for initialization parameters. The server parameter file can reside on the machine where the Oracle database server executes. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup.
Initialization Parameter Files
An initialization parameter file is a text file that contains a list of initialization parameters. The file should be written in the client's default character set.
The following are sample entries in an initialization parameter file:
The name of the initialization parameter file varies depending on the operating system. For example, it can be in mixed case or lowercase, or it can have a logical name or a variation of the name
init.ora . Also supplied is an initdw.ora file, which contains suggested parameter settings for data warehouses and data marts. The database administrator can choose a different filename for the initialization parameter file.
Refer to your operating system-specific Oracle documentation for the default locations and filenames for initialization parameter files on your operating system. The initialization parameter file is read by the client-side tool used to start the server (such as SQL*Plus).
Sample initialization parameter files are provided on the Oracle distribution medium for each operating system. A sample file is sufficient for initial use, but you will probably want to modify the file to tune the database for best performance. Any changes will take effect after you completely shut down and restart the instance.
Specifying Values in an Initialization Parameter File
This section describes several aspects of setting parameter values in an initialization parameter file.
Rules Governing Initialization Parameter Files
The following rules govern the specification of parameters in initialization parameter files:
Using Special Characters in Parameter Values
If a parameter value contains a special character, then the special character must be preceded by a backslash or the entire parameter value must be enclosed in quotation marks. For example, you can specify special characters using either of the following:
Table 1-1 lists the special characters you can use in initialization parameter files.
Table 1-1 Special Characters in Initialization Parameter Files
If a special character must be treated literally in an initialization parameter file, then it must either be preceded by the backslash character, or the entire string containing the special character must be enclosed in quotation marks.
Using the Escape Character
As described in 'Rules Governing Initialization Parameter Files', the backslash () can also signify a line continuation. If the backslash is followed by an alphanumeric character, then the backslash is treated as a normal character in the input. If it is not followed by an alphanumeric character, then the backslash is treated either as a backslash or as a continuation character.
Using Quotation Marks
Quotation marks can be nested in one of two ways. The first method is to double the quotation marks in the nested string. For example:
The second method is to alternate single and double quotation marks. For example:
![]() Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
December 2022
Categories |