Fragmented Tables Information
Hi all,
i m new to informix, i have many fragmented and non fragmented tables in my db.
Requirement 1: i want to just display the fragmented tables
Requirement 2: i want to display only those fragemented tables which are
closed to finish their fragments
For example i have following fragmented tables
create table parttab1
(
name varchar(19) not null ,
sum_date date not null
)
fragment by expression
partition p1 ((sum_date >= DATE ('02/01/2011'
) ) AND (sum_date < DATE ('03/01/2011' ) ) ) in
datadbs1,
partition p2 ((sum_date >= DATE ('03/01/2011'
) ) AND (sum_date < DATE ('04/01/2011' ) ) ) in
datadbs1,
partition p3 ((sum_date >= DATE ('04/01/2011'
) ) AND (sum_date < DATE ('05/01/2011' ) ) ) in
datadbs1,
partition p4 ((sum_date >= DATE ('05/01/2011'
) ) AND (sum_date < DATE ('06/01/2011' ) ) ) in
datadbs1,
partition p5 ((sum_date >= DATE ('06/01/2011'
) ) AND (sum_date < DATE ('07/01/2011' ) ) ) in
datadbs1,
partition p6 ((sum_date >= DATE ('07/01/2011'
) ) AND (sum_date < DATE ('08/01/2011' ) ) ) in
datadbs1,
partition p7 ((sum_date >= DATE ('08/01/2011'
) ) AND (sum_date < DATE ('09/01/2011' ) ) ) in
datadbs1,
partition p8 ((sum_date >= DATE ('09/01/2011'
) ) AND (sum_date < DATE ('10/01/2011' ) ) ) in
datadbs1,
remainder in datadbs1
extent size 10240 next size 51200 lock mode row;
create table parttab2
(
name varchar(19) not null ,
sum_date date not null
)
fragment by expression
partition p1 ((sum_date >= DATE ('02/01/2011'
) ) AND (sum_date < DATE ('03/01/2011' ) ) ) in
datadbs1,
partition p2 ((sum_date >= DATE ('03/01/2011'
) ) AND (sum_date < DATE ('04/01/2011' ) ) ) in
datadbs1,
partition p3 ((sum_date >= DATE ('04/01/2011'
) ) AND (sum_date < DATE ('05/01/2011' ) ) ) in
datadbs1,
partition p4 ((sum_date >= DATE ('05/01/2011'
) ) AND (sum_date < DATE ('06/01/2011' ) ) ) in
datadbs1,
partition p5 ((sum_date >= DATE ('06/01/2011'
) ) AND (sum_date < DATE ('07/01/2011' ) ) ) in
datadbs1,
remainder in datadbs1
extent size 10240 next size 51200 lock mode row;
I just want to display all those fragmented table which having partioned only
for next month (e.g parttab have last partion for month of june so i need to
add new partions)
i want to get this information so i that i know the tables getting close to
finish their partitions so i can add partitions to these tables
regards,
Muhammad Shakeel Azeem
The info you seek is in sysfragments.
Select a.tabname, b.exprtext=20
from systables a, sysfragments b
where a.tabid=3Db.tabid
Alas, exprtext is a text field and you can't query on it.
What I do is name the fragments according to their timespan, I have a 4 =
char abbreviation for each table, to that I add the date and I get =
something like: tabx20110501.
Then I can do the query above using partition instead of exprtext.
I would encourage you to reverse the order of your date comparisons:
> (sum_date < DATE ('03/01/2011' )=20
> AND (sum_date >=3D DATE ('02/01/2011' )
When the engine is looking for where to put the data, it evaluates the =
first condition, and if it passes, evaluates the second. In your =
scheme, data will always pass the first evaluation and force evaluation =
of the second. By reversing the dates, the data will typically fail the =
first evaluation and not have to do the second.
j.
On May 25, 2011, at 4:54 AM, MUHAMMAD SHAKEEL AZEEM wrote:
> Hi all,=20
>=20
> i m new to informix, i have many fragmented and non fragmented tables =
in my=20
> db.=20
> Requirement 1: i want to just display the fragmented tables=20
> Requirement 2: i want to display only those fragemented tables which =
are=20
> closed to finish their fragments=20
> For example i have following fragmented tables=20
>=20
> create table parttab1=20
> (=20>=20
> name varchar(19) not null ,=20
>=20
> sum_date date not null=20
> )=20
> fragment by expression=20
>=20
> partition p1 ((sum_date >=3D DATE ('02/01/2011'=20
>=20
> ) ) AND (sum_date < DATE ('03/01/2011' ) ) ) in=20
>=20
> datadbs1,=20
>=20
> partition p2 ((sum_date >=3D DATE ('03/01/2011'=20
>=20
> ) ) AND (sum_date < DATE ('04/01/2011' ) ) ) in=20
>=20
> datadbs1,=20
>=20
> partition p3 ((sum_date >=3D DATE ('04/01/2011'=20
>=20
> ) ) AND (sum_date < DATE ('05/01/2011' ) ) ) in=20
>=20
> datadbs1,=20
>=20
> partition p4 ((sum_date >=3D DATE ('05/01/2011'=20
>=20
> ) ) AND (sum_date < DATE ('06/01/2011' ) ) ) in=20
>=20
> datadbs1,=20
>=20
> partition p5 ((sum_date >=3D DATE ('06/01/2011'=20
>=20
> ) ) AND (sum_date < DATE ('07/01/2011' ) ) ) in=20
>=20
> datadbs1,=20
>=20
> partition p6 ((sum_date >=3D DATE ('07/01/2011'=20
>=20
> ) ) AND (sum_date < DATE ('08/01/2011' ) ) ) in=20
>=20
> datadbs1,=20
>=20
> partition p7 ((sum_date >=3D DATE ('08/01/2011'=20
>=20
> ) ) AND (sum_date < DATE ('09/01/2011' ) ) ) in=20
>=20
> datadbs1,=20
>=20
> partition p8 ((sum_date >=3D DATE ('09/01/2011'=20
>=20
> ) ) AND (sum_date < DATE ('10/01/2011' ) ) ) in=20
>=20
> datadbs1,=20
>=20
> remainder in datadbs1=20
> extent size 10240 next size 51200 lock mode row;=20
>=20
> create table parttab2=20
> (=20>=20
> name varchar(19) not null ,=20
>=20
> sum_date date not null=20
> )=20
> fragment by expression=20
>=20
> partition p1 ((sum_date >=3D DATE ('02/01/2011'=20
>=20
> ) ) AND (sum_date < DATE ('03/01/2011' ) ) ) in=20
>=20
> datadbs1,=20
>=20
> partition p2 ((sum_date >=3D DATE ('03/01/2011'=20
>=20
> ) ) AND (sum_date < DATE ('04/01/2011' ) ) ) in=20
>=20
> datadbs1,=20
>=20
> partition p3 ((sum_date >=3D DATE ('04/01/2011'=20
>=20
> ) ) AND (sum_date < DATE ('05/01/2011' ) ) ) in=20
>=20
> datadbs1,=20
>=20
> partition p4 ((sum_date >=3D DATE ('05/01/2011'=20
>=20
> ) ) AND (sum_date < DATE ('06/01/2011' ) ) ) in=20
>=20
> datadbs1,=20
>=20
> partition p5 ((sum_date >=3D DATE ('06/01/2011'=20
>=20
> ) ) AND (sum_date < DATE ('07/01/2011' ) ) ) in=20
>=20
> datadbs1,=20
>=20
> remainder in datadbs1=20
> extent size 10240 next size 51200 lock mode row;=20
>=20
> I just want to display all those fragmented table which having =
partioned only=20
> for next month (e.g parttab have last partion for month of june so i =
need to=20
> add new partions)=20
> i want to get this information so i that i know the tables getting =
close to=20
> finish their partitions so i can add partitions to these tables=20
>=20
> regards,=20
> Muhammad Shakeel Azeem=20
>=20
>=20
> =
**************************************************************************=
*****=20
> Forum Note: Use "Reply" to post a response in the discussion forum.=20=
>=20
Hi all,
This is my actual post ,i don't know why format of this message disturbed
i m new to informix, i have many fragmented and non fragmented tables in my db.
Requirement 1: i want to just display the fragmented tables
Requirement 2: i want to display only those fragemented tables which are
closed to finish their fragments
For example i have following fragmented tables
create table parttab1
(
name varchar(19) not null ,sum_date date not null
)
fragment by expression
partition p1 ((sum_date >= DATE ('02/01/2011') ) AND (sum_date < DATE
('03/01/2011' ) ) ) in datadbs1,
partition p2 ((sum_date >= DATE ('03/01/2011') ) AND (sum_date < DATE
('04/01/2011' ) ) ) in datadbs1,
partition p3 ((sum_date >= DATE ('04/01/2011') ) AND (sum_date < DATE
('05/01/2011' ) ) ) in datadbs1,
partition p4 ((sum_date >= DATE ('05/01/2011') ) AND (sum_date < DATE
('06/01/2011' ) ) ) in datadbs1,
partition p5 ((sum_date >= DATE ('06/01/2011') ) AND (sum_date < DATE
('07/01/2011' ) ) ) in datadbs1,
partition p6 ((sum_date >= DATE ('07/01/2011' ) ) AND (sum_date < DATE
('08/01/2011' ) ) ) in datadbs1,
partition p7 ((sum_date >= DATE ('08/01/2011' ) ) AND (sum_date < DATE
('09/01/2011' ) ) ) in datadbs1,
partition p8 ((sum_date >= DATE ('09/01/2011' ) ) AND (sum_date < DATE
('10/01/2011' ) ) ) in datadbs1,
remainder in datadbs1
extent size 10240 next size 51200 lock mode row;
create table parttab2
(
name varchar(19) not null ,sum_date date not null
)
fragment by expression
partition p1 ((sum_date >= DATE ('02/01/2011' ) ) AND (sum_date < DATE
('03/01/2011' ) ) ) in datadbs1,
partition p2 ((sum_date >= DATE ('03/01/2011' ) ) AND (sum_date < DATE
('04/01/2011' ) ) ) in datadbs1,
partition p3 ((sum_date >= DATE ('04/01/2011' ) ) AND (sum_date < DATE
('05/01/2011' ) ) ) in datadbs1,
partition p4 ((sum_date >= DATE ('05/01/2011' ) ) AND (sum_date < DATE
('06/01/2011' ) ) ) in datadbs1,
partition p5 ((sum_date >= DATE ('06/01/2011' ) ) AND (sum_date < DATE
('07/01/2011' ) ) ) in datadbs1,
remainder in datadbs1
extent size 10240 next size 51200 lock mode row;
I just want to display all those fragmented table which having partioned only
for next month (e.g parttab have last partion for month of june so i need to
add new partions)
i want to get this information so i that i know the tables getting close to
finish their partitions so i can add partitions to these tables
regards,
Muhammad Shakeel Azeem
Requirement #1:
select tabname from systables where partnum = 0;
Requirement #2 is a bit more difficult because the fragmentation expression
is stored in the TEXT type column exprtext which you cannot search with
ordinary SQL nor using the Basic Text Search datablade. Your best bet will
be to run dbschema and parse the output using AWK or Perl.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, May 25, 2011 at 4:54 AM, MUHAMMAD SHAKEEL AZEEM
<mazeem@i2cinc.com>wrote:
> Hi all,
>
> i m new to informix, i have many fragmented and non fragmented tables in my
> db.
> Requirement 1: i want to just display the fragmented tables
> Requirement 2: i want to display only those fragemented tables which are
> closed to finish their fragments
> For example i have following fragmented tables
>
> create table parttab1
> (>
> name varchar(19) not null ,
>
> sum_date date not null
> )
> fragment by expression
>
> partition p1 ((sum_date >= DATE ('02/01/2011'
>
> ) ) AND (sum_date < DATE ('03/01/2011' ) ) ) in
>
> datadbs1,
>
> partition p2 ((sum_date >= DATE ('03/01/2011'
>
> ) ) AND (sum_date < DATE ('04/01/2011' ) ) ) in
>
> datadbs1,
>
> partition p3 ((sum_date >= DATE ('04/01/2011'
>
> ) ) AND (sum_date < DATE ('05/01/2011' ) ) ) in
>
> datadbs1,
>
> partition p4 ((sum_date >= DATE ('05/01/2011'
>
> ) ) AND (sum_date < DATE ('06/01/2011' ) ) ) in
>
> datadbs1,
>
> partition p5 ((sum_date >= DATE ('06/01/2011'
>
> ) ) AND (sum_date < DATE ('07/01/2011' ) ) ) in
>
> datadbs1,
>
> partition p6 ((sum_date >= DATE ('07/01/2011'
>
> ) ) AND (sum_date < DATE ('08/01/2011' ) ) ) in
>
> datadbs1,
>
> partition p7 ((sum_date >= DATE ('08/01/2011'
>
> ) ) AND (sum_date < DATE ('09/01/2011' ) ) ) in
>
> datadbs1,
>
> partition p8 ((sum_date >= DATE ('09/01/2011'
>
> ) ) AND (sum_date < DATE ('10/01/2011' ) ) ) in
>
> datadbs1,
>
> remainder in datadbs1
> extent size 10240 next size 51200 lock mode row;
>
> create table parttab2
> (>
> name varchar(19) not null ,
>
> sum_date date not null
> )
> fragment by expression
>
> partition p1 ((sum_date >= DATE ('02/01/2011'
>
> ) ) AND (sum_date < DATE ('03/01/2011' ) ) ) in
>
> datadbs1,
>
> partition p2 ((sum_date >= DATE ('03/01/2011'
>
> ) ) AND (sum_date < DATE ('04/01/2011' ) ) ) in
>
> datadbs1,
>
> partition p3 ((sum_date >= DATE ('04/01/2011'
>
> ) ) AND (sum_date < DATE ('05/01/2011' ) ) ) in
>
> datadbs1,
>
> partition p4 ((sum_date >= DATE ('05/01/2011'
>
> ) ) AND (sum_date < DATE ('06/01/2011' ) ) ) in
>
> datadbs1,
>
> partition p5 ((sum_date >= DATE ('06/01/2011'
>
> ) ) AND (sum_date < DATE ('07/01/2011' ) ) ) in
>
> datadbs1,
>
> remainder in datadbs1
> extent size 10240 next size 51200 lock mode row;
>
> I just want to display all those fragmented table which having partioned
> only
> for next month (e.g parttab have last partion for month of june so i need
> to
> add new partions)
> i want to get this information so i that i know the tables getting close to
> finish their partitions so i can add partitions to these tables
>
> regards,
> Muhammad Shakeel Azeem
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--bcaec5014c47df8bbb04a419b609
Oh, BTW, if you are running Informix 11.70 you can alter the fragmentation
scheme to use an interval/range fragmentation scheme which will
automatically add a new fragment for you each month.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, May 25, 2011 at 9:30 AM, Art Kagel <art.kagel@gmail.com> wrote:
> Requirement #1:
>
> select tabname from systables where partnum = 0;>
> Requirement #2 is a bit more difficult because the fragmentation expression
> is stored in the TEXT type column exprtext which you cannot search with
> ordinary SQL nor using the Basic Text Search datablade. Your best bet will
> be to run dbschema and parse the output using AWK or Perl.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> Blog: http://informix-myview.blogspot.com/
>
> Disclaimer: Please keep in mind that my own opinions are my own opinions
> and do not reflect on my employer, Advanced DataTools, the IIUG, nor any
> other organization with which I am associated either explicitly, implicitly,
> or by inference. Neither do those opinions reflect those of other
> individuals affiliated with any entity with which I am affiliated nor those
> of the entities themselves.
>
>
>
>
> On Wed, May 25, 2011 at 4:54 AM, MUHAMMAD SHAKEEL AZEEM <mazeem@i2cinc.com
> > wrote:
>
>> Hi all,
>>
>> i m new to informix, i have many fragmented and non fragmented tables in
>> my
>> db.
>> Requirement 1: i want to just display the fragmented tables
>> Requirement 2: i want to display only those fragemented tables which are
>> closed to finish their fragments
>> For example i have following fragmented tables
>>
>> create table parttab1
>> (>>
>> name varchar(19) not null ,
>>
>> sum_date date not null
>> )
>> fragment by expression
>>
>> partition p1 ((sum_date >= DATE ('02/01/2011'
>>
>> ) ) AND (sum_date < DATE ('03/01/2011' ) ) ) in
>>
>> datadbs1,
>>
>> partition p2 ((sum_date >= DATE ('03/01/2011'
>>
>> ) ) AND (sum_date < DATE ('04/01/2011' ) ) ) in
>>
>> datadbs1,
>>
>> partition p3 ((sum_date >= DATE ('04/01/2011'
>>
>> ) ) AND (sum_date < DATE ('05/01/2011' ) ) ) in
>>
>> datadbs1,
>>
>> partition p4 ((sum_date >= DATE ('05/01/2011'
>>
>> ) ) AND (sum_date < DATE ('06/01/2011' ) ) ) in
>>
>> datadbs1,
>>
>> partition p5 ((sum_date >= DATE ('06/01/2011'
>>
>> ) ) AND (sum_date < DATE ('07/01/2011' ) ) ) in
>>
>> datadbs1,
>>
>> partition p6 ((sum_date >= DATE ('07/01/2011'
>>
>> ) ) AND (sum_date < DATE ('08/01/2011' ) ) ) in
>>
>> datadbs1,
>>
>> partition p7 ((sum_date >= DATE ('08/01/2011'
>>
>> ) ) AND (sum_date < DATE ('09/01/2011' ) ) ) in
>>
>> datadbs1,
>>
>> partition p8 ((sum_date >= DATE ('09/01/2011'
>>
>> ) ) AND (sum_date < DATE ('10/01/2011' ) ) ) in
>>
>> datadbs1,
>>
>> remainder in datadbs1
>> extent size 10240 next size 51200 lock mode row;
>>
>> create table parttab2
>> (>>
>> name varchar(19) not null ,
>>
>> sum_date date not null
>> )
>> fragment by expression
>>
>> partition p1 ((sum_date >= DATE ('02/01/2011'
>>
>> ) ) AND (sum_date < DATE ('03/01/2011' ) ) ) in
>>
>> datadbs1,
>>
>> partition p2 ((sum_date >= DATE ('03/01/2011'
>>
>> ) ) AND (sum_date < DATE ('04/01/2011' ) ) ) in
>>
>> datadbs1,
>>
>> partition p3 ((sum_date >= DATE ('04/01/2011'
>>
>> ) ) AND (sum_date < DATE ('05/01/2011' ) ) ) in
>>
>> datadbs1,
>>
>> partition p4 ((sum_date >= DATE ('05/01/2011'
>>
>> ) ) AND (sum_date < DATE ('06/01/2011' ) ) ) in
>>
>> datadbs1,
>>
>> partition p5 ((sum_date >= DATE ('06/01/2011'
>>
>> ) ) AND (sum_date < DATE ('07/01/2011' ) ) ) in
>>
>> datadbs1,
>>
>> remainder in datadbs1
>> extent size 10240 next size 51200 lock mode row;
>>
>> I just want to display all those fragmented table which having partioned
>> only
>> for next month (e.g parttab have last partion for month of june so i need
>> to
>> add new partions)
>> i want to get this information so i that i know the tables getting close
>> to
>> finish their partitions so i can add partitions to these tables
>>
>> regards,
>> Muhammad Shakeel Azeem
>>
>>
>>
>>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
>>
>
--20cf30549e6b63f22c04a419ba11