Materialized View

In data warehouses, materialized views can be used to pre compute and store aggregated data such as the sum of sales. Materialized views in these environments are typically referred to as summaries, because they store summarized data. They can also be used to pre compute joins with or without aggregations. A materialized view eliminates the overhead associated with expensive joins or aggregations for a large or important class of queries.

Need for Materialized View

• It increases the speed of queries on very large databases. Queries to large databases often involve joins between tables or aggregations such as SUM, or both. These operations are very expensive in terms of time and processing power. The type of materialized view that is created determines how the materialized view can be refreshed and used by query rewrite.

• It can be used in a number of ways and almost identical syntax can be used to perform a number of roles.

• It improves query performance by pre calculating expensive join and aggregation operations on the database prior to execution time and storing the results in the database. In general, rewriting queries to use materialized views rather than detail table’s results in a significant performance gain.

Where We Can Use Them

In essence, there are three situations in which we might want to make the same set of information available in multiple places.

The first of these is in summary tables. We provide summary tables in a data warehouse because not every query is going to need the very detailed information that our fact tables may provide, and because allowing high‑level queries to scan a much smaller pre‑aggregated data set reduces our I/O burden.

The second situation is in dimension tables. The dimension tables contain lists of all the values in the key columns of the fact tables, and maybe some more information on those values such as descriptive text for codes.

The third situation is in the fact tables themselves. I will admit this is a bit of a stretch for many people, but the fact tables are generally the cleansed and transformed versions of some kind of source data set. That data set may be a set of OLTP database tables, or a set of flat files, but the fact tables are still in some ways a duplication of the original data set.

Types of Materialized Views

• Materialized Views with Joins and Aggregates
• Single-Table Aggregate Materialized Views
• Materialized Views Containing Only Joins

Materialized Views with Joins and Aggregates

In data warehouses, materialized views would normally contain one of the aggregates. For fast refresh to be possible, the SELECT list must contain the entire GROUP BY columns (if present), and may contain one or more aggregate functions. The aggregate function must be one of: SUM, COUNT(x), COUNT (*), COUNT (DISTINCT x), AVG, VARIANCE, STDDEV, MIN, and MAX, and the expression to be aggregated can be any SQL value expression.

If a materialized view contains joins and aggregates, then it cannot be fast refreshed using a materialized view log. Therefore, for a fast refresh to be possible, only new data can be added to the detail tables and it must be loaded using the direct path method

Example:

CREATE MATERIALIZED VIEW store_sales_mv

  PCTFREE 0 TABLESPACE mviews

  STORAGE (initial 16k next 16k pctincrease 0)

  BUILD DEFERRED

  REFRESH COMPLETE ON DEMAND

  ENABLE QUERY REWRITE

  AS

  SELECT

   s.store_name,

     SUM(dollar_sales) AS sum_dollar_sales

      FROM store s, fact f

      WHERE f.store_key = s.store_key

      GROUP BY s.store_name;

This example creates a materialized view store_sales_mv that computes the sum of sales by store. It is derived by joining the tables store and fact on the column store_key. The materialized view does not initially contain any data because the build method is DEFERRED. A complete refresh is required for the first refresh of a build deferred materialized view. When it is refreshed, a complete refresh is performed and, once populated; this materialized view can be used by query rewrite. 

Single-Table Aggregate Materialized Views

A materialized view that contains one or more aggregates (SUM, AVG, VARIANCE, STDDEV, and COUNT) and a GROUP BY clause may be based on a single table. The aggregate function can involve an expression on the columns such as SUM(a*b). If this materialized view is to be incrementally refreshed, then a materialized view log must be created on the detail table with the INCLUDING NEW VALUES option, and the log must contain all columns referenced in the materialized view query definition.

Example:

CREATE MATERIALIZED VIEW log on fact   

  with rowid (store_key, time_key, dollar_sales, unit_sales)  

  including new values;  

CREATE MATERIALIZED VIEW sum_sales  

  PARALLEL

  BUILD IMMEDIATE  

  REFRESH FAST ON COMMIT  

  AS  

  SELECT f.store_key, f.time_key,  

         COUNT(*) AS count_grp,  

  SUM(f.dollar_sales) AS sum_dollar_sales,  

        COUNT(f.dollar_sales) AS count_dollar_sales,  

  SUM(f.unit_sales) AS sum_unit_sales,  

        COUNT(f.unit_sales) AS count_unit_sales 

  FROM fact f  

  GROUP BY f.store_key, f.time_key;

In this example, a materialized view has been created which contains aggregates on a single table. Because the materialized view log has been created, the materialized view is fast refreshable. If DML is applied against the fact table, then, when the commit is issued, the changes will be reflected in the materialized view. 

Materialized Views Containing Only Joins

Materialized views may contain only joins and no aggregates, such as in the next example where a materialized view is created which joins the fact table to the store table. The advantage of creating this type of materialized view is that expensive joins will be pre calculated.

Incremental refresh for a materialized view containing only joins is possible after any type of DML to the base tables (direct load or conventional INSERT, UPDATE, or DELETE). It can be defined to be refreshed ON COMMIT or ON DEMAND. If it is ON COMMIT, the refresh is performed at commit time of the transaction that does DML on the materialized view's detail table.

If you specify REFRESH FAST, Oracle performs further verification of the query definition to ensure that fast refresh can be performed if any of the detail tables change. These additional checks include:

• A materialized view log must be present for each detail table.
• The rowids of all the detail tables must appear in the SELECT list of the materialized view query definition.
• If there are outer joins, unique constraints must exist on the join columns of the inner table.

For example, if you are joining the fact and a dimension table and the join is an outer join with the fact table being the outer table, there must exist unique constraints on the join columns of the dimension table.

If some of the above restrictions are not met, then the materialized view should be created as REFRESH FORCE to take advantage of incremental refresh when it is possible. If the materialized view is created as ON COMMIT, Oracle performs all of the fast refresh checks. If one of the tables did not meet all of the criteria, but the other tables did, the materialized view would still be incrementally refreshable with respect to the other tables for which all the criteria are met.

In a data warehouse star schema, if space is at a premium, you can include the rowid of the fact table only because this is the table that will be most frequently updated and the user can specify the FORCE option when the materialized view is created.

A materialized view log should contain the rowid of the master table. It is not necessary to add other columns.

To speed up refresh, it is recommended that the user create indexes on the columns of the materialized view that stores the rowids of the fact table.

Example:

CREATE MATERIALIZED VIEW LOG ON fact

  WITH ROWID;

CREATE MATERIALIZED VIEW LOG ON time

  WITH ROWID;

CREATE MATERIALIZED VIEW LOG ON store

  WITH ROWID;

CREATE MATERIALIZED VIEW detail_fact_mv 

       PARALLEL

       BUILD IMMEDIATE

       REFRESH FAST

       AS

       SELECT

    f.rowid "fact_rid", t.rowid "time_rid", s.rowid "store_rid",

      s.store_key, s.store_name, f.dollar_sales,

      f.unit_sales, f.time_key

       FROM fact f, time t, store s 

       WHERE f.store_key = s.store_key(+) AND 

       f.time_key = t.time_key(+);

In this example, in order to perform a REFRESH FAST, unique constraints should exist on s.store_keyand t.time_key. It is also recommended that indexes be created on the columns fact_rid, time_rid, and store_rid, as illustrated below, which will improve the performance of refresh. 

Slowly Changing Dimensions

Slowly Changing Dimensions describe the behavior of changing information within the dimension tables of a data warehouse. Business users may or may not decide to preserve history in the data warehouse tables.

Type 1: Overwriting the old values in the dimension record and thereby losing the ability to track the old history.

Type 2: Creating an additional dimension record at the time of the change with the new attribute values, and thereby segmenting history very accurately between the old description and the new description.

Type 3: Creating new “current” fields within the original dimension record to record the new attribute values, while keeping the original attribute values as well, thereby being able to describe history both forward and backward from the change either in terms of the original attribute values or in terms of the current attribute values.

DataStage easily handles all three types of Slowly Changing Dimensions within the DataStage transform. Because of this simplicity, no special features or “gizmos” are required for the basic functionality and the “road is clear” to add the more complex functionality that is often required for other transformations.

SCD’s can require a number of relatively complex data management decisions. DataStage affords the luxury of being able to implement complex strategies for handling not only the first three SCD’s, but also other SCD processes that have been postulated in recent data warehousing literature.

EXAMPLE

We will analyze slowly changing dimensions through a simple, practical example. Three records need to be loaded into a data warehouse dimension table. The first two update old records; one has never been loaded. The data modeler mixes all three versions of SCD’s throughout the dimension. He said last week that if there is an SCD2, don’t even touch the old records for SCD 1’s or 3’s. This week he has changed his mind and wants old 1’s and 3’s, but not the old values of 1’s and 3’s, changed.

You start a simple DataStage job and add the stages shown below. It has a source stage for your three new records, a transform stage for the logic and lookups, and a write back to the database dimension table (Insert or update). The job looks like this:

 

You take a few seconds to point the Source_Records stage at the incoming file and see the three records. DataStage View Data provides quick insight into the source while ensuring that you can connect. The records in this example have descriptive variables for each type of SCD. Some have new values and some have old.

You point the Dimension_Lookup stage at your database table. This happens to be an Oracle native OCI database stage; it could be native connections to DB2, Informix, Sybase, etc., ODBC, or fast, flexible DataStage hash tables. View Data tests the connection and shows the first 1,000 (in this case 2) records at the same time.

HOW TO DO THE THREE SCD’s

There are a lot of options in maintaining SCD Types 1, 2, and 3. End users, data administrators, and business requirements all may dictate one or multiple actions, even for the same data. The dimension record is identified in four ways:

Natural Key: The natural key comes from the OLTP system. It is the lookup by the incoming input record.

Dim Key Artificial: A surrogate key assigned by the DataStage routine for each new record. Used as a unique key and parent key for fact tables.

Dim Key Art Orig: Tracks the original surrogate key for the particular dimension record. Used to select or update all of the dimension records as a set.

Current Flag: A simple flag to denote the most current dimension record, including the lookup.

Your job demonstrates a simple form of dimensional maintenance. A data input (a sequential file in this case--database connections, FTP, other transformation process, or pipes would be just as easy) inputs new or updated information into the dimension transformation. You drag-and-drop to join the stages with data links:

1.      Dimension Source Link: A data input record is read from the source.

2.      Old_Dimension_Lookup Link: A lookup (native database connection in this case) seeks an existing dimension record with the same natural key as the input.

3.      Type_3_or_1_Update Only: If no Type 2 SCD variables are changed and there are changes to Type 1 or Type 3 SCD’s, send the record down this Update link. No CURRENT_FLAGs change.

4.      Type_2_Current_Flag_Update_to_N: If Type 2 SCD variables are changed (causing an insert of a new CURRENT record), update the existing records to change the existing CURRENT flags to “N”. We have chosen to also update any Type 1 and current Type 3 SCD’s in old records for consistency.

5.      Type_2_or_New_Insert Link: If no existing dimension record is found or if one changed field is a Type 2 SCD, send the record down this Insert link. Mark the new record’s CURRENT_FLAG to “Y”

6.      Database_Errors: If a database error is detected in the Type_2_or_New_Insert Link, save the entire record with the surrogate key and the database error codes in a flat file.

The lookup work is performed in the Process_SCDs Transform stage shown below. The designer’s transformation job is made simple by separating the logic of what information is stored and what action is to be taken. Step 1: Mapping, Step 2: database logic.

1.      How do the Columns map from source to target? First, the columns are mapped from the sources on the left to the targets on the right. The incoming source (Dimension_Source link) looks up the Old Dimension record using the Natural_Key. The Derivations on the right show from which source and column each field is taken to send out the Output Links to the target. It will either Update or Insert a new record depending upon whether a lookup record is found and which values have changed.

This mapping is very visual and shows replacement by a Type 1 SCD and Type 3 SCD along with the storage of the old value for the Type 3 SCD in the variable SCD_3_LastVal. In the case of a Type 2 SCD, all columns for the Insert are populated from the source record except for an automatic new key value for the dimension table. Only one or two SQL statements are generated for any particular record.

2. Which columns are SCD’s?  Stage variables hold values within the transform stage for further processing. Stage variables can be used to save information from record to record, to calculate a value used in many later calculations, or to serve as a local accumulator. Stage variables easily provide the logic for what to do with the SCD. Because stage variables  support all of the complex logic of DataStage, more complex logic than a simple equality test can be used to determine whether an SCD variable has actually changed. The logic for the four variables is shown below:

Stage Variable	Definition
Type2	Any SCD 2 variable change?
NewRecord	Was a lookup record not found?
Type_3_or_1_Update	Did an SCD 3 or SCD1 variable change?
NewKeyNeeded	A simple test determines whether to ask for a new surrogate key. In this way, surrogate keys are not wasted and DBA’s researching missing record keys are not confused.

Other variables are added into the Type2 or Type 3_or_1_Update OR clauses depending upon the treatment desired. If the record is found and all old and new SCD values are equal, no action is taken.

3. Update vs. Insert: Finally, the hierarchy between INSERTs and UPDATEs needs to be detailed. The DataStage Constraint logic details the simple logic for this process:

The database stage displays update actions for modifying the database either inserting, updating, truncating, insert then update, and so forth. An extra link is included for database errors. For added database integrity, the developer may decide to use DataStage Transaction Control to ensure that either all database operations are applied or all operations are rolled back within a single transaction for each record.

Running the Slowly Changing Dimension Program

The input data, original dimension, and final dimension table data looks as below. The first record is replaced by the third record as an SCD_2. The second record is changed in place by the SCD_1 and SCD_3 values, the third is the replacement for the SCD_2, and the fourth is a brand new record.

During running, the DataStage Debugger shows the values of each variable on a link-by-link basis.

That’s it! You have built a powerful dimensional maintenance process that can be crafted to the complex logic that all data warehouses will need without going outside of DataStage.

Simple Dimension Maintenance Programs May not Perform

Now that the process for Slowly Changing Dimensions has been built, issues of production support and performance must be considered. DataStage jobs are easily tuned for production throughput and power. The developer can easily move links to other stage types to take advantage of several DataStage features that increase power and decrease demands on the database.

The changes are detailed in the job below. The steps to develop it are shown:

Limit database I/O processes for dimensional lookups: The dimension table itself is loaded into a high-performance hash table local to the DataStage ETL server. Only one SELECT is sent against the database to select records into the index. Because the hash index is persistent, this step may be performed at any time before the main job runs or as a dependent step for this job.

Use the index, not the table: UPDATES and INSERTS are performed on the index, not on the dimension table itself. The original job must have INSERTed records available for later records with the same key (to avoid inserting duplicate records), a highly inefficient process for a database insert process.

Load the dimension in bulk: A rapidly-performing bulk load database process is used to update the dimension table after the process has run. Separate bulk load processes will update or insert different sets of records. This is an optional step; DataStage can also maintain production speed by loading the dimension table in parallel while the ETL process continues to use the indexes for fact records.

Build restart or reversing capabilities: The ability to restart the job by the row number can be built into DataStage job parameters. When the job is run, Job Parameter values can be changed in the GUI Director, Scheduler, or from the operating system command line.

Conclusion

DataStage dimension maintenance is very straightforward. Working with SCD’s is a simple, three-step process within the DataStage development environment.

The power of the basic DataStage process makes the use of “gizmos” to manage SCD’s superfluous and actually counterproductive when the developer wants to process slowly changing dimensions “out of the box” using other alternatives. A recent customer put it succinctly:  “It would have taken a huge amount of effort to do what we wanted when the Wizard only did what it wanted to!”

Some of the ways to do Type 2 SCDs include updating the key in the dimension and changing the fact table.  An alternative is to keep the key in the fact table and add and adjust the keys in the dimension  (There are multiple ways to do this).  Alternatives may be chosen because of volume, end user requirements/preference, the source of the dimension information, etc.

DataStage jobs are also easily modified from basic prototype jobs to robust production applications. Features such as Viewing Data in sources and targets and always-available Inline Debugging are invaluable in making these applications production-ready.

creating a unique counter in DataStage jobs

This entry describes various ways of creating a unique counter in DataStage jobs.

A parallel job has a surrogate key stage that creates unique IDs, however it is limited in that it does not support conditional code and it may be more efficient to add a counter to an existing transformer rather than add a new stage.

In a server job there are a set of key increment routines installed in the routine SDK samples that offer a more complex counter that remembers values between job executions.

The following section outlines a transformer only technique.

Steps

In a DataStage job the easiest way to create a counter is within the Transformer stage with a Stage Variable.

svMyCounter = svMyCounter + 1

This simple counter adds 1 each time a row is processed.

The counter can be given a seed value by passing a value in as a job parameter and setting the initial value of svMyCounter to that job parameter.

In a parallel job this simple counter will create duplicate values on each node as the transformer is split into parallel instances. It can be turned into a unique counter by using special parallel macros.

1.     Create a stage variable for the counter, eg. SVCounter.

2.     At the Stage Properties form set the Initial Value of the Stage Variable to "@PARTITIONNUM - @NUMPARTITIONS + 1".

3.     Set the derivation of the stage variable to "svCounter + @NUMPARTITIONS". You can embed this in an IF statement if it is a conditional counter.

Each instance will start at a different number, eg. -1, -2, -3, -4. When the counter is incremented each instance is increment by the number of partitions, eg. 4. This gives us a sequence in instance 1 of 1, 5, 9, 13... Instance 2 is 2, 6, 10, 14... etc.

Remember this method only works if your data is evenly balanced i.e. equal number of rows going through each partition. Alternative syntax is:

@INROWNUM * @NUMPARTITIONS + @PARTITIONNUM 

Performance Analysis of Various stages in DataStag

Sequential File Stage -
The sequential file Stage is a file Stage. It is the most common I/O Stage used in a DataStage Job. It is used to read data from or write data to one or more flat Files. It can have only one input link or one Output link .It can also have one reject link. While handling huge volumes of data, this Stage can itself become one of the major bottlenecks as reading and writing from this Stage is slow.Sequential files should be used in following conditionsWhen we are reading a flat file (fixed width or delimited) from UNIX environment which is FTPed from some external systemsWhen some UNIX operations has to be done on the file Don’t use sequential file for intermediate storage between jobs. It causes performance overhead, as it needs to do data conversion before writing and reading from a UNIX file.In order to have faster reading from the Stage the number of readers per node can be increased (default value is one).

Data Set Stage :
The Data Set is a file Stage, which allows reading data from or writing data to a dataset. This Stage can have a single input link or single Output link. It does not support a reject link. It can be configured to operate in sequential mode or parallel mode. DataStage parallel extender jobs use Dataset to store data being operated on in a persistent form.Datasets are operating system files which by convention has the suffix .dsDatasets are much faster compared to sequential files.Data is spread across multiple nodes and is referred by a control file.Datasets are not UNIX files and no UNIX operation can be performed on them.Usage of Dataset results in a good performance in a set of linked jobs.They help in achieving end-to-end parallelism by writing data in partitioned form and maintaining the sort order.

Lookup Stage –
A Look up Stage is an Active Stage. It is used to perform a lookup on any parallel job Stage that can output data. The lookup Stage can have a reference link, single input link, single output link and single reject link.Look up Stage is faster when the data volume is less.It can have multiple reference links (if it is a sparse lookup it can have only one reference link)The optional reject link carries source records that do not have a corresponding input lookup tables.Lookup Stage and type of lookup should be chosen depending on the functionality and volume of data.Sparse lookup type should be chosen only if primary input data volume is small.If the reference data volume is more, usage of Lookup Stage should be avoided as all reference data is pulled in to local memory

Join Stage :
Join Stage performs a join operation on two or more datasets input to the join Stage and produces one output dataset. It can have multiple input links and one Output link.There can be 3 types of join operations Inner Join, Left/Right outer Join, Full outer join. Join should be used when the data volume is high. It is a good alternative to the lookup stage and should be used when handling huge volumes of data.Join uses the paging method for the data matching.

Merge Stage :
The Merge Stage is an active Stage. It can have multiple input links, a single output link, and it supports as many reject links as input links. The Merge Stage takes sorted input. It combines a sorted master data set with one or more sorted update data sets. The columns from the records in the master and update data sets are merged so that the output record contains all the columns from the master record plus any additional columns from each update record. A master record and an update record are merged only if both of them have the same values for the merge key column(s) that you specify. Merge key columns are one or more columns that exist in both the master and update records. Merge keys can be more than one column. For a Merge Stage to work properly master dataset and update dataset should contain unique records. Merge Stage is generally used to combine datasets or files.

Sort Stage :
The Sort Stage is an active Stage. The Sort Stage is used to sort input dataset either in Ascending or Descending order. The Sort Stage offers a variety of options of retaining first or last records when removing duplicate records, Stable sorting, can specify the algorithm used for sorting to improve performance, etc. Even though data can be sorted on a link, Sort Stage is used when the data to be sorted is huge.When we sort data on link ( sort / unique option) once the data size is beyond the fixed memory limit , I/O to disk takes place, which incurs an overhead. Therefore, if the volume of data is large explicit sort stage should be used instead of sort on link.Sort Stage gives an option on increasing the buffer memory used for sorting this would mean lower I/O and better performance.

Transformer Stage :
The Transformer Stage is an active Stage, which can have a single input link and multiple output links. It is a very robust Stage with lot of inbuilt functionality. Transformer Stage always generates C-code, which is then compiled to a parallel component. So the overheads for using a transformer Stage are high. Therefore, in any job, it is imperative that the use of a transformer is kept to a minimum and instead other Stages are used, such as:Copy Stage can be used for mapping input links with multiple output links without any transformations. Filter Stage can be used for filtering out data based on certain criteria. Switch Stage can be used to map single input link with multiple output links based on the value of a selector field. It is also advisable to reduce the number of transformers in a Job by combining the logic into a single transformer rather than having multiple transformers .

Funnel Stage –
Funnel Stage is used to combine multiple inputs into a single output stream. But presence of a Funnel Stage reduces the performance of a job. It would increase the time taken by job by 30% (observations). When a Funnel Stage is to be used in a large job it is better to isolate itself to one job. Write the output to Datasets and funnel them in new job. Funnel Stage should be run in “continuous” mode, without hindrance.

Overall Job Design :While designing DataStage Jobs care should be taken that a single job is not overloaded with Stages. Each extra Stage put in a Job corresponds to lesser number of resources available for every Stage, which directly affects the Jobs Performance. If possible, big jobs having large number of Stages should be logically split into smaller units. Also if a particular Stage has been identified to be taking lot of time in a job, like a transformer Stage having complex functionality with a lot of Stage variables and transformations, then the design of jobs could be done in such a way that this Stage is put in a separate job all together (more resources for the transformer Stage!!!). Also while designing jobs, care must be taken that unnecessary column propagation is not done. Columns, which are not needed in the job flow, should not be propagated from one Stage to another and from one job to the next. As far as possible, RCP (Runtime Column Propagation) should be disabled in the jobs. Sorting in a job should be taken care try to minimise number sorts in a job. Design a job in such a way as to combine operations around same sort keys, if possible maintain same hash keys. Most often neglected option is “don’t sort if previously sorted” in sort Stage, set this option to “true”. This improves the Sort Stage performance a great deal. In Transformer Stage “Preserve Sort Order” can be used to maintain sort order of the data and reduce sorting in the job.In a transformer minimum of Stage variables should be used. More the no of Stage variable lower is the performance. An overloaded transformer can choke the data flow and lead to bad performance or even failure of job at some point. In order to minimise the load on transformer we can Avoid some unnecessary function calls. For example to convert a varchar field with date value can be type cast into Date type by simple formatting the input value. We need not use StringToDate function, which is used to convert a String to Date type.Implicit conversion of data types.
Reduce the number of Stage variables used. It was observed in our previous project by removing 5 Stage variables and 6 function calls, runtime for the job was reduced from 2 hours to 1 hour 10 min (approximately) with 100 million records input.Try to balance load on transformers by sharing the transformations across existing transformers. This would ensure smooth flow of data.If you require type casting, renaming of columns or addition of new columns, use Copy or Modify Stages to achieve thisWhenever you have to use Lookups on large tables, look at the options such as unloading the lookup tables to datasets and using, user defined join SQL to reduce the look up volume with the help of temp tables, etc.The Copy stage should be used instead of a Transformer for simple operations including:o Job Design placeholder between stages o Renaming Columnso Dropping Columnso Implicit (default) Type Conversions The “upsert” works well if the data is sorted on the primary key column of the table which is being loaded. Or Determine , if the record already exists or not to have “Insert” and “Update” separately.It is sometimes possible to re-arrange the order of business logic within a job flow to leverage the same sort order, partitioning, and groupings. Don’t read from a Sequential File using SAME partitioning. Unless more than one source file is specified, this scenario will read the entire file into a single partition, making the entire downstream flow run sequentially (unless it is repartitioned)

Performance Analysis of Various stages in DataStag

Sequential File Stage -
The sequential file Stage is a file Stage. It is the most common I/O Stage used in a DataStage Job. It is used to read data from or write data to one or more flat Files. It can have only one input link or one Output link .It can also have one reject link. While handling huge volumes of data, this Stage can itself become one of the major bottlenecks as reading and writing from this Stage is slow.Sequential files should be used in following conditionsWhen we are reading a flat file (fixed width or delimited) from UNIX environment which is FTPed from some external systemsWhen some UNIX operations has to be done on the file Don’t use sequential file for intermediate storage between jobs. It causes performance overhead, as it needs to do data conversion before writing and reading from a UNIX file.In order to have faster reading from the Stage the number of readers per node can be increased (default value is one).

Data Set Stage :
The Data Set is a file Stage, which allows reading data from or writing data to a dataset. This Stage can have a single input link or single Output link. It does not support a reject link. It can be configured to operate in sequential mode or parallel mode. DataStage parallel extender jobs use Dataset to store data being operated on in a persistent form.Datasets are operating system files which by convention has the suffix .dsDatasets are much faster compared to sequential files.Data is spread across multiple nodes and is referred by a control file.Datasets are not UNIX files and no UNIX operation can be performed on them.Usage of Dataset results in a good performance in a set of linked jobs.They help in achieving end-to-end parallelism by writing data in partitioned form and maintaining the sort order.

Lookup Stage –
A Look up Stage is an Active Stage. It is used to perform a lookup on any parallel job Stage that can output data. The lookup Stage can have a reference link, single input link, single output link and single reject link.Look up Stage is faster when the data volume is less.It can have multiple reference links (if it is a sparse lookup it can have only one reference link)The optional reject link carries source records that do not have a corresponding input lookup tables.Lookup Stage and type of lookup should be chosen depending on the functionality and volume of data.Sparse lookup type should be chosen only if primary input data volume is small.If the reference data volume is more, usage of Lookup Stage should be avoided as all reference data is pulled in to local memory

Join Stage :
Join Stage performs a join operation on two or more datasets input to the join Stage and produces one output dataset. It can have multiple input links and one Output link.There can be 3 types of join operations Inner Join, Left/Right outer Join, Full outer join. Join should be used when the data volume is high. It is a good alternative to the lookup stage and should be used when handling huge volumes of data.Join uses the paging method for the data matching.

Merge Stage :
The Merge Stage is an active Stage. It can have multiple input links, a single output link, and it supports as many reject links as input links. The Merge Stage takes sorted input. It combines a sorted master data set with one or more sorted update data sets. The columns from the records in the master and update data sets are merged so that the output record contains all the columns from the master record plus any additional columns from each update record. A master record and an update record are merged only if both of them have the same values for the merge key column(s) that you specify. Merge key columns are one or more columns that exist in both the master and update records. Merge keys can be more than one column. For a Merge Stage to work properly master dataset and update dataset should contain unique records. Merge Stage is generally used to combine datasets or files.

Sort Stage :
The Sort Stage is an active Stage. The Sort Stage is used to sort input dataset either in Ascending or Descending order. The Sort Stage offers a variety of options of retaining first or last records when removing duplicate records, Stable sorting, can specify the algorithm used for sorting to improve performance, etc. Even though data can be sorted on a link, Sort Stage is used when the data to be sorted is huge.When we sort data on link ( sort / unique option) once the data size is beyond the fixed memory limit , I/O to disk takes place, which incurs an overhead. Therefore, if the volume of data is large explicit sort stage should be used instead of sort on link.Sort Stage gives an option on increasing the buffer memory used for sorting this would mean lower I/O and better performance.

Transformer Stage :
The Transformer Stage is an active Stage, which can have a single input link and multiple output links. It is a very robust Stage with lot of inbuilt functionality. Transformer Stage always generates C-code, which is then compiled to a parallel component. So the overheads for using a transformer Stage are high. Therefore, in any job, it is imperative that the use of a transformer is kept to a minimum and instead other Stages are used, such as:Copy Stage can be used for mapping input links with multiple output links without any transformations. Filter Stage can be used for filtering out data based on certain criteria. Switch Stage can be used to map single input link with multiple output links based on the value of a selector field. It is also advisable to reduce the number of transformers in a Job by combining the logic into a single transformer rather than having multiple transformers .

Funnel Stage –
Funnel Stage is used to combine multiple inputs into a single output stream. But presence of a Funnel Stage reduces the performance of a job. It would increase the time taken by job by 30% (observations). When a Funnel Stage is to be used in a large job it is better to isolate itself to one job. Write the output to Datasets and funnel them in new job. Funnel Stage should be run in “continuous” mode, without hindrance.

Overall Job Design :While designing DataStage Jobs care should be taken that a single job is not overloaded with Stages. Each extra Stage put in a Job corresponds to lesser number of resources available for every Stage, which directly affects the Jobs Performance. If possible, big jobs having large number of Stages should be logically split into smaller units. Also if a particular Stage has been identified to be taking lot of time in a job, like a transformer Stage having complex functionality with a lot of Stage variables and transformations, then the design of jobs could be done in such a way that this Stage is put in a separate job all together (more resources for the transformer Stage!!!). Also while designing jobs, care must be taken that unnecessary column propagation is not done. Columns, which are not needed in the job flow, should not be propagated from one Stage to another and from one job to the next. As far as possible, RCP (Runtime Column Propagation) should be disabled in the jobs. Sorting in a job should be taken care try to minimise number sorts in a job. Design a job in such a way as to combine operations around same sort keys, if possible maintain same hash keys. Most often neglected option is “don’t sort if previously sorted” in sort Stage, set this option to “true”. This improves the Sort Stage performance a great deal. In Transformer Stage “Preserve Sort Order” can be used to maintain sort order of the data and reduce sorting in the job.In a transformer minimum of Stage variables should be used. More the no of Stage variable lower is the performance. An overloaded transformer can choke the data flow and lead to bad performance or even failure of job at some point. In order to minimise the load on transformer we can Avoid some unnecessary function calls. For example to convert a varchar field with date value can be type cast into Date type by simple formatting the input value. We need not use StringToDate function, which is used to convert a String to Date type.Implicit conversion of data types.
Reduce the number of Stage variables used. It was observed in our previous project by removing 5 Stage variables and 6 function calls, runtime for the job was reduced from 2 hours to 1 hour 10 min (approximately) with 100 million records input.Try to balance load on transformers by sharing the transformations across existing transformers. This would ensure smooth flow of data.If you require type casting, renaming of columns or addition of new columns, use Copy or Modify Stages to achieve thisWhenever you have to use Lookups on large tables, look at the options such as unloading the lookup tables to datasets and using, user defined join SQL to reduce the look up volume with the help of temp tables, etc.The Copy stage should be used instead of a Transformer for simple operations including:o Job Design placeholder between stages o Renaming Columnso Dropping Columnso Implicit (default) Type Conversions The “upsert” works well if the data is sorted on the primary key column of the table which is being loaded. Or Determine , if the record already exists or not to have “Insert” and “Update” separately.It is sometimes possible to re-arrange the order of business logic within a job flow to leverage the same sort order, partitioning, and groupings. Don’t read from a Sequential File using SAME partitioning. Unless more than one source file is specified, this scenario will read the entire file into a single partition, making the entire downstream flow run sequentially (unless it is repartitioned)

creating a unique counter in DataStage job

This entry describes various ways of creating a unique counter in DataStage jobs.

A parallel job has a surrogate key stage that creates unique IDs, however it is limited in that it does not support conditional code and it may be more efficient to add a counter to an existing transformer rather than add a new stage.

In a server job there are a set of key increment routines installed in the routine SDK samples that offer a more complex counter that remembers values between job executions.

The following section outlines a transformer only technique.

Steps

In a DataStage job the easiest way to create a counter is within the Transformer stage with a Stage Variable.

svMyCounter = svMyCounter + 1

This simple counter adds 1 each time a row is processed.

The counter can be given a seed value by passing a value in as a job parameter and setting the initial value of svMyCounter to that job parameter.

In a parallel job this simple counter will create duplicate values on each node as the transformer is split into parallel instances. It can be turned into a unique counter by using special parallel macros.

1.     Create a stage variable for the counter, eg. SVCounter.

2.     At the Stage Properties form set the Initial Value of the Stage Variable to "@PARTITIONNUM - @NUMPARTITIONS + 1".

3.     Set the derivation of the stage variable to "svCounter + @NUMPARTITIONS". You can embed this in an IF statement if it is a conditional counter.

Each instance will start at a different number, eg. -1, -2, -3, -4. When the counter is incremented each instance is increment by the number of partitions, eg. 4. This gives us a sequence in instance 1 of 1, 5, 9, 13... Instance 2 is 2, 6, 10, 14... etc.

Remember this method only works if your data is evenly balanced i.e. equal number of rows going through each partition. Alternative syntax is:

@INROWNUM * @NUMPARTITIONS + @PARTITIONNUM