update statistics

Hi, I have an error during executing the following command update statistics high for table tablename1SQL Error(-567):Cannot write sorted rows ISAM error:no free disk space for sort Where it tries to find free disk space (tempdb or on the dbspace that the table resides)? The specific table is fragmented on more than one dbspaces and there is free space on these dbspaces. The table has now 53 million rows Any ideas Thanks Sergios

Check the Administrators Guide and the Performance Guide for details of the order in which IDS uses dbspaces and filesystems for temporary sort-work files. But, Q&D it looks like the tempdbspaces you have set up in DBSPACETEMP, if any, are too small to hold the sort-work files. Also look up the environment variable DBUPSPACE to increase the size of the in-memory sort-work areas to reduce disk IO during sorting. Art S. Kagel ----- Original Message ----- From: Sergios.Zag.... <ids@iiug.org> At: 2/16 9:38 Hi, I have an error during executing the following command update statistics high for table tablename1SQL Error(-567):Cannot write sorted rows ISAM error:no free disk space for sort Where it tries to find free disk space (tempdb or on the dbspace that the table resides)? The specific table is fragmented on more than one dbspaces and there is free space on these dbspaces. The table has now 53 million rows Any ideas Thanks Sergios ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

It depends on your settings..... I presume you are in 7.x, so you should check your env variable DBSPACETEMP,and the onconfig parameter and take a look at your temp dbspaces. esteban.- --- "sergios.zag...." <sergios.zagotsis@egnatiabank.gr> escribió: > > Hi, > > I have an error during executing the following > command > update statistics high for table tablename1> SQL Error(-567):Cannot write sorted rows > ISAM error:no free disk space for sort> > Where it tries to find free disk space (tempdb or on > the dbspace that the > table resides)? > The specific table is fragmented on more than one > dbspaces and there is free > space on these dbspaces. > The table has now 53 million rows > > Any ideas > > Thanks > > Sergios > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the > discussion forum. > > ___________________________________________________________ 1GB gratis, Antivirus y Antispam Correo Yahoo!, el mejor correo web del mundo http://correo.yahoo.com.ar

Hi, There are 4 tmpdb spaces defined onconfig statement DBSPACETEMP tmpdbs1:tmpdbs2:tmpdbs3:tmpdbs4 onstat -d output: .................................3ef8c978 7 0x2001 8 1 N T informix tmpdbs1 3ef8cac8 8 0x2001 9 1 N T informix tmpdbs2 3ef8cc18 9 0x2001 10 1 N T informix tmpdbs3 3ef8cd68 10 0x2001 11 1 N T informix tmpdbs4 3ef55a48 8 7 0 500000 499847 PO- E:\\\\IFMXDATA\\\\ol_prime\\\\tmpdbs1_dat.000 3ef55bb0 9 8 0 500000 499847 PO- E:\\\\IFMXDATA\\\\ol_prime\\\\tmpdbs2_dat.000 3ef55d18 10 9 0 500000 499847 PO- E:\\\\IFMXDATA\\\\ol_prime\\\\tmpdbs3_dat.000 3ef55e80 11 10 0 500000 499847 PO- E:\\\\IFMXDATA\\\\ol_prime\\\\tmpdbs4_dat.000 ............................................................... We have Informix 9.30 TC3 on a Xeon 2x3.8 GHz with 4GB Ram There is no DBUPSPACE environment variable define C Drive has 50GB free disk and E drive has 800GB free disk onstat -g seq output Informix Dynamic Server Version 9.30.TC3 -- On-Line -- Up 23:28:59 --1354176 Kbytes onconfig statements MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority DS_MAX_QUERIES 32 # Maximum number of decision support queries DS_TOTAL_MEMORY 4096 # Decision support memory (Kbytes) DS_MAX_SCANS 1048576 # Maximum number of decision support scans I suppose that during update statistics the DS_TOTAL_MEMORY value is used and that low value causes the problem. Am I right? I am not sure which value do I have to set for that variable. There is any way to calculate it? Thanks Sergios ----- Original Message ----- From: "ART KAGEL, ...." <kagel@bloomberg.net> To: <ids@iiug.org> Sent: Thursday, February 16, 2006 4:42 PM Subject: Re: update statistics [6406] > > Check the Administrators Guide and the Performance Guide for details of the > order in which IDS uses dbspaces and filesystems for temporary sort-work > files. > But, Q&D it looks like the tempdbspaces you have set up in DBSPACETEMP, if > any, > are too small to hold the sort-work files. Also look up the environment > variable DBUPSPACE to increase the size of the in-memory sort-work areas to > reduce disk IO during sorting. > > Art S. Kagel > > ----- Original Message ----- > From: Sergios.Zag.... <ids@iiug.org> > At: 2/16 9:38 > > Hi, > > I have an error during executing the following command > update statistics high for table tablename1> SQL Error(-567):Cannot write sorted rows > ISAM error:no free disk space for sort> > Where it tries to find free disk space (tempdb or on the dbspace that the > table resides)? > The specific table is fragmented on more than one dbspaces and there is free > space on these dbspaces. > The table has now 53 million rows > > Any ideas > > Thanks > > Sergios > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. > > > **************************************************************************** *** > Forum Note: Use "Reply" to post a response in the discussion forum. >

> ----- Original Message ----- > From: Sergios.Zag.... <ids@iiug.org> > At: 2/17 3:12 > > Hi, Hello. > There are 4 tmpdb spaces defined > > onconfig statement > DBSPACETEMP tmpdbs1:tmpdbs2:tmpdbs3:tmpdbs4 OK, so 4GB of tempdbspace... > onstat -d output: > ..................................> 3ef8c978 7 0x2001 8 1 N T informix tmpdbs1 > 3ef8cac8 8 0x2001 9 1 N T informix tmpdbs2 > 3ef8cc18 9 0x2001 10 1 N T informix tmpdbs3 > 3ef8cd68 10 0x2001 11 1 N T informix tmpdbs4 > > 3ef55a48 8 7 0 500000 499847 PO- > E:\\\\IFMXDATA\\\\ol_prime\\\\tmpdbs1_dat.000 > 3ef55bb0 9 8 0 500000 499847 PO- > E:\\\\IFMXDATA\\\\ol_prime\\\\tmpdbs2_dat.000 > 3ef55d18 10 9 0 500000 499847 PO- > E:\\\\IFMXDATA\\\\ol_prime\\\\tmpdbs3_dat.000 > 3ef55e80 11 10 0 500000 499847 PO- > E:\\\\IFMXDATA\\\\ol_prime\\\\tmpdbs4_dat.000 > ................................................................ > > We have Informix 9.30 TC3 on a Xeon 2x3.8 GHz with 4GB Ram 9.30xC3 is the first optimized server version in the 9.xx codebase, so the expanded use of DBUPSPACE and PDQPRIORITY applies. (See John Miller III's article: http://www-128.ibm.com/developerworks/db2/zones/informix/library/techarticle/mil ler/0203miller.html ) > There is no DBUPSPACE environment variable define Unless you set PDQPRIORITY, DBUPSPACE is the only way to increase the memory used for sorting from the default of 15MB for this version (4MB for earlier versions) to the maximum without PDQ of 50MB (35MB in earlier releases). I do not see from your notes below that you have PDQPRIORITY set, so DS_TOTAL_MEMORY is ignored. Only if PDQPRIORITY >= 1 is DS memory used for sorting. Do you have PSORT_DBTEMP set? If so the folders/directories/filesystems listed there will take precedence over the temp dbspaces listed in DBSPACETEMP for writing sort-work files and DBSPACETEMP will be ignored. > C Drive has 50GB free disk and E drive has 800GB free disk > onstat -g seq output > Informix Dynamic Server Version 9.30.TC3 -- On-Line -- Up 23:28:59 --> 1354176 Kbytes > > onconfig statements > MAX_PDQPRIORITY 100 # Maximum allowed pdqpriority > DS_MAX_QUERIES 32 # Maximum number of decision support queries > DS_TOTAL_MEMORY 4096 # Decision support memory (Kbytes) > DS_MAX_SCANS 1048576 # Maximum number of decision support scans> > I suppose that during update statistics the DS_TOTAL_MEMORY value is used Only, as I stated above, PDQPRIORITY is set >0. Otherwise the session will allocate a work area from the general virtual memory segment pool (or cause another virtual segment to be allocated) sized according to the contents of DBUPSPACE or the default of 15MB if that var is unset. > and that low value causes the problem. Am I right? No. The -567 error definitely refers to writing the sort-work area (whereever it's allocated) to disk when the dataset to be sorted is larger than the work area. Your problem is disk related somehow. > I am not sure which value do I have to set for that variable. There is any > way to calculate it? I should have referred to this originally: I see you are trying to run UPDATE STATISTICS HIGH on the entire table. This is unneccessary forperformance and takes a HUGE amount of resources and sort space. You should read the Performance Guide and John Miller III's paper (URL above) to see the recommended suite of commands to create useful levels of stats and data distributions with minimal work and runtime. Alternatively, you could get my dostats utility (new version coming soon - one bug left to zap) which implements both sets of protocols automatically. Dostats is contained in the package utils2_ak in the IIUG Software Repository. Art S. Kagel > Thanks > > Sergios > <Original Post SNIPPED>