Export does not pickup whr-files generated by ORA PL/SQL splitter for table splitting

[former_member240105]

Dear Community,

                            We are doing POC for BI (640 version ) from HP-UX to LINUX on Oracle DB and using MIGMON option manually for SAPINST 640 version and have  used latest R3load,R3szchk,R3ldctl for this. Due to size of tables ( PSA and ODS - One PSA table took 15 hours to export with 150 gig) and the fact that R3ta taking long time to split those tables, we considered to use Oracle PL/SQL splittter for top 20 tables for table splitting which does in couple of minutes ( included partition tables ).WHR files generated by this splitter were moved to DATA directory.

Issue : Whr files generated by this PL/SQL splitter are not picked up by Migmon during export and on analysis observed that STR files not generated for these tables and are still part of SAP standard packages (SAPDODS and SAPDFACT) - ( reason might be because of these tables not considered to be part of separate package ). In the same installation and at the  same time , used another option splitting tool parameters ( Largest tables in separate packages and number of tables to be extracted -10 ) for which STR files generated , since we have not controlled this ( no input file given for predefined tables to be extracted ) , 5 tables were considered in common for both options for which STR files generated and picked up my R3load. How do we make other whr files ( PL/SQL splitter )  for 15 tables to be picked up by Migmon for which STR files not generated? Do we need to include these top 20 tables split manually by Oracle PL/SQL splitter in separte text file somewhere  ? 

Following options used in export_monitor_cmd.properties.

server

dbType=ORA

exportDirs=/export/exp_640/ABAP

installDir=/export/AWP_Export

orderBy=name

r3loadExe=/sapmnt/AWP/exe/R3load

tskFiles=yes

dataCodepage=4103

jobNum=6

monitorTimeout=30

loadArgs=-stop_on_error

net

netExchangeDir=/export/exp_640/ABAP/netExchange

trace=all

Appreciate your help in explaining and guiding us for this procedure in right direction.

Thanks,

Hari B

[Former Member · Accepted solution]

hello,

The error is very clear you need the STR file to get it work. Does the file exists in DATA folder? Normally the STR files are generated by R3ldctl process.

Could you please paste _BIC_B0000403000-1.log and R3ldctl.log to continue helping you.

Michael

[former_member240105 · Accepted solution]

Thanks Michael,

[former_member240105]

We are able to find out why .whr files generated by table splitter not picked up by R3load. The reason is first of all STR files not generated for these splitted tables by R3ldctl, the issue was resolved after giving the input file for all of these files under Predefined tables to be Extracted option in Package splitting.

I have few more questions. How can I control option 2 ?

Question 1 -

option 1 - Largest tables in separate packages ( partition tables are not considered for BI system ) -  Picks up top tables based on size and input values excluding partition tables.

option 2 - Predefined tables to be Extracted with input file - Works fine as I am controlling it and can give partition tables here.

The question is does Option 2  still works if I don't select option 1 in package splitting tool ? I mean check mark box.

Question 2 -

We tried unsorted for one of the big partition table without split that took around 11 hours (/BIC/BXXXX - 150 Gig )  Vs sorted with table spitting. But to our surprise both took more or less equal amount of time. Our assumption was unsorted will cut down total amount of time by half i.e around 5 hours. Then what is the use of unsorted option then except the  no usage of temp tablespace in oracle.

Question 3 -

While doing unsorted export, we have maintained ddlFile and orderBy text file with all packages except the two which are supposed to be exported in sorted order. But R3load still considered those two packages which I have not maintained in orderBy text file.

orderBy= OrderBy.txt

ddlFile = /export/exp_640/ABAP/DB/DDLORA_LRG.TPL

ddlMap =

I specified top 10 tables at the top then packages next but during export those tables are considered at the last.

Is it the correct way or am I missing something here ? Do I need to specify ddlMap for this ? 

Appreciate your help and valuable information in this regard.

[former_member240105]

Forgot to post the logs from export_monitor.log.

I see the following files in DATA directory.

ls -ltr _BIC_B0000403000*

-rwxrwxr-x   1 awpadm     sapsys          89 Apr 11 13:07 _BIC_B0000403000-1.WHR

-rwxrwxr-x   1 awpadm     sapsys         120 Apr 11 13:07 _BIC_B0000403000-2.WHR

-rwxrwxr-x   1 awpadm     sapsys         120 Apr 11 13:07 _BIC_B0000403000-3.WHR

-rwxrwxr-x   1 awpadm     sapsys         120 Apr 11 13:07 _BIC_B0000403000-4.WHR

-rwxrwxr-x   1 awpadm     sapsys          90 Apr 11 13:07 _BIC_B0000403000-5.WHR

-rwxrwxr-x   1 awpadm     sapsys         567 Apr 11 13:07 _BIC_B0000403000.log

Logs from export_monitor.log for the above table.

ERROR: 2014-04-12 17:52:14 com.sap.inst.migmon.LoadTask run

Unloading of '_BIC_B0000403000-1' export package is interrupted with exception.

java.io.FileNotFoundException: DATA/_BIC_B0000403000.STR

        at com.sap.inst.migmon.LoadTask.buildTaskFileCommand(LoadTask.java:463)

        at com.sap.inst.migmon.LoadTask.processPackage(LoadTask.java:267)

        at com.sap.inst.migmon.LoadTask.run(LoadTask.java:195)

        at java.lang.Thread.run(Thread.java:579)

        at com.sap.inst.lib.util.ThreadDispatcher$ThreadWrapper.run(ThreadDispatcher.java:113)

ERROR: 2014-04-12 17:52:14 com.sap.inst.migmon.LoadTask run

Unloading of '_BIC_B0000403000-2' export package is interrupted with exception.

java.io.FileNotFoundException: DATA/_BIC_B0000403000.STR

        at com.sap.inst.migmon.LoadTask.buildTaskFileCommand(LoadTask.java:463)

        at com.sap.inst.migmon.LoadTask.processPackage(LoadTask.java:267)

        at com.sap.inst.migmon.LoadTask.run(LoadTask.java:195)

        at java.lang.Thread.run(Thread.java:579)

        at com.sap.inst.lib.util.ThreadDispatcher$ThreadWrapper.run(ThreadDispatcher.java:113)

TRACE: 2014-04-12 17:52:44 com.sap.inst.migmon.LoadTask run

Unloading of '_BIC_B0000403000-5' export package is started.

TRACE: 2014-04-12 17:52:44 com.sap.inst.migmon.LoadTask run

Unloading of '_BIC_B0000403000-3' export package is started.

TRACE: 2014-04-12 17:52:44 com.sap.inst.migmon.LoadTask run

Unloading of '_BIC_B0000403000-4' export package is started.

ERROR: 2014-04-12 17:52:44 com.sap.inst.migmon.LoadTask run

Unloading of '_BIC_B0000403000-5' export package is interrupted with exception.

java.io.FileNotFoundException: DATA/_BIC_B0000403000.STR

        at com.sap.inst.migmon.LoadTask.buildTaskFileCommand(LoadTask.java:463)

        at com.sap.inst.migmon.LoadTask.processPackage(LoadTask.java:267)

        at com.sap.inst.migmon.LoadTask.run(LoadTask.java:195)

        at java.lang.Thread.run(Thread.java:579)

        at com.sap.inst.lib.util.ThreadDispatcher$ThreadWrapper.run(ThreadDispatcher.java:113)

ERROR: 2014-04-12 17:52:44 com.sap.inst.migmon.LoadTask run

Unloading of '_BIC_B0000403000-3' export package is interrupted with exception.

java.io.FileNotFoundException: DATA/_BIC_B0000403000.STR

        at com.sap.inst.migmon.LoadTask.buildTaskFileCommand(LoadTask.java:463)

        at com.sap.inst.migmon.LoadTask.processPackage(LoadTask.java:267)

        at com.sap.inst.migmon.LoadTask.run(LoadTask.java:195)

        at java.lang.Thread.run(Thread.java:579)

        at com.sap.inst.lib.util.ThreadDispatcher$ThreadWrapper.run(ThreadDispatcher.java:113)

ERROR: 2014-04-12 17:52:44 com.sap.inst.migmon.LoadTask run

Unloading of '_BIC_B0000403000-4' export package is interrupted with exception.

java.io.FileNotFoundException: DATA/_BIC_B0000403000.STR

        at com.sap.inst.migmon.LoadTask.buildTaskFileCommand(LoadTask.java:463)

        at com.sap.inst.migmon.LoadTask.processPackage(LoadTask.java:267)

        at com.sap.inst.migmon.LoadTask.run(LoadTask.java:195)

        at java.lang.Thread.run(Thread.java:579)

        at com.sap.inst.lib.util.ThreadDispatcher$ThreadWrapper.run(ThreadDispatcher.java:113)

[former_member185239]

Hi Hariharan,

Kindly paste the logs _BIC_B0000403000.log from data directory.

With Regards

Ashutosh Chaturvedi

[former_member240105]

Hi Ashutosh,

                    Here is the PL/SQL splitter log.I don't see any export log for this table.

awpadm 38> more _BIC_B0000403000.log

Oracle table splitter Version: 2.0.1 at 2014.04.10 17:49:15

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

PL/SQL Release 10.2.0.4.0 - Production

CORE    10.2.0.4.0      Production

TNS for HPUX: Version 10.2.0.4.0 - Production

NLSRTL Version 10.2.0.4.0 - Production

Splitting table: /BIC/B0000403000

Using Test Level: 0

Rows: 57053320 Size: 34519 MB

Splitting partitioned table /BIC/B0000403000 by partition key

in 5 packages.

Splitting of table /BIC/B0000403000 successfully completed, needed 26100 msecs CPU time, 84880 msecs elapsed time.

Regards,

Hari B

[Sriram2009]

Hi Hari

Could you share the details of SAP BI Version & kernel . Support package level?

Regards

Sriram