Tuesday, May 5, 2009

Pivot query in oracle

Scenario 1:
CREATE TABLE t1 (
id NUMBER PRIMARY KEY,
sum1 NUMBER,
sum2 NUMBER,
sum3 NUMBER
);
INSERT INTO t1 VALUES (1,20,40,50);
INSERT INTO t1 VALUES (2,30,20,25);
INSERT INTO t1 VALUES (3,15,60,55);
COMMIT;
ID SUM1 SUM2 SUM3
---------- ---------- ---------- ----------
1 20 40 50
2 30 20 25
3 15 60 55
SELECT id, num, DECODE(num,'1',sum1,'2',sum2,'3',sum3) data
from t1,
(SELECT '1' num FROM dual UNION ALL
SELECT '2' num FROM dual UNION ALL
SELECT '3' num FROM dual )

ID NUM data
1 1
1 2
1 3
2 1
2 2
2 3



Scenario 2:
scott@DEV816> select job, deptno, count(*) 2 from emp 3 group by job, deptno 4 /JOB DEPTNO COUNT(*)--------- ---------- ----------ANALYST 20 2CLERK 10 1CLERK 20 2CLERK 30 1MANAGER 10 1MANAGER 20 1MANAGER 30 1PRESIDENT 10 1SALESMAN 30 49 rows selected.And you would like to make DEPTNO be a column. We have 4 deptno's in EMP, 10,20,30,40. We can make columns dept_10, dept_20, dept_30, dept_40 that have the values that are currently in the count column. It would look like this:scott@DEV816> scott@DEV816> select job, 2 max( decode( deptno, 10, cnt, null ) ) dept_10, 3 max( decode( deptno, 20, cnt, null ) ) dept_20, 4 max( decode( deptno, 30, cnt, null ) ) dept_30, 5 max( decode( deptno, 40, cnt, null ) ) dept_40 6 from ( select job, deptno, count(*) cnt 7 from emp 8 group by job, deptno ) 9 group by job 10 /JOB DEPT_10 DEPT_20 DEPT_30 DEPT_40--------- ---------- ---------- ---------- ----------ANALYST 2CLERK 1 2 1MANAGER 1 1 1PRESIDENT 1SALESMAN 4

Monday, May 4, 2009

Informatica Architecture --Integration Service vs Power center Server

The differences between 7.x and 8.x is on architecture as well as The architecture of PowerCenter 8.x primarily differs from that of PowerCenter 7.x in the administration of the product. PowerCenter has now adopted a Service Oriented Architecture (SOA).The SOA architecture has replaced several servers that were used within the PowerCenter 7.x installer. A Tomcat web server is used to run an Informatica 'domain' comprising of several repositories or integration 'services'. An integration service is responsible for data movement.
SOA allows a single console tool to be used to administer the PowerCenter installer. This tool is web based and can be accessed from anywhere in your network. SOA supports several integration or repository services to run concurrently. This allows high availability to be implemented (if a server hosting an integration service fails these tasks can be 'failed over' onto another node in the domain).
Additionally PowerCenter 8.x supports the use of Java in mappings, the ability to 'push down' processing into the database and other similar improvements. There is nosignificant change in the client interface.
Additional Information: Several terms have acquired a new meaning with the change in product architecture.
PowerCenter 7.x PowerCenter 8.x R epository server and repository R epository service PowerCenter Server I ntegration service Some other enchancements like push down processing, more flexible java transformations, SQL transformations, string parsing functions are newly added to 8.x. Only change we can see in it is a single place where we do all administration stuff. Repository server is renamed as repository service and informatica pc server renamed as integration service. Not much change in client interface as we still have Repository Manager,Designer,Workflow Manager and Workflow Monitor.
Also on 8.5 and 8.6, security, user and group administration has been moved to the administration console.
When comparing to the architecture 7.1 n 8.1,DOMAIN,NODE,INTEGRATION SERVICES added to the 8.1 architectutre.DOMAIN is a web based admin console.NODE is like INF server or power centre server.INTEGRATION SERVICES is like repository server.DOMAIN,NODE,INTEGRATION SERVICES are at one place that is DOMAIN DATABASE.In 8.1 we don't have admin console n repository agent.and also you can install multiple NODES in a single machine.and you can intiate multiple processes simultaneously.In INF 8.1 has more security you can not create repository with out previliges.you no need to start the services in control panel every time.and also some of the transformations added in 8.1.They are SQL,JAVA transformations.It supports unstructured data like emails,word,pdfs.concept of flatfile updation is introduced.



Effective in PowerCenter 8.0, you might notice an increase in memory and CPU resource usage on machines running PowerCenter Services. The following factors can increase memory and CPU resource usage:
Updates to Integration Service and Repository Service processes. The pmserver and pmdtm process for the Integration Service and the pmrepagent process for the Repository Service include updated functionality that requires more memory and CPU resource usage.
High availability. You configure a domain for high availability, service restart, failover, and recovery operations.
Java Runtime Environment (JRE). The PowerCenter service-oriented architecture uses JRE to run the PowerCenter domains and nodes. Domains and nodes require more memory and CPU resources than when you run a PowerCenter Server or Repository Server in PowerCenter 7.x.
Log Manager. The Log Manager requires additional memory and CPU resources when it collects and processes a large number of log events.

Oracle 10g warehouse features

The list includes these Oracle 10g warehouse features:
Materialized Views — The Oracles materialized views (MV) feature uses Oracle replication to allow you to pre-summarize and pre-join tables.
Automated Workload Repository — The AWR is a critical component for data warehouse predictive tools such as the dbms_advisor package.
Multiple Blocksizes — All data warehouse indexes that are accessed via range scans and Oracle objects that must be accessed via full-table, or full-index, scans should be in a 32k blocksize.
STAR query optimization — The Oracle 10g STAR query features make it easy to make complex DSS queries run at super-fast speeds.
Multi-level partitioning of tables and indexes — Oracle now has multi-level intelligent partitioning methods that allow Oracle to store data in a precise scheme.
Asynchronous Change Data Capture — Change data capture allows incremental extraction, so only changed data to be extracted easily.
Oracle Streams — Streams-based feed mechanisms can capture the necessary data changes from the operational database and send it to the destination data warehouse.
Read-only Tablespaces — If you have a time-series warehouse in which information eventually becomes static, using tablespace partitions and marking the older tablespaces as read-only can greatly improve performance.
Automatic Storage Management (ASM) — The revolutionary new method for managing the disk I/O subsystem removes the tedious and time-consuming chore of I/O load balancing and disk management.
Advanced Data Buffer Management — Using Oracle 10g’s multiple block sizes and KEEP pool, you can preassign warehouse objects to separate data buffers and ensure that your working set of frequently-referenced data is always cached.