SAP partitioning engine for Oracle

[darren_keenan]

Hello,

I am trying to use the partitioning engine ("RSORAPARTGEN" in se38) to automatically create and maintain table partitions.

When I get to table CDCLS, and click "calculate partitions", I get the following message:

"Please create for table CDCLS an index on fields MANDANT and CHANGENR with SQLPLUS...."

I created an index as requested, but when I return to the transaction and click "calculate partitions" again, I get the same message.  Is there a specific naming convention that is required or something else I need to do to get the engine to recognize that I have created the index?

Thank you.

-Darren Keenan

Basis Admin

[Reagan · Accepted solution]

Hello

What is the name of the index you have created for the table CDCLS ?

Check this SAP note 1333328 - Partitioning Engine for Oracle

If the index is created as suggested in the above SAP note then check the BASIS level of the system and if required activate the index in the ABAP dictionary using Tx SE11 and retry.

Regards

RB

[darren_keenan · Accepted solution]

Thank you Reagan.  That note did help with the syntax.  Specifically, I created the index using this command from the note:
SQL> create index "CDCLS~Y" on SAPERP.CDCLS("MANDANT","CHANGENR");

However, the index is still not recognized, so I went to SE11 as suggested.  Normally, when I enter change mode in SE11, I expect to see options for creating/maintaining associated indexes, but I did not see those options.  I thought it might be because it was a cluster table, but I went to a cluster table I am more familiar with (DFKKOP), and immediately saw the options I was looking for.

Also, for table CDCLS, when I tried to do a change, it prompted me for a registration key, and I thought perhaps the index settings were locked out without a registration key.  I registered the object, and entered the key, but I still do not see the index options.  I can see the existing, primary index, but there does not seem to be an ability to do anything but display it.

If you have any more information on this, I would appreciate it.

Thank you.

[ACE-SAP · Accepted solution]

Hello

DFKKOP is a transparent table at least on an Ehp3 system.

Creating partition on a cluster table can be a risky operation.

Are you aware that the partition engine is meant to only be used in the context of data archiving?

Why are you willing to create partitions ?

Best regards

[darren_keenan · Accepted solution]

Yes, I am trying to partition in conjunction with archiving.

However, my reference to table DFKKOP is not really part of the problem.  It was just an example of what I expected to see when using SE11 to view table CDCLS.  The main problem is getting the SAP partitioning tool to recognize that I have built the index it requested so that it will perform the analysis phase.

===============================

I responded to this popup:

... With these commands:

SQL> create index "CDCLS~Y" on SAPERP.CDCLS("MANDANT","CHANGENR");

Index created.

SQL> commit;

Commit complete.

===============================

But I still get the popup.

Thanks for trying to help though.

[Reagan · Accepted solution]

Hello Darren

In that case I am not sure where the problem is.

Maybe stupid, I would try to update the statistics of the table CDCLS and see if that helps.

Regards

RB

[Former Member · Accepted solution]

create index "CDCLS~Y" on SAPERP.CDCLS("MANDANT","CHANGENR");

What's strange in my eyes:
The 'SAPERP' is there for the table, but not for the index.
As which oracle user did you try this?

Just a wild guess, but maybe your SAP schema user doesn't see the index, because it belongs to someone else.
So i would drop the index, and recreate it like this:

create index SAPERP."CDCLS~Y" on SAPERP.CDCLS("MANDANT","CHANGENR");

regards

[Reagan · Accepted solution]

That's it. You just found the problem.

[darren_keenan · Accepted solution]

Thanks.  That fixed it.

It's my fault for not looking at the syntax, but I got the syntax directly from the note. At a minimum, the note should be fixed, but what would make even more sense is to just provide the command needed as they do after you click "calculate".

(this is displayed after you click "calculate")

Please run brspace (710) as <ora>sid to start redefinition:

brspace -f tbreorg  -a long2lob  -o SAPERP  -t CDCLS  -USD CDCLS

Thanks for all the help.  Too much time as a Basis admin and not enough as an Oracle DBA...

[Reagan · Accepted solution]

At a minimum, the note should be fixed,

I believe the note is correct. The note is suggesting you to connect as the schema user and then create the index. Here is what the note says.

> sqlplus sapr3/<passwd>
SQL> create index "CDCLS~Y" on "CDCLS"("MANDANT","CHANGENR");
SQL> create index "CDHDR~Y" on "CDHDR"("MANDANT","CHANGENR");

I even overlooked that.

Regards

RB

[darren_keenan · Accepted solution]

I overlooked it too.  I mistakenly thought that the oraSID user (which was how I was logged in) owned the schema.  I don't know if I have ever started sqlplus as sapr3 outside of a classroom.

Thanks again.

[darren_keenan]

Has anyone had an issues with table EDI40 when trying to use the partitioning engine?

This table is one of those that requires "inspection" before the partitions can be calculated.  When you click "calculate", you see this message:

Required table data are missing. Data will be

collected in a batch job. Please start batch job

with button "Inspect Table".

On prior tables, this was not an issue.  When "inspect" is clicked, you are presented with run options (foreground, background), and when the inspect job finishes, you then return and do a calculation as normal.

However, for table EDI40, when you click "inspect", it presents the run options, and even runs (verified in SM37), but when you return to the table, it presents again with an "inspect" dialog and run option as if the inspect never ran.  This is the only table where this is a problem.

I have not found any references to this table in the notes other than those that discuss conversion of field types.  I don't see any errors or warning in the job logs either.  Could this be a specific bug?

Thanks in advance.