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)

Read Sequential File with SCHEMA file

In Datastage, Schemas are an alternative way for you to specify column definitions for the data used by parallel jobs. You do not need to define any column and data format for reading a file. We are using this method when we needed a generic job for scenario like reading multiple files having different metadata defined.

Here, I am going to show you how to read the seq file with help of schema

A. Design

I am using below design to demonstrate this functionality, I am reading the data and dumping it to another file with different format.

Auto Generate Table Schema in DataStage

Few days ago, I got a request to explain how to generate the SCHEMA file of a table ? So here we goes.....

a. Design :

We can generate the SCHEMA file with this job. As we can see there are only two stages. One is DB connector stage ( it can be any DB) and a seq file stage which will hold the SCHEMA definition.

File descripters in Unix

A file descriptor is an abstract value of type Unix.file_descr, containing information necessary to use a file: a pointer to the file, the access rights, the access modes (read or write), the current position in the file
or we can say
Under the covers of the Unix operating system, files are referenced, copied, and moved by unique numbers known as file descriptors.

Three descriptors are predefined. They correspond to standard input, standard output, and standard error.

0 - stdin (Standard Input)  "<"
1 - stdout ( Standard Output) "1>" or ">"
2 - stderr ( Standard Error) "2>"

Read Fixed Width Flat File with Sequential File Stage

If we have a fixed width Flat file, we can read it via Sequential File stage only, no need to use Import Stage.   For reading a Fixed Width Flat file, you should know the format of Fixed width data. How are the data splitting into two or more output columns.

Here, I demonstrate How to read a Fixed Width Flat file from Sequential File Stage

A. Input File :

Below is the Input file which we are going to use in our job. This file contains a column RollNo which is concatenation of 4 fixed width columns which we have to split and read.

Handling Filename with Spaces in Linux

Spaces in Unix/Linux Filename is not a good habit as usually it is used as delimiter for field values rather than being in filename itself. In Linux, SPACE is used as a IFS ( Internal Field Separator) that is used for word splitting after expansion and to split lines into words with the read build in command. The default values of IFS is SPACE, TAB, NEW LINE.

     This is why whenever we are doing something with File-name having spaces in Linux, we got error like..
: No such File or Directory

   What if you got some files from a "Unix dumb" Server source, How you will handle the filename with spaces, Here are some simple ways to deal with it.

APT_CONFIG_FILE : Configuration File

APT_CONFIG_FILE is the file using which DataStage determines the configuration file (one can have many configuration files for a project) to be used. In fact, this is what is generally used in production. However, if this environment variable is not defined then how DataStage determines which file to use?

1)If the APT_CONFIG_FILE environment variable is not defined then DataStage look for default configuration file (config.apt) in following path:

1)Current working directory.

2)INSTALL_DIR/etc, where INSTALL_DIR ($APT_ORCHHOME) is the top level directory of DataStage installation.

What are the different options a logical node can have in the configuration file?

Surrogate Key Generator - Create/Update State File

In last post ( Surrogate Key Generator - Create State File ) we have seen how to generate the surrogate key file, In this post we will discuss about what are the other ways to generate it and how to update state file

Update Surrogate State File (Flat File) :-
To update the state file, add a Surrogate Key Generator stage to a job with a single input link from another stage.

 

Surrogate Key Generator - Create State File

Surrogate keys are numeric keys that can replace business keys in the datawarehouse.  A surrogate key has the following characteristics:

1) It is typically an integer.
2) Surrogate keys are unique.
3) Surrogate keys allow you to combine data from tables with incompatible keys.
4) It has no meaning. You will not be able to know the meaning of that row of data based on the surrogate key value.
5) It is not visible to end users. End users should not see a surrogate key in a report.

I will not go in details of surrogate key, I will post it later, But we will see how to generate surrogate keys in DataStage.

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 Coding Checklist

1. Ensure that the null handling properties are taken care for all the nullable fields. Do not set the null field value to some value which may be present in the source.
2. Ensure that all the character fields are trimmed before any processing. Normally extra spaces in the data may lead to some errors like lookup mismatch which are hard to detect.
3. Always save the metadata (for source, target or lookup definitions) in the repository to ensure re usability and consistency.

Interview Questions : DataWareHouse - Part 4

What are the types of Synonyms?
There are two types of Synonyms Private and Public

What is a Redo Log?
The set of Redo Log files YSDATE, UID, USER or USERENV SQL functions, or the pseudo columns LEVEL or ROWNUM.

What is an Index Segment?
Each Index has an Index segment that stores all of its data.

Explain the relationship among Database, Table space and Data file?
Each databases logically divided into one or more table spaces one or more data files are explicitly created for each table space.

DataStage Scenario - Problem9

Goal : Distribute the data is below requirement

Input file's data is distributed like 1st 3 row in file1, next 3 row in file2, next 3 rows in file3 again next 3 rows in file1 and continues.

DataStage Scenario - Problem8

Goal : Distribute the data as below condition -

Given : FileA and FileB contains some data in single column.
Target : File_A : Which contains the data which is available in A but not in B
Target : File_B : Which contains the data which is available in B but not in A
Target : File_AB : Which contains the data which is available in A and B Both

Hash Files in DataStage

Guys, Sharing a nice & detailed information on HASH files, Its old but betterto know.

What Exactly Are Hash Files?

DataStage® Server Edition utilizes several different types of hash files. The default and most versatile type is a dynamic (more precisely a type 30) hash file. A hash file can simply be described as a file that distributes data throughout a pre-sized and space allocated file. Every row has to have a key to determine where the row will reside within the file. This key is run thru an algorithm (technically a hashing algorithm, hence the name) to determine the location in the file for the row. One and only one row can exist at a single location.

DataStage Scenario - Problem6

Goal : Get the count of Vowels in Columns

Input :

Akash Aggrawal
Priya Awasthi  
Anil chahal    
Diya Singh    
Kashish Patel 
Sunil Verma    
Rashid Patel    
Rashmi Arya   
Gopal Joshi     
Neha Tomar    

List of Environment Variables in DataStage

General Job Administration

APT_CHECKPOINT_DIR
APT_CLOBBER_OUTPUT
APT_CONFIG_FILE
APT_DISABLE_COMBINATION
APT_EXECUTION_MODE
APT_ORCHHOME
APT_STARTUP_SCRIPT
APT_NO_STARTUP_SCRIPT
APT_STARTUP_STATUS
APT_THIN_SCORE

How to setup environment variables in DataStage

In DataStage you can set environment variables using three different methods. The right method depends on how often you need to change the value of an environment variable and how specific this value is for a particular project or job. Here is a description of these methods:

Conductor Node in Datastage

Below is the sample APT CONFIG FILE ,see in bold to mention conductor node.


{
node "node0"
{
fastname "server1"
pools "conductor"
resource disk "/datastage/Ascential/DataStage/Datasets/node0" {pools "conductor"}
resource scratchdisk "/datastage/Ascential/DataStage/Scratch/node0" {pools ""}
}

DataStage Scenario - Problem2

Goal : To get the Unique and Duplicates values from Input Data

 

Input :

There is a input file which contains duplicates data, Suppose :