DATASTAGE QUESTIONS

DATASTAGE QUESTIONS

1. WHAT IS DIFFERENCE BETWEEN SEQUENTAIL FILE AND DATA SET?

Sequentail file--- reads the data sequentially. cannot handle nullability. memory linit is 2gb. file extension is .txt/.csv
Dataset-----------reads the data parallel can handle nulls. extension of file is .ds

2.what is APT configuration file?

It is the environment variable which is used to recognize the *.apt file in DataStage. It is also used to keep the node information, scratch information and disk storage information.

3. what is diffenrece between datasatge 11.x version and previous?

DataStage 11.5 is the first version that promises to run anywhere - it can run on a dedicated DataStage engine (Windows, Linux, AIX or Z). It can push processing down into databases via Balanced Optimizer.  It can run natively on Hadoop.

  Tell any one of these:
 Dynamic RDBMS Stage: replaced by DRS Connector
 Oracle OCI, Oracle OCI Load: replaced by Oracle Connector
 Teradata API: replaced by Teradata Connector
 DB2 UDB Load, UBD API, DB2 Z: replaced by DB2 Connector

4. What is descriptor file and data file in dataset?

As the name says, data files contains the data and the descriptor file contains the information about the data in the data files.

5. How to remove duplicates in datastage?
Remove Duplicate Stage:
Duplicates can be detached by using Sort stage. We can use the opportunity, as allow duplicate = false.

6. Differnce between Join,Merge,Looup?

Join: linknames--Left,Right,Intermidiate
        Sorting is mandatory
        supports-  left join,inner join,right outer join,full outer join

Lookup: lonk names--- master,refernce
  sorting is optional
supports-- drop(inner join)  continue- left ouer join

All the three are dissimilar from each other in the way they use the memory storage, compare input necessities and how they treat various data . Join and Merge needs minimum memory as compared to the Lookup stage.

7.What are differnt types of Lookups in datasatge?

There are two types of Lookups in DataStage i.e. Normal lookup and Sparse lookup.

Normal lookup--- volume of primary is high and refernce is less
Sparse lookup--- Volume of refernce is high and primary less.
Default is Normal lookup

8.what are differnt types of partioning techniques?

Key based-Hash,Range
Key less--same,auto

9.Components in Datastage?

Designer-  Design parllall and sequnce jobs,compile jobs,
Director-- see the logs of jobs, schdule jobs
Adminstrataor--- create project,set permission project
manager- import/export the prject

10 How to import/export  jobs from one env to another env?

using export--- job design without executables--.dsx componenets
import----- .dsx format


Q #11) What are the primary usages of Datastage tool?

Datastage is an ETL tool which is primarily used for extracting data from source systems, transforming that data and finally loading it to target systems.

Q #12) What is a DataStage job?

The Datastage job is simply a DataStage code that we create as a developer. It contains different stages linked together to define data and process flow.
Stages are nothing but the functionalities that get implemented.
For example: Let’s assume that I want to do a sum of the sales amount. This can be a ‘group by’ operation that will be performed by one stage.
Now, I want to write the result to a target file. So, this operation will be performed by another stage. Once, I have defined both the stages, I need to define the data flow from my ‘group by’ stage to the target file stage. This data flow is defined by DataStage links.
Once, I have defined both the stages, I need to define the data flow from my ‘group by’ stage to the target file stage. This data flow is defined by DataStage links.

Q #13) What are DataStage sequences?

Datastage sequence connects the DataStage jobs in a logical flow.

Q #14) Where do the Datastage jobs get stored?

The Datastage jobs get stored in the repository. We have various folders in which we can store the Datastage jobs.

Datastage Interview Questions

Datastage Interview Questions

1. What is configuration file?

APT configuration file contains details of node. In this it has sub parts node, pool, scratch disk, resource disk

2. What is difference between join, lookup ?

Join---(a) does not support reject link,

            (b)  in this link name are left, right, intermediate, 

            ©   supports inner join, left outerjoin, right outerjoin, full outerjoin

Lookup-(a) supports reject link

               (b) in this link names are   primary, reference

               © supports inner join (called as drop) left outer join(continue)

3. What is dataset?

Dataset is file stage with extension .ds.  in this data is stored in descriptor file and data fiile.

To retrieve data from dataset from unix “ orchadmin command is used.

4. What are different types of portioning techniques?

Key based portioning—hash,modulus,range

Key less portioning---- same,auto

5. How to remove duplicates in datastage?

REMOVE DUPLICATE STAGE.

IN COPY STAGE( IN THIS GO TO INPUT--- PARTIONINS-CHANGE TO HASH PARTIONS-CHECK SORT- UNIQUE(THIS UNIQUES REMOVE DUPLICATES)

SORT STAGE AND TRANSFORMER STAGE--- KEY CHANGE COLUMN SET TO TRUE(THIS IS DONE IN SORT STAGE)—TRANSFORMER STAGE(CONSTARINT)

6. What is difference between active stage and passive stage?

Active stages- all processing stages are active(transformation part)

Passive stage- file stage,database stage.

7. How to  handle unix commands in sequential file? What other stages supports unix command?

In sequential file stage there is options—in that slect filter(here we can write unix) and change the file method to file pattern. 

External filter stage supports unix command

8. Difference between filter and switch?

Switch is used to write case statement and we can do on single coulumn

Fileter is used to write where clause and we can do on multiple columns

9. How to find nth maximum salary from emp?

 In oracle select * from ( select sal,empid,rownum over(order by sal desc) rn from emp) where rn=-2

10. How to remove duplicates from table?

11. Difference between union and union all?

Union- remove duplicates

Union all- with duplictaes

12. How to delete nth line in unix?

Sed nd

13. Where/Group by/having/ based scenario?

SELECT Salesperson, SUM(TotalSale)

FROM SALES

GROUP BY Salesperson

HAVING Salesperson <>'Bennett';

14. how to replace one string with another in unix?

Sed ‘s\ old string\ new string’  filename

15. how to remove duplicates in a file through unix

   Uniq command