I still have to figure out the internals of Informatica BULK target load type. How exactly it works, how it is different from its Normal load, and more importantly how its implementation is different from database to database. Today I have encountered with some issue with bulk load type enabled and ROLLBACKing the transaction (in Informatica) using Transaction Control transformation.
My idea is to issue ROLLBACK after inserting few records into the target. So, I have picked SCOTT.EMP table (in Oracle) as source with ORDER BY EMPNO.
As I wanted to issue ROLLBACK in the half i.e. after loading about 7 records out of 14, I created a Transaction Control Transformation with “IIF(EMPNO = 7782, TC_ROLLBACK_BEFORE)”
This failed with below ORA- error.

Rollback statistics:
WRT_8162 ===================================================
WRT_8330 Rolled back [6] inserted, [0] deleted, [0] updated rows for the target [EMP]

CMN_1022 [
ORA-24795: Illegal ROLLBACK attempt made

Database driver error...
Function Name : Commit
Database driver error...
Function Name : Rollback]

However, this worked well with Normal target load type. If Informatica’s bulk load type is like Oracle’s INSERT with  APPEND hint, direct transaction in Oracle should also fail, but that would not be the case. So, this makes me feel that Informatica has some special handling of BULK load type without completely throwing it to database side.

karteek@orcl10gr2>insert into /*+ APPEND */ emp select * from scott.emp;
14 rows created.
karteek@orcl10gr2>rollback;
Rollback complete.
karteek@orcl10gr2>

• APPEND hint makes the INSERT statement to write the data directly into the data blocks above the high water mark, and more over this supports only INSERT with SELECT clause, but not with VALUES clause where in we pass values for each record.
• Chances are there that Informatica is using an approach similar to BULK COLLECT INTO in Oracle. But, this might not be exactly the same approach, because BULK COLLECT is possible only in PL/SQL.

I think there are few questions that are left unanswered yet.

Update (03/08/2010): (per Srinivas comment below)

I am trying to load the below comma seperated file into a table using direct path enabled in sqlldr.
C:\Users\Karteek\test>type num.dat

1,3
2,4
3,7

SQL>desc tnum;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NOT NULL NUMBER
B NUMBER

SQL> select index_name,status from user_indexes where table_name = 'TNUM';
INDEX_NAME STATUS
------------------------------ --------
TNUM_PK VALID
SQL> select * from tnum;

no rows selected

SQL> $sqlldr karteek control=num.ctl direct=true
Password:
SQL*Loader: Release 10.2.0.3.0 - Production on Mon Mar 8 11:03:47 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.

Load completed - logical record count 3.

SQL> select * from tnum;

A B
---------- ----------
1 3
2 4
3 7

SQL> select index_name,status from user_indexes where table_name = 'TNUM';
INDEX_NAME STATUS
------------------------------ --------
TNUM_PK VALID

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options

C:\Users\Karteek\test>

Happy Holidays!!
Karteek

I just now installed and configured it, and also did a simple "Hello World” kind of test run which went fine.
Quite a simple installtion it was (especially on XP).

There are 2 differences between installation on Windows XP (32bit) and Vista:

1. appropriate .Net runtime Vista (it needs 1.1) and
2. picking up the right setup files

Windows Vista comes with .Net comes .Net 2.0. Use the below link to install .Net 1.1 on Vista. This is required for PowerCenter client applications to properly work.

http://www.mydigitallife.info/2007/12/27/install-microsoft-net-framework-11-on-windows-vista-fix-regsvcsexe-fails-error/

On Windows XP run the batch file “Informatica 8.6\Server\install.bat” for server, and “Informatica 8.6\Client\install.bat” for client (This is a standard approach).

On Vista, this is slightly different. For some reason running the same batch files on Vista in XP compatibility mode is not working (failing while creating Informatica Services, with Error Code – S. Symptoms explained here) – perhaps batch script is not initiating the original installer/.exe files in compatibility mode, may be !
So, as a workaround, run the actual installation files in XP Compatibility Mode as Administrator.
Installation files are as below (in PowerCenter 8.6.0 setup CD):

• Informatica 8.6\Server\Server\Windows\Disk1\InstData\VM\install.exe  - for installing server
• Informatica 8.6\Client\Client\Disk1\InstData\VM\install.exe  - for installing client

Installation there after is quite easy – just follow the instructions. All this worked for me. If you find any difficulty, let me know.

Hope it helps!
Karteek

In the last post I described the approach for installing Informatica Powercenter 7.1 on WIndows Vista . There was something I did on configuring Windows services which I would like to mention here.

When Informtica is installed on Windows with repository on Oracle database, it will show the following services (as display names) in “Services” window.

1. OracleServiceORCL10G (there are other Oracle services as well like, but I am not considering them for the current scenario, as this service alone is just enough to describe what I want to say)
2. Informatica Repository Server (PmRepServer)
3. Informatica (Powermart)

Previously I used to keep Oracle in “Automatic” startup option so that Oracle database is available as my machine boots up (means I do not have to manually bring it up). I had wished the same effect for both Informatica services as well, so that I did not have to start them every time I reboot my machine. Problem was that there is a dependency among these 3 services. Rep service can not start unless Oracle database is available (since Rep service requires rep database), and Informatica service can not start without Rep service in place running. Due to these dependencies among services and I did not set up any dependency rule between them, they all used to start their services at one time (after system reboots), but only Oracle used to succeed as Oracle does not have dependency on others (at least not on other 2 Informatica services).

I always overlooked the “Dependencies” tab that is there when you open properties of a service (Right click on service and choose Properties). But the last time, while I was installing Infa 7.1 on my Vista laptop I gave a close look at, and realized that that was something useful for me. You can very easily define the dependencies between services so that they start or stop in order.

The following steps need to be performed in order to create a dependency (Consider taking backup of current registry settings)

1. Run 'regedit' to open your registry.
2. Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services and locate the service that you need to set a dependency for. (here you need to search for service name, not display name i.e. locate PmRepServer not “Informatica Repository Server”)
3. Open the 'DependOnService' key on the right side. If the selected service does not have a 'DependOnService' key, then create one by right-clicking and selecting New > Multi-String Value.
4. In the value field, enter the names of all services that the current service will depend on. Each service name must be entered properly (again, it should be service name, not disaplay name) and on a separate line.
5. Click OK

Perform the step 2 and 3 for both “Informatica Repository Server” (PmRepServer) with “OracleServiceORCL10G” as DependOnService, and “Informatica” (Powermart) as DependOnService on “Informatica Repository Server”.

This setup makes sure that the service start happens only in “OracleServiceORCL10G > Informatica Repository Server > Informatica” order, and the service stop in reverse order.

There is a new Startup Type option introduced in Windows Vista called “Automatic (Delayed Start)”. I feel this is another good feature. This option delays the Automatic starting up of services for some time (may be few seconds) until the machine boot-up is completely done (you can avoid/reduce the painful slow/no responses on restarts). I enabled this option on “OracleServiceORCL10G” service. Anyway, other 2 services are now dependent on Oracle service, so enabling delayed startup type explicitly would have no additional effect, so they are just “Automatic” startup type.

Hope it helps!

Karteek