Split Up PSApBTAB dbspace

Dear, We are on NT / Inf 7.31TD2x4 .Our dbspace PSAPBTAB is growing currently it is 225gb. We need to split this inot no of small dbspaces like PSAPBTAB1,PSAPBTAB2 ..... Can you help me to tell the procedure for splitting up this dbspace. Regards, Sanjiv

Here's a very rough estimate of what you would need to do: Take full Informix backup. Create two new dbspaces. Identify tables contained in original dbspace. Unload data for given tables. Create table schemas for given tables.Update table schemas with 'new' dbspace storage location. Drop tables. Recreate tables using 'new' table schemas pointing to 'new' dbspace location. Load tables. Recreate indices, etc. for tables. Drop dbspace -- if any Informix objects are still there, then the dbspace will not drop -- further analysis required. This assumes that: Whole tables are contained in original dbspace (no parts of fragmented tables). Any detached indices in the original dbspace are tied to tables in the original dbspace. Anything else I may have forgotten -- insert standard disclaimer here . . . . . -----Original Message----- From: Sanjiv K Khiste [mailto:khistesk@LNTEBG.com] Sent: Tuesday, May 20, 2003 11:39 PM To: ids@iiug.org Subject: Split Up PSApBTAB dbspace [1188] Dear, We are on NT / Inf 7.31TD2x4 .Our dbspace PSAPBTAB is growing currently it is 225gb. We need to split this inot no of small dbspaces like PSAPBTAB1,PSAPBTAB2 ..... Can you help me to tell the procedure for splitting up this dbspace. Regards, Sanjiv "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."

Sanjiv, I also suggest using the SAPDBA tool during your reorg. It will do the job, but you will have to do the thought work to figure out your space requirements. SAPDBA will say something like "I have the scripts ready, shall I run now". At that point, jump out/open new session, go modify the scripts created by SAPDBA to change your dbspace storage location and extent sizing (if desired), and let SAPDBA continue it's job. we scripted the onspaces commands to add dbspaces, then ran SAPDBA to control most of the reorg, then scripted any remaining onspaces and oncheck work. test, test, test. testing is very important as your SAP install may have a lot of Informix table versioning (as ours does), which could hit you hard and gobble more space than you think. Use "oncheck -pT" on the tables to figure it out in advance (this will?may? (i forget) lock your tables and run a long time... to avoid user complaints, run on a recently refreshed test system). Norma Jean -----Original Message----- From: John_Carlson@whsmithusa.com [mailto:John_Carlson@whsmithusa.com] Sent: Wednesday, May 21, 2003 8:00 AM To: ids@iiug.org; forum.subscriber@iiug.org Subject: RE: Split Up PSApBTAB dbspace [1191] Here's a very rough estimate of what you would need to do: Take full Informix backup. Create two new dbspaces. Identify tables contained in original dbspace. Unload data for given tables. Create table schemas for given tables. Update table schemas with 'new' dbspace storage location. Drop tables. Recreate tables using 'new' table schemas pointing to 'new' dbspace location. Load tables. Recreate indices, etc. for tables. Drop dbspace -- if any Informix objects are still there, then the dbspace will not drop -- further analysis required. This assumes that: Whole tables are contained in original dbspace (no parts of fragmented tables). Any detached indices in the original dbspace are tied to tables in the original dbspace. Anything else I may have forgotten -- insert standard disclaimer here . . . . . -----Original Message----- From: Sanjiv K Khiste [mailto:khistesk@LNTEBG.com] Sent: Tuesday, May 20, 2003 11:39 PM To: ids@iiug.org Subject: Split Up PSApBTAB dbspace [1188] Dear, We are on NT / Inf 7.31TD2x4 .Our dbspace PSAPBTAB is growing currently it is 225gb. We need to split this inot no of small dbspaces like PSAPBTAB1,PSAPBTAB2 ... Can you help me to tell the procedure for splitting up this dbspace. Regards, Sanjiv "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer." --openmail-part-3d6cae2c-00000002 Content-Type: application/rtf Content-Disposition: attachment; filename="BDY.RTF" ;Creation-Date="Wed, 21 May 2003 09:10:40 -0500" Content-Transfer-Encoding: base64 {\\rtf1\\ansi\\ansicpg1252\\fromtext \\deff0{\\fonttbl {\\f0\\fswiss Arial;} {\\f1\\fmodern Courier New;} {\\f2\\fnil\\fcharset2 Symbol;} {\\f3\\fmodern\\fcharset0 Courier New;}} {\\colortbl\\red0\\green0\\blue0;\\red0\\green0\\blue255;} \\uc1\\pard\\plain\\deftab360 \\f0\\fs20 Sanjiv,\\par I also suggest using the SAPDBA tool during your reorg.\\par It will do the job, but you will have to do the thought work to figure out your space requirements. SAPDBA will say something like "I have the scripts ready, shall I run now". At that point, jump out/open new session, go modify the scripts created by SAPDBA to change your dbspace storage location and extent sizing (if desired), and let SAPDBA continue it's job.\\par \\par we scripted the onspaces commands to add dbspaces, then ran SAPDBA to control most of the reorg, then scripted any remaining onspaces and oncheck work.\\par \\par test, test, test.\\par testing is very important as your SAP install may have a lot of Informix table versioning (as ours does), which could hit you hard and gobble more space than you think. Use "oncheck -pT" on the tables to figure it out in advance (this will?may? (i forget) lock your tables and run a long time... to avoid user complaints, run on a recently refreshed test system).\\par \\par Norma Jean\\par \\par \\par -----Original Message-----\\par From: John_Carlson@whsmithusa.com [mailto:John_Carlson@whsmithusa.com]\\par Sent: Wednesday, May 21, 2003 8:00 AM\\par To: ids@iiug.org; forum.subscriber@iiug.org\\par Subject: RE: Split Up PSApBTAB dbspace [1191]\\par \\par \\par Here's a very rough estimate of what you would need to do:\\par \\par Take full Informix backup.\\par Create two new dbspaces.\\par Identify tables contained in original dbspace.\\par Unload data for given tables.\\par Create table schemas for given tables.\\par Update table schemas with 'new' dbspace storage location.\\par Drop tables.\\par Recreate tables using 'new' table schemas pointing to 'new' dbspace\\par location.\\par Load tables.\\par Recreate indices, etc. for tables.\\par Drop dbspace -- if any Informix objects are still there, then the dbspace\\par will not drop -- further analysis required.\\par \\par This assumes that:\\par Whole tables are contained in original dbspace (no parts of fragmented\\par tables).\\par Any detached indices in the original dbspace are tied to tables in the\\par original dbspace.\\par Anything else I may have forgotten -- insert standard disclaimer here . . .\\par . .\\par \\par \\par \\par -----Original Message-----\\par From: Sanjiv K Khiste [mailto:khistesk@LNTEBG.com] \\par Sent: Tuesday, May 20, 2003 11:39 PM\\par To: ids@iiug.org\\par Subject: Split Up PSApBTAB dbspace [1188] \\par \\par \\par Dear,\\par \\par We are on NT / Inf 7.31TD2x4 .Our dbspace PSAPBTAB is growing currently it\\par is 225gb.\\par

PSAPBTAB is an SAP-Generated DBSpace and must always be present; however, the tables and indices within it can and should be split out before it gets too large for its own good. Normally, that condition will exist moments after the SAP PREPARE scripts generate and load the spaces the first time. There is a tool that comes with SAP Informix called SAPDBA. Use it to create and relocate tables from PSAPBTAB (and PSAPSTAB) to other dbspaces. The reason you need to use SAPDBA for this is to allow the internal SAP processes to track the table's movement, since they record this information in their Data Dictionary. At the same time you are relocating the tables, you should look into separating the indices from the tables. Look into SAP OSS Notes for how-to notes on the SAPDBA and REORGANIZATION advice they offer. Take care. Clifton ----- Original Message ----- From: "John Carlson " <John_Carlson@whsmithusa.com> To: <ids@iiug.org> Sent: Wednesday, May 21, 2003 7:59 AM Subject: RE: Split Up PSApBTAB dbspace [1191] > Here's a very rough estimate of what you would need to do: > > Take full Informix backup. > Create two new dbspaces. > Identify tables contained in original dbspace. > Unload data for given tables. > Create table schemas for given tables.> Update table schemas with 'new' dbspace storage location. > Drop tables. > Recreate tables using 'new' table schemas pointing to 'new' dbspace > location. > Load tables. > Recreate indices, etc. for tables. > Drop dbspace -- if any Informix objects are still there, then the dbspace > will not drop -- further analysis required. > > This assumes that: > Whole tables are contained in original dbspace (no parts of fragmented > tables). > Any detached indices in the original dbspace are tied to tables in the > original dbspace. > Anything else I may have forgotten -- insert standard disclaimer here . . . > . . > > > > -----Original Message----- > From: Sanjiv K Khiste [mailto:khistesk@LNTEBG.com] > Sent: Tuesday, May 20, 2003 11:39 PM > To: ids@iiug.org > Subject: Split Up PSApBTAB dbspace [1188] > > > Dear, > > We are on NT / Inf 7.31TD2x4 .Our dbspace PSAPBTAB is growing currently it > is 225gb. > > We need to split this inot no of small dbspaces like PSAPBTAB1,PSAPBTAB2 > .... > > Can you help me to tell the procedure for splitting up this dbspace. > > Regards, > Sanjiv > > > > > "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel > Retail. This email message and all attachments may contain legally > privileged and confidential information intended solely for the use of the > addressee. If you are not the intended recipient, you should immediately > stop reading this message and delete it from the system. Any unauthorized > reading, distribution, copying, or other use of this message or its > attachments is strictly prohibited. All personal messages express solely the > sender's views and not those of WHSmith USA Travel Retail. This message may > not be copied or distributed without this disclaimer."

Ahh, SAP . . . . . ? Guess this comes under "anything else I may have forgotten". -----Original Message----- From: NormaJean.Sebastian@tellabs.com [mailto:NormaJean.Sebastian@tellabs.com] Sent: Wednesday, May 21, 2003 10:11 AM To: forum.subscriber@iiug.org; ids@iiug.org; John Carlson Subject: RE: Split Up PSApBTAB dbspace [1191] Sanjiv, I also suggest using the SAPDBA tool during your reorg. It will do the job, but you will have to do the thought work to figure out your space requirements. SAPDBA will say something like "I have the scripts ready, shall I run now". At that point, jump out/open new session, go modify the scripts created by SAPDBA to change your dbspace storage location and extent sizing (if desired), and let SAPDBA continue it's job. we scripted the onspaces commands to add dbspaces, then ran SAPDBA to control most of the reorg, then scripted any remaining onspaces and oncheck work. test, test, test. testing is very important as your SAP install may have a lot of Informix table versioning (as ours does), which could hit you hard and gobble more space than you think. Use "oncheck -pT" on the tables to figure it out in advance (this will?may? (i forget) lock your tables and run a long time... to avoid user complaints, run on a recently refreshed test system). Norma Jean -----Original Message----- From: John_Carlson@whsmithusa.com [mailto:John_Carlson@whsmithusa.com] Sent: Wednesday, May 21, 2003 8:00 AM To: ids@iiug.org; forum.subscriber@iiug.org Subject: RE: Split Up PSApBTAB dbspace [1191] Here's a very rough estimate of what you would need to do: Take full Informix backup. Create two new dbspaces. Identify tables contained in original dbspace. Unload data for given tables. Create table schemas for given tables.Update table schemas with 'new' dbspace storage location. Drop tables. Recreate tables using 'new' table schemas pointing to 'new' dbspace location. Load tables. Recreate indices, etc. for tables. Drop dbspace -- if any Informix objects are still there, then the dbspace will not drop -- further analysis required. This assumes that: Whole tables are contained in original dbspace (no parts of fragmented tables). Any detached indices in the original dbspace are tied to tables in the original dbspace. Anything else I may have forgotten -- insert standard disclaimer here . . . . . -----Original Message----- From: Sanjiv K Khiste [mailto:khistesk@LNTEBG.com] Sent: Tuesday, May 20, 2003 11:39 PM To: ids@iiug.org Subject: Split Up PSApBTAB dbspace [1188] Dear, We are on NT / Inf 7.31TD2x4 .Our dbspace PSAPBTAB is growing currently it is 225gb. We need to split this inot no of small dbspaces like PSAPBTAB1,PSAPBTAB2 ... Can you help me to tell the procedure for splitting up this dbspace. Regards, Sanjiv "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer." "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."

hi bro, the easiest (but kinda clumsy...) - dbexport your instance - drop database - drop dbspace PSAPBTAB - oninit -i - create new dbspaces PSAPBTAB1, PSAPBTAB2, and so on (also create new dbspaces for physical and logical logs!) - move logs to new dbspaces - edit the database query script file and add the "in PSAPBTABx" after CREATE TABLE clauses (check the create table syntax form details) - dbimport pretty nasty indeed, of course might be better methods, but this might help greetings

Hello all, I have some clarification queries on the above topic. It will be great if someone can clarify my query. In our setup, We use the LVM say VERITAS volume manager for database volumes configured with RAID 10 and striping . We created the volumes using LVM in such an order that the data is distributed across all disks in the form of striping. For ex, first volume on the first stripeset,second volume on the second stripeset......and so on and again back to first stripe set etc. From Informix using SAPDBA , I will assign the available volumes when the database requires in the form of chunks. The data distribution on the disk is taken care by the LVM and is transparent to the Informix. Having said that we have dbspaces not evenly grown in size. For example, we have psapbtab for 150GB, psapstab for 70GB more than 60% of the whole database size in these 2 dbspaces itself. Though i have removed some of the larger tables into its own dbspace and index detached , i cant keep creating new dbspaces for all the growing tables as it will increase the dbspace maintenance headaches.I still have lot of growing tables but i frequently maintain those tables with fewer extents possible inside psapbtab/psapstab. Since there could be frequently used tables inside psapbtab due to the bigger dbspace size but all spread out from the Volume layout., i dont think any I/O bottleneck in this scenario. Ofcourse, I/O issue will always be there for Poor SQL's on big tables with concurrent access and not exactly matching the available index under any circumstances. Question ------------ 1) Under this scenario, Does the split up of PSapBtab dbspace improve I/O performance ? 2) If so, please pass on your brief explanation. Any comments are greatly appreciated. Thanks in advance. Rajesh Rajasekaran Informix Database Administrator Forest Pharmaceuticals Inc. 314-493-7073 (Work) -----Original Message----- From: ESTEBAN RIEZNIK [mailto:erieznikr@repsolypf.com] Sent: Wednesday, June 11, 2003 4:21 PM To: ids@iiug.org Subject: Re: Split Up PSApBTAB dbspace [1336] hi bro, the easiest (but kinda clumsy...) - dbexport your instance - drop database - drop dbspace PSAPBTAB - oninit -i - create new dbspaces PSAPBTAB1, PSAPBTAB2, and so on (also create new dbspaces for physical and logical logs!) - move logs to new dbspaces - edit the database query script file and add the "in PSAPBTABx" after CREATE TABLE clauses (check the create table syntax form details)- dbimport pretty nasty indeed, of course might be better methods, but this might help greetings

hi bro, Sorry if you have already received the same answer (there was some problem with the server :-) ) Splitting up a dbspace might improve maintennance and performance. But the most important for perfomance improvement is the underlying drives. The more drives with split across / striped raw devices, the better. always has, always will... Consider this: map a bunch of drives into 1 huge drive, doing this by means of your RAID card or by the external storage device (symmetrix, EVA, and the like...) let the hardware do all the nasty work. We recently migrated from a nasty 20+ separated disks scheme to this one. We just forgot about stripping, chunks distribution, balancing, etc. I's all up top the hardware About the DB structure, we did the following - the hugest table in its own dbspace - the next 5 hugest tables in another dbspace - the remaining tables (about 800) in another dbspace - all indexes in another dbspace The dbspaces keep growing pretty steady. hope it helps.