1. How to remove duplicate rows?
We have various ways to do the same.
ServerJobs
-Using Hash File.
-Using Stage variable
Detailed description as below. we have to define two stage variable for example stgV1 and stgV2.
Derivation...............Stage Variable
-----------------------------------------
stgV1......................stgV2
IputKeyColm(Id).....stgV1
We have to write a constraint like as below
if stgV1<>stgV2 ------------->this stream is to produce unique records
if stgV1=stgV2 ------------->this stream is to produce duplicate records
hope I am clear with the same....
Parallel Jobs
-Using Stage Variables
-Using Remove Duplicates Stage
-Using Perform sort option [specifically need to select unique option] under PartitioningTab
-Using SortStage
The Duplicates can be eliminated by loading the corresponding data in the Hash file. Specify the columns on which u want to eliminate as the keys of hash.
You can delete duplicate records from source itself using data stage taking option as user defined query, instead of taking table read option. and u can use remove duplicate stage in data stage. and using of hash file as source also based on the hash key.
2. What are the Repository Tables in Data Stage and what are they?
A data warehouse is a repository (centralized as well as distributed) of Data, able to answer any adhoc, analytical, historical or complex queries. Metadata is data about data. Examples of metadata include data element descriptions, data type descriptions, attribute/property descriptions, range/domain descriptions, and process/method descriptions. The repository environment encompasses all corporate metadata resources: database catalogs, data dictionaries, and navigation services. Metadata includes things like the name, length, valid values, and description of a data element. Metadata is stored in a data dictionary and repository. It insulates the data warehouse from changes in the schema of operational systems. In data stage I/O and Transfer, under interface tab: input, out put & transfer pages. U will have 4 tabs and the last one is build under that u can find the TABLE NAME .The Data Stage client components are: Administrator Administers Data Stage projects and conducts housekeeping on the server Designer Creates Data Stage jobs that are compiled into executable programs Director Used to run and monitor the Data Stage jobs Manager Allows you to view and edit the contents of the repository.
3. Differentiate Database data and Data warehouse data?
By Database, one means OLTP (On Line Transaction Processing). This can be the source systems or the ODS (Operational Data Store), which contains the transactional data.
Database data only for transactional purpose & Data warehouse data only for analytical purpose for decision making.
Data base contains detail data. Data warehouse contains summary data
4.If a Data Stage job aborts after say 1000 records, how to continue the job from 1000th record after fixing the error?
By specifying Check pointing in job sequence properties, if we restart the job. Then job will start by skipping up to the failed record. This option is available in 7.5 edition.
5. What is merge and how it can be done please explain with simple example taking 2 tables.......?
Merge is used to join two tables. It takes the Key columns sort them in Ascending or descending order. Let us consider two table i.e Emp, Dept. If we want to join these two tables we are having DeptNo as a common Key so we can give that column name as key and sort Deptno in ascending order and can join those two tables
6. It is possible to call one job in another job in server jobs?
I think we can call a job into another job. In fact calling doesn't sound good, because you attach/add the other job through job properties. In fact, you can attach zero or more jobs.
Steps will be Edit --> Job Properties --> Job Control
Click on Add Job and select the desired job.
7. What are the environment variables in data stage? Give some examples?
There are the variables used at the project or job level. We can use them to configure the job i.e. We can associate the configuration file(Without this u can not run your job), increase the sequential or dataset read/ write buffer.
ex: $APT_CONFIG_FILE
Like above we have so many environment variables. Please go to job properties and click on "add environment variable" to see most of the environment variables.
8. What happens if RCP is disable ?
Runtime column propagation (RCP): If RCP is enabled for any job, and specifically for those stages whose output connects to the shared container input, then Meta data will be propagated at run time, so there is no need to map it at design time.
If RCP is disabled for the job, in such case OSH has to perform Import and export every time when the job runs and the processing time job is also increased.
9. How can I extract data from DB2 (on IBM iSeries) to the data warehouse via Data stage as the ETL tool? I mean do I first need to use ODBC to create connectivity and use an adapter for the extraction and transformation of data?
You would need to install ODBC drivers to connect to DB2 instance (does not come with regular drivers that we try to install, use CD provided for DB2 installation, that would have ODBC drivers to connect to DB2) and then try out
10. What is the mean of Try to have the constraints in the 'Selection' criteria of the jobs itself. This will eliminate the unnecessary records even getting in before joins are made?
This means try to improve the performance by avoiding use of constraints wherever possible and instead using them while selecting the data itself using a where clause. This improves performance.
11. What does a config File in parallel extender consist of?
Config file consists of the following.
a) Number of Processes or Nodes.
b) Actual Disk Storage Location.
The APT Configuration file is having the information of resource disk, node pool, and scratch information, node information in the since it contains the how many nodes we given to run the jobs, because based on the nodes only data stage will create processors at back end while running the jobs, resource disk means this is the place where exactly jobs will be loading, scratch information will be useful whenever we using the lookups in the jobs
APT Configuration file is the configuration file which defines the nodes for the specific project and actual disk storage.
12. Can we use shared container as lookup in data stage server jobs?
I am using Data Stage 7.5, UNIX. We can use shared container more than one time in the job. There is any limit to use it. why because in my job i used the Shared container at 6 flows. At any time only 2 flows are working. can you please share the info on this.
No. We cant use container as lookup.
Container is used to reduce the complexity view to simple view of stages or jobs, so there will be only jobs available inside and not source table or anything.
13. How to find the number of rows in a sequential file?
Using Row Count System variable
Use the aggregator stage to use the count record facility
14. What is the difference between validated ok and compiled in data stage?
When we say "Validating a Job", we are talking about running the Job in the "check only" mode. The following checks are made:
- Connections are made to the data sources or data warehouse.
- SQL SELECT statements are prepared.
- Files are opened. Intermediate files in Hashed File, Universe, or ODBC stages that use the local data source are created, if they do not already exist.
15. What are the differences between the data stage 7.0 and 7.5 in server jobs?
There is lot of Differences: There are lots of new stages are available in DS7.5 For
Eg: CDC Stage Stored procedure Stage etc…
16. Is it possible to run parallel jobs in server jobs?
No, it is not possible to run Parallel jobs in server jobs. But Server jobs can be executed in Parallel jobs
17. What is DS Administrator used for - did u use it?
The Administrator enables you to set up Data Stage users, control the purging of the Repository, and, if National Language Support (NLS) is enabled, install and manage maps and locales.
18. What is Modulus and Splitting in Dynamic Hashed File?
The modulus size can be increased by contacting your Unix Admin.
In a Hashed File, the size of the file keeps changing randomly.
If the size of the file increases it is called as "Modulus".
If the size of the file decreases it is called as "Splitting".
The modulus size can be increase/decrease by contacting your Data Stage Admin
19. How can ETL access excel file to Data mart?
Open the ODBC Data Source Administrator found in the control panel/administrative tools. Under the system DSN tab, add the Driver to Microsoft Excel. Then you will be able to access the XLS file from Data stage.
20.What is the exact difference between Join, Merge and Lookup Stage?
The exact difference between Join, Merge and lookup is The three stages differ mainly in the memory they use Data Stage doesn't know how large your data is, so cannot make an informed choice whether to combine data using a join stage or a lookup stage. Here's how to decide which to use:
if the reference datasets are big enough to cause trouble, use a join. A join does a high-speed sort on the driving and reference datasets. This can involve I/O if the data is big enough, but the I/O is all highly optimized and sequential. Once the sort is over the join processing is very fast and never involves paging or other I/O
Unlike Join stages and Lookup stages, the Merge stage allows you to specify several reject links as many as input links.
The three stages differs with each other with respect to
1. Input Requirements
2. Treatment of unmatched records
3. Memory Usage
Join requires less memory usage
Lookup requires more memory usage and
Merge requires less memory usage
21. What about System variables?
Data Stage provides a set of variables containing useful system information that you can access from a transform or routine. System variables are read-only.
@DATE The internal date when the program started. See the Date function.
@DAY The day of the month extracted from the value in @DATE.
@FALSE The compiler replaces the value with 0.
@FM A field mark, Char(254).
@IM An item mark, Char(255).
@INROWNUM Input row counter. For use in constrains and derivations in Transformer stages.
@OUTROWNUM Output row counter (per link). For use in derivations in Transformer stages.
@LOGNAME The user login name.
@MONTH The current extracted from the value in @DATE.
@NULL The null value.
@NULL.STR The internal representation of the null value, Char(128).
@PATH The pathname of the current Data Stage project.
@SCHEMA The schema name of the current Data Stage project.
@SM A sub value mark (a delimiter used in Universe files), Char(252).
@SYSTEM.RETURN.CODE Status codes returned by system processes or commands.
@TIME The internal time when the program started. See the Time function.
@TM A text mark (a delimiter used in Universe files), Char(251).
@TRUE The compiler replaces the value with 1.
@USERNO The user number.
@VM A value mark (a delimiter used in Universe files), Char(253).
@WHO The name of the current Data Stage project directory.
@YEAR The current year extracted from @DATE.
REJECTED Can be used in the constraint expression of a Transformer stage of an output link. REJECTED is initially TRUE, but is set to FALSE whenever an output link is successfully written.
22. Importance of Surrogate Key in Data warehousing?
The concept of surrogate comes into play when there is slowly changing dimension in a table. In such condition there is a need of a key by which we can identify the changes made in the dimensions. These slowly changing dimensions can be of three type namely SCD1, SCD2, SCD3.These are system generated key. Mainly they are just the sequence of numbers or can be alphanumeric values also.
23. How to handle the rejected rows in data stage?
We can handle rejected rows in two ways with help of Constraints in a Tansformer.1) By Putting on the Rejected cell where we will be writing our constraints in the properties of the Transformer2)Use REJECTED in the expression editor of the Constraint Create a hash file as a temporary storage for rejected rows. Create a link and use it as one of the output of the transformer. Apply either of the two steps above said on that Link. All the rows which are rejected by all the constraints will go to the Hash File.
24. How can you do incremental load in data stage?
Incremental load means daily load. When ever you are selecting data from source, select the records which are loaded or updated between the timestamp of last successful load and today’s load start date and time. For this u have to pass parameters for those two dates. Store the last run date and time in a file and read the parameter through job parameters and state second argument as current date and time.
25. How do we do the automation of data stage jobs?
We can call Data stage Batch Job from Command prompt using 'dsjob'. We can also pass all the parameters from command prompt. Then call this shell script in any of the market available schedulers.
The 2nd option is schedule these jobs using Data Stage director.
26. Can anyone tell me how to extract data from more than 1 heterogeneous Sources? Mean, example 1 sequential file, Sybase, Oracle in a single Job?
Yes you can extract the data from two heterogeneous sources in data stages using the transformer stage it's so simple you need to just form a link between the two sources in the transformer stage that's it Bye Hammed
27. What is merge? And how to use merge?
Merge is a stage that is available in both parallel and server jobs.
The merge stage is used to join two tables (server/parallel) or two ables/datasets(parallel). Merge requires that the master table/dataset and the update table/dataset to be sorted. Merge is performed on a key field, and the key field is mandatory in the master and update dataset/table. Merge is used to merge the two tables, but when master and update records are merged only both have same merge key columns.
28. What's the difference between Data stage Developers and Data stage Designers. What are the skills required for this?
Data stage developer is one how will code the jobs. Data stage designer is how will design the job, i mean he will deal with blue prints and he will design the jobs the stages that are required in developing the code
29.What is the OCI? And how to use the ETL Tools?
OCI doesn't mean the orabulk data. It actually uses the "Oracle Call Interface" of the oracle to load the data. It is kind of the lowest level of Oracle being used for loading the data.
30.What are the different types of lookups in data stage?
There are two types of lookup stage and look up file set
Lookup: Lookup reference to another stage or Database to get the data from it and transforms to other database.
Lookup File Set: It allows you to create a lookup file set or reference one for a lookup. The stage can have a single input link or a single output link. The output link must be a reference link. The stage can be configured to execute in parallel or sequential mode when used with an input link. When creating Lookup file sets, one file will be created for each partition. The individual files are referenced by a single descriptor file, which by convention has the suffix .
There are two types of lookups
1. Normal look up
2. Sparse look up
Normal look up: To perform this look up data will be stored in the memory first and then look up will be performed bue to which it takes more execution time
Sparse look up: Sql query will be directly fired on the database related record due to which execution is faster than normal look up. For a single job we can use only one file with sparse look up where as no: of normal lookups can be used. But once we mention file to do sparse lookup it will not accept normal look up after that. So make sure to mention all your normal lookups first and sparse lookups at the last.
31. How to implement type2 slowly changing dimension in data stage? Give me with example?
Slow changing dimension is a common problem in Data warehousing. For example: There exists a customer called Lisa in a company ABC and she lives in New York. Later she moved to Florida. The company must modify her address now. In general 3 ways to solve this problem.
Type 1: The new record replaces the original record, no trace of the old record at all, Type 2: A new record is added into the customer dimension table. Therefore, the customer is treated essentially as two different people.
Type 3: The original record is modified to reflect the changes.
In Type1 the new one will over write the existing one that means no history is maintained, History of the person where she stayed last is lost, simple to use.
In Type2 New record is added, therefore both the original and the new record Will be present, the new record will get its own primary key, Advantage of using this type2 is, Historical information is maintained But size of the dimension table grows, storage and performance can become a concern.
Type2 should only be used if it is necessary for the data warehouse to track the historical changes.
In Type3 there will be 2 columns one to indicate the original value and the other to indicate the current value. Example a new column will be added which shows the original address as New York and the current address as Florida. Helps in keeping some part of the history and table size is not increased. But one problem is when the customer moves from Florida to Texas the New York information is lost. So Type 3 should only be used if the changes will only occur for a finite number of times.
32.What is DS Manager used for - did u use it?
The Manager is a graphical tool that enables you to view and manage the contents of the Data Stage Repository
33. What are other Performance tunings you have done in your last project to increase the performance of slowly running jobs?
Minimize the usage of Transformer (Instead of this use Copy, modify, Filter, Row Generator) Use SQL Code while extracting the data
Handle the nulls
Minimize the warnings
Reduce the number of lookups in a job design
Use not more than 20stages in a job
Use IPC stage between two passive stages Reduces processing time
Drop indexes before data loading and recreate after loading data into tables
Generally we cannot avoid no of lookups if our requirements to do lookups compulsory.
There is no limit for no of stages like 20 or 30 but we can break the job into small jobs then we use dataset Stages to store the data.
IPC Stage that is provided in Server Jobs not in Parallel Jobs
Check the write cache of Hash file. If the same hash file is used for Look up and as well as target, disable this Option.
If the hash file is used only for lookup then "enable Preload to memory". This will improve the performance. Also, check the order of execution of the routines.
Don't use more than 7 lookups in the same transformer; introduce new transformers if it exceeds 7 lookups.
Use Preload to memory option in the hash file output.
Use Write to cache in the hash file input.
Write into the error tables only after all the transformer stages.
Reduce the width of the input record - remove the columns that you would not use.
Cache the hash files you are reading from and writing into. Make sure your cache is big enough to hold the hash files.
Use ANALYZE.FILE or HASH.HELP to determine the optimal settings for your hash files.
This would also minimize overflow on the hash file.
If possible, break the input into multiple threads and run multiple instances of the job.
Staged the data coming from ODBC/OCI/DB2UDB stages or any database on the server using Hash/Sequential files for optimum performance also for data recovery in case job aborts.
Tuned the OCI stage for 'Array Size' and 'Rows per Transaction' numerical values for faster inserts, updates and selects.
Tuned the 'Project Tumbles' in Administrator for better performance.
Used sorted data for Aggregator.
Sorted the data as much as possible in DB and reduced the use of DS-Sort for better performance of jobs
Removed the data not used from the source as early as possible in the job.
Worked with DB-admin to create appropriate Indexes on tables for better performance of DS queries
Converted some of the complex joins/business in DS to Stored Procedures on DS for faster execution of the jobs.
If an input file has an excessive number of rows and can be split-up then use standard logic to run jobs in parallel.
Before writing a routine or a transform, make sure that there is not the functionality required in one of the standard routines supplied in the sdk or ds utilities categories.
Constraints are generally CPU intensive and take a significant amount of time to process. This may be the case if the constraint calls routines or external macros but if it is inline code then the overhead will be minimal.
Try to have the constraints in the 'Selection' criteria of the jobs itself. This will eliminate the unnecessary records even getting in before joins are made.
Tuning should occur on a job-by-job basis.
Use the power of DBMS.
Try not to use a sort stage when you can use an ORDER BY clause in the database.
34. Using a constraint to filter a record set is much slower than performing a SELECT? WHERE?
Make every attempt to use the bulk loader for your particular database. Bulk loaders are generally faster than using ODBC or OLE.
35. What is the order of execution done internally in the transformer with the stage editor having input links on the left hand side and output links?
Stage variables, constraints and column derivation or expressions.
36. What is the difference between In-process and Inter-process?
In-process
You can improve the performance of most Data Stage jobs by turning in-process row buffering on and recompiling the job. This allows connected active stages to pass data via buffers rather than row by row.
Note: You cannot use in-process row-buffering if your job uses COMMON blocks in transform functions to pass data between stages. This is not recommended practice, and it is advisable to redesign your job to use row buffering rather than COMMON blocks.
Inter-process
Use this if you are running server jobs on an SMP parallel system. This enables the job to run using a separate process for each active stage, which will run simultaneously on a separate processor.
Note: You cannot inter-process row-buffering if your job uses COMMON blocks in transform functions to pass data between stages. This is not recommended practice, and it is advisable to redesign your job to use row buffering rather than COMMON blocks.
37.What is DS Designer used for - did u use it?
You use the Designer to build jobs by creating a visual design that models the flow and transformation of data from the data source through to the target warehouse. The Designer graphical interface lets you select stage icons, drop them onto the Designer work area, and add links.
38. How to handle Date conventions in Data stage? Convert a mm/dd/yyyy format to yyyy-dd-mm?
Function to convert mm/dd/yyyy format to yyyy-dd-mm is
Oconv(Iconv(Filedname,"D/MDY[2,2,4]"),"D-YDM[4,2,2]") .
39. If data is partitioned in your job on key 1 and then you aggregate on key 2, what issues could arise?
Data will partition on both the keys! Hardly it will take more for execution.
40. What is purpose of using the key and difference between Surrogate keys and natural key?
We use keys to provide relationships between the entities (Tables). By using primary and foreign key relationship, we can maintain integrity of the data.
The natural key is the one coming from the OLTP system.
The surrogate key is the artificial key which we are going to create in the target DW. We can use these surrogate keys instead of using natural key. In the SCD2 scenarios surrogate keys play a major role.
Natural key: key that is formed of attributes that already exist in the real world. e.g.SSN id.
Surrogate key: it can be thought of as a replacement of the natural key that has no business meaning. E.g. sequence in oracle.
I think there is a bug in the data stage 8.0.1 surrogate key generator. It seems to be very much inconsistent.
41. Is it possible to move the data from oracle ware house to SAP Warehouse using with DATASTAGE Tool?
We can use Data Stage Extract Pack for SAP R/3 and Data Stage Load Pack for SAP BW to transfer the data from oracle to SAP Warehouse. These Plug In Packs are available with Data Stage Version 7.5
42. How to kill the job in data stage?
By using Cleanup Resource or By killing the respective process ID
43. How we can call the routine in data stage job? Explain with steps?
Routines are used for implementing the business logic they are two types 1) Before Sub Routines and 2)After Sub Routine steps double click on the transformer stage right click on any one of the mapping field select [dstoutines] option within edit window give the business logic and select the either of the options( Before / After Sub Routines).
44. What are the most important aspects that a beginner must consider doin his first DS project?
He should be good at Data Warehousing Concepts and he should be familiar with all stages
45. How I can convert Server Jobs into Parallel Jobs?
I have never tried doing this, however, I have some information which will help you in saving a lot of time. You can convert your server job into a server shared container. The server shared container can also be used in parallel jobs as shared container.
I don't think this conversion might be possible.
Using ipc stage or link partitioner/link collector can incorporate a n amount of parallelism in the server jobs.
My requirement is like this:
Here is the codification suggested:
SALE_HEADER_XXXXX_YYYYMMDD.PSV
SALE_LINE_XXXXX_YYYYMMDD.PSV
XXXXX = LVM sequence to ensure unicity and continuity of file exchanges
Caution, there will an increment to implement.
YYYYMMDD = LVM date of file creation
COMPRESSION AND DELIVERY TO: SALE_HEADER_XXXXX_YYYYMMDD.ZIP AND SALE_LINE_XXXXX_YYYYMMDD.ZIP
If we run that job the target file names are like this
sale_header_1_20060206 & sale_line_1_20060206.
If we run next time means the target files we like this sale_header_2_20060206 & sale_line_2_20060206.
If we run the same in next day means the target files we want like this
sale_header_3_20060306 & sale_line_3_20060306.
i.e., whenever we run the same job the target files automatically changes its filename to
filename_increment to previous number(previousnumber + 1)_currentdate;
Please do needful by repling this question..
This can be done by using unix script
1. Keep? the Target filename as constant name xxx.psv
2. Once the job completed, invoke the Unix Script through After job routine - ExecSh
3. The script should get the number used in previous file and increment it by 1, After that move the file from xxx.psv to filename_(previousnumber + 1)_currentdate.psv and then delete the xxx.psv file.This is the?Easiest way to implement.
46.What is the difference between symmetrically parallel processing, massively parallel processing?
Symmetric Multiprocessing (SMP) - Some Hardware resources may be shared by processor. Processors communicate via shared memory and have single operating system.
Cluster or Massively Parallel Processing (MPP) - Known as shared nothing in which each processor have exclusive access to hardware resources. Cluster systems can be physically dispersed. The processor has their own operations system and communicate via high speed network
47. What is the purpose of exception activity in data stage 7.5?
The stages followed by exception activity will be executed whenever there is an unknown error occurs while running the job sequencer.
48. Where we use link partitioner in data stage job? explain with example?
We use Link Partitioner in Data Stage Server Jobs. The Link Partitioner stage is an active stage which takes one input and allows you to distribute partitioned rows to up to 64 output links.
The 100 employees salaries in one organization .i want to load >300 emps in one target and <250 and >450 in one target that time go for link partioner stage
49. How to parameterize a field in a sequential file? I am using Data stage as ETL Tool, Sequential file as source?
We cannot parameterize a particular field in a sequential file, instead we can parameterize the source file name in a sequential file.
50. How to drop the index before loading data in target and how to rebuild it in data stage?
This can be achieved by "Direct Load" option of SQL Loaded utility.
If we are using OCI & database stage, we can use Before/After sql property in stage properties.
51.If the size of the Hash file exceeds 2GB..What happens? Does it overwrite the current rows?
It overwrites the file
52. Can we retain the removed duplicate values...If yes, how can we do that?
It is possible to access the same job two users at a time in data stage
No, it is not possible to access the same job two users at the same time. DS will produce the following error: "Job is accessed by other user"
No it is not possible for the same job to be accessed by two users, because if one of the job is being used by one user then if the second one tries to open the same job then you get the error as "Job (job name) is being accessed by another user"
--In Data stage 8 it is possible to open the job as Read-Only. In order to do some modification in the read-only job, take a copy of the job as modification cannot be done in a read only job. This feature is not available in Data stage 7.5.
53. What is job control? How it is developed? Explain with steps?
Controlling Data stage jobs through some other Data stage jobs. Ex: Consider two Jobs XXX and YYY. The Job YYY can be executed from Job XXX by using Data stage macros in Routines.
To execute one job from other job, following steps needs to be followed in Routines.
1. Attach job using DS Attach job function.
2. Run the other job using DS Run job function
3. Stop the job using DSS top Job function
54. Where actually the flat files store? What is the path?
Flat files stores the data and the path can be given in general tab of the sequential file stage
55. Will the data stage consider the second constraint in the transformer once the first condition is satisfied ( if the link ordering is given)?
Will Data stage consider the second constraint in the transformer if the first constraint is satisfied (if link ordering is given)?"
Answer: Yes.
56. What is a project? Specify its various components?
You always enter Data Stage through a Data Stage project. When you start a Data Stage client you are prompted to connect to a project. Each project contains:
Data Stage jobs:
Built-in components: These are predefined components used in a job.
User-defined components: These are customized components created using the Data Stage Manager or Data Stage Designer
You always enter Data Stage through a Data Stage project. When you start a Data Stage client you are prompted to connect to a project. Each project contains:
57. Briefly describe the various client components?
There are four client components
Data Stage Designer: A design interface used to create Data Stage applications (known as jobs). Each job specifies the data sources, the transforms required, and the destination of the data. Jobs are compiled to create executables that are scheduled by the Director and run by the Server.
Data Stage Director: A user interface used to validate, schedule, run, and monitor Data Stage jobs.
Data Stage Manager: A user interface used to view and edit the contents of the Repository.
Data Stage Administrator: A user interface used to configure Data Stage projects and users.
58. What are the Steps involved in development of a job in Data Stage?
The steps required are:
Select the data source stage depending upon the sources for ex: flat file, database, xml etc
Select the required stages for transformation logic such as transformer, link collector, link practitioner, Aggregator, merge etc.
Select the final target stage where u want to load the data either it is data warehouse, data mart, ODS, staging etc
59. What are constraints and derivation?
Explain the process of taking backup in Data Stage?
What are the different types of lookups available in Data Stage?
Constraints are used to check for a condition and filter the data. Example: Cust_Id<>0 is set as a constraint and it means and only those records meeting this will be processed further.
Derivation is a method of deriving the fields, for example if you need to get some SUM, AVG etc.
60. How does Data Stage handle the user security?
We have to create users in the Administrators and give the necessary privileges to users.
61. What is meaning of file extender in data stage server jobs?
Can we run the data stage job from one job to another job that file data where it is stored and what is the file extender in data stage jobs?
What is meaning of file extender in data stage server jobs?
Can we run the data stage job from one job to another job that file data where it is stored and what is the file extender in data stage jobs?
File extender means the adding the columns or records to the already existing the file, in the data stage. We can run the data stage job from one job to another job in data stage.
62. What is the difference between DRS and ODBC stage?
To answer your question the DRS stage should be faster then the ODBC stage as it uses native database connectivity. You will need to install and configure the required database clients on your Data Stage server for it to work.
Dynamic Relational Stage was leveraged for People soft to have a job to run on any of the supported databases. It supports ODBC connections too. Read more of that in the plug-in documentation.
ODBC uses the ODBC driver for a particular database, DRS is a stage that tries to make it seamless for switching from one database to another. It uses the native connectivities for the chosen target...
63. How to use rank & update strategy in data stage?
Don't mix informatica with Data stage.
In Data stage, we don’t have such kind of stages.
64. What is the max capacity of Hash file in Data Stage?
Take a look at the uvconfig file:
# 64BIT_FILES - This sets the default mode used to
# create static hashed and dynamic files.
# A value of 0 results in the creation of 32-bit
# files. 32-bit files have a maximum file size of
# 2 gigabytes. A value of 1 results in the creation
# of 64-bit files (ONLY valid on 64-bit capable platforms).
# The maximum file size for 64-bit
# files is system dependent. The default behavior
# may be overridden by keywords on certain commands.
64BIT_FILES 0
65. How to implement type2 slowly changing dimensions in data stage? Explain with example?
We can handle SCD in the following ways
Type 1: Just use, Insert rows Else Update rows? Or? Update rows Else Insert rows? in update action of target
Type 2: Use the steps as follows
a) U have use one hash file to Look-Up the target
b) Take 3 instances of target
c) Give different conditions depending on the process
d) Give different update actions in target
e) Use system variables like Sysdate and Null.
66. What is merge and how it can be done please explain with simple example taking 2 tables .......?
Merge is used to join two tables. It takes the Key columns sort them in Ascending or descending order. Let us consider two table i.e. Emp, Dept. If we want to join these two tables we are having Deptno as a common Key so we can give that column name as key and sort Deptno in ascending order and can join those two tables
67. How can ETL excel file to Data mart?
Open the ODBC Data Source Administrator found in the controlpanel/administrative tools. Under the system DSN tab, add the Driver to Microsoft Excel.
Then you'll be able to access the XLS file from Data stage.
68. How do we do the automation of ds jobs?
We can call Data stage Batch Job from Command prompt using 'dsjob'. We can also pass all the parameters from command prompt. Then call this shell script in any of the market available schedulers. The 2nd option is schedule these jobs using Data Stage director.
69. What are constraints and derivation?
Explain the process of taking backup in Data Stage?
What are the different types of lookups available in Data Stage?
Constraints are used to check for a condition and filter the data. Example: Cust_Id<>0 is set as a constraint and it means and only those records meeting this will be processed further. Derivation is a method of deriving the fields, for example if you need to get some SUM,AVG etc.
70. How does Data Stage handle the user security?
We have to create users in the Administrators and give the necessary privileges to users.
What is meaning of file extender in data stage server jobs.
can we run the data stage job from one job to another job that file data where it is stored and what is the file extender in ds jobs.
71. What is meaning of file extender in data stage server jobs.
can we run the data stage job from one job to another job that file data where it is stored and what is the file extender in ds jobs.
File extender means the adding the columns or records to the already existing the file, in the data stage, we can run the data stage job from one job to another job in data stage.
72. What is the difference between DRS and ODBC stage?
To answer your question the DRS stage should be faster then the ODBC stage as it uses native database connectivity. You will need to install and configure the required database clients on your Data Stage server for it to work.
Dynamic Relational Stage was leveraged for PeopleSoft to have a job to run on any of the supported databases. It supports ODBC connections too. Read more of that in the plug-in documentation.
ODBC uses the ODBC driver for a particular database, DRS is a stage that tries to make it seamless for switching from one database to another. It uses the native connectivities for the chosen target
73. How to use rank and update strategy in data stage?
Don't mix informatica with Data stage.
In Data stage, we don’t have such kind of stages.
74. What is the max capacity of Hash file in Data Stage?
Take a look at the uvconfig file:
# 64BIT_FILES - This sets the default mode used to
# create static hashed and dynamic files.
# A value of 0 results in the creation of 32-bit
# files. 32-bit files have a maximum file size of
# 2 gigabytes. A value of 1 results in the creation
# of 64-bit files (ONLY valid on 64-bit capable platforms).
# The maximum file size for 64-bit
# files is system dependent. The default behavior
# may be overridden by keywords on certain commands.
64BIT_FILES 0
75. How to implement type2 slowly changing dimensions in data stage? Explain with example?
We can handle SCD in the following ways
Type 1: Just use, Insert rows Else Update rows?
Or ? Update rows Else Insert rows?, in update action of target
Type 2: Use the steps as follows
a) U have use one hash file to Look-Up the target
b) Take 3 instances of target
c) Give different conditions depending on the process
d) Give different update actions in target
e) Use system variables like Sysdate and Null.
76. How can we improve the performance of Data Stage?
Trun in-process buffer and transaction size
77. What is data set? and what is file set?
File set:- It allows you to read data from or write data to a file set. The stage can have a single input link. a single output link, and a single rejects link. It only executes in parallel mode The data files and the file that lists them are called a file set. This capability is useful because some operating systems impose a 2 GB limit on the size of a file and you need to distribute files among nodes to prevent overruns.
Datasets r used to import the data in parallel jobs like ODBC in server jobs
78. How the hash file is doing lookup in server jobs? How is it comparing the key values?
Hashed File is used for two purposes: 1. Remove Duplicate Records 2. Then Used for reference lookups. The hashed file contains 3 parts: Each record having Hashed Key, Key Header and Data portion. By using hashed algorithm and the key valued the lookup is faster.
79. What are orabulk and bcp stages?
ORABULK is used to load bulk data into single table of target oracle database.
BCP is used to load bulk data into a single table for Microsoft sql server and Sybase.
80. How is data stage 4.0 functionally different from the enterprise edition now?? what are the exact changes?
There are lot of Changes in DS EE. CDC Stage, Procedure Stage, Etc..........
81. Data Stage from Staging to MDW is only running at 1 row per second! What do we do to emedy?
I am assuming that there are too many stages, which is causing problem and providing the solution.
In general. if you too many stages (especially transformers , hash look up), there would be a lot of overhead and the performance would degrade drastically. I would suggest you to write a query instead of doing several look ups. It seems as though embarrassing to have a tool and still write a query but that is best at times.
If there are too many look ups that are being done, ensure that you have appropriate indexes while querying. If you do not want to write the query and use intermediate stages, ensure that you use proper elimination of data between stages so that data volumes do not cause overhead. So, there might be a re-ordering of stages needed for good performance.
Other things in general that could be looked in:
1) For massive transaction set hashing size and buffer size to appropriate values to perform as much as possible in memory and there is no I/O overhead to disk.
2) Enable row buffering and set appropriate size for row buffering
3) It is important to use appropriate objects between stages for performance
What user variable activity when it used how it used! Where it is used with real example
By using This User variable activity we can create some variables in the job sequence, this variables r available for all the activities in that sequence.
Most probably this activity is @ starting of the job sequence
82. How can I specify a filter command for processing data while defining sequential file output data?
We have some thing called as after job subroutine and before subroutine, with then we can execute the UNIX commands.
Here we can use the sort summand or the filter CD command
83. What are validations you perform after creating jobs in designer? What r the different type of errors u faced during loading and how u solves them?
Check for Parameters.
And check for input files are existed or not and also check for input tables existed or not and also usernames, data source names, passwords like that
84. What is the difference between Data stage and Data stage TX?
Its a critical question to answer, but one thing i can tell u that Data stage Tx is not a ETL tool & this is not a new version of Data stage 7.5.
Tx is used for ODS source, this much i know
DS TX is not a ETL tool. It comes in a category of EAI. TX helps in connecting various frontend to diff back ends. it has a inbuilt plug-in for many diff sources and targets. In short it can make ur application platform independent. It can also parse data.
85. If you are running your Parallel Job on 4 node configuration file and you have 10 stages on the canvas, how many processes does data stage creates?
Answer is 40 [considering number of stages and the player processes]
--You have 10 stages and each stage can be partitioned and run on 4 nodes which makes total number of processes generated are 40
Note: It may vary based on the operator combinability.
86. Does Enterprise Edition only add the parallel processing for better performance?
Are any stages/transformations available in the enterprise edition only?
Data Stage Standard Edition was previously called Data Stage and Data Stage Server Edition. Data Stage Enterprise Edition was originally called Orchestrate, and then renamed to Parallel Extender when purchased by Acential. Data Stage Enterprise: Server jobs, sequence jobs, parallel jobs. The enterprise edition offers parallel processing features for scalable high volume solutions. Designed originally for UNIX, it now supports Windows, Linux and Unix System Services on mainframes. ? Data Stage Enterprise MVS: Server jobs, sequence jobs, parallel jobs, mvs jobs. MVS jobs are jobs designed using an alternative set of stages that are generated into cobol/JCL code and are transferred to a mainframe to be compiled and run. Jobs are developed on a Unix or Windows server transferred to the mainframe to be compiled and run. The first two versions share the same Designer interface but have a different set of design stages depending on the type of job you are working on. Parallel jobs have parallel stages but also accept some server stages via a container. Server jobs only accept server stages, MVS jobs only accept MVS stages. There are some stages that are common to all types (such as aggregation) but they tend to have different fields and options within that stage.
87. How can you implement Complex Jobs in data stage?
Complex design means having more joins and more look ups. Then that job design will be called as complex job. We can easily implement any complex design in Data Stage by following simple tips in terms of increasing performance also. There is no limitation of using stages in a job. For better performance, Use at the Max of 20 stages in each job. If it is exceeding 20 stages then go for another job. Use not more than 7 look ups for a transformer otherwise go for including one more transformer.
88. How can u implement slowly changed dimensions in data stage? Explain?
2) can u join flat file and database in data stage? How?
Yes, we can do it in an indirect way. First create a job which can populate the data from database into a Sequential file and name it as Seq_First1. Take the flat file which you are having and use a Merge Stage to join the two files. You have various join types in Merge Stage like Pure Inner Join, Left Outer Join, Right Outer Join etc., You can use any one of these which suits your requirements.
89. How to implement routines in data stage?
There are 3 kind of routines is there in Data stage.
1. Server routines which will used in server jobs. These routines will write in BASIC Language
2. Parallel routines which will use in parallel jobs. These routines will write in C/C++ Language
3. Mainframe routines which will used in mainframe jobs
90. What is difference between server jobs & parallel jobs?
Server jobs: These are available if you have installed Data Stage Server. They run on the Data Stage Server, connecting to other data sources as necessary.
Parallel jobs: These are only available if you have installed Enterprise Edition. These run on Data Stage servers that are SMP, MPP, or cluster systems. They can also run on a separate z/OS (USS) machine if required.
In server job sequence we can run the jobs sequentially only. That’s one after another.
But in Parallel sequence we can run it parallel if there is no dependency b/w jobs.
91. How we use NLS function in Data stage? What are advantages of NLS function? Where we can use that one? Explain briefly?
By using NLS function we can do the following
- Process the data in a wide range of languages
- Use Local formats for dates, times and money
- Sort the data according to the local rules
If NLS is installed, various extra features appear in the product.
For Server jobs, NLS is implemented in Data Stage Server engine
For Parallel jobs, NLS is implemented using the ICU library.
92. What happens out put of hash file is connected to transformer ..? What error it thoughts?
If Hash file output is connected to transformer stage the hash file will consider as the Lookup file if there is no primary link to the same Transformer stage, if there is no primary link then this will treat as primary link itself. you can do SCD in server job by using Lookup functionality. This will not return any error code.
93. What is version Control?
Version Control stores different versions of DS jobs, run different versions of same job
Reverts to previous version of a job view version histories
94. What is ' inserting for update ' in data stage?
I think 'insert to update' is updated value is inserted to maintain history
95. How can we pass parameters to job by using file?
You can do this, by passing parameters from UNIX file, and then calling the execution of a data stage job. the ds job has the parameters defined (which are passed by Unix)
96. Where does Unix script of data stage executes weather in client machine or in server. Suppose if it executes on server then it will execute?
Data stage jobs are executed in the server machines only. There is nothing that is stored in the client machine.
97. Defaults nodes for data stage parallel Edition?
Actually the Number of Nodes depends on the number of processors in your system. If your system is supporting two processors we will get two nodes by default.
98. How do you merge two files in DS?
Either used Copy command as a Before-job subroutine if the metadata of the 2 files are same or created a job to concatenate the 2 files into one if the metadata is different.
99. What is DS Director used for - did u use it?
Data stage Director is GUI to monitor, run, validate & schedule data stage server jobs.
100. What are types of Hashed File?
Hashed File is classified broadly into 2 types.
a) Static - Sub divided into 17 types based on Primary Key Pattern.
b) Dynamic - sub divided into 2 types
i) Generic
ii) Specific.
Default Hashed file is "Dynamic - Type30.
101. How do you pass filename as the parameter for a job?
1. Go to Data Stage Administrator->Projects->Properties->Environment->UserDefined. Here you can see a grid, where you can enter your parameter name and the corresponding the path of the file.
2. Go to the stage Tab of the job, select the NLS tab, click on the "Use Job Parameter" and select the parameter name which you have given in the above. The selected parameter name appears in the text box beside the "Use Job Parameter" button. Copy the parameter name from the text box and use it in your job. Keep the project default in the text box.
102. How can we create Containers?
There are two types of containers
1. Local Container
2. Shared Container
Local container is available for that particular Job only.
Where as Shared Containers can be used anywhere in the project.
Local container:
Step1:Select the stages required
Step2:Edit>ConstructContainer>Local
Shared Container:
Step1:Select the stages required
Step2:Edit>ConstructContainer>Shared
Shared containers are stored in the SharedContainers branch of the Tree Structure
103. How can we improve the performance of Data Stage jobs?
Performance and tuning of DS jobs:
1. Establish Baselines
2. Avoid the Use of only one flow for tuning/performance testing
3. Work in increment
4. Evaluate data skew
5. Isolate and solve
6. Distribute file systems to eliminate bottlenecks
7. Do not involve the RDBMS in initial testing
8. Understand and evaluate the tuning knobs available.
125. What are the Job parameters?
These Parameters are used to provide Administrative access and change run time values of the job.
EDIT>JOBPARAMETERS
In that Parameters Tab we can define the name, prompt, type, value
104. What are all the third party tools used in Data Stage?
Autosys, TNG, event coordinator are some of them that I know and worked with
105. How can we implement Lookup in Data Stage Server jobs?
The DB2 stage can be used for lookups.
In the Enterprise Edition, the Lookup stage can be used for doing lookups.
We can implement lookups in server jobs using Hash files and Transformer stage. Lookup stages are available in parallel jobs only.
106. What is the utility you use to schedule the jobs on a UNIX server other than using Ascential Director?
"AUTOSYS": Through autosys u can automate the job by invoking the shell script written to schedule the data stage jobs.
107. What are Sequencers?
A sequencer allows you to synchronize the control flow of multiple activities in a job sequence. It can have multiple input triggers as well as multiple output triggers. The sequencer operates in two modes: ALL modes. In this mode all of the inputs to the sequencer must be TRUE for any of the sequencer outputs to fire. ANY mode, in this mode, output triggers can be fired if any of the sequencer inputs are TRUE
108. Explain the differences between Oracle8i/9i?
Mutli processing, databases more dimensional modeling
109. What r XML files and how do you read data from XML files and what stage to be used?
In the pallet there is Real time stages like xml-input, xml-output, xml-transformer
110 How do you pass the parameter to the job sequence if the job is running at night?
Two ways
1. Ste the default values of Parameters in the Job Sequencer and map these parameters to job.
2. Run the job in the sequencer using dsjobs utility where we can specify the values to be taken for each parameter.
111. How do you remove duplicates without using remove duplicate stage?
In the target make the column as the key column and run the job.
hi , by using sort stage we remove duplicates as making options allow duplicates= false.
112. What is the flow of loading data into fact & dimensional tables?
Here is the sequence of loading a data warehouse.
1. The source data is first loading into the staging area, where data cleansing takes place.
2. The data from staging area is then loaded into dimensions/lookups.
3.Finally the Fact tables are loaded from the corresponding source tables from the staging area.
113. what is the difference between DNS DHCP ?
File system of windows? compare with nis & dns
No answer available currently. Be the first one to reply to this question by submitting your answer from the form below.
114. Functionality of Link Partioner and Link Collector?
server jobs mainly execute the jobs in sequential fashion,the ipc stage as well as link partioner and link collector will simulate the parllel mode of execution over the sever jobs having single cpu Link Partitioner : It receives data on a single input link and diverts the data to a maximum no.of 64 output links and the data processed by the same stage having same meta dataLink Collector : It will collects the data from 64 inputlinks, merges it into a single data flowand loads to target. these both r active stagesand the design and mode of execution of serverjobs has to be decidead by the designer
Link Partitioner : It actually splits data into various partitions or data flows using various
partition methods .
Link Collector : It collects the data coming from partitions, merges it into a single data flow and loads to target.
Containers : Usage and Types?
Container is a collection of stages used for the purpose of Reusability. There are 2 types of Containers.
a) Local Container: Job Specific
b) Shared Container: Used in any job within a project. ?
There are two types of shared container:?
1.Server shared container. Used in server jobs (can also be used in parallel jobs).?
2.Parallel shared container. Used in parallel jobs. You can also include server shared containers in parallel jobs as a way of incorporating server job functionality into a parallel stage (for example, you could use one to make a server plug-in stage available to a parallel job).regards jagan
115. What are Stage Variables, Derivations and Constants?
Stage Variable - An intermediate processing variable that retains value during read and doesnt pass the value into target column.
Derivation - Expression that specifies value to be passed on to the target column.
Constant - Conditions that are either true or false that specifies flow of data with a link.
In datastage transformer stage can mention Constraint to columns deviate the output to desired manner not constant
116. How to improve the performance of hash file?
You can improve performance of hashed file by
1 .Preloading hash file into memory --this can be done by enabling preloading options in hash file output stage
2. Write caching options --.It makes data written into cache before being flushed to disk. you can enable this to ensure that hash files are written in order onto cash before flushed to disk instead of order in which individual rows are written
3 .Preallocating-- Estimating the approx size of the hash file so that file needs not to be splitted to often after write operation
117. What does separation option in static hash-file mean?
The different hashing algorithms are designed to distribute records evenly among the groups of the file based on characters and their position in the record ids.
When a hashed file is created, Separation and Modulo respectively specifies the group buffer size and the number of buffers allocated for a file. When a Static Hash file is created, DATASTAGE creates a file that contains the number of groups specified by modulo.
Size of Hash file = modulus(no. groups) * Separations (buffer size)
118. How to remove duplicates in server job?
1) Use a hashed file stage or
2) If you use sort command in UNIX(before job sub-routine), you can reject duplicated records using -u parameter or
3) using a Sort stage
119. If I add a new environment variable in Windows, how can I access it in Data Stage?
U can view all the environment variables in designer. U can check it in Job properties. U can add and access the environment variables from Job properties
120. There are three different types of user-created stages available for PX.
What are they? Which would you use? What are the disadvantage for using each type?
These are the three different stages:
i) Custom ii) Build iii) Wrapped
121. I want to process 3 files in sequentially one by one, how can I do that? While processing the files it should fetch files automatically?
If the metadata for all the files r same then create a job having file name as parameter, then use same job in routine and call the job with different file name...or u can create sequencer to use the job...
I think in datastage8.0.1 there is an option in the sequence job namely loop via which the purpose can be achieved
122. What is difference between data stage and informatica?
Here is a very good articles on these differences... whic hhelps to get an idea.. Basically it's depends on what you are tring to accomplish
123. What are the requirements for your ETL tool? Do you have large sequential files (1 million rows, for example) that need to be compared every day versus yesterday?
If so, then ask how each vendor would do that. Think about what process they are going to do. Are they requiring you to load yesterdays file into a table and do lookups?
If so, RUN!! Are they doing a match/merge routine that knows how to process this in sequential files? Then maybe they are the right one. It all depends on what you need the ETL to do. If you are small enough in your data sets, then either would probably be OK.
124. Whets difference between operational data stage (ODS) & data warehouse?
A data warehouse is a decision support database for organizational needs. It is subject oriented, on volatile, integrated and time variant collect of data.
ODS (Operational Data Source) is a integrated collection of related information. It contains maximum 90 days information.
125. What is iconv and oconv functions?
Iconv( )-----converts string to internal storage formatOconv( )----converts an expression to an output format
126. What will you in a situation where somebody wants to send you a file and use that file as an input or reference and then run job?
A. Under Windows: Use the 'WaitForFileActivity' under the Sequencers and then run the job. May be you can schedule the sequencer around the time the file is expected to arrive.
B. Under UNIX: Poll for the file. Once the file has start the job or sequencer depending on the file.
127. Did you work in UNIX environment?
Some times u need to write Unix programs in back round like batch programs ! Because data stage can invoke a batch processing in every 24 hrs. so.......Unix must... so that we can run the Unix program, in back round even min/ hrs
128. What is Hash file stage and what is it used for?
We can also use the Hash File stage to avoid / remove duplicate rows by specifying the hash key on a particular filled
129. Did you Parameterize the job or hard-coded the values in the jobs?
Always parameterized the job. Either the values are coming from Job Properties or from a ?Parameter Manager? ? a third part tool. There is no way you will hard? Code some parameters in your jobs. The often Parameterized variables in a job are: DB DSN name, username, password, dates W.R.T for the data to be looked against it.
130. Suppose if there are million records did you use OCI? if not then what stage do you prefer?
Using Orabulk
131. What are environment variables? What is the use of this?
Basically Environment variable is predefined variable those we can use while creating DS job. We can set either as Project level or Job level. Once we set specific variable that variable will be available into the project/job.
We can also define new environment variable. For that we can got to DS Admin .
132. What is data set? And what is file set?
File set:- It allows you to read data from or write data to a file set. The stage can have a single input link. a single output link, and a single rejects link. It only executes in parallel modeThe data files and the file that lists them are called a file set. This capability is useful because some operating systems impose a 2 GB limit on the size of a file and you need to distribute files among nodes to prevent overruns.
Datasets r used to import the data in parallel jobs like odbc in server jobs
133. How the hash file is doing lookup in server jobs? How is it comparing the key values?
Hashed File is used for two purpose: 1. Remove Duplicate Records 2. Then Used for reference lookups. The hashed file contains 3 parts: Each record having Hashed Key, Key Header and Data portion. By using hashed algorithm and the key valued the lookup is faster.
134. What are orabulk and bcp stages?
ORABULK is used to load bulk data into single table of target oracle database.
BCP is used to load bulk data into a single table for Microsoft sql server and Sybase.
135. How is datastage 4.0 functionally different from the enterprise edition now?? what are the exact changes?
There are lot of Changes in DS EE. CDC Stage, Procedure Stage, Etc..........
136. Data Stage from Staging to MDW is only running at 1 row per second! What do we do to remedy?
I am assuming that there are too many stages, which is causing problem and providing the solution.
In general. if you too many stages (especially transformers , hash look up), there would be a lot of overhead and the performance would degrade drastically. I would suggest you to write a query instead of doing several look ups. It seems as though embarrassing to have a tool and still write a query but that is best at times. If there are too many look ups that are being done, ensure that you have appropriate indexes while querying. If you do not want to write the query and use intermediate stages, ensure that you use proper elimination of data between stages so that data volumes do not cause overhead. So, there might be a re-ordering of stages needed for good performance.
Other things in general that could be looked in:
1) for massive transaction set hashing size and buffer size to appropriate values to perform as much as possible in memory and there is no I/O overhead to disk.
2) Enable row buffering and set appropriate size for row buffering
3) It is important to use appropriate objects between stages for performance
137. What user variable activity when it used how it used! Where it is used with real example?
By using This User variable activity we can create some variables in the job sequence, this variables r available for all the activities in that sequence.
Most probably this activity is @ starting of the job sequence
138. How can I specify a filter command for processing data while defining sequential file output data?
We have some thing called as after job subroutine and before subroutine, with then we can execute the Unix commands.
Here we can use the sort summand or the filter cdommand
139. What is the difference between Data stage and Data stage TX?
Its a critical question to answer, but one thing i can tell u that Data stage Tx is not a ETL tool & this is not a new version of Data stage 7.5.
Tx is used for ODS source ,this much i know
DS TX is not a ETL tool. It comes in a category of EAI.
TX helps in connecting varios frontend to diff backends.it has a inbuilt plugin for many diff sources and targets.In short it can make ur application platform independent.It can also parse data.
140. If your running 4 ways parallel and you have 10 stages on the canvas, how many processes does data stage create?
Answer is 40
You have 10 stages and each stage can be partitioned and run on 4 nodes which makes total number of processes generated are 40
141. Does Enterprise Edition only add the parallel processing for better performance?
Are any stages/transformations available in the enterprise edition only?
? Data Stage Standard Edition was previously called Data Stage and Data Stage Server Edition. ? Data Stage Enterprise Edition was originally called Orchestrate, then renamed to Parallel Extender when purchased by Ascential. ? DataStage Enterprise: Server jobs, sequence jobs, parallel jobs. The enterprise edition offers parallel processing features for scalable high volume solutions. Designed originally for Unix, it now supports Windows, Linux and Unix System Services on mainframes. ? Data Stage Enterprise MVS: Server jobs, sequence jobs, parallel jobs, mvs jobs. MVS jobs are jobs designed using an alternative set of stages that are generated into Cobol/JCL code and are transferred to a mainframe to be compiled and run. Jobs are developed on a Unix or Windows server transferred to the mainframe to be compiled and run. The first two versions share the same Designer interface but have a different set of design stages depending on the type of job you are working on. Parallel jobs have parallel stages but also accept some server stages via a container. Server jobs only accept server stages, MVS jobs only accept MVS stages. There are some stages that are common to all types (such as aggregation) but they tend to have different fields and options within that stage.
142. How can you implement Complex Jobs in data stage?
Complex design means having more joins and more look ups. Then that job design will be called as complex job. We can easily implement any complex design in DataStage by following simple tips in terms of increasing performance also. There is no limitation of using stages in a job. For better performance, Use at the Max of 20 stages in each job. If it is exceeding 20 stages then go for another job.Use not more than 7 look ups for a transformer otherwise go for including one more transformer.
143. How can u implement slowly changed dimensions in data stage? Explain?
2) can u join flat file and database in data stage? How?
Yes, we can do it in an indirect way. First create a job which can populate the data from database into a Sequencial file and name it as Seq_First1. Take theflat file which you are having and use a Merge Stage to join the two files. You have various join types in Merge Stage like Pure Inner Join, Left Outer Join, Right Outer Join etc., You can use any one of these which suits your requirements.
144. I want to process 3 files in sequentially one by one , how can i do that. while processing the files it should fetch files automatically?
If the metadata for all the files r same then create a job having file name as parameter, then use same job in routine and call the job with different file name...or u can create sequencer to use the job...
I think in datastage8.0.1 there is an option in the sequence job namely loop via which the purpose can be achieved
145. What is difference between server jobs & parallel jobs?
Here is the diff
Server jobs: These are available if you have installed Data Stage Server. They run on the Data Stage Server, connecting to other data sources as necessary.
Parallel jobs: These are only available if you have installed Enterprise Edition. These run on Data Stage servers that are SMP, MPP, or cluster systems. They can also run on a separate z/OS (USS) machine if required.
In server job sequence we can run the jobs sequentially only. That’s one after another.
But in Parallel sequence we can run it parallel if there are no dependency b/w jobs.
146. How we use NLS function in Data stage? What are advantages of NLS function? where we can use that one? Explain briefly?
By using NLS function we can do the following
- Process the data in a wide range of languages
- Use Local formats for dates, times and money
- Sort the data according to the local rules
If NLS is installed, various extra features appear in the product.
For Server jobs, NLS is implemented in Data Stage Server engine
For Parallel jobs, NLS is implemented using the ICU library.
147. What happens out put of hash file is connected to transformer .. What error it troughs?
If Hash file output is connected to transformer stage the hash file will consider as the Lookup file if there is no primary link to the same Transformer stage, if there is no primary link then this will treat as primary link itself. you can do SCD in server job by using Lookup functionality. This will not return any error code.
148. What is ' insert for update ' in data stage?
I think 'insert to update' is updated value is inserted to maintain history
149. How can we pass parameters to job by using file.?
You can do this, by passing parameters from Unix file, and then calling the execution of a data stage job. the ds job has the parameters defined (which are passed by Unix)
150. Defaults nodes for data stage parallel Edition?
Actually the Number of Nodes depend on the number of processors in your system. If your system is supporting two processors we will get two nodes by default.
151. How can we implement Lookup in Data Stage Server jobs?
The DB2 stage can be used for lookups.
In the Enterprise Edition, the Lookup stage can be used for doing lookups.
We can implement lookups in server jobs using Hash files and Transformer stage. Lookup stages are available in parallel jobs only.
152. What is the utility you use to schedule the jobs on a UNIX server other than using Ascential Director?
"AUTOSYS": Thru autosys u can automate the job by invoking the shell script written to schedule the datastage jobs.
153. what is the difference between DNS DHCP ?
file system of windows?
compare with nis & dns
No answer available currently. Be the first one to reply to this question by submitting your answer from the form below.
154. Functionality of Link Partitioner and Link Collector?
server jobs mainly execute the jobs in sequential fashion,the ipc stage as well as link partioner and link collector will simulate the parllel mode of execution over the sever jobs having single cpu Link Partitioner : It receives data on a single input link and diverts the data to a maximum no.of 64 output links and the data processed by the same stage having same meta dataLink Collector : It will collects the data from 64 inputlinks, merges it into a single data flowand loads to target. these both r active stagesand the design and mode of execution of serverjobs has to be decidead by the designer
Link Partitioner : It actually splits data into various partitions or data flows using various
partition methods .
Link Collector : It collects the data coming from partitions, merges it into a single data flow and loads to target.
Containers : Usage and Types?
Container is a collection of stages used for the purpose of Reusability. There are 2 types of Containers.
a) Local Container: Job Specific
b) Shared Container: Used in any job within a project. ?
There are two types of shared container:?
1.Server shared container. Used in server jobs (can also be used in parallel jobs).?
2.Parallel shared container. Used in parallel jobs. You can also include server shared containers in parallel jobs as a way of incorporating server job functionality into a parallel stage (for example, you could use one to make a server plug-in stage available to a parallel job).
