Skip to Content
-3

Maintaining a growing table

Apr 25 at 07:47 AM

102

avatar image

Hello Experts,

I have a issue. I have got a table in the production where the custom table is growing based on the error log and the msgno which the customer is using to increment everytime the error is created. There is a max limit reach now.

What is the best manner in which we can handle and avoid this case in future.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Best Answer
Rob Dielemans Apr 26 at 12:17 PM
2

Hi,

The best solution would be to use SAP standard tools for logging like SLG1.

Here is a blog on this very subject by akankshi prasad

https://blogs.sap.com/2012/04/18/create-and-view-log-using-slg0-and-slg1-transaction/

It would be foolish to continue your current path since it has been shown to not be a future proof solution, it is wiser to admit your mistake and fix it.

Kind regards, Rob Dielemans

Share
10 |10000 characters needed characters left characters exceeded
Raymond Giuseppi Apr 25 at 08:31 AM
1

If you got problem with table size, you could consider archive or purge of those data. If data should be kept, use archiving, create those as described in Archive Development Kit, schedule archiving with SARA and adapt some display programs to allow read of archive.

If your got problem with key limits/size, you could add a key of time/date type like year before a range number managed key SNRO.

What you wrote is suggesting some log of errors?

" based on the error log and the msgno which the customer is using to increment everytime the error is created.

In this case why didn't you use standard logging provided by sap tools (ref: Application Log – (BC-SRV-BAL)) so getting the provided tools for no extra costs?

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Hello Raymond,

In this case what is the structure going to look like ? we got two cases here.

1. To get the new number which is exhausted because of the length.

a. Add length - option to the field

b. Add length and new field for ex timestamp (as given by Himanshu) you mean that ? here

"If your got problem with key limits/size, you could add a key of time/date type like year before a range number managed key SNRO." - Can you explain on this .

Application Log – (BC-SRV-BAL) - does not give any problem like this you mean ? and does the same support archiving of the SLG messages ?

Also I would like to mention that this is an IDOC application.

2. To maintain a mechanism to cut down record number and not to get into the dead en issue like this

As Kiran said , custom prog to maintain data number .

0

The first question you have to answer is: Do you want to keep trace of those records for a very long time or even forever (Even if nothing man builds lasts forever)

  • If no: purge oldest records, If yes: archive data (The standard Application log as both options, either set some expiration date or use archiving object BC_SBAL)
  • Then restart the range number from 0, and watch range number to prevent the duplicate keys errors.

Other solution(s) can be of the increase size of the key type, give a size wide enough to report the problem behind horizon (e.g. when technology will have changed or your retirement scheduled date...) adding a year field for example is actually add 4 character to key (Also manage range number per year)

0
Kiran K Apr 25 at 08:04 AM
1

Pras,

Btw,what do you mean by maximum limit reached now ? Is it the field length of the counter or the no.of records keep on increasing causing perfromance issues while fetching data from this table.

If you have date field in the table, then decide upon a cut over date and delete the records prior to that cut-over date.

You can develop a custom program to do this activity which can be scheduled as a background job,if required.

You can even consider the option of Acrhiving the data using SARA.

K.Kiran.

Share
10 |10000 characters needed characters left characters exceeded
Pras Venkat Apr 27 at 11:49 AM
1

Thanks Rob, Kiran, Himanshu and Jelena for the comments provided.

Share
10 |10000 characters needed characters left characters exceeded
Rob Dielemans Apr 25 at 08:43 AM
0

Hi,

What do you mean with limit reached?

Is this related to the number of unique keys, or is this storage space?

Kind regards, Rob Dielemans

Show 5 Share
10 |10000 characters needed characters left characters exceeded

Hello Rob and Kiran,

The field length of num is 6 and has messnum has reached 999999 and so to log the next error there is no free number.

1. So we could try to delete the old records to contain the size but anyway the next number is not available until we increase the length of the num field. Because the latest number is one in the latest set of records which we plan to keep now.

2. If we do the above step , it can again give the same problem in the future if the series is done ?

As kiran mentioned, a custom prog to delete records older than specific date could be done. But as of the what is the next number available until we increase the type length and what is the guarantee in future ?

0

Pras,

Whichever the prog is responsible for updating the counter, in that prog you add a logic to reset the counter to 0 once it reaches to maximum limit of 999999.

Moreover,if you share the structure of the table and what exactly is the objective of this table,may be experts can provide you with a better and permanent solution which will be maintenance free in the long run.

K.Kiran.

0

Hi,

You can change the key and adjust the table and change the abap which fills this table.

How long did it take to get to a million entries?

Kind regards, Rob Dielemans

0

Hello Kiran, Rob and Raymond

The structure is here. The Key MSGNUMBER is the 99999 field

It does not use any range I checked now.

It uses a max of statement and then adds a number in the program to log changes.

1. As Raymond said

a. I could delete the entire table contents - It could automatically make the counter start from 0 again but the problem would repeat in future. In this case we could add a new field year and then combine with MSGNUMBER as said by Raymond ? and adapt the program for the same to use the year concept.

b. On the archiving part If I have to create a archive , which I have not done until now , does it require to hold the same structure that is present now ? - I understand it is a deep question.

Current talks on deleting the entries in the table and any suggestions in this line would be straight forward helpful.

key.png (10.9 kB)
0

If this is an "error log" table, as you were saying, then why would you need archiving? Archiving is needed for the business information that has a legally required retention period (e.g. invoices may have to be kept for N years). The data in utility / technical tables is simply deleted periodically.

This looks like a poor key choice for such table but, unfortunately, now you're stuck with this. As far as "band-aid" level solutions go, as others said, you'll need to modify the code that updates the counter. You could use the standard number range functionality (SNRO transaction) for that. Those ranges can be "rolled" automatically, there is a flag for that. This feature has been discussed on SCN before, search in Google.

But a better solution would be to just redesign this whole thing. I'm wondering why do you even need a custom table when we have standard application log functionality. It comes with the classes these days, has its own display transaction and cleanup process. Or if you must have a Z table then at least it needs to have the keys that are better suitable for the expected data volume and usage. You could use some generic number like GUID and that should prevent from running out of keys for a very long time. Or you could choose a different combination of fields as a key, depending on how this data is accessed. (This part is not clear yet, so it's hard to offer more specific suggestions.)

Either way, you'll need a program to delete the old entries and it should run periodically in a background job.

2
Himanshu Gupta
Apr 25 at 08:06 AM
0

Hi,

you can use the timestamp value (Date +time) with the increment number in case many message at same timestamp .

Regards

Himanshu

Share
10 |10000 characters needed characters left characters exceeded