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.
No comments:
Post a Comment