Types of Jobs in DataStage

Server jobs

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

Trigger types in Sequencer Job

While designing Sequencer jobs when we are using any Sequencer Stage ( Job activity, Command activity or others ), it has a tab named 'trigger' which actually determine the actions that occur when the activity runs. Each activity can output different trigger types. 

Three types of triggers are available, with some types having subtypes.

Conditional

A conditional trigger runs the target activity if the source activity fulfills the specified condition. The condition is defined by an expression, and can be one of the following types:

DataStage Scenario - Design10 - job1

DataStage Scenario Problem -->  DataStage Scenario - Problem10 

Solution Design :

a) Job Design :  

   Below is the design which can achieve the output as we needed. In this design, we are reading the data from flat file, generating a dummy column in column generator stage, doing aggregate on that and sending the output to seq file.

Data Warehouse Testing Checklist

Unit testing checklist

 A checklist will aid database programmers to systematically test their code before formal QA testing.

• Check the mapping of fields that support data staging and in data marts.
• Check for duplication of values generated using sequence generators.
• Check the correctness of surrogate keys that uniquely identify rows of data.
• Check for data-type constraints of the fields present in staging and core levels.
• Check the data loading status and error messages after ETLs (extracts, transformations, loads).
• Look for string columns that are incorrectly left or right trimmed.
• Make sure all tables and specified fields were loaded from source to staging.
• Verify that not-null fields were populated.
• Verify that no data truncation occurred in each field.
• Make sure data types and formats are as specified during database design.
• Make sure there are no duplicate records in target tables.
• Make sure data transformations are correctly based on business rules.
• Verify that numeric fields are populated precisely.
• Make sure every ETL session completed with only planned exceptions.
• Verify all data cleansing, transformation, and error and exception handling.
• Verify stored procedure calculations and data mappings.

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 Naming Standard

For maintaining the design and easy understanding of what happening inside the job, we are giving meaningful name to stages, links, jobs and sequencer. So that other developer/designer can have a idea about purpose of job/seq by seeing the design.

Here I have shared some DataStage Naming Standard we usually follow.

Oracle Interview Questions - Part-4

76. What is the OPTIMAL parameter?
It is used to set the optimal length of a rollback segment.

77. What is the functionality of SYSTEM table space?
To manage the database level transactions such as modifications of the data dictionary table that record information about the free space usage.

78. How will you create multiple rollback segments in a database?
- Create a database, which implicitly creates a SYSTEM rollback segment in a SYSTEM tablespace.
- Create a second rollback segment name R0 in the SYSTEM tablespace.
- Make new rollback segment available (after shutdown, modify init.ora file and start database)
- Create other tablespaces (RBS) for rollback segments.
- Deactivate rollback segment R0 and activate the newly created rollback segments.

DataStage Error : Loop final value not numeric - cannot execute it

Generally, you will face this Error when you are getting values from a file or Unix commands and using that value in Loop ( either transformer loop or sequencer loop ).

Solution : 

 

Datastage Common Errors and Solutions

1.     While running ./NodeAgents.sh start command... getting the following error: “LoggingAgent.sh process stopped unexpectedly”

SOL:   needs to kill LoggingAgentSocketImpl

              Ps –ef |  grep  LoggingAgentSocketImpl   (OR)

              PS –ef |               grep Agent  (to check the process id of the above)

2.     Warning: A sequential operator cannot preserve the partitioning of input data set on input port 0

SOL:    Clear the preserve partition flag before Sequential file stages.

How to Schedule Jobs and Sequencers via Datastage Scheduler

Datastage Native Job Scheduler

Datastage includes a scheduling option, it does not have its own. DataStage doesn't include a "scheduler" so leverages the underlying O/S. For UNIX that means cron and a check of the crontab entries for the scheduling user will have what you need. DataStage leverages cron for recurring schedules and at for 'one off' schedules. For Windows, it uses scheduled tasks of Windows. 

From the operating system command line, logged in as the scheduling user, a "crontab -l" will list the scheduled jobs.

Interview Questions : DataStage - self-3

100    If 1st and 8th record is duplicate then which will be skipped? Can you configure it?
101    How do you import and export datastage jobs? What is the file extension? (See each component while importing and exporting).
102    How do you rate yourself in DataStage?
103    Explain DataStage Architecture?
104    What is repository? What are the repository items?
105    What is difference between routine and transform?
106    When you write the routines?

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.

Interview Questions : DataStage - Part 2

For more : Visit HERE

What is the importance of Surrogate Key in Data warehousing?

Ans : Surrogate Key is a Primary Key for a Dimension table. Most importance of using it is independent of underlying database. i.e Surrogate Key is not affected by the changes going on with a database

What does a Config File in parallel extender consist of?

Ans: Config file consists of the following.
a) Number of Processes or Nodes.
b) Actual Disk Storage Location.

Oracle Interview Questions - Part-2

26. What is hash cluster?
A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.

27. When can hash cluster used?
Hash clusters are better choice when a table is often queried with equality queries. For such queries the specified cluster key value is hashed. The resulting hash key value points directly to the area on disk that stores the specified rows.

Oracle Interview Questions - Part-1

1. What are the components of physical database structure of Oracle database?
Oracle database is comprised of three types of files. One or more datafiles, two or more redo log files, and one or more control files.

2. What are the components of logical database structure of Oracle database?
There are tablespaces and database’s schema objects.

3. What is a tablespace?
A database is divided into Logical Storage Unit called tablespaces. A tablespace is used to grouped related logical structures together.

datastage4you QAnT - April2013 - 2

Click here ---> datastage4you QAnT - April2013 - 1

#14. DataStage Server – starting the server
a) DB2
su - db2inst1 -c "db2start“
b) WAS Server
/opt/IBM/WebSphere/AppServer/bin/startServer.sh server1 -user wasadmin -password PASSWORD
c) NODE
/opt/IBM/InformationServer/ASBNode/bin/NodeAgents.sh start
d) Engine
/opt/IBM/InformationServer/Server/DSEngine/bin/uv -admin -start

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.

Sequence job stages

The following stages are available when you create a sequence job.
Stages that are available in sequence jobs