DATAWAREHOUSE CONCEPTS

Saturday, 25 April 2015

Informatica Repository Query to Find All the Source and Target connections in Your Respository

The below Informatica repository query can be used to get the Source Connections, Target connections of all the instances used in the informatica.

Apart from that we have details of the Folder, mapping name ,session name , and the connection name as shown in Informatica.

SELECT DISTINCT

C.SUBJECT_AREA FOLDER_NAME,
C.MAPPING_NAME MAPPING_NAME,
C.SESSION_NAME SESSION_NAME,
E.READER_WRITER_TYPE,
E.INSTANCE_NAME,
E.CNX_NAME,

CASE
WHEN E.WIDGET_TYPE = 2
THEN 'TARGET CONNECTION'
ELSE
CASE
WHEN E.WIDGET_TYPE IN (1, 3, 56, 45, 55, 84)
THEN
'SOURCE CONNECTION'
ELSE
NULL
END
END CNX_TYPE

FROM INFA_REPO.REP_VERSION_PROPS A,
     INFA_REPO.REP_USERS B,
     INFA_REPO.REP_LOAD_SESSIONS C,
     INFA_REPO.REP_REPOSIT_INFO D,
     INFA_REPO.REP_SESS_WIDGET_CNXS E

WHERE ( A.USER_ID = B.USER_ID
AND C.SESSION_ID <> D.REPOSITORY_ID
AND C.SESSION_ID = A.OBJECT_ID
AND C.SUBJECT_ID = A.SUBJECT_ID
AND A.OBJECT_TYPE = 68
AND C.SESSION_ID = E.SESSION_ID
AND C.SESSION_VERSION_NUMBER =
E.SESSION_VERSION_NUMBER)

Sunday, 29 March 2015

INTERNET OF THINGS (IOT)-MEANING AND EXAMPLES

The Internet of Things (IoT) is a fast emerging network of IP-connected devices and the communication (transfer of data) that occurs amid these devices without any human interface. IoT devices utilizes wireless sensor networks, control systems, micro-electromechanical systems (MEMS) and the Internet to interact with external environment.

Kevin Ashton, cofounder and executive director of the Auto-ID Center at MIT, first mentioned the Internet of Things: "All the information available on internet was captured or created by human beings(by typing, pressing a record button, taking a digital picture or scanning a bar code) with limited accuracy and attention.Instead if we could use computers to track things then we could know when things needed replacing, repairing or recalling and whether they were fresh or past their best".

Examples that come under Internet of Things consist of thermostats, cars, electronic appliances, lights in household and commercial environments, alarm clocks, speaker systems, vending machines and more.Few other scenarios where IoT comes into picture are

Automobile with built in sensors to alert the driver in case of any concern.Its possible to detect when components like batteries will fail and replacement is needed.
Printers detects its low ink level and order for replacement.
Stock-out prevention through connected and intelligent supply chains.
A person with a heart monitor implant.

Objects that are part of IoT becomes connected to the surrounding objects and helps in creating creating a network of connections between secluded data sources.

Important thing here is the handing over of IP address for these IoT devices.According to Steve Leibson, who identifies himself as “occasional docent at the Computer History Museum,” the address space expansion means that we could “assign an IPV6 address to every atom on the surface of the earth, and still have enough addresses left to do another 100+ earths.”

SECURITY in the IoT:

As the number of IoT devices augments the security concerns for this also increases.Studies disclose that by 2020 there will be around 50 billion connected devices. Hackers can make use your data and can change your requirement.Consequently its needed that we look after the IoT devices against malware, spyware, worms, and trojans.

If you like this post, please share it on google by clicking on the Google +1 button.

Please go through our latest post TOP 6 BIG DATA TRENDS IN THE NEAR FUTURE

Monday, 23 February 2015

READ ONLY TABLES In Oracle 11g

Since Oracle 11g we can place a table in read -only mode with the ALTER TABLE …READ ONLY statement. Read-Only tables are like normal tables, the only dissimilarity being it disallows any attempt to modify the data. Any DML (insert/update/delete/truncate) and certain DDL statement that affects the data are not allowed on these tables.

SYNTAX

ALTER TABLE SALES_TEST READ ONLY;

To Alter a table to read-only mode you must have ALTER ANY TABLE privilege on the table.

If you try to insert a record into this table now,

INSERT INTO SALES_TEST VALUES (100);

Error:

Update operation not allowed on the table.

Even though the table is read –only mode, index creation statement is possible.

CREATE INDEX SALES_TEST_IND ON SALES_TEST (ID);

Index Created.

To Revert back the table to READ/WRITE mode.

SYNTAX

ALTER TABLE SALES_TEST READ WRITE;

Prior 11g Read-only mode could only be associated to database and tablespace level. Previously to make a table read-only we have to grant SELECT privilege to appropriate users on the table. But still the table will be available for read and write to the creator of the table. With the new Oracle 11g enhancement no DML will be allowed on the table even by the owner of the table.

How To Check A Table Is In Read Only Or Not?

To identify the mode of the table you can use the READ_ONLY column. Its set to YES if table is read only and NO when table is not read-only.

SELECT TABLE_NAME ,READ_ONLY FROM USER_TABLES WHERE TABLE_NAME=’SALES_TEST ‘

The best example for tables which makes sense in read-only mode is the configuration tables that are not to be modified after installation. The same is applicable for tables that store historical or reference data.

Operations like DML, TRUNCATION, DROP, and FLASHBACK are not permitted on READ ONLY TABLE.

Read the most discussed topics of DATAWAREHOUSECONCEPTS- TOP 10 TOPICS OF 2014

VIRTUALCOLUMN IN ORACLE 11G

INVISIBLEINDEXES - ORACLE 11G NEW FEATURE

INTRODUCTIONTO NEWSQL

HOW TO UNLOCKTHE LOCKED TABLE IN ORACLE

ORACLEDATABASE 12C NEW FEATURE-FETCH& OFFSET

Sunday, 11 January 2015