Running ODSGenealogy.bat without BOMExtract

[Former Member]

Hi,

We are having issues with ODSGenealogy taking too long time because BOM's are
added frequently and the ODSBOMExtract gets too many rows to handle.

Is it possible to run the ODSGenealogy.bat script and comment out just the
ODSBOMExtract and ODSBOMExpansion part?

@echo off
title ODS Genealogy
if not exist ClientEnvironment.bat goto noEnv
call ClientEnvironment

:loop1
"%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSSFCBOMExtract %*
"%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSSFCBOMExpansion %*
rem uncomment the two lines below to keep running until extract_date_time is greater than current time - 1 hour
rem "%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" com.sap.me.ods.ODSCheckDate SFC_BOM
rem if %ERRORLEVEL% EQU 1  goto loop1

:loop2
"%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSResourceTimeLogExtract %*
"%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSResourceTimeLogExpansion %*
"%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSResourceTimeLogSummary %*
rem uncomment the two lines below to keep running until extract_date_time is greater than current time - 1 hour
rem "%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" com.sap.me.ods.ODSCheckDate RESOURCE_TIME_LOG
rem if %ERRORLEVEL% EQU 1  goto loop2

:loop3
"%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSCompTimeLogExtract %*
"%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSCompTimeLogExpansion %*
"%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSTimeBasedExpansion %*
rem uncomment the two lines below to keep running until extract_date_time is greater than current time - 1 hour
rem "%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" com.sap.me.ods.ODSCheckDate COMP_TIME_LOG
rem if %ERRORLEVEL% EQU 1  goto loop3

:loop4
"%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSBOMExtract %*
"%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSBOMExpansion %*
rem uncommented the two lines below to keep running until extract_date_time is greater than current time - 1 hour
rem "%JAVA_HOME%\bin\java.exe" -classpath "%CLIENT_CLASSPATH%" %JAVA_OPTIONS% com.sap.me.ods.ODSCheckDate BOM
rem if %ERRORLEVEL% EQU 1  goto loop4

goto end
:noEnv
echo ERROR: Client Environment file "ClientEnvironment.bat" not found.
:end
pause

Best regards,

Johan Nordebrink

[Former Member · Accepted solution]

Johan

You can do this, but you will still need the BOM data, as the ods_resource_time_member table populated in loop2 references the BOM. You could run the BOM portion in its own batch file in a loop, processing smaller timeframes until it catches up, if you know how to do that? If you have a hige number of boms created within a small timeframe, you may have to give more memory to the extract too.

[sergiy_katerinich · Accepted solution]

I doubt ODSResourceTimeLog* transactions look into BOMs. You can confirm this by turning on SQL trace for ODS and looking through the actual SQL queries.
Thus I believe loop4 can be commented out without any problem.

[Former Member · Accepted solution]

Hi Stuart,

Do you have any idea what timeframe to run with is mostly used?
If I run it with just 1 minute time-frame it can sometimes take up to 3 minutes
to process depending of if any BOM was created during that time.
I have attached a text file with the insert statement that takes about 3 minutes
to find and insert 659 rows to ODS_BOM_COMPONENT Table.

Also memory is interesting. My current ClientEnvironment.bat settings are:
set JAVA_OPTIONS=-Xms1024m -Xmx2048m

Could I increase this even further? And will it help, since the time spent anyway
seems to lie in the SQL query.

Best regards,
Johan

Problem SQL Insert.txt.zip

[Former Member · Accepted solution]

Hi Johan

Normally, you need to run it with the largest timeframe you can get away with - but if that means it's a very small timeframe, like 1 minute, it's what you need to do.

More memory won't make any difference - that's only needed if you run out of memory due to too much data to process in the selected timeframe. You just have to be patient - although 1 min of data is taking 3 mins to process, over a long period of time this will be OK, since you won't have data for every minute of the day. But the attached SQL is not helping: there is an outer join to the item table, twice, and these really need to be inner joins. An outer join is implying that there may be no matching material in the item table for a given bom component, which makes no sense -  the material must exist. This incorrect construct will be significantly affecting the query performance when you have large bom_component and item tables.

[Former Member · Accepted solution]

Hi Stuart,

Do you suggest I log a ticket for making changes to the query?

Best regards,

Johan

[Former Member · Accepted solution]

Yes, you should.

[Former Member · Accepted solution]

Hi Sergiy,

When trying to run ODSGenealogy without BOMExtract it seems to work, but
when running it repeatedly with interval 720 minutes I get an error.

After the first 720 minutes has passed it will give this error in the command prompt:

Exception in thread "main" java.lang.NullPointerException: while trying to invok

e the method com.sap.me.ods.olap.ODSBase.safeClose() of an object loaded from lo

cal variable 'base'

        at com.sap.me.ods.ODSCheckDate.main(ODSCheckDate.java:53)

In the ODS log file this is displayed, which is really interesting because I am running it on an SQL Server database.

Fri Oct 17 14:25:30 CEST 2014 null: Exception Occurred.

java.lang.RuntimeException: java.lang.ClassNotFoundException: oracle.jdbc.pool.OracleDataSource

at com.sap.me.ods.database.OracleDataSourceFactory.getDataSource(OracleDataSourceFactory.java:18)

at com.sap.me.ods.olap.ODSBase.getODSDataSource(ODSBase.java:351)

at com.sap.me.ods.olap.ODSBase.init(ODSBase.java:232)

at com.sap.me.ods.olap.ODSBase.<init>(ODSBase.java:181)

at com.sap.me.ods.ODSCheckDate.main(ODSCheckDate.java:33)

Caused by: java.lang.ClassNotFoundException: oracle.jdbc.pool.OracleDataSource

at java.net.URLClassLoader$1.run(URLClassLoader.java:255)

at java.security.AccessController.doPrivileged(Native Method)

at java.net.URLClassLoader.findClass(URLClassLoader.java:243)

at java.lang.ClassLoader.loadClass(ClassLoader.java:372)

at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:344)

at java.lang.ClassLoader.loadClass(ClassLoader.java:313)

at java.lang.Class.forName0(Native Method)

at java.lang.Class.forName(Class.java:171)

at com.sap.me.ods.database.OracleDataSourceFactory.getDataSource(OracleDataSourceFactory.java:16)

Best regards,

Johan

[sergiy_katerinich · Accepted solution]

It is simply a bug in the script - see SAP Note 1775623.

I've been fighting with it for a wile and hope that soon it will be fixed.