Informatica ETL : Convert Any date to end of day i.e. 23:59:59 or 11:59:59

SET_DATE_PART(
        SET_DATE_PART(
           SET_DATE_PART(v_Your_date_Port, 'HH', 23)
                                      , 'MI', 59)
                            , 'SS', 59)

Environment Variables now working - Command Task

If you are struggling to get a script (which is referring to some environment variable) working vai command task but the same script will just work fine on the command line try restarting the Informatica server, as for newly created environment Informatica server will not be aware of unless restarted.


Please subscribe on my YouTube Channel.

Update of record failing using Update Strategy

Check following

Go to Session Properties -> ensure we have set DataDriven

Also to further Debug, you can set Tracing Level to Verbose Data on the updateStartegy.

Please subscribe on my YouTube Channel.

Informatica variable not getting set - using setvariable

Please subscribe on my YouTube Channel.



There could multiple reasons as to why the Variable is not getting set as expected.

In my scenario, My SQL override didn't pick any data to be loaded to the target table.

The SETVARIABLE function executes only if a row is marked as insert or update. SETVARIABLE ignores all other row types and the current value remains unchanged.

At the end of a successful session, the PowerCenter Integration Service compares the final current value of the variable to the start value of the variable.

The PowerCenter Integration Service does not save the final value of a mapping variable to the repository when any of the following conditions are true:

• The session fails to complete.
• The session is configured for a test load.
• The session is a debug session.
• The session runs in debug mode and is configured to discard session output. 

If you always want your variable to be set after every run then the workaround I used it to do a union of your SQL override with dummy data row, implies you will always have at least one row to be inserted in the target table.
you later can delete this extra row using post-session SQL.


select col1, col2 from yourTargetTable where conditionThatDoesntReturnAnyRow=0
union
select 'dummy1','dummy2'

How to Remove time from SYSDATE

Please subscribe on my YouTube Channel.


Truncate TimeStamp from Sysdate.

TRUNC(SYSDATE) will remove Timestamp

TO_CHAR(SYSDATE,'YYYYMMDD') will format the required format

Session Variables not getting set using SETMAXVARIABLE, SETMINVARIABLE,SETVARIABLE,SETCOUNTVARIABLE

Make sure the Port is Variable Port.


Make sure in Transformation where you have set the variable, the port should be a Variable Port.
If you don't check it as variable port the Session variables values will not persist.

Setting Up DB2 connection String in Informatica

Please subscribe on my YouTube Channel.

1. You need to Install 64 bit client - ibm_data_server_client_winx64_v11.1 
2. Configure windows variables
1. SET DB2CODEPAGE=437
2. SET DB2HOME=C:\Program Files\IBM\SQLLIB
3. SET DB2INSTANCE=DB2
4. SET LIB=C:\Informatica\9.6.1\server\bin
5. SET LIBPATH=C:\Informatica\9.6.1\server\bin
6. SET PATH=C:\Informatica\9.6.1\server\bin\;C:\PROGRA~1\IBM\SQLLIB\BIN;C:\PROGRA~1\IBM\SQLLIB\FUNCTION
7. Execute db2cmd -i -w db2clpsetcp
3. Catalog DB2 databases: run the following commands from command line.

db2 catalog tcpip node <node name> remote <host> server <port>

db2 catalog db <database name> at node <node name>