IBM InfoSphere DataStage

New features and changes in InfoSphere Information Server Version 11.7

2017-12-21T20:34:00.002+05:30

Latest information:
Governance updates
Data Quality updates
Integration updates
Administration and management updates
Deprecated features

Governance updates

Information Governance Catalog New
InfoSphere Information Governance Catalog
Information Server Enterprise Search
Information Server Governance Monitor

Information Governance Catalog New

New in 11.7

This version of Information Governance Catalog has a new user interface which improves user experience.
You can search for assets in your entire enterprise by using enhanced search that takes into account factors like text match, related assets, ratings and comments, modification date, quality score, and usage.
You can browse the assets in your catalog and narrow down the results by using filters.
You can use producers, which are applications that collect relevant data on systems like Db2®, Hive, Hadoop Distributed File System (HDFS), Oracle, or Teradata, to improve the quality of search results.
You can use unstructured data sources to enrich your data with information that has no clear structure - emails, instant messages, word-processing documents, or audio or video files.
You can view a graphical depiction of relationships between assets in the graph explorer.
You can add comments and ratings to assets.
On the home page, you can display the most important information such as assets with highest rates, your collections, or information assets statistics.
Administrators can create custom profiles to display customized details page of assets.

Information Server Enterprise Search

New in 11.7

Information Server Enterprise Search is a stand-alone application which enables you to explore data in your enterprise.
You can search for assets in your entire enterprise by using enhanced search that takes into account factors like text match, related assets, ratings and comments, modification date, quality score, and usage.
You can use producers, which are applications that collect relevant data on systems like Db2®, Hive, Hadoop Distributed File System (HDFS), Oracle, or Teradata, to improve the quality of search results.
When you view basic information about an asset in the search results, you can open the details page in the application of origin such as Information Governance Catalog.
You can view a graphical depiction of relationships between assets in the graph explorer.
You can add comments and ratings to assets.

InfoSphere Information Governance Catalog

New in 11.7

You can discover new asset family - unstructured data sources. These assets are synchronized from IBM Stored IQ, and represent data with no clear structure. New asset types are instances, volumes, infosets and filters.
You can export your own asset types that you added to Information Governance Catalog by using REST API bundles. You can later reimport such assets and merge them with the existing ones.
When using REST API, two new properties are displayed in the basic information about an asset: asset group and class name.
When searching for assets by using REST API, you can specify context, such as the name of a host, to narrow down search results.
When searching for assets by using REST API, you can use new parameter IncludeHistory to specify whether the asset history (if applicable) is included in the details of returned assets.
You can create, edit and delete custom attributes by using REST API.

Information Server Governance Monitor

New in 11.7

Information Server Governance Monitor is a tool where you can monitor the status and health of the data in your enterprise.
On the Curation Dashboard, you can see whether the data in your enterprise is cataloged, classified and governed.
On the Quality Dashboard, you can review the overall quality of the data in your enterprise, including scoring and quality dimensions.

Data Quality updates

InfoSphere Information Analyzer

InfoSphere Information Analyzer

New in 11.7

You can create and run automation rules to automate the process of applying and running rule definitions and quality dimensions.
Terms can be automatically assigned to data sets and columns when you use automated discovery to import data or when you run a column analysis.
You can run automated discovery to import and analyze new data sets from a data connection. With one click you can register data sets and add metadata from the data connection to a default workspace, run column and quality analysis, and assign business terms to imported assets.
You can create new data quality dimensions in order to detect if a row, column, or cell contains a particular data quality problem.
When you run column analysis, a single frequency distribution table can be generated for each project rather than each column in the analysis.
You can use different credentials for running analysis jobs than the ones you use to import data by using by using InfoSphere Metadata Asset Manager. You can set the credentials for a specific project.
When you run quality and column analysis, you can update settings for a specific analysis. For example, you can run the analysis with a data sample, specify how you want analysis statistics generated and stored, or apply advanced engine settings.
You can run stored procedures for SQL Server and Oracle to get the analysis results from Information Analysis Database.
You can export the results of the overlap analysis.
New data class type 'Script' is introduced. By using this script classifier, you can classify your data by creating a custom script snippet.

Integration updates

DataStage Flow Designer
InfoSphere DataStage
Connectivity
InfoSphere Information Server on Hadoop

DataStage Flow Designer

New in 11.7

You can use DataStage Flow Designer, a web-based tool, to create, edit, load, and run DataStage jobs.
You can search for jobs by using built-in search.
Metadata is automatically propagated to subsequent stages in a flow.
When compilation errors occur, all of them are highlighted and a hover over each stage provides details, which makes it easier to correct the errors.
If you are new to the product, you can take a quick tour to learn how to use its features.

InfoSphere DataStage

New in 11.7

By using DataStage Product Insights, you can connect your InfoSphere DataStage installation to IBM Cloud Product Insights. Such connection gives you ability to review your installation details and metrics like CPU usage, memory usage, active jobs, jobs that failed, and jobs that completed.
Data Masking stage supports Optim Data Privacy Providers version 11.3.0.5.

Connectivity

New in 11.7

HBase connector is supported. You can use HBase connector to connect to tables stored in the HBase database and perform the following operations:
- Read data from or write data to HBase database.
- Read data in parallel mode.
- Use HBase table as a lookup table in sparse or normal mode.
- Kerberos keytab locality is supported.
Hive connector supports the following features:
- Modulus partition mode and minimum maximum partition mode during the read operation are supported.
- Kerberos keytab locality is supported.
- Connector supports connection to Hive on Amazon EMR.
Kafka connector supports the following features:
- Continuous mode, where incoming topic messages are consumed without stopping the connector.
- Transactions, where a number of Kafka messages is fetched within a single transaction. After record count is reached, an end of wave marker is sent to the output link.
- TLS connection to Kafka.
- Kerberos keytab locality is supported.
IBM MQ version 9 is supported.
IBM InfoSphere Data Replication CDC Replication version 11.3 is supported.
SQL Server Operator supports SQL Server Native Client 11.
Sybase Operator supports unichar and univarchar datatypes in Sybase Adaptive Server Enterprise.
Amazon S3 connector supports connecting by using a HTTP proxy server.
File connector supports the following features:
- Native HDFS FileSystem mode is supported.
- You can import metadata from the ORC files.
- New data types are supported for reading and writing the Parquet formatted files: Date / Time and Timestamp.
JDBC connector is certified to connect to MongoDB and Amazon Redshift.

InfoSphere Information Server on Hadoop

New in 11.7

The following Hadoop distributions are supported: MapR 5.2.2, Cloudera CDH 5.13, and Hortonworks HDP 2.6.2.
Parallel engine configuration files on YARN can include nodes which are not part of Hadoop cluster, so Hadoop jobs can access relational databases outside the cluster.
Parallel jobs on YARN can use Hadoop Shuffle space for scratch files.
Parallel jobs give clearer messages when they are preempted by the YARN resource manager.
Ambari scripts have been enhanced to improve administration.

Administration and management updates

Managing metadata

Managing metadata

New in 11.7

You can run automated discovery by using command line to discover the database schema available for a given database or the files and folders for a given file system.

Deprecated features

Features deprecated in 11.7

Using InfoSphere Data Click to move data in IBM InfoSphere BigInsights is no longer supported. In previous releases, InfoSphere Data Click was used to copy selected database tables, data files, data file folders, and Amazon S3 buckets from the catalog to a target distributed file system, such as a Hadoop Distributed File System (HDFS) in IBM InfoSphere BigInsights.
The following asset types are deprecated in Information Governance Catalog:
- Machine Profiles
- Blueprint Director
- CDC Mapping Document
- Warehouse Mapping Document
- Information Server Reports
- External Assets
Data policy asset type is deprecated in Information Analyzer.

How do I make the Oracle EE Stage Read Operator Run in Parallel?

2014-08-30T23:49:00.000+05:30

By default the oraread operator is set to run in sequential mode. To enable parallel mode on oraread , you have to add the partition table property. This is set in the Source properties section. Then you specify the table name of this property. If more than one table is being used in the select statement, then specify only one of the tables used.

You can then specify a Partitioning Algorithm to use when partitioning the table across nodes.

It is recommend that you add the environment APT_ORAREAD_PARALLEL_ALGORITHM to the job and set its value to ROWID_HASH

APT_ORAREAD_PARALLEL_ALGORITHM

This environment variable is used to determine which partitioning algorithm to use during Oracle Enterprise parallel read operations. The algorithm defines how the stage divides the input dataset into subsets so that each parallel instance of the stage reads one subset of the data. The environment variable can be set to one of the following values:

ROUND_ROBIN - The operator divides the rows from the input dataset in a round-robin fashion using modulus function applied on the row identifier (ROWID) values of the rows within the storage blocks in which they reside

ROWID_HASH - The operator divides the rows from the input dataset in an approximately random fashion using modulus function applied on the hash codes calculated from the rowid values of the rows (recommended)

ROWID_RANGE - The operator divides the rows from the input dataset by taking into account the physical collocation of rows in the table segment and splitting the overall range of rowid values into sub-ranges. This is the default option and is a preferred option for use.

If the environment variable is not defined or is set to a value other than the values listed above, the ROWID_RANGE value is used by default.

If the ROWID_RANGE option is selected (either explicitly or implicitly) and the Oracle user does not have access to DBA_EXTENTS dictionary view, or the target table is an index-organized table (IOT) or a view, then the stage cannot use ROWID_RANGE algorithm and it automatically switches at runtime to using ROWID_HASH partitioning algorithm instead.

Article Source: IBM Support Guide

DataStage Scenario on Finding Unique Distance from Source and Destination

2014-05-10T02:46:00.000+05:30

Source,Destination,Distance
hyd,bang,1000
delhi,chennai,1500
chennai,bang,600
bang,hyd,1000
bombay,pune,1000
bang,chennai,600

**Sorted on Distance**

Source,Destination,Distance
bang,chennai,600
chennai,bang,600
bang,hyd,1000
bombay,pune,1000
hyd,bang,1000
delhi,chennai,1500

**Stg Variables**
PrevActualStr: ActualStr
ActualStr: inp.Src:inp.Dest
RevStr: inp.Dest:inp.Src
isDup: if PrevActualStr = RevStr then 1 else 0

**Constraint**
isDup = 0 [Constraint]

The below steps will explain you, How the records will be processed to o/p based on the above criteria.

Rec-1:

PrevActualStr: Garbage value
ActualStr: bangchennai
RevStr: chennaibang
isDup: 0 [if PrevActualStr = RevStr then 1 else 0]

isDup = 0 [Rec will be processed to o/p as per our constraint isDup=0]

O/P
Src Dest Distance
bang chennai 600

Rec-2:

PrevActualStr: bangchennai
ActualStr: chennaibang
RevStr: bangchennai
isDup: 1 [if PrevActualStr = RevStr then 1 else 0]

isDup = 1 [Rec willn't be processed to o/p as our constraint isDup=0]

O/P
Src Dest Distance
bang chennai 600

Rec-3

PrevActualStr: chennaibang
ActualStr: banghyd
RevStr: hydbang
isDup: 0 [if PrevActualStr = RevStr then 1 else 0]

isDup = 0

O/P
Src Dest Distance
bang chennai 600
bang hyd 1000
---------------------
====================

Performance Optimization technique to handle variable length data in Data Set

2013-07-07T16:02:00.000+05:30

How to replace a string in DataStage

2013-02-14T11:55:00.001+05:30

I have seen several web sites which are explaining most of the things on DataStage, but I have not seen easiest way to replace a string by using DataStage functionality without using Ereplace Function.

Field Name: SERVICE

Requirement: If the service description contains the word "Functional", then replace the word with "Technical".

Source Service Desc: My Functional Service for you...!!!

Expected Target Service Desc: My Technical Service for you...!!!

Logic to get the output as per the specified requirement;

If Index(UpCase(Ln_L40_Cust_Det.SERVICE),'FUNCTIONAL',1) > 0 Then

Left(Ln_L40_Cust_Det.SERVICE,Index(UpCase(Ln_L40_Cust_Det.SERVICE),'Functional',1)-1):'Technical':Right(Ln_L40_Cust_Det.SERVICE,Len(Ln_L40_Cust_Det.SERVICE)-Len(Left(Ln_L40_Cust_Det.SERVICE,Index(UpCase(Ln_L40_Cust_Det.SERVICE),'Functional',1)-1):'Functional'))

Else
Ln_L40_Cust_Det.SERVICE

Link Name:Ln_L40_Cust_Det

Please let me know if you have any queries about the logic.

New features and changes for IBM InfoSphere Information Server, Version 8.7

2012-01-14T23:09:00.008+05:30

New features and changes were introduced for IBM® InfoSphere® Information Server, Version 8.7 along with documentation updates. The new and changed features and documentation updates are described in the following sections.

InfoSphere Information Server, Version 8.7, new features and changes:

Common capabilities across the InfoSphere Information Server suite
InfoSphere Business Glossary
InfoSphere DataStage
InfoSphere Information Analyzer
InfoSphere Metadata Workbench
InfoSphere QualityStage

Documentation changes included in the Version 8.7 release:

Documentation introduced with Version 8.7

Top | Next

Administering

Bottom

Maintenance mode: Using a new command-line tool, the suite administrator can place InfoSphere Information Server into maintenance mode. While in maintenance mode, only users with the suite administrator role can log in. This feature simplifies the way that administrators can quiesce the system when they require exclusive access. For more information, refer to Maintenance mode.
Command-line administration of user and roles: Administrators in some organizations standardize the way that they establish the InfoSphere Information Server environment for new projects. In order to support that process from beginning to end, the command-line interfaces are expanded to allow the assignment of users and roles for InfoSphere DataStage® and QualityStage™ projects. For more information, refer to Managing assets by using the command line.
IPv6 Support: For organizations that need to migrate their networks to the newer, expanded set of Internet Protocol addresses. InfoSphere Information Server, Version 8.7 is fully compatible with IPv6 addresses. Also, it supports dual-stack protocol implementations that are mixed between IPv4 and IPv6. For more information, refer to Configuring your network.
New backup and restore functionality: To prevent the loss of data and to prepare for disaster recovery, organizations need robust backup strategies. InfoSphere Information Server, Version 8.7 introduces a new administration tool called isrecovery, which provides capabilities to backup and restore the databases, profiles, and directories that are associated with InfoSphere Information Server. The isrecovery tool supports all three server tiers: the services tier, the engine tier, and the metadata repository tier. When you run a backup or recovery, all tiers that are installed on the computer are backed up simultaneously.

Top | Next

Connecting to external sources

Previous | Bottom

Connectivity in InfoSphere Information Server, Version 8.7 includes the following new features and changes.

Netezza® connector

Provides native connectivity to the Netezza appliance, and offers improved and consolidated support for greater performance and functionality. For more information, refer to Netezza Performance Server®. The Netezza connector includes the following features.

Uses the massively parallel, highly scalable, and dynamically configurable framework of InfoSphere DataStage to access each SPU in the Netezza appliance for optimum performance. For example, the connector supports performance loading by using the external table load method, which can be on the same database or on an external database.
Supports the update then insert operation to speed up writing to disk.
Creates tables by using random or user-defined distribution keys.
Row-selected SQL operation, which dynamically chooses the SQL operation at run time based on the actual data.
Supports two-level and three-level reference object naming.
Uses various write modes, table actions, and transaction modes.
Uses user-defined SQL. You can use a user-defined UDX in the SQL statement by loading from a flat file or you can use SQL that is pre-defined.
Uses properties for before and after SQL statements that can run across all the runtime nodes, or just one node. Various post actions are available if the statement fails.
Outstanding out of box performance rates for loading and unloading data. Lab performance tests measured rates comparable to Netezza command-line bulk load utilities.

Big Data and Hadoop support

Organizations are increasingly adopting Big Data tooling to address the most challenging data volumes in their business. InfoSphere Information Server, Version 8.7 adds the new Big Data File stage so that these organizations can make the most of this data through the user-friendly InfoSphere DataStage development paradigm. With support for the Hadoop Distributed File System (HDFS), users can access the data directly on this distributed file system. The Big Data File stage mirrors the Sequential File stage experience so that users will find it intuitive to get started. In that way, the Big Data File stage provides reading from multiple files in parallel (either listed specifically or through file patterns) to simplify how data is merged into a common transformation process. Additionally, the InfoSphere DataStage parallel engine can mimic the same degree of partitioning as the Big Data file, or the engine can dynamically repartition that data "on the fly" based on different business requirements. These features offer both flexibility and optimization as organizations address their Big Data challenges.

Other new connectivity features

DRS connector

Allows the database type to be dynamically chosen at run time, which enables jobs to be reused in various deployment scenarios. Supports this flexible connectivity between Oracle, IBM DB2®, and ODBC.

CDC Transaction stage

Allows information that is captured from log-based sources by using InfoSphere Change Data Capture (InfoSphere CDC) to be delivered in near real-time to InfoSphere DataStage. By using our unique bookmarking technology, this solution architecture provides guaranteed data delivery and transaction integrity through a complex set of data transformation and cleansing tasks to one or more targets.

Teradata Connector TMSM and Dual Load support

Provides native support to the Teradata Multi-System Manager (TMSM) and Dual Load capability.

Enhanced SQL logging for connectors

Increased coverage in connectors for writing executed SQL statements to the job log, which provides better support for exception research.

DB2 connector performance enhancement

Improved DB2 connector performance when loading to IBM DB2.

Other new database connectivity enhancements include support for the following data sources

IBM DB2 for Linux, UNIX, and Microsoft Windows, Version 9.7 and 9.8 (pureScale®)
IBM DB2 for z/OS®, Version 10
IBM WebSphere® MQ, Version 7.0
IBM Informix®, Version 11.7
Teradata Database 13.10 and 14.0
Oracle Database 11g Release2
Sybase ASE, Version 15.5
Sybase IQ, Version 15.2
Netezza 4.6, 6
DataDirect ODBC, Version 6.1

Top | Next

InfoSphere Blueprint Director

Previous | Bottom

InfoSphere Blueprint Director Version 8.7 includes the following new capabilities. For more information, refer to InfoSphere Blueprint Director.

Milestone planning support

Most information projects are implemented and deployed in multiple iterations. For example, data warehousing and MDM projects have multiple milestones at which new data sources and new information consumers are added. Milestone planning support:

Enables you to specify the milestone at which an element becomes effective or remains effective. For example, you could specify that a new data source and load process into the warehouse will be deployed at a milestone called 1Q12, the first quarter of the year. Another data source and corresponding load process could then be deployed at the start of the second quarter of the year, when the first data source and load process will be undeployed. In addition to specifying when a milestone element becomes effective, InfoSphere Blueprint Director gives you the ability to visualize the milestones so that the team can view the evolution of the information landscape in the blueprint definition.
Enhances one of the key differentiators of InfoSphere Blueprint Director, which is the support of the entire life cycle. InfoSphere Blueprint Director can specify how the landscape evolves over time in multiple agile iterations.

Task management integration

In addition to providing method guidance for information projects, InfoSphere Blueprint Director enables you to select steps from the method guidance that can be tracked as tasks. The method documentation shows which roles are required, what their tasks are, in which sequence these tasks should be performed, and which artifacts will be delivered. Task management integration gives you the ability to:

Assign tasks that are either derived from the method or created manually
View assigned tasks for a person and allows that person to articulate that the task is complete
Have a sample and basic dashboard of task completion for a project

Top | Next

InfoSphere Metadata Asset Manager

Previous | Bottom

The new InfoSphere Metadata Asset Manager, which is installed as a part of InfoSphere Information Server, provides centralized import and management of common metadata, including database and data file assets, logical and physical data models, and business intelligence assets. For more information, refer to Importing and managing assets by using IBM InfoSphere Metadata Asset Manager.

Logical data model and physical data model support

The suite tools and metadata repository of InfoSphere Information Server now support logical data models along with physical data models and their contents, including logical entities and attributes, and design tables and columns. On import, relationships between logical and physical data models and the schemas that implement them are set automatically, when it is possible to do so. You can use the Repository Management tab of InfoSphere Metadata Asset Manager to set additional implementation relationships. For more information, refer to Logical data model assets.

Import

InfoSphere Metadata Asset Manager centralizes the metadata import in Information Server into a web interface. You can import metadata to a staging area where you can inspect it before you share it with others. For more information, refer to Importing and sharing assets.

This centralized import functionality has the following benefits:

You can use both bridges and connectors for metadata imports.
You can analyze and manually compare, reimport, and preview the effect the import will have on existing metadata assets before you share it.
You have one-click access to InfoSphere Metadata Workbench (if installed) to view specific existing assets that might be affected by the import.
You can maintain the metadata throughout its life cycle, keeping a record of the contents of all imports and reimports to the staging area and to the metadata repository.
You can eliminate any problem assets, such as duplicates, before they are shared.
You can run express imports that automate the analysis and preview, and automatically import directly without first staging the metadata.

In InfoSphere Information Server Version 8.7, InfoSphere Metadata Asset Manager is the preferred tool for importing metadata into InfoSphere Information Server.

Repository Management

The Repository Management tab was moved into InfoSphere Metadata Asset Manager from its former location in the web console. For more information, refer to Managing common metadata assets.

In addition to the ability to browse and search common metadata in the metadata repository, the Repository Management tab contains the following new functionality:

Improved management of duplicate assets, including the ability to merge or delete duplicates.
You can display and delete any assets that have been disconnected from their parent assets.
You can set implementation relationships between logical data models, physical data models, and database schemas or data files. These relationships let you trace a column that is used by an InfoSphere DataStage and QualityStage job back to the design column and logical entity that originally defined the column.

Top | Next

Managing security

Previous | Bottom

Strongly encrypted credential files for command-line utilities: Provide a unified means for handling and encrypting credential arguments for the command-line tools, such as istool and dsjob commands. This option provides encryption "at rest" for the login credentials. For more information, refer to The credentials file.
Strongly encrypted job parameter files for dsjob command: Version 8.7 extends the existing encryption capability of InfoSphere Information Server by adding encryption to the dsjob command-line paramfile option, which specifies that a file contains strongly encrypted parameter values. This option provides encryption "at rest" for job parameters. For more information, refer to Securing credentials and parameter values in dsjob commands.
Encryption algorithm customization: Although the AES-128 symmetric-key encryption algorithm provided by InfoSphere Information Server is compliant with many government and industry standards, administrators of some organizations might want to use their own standard encryption variant. For those customers, InfoSphere Information Server, Version 8.7 provides the means to override the AES-128 algorithm. For more information, refer to Adding a custom encryption provider.
Mixed LDAP and operating system support by using PAM: Version 8.7 leverages Pluggable Authentication Module (PAM) as a means to mix the existing authentication protocols of LDAP and the local operating system to support both individual and "functional" accounts simultaneously. Functional users can represent a role that can be shared by more than one person within an organization, or a role that can be assigned to a scripted process. For more information, refer to Shared user registry overview.
Automated audit trail for all DBMS access with InfoSphere Guardium®: This feature is provided to satisfy regulatory mandates that require an organization to know which people and processes are accessing sensitive or personally identifiable information (PII). Connectors for InfoSphere Information Server, Version 8.7 can be configured to automatically register entries for an audit trail into InfoSphere Guardium, which provides a complete view of the origin of any data integration activity that affects the database.

Top | Next

InfoSphere Business Glossary

Previous | Bottom

InfoSphere Business Glossary Version 8.7 includes the following new capabilities. For more information, refer to IBM InfoSphere Business Glossary.

Provides a new integrated user interface for authoring, administering, and viewing the glossary
Supports a workflow process for collaborative glossary content development. For more information, refer to Administering the glossary.
Enables an InfoSphere Business Glossary administrator to assign permissions for authoring the glossary. For more information, refer to Authoring glossary content.
Enables an InfoSphere Business Glossary administrator and author to assign labels to assets and terms. For more information, refer to Labels.
Enables an InfoSphere Business Glossary administrator to export specific subcategories. For more information, refer to Importing and exporting glossary content.
Provides enhanced search and filtering capabilities
Support for physical data models, logical data models, and implemented data resources
Provides a utility that generates a glossary from a logical data model
Enables an InfoSphere Business Glossary administrator to configure external asset types
Enables client-driven multilingual support for user interface labels and for support for right-to-left languages. For more information, refer to Configuring a specific locale.
Provides a new user role, Business Glossary Asset Assigner, that enables you to assign assets to terms from other InfoSphere Information Server tools. For more information, refer to Security roles.
Support for logical data models in InfoSphere Business Glossary Client for Eclipse:
- Importing term assignments to a logical data model package.
- Exporting term assignments from a logical data model package.
Supports integration with IBM InfoSphere Warehouse Design Studio and with IBM Optim™ Development Studio

Top | Next

InfoSphere DataStage

Previous | Bottom

InfoSphere DataStage contains the following new features.

The InfoSphere DataStage Debugger

The following screen capture shows a job where a breakpoint is established to debug the job. For more information, refer to The IBM InfoSphere DataStage Debugger.

Figure 1. A job that shows the menu and window of the InfoSphere DataStage Debugger

InfoSphere Information Server, Version 8.7 extends existing debugging capabilities for parallel jobs through an interactive debugger in the Designer client. Through this capability, you can establish breakpoints on the data flow links of jobs in order to interrogate the values as they are being transformed at job run time by the various components.
The debugging process is straightforward. Right-click any link to toggle the breakpoint on or off. Once a breakpoint is established, the default mode causes a debug run to pause at every row passing through that link. You can edit the breakpoint and change this default behavior so that the job pauses at a different frequency or upon a certain data condition, for example account_id = ‘12345’.
When the debug run is paused at a break condition, you can view the individual nodes where any of the stages are running, the data on each of those nodes, and the number of rows that already passed through that link. During investigation, you can examine the properties of any of the stages that compose the job design and then alter the breakpoints based on new information that you learn by investigating the data. You can also add specific fields to the watch list in order to focus attention on only the set of fields you are most interested in.
Because the debug mode of the job runs with the same configuration file as the standard run, debug mode catches any specific data conditions that exist based on how data is being partitioned and distributed across the infrastructure. So, regardless of whether the job is running on a small computer or across an enterprise class grid, or whether the databases accessed by the job are partitioned out to 128 nodes, the debug results mimic the way the job will be deployed in your standard environment.

InfoSphere DataStage Operations Console for accessing operational metadata and monitoring the runtime environment

The new InfoSphere DataStage Operations Console contains the following features. For more information, refer to Monitoring jobs.

The following screen capture shows the InfoSphere DataStage Operations Console main dashboard.

Figure 2. The InfoSphere DataStage Operations Console

Production operators, development teams, and other stakeholders need access to the operational metadata of their data integration environment. Whether they are confirming that the environment is live, reviewing job run history, or working to troubleshoot system failures, these users need direct access to the most relevant information in order to perform their jobs efficiently.
The Operations Console provides a view-only client that allows simple, web-based access to the runtime environment of InfoSphere Information Server. High-level graphs provide a summary view of the current state of the environment, such as what is running, how much resource is being consumed, and whether the environment is healthy. With several clicks, you can find the supporting details that you need to answer questions and achieve higher levels of productivity. Key features of this interface include:
- Dashboard style graphs representing current job activity
- Indications of successful or failed jobs statuses
- System health information
- Resource consumption views
- Server-wide and project-specific access based on the privileges of a particular user
- High-level summary and detailed statistics for the job run history, including job start date/time, elapsed run time, record counts, parameter detail, and control and error messages
- Utilities to compare multiple job runs and to filter relevant differences
To support the innovative paradigm of this interface, InfoSphere Information Server includes a new role called Operations Viewer. This role is restricted to use only the Operations Console read-only views. Administrators can generously assign this role to users and groups, including in production, and trust that no objects within InfoSphere Information Server can be altered or run. Additionally, in situations where operational metadata needs to be shared with other applications in the enterprise, organizations can use the fully documented metadata schema to define those integrations.

Balanced Optimization for Netezza

The Balanced Optimization capability provides the ability to push transformation into source databases, process in the high performance engine of InfoSphere DataStage, and push processing into target databases. You express the logic of your data integration processes in the current, flow-oriented convention of InfoSphere DataStage, and then that logic is automatically optimized to achieve the benefits of pushing the desired parts of the processing to the database.
In InfoSphere DataStage Balanced Optimization for Version 8.7, that development paradigm has been extended to interoperate with Netezza through the new Netezza connector. In jobs where you use this functionality, transformation processing is pushed to the Netezza appliance. The job uses the SPUs and parallel capability of the appliance to lessen the data round-trip time by keeping the data in the appliance itself. This capability is particularly useful when data reduction can be achieved at the source DBMS, or when the source and target of a given data integration task derive from the same DBMS.

Top | Next

InfoSphere Information Analyzer

Previous | Bottom

IBM InfoSphere Information Analyzer, Version 8.7 includes the following new features and changes.

Data Rules stage

The following screen capture shows a job that uses the Data Rules stage.

Figure 3. A job that uses the Data Rules stage showing the number and validity of processed records

You use this new feature to invoke InfoSphere Information Analyzer data rules in-stream as part of an InfoSphere DataStage and QualityStage job, or within an InfoSphere Information Services Director web service. You can include rules as part of an ETL or data cleansing process. In these cases, the rule or rule set definitions created in InfoSphere™ Information Analyzer are reusable as a unique rule validation stage to ensure that incoming content meets the prescribed rules, and the outgoing contents can be produced based on defined rule logic. Also, it is now possible to check whether your data is valid in real time, for example, by capturing the changes in real time by using InfoSphere Change Data Capture, and then validating the changes with the Data Rules stage. For more information, refer to Data Rules stage.

Logical data models, physical data models, and implementation models

InfoSphere Information Server and Foundation Tools, including InfoSphere Metadata Asset Manager, are adopting broad support for physical and logical data models. As part of that overall theme, InfoSphere Information Analyzer supports use of physical and logical data model assets in rule definitions and a facilitated approach to discover and bind associated assets. In inferred model generation, the physical data model generation is enhanced for a more integrated approach. For more information, refer to:

HTTP API enhancements for flow through processing

Additional APIs are added in this release to form a more complete set of interfaces supporting flow-through processing. For more information, refer to Developing applications with the HTTP API.

The API enhancements include these domains:

Folder support
Metrics and benchmarks
Key and cross-domain analysis
Public rules

Enhancements on rules output

This enhancement allows distinct records to be generated in rule output when the rule contains only aggregation functions. Only one record is output per grouping value.

Performance enhancements

Performance enhancements for this release include:

Rules that use join conditions are accelerated by pushing down the join operations to the SQL select statement executed on the analyzed source whenever it is possible, instead of loading all the data and joining them in InfoSphere DataStage and QualityStage.
Rules that use a sampling are accelerated by reducing the number of rows that must be loaded from the source database to InfoSphere DataStage and QualityStage.
IBM DB2 compression for Information Analyzer database (IADB). When using IBM DB2 as the analysis database, you can configure IBM DB2 compression to reduce the storage footprint of the analysis database. Query performance is not impacted.
Internal algorithms refactoring for performance. Column analysis performance enhancements through internal algorithm refactoring, and other internal refactoring take advantage of new infrastructure features.
The rule execution engine was reengineered and optimized for speed.
Performance enhancements for key user interface areas including open project, open project properties, and the home page.

Top | Next

InfoSphere Metadata Workbench

Previous | Bottom

InfoSphere Metadata Workbench Version 8.7 includes the following new features and changes. The following screen captures show the browser-based interface of metadata workbench.

Figure 4. The metadata workbench canvas showing a data lineage report

Figure 5. The metadata workbench inventory pane of a data lineage report showing additional information for each asset

Supports logical data models

The metadata workbench displays logical data models, physical data models, and implemented data resources and their relationships. You can view, search, and query the models, entities, and attributes. The details page of a term displays its navigation (term -> logical data model asset -> physical data model asset -> implemented data resource).
You can assign logical data model assets and physical data model assets to terms. In addition, you can assign stewards to logical data model assets and to physical data model assets.
You can use published queries to display the relationships between logical data models, physical data models, and implemented data resources.

Supports Netezza connector

Supports data lineage, impact analysis, and other metadata management functions for the new Netezza connector.

Supports InfoSphere CDC metadata

Metadata for the sources of InfoSphere Change Data Capture (InfoSphere CDC) is integrated into InfoSphere Metadata Workbench. This integration allows for functions like impact analysis and data lineage to extend from the initial log-based replication task, throughout the rest of the information management infrastructure.

Data lineage UI enhancements

Data lineage displays the flow of data, including ETL information, from the data source to the BI report. You can expand an ETL source or a data source and select a database table or a column to display the lineage flow through those selected assets. For more information, refer to Running the Manage Lineage utility.

Supports SAP stages

Data lineage reports include SAP stages.

Supports InfoSphere Warehouse

The metadata workbench provides data lineage and impact analysis reports that are based on design flow metadata. The reports include assets from data sources to InfoSphere Warehouse, and from InfoSphere Warehouse to the data marts. The reports use the mappings of data flows that are created by Informix SQL Warehousing Tool (SQW) and from Design Studio.

Simplified automated services

The following steps in automated services are combined into a single UI called Manage Lineage. For more information, refer to Manage Lineage utility.

Detect association between stages and data sources, between databases and database aliases, and between two identical schemas. This step was previously called Stage Binding.
Map a database name to an alias name. This step was previously called Database Alias.
Set the relationship between stages and data sources, between databases and database aliases, and between two identical schemas.

Displays custom attributes from InfoSphere FastTrack

InfoSphere FastTrack allows custom attributes to be created and assigned to an InfoSphere FastTrack mapping project. The metadata workbench displays these custom attributes in the details page of the mapping project.

Supports custom attributes for implemented data resources, BI Reports, data files, and extended data sources

The metadata workbench displays these custom attributes in the details page of these assets.

Displays folder structure for extension mapping documents and extended data sources

The administrator can create and manage folders for extension mapping documents and extended data sources. These folders can be organized into a hierarchical structure. The folder structure is maintained during import. The administrator can browse the folder structure. In addition, the administrator can move extension mapping documents and extended data sources to a different folder.

Provides home page enhancements

The home page has been simplified to enable you to search for an asset or to run an existing query.

Top | Next

InfoSphere QualityStage

Previous | Bottom

InfoSphere QualityStage, Version 8.7 includes the following new features and changes.

Match Specification Setup Wizard

You can now use a wizard to create basic match specifications for US name and address data. The specifications that you create with the wizard provide criteria for matching records from one or two sources. The specifications support common matching goals for individuals and businesses, such as deduplication and grouping records with similar values. You can use the specifications in the new predefined matching jobs or as models for specifications that meet your matching requirements. For more information, refer to Creating match specifications.

Predefined jobs

InfoSphere QualityStage, Version 8.7 includes predefined standardization and matching jobs for US name and address data. You can use predefined standardization jobs to generate standardized data and the frequency information for that data. You can use predefined matching jobs to match records from one or two sources of input data. Sample data is provided for use with the predefined jobs. For more information, refer to Predefined standardization jobs and Predefined matching jobs.

Match Statistics report

The new Match Statistics report presents summary-level statistics about the matching results and statistics about the matching process for each match pass. For more information, refer to Match Statistics reports.

Indian rule sets

You can use the new Indian rule sets in jobs that standardize Indian address and area data. A shared container is imported with the rule sets to help you design a job that uses the rule sets. For more information, refer to Predefined rule sets and Indian address rule sets.

Rule set enhancements

The predefined rule sets are enhanced in the following ways:

Some of the domain-specific rule sets now generate improved values for matching.
The USADDR rule set is enhanced in several ways. For example, the value ONE in ONE CHILDRENS PLACE is identified as a house number.
The column descriptions in the rule sets provide more information.
Rule sets now use the REPEAT statement to apply actions repetitively until an action does not match the associated pattern.

Top

Documentation introduced with Version 8.7

Previous | Bottom

Administration

Common metadata assets

Added documentation of common metadata assets, the assets that are shared by suite tools. Common metadata assets include logical data models, physical data models, implemented data resources, and business intelligence assets. For more information, refer to Common metadata assets. The documentation includes the following features:

Definitions of each asset type
Explanation of how the identity of assets in the metadata repository is established
Explanation of implementation relationships between logical data model assets, physical data model assets, and implemented data resources
Delete rules for assets
Explanation of duplicate assets and disconnected assets

IBM InfoSphere Information Server Guide to Managing Common Metadata

Added a single new PDF guide that includes the content for the following areas, which are grouped under the topic, Managing metadata, in the information center. To download the PDF, refer to IBM InfoSphere Information Server Version 8.7 product documentation.

Importing and managing assets by using IBM InfoSphere Metadata Asset Manager
Exchanging metadata by using IBM InfoSphere Metadata Integration Bridges
Common metadata assets

InfoSphere DataStage

The InfoSphere DataStage glossary lists and defines terms that administrators and job developers will find useful for lending understanding as they use the software. For more information, refer to InfoSphere DataStage glossary.
For more information about the methods you can use to examine problematic parallel jobs, refer to Debugging parallel jobs.

InfoSphere Metadata Asset Manager

Added documentation of the new InfoSphere Metadata Asset Manager, including documentation of the following functionality:

InfoSphere Metadata Integration Bridges

Revised documentation of bridges to emphasize importing by using InfoSphere Metadata Asset Manager. Also added documentation about importing logical data models and physical data models. For more information, refer to Exchanging metadata by using IBM InfoSphere Metadata Integration Bridges.
Updated List of supported bridges and System requirements for bridges.

Managing assets by using the command line

Added documentation for the following new functionality:

Generating glossary content from business intelligence models. For more information, refer to glossary bi2bg command.
Importing and exporting logical data models, physical data models, and custom attributes. For more information, refer to Common metadata assets.

Top

The tsort operator

2011-06-05T07:07:00.000+05:30

Bottom

The tsort operator

WebSphere DataStage provides the sort operator, tsort that you can use to sort the records of a data set. The tsort operator can run as either a sequential or a parallel operator. The execution mode of the tsort operator determines its action:

Sequential mode: The tsort operator executes on a single processing node to sort an entire data set. On completion, the records of the data set are sorted completely.
Parallel mode: The tsort operator executes on multiple processing nodes in your system. On completion, the records within each partition of a data set are sorted. This type of sort is called a partition sort.

The following figure shows the difference between these two operating modes:

The tsort operator on the left side of this figure runs sequentially to completely sort the records of a data set. Typically, you use the tsort operator sequentially when you need a total sort of a data set, or when you create a sequential job.

The tsort operator on the right side of this figure executes in parallel and sorts records within each partition. Remember that a parallel operator executes on multiple nodes in a system, where each node receives a partition of a data set. A parallel tsort operator outputs multiple partitions, where the records are sorted within the partition.

Typically, you use a parallel tsort operator as part of a series of operators that requires sorted partitions. For example, you can combine a sort operator with an operator that removes duplicate records from a data set. After the partitions of a data set are sorted, duplicate records in a partition are adjacent.

To perform a parallel sort, you insert a partitioner in front of the tsort operator. This lets you control how the records of a data set are partitioned before the sort. For example, you could hash partition records by a name field, so that all records whose name field begins with the same one-, two-, or three-letter sequence are assigned to the same partition. See "Example: Using a Parallel tsort Operator" for more information.

If you combine a parallel sort with a sort merge collector, you can perform a total sort. A totally sorted data set output is completely ordered, meaning the records in each partition of the output data set are ordered, and the partitions themselves are ordered. See "Performing a Total Sort" for more information.

Example: using a parallel tsort operator

A parallel tsort operator runs on multiple processing nodes in your system to sort the records within each partition of a data set. The default execution mode of the operator is parallel, using the any partitioning method. However, you typically use a partitioning operator with the tsort operator to set an explicit partitioning method.

Choose a partitioning method that is correct for the sorting operation. For example, assume that you are sorting records in a data set based on the last name field of each record. If you randomly allocate records into any partition, records with similar last names are not guaranteed to be in the same partition and are not, therefore, processed by the same node. Similar records can be sorted by an operator only if they are in the same partition of the data set.

A better method of partitioning data in this case is to hash the records by the first five or six characters of the last name. All records containing similar names would be in the same partition and, therefore, would be processed by the same node. The tsort operator could then compare the entire last names, first names, addresses, or other information in the records, to determine the sorting order of the records.

For example:

record ( fname:string[30]; lname:string[30]; )
... | modify -spec "lname_hash:string[6] =   substring[0,6](lname)"
      | hash -key lname_hash | tsort -key lname | ...

WebSphere DataStage supplies a hash partitioner operator that allows you to hash records by one or more fields. See "The hash Partitioner" for more information on the hash operator. You can also use any one of the supplied WebSphere DataStage partitioning methods.

The following example is a modification of the previous example, "Example: Using a Sequential tsort Operator" , to execute the tsort operator in parallel using a hash partitioner operator. In this example, the hash operator partitions records using the integer field a, the primary sorting key. Thus all records containing the same value for field a will be assigned to the same partition. The figure below shows this example:

Shown below is the osh command corresponding to this example:

$ osh " hash -key a -key e < unSortedDS.ds | 
        tsort -key a -key e > sortedDS.ds"

Performing a total sort

The previous section showed an example of a parallel sorter using the hash partitioner to sort the partitions of a data set. When you use a hash partitioner, all records containing the same hash key values are in the same partition and are therefore processed and sorted by the same node.

In contrast to a partition sort, you can also use the psort operator to perform a total sort.

The following figure shows a total sort performed on a data set with two partitions:

In this example, the partitions of the output data set contain sorted records, and the partitions themselves are sorted. As you can see in this example, a total sort requires that all similar and duplicate records are located in the same partition of the data set. Similarity is based on the key fields in a record.

Because each partition of a data set is sorted by a single processing node, another requirement must also be met before a total sort can occur. Not only must similar records be in the same partition, but the partitions themselves should be approximately equal in size so that no one node becomes a processing bottleneck. To meet these two requirements, you use the range partitioner on the input data set before performing the actual sort.

The range partitioner guarantees that all records with the same key fields are in the same partition, but it does more. The range partitioner also calculates partition boundaries, based on the sorting keys, in order to evenly distribute records to the partitions. All records with sorting keys values between the partition boundaries are assigned to the same partition so that the partitions are ordered and that the partitions are approximately the same size. See the next section for more information on using the range partitioning operator.

You need to perform a total sort only when your job requires a completely ordered data set, either as output or as a preprocessing step. For example, you might want to perform a total sort on a mailing list so that the list is sorted by zip code, then by last name within zip code.

For most jobs, a partition sort is the correct sorting component. For example, if you are sorting a data set as a preprocessing step to remove duplicate records, you use a hash partitioner with a partition sort to partition the data set by the sorting key fields; a total sort is unnecessary. A hash partitioner assigns records with the same sorting key fields to the same partition, then sorts the partition. A second operator can compare adjacent records in a partition to determine if they have the same key fields to remove any duplicates.

Top

How to generate Sequence Numbers in DataStage using @INROWNUM and @PARTITIONNUM System Variables

2011-04-17T01:12:00.000+05:30

This is one of the basic requirement in DataStage, we'll have to generate sequence numbers and then assign the same values to your required O/P field (e.g. 1, 2, 3, …). If you are using the import osh operator (through a stage, e.g. the Sequential File Stage) to read external data, you can use the -recordNumberField parameter.

Solution: Generate Row Number Field with DataStage Transformer Stage

There are number of different ways to solve this problem. Here I will share with you the easiest way to generate and assign sequence numbers using a DataStage Parallel Transformer stage.

@PARTITIONNUM + @NUMPARTITIONS * (@INROWNUM - 1) + 1

Note: This logic only works if your data is evenly balanced i.e., equal number of rows going through each partition.

The above logic uses three DataStage system variables which are listed as below;

@INROWNUM – This system variable contains the row number within the partition. For each partition this variable starts from 1: 1, 2, 3, …
@NUMPARTITIONS – This system variable contains the number of partitions (1, 2, 3, …) the stage is running on.
@PARTITIONNUM – This system variable contains the number (0, 1, 2,…) of the partition that is processing the particular row.
To understand the derivation expression better, let’s see an example.

Example-1: Generating Sequence Numbers Using 2-Node Config File

	2 NODE CONFIG FILE
Sample Field	@INROWNUM	@PARTITIONNUM	@PARTITIONNUM + @NUMPARTITIONS * (@INROWNUM - 1) + 1
A	1	0	1
B	1	1	2
C	2	0	3
D	2	1	4
E	3	0	5
G	3	1	6
H	4	0	7
I	4	1	8
J	5	0	9
K	5	1	10
L	6	0	11
M	6	1	12
N	7	0	13
O	7	1	14
P	8	0	15
Q	8	1	16
R	9	0	17
S	9	1	18
T	10	0	19
U	10	1	20
V	11	0	21

Example-2: Generating Sequence Numbers Using 4-Node Config File

	4 NODE CONFIG FILE
Sample Field	@INROWNUM	@PARTITIONNUM	@PARTITIONNUM + @NUMPARTITIONS * (@INROWNUM - 1) + 1
A	1	0	1
B	1	1	2
C	1	2	3
D	1	3	4
E	2	0	5
G	2	1	6
H	2	2	7
I	2	3	8
J	3	0	9
K	3	1	10
L	3	2	11
M	3	3	12
N	4	0	13
O	4	1	14
P	4	2	15
Q	4	3	16
R	5	0	17
S	5	1	18
T	5	2	19
U	5	3	20
V	6	0	21

New features & changes in IBM InfoSphere Information Server 8.5

2011-03-02T19:09:00.014+05:30

New features and changes were introduced in IBM® InfoSphere™ Information Server, Version 8.5 along with documentation updates. The new and changed features and documentation updates are described in the following sections.

InfoSphere Information Server, Version 8.5, new features and changes:

Suite and product module changes

IBM InfoSphere Business Glossary

IBM InfoSphere DataStage

IBM InfoSphere FastTrack

IBM InfoSphere Information Analyzer

IBM InfoSphere Information Services Director

IBM InfoSphere Metadata Workbench

IBM InfoSphere QualityStage

Documentation changes included in the Version 8.5 release:

Documentation introduced with Version 8.5

Top | Next

Suite and product module changes

Bottom

System requirements: InfoSphere Information Server has added support for several new operating systems, database servers, application servers, and web browsers. For more information, refer to System Requirements.

Installing

Expanded high availability (HA) and scalability options

InfoSphere Information Server now supports a wider variety of topologies for HA and scalability:

The services tier can now be implemented within an IBM WebSphere® Application Server Network Deployment clustered environment.
The metadata repository tier can now be implemented within a DB2® database cluster or HADR configuration, or an Oracle database Real Application Clusters (RAC) configuration.
InfoSphere Information Server components can now be implemented on redundant servers that are managed by HA management software such as IBM Tivoli® System Automation for Multiplatforms.

For more information, refer to High availability configurations.

Redesigned installation program

The InfoSphere Information Server installation program now runs within a standard Web browser, or in silent mode from the command-line. It performs a thorough requirements check that helps ensure that the installation is successful. It supports incremental removal of products and tiers as well as incremental installation. It can optionally install WebSphere Application Server and DB2. For more information, refer to Installing new software.

Multiple analysis databases and how they relate to installing InfoSphere Information Server

IBM InfoSphere Information Analyzer now supports multiple analysis databases. Each InfoSphere Information Analyzer project is associated with a specific analysis database. The analysis database might be used by a single InfoSphere Information Analyzer project, or it might be shared by multiple InfoSphere Information Analyzer projects. For more information about what you need to know at installation time, refer to Locations for the analysis databases.

Secure HTTP (HTTPS) support

IBM InfoSphere Information Server supports HTTPS communication between the application server and client components. If your environment requires confidentiality at this level, you can enable HTTPS.

HTTPS provides two benefits:

Encryption: Data sent over an HTTPS connection is encrypted.
Identification: Communication is allowed only if the server is positively verified. Before communications begin, the server sends the client a certificate. The signature in the certificate is decrypted by the client to verify the authenticity of the sender.

HTTPS configuration is optional. If you do not configure HTTPS, HTTP is used instead.

For more information, refer to Secure Sockets Layer (SSL) configuration.

Secure Sockets Layer (SSL) for inbound RMI/IIOP is disabled by default

SSL for inbound RMI/IIOP is no longer enabled when you install InfoSphere Information Server. You must now actively enable this security feature. For more information, refer to Enablement of Secure Sockets Layer (SSL).

Administering

New Auditing Service: The Auditing Service creates an audit trail of security-related events. This includes all activities that set or modify security-related settings and all user authentications and application logins. You can configure which audit events to log and how much information to include based on auditing requirements. For information about how to configure the Auditing Service, refer to Audit logging configuration.
New roles: There are new roles for common metadata, IBM InfoSphere Business Glossary, and InfoSphere Information Analyzer. For more information, refer to Security role overview.
New entitlement scheme: IBM has changed the entitlement scheme for InfoSphere Information Server. IBM no longer supplies each customer with a custom license file that programmatically restricts the product modules and components that can be installed and used. Instead, all InfoSphere Information Server product components and features are presented for installation. It is your responsibility to install only those items that are indicated on your Proof of Entitlement.; When you install IBM InfoSphere DataStage®, the program prompts you to select the InfoSphere DataStage editions and feature packs to install and activate. Each item in the selection list enables associated InfoSphere DataStage canvases and job features. Select the items for which you have a valid Proof of Entitlement from IBM. The installation program activates the features that are associated with the items that you select. Any other editions or feature packs are deactivated and cannot be used.; If you later acquire entitlements for an additional InfoSphere DataStage edition or feature pack, to use the features that are included in the item you must activate the item by using the LicensingServiceAdmin tool. If you no longer have entitlement for an item, you must deactivate it by using the tool. When you deactivate the edition or feature pack, the features within the item are no longer available for use.; For more information, refer to Activating entitled IBM InfoSphere DataStage editions and feature packs.

New URL for the web console: The URL for the InfoSphere Information Server web console is http://hostname:9080/ibm/iis/console. The previous URL was http://hostname:9080/.

Reporting

Report templates: Provides report templates for the suite components that you installed. The Information Center provides tables that detail the roles, permissions, and result formats for all report templates. For more information, refer to Available reports and templates.

Data source connectors

Connectors for Server jobs: You can now use IBM InfoSphere Connectors to create and run InfoSphere DataStage Server jobs.
Connector migration tool support: Provides support for InfoSphere DataStage server jobs.
z/OS® File Stage: You can now use the Z/OS File Stage to read and write mainframe resident native files seamlessly from InfoSphere Information Server, running on non-Z/OS platforms. This requires IBM InfoSphere Classic Federation.
Distributed Transaction Stage: Provides the following additional targets: ODBC and IBM WebSphere MQ.
Connectors support multiple input links: You can now create multiple input links and configure the write methods in the target connector stage.

New data integration project management tool

Blueprint Director is a new capability to further strengthen the alignment of business and IT allowing you to govern your information projects. The integration team can collaborate on actionable information blueprints that connect the business vision with the corresponding technical metadata. It directs the team to apply best practices based on reference architectures and methodology.

Moving assets between installations of InfoSphere Information Server

You can now use the istool command line to move the following types of assets between metadata repositories:

InfoSphere Business Glossary assets: Categories and terms and links to assigned assets and to stewards.
InfoSphere FastTrack assets: Project templates, projects, mapping specifications, mapping components, and their related assets.
InfoSphere Information Analyzer assets: Projects, data classes, and the common metadata and reports that are associated with projects.
Reporting assets: Reports and report results that are created in the InfoSphere Information Server console or Web console.
Security assets: Users, groups, roles, and credentials for InfoSphere Information Server.

In previous releases only InfoSphere DataStage and QualityStage assets and common metadata could be transferred between repositories. This new functionality lets you move a wide range of inter-related InfoSphere Information Server assets between development, test, and production environments. For more information, refer to Managing assets by using the command line.

Full command-line support for MetaBroker® and bridge imports

You can import metadata by using the command-line and automatically save your import parameters and filter settings to files for future command-line use. In previous releases, command-line support was limited and required manual creation of parameter XML files. For more information, refer to Importing metadata by using the command line.

Top | Next

IBM InfoSphere Business Glossary

Previous | Bottom

Enhanced InfoSphere Business Glossary Client for Eclipse integration with InfoSphere Data Architect: InfoSphere Data Architect users can view updated content from the InfoSphere Business Glossary and create assigned relationships between these terms and data models in InfoSphere Data Architect. You can exchange these relationships between InfoSphere Data Architect and the business glossary. For more information, refer to Integration with Eclipse-based software.
Enhanced integration with InfoSphere Information Analyzer: valid values: InfoSphere Business Glossary users can view the valid values and valid values reference tables that were used as the basis of InfoSphere Information Analyzer analysis of database columns. Previous versions included other InfoSphere Information Analyzer analysis information but not valid values or valid values reference tables. For information about how to view the details of an asset, which includes these values, refer to Viewing the properties of an asset.
Basic User role: This new user role enables InfoSphere Business Glossary business users to view only the information most relevant to them. Users who are assigned only this InfoSphere Business Glossary role can view business terms and their attributes. These users cannot view assigned assets and data sources. For more information, refer to Roles.
Business intelligence to business glossary import: You can now import a business intelligence (BI) model, such as an IBM Cognos® Framework Manager model or a BusinessObjects Universe and create a structured glossary from it. Because, in many cases, business intelligence reports represent the intersection of business language and key data, these models can be excellent sources for initial population of glossary content. For more information, refer to Automatically generating IBM InfoSphere Business Glossary content from business intelligence models.

Top | Next

IBM InfoSphere DataStage

Previous | Bottom

High-availability configuration for the engine tier: You can configure an active-passive configuration managed by high-availability cluster-management software, such as IBM Tivoli System Automation for Multiplatforms. For more information, refer to High availability configurations.
Recovering from an engine tier failover: New tools and features are available for ensuring the integrity of projects and data after an engine tier failover. For more information, refer to Engine tier failover recovery.
Integrating with your source control system: You can now use the InfoSphere Information Server Manager deployment tool to manage your InfoSphere DataStage and QualityStage jobs and associated assets in your source control system. The deployment tool gives you direct access to these source control systems: Concurrent Versions System (CVS) and IBM Rational® ClearCase®. You can use any source control system that supports the Eclipse Team programming interface. For more information, refer to Source control of InfoSphere DataStage and QualityStage assets.
Optimizing parallel jobs in the DataStage Designer client: Balanced Optimization is integrated into the Designer client to provide a more natural workflow for improving the performance of some IBM InfoSphere DataStage jobs. For more information, refer to Introduction to InfoSphere DataStage Balanced Optimization.
Enhancements to the Transformer stage: Null values can now be included in any expression, so you no longer need to explicitly handle null values in an expression. You can now define loops that output multiple output rows from every input row or that aggregate multiple input rows into a single input row. When your data is grouped on a column, and is sorted on that column, you can now detect when you process the last row before the value in that column changes. For more information, refer to Transformer stage.
Vertical pivoting: You can now specify a vertical pivoting operation in the Enterprise Pivot stage that maps a set of rows in the input data to single or multiple output columns. For more information, refer to Pivot Enterprise stage.
Multiple null field values on Import: You can now specify a set of values in the import data that are treated as null when importing data in stage such as the Sequential File stage. For more information, refer to Sequential File stage: Output link format tab.
Copying roles from an existing project.: You can now specify that a new project will inherit users, groups, and associated roles from an existing project. For more information, refer to Add Project window.
Environment variables created during import: When you import a job or parameter set that uses environment variables as parameters, if the environment variables do not already exist in the project, they will be created. for more information, refer to Importing previously exported objects.
Missing columns in the Data Set stage: You can now specify what action is taken for columns that are defined on the link but are not contained in the source data set. For more information, refer to Data Set stage: Options category.
Saving job logs to the metadata repository: When jobs run, you can save the logging information to the metadata repository of InfoSphere Information Server. For more information, refer to Enabling operational repository logging.
Improved support features in the Designer client: For more information, refer to Handling exceptions in the Designer client.

Top | Next

IBM InfoSphere FastTrack

Previous | Bottom

Data Rule Definitions: You can use Data Rule Definitions (DRDs) to define the relationships between data sources when creating mapping specifications. For more information, refer to Creating shared data rules.
Audit Trail history: You can track the history of changes made in InfoSphere FastTrack for auditing purposes. For more information, refer to Tracking changes for auditing.
Mapping report enhancements: For a large number of column mappings, you can now view the mapping report in a specific order, such as by user, by table, by status, or by date. For more information, refer to Reports for IBM InfoSphere FastTrack.
Improved mapping specification retrieval capability: You can now retrieve mapping specifications from an existing InfoSphere DataStage job into InfoSphere FastTrack as mapping specifications or mapping compositions. For more information, refer to Generating mapping specifications from IBM InfoSphere DataStage jobs.
Mapping compositions: A mapping composition is a new first order object that allows you to define a set of mapping specifications and their relationships. Previously, you could only define compositions real-time when generating jobs. Now you can name these mapping compositions and reuse them. For more information, refer to Populating mapping specifications with mapping components.
Specification Change Propagation updates: Use the InfoSphere FastTrack Generation Wizard to determine if a targeted, existing InfoSphere DataStage job can be annotated. InfoSphere FastTrack can now detect the delta between a current job generation selection and the history of the first job iteration. The delta is then propagated to the related InfoSphere DataStage job. For more information, refer to Specification change propagation.
Project template support: A Project Template is available to facilitate the sharing of settings and objects across projects. The Project Template has common settings and objects that can be used by multiple projects. For more information, refer to Using a project template.
Reject log records for lookup definitions: You can now choose to log the reject records in a sequential file stage by using the modified Lookup Definition feature in InfoSphere FastTrack. For more information, refer to Adding source columns to mapping specifications from lookup tables.
Hyperlink enhancements: URLs can be made into hyperlinks in fields that link to an external document. For more information, refer to Creating mapping specifications.
Context assistance feature: From the Expression Editor field, press <ctrl> + space to open a list of valid functions and fields from which you can select to populate the expression editor. For more information, refer to Applying rule expressions on columns to transform data.
Scope feature: Define the scope for your mapping specifications so that you can later select the metadata to use from a consolidated list. For more information, refer to Defining the scope for your mapping specifications.
Enhanced CSV Import: You can create a mapping specification from a CSV (Comma Separated Values) file by combining multiple columns in the input file to create one or more values, such as a multi-part name, for your mapping specification. For more information, refer to Import from CSV options.

Top | Next

IBM InfoSphere Information Analyzer

Previous | Bottom

IBM InfoSphere Information Analyzer can help you quickly and easily understand your data by offering data quality assessment, flexible data rules design and analysis, and quality monitoring capabilities. These insights can help you derive more information from enterprise data to accelerate information-centric projects.

Supports sharing of InfoSphere Information Analyzer source analysis results through InfoSphere Business Glossary

In addition to the existing summary results, you can now gain better understanding and insight into the valid values of source columns that are represented by business terms. For more information, refer to Metadata management and Local and global variables.

Expanded collaboration between business users and IT

You now have an improved ability to link data sources directly to business terms in InfoSphere Information Analyzer and deliver broader understanding of data to business users through context-sensitive menu options, enhanced term browsers, and presentation of terms with standard data classifications. For more information, refer to Creating, editing, or deleting InfoSphere Business Glossary terms.

Supports asset interchange for projects and analysis results

You can now export InfoSphere Information Analyzer project information to other InfoSphere Information Server environments to maximize distribution of data analysis insights. For more information, refer to InfoSphere Information Analyzer assets.

Supports import of published analysis results

You can import the analysis results from other IBM InfoSphere Information Server products to view high-level analysis results within InfoSphere Information Analyzer. For more information, refer to Importing published analysis results.

Supports native Oracle connectivity

You can leverage native connectors when you run column analyses, key analyses, and data rules. Teradata, DB2, and Oracle connectors are supported in addition to ODBC connectors.

Supports external retrieval of information via HTTP API and HTTPS protocol

You can now develop applications with the Hypertext Transfer Protocol (HTTP) application programming interface (API) to access and analyze InfoSphere Information Analyzer content. You can:

Integrate InfoSphere Information Analyzer content in other non-IBM environments without the use of the rich client. For example, you can:
- Run column analysis or data rules from a web portal and retrieve the results
- Program the batch execution of a large set of rules and column analysis tasks with a non-IBM scheduler
Generate custom reports, such as combine the results of several requests in one report and use XSLTs to format it as needed
Deploy InfoSphere Information Analyzer project templates without the use of the rich client. For example, you can:
- Extract existing projects as XML documents
- Edit the XML manually if necessary and use source control to track changes
- Deploy on different systems. The rules are automatically recreated.

For more information, refer to Developing applications with the HTTP API.

Top | Next

IBM InfoSphere Information Services Director

Previous | Bottom

New command line interface tool: The InfoSphere Information Services Director administration and deployment command line interface can be used to administer InfoSphere Information Services Director resources such as applications and services. You can use this tool to move these resources between different installations of InfoSphere Information Services Director, deploy and undeploy these resources, and modify the metadata information in these resources. For more information, refer to IBM InfoSphere Information Services Director command line interface.
New service bindings: The Text over HTTP binding allows you to customize the format of messages for HTTP requests and response payloads to conform to specific requirements. When you use the Text over HTTP binding, you can send data in SOAP, XML, text, or any other format to InfoSphere Information Services Director by transforming the request payload. You can apply your own XSLT stylesheet before it is sent to the information provider to transform the request payload. The XSLT style sheet that you provide is also used to transform the information provider results before it is returned in the HTTP response. For more information, refer to Text over HTTP service binding.; For distributed hypermedia systems, IBM InfoSphere Information Services Director can generate services that use a REST (Representational State Transfer) invocation: HTTP GET, POST, PUT, and DELETE for service requests and XML or JSON format for the response. For more information, refer to REST 2.0 service binding.

Top | Next

IBM InfoSphere Metadata Workbench

Previous | Bottom

Supports InfoSphere FastTrack mapping specifications for extended data lineage: InfoSphere FastTrack allows the documentation of source-to-target mappings, which are similar to extended mapping documents in the metadata workbench. Business users can directly use InfoSphere FastTrack mapping specifications for extended data lineage without the need to import the mapping specification. For more information, refer to Configuring InfoSphere FastTrack mapping specifications for lineage reports.
Supports IBM InfoSphere Discovery mapping specifications for extended data lineage: InfoSphere Discovery allows the documentation of source-to-target mappings, which are similar to extended mapping documents in the metadata workbench. Business users can import InfoSphere Discovery mapping specifications for extended data lineage by using a configuration file in an XML format. The configuration file defines which InfoSphere Discovery column corresponds to which column the metadata workbench. For more information, refer to Importing extension mapping documents and their mappings.
Business Intelligence assets can be deleted using the Repository Management tab of the Web console: Business Intelligence (BI) assets can no longer be deleted in the Metadata Workbench. This functionality is available in the Repository Management tab of the Web console, where all types of common metadata can be browsed and deleted. For more information, refer to Managing metadata assets.

Top | Next

IBM InfoSphere QualityStage

Previous | Bottom

Combinable processing: The Designer client now combines the process that executes in the Standardize stage with other operators in a job so that you have one process rather than a series of processes linked. You can reduce the footprint of the job by minimizing the number of processes that have to be started. Multi-national Standardize (MNS) reaps benefits from the combinable Standardize operator. For more information, refer to Advanced tab.
Locale tokenization: You can specify the regional setting that you want to use in a rule set, and thereby indicate the way you want tokens handled. For more information, refer to Specifying the tokenizer.
Reporting for data standardization: You can use the reports to assess the results of your standardization process and verify that the results meet your data quality objectives. For more information, refer to Standardization Quality Assessment (SQA) reports.
Additional rules: Rules enforce consistency and quality on the data that they are applied against. You now have an increased number of rule sets to handle country or region-specific data. For the list of predefined rule sets, refer to Predefined rule sets. For information on how to install rules from asset interchange, refer to Importing rule sets.
Additional rules tools: You can install a rule set development package from asset interchange. The package includes Standardization rules templates for domains, a Standardization rules development kit, and a Standardization quality assessment kit. For more information on the package, refer to Predefined rule sets.
Enhanced Match Designer: The content, format, and functionality within the Match Designer is enhanced for ease of use and provides more insight into the matching process. For example, you can view all match types and reorder Blocking Columns and Match Commands. For more information on creating match passes, refer to Defining match passes.
Match performance improvements: You can make changes to the Sort stage in the InfoSphere DataStage and QualityStage Designer client to improve match performance.
Easily consumable reports for match specifications: Use the reports to obtain sign off from business stakeholders on the criteria used to match data. For more information, refer to Reporting for match specifications.

Top

Documentation introduced with Version 8.5

Previous | Bottom

The following documentation additions and changes were introduced with InfoSphere Information Server Version 8.5.

Reorganization of the Planning, Installation, and Configuration information

The Installation information has been reorganized into the following sections so that it is easier for you to use:

The installation information now also includes checklists that guide the user through the installation of InfoSphere Information Server in various server topologies and configurations.

New troubleshooting topics

Added new troubleshooting topics for:

General installation
Authentication
Application server issues
Reporting
Client and console
Software removal problems

For more information, refer to Troubleshooting installations.

Overview of IBM InfoSphere Information Server

IBM InfoSphere Foundation Tools: Added an overview of the IBM InfoSphere Foundation Tools. For more information, refer to InfoSphere Foundation Tools.
Overview of high availability and clustering: Added an overview of high availability and clustering. For more information, refer to Scalability of the common services tier and High availability in InfoSphere Information Server.
New Auditing Service: The Auditing Service creates an audit trail of security-related events. This includes all activities that set or modify security-related settings and all user authentications and application logins. You can configure which audit events to log and how much information to include based on auditing requirements. For an overview of the new Auditing Service, refer to Administrative services.
IBM InfoSphere Classic Federation: Added an overview of IBM InfoSphere Classic. For more information, refer to Classic products overview.
Integration scenarios: The integration scenarios focus on data quality within a data warehouse implementation. For more information, refer to InfoSphere Information Server integration scenarios.

InfoSphere Information Analyzer methodology and best practices: Includes a new section of methodology and best practices for working with rules in InfoSphere Information Analyzer by using defined and documented methods. For more information, refer to Data quality methodology.

InfoSphere QualityStage

Easily available pattern-action language information: Now integrated into the Information Center. See IBM InfoSphere QualityStage Pattern Action reference.
Major changes to the user's guide: Includes a walk through of the overall InfoSphere QualityStage methodology, new information about using match weight overrides, and elaboration on concepts such as match. For more information, refer to InfoSphere QualityStage methodology and the topics that immediately follow the topic. For information on match weight overrides, refer to Specifying weight overrides. For information that explains key concepts of match, refer to the topics such as Match: key concepts, How match manages large volumes of data, and Weights and record comparisons.
New information about designing real-time jobs: Enhanced the InfoSphere Information Services Director user's guide. See Designing IBM InfoSphere DataStage and QualityStage jobs as services.
Updated information about rule sets: Included a comprehensive listing of predefined rule sets and rule set characteristics. For the list of predefined rule sets, refer to Predefined rule sets. For rule set characteristics, refer to the topics under Categories of rule sets. For characteristics of Japanese-specific rule sets, refer to Characteristics of Japanese rule sets.

Top

DataStage Parallel Processing

2011-02-08T08:30:00.001+05:30

Following figure represents one of the simplest jobs you could have — a data source,
a Transformer (conversion) stage, and the data target. The links between the stages represent the flow of data into or out of a stage. In a parallel job, each stage would normally (but not always) correspond to a process. You can have multiple instances of each process to run on the available processors in your system.

A parallel DataStage job incorporates two basic types of parallel processing — pipeline and partitioning. Both of these methods are used at runtime by the Information Server engine to execute the simple job shown in Figure 1-8. To the DataStage developer, this job would appear the same on your Designer canvas, but you can optimize it through advanced properties.

Pipeline parallelism
In the following example, all stages run concurrently, even in a single-node configuration. As data is read from the Oracle source, it is passed to the Transformer stage for transformation, where it is then passed to the DB2 target. Instead of waiting for all source data to be read, as soon as the source data stream starts to produce rows, these are passed to the subsequent stages. This method is called pipeline parallelism, and all three stages in our example operate simultaneously regardless of the degree of parallelism of the configuration file. The Information Server Engine always executes jobs with pipeline parallelism.

If you ran the example job on a system with multiple processors, the stage reading would start on one processor and start filling a pipeline with the data it had read. The transformer stage would start running as soon as there was data in the pipeline, process it and start filling another pipeline. The stage writing the transformed data to the target database would similarly start writing as soon as there was data available. Thus all three stages are operating simultaneously.

Partition parallelism
When large volumes of data are involved, you can use the power of parallel processing to your best advantage by partitioning the data into a number of separate sets, with each partition being handled by a separate instance of the job stages. Partition parallelism is accomplished at runtime, instead of a manual process that would be required by traditional systems.

The DataStage developer only needs to specify the algorithm to partition the data, not the degree of parallelism or where the job will execute. Using partition parallelism the same job would effectively be run simultaneously by several processors, each handling a separate subset of the total data. At the end of the job the data partitions can be collected back together again and written to a single data source. This is shown in following figure.

Attention: You do not need multiple processors to run in parallel. A single processor is capable of running multiple concurrent processes.

Partition parallelism [Combining pipeline and partition parallelism]
The Information Server engine combines pipeline and partition parallel processing to achieve even greater performance gains. In this scenario you would have stages processing partitioned data and filling pipelines so the next one could start on that partition before the previous one had finished. This is shown in the following figure.

In some circumstances you might want to actually re-partition your data between stages. This could happen, for example, where you want to group data differently. Suppose that you have initially processed data based on customer last name, but now you want to process on data grouped by zip code. You will have to re-partition to ensure that all customers sharing the same zip code are in the same group. DataStage allows you to re-partition between stages as and when necessary. With the Information Server engine, re-partitioning happens in memory between stages, instead of writing to disk.

DataStage Best Practices

2011-02-03T20:57:00.001+05:30

This section provides an overview of recommendations for standard practices.
The recommendations are categorized as follows:
* Standards
* Development guidelines
* Component usage
* DataStage Data Types
* Partitioning data
* Collecting data
* Sorting
* Stage specific guidelines

Standards

It is important to establish and follow consistent standards in:
* Directory structures for installation and application support directories.
* Naming conventions, especially for DataStage Project categories, stage names, and links.
All DataStage jobs should be documented with the Short Description field, as well as Annotation fields.
It is the DataStage developer’s responsibility to make personal backups of their work on their local workstation, using DataStage's DSX export capability. This can also be used for integration with source code control systems.
Note: A detailed discussion of these practices is beyond the scope of this Redbooks publication, and you should speak to your Account Executive to engage IBM IPS Services.

Development guidelines

Modular development techniques should be used to maximize re-use of DataStage jobs and components:
* Job parameterization allows a single job design to process similar logic instead of creating multiple copies of the same job. The Multiple-Instance job property allows multiple invocations of the same job to run simultaneously.
* A set of standard job parameters should be used in DataStage jobs for source and target database parameters (DSN, user, password, etc.) and directories where files are stored. To ease re-use, these standard parameters and settings should be made part of a Designer Job Parameter Sets.
* Create a standard directory structure outside of the DataStage project directory for source and target files, intermediate work files, and so forth.
* Where possible, create re-usable components such as parallel shared containers to encapsulate frequently-used logic.
* DataStage Template jobs should be created with:
– Standard parameters such as source and target file paths, and database login properties
– Environment variables and their default settings
– Annotation blocks
* Job Parameters should always be used for file paths, file names, database login settings.
* Standardized Error Handling routines should be followed to capture errors and rejects.

Component usage

The following guidelines should be followed when constructing parallel jobs in IBM InfoSphere DataStage Enterprise Edition:
* Never use Server Edition components (BASIC Transformer, Server Shared Containers) within a parallel job. BASIC Routines are appropriate only for job control sequences.
* Always use parallel Data Sets for intermediate storage between jobs unless that specific data also needs to be shared with other applications.
* Use the Copy stage as a placeholder for iterative design, and to facilitate default type conversions.
* Use the parallel Transformer stage (not the BASIC Transformer) instead of the Filter or Switch stages.
Chapter 1. IBM InfoSphere DataStage overview 29
* Use BuildOp stages only when logic cannot be implemented in the parallel Transformer.

DataStage data types

The following guidelines should be followed with DataStage data types:
* Be aware of the mapping between DataStage (SQL) data types and the internal DS/EE data types. If possible, import table definitions for source databases using the Orchestrate Schema Importer (orchdbutil) utility.
* Leverage default type conversions using the Copy stage or across the Output mapping tab of other stages.

Partitioning data

In most cases, the default partitioning method (Auto) is appropriate. With Auto partitioning, the Information Server Engine will choose the type of partitioning at runtime based on stage requirements, degree of parallelism, and source and target systems. While Auto partitioning will generally give correct results, it might not give optimized performance. As the job developer, you have visibility into requirements, and can optimize within a job and across job flows. Given the numerous options for keyless and keyed partitioning, the following objectives form a methodology for assigning partitioning:

Objective 1
Choose a partitioning method that gives close to an equal number of rows in each partition, while minimizing overhead. This ensures that the processing workload is evenly balanced, minimizing overall run time.

Objective 2
The partition method must match the business requirements and stage functional requirements, assigning related records to the same partition if required. Any stage that processes groups of related records (generally using one or more key columns) must be partitioned using a keyed partition method. This includes, but is not limited to: Aggregator, Change Capture, Change Apply, Join, Merge, Remove Duplicates, and Sort stages. It might also be necessary for Transformers and BuildOps that process groups of related records.

Objective 3
Unless partition distribution is highly skewed, minimize re-partitioning, specially in cluster or Grid configurations. Re-partitioning data in a cluster or Grid configuration incurs the overhead of network transport.

Objective 4
Partition method should not be overly complex. The simplest method that meets the above objectives will generally be the most efficient and yield the best performance. Using the above objectives as a guide, the following methodology can be
applied:
a. Start with Auto partitioning (the default).
b. Specify Hash partitioning for stages that require groups of related records
as follows:
• Specify only the key column(s) that are necessary for correct grouping as long as the number of unique values is sufficient
• Use Modulus partitioning if the grouping is on a single integer key column
• Use Range partitioning if the data is highly skewed and the key column values and distribution do not change significantly over time (Range Map can be reused)
c. If grouping is not required, use Round Robin partitioning to redistribute data equally across all partitions.
• Especially useful if the input Data Set is highly skewed or sequential
d. Use Same partitioning to optimize end-to-end partitioning and to minimize re-partitioning
• Be mindful that Same partitioning retains the degree of parallelism of the upstream stage
• Within a flow, examine up-stream partitioning and sort order and attempt to preserve for down-stream processing. This may require re-examining key column usage within stages and re-ordering stages within a flow (if business requirements permit).
Note: In satisfying the requirements of this second objective, it might not be possible to choose a partitioning method that gives an almost equal number of rows in each partition.

Across jobs, persistent Data Sets can be used to retain the partitioning and sort
order. This is particularly useful if downstream jobs are run with the same degree
of parallelism (configuration file) and require the same partition and sort order.

Collecting data

Given the options for collecting data into a sequential stream, the following guidelines form a methodology for choosing the appropriate collector type:
1. When output order does not matter, use Auto partitioning (the default).
2. Consider how the input Data Set has been sorted:
– When the input Data Set has been sorted in parallel, use Sort Merge collector to produce a single, globally sorted stream of rows.
– When the input Data Set has been sorted in parallel and Range partitioned, the Ordered collector might be more efficient.
3. Use a Round Robin collector to reconstruct rows in input order for round-robin partitioned input Data Sets, as long as the Data Set has not been re-partitioned or reduced.

Sorting

Apply the following methodology when sorting in an IBM InfoSphere DataStage Enterprise Edition data flow:
1. Start with a link sort.
2. Specify only necessary key column(s).
3. Do not use Stable Sort unless needed.
4. Use a stand-alone Sort stage instead of a Link sort for options that are not available on a Link sort:
– The “Restrict Memory Usage” option should be included here. If you want more memory available for the sort, you can only set that via the Sort Stage — not on a sort link. The environment variable $APT_TSORT_STRESS_BLOCKSIZE can also be used to set sort memory usage (in MB) per partition.
– Sort Key Mode, Create Cluster Key Change Column, Create Key Change Column, Output Statistics.
– Always specify “DataStage” Sort Utility for standalone Sort stages.
– Use the “Sort Key Mode=Don’t Sort (Previously Sorted)” to resort a sub-grouping of a previously-sorted input Data Set.
5. Be aware of automatically-inserted sorts:
– Set $APT_SORT_INSERTION_CHECK_ONLY to verify but not establish required sort order.
6. Minimize the use of sorts within a job flow.
7. To generate a single, sequential ordered result set, use a parallel Sort and a
Sort Merge collector.

Stage specific guidelines

The guidelines by stage are as follows:

Transformer
Take precautions when using expressions or derivations on nullable columns within the parallel Transformer:
– Always convert nullable columns to in-band values before using them in an expression or derivation.
– Always place a reject link on a parallel Transformer to capture / audit possible rejects.

Lookup
It is most appropriate when reference data is small enough to fit into available shared memory. If the Data Sets are larger than available memory resources, use the Join or Merge stage. Limit the use of database Sparse Lookups to scenarios where the number of input rows is significantly smaller (for example 1:100 or more) than the number of reference rows, or when exception processing.

Join
Be particularly careful to observe the nullability properties for input links to any form of Outer Join. Even if the source data is not nullable, the non-key columns must be defined as nullable in the Join stage input in order to identify unmatched records.

Aggregators
Use Hash method Aggregators only when the number of distinct key column values is small. A Sort method Aggregator should be used when the number of distinct key values is large or unknown.

Database Stages
The following guidelines apply to database stages:
– Where possible, use the Connector stages or native parallel database stages for maximum performance and scalability.
– The ODBC Connector and ODBC Enterprise stages should only be used when a native parallel stage is not available for the given source or target database.
– When using Oracle, DB2, or Informix databases, use Orchestrate Schema Importer (orchdbutil) to properly import design metadata.
– Take care to observe the data type mappings.
– If possible, use an SQL where clause to limit the number of rows sent to a
DataStage job.
– Avoid the use of database stored procedures on a per-row basis within a high-volume data flow. For maximum scalability and parallel performance, it is best to implement business rules natively using DataStage parallel components.

DataStage OSH Script

2011-01-26T07:58:00.000+05:30

The IBM InfoSphere DataStage and QualityStage Designer client creates IBM InfoSphere DataStage jobs that are compiled into parallel job flows, and reusable components that execute on the parallel Information Server engine. It allows you to use familiar graphical point-and-click techniques to develop job flows for extracting, cleansing, transforming, integrating, and loading data into target files, target systems, or packaged applications.

The Designer generates all the code. It generates the OSH (Orchestrate SHell Script) and C++ code for any Transformer stages used.
Briefly, the Designer performs the following tasks:
* Validates link requirements, mandatory stage options, transformer logic, etc.
* Generates OSH representation of data flows and stages (representations of
framework “operators”).
* Generates transform code for each Transformer stage which is then compiled
into C++ and then to corresponding native operators.
* Reusable BuildOp stages can be compiled using the Designer GUI or from
the command line.
Here is a brief primer on the OSH:
* Comment blocks introduce each operator, the order of which is determined by
the order stages were added to the canvas.
* OSH uses the familiar syntax of the UNIX shell. such as Operator name,
schema, operator options (“-name value” format), input (indicated by n< where n is the input#), and output (indicated by the n> where n is the output #).
* For every operator, input and/or output data sets are numbered sequentially
starting from zero.
* Virtual data sets (in memory native representation of data links) are
generated to connect operators.

Framework (Information Server Engine) terms and DataStage terms have equivalency. The GUI frequently uses terms from both paradigms. Runtime messages use framework terminology because the framework engine is where execution occurs. The following list shows the equivalency between framework and DataStage terms:
* Schema corresponds to table definition
* Property corresponds to format
* Type corresponds to SQL type and length
* Virtual data set corresponds to link
* Record/field corresponds to row/column
* Operator corresponds to stage

Note: The actual execution order of operators is dictated by input/output designators, and not by their placement on the diagram. The data sets connect the OSH operators. These are “virtual data sets”, that is, in memory data flows. Link names are used in data set names — it is therefore good practice to give the links meaningful names.

DataStage Modules

2011-01-15T20:51:00.000+05:30

The DataStage Client components:

Administrator :- Administers DataStage projects, manages global settings and interacts with the system. Administrator is used to specify general server defaults, add and delete projects, set up project properties and provides a command interface to the datastage repository.
With Datastage Administrator users can set job monitoring limits, user privileges, job scheduling options and parallel jobs default.
Designer :- used to create DataStage jobs which are compiled into executable programs. is a graphical, user-friendly application which applies visual data flow method to develop job flows for extracting, cleansing, transforming, integrating and loading data. It’s a module mainly used by Datastage developers.
Manager :- it's a main interface to the Datastage Repository, allows its browsing and editing. It displays tables and files layouts, routines, transforms and jobs defined in the project. It is mainly used to store and manage reusable metadata.
Director :- manages running, validating, scheduling and monitoring DataStage jobs. It’s mainly used by operators and testers.

Datastage Administrator view and project properties

Datastage Designer view with a job sequence

Datastage Manager view

DataStage Execution Flow

2011-01-02T06:50:00.002+05:30

When you execute a job, the generated OSH and contents of the configuration file ($APT_CONFIG_FILE) is used to compose a “score”. This is similar to a SQL query optimization plan.

At runtime, IBM InfoSphere DataStage identifies the degree of parallelism and node assignments for each operator, and inserts sorts and partitioners as needed to ensure correct results. It also defines the connection topology (virtual data sets/links) between adjacent operators/stages, and inserts buffer operators to prevent deadlocks (for example, in fork-joins). It also defines the number of actual OS processes. Multiple operators/stages are combined within a single OS process as appropriate, to improve performance and optimize resource requirements.

The job score is used to fork processes with communication interconnects for data, message and control3. Processing begins after the job score and processes are created. Job processing ends when either the last row of data is processed by the final operator, a fatal error is encountered by any operator, or the job is halted by DataStage Job Control or human intervention such as DataStage Director STOP.

Job scores are divided into two sections — data sets (partitioning and collecting) and operators (node/operator mapping). Both sections identify sequential or parallel processing.

The execution (orchestra) manages control and message flow across processes and consists of the conductor node and one or more processing nodes as shown in Figure 1-6. Actual data flows from player to player — the conductor and section leader are only used to control process execution through control and message channels.

Conductor is the initial framework process. It creates the Section Leader (SL) processes (one per node), consolidates messages to the DataStage log, and manages orderly shutdown. The Conductor node has the start-up process. The Conductor also communicates with the players.

Note: You can direct the score to a job log by setting $APT_DUMP_SCORE. To identify the Score dump, look for “main program: This step....”.

Section Leader is a process that forks player processes (one per stage) and manages up/down communications. SLs communicate between the conductor and player processes only. For a given parallel configuration file, one section leader will be started for each logical node.

Players are the actual processes associated with the stages. It sends stderr and stdout to the SL, establishes connections to other players for data flow, and cleans up on completion. Each player has to be able to communicate with every other player. There are separate communication channels (pathways) for control, errors, messages and data. The data channel does not go through the section eader/conductor as this would limit scalability.

Data flows directly from upstream operator to downstream operator.

Data Transformations

2010-12-21T07:49:00.001+05:30

Data transformation and movement is the process by which source data is selected, converted, and mapped to the format required by targeted systems. The process manipulates data to bring it into compliance with business, domain, and integrity rules and with other data in the target environment. Transformation can take some of the following forms:

Aggregation
Consolidating or summarizing data values into a single value. Collecting daily sales data to be aggregated to the weekly level is a common example of aggregation.

Basic conversion
Ensuring that data types are correctly converted and mapped from source to target columns.

Cleansing
Resolving inconsistencies and fixing the anomalies in source data.

Derivation
Transforming data from multiple sources by using a complex business rule or algorithm.

Enrichment
Combining data from internal or external sources to provide additional meaning to the data.

Normalizing
Reducing the amount of redundant and potentially duplicated data.
Combining
The process of combining data from multiple sources via parallel Lookup, Join, or Merge operations.

Pivoting
Converting records in an input stream to many records in the appropriate table in the data warehouse or data mart.

Sorting
Grouping related records and sequencing data based on data or string values.

DataStage Stages and Jobs

2010-12-07T21:48:00.003+05:30

An IBM InfoSphere DataStage job consists of individual stages linked together which describe the flow of data from a data source to a data target. A stage usually has at least one data input and/or one data output. However, some stages can accept more than one data input, and output to more than one stage. Each stage has a set of predefined and editable properties that tell it how to perform or process data. Properties might include the file name for the Sequential File stage, the columns to sort, the transformations to perform, and the database table name for the DB2 stage. These properties are viewed or edited using stage editors. Stages are added to a job and linked together using the Designer. Figure shows some of the stages and their iconic representations.

Stages and links can be grouped in a shared container. Instances of the shared container can then be reused in different parallel jobs. You can also define a local container within a job — this groups stages and links into a single unit, but can only be used within the job in which it is defined. The different types of jobs have different stage types. The stages that are available in the Designer depend on the type of job that is currently open in the Designer. Parallel Job stages are organized into different groups on the Designer palette as follows:
General includes stages such as Container and Link. Data Quality includes stages such as Investigate, Standardize, Reference Match, and Survive.

Database includes stages such as Classic Federation, DB2 UDB, DB2 UDB/Enterprise, Oracle, Sybase, SQL Server®, Teradata, Distributed Transaction, and ODBC.
Development/Debug includes stages such as Peek, Sample, Head, Tail, and Row Generator.
File includes stages such as Complex Flat File, Data Set, Lookup File Set, and Sequential File.
Processing includes stages such as Aggregator, Copy, FTP, Funnel, Join, Lookup, Merge, Remove Duplicates, Slowly Changing Dimension, Surrogate Key Generator, Sort, and Transformer
Real Time includes stages such as Web Services Transformer, WebSphere MQ, and Web Services Client.
Restructure includes stages such as Column Export and Column Import.

DataStage Functions

2010-11-23T08:45:00.043+05:30

Bottom
In its simplest form, IBM InfoSphere DataStage performs data transformation and movement from source systems to target systems in batch and in real time. The data sources might include indexed files, sequential files, relational databases, archives, external data sources, enterprise applications, and message queues.

DataStage manages data that arrives and data that is received on a periodic or scheduled basis. It enables companies to solve large-scale business problems with high-performance processing of massive data volumes. By leveraging the parallel processing capabilities of multiprocessor hardware platforms, DataStage can scale to satisfy the demands of ever-growing data volumes, stringent real-time requirements, and ever-shrinking batch windows.

Leveraging the combined suite of IBM Information Server, DataStage can simplify the development of authoritative master data by showing where and how information is stored across source systems. DataStage can also consolidate disparate data into a single, reliable record, cleanses and standardizes information, removes duplicates, and links records together across systems. This master record can be loaded into operational data stores, data warehouses, or master data applications such as IBM MDM using IBM InfoSphere DataStage.
IBM InfoSphere DataStage delivers four core capabilities:

* Connectivity to a wide range of mainframe, legacy, and enterprise applications, databases, file formats, and external information sources.

* Prebuilt library of more than 300 functions including data validation rules and very complex transformations.

* Maximum throughput using a parallel, high-performance processing architecture.

* Enterprise-class capabilities for development, deployment, maintenance, and high-availability. It leverages metadata for analysis and maintenance. It also operates in batch, real time, or as a Web service.
IBM InfoSphere DataStage enables an integral part of the information integration process.

Functions used in IBM InfoSphere DataStage and QualityStage

The functions that are valid in IBM® InfoSphere® DataStage® and QualityStage are also valid in IBM Information Server
FastTrack, The following is a list of functions that are generally used when defining a column derivation in a Transformer stage.

Date and time functions
Logical functions
Mathematical functions
Null handling functions
Number functions
Raw functions
String functions
Vector function
Type conversion functions
Type casting functions
Utility functions

Top | Next

Date and time functions

Bottom
The following table lists the functions that are available in the Date and Time category (Square brackets indicate an argument is optional):

Name	Description	Arguments	Output
DateFromDaysSince	Returns a date by adding an integer to a baseline date	number (int32) [baseline date]	date
DateFromJulianDay	Returns a date from the given julian date	juliandate (uint32)	date
DaysSinceFromDate	Returns the number of days from source date to the given date	source_date given_date	days since (int32)
HoursFromTime	Returns the hour portion of a time	time	hours (int8)
JulianDayFromDate	Returns julian day from the given date	date	julian date (int32)
MicroSecondsFromTime	Returns the microsecond portion from a time	time	microseconds (int32)
MinutesFromTime	Returns the minute portion from a time	time	minutes (int8)
MonthDayFromDate	Returns the day of the month given the date	date	day (int8)
MonthFromDate	Returns the month number given the date	date	month number (int8)
NextWeekdayFromDate	Returns the date of the specified day of the week soonest after the source date	source date day of week (string)	date
PreviousWeekdayFromDate	Returns the date of the specified day of the week most recent before the source date	source date day of week (string)	date
SecondsFromTime	Returns the second portion from a time	time	seconds (dfloat)
SecondsSinceFromTimestamp	Returns the number of seconds between two timestamps	timestamp base timestamp	seconds (dfloat)
TimeDate	Returns the system time and date as a formatted string	-	system time and date (string)
TimeFromMidnightSeconds	Returns the time given the number of seconds since midnight	seconds (dfloat)	time
TimestampFromDateTime	Returns a timestamp form the given date and time	date time	timestamp
TimestampFromSecondsSince	Returns the timestamp from the number of seconds from the base timestamp	seconds (dfloat) [base timestamp]	timestamp
TimestampFromTimet	Returns a timestamp from the given unix time_t value	timet (int32)	timestamp
TimetFromTimestamp	Returns a unix time_t value from the given timestamp	timestamp	timet (int32)
WeekdayFromDate	Returns the day number of the week from the given date. Origin day optionally specifies the day regarded as the first in the week and is Sunday by default	date [origin day]	day (int8)
YeardayFromDate	Returns the day number in the year from the given date	date	day (int16)
YearFromDate	Returns the year from the given date	date	year (int16)
YearweekFromDate	Returns the week number in the year from the given date	date	week (int16)

Date, Time, and Timestamp functions that specify dates, times, or timestamps in the argument use strings with specific formats: For a date, the format is %yyyy-%mm-%dd For a time, the format is %hh:%nn:%ss, or, if extended to include microseconds, %hh:%nn:%ss.x where x gives the number of decimal places seconds is given to. For a timestamp the format is %yyyy-%mm-%dd %hh:%nn:%ss, or, if extended to include microseconds, %yyyy-%mm-%dd %hh:%nn:%ss.x where x gives the number of decimal places seconds is given to. This applies to the arguments date, baseline date, given date, time, timestamp, and base timestamp. Functions that have days of week in the argument take a string specifying the day of the week, this applies to day of week and origin day.

Top | Next

Logical Functions

Previous | Bottom
The following table lists the functions available in the Logical category (square brackets indicate an argument is optional):

Name	Description	Arguments	Output
Not	Returns the complement of the logical value of an expression	expression	Complement (int8)
BitAnd	Returns the bitwise AND of the two integer arguments	number 1 (uint64) number 2 (uint64)	number (uint64)
BitOr	Returns the bitwise OR of the two integer arguments	number 1 (uint64) number 2 (uint64)	number (uint64)
BitXOr	Returns the bitwise Exclusive OR of the two integer arguments	number 1 (uint64) number 2 (uint64)	number (uint64)
BitExpand	Returns a string containing the binary representation in "1"s and "0"s of the given integer	number (uint64)	string
BitCompress	Returns the integer made from the string argument, which contains a binary representation of "1"s and "0"s.	number (string)	number (uint64)
SetBit	Returns an integer with specific bits set to a specific state, where origfield is the input value to perform the action on, bitlist is a string containing a list of comma separated bit numbers to set the state of, and bitstate is either 1 or 0, indicating which state to set those bits.	origfield (uint64) bitlist (string) bitstate (uint8)	number (uint64)

Top | Next

Mathematical Functions

Previous | Bottom
The following table lists the functions available in the Mathematical category (square brackets indicate an argument is optional):

Name	Description	Arguments	Output
Abs	Absolute value of any numeric expression	number (int32)	result (dfloat)
Acos	Calculates the trigonometric arc-cosine of an expression	number (dfloat)	result (dfloat)
Asin	Calculates the trigonometric arc-sine of an expression	number (dfloat)	result (dfloat)
Atan	Calculates the trigonometric arc-tangent of an expression	number (dfloat)	result (dfloat)
Ceil	Calculates the smallest dfloat value greater than or equal to the given decimal value	number (decimal)	result (dfloat)
Cos	Calculates the trigonometric cosine of an expression	number (dfloat)	result (dfloat)
Cosh	Calculates the hyperbolic cosine of an expression	number (dfloat)	result (dfloat)
Div	Outputs the whole part of the real division of two real numbers (dividend, divisor)	dividend (dfloat) divisor (dfloat)	result (dfloat)
Exp	Calculates the result of base 'e' raised to the power designated by the value of the expression	number (dfloat)	result (dfloat)
Fabs	Calculates the absolute value of the given value	number (dfloat)	result (dfloat)
Floor	Calculates the largest dfloat value less than or equal to the given decimal value	number (decimal)	result (dfloat)
Ldexp	Calculates a number from an exponent and mantissa	mantissa (dfloat) exponent (int32)	result (dfloat)
Llabs	Returns the absolute value of the given integer	number (uint64)	result (int64)
Ln	Calculates the natural logarithm of an expression in base 'e'	number (dfloat)	result (dfloat)
Log10	Returns the log to the base 10 of the given value	number (dfloat)	result (dfloat)
Max	Returns the greater of the two argument values	number 1 (int32) number 2(int32)	result (int32)
Min	Returns the lower of the two argument values	number 1 (int32) number 2 (int32)	result (int32)
Mod	Calculates the modulo (the remainder) of two expressions (dividend, divisor)	dividend (int32) divisor (int32)	result (int32)
Neg	Negate a number	number (dfloat)	result (dfloat)
Pwr	Calculates the value of an expression when raised to a specified power (expression, power)	expression (dfloat) power (dfloat)	result (dfloat)
Rand	Return a psuedo random integer between 0 and 2³²-1	-	result (uint32)
Random	Returns a random number between 0 2³²-1	-	result (uint32)
Sin	Calculates the trigonometric sine of an angle	number (dfloat)	result (dfloat)
Sinh	Calculates the hyperbolic sine of an expression	number (dfloat)	result (dfloat)
Sqrt	Calculates the square root of a number	number (dfloat)	result (dfloat)
Tan	Calculates the trigonometric tangent of an angle	number (dfloat)	result (dfloat)
Tanh	Calculates the hyperbolic tangent of an expression	number (dfloat)	result (dfloat)

Top | Next

Null handling functions

Previous | Bottom
The following table lists the functions available in the Null Handling category (square brackets indicate an argument is optional):

Name	Description	Arguments	Output
IsNotNull	Returns true when an expression does not evaluate to the null value	any	true/false (int8)
IsNull	Returns true when an expression evaluates to the null value	any	true/false (int8)
MakeNull	Change an in-band null to out of band null	any (column) string (string)	-
NullToEmpty	Returns an empty string if input column is null, otherwise returns the input column value	input column	input column value or empty string
NullToZero	Returns zero if input column is null, otherwise returns the input column value	input column	input column value or zero
NullToValue	Returns specified value if input column is null, otherwise returns the input column value	input column, value	input column value or value
SetNull	Assign a null value to the target column	-	-

Hint: true = 1 false = 0

Top | Next

Number functions

Previous | Bottom
The following table lists the functions available in the Number category (square brackets indicate an argument is optional):

Name	Description	Arguments	Output
MantissaFromDecimal	Returns the mantissa from the given decimal	number (decimal)	result (dfloat)
MantissaFromDFloat	Returns the mantissa from the given dfloat	number (dfloat)	result (dfloat)

Top | Next

Raw functions

Previous | Bottom
The following table lists the functions available in the Raw category (square brackets indicate an argument is optional):

Name	Description	Arguments	Output
RawLength	Returns the length of a raw string	input string (raw)	Result (int32)

Top | Next

String functions

Previous | Bottom
The following table lists the functions available in the String category (square brackets indicate an argument is optional):

Name	Description	Arguments	Output
AlNum	Return whether the given string consists of alphanumeric characters	string (string)	true/false (int8)
Alpha	Returns 1 if string is purely alphabetic	string (string)	result (int8)
CompactWhiteSpace	Return the string after reducing all consective whitespace to a single space	string (string)	result (string)
Compare	Compares two strings for sorting	string1 (string) string2 (string) [justification (L or R)]	result (int8)
ComparNoCase	Case insensitive comparison of two strings	string1 (string) string2 (string)	result (int8)
ComparNum	Compare the first n characters of the two strings	string1 (string) string2 (string) length (int16)	result (int8)
CompareNumNoCase	Caseless comparison of the first n characters of the two strings	string1 (string) string2 (string) length (int16)	result (int8)
Convert	Converts specified characters in a string to designated replacement characters	fromlist (string) tolist (string) expression (string)	result (string)
Count	Count number of times a substring occurs in a string	string (string) substring (string)	result (int32)
Dcount	Count number of delimited fields in a string	string (string) delimiter (string)	result (int32)
DownCase	Change all uppercase letters in a string to lowercase	string (string)	result (string)
DQuote	Enclose a string in double quotation marks	string (string)	result (string)
Field	Return 1 or more delimited substrings	string (string) delimiter (string) occurrence (int32) [number (int32)]	result (string)
Index	Find starting character position of substring	string (string) substring (string) occurrence (int32)	result (int32)
Left	Leftmost n characters of string	string (string) number (int32)	result (string)
Len	Length of string in characters	string (string)	result (int32)
Num	Return 1 if string can be converted to a number	string (string)	result (int8)
PadString	Return the string padded with the optional pad character and optional length	string (string) padlength (int32)	result (string)
Right	Rightmost n characters of string	string (string) number (int32)	result (string)
Soundex	Returns a string which identifies a set of words that are (roughly) phonetically alike based on the standard, open algorithm for SOUNDEX evaluation	string (string)	result (string)
Space	Return a string of N space characters	length (int32)	result (string)
Squote	Enclose a string in single quotation marks	string (string)	result (string)
Str	Repeat a string	string (string) repeats (int32)	result (string)
StripWhiteSpace	Return the string after stripping all whitespace from it	string (string)	result (string)
Trim	Remove all leading and trailing spaces and tabs plus reduce internal occurrences to one	string (string) [stripchar (string)] [options (string)]	result (string)
TrimB	Remove all trailing spaces and tabs	string (string)	result (string)
TrimF	Remove all leading spaces and tabs	string (string)	result (string)
Trim Leading Trailing	Returns a string with leading and trailing whitespace removed	string (string)	result (string)
Upcase	Change all lowercase letters in a string to uppercase	string (string)	result (string)

Hint: true = 1 false = 0
Possible options for the Trim function are:
* L Removes leading occurrences of character.
* T Removes trailing occurrences of character.
* B Removes leading and trailing occurrences of character.
* R Removes leading and trailing occurrences of character, and reduces multiple occurrences to a single occurrence.
* A Removes all occurrences of character.
* F Removes leading spaces and tabs.
* E Removes trailing spaces and tabs.
* D Removes leading and trailing spaces and tabs, and reduces multiple spaces and tabs to single ones.

Top | Next

Vector function

Previous | Bottom
The following function can be used within expressions to access an element in a vector column. The vector index starts at 0.

Name	Description	Arguments	Output
ElementAt	Accesses an element of a vector	input column index (int)	element of vector

This can be used as part of, or the whole of an expression. For example, an expression to add 1 to the third element of an vector input column 'InLink.col1' would be: ElementAt(InLink.col1, 2) + 1

Top | Next

Type Conversion Functions

Previous | Bottom
The following table lists the functions available in the Type Conversion category (square brackets indicate an argument is optional):

Name	Description	Arguments	Output
DateToString	Return the string representation of the given date	date [format (string)]	result (string)
DecimalToDecimal	Returns the given decimal in decimal representation with specified precision and scale	decimal (decimal) [rtype (string)] [packedflag (int8)]	result (decimal)
DecimalToDFloat	Returns the given decimal in dfloat representation	number (decimal) ["fix_zero"]	result (dfloat)
DecimalToString	Return the string representation of the given decimal	number (decimal) ["fix_zero"]	result (string)
DfloatToDecimal	Returns the given dfloat in decimal representation	number (dfloat) [rtype (string)]	result (decimal)
DfloatToStringNoExp	Returns the given dfloat in its string representation with no exponent, using the specified scale	number (dfloat) scale (string)	result (string)
IsValid	Return whether the given string is valid for the given type. Valid types are "date", "decimal", "dfloat", "sfloat", "int8", "uint8", "int16", "uint16", "int32", "uint32", "int64", "uint64", "raw", "string", "time", "timestamp". "ustring"	type (string) format (string)	result (int8)
StringToDate	Returns a date from the given string in the given format	date (string) format (string)	date
StringToDecimal	Returns the given string in decimal representation	string (string) [rtype (string)]	result (decimal)
StringToRaw	Returns a string in raw representation	string (string)	result (raw)
StringToTime	Returns a time representation of the given string	string (string) [format (string)]	time
StringToTimestamp	Returns a timestamp representation of the given string	string (string) [format (string)]	timestamp
TimestampToDate	Returns a date from the given timestamp	timestamp	date
TimestampToString	Return the string representation of the given timestamp	timestamp [format (string)]	result (string)
TimestampToTime	Returns the time from a given timestamp	timestamp	time
TimeToString	Return the string representation of the given time	time [format (string)]	result (string)
StringToUstring	Returns a ustring from the given string, optionally using the specified map (otherwise uses project default)	string (string) [,mapname (string)]	result (ustring)
UstringToString	Returns a string from the given ustring, optionally using the specified map (otherwise uses project default)	string(ustring) [,mapname (string)]	result (string)

Rtype: The rtype argument is a string, and should contain one of the following:
* ceil: Round the source field toward positive infinity. E.g, 1.4 -> 2, -1.6 -> -1.
* floor: Round the source field toward negative infinity. E.g, 1.6 -> 1, -1.4 -> -2.
* round_inf: Round or truncate the source field toward the nearest representable value, breaking ties by rounding positive values toward positive infinity and negative values toward negative infinity. E.g, 1.4 -> 1, 1.5 -> 2, -1.4 -> -1, -1.5 -> -2.
* trunc_zero. Discard any fractional digits to the right of the rightmost fractional digit supported in the destination, regardless of sign. For example, if the destination is an integer, all fractional digits are truncated. If the destination is another decimal with a smaller scale, round or truncate to the scale size of the destination decimal. E.g, 1.6 -> 1, -1.6 -> -1.
The default is trunc_zero.
Format string: Date, Time, and Timestamp functions that take a format string (e.g., timetostring(time, stringformat)) need to have the date format specified. The format strings are described in Date and time formats. Where your dates, times, or timestamps convert to or from ustrings, InfoSphere DataStage will pick this up automatically. In these cases the separators in your format string (for example, `:' or `-') can themselves be Unicode characters.
fix_zero: By default decimal numbers comprising all zeros are treated as invalid. If the string fix_zero is specified as a second argument, then all zero decimal values are regarded as valid.

Top | Next

Type "casting" functions

Previous | Bottom
There is a special class of type conversion function to help you when performing mathematical calculations using numeric fields. For example, if you have a calculation using an output column of type float derived from an input column of type integer in a Parallel Transformer stage the result will be derived as an integer regardless of its float type. If you want a non-integral result for a calculation using integral operands, you can use the following functions (which act in a similar way as casting in C) to cast the integer operands into non-integral operands:

Name	Description	Arguments	Output
AsDouble	Treat the given number as a double	number (number)	number (double)
AsFloat	Treat the given number as a float	number (number)	number (float)
AsInteger	Treat the given number as an integer	number (number)	number (int)

Top

Utility functions

Previous | Bottom
The following table lists the functions available in the Utility category (square brackets indicate an argument is optional):

Name	Description	Arguments	Output
GetEnvironment	Returns the value of the given environment variable	environment variable (string)	result (string)
NextSKChain	Returns the value of the surrogate key column for the next record in the chain, or `value` for the newest record	`value` (number)	result (int64)
NextSurrogateKey	Returns the value of the next surrogate key	None	result (int64)
PrevSKChain	Returns the value of the surrogate key column for the previous record in the chain, or `value` for the first record	`value` (number)	result (int64)

Top

DataStage FAQs and Best Practices

2010-11-11T20:39:00.000+05:30

1. General Datastage issues

1.1. What are the ways to execute datastage jobs?

A job can be run using a few different methods:
* from Datastage Director (menu Job -> Run now...)
* from command line using a dsjob command
* Datastage routine can run a job (DsRunJob command)
* by a job sequencer

1.2. How to invoke a Datastage shell command?

Datastage shell commands can be invoked from :
* Datastage administrator (projects tab -> Command)
* Telnet client connected to the datastage server

1.3. How to stop a job when its status is running?

To stop a running job go to DataStage Director and click the stop button (or Job -> Stop from menu). If it doesn't help go to Job -> Cleanup Resources, select a process with holds a lock and click Logout
If it still doesn't help go to the datastage shell and invoke the following command: ds.tools.It will open an administration panel. Go to 4.Administer processes/locks , then try invoking one of the clear locks commands (options 7-10).

1.4. How to run and schedule a job from command line?

To run a job from command line use a dsjob command
Command Syntax: dsjob [-file | [-server ][-user ][-password ]] []
The command can be placed in a batch file and run in a system scheduler.

1.5. How to release a lock held by jobs?

Go to the datastage shell and invoke the following command: ds.tools
It will open an administration panel. Go to 4.Administer processes/locks , then try invoking one of the clear locks commands (options 7-10).

1.6. User privileges for the default DataStage roles?

The role privileges are:
* DataStage Developer - user with full access to all areas of a DataStage project
* DataStage Operator - has privileges to run and manage deployed DataStage jobs
* -none- - no permission to log on to DataStage

1.7. What is a command to analyze hashed file?

There are two ways to analyze a hashed file. Both should be invoked from the datastage command shell. These are:
* FILE.STAT command
* ANALYZE.FILE command

1.8. Is it possible to run two versions of datastage on the same pc?

Yes, even though different versions of Datastage use different system dll libraries.
To dynamically switch between Datastage versions install and run DataStage Multi-Client Manager. That application can unregister and register system libraries used by Datastage.

1.9. How to send notifications from Datastage as a text message (sms) to a cell phone

There is a few possible methods of sending sms messages from Datastage. However, there is no easy way to do this directly from Datastage and all methods described below will require some effort.
The easiest way of doing that from the Datastage standpoint is to configure an SMTP (email) server as a mobile phone gateway. In that case, a Notification Activity can be used to send message with a job log and any desired details. DSSendMail Before-job or After-job subroutine can be also used to send sms messages.
If configured properly, the recipients email address will have the following format: 600123456@oursmsgateway.com
If there is no possibility of configuring a mail server to send text messages, you can to work it around by using an external application run directly from the operational system. There is a whole bunch of unix scripts and applications to send sms messages.
In that solution, you will need to create a batch script which will take care of sending messages and invoke it from Datastage using ExecDOS or ExecSh subroutines passing the required parameters (like phone number and message body).
Please keep in mind that all these solutions may require a contact to the local cellphone provider first and, depending on the country, it may not be free of charge and in some cases the provider may not support the capability at all.

2. Datastage development and job design

2.1. Error in Link collector - Stage does not support in-process active-to-active inputs or outputs

To get rid of the error just go to the Job Properties -> Performance and select Enable row buffer.
Then select Inter process which will let the link collector run correctly.
Buffer size set to 128Kb should be fine, however it's a good idea to increase the timeout.

2.2. What is the DataStage equivalent to like option in ORACLE

The following statement in Oracle:
select * from ARTICLES where article_name like '%WHT080%';
Can be written in DataStage (for example as the constraint expression):
incol.empname matches '...WHT080...'

2.3. what is the difference between logging text and final text message in terminator stage

Every stage has a 'Logging Text' area on their General tab which logs an informational message when the stage is triggered or started.
* Informational - is a green line, DSLogInfo() type message.
* The Final Warning Text - the red fatal, the message which is included in the sequence abort message

2.4. Error in STPstage - SOURCE Procedures must have an output link

The error appears in Stored Procedure (STP) stage when there are no stages going out of that stage.To get rid of it go to 'stage properties' -> 'Procedure type' and select Transform

2.5. How to invoke an Oracle PLSQL stored procedure from a server job

To run a pl/sql procedure from Datastage a Stored Procedure (STP) stage can be used.
However it needs a flow of at least one record to run.
It can be designed in the following way:
* source odbc stage which fetches one record from the database and maps it to one column - for example: select sysdate from dual
* A transformer which passes that record through. If required, add pl/sql procedure parameters as columns on the right-hand side of tranformer's mapping
* Put Stored Procedure (STP) stage as a destination. Fill in connection parameters, type in the procedure name and select Transform as procedure type. In the input tab select 'execute procedure for each row' (it will be run once).

2.6. Is it possible to run a server job in parallel?

Yes, even server jobs can be run in parallel.
To do that go to 'Job properties' -> General and check the Allow Multiple Instance button.
The job can now be run simultaneously from one or many sequence jobs. When it happens datastage will create new entries in Director and new job will be named with automatically generated suffix (for example second instance of a job named JOB_0100 will be named JOB_0100.JOB_0100_2). It can be deleted at any time and will be automatically recreated by datastage on the next run.

2.7. Error in STPstage - STDPROC property required for stage xxx

The error appears in Stored Procedure (STP) stage when the 'Procedure name' field is empty. It occurs even if the Procedure call syntax is filled in correctly.
To get rid of error fill in the 'Procedure name' field.

2.8. Datastage routine to open a text file with error catching

Note! work dir and file1 are parameters passed to the routine.
* open file1
OPENSEQ work_dir : '\' : file1 TO H.FILE1 THEN
CALL DSLogInfo("******************** File " : file1 : " opened successfully", "JobControl")
END ELSE
CALL DSLogInfo("Unable to open file", "JobControl")
ABORT

2.9. Datastage routine which reads the first line from a text file

Note! work dir and file1 are parameters passed to the routine.

* open file1
OPENSEQ work_dir : '\' : file1 TO H.FILE1 THEN
CALL DSLogInfo("******************** File " : file1 : " opened successfully", "JobControl")
END ELSE
CALL DSLogInfo("Unable to open file", "JobControl")
ABORT
END

READSEQ FILE1.RECORD FROM H.FILE1 ELSE
Call DSLogWarn("******************** File is empty", "JobControl")
END

firstline = Trim(FILE1.RECORD[1,32]," ","A") ******* will read the first 32 chars
Call DSLogInfo("******************** Record read: " : firstline, "JobControl")
CLOSESEQ H.FILE1

END

2.10. How to test a datastage routine or transform?

To test a datastage routine or transform go to the Datastage Manager.
Navigate to Routines, select a routine you want to test and open it. First compile it and then click 'Test...' which will open a new window. Enter test parameters in the left-hand side column and click run all to see the results.
Datastage will remember all the test arguments during future tests.

2.11. When hashed files should be used? What are the benefits or using them?

Hashed files are the best way to store data for lookups. They're very fast when looking up the key-value pairs.
Hashed files are especially useful if they store information with data dictionaries (customer details, countries, exchange rates). Stored this way it can be spread across the project and accessed from different jobs.

2.12. How to construct a container and deconstruct it or switch between local and shared?

To construct a container go to Datastage designer, select the stages that would be included in the container and from the main menu select Edit -> Construct Container and choose between local and shared.
Local will be only visible in the current job, and share can be re-used. Shared containers can be viewed and edited in Datastage Manager under 'Routines' menu.
Local Datastage containers can be converted at any time to shared containers in datastage designer by right clicking on the container and selecting 'Convert to Shared'. In the same way it can be converted back to local.

2.13. Corresponding datastage data types to ORACLE types?

Most of the datastage variable types map very well to oracle types. The biggest problem is to map correctly oracle NUMBER(x,y) format.
The best way to do that in Datastage is to convert oracle NUMBER format to Datastage Decimal type and to fill in Length and Scale column accordingly.
There are no problems with string mappings: oracle Varchar2 maps to datastage Varchar, and oracle char to datastage char.

2.14. How to adjust commit interval when loading data to the database?

In earlier versions of datastage the commit interval could be set up in:
General -> Transaction size (in version 7.x it's obsolete)
Starting from Datastage 7.x it can be set up in properties of ODBC or ORACLE stage in Transaction handling -> Rows per transaction.

2.15. What is the use of INROWNUM and OUTROWNUM datastage variables?

@INROWNUM and @OUTROWNUM are internal datastage variables which do the following:
* @INROWNUM counts incoming rows to a transformer in a datastage job
* @OUTROWNUM counts oucoming rows from a transformer in a datastage job
These variables can be used to generate sequences, primary keys, id's, numbering rows and also for debugging and error tracing.
They play similiar role as sequences in Oracle.
If set to 0 the commit will be issued at the end of a successfull transaction.

2.16. Datastage trim function cuts out more characters than expected

By deafult datastage trim function will work this way:
Trim(" a b c d ") will return "a b c d" while in many other programming/scripting languages "a b c d" result would be expected.
That is beacuse by default an R parameter is assumed which is R - Removes leading and trailing occurrences of character, and reduces multiple occurrences to a single occurrence.
To get the "a b c d" as a result use the trim function in the following way: Trim(" a b c d "," ","B")

2.17. Database update actions in ORACLE stage

The destination table can be updated using various Update actions in Oracle stage. Be aware of the fact that it's crucial to select the key columns properly as it will determine which column will appear in the WHERE part of the SQL statement. Available actions:
* Clear the table then insert rows - deletes the contents of the table (DELETE statement) and adds new rows (INSERT).
* Truncate the table then insert rows - deletes the contents of the table (TRUNCATE statement) and adds new rows (INSERT).
* Insert rows without clearing - only adds new rows (INSERT statement).
* Delete existing rows only - deletes matched rows (issues only the DELETE statement).
* Replace existing rows completely - deletes the existing rows (DELETE statement), then adds new rows (INSERT).
* Update existing rows only - updates existing rows (UPDATE statement).
* Update existing rows or insert new rows - updates existing data rows (UPDATE) or adds new rows (INSERT). An UPDATE is issued first and if succeeds the INSERT is ommited.
* Insert new rows or update existing rows - adds new rows (INSERT) or updates existing rows (UPDATE). An INSERT is issued first and if succeeds the UPDATE is ommited.
* User-defined SQL - the data is written using a user-defined SQL statement.
* User-defined SQL file - the data is written using a user-defined SQL statement from a file.

2.18. Use and examples of ICONV and OCONV functions?

ICONV and OCONV functions are quite often used to handle data in Datastage.
ICONV converts a string to an internal storage format and OCONV converts an expression to an output format.
Syntax:
Iconv (string, conversion code)
Oconv(expression, conversion )

Some useful iconv and oconv examples:
Iconv("10/14/06", "D2/") = 14167
Oconv(14167, "D-E") = "14-10-2006"

2.19. ERROR 81021 Calling subroutine DSR_RECORD ACTION=2

Error message:

DataStage Repository Interface:
Error calling subroutine: DSR_RECORD (Action=2);
check DataStage is set up correctly in project
Development (Internal Error (81021))

Datastage system help gives the following error desription:
SYS.HELP. 081021
MESSAGE.. dsrpc: Error writing to Pipe.

The problem appears when a job sequence is used and it contains many stages (usually more than 10) and very often when a network connection is slow.

Basically the cause of a problem is a failure between DataStage client and the server communication.

The solution to the issue is:
# Do not log in to Datastage Designer using 'Omit' option on a login screen. Type in explicitly username and password and a job should compile successfully.
# execute the DS.REINDEX ALL command from the Datastage shell - if the above does not help

Oconv(14167, "D DMY[,A,]") = "14 OCTOBER 2006"
Oconv(12003005, "MD2$,") = "$120,030.05"

That expression formats a number and rounds it to 2 decimal places:
Oconv(L01.TURNOVER_VALUE*100,"MD2")

Iconv and oconv can be combined in one expression to reformat date format easily:
Oconv(Iconv("10/14/06", "D2/"),"D-E") = "14-10-2006"

2.20. How to check Datastage internal error descriptions

# To check the description of a number go to the datastage shell (from administrator or telnet to the server machine) and invoke the following command: SELECT * FROM SYS.MESSAGE WHERE @ID='081021'; - where in that case the number 081021 is an error number

The command will produce a brief error description which probably will not be helpful in resolving an issue but can be a good starting point for further analysis.

2.21. Error timeout waiting for mutex

The error message usually looks like follows:
... ds_ipcgetnext() - timeout waiting for mutex

There may be several reasons for the error and thus solutions to get rid of it.
The error usually appears when using Link Collector, Link Partitioner and Interprocess (IPC) stages. It may also appear when doing a lookup with the use of a hash file or if a job is very complex, with the use of many transformers.

There are a few things to consider to work around the problem:
- increase the buffer size (up to to 1024K) and the Timeout value in the Job properties (on the Performance tab).
- ensure that the key columns in active stages or hashed files are composed of allowed characters – get rid of nulls and try to avoid language specific chars which may cause the problem.
- try to simplify the job as much as possible (especially if it’s very complex). Consider splitting it into two or three smaller jobs, review fetches and lookups and try to optimize them (especially have a look at the SQL statements).

2.22. ERROR 30107 Subroutine failed to complete successfully

Error message:
Error calling subroutine:
DSR_RECORD (Action=2); or *DataStage*DSR_SELECT (Action=7);
check DataStage is set up correctly in project Development
(Subroutine failed to complete successfully(30107))

Datastage system help gives the following error desription:
SYS.HELP. 930107
MESSAGE.. DataStage/SQL: Illegal placement of parameter markers

The problem appears when a project is moved from one project to another (for example when deploying a project from a development environment to production).
The solution to the issue is:
# Rebuild the repository index by executing the DS.REINDEX ALL command from the Datastage shell

2.23. Datastage Designer hangs when editing job activity properties

The appears when running Datastage Designer under Windows XP after installing patches or the Service Pack 2 for Windows.
After opening a job sequence and navigating to the job activity properties window the application freezes and the only way to close it is from the Windows Task Manager.

The solution of the problem is very simple. Just Download and install the “XP SP2 patch” for the Datastage client.
It can be found on the IBM client support site (need to log in):
https://www.ascential.com/eservice/public/welcome.do

Go to the software updates section and select an appropriate patch from the Recommended DataStage patches section.
Sometimes users face problems when trying to log in (for example when the license doesn’t cover the IBM Active Support), then it may be necessary to contact the IBM support which can be reached at WDISupport@us.ibm.com

2.24. Can Datastage use Excel files as a data input?

# Microsoft Excel spreadsheets can be used as a data input in Datastage. Basically there are two possible approaches available: Access Excel file via ODBC - this approach requires creating an ODBC connection to the Excel file on a Datastage server machine and use an ODBC stage in Datastage. The main disadvantage is that it is impossible to do this on an Unix machine. On Datastage servers operating in Windows it can be set up here:
Control Panel -> Administrative Tools -> Data Sources (ODBC) -> User DSN -> Add -> Driver do Microsoft Excel (.xls) -> Provide a Data source name -> Select the workbook -> OK
# Save Excel file as CSV - save data from an excel spreadsheet to a CSV text file and use a sequential stage in Datastage to read the data.

Interview Questions

2010-10-27T08:38:00.005+05:30

1. How to remove duplicate rows?
We have various ways to do the same.
ServerJobs
-Using Hash File.
-Using Stage variable
Detailed description as below. we have to define two stage variable for example stgV1 and stgV2.
Derivation...............Stage Variable
-----------------------------------------
stgV1......................stgV2
IputKeyColm(Id).....stgV1

We have to write a constraint like as below
if stgV1<>stgV2 ------------->this stream is to produce unique records
if stgV1=stgV2 ------------->this stream is to produce duplicate records
hope I am clear with the same....
Parallel Jobs
-Using Stage Variables
-Using Remove Duplicates Stage
-Using Perform sort option [specifically need to select unique option] under PartitioningTab
-Using SortStage

The Duplicates can be eliminated by loading the corresponding data in the Hash file. Specify the columns on which u want to eliminate as the keys of hash.
You can delete duplicate records from source itself using data stage taking option as user defined query, instead of taking table read option. and u can use remove duplicate stage in data stage. and using of hash file as source also based on the hash key.

2. What are the Repository Tables in Data Stage and what are they?
A data warehouse is a repository (centralized as well as distributed) of Data, able to answer any adhoc, analytical, historical or complex queries. Metadata is data about data. Examples of metadata include data element descriptions, data type descriptions, attribute/property descriptions, range/domain descriptions, and process/method descriptions. The repository environment encompasses all corporate metadata resources: database catalogs, data dictionaries, and navigation services. Metadata includes things like the name, length, valid values, and description of a data element. Metadata is stored in a data dictionary and repository. It insulates the data warehouse from changes in the schema of operational systems. In data stage I/O and Transfer, under interface tab: input, out put & transfer pages. U will have 4 tabs and the last one is build under that u can find the TABLE NAME .The Data Stage client components are: Administrator Administers Data Stage projects and conducts housekeeping on the server Designer Creates Data Stage jobs that are compiled into executable programs Director Used to run and monitor the Data Stage jobs Manager Allows you to view and edit the contents of the repository.

3. Differentiate Database data and Data warehouse data?
By Database, one means OLTP (On Line Transaction Processing). This can be the source systems or the ODS (Operational Data Store), which contains the transactional data.
Database data only for transactional purpose & Data warehouse data only for analytical purpose for decision making.
Data base contains detail data. Data warehouse contains summary data

4.If a Data Stage job aborts after say 1000 records, how to continue the job from 1000th record after fixing the error?
By specifying Check pointing in job sequence properties, if we restart the job. Then job will start by skipping up to the failed record. This option is available in 7.5 edition.

5. What is merge and how it can be done please explain with simple example taking 2 tables.......?
Merge is used to join two tables. It takes the Key columns sort them in Ascending or descending order. Let us consider two table i.e Emp, Dept. If we want to join these two tables we are having DeptNo as a common Key so we can give that column name as key and sort Deptno in ascending order and can join those two tables

6. It is possible to call one job in another job in server jobs?
I think we can call a job into another job. In fact calling doesn't sound good, because you attach/add the other job through job properties. In fact, you can attach zero or more jobs.
Steps will be Edit --> Job Properties --> Job Control
Click on Add Job and select the desired job.

7. What are the environment variables in data stage? Give some examples?
There are the variables used at the project or job level. We can use them to configure the job i.e. We can associate the configuration file(Without this u can not run your job), increase the sequential or dataset read/ write buffer.
ex: $APT_CONFIG_FILE
Like above we have so many environment variables. Please go to job properties and click on "add environment variable" to see most of the environment variables.

8. What happens if RCP is disable ?
Runtime column propagation (RCP): If RCP is enabled for any job, and specifically for those stages whose output connects to the shared container input, then Meta data will be propagated at run time, so there is no need to map it at design time.
If RCP is disabled for the job, in such case OSH has to perform Import and export every time when the job runs and the processing time job is also increased.

9. How can I extract data from DB2 (on IBM iSeries) to the data warehouse via Data stage as the ETL tool? I mean do I first need to use ODBC to create connectivity and use an adapter for the extraction and transformation of data?
You would need to install ODBC drivers to connect to DB2 instance (does not come with regular drivers that we try to install, use CD provided for DB2 installation, that would have ODBC drivers to connect to DB2) and then try out

10. What is the mean of Try to have the constraints in the 'Selection' criteria of the jobs itself. This will eliminate the unnecessary records even getting in before joins are made?
This means try to improve the performance by avoiding use of constraints wherever possible and instead using them while selecting the data itself using a where clause. This improves performance.

11. What does a config File in parallel extender consist of?
Config file consists of the following.
a) Number of Processes or Nodes.
b) Actual Disk Storage Location.

The APT Configuration file is having the information of resource disk, node pool, and scratch information, node information in the since it contains the how many nodes we given to run the jobs, because based on the nodes only data stage will create processors at back end while running the jobs, resource disk means this is the place where exactly jobs will be loading, scratch information will be useful whenever we using the lookups in the jobs
APT Configuration file is the configuration file which defines the nodes for the specific project and actual disk storage.

12. Can we use shared container as lookup in data stage server jobs?
I am using Data Stage 7.5, UNIX. We can use shared container more than one time in the job. There is any limit to use it. why because in my job i used the Shared container at 6 flows. At any time only 2 flows are working. can you please share the info on this.
No. We cant use container as lookup.
Container is used to reduce the complexity view to simple view of stages or jobs, so there will be only jobs available inside and not source table or anything.

13. How to find the number of rows in a sequential file?
Using Row Count System variable
Use the aggregator stage to use the count record facility

14. What is the difference between validated ok and compiled in data stage?
When we say "Validating a Job", we are talking about running the Job in the "check only" mode. The following checks are made:
- Connections are made to the data sources or data warehouse.
- SQL SELECT statements are prepared.
- Files are opened. Intermediate files in Hashed File, Universe, or ODBC stages that use the local data source are created, if they do not already exist.

15. What are the differences between the data stage 7.0 and 7.5 in server jobs?
There is lot of Differences: There are lots of new stages are available in DS7.5 For
Eg: CDC Stage Stored procedure Stage etc…

16. Is it possible to run parallel jobs in server jobs?
No, it is not possible to run Parallel jobs in server jobs. But Server jobs can be executed in Parallel jobs

17. What is DS Administrator used for - did u use it?
The Administrator enables you to set up Data Stage users, control the purging of the Repository, and, if National Language Support (NLS) is enabled, install and manage maps and locales.

18. What is Modulus and Splitting in Dynamic Hashed File?
The modulus size can be increased by contacting your Unix Admin.
In a Hashed File, the size of the file keeps changing randomly.
If the size of the file increases it is called as "Modulus".
If the size of the file decreases it is called as "Splitting".
The modulus size can be increase/decrease by contacting your Data Stage Admin

19. How can ETL access excel file to Data mart?
Open the ODBC Data Source Administrator found in the control panel/administrative tools. Under the system DSN tab, add the Driver to Microsoft Excel. Then you will be able to access the XLS file from Data stage.

20.What is the exact difference between Join, Merge and Lookup Stage?
The exact difference between Join, Merge and lookup is The three stages differ mainly in the memory they use Data Stage doesn't know how large your data is, so cannot make an informed choice whether to combine data using a join stage or a lookup stage. Here's how to decide which to use:
if the reference datasets are big enough to cause trouble, use a join. A join does a high-speed sort on the driving and reference datasets. This can involve I/O if the data is big enough, but the I/O is all highly optimized and sequential. Once the sort is over the join processing is very fast and never involves paging or other I/O
Unlike Join stages and Lookup stages, the Merge stage allows you to specify several reject links as many as input links.
The three stages differs with each other with respect to
1. Input Requirements
2. Treatment of unmatched records
3. Memory Usage
Join requires less memory usage
Lookup requires more memory usage and
Merge requires less memory usage

21. What about System variables?
Data Stage provides a set of variables containing useful system information that you can access from a transform or routine. System variables are read-only.
@DATE The internal date when the program started. See the Date function.
@DAY The day of the month extracted from the value in @DATE.
@FALSE The compiler replaces the value with 0.
@FM A field mark, Char(254).
@IM An item mark, Char(255).
@INROWNUM Input row counter. For use in constrains and derivations in Transformer stages.
@OUTROWNUM Output row counter (per link). For use in derivations in Transformer stages.
@LOGNAME The user login name.
@MONTH The current extracted from the value in @DATE.
@NULL The null value.
@NULL.STR The internal representation of the null value, Char(128).
@PATH The pathname of the current Data Stage project.
@SCHEMA The schema name of the current Data Stage project.
@SM A sub value mark (a delimiter used in Universe files), Char(252).
@SYSTEM.RETURN.CODE Status codes returned by system processes or commands.
@TIME The internal time when the program started. See the Time function.
@TM A text mark (a delimiter used in Universe files), Char(251).
@TRUE The compiler replaces the value with 1.
@USERNO The user number.
@VM A value mark (a delimiter used in Universe files), Char(253).
@WHO The name of the current Data Stage project directory.
@YEAR The current year extracted from @DATE.
REJECTED Can be used in the constraint expression of a Transformer stage of an output link. REJECTED is initially TRUE, but is set to FALSE whenever an output link is successfully written.

22. Importance of Surrogate Key in Data warehousing?
The concept of surrogate comes into play when there is slowly changing dimension in a table. In such condition there is a need of a key by which we can identify the changes made in the dimensions. These slowly changing dimensions can be of three type namely SCD1, SCD2, SCD3.These are system generated key. Mainly they are just the sequence of numbers or can be alphanumeric values also.

23. How to handle the rejected rows in data stage?
We can handle rejected rows in two ways with help of Constraints in a Tansformer.1) By Putting on the Rejected cell where we will be writing our constraints in the properties of the Transformer2)Use REJECTED in the expression editor of the Constraint Create a hash file as a temporary storage for rejected rows. Create a link and use it as one of the output of the transformer. Apply either of the two steps above said on that Link. All the rows which are rejected by all the constraints will go to the Hash File.

24. How can you do incremental load in data stage?
Incremental load means daily load. When ever you are selecting data from source, select the records which are loaded or updated between the timestamp of last successful load and today’s load start date and time. For this u have to pass parameters for those two dates. Store the last run date and time in a file and read the parameter through job parameters and state second argument as current date and time.

25. How do we do the automation of data stage jobs?
We can call Data stage Batch Job from Command prompt using 'dsjob'. We can also pass all the parameters from command prompt. Then call this shell script in any of the market available schedulers.
The 2nd option is schedule these jobs using Data Stage director.

26. Can anyone tell me how to extract data from more than 1 heterogeneous Sources? Mean, example 1 sequential file, Sybase, Oracle in a single Job?
Yes you can extract the data from two heterogeneous sources in data stages using the transformer stage it's so simple you need to just form a link between the two sources in the transformer stage that's it Bye Hammed

27. What is merge? And how to use merge?
Merge is a stage that is available in both parallel and server jobs.
The merge stage is used to join two tables (server/parallel) or two ables/datasets(parallel). Merge requires that the master table/dataset and the update table/dataset to be sorted. Merge is performed on a key field, and the key field is mandatory in the master and update dataset/table. Merge is used to merge the two tables, but when master and update records are merged only both have same merge key columns.

28. What's the difference between Data stage Developers and Data stage Designers. What are the skills required for this?
Data stage developer is one how will code the jobs. Data stage designer is how will design the job, i mean he will deal with blue prints and he will design the jobs the stages that are required in developing the code

29.What is the OCI? And how to use the ETL Tools?
OCI doesn't mean the orabulk data. It actually uses the "Oracle Call Interface" of the oracle to load the data. It is kind of the lowest level of Oracle being used for loading the data.

30.What are the different types of lookups in data stage?
There are two types of lookup stage and look up file set
Lookup: Lookup reference to another stage or Database to get the data from it and transforms to other database.
Lookup File Set: It allows you to create a lookup file set or reference one for a lookup. The stage can have a single input link or a single output link. The output link must be a reference link. The stage can be configured to execute in parallel or sequential mode when used with an input link. When creating Lookup file sets, one file will be created for each partition. The individual files are referenced by a single descriptor file, which by convention has the suffix .
There are two types of lookups
1. Normal look up
2. Sparse look up
Normal look up: To perform this look up data will be stored in the memory first and then look up will be performed bue to which it takes more execution time

Sparse look up: Sql query will be directly fired on the database related record due to which execution is faster than normal look up. For a single job we can use only one file with sparse look up where as no: of normal lookups can be used. But once we mention file to do sparse lookup it will not accept normal look up after that. So make sure to mention all your normal lookups first and sparse lookups at the last.

31. How to implement type2 slowly changing dimension in data stage? Give me with example?
Slow changing dimension is a common problem in Data warehousing. For example: There exists a customer called Lisa in a company ABC and she lives in New York. Later she moved to Florida. The company must modify her address now. In general 3 ways to solve this problem.
Type 1: The new record replaces the original record, no trace of the old record at all, Type 2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two different people.
Type 3: The original record is modified to reflect the changes.
In Type1 the new one will over write the existing one that means no history is maintained, History of the person where she stayed last is lost, simple to use.
In Type2 New record is added, therefore both the original and the new record Will be present, the new record will get its own primary key, Advantage of using this type2 is, Historical information is maintained But size of the dimension table grows, storage and performance can become a concern.
Type2 should only be used if it is necessary for the data warehouse to track the historical changes.
In Type3 there will be 2 columns one to indicate the original value and the other to indicate the current value. Example a new column will be added which shows the original address as New York and the current address as Florida. Helps in keeping some part of the history and table size is not increased. But one problem is when the customer moves from Florida to Texas the New York information is lost. So Type 3 should only be used if the changes will only occur for a finite number of times.

32.What is DS Manager used for - did u use it?
The Manager is a graphical tool that enables you to view and manage the contents of the Data Stage Repository

33. What are other Performance tunings you have done in your last project to increase the performance of slowly running jobs?
Minimize the usage of Transformer (Instead of this use Copy, modify, Filter, Row Generator) Use SQL Code while extracting the data
Handle the nulls
Minimize the warnings
Reduce the number of lookups in a job design
Use not more than 20stages in a job
Use IPC stage between two passive stages Reduces processing time
Drop indexes before data loading and recreate after loading data into tables
Generally we cannot avoid no of lookups if our requirements to do lookups compulsory.
There is no limit for no of stages like 20 or 30 but we can break the job into small jobs then we use dataset Stages to store the data.
IPC Stage that is provided in Server Jobs not in Parallel Jobs
Check the write cache of Hash file. If the same hash file is used for Look up and as well as target, disable this Option.
If the hash file is used only for lookup then "enable Preload to memory". This will improve the performance. Also, check the order of execution of the routines.
Don't use more than 7 lookups in the same transformer; introduce new transformers if it exceeds 7 lookups.
Use Preload to memory option in the hash file output.
Use Write to cache in the hash file input.
Write into the error tables only after all the transformer stages.
Reduce the width of the input record - remove the columns that you would not use.
Cache the hash files you are reading from and writing into. Make sure your cache is big enough to hold the hash files.
Use ANALYZE.FILE or HASH.HELP to determine the optimal settings for your hash files.
This would also minimize overflow on the hash file.
If possible, break the input into multiple threads and run multiple instances of the job.
Staged the data coming from ODBC/OCI/DB2UDB stages or any database on the server using Hash/Sequential files for optimum performance also for data recovery in case job aborts.
Tuned the OCI stage for 'Array Size' and 'Rows per Transaction' numerical values for faster inserts, updates and selects.
Tuned the 'Project Tumbles' in Administrator for better performance.
Used sorted data for Aggregator.
Sorted the data as much as possible in DB and reduced the use of DS-Sort for better performance of jobs
Removed the data not used from the source as early as possible in the job.
Worked with DB-admin to create appropriate Indexes on tables for better performance of DS queries
Converted some of the complex joins/business in DS to Stored Procedures on DS for faster execution of the jobs.
If an input file has an excessive number of rows and can be split-up then use standard logic to run jobs in parallel.
Before writing a routine or a transform, make sure that there is not the functionality required in one of the standard routines supplied in the sdk or ds utilities categories.
Constraints are generally CPU intensive and take a significant amount of time to process. This may be the case if the constraint calls routines or external macros but if it is inline code then the overhead will be minimal.
Try to have the constraints in the 'Selection' criteria of the jobs itself. This will eliminate the unnecessary records even getting in before joins are made.
Tuning should occur on a job-by-job basis.
Use the power of DBMS.
Try not to use a sort stage when you can use an ORDER BY clause in the database.

34. Using a constraint to filter a record set is much slower than performing a SELECT? WHERE?
Make every attempt to use the bulk loader for your particular database. Bulk loaders are generally faster than using ODBC or OLE.

35. What is the order of execution done internally in the transformer with the stage editor having input links on the left hand side and output links?
Stage variables, constraints and column derivation or expressions.

36. What is the difference between In-process and Inter-process?
In-process
You can improve the performance of most Data Stage jobs by turning in-process row buffering on and recompiling the job. This allows connected active stages to pass data via buffers rather than row by row.
Note: You cannot use in-process row-buffering if your job uses COMMON blocks in transform functions to pass data between stages. This is not recommended practice, and it is advisable to redesign your job to use row buffering rather than COMMON blocks.

Inter-process
Use this if you are running server jobs on an SMP parallel system. This enables the job to run using a separate process for each active stage, which will run simultaneously on a separate processor.
Note: You cannot inter-process row-buffering if your job uses COMMON blocks in transform functions to pass data between stages. This is not recommended practice, and it is advisable to redesign your job to use row buffering rather than COMMON blocks.

37.What is DS Designer used for - did u use it?
You use the Designer to build jobs by creating a visual design that models the flow and transformation of data from the data source through to the target warehouse. The Designer graphical interface lets you select stage icons, drop them onto the Designer work area, and add links.

38. How to handle Date conventions in Data stage? Convert a mm/dd/yyyy format to yyyy-dd-mm?
Function to convert mm/dd/yyyy format to yyyy-dd-mm is
Oconv(Iconv(Filedname,"D/MDY[2,2,4]"),"D-YDM[4,2,2]") .

39. If data is partitioned in your job on key 1 and then you aggregate on key 2, what issues could arise?
Data will partition on both the keys! Hardly it will take more for execution.

40. What is purpose of using the key and difference between Surrogate keys and natural key?
We use keys to provide relationships between the entities (Tables). By using primary and foreign key relationship, we can maintain integrity of the data.
The natural key is the one coming from the OLTP system.
The surrogate key is the artificial key which we are going to create in the target DW. We can use these surrogate keys instead of using natural key. In the SCD2 scenarios surrogate keys play a major role.
Natural key: key that is formed of attributes that already exist in the real world. e.g.SSN id.

Surrogate key: it can be thought of as a replacement of the natural key that has no business meaning. E.g. sequence in oracle.
I think there is a bug in the data stage 8.0.1 surrogate key generator. It seems to be very much inconsistent.

41. Is it possible to move the data from oracle ware house to SAP Warehouse using with DATASTAGE Tool?
We can use Data Stage Extract Pack for SAP R/3 and Data Stage Load Pack for SAP BW to transfer the data from oracle to SAP Warehouse. These Plug In Packs are available with Data Stage Version 7.5

42. How to kill the job in data stage?
By using Cleanup Resource or By killing the respective process ID

43. How we can call the routine in data stage job? Explain with steps?
Routines are used for implementing the business logic they are two types 1) Before Sub Routines and 2)After Sub Routine steps double click on the transformer stage right click on any one of the mapping field select [dstoutines] option within edit window give the business logic and select the either of the options( Before / After Sub Routines).

44. What are the most important aspects that a beginner must consider doin his first DS project?
He should be good at Data Warehousing Concepts and he should be familiar with all stages

45. How I can convert Server Jobs into Parallel Jobs?
I have never tried doing this, however, I have some information which will help you in saving a lot of time. You can convert your server job into a server shared container. The server shared container can also be used in parallel jobs as shared container.
I don't think this conversion might be possible.
Using ipc stage or link partitioner/link collector can incorporate a n amount of parallelism in the server jobs.
My requirement is like this:
Here is the codification suggested:
SALE_HEADER_XXXXX_YYYYMMDD.PSV
SALE_LINE_XXXXX_YYYYMMDD.PSV

XXXXX = LVM sequence to ensure unicity and continuity of file exchanges
Caution, there will an increment to implement.
YYYYMMDD = LVM date of file creation

COMPRESSION AND DELIVERY TO: SALE_HEADER_XXXXX_YYYYMMDD.ZIP AND SALE_LINE_XXXXX_YYYYMMDD.ZIP
If we run that job the target file names are like this
sale_header_1_20060206 & sale_line_1_20060206.

If we run next time means the target files we like this sale_header_2_20060206 & sale_line_2_20060206.
If we run the same in next day means the target files we want like this
sale_header_3_20060306 & sale_line_3_20060306.
i.e., whenever we run the same job the target files automatically changes its filename to
filename_increment to previous number(previousnumber + 1)_currentdate;
Please do needful by repling this question..
This can be done by using unix script
1. Keep? the Target filename as constant name xxx.psv
2. Once the job completed, invoke the Unix Script through After job routine - ExecSh
3. The script should get the number used in previous file and increment it by 1, After that move the file from xxx.psv to filename_(previousnumber + 1)_currentdate.psv and then delete the xxx.psv file.This is the?Easiest way to implement.

46.What is the difference between symmetrically parallel processing, massively parallel processing?
Symmetric Multiprocessing (SMP) - Some Hardware resources may be shared by processor. Processors communicate via shared memory and have single operating system.
Cluster or Massively Parallel Processing (MPP) - Known as shared nothing in which each processor have exclusive access to hardware resources. Cluster systems can be physically dispersed. The processor has their own operations system and communicate via high speed network

47. What is the purpose of exception activity in data stage 7.5?
The stages followed by exception activity will be executed whenever there is an unknown error occurs while running the job sequencer.

48. Where we use link partitioner in data stage job? explain with example?
We use Link Partitioner in Data Stage Server Jobs. The Link Partitioner stage is an active stage which takes one input and allows you to distribute partitioned rows to up to 64 output links.
The 100 employees salaries in one organization .i want to load >300 emps in one target and <250 and >450 in one target that time go for link partioner stage

49. How to parameterize a field in a sequential file? I am using Data stage as ETL Tool, Sequential file as source?
We cannot parameterize a particular field in a sequential file, instead we can parameterize the source file name in a sequential file.

50. How to drop the index before loading data in target and how to rebuild it in data stage?
This can be achieved by "Direct Load" option of SQL Loaded utility.
If we are using OCI & database stage, we can use Before/After sql property in stage properties.

51.If the size of the Hash file exceeds 2GB..What happens? Does it overwrite the current rows?
It overwrites the file

52. Can we retain the removed duplicate values...If yes, how can we do that?
It is possible to access the same job two users at a time in data stage
No, it is not possible to access the same job two users at the same time. DS will produce the following error: "Job is accessed by other user"
No it is not possible for the same job to be accessed by two users, because if one of the job is being used by one user then if the second one tries to open the same job then you get the error as "Job (job name) is being accessed by another user"
--In Data stage 8 it is possible to open the job as Read-Only. In order to do some modification in the read-only job, take a copy of the job as modification cannot be done in a read only job. This feature is not available in Data stage 7.5.

53. What is job control? How it is developed? Explain with steps?
Controlling Data stage jobs through some other Data stage jobs. Ex: Consider two Jobs XXX and YYY. The Job YYY can be executed from Job XXX by using Data stage macros in Routines.
To execute one job from other job, following steps needs to be followed in Routines.
1. Attach job using DS Attach job function.
2. Run the other job using DS Run job function
3. Stop the job using DSS top Job function

54. Where actually the flat files store? What is the path?
Flat files stores the data and the path can be given in general tab of the sequential file stage

55. Will the data stage consider the second constraint in the transformer once the first condition is satisfied ( if the link ordering is given)?
Will Data stage consider the second constraint in the transformer if the first constraint is satisfied (if link ordering is given)?"
Answer: Yes.

56. What is a project? Specify its various components?
You always enter Data Stage through a Data Stage project. When you start a Data Stage client you are prompted to connect to a project. Each project contains:
Data Stage jobs:
Built-in components: These are predefined components used in a job.
User-defined components: These are customized components created using the Data Stage Manager or Data Stage Designer
You always enter Data Stage through a Data Stage project. When you start a Data Stage client you are prompted to connect to a project. Each project contains:

57. Briefly describe the various client components?
There are four client components
Data Stage Designer: A design interface used to create Data Stage applications (known as jobs). Each job specifies the data sources, the transforms required, and the destination of the data. Jobs are compiled to create executables that are scheduled by the Director and run by the Server.
Data Stage Director: A user interface used to validate, schedule, run, and monitor Data Stage jobs.
Data Stage Manager: A user interface used to view and edit the contents of the Repository.
Data Stage Administrator: A user interface used to configure Data Stage projects and users.

58. What are the Steps involved in development of a job in Data Stage?
The steps required are:
Select the data source stage depending upon the sources for ex: flat file, database, xml etc
Select the required stages for transformation logic such as transformer, link collector, link practitioner, Aggregator, merge etc.
Select the final target stage where u want to load the data either it is data warehouse, data mart, ODS, staging etc

59. What are constraints and derivation?
Explain the process of taking backup in Data Stage?
What are the different types of lookups available in Data Stage?
Constraints are used to check for a condition and filter the data. Example: Cust_Id<>0 is set as a constraint and it means and only those records meeting this will be processed further.
Derivation is a method of deriving the fields, for example if you need to get some SUM, AVG etc.

60. How does Data Stage handle the user security?
We have to create users in the Administrators and give the necessary privileges to users.

61. What is meaning of file extender in data stage server jobs?
Can we run the data stage job from one job to another job that file data where it is stored and what is the file extender in data stage jobs?
What is meaning of file extender in data stage server jobs?
Can we run the data stage job from one job to another job that file data where it is stored and what is the file extender in data stage jobs?
File extender means the adding the columns or records to the already existing the file, in the data stage. We can run the data stage job from one job to another job in data stage.

62. What is the difference between DRS and ODBC stage?
To answer your question the DRS stage should be faster then the ODBC stage as it uses native database connectivity. You will need to install and configure the required database clients on your Data Stage server for it to work.

Dynamic Relational Stage was leveraged for People soft to have a job to run on any of the supported databases. It supports ODBC connections too. Read more of that in the plug-in documentation.
ODBC uses the ODBC driver for a particular database, DRS is a stage that tries to make it seamless for switching from one database to another. It uses the native connectivities for the chosen target...

63. How to use rank & update strategy in data stage?
Don't mix informatica with Data stage.
In Data stage, we don’t have such kind of stages.

64. What is the max capacity of Hash file in Data Stage?
Take a look at the uvconfig file:
# 64BIT_FILES - This sets the default mode used to
# create static hashed and dynamic files.
# A value of 0 results in the creation of 32-bit
# files. 32-bit files have a maximum file size of
# 2 gigabytes. A value of 1 results in the creation
# of 64-bit files (ONLY valid on 64-bit capable platforms).
# The maximum file size for 64-bit
# files is system dependent. The default behavior
# may be overridden by keywords on certain commands.
64BIT_FILES 0

65. How to implement type2 slowly changing dimensions in data stage? Explain with example?
We can handle SCD in the following ways
Type 1: Just use, Insert rows Else Update rows? Or? Update rows Else Insert rows? in update action of target
Type 2: Use the steps as follows
a) U have use one hash file to Look-Up the target
b) Take 3 instances of target
c) Give different conditions depending on the process
d) Give different update actions in target
e) Use system variables like Sysdate and Null.

66. What is merge and how it can be done please explain with simple example taking 2 tables .......?
Merge is used to join two tables. It takes the Key columns sort them in Ascending or descending order. Let us consider two table i.e. Emp, Dept. If we want to join these two tables we are having Deptno as a common Key so we can give that column name as key and sort Deptno in ascending order and can join those two tables

67. How can ETL excel file to Data mart?
Open the ODBC Data Source Administrator found in the controlpanel/administrative tools. Under the system DSN tab, add the Driver to Microsoft Excel.
Then you'll be able to access the XLS file from Data stage.

68. How do we do the automation of ds jobs?
We can call Data stage Batch Job from Command prompt using 'dsjob'. We can also pass all the parameters from command prompt. Then call this shell script in any of the market available schedulers. The 2nd option is schedule these jobs using Data Stage director.

69. What are constraints and derivation?
Explain the process of taking backup in Data Stage?
What are the different types of lookups available in Data Stage?
Constraints are used to check for a condition and filter the data. Example: Cust_Id<>0 is set as a constraint and it means and only those records meeting this will be processed further. Derivation is a method of deriving the fields, for example if you need to get some SUM,AVG etc.

70. How does Data Stage handle the user security?
We have to create users in the Administrators and give the necessary privileges to users.
What is meaning of file extender in data stage server jobs.
can we run the data stage job from one job to another job that file data where it is stored and what is the file extender in ds jobs.

71. What is meaning of file extender in data stage server jobs.
can we run the data stage job from one job to another job that file data where it is stored and what is the file extender in ds jobs.
File extender means the adding the columns or records to the already existing the file, in the data stage, we can run the data stage job from one job to another job in data stage.

72. What is the difference between DRS and ODBC stage?
To answer your question the DRS stage should be faster then the ODBC stage as it uses native database connectivity. You will need to install and configure the required database clients on your Data Stage server for it to work.
Dynamic Relational Stage was leveraged for PeopleSoft to have a job to run on any of the supported databases. It supports ODBC connections too. Read more of that in the plug-in documentation.
ODBC uses the ODBC driver for a particular database, DRS is a stage that tries to make it seamless for switching from one database to another. It uses the native connectivities for the chosen target

73. How to use rank and update strategy in data stage?
Don't mix informatica with Data stage.
In Data stage, we don’t have such kind of stages.

74. What is the max capacity of Hash file in Data Stage?
Take a look at the uvconfig file:
# 64BIT_FILES - This sets the default mode used to
# create static hashed and dynamic files.
# A value of 0 results in the creation of 32-bit
# files. 32-bit files have a maximum file size of
# 2 gigabytes. A value of 1 results in the creation
# of 64-bit files (ONLY valid on 64-bit capable platforms).
# The maximum file size for 64-bit
# files is system dependent. The default behavior
# may be overridden by keywords on certain commands.
64BIT_FILES 0

75. How to implement type2 slowly changing dimensions in data stage? Explain with example?
We can handle SCD in the following ways
Type 1: Just use, Insert rows Else Update rows?
Or ? Update rows Else Insert rows?, in update action of target
Type 2: Use the steps as follows
a) U have use one hash file to Look-Up the target
b) Take 3 instances of target
c) Give different conditions depending on the process
d) Give different update actions in target
e) Use system variables like Sysdate and Null.

76. How can we improve the performance of Data Stage?
Trun in-process buffer and transaction size

77. What is data set? and what is file set?
File set:- It allows you to read data from or write data to a file set. The stage can have a single input link. a single output link, and a single rejects link. It only executes in parallel mode The data files and the file that lists them are called a file set. This capability is useful because some operating systems impose a 2 GB limit on the size of a file and you need to distribute files among nodes to prevent overruns.
Datasets r used to import the data in parallel jobs like ODBC in server jobs

78. How the hash file is doing lookup in server jobs? How is it comparing the key values?
Hashed File is used for two purposes: 1. Remove Duplicate Records 2. Then Used for reference lookups. The hashed file contains 3 parts: Each record having Hashed Key, Key Header and Data portion. By using hashed algorithm and the key valued the lookup is faster.

79. What are orabulk and bcp stages?
ORABULK is used to load bulk data into single table of target oracle database.
BCP is used to load bulk data into a single table for Microsoft sql server and Sybase.

80. How is data stage 4.0 functionally different from the enterprise edition now?? what are the exact changes?
There are lot of Changes in DS EE. CDC Stage, Procedure Stage, Etc..........

81. Data Stage from Staging to MDW is only running at 1 row per second! What do we do to emedy?
I am assuming that there are too many stages, which is causing problem and providing the solution.
In general. if you too many stages (especially transformers , hash look up), there would be a lot of overhead and the performance would degrade drastically. I would suggest you to write a query instead of doing several look ups. It seems as though embarrassing to have a tool and still write a query but that is best at times.
If there are too many look ups that are being done, ensure that you have appropriate indexes while querying. If you do not want to write the query and use intermediate stages, ensure that you use proper elimination of data between stages so that data volumes do not cause overhead. So, there might be a re-ordering of stages needed for good performance.
Other things in general that could be looked in:
1) For massive transaction set hashing size and buffer size to appropriate values to perform as much as possible in memory and there is no I/O overhead to disk.
2) Enable row buffering and set appropriate size for row buffering
3) It is important to use appropriate objects between stages for performance
What user variable activity when it used how it used! Where it is used with real example
By using This User variable activity we can create some variables in the job sequence, this variables r available for all the activities in that sequence.
Most probably this activity is @ starting of the job sequence

82. How can I specify a filter command for processing data while defining sequential file output data?
We have some thing called as after job subroutine and before subroutine, with then we can execute the UNIX commands.
Here we can use the sort summand or the filter CD command

83. What are validations you perform after creating jobs in designer? What r the different type of errors u faced during loading and how u solves them?
Check for Parameters.
And check for input files are existed or not and also check for input tables existed or not and also usernames, data source names, passwords like that

84. What is the difference between Data stage and Data stage TX?
Its a critical question to answer, but one thing i can tell u that Data stage Tx is not a ETL tool & this is not a new version of Data stage 7.5.
Tx is used for ODS source, this much i know
DS TX is not a ETL tool. It comes in a category of EAI. TX helps in connecting various frontend to diff back ends. it has a inbuilt plug-in for many diff sources and targets. In short it can make ur application platform independent. It can also parse data.

85. If you are running your Parallel Job on 4 node configuration file and you have 10 stages on the canvas, how many processes does data stage creates?
Answer is 40 [considering number of stages and the player processes]
--You have 10 stages and each stage can be partitioned and run on 4 nodes which makes total number of processes generated are 40
Note: It may vary based on the operator combinability.

86. Does Enterprise Edition only add the parallel processing for better performance?
Are any stages/transformations available in the enterprise edition only?
Data Stage Standard Edition was previously called Data Stage and Data Stage Server Edition. Data Stage Enterprise Edition was originally called Orchestrate, and then renamed to Parallel Extender when purchased by Acential. Data Stage Enterprise: Server jobs, sequence jobs, parallel jobs. The enterprise edition offers parallel processing features for scalable high volume solutions. Designed originally for UNIX, it now supports Windows, Linux and Unix System Services on mainframes. ? Data Stage Enterprise MVS: Server jobs, sequence jobs, parallel jobs, mvs jobs. MVS jobs are jobs designed using an alternative set of stages that are generated into cobol/JCL code and are transferred to a mainframe to be compiled and run. Jobs are developed on a Unix or Windows server transferred to the mainframe to be compiled and run. The first two versions share the same Designer interface but have a different set of design stages depending on the type of job you are working on. Parallel jobs have parallel stages but also accept some server stages via a container. Server jobs only accept server stages, MVS jobs only accept MVS stages. There are some stages that are common to all types (such as aggregation) but they tend to have different fields and options within that stage.

87. How can you implement Complex Jobs in data stage?
Complex design means having more joins and more look ups. Then that job design will be called as complex job. We can easily implement any complex design in Data Stage by following simple tips in terms of increasing performance also. There is no limitation of using stages in a job. For better performance, Use at the Max of 20 stages in each job. If it is exceeding 20 stages then go for another job. Use not more than 7 look ups for a transformer otherwise go for including one more transformer.

88. How can u implement slowly changed dimensions in data stage? Explain?
2) can u join flat file and database in data stage? How?
Yes, we can do it in an indirect way. First create a job which can populate the data from database into a Sequential file and name it as Seq_First1. Take the flat file which you are having and use a Merge Stage to join the two files. You have various join types in Merge Stage like Pure Inner Join, Left Outer Join, Right Outer Join etc., You can use any one of these which suits your requirements.

89. How to implement routines in data stage?
There are 3 kind of routines is there in Data stage.
1. Server routines which will used in server jobs. These routines will write in BASIC Language
2. Parallel routines which will use in parallel jobs. These routines will write in C/C++ Language
3. Mainframe routines which will used in mainframe jobs

90. What is difference between server jobs & parallel jobs?
Server jobs: These are available if you have installed Data Stage Server. They run on the Data Stage Server, connecting to other data sources as necessary.
Parallel jobs: These are only available if you have installed Enterprise Edition. These run on Data Stage servers that are SMP, MPP, or cluster systems. They can also run on a separate z/OS (USS) machine if required.
In server job sequence we can run the jobs sequentially only. That’s one after another.
But in Parallel sequence we can run it parallel if there is no dependency b/w jobs.

91. How we use NLS function in Data stage? What are advantages of NLS function? Where we can use that one? Explain briefly?
By using NLS function we can do the following
- Process the data in a wide range of languages
- Use Local formats for dates, times and money
- Sort the data according to the local rules
If NLS is installed, various extra features appear in the product.
For Server jobs, NLS is implemented in Data Stage Server engine
For Parallel jobs, NLS is implemented using the ICU library.

92. What happens out put of hash file is connected to transformer ..? What error it thoughts?
If Hash file output is connected to transformer stage the hash file will consider as the Lookup file if there is no primary link to the same Transformer stage, if there is no primary link then this will treat as primary link itself. you can do SCD in server job by using Lookup functionality. This will not return any error code.

93. What is version Control?
Version Control stores different versions of DS jobs, run different versions of same job
Reverts to previous version of a job view version histories

94. What is ' inserting for update ' in data stage?
I think 'insert to update' is updated value is inserted to maintain history

95. How can we pass parameters to job by using file?
You can do this, by passing parameters from UNIX file, and then calling the execution of a data stage job. the ds job has the parameters defined (which are passed by Unix)

96. Where does Unix script of data stage executes weather in client machine or in server. Suppose if it executes on server then it will execute?
Data stage jobs are executed in the server machines only. There is nothing that is stored in the client machine.

97. Defaults nodes for data stage parallel Edition?
Actually the Number of Nodes depends on the number of processors in your system. If your system is supporting two processors we will get two nodes by default.

98. How do you merge two files in DS?
Either used Copy command as a Before-job subroutine if the metadata of the 2 files are same or created a job to concatenate the 2 files into one if the metadata is different.

99. What is DS Director used for - did u use it?
Data stage Director is GUI to monitor, run, validate & schedule data stage server jobs.

100. What are types of Hashed File?
Hashed File is classified broadly into 2 types.
a) Static - Sub divided into 17 types based on Primary Key Pattern.
b) Dynamic - sub divided into 2 types
i) Generic
ii) Specific.
Default Hashed file is "Dynamic - Type30.

101. How do you pass filename as the parameter for a job?
1. Go to Data Stage Administrator->Projects->Properties->Environment->UserDefined. Here you can see a grid, where you can enter your parameter name and the corresponding the path of the file.
2. Go to the stage Tab of the job, select the NLS tab, click on the "Use Job Parameter" and select the parameter name which you have given in the above. The selected parameter name appears in the text box beside the "Use Job Parameter" button. Copy the parameter name from the text box and use it in your job. Keep the project default in the text box.

102. How can we create Containers?
There are two types of containers
1. Local Container
2. Shared Container
Local container is available for that particular Job only.
Where as Shared Containers can be used anywhere in the project.
Local container:
Step1:Select the stages required
Step2:Edit>ConstructContainer>Local
Shared Container:
Step1:Select the stages required
Step2:Edit>ConstructContainer>Shared
Shared containers are stored in the SharedContainers branch of the Tree Structure

103. How can we improve the performance of Data Stage jobs?
Performance and tuning of DS jobs:
1. Establish Baselines
2. Avoid the Use of only one flow for tuning/performance testing
3. Work in increment
4. Evaluate data skew
5. Isolate and solve
6. Distribute file systems to eliminate bottlenecks
7. Do not involve the RDBMS in initial testing
8. Understand and evaluate the tuning knobs available.

125. What are the Job parameters?
These Parameters are used to provide Administrative access and change run time values of the job.
EDIT>JOBPARAMETERS
In that Parameters Tab we can define the name, prompt, type, value

104. What are all the third party tools used in Data Stage?
Autosys, TNG, event coordinator are some of them that I know and worked with

105. How can we implement Lookup in Data Stage Server jobs?
The DB2 stage can be used for lookups.
In the Enterprise Edition, the Lookup stage can be used for doing lookups.
We can implement lookups in server jobs using Hash files and Transformer stage. Lookup stages are available in parallel jobs only.

106. What is the utility you use to schedule the jobs on a UNIX server other than using Ascential Director?
"AUTOSYS": Through autosys u can automate the job by invoking the shell script written to schedule the data stage jobs.

107. What are Sequencers?
A sequencer allows you to synchronize the control flow of multiple activities in a job sequence. It can have multiple input triggers as well as multiple output triggers. The sequencer operates in two modes: ALL modes. In this mode all of the inputs to the sequencer must be TRUE for any of the sequencer outputs to fire. ANY mode, in this mode, output triggers can be fired if any of the sequencer inputs are TRUE

108. Explain the differences between Oracle8i/9i?
Mutli processing, databases more dimensional modeling

109. What r XML files and how do you read data from XML files and what stage to be used?
In the pallet there is Real time stages like xml-input, xml-output, xml-transformer

110 How do you pass the parameter to the job sequence if the job is running at night?
Two ways
1. Ste the default values of Parameters in the Job Sequencer and map these parameters to job.
2. Run the job in the sequencer using dsjobs utility where we can specify the values to be taken for each parameter.

111. How do you remove duplicates without using remove duplicate stage?
In the target make the column as the key column and run the job.
hi , by using sort stage we remove duplicates as making options allow duplicates= false.

112. What is the flow of loading data into fact & dimensional tables?
Here is the sequence of loading a data warehouse.
1. The source data is first loading into the staging area, where data cleansing takes place.
2. The data from staging area is then loaded into dimensions/lookups.
3.Finally the Fact tables are loaded from the corresponding source tables from the staging area.

113. what is the difference between DNS DHCP ?
File system of windows? compare with nis & dns
No answer available currently. Be the first one to reply to this question by submitting your answer from the form below.

114. Functionality of Link Partioner and Link Collector?
server jobs mainly execute the jobs in sequential fashion,the ipc stage as well as link partioner and link collector will simulate the parllel mode of execution over the sever jobs having single cpu Link Partitioner : It receives data on a single input link and diverts the data to a maximum no.of 64 output links and the data processed by the same stage having same meta dataLink Collector : It will collects the data from 64 inputlinks, merges it into a single data flowand loads to target. these both r active stagesand the design and mode of execution of serverjobs has to be decidead by the designer
Link Partitioner : It actually splits data into various partitions or data flows using various
partition methods .
Link Collector : It collects the data coming from partitions, merges it into a single data flow and loads to target.
Containers : Usage and Types?
Container is a collection of stages used for the purpose of Reusability. There are 2 types of Containers.
a) Local Container: Job Specific
b) Shared Container: Used in any job within a project. ?
There are two types of shared container:?
1.Server shared container. Used in server jobs (can also be used in parallel jobs).?
2.Parallel shared container. Used in parallel jobs. You can also include server shared containers in parallel jobs as a way of incorporating server job functionality into a parallel stage (for example, you could use one to make a server plug-in stage available to a parallel job).regards jagan

115. What are Stage Variables, Derivations and Constants?
Stage Variable - An intermediate processing variable that retains value during read and doesnt pass the value into target column.
Derivation - Expression that specifies value to be passed on to the target column.
Constant - Conditions that are either true or false that specifies flow of data with a link.
In datastage transformer stage can mention Constraint to columns deviate the output to desired manner not constant

116. How to improve the performance of hash file?
You can improve performance of hashed file by
1 .Preloading hash file into memory --this can be done by enabling preloading options in hash file output stage
2. Write caching options --.It makes data written into cache before being flushed to disk. you can enable this to ensure that hash files are written in order onto cash before flushed to disk instead of order in which individual rows are written
3 .Preallocating-- Estimating the approx size of the hash file so that file needs not to be splitted to often after write operation

117. What does separation option in static hash-file mean?
The different hashing algorithms are designed to distribute records evenly among the groups of the file based on characters and their position in the record ids.
When a hashed file is created, Separation and Modulo respectively specifies the group buffer size and the number of buffers allocated for a file. When a Static Hash file is created, DATASTAGE creates a file that contains the number of groups specified by modulo.
Size of Hash file = modulus(no. groups) * Separations (buffer size)

118. How to remove duplicates in server job?
1) Use a hashed file stage or
2) If you use sort command in UNIX(before job sub-routine), you can reject duplicated records using -u parameter or
3) using a Sort stage

119. If I add a new environment variable in Windows, how can I access it in Data Stage?
U can view all the environment variables in designer. U can check it in Job properties. U can add and access the environment variables from Job properties

120. There are three different types of user-created stages available for PX.
What are they? Which would you use? What are the disadvantage for using each type?
These are the three different stages:
i) Custom ii) Build iii) Wrapped

121. I want to process 3 files in sequentially one by one, how can I do that? While processing the files it should fetch files automatically?
If the metadata for all the files r same then create a job having file name as parameter, then use same job in routine and call the job with different file name...or u can create sequencer to use the job...
I think in datastage8.0.1 there is an option in the sequence job namely loop via which the purpose can be achieved

122. What is difference between data stage and informatica?
Here is a very good articles on these differences... whic hhelps to get an idea.. Basically it's depends on what you are tring to accomplish

123. What are the requirements for your ETL tool? Do you have large sequential files (1 million rows, for example) that need to be compared every day versus yesterday?
If so, then ask how each vendor would do that. Think about what process they are going to do. Are they requiring you to load yesterdays file into a table and do lookups?
If so, RUN!! Are they doing a match/merge routine that knows how to process this in sequential files? Then maybe they are the right one. It all depends on what you need the ETL to do. If you are small enough in your data sets, then either would probably be OK.

124. Whets difference between operational data stage (ODS) & data warehouse?
A data warehouse is a decision support database for organizational needs. It is subject oriented, on volatile, integrated and time variant collect of data.
ODS (Operational Data Source) is a integrated collection of related information. It contains maximum 90 days information.

125. What is iconv and oconv functions?
Iconv( )-----converts string to internal storage formatOconv( )----converts an expression to an output format

126. What will you in a situation where somebody wants to send you a file and use that file as an input or reference and then run job?
A. Under Windows: Use the 'WaitForFileActivity' under the Sequencers and then run the job. May be you can schedule the sequencer around the time the file is expected to arrive.
B. Under UNIX: Poll for the file. Once the file has start the job or sequencer depending on the file.

127. Did you work in UNIX environment?
Some times u need to write Unix programs in back round like batch programs ! Because data stage can invoke a batch processing in every 24 hrs. so.......Unix must... so that we can run the Unix program, in back round even min/ hrs

128. What is Hash file stage and what is it used for?
We can also use the Hash File stage to avoid / remove duplicate rows by specifying the hash key on a particular filled

129. Did you Parameterize the job or hard-coded the values in the jobs?
Always parameterized the job. Either the values are coming from Job Properties or from a ?Parameter Manager? ? a third part tool. There is no way you will hard? Code some parameters in your jobs. The often Parameterized variables in a job are: DB DSN name, username, password, dates W.R.T for the data to be looked against it.

130. Suppose if there are million records did you use OCI? if not then what stage do you prefer?
Using Orabulk

131. What are environment variables? What is the use of this?
Basically Environment variable is predefined variable those we can use while creating DS job. We can set either as Project level or Job level. Once we set specific variable that variable will be available into the project/job.
We can also define new environment variable. For that we can got to DS Admin .

132. What is data set? And what is file set?
File set:- It allows you to read data from or write data to a file set. The stage can have a single input link. a single output link, and a single rejects link. It only executes in parallel modeThe data files and the file that lists them are called a file set. This capability is useful because some operating systems impose a 2 GB limit on the size of a file and you need to distribute files among nodes to prevent overruns.
Datasets r used to import the data in parallel jobs like odbc in server jobs

133. How the hash file is doing lookup in server jobs? How is it comparing the key values?
Hashed File is used for two purpose: 1. Remove Duplicate Records 2. Then Used for reference lookups. The hashed file contains 3 parts: Each record having Hashed Key, Key Header and Data portion. By using hashed algorithm and the key valued the lookup is faster.

134. What are orabulk and bcp stages?
ORABULK is used to load bulk data into single table of target oracle database.
BCP is used to load bulk data into a single table for Microsoft sql server and Sybase.

135. How is datastage 4.0 functionally different from the enterprise edition now?? what are the exact changes?
There are lot of Changes in DS EE. CDC Stage, Procedure Stage, Etc..........

136. Data Stage from Staging to MDW is only running at 1 row per second! What do we do to remedy?
I am assuming that there are too many stages, which is causing problem and providing the solution.
In general. if you too many stages (especially transformers , hash look up), there would be a lot of overhead and the performance would degrade drastically. I would suggest you to write a query instead of doing several look ups. It seems as though embarrassing to have a tool and still write a query but that is best at times. If there are too many look ups that are being done, ensure that you have appropriate indexes while querying. If you do not want to write the query and use intermediate stages, ensure that you use proper elimination of data between stages so that data volumes do not cause overhead. So, there might be a re-ordering of stages needed for good performance.
Other things in general that could be looked in:
1) for massive transaction set hashing size and buffer size to appropriate values to perform as much as possible in memory and there is no I/O overhead to disk.
2) Enable row buffering and set appropriate size for row buffering
3) It is important to use appropriate objects between stages for performance

137. What user variable activity when it used how it used! Where it is used with real example?
By using This User variable activity we can create some variables in the job sequence, this variables r available for all the activities in that sequence.
Most probably this activity is @ starting of the job sequence

138. How can I specify a filter command for processing data while defining sequential file output data?
We have some thing called as after job subroutine and before subroutine, with then we can execute the Unix commands.
Here we can use the sort summand or the filter cdommand

139. What is the difference between Data stage and Data stage TX?
Its a critical question to answer, but one thing i can tell u that Data stage Tx is not a ETL tool & this is not a new version of Data stage 7.5.
Tx is used for ODS source ,this much i know
DS TX is not a ETL tool. It comes in a category of EAI.
TX helps in connecting varios frontend to diff backends.it has a inbuilt plugin for many diff sources and targets.In short it can make ur application platform independent.It can also parse data.

140. If your running 4 ways parallel and you have 10 stages on the canvas, how many processes does data stage create?
Answer is 40
You have 10 stages and each stage can be partitioned and run on 4 nodes which makes total number of processes generated are 40

141. Does Enterprise Edition only add the parallel processing for better performance?
Are any stages/transformations available in the enterprise edition only?
? Data Stage Standard Edition was previously called Data Stage and Data Stage Server Edition. ? Data Stage Enterprise Edition was originally called Orchestrate, then renamed to Parallel Extender when purchased by Ascential. ? DataStage Enterprise: Server jobs, sequence jobs, parallel jobs. The enterprise edition offers parallel processing features for scalable high volume solutions. Designed originally for Unix, it now supports Windows, Linux and Unix System Services on mainframes. ? Data Stage Enterprise MVS: Server jobs, sequence jobs, parallel jobs, mvs jobs. MVS jobs are jobs designed using an alternative set of stages that are generated into Cobol/JCL code and are transferred to a mainframe to be compiled and run. Jobs are developed on a Unix or Windows server transferred to the mainframe to be compiled and run. The first two versions share the same Designer interface but have a different set of design stages depending on the type of job you are working on. Parallel jobs have parallel stages but also accept some server stages via a container. Server jobs only accept server stages, MVS jobs only accept MVS stages. There are some stages that are common to all types (such as aggregation) but they tend to have different fields and options within that stage.

142. How can you implement Complex Jobs in data stage?
Complex design means having more joins and more look ups. Then that job design will be called as complex job. We can easily implement any complex design in DataStage by following simple tips in terms of increasing performance also. There is no limitation of using stages in a job. For better performance, Use at the Max of 20 stages in each job. If it is exceeding 20 stages then go for another job.Use not more than 7 look ups for a transformer otherwise go for including one more transformer.

143. How can u implement slowly changed dimensions in data stage? Explain?
2) can u join flat file and database in data stage? How?
Yes, we can do it in an indirect way. First create a job which can populate the data from database into a Sequencial file and name it as Seq_First1. Take theflat file which you are having and use a Merge Stage to join the two files. You have various join types in Merge Stage like Pure Inner Join, Left Outer Join, Right Outer Join etc., You can use any one of these which suits your requirements.

144. I want to process 3 files in sequentially one by one , how can i do that. while processing the files it should fetch files automatically?
If the metadata for all the files r same then create a job having file name as parameter, then use same job in routine and call the job with different file name...or u can create sequencer to use the job...
I think in datastage8.0.1 there is an option in the sequence job namely loop via which the purpose can be achieved

145. What is difference between server jobs & parallel jobs?
Here is the diff
Server jobs: These are available if you have installed Data Stage Server. They run on the Data Stage Server, connecting to other data sources as necessary.
Parallel jobs: These are only available if you have installed Enterprise Edition. These run on Data Stage servers that are SMP, MPP, or cluster systems. They can also run on a separate z/OS (USS) machine if required.
In server job sequence we can run the jobs sequentially only. That’s one after another.
But in Parallel sequence we can run it parallel if there are no dependency b/w jobs.
146. How we use NLS function in Data stage? What are advantages of NLS function? where we can use that one? Explain briefly?
By using NLS function we can do the following
- Process the data in a wide range of languages
- Use Local formats for dates, times and money
- Sort the data according to the local rules
If NLS is installed, various extra features appear in the product.
For Server jobs, NLS is implemented in Data Stage Server engine
For Parallel jobs, NLS is implemented using the ICU library.

147. What happens out put of hash file is connected to transformer .. What error it troughs?
If Hash file output is connected to transformer stage the hash file will consider as the Lookup file if there is no primary link to the same Transformer stage, if there is no primary link then this will treat as primary link itself. you can do SCD in server job by using Lookup functionality. This will not return any error code.

148. What is ' insert for update ' in data stage?
I think 'insert to update' is updated value is inserted to maintain history

149. How can we pass parameters to job by using file.?
You can do this, by passing parameters from Unix file, and then calling the execution of a data stage job. the ds job has the parameters defined (which are passed by Unix)

150. Defaults nodes for data stage parallel Edition?
Actually the Number of Nodes depend on the number of processors in your system. If your system is supporting two processors we will get two nodes by default.

151. How can we implement Lookup in Data Stage Server jobs?
The DB2 stage can be used for lookups.
In the Enterprise Edition, the Lookup stage can be used for doing lookups.
We can implement lookups in server jobs using Hash files and Transformer stage. Lookup stages are available in parallel jobs only.

152. What is the utility you use to schedule the jobs on a UNIX server other than using Ascential Director?
"AUTOSYS": Thru autosys u can automate the job by invoking the shell script written to schedule the datastage jobs.

153. what is the difference between DNS DHCP ?
file system of windows?
compare with nis & dns
No answer available currently. Be the first one to reply to this question by submitting your answer from the form below.

154. Functionality of Link Partitioner and Link Collector?
server jobs mainly execute the jobs in sequential fashion,the ipc stage as well as link partioner and link collector will simulate the parllel mode of execution over the sever jobs having single cpu Link Partitioner : It receives data on a single input link and diverts the data to a maximum no.of 64 output links and the data processed by the same stage having same meta dataLink Collector : It will collects the data from 64 inputlinks, merges it into a single data flowand loads to target. these both r active stagesand the design and mode of execution of serverjobs has to be decidead by the designer
Link Partitioner : It actually splits data into various partitions or data flows using various
partition methods .
Link Collector : It collects the data coming from partitions, merges it into a single data flow and loads to target.
Containers : Usage and Types?
Container is a collection of stages used for the purpose of Reusability. There are 2 types of Containers.
a) Local Container: Job Specific
b) Shared Container: Used in any job within a project. ?
There are two types of shared container:?
1.Server shared container. Used in server jobs (can also be used in parallel jobs).?
2.Parallel shared container. Used in parallel jobs. You can also include server shared containers in parallel jobs as a way of incorporating server job functionality into a parallel stage (for example, you could use one to make a server plug-in stage available to a parallel job).

DataStage Director

2010-10-10T21:37:00.000+05:30

Use the Director to validate, run, schedule, and monitor your DataStage jobs. You can also gather statistics as the job runs.

DataStage Designer

2010-09-26T08:36:00.001+05:30

The DataStage Designer allows you to use familiar graphical point-and-click techniques to develop job flows for extracting, cleansing, transforming, integrating and loading data into target files and tables.

The Designer provides a “visual data flow”method to easily interconnect and configure reusable components.

DataStage Administrator

2010-09-12T20:30:00.002+05:30

Use the Administrator to specify general server defaults, add and delete projects, and to set project properties. The Administrator alsoprovides a command interface to the DataStage repository.

Use the Administrator Project Properties window to:

Set job monitoring limits and other Director defaults on the General tab.
Set user and group privileges on the Permissions tab.
Enable or disable server-side tracing on the Tracing tab.
Specify a user name and password for scheduling jobs on the Schedule tab.
Specify parallel job defaults on the Parallel tab.
Specify Job Sequencer defaults on the Sequence tab.

DW ETL Process

2010-08-20T09:19:00.000+05:30

Extract, transform and load (ETL) is a process in database usage and especially in data warehousing that involves:

* Extracting data from outside sources
* Transforming it to fit operational needs (which can include quality levels)
* Loading it into the end target (database or data warehouse)

Extract

The first part of an ETL process involves extracting the data from the source systems.
Most data warehousing projects consolidate data from different source systems. Each separate system may also use a different data organization/format. Common data source formats are relational databases and flat files, but may include non-relational database structures such as Information Management System (IMS) or other data structures such as Virtual Storage Access Method (VSAM) or Indexed Sequential Access Method (ISAM), or even fetching from outside sources such as through web spidering or screen-scraping. The streaming of the extracted data source and load on-the-fly to the destination database is another way of performing ETL when no intermediate data storage is required. In general, the goal of the extraction phase is to convert the data into a single format which is appropriate for transformation processing.

An intrinsic part of the extraction involves the parsing of extracted data, resulting in a check if the data meets an expected pattern or structure. If not, the data may be rejected entirely or in part.

Transform

The transform stage applies a series of rules or functions to the extracted data from the source to derive the data for loading into the end target. Some data sources will require very little or even no manipulation of data. In other cases, one or more of the following transformation types may be required to meet the business and technical needs of the target database:

* Selecting only certain columns to load (or selecting null columns not to load). For example, if the source data has three columns (also called attributes) say roll_no, age and salary then the extraction may take only roll_no and salary. Similarly, the extraction mechanism may ignore all those records where salary is not present (salary = null).
* Translating coded values (e.g., if the source system stores 1 for male and 2 for female, but the warehouse stores M for male and F for female), this calls for automated data cleansing; no manual cleansing occurs during ETL
* Encoding free-form values (e.g., mapping "Male" to "1" and "Mr" to M)
* Deriving a new calculated value (e.g., sale_amount = qty * unit_price)
* Sorting
* Joining data from multiple sources (e.g., lookup, merge)
* Aggregation (for example, rollup — summarizing multiple rows of data — total sales for each store, and for each region, etc.)
* Generating surrogate-key values
* Transposing or pivoting (turning multiple columns into multiple rows or vice versa)
* Splitting a column into multiple columns (e.g., putting a comma-separated list specified as a string in one column as individual values in different columns)
* Disaggregation of repeating columns into a separate detail table (e.g., moving a series of addresses in one record into single addresses in a set of records in a linked address table)
* Lookup and validate the relevant data from tables or referential files for slowly changing dimensions.
* Applying any form of simple or complex data validation. If validation fails, it may result in a full, partial or no rejection of the data, and thus none, some or all the data is handed over to the next step, depending on the rule design and exception handling. Many of the above transformations may result in exceptions, for example, when a code translation parses an unknown code in the extracted data.

Load

The load phase loads the data into the end target, usually the data warehouse (DW). Depending on the requirements of the organization, this process varies widely. Some data warehouses may overwrite existing information with cumulative information, frequently updating extract data is done on daily, weekly or monthly basis. Other DW (or even other parts of the same DW) may add new data in a historicized form, for example, hourly. To understand this, consider a DW that is required to maintain sales records of the last year. Then, the DW will overwrite any data that is older than a year with newer data. However, the entry of data for any one year window will be made in a historicized manner. The timing and scope to replace or append are strategic design choices dependent on the time available and the business needs. More complex systems can maintain a history and audit trail of all changes to the data loaded in the DW.

As the load phase interacts with a database, the constraints defined in the database schema — as well as in triggers activated upon data load — apply (for example, uniqueness, referential integrity, mandatory fields), which also contribute to the overall data quality performance of the ETL process.

* For example, a financial institution might have information on a customer in several departments and each department might have that customer's information listed in a different way. The membership department might list the customer by name, whereas the accounting department might list the customer by number. ETL can bundle all this data and consolidate it into a uniform presentation, such as for storing in a database or data warehouse.

* Another way that companies use ETL is to move information to another application permanently. For instance, the new application might use another database vendor and most likely a very different database schema. ETL can be used to transform the data into a format suitable for the new application to use.

Real-life ETL cycle

The typical real-life ETL cycle consists of the following execution steps:

1. Cycle initiation
2. Build reference data
3. Extract (from sources)
4. Validate
5. Transform (clean, apply business rules, check for data integrity, create aggregates or disaggregates)
6. Stage (load into staging tables, if used)
7. Audit reports (for example, on compliance with business rules. Also, in case of failure, helps to diagnose/repair)
8. Publish (to target tables)
9. Archive
10. Clean up

Challenges

ETL processes can involve considerable complexity, and significant operational problems can occur with improperly designed ETL systems.

The range of data values or data quality in an operational system may exceed the expectations of designers at the time validation and transformation rules are specified. Data profiling of a source during data analysis can identify the data conditions that will need to be managed by transform rules specifications. This will lead to an amendment of validation rules explicitly and implicitly implemented in the ETL process.

Data warehouses are typically assembled from a variety of data sources with different formats and purposes. As such, ETL is a key process to bring all the data together in a standard, homogenous environment.

Design analysts should establish the scalability of an ETL system across the lifetime of its usage. This includes understanding the volumes of data that will have to be processed within service level agreements. The time available to extract from source systems may change, which may mean the same amount of data may have to be processed in less time. Some ETL systems have to scale to process terabytes of data to update data warehouses with tens of terabytes of data. Increasing volumes of data may require designs that can scale from daily batch to multiple-day microbatch to integration with message queues or real-time change-data capture for continuous transformation and update.

Performance

ETL vendors benchmark their record-systems at multiple TB (terabytes) per hour (or ~1 GB per second) using powerful servers with multiple CPUs, multiple hard drives, multiple gigabit-network connections, and lots of memory.

In real life, the slowest part of an ETL process usually occurs in the database load phase. Databases may perform slowly because they have to take care of concurrency, integrity maintenance, and indices. Thus, for better performance, it may make sense to do

* Direct Path Extract method or bulk unload whenever is possible (instead of querying the database) to reduce the load on source system while getting high speed extract
* most of the transformation processing outside of the database
* and to use bulk load operations whenever possible.

Still, even using bulk operations, database access is usually the bottleneck in the ETL process. Some common methods used to increase performance are:

* Partition tables (and indices). Try to keep partitions similar in size (watch for null values which can skew the partitioning).
* Do all validation in the ETL layer before the load. Disable integrity checking (disable constraint ...) in the target database tables during the load.
* Disable triggers (disable trigger ...) in the target database tables during the load. Simulate their effect as a separate step.
* Generate IDs in the ETL layer (not in the database).
* Drop the indices (on a table or partition) before the load - and recreate them after the load (SQL: drop index ...; create index ...).
* Use parallel bulk load when possible — works well when the table is partitioned or there are no indices. Note: attempt to do parallel loads into the same table (partition) usually causes locks — if not on the data rows, then on indices.
* If a requirement exists to do insertions, updates, or deletions, find out which rows should be processed in which way in the ETL layer, and then process these three operations in the database separately. You often can do bulk load for inserts, but updates and deletes commonly go through an API (using SQL).

Whether to do certain operations in the database or outside may involve a trade-off. For example, removing duplicates using distinct may be slow in the database; thus, it makes sense to do it outside. On the other side, if using distinct will significantly (x100) decrease the number of rows to be extracted, then it makes sense to remove duplications as early as possible in the database before unloading data.

A common source of problems in ETL is a big number of dependencies among ETL jobs. For example, job "B" cannot start while job "A" is not finished. You can usually achieve better performance by visualizing all processes on a graph, and trying to reduce the graph making maximum use of parallelism, and making "chains" of consecutive processing as short as possible. Again, partitioning of big tables and of their indices can really help.

Another common issue occurs when the data is spread between several databases, and processing is done in those databases sequentially. Sometimes database replication may be involved as a method of copying data between databases - and this can significantly slow down the whole process. The common solution is to reduce the processing graph to only three layers:

* Sources
* Central ETL layer
* Targets

This allows processing to take maximum advantage of parallel processing. For example, if you need to load data into two databases, you can run the loads in parallel (instead of loading into 1st - and then replicating into the 2nd).

Of course, sometimes processing must take place sequentially. For example, you usually need to get dimensional (reference) data before you can get and validate the rows for main "fact" tables.

Parallel processing

A recent[update] development in ETL software is the implementation of parallel processing. This has enabled a number of methods to improve overall performance of ETL processes when dealing with large volumes of data.

ETL applications implement three main types of parallelism:

* Data: By splitting a single sequential file into smaller data files to provide parallel access.
* Pipeline: Allowing the simultaneous running of several components on the same data stream. For example: looking up a value on record 1 at the same time as adding two fields on record 2.
* Component: The simultaneous running of multiple processes on different data streams in the same job, for example, sorting one input file while removing duplicates on another file.

All three types of parallelism usually operate combined in a single job.

An additional difficulty comes with making sure that the data being uploaded is relatively consistent. Because multiple source databases may have different update cycles (some may be updated every few minutes, while others may take days or weeks), an ETL system may be required to hold back certain data until all sources are synchronized. Likewise, where a warehouse may have to be reconciled to the contents in a source system or with the general ledger, establishing synchronization and reconciliation points becomes necessary.

Rerunnability, recoverability

Data warehousing procedures usually subdivide a big ETL process into smaller pieces running sequentially or in parallel. To keep track of data flows, it makes sense to tag each data row with "row_id", and tag each piece of the process with "run_id". In case of a failure, having these IDs will help to roll back and rerun the failed piece.

Best practice also calls for "checkpoints", which are states when certain phases of the process are completed. Once at a checkpoint, it is a good idea to write everything to disk, clean out some temporary files, log the state, and so on.

Best practices

Four-layered approach for ETL architecture design

* Functional layer: Core functional ETL processing (extract, transform, and load).
* Operational management layer: Job-stream definition and management, parameters, scheduling, monitoring, communication and alerting.
* Audit, balance and control (ABC) layer: Job-execution statistics, balancing and controls, rejects- and error-handling, codes management.
* Utility layer: Common components supporting all other layers.

Use file-based ETL processing where possible

* Storage costs relatively little
* Intermediate files serve multiple purposes:
o Used for testing and debugging
o Used for restart and recover processing
o Used to calculate control statistics
* Helps to reduce dependencies - enables modular programming.
* Allows flexibility for job-execution and -scheduling
* Better performance if coded properly, and can take advantage of parallel processing capabilities when the need arises.

Use data-driven methods and minimize custom ETL coding

* Parameter-driven jobs, functions, and job-control
* Code definitions and mapping in database
* Consideration for data-driven tables to support more complex code-mappings and business-rule application.

Qualities of a good ETL architecture design

* Performance
* Scalable
* Migratable
* Recoverable (run_id, ...)
* Operable (completion-codes for phases, re-running from checkpoints, etc.)
* Auditable (in two dimensions: business requirements and technical troubleshooting)

Dealing with keys

Keys are some of the most important objects in all relational databases as they tie everything together. A primary key is a column which is the identifier for a given entity, where a foreign key is a column in another table which refers a primary key.
These keys can also be made up from several columns, in which case they are composite keys. In many cases the primary key is an auto generated integer which has no meaning for the business entity being represented, but solely exists for the purpose of the relational database - commonly referred to as a surrogate key.

As there will usually be more than one datasource being loaded into the warehouse the keys are an important concern to be addressed.
Your customers might be represented in several data sources, and in one their SSN (Social Security Number) might be the primary key, their phone number in another and a surrogate in the third. All of the customers information needs to be consolidated into one dimension table.

A recommended way to deal with the concern is to add a warehouse surrogate key, which will be used as foreign key from the fact table.[1]

Usually updates will occur to a dimension's source data, which obviously must be reflected in the data warehouse.
If the primary key of the source data is required for reporting, the dimension already contains that piece of information for each row. If the source data uses a surrogate key, the ware house must keep track of it even though it is never used in queries or reports.

That is done by creating a lookup table which contains the warehouse surrogate key and the originating key [2]. This way the dimension is not polluted with surrogates from various source systems, while the ability to update is preserved.

The lookup table is used in different ways depending on the nature of the source data. There are 5 types to consider [3], where three selected ones are included here:
Type 1:
- The dimension row is simply updated to match the current state of the source system. The warehouse does not capture history. The lookup table is used to identify which dimension row to update/overwrite.
Type 2:
- A new dimension row is added with the new state of the source system. A new surrogate key is assigned. Source key is no longer unique in the lookup table.
Fully-logged:
- A new dimension row is added with the new state of the source system, while the previous dimension row is updated to reflect it is no longer active and record time of deactivation.

Work should be put in to guidance on which situations the options apply to. Is that solely a business decision?
Which factors influence the choice? The update strategy might (full wipe, incremental etc.)

Tools

Programmers can set up ETL processes using almost any programming language, but building such processes from scratch can become complex. Increasingly, companies are buying ETL tools to help in the creation of ETL processes.[citation needed]

By using an established ETL framework, one may increase one's chances of ending up with better connectivity and scalability. A good ETL tool must be able to communicate with the many different relational databases and read the various file formats used throughout an organization. ETL tools have started to migrate into Enterprise Application Integration, or even Enterprise Service Bus, systems that now cover much more than just the extraction, transformation, and loading of data. Many ETL vendors now have data profiling, data quality, and metadata capabilities.

Open-source ETL frameworks

* Apatar
* CloverETL
* Flat File Checker
* Jitterbit 2.0
* Pentaho Data Integration Kettle Project
* RapidMiner
* Scriptella
* Talend Open Studio

Proprietary ETL frameworks

* Adeptia
* IBM InfoSphere DataStage
* Informatica PowerCenter
* Oracle Data Integrator (ODI)
* Ab Initio
* Altova MapForce
* HiT Software Allora
* Digital Fuel Service Flow
* WisdomForce DatabaseSync System
* Phocas ETL
* Microsoft SQL Server Integration Services (SSIS)
* Coglin Mill RODIN Data Asset Management
* Twister Data Integrator (TDI)
* SAS Data Integration Studio
* SnapLogic Server

DataStage Architecture

2010-08-05T20:24:00.000+05:30

The top half displays the Clients. Below there are two engines: The Server engine that runs DataStage server jobs and the parallel engine that runs parallel jobs. Our focus in this course is on Parallel jobs.

The DataStage client components are:

Administrator
Administers DataStage projects and conducts housekeeping on the server

Designer
Creates DataStage jobs that are compiled into executable programs

Director
Used to run and monitor the DataStage jobs
The Repository is used to store DataStage objects. The Repository is shared with other applications in the Suite.

DataStage Overview

2010-07-20T08:21:00.001+05:30

IBM® InfoSphere® DataStage® integrates data across multiple and high volumes of data sources and target applications.

IBM InfoSphere DataStage is an ETL tool and part of the IBM Information Platforms Solutions suite and the IBM InfoSphere. It uses a graphical notation to construct data integration solutions and is available in various versions such as the Server Edition and the Enterprise Edition. Like several other IBM products (e.g. IBM WebSphere Portal from the IBM Lotus family), DataStage belongs to another brand than points its name.
DataStage was conceived at VMark, a spin off from Prime Computers that developed two notable products: UniVerse database and the DataStage ETL tool. The first VMark ETL prototype was built by Lee Scheffler in the first half of 1996. Peter Weyman was VMark VP of Strategy and identified the ETL market as an opportunity. He appointed Lee Scheffler as the architect and conceived the product brand name "Stage" to signify modularity and component-orientation]. This tag was used to name DataStage and subsequently used in related products QualityStage, ProfileStage, MetaStage and AuditStage. Lee Scheffler presented the DataStage product overview to the board of VMark in June 1996 and it was approved for development. The product was in alpha testing in October, beta testing in November and was generally available in January 1997.

VMark acquired UniData in October 1997 and renamed itself to Ardent Software. In 1999 Ardent Software was acquired by Informix the database software vendor. In April 2001 IBM acquired Informix and took just the database business leaving the data integration tools to be spun off as an independent software company called Ascential Software. In November 2001, Ascential Software Corp. of Westboro, Mass. acquired privately held Torrent Systems Inc. of Cambridge, Mass. for $46 million in cash. Ascential has stated a commitment to integrate Orchestrate's parallel processing capabilities directly into the DataStageXE platform. . In March 2005 IBM acquired Ascential Software and made DataStage part of the WebSphere family as WebSphere DataStage. In 2006 the product was released as part of the IBM Information Server under the Information Management family but was still known as WebSphere DataStage. In 2008 the suite was renamed to InfoSphere Information Server and the product was renamed to InfoSphere DataStage.

• Enterprise Edition (PX): a name give to the version of DataStage that had a parallel processing architecture and parallel ETL jobs.
• Server Edition: the name of the original version of DataStage representing Server Jobs. Early DataStage versions only contained Server Jobs. DataStage 5 added Sequence Jobs and DataStage 6 added Parallel Jobs via Enterprise Edition.
• MVS Edition: mainframe jobs, developed on a Windows or Unix/Linux platform and transferred to the mainframe as compiled mainframe jobs.
• DataStage for PeopleSoft: a server edition with prebuilt PeopleSoft EPM jobs under an OEM arragement with PeopleSoft and Oracle Corporation.
• DataStage TX: for processing complex transactions and messages, formerly known as Mercator. Now known as WebSphere Transformation Extender.
• ISD (Information Services Director, ex. DataStage RTI): Real Time Integration pack can turn server or parallel jobs into SOA services.

InfoSphere DataStage is a powerful data integration tool. It was acquired by IBM in 2005 and has become a part of IBM Information Server Platform. It uses a client/server design where jobs are created and administered via a Windows client against central repository on a server. The IBM InfoSphere DataStage is capable of integrating data on demand across multiple and high volumes of data sources and target applications using a high performance parallel framework. InfoSphere DataStage also facilitates extended metadata management and enterprise connectivity

It has three levels of Parallelism like AbInito which are:

1) Pipeline Parallelism

2) Data Parallelism

3) Component Parallelism

Architecture

It integrates data on demand with a high performance parallel framework, extended metadata management, and enterprise connectivity.

* Supports the collection, integration and transformation of large volumes of data, with data structures ranging from simple to highly complex.
* Offers scalable platform that enables companies to solve large-scale business problems through high-performance processing of massive data volumes
* Supports real-time data integration.
* Enables developers to maximize speed, flexibility and effectiveness in building, deploying, updating and managing their data integration infrastructure.
* Completes connectivity between any data source and any application.

What is IBM WebSphere DataStage?

• Design jobs for Extraction, Transformation, and Loading (ETL)
• Ideal tool for data integration projects –such as, data warehouses, data marts, and system migrations
• Import, export, create, and manage metadata for use within jobs
• Schedule, run, and monitor jobs, all within DataStage
• Administer your DataStage development and execution environments Create batch (controlling) jobs

DataStage is a comprehensive tool for the fast, easy creation and maintenance of data marts and data warehouses. It provides the tools you need to build, manage, and expand them. With DataStage, you canbuild solutions faster and give users access to the data and reports they need.

With DataStage you can:
• Design the jobs that extract, integrate, aggregate, load, and transform the data for your data warehouse or data mart.
• Create and reuse metadata and job components.
• Run, monitor, and schedule these jobs.
• Administer your development and execution environments.

IBM InfoSphere DataStage

New features and changes in InfoSphere Information Server Version 11.7

Governance updates

Information Governance Catalog New

Information Server Enterprise Search

InfoSphere Information Governance Catalog

Information Server Governance Monitor

Data Quality updates

InfoSphere Information Analyzer

Integration updates

DataStage Flow Designer

InfoSphere DataStage

Connectivity

InfoSphere Information Server on Hadoop

Administration and management updates

Managing metadata

Deprecated features

How do I make the Oracle EE Stage Read Operator Run in Parallel?

DataStage Scenario on Finding Unique Distance from Source and Destination

Performance Optimization technique to handle variable length data in Data Set

How to replace a string in DataStage

New features and changes for IBM InfoSphere Information Server, Version 8.7

Table of contents

Administering

Connecting to external sources

InfoSphere Blueprint Director

InfoSphere Metadata Asset Manager

Managing security

InfoSphere Business Glossary

InfoSphere DataStage

InfoSphere Information Analyzer

InfoSphere Metadata Workbench

InfoSphere QualityStage

Documentation introduced with Version 8.7

The tsort operator

The tsort operator

Example: using a parallel tsort operator

Performing a total sort

How to generate Sequence Numbers in DataStage using @INROWNUM and @PARTITIONNUM System Variables

New features & changes in IBM InfoSphere Information Server 8.5

Table of contents

Suite and product module changes

IBM InfoSphere Business Glossary

IBM InfoSphere DataStage

IBM InfoSphere FastTrack

IBM InfoSphere Information Analyzer

IBM InfoSphere Information Services Director

IBM InfoSphere Metadata Workbench

IBM InfoSphere QualityStage

Documentation introduced with Version 8.5

DataStage Parallel Processing

DataStage Best Practices

Standards

Development guidelines

Component usage

DataStage data types

Partitioning data

Collecting data

Sorting

Stage specific guidelines

DataStage OSH Script

DataStage Modules

DataStage Execution Flow

Data Transformations

DataStage Stages and Jobs

DataStage Functions

Functions used in IBM InfoSphere DataStage and QualityStage

Date and time functions

Logical Functions

Mathematical Functions

Null handling functions

Number functions

Raw functions

String functions

Vector function

Type Conversion Functions

Type "casting" functions

Utility functions

DataStage FAQs and Best Practices

Interview Questions