Fw: unloading a table more efficiently...

I found the HPL online documentation on CD. > 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. > > > >