Firebird Interview Questions for freshers experienced :-
1. Can I Concurrently Access A Firebird Database With Embedded And With Regular Server?
- If you mean it’s one database and two applications then: NO
- If you mean it’s two databases and one application then: YES
2. How To Activate All Indexes In Firebird?
If you run Firebird 1.x which doesn’t have EXECUTE BLOCK, you can run the following query:
- select ‘ALTER INDEX ‘||rdb$index_name|| ‘ ACTIVE:’
- from rdb$indices
- where rdb$system_flag is not null and rdbSsystem_flag 0
3. How To Add Remove, Modify Users Using Sql?
It is currently not possible. You need to use service API. Access to it is provided by most connectivity libraries (except ODBC).
4. How To Change Database Dialect?
While you could simply change a flag in database file it isn’t recommended as there’s much more to it. Different dialects have different ways of handling numeric and date operations, which affects all object that are compiled into BLR (stored procedures, triggers, views, computed fields, etc.) Fixing all that on-the-fly would be very hard, so the recommended way is to create a new database and copy the data. You can easily extract the existing database structure using isql and then copy the data using some of the tools.
5. How To Configure Events With Firewall?
If firewall is on client, you don’t have to do anything special. If firewall is on the server, you need to set RemoteAuxPort setting in Firebird,conf file and forward traffic from firewall to that port.
6. How Do Convert Or Display The Date Or Time As String?
Simply use CAST to appropriate CHAR or VARCHAR data type (big enough). Example:
CREATE TABLE t1 (t time, d date. ts timestamp);
INSERT INTO t1 (t,d,ts) VALUES (‘14:59:23’, ‘2007-12-3 1’, ‘2007-12-31 14:59’);
SELECT CAST(t as varchar(13)), CAST(d as varchar( 10)), CAST(ts as varchar(24)) FROM t1;
Firebird would output times in HH:MM:SS.mmmm format (hours, minutes, seconds, milliseconds), and dates in YYYY-MM-DD (year, month, day) format.
if you wish a different formatting you can either use SUBSTRING to extract the info from char column, or use EXTRACT to buld a different string:
SELECT extract(day from d)||’.’||extract(month from d)||’.‘||extract(year from d) FROMt1;
7. How To Create A Database From My Program?
Firebird doesn’t provide a way to create database using SQL You need to either use the Services API, or external tool. As API for database creation is often not available in libraries, you can call Firebird’s isql tool to do it for you.
Let’s first do it manually. Run the isql, and then type:
SQL>CREATE DATAB ASE ‘C :\dbases\database. Rib’ user ‘SYSDBA’ password ‘masterkey’;
That’s it, Database is created. Type exit; to leave isql. To do it from program, you can either feed the text to execute to isql via stdin, or create a small file (ex. create sql) containing the CREATE DATABASE statement and then invoke isql with -i option: isql -i create.sql
8. How To Deactivate Triggers?
You can use these SQL commands:
ALTER TRIGGER trigger_name INACTIVE;
ALTER TRIGGER trigger_name ACTIVE;
Most tools have options to activate and deactivate all triggers for a table. For example, in Flame Robin, open the properties screen for a table, click on Triggers at top and then Activate or Deactivate All Triggers options at the bottom of the page.
9. How To Debug Stored Procedures?
Firebird still doesn’t offer hooks for stored procedure debugging yet. Here are some common workarounds:
- You can log values of your variables and trace the execution via external tables. External tables are not a subject of transaction control, so the trace won’t be lost if transaction is rolled back.
- You can turn your non-selectable stored procedure into selectable and run it with ‘SELECT * FROM’ instead of ’EXECUTE PROCEDURE’ in order to trace the execution. Just make sure you fill in the variables and call SUSPEND often. It’s a common practice to replace regular variables with output columns of the same name – so that less code needs to be changed.
- Some commercial tools like IBExpert or Database Workbench parse the stored procedure body and execute statements one by one giving you the emulation of stored procedure run. While it does work properly most of the time, please note that the behaviour you might see in those tools might not be exactly the same as one seen with actual Firebird stored procedure – especially if you have uninitialized variables or other events where behavior is undefined. Make sure you file the bug reports to tool makers and not to Firebird development team if you run such ‘stored procedure debuggers’.
- Since Firebird 2.0 you can also use EXECUTE BLOCK to simulate stored procedures. EXECUTE BLOCK does not support input parameters, so you need to convert all of those to local variables (with DECLARE VARIABLE)
10. How To Detect Applications And Users That Hold Transactions Open Too Long?
To do this, you need Firebird 2.1 or a higher version. First, run gstat tool (from your Firebird installation’s bin directory), and you’ll get an output like this:
gstat -h faqs.gdb
Database “faqs.gdb”
Database header page information:
Flags 0
Checksum 12345
Generation 919
Page size 4096
ODS version 11 .1
Oldest transaction 812
Oldest active 813
Oldest snapshot 813
Next transaction 814
Now, connect to that database and query the MON$TRANSACTTONS table to get the MON$ATTACHMENT_ID for that transaction, and then query the MONSATTACHMENTS table to get the user name, application name, 1P address and even PID on the client machine. We are looking for the oldest active transaction, so in this case, a query would look like:
SELECT ma.*
FROM MON$ATTACHMENTS ma
join MON$TRANSACTIONS mt
on ma.MON$ATTACHMENT ID – mt.MONSATTACHMENTID
where mt.MONSTRANSACTION_ID = 813,
11. How To Detect The Server Version?
You can get this via Firebird Service API. It does not work for Firebird Classic 1.0, so if you don’t get an answer you’ll know it’s Firebird Classic 1.0 or InterBase Classic 6.0. Otherwise it returns a string like this:
LI-V2.0.0. 12748 Firebird 2.0
or…
LI-V1 .5.3.4870 Firebird 1.5
The use of API depends on programming language and connectivity library you use. Some might even not provide it. Those that do, call the isc_info_svc_server_version API.
If you use Firebird 2.1, you can also retrieve the engine version from a global context variable, like this:
SELECT rdbSget_context(’SYSTEM’, ‘ENGINE VERSION’)
from rdb$database ;
12. How To Determine Who Is And Change The Owner Of Database?
Use the following query:
SELECT DISTINCT RDB$OWNER_NAME AS DATABASE_OWNER
FROM RDB$RELATIONS
WHERE (RDB$SYSTEM_FLAG = 1 );
13. How To Open The Database In Exclusive Mode?
You need to shutdown the database (using gfix or some other tool). Firebird 2.0 offers various shutdown modes (single-user, single-connection, multiple connection, etc.).
14. How To Move A Multi-file Database?
You are probably used to having a single-file database which you can move around as much as you want. But, your database has grown too big and now you need a multi-file database. Paths to the secondary files are absolute and stored in the header page of the first database file. If you need to move those files, it is recommended that you backup the database and restore at new location. However, if you really need to copy them around, you can use freeware tool G1ink by Ivan Prenosil:
15. How To Monitor Firebird Server Activity?
Firebird 2.1 introduces server-side monitoring via special system tables. This way you can monitor your server directly from SQL Those system tables all have prefix MON$ in their name. To use them, you need to make sure your database file is created with Firebird 2.1 or higher (ODS version 11.1). If you have a database that is created with earlier versions, you need to do backup and subsequent restore with Firebird 2.1 to have those tables.
16. How To Migrate Paradox, Dbase Or Foxpro Database To Firebird?
The easiest way is to download the freeware IBDataPump by CleverComponents. It will extract the metadata from Paradox/dBase/FoxPro database, create all the tables in a Firebird database and then copy all the data. You’ll probably have a ready-to-go Firebird database in less than one hour.
17. How To Load A File Into Database Column?
While some other database systems might have an SQL function for this, with Firebird you need an application. Datatype that holds binary files is called BLOB, and you should use sub_type zero, as sub_type one is for text-only data. Let’s create a table to hold the file. We’ll have a filename column and a blob column containing the file itself:
CREATE TABLE t1
(
file_name VARCHAR(200),
file_data BLOB SUB_TYPE 0
);
The blobs are loaded via parametrized query:
INSERT INTO t1 (file_name, file_data) VALUES (?,? );
18. How To Extract Metadata For The Entire Database?
It’s quite simple, use isql with -x or -a options. Please be careful and test if it works. Some commercial administration tools like to play with system tables directly, and isql isn’t always able to understand their hacks You can also extract DDL with FlameRobin Open the properties page for the database and select DDL option at the top.
19. How To Export Data From Database And Import Into Another?
If your databases are on-line, i.e. visible to each other via network, then you can use some data pump tool like freeware lB Pump or some of advanced commercial administration tools which have this option integrated.
If your databases are offline, you should first export the data and then import it on the other end. Most admin. tools can do export to CVS, XML or INSERT statements. If efficiency is important, or your have data with BLOB column, you can use the open source FBExport tool.
If you are looking for a way to easily import CS\’ or XML data into Firebird, take a look at XML Wizard tool. You can also use it to import data from Microsoft Excel or OpenOffice by saving the sheet to .csv format and then importing via XML Wizard
20. How To Drop All Foreign Keys In Database?
Deleting all foreign keys can be done by querying the system tables and droping them one by one. If you use Firebird 2 or higher, it can be done with a single SQL statement:
set term!!;
EXECUTE BLOCK RETURNS (stmt VARCHAR(1 000)) AS
BEGIN
FOR
select ‘alter table ‘||r.rdb$relation_name ||’ drop constraint’|| ‘ r. rdb$constraint_name||’;’ from rdb$relation _constraints r
where (r. rdb$constraint_type=’FORETGN KEY’)
into :stmt
DO begin suspend; execute statement :stmt; end
END!!
set term; !!
If you use Firebird i .x, you can run the following query to get statements to execute and then copy/paste the result and execute:
select ‘ALTER TABLE’ ||r. rdb$relat ion_name ||’DROP CONSTRAINT’|| r. rdb$constraint_name||’:’ from rdb$relation_constraints r
where (r.rdb$constraint_type=’FORETGN KEY’)
21. How To Do Replication Of Firebird Databases?
Firebird does not offer replication out-of-the-box, you need to use some 3rd party tools. Those external tools add specific triggers that log all the changes in database and replicate to other databases.
22. How To Disconnect The User Connection?
Currently there is no easy way of doing it. You can bring database to some of shutdown modes, but it affects all users. If you use Classic you can (with some effort) find the users process by detecting the IP address and open database files of that process and simply kill that process. With Super Server it is not possible as the connection is run in a thread of’ multithreaded SuperServer process.
There are plans for future versions of Firebird to address this. For example. version 2.1 introduces ability to cancel running queries (by deleting the relevant records from MON$STATEMENTS table).
23. How To Present Firebird.log File From Filling Up The Disk Partition?
Here are some tips:
a) create a scheduled task or cron job that will truncate or rotate the log file. By rotation, we mean renaming the flies in such way that you always have a number of previous logs available. Example.
delete flrebird.log.5
rename firebird. iog.4 firebird. log. 5
rename flrebird.iog.3 firebird.log.4
rename flrebird.log.2 firebird.log.3
rename firebird.log.l f’irebird.iog,2
rename firebird.iog firebird.log.1
This way you’ll always have last five logs available, and those too old get deleted. You can also use zip, rar, bzip2 or some other packer to compress the old log files. Since they are plain text, they compress very well.
b) redirect logging to void. For example, on Linux, you can do it by creating a symlink to /dev/null instead of the regular log file:
# cd /opt/firebird
# rm -f firebird log
in -s /dev/null firebird.log
24. How To Pump The Data From One Database To Another?
Many recommend IB Pump or IB Data Pump, but the problem is when you have complex relations between tables. In such cases, it is better to use tool like FB Copy which sorts the tables by dependencies (foreign keys, check constraints) into correct order
25. How To Recreate The Index On A Firebird Table?
Recreating the index:
ALTER INDEX indexName INACTIVE.
ALTER INDEX indexName ACT1VE
26. How To Reorder The Table Columns (fields)?
While the order should not matter to applications (you should always use explicit column names in queries), perhaps it’s easier for you when you work with tables in database administration tools. You can move a column to different location using the following SQL statement:
ALTER TABLE table_name ALTER field name POSITION new_position:
Positions are numbered from one. If you wish to exchange two fields, make sure you run the statement for both of them. It’s easy to run tools like Flame Robin to do this (Reorder Fields option at table’s properties screen).
27. How To Repair A Corrupt Firebird Database?
Here’s a short step-by-step walkthrough.
- disconnect users and disable incoming connections to the database
- make a copy of database file (or two copies) and work on that
- use GFIX with -v option to validate the database file
- use GF1X with -v and -f to do full validation
If problem is not too serious, you can try to backup the broken db and restore under a new name:
- use GF1X -mend to prepare corrupt database for backup
- use GBAK -b -g to backup the database. -g disables garbage collection
- use GBAK -c to restore backup to a new database.
If you succeed, you have fixed the problem and have a functional database. If not, you can try to create an empty database with the same structure and pump the data to it.
One of the reasons why backup or restore can fail is if some broken database triggers exist, and prevent connection to the database. For example, a database trigger might use some table which has a broken index, etc. To work around this, connect to database with isql tool using -nodbtriggers option and then disable those triggers. You can enable them later when you fix other problems and get a working database again.
28. How To Select A Random Record From A Table?
There is no such feature in Firebird, but you can use some tricks. The following example requires that you have a unique integer column (primary key is usually used):
SELECT.. field_list…
FROM table t1
WHERE conditions
ORDER BY (t1 .int_col + seed)*4294967291 -((tl .int_col + seed)*4294967291/49157)*49157;
If you just need one random record, limit the result set using FIRST or ROWS clause. This query will give consistent records for the same seed. If you wish to be completely random, you need to change the seed. You could use the value of int_col from previous run, or simply fetch a new value from a generator (just make sure the same value for seed is used in both places in expression).
29. How To Specify Transaction Or Query Timeout?
In order to keep the server low reasonable, you might want to limit the time a single query can consume. Firebird does not support this directly yet (there are plans for Firebird 3.0).
However, you could periodically query the monitoring tables to detect and cancel long running queries. You can do:
SELECT * FROM MON$STATEMENTS:
Look for those having MON$STATE set to 1.
30. What Is A Staging Area? Do We Need It? What Is The Purpose Of A Staging Area?
Data staging is actually a collection of processes used to prepare source system data for loading a data warehouse. Staging includes the following steps:
- Source data extraction, Data transformation (restructuring),
- Data transformation (data cleansing, value transformations),
- Surrogate key assignments.
31. How to select a random record from a table?
There is no such feature in Firebird, but you can use some tricks. The following example requires that you have a unique integer column (primary key is usually used):
SELECT …field_list…
FROM table t1
WHERE conditions
ORDER BY (t1.int_col + seed)*4294967291-((t1.int_col + seed)*4294967291/49157)*49157;
If you just need one random record, limit the result set using FIRST or ROWS clause. This query will give consistent records for the same seed. If you wish to be completely random, you need to change the seed. You could use the value of int_col from previous run, or simply fetch a new value from a generator (just make sure the same value for seed is used in both places in expression).
32. How to specify transaction or query timeout?
In order to keep the server low reasonable, you might want to limit the time a single query can consume. Firebird does not support this directly yet (there are plans for Firebird 3.0).
However, you could periodically query the monitoring tables (Firebird 2.1 and above) to detect and cancel long running queries. You can do:
SELECT * FROM MON$STATEMENTS;
Look for those having MON$STATE set to 1.
Please note that your database needs to be at least ODS 11.1, i.e. created with Firebird 2.1 or above. Older databases won’t show you these tables even if you use Firebird 2.1 to access them. To learn more about ODS and how to retrieve it.
33. How to stop SuperServer service on Linux using only Firebird tools?
The server is started and stopped by ‘fbmgr’ executable from ‘bin’ directory of your Firebird installation. It is called ‘ibmgr’ in Firebird 1.0. To start the server type:
/opt/firebird/bin/fbmgr -start
To start the server with Guardian (Guardian watches the server and restarts it if it crashes) type:
/opt/firebird/bin/fbmgr -start -forever
To stop a running server, type:
/opt/firebird/bin/fbmgr -shut -user SYSDBA -pass *****
To force a shutdown, type:
/opt/firebird/bin/fbmgr -shut -force -user SYSDBA -pass *****
If you use Firebird 2 or higher, you can also use the regular ‘kill’ command to shutdown the server, as it handles the signals properly. Make sure you first kill the guardian and then the server (otherwise guardian would restart the server).
34. How to tell Firebird to only accept conections from XYZ host or network?
This isn’t really a thing you should be configuring in Firebird. There is a RemoteBindAddress setting in firebird.conf which configures on which interfaces/addresses the Firebird listens but that’s all. You should really use your system’s firewall to set this up.
Beside firewall, if you use Classic on Linux, you can use xinetd or inetd access control files /etc/hosts.allow and /etc/hosts.deny. With xinetd you can also edit the xinetd configuration file for Firebird service, which is in /etc/xinetd.d/firebird and add a line like this:
“only_from = 192.168.0.0/24”
35. How to use events with ZeBeDee, SSH or stunnel?
You have to use SuperServer, set up RemoteAuxPort setting in firebird.conf and create two tunnels (one for data, other for events).
36. How to write UDF s in Delphi?
It’s quite simple, the only thing you need to remember is that you must always use ib_util_malloc() to allocate memory if your UDF returns string result. The UDF must be declared as FREE_IT, so that Firebird releases the memory after it reads the string.
To use ib_util_malloc(), you need to import it from ib_util.dll into your program – and make sure you use it instead of regular memory alocating functions. Here’s a simple example of Delphi UDF:
function ib_util_malloc(l: integer): pointer; cdecl; external ‘ib_util.dll’;
function ChangeMyString(const p: PChar): PChar; cdecl;
var
s: string;
begin
s := DoSomething(string(p));
Result := ib_util_malloc(Length(s) + 1);
StrPCopy(Result, s);
end;
Declaration in Firebird:
DECLARE EXTERNAL FUNCTION ChangeMyString
CString(255)
RETURNS CString(255) FREE_IT
ENTRY_POINT ‘ChangeMyString’ MODULE_NAME ‘……’
37. Is it possible to determine clients IP address?
To get it from SQL, you need to use Firebird 2.0 (own address), or Firebird 2.1 (anyone’s):
- If you use Firebird 2.0 or higher, use the GET_RDB$Context function with (‘SYSTEM’, ‘CLIENT_ADDRESS’) parameters.
- If you use Firebird 2.1 or higher, you can get address of any client by selecting from the monitoring tables.
- With Firebird 1.x you can try to get the information from TCP/IP stack, using netstat or lsof commands from the command-prompt. Just search for Firebird’s port (3050 or gds_db).
38. Is there a way to automate SQL execution from the command-line, batch job or shell script?
Yes. You can use isql for this. It is located in the ‘bin’ directory of your Firebird installation. If you wish to try it interactively, run isql and then type:
isql localhost:my_database -user sysdba -pass ******
SQL> input my_script.sql;
SQL> commit;
SQL>
To run it from a batch (.bat) file or a shell script, use -i switch:
isql -i my_script.sql localhost:my_database -user sysdba -pass ******
If you have some DML statements in your script, make sure you put the COMMIT command at the end of the file. Also, make sure the file ends with a newline, as isql executes the commands on the line only after it gets the newline character.
39. Is there a way to detect whether fbclient.dll or fbembed.dll is loaded?
There are some ways to detect it:
- check the size of DLL file
- if you are using different versions of Firebird (for example 1.5.4 and 2.0.1, you can query the server version via Services API)
You should understand that fbembed can be used as a regular Firebird client. Checking whether embedded or fbclient is loaded for licensing or similar needs is really not useful. You could use the connection string as guide, but super server can establish direct local connections without localhost prefix.
If you combine all this information, you could get some conclusions:
- if DLL size matches fbembed and connection string doesn’t have hostname, you are using embedded
- if DLL size matches fbembed and connection string does have hostname, you are using either super server or classic
- if DDL size matches fbclient and connection string doesn’t have hostname, you are using super server via local connection (IPC, XNET)
- if DLL size matches fbclient and connection string does have hostname, you are using either super server or classic
40. Is there an example how to configure ExternalFileAccess setting in firebird.conf?
Firebird’s config file (firebird.conf) does have descriptions inside that explain everything, but sometimes they are confusing and hard to understand what should you do exactly if you don’t have examples. One of such settings is ExternalFileAccess. Some people are even tempted to put Full as it is much easier than trying to guess what’s the correct format. Here are the basic settings (‘None’ to disallow external tables and ‘Full’ to allow them anywhere) which you probably understood yourself:
ExternalFileAccess = None
ExternalFileAccess = Full
And here are those tricky Restrict settings:
ExternalFileAccess = Restrict C:somedirectory
For multiple directories, use something like this:
ExternalFileAccess = Restrict C:somedirectory;C:someotherdirectory
For Linux users:
ExternalFileAccess = Restrict /some/directory
41. Is there an example how to configure UdfAccess setting in firebird.conf?
Well, there’s one right there in the firebird.conf, but perhaps it isn’t obvious enough. Here are the basic settings (‘None’ to disallow UDFs completely and ‘Full’ to allow them anywhere) which you probably understood yourself:
UdfAccess = None
UdfAccess = Full
And here is that tricky Restrict setting:
UdfAccess = Restrict C:somedirectory
For multiple directories, use something like this:
UdfAccess = Restrict C:somedirectory;C:someotherdirectory
For Linux users:
UdfAccess = Restrict /some/directory
In the default setting ‘Restrict UDF’, ‘UDF’ is a directory relative to root directory of Firebird installation.
42. Is there some bulk load or other way to import a lot of data fast?
Currently there is only one way to quickly load a lot of data into database. That is by using external tables. You should read the manual for details, but here’s a short explanation. You create a binary or textual file using the external table format and then hook it up in the database using a statement like this:
CREATE TABLE ext1 EXTERNAL ‘c:myfile.txt’
(
field1 char(20),
field2 smallint
);
To do quick import into regular table, do something like this:
INSERT INTO realtable1 (field1, field2)
SELECT field1, field2 FROM ext1;
This insert would still check constraints, foreign keys, fire triggers and build indexes. If you can, it is wise to deactivate indexes and triggers while loading and activate them when done.
Make sure you drop the external table when done, in order to release the lock on the file.
The main problem with external tables is handling of NULLs and BLOBs. If you need to deal with those, you’re better off using some tool like FBExport. However, please note that external tables are much faster.
43. What is the best way to determine whether Firebird server is running?
If you want to do it from an application, a simple try to connect should suffice. Otherwise you have various options:
a) check if firebird server is in the list of running programs (use task manager on Windows, or ‘ps ax’ command on Linux). Please note that Classic won’t be running until there is a connection established.
b) check whether the port 3050 is open on the machine. First, you can check with netstat command, and if it is open, you can test whether it accepts connections by telnet-ing to the port. Just type:
telnet [hostname|IPaddress] 3050
Example:
telnet localhost 3050
If you use Linux, you can also check the open port with ‘lsof’ command. It outputs a lot, so you might want to ‘grep’ for 3050 or gds_db strings:
# lsof | grep gds_db
# lsof | grep 3050
c) if all of this fails, perhaps you should check whether the remote server is reachable at all. You can use ‘ping’ command:
ping [hostname|IPaddress]
Example:
ping 192.168.0.22
Please note that ping can still give you ‘host unreachable’ message even if host is up. This is because the firewall software can drop the ICMP (ping) packets (it’s done to prevent some viruses from spreading, or network scans).
44. What Is The Metadata Extension?
Informatica allows end users and partners to extend the metadata stored in the repository by associating information with individual objects in the repository. For example, when you create a mapping, you can store your contact information with the mapping. You associate information with repository metadata using metadata extensions.
Informatica Client applications can contain the following types of metadata extensions:
- Vendor-defined. Third-party application vendors create vendor-defined metadata extensions. You can view and change the values of vendor-defined metadata extensions, but you cannot create, delete, or redefine them.
- User-defined. You create user-defined metadata extensions using PowerCenter/PowerMart. You can create, edit, delete, and view user-defined metadata extensions. You can also change the values of user-defined extensions.
45. How Can We Use Mapping Variables In Informatica? Where Do We Use Them?
Yes. we can use mapping variable in Informatica.
The Informatica server saves the value of mapping variable to the repository at the end of session run and uses that value next time we run the session.
46. What Is Etl Process ?how Many Steps Etl Contains Explain With Example?
ETL is extraction, transforming, loading process, you will extract data from the source and apply the business role on it then you will load it in the target the steps are :
- define the source(create the odbc and the connection to the source DB)
- define the target (create the odbc and the connection to the target DB)
- create the mapping ( you will apply the business role here by adding transformations , and define how the data flow will go from the source to the target )
- create the session (its a set of instruction that run the mapping )
- create the work flow (instruction that run the session)
47. Give Some Popular Tools?
Popular Tools:
- IBM Web Sphere Information Integration (Accentual DataStage)
- Ab Initio
- Informatica
- Talend
48. Give Some Etl Tool Functionalities?
While the selection of a database and a hardware platform is a must, the selection of an ETL tool is highly recommended, but it’s not a must. When you evaluate ETL tools, it pays to look for the following characteristics:
Functional capability: This includes both the ‘transformation’ piece and the ‘cleansing’ piece. In general, the typical ETL tools are either geared towards having strong transformation capabilities or having strong cleansing capabilities, but they are seldom very strong in both. As a result, if you know your data is going to be dirty coming in, make sure your ETL tool has strong cleansing capabilities. If you know there are going to be a lot of different data transformations, it then makes sense to pick a tool that is strong in transformation.
Ability to read directly from your data source: For each organization, there is a different set of data sources. Make sure the ETL tool you select can connect directly to your source data.
Metadata support: The ETL tool plays a key role in your metadata because it maps the source data to the destination, which is an important piece of the metadata. In fact, some organizations have come to rely on the documentation of their ETL tool as their metadata source. As a result, it is very important to select an ETL tool that works with your overall metadata strategy.
49. How Can I Edit The Xml Target, Are There Anyways Apart From The Editing The Xsd File. Can I Directly Edit The Xml Directly In Informatica Designer?
No you cannot edit it from Informatica designer. But still you can change the precession of the ports if xml source is imported from DTD file.
50. Why does reading require write privileges on database file?
In order to run the SELECT statmement, it still needs to start a transaction.
If you wish to build a read-only database to place on some read-only media like CD or DVD ROM, you can do it with:
gfix -mode read_only database.fdb
…or within your favorite administration tool. It is also available via ServicesAPI, so you may do it from your application as well. Please note that you can only make this change while preparing the database, because the read-only flag needs to be written in the database file.
When the database becomes read-only, the only thing you can write to is the read_only flag (to reset it back to read-write).
51. Where Do We Use Connected And Un Connected Lookups?
If return port only one then we can go for unconnected. More than one return port is not possible with Unconnected. If more than one return port then go for Connected.
52. What Are The Various Tools? – Name A Few.
- Abinitio
- DataStage
- Informatica
- Cognos Decision Stream
- Oracle Warehouse Builder
- Business Objects XI (Extreme Insight)
- SAP Business Warehouse
- SAS Enterprise ETL Server
53. What Are The Various Test Procedures Used To Check Whether The Data Is Loaded In The Backend, Performance Of The Mapping, And Quality Of The Data Loaded In Informatica?
The best procedure to take a help of debugger where we monitor each and every process of mappings and how data is loading based on conditions breaks.
54. What Is The Difference Between Joiner And Lookup?
joiner is used to join two or more tables to retrieve data from tables(just like joins in sql).
Look up is used to check and compare source table and target table .(just like correlated sub-query in sql).
55. If A Flat File Contains 1000 Records How Can I Get First And Last Records Only?
By using Aggregator transformation with first and last functions we can get first and last record.
56. How Do You Calculate Fact Table Granularity?
Granularity, is the level of detail in which the fact table is describing, for example if we are making time analysis so the granularity maybe day based – month based or year based.
57. What Are The Different Versions Of Informatica?
Here are some popular versions of Informatica.
Informatica Powercenter 4.1,
Informatica Powercenter 5.1,
Powercenter Informatica 6.1.2,
Informatica Powercenter 7.1.2,
Informatica Powercenter 8.1,
Informatica Powercenter 8.5,
Informatica Powercenter 8.6.
58. Techniques Of Error Handling – Ignore , Rejecting Bad Records To A Flat File , Loading The Records And Reviewing Them (default Values)?
Rejection of records either at the database due to constraint key violation or the informatica server when writing data into target table. These rejected records we can find in the bad files folder where a reject file will be created for a session. We can check why a record has been rejected. And this bad file contains first column a row indicator and second column a column indicator.
These row indicators are of four types
D-valid data,
O-overflowed data,
N-null data,
T- Truncated data,
And depending on these indicators we can changes to load data successfully to target.
59. What Is The Difference Between Power Center & Power Mart?
PowerCenter – ability to organize repositories into a data mart domain and share metadata across repositories.
PowerMart – only local repository can be created.
60. What Are Snapshots? What Are Materialized Views & Where Do We Use Them? What Is A Materialized View Log?
Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table. Snapshots are mirror or replicas of tables.
Views are built using the columns from one or more tables. The Single Table View can be updated but the view with multi table cannot be updated.
A View can be updated/deleted/inserted if it has only one base table if the view is based on columns from one or more tables then insert, update and delete is not possible.
Materialized view
A pre-computed table comprising aggregated or joined data from fact and possibly dimension tables. Also known as a summary or aggregate table.
61. What Is Virtual Data Wearhousing?
A virtual data warehouse provides a collective view of the completed data. It can be considered as a logical data model of the containing metadata.
62. What Is Active Data Wearhousing?
An active data warehouse represents a single state of the business. It considers the analytic perspectives of customers and suppliers. It helps to deliver the updated data through reports.
63. What Is Data Modeling And Data Mining?
Data Modeling is a technique used to define and analyze the requirements of data that supports organization’s business process. In simple terms, it is used for the analysis of data objects in order to identify the relationships among these data objects in any business.
Data Mining is a technique used to analyze datasets to derive useful insights/information. It is mainly used in retail, consumer goods, telecommunication and financial organizations that have a strong consumer orientation in order to determine the impact on sales, customer satisfaction and profitability.
64. What Are Critical Success Factors?
Key areas of activity in which favorable results are necessary for a company to obtain its goal.
There are four basic types of CSFs which are:
- Industry CSFs
- Strategy CSFs
- Environmental CSFs
- Temporal CSFs
65. What Is Data Cube Technology Used For?
Data cubes are commonly used for easy interpretation of data. It is used to represent data along with dimensions as some measures of business needs. Each dimension of the cube represents some attribute of the database. E.g profit per day, month or year.
66. What Is Data Cleaning?
- Data cleaning is also known as data scrubbing.
- Data cleaning is a process which ensures the set of data is correct and accurate. Data accuracy and consistency, data integration is checked during data cleaning. Data cleaning can be applied for a set of records or multiple sets of data which need to be merged.
67. Explain How To Mine An Olap Cube?
An extension of data mining can be used for slicing the data the source cube in discovered data mining.
The case table is dimensioned at the time of mining a cube.
68. What Are Different Stages Of Data Mining?
A stage of data mining is a logical process for searching large amount information for finding important data.
Stage 1: Exploration: One will want to explore and prepare data. The goal of the exploration stage is to find important variables and determine their nature.
Stage 2: pattern identification: Searching for patterns and choosing the one which allows making best prediction, is the primary action in this stage.
Stage 3: Deployment stage: Until consistent pattern is found in stage 2, which is highly predictive, this stage cannot be reached. The pattern found in stage 2, can be applied for the purpose to see whether the desired outcome is achieved or not.
69. What Are The Different Problems That Data Mining Can Slove?
Data mining can be used in a variety of fields/industries like marketing of products and services, AI, government intelligence.
The US FBI uses data mining for screening security and intelligence for identifying illegal and incriminating e-information distributed over internet.
70. What Is Data Purging?
Deleting data from data warehouse is known as data purging. Usually junk data like rows with null values or spaces are cleaned up.
Data purging is the process of cleaning this kind of junk values.
71. What Is Bus Schema?
A BUS schema is to identify the common dimensions across business processes, like identifying conforming dimensions. It has conformed dimension and standardized definition of facts.
72. Define Non-additive Facts?
Non additive facts are facts that cannot be summed up for any dimensions present in fact table. These columns cannot be added for producing any results.
73. What Is Conformed Fact? What Are Conformed Dimensions Used For?
Conformed fact in a warehouse allows itself to have same name in separate tables. They can be compared and combined mathematically. Conformed dimensions can be used across multiple data marts. They have a static structure. Any dimension table that is used by multiple fact tables can be conformed dimensions.
74. What Is A Three Tier Data Warehouse?
A data warehouse can be thought of as a three-tier system in which a middle system provides usable data in a secure way to end users. On either side of this middle system are the end users and the back-end data stores.
75. What Is The Difference Between Informatica 7.0&8.0?
The only difference b/w informatica 7 & 8 is… 8 is a SOA (Service Oriented Architecture) whereas 7 is not. SOA in informatica is handled through different grid designed in server.
76. What Is Latest Version Of Power Center / Power Mart?
The Latest Version is 7.2
77. What Are The Modules In Power Mart?
- PowerMart Designer
- Server
- Server Manager
- Repository
- Repository Manager
78. What Are Active Transformation / Passive Transformations?
Active transformation can change the number of rows that pass through it. (Decrease or increase rows)
Passive transformation cannot change the number of rows that pass through it.
79. What Are The Different Lookup Methods Used In Informatica?
Connected lookup:
Connected lookup will receive input from the pipeline and sends output to the pipeline and can return any number of values it does not contain return port.
Unconnected lookup:
Unconnected lookup can return only one column it contain return port.
80. How Do We Call Shell Scripts From Informatica?
Specify the Full path of the Shell script the “Post session properties of session/workflow”.
81. What Is Informatica Metadata And Where Is It Stored?
Informatica Metadata is data about data which stores in Informatica repositories.
82. What Is A Mapping, Session, Worklet, Workflow, Mapplet?
- A mapping represents dataflow from sources to targets.
- A mapplet creates or configures a set of transformations.
- A workflow is a set of instructions that tell the Informatica server how to execute the tasks.
- A worklet is an object that represents a set of tasks.
- A session is a set of instructions that describe how and when to move data from sources to targets.
83. What Are Parameter Files? Where Do We Use Them?
Parameter file defines the value for parameter and variable used in a workflow, worklet or session.
84. Can We Override A Native Sql Query Within Informatica? Where Do We Do It? How Do We Do It?
Yes, we can override a native sql query in source qualifier and lookup transformation.
In lookup transformation we can find “Sql override” in lookup properties. By using this option we can do this.
85. Can We Use Procedural Logic Inside Infromatica? If Yes How , If No How Can We Use External Procedural Logic In Informatica?
Yes, you can use advanced external transformation, You can use c++ language on unix and c++, vb vc++ on windows server.
86. Do We Need An Etl Tool? When Do We Go For The Tools In The Market?
ETL Tool:
It is used to Extract(E) data from multiple source systems(like RDBMS, Flat files, Mainframes, SAP, XML etc) transform(T) then based on Business requirements and Load(L) in target locations.(like tables, files etc).
Need of ETL Tool:
An ETL tool is typically required when data scattered across different systems.(like RDBMS, Flat files, Mainframes, SAP, XML etc).
87. How To Determine What Records To Extract?
When addressing a table some dimension key must reflect the need for a record to get extracted. Mostly it will be from time dimension (e.g. date >= 1st of current month) or a transaction flag (e.g. Order Invoiced Stat). Foolproof would be adding an archive flag to record which gets reset when record changes.
88. What Is Full Load & Incremental Or Refresh Load?
- Full Load: completely erasing the contents of one or more tables and reloading with fresh data.
- Incremental Load: applying ongoing changes to one or more tables based on a predefined schedule.
89. When Do We Analyze The Tables? How Do We Do It?
The ANALYZE statement allows you to validate and compute statistics for an index, table, or cluster. These statistics are used by the cost-based optimizer when it calculates the most efficient plan for retrieval. In addition to its role in statement optimization, ANALYZE also helps in validating object structures and in managing space in your system. You can choose the following operations: COMPUTER, ESTIMATE, and DELETE. Early version of Oracle7 produced unpredictable results when the ESTIMATE operation was used. It is best to compute your statistics.
EX:
select OWNER,
sum(decode(nvl(NUM_ROWS,9999), 9999,0,1)) analyzed,
sum(decode(nvl(NUM_ROWS,9999), 9999,1,0)) not_analyzed,
count(TABLE_NAME) total
from dba_tables
where OWNER not in (‘SYS’, ‘SYSTEM’)
group by OWNER
90. Compare Etl & Manual Development?
These are some differences b/w manual and ETL development.
ETL
- The process of extracting data from multiple sources.(ex. flatfiles, XML, COBOL, SAP etc) is more simpler with the help of tools.
- High and clear visibility of logic.
- Contains Meta data and changes can be done easily.
- Error handling, log summary and load progress makes life easier for developer and maintainer.
- Can handle Historic data very well.
Manual
- Loading the data other than flat files and oracle table need more effort.
- complex and not so user friendly visibility of logic.
- No Meta data concept and changes needs more effort.
- need maximum effort from maintenance point of view.
- as data grows the processing time degrades.
91. What Is Real Time Data-wearhousing?
- In real time data-warehousing, the warehouse is updated every time the system performs a transaction.
- It reflects the real time business data.
- This means that when the query is fired in the warehouse, the state of the business at that time will be returned.
92. Explain The Use Lookup Tables And Aggregate Tables?
An aggregate table contains summarized view of data.
Lookup tables, using the primary key of the target, allow updating of records based on the lookup condition.
93. Define Slowly Changing Dimensions (scd)?
SCD are dimensions whose data changes very slowly.
eg: city or an employee.
This dimension will change very slowly.
The row of this data in the dimension can be either replaced completely without any track of old record OR a new row can be inserted, OR the change can be tracked.
94. What Is Cube Grouping?
A transformer built set of similar cubes is known as cube grouping. They are generally used in creating smaller cubes that are based on the data in the level of dimension.
95. What Is Data Wearhousing?
A data warehouse can be considered as a storage area where relevant data is stored irrespective of the source.
Data warehousing merges data from multiple sources into an easy and complete form.
96. Can Informatica Load Heterogeneous Targets From Heterogeneous Sources?
No, In Informatica 5.2 and
Yes, in Informatica 6.1 and later.