DataStage is an ETL tool which is used to Extract the data from different data source, Transform the data as per the business requirement and Load into the target database. The data source can be of any type like Relational databases, files, external data sources, etc.
Developing new tools and processes to ensure effective use of DataStage product. The position will also be responsible for administrating and maintaining a DataStage shared environment. This includes: Designing and sizing the environment.
The DataStage Designer is the primary interface to the metadata repository and provides a graphical user interface that enables you to view, edit, and assemble DataStage objects from the repository needed to create an ETL job. An ETL job should include source and target stages.
DataStage Director has three view options: The Status view displays the status, date and time started, elapsed time, and other run information about each job in the selected repository category. The Schedule view displays job scheduling details. The Log view displays all of the events for a particular run of a job.
Oracle connector is used to access Oracle database systems and perform various read, write, and load functions. Setting required user privileges.
Parameter sets enable you to expose different parameters to the user. And, to return different information based on the parameters specified by the user. You can only use one parameter set at a time.
########################################################################################
ORA - data from Oracle Database
DS - Dataset Files
FILE - Sequential Files
AGG - Aggregation
SHARED - Shared Containers
TRANSFORMER
MERGE
JOIN
LOOKUP
FUNNEL
SORT
FILTER
REMOVE_DUPLICATES
ORACLE_DATABASE
EXECUTE_COMMANDS
Sequence job, that you use to specify a sequence of parallel jobs or server jobs to run. You specify the control information, such as the different courses of action to take depending on whether a job in the sequence succeeds or fails.
########################################################################################
1] SRC :- EMP(ORA)
TRG(FILE) :- EMPNO,ENAME,SAL,SEQ_NUM
DATA :- ONLY FOR SAL >= 1000 & GENERATE THE SEQ_NUM.
2] SRC :- EMP(ORA), DEPT_(ORA)
TRG(DS) :- RANK_NUM,EMPNO,ENAME,SAL,DEPTNO,DNAME,LOC
RANK_NUM = SEQUENCE NUMBERS
DATA :-TOP 3 SAL RECORDS
3] SRC :- EMP
TRG(DS) :- EMPNO,ENAME,SAL,COMM,TSAL,DEPTNO,RANK_NUM
RANK_NUM = SEQUENCE NUMBERS
DATA :- TSAL = SAL + COMM[HANDLE NULL]
BOTTOM 5 TSAL RECORDS.
4] SRC :- EMP
TRG[2 ORA TABLES] :- EMPNO,ENAME,SAL,TAX,SEQ_NUM
SEQ_NUM = SEQUENCE NUMBERS
DATA :- TAX = SAL * 0.10
DATA INTO THE TRGS MUST BE LOADED IN AN ALTERNATE WAY.
@PARTITIONNUM + ((@INROWNUM -1) * @NUMPARTITIONS) +1
-------------------------------------------------------------
1] SRC :- EMP(ORA),DEPT(ORA)
TRG(2) :- DEPTNO,DNAME,LOC,SUM_SAL,TAX_SUM_SAL
TRG1(ORA) :-
SUM(SAL) GROUP BY DEPTNO
TAX_SUM_SAL = SUM_SAL * 0.50 IF DEPTNO = 10
TAX_SUM_SAL = SUM_SAL * 0.25 IF DEPTNO = 20
TAX_SUM_SAL = SUM_SAL * 0.10 IF DEPTNO = 30
DATA FOR TRG1(DS) :- (DEPTNO = 10 OR DEPTNO = 20) AND TAX_SUM_SAL >= 2000
TRG2(FILE) :- SUM_SAL BETWEEN 4000 & 15000
2] SRC :- EMP(ORA)
TRG(DS) :- EMPNO,ENAME,SAL,DEPTNO
DEPTNO = P1(INTEGER) & SAL >= P2(INTEGER)
3] SRC :- EMP(ORA),DEPT(ORA)
TRG() :-
CREATE A PARAMETER SET WITH REQUIRED PARAMETERS & USE IN THIS JOB :-
TRG1(FILE) :- EMPNO,ENAME,SAL,COMM,TSAL,TAX,TRATE,NETSAL
TSAL = SAL + COMM[HANDLE NULL]
TRATE = P1(FLOAT/DOUBLE)
TAX = TSAL * TRATE
NETSAL = TSAL - TAX
DATA ONLY FOR NETSAL >= P2(FLOAT/DOUBLE)
TRG2(DS) :- SEQ_NUM,DEPTNO,DNAME,SUM_SAL,TAX_SUM_SAL,TAX_RATE
TAX_RATE = P3(FLOAT/DOUBLE)
SUM_SAL = SUM(SAL) GROUP BY DEPTNO
TAX_SUM_SAL = SUM_SAL * TAX_RATE
SEQ_NUM = GENERATE SEQUENCE NUMBER