Splitting of tables on the basis of partnum
A user migrating a 350GB SAP R/3 4.6B / IDS 10 database to Oracle with SAP's R3load tool wanted to speed up the 20-hour export of table objk by splitting it into smaller tables, asking whether this could be done by hand-editing partnum in systables. Suggestions included ALTER FRAGMENT ... DETACH (rejected, since unloading happens at the SAP application level), SAP data archiving (transaction SARA), and R3load's table-splitting per SAP note 952514 — though the poster said that note needs kernel 6.40+. Direct systables editing was strongly discouraged; the advice was to detach fragments into separate tables, unload in parallel and re-merge after load, or check with SAP. Much of the thread drifted into discussion of SAP dropping Informix support. No confirmed resolution is recorded.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi all, We are in the process of migrating of our informix database to oracle database in SAP environment. We are using SAP specific tools (R3load) to do the migration. Here, while exporting the informix database its taking a very long time. Hence, we are planning to split the large tables into a number of smaller tables. For Example: There is a table objk which is around 350 GB and it is taking about 20 Hrs to export. Here, if we can split this table into 8 smaller tables, the export would finish in say 3 Hrs. If anybody can suggest how we can split this table on the basis of partnum. Something like " update systables set partnum = xxxxx where tabid =XXX and partnum = xxx it would be really helpful. Thank You, Salman
Try "ALTER FRAGMENT ON TABLE table1 DETACH dbspace2 table2" On Mar 25, 8:44 pm, Salman Qayyum <salman...@gmail.com> wrote: > Hi all, > > We are in the process of migrating of our informix database to oracle > database in SAP environment. We are using SAP specific tools (R3load) > to do the migration. > Here, while exporting the informix database its taking a very long > time. Hence, we are planning to split the large tables into a number > of smaller tables. For Example: There is a table objk which is around > 350 GB and it is taking about 20 Hrs to export. Here, if we can split > this table into 8 smaller tables, the export would finish in say 3 > Hrs. If anybody can suggest how we can split this table on the basis > of partnum. Something like " update systables set partnum = xxxxx > where tabid =XXX and partnum = xxx it would be really helpful. > > Thank You, > > Salman
The load FAQ can be found at http://artentech.com/downloads.htm j. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of Salman Qayyum Sent: Wednesday, March 25, 2009 4:44 AM To: informix-list@iiug.org Cc: salman404@gmail.com Subject: Splitting of tables on the basis of partnum Hi all, We are in the process of migrating of our informix database to oracle database in SAP environment. We are using SAP specific tools (R3load) to do the migration. Here, while exporting the informix database its taking a very long time. Hence, we are planning to split the large tables into a number of smaller tables. For Example: There is a table objk which is around 350 GB and it is taking about 20 Hrs to export. Here, if we can split this table into 8 smaller tables, the export would finish in say 3 Hrs. If anybody can suggest how we can split this table on the basis of partnum. Something like " update systables set partnum = xxxxx where tabid =XXX and partnum = xxx it would be really helpful. Thank You, Salman _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list
On 25 Mar, 09:44, Salman Qayyum <salman...@gmail.com> wrote: > Hi all, > > We are in the process of migrating of our informix database to oracle > database in SAP environment. We are using SAP specific tools (R3load) > to do the migration. > Here, while exporting the informix database its taking a very long > time. Hence, we are planning to split the large tables into a number > of smaller tables. For Example: There is a table objk which is around > 350 GB and it is taking about 20 Hrs to export. Here, if we can split > this table into 8 smaller tables, the export would finish in say 3 > Hrs. If anybody can suggest how we can split this table on the basis > of partnum. Something like " update systables set partnum = xxxxx > where tabid =XXX and partnum = xxx it would be really helpful. > > Thank You, > > Salman How come you are moving away from Informix? has no-one from IBM been to talk to you?
<david@smooth1.co.uk> wrote in message news:ff2e7be6-b090-477e-ac82-1ccb7f9d03de@v6g2000vbb.googlegroups.com... > On 25 Mar, 09:44, Salman Qayyum <salman...@gmail.com> wrote: >> Hi all, >> >> We are in the process of migrating of our informix database to oracle >> database in SAP environment. We are using SAP specific tools (R3load) >> to do the migration. >> Here, while exporting the informix database its taking a very long >> time. Hence, we are planning to split the large tables into a number >> of smaller tables. For Example: There is a table objk which is around >> 350 GB and it is taking about 20 Hrs to export. Here, if we can split >> this table into 8 smaller tables, the export would finish in say 3 >> Hrs. If anybody can suggest how we can split this table on the basis >> of partnum. Something like " update systables set partnum = xxxxx >> where tabid =XXX and partnum = xxx it would be really helpful. >> >> Thank You, >> >> Salman > > How come you are moving away from Informix? has no-one from IBM been > to talk to you? The latest SAP "kernel", v7, does not have Informix support so nothing much that an IBM rep says to the customer *can* make any difference. As has been observed many times before, the conversion rate from Informix to DB2 is so negligible as to be effectively zero. In fact, I've never had a customer leave Informix for DB2, but plenty have left for Oracle and MS SQL Server. I did hear that IBM UK helped a large elevator company do an Informix-DB2 port about 5 years ago.
> has no-one from IBM been > to talk to you? My guess is that's the reason
Salman Qayyum schrieb: > Hi all, > > We are in the process of migrating of our informix database to oracle > database in SAP environment. We are using SAP specific tools (R3load) > to do the migration. > Here, while exporting the informix database its taking a very long > time. Hence, we are planning to split the large tables into a number > of smaller tables. For Example: There is a table objk which is around > 350 GB and it is taking about 20 Hrs to export. Here, if we can split > this table into 8 smaller tables, the export would finish in say 3 > Hrs. If anybody can suggest how we can split this table on the basis > of partnum. Something like " update systables set partnum = xxxxx > where tabid =XXX and partnum = xxx it would be really helpful. Hi Salman, (and to all others who have answered so far) A database migration in an SAP environment is quite a peculiar thing. Usually the target database is not the same as the source database, so all database specific tools and methods fail (backup/restore, HPL). There is a tool by SAP, R3load, that performs an unload (and afterwards the load) in a totally database independent manner. It is the only tool that is supported by SAP and it is well integrated into the installation scripts. There are certain techniques that help to speed up the migration: - Archiving, the SAP method to remove old business data from the database. Do it. Do it thoroughly. (Use transaction SARA, object PM_ORDER for table objk, among others) - Get a certified /and/ experienced consultant. - As for the table objk, R3load can split on table level. See SAP note 952514. Find further information on http://service.sap.com/osdbmigration. If you need help, ask SAP on component BC_INS-MIG. HTH and best regards Christian
> From: david@smooth1.co.uk > > How come you are moving away from Informix? has no-one from IBM been > to talk to you? > _______________________________________________ > David, I suppose you forgot to read the memo: SAP is no longer supporting Informix going forward. So SAP customers have only 2 options: 1) Stay on Informix and convert from SAP to Lawson or Infor 2) Migrate SAP app from Informix to either DB2 or Oracle. Now I'm not sure if Lawson changed their statement on continued support for Informix, because they too had indicated that they were moving away from continuing to support Informix.... Also to migrate from SAP to another ERP application, you have the issue of BPR (Business Process Re-engineering). >From what I've heard via the grapevine is that most of the Informix/SAP customers ditched IBM and opted for Oracle over DB2 ! Go figure! But hey! What do I know? IBM has record year and Q4, yet they're about to drop the axe on ~4K employees here in the US. (I guess they were made redundant after they trained their Indian counterparts if the news reports are correct.) -G _________________________________________________________________ Hotmail® is up to 70% faster. Now good news travels really fast. http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_HM_70faster_032009
> From: neil.truby@ardenta.com > The latest SAP "kernel", v7, does not have Informix support so nothing much > that an IBM rep says to the customer *can* make any difference. > As has been observed many times before, the conversion rate from Informix to > DB2 is so negligible as to be effectively zero. In fact, I've never had a > customer leave Informix for DB2, but plenty have left for Oracle and MS SQL > Server. I did hear that IBM UK helped a large elevator company do an > Informix-DB2 port about 5 years ago. > Neil, Tellabs is *one* of the customers who migrated SAP from Informix to DB2. NJ can confirm since she was the DBA. Sears migrated Peoplesoft off Informix to DB2 a couple of years back. (Going from memory on that one...) But yes, you are correct. Informix to DB2 migrations are rare and usually what happens is that the sales rep opens the door to an Oracle migration when they bring up the issue of Informix to DB2 ... Knowing that, you'd wonder why IBM reps did this... It boiled down to their comp plan. At the time, IBM S&D didn't comp the rep on license renewals only on net new license sales. So to make quota, they would try and sell a DB2 migration to their Informix customers. (Note: You can check the Eagle Sales Plan(s) which should indicate that a migration from Informix to DB2 was considered a net new license. Pitty the poor pillar rep who thought about convincing a DB2 customer to migrate to Informix!) But hey! What do I know? Its not like I was there watching this happen. ;-) -G _________________________________________________________________ Quick access to Windows Live and your favorite MSN content with Internet Explorer 8. http://ie8.msn.com/microsoft/internet-explorer-8/en-us/ie8.aspx?ocid=B037MSN55C0701A
Hi All, Thank You so much for all the replies. Well, as Neil said, SAP does not support Informix as of Kernel 700 and thats the reason our organization has decided to migrate from Informix 10 to Oracle database. The Current Version of SAP is 4.6D and we are planning to upgrade. And there is no other reason to migrate from Informix as it is the best Database I have ever seen or worked on. Christian : SAP note 952514 is for atleast Kernel 6.40 or above and ours is 4.6D Allan : alter fragment on table will not work as we are unloading the data from the application level (Using SAP specific tool R3load) Also, I am sure we can partition the larger tables into multiple smaller tables by updating the partnum on the table systables. If anybody can guide, that would be really great. Our Env: SAP R/3 4.6B Kernel : 4.6D_EXT Database : IDS 10.00.FC7XB On Mar 26, 3:45 pm, Christian Knappke <chkn...@gmx.net> wrote: > Salman Qayyum schrieb: > > > Hi all, > > > We are in the process of migrating of our informix database to oracle > > database in SAP environment. We are using SAP specific tools (R3load) > > to do the migration. > > Here, while exporting the informix database its taking a very long > > time. Hence, we are planning to split the large tables into a number > > of smaller tables. For Example: There is a table objk which is around > > 350 GB and it is taking about 20 Hrs to export. Here, if we can split > > this table into 8 smaller tables, the export would finish in say 3 > > Hrs. If anybody can suggest how we can split this table on the basis > > of partnum. Something like " update systables set partnum = xxxxx > > where tabid =XXX and partnum = xxx it would be really helpful. > > Hi Salman, > > (and to all others who have answered so far) > > A database migration in an SAP environment is quite a peculiar thing. > Usually the target database is not the same as the source database, so > all database specific tools and methods fail (backup/restore, HPL). > There is a tool by SAP, R3load, that performs an unload (and > afterwards the load) in a totally database independent manner. It is > the only tool that is supported by SAP and it is well integrated into > the installation scripts. > > There are certain techniques that help to speed up the migration: > - Archiving, the SAP method to remove old business data from the > database. Do it. Do it thoroughly. (Use transaction SARA, object > PM_ORDER for table objk, among others) > - Get a certified /and/ experienced consultant. > - As for the table objk, R3load can split on table level. > See SAP note 952514. > > Find further information onhttp://service.sap.com/osdbmigration. > If you need help, ask SAP on component BC_INS-MIG. > > HTH and best regards > Christian
Salman Qayyum wrote: > Also, I am sure we can partition the larger tables into multiple > smaller tables by updating the partnum on the table systables. If > anybody can guide, that would be really great. I would not even think about this approach! A method that might work is: shutdown the SAP system, detach all fragments of objk into separate tables, unload these in parallel, unload all the rest, load everything into the target database, insert data of separate fragment tables into objk. Caution: this is not supported by SAP and it affords manual intervention and brains work. Try, test, practice thoroughly, preferably on a test system. But, as I mentined before, what about archiving? > Our Env: > > SAP R/3 4.6B > Kernel : 4.6D_EXT > Database : IDS 10.00.FC7XB Ask SAP if R3load according to note 952451 can be used with your system. HTH and best regards Christian -- Disclaimer: all recommendations and ideas mentioned above or below are my own and not necessarily those of my employer. Everything must be tested before applying it in a production environment. > > On Mar 26, 3:45 pm, Christian Knappke <chkn...@gmx.net> wrote: >> Salman Qayyum schrieb: >> >>> Hi all, >>> We are in the process of migrating of our informix database to oracle >>> database in SAP environment. We are using SAP specific tools (R3load) >>> to do the migration. >>> Here, while exporting the informix database its taking a very long >>> time. Hence, we are planning to split the large tables into a number >>> of smaller tables. For Example: There is a table objk which is around >>> 350 GB and it is taking about 20 Hrs to export. Here, if we can split >>> this table into 8 smaller tables, the export would finish in say 3 >>> Hrs. If anybody can suggest how we can split this table on the basis >>> of partnum. Something like " update systables set partnum = xxxxx >>> where tabid =XXX and partnum = xxx it would be really helpful. >> Hi Salman, >> >> (and to all others who have answered so far) >> >> A database migration in an SAP environment is quite a peculiar thing. >> Usually the target database is not the same as the source database, so >> all database specific tools and methods fail (backup/restore, HPL). >> There is a tool by SAP, R3load, that performs an unload (and >> afterwards the load) in a totally database independent manner. It is >> the only tool that is supported by SAP and it is well integrated into >> the installation scripts. >> >> There are certain techniques that help to speed up the migration: >> - Archiving, the SAP method to remove old business data from the >> database. Do it. Do it thoroughly. (Use transaction SARA, object >> PM_ORDER for table objk, among others) >> - Get a certified /and/ experienced consultant. >> - As for the table objk, R3load can split on table level. >> See SAP note 952514. >> >> Find further information onhttp://service.sap.com/osdbmigration. >> If you need help, ask SAP on component BC_INS-MIG. >> >> HTH and best regards >> Christian >
> I suppose you forgot to read the memo: SAP is no longer supporting Informix going forward. So SAP customers have only 2 options: >> 1) Stay on Informix and convert from SAP to Lawson or Infor As you later went on to say, the later versions of Lawson do not support Informix either.
> From: neil.truby@ardenta.com > Subject: Re: Splitting of tables on the basis of partnum > Date: Thu, 26 Mar 2009 15:43:29 +0000 > To: informix-list@iiug.org > > > I suppose you forgot to read the memo: > > SAP is no longer supporting Informix going forward. > So SAP customers have only 2 options: > > >> 1) Stay on Informix and convert from SAP to Lawson or Infor > > As you later went on to say, the later versions of Lawson do not support > Informix either. > Well, Lawson is out of MN and I thought that IBM was trying to get Lawson to backtrack on that statement. I guess IBM either didn't try, or they tried and failed, or they are still trying. Infor I believe has both 4Gen and Baan, however I may be wrong and Infor is just Baan and SSA??? _________________________________________________________________ Hotmail® is up to 70% faster. Now good news travels really fast. http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_HM_70faster_032009
"Ian Michael Gumby" <im_gumby@hotmail.com> wrote in message news:mailman.429.1238084360.1831.informix-list@iiug.org... >>Well, Lawson is out of MN and I thought that IBM was trying to get Lawson >>to backtrack on that statement. >> I guess IBM either didn't try, or they tried and failed, or they are >> still trying. I think IBM tried very hard indeed. But in the end this vendor, as others, did not want to support 2 IBM databases.