Ab Initio Data Warehousing Interview Preparation Guide

Download PDF

Dependency analysis will answer the questions regarding datalinage.That is where does the data come from,what applications prodeuce and depend on this data etc.

We can retrieve the maximum (surrogate key) from the existing data,the by using scan or next_in_sequence/reformat we can generate further sequence for new records.

Because implicit is using for internal processing and explicit is using for user open data requied.

Basically,This is a part of D.B.A responsibilities GRANT means permissions for example GRANT CREATE TABLE ,CREATE VIEW AND MANY MORE .

REVOKE means cancel the grant (permissions).So,Grant or Revoke both commands depend upon D.B.A.

By using rollup we cant generate cumulative summary records for that we will be using scan.

Because every job depend upon another job for example if you first job result is successfull then another job will execute otherwise your job doesn't work.

Do you mean by merging Gui map files in WR.If so, by merging GUI map files in GUI map editor it wont create corresponding test script.without testscript you cant run a file.So it is impossible to run a file by merging 2 GUI map files.

Take an entire database backup and restore it in different instance.

Take a statistics of all valid and invalid objects and match.

Periodically refresh

Explain plan can be reviewed to check the execution plan of the query. This would guide if the expected indexes are used or not.

If you are trying to install the Ab -Initio on stand alone machine , then it is not necessary to create the repository , While installing It creates automatically for you under abinitio folder ( where you installing the Ab-Initio) If you are still not clear please ask your Question on the same portal .

There are quite a few factors that determines the approach such as what type of version control are used, what is the size of the change, what is the impact of the change, is it a new procedure or replacing an existing and so on.

If it is a new, then just drop the wrong one

if it is a replacement then how big is the change and what will be the possible impact, depending upon you can have the entire database backed up or just create a script for your original procedure before messing it up or you just do an ed and change the file back to original and reapply. you may rename the old procedure as old and then work on new and so on.

few issues to keep in mind are synonyms, dependancies, grants, any job calling the procedure at the time of change and so on. In nutshell, scenario can be varied and solution also can be varied.

Truncate :
It is a DDL command, used to delete tables or clusters. Since it is a DDL command hence it is auto commit and Rollback can't be performed. It is faster than delete.

Delete:
It is DML command, generally used to delete a record, clusters or tables. Rollback command can be performed , in order to retrieve the earlier deleted things. To make deleted things permanently, "commit" command should be used.

There are several ways to do this:

1) We can move the table in the same or other tablespace and rebuild all the indexes on the table.

alter table <table_name> move <tablespace_name> this activity reclaims the defragmented space in the table

analyze table table_name compute statistics to capture the updated statistics.

2)Reorg could be done by taking a dump of the table, truncate the table and import the dump back into the table.

Use hints /*+ <hint> */, these acts as directives to the optimizer

The oracle engine uses work areas for internal processing in order to the execute sql statement is called cursor.There are two types of cursors like Implecit cursor and Explicit cursor.Implicit cursor is using for internal processing and Explicit cursor is using for user open for data required.

Recompile is useful when the tables referenced by the stored proc undergoes a lot of modification/deletion/addition of data. Due to the heavy modification activity the execute plan becomes outdated and hence the stored proc performance goes down. If we create the stored proc with recompile option, the sql server wont cache a plan for this stored proc and it will be recompiled every time it is run.

Main Purpose of Stored Procedure for reduse the network trafic and all sql statement executing in cursor so speed too high.

A Cartesian join will get you a Cartesian product. A Cartesian join is when you join every row of one table to every row of another table. You can also get one by joining every row of a table to every row of itself.

An outer join is used when one wants to select all the records from a port - whether it has satisfied the join criteria or not.

In RDBMS the relationship between the two tables is represented as Primary key and foreign key relationship.Wheras the primary key table is the parent table and foreignkey table is the child table.The criteria for both the tables is there should be a matching column.

If the user wants to group the records on particular field values then rollup is best way to do that. Rollup is a multi-stage transform function and it contains the following mandatory functions.
1. initialise
2. rollup
3. finalise
Also need to declare one temporary variable if you want to get counts of a particular group.

For each of the group, first it does call the initialise function once, followed by rollup function calls for each of the records in the group and finally calls the finalise function once at the end of last rollup call.

To convert 4 way to 8 way partition we need to change the layout in the partioning component. There will be seperate parameters for each and every type of partioning eg. AI_MFS_HOME, AI_MFS_MEDIUM_HOME, AI_MFS_WIDE_HOME etc.

The appropriate parameter need to be selected in the component layout for the type of partioning.

ablocal_expr is a parameter of itable component of Ab Initio.ABLOCAL() is replaced by the contents of ablocal_expr.Which we can make use in parallel unloads.There are two forms of AB_LOCAL() construct, one with no arguments and one with single argument as a table name(driving table).

The use of AB_LOCAL() construct is in Some complex SQL statements contain grammar that is not recognized by the Ab Initio parser when unloading in parallel. You can use the ABLOCAL() construct in this case to prevent the Input Table component from parsing the SQL (it will get passed through to the database). It also specifies which table to use for the parallel clause.

The latest version of GDE ism1.15 AND Co>operating system is 2.14

You can use $mpjret in endscript like

if 0 -eq($mpjret)

then

echo "success"

else

mailx -s "[graphname] failed" mailid

ChalapathiFirst we can check the properties in internet options and then u can check in cmd format telenet abinitio ip_add.