Informatica Interview Question Part -13

Partition Types Overview

Creating Partition Tables

To create a partition table gives the following statement

Create table sales (year number(4),

                    product varchar2(10),

                   amt number(10))

                   partition by range (year)

     (

          partition p1 values less than (1992) ,

          partition p2 values less than (1993),

          partition p5 values less than (MAXVALUE)

    );

The following example creates a table with list partitioning

Create table customers (custcode number(5),

                  Name varchar2(20),

                  Addr varchar2(10,2),

                  City varchar2(20),

                  Bal number(10,2))

     Partition by list (city),

Partition north_India values (‘DELHI’,’CHANDIGARH’),

Partition east_India values (‘KOLKOTA’,’PATNA’),

Partition south_India values (‘HYDERABAD’,’BANGALORE’,

                               ’CHENNAI’),

Partition west India values (‘BOMBAY’,’GOA’);

alter table sales add partition p6 values less than (1996);

alter table customers add partition central_India values 

(‘BHOPAL’,’NAGPUR’);SSS

Alter table sales drop partition p5;

Alter table sales merge partition p2 and p3 into

partition p23;

The following statement adds a new set of cities ('KOCHI', 'MANGALORE') to an existing partition list.

ALTER TABLE customers

   MODIFY PARTITION south_india

      ADD VALUES ('KOCHI', 'MANGALORE');

The statement below drops a set of cities (‘KOCHI' and 'MANGALORE') from an existing partition value list.

ALTER TABLE customers

   MODIFY PARTITION south_india

      DROP VALUES (‘KOCHI’,’MANGALORE’);

SPLITTING PARTITIONS

You can split a single partition into two partitions. For example to split the partition p5 of sales table into two partitions give the following command.

Alter table sales split partition p5 into

  (Partition p6 values less than (1996),

   Partition p7 values less then (MAXVALUE));

TRUNCATING PARTITON

Truncating a partition will delete all rows from the partition.

To truncate a partition give the following statement

Alter table sales truncate partition p5;

LISTING INFORMATION ABOUT PARTITION TABLES

To see how many partitioned tables are there in your schema give the following statement

Select * from user_part_tables;

To see on partition level partitioning information

Select * from user_tab_partitions;

TASKS

The Workflow Manager contains many types of tasks to help you build workflows and worklets. We can create reusable tasks in the Task Developer.

Types of tasks:

Task Type	Tool where task can be created	Reusable or not
Session	Task Developer	Yes
Email	Workflow Designer	Yes
Command	Worklet Designer	Yes
Event-Raise	Workflow Designer	No
Event-Wait	Worklet Designer	No
Timer		No
Decision		No
Assignment		No
Control		No

SESSION TASK

A session is a set of instructions that tells the Power Center Server how and when to move data from sources to targets.

To run a session, we must first create a workflow to contain the Session task.

We can run as many sessions in a workflow as we need. We can run the Session tasks sequentially or concurrently, depending on our needs.

The Power Center Server creates several files and in-memory caches depending on the transformations and options used in the session.

EMAIL TASK

The Workflow Manager provides an Email task that allows us to send email during a workflow.

Created by Administrator usually and we just drag and use it in our mapping.

Steps:

1.   In the Task Developer or Workflow Designer, choose Tasks-Create.

2.   Select an Email task and enter a name for the task. Click Create.

3.   Click Done.

4.   Double-click the Email task in the workspace. The Edit Tasks dialog box appears.

5.   Click the Properties tab.

6.   Enter the fully qualified email address of the mail recipient in the Email User Name field.

7.   Enter the subject of the email in the Email Subject field. Or, you can leave this field blank.

8.   Click the Open button in the Email Text field to open the Email Editor.

9.   Click OK twice to save your changes.

Example: To send an email when a session completes:

Steps:

1.   Create a workflow wf_sample_email

2.   Drag any session task to workspace.

3.   Edit Session task and go to Components tab.

4.   See On Success Email Option there and configure it.

5.   In Type select reusable or Non-reusable.

6.   In Value, select the email task to be used.

7.   Click Apply -> Ok.

8.   Validate workflow and Repository -> Save

9.   We can also drag the email task and use as per need.

10.                We can set the option to send email on success or failure in components tab of a session task.

COMMAND TASK

The Command task allows us to specify one or more shell commands in UNIX or DOS commands in Windows to run during the workflow.

For example, we can specify shell commands in the Command task to delete reject files, copy a file, or archive target files.

Ways of using command task:

1. Standalone Command task: We can use a Command task anywhere in the workflow or worklet to run shell commands.

2. Pre- and post-session shell command: We can call a Command task as the pre- or post-session shell command for a Session task. This is done in COMPONENTS TAB of a session. We can run it in Pre-Session Command or Post Session Success Command or Post Session Failure Command. Select the Value and Type option as we did in Email task.

Example: to copy a file sample.txt from D drive to E.

Command: COPY D:\sample.txt E:\ in windows

Steps for creating command task:

1.   In the Task Developer or Workflow Designer, choose Tasks-Create.

2.   Select Command Task for the task type.

3.   Enter a name for the Command task. Click Create. Then click done.

4.   Double-click the Command task. Go to commands tab.

5.   In the Commands tab, click the Add button to add a command.

6.   In the Name field, enter a name for the new command.

7.   In the Command field, click the Edit button to open the Command Editor.

8.   Enter only one command in the Command Editor.

9.   Click OK to close the Command Editor.

10.                Repeat steps 5-9 to add more commands in the task.

11.                Click OK.

Steps to create the workflow using command task:

1.   Create a task using the above steps to copy a file in Task Developer.

2.   Open Workflow Designer. Workflow -> Create -> Give name and click ok.

3.   Start is displayed. Drag session say s_m_Filter_example and command task.

4.   Link Start to Session task and Session to Command Task.

5.   Double click link between Session and Command and give condition in editor as

6.   $S_M_FILTER_EXAMPLE.Status=SUCCEEDED

7.   Workflow-> Validate

8.   Repository –> Save

WORKING WITH EVENT TASKS

We can define events in the workflow to specify the sequence of task execution.

Types of Events:

Pre-defined event: A pre-defined event is a file-watch event. This event Waits for a specified file to arrive at a given location.

User-defined event: A user-defined event is a sequence of tasks in the Workflow. We create events and then raise them as per need.

Steps for creating User Defined Event:

1.   Open any workflow where we want to create an event.

2.   Click Workflow-> Edit -> Events tab.

3.   Click to Add button to add events and give the names as per need.

4.   Click Apply -> Ok. Validate the workflow and Save it.

Types of Events Tasks:

EVENT RAISE: Event-Raise task represents a user-defined event. We use this task to raise a user defined event.

EVENT WAIT: Event-Wait task waits for a file watcher event or user defined event to occur before executing the next session in the workflow.

Example1: Use an event wait task and make sure that session s_filter_example runs when abc.txt file is present in D:\FILES folder.

Steps for creating workflow:

1.   Workflow -> Create -> Give name wf_event_wait_file_watch -> Click ok.

2.   Task -> Create -> Select Event Wait. Give name. Click create and done.

3.   Link Start to Event Wait task.

4.   Drag s_filter_example to workspace and link it to event wait task.

5.   Right click on event wait task and click EDIT -> EVENTS tab.

6.   Select Pre Defined option there. In the blank space, give directory and filename to watch. Example: D:\FILES\abc.tct

7.   Workflow validate and Repository Save.

Example 2: Raise a user defined event when session s_m_filter_example succeeds. Capture this event in event wait task and run session S_M_TOTAL_SAL_EXAMPLE

Steps for creating workflow:

1.   Workflow -> Create -> Give name wf_event_wait_event_raise -> Click ok.

2.   Workflow -> Edit -> Events Tab and add events EVENT1 there.

3.   Drag s_m_filter_example and link it to START task.

4.   Click Tasks -> Create -> Select EVENT RAISE from list. Give name

5.   ER_Example. Click Create and then done. Link ER_Example to s_m_filter_example.

6.   Right click ER_Example -> EDIT -> Properties Tab -> Open Value for User Defined Event and Select EVENT1 from the list displayed. Apply -> OK.

7.   Click link between ER_Example and s_m_filter_example and give the condition $S_M_FILTER_EXAMPLE.Status=SUCCEEDED

8.   Click Tasks -> Create -> Select EVENT WAIT from list. Give name EW_WAIT. Click Create and then done.

9.   Link EW_WAIT to START task.

10.                Right click EW_WAIT -> EDIT-> EVENTS tab.

11.                Select User Defined there. Select the Event1 by clicking Browse Events button.

12.                Apply -> OK.

13.                Drag S_M_TOTAL_SAL_EXAMPLE and link it to EW_WAIT.

14.                Mapping -> Validate

15.                Repository -> Save.

Run workflow and see.

TIMER TASK

The Timer task allows us to specify the period of time to wait before the Power 

Center Server runs the next task in the workflow. The Timer task has two types of settings:

Absolute time: We specify the exact date and time or we can choose a user-defined workflow variable to specify the exact time. The next task in workflow will run as per the date and time specified.

Relative time: We instruct the Power Center Server to wait for a specified period of time after the Timer task, the parent workflow, or the top-level workflow starts.

Example: Run session s_m_filter_example relative to 1 min after the timer task.

Steps for creating workflow:

1.   Workflow -> Create -> Give name wf_timer_task_example -> Click ok.

2.   Click Tasks -> Create -> Select TIMER from list. Give name TIMER_Example. Click Create and then done.

3.   Link TIMER_Example to START task.

4.   Right click TIMER_Example-> EDIT -> TIMER tab.

5.   Select Relative Time Option and Give 1 min and Select ‘From start time of this task’ Option.

6.   Apply -> OK.

7.   Drag s_m_filter_example and link it to TIMER_Example.

8.   Workflow-> Validate and Repository -> Save.

DECISION TASK

The Decision task allows us to enter a condition that determines the execution of the workflow, similar to a link condition.

The Decision task has a pre-defined variable called $Decision_task_name.condition that represents the result of the decision condition.

The Power Center Server evaluates the condition in the Decision task and sets the pre-defined condition variable to True (1) or False (0).

We can specify one decision condition per Decision task.

Example: Command Task should run only if either s_m_filter_example or

S_M_TOTAL_SAL_EXAMPLE succeeds. If any of s_m_filter_example or

S_M_TOTAL_SAL_EXAMPLE fails then S_m_sample_mapping_EMP should run.

Steps for creating workflow:

1.   Workflow -> Create -> Give name wf_decision_task_example -> Click ok.

2.   Drag s_m_filter_example and S_M_TOTAL_SAL_EXAMPLE to workspace and link both of them to START task.

3.   Click Tasks -> Create -> Select DECISION from list. Give name DECISION_Example. Click Create and then done. Link DECISION_Example to both s_m_filter_example and S_M_TOTAL_SAL_EXAMPLE.

4.   Right click DECISION_Example-> EDIT -> GENERAL tab.

5.   Set ‘Treat Input Links As’ to OR. Default is AND. Apply and click OK.

6.   Now edit decision task again and go to PROPERTIES Tab. Open the Expression editor by clicking the VALUE section of Decision Name attribute and enter the following condition: $S_M_FILTER_EXAMPLE.Status = SUCCEEDED OR $S_M_TOTAL_SAL_EXAMPLE.Status = SUCCEEDED

7.   Validate the condition -> Click Apply -> OK.

8.   Drag command task and S_m_sample_mapping_EMP task to workspace and link them to DECISION_Example task.

9.   Double click link between S_m_sample_mapping_EMP & DECISION_Example & give the condition: $DECISION_Example.Condition = 0. Validate & click OK.

10.                Double click link between Command task and DECISION_Example and give the condition: $DECISION_Example.Condition = 1. Validate and click OK.

11.                Workflow Validate and repository Save.

Run workflow and see the result.

CONTROL TASK

We can use the Control task to stop, abort, or fail the top-level workflow or the parent workflow based on an input link condition.

A parent workflow or worklet is the workflow or worklet that contains the Control task.

We give the condition to the link connected to Control Task.

Control Option	Description
Fail Me	Fails the control task.
Fail Parent	Marks the status of the WF or worklet that contains the Control task as failed.
Stop Parent	Stops the WF or worklet that contains the Control task.
Abort Parent	Aborts the WF or worklet that contains the Control task.
Fail Top-Level WF	Fails the workflow that is running.
Stop Top-Level WF	Stops the workflow that is running.
Abort Top-Level WF	Aborts the workflow that is running.

Example: Drag any 3 sessions and if anyone fails, then Abort the top level workflow.

Steps for creating workflow:

1.   Workflow -> Create -> Give name wf_control_task_example -> Click ok.

2.   Drag any 3 sessions to workspace and link all of them to START task.

3.   Click Tasks -> Create -> Select CONTROL from list. Give name cntr_task.

4.   Click Create and then done.

5.   Link all sessions to the control task cntr_task.

6.   Double click link between cntr_task and any session say s_m_filter_example and give the condition: $S_M_FILTER_EXAMPLE.Status = SUCCEEDED.

7.   Repeat above step for remaining 2 sessions also.

8.   Right click cntr_task-> EDIT -> GENERAL tab. Set ‘Treat Input Links As’ to OR. Default is AND.

9.   Go to PROPERTIES tab of cntr_task and select the value ‘Fail top level

10.                Workflow’ for Control Option. Click Apply and OK.

11.                Workflow Validate and repository Save.

Run workflow and see the result.

ASSIGNMENT TASK

The Assignment task allows us to assign a value to a user-defined workflow variable.

See Workflow variable topic to add user defined variables.

·         To use an Assignment task in the workflow, first create and add the

·         Assignment task to the workflow. Then configure the Assignment task to assign values or expressions to user-defined variables.

·         We cannot assign values to pre-defined workflow.

Steps to create Assignment Task:

1.   Open any workflow where we want to use Assignment task.

2.   Edit Workflow and add user defined variables.

3.   Choose Tasks-Create. Select Assignment Task for the task type.

4.   Enter a name for the Assignment task. Click Create. Then click done.

5.   Double-click the Assignment task to open the Edit Task dialog box.

6.   On the Expressions tab, click Add to add an assignment.

7.   Click the Open button in the User Defined Variables field.

8.   Select the variable for which you want to assign a value. Click OK.

9.   Click the Edit button in the Expression field to open the Expression Editor.

10.                Enter the value or expression you want to assign.

11.                Repeat steps 7-10 to add more variable assignments as necessary.

12.                Click OK.