Types of Jobs in DataStage

Server jobs

• Executed by the DataStage server engine
• Compiled into Basic
• Runtime monitoring in DataStage Director
• No parallel capability

Maximum number of DataStage Jobs in a project

Filesystems can have a hard limit on number of subdirectories that can be contained in a directory, for example 32766 (32K -2) for Linux

For every job, DataStage will create hash files as subdirectories in the project directory. Here is a list of the subdirectories created for every job. In this list "nn" represents the number (or id) of the job:

DS_TEMPnn

RT_CONFIGnn

RT_STATUSnn

RT_LOGnn

RT_BPnn

RT_BPnn.O

RT_SCnn (only for parallel jobs)

Things need to consider while developing a Datastage job

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.

How to Create Custom Stages in Datastage Parallel Jobs ?

In addition to the wide range of parallel stage types available, the Designer allows you to define your own stage types, which you can then use in parallel jobs.
For creating custom stage in datastage which will functions as per our requirement and we will be able to modify that stage as we may want. Here is some information on custom stage functionality in datastage in parallel jobs and how can we create our own wrapped custom stage.

Some more design tips for DataStage Job Development

• Common information like home directory, system date, username, password should be initialized in a global variable and then variable should be referred everywhere.

• Stage Variables allow you to hold data from a previous record when the next record, allowing you to compare between previous and current records. Stage variables also allow you return multiple errors for a record of information. By being able to evaluate all data in a record and not just error on the first exception that is found, the cleanup of data is more efficient and requires less iteration.

DataStage Parallel job: Retrieve sql codes on a failed upsert

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.

How many places u can call Routines?

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.)

Create a unique counter in datastage

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

In a server job there are a set of key increment routines installed in the routine SDK samples that offer a more complex counter that remembers values between job executions.
The following section outlines a transformer only technique.

How can I run the osh command of the InfoSphere Parallel Engine?

The osh command is the main program of the InfoSphere Parallel Engine. This command is used by DataStage to perform several different tasks including parallel job execution and dataset management. Normally, there is no need to run this command directly but sometimes it is useful to use it for troubleshooting purposes.

To run this command there are 3 environment variables that must be set. These are:

1. APT_ORCHHOME should point to Parallel Engine location
2. APT_CONFIG_FILE should point to a configuration file
3. LD_LIBRARY_PATH should include the path to the parallel engine libraries. Please note that the name of this environment variable may take a different name (such as LIBPATH in AIX or SLIB_PATH in HP-UX) depending on your Operating System. Note: This variable does not need to be set in Windows environments.

Stage to OPerator in DataStage - the Real Driver

The parallel job stages are built on operators. Or Operators are individual parallel engine stages where the data actually flows. In a typical job flow, operators are end-points, and data sets are the links between the operators.

Each operator listed in the DUMP SCORE is a number of processes that are dependent on:

• the job's established configuration file (APT_CONFIG_FILE)
• constrained by the node pool settings
• the operator configuration in the parallel engine code
• Several environment variables, such as APT_DISABLE_COMBINATION, being set/unset.

Oracle Interview Questions - Part-3

51. What is a database instance? Explain.
A database instance (Server) is a set of memory structure and background processes that access a set of database files. The processes can be shared by all of the users. The memory structure that is used to store the most queried data from database. This helps up to improve database performance by decreasing the amount of I/O performed against data file.

52. What is Parallel Server?
Multiple instances accessing the same database (only in multi-CPU environments)

Diagnosing DataStage Job Monitor problems

DataStage job run statistics (i.e. rows per second processed) do not update in DataStage Designer or Director clients.

 

Check these details :

This section contains a quick series of diagnosis steps for those familiar with DataStage and Job monitor. If more detail is needed for any step, please refer to the more detailed instructions in the "Resolving the Problem" section:
If customer has checked and verified that the following:

datastage4you QAnT - April2013 - 1

 #1. Use the Data Set Management utility, which is available in the Tools menu of the DataStage Designer or the DataStage Manager, to examine the schema, look at row counts, and delete a Parallel Data Set. You can also view the data itself.

#2. Use Sort stages instead of Remove duplicate stages. Sort stage has got more grouping options and sort indicator options.

#3. for quick checking if DS job is running on Server or not from UNIX
ps -ef | grep 'DSD.RUN'

Difference Between The Continuous Funnel And Sort Funnel

# Continuous Funnel combines the records of the input data in no guaranteed order. It takes one record from each input link in turn. If data is not available on an input link, the stage skips to the next link rather than waiting.

# Sort Funnel combines the input records in the order defined by the value(s) of one or more key columns and the order of the output records is determined by these sorting keys.

Local Containers and Shared Container

A container, as its name indicates, is used to group stages and links. Containers help simplify and modularize server job designs and allow you to replacing complex areas of the diagram with a single container stage. For example, if you have a lookup that is used by multiple jobs, you can put the jobs and links that generate the lookup into a share container and use it to different jobs. In a way, you can look at it like a procedure or function in the programming term.

Containers are linked to other stages or containers in the job by input and output stages.

Two types of container:

DataStage - Problem when running large parallel jobs on Windows

If you have large parallel jobs (8.1) running in Windows environment then it is worth checking the servers event log. You could find an error related to Nutcracker.exe. An administrator with access right on the server can see the events logs.

Nutcracker error could come up while running very heavy jobs where total cpu utilization is upto or above 90% and server starts using virtual memory. This is just an example of the impact of running large parallel jobs in Windows server. Hence to make the system somewhat full proof following changes can be done in Windows registry. The idea is to tune the TCP/IP buffer, increase the heap size and set the virtual memory to the recommended amount.

All about 000 - 421 : DataStage Certification Exam Test Preparation

1. DataStage v8 Configuration (5%)

1. Describe how to properly configure DataStage V.8.0.
1. This is kind of vague but focus on how DataStage 8 gets attached to a Metadata Server via the Metadata Console and how security rights are set up. 
2. Read up on configuring DB2 and Oracle client and ODBC. 
3. Get to know the dsenv file.  Read the DataStage Installation Guide for post-installation steps.

14 Good design tips in Datastage

1) When you need to run the same sequence of jobs again and again, better create a sequencer with all the jobs that you need to run. Running this sequencer will run all the jobs. You can provide the sequence as per your requirement.

2) If you are using a copy or a filter stage either immediately after or immediately before a transformer stage, you are reducing the efficiency by using more stages because a transformer does the job of both copy stage as well as a filter stage

Alphabetical list of stages in DataStage

The following tables contain an alphabetized list of all the stages that are available in IBM InfoSphere Information Server, Version 8.5, as included with the base installation or with add-on installations.

Whether a particular stage is displayed on the DataStage Designer client palette depends on the type of job you are creating and the products and add-ons that you installed. On the palette, stages are organized by category. In this document, stages are organized alphabetically.

Parallel job stages and server job stages [ A - F ]

 The stages in this document are included and available with IBM InfoSphere DataStage unless otherwise noted in the following key.

Palette category	Description
Data Quality1	Available only with IBM InfoSphere QualityStage
Data Quality2	Available as a separately licensed add-on module to InfoSphere QualityStage
Application	Available as an add-on Pack to IBM InfoSphere DataStage and QualityStage

The following key includes additional notes about stages in the Designer client.