how to execute Oracle merge statement in ADF v copy data ?

Copy data activity expects a mapping between source and sink to insert record from source to target.

In real time, especially for scd type 1 data load, you may require insert, update and delete statement execution.

Adf doesn't support scd type 1 for Oracle db source.

Resolution:

Oracle supports the above through merge statement.

For implementing above in copy data, I have done the trailing:

1. The select SQL statement used in merge clause be used as source SQL. In filter ad 1=2

2. The update sink connection with target table in merge clause

3. In sink section, execute merge clause statement in pre-copy script section

4. Debug for insert , update and delete use cases.

Let me know if you face any challenges.

P.S: we don't need to call procedures to achieve the above

Error while Copying data from Oracle 12c to Oracle 19c db using ADF V2 copy data activity

In ADF pipeline, while I use copy data for connecting to Oracle 19c Database, i get following error:

Components Used :

Copy data source / sink uses Oracle 19c and connectivity is established through Oracle linked service with SHIR run time

While copying data from azure sql to oracle I am getting following error.

{
    "errorCode": "2200",
    "message": "Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver]Data type for parameter 3 has changed since first SQLExecute call.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [HY000] [Microsoft][ODBC Oracle Wire Protocol driver]Data type for parameter 3 has changed since first SQLExecute call.,Source=msora28.dll,'",
    "failureType": "UserError",
    "target": "Copy_26n"
}

Resolution:

While creating linked service for Oracle 19c DB, add additional connection properties:

Property name: workarounds 

Value: 536870912

More products, My account link is not appearing answers

I was facing a peculiar problem with Siebel- OBIEE integrated environment.

Even though I am administrator and I have admin privilege in Siebal , catalog I could not see dashboard, answers, more products, log out in answers.

I suspected it related to integrated environment. So launched answers link separately. That is, right click on Answers and open in new window.
Now pass your user id and password as parameter by
https:///Analytics/saw.dll?Answers&NQuser='userid'&NQPass='pwd'.
It worked wonder.

OBI Publisher with empty OBI catalog

yesterday we installed OBI publisher in our development environment and today i was troubleshooting issue with config, integrating it with presentation service.
here is scenario:
1. creating report using BI answers as data source.
2. select the report you want to use. it gives empty BI Catqalog message.
on analysis, we found that it's calling saw_popup.jsp. for troubleshooting, we introduced few out.print statement. This inturn gave following error:
Bad envelop tag: HTML. this clearly indicates that it's related SSO.

siteminder is not supported by Oracle for marketing Integration. we suspected that it could be the same issue here as well. so we created a virtual path analytics_r which is unprotected by siteminder. at the same, it has access only to web, app server of analytics.

after making these changes, OBI worked fine.

Please note: virtual path unprotected is case sensitive.

Issues - runing DAC/OC4j as windows service

i found some issues with DaC windows service. for some reason, it's not reading customsql folder.
i will update you when it's fixed

- jvm.dll from server folder didn't help

how to create COB/stand by repository for Informatica

In most of the corporates have Disater recovery or failover environments.
Practically,in Informatica 8.1 you can create only 1 domain per DB. For Disater Recovery, you may require to use same DB by informatica application in different server/ you will be using differnt db all together.

The very problem comes when you go for installation.

What we did..
for Stand by application, we installed it by pointing to dev db with same Domain name , node name, port as in live server.

after installation, we updated the repository so that it's pointing live informatica repository DB.

If you want to use the informatica application permanently by retiring the live server, then, you can update the hostname of 'PCSF%' and OPB_Reposit tables.

if you want to use it as stand by then update the hosts file with live server name and ip address of stand by server

obiee presentation service in hanging state - troubleshoot

After starting obeii service it was hanging and never went to started state. On Analysis nothing notable found in SAWlog.log. On futher digging found following message in the event viewer..
" The description for Event ID[21] in source[oracle BI Utility ] cannot be found. The local computer may not have the neccessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event . Invalid error message catalog d:\Apps\OracleBI\server\Locale\NQErrorMsg.cat"

Troubleshooting : NQErrorMsg.cat is in corrupted state(instead of NQErrorMsg found NQErrorMsg_L_en.cat). Replace the files from install setup.

Unable to open Admin tool in OBIEE / siebal analytics

I am unable to open Admin tool in OBIEE/ siebel analytics
- check the NQSConfig
- check with nqsconfig which came along with installation
- if it works fine with original config file, then the problem should be with your NQSConfig.
- in such scenarios, even your OBI server service won't start.

Failed to open SMTP server

Failed to open SMTP server.
- ping the smtp server and see if ping is successful.
- You will also get this error if the smtp server recently configured. Meaning the configuration is wrong.
- select the oproperty of the smtp server and check if the ip address is perfect.

DAC Client using pmrep and pmcmd

How to Verify that the DAC Client is able to use pmrep and pmcmd
From a Windows command prompt, execute pmrep and then pmcmd. The test is
successful if the pmrep and pmcmd prompts appear.
If pmrep and pmcmd are not recognized, then:
■ Ensure Service Pack 4 has been applied to Informatica PowerCenter 8.1.1.
■ Verify that the INFA_DOMAINS_FILE variable points to the domains.infa file
located in the Informatica directory.
■ Verify that the PATH variable includes the path to the Informatica binaries
(\Informatica\PowerCenter\client\bin), and that pmrep.exe and pmcmd.exe
exist in the \bin folder.