How to check the status of node manger and to kill the node manager.

Below commands are used to check the running applications.

check using ps -ef | grep weblogic to see if any weblogic services left running.

check using ps -ef | grep node to see if any nodemanager services left running.

Below commands are used to kill the node manager and weblogic.

 kill -9 PID to kill them (PID is first number in ps -ef)

 

ORA-01840: input value not long enough for date format

Issue: when we create a virtual column with DATE datatype,we should have the proper value for the physical column.
Ex:We created a virtual column X_CAL_WEEK_END_DT based on the physical column CAL_WEEK_END_DT_WID.

CAL_WEEK_END_DT_WID having the date values in the following formate YYYYMMDD.But one value has 0.Without knowing the value of zero(0),we created Virtual column with below script

ALTER TABLE w_day_d ADD x_CAL_WEEK_END_DT GENERATED ALWAYS AS (TO_DATE(TO_CHAR("CAL_WEEK_END_DT_WID"),'yyyymmdd'))  VIRTUAL;

The above alter script can not covert the value ZERO(0) to DATE.

FIX:Changed the alter script like below.
ALTER TABLE w_day_d ADD x_CAL_WEEK_END_DT GENERATED ALWAYS AS (CASE  WHEN "CAL_WEEK_END_DT_WID"<>0 THEN TO_DATE(TO_CHAR("CAL_WEEK_END_DT_WID"),'yyyymmdd') END)  VIRTUAL;

ORA-00938: not enough arguments for function

Issue:Coalesce function is uesd in the map without placing sufficient arguments.
Ex:COALESCE(
(CASE
WHEN
 SQ_W_MCAL_PERIOD_D.ADJUSTMENT_PERIOD_FLG = 'Y' THEN
 SQ_W_MCAL_PERIOD_D.MCAL_PRIOR_PERIOD_WID
ELSE
 LKP_PERIOD_PRIOR.ROW_WID

END )
)  AS MCAL_PRIOR_PERIOD_WID

Fix:For example if we miss the replacing value in the COALESCE function then will get this error.So we need to place arguments properly.
 Ex:
 COALESCE(
(CASE
WHEN
 SQ_W_MCAL_PERIOD_D.ADJUSTMENT_PERIOD_FLG = 'Y' THEN
 SQ_W_MCAL_PERIOD_D.MCAL_PRIOR_PERIOD_WID
ELSE
 LKP_PERIOD_PRIOR.ROW_WID

END )
,0)  AS MCAL_PRIOR_PERIOD_WID

TO_DATE_WID function in ODI

TO_DATE_WID function is used to convert the DATE(07/24/2015) to DATE_WID(20150724)(YYYYMMDD) in ODI.

How to write sql query in ODI

There are two possible ways to use sql query as a source.
1)Using knowledge module as SQL AS SOURCE
2)Using the property as OBI_SOURCE_SQL_OVERRIDE in the knowledge module IKM BIAPPS Oracle Control Append(this KM will be available in Biaaps11.1.1.8.1,etc...).

                                      Steps to Implement this scenario using BIApps Knowledge Module
                                  --------------------------------------------------------------------------
Step 1:Create temp interface
Step2:Create a temp table with the required columns in the interface like below.

Step3:Click on flow tab-->select knowledge module as IKM BIAPPS Oracle Control Append or IKM BIAPPS Oracle Incremental Update.
Step4:Select the property as OBI_SOURCE_SQL_OVERRIDE in the knowledge module IKM BIAPPS Oracle Control Append or IKM BIAPPS Oracle Incremental Update and then write the query like below.

 Note:The number of ports in the temp table and the number of columns in the select list should be match.
Note: No need to drag any source table.Here sql query as source.
Step5:Click on save
Step6:Create a new interface and drag the temp interface in the workspace of new interface.
Step7:Drag the target datastore from the models.Click yes to auto mapping.

Step8:Click on source table then enable the property as use temporary interface as Derived table(Sub-select).

Step9:Click on flow tab then select knowledge module as IKM BIAPPS Oracle Control Append or IKM BIAPPS Oracle Incremental Update.
Step10:Click on save.
Step11:Execute the interface.

ODI-1228:Task IS_INCREMENTAL(Variable) fails on the target connection (BIAPPS 11.1.1.8.1)

Cause:Step name of IS_INCREMENTAL in package is IS_INCREMENTAL.Step name of IS_INCREMENTAL in package should be with the name Refresh IS_INCREMENTAL.

Solution:Step name of IS_INCREMENTAL in package changed from IS_INCREMENTAL to Refresh IS_INCREMENTAL.

ORA-01850: hour must be between 0 and 23 in ODI

Cause:In Date column Hour part (value) is getting 240000 from source table.

Solution:Hour value need to be corrected in Source table.To resolve this issue,we have corrected the hour value from 24 to 235959 in source table.

How to use select statements to columns of target table in ODI (BIAPPS 11.1.1.8.1)

We can use select statements to columns of target tables.Below screen shot showing how to use select statements for columns of target tables.

But that select statement should not return more than one row.This select statement should be in braces.

Changed on Date logic in ODI(Biapps 11.1.1.8.1)

Changed on Date logic in ODI (Biapps11.1.1.8.1) if source system is JDE
----------------------------------------------------------------------
RUN_REPLICATED_TRANSACTIONAL('#IS_SDS_DEPLOYED',
JDE_DATE_CONV(TO_INTEGER(SQ_F4140.PJUPMJ),
TO_INTEGER(SQ_F4140.PJTDAY)),
SQ_F4140.CDC$_SRC_LAST_UPDATE_DATE)

The above function will work based on the IS_SDS_DEPLOYED variable.
If IS_SDS_DEPLOYED=N then JDE_DATE_CONV(TO_INTEGER(SQ_F4140.PJUPMJ),TO_INTEGER(SQ_F4140.PJTDAY))--JDE_DATE_CONV function will convert the JDE formate to the reguler formate like MM/DD/YYYY
ELSE
F4140.CDC$_SRC_LAST_UPDATE_DATE--(Golden gate date)

Implementation OF JDE_DATE_CONV FUNCTION
---------------------------------------------------------

CASE
WHEN $(DATE_PART)=0 OR $(DATE_PART) IS NULL THEN NULL
ELSE
DATEADD(day,($(DATE_PART)-(1000*(ROUND(($(DATE_PART)/1000),0)))),DATEADD(year, ROUND(($(DATE_PART)/1000),0), '1899-12-31 00:00:00'))
END

ORA-02287 :Sequence number not allowed here in ODI

Cause:Update option is enabled or checked for row_wid  in interface.Due to this row_wid also be including in the update statement.

Solution: Update option  need to be unchecked.So that row_wid wil be excluded in the update statement.

ODI-1519 ERROR

In BIACM,the load plan got failed with ODI-1519.

CAUSE:

END_DATE in BIACM parameter is not high enough.

SOLUTION:

Please check the value of the parameter END_DATE in BIACM.If it is set to DEC 31of 1950,then it needs to set to large/higher value that covers the year 2015(e.g.,set it to Dec 31 of 2050).Once this is re-set,the fix is noticed in the next run.