-
How can we filter in sequential stage
dhora9999
- May 23rd, 2011
Ya you are right, If you do not know the answer please do not cut paste here. Please answer the questions promptly. do not confuse anybody..Thanks D!!
rajani
- Feb 21st, 2007
In the Filter option,u can use the UNIX command like this to filter the input records..head -3 input_file.dat
-
Improve performance using copy stage
How can we improve performance using copy stage
Datastage Etl
- May 13th, 2011
Copy stage is used to send the data to the multiple sources.?Here we can ?change the column names . Rather than using Transformer Stage where ever necessary , we can use the copy stage. Like this, c...
narra satish
- Mar 21st, 2011
By using copy stage we can increase the performance. In this stage we can do sorting, Removing unwanted columns
-
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...
-
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.
Venkat Poonati
- Mar 29th, 2011
1. fastname-----usually networkname,It is physicall node name,That stages use to open ...
aladap12
- Jun 26th, 2009
Biggest strength of Datastage PX is its configuration file.. Datastage engine learns about size and shape of the system by reading configuration file.It contains Nodes and resource available for running the job.
-
ETL Tools Lookup Stage
By using the Lookup stage we will combine the data from multiple table. By using join stage also we will combine the data from multiple tables. Then what is the need of Lookup stage?
Venkat Poonati
- Mar 29th, 2011
There are three things to consider here1.memory usage As with joiner fewer rows has to be in memory at any time.This is not with lookup2.treating of rows with unmatched keys Joiner w...
narra satish
- Mar 21st, 2011
Join and lookup is differ in terms of memory usage. Join is light weight than lookup stage. Join stage won't use much system resources. While performing lookup will used more system resources. In case of lookup it fetch the whole data from refernce link into RAM memory and then it perform lookup.
-
How can we generate a surrogate key in server/parallel jobs?
narra satish
- Mar 28th, 2011
Hi,By using transformer we can generate the servgate key.In stagevariablesand write the derviation.
neeraj82
- Jul 3rd, 2009
You can generate through system variable in transformer which is built-in name is keymgt. You don't have remember full name and in parallel you can generate through surrogate key generator.
-
Reading multiple files through sequential file
What are the ways to read multiiple files from sequentila file if the both files are differnt
-
Change File Attribute
How to change the attribute of a .dsx format file to make it read only?
narra satish
- Mar 7th, 2011
First import the job in .dsxformat and change the attributes ther 0-Editable1-Read onlyAnd import again and override it.
-
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 ...
-
What is the exact difference b/w LOOKUP stage , JOIN stage and MERGE stage?
madhavsandireddi
- Feb 24th, 2011
The major difference between LKP stage, Join stage, merge stage are LKP & JOIN: If the reference data in the LKP stage is huge when compared to the primary data. there is no reject requiremen...
Pavan Batchu
- Nov 12th, 2007
Lookup stage: 1.Can only return multiple matching rows from one reference.2. Can reject rows based on constraint.3. Can set failure.4. Does not need partitioned and sorted input. Merge stage: 1. Can...
-
Passing parameters from one job to another job using command prompt
How can we pass parameters from one job to another job by using command line prompt?plz send ans... thanks in advance.Alam
cevvavijay
- Feb 10th, 2011
We can pass parameter to a job using two ways .. using dsjob- command line or from a sequencer.Other way would be -You configure single parameter set ( version 8.0 onwards) and use the same in both th...
-
How to Create new user in Datastage 7.5
I want to create new user in datastage 7.5 I dont know how to do it?
manoj kumar ganji
- Jan 23rd, 2011
User creation in 7.5 is os dependant i.e.., you can make creation of users in OS. For example when you load DS on Windows XP, then create new user in user accounts in control panel.
tisha24
- Dec 13th, 2010
The user created in and for windows will be a user for datastage by default..You can't crete a user from datastage environment. If you want to create a user create through Control panel user.. The...
-
Job Unlock
How to unlock a job in Datastage 8.0?
sivakumar.datastagepx
- Jan 19th, 2011
$ps -EF$kill PIDBy using above 2 commands in unix ...then we can kill the jobotherwise tools-> cleanup resourses
svasu.r
- Aug 14th, 2010
If you want unlock the jobDS DIRECTOR----------->JOB TAB-------->Clean up on resourcesIn admin enable priviliges to director
-
OCI Stage
Why array list is used in Oracle OCI stage? Brief about Important Options in OCI Stage.
Mili Jon
- Jan 16th, 2011
Array size is a property of OCI stage which help to reduce the context switch between DataStage and Oracle database. We should keep this in mind that however it help us reducing the context switch but...
DSQuest
- May 7th, 2010
I Suppose it is called "Array Size"....Array Size is mainly used to increase the buffer during Write operations into a Oracle DB in a Server job using the Oracle OCI stage.This comes of grea...
-
What happens if the job fails at night?
Mili Jon
- Jan 16th, 2011
Job aborts and logs can be seen on Director client. However a notification can be set using the notification Stage to send emails during failure.
gagan8877
- May 8th, 2007
U can define a job sequence to send an email using SMTP activity if the job fails. Or log the failure to a log file using DSlogfatal/DSLogEvent from controlling job or using a After Job Routine. orUse dsJob -log from CLI.
-
Orchestrate Schema
What is Orchestrate Schema? Distinguish internal data type (Orchestrate schema) vs external data type
nikhilanshuman
- Dec 25th, 2010
Orchestrate schema defines the fields and their datatypes.There are two types of Orchestarte shcemas a) Input Schems b)Output schema.Orchestrate Schema can be compared with "STRUCT" of C.Example : RollNo - int32
DataStage Interview Questions
Read Best Answer
Ans