Does the selection of 'Clear the table and Insert rows' in the ODBC stage send a Truncate statement to the DB or does it do some kind of Delete logic.
Ans: There is no TRUNCATE on ODBC stages. It is Clear table blah blah and that is a delete from statement. On an OCI stage such as Oracle, you do have both Clear and Truncate options. They are radically different in permissions (Truncate requires you to have alter table permissions where Delete doesn't).
Tell me one situation from your last project, where you had faced problem and How did you solve it?
Ans: The jobs in which data is read directly from OCI stages are running extremely slow. I had to stage the data before sending to the transformer to make the jobs run faster.
B. The job aborts in the middle of loading some 500,000 rows. Have an option either cleaning/deleting the loaded data and then run the fixed job or run the job again from the row the job has aborted. To make sure the load is proper we opted the former.
Why do we have to load the dimensional tables first, then fact tables:
Ans: As we load the dimensional tables the keys (primary) are generated and these keys (primary) are Foreign keys in Fact tables.
How will you determine the sequence of jobs to load into data warehouse?
Ans: First we execute the jobs that load the data into Dimension tables, then Fact tables, then load the Aggregator tables (if any).
What are the command line functions that import and export the DS jobs?
Ans: A. dsimport.exe- imports the DataStage components.
B. dsexport.exe- exports the DataStage components.
What is the utility you use to schedule the jobs on a UNIX server other than using Ascential Director?
Ans: Use crontab utility along with dsexecute() function along with proper parameters passed.
How would call an external Java function which are not supported by DataStage?
Ans: Starting from DS 6.0 we have the ability to call external Java functions using a Java package from Ascential. In this case we can even use the command line to invoke the Java function and write the return values from the Java program (if any) and use that files as a source in DataStage job.
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.
Ans: 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.
Read the String functions in DS
Ans: Functions like [] -> sub-string function and ':' -> concatenation operator
Syntax: string [ [ start, ] length ]
string [ delimiter, instance, repeats ]
How did you connect with DB2 in your last project?
Ans: 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 Access ODBC Driver 9.00.02.02' - ODBC drivers to connect to AS400/DB2.
What are Sequencers?
Ans: Sequencers are job control programs that execute other jobs with preset Job parameters.
Differentiate Primary Key and Partition Key?
Ans: Primary Key is a combination of unique and not null. It can be a collection of key values called as composite primary key. Partition Key is a just a part of Primary Key
How did you handle an 'Aborted' sequencer?
Ans: 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.