partitioning cluster cdcls

[Former Member]

Hello Forum

has anybody ever succeeded in changing CDCLS or another cluster tablespace to partition-by-growth ? After our first test with a "normal" tablespace we tried to convert CDCLS with SE14. The problem is that after changing the storage parameters there is no possibility to "force the conversion" by going to the EXTRA button. CDCLS is our biggest tablespace in production which really needs to be converted before reaching 64GB limit. SAP BASIS 7.00 level 20.

Best regards

Martin Ketterer

[Former Member]

Dear Martin,

you can't imagine how impressed I am to find a thread about the partitioning-by-growth for the CDCLS!!! Exactly this I wanted to do today and couldn't find out, how. Normally we do partitioning by growth in this way:

Unload the data with host tools.

Specify the parameters in SE14 and activate and adjust the database with the option to delete the data.

Reload the data with host tools.

So, I don't know, what ICNV is? Can you tell me? If not, I had to partition this table "by hand". Thats not so comfortable.

Thanks a lot!

Agnes Norwidat

[Former Member]

Hello Agnes

well I can tell you some experiences we made. First of all there was a need to convert CDCLS because we were not able to archive data in time, i.e. we are now facing the 64GB problem. The first test we made in a sandbox system showed us how to convert a transparent table to UTS PBG via SE14 (not by DB2 means UNLOAD/LOAD). In this system we then tried to convert CDCLS which is very small in the same way but this was not possible. So we searched for SAP notes on that issue and even opened a message to SAP and got the information that there were two ways to do it :

first of all unload the data , drop/create the tablespace as you mentioned it, and load the data into the new object. A test showed us that we would have to face about 5 hours runtime in production in that case. Since CDCLS is a very important table this would mean offline time.

the second way was then using ICNV (=incremental conversion). Although this is not (yet) an officially published possibility I got the information from SAP (in the message) that it already had been used by customers. The benefit of ICNV is that you have a conversion in an online system. You can roughly compare it to DB2 online reorg with the exception of the runtime. A good source of information is the "i" button on screen of transaction ICNV (try it and read it through, it is worth while). For large tables it is important to be aware of the "switch phases" at the beginning and at the end because there is a lock from SAP and programs will abend.

We are now in the phase of converting our consolidation system's CDCLS which really is very slow - SAP is informed. I can continue with an experience report when there is news.

Best regards

Martin

[Former Member]

Hello Agnes

I can not recommend to use ICNV in order to avoid the 64GB limit problem. It runs terribly slow which is "works as designed". You can count with around 2000 rows per minute in a production system - the problem was that we tested it i a sandbox system with no users on it. So we got around 8000 rows per minute in the sandbox and the deterioation in production with 2000/min. You can easily get an approximate runtime by dividing your total rows of a table with 2000 and you only have to convert minutes runtime into weeks ....

Best regards

Martin

[marco_mica]

I know from conference calls with other SAP customers in NA, that they had converted both CDCLS and RFBLG tables to partition by growth. I did not hear of any problems. I know they were using ICNV for conversions, don't know if that was on these two tables in particular. I also see in my notes that they strongly recommended a reorg of the tables after the ICNV conversion. We have not yet partitioned these tables, but were planning on using PBG for them once we are on version 9.

[Former Member]

Hello Marco

thank you very much for your answer. I had a message with this question open at SAP too and after the first answer that it was not possible to use ICNV (according to SAP Notes 135207 and 96515) they forwarded it to the DB2/SAP group. The result is exactly the same as your suggestion, it is now possible to do it by ICNV. Even your hint to reorg the tablespace afterwards is correct (we know how disorganized it would be after all those inserts). The first test in a sandbox system with only about 200,000 rows worked ok, now we are perparing the next test system which should give us a rough estimate of the needed amount of work processes and runtime (very long ...of course).

Best regards

Martin

[Former Member]

Hello Kay

thank you for your answer. I understand that there can be a problem in partitioning CDCLS in the traditional way although it should be possible to use SE14. Besides that we want to establish partition-by-growth in order to get the advantages of this feature - I think it is not so easy to find a really optimal partitioning key.

Next step will then be to open a question at SAP directly if no one has ever changed to UTS.

Best regards

Martin

[KayKa]

Hi Martin,

the CDCLS isn't a good choice to partition by se14. The columns of CDCLS~0 are too long for the sql-engine of the se14. We copy the result of the limitkey run and format it in a editor and then run the partition definition manually on the host. Afterwards we do a load with host tools.

But i don't know if we implement a partition by growth.

hth

Kay