RE: Frag elimination Bug ? Workaround ?

bleb@harland.net wrote: > David Williams <djw@smooth1.demon.co.uk> wrote: > > >In article <F122B1778C85B6D6004166D7852565F5.004166D6852565F5@notes- > >inet1.harland.net>, Barry Leb <bleb@harland.net> writes > >>Has anyone seen this problem and do you know of a workaround? > >> > >>Sun 6000e, Solaris 2.6 > >>Informix 7.24.uc4 > >> > >>Fragmenting a table across 58 dbspaces.(carrying 13 mos of weekly = data > >>which will roll-off each weekl). Fragmenting by expression on the > >>first column which is a date. This is what happens: > >> > > Exactly what are the fragment expressions? > > fragment by expression > inv_dte =3D '3/31/1997' in dbs01 > inv_dte =3D '4/7/1997 in dbs02 > . > . > . > etc > [..snipped..] There is a limitation referred to in TFM that warns against using 2-digit = century in fragment expressions - and I realise you're not doing that. = But the manual (IMHO) does not go far enough to warn you that the = fragment expression seems to be stored with the $DBDATE that's effective = at the time of the ALTER FRAGMENT execution. Subsequent queries that use = a different $DBDATE run into all sorts of problems. We have a number of fragmentation strategies that rely on date-ranges (eg = 5 days to a DBspace etc) and used to have all manner of grief with = performance and elimination etc. I found the solution to this is to manipulate the fragment expressions = using the Informix internal representation of the date (ie 1998-05-01 =3D = 35915). Whilst it reduces the 'readability' of the dbschema and = sysfragments info, all our fragment elimination problems immediately = disappeared. So try: FRAGMENT BY EXPRESSION > inv_dte =3D 35519 IN dbs01 > inv_dte =3D 35526 IN dbs02 as a work-around and see how you go. A simple way to perform the translation from DATE to INTEGER is to do this= : SELECT date_field, TRUNC(DATE(date_field)) AS int_value FROM ... HTH RET +------------------------------------------+ | Richard Thomas | | DBA - Marketing Information Systems | | Optus IT | | email: richard_thomas@yes.optus.com.au | | Ph: +61 2 9342 7188 | | "My opinions are my opinions" | +------------------------------------------+