Thursday, December 21, 2017

New features and changes in InfoSphere Information Server Version 11.7

Posted by Venkat ♥ Duvvuri 8:34 PM, under | No comments

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.

Saturday, August 30, 2014

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

Posted by Venkat ♥ Duvvuri 11:49 PM, under | 1 comment


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

Saturday, May 10, 2014

DataStage Scenario on Finding Unique Distance from Source and Destination

Posted by Venkat ♥ Duvvuri 2:46 AM, under | 1 comment

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
---------------------
====================

Sunday, July 7, 2013

Performance Optimization technique to handle variable length data in Data Set

Posted by Venkat ♥ Duvvuri 4:02 PM, under | 4 comments

Thursday, February 14, 2013

How to replace a string in DataStage

Posted by Venkat ♥ Duvvuri 11:55 AM, under | 7 comments


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;

Saturday, January 14, 2012

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

Posted by Venkat ♥ Duvvuri 11:09 PM, under | 6 comments

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.

Table of contents

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

Sunday, June 5, 2011

The tsort operator

Posted by Venkat ♥ Duvvuri 7:07 AM, under | 6 comments

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.

Sunday, April 17, 2011

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

Posted by Venkat ♥ Duvvuri 1:12 AM, under | No comments


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

Wednesday, March 2, 2011

New features & changes in IBM InfoSphere Information Server 8.5

Posted by Venkat ♥ Duvvuri 7:09 PM, under | 9 comments

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.

Table of contents

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

Suite and product module changes

IBM InfoSphere Business Glossary

IBM InfoSphere DataStage

Tuesday, February 8, 2011

DataStage Parallel Processing

Posted by Venkat ♥ Duvvuri 8:30 AM, under | No comments

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.

Thursday, February 3, 2011

DataStage Best Practices

Posted by Venkat ♥ Duvvuri 8:57 PM, under | No comments

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.

Wednesday, January 26, 2011

DataStage OSH Script

Posted by Venkat ♥ Duvvuri 7:58 AM, under | No comments

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.

Saturday, January 15, 2011

DataStage Modules

Posted by Venkat ♥ Duvvuri 8:51 PM, under | 1 comment

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

Sunday, January 2, 2011

DataStage Execution Flow

Posted by Venkat ♥ Duvvuri 6:50 AM, under | No comments

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.

Tuesday, December 21, 2010

Data Transformations

Posted by Venkat ♥ Duvvuri 7:49 AM, under | No comments

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.

Tuesday, December 7, 2010

DataStage Stages and Jobs

Posted by Venkat ♥ Duvvuri 9:48 PM, under | 1 comment

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.

Tuesday, November 23, 2010

DataStage Functions

Posted by Venkat ♥ Duvvuri 8:45 AM, under | No comments

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.
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 232-1 - result (uint32)
Random Returns a random number between 0 232-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