RE: unloading a table more efficiently...

I would suggest: -- get all users off the database, -- unload the table, -- drop the table, -- re-create the table *only* (no indexes), but using the properly calculated first/next extent sizes (and *I* would try to get all of the data into one extent), -- reload the data into the table, -- re-create all indexes (NOTE: You've got a LOT of indexes, there!! Are you sure you need them all? They are probably the reason that your dbload took so long. You take those indexes away for the data load, and watch your dbload FLY!!) My experience is that, with a large load, it is almost always faster to drop indexes, load table, then rebuild indexes -- especially if I have a lot of memory to throw at the index build. If you do the above, you should end up with very few extents (i.e., s/b ONE for each table/index) unless you've got other things sharing the same dbspaces. HTH, Paul Mosser -----Original Message----- From: Denmark B. Weatherburn [mailto:dweatherb@btl.net] Sent: Tuesday, February 06, 2001 4:14 PM To: Informix Users Group Subject: unloading a table more efficiently... Hi Informix Users, I need your advice again. We are a financial institution in Belize ("Temptation Island"). We are running Solaris 2.7 with Informix IDS Workgroup Server 7.30.UC3. I (SysAdmin/DBA) want to improve the loading of a GL history table. Statistics on table: Kbytes Mbytes tabname rowsize nrows nrows*size nrows*size nexts mch 494 1064897 513729.6 501.7 178 ndatapgs Pgs=>Mb npgsused Pgs=>Mb npgsalloc Pgs=>Mb 266225 1039.9 293311 1145.7 294784 1151.5 From dbschema in dbexport directory: { TABLE "opsi".mch row size = 494 number of columns = 68 index size = 838 } { unload file name = mch__00458.unl number of rows = 1064897 } create table "opsi".mch ( coentfincble decimal(5,0) not null , femovcble decimal(8,0) not null , cotiuniorgext char(1) not null , couniorgext decimal(5,0) not null , cocomcont char(3) not null , nrcomcble decimal(5,0) not null , nrsecmco decimal(5,0) not null , nrctacble char(15) not null , txdesmov char(50) not null , . . primary key (coentfincble,femovcble,cotiuniorgext,couniorgext,cocomcont,nrco mcble,nrsecmco) ) in testgldbs extent size 589568 next size 58956; revoke all on "opsi".mch from "public"; create index "opsi".akmch on "opsi".mch (coentfincble,nrctacble, cotiuniorgext1,couniorgext1) in testgldbs; create index "opsi".akmch1 on "opsi".mch (coentfincble,cotiuniorgext1, couniorgext1,nrtercble,nrctacble,femovcble,cotiuniorgext, couniorgext,cocomcont,nrcomcble) in testgldbs; create index "opsi".akmch2 on "opsi".mch (coentfincble,nrctacble, cotiuniorgext1,couniorgext1,femovcble) in testgldbs; create index "opsi".akmch3 on "opsi".mch (coentfincble,femovcble, nrctacble,cotiuniorgext1,couniorgext1,cocomcont,nrcomcble, nrsecmco) in testgldbs; create index "opsi".akmch4 on "opsi".mch (coentfincble,femovcble, nrctacble,cotiuniorgext1,couniorgext1,comon,nrtercble,coprod, cosbp,cotiuniorgext2,couniorgext2,cotiuniorgext3,couniorgext3, nruserid,txvalsdocble1) in testgldbs; create index "opsi".akmch5 on "opsi".mch (coentfincble,cotiuniorgext, couniorgext,cotiuniorgext1,couniorgext1,cocomcont,nrcomcble, nrsecmco) in testgldbs; create index "opsi".akmch6 on "opsi".mch (coentfincble,cotiuniorgext, couniorgext,cocomcont,nrcomcble,nrsecmco) in testgldbs; create index "opsi".akmch7 on "opsi".mch (coentfincble,cotiuniorgext, couniorgext,nruserid,cocomcont,nrcomcble,nrsecmco) in testgldbs; create index "opsi".akmch8 on "opsi".mch (coentfincble,cotiuniorgext, couniorgext,nrtercble,cocomcont,nrcomcble,nrsecmco) in testgldbs; create index "opsi".akmch9 on "opsi".mch (coentfincble,cotiuniorgext, couniorgext,nrctacble,cocomcont,nrcomcble,nrsecmco) in testgldbs; create index "opsi".akmch10 on "opsi".mch (coentfincble,femovcble, cotiuniorgext1,couniorgext1,cocomcont,nrcomcble,nrsecmco) in testgldbs; create index "opsi".akmch11 on "opsi".mch (coentfincble,nrctacble, femovcble,cotiuniorgext,couniorgext,cocomcont,nrcomcble,nrsecmco) in testgldbs; create index "opsi".akmch12 on "opsi".mch (coentfincble,cotiuniorgext1, couniorgext1,femodmovcble) in testgldbs; Using dbload this table takes 7.5 hours to load. I am currently setting up a testing environment to evaluate other loading methods. Notice that currently this table has 178 extents. I want to reduce the number of extents to one during the dbimport by adding the "extent/next size" clause. The migration guide discusses other tools such as onunload/onload and the HPL. However, it also says that the HPL (onpload) can't be used with the Online Workgroup Server. We have IDS Workgroup Edition 7.30.UC3. Please give me some advice about HPL and its use with Workgroup Edition? I have the command line utility and I'd loke to try it; however, I have not seen any documentation on the HPL (onunload) besides the syntax. Is there a scripting language I should learn to be able to use the HPL (onpload) effectively? Any examples would be appreciated. I've used the onunload/onload once before to move a database between different Sun machines, although it was not successfull. The integrity of the data was questionable. I had to resort to a regular dbexport/dbimport. Perhaps it was related to the fact that the machines had different number representations (E3000 => Sparc5). I'm willing to try it again though. On a related note, I asked the question a few days ago about the optimum number of extents for a table. Of course the answer is one. I read all the worthy responses. One from Jack Parker caught my attention though. He recommends selecting a smaller initial extent size (because index space allocation is made according to the initial extent size) followed by a large next extent size, loading the table and then altering the next extent size downward. I have tried in the past to calculate exactly how much space is used by indexes directly, but I had to resort to calculations using the dbspace size, free space and number of pages allocated. I was able to subtract the datapages used and the free space which left me with the storage for overhead and index. What I found out is that the 12 detached indexes for this table takes up a lot of storage in the dbspace. Exactly how much, I don't know. Any ideas about calculating the index space usage and using index storage more efficiently? I know I'm asking for advice about several issues but I think this list is the appropriate forum. Especially with experienced user/DBAs participating. Thanks in advance, Regards, Denmark W.