UNIX - Command to count the number of times a string appears in a file

grep -c "input_string" filename

UNIX - Creating read-only file in UNIX

1. Create file using touch file_name.


2. Giving Read-only permission to the file by 

chmod 400 file_name.

Informatica - Difference between STOP and ABORT

Informatica - Difference between STOP and ABORT

1. Process:
The main difference between STOP and ABORT is process timeout period.

STOP:
It will stop reading from the source. But it will continue updating/committing changes in the target.

ABORT:It is same as that of STOP. But ABORT has the timeout period of 60 seconds. If the session fails to update/commit the changes in the target before 60 seconds then the session will be compulsarily aborted by terminating the DTM process thread.

2. Memory release:

STOP:
STOP will release the memory block that was occupied by the session properly.

ABORT:
ABORT will not release the memory immediately (taken care of other memory release mechanisms).

3. Consistency:

STOP: Stop will try to rollback to ensure the consistency of data.

ABORT: It will kill the process immediately and can not be rolled back. (Equivalent to UNIX kill -9)

Informatica 9 Architecture

Informatica 9 Architecture

DWH - Snowflake Schema

Snowflake schema

The snowflake schema is represented by centralized fact tables which are connected to multiple dimensions. "Snow flaking" is a method of normalizing the dimension tables in a STAR schema. The principle behind snow flaking is normalization of the dimension tables by removing low cardinality attributes and forming separate tables.

Snowflake schemas are generally used when a dimensional table becomes very big and when a star schema can’t represent the complexity of a data structure. For example if a PRODUCT dimension table contains millions of rows, the use of snowflake schemas should significantly improve performance by moving out some data to other table (with BRANDS for instance). 


The problem is that the more normalized the dimension table is, the more complicated SQL joins must be issued to query them. This is because in order for a query to be answered, many tables need to be joined and aggregates generated. 

SQL - VIEWS

VIEWS

VIEW is a logical representation of another table or combination of tables. A view derives its data from the tables on which it is based. These tables are called base tables. Base tables might in turn be actual tables or might be views themselves. All operations performed on a view actually affect the base table of the view.

A view can be thought of as a stored query or a virtual table. You can use views in most places where a table can be used.
For example, the employees table has several columns and numerous rows of information. If you want users to see only five of these columns or only specific rows, then you can create a view of that table for other users to access.

You can query views, and with some restrictions you can update, insert into, and delete from views. All operations performed on a view actually affect data in some base table of the view and are subject to the integrity constraints and triggers of the base tables.

You cannot explicitly define triggers on views, but you can define them for the underlying base tables referenced by the view. Oracle does support definition of logical constraints on views.

Unlike a table, a view is not allocated any storage space, nor does a view actually contain data. Rather, a view is defined by a query that extracts or derives data from the tables that the view references. These tables are called base tables. Base tables can in turn be actual tables or can be views themselves (including materialized views). Because a view is based on other objects, a view requires no storage other than storage for the definition of the view (the stored query) in the data dictionary.

USES OF VIEWS

1) Provide an additional level of table security by restricting access to a predetermined set of rows or columns of a table.

2) Hide data complexity
For example, a single view can be defined with a join, which is a collection of related columns or rows in multiple tables. However, the view hides the fact that this information actually originates from several tables.

3) Simplify statements for the user
For example, views allow users to select information from multiple tables without actually knowing how to perform a join.

4) Present the data in a different perspective from that of the base table
For example, the columns of a view can be renamed without affecting the tables on which the view is based.

5) Save complex queries
For example, a query can perform extensive calculations with table information. By saving this query as a view, you can perform the calculations each time the view is queried.

Creating view

Syntax:

CREATE [OR REPLACE] [FORCE] [NOFORCE] VIEW view_nameAS subquery[WITH CHECK OPTION [CONSTRAINT constraint]]

FORCE : creates the view regardless of whether the view's base tables exist or the owner of the schema containing the view has privileges on them.

NOFORCE : creates the view only if the base tables exist and the owner of the schema containing the view has privileges on them. The default is NOFORCE.

PREREQUISITES:
To create a view in your own schema, you must have CREATE VIEW system privilege. To create a view in another user's schema, you must have CREATE ANY VIEW system privilege.

Example:

To create a view CSStudent (computer science students) from the Student table, enter:

SQL> CREATE OR REPLACE VIEW CSStudent AS SELECT * FROM Student WHERE Major = 'CS';

Eg2:
The following statement creates a view on a subset of data in the emp table:

CREATE VIEW sales_staff AS SELECT empno, ename, deptno FROM emp WHERE deptno = 10 WITH CHECK OPTION CONSTRAINT sales_staff_cnst;

Creating Views with check option

The query that defines the sales_staff view references only rows in department 10. Furthermore, the CHECK OPTION creates the view with the constraint (named sales_staff_cnst) that INSERT and UPDATE statements issued against the view cannot result in rows that the view cannot select. For example, the following INSERT statement successfully inserts a row into the emp table by means of the sales_staff view, which contains all rows with department number 10:

INSERT INTO sales_staff VALUES (7584, 'OSTER', 10);

However, the following INSERT statement returns an error because it attempts to insert a row for department number 30, which cannot be selected using the sales_staff view:

INSERT INTO sales_staff VALUES (7591, 'WILLIAMS', 30);

The view could optionally have been constructed specifying the WITH READ ONLY clause, which prevents any updates, inserts, or deletes from being done to the base table through the view. If no WITH clause is specified, the view, with some restrictions, is inherently updatable.

Different types of VIEWS

1) Simple Views

2) Complex Views

Simple Views:

The view which is created by selecting only one table.A Simple view does not contain functions.You can always perform DML operations through Simple views.

2)Complex Views:

The view which is created by selecting more than one tables. A Complex view contains functions.You cannot always perform DML through Complex views.

Rules for performing DML Operations on a view:

1) You can perform DML operations on Simple views.

2) You cannot always perform DML through Complex views.

3) You cannot delete a row if the view contains the following:

Group functions such as SUM, MIN, MAX, AVG and ......A GROUP BY clause.The DISTINCT keyword.

4) You cannot update data in a view if it contains,
Group functions such as SUM, MIN, MAX, AVG and ......A GROUP BY clause.The DISTINCT keyword.Columns defined by expressions such as SALARY*1.2The ROWNUM pseudo column.

5) You cannot insert data in a view if it contains,
Group functions such as SUM, MIN, MAX, AVG and ......A GROUP BY clause.The DISTINCT keyword.Columns defined by expressions such as SALARY*1.2The ROWNUM pseudo column.There are NOT NULL columns in the base tables that are not selected by the view.


Updating a Join View

An updatable join view (also referred to as a modifiable join view) is a view that contains more than one table in the top-level FROM clause of the SELECT statement, and is not restricted by the WITH READ ONLY clause.

The rules for updatable join views are as follows.

1) Any INSERT, UPDATE, or DELETE operation on a join view can modify only one underlying base table at a time.

2) All updatable columns of a join view must map to columns of a key-preserved table. If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are not updatable.

3) Rows from a join view can be deleted as long as there is exactly one key-preserved table in the join.

4) An INSERT statement must not explicitly or implicitly refer to the columns of a non-key-preserved table.

PL/SQL - TRIGGERS

TRIGGERS

A database trigger is a stored procedure that is fired when an INSERT, UPDATE, or DELETE statements is issued against the associate table. A trigger defines an action the database should take when some database related event occurs. A trigger can include SQL and PL/SQL statements to executor as a unit and can invoke other stored procedures.

USES OF TRIGGERS

1) Prevent invalid transactions

2) Enforce complex security authorizations

3) Enforce complex business rules

4) Provide auditing

5) Maintain synchronous table replicates

6) Modify table data when DML statements are issued against views

7) Publish information about database events, user events, and SQL statements to subscribing applications

PARTS OF A TRIGGER

A database trigger has three parts:

1) Triggering event or Statement.

2) Trigger constraint (Optional)

3) Trigger action

1) Triggering Event or Statement:

A triggering event or statement is the SQL statement that causes a trigger to be fired. Atriggering event can be an INSERT, UPDATE, or DELETE statement for a specific table.

2) Trigger Constraint or Restriction

A trigger restriction specifies a Boolean (logical) expression that must be TRUE for thetrigger to fire. The trigger action is not executed if the trigger restriction evaluates to FALSE. Atrigger restriction is an option available for triggers that are fired for each row. Its functionis to conditionally control the execution of a trigger. A trigger restriction is specified using aWHEN clause. It is an optional part of trigger.

3) Trigger Action

A trigger action is the procedure (PL/SQL block) that contains the SQL statements andPL/SQL code to be executed when a triggering statement is issued and the trigger restriction evaluates to TRUE.

Types of Triggers

A trigger’s type is defined by the type of triggering transaction and by the level atwhich the trigger is executed. Oracle has the following types of triggers depending on thedifferent applications:

1)Row Level Triggers

2)Statement Level Triggers

3)Before Triggers

4) After Triggers

Row Level Triggers

Row level triggers execute once for each row in a transaction. The commands of row leveltriggers are executed on all rows that are affected by the command that enables thetrigger. For example, if an UPDATE statement updates multiple rows of a table, a row triggeris fired once for each row affected by the UPDATE statement. If the triggering statementaffects no rows, the trigger is not executed at all. Row level triggers are created using theFOR EACH ROW clause in the CREATE TRIGGER command.

Statement Level Triggers

Statement level triggers are triggered only once for each transaction. Statement level trigger are thedefault type of trigger created via the CREATE TRIGGER command. Consider a case where therequirement is to prevent the DELETE operation during Sunday. For this whenever DELETEstatement deletes records, there must be PL/SQL block that will be fired only once by DELETEstatement to check that day must not be Sunday by referencing system date.

Before and After Trigger

Since triggers are executed by events, they may be set to occur immediately before or after those events. When a trigger is defined, you can specify whether the trigger must occur before or after the triggering event i.e. INSERT, UPDATE, or DELETE commands. BEFORE triggers are used, the trigger action should determine whether or not the triggering statement should be allowed to complete. By
using a BEFORE trigger, you can eliminate unnecessary processing of the triggering statement. For example: To prevent deletion on Sunday,Statement level before trigger is used on DELETEstatement. BEFORE triggers are used to derive specific column values before completing a triggering INSERT Or UPDATE statement. AFTER trigger executes the trigger action after the triggering statement is executed. AFTER triggers are used, when the triggering statement is set to completebefore executing the trigger action. For example: To perform cascade delete operation, it means that user deletes the record from one table, the corresponding records in other tables are also deleted automatically by a trigger which fired after the execution of delete statement issued by the user.

Statement Level

BEFORE option

Oracle fires the trigger only once, before executing the triggering statement.

AFTER option

Oracle fires the trigger only once, after executing the trigger statement.

Row Level

BEFORE option

Oracle fires the trigger before modifying each row affected by the triggering statement.

AFTER option

Oracle fires the trigger after modifying each row affected by the triggering statement.

Instead of triggers

Instead of triggers was first featured in Oracle 8. This was something new in the world of triggers. These are triggers that are defined on a view rather than on a table. Can use INSTEAD OF trigger to tell Oracle what to do instead of performing the actions that invoked the trigger.

Syntax of creating trigger

CREATE OR REPLACE TRIGGER trigger_name
BEFOREAFTER
DELETEINSERTUPDATE [OF column_name, ]
ON table_name
[REFERENCING {OLD AS old, NEW AS new}]
[FOR EACH ROW [WHEN condition]
DECLARE
variable declaration;
Constant declaration;
BEGIN
PL/SQL subprogram body;
EXCEPTON exception PL/SQL BLOCK;
END;



REFERENCING

specified correlation names. The user could use the Correlation names in the PL/SQL block and WHEN clause of a row trigger to refer specifically to old and new values of the current row. The default correlation names are OLD and NEW. If the row is associated with a table named OLD or NEW, this clause can be used to specify different names to avoid confusion between the table name and the
correlation name. WHEN Clausespecifies the trigger restriction. This condition has to be satisfied to fire the trigger. This condition can be specified for the ROW TRIGGER.

Example

To Create a trigger on the emp table, which store the empno and operation in tableauditor for each operation i.e. Insert, Update and Delete.

CREATE OR REPLACE TRIGGER EMP_AUDIT
AFTER INSERT OR UPDATE OR DELETE ON EMP FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO AUDITOR VALUES(:NEW.EMPNO,'INSERT');

ELSIF UPDATING THEN
INSERT INTO AUDITOR VALUES(:NEW.EMPNO,'UPDATE');

ELSIF DELETING THEN
INSERT INTO AUDITOR VALUES(:OLD.EMPNO,'DELETE');

END IF;

END;

Note: The same functionality can be achieved with the BEFORE type trigger also.

Enabling and Disabling Triggers

When a trigger is created it is automatically enabled and is triggered whenever the triggering command and the execution command is true. An enabled trigger executes the trigger body if the triggering statement is issued. To disable the execution use the ALTER TRIGGER Command with the DISABLE clause. A disable trigger does not execute the trigger body even if the triggeringstatement is issued. We can disable / enable the trigger by the following

syntax:ALTER TRIGGER DISABLE / ENABLE

Dropping Triggers

Triggers may be dropped via the drop trigger command. In order to drop a trigger, you musteither own the trigger or have the DROP ANY TRIGGER system privilege.

Syntax: DROP TRIGGER trigger_name;

Example:DROP TRIGGER rec_check;

Database Trigger V/s Procedure

Triggers don't accept parameters but procedures can accept parameters.
A trigger is automatically executed without any action required by the user. A store procedure on the other hand needs to be explicitly invoked.