Rolling Window tables
I=92m trying to get this feature to work. I have a database, original created with RANGE/Interval that I=92ve = ALTERED to ROLLING WINDOW: This is what happened to the fragmentation clause for these tables: fragment by range(callstartdate) interval(interval( 1 = 00:00:00) day(9) to second) rolling (30 fragments) limit to 10 gb discard any store in(xxx_dbspace)=20 partition p0 VALUES < DATE ('01/01/2000' ) in xxx_dbspace extent size 32 next size 32 lock mode row; While there are primary keys defined, there are no foreign keys. I=92ve enable the ph_task =93purge_tables=94 task and set it to run = (now) every 15 minutes while I work through this. I=92ve tried calling syspurge() - to no effect, it reports procedure not = found, I also can=92t seem to find documentation on what the (apparently = optional) parameters to that procedure are, third one looks like = tablename. The table I am working with has 33 fragments - so three should be good = to get dropped. I can see that the purge_tables task has executed twice, but it hasn=92t = done anything. What am I missing? Has anyone else got this to work? Do I have to = rebuild all of the indexes to ensure they are attached? There is no = storage clause at this time for any of the indexes. cheers j.=
Ah my bad. I had it set to 300 fragments, I only had 33. Set it to 30 = - and it worked fine. j. On Sep 17, 2014, at 12:14 PM, Jack Parker <jack.parker4@verizon.net> = wrote: > I=3D92m trying to get this feature to work.=20 >=20 > I have a database, original created with RANGE/Interval that I=3D92ve = =3D=20 > ALTERED to ROLLING WINDOW:=20 >=20 > This is what happened to the fragmentation clause for these tables:=20 > fragment by range(callstartdate) interval(interval( 1 =3D=20 > 00:00:00) day(9) to second)=20 >=20 > rolling (30 fragments) limit to 10 gb discard any=20 >=20 > store in(xxx_dbspace)=3D20=20 >=20 > partition p0 VALUES < DATE ('01/01/2000' ) in xxx_dbspace=20 > extent size 32 next size 32 lock mode row;=20 >=20 > While there are primary keys defined, there are no foreign keys.=20 >=20 > I=3D92ve enable the ph_task =3D93purge_tables=3D94 task and set it to = run =3D=20 > (now) every 15 minutes while I work through this.=20 >=20 > I=3D92ve tried calling syspurge() - to no effect, it reports procedure = not =3D=20 > found, I also can=3D92t seem to find documentation on what the = (apparently =3D=20 > optional) parameters to that procedure are, third one looks like =3D=20= > tablename.=20 >=20 > The table I am working with has 33 fragments - so three should be good = =3D=20 > to get dropped.=20 >=20 > I can see that the purge_tables task has executed twice, but it = hasn=3D92t =3D=20 > done anything.=20 >=20 > What am I missing? Has anyone else got this to work? Do I have to =3D=20= > rebuild all of the indexes to ensure they are attached? There is no =3D=20= > storage clause at this time for any of the indexes.=20 >=20 > cheers=20 > j.=3D=20 >=20 >=20 > = **************************************************************************= *****=20 > Forum Note: Use "Reply" to post a response in the discussion forum.=20= >=20
On 17/09/14 17:14, Jack Parker wrote:
> I=92m trying to get this feature to work.
>
> I have a database, original created with RANGE/Interval that I=92ve =
> ALTERED to ROLLING WINDOW:
>
> This is what happened to the fragmentation clause for these tables:
> fragment by range(callstartdate) interval(interval( 1 =
> 00:00:00) day(9) to second)
>
> rolling (30 fragments) limit to 10 gb discard any
>
> store in(xxx_dbspace)=20
>
> partition p0 VALUES < DATE ('01/01/2000' ) in xxx_dbspace
> extent size 32 next size 32 lock mode row;
>
> While there are primary keys defined, there are no foreign keys.
>
> I=92ve enable the ph_task =93purge_tables=94 task and set it to run =
> (now) every 15 minutes while I work through this.
>
> I=92ve tried calling syspurge() - to no effect, it reports procedure not =
> found, I also can=92t seem to find documentation on what the (apparently =
> optional) parameters to that procedure are, third one looks like =
> tablename.
>
> The table I am working with has 33 fragments - so three should be good =
> to get dropped.
>
> I can see that the purge_tables task has executed twice, but it hasn=92t =
> done anything.
>
> What am I missing? Has anyone else got this to work? Do I have to =
> rebuild all of the indexes to ensure they are attached? There is no =
> storage clause at this time for any of the indexes.
>
> cheers
> j.=
>
Jack - I'm the culprit for RWT.
Care to share a few more details?
In the meantime, a couple of guesses:
syspurge() takes no arguments - and that's a lie! - well, it takes no
documented arguments, and certainly it does not take three or more (are you
not mixing it with the UDR taht you can specify in the STORE IN clause?), so
if you are passing it arguments, that might be a reason why it isn't resolved.
Re the number of fragments, as specified in the ROLLING, that's the number of
data fragments, so if you have one index, the fragment ditching will start
when oncheck -pt reports 62 fragments.
--
Ciao,
Marco
______________________________________________________________________________
Marco Greco /UK /IBM Standard disclaimers apply!
Structured Query Scripting Language http://www.4glworks.com/sqsl.htm
4glworks http://www.4glworks.com
Informix on Linux http://www.4glworks.com/ifmxlinux.htm
Now working. The only complaint I have is when trying to =93call=94 or =
=93execute procedure=94 syspurge() in the sysadmin database, it says not =
found - I can clearly see it in sys procedures.
j.=20
On Sep 17, 2014, at 1:24 PM, Marco Greco <marco@4glworks.com> wrote:
> On 17/09/14 17:14, Jack Parker wrote:=20
>> I=3D92m trying to get this feature to work.=20
>>=20
>> I have a database, original created with RANGE/Interval that I=3D92ve =
=3D=20
>> ALTERED to ROLLING WINDOW:=20
>>=20
>> This is what happened to the fragmentation clause for these tables:=20=
>> fragment by range(callstartdate) interval(interval( 1 =3D=20
>> 00:00:00) day(9) to second)=20
>>=20
>> rolling (30 fragments) limit to 10 gb discard any=20
>>=20
>> store in(xxx_dbspace)=3D20=20
>>=20
>> partition p0 VALUES < DATE ('01/01/2000' ) in xxx_dbspace=20
>> extent size 32 next size 32 lock mode row;=20
>>=20
>> While there are primary keys defined, there are no foreign keys.=20
>>=20
>> I=3D92ve enable the ph_task =3D93purge_tables=3D94 task and set it to =
run =3D=20
>> (now) every 15 minutes while I work through this.=20
>>=20
>> I=3D92ve tried calling syspurge() - to no effect, it reports =
procedure not =3D=20
>> found, I also can=3D92t seem to find documentation on what the =
(apparently =3D=20
>> optional) parameters to that procedure are, third one looks like =3D=20=
>> tablename.=20
>>=20
>> The table I am working with has 33 fragments - so three should be =
good =3D=20
>> to get dropped.=20
>>=20
>> I can see that the purge_tables task has executed twice, but it =
hasn=3D92t =3D=20
>> done anything.=20
>>=20
>> What am I missing? Has anyone else got this to work? Do I have to =3D=20=
>> rebuild all of the indexes to ensure they are attached? There is no =3D=
=20
>> storage clause at this time for any of the indexes.=20
>>=20
>> cheers=20
>> j.=3D=20
>>=20
>=20
> Jack - I'm the culprit for RWT.=20
> Care to share a few more details?=20
>=20
> In the meantime, a couple of guesses:=20
> syspurge() takes no arguments - and that's a lie! - well, it takes no=20=
> documented arguments, and certainly it does not take three or more =
(are you=20
> not mixing it with the UDR taht you can specify in the STORE IN =
clause?), so=20
> if you are passing it arguments, that might be a reason why it isn't =
resolved.=20
>=20
> Re the number of fragments, as specified in the ROLLING, that's the =
number of=20
> data fragments, so if you have one index, the fragment ditching will =
start=20
> when oncheck -pt reports 62 fragments.=20
>=20
> --=20
> Ciao,=20
> Marco=20
> =
__________________________________________________________________________=
____=20
> Marco Greco /UK /IBM Standard disclaimers apply!=20
>=20
> Structured Query Scripting Language http://www.4glworks.com/sqsl.htm=20=
> 4glworks http://www.4glworks.com=20
> Informix on Linux http://www.4glworks.com/ifmxlinux.htm=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
Also, where does it log what it did?
j.
On Sep 17, 2014, at 1:24 PM, Marco Greco <marco@4glworks.com> wrote:
> On 17/09/14 17:14, Jack Parker wrote:=20
>> I=3D92m trying to get this feature to work.=20
>>=20
>> I have a database, original created with RANGE/Interval that I=3D92ve =
=3D=20
>> ALTERED to ROLLING WINDOW:=20
>>=20
>> This is what happened to the fragmentation clause for these tables:=20=
>> fragment by range(callstartdate) interval(interval( 1 =3D=20
>> 00:00:00) day(9) to second)=20
>>=20
>> rolling (30 fragments) limit to 10 gb discard any=20
>>=20
>> store in(xxx_dbspace)=3D20=20
>>=20
>> partition p0 VALUES < DATE ('01/01/2000' ) in xxx_dbspace=20
>> extent size 32 next size 32 lock mode row;=20
>>=20
>> While there are primary keys defined, there are no foreign keys.=20
>>=20
>> I=3D92ve enable the ph_task =3D93purge_tables=3D94 task and set it to =
run =3D=20
>> (now) every 15 minutes while I work through this.=20
>>=20
>> I=3D92ve tried calling syspurge() - to no effect, it reports =
procedure not =3D=20
>> found, I also can=3D92t seem to find documentation on what the =
(apparently =3D=20
>> optional) parameters to that procedure are, third one looks like =3D=20=
>> tablename.=20
>>=20
>> The table I am working with has 33 fragments - so three should be =
good =3D=20
>> to get dropped.=20
>>=20
>> I can see that the purge_tables task has executed twice, but it =
hasn=3D92t =3D=20
>> done anything.=20
>>=20
>> What am I missing? Has anyone else got this to work? Do I have to =3D=20=
>> rebuild all of the indexes to ensure they are attached? There is no =3D=
=20
>> storage clause at this time for any of the indexes.=20
>>=20
>> cheers=20
>> j.=3D=20
>>=20
>=20
> Jack - I'm the culprit for RWT.=20
> Care to share a few more details?=20
>=20
> In the meantime, a couple of guesses:=20
> syspurge() takes no arguments - and that's a lie! - well, it takes no=20=
> documented arguments, and certainly it does not take three or more =
(are you=20
> not mixing it with the UDR taht you can specify in the STORE IN =
clause?), so=20
> if you are passing it arguments, that might be a reason why it isn't =
resolved.=20
>=20
> Re the number of fragments, as specified in the ROLLING, that's the =
number of=20
> data fragments, so if you have one index, the fragment ditching will =
start=20
> when oncheck -pt reports 62 fragments.=20
>=20
> --=20
> Ciao,=20
> Marco=20
> =
__________________________________________________________________________=
____=20
> Marco Greco /UK /IBM Standard disclaimers apply!=20
>=20
> Structured Query Scripting Language http://www.4glworks.com/sqsl.htm=20=
> 4glworks http://www.4glworks.com=20
> Informix on Linux http://www.4glworks.com/ifmxlinux.htm=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20