-
What are the Environmental variables in Datastage?
Sushil
- Aug 19th, 2013
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
glaciya
- Dec 12th, 2011
The Environmental variables in datastage are some pathes which can support system can use as shortcuts to fulfill the program running instead of doing nonsense activity. In most time, environmental variables are defined when the software have been installed or being installed.
-
Job based scenario
TABLE T1 with c1 and Table T2 with c1
1 3
2 4
2 5
3
4
5
...Dinesh
- Aug 19th, 2011
Use lookup stage...and choose reject option !
you will get the matched records in master output file(3,4,5,5) and unmatched in reject file(1,2,2,6,7). -
How did you handle an 'Aborted' sequencer?
In almost all cases we have to delete the data inserted by this from DB manually and fix the job and then run the job again.
Read Best Answer
Editorial / Best Answer
ritu singhai- Member Since Jul-2008 | Jul 5th, 2008
Have you set the compilation options for the sequence so that in case job aborts, you need not to run it from from the first job. By selecting that compilation option you can run that aborted sequence from the point the sequence was aborted.
Like for example, you have 10 jobs(job1, job2, job3 etc.) in a sequence and the job 5 aborts, then by checking "Add checkpoints so sequence is restartable on failure" and "Automatically handle activities that fail" you can restart this sequence from job 5 only. it will not run the jobs 1,2,3 and 4.
Please check these options in your sequence.
Hope this helps.Mallikarjuna_G
- Aug 10th, 2013
Two things needs to be handled when a Job Sequence aborts. 1. We must have an exception handling code that notifies us about the failure 2. When we re-run the sequence after fixing the Sequence, it ...
Riten
- May 30th, 2012
To Handle Aborted Sequence These are the steps to be taken: First Add Terminator to Job Sequence and Choose Terminator with Other option(Trigger in Job sequence) ( if job fails it will go to Other lin...
-
What is the purpose of the copy stage:
What is the uses of the copy stage with out copies input link to output link datasetsand it can have any purposes pls send me with example
Mallikarjuna_G
- Aug 10th, 2013
Use Copy stage for simple operations like :
to multicast the same input data among multiple output links.
placeholder between stages to avoid buffer issues
renaming Columns
dropping Columns
implicit (default) type ConversionsAkila
- Apr 1st, 2013
Besides used for making copies of the input, copy stage is also helpful for achieving 1) dropping columns between stages 2) to change column names 3) if the scenario is to end the job flow directly f...
-
Which partition is best in performance level?
Mallikarjuna_G
- Aug 10th, 2013
It depends on the job design and requrement. When you specify SAME partitioning, datastage uses the partioing method defined in the previous stage and will not perform any partitioning in the current...
Muralidhar
- May 30th, 2013
Same partition.
The reason is it keeps the previous partition and send to output as it is. -
No of processors in DataStage
How do we know the no of processors in a job? Is there any specific calculation for this.
Mallikarjuna_G
- Aug 10th, 2013
There will not be any processors in the job. Your question could be - find the number of nodes/processors on which the job is running. So the answer to this - go to Director and open the log for the ...
Kumaresh
- Jul 19th, 2013
Run the job. Go to director log. Check for the apt config data displayed over there. That will show the number of processors/nodes.
-
Dimension Modelling types along with their significance
Data Modelling is Broadly classified into 2 types. a) E-R Diagrams (Entity - Relatioships). b) Dimensional Modelling.
BHANU
- Jul 9th, 2013
Dimensional modeling is a technique for conceptualizing and visualizing the data there are two types of dimensional modeling
1.Snowflake schema
2.Star schema
cyberdiptikanta
- Jun 26th, 2010
Dimensional Modelling:It is a designing methodlogy for designing datawarehouse with dimensions& facts.It is of three types 1) Conceptual Modelling 2) Logical Modelling 3) Physical Modelling1) Conc...
-
-
Join and Lookup Stage
If you have a huge volume of data to be referenced, which stage will you use? Join or Lookup stage? Why
-
User Created Stages
Explain Wrapped, Build and Custom stages?
Phuzzball
- Feb 15th, 2012
Datastage allows you to create your own stages with custom properties. There are three types of custom stages: Custom: This is Orchestrate based custom stage. Orchestrate operators are used for def...
MArcus_Datastage
- Mar 17th, 2009
Custom: This allows knowledgeable Orchestrate users to specifyan Orchestrate operator as a DataStage stage. This is then availableto use in DataStage Parallel jobs.Build: This allows you to design and...
-
How did u connect with DB2 in your last project?
Most of the times the data was sent to us in the form of flat files. The data is dumped and sent to us. In some cases were we need to connect to DB2 for look-ups as an instance then we used ODBC drivers to connect to DB2 (or) DB2-UDB depending the situation and availability. Certainly DB2-UDB is better in terms of performance as you know the native drivers are always better than ODBC drivers. 'iSeries...
Read Best Answer
Editorial / Best Answer
nikhilanshuman- Member Since Nov-2009 | Dec 17th, 2010
By making use of "DB2/UDB Stage".There we basically need to specify connection parameters like client instance name,default server,default database.
nikhilanshuman
- Dec 17th, 2010
By making use of "DB2/UDB Stage".There we basically need to specify connection parameters like client instance name,default server,default database.
-
What are other Performance tunings you have done in your last project to increase the performance of slowly running jobs?
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 Tunables' in Administrator for better performance.Used sorted data for Aggregator.Sorted...
Read Best Answer
Editorial / Best Answer
sistlasatish- Member Since Nov-2005 | Nov 14th, 2005
- Minimise the usage of Transformer (Instead of this use Copy, modify, Filter, Row Generator)
- Use SQL Code while extracting the data
- Handle the nulls
- Minimise 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
- Gen\'ll 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 writting 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 Tunables' 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.
- 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.
sistlasatish
- Nov 14th, 2005
Minimise the usage of Transformer (Instead of this use Copy, modify, Filter, Row Generator)Use SQL Code while extracting the dataHandle the nullsMinimise the warningsReduce the number of lookups in a ...
-
What are OConv () and Iconv () functions and where are they used?
IConv() - Converts a string to an internal storage formatOConv() - Converts an expression to an output format.
Read Best Answer
Editorial / Best Answer
sekr- Member Since Dec-2005 | Jan 30th, 2006
iconv is used to convert the date into into internal format i.e only datastage can understand
example :- date comming in mm/dd/yyyy format
datasatge will conver this ur date into some number like :- 740
u can use this 740 in derive in ur own format by using oconv.
suppose u want to change mm/dd/yyyy to dd/mm/yyyy
now u will use iconv and oconv.
ocnv(iconv(datecommingfromi/pstring,SOMEXYZ(seein help which is iconvformat),defineoconvformat))
sekr
- Jan 30th, 2006
iconv is used to convert the date into into internal format i.e only datastage can understand example :- date comming in mm/dd/yyyy format datasatge will conver this ur date into some number like :- 7...
-
How did you connect to DB2 in your last project?
Read Best Answer
Editorial / Best Answer
gagan8877- Member Since May-2007 | May 8th, 2007
The following stages can connect to DB2 Database:
ODBC
DB2 Plug-in Stage
Dynamic Relational Stagegagan8877
- May 8th, 2007
The following stages can connect to DB2 Database:ODBCDB2 Plug-in StageDynamic Relational Stage
-
What is Metastage?
Read Best Answer
Editorial / Best Answer
spartankiya- Member Since Feb-2007 | Feb 19th, 2007
MetaStage is a persistent metadata Directory that uniquely synchronizes metadata across multiple separate silos, eliminating re keying and the manual establishment of cross-tool relationships. Based on patented technology, it provides seamless cross-tool integration throughout the entire Business Intelligence and data integration life cycle and tool sets
spartankiya
- Feb 19th, 2007
MetaStage is a persistent metadata Directory that uniquely synchronizes metadata across multiple separate silos, eliminating re keying and the manual establishment of cross-tool relationships. Based o...
-
Reading multiple files through sequential file
What are the ways to read multiiple files from sequentila file if the both files are differnt
-
Explain about Error Handling in Datastage and best practices.
madhavsandireddi
- Feb 25th, 2011
In sequence job we have to run a job and if a job is aborted and if we do not have terminator activity then control goes to the exception handler where it checks for what reason the job is aborted..ThanksMadhav
nikhilanshuman
- Dec 29th, 2009
In datastage sequence,there is "Exception Handler" activity.When you are calling your jobs from a Datastage Sequence you should do following :Step 1: Go to properties of master sequence and ...
-
Flatfiles Validations
When we are extracting the flatfiles, What are the basic required validations?
nikhilanshuman
- Dec 9th, 2010
Folllowing are some common validations performed:a) Check for blank lines and remove them.b) Check the number of column in each row of the file.c) If there is a? trailer line in the flat file contain...
-
Datastage Lookup
By using lookup we join the data from more than one table, we can implement the same using join stage, then what is the need of Lookup stage especially in Datastage?
nikhilanshuman
- Dec 9th, 2010
Datastage lookup has some additional benefits over join stage :a) A lookup stage supports reject link which is not supported by join stage.b) Lookup stage has an option to fail the job if the lookup f...
-
Datastage versions difference
What is the difference between datastage 7.1 & 8.0.1 versions ?
shyam.kandi99
- Nov 9th, 2010
Diff B/W Data Stage version 7.5 and Version 8.0I ) in 7.5 >> 4 client components in 8.0 5Client ComponentsDS Designer Web ConsoleDS Director Information AnalyzerDS Manager DS DesignerDS Administ...
DataStage Interview Questions
Read Best Answer
Ans