IDS Forum

Re: Urgent:Error No. 136

Posted By:
Date:

Thanks Tim and everybody for quickly turnaround with
the possible solutions for this problem.

I have fragmented the table in six different dbspaces
and now I am in the process of moving data in the new
table. This activity gonna take time as we need to
keep business running as usual and copy the stuff in
night .

Thanks,

Vineet
--- timothy.a.brown@marconi.com wrote:
>
> Hi Vineet,
>
> For the version you are using, there is a 32gb
> tblspace limitation for the
> table optpgtrx_t. You reached this limitation.
> Tables are stored in
> tblspaces, which in turn are stored in dbspaces.
> First steps to take would
> be to drop the indexes and recreate them in another
> dbspace (with the 'in
> dbspace' appended to the end of the create index
> statement). You will, of
> course, have to create a new dbspace for the indexes
> if the 'dbspace'
> doesn't exist. By detaching the indexes, each index
> will now have it's own
> tblspace. This frees space in the table's tblspace.
> However, if you have
> extra time, it would be benefitial to use a
> fragmentation strategy on the
> data also before recreating the indexes.
>
> For instance, here is a sample to do the whole
> kitten-ka-bootle
>
> 1) create dbspaces on seperate disks (preferably)
> optdbs01_d1 with 8 gb (data fragment)
> optdbs01_d2 with 8 gb (data fragment)
> optdbs01_d3 with 8 gb (data fragment)
> optdbs01_d4 with 8 gb (data fragment)
> optdbs01_i with ?? gb (detached indexes) -- give it
> a couple of gig to be
> safe
>
> 2) setup pdq resources
> onmode -D 95 #max pdq priority
> onmode -Q 1 #max pdq queries
> #onmode -a 100000 #100mb add to virtual shm (check
> 'onstat -g seg|grep V'
> to see if needed)
> onmode -M 100000 #100mb pdq memory
>
> 3) turn off db logging if you plan of fragmenting
> the data in addition to
> detaching the indexes
> kick off all users
> ontape -N dbname
>
> 4) uncomment the two alter statements if you plan of
> fragmenting the
> data...
> #!/usr/bin/ksh
> export OPTCOMPIND=2
> export PDQPRIORITY=high
> # get NUMCPUVPS from ONCONFIG file
> # and multiply by 2 for HP-UX (Art K.'s recommend)
> NUMCPUVPS=5
> export PSORT_NPROCS=`expr $NUMCPUVPS \* 2`
> export DELIMIDENT=1
> OUTPUT=optpgtrx_t.`date +"%Y%m%d%H%M%S"`
> dbaccess -e $INFORMIX_DBID <<EOF >> $OUTPUT 2>&1
> drop index "optpgtrxn_x01";
> drop index "optpgtrxn_x02";
> -- alter table "informix"."optpgtrx_t" modify next
> size `expr 10485760 /
> 4`;
> -- alter fragment on table "informix"."optpgtrx_t"
> init fragment by round
> robin in
> optdbs01_d1,optdbs01_d2,optdbs01_d3,optdbs01_d4;
> create unique index "informix".optpgtrxn_x01 on
> "informix".optpgtrx_t
> (n_objid,n_objpg,n_pg_version desc) fillfactor
> 90 in optdbs01_i;
> create index "informix".optpgtrxn_x02 on
> "informix".optpgtrx_t
> (n_clstrid,n_objid,n_objpg) fillfactor 90 in
> optdbs01_i;
> update statistics low for table
> "informix"."optpgtrx_t";
> EOF
>
> 5) turn db logging back on (if you turned it off)
> ontape -s -L 0 -U dbname
>
>
>
>
>
>
> "Vineet
>
> Mehr...." To:
> ids@iiug.org
> <vin_us@yahoo.c cc:
>
> om> Subject:
> Urgent:Error No. 136 [1132]
> Sent by:
>
> forum.subscribe
>
> r@iiug.org
>
>
>
>
>
> 05/13/2003
>
> 09:11 PM
>
>
>
>
>
>
>
>
>
> Environment: Informix Dynamic Server 7.31UD2R1
> on HP-UX 11.0
>
> Table definition :
>
> create table "informix".optpgtrx_t
> (
> n_objid integer,
> n_objpg integer,
> n_pg_version integer,
> n_objabspg integer,
> n_clstrid integer,
> u_objpg byte in cams60_22,
> u_objpg_scnd byte in infx60_03
> ) in optdbs01 extent size 31457280 next size
> 10485760 lock mode row;
> revoke all on "informix".optpgtrx_t from "public";
>
> create unique index "informix".optpgtrxn_x01 on
> "informix".optpgtrx_t
> (n_objid,n_objpg,n_pg_version desc);
> create index "informix".optpgtrxn_x02 on
> "informix".optpgtrx_t
> (n_clstrid,n_objid,n_objpg);
>
>
> Table statistics:
> npages--16777215
> nused--16777215
> ndata- 12749689
> nrows-178495641
> nextns-17
>
> The dbspace optdbs01 in which the table resides has
> around 8GB free space available.
> Here is the output from onmonitor for optdbs01
> DBspace has total of 20971500 pages, 4194175 of
> which
> are free
>
> Are we hitting some kind of bug in informix for
> maximum number of pages in a tablespace.
>
> What could be the possible solution for this kind of
> problem.
>
> Thanks,
>
> Vineet
>
>
>
>
>
>
>
>
> __________________________________
> Do you Yahoo!?
> The New Yahoo! Search - Faster. Easier. Bingo.
> http://search.yahoo.com
>
>
>
>
>


__________________________________
Do you Yahoo!?
The New Yahoo! Search - Faster. Easier. Bingo.
http://search.yahoo.com

Messages In This Thread

• Urgent:Error No. 136
  Vineet Mehrotra -- Tuesday, 13 May 2003, at 9:11 p.m.
  • RE: Urgent:Error No. 136
    Zablatzky, Wayne -- Tuesday, 13 May 2003, at 9:54 p.m.
    • RE: Urgent:Error No. 136
      Martin Fuerderer -- Wednesday, 14 May 2003, at 3:41 a.m.
  • Re: Urgent:Error No. 136
    Vineet Mehrotra -- Wednesday, 14 May 2003, at 4:00 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.