cancel
Showing results for 
Search instead for 
Did you mean: 

slow in data selection

Former Member
0 Kudos

The data selection for few BIG (e.g. PPOIX or PCL#)   tables is very slow.  The project is a  time-reduction of ERP using TDMS4.0.

In my previous similar projects, I set the parameter P_CLU to Y for tables like BKPF.

For PCL# tables, is there any effective way to improve the performance?

Thanks!!

Accepted Solutions (1)

Accepted Solutions (1)

former_member65049
Active Participant
0 Kudos

Hi,

When the data selection is slow, you need to consider below points:

  • Update the DB statistics for all tables/indexes in the sender system.
  • Check the oracle database enqueue lock event. This issue could also be due to the process waiting to insert entries into table DMC_INDXCL. This needs to be checked by the DB administrator.

  • You can also enable parallel data selection for some big tables.

         It can be done for flat objects or for structured objects.

         You need to use the view V_CNVMBTPRECALC, it maintain entries in

         table CNVMBTPRECALCOBJ.

         For ex., if a table has 20 million records then you can have the value of NUMREC as 1 million.

         This would mean that there will be a job scheduled for every 1 million records.

Hope this helps.

Regards,

Rupam

Former Member
0 Kudos

Rupam:

Thank you very much for your reply.

Because it has run for days so that I will use the 3rd way in future package.

I used following method in my previous project:

1) Kill already running data selection job (if running) in sender system.

2) Force redefine migration object using troubleshooter.

3) Execute report DMC_GENERATE_ACPLAN_DELIMITER in the central system. It generates and executes a report in the sender system to calculate the basis of the parallelization (that means, it determines key field values to be used as boundaries of the selections applied to the multiple parallel-running jobs). Note down name of report generated.

4) Go to sender system, open tcode sa38 enter above generated report and press F8. Enter Package number and execute it in background mode using menu option (program-> Execute in Background):

5) Check progress of this batch job in sm37 of sender system. Name of batch job would be same as that of report program executed in previous step

6) Once the batch job in the sender system is finished, report DMC_CREATE_PRECALC_ACP_W_DELIM can be started in the central system to retrieve the results.

7) After that, change number of maximum jobs for migration object using troubleshooter “Change Technical Settings” and start data selection activity from process tree.

In my previous project , the migration object refiened at step 2) was X_BKPF.

This time I only have 2 tables left i.e. PCL# (2 and 4).  But I can only find the migration objects:

X_PCL1 and X_PCL3.  Do you know what is the reason? Where to find out X_PCL# ( #=2 and 4)?

Thanks!

former_member65049
Active Participant
0 Kudos

Hi,

You can check the tables in 'extended process monitor', select the 'Task icon (arrow icon)' for the latest execution number. There you see the subactivities which are in 'running' status. You click on the running subactivities, there you can find the migration object names which are in running status for data selection.

Alternatively, you can go to transaction DTLMON in the central system, give the mass transfer id.

To get the mass transfer id, check the log of activity "Definition of mass transfer id for participating tables".

In DTLMON, you need to go to "Relevant tables" tab and see the tables which are in "In process" and "Not calculated" status.

Hope it helps.

Thanks,

Rupam

Former Member
0 Kudos

Rupam:

Thanks!

I am also considering following method (i.e. your way):

Automatic way to parallelize data selection: 

Maintain entry in table CNVMBTPRECALCOBJ using view V_CNVMBTPRECALC for migration object. You need to specify name of header table (in case it is structured migration object), delimiter value (maximum number of records which one batch job should handle) and name of migration object.

Fo  For Example: if number of records filled in TDMS header table CNVTDMS_05_BKPF is 10 million (You can find this information from logs of corresponding fill header activity) and you want to schedule 5 batch jobs for data selection of migration objects X_BKPF.

You need to maintain entry in stated table with CNVTDMS_05_BKPF, 2000000 and X_BKPF as name of header table, delimiter value and name of migration logic.

In case of flat migration object, name of header table would be blank.

In case of fill header migration objects, set parameter P_SEL = ‘X’ for corresponding fill header as well.

I am pretty sure that for PCL# it's going to be flat migrtion object.  But could you tell for a given table,  how to decide it's going to be a flat migration object OR fill header migration object?

Thanks!

Former Member
0 Kudos

All:

I used following method to redefine the migration objects for the PCL# table and it INDEED started with multi-threads.

However, it failed after 11 hours due to DB full.  After adding more space and re-starting the phase, it is NO LONGER multi-threads.

So I have to go thru following method before each re-start?  I did not had failure in my previous TDMS projects due to full DB.

Please advise. Thanks!

Method used:

1) Kill already running data selection job (if running) in sender system.

2) Force redefine migration object using troubleshooter.

3) Execute report DMC_GENERATE_ACPLAN_DELIMITER in the central system. It generates and executes a report in the sender system to calculate the basis of the parallelization (that means, it determines key field values to be used as boundaries of the selections applied to the multiple parallel-running jobs). Note down name of report generated.

4) Go to sender system, open tcode sa38 enter above generated report and press F8. Enter Package number and execute it in background mode using menu option (program-> Execute in Background):

5) Check progress of this batch job in sm37 of sender system. Name of batch job would be same as that of report program executed in previous step

6) Once the batch job in the sender system is finished, report DMC_CREATE_PRECALC_ACP_W_DELIM can be started in the central system to retrieve the results.

7) After that, change number of maximum jobs for migration object using troubleshooter “Change Technical Settings” and start data selection activity from process tree.

Answers (0)