DATASTAGE

Why Entire partition is used in LOOKUP stage ?

2013-03-22T12:02:00.000-07:00

Entire partition has all data across the nodes So while matching(in lookup) the records all data should be present across all nodes.

For lookup sorting is not required.so when we are not using entire partition then reference data splits into all nodes. Then each primary record need check with all nodes for matched reference record.Then we face performance issue.If we use entire in lookup then one primary record needs to look into 1 node is enough.if match found then that record goes to target otherwise it move to reject,drop etc(based on requirement)no need check in another node.In this case if we are running job in 4 nodes then at a time 4 records should process.
Note:Please remember we go for lookup only we have small reference data.If we go for big data it is performance issue(I/O work will increase here) and also some times job will abort.

FastTrack Makes Your DataStage Development Faster

2011-12-14T12:27:00.001-08:00

IBM introduced a tool called FastTrack that is a source to target mapping tool that is plugged straight into the Information Server and runs inside a browser.
The tool was introduced with the Information Server and is available in the 8.1 version.
As the name suggests IBM are using it to help in the analysis and design stage of a data integration project to do the source to target mapping and the definition of the transform rules. Since it is an Information Server product it runs against the Metadata Server and can share metadata with the other products and it can run inside a browser.
I have talked about it previously in New Product: IBM FastTrack for Source To Target Mapping and FastTrack Excel out of your DataStage project but now I have had the chance to see it in action on a Data Warehouse project. We have been using the tool for a few weeks now and we are impressed. It’s been easier to learn than other Information Server products and it manages to fit most of what you need inside frames on a single browse screen. Very few bugs and it has been in the hands of someone who doesn’t know a lot about DataStage and they have been able to complete mappings and generate DataStage jobs.
I hope to get some screenshots up in the weeks to come but here are some observations in how we have saved time with FastTrack:

FastTrack provides faster access to metadata. In an Excel/Word mapping environment you need to copy and paste your metadata from a tool that can show it into your mapping document. FastTrack can see metadata imported through any of the Information Server import methods such as DataStage plugin imports, the import bridges from Meta Integration Technologies Inc (MITI): ErWin, InfoSphere Data Architect, Cognos, Business Objects, Informatica etc. The imports via the database connectors from any other Information Server product such as the table definitions imported and profiled by Information Analyser. You can import an entire database in a few minutes and drag and drop it onto your various mappings.
FastTrack lets you map columns from XML and Cobol Copybook hierarchies to flat file relational database targets without any metadata massaging. In Excel you would spend days cutting and chopping XML and Cobol complex flat file definitions. With FastTrack you can access a definition imported through the DataStage Cobol or XML importers and map away.
FastTrack lets you do source to target mapping straight out of your modelling tool. You can import your model straight into the Metadata Server via a bridge and start mapping it. No mucking around with database DDLs and no need to get access to create database schemas. This can be handy in the early days of a project.
FastTrack has some great auto mapping functions. There is a discovery functions where you drag and drop the source or target table onto one side of the mapping and then use the discover function to find candidate matches for the other side – then choose the “Best Match” to take the first of the candidates. If you choose multiple columns you can Discover and Best Match all the columns in your table. It searches through for matching column names against the candidate tables.
FastTrack can map auto match on the business glossary terms attached to those columns. It is one of the few products in the Information Server that makes productive use of the Business Glossary to speed things up. Of course you need to create your Glossary and fill it with terms and map those terms to physical metadata first! FastTrack lets you add Glossary terms to physical columns as you map.
FastTrack lets you balance the mapping between business analysts and ETL developers. Both can use the tool – it’s an Excel style interface – but business analysts may be faster at mapping early in the project as they gather requirements and rules and ETL This can help avoid bottlenecks on your team if anyone can do mapping and can push the results straight into DataStage.
FastTrack creates DataStage jobs. These jobs have the source and target connectors already loaded and configured and stages such as lookup, join and transformer already built. It even lets you add Transformer derivations such as macros, job parameters and functions from a function list.
FastTrack handles DataStage naming conventions. FastTrack holds a set of rules for naming DataStage stages and links that you can configure to match your naming convention. Normal DataStage development means dragging and dropping stages and links onto a canvas and renaming every one. FastTrack does the naming for you.
FastTrack lets you add the links for joins and lookups. I don’t know if you’ve tried to map joins and lookups in Excel but it’s not pretty – you have room to map the extra columns but there is no easy way to show the key fields that join the two sources together. Generally you make a note of it under the mapping. In FastTrack you choose the join/lookup table, choose the key fields that do the join and bring in the extra columns for mapping to the output table and it generates the required stages in DataStage.
FastTrack shows progress of mapping tasks. Once you have created a mapping for all interfaces FastTrack will produce a report showing how much of each mapping has been finished saving you the time of tracking progress manually.

What FastTrack can do better.

Better bulk export and import functions – preferably XML and Excel. Excel for when we produce documentation. XML for when we want to back it up or move it between repositories. (Or export, run a search and replace to globally rename a transform value and import it again).
Global search and replace on transformation values, similar to the search and replace in the DataStage Transformer, for globally renaming things like function names and job parameter values.
More DataStage stages – it currently lets you configure settings for Lookup, Join, Connectors and Transformers. Would like to see Surrogate Key, Change Data Capture and Slowly Changing Dimension support – though it’s debatable whether those are business analyst functions for FastTrack or developer functions for DataStage. It would be cool to define Type 1 Type 2 and key fields for dimension table mapping.
Let you run Discover and Best Match on Business Glossary terms so you can find terms that suit the column name you are mapping.
Discover transformation rules as well as mappings … oh hang on, that’s in the next release!
Reverse engineer DataStage Server jobs so you can generate DataStage Enterprise jobs from a Server job mapping.
More flexible licensing. You buy licenses in packs of 10 – and that’s too many for a lot of customers!

IBM® InfoSphere™ Information Server 8.7

2011-11-30T12:55:00.001-08:00

IBM® InfoSphere™ Information Server 8.7 helps you take delivery of trusted information to the next level with a more comprehensive integration platform. New InfoSphere Information Server capabilities help strengthen collaboration between business and IT professionals to make it easier for strategic initiatives, such as business analytics, master data management, data warehousing, and application consolidation and migration projects, to utilize and act on trusted information.
IBM InfoSphere Information Server 8.7 helps you turn insight into actionable results by:

Capitalizing on productivity enhancements
Deliver better business results and reduce implementation time and cost by using new features that make sure your data integration platform is working for you. Take advantage of new operational intelligence capabilities, smart management of metadata and metadata imports, and new parallel debugging capabilities that enhance developer productivity when working with partitioned data.
Leveraging comprehensive information governance capabilities
Establish smarter, sustainable governance practices - including enriched data quality capabilities - that meet your business requirements and support consistent business outcomes. Gain more control when managing terms through a review and approval process, and increase your insight with holistic and personalized views of business terms, data models, assets, data flows and their relationships.
Harnessing the power of new and stronger integration
Promote collaboration and streamline the process of delivering enterprise business solutions through tight and deep metadata integration with key systems to take advantage of new opportunities.
Using big data support to successfully address the most challenging data volumes in your business
Organizations are increasingly adopting big data tooling to address the most challenging data volumes in their business. As part of this release, InfoSphere Information Server 8.7 will support big data and Hadoop, enabling you to directly access big data on a distributed file system

Examples of the significant new integration capabilities delivered as part of InfoSphere Information Server 8.7 include:

New Netezza connectivity is delivered as part of this release, via a Netezza Connector that provides native connectivity to the Netezza appliance and supports rapid loading of data to the appliance as well as balanced optimization, so you can easily determine and adjust where transformation will occur—in the source database using the high-performance InfoSphere DataStage engine, and/or in the target Netezza database(s).
A new configuration option for the database connectivity layer of Information Server creates an audit log for any database operation directly into InfoSphere Guardium
A new InfoSphere Change Data Capture stage, as well as expanded metadata capture with Data Replication/Change Data Delivery for end-to-end data lineage
A new Teradata Connector TMSM and dual load support to help Teradata users better manage disaster recovery
Advanced capabilities to identify, cleanse, and manage metadata for SAP projects help SAP project managers meet their go-live dates

Conversion error calling conversion routine date from string data may have been lost

2011-11-18T11:46:00.001-08:00

I have a file which has five columms. A field "pdate" is char data type which has date values (format ddmmyy eg 011008). This date value is need to be converted before loading into a target table in oracle 10g.
I have used the converion function StringToDate(ln_TTS_DelvyDate_in.pu_date,"%dd-%mm-%yy") to convert the char value into date.
.

The job is: Seq file--->TRANSFORMER---->SeqFile

When I'm executing this job it gives the following error:

Tr_Split,1: Conversion error calling conversion routine date_from_string data may have been lost

In the output file
The "pudate" has '*******' being propagated.

The input date column will not have null values.

Input Data- Char data type

011008

Output Data - date data type

******
-------------------------------------------




Solution::::

The conversion function should not have hyphens as your data doesn't have it. StringToDate(ln_TTS_DelvyDate_in.pu_date,"%dd%mm%yy") should work. or This warning will also occurs if data itself contains junk data that does not support the required format even though conversion function StringToDate(ln_TTS_DelvyDate_in.pu_date,"%dd%mm%yy") is present. Best case is replace the source file which does not have junk data.

TNS entry error when trying to connect to a database using DS in order to import the table definition?

2011-11-18T09:17:00.001-08:00

This usually indicates that either the DataStage userid running the job does not have read access to the Oracle run-time client files, or that the environment variable ORACLE_HOME is not defined.

ORA-12154: TNS:could not resolve service name
The above error indicate that the connection identifier or service name specified in the DataStage job (or in the ORACLE_SID environment variable or ODBC definition) was not known to either the Oracle client or the Oracle server. The connection/service identifiers known to the Oracle run-time client are defined in the tnsnames.ora file, $ORACLE_HOME/network/admin/tnsnames.ora
Verify that the identifier specified for the failing Oracle connection has been defined in tnsnames.ora. If it is correctly defined, then next verify that the ORACLE_HOME environment variable is correctly defined, and that tnsnames.ora file has correct read permissions.
If the above items are configured correctly, also check the listener.log on Oracle server to confirm that the service id (or the database it maps to) are known to the Oracle server.
Setting up environment variables required to use Oracle run-time client
The Oracle client requires that the following environment variables be defined. These should be set in the .dsenv file in the DataStage DSEngine directory.

ORACLE_HOME=/home/oracle
LIBPATH=$LIBPATH:$ORACLE_HOME/lib:
PATH=$PATH:$ORACLE_HOME/bin

Change the path defined for ORACLE_HOME to the correct path for your system. ORACLE_HOME should be set to the absolute path to the home Oracle directory which is the directory level directly above the lib and bin directories.

Please also note that the name of the library path environment variable varies with different operating systems:

AIX - use LIBPATH
Solaris - use LD_LIBRARY_PATH
HP-UX - use SHLIB_PATH
Linux - use LD_LIBRARY_PATH

Parallel job: Retrieve sql codes on a failed upsert

2011-11-01T20:00:00.001-07:00

Parallel job: Retrieve sql codes on a failed upsert

Introduction

When an enterprise database stage such as DB2 or Oracle is set to upsert it is possible to create a reject link to trap rows that fail any update or insert statements. By default this reject link holds just the columns written to the stage, they do not show any columns indicating why the row was rejected and often no warnings or error messages appear in the job log.

Steps

There is an undocumented feature in the DB2 and Oracle enterprise stage where a reject link out of the stage will carry two new fields, sqlstate and sqlcode. These hold the return codes from the RDBMS engine for failed upsert transactions. The fields are called sqlstate and sqlcode.
To see these values add a peek to your reject link, the sqlstate and sqlcode should turn up for each rejected row in the job log. To trap these values add a copy stage to your reject link, add sqlstate and sqlcode to the list of output columns, on the output columns tab check the "Runtime column propagation" check box, this will turn your two new columns from invalid red columns to black and let your job compile. If you do not see this check box use the Administrator tool to turn on column propagation for your project.
When the job runs and a RDBMS reject occurs the record is sent down the reject link, two new columns are propagated down that link and are defined by the copy stage and can then be written out to an error handling table or file.
If you do not want to turn on column propagation for your project you can still define the two new columns with a Modify stage by creating them in two specifications. sqlcode=sqlcode and sqlstate=sqlstate. Despite column propagation being turned off the Modify stage will still find the two columns on the input link and use the specification to add them to the output schema.

Examples

Oracle By default, oraupsert produces no output data set. By using the -reject option, you can specify an optional output data set containing the records that fail to be inserted or updated. It’s syntax is: -reject filename For a failed insert record, these sqlcodes cause the record to be transferred to your reject dataset: -1400: cannot insert NULL -1401: inserted value too large for column -1438: value larger than specified precision allows for this column -1480: trailing null missing from string bind value
For a failed update record, these sqlcodes cause the record to be transferred to your reject dataset: -1: unique constraint violation -1401: inserted value too large for column -1403: update record not found -1407: cannot update to null -1438: value larger than specified precision allows for this column -1480: trailing null missing from string bind value An insert record that fails because of a unique constraint violation (sqlcode of -1) is used for updating.
DB2 When you specify the -reject option, any update record that receives a status of SQL_PARAM_ERROR is written to your reject data set. It’s syntax is: -reject filename

Conclusion

Always place a reject link on a Database stage that performs an upsert. There is no other way to trap rows that fail that upsert statement.
For other database actions such as load or import a different method of trapping rejects and messages is required.

AutoSys Cheatsheet

2011-10-21T12:56:00.000-07:00

AutoSys: UNIX

Cd to the "autouser" ($AUTOUSER) directory and "." (or source) the "ksh" file. Ex: ". ./autosys.ksh.machine" After installing AutoSys, first make sure that the DB is up and running. Check the installation by running the command chk_auto_up to verify connection to the DB and event processor.

Enter the KEYS through "gatekeeper", add keys

Run the "autosys_secure" command to set the AutoSys Edit and Exec Super users (and also to enter NT users/passwords)

Start the Event Processor by running the command "eventor"

Shutdown AutoSys: "sendevent -E STOP_DEMON"

To start the AutoSys GUI set your DISPLAY and run the command "autosc &".

NT: Start AutoSys from start->programs->AutoSys-> administrator ->Graphical User Interface ->Command Prompt

Command Line Commands:

1. gatekeeper: Allows you to enter the License Keys which allow you to run AutoSys.

2. eventor [-M machine_name] : Starts the event processor.

3. autorep -J [ALL | Job_name] [-q] [> file_name], -d (detail), -r (run number), -o (override), jil < file_na -G (global var report), -M -q for machine definitions.
Ex: autorep -J job_name -d
autorep -J job_name -d
autorep -J job_name -q > file_name queries the DB & save job Dfn. Into a file
vi file_name
When you want a report of a box use the -L0 option
Autorep -J job_name -l1 report on the job for the day -1 (prev day)

4. sendevent -E STARTJOB -J job_name, sendevent -E FORCE_STARTJOB -J job_name, [JOB_ON_ICE, JOB_OFF_ICE, JOB_ON_HOLD, JOB_OFF_HOLD, SET_GLOBAL, STOP_DEMON. . . .]
sendevent -E STOP_DEMON - to stop AutoSys
(ex: sendevent -E SET_GLOBAL -G "var_name=/home/mydir" to set a var)
(ex: sendevent -E SET_GLOBAL -G "var_name=DELETE" to delete a var)]

5. chk_auto_up: checks to see if event processor and the DB are both up.

6. autoping -m machine: verify that both client & server are correctly configured.

7. cron2jil -f cronfile [-d outdir] [-I incl_file] [-m machine] [-p prefix]

8. jil
To insert a job directly into the DB
insert_job: job.id job_type: c
machine: machine_name
command: echo testing jil
[go | ;] (depending on the DB you are using)
Template example:

/* ----------------- template ----------------- */

insert_job: template job_type: c
box_name: box1
command: ls -l
machine: localhost
owner: lyota01@TANT-A01
permission: gx,ge,wx,we,mx,me
date_conditions: 1
days_of_week: all
start_times: "15:00, 14:00"
run_window: "14:00 - 6:00"
condition: s (job1)
description: "description field"
n_retrys: 12
term_run_time: 60
box_terminator: 1
job_terminator: 1
std_out_file: /tmp/std_out
std_err_file: /tmp/std_err
min_run_alarm: 5
max_run_alarm: 10
alarm_if_fail: 1
max_exit_success: 2
chk_files: /tmp 2000
profile: /tmp/.profile
job_load: 25
priority: 1
auto_delete: 12

9. autosyslog -e: same as tail -f autosys_log_file. This command must be run from the machine where the server resides if used with the -e option. Else it can be used with the -J option to see that job's run log.

10. job_depends: -[c|d|t] -J jobname [-F "mm/dd/yy time"] [-T "mm/dd/yy time"] (Note: It will only print out the first occurrence found)

11. monbro -n monitor_name: Allows you to run from command line monitor/browser programs previously created using the monitor/browser GUI.exec superuser: AUTOSYS superuser

12. autocal_asc full_cal_name: prints, adds & deletes custom calendar definitions.

13. autostatus: Reports the current status of a specific job, or the value of an AutoSys global variable. Ex: autostatus -J job_name, -S instance

14. autotimezone -l : Allows additions, deletions, and queries to the timezones table (-l provides list).

15. autotrack: Tracks & report changes to the AutoSys DB. Ex: autotrack -l 2 (level 2) [sets the tracking level] autotrack -U sys -v (user sys: verbose) To start using the autotrack utility type: autotrack -u to set tracking level 1 or 2. By default it is set to 0. Autotrack -l will list the current tracking level. Options -[J, U, m, F, T, and t] are to request reporting on a specific Job, User, machine, time window (-F -T), and event type (t). Type is used in conjunction w/other parameters. autotrack w/no arguments retrieves information an all events omitting detail. -v option is for verbose.

16. autosys_secure: to change edit, exec superusers, change DB passwd, change remote authentication method.

17. chase [-A|E]: Makes sure that jobs claiming to be running in the client machine are running. The "-E" option restarts the job.

18. archive_events: to archive events in the DB which are older than x days to prev DB from becoming full.

19. clean_files: Deletes old remote agent log files. It does it by searching the DB for all machines which have had jobs started on them.

20. autostatad: to get the status of a PeopleSoft job. You can define one of the user definable buttons to view PeopleSoft job: Autocons*userButton1Label: Adapter Status

User definable buttons: There are user definable buttons in the operator's console.

How to configure:

Autocons*userButton1Command: /autosys/bin/autostatad -J $JOB -g & (which allows you to have a command button on the operator's console.)

Dependencies:

success (job) and s(job_b)
failure(job_a) or f (job_b)
notrunning (job)
terminated(job)
exitcode(job) > 5 and exitcode(job_b) != 10
value(global_name)=100
done(job)

Hostscape: Schedule a job to run every x minutes & then go into forecasting. Make that job fail.

· Solid black line: Hostscape can communicate with the remote agent in the client machine.

· Solid red line: Hostscape can't communicate with the remote agent but it can communicate with the internet daemon (inetd) running on that machine..

· Dashed red line: Hostscape can't communicate with the client machine at all. Client is probably down.

· Accessing a variable name: $$GLOBAL_VAR_NAME (unless used in dependency condition with a job definition. If used in the "command" field, you must use the $$)

Tunable Parameters:

· $AUTOUSER/config.ACE

· $AUTOUSER/autosys.ksh.xxx

· /etc/auto.profile

· /etc/inetd.conf

· /etc/services

Notify.Ace: The alarms to notify on are:
(There is an example in $AUTOSYS/install/data/Notify.Ace).

· DB_ROLLOVER

· DB_PROBLEM

· EP_HIGH_AVAILABILITY

· EP_ROLLOVER

· EP_SHUTDOWN

Where to go to find the Errors:

· $AUTOUSER/out/event_demon.$AUTOSERV
($AUTOUSER/out/event_demon.ACE)

· Output from the job definition output & error files

· /tmp files created for job_run at client machine

· $AUTOSYS/out/DBMaint.out for DB problems

· $SYBASE/install/errorlog_$DSQUERY when event server will not start.

· NT: AutoNuTc\lib/X11\app-defaults\xpert

AutoSys Maintenance: DBMaint @$AUTOSYS/bin

Once a day the Database goes into a maintenance cycle. Every day at 3:00am it runs a program called DBMaint. This is user configurable. The program runs DBstatistics which is found in $AUTOSYS/bin.

app-defaults file: /usr/openwin/lib/app-defaults directory. Autocons, Xpert, etc.. ( or: /usr/lib/X11/app-defaults, /autosys/bin/X11/app-defaults)

Environment file: /etc./auto.profile
C programs: $AUTOSYS/code
Where to change AutoSys screen fonts: /usr/openwin/lib/app-defaults
Where to look for troubleshooting: Chapter 15
Summary of commands: Appendix C

$AUTO_JOB_NAME: when naming a file dynamically using as prefix AutoSys's job name.
$AUTORUN: unique identifier for the run of that job
$AUTOPID: unique identifier for that job's run number (PID)
$JOID: DB identifier for a job. To extract from the DB: select joid from job where job_name=" "

Creating a Virtual Machine:

insert_machine: virtual
type: v /* default, not required */
machine: real_1
machine: real_2
max_load: 100
factor: 0.5 /* used to describe the relative processing power of a machine. Usually between 0.0-1.0*/
machine: real_2
max_load: 60 /* this is designed to limit the loading of a machine */

Load Balancing, Queuing, priorities:

insert_job: test_load
machine: localhost
command: echo "Test load balancing"
job_load: 50
priority: 1 /* this only affects queues */

Note: For 5.0 we will be using information from ServerVision's towards our load balancer which is composed of 26 categories such as i/o usage, disk usage, CPU usage, etc.

Testing:
zql
zql -U autosys -P autosys

NOTES:

When a job is stuck in the starting condition this means that the event processor communicated with the remote agent and passed all the information the remote agent ran the job but was not able to communicate to the DB. Once testing is done with AutoSys one should change the default refresh interval for AutoSys. This is so there is less querying to the DB. When AutoSys goes from dual mode to single mode, always run the autobcp command before bringing AutoSys back to dual mode/High Availability. Default behavior for stdout is to always appends. If you want to overwrite the file enter the following, no spaces: ">file.out"

Box Logic

Use boxes to group jobs with like scheduling parameters, not as means of grouping jobs organizationally. For example, if you have a number of jobs that run daily at 1:00 a.m., you could put all these jobs in a box and assigning a daily start condition to the box. However, a variety of account processing jobs with diverse starting conditions should not be grouped in the same box.

Default Box Job Behavior

Some important rules to remember about boxes are:

Jobs run only once per box execution.
Jobs in a box will start only if the box itself is running.
As long as any job in a box is running, the box remains in RUNNING state; the box cannot complete until all jobs have run.
By default, a box will return a status of SUCCESS only when all the jobs in the box have run and the status of all the jobs is "success." Default SUCCESS is described in Default Box Success and Box Failure on page 5-13.
By default, a box will return a status of FAILURE only when all jobs in the box have run and the status of one or more of the jobs is "failure." Default FAILURE is described in Default Box Success and Box Failure on page 5-13.
Unless otherwise specified, a box will run indefinitely until it reaches a status of SUCCESS or FAILURE. For a description of how to override this behavior, see Box Job Attributes and Terminators on page 5-6.
Changing the state of a box to INACTIVE (via the sendevent command) changes the state of all the jobs in the box to INACTIVE.

When you Should Not Use a Box

The fact that all jobs in a box change status when a box starts running has lead some to use boxes to implement "job cycle" behavior. Be aware that placing jobs in a box to achieve this end may bring with it undesired behavior due to the nature of boxes.

Avoid the temptation to put jobs in a box as a short cut for performing events (such as ON_ICE or ON_HOLD) on a large number of jobs at once. You will most likely find that the default behavior of boxes inhibits the expected execution of the jobs you placed in the box.

Likewise, you should not place jobs in a box solely because you want to run reports on all of them. When you run autorep on a box, you will get a report on the box and all the jobs in the box (unless you use the -L0 option). In addition, if you use wildcarding when specifying a job name, you could get duplicate entries in your report. For example, suppose you have a box named "acnt_box" containing three jobs named "acnt_job1", "acnt_job2", and "daily_rep". If you specify acnt% as the job name for the autorep report, the report will have an entry for the box "acnt_box" and an entry for each job in the box. Then autorep will continue searching for all job names matching the wildcard characters and, thus, will list "acnt_job1" and "acnt_job2" a second time.

What Happens when a Box Runs

As soon as a box starts running, all the jobs in the box (including sub-boxes) change to status ACTIVATED, meaning they are eligible to run. (Because of this, jobs in boxes do not retain their statuses from previous box cycles.) Then each job is analyzed for additional starting conditions. All jobs with no additional starting conditions are started, without any implied ordering or prioritizing. Jobs with additional starting conditions remain in the ACTIVATED state until those additional dependencies have been met. The box remains in the RUNNING state as long as there are activated or running jobs in the box.

If a box is terminated before a job in it was able to start, the status of that job will change directly from ACTIVATED to INACTIVE.

Note o Jobs in a box cannot start unless the box is running. However, once the job starts running, it will continue to run even if the box is later stopped for some reason.

Time Conditions in a Box

Each job in a box will run only once per box execution. Therefore, you should not define more than one time attribute for any job in a box because the job will only run the first time. If you want to put a job in a box, but you also want it to run more than once, you must assign multiple start time conditions to the box itself, and define no time conditions for the job. Remember also that the box must be running before the job can start. Do not assign a start time for a job in a box if the box will not be running at that time. If you do, the next time the box starts the job will start immediately.

The following example illustrates a scenario that would not work properly if placed in a box.

"job_a" is defined to run repeatedly until it succeeds. "job_report" has one starting condition-the success of "job_a".

How Job Status Changes Affect Box Status

If a box that is not running contains a job that changes status, as a result of a FORCE_STARTJOB or CHANGE_STATUS event, the new job status could change the status of its container box. A change of status of the box could trigger the start of downstream jobs that are dependent on the box.

If a box contained only one job, and the job changed status, the box status would change.

2011-09-22T23:38:00.001-07:00

AUTOSYS

The DataStage 8.5 Job Migration Assistant Makes Upgrading, Testing and Deployment Easier

2011-09-22T22:57:00.000-07:00

IBM have released a tool that makes product upgrades and environment management and job deployment and regression testing easier.
This is more than a Migration Assistant, it is useful for migrating between DataStage versions but it is also useful for migrating DataStage jobs from a development environment to testing and production. It is useful for managing duplicate versions of jobs, executing test plans or job documentation.

The DataStage Migration Assistant can be downloaded here and according to the support website:

The Job Migration Test Assistant tool is a web-based application which assists in migration of DataStage projects from previous releases into the 8.5 release. The Job Migration Test Assistant can compare the status of DataStage jobs or sequences between the older version and the newer version, and perform difference analysis including job level or table schema level checks. Reports can be genrated based on anomalies in the data between the versions.

It is downloaded through the IBM Fix Central website which means anyone can access it. There is a catch – the product requires a DB2 repository and it comes with a JMTA_DDL_DB2.sql script. Information Server can store its repository on DB2, SQL Server or Oracle so there will be some sites out there without DB2. You can use the free express version of DB2 or the version that comes bundled with Information Server.
It comes with a couple prerequisites – two interim fixes for Information Server 8.5.

Migration Assistant for Upgrades

Here is a screenshot of the DataStage job overview showing jobs in the new environment of various statuses – Failed, Not Completed, Compiled, Ran Successfully and Ran with Warnings. It has another graph for the new environment – Missing and New.

Migration Assistant for Environment Management

There is a lot more to the product than just upgrading DataStage. The Migration Assistant can help to migrate between environments on the same version – for example synchronising your development, test and production environments. Finding duplicate jobs between projects and merging them.

There is a tree for comparing objects between environments and comparing DataStage jobs:

Migration Assistant for Testing

The Migration Assistant has some functions to help unit test an environment upgrade. These same test functions can help for any type of DataStage testing – you can keep using the Migration Assistant as part of your standard test plan. You can compare a release of DataStage jobs with the previous release by comparing a UAT test environment to a production environment, or by comparing a test environment to a test baseline environment. With regression testing the baseline you are comparing against needs to be similar to the new set of jobs being tested.

It provides a regression testing check – the ability to find any link in any job that has a row count that is different to the regression test baseline.

Another regression testing feature is the log compare – this will compare all the logs for one environment against the logs in another environment to try and find something that is different in a DataStage job – such as a new warning, or an abort or unusual information messages. There is currently no other feature in any Information Server product that lets you compare DataStage log messages.

2011-09-22T21:45:00.000-07:00

IBM InfoSphere DataStage Data Flow and Job Design

2011-09-22T21:28:00.000-07:00

Datastage Installation

DataStage 8.1 to DataStage 8.5

2011-09-16T06:46:00.000-07:00

1. DataStage Designer performance improvement By changing the Metadata algorythm, copy/delete/save jobs got faster about 30-40%.
2. Parallel Engine Performance and Resource improvements Resource usage is about 5% smaller than 8.1, for T-Sort, Windows desktop heap size has been decreased 94%.
3. Transformer enhancements Key break support
LastRowInGroup() function is added. This will return true for the last record of the group.
LastRow() will return the last record of input.
Output looping :: Allows multiple output records to be created per single input record.
Input looping    :: Allows aggregation of input records so that aggregated data can be included with the original input data. ( like adding average column to the original input is now possible. ( 2 pass.... calculation. )
New Null handling This is pretty complicate and need more verification by myself to explain clearly. But this is the description I got.
Null values can now be included in any expression.
        -> Null values no longer need to be explicitly handled.
            A null value in an expression will return a null value result. As long as the target column is nullable, records will not be dropped. Stage variables are now always nullable.

APT_TRANSFORM_COMPILE_OLD_NULL_HANDLING is prepared to support backward compatibility.

New Transformer Functions
Create/offset a time, date or timestamp from component arguments
DateFromComponents(int32 years, int32 months, int32 dayofmonth)
DateOffsetByComponents(date basedate, int32 yearoffset, int32 monthoffset, int32 dayoffset) DateOffsetByDays(date basedate, int32 offset)
TimeFromComponents(int32 hours, int32 minutes, int32 seconds, int32 microseconds) TimeOffsetByComponents(time basetime, int32 houroffset, int32 minuteoffset, dfloat secondoffset) TimeOffsetBySeconds(time basetime, dfloat secondoffset)
TimestampOffsetByComponents(timestamp basetimestamp, int32 yearoffset, int32 monthoffset, int32 dayoffset, int32 houroffset, int32 minuteoffset, dfloat secondoffset)
TimestampOffsetBySeconds(timestamp basetimestamp, dfloat secondoffset)

Various packed decimal conversions
DecimalToDate(decimal basedecimal [,string format] )
DecimalToTime(decimal basedecimal [,string format] )
DecimalToTimestamp(decimal basedecimal [,string format] )
DateToDecimal(date basedate [,string format] )
TimeToDecimal(time basetime [,string format] )
TimestampToDecimal(timestamp basetimestamp [,string format] )

4. DataStage Function enhancements New Client \ Domain Compatibility Check Before/after routines now mask encrypted params Copy project permissions from existing project when creating new project Environment variable enhancements: creation during import Add PX Stage Reset Support Enhancement to Parallel Data Set Stage Multiple Null Field Values on Import Enhancements to improve Multi-Client Manager support
5. DataStage Serviceability enhancements New Audit Tracing Enhanced Exception Dialog ISA Lite Enhancements for DataStage Enhanced Project Creation Failure Details
6. ParallelPivot - Adding Vertical Pivoting
7. CVS (Code Version Control Integration) Information Server Manager was created on Eclipse from 8.1 Now the CVS or Subversion plugins to Eclipse are available for DataStage components.

Ten Reasons Why You Need DataStage 8.5

2011-09-16T06:22:00.000-07:00

Information Server 8.5 came out a couple weeks ago and is currently available on IBM Passport Advantage for existing customers and from IBM PartnerWorld for IM partners. The XML pack described below is available as a separate download from the IBM Fix Central website.

This is a list of the ten best things in DataStage 8.5. Most of these are improvements in DataStage Parallel Jobs only while a couple of them will help Server Job customers as well.

1. It’s Faster

Faster, faster, faster. A lot of tasks in DataStage 8.5 are at least 40% faster than 8.1 such as starting DataStage, opening a job, running a Parallel job and runtime performance have all improved.

2. It' is now an XML ETL Tool

Previous versions of DataStage were mediocre at processing XML. DataStage 8.5 is a great XML processing tool. It can open, understand and store XML schema files. This pack in New Hierarchical Transformer makes DataStage great aXML Tool and if you have XML files without schemas you can follow a tip at the DataStage Real Time blog: The new XMLPack in 8.5….generating xsd’s….

The new XML read and transform stages are much better at reading large and complex XML files and processing them in parallel:

3. Transformer Looping

The best Transformer yet. The DataStage 8.5 parallel transformer is the best version yet thanks to new functions for looping inside a transformer and performing transformations across a grouping of records.

With looping inside a Transformer you can output multiple rows for each input row. In this example a record has a company name and four revenue sales figures for four regions – the loop will go through each column and output a row for each value if it is populated:

4. Transformer Remembering

DataStage 8.5 Transformer has Remembering and key change detection which is something that ETL experts have been manually coding into DataStage for years using some well known workarounds. A key change in a DataStage job involves a group of records with a shared key where you want to process that group as a type of array inside the overall recordset.

I am going to make a longer post about that later but there are two new cache objects inside a Transformer – SaveInputRecord() and GetSavedInputRecord(0 where you can save a record and retrieve it later on to compare two or more records inside a Transformer.

There are new system variables for looping and key change detection - @ITERATION, LastRow() indicates the last row in a job, LastTwoInGroup(InputColumn) indicates a particular column value will change in the next record.

Here is an aggregation example where rows are looped through and an aggregate row is written out when the key changes:

4. Easy to Install

Easier to install and more robust. DataStage 8.5 has the best installer of any version of DataStage ever. Mind you – I jumped aboard the DataStage train in version 3.6 so I cannot vouch for earlier installers but 8.5 has the best wizard, the best pre-requisite checking and the best recovery. It also has the IBM Support Assistant packs for Information Server that make debugging and reporting of PMRs to IBM much easier. There is also a Guide to Migrating to InfoSphere Information Serve 8.5 that explains how to migrate from most earlier versions.

See my earlier blog post Why Information Server 8.5 is Easier to Install than Information Server 8.1.

Patch Merge – that’s right, patch merge. The new installer has the ability to merge patches and fixes into the install for easier management of patches and fixes.

5. Check In and Check Out Jobs

Check in and Check out version control. DataStage 8.5 Manager comes with direct access to the source control functions of CVS and Rational ClearCase in an Eclipse workspace. You can send artefacts to the source control system and replace a DataStage component from out of the source control system.

DataStage 8.5 comes with out of the box menu integration with CVS and Rational ClearCase but for other source control systems you need to use the Eclipse source control plugins.

6. High Availability Easier than ever

High Availability – the version 8.5 installation guide has over thirty pages on Information Server topologies including a bunch of high availability scenarios across all tiers of the product. On top of that there are new chapters for the high availability of the metadata repository, the services layer and the DataStage engine.

Horizontal and vertical scaling and load balancing.
Cluster support for WebSphere Application Server.

Cluster support for XMETA repository: DB2 HADR/Cluster or Oracle RAC.
Improved failover support on the engine.

7. New Information Architecture Diagramming Tool

InfoSphere Blueprint Direct – DataStage 8.5 comes with a free new product for creating diagrams of an information architecture and linking elements in the diagram directly into DataStage jobs and Metadata Workbench metadata. Solution Architects can draw a diagram of a data integration solution including sources, Warehouses and repositories.

8. Vertical Pivot

There are people out there who have been campaigning for vertical pivot for a long time – you know who you are! It is now available and it can pivot multiple input rows with a common key into output rows with multiple columns. Key based groups, columnar pivot and aggregate functions.

You can also do this type of vertical pivoting in the new Transformer using the column change detection and row cache – but the Vertical pivot stage makes it easier as a specialised stage.

9. Z/OS File Stage

Makes it easier to process complex flat files by providing native support for mainframe files. Use it for VSAM files – KSDS, ESDS, RRDS. Sequential QSAM, BDAM, BSAM. Fixed and variable length records. Single or multiple record type files.

10. Balanced Optimizer Comes Home

In DataStage 8.5 the Balanced Optimizer has been merged into the Designer and it has a number of usability improvements that turns DataStage into a better ETLT or ELT option. Balanced Optimizer looks at a normal DataStage job and comes up with a version that pushes some of the steps down onto a source or target database engine. IE it balances the load across the ETL engine and the database engines.

Version 8.5 has improved logging, improved impact analysis support and easier management of optimised versions of jobs in terms of creating, deleting, renaming, moving, compiling and deploying them.

When to choose Server or Parallel Data stage job

2011-08-29T22:12:00.000-07:00

The choice of server or parallel depends upon time to implement, functionality and cost.
When we have lots of functionality to implement for lower volume and hardware is less and ease of implementation we can go for Server jobs.
Parallel jobs are costly due to high scale of hardware , difficult to implement, extreme processing capabilities for absurd volumes with vast array of operators for high-performance manipulation.
When the data volume is less it is better to go for Server job as parallel jobs can have a longer start up time.
When data volume is high, it is better to choose parallel job than server job. Parallel job will be a lot faster than server job even if it runs on single node. The obvious incentive for going parallel is data volume. Parallel jobs can remove bottlenecks and run across multiple nodes in a cluster for almost unlimited scalability. At this point parallel jobs become the faster and easier option. A parallel sort stage is lot faster than server stage. A Transformer stage in parallel job with the same transformations in server job is faster. Even on one node with a compiled transformer stage, the parallel version was three times faster. On 1 node configuration that does not have a lot of parallel processing also we can still get big performance improvements from an Enterprise Edition job. The improvements will be multiplied 10 or more than that if we work on 2CPU machines and two nodes in most stages.
Parallel jobs take advantage of both pipeline parallelism and partitioning parallelism.
We can improve the performance of server job by enabling inter process row buffering. This helps stages to exchange data as soon as it is available in the link. IPC stage also helps passive stage to read data from another as soon as data is available. In other words, stages do not have to wait for the entire set of records to be read first and then transferred to the next stage. Link partitioner and link collector stages can be used to achieve a certain degree of partitioning parallelism.
Look up with sequential file is possible in parallel jobs and not possible in server jobs.

Over command

2011-08-01T08:22:00.000-07:00

A window function in SQL:2003 is an aggregate function applied to a partition of the result set.
For example, sum(population) OVER( PARTITION BY city )
calculates the sum of the populations of all rows having the same city value as the current row.
Partitions are specified using the OVER clause which modifies the aggregate. Syntax: :: =
OVER ( [ PARTITION BY , ... ]
[ ORDER BY ] )
The OVER clause can partition and order the result set. Ordering is used for order-relative functions such as row_number.

Extending Infosphere Datastage

2011-06-13T13:52:00.000-07:00

Extending Infosphere Datastage
One of the great features of Datastage Parallel engine is a possibility of extending it by creating custom components. The following article describes what are the options to build a DataStage PX stage that handles special processing needs not supplied with the native stages.Many people may ask why build a custom stage?Basically the main reason is to implement a complex business logic, not easily accomplished using standard Datastage stages or reuse of existing C, C++, Java, COBOL transformations.
Datastage jobs can be extended using the following options:
Wrappers
Buildops
Custom Stages (CustomOPS)
Both BuildOPS and CustomOPS are primarly C++ code.
Wrappers
Wrappers are good if you cannot or do not want to modify the application and performance is not critical. Basically an OS-level legacy executable can be wrapped and turned into a Datastage PX stage (capable of parallel execution within the framework). Examples of commands that can be wrapped: a Binary file, Unix command (ls, grep, etc), Shell script. There are a few conditions that the legacy executable needs to fulfill:
amenable to data-partition parallelism
no dependencies between rows
pipe-safe
can read rows sequentially
no random access to data
Wrappers are treated by Infosphere Datastage as a black box, the application has no knowledge of contents, has no means of managing anything that occurs inside the wrapper, it only knows how to export data to and import data from the wrapper. So it is a user's task to know at design time the intended behavior of the wrapper and its schema interface. Example: wrapping ls Unix command:Ls /dwdev/sourcedata would yield a list of files and subdirectories. The wrapper is thus comprised of the command and a parameter that contains a disk location.
Buildops
Buildops are good if users need custom coding but do not need dynamic (runtime-based) input and output interfaces. Buildop provides a simple means of extending beyond the functionality provided by PX, but does not use an existing executable (like the wrapper).
Reasons to use Buildop include:
Speed / Performance
Complex business logic that cannot be easily represented using existing stages, like advanced lookups across a range of values, custom surrogate key generation, calculating olling aggregates
Build once and reusable everywhere within project, no shared container necessary
Can combine functionality from different stages into one
The Datastage interface called buildop automatically performs the tedious, error-prone tasks rquired to compile the program, such as invoke needed header files and build the necessary 'plumbing' for a correct and efficient parallel execution. The code needs to be Ansi C/C++ compliant. If code does not compile outside of Datastage, it will not compile within Datastage PX either. The good thing is that BuildOPS are compiled by DataStage itself.
Custom stage
Custom (C++ coding using framework API) is used when there is a need for custom coding and for dynamic input and output interfaces.
The main reasons for building a custom stage:
Add PX operator that is not already in DataStage
Build an Operator and add to DataStage palette
Use Datastage API
Use Custom Stage to add new operator to PX canvas
CustomOPS vs BuildOPS
While Datastage compiles BuildOPS, Datastage developer is responsible for compiling CustomOPS.
CustomOPS can be vey flexible, BuildOPS are generally used for a specific purpose. CustomOPS are generally used like any normal stages.
BuildOPS are purely written in C++ , while Custom Operators can use anything as long as landing functions are defined in C++ (thus languages like Pro*C, Fortran, Pascal, etc. can be used).
BuildOPS are limited to a specific table definition for input and output. Custom Operators have a great flexibility.

DataStage Parallel routines made really easy

2011-06-12T22:05:00.000-07:00

there are 3 kind of routines is there in Datastage.

1.server routines which will used in server jobs.

these routines will write in BASIC Language

2.parlell routines which will used in parlell jobs

These routines will write in C/C++ Language

3.mainframe routines which will used in mainframe jobs

http://blogs.ittoolbox.com/dw/soa/archives/datastage-parallel-routines-made-really-easy-20926

What is Phantom error in the datastage. How to overcome this error.

2011-06-11T17:52:00.000-07:00

Phantom Process is Orphened process.
Some times some processes will still running in the server even though you kill the actual process.

Some threads will be keep running without any source process they are called Phantom Process.

if you see the Directory called PH this folder caprures the log of phantom process.

If you are getting this error but please check the active process in the datastage server and kill them if they are running since very long time

In the transformer stage the order of the output links should be such that the dependant links should come after independent links. otherwise job will abort with message phantom error

How many places u can call Routines?

2011-06-11T17:28:00.000-07:00

Routines can be called at the following places:
a) In the job properties There is an option to call the Before and After job subroutines.
b) In the job sequence there is an activity called "Routine Activity".From there also the routines could be called.
c) In the derivation part of theTransformer of a parallel job "parallel routines"can be called.
d) In the derivation part of theTransformer of a server job "server routines"can be called.
f)In the server job stages also before and after job subroutines can be called.
g)We have called routines from user variable activity of a sequence as well.(Here basically transforms are called which return a value to the variable of the user variable activity after their execution.)

how to create batches in Datastage from command prompt

2011-06-11T17:24:00.000-07:00

From command prompt batches can be created in following way :
a) create a batch file say RunbatchJobs.bat
b) Open this file in notepad.
c) Now write the command "dsjob" with proper syntax for each job you want to run.
d) in there are four jobs to be run in a batch use dsjob command 4 times with different job names on each line.
e) Save the file and close it.
f) Next time whenever you want to run the jobs just click on the batch file "RunbatchJobs.bat".All jobs will run one by one by the batch file.

Traditionally batch programs are created in the following way :
A Batch Program is used to run a batch of jobs by writing the Server routine code in the job control section.To generate a batch program do following:
a) Open datasatge director.
b) Go to Tools->Batch->New.
c) A new window will open with the "Job Control" tab selected.
d)Write the routine code and save it.You may run multiple jobs in batch by making use of this.
Note : The similar thing can be achieved more easily by making use of Datsatage Sequences.

Batch programe is the programe it's generate run time to maintain by the datastage it self but u can easy to change own the basis of your requirement (Extraction, Transformation,Loading) .Batch programe are generate depands your job nature either simple job or sequencer job,You can see this programe on job controll option.

Basic shell scripting questions

2011-06-11T14:38:00.000-07:00

How do you find out what’s your shell? - echo $SHELL
What’s the command to find out today’s date? - date
What’s the command to find out users on the system? - who
How do you find out the current directory you’re in? - pwd
How do you remove a file? - rm
How do you find out your own username? - whoami
How do you send a mail message to somebody? - mail somebody@techinterviews.com -s ‘Your subject’ -c ‘cc@techinterviews.com‘

How do you count words, lines and characters in a file? - wc
How do you search for a string inside a given file? - grep string filename
How do you search for a string inside a directory? - grep string *
How do you search for a string in a directory with the subdirectories recursed? - grep -r string *
What are PIDs? - They are process IDs given to processes. A PID can vary from 0 to 65535.
How do you list currently running process? - ps
How do you stop a process? - kill pid
How do you find out about all running processes? - ps -ag
How do you stop all the processes, except the shell window? - kill 0
How do you fire a process in the background? - ./process-name &
How do you refer to the arguments passed to a shell script? - $1, $2 and so on. $0 is your script name.
What’s the conditional statement in shell scripting? - if {condition} then … fi
How do you do number comparison in shell scripts? - -eq, -ne, -lt, -le, -gt, -ge
How do you test for file properties in shell scripts? - -s filename tells you if the file is not empty, -f filename tells you whether the argument is a file, and not a directory, -d filename tests if the argument is a directory, and not a file, -w filename tests for writeability, -r filename tests for readability, -x filename tests for executability
How do you do Boolean logic operators in shell scripting? - ! tests for logical not, -a tests for logical and, and -o tests for logical or.
How do you find out the number of arguments passed to the shell script? - $#
What’s a way to do multilevel if-else’s in shell scripting? - if {condition} then {statement} elif {condition} {statement} fi
How do you write a for loop in shell? - for {variable name} in {list} do {statement} done
How do you write a while loop in shell? - while {condition} do {statement} done
How does a case statement look in shell scripts? - case {variable} in {possible-value-1}) {statement};; {possible-value-2}) {statement};; esac
How do you read keyboard input in shell scripts? - read {variable-name}
How do you define a function in a shell script? - function-name() { #some code here return }
How does getopts command work? - The parameters to your script can be passed as -n 15 -x 20. Inside the script, you can iterate through the getopts array as while getopts n:x option, and the variable $option contains the value of the entered option.

sequence triggers and expressions

2011-06-01T13:39:00.000-07:00

Sequence stage has a tab called trigger. This tab gives you a place to specify the expressions on the input links or in other words this tab provides a facility to control the behaviour of job activities inside sequences when executed one after another.
For example we have two job A and Job B. Jobs B should always run once job A finishes successfully. There should not be any warnings in the execution of job A and then only job B's execution should starts. Hence to achieve this we will select appropriate "Expression Type" and "Expression" in trigger tab of the sequence.

In above scenario "Expression Type" would be "OK - (Conditional)" and "Expression" section can be blank. Now what would we write if we have a job which can generate warnings and still we want job B to run. Now in this situation we will be specifying different "Expression Type" and "Expressions". Here we should choose Expression type as Custom - (Conditional) and specify Expression as "DSJS.RUNWARN Or DSJS.RUNOK". This expression will make sure that job B runs even if job A generated warnings or executed cleaned.

"DSJS.RUNWARN and DSJS.RUNOK" are status which can be used in the expression section of sequence. Following is the list of available status which can be used in the expression depending upon the requirement. DSJS.RUNNING - Job running
DSJS.RUNOK - Job finished a normal run with no warnings
DSJS.RUNWARN - Job finished a normal run with warnings
DSJS.RUNFAILED - Job finished a normal run with a fatal error
DSJS.QUEUED - Job queued waiting for resource allocation
DSJS.VALOK - Job finished a validation run with no warnings
DSJS.VALWARN - Job finished a validation run with warnings
DSJS.VALFAILED - Job failed a validation run
DSJS.RESET - Job finished a reset run
DSJS.CRASHED - Job was stopped by some indeterminate action
DSJS.STOPPED - Job was stopped by operator intervention
DSJS.NOTRUNNABLE - Job has not been compiled
DSJS.NOTRUNNING - Any other statusThere are few things we should remember with respect to triggers on sequences and they are as follows:
1 - Job activities in a sequence can have one input trigger and multiple output triggers.
2 - Trigger name must be unique in the activity however you can have multiple same named triggers in a sequence.
3 - Trigger would fire if the source meets the conditions defined in the activity. In our example even if Job A generates warning job B will be executed because trigger expression suffice this condition.
4 - DataStage macros can be used in triggers.If we use correct and appropriate expressions types and expressions in our sequences then we can control the interconnected activities more efficiently in the sequences for better performance and output.

Notes

2011-06-01T13:34:00.000-07:00

Datasets
Datasets are the best when storing the results intermediately. Datasets will keep the partitions and sort order if set. This will save re-partitioning, sorting and would make the job more robust.

Performance of the job can be improved if:
1) Unnecessary column are removed from the up and down stream links.
2) Removing these unnecessary columns will help reducing the memory consumption.
3) Always specify the list of columns in the select statement when reading from database. This will not bring unnecessary column data in the job which will save memory and network consumption.
4) Use RCP very carefully.
5) Understand the data-type before using them in the job. Do the data profiling before bringing data in the job.

Sort operations:
Always perform the following checks first before using the sort in the design:
1) Is sort really needed?
2) What is the volume of data going to be sorted?
3) Is data being read from database first and getting sorted in the job? Can we not sort the data in the database and bring sorted data?
4) What are the values set in the system related to Sort stage?If we give attention to above questions before applying sort then this will help up creating more performant job.

Parallelism is not always good:
Remember parallelism is not always beneficial. You have to think about the design of the job and the configuration. Degree of parallelism is determined by the configuration file where you can check how many node are defined. Increased parallelism can bring more overhead but will help distributing the work.

To get the maximum performance from job:
To get the maximum performance from job we should start the job design with the smaller set of the data and then increase the amount of data. We will only get the best performing job when we will experiment with the design of the job using different partitioning methods etc.

Point to remember while partitioning the data:
While partitioning the data make sure that the partitions are having equal amount of data in them. inequality will make the job less performant.

use of this?

2011-06-01T13:15:00.000-07:00

$PROJDEF - Populates the current value of the environment variable defined for a job. Value set for the variable will be used during the execution of the job. Benefit of using this special value is that we do not need to worry about the changing value of the environment variables (if it changes), job will use the current value of the variable.
$ENV - Set current value of the environment variable.
$UNSET - Unset current value of the environment variable.

Lookup/Join/Merge

2011-06-01T12:19:00.000-07:00

Lookup - give an opportunity to refer the data without sorting it. If the reference data is small and manageable in the memory. In this scenario this stage can give boost to the performance of the job. This stage is also to merge the information from two different sources.
Join - The fact is join does support two or more input links (left right and possibly intermediate links). But yes if you are tallking about full outer join then more than two links are not supported.
Coming back to main question of difference between Join and Merge Stage the other significant differences that I have noticed are:
1) Number Of Reject Link
(Join) does not support reject link.
(Merge) has as many reject link as the update links( if there are n-input links then 1 will be master link and n-1 will be the update link).
2) Data Selection
(Join) There are various ways in which data is being selected. e.g. we have different types of joins inner outer( left right full) cross join etc. So you have different selection criteria for dropping/selecting a row.
(Merge) Data in Master record and update records are merged only when both have same value for the merge key columns.
Merge - As the name suggest the stage is to merge the two or more sources also this stage needs the sorted data input. The benefit of this stage on above join stage is that in this stage we can have the reject links which is not possible in the join stage however it is possible in the lookup.