Convert pool table to transparent table and vice-versa

[Former Member]

Hi all,

We were facing a huge performace issue with a pool table A017 (table pool - KAPOL). SAP note recommneds the conversion of pool table to transparent table.

I wanted to know if we can revert back to pool table from transparent table. We carried out the conversion on one of our servers but now cant find an option to revert back to pool table.(no checkbox in techincal settings)

Please suggest if we missed out on anything or ways to go about.

Thanks

Moderator message - Welcome to SCN.

Moderator message - Moved to the correct forum

Edited by: Rob Burbank on Sep 24, 2009 10:07 AM

[Former Member]

Hi,

DId you try the option in your Database Utility?

Another option to increase performance of this pooled table, is Turn the buffering off by selecting "Buffering not allowed". This will definitely improve the performance.

You can monitor the performance through tcode: STAD to see the difference.

Regards,

Subramanian

[Former Member]

Thanks for your reply. Yes, we have already swtiched the buffering off. There has been a marginal improvement but most of the cases still time out or takes hours long.

I did not see any option in database utility to revert back.

Any other suggetions?

Thanks

[Former Member]

Hi,

Have you looked in the correct place? The menu to change table from pooled to transparent and vice versa is not in the Technical Settings, but in the main table maintenance window of SE11, menu Extras -> Change Table Category. In my system the option to change from transparent back to pooled is available there.

Regards,

Mark

[Former Member]

Yes, I did check that option before. Its greyed out in my system. I read on documentation that you cannot convert revised tables. Can you please check if table in your system is revised?

[Former Member]

Hi,

Sorry, I wasn't aware that such a limitation existed. However to be honest I see absolutely no reason why you should ever want to convert from transparent back to pooled. To my knowledge pooled tables (unlike clustered tables) offer no performance gain. Pools were introduced in the past, as one of the other replies to your post mentions, to overcome a limitation on the maximum number of tables in some (very) old DBMS versions.

Regards,

Mark

[Former Member]

Hi Experts,

Same issue was happened to me , I did a conversion for an standard SAP table, and then we thought that its not required to convert , but now I am not able to find any option to retriew back to pooled , Possible ways i hope so we can do this only in database level , but If any body can give me a solution , It could be helpful .

Please Experts need your valuable Suggestion.

Regards,

Shiv.G

[Sandra_Rossi]

Mark explained above how to do it

[Former Member]

Hi Experts,

I checked as Mark Suggested ,

Extras -> Change Table Category , This Option is blocked for me aswell .

Regards,

Shiv.G

[ThomasZloch]

You'll have to be in change mode for this option to be available. Might require an object registration key.

What is the problem in just leaving it as transparent?

Thomas

[Sandra_Rossi]

Did you enter in CHANGE mode?

[Former Member]

OK, I've tried this out in a NW 7.0 system with a SAP table:

- Converted from pooled to transparent in SE11 (needs object key, opens repair)

- Activate -> error because it wants converting in SE14

- SE14 -> convert direct -> OK, table is now transparent

- Made a few structure changes to the table (add field etc.), activated new version

- SE11 -> Extras -> Change Table Category --> specified "Pooled" (option was not greyed out)

- Tab "Delivery and Maintenance" -> entered pool "ATAB"

- Activate -> no error but table stayed transparent

- Technical Settings: check box "Maintain as transparent table" was checked (which is why the conversion request was ignored) -> cleared the check box

- SE11 -> Extras -> Change Table Category --> specified "Pooled" (option was not greyed out)

- Tab "Delivery and Maintenance" -> entered pool "ATAB"

- Activate + SE14 conversion => table was again pooled

Could the check box in the Technical Settings be the problem?

BTW, if you feel uncomfortable testing this with a real application table, use one of the sample tables for the old SAP transport training (names BC325*), which are in every ssytem. I used BC325V00 for my test.

Rgds,

Mark

[Former Member]

Sandra:

I did look at the note you mentioned which speaks of avoiding SQL placeholders. As per my understanding of the note those SQL statements are generated by Database Interface and dont think we can change it anyhow. Upon cross-checking the code for transaction WMF1 I did not find any SELECT statements on table pool KAPOL.

Mark:

Thanks. We carried out the conversion an application table from pool -> transparent in one of our systems. The checkbox "maintain as transparent table" dissappeared.

[Sandra_Rossi]

>

> As per my understanding of the note those SQL statements are generated by Database Interface

Yes

>

> and dont think we can change it anyhow.

I can't answer to that argument based on the information I have

>

> Upon cross-checking the code for transaction WMF1 I did not find any SELECT statements on table pool KAPOL.

It's because the open sql is generic, something like SELECT * FROM (tabname) ... (tabname variable corresponds to A017, etc., but not KAPOL)

In fact, you should look at ST05 sql trace (or the cursor cache in ST04), you will be automatically directed to the source and the abap statement which triggers the query.

[Former Member]

Thanks Sandra. I dont think that is feasible when have an option to convert. We soon will be doing the conversion.

[Former Member]

Hey all,

Yes, we can convert back from transparent -> pool table. Thanks for all your inputs. We carried out the conversion. Issue resolved.

Thanks.

[Sandra_Rossi]

Please don't forget to give references. It's [note 856193 Pooled tables: SQL placeholder in the key fields|http://service.sap.com/sap/support/notes/856193].

So it seems your original problem was a performance issue. I think you should avoid to convert a table pool (I don't fully trust database utility... I advise a manual backup), are you sure what the performance issue is exactly, and can't you find another workaround, like changing SELECTs?

[Former Member]

Thanks Sandra. I second your point and its our last option to convert Pool table to Transparent table. Our main problem is while doing markdowns(IS-Retail) through WMF1 which accesses table pool KAPOL(pooled table A017). Due to the large number of entries(around 15 million) it goes into time-out. I dont think changing SELECT queries is an option as its a standard transaction. In our system the option to convert back to pool-table is disabled(mostly because it is revised table - we had unbuffered it). I wanted to know if there is a work-around. If not and we decide to go ahead with the conversion, then is there any way to revert back.

Thanks.

[Sandra_Rossi]

Thx for the info!

>

> \[...\] I dont think changing SELECT queries is an option as its a standard transaction \[...\]

Don't be too afraid of modifications of standard, in your case maybe it is a very good option. As you understood, I'm not really a fan of table adjustments: it may take too much time especially for big volumes (it's a problem for a production system), in case of adjustment error (for example, the temporary table cannot be extended (blocks) for some reasons) and a bad recovery procedure would lead to panic...

Now, as Mark says, you can convert a transparent table to a pooled table.

I think the biggest issue is the adjustment (not related to the pooled table by the way). You might look at threads about table adjustments, to search advices to reduce the risks of losing time during adjustment.

When you're done with the conversion, your feedback will be very interesting.

[ThomasZloch]

My guess on why there might be no option to go from transparent to pool is that pool tables are an obsolete relic from old times when the maximum number of database tables was limited, so several DDIC tables (especially customizing) were combined into one database table. This is not necessary anymore.

Thomas