SEO

I have started a document for IDS Feature Requests at

http://www.smooth1.co.uk/pages/smoot...com/IDS_FR.txt

Can people add to this? Please post (email address, date, request) to
this topic.

(Using Google Groups I cannot see peoples full email address!)

David.

PS IDS 10.00.UC4 has Truncate Table !!

http://publib.boulder.ibm.com/infoce...otes_10.0.html

IDS 10.0 also has:
"Be able to use raw devices in Linux (O_DIRECT when opening chunks)."

See:
http://www-128.ibm.com/developerwork.../dm-0503szabo/

david@smooth1.co.uk wrote:
> PS IDS 10.00.UC4 has Truncate Table !!
>
> http://publib.boulder.ibm.com/infoce...otes_10.0.html
>

Hi David,

Just a thought, pl. include in the list if ok:

Feature When
Whom

Do not drop views if parent table 14-12-2005
hariog@yahoo.com
has been dropped. Just invalidate
it. Also, command to validate and
list such views.

Thanks.

david@smooth1.co.uk napisa³(a):
> I have started a document for IDS Feature Requests at
>
> http://www.smooth1.co.uk/pages/smoot...com/IDS_FR.txt
>
> Can people add to this? Please post (email address, date, request) to
> this topic.
>
> (Using Google Groups I cannot see peoples full email address!)
>
> David.
>
There are few things which would be very useful for my Informix database.

1. Ability to have SPL procedures >64k
Not critical but it would be good to not be forced to split SPL
procedures.

2. Time based periodic fragmentation
This needs some explanation. I'll describe my particular case for
simplicity (I'm not very fluent in English) first.
Our database have millions of financial transactions. Each
transaction have its date. 'Transaction' table have 12 fragments each
holding transactions with the same month.
Such scheme is useful: processing current month transactions is
faster; also purging is faster - by simply detaching oldest fragment.

There is an issue with such fragmentation scheme: although "fragment
on month(transaction_date)" works there is no fragment elimination.
This is because fragment elimination is possible only for simple
expression. Simple expression cannot contain function, even if it is
built-in month function.

We are using simple workaround: there is special field containing
month. This field is used for fragmentation.

However it would be good to support such fragmentation scheme natively.
In more general case things are more complex: maybe somebody needs
bi-monthly fragments, or weekly fragments, or ... .
It seems that all such needs would be fulfilled if Informix would
support fragment elimination also for "simple function".
"Simple function" is a function taking one argument (transaction
date in my particular case) and returning integer value (number of the
month). Is this possible?
It would be something like this:
FRAGMENT BY EXPRESION SWITCH (month(tr_date), frag1, .. frag12)
or
FRAGMENT BY EXPRESION SWITCH (month(tr_date), frag1, .. frag12)
REMAINDER IN bad_frag

3. Tools for reclaiming disk space
There are some tables for which we have plenty of inserts and
deletes every day within one fragment. After some time it appears that
all pages in such a fragment are marked as used although often there is
only one short record in the page.
Such a case is very uncomfortable: our reports are alerting DBAs
that there is a danger of exhausting storage within the fragment because
nptotal (number of allocated pages) is approaching total number of pages
in the fragment (we are using a rule that less than 5% of free pages
generates alert).
This is not true because there are plenty of storage within already
allocated pages. However nobody knows how to measure remaining space.

Solution is to periodically reclaim unused storage space. There are
2 options:
- alter index to cluster;
- alter fragment .. init;
Unfortunately it is not easy to do that regularly: both methods
require much time with exclusive access to the table and much spare disk
space - for old and new location of the data. This is resource hungry
task - I often seen 'long transaction aborted' for larger tables.

Similar issue was with defragmenting filesystem. Earlier the only
possibility to defragment filesystem was:
- backup files (sometimes tar cvf was sufficient);
- delete files;
- restore files.
Backup on disk required as much space as used by the files in the old
disk location.
Later on disk defragmenters were discovered such as Disk Speed and others.

My proposal is to have such a tool for database defragmenting. It
would work using plenty of small transactions (transactions are
necessary - in case replication is used).
It could be dedicated SQL statement or command line tool+background
process.
Example SQL would be: ALTER INDEX i1 TO CLUSTER NOTRANSACTION
or ALTER INDEX i1 TO CLUSTER NOTRANSACTION COMMITEVERY 100 RECORDS

Such a statement is not quite transactional because interrupting it
causes that part of the job is already done.
It sounds like a heresy but is not: data content is not affected, only
physical disk placement. Also data integrity is ensured (data are moving
in small transactional batches).
If this is unacceptable in SQL then let it be command line tool
equivalent. There is already a case: dbload utility when interrupted
leaves table partially loaded.



Robert

robsosno napisa³(a):
> david@smooth1.co.uk napisa³(a):
>
>> I have started a document for IDS Feature Requests at
>>
>> http://www.smooth1.co.uk/pages/smoot...com/IDS_FR.txt
>>
>> Can people add to this? Please post (email address, date, request) to
>> this topic.
>>
>> (Using Google Groups I cannot see peoples full email address!)
>>
>> David.
>>
....
>
> 2. Time based periodic fragmentation
> Is this possible?
....
> It would be something like this:
> FRAGMENT BY EXPRESION SWITCH (month(tr_date), frag1, .. frag12)
> or
> FRAGMENT BY EXPRESION SWITCH (month(tr_date), frag1, .. frag12)
> REMAINDER IN bad_frag
>
I need to add some further explanation.
SQLs like these:
a) select * from transaction where tr_date>='1/1/2005' and
tr_date<='31/03/2005'
b) select * from transaction where tr_date>='1/1/2005' and
tr_date<='31/03/2006'

cannot use fragment elimination based on month. This is because query
a)is using frag1, frag2, frag3 while query b) usues all fragments
although month(tr_date) are the same.

I need fragment elimination for the query
c) select * from transaction where tr_date>='1/1/2005' and
tr_date<='31/03/2005' and month(tr_date)>=1 and month(tr_date)<=3
This can easily be done by a developer.

In theory there is no need to additional syntax for this. It is just
because fragmentation scheme could be re-written as:
FRAGMENT BY EXPRESSION month(tr_date=1) in frag1, month(tr_date) in
frag2, ...

In practice this is too complex for SQL parser. SWITCH syntax is only
for purposes to make it easier to discover that fragment can be eliminated.

Robert

Roger wrote:
> robsosno napisa³(a):
>
>> david@smooth1.co.uk napisa³(a):
>>
>>> I have started a document for IDS Feature Requests at
>>>
>>> http://www.smooth1.co.uk/pages/smoot...com/IDS_FR.txt
>>>
>>> Can people add to this? Please post (email address, date, request) to
>>> this topic.
>>>
>>> (Using Google Groups I cannot see peoples full email address!)
>>>
>>> David.
>>>
> ...
>
>>
>> 2. Time based periodic fragmentation
>> Is this possible?
>
> ...
>
>> It would be something like this:
>> FRAGMENT BY EXPRESION SWITCH (month(tr_date), frag1, .. frag12)
>> or
>> FRAGMENT BY EXPRESION SWITCH (month(tr_date), frag1, .. frag12)
>> REMAINDER IN bad_frag
>>
> I need to add some further explanation.
> SQLs like these:
> a) select * from transaction where tr_date>='1/1/2005' and
> tr_date<='31/03/2005'
> b) select * from transaction where tr_date>='1/1/2005' and
> tr_date<='31/03/2006'
>
> cannot use fragment elimination based on month. This is because query
> a)is using frag1, frag2, frag3 while query b) usues all fragments
> although month(tr_date) are the same.
>
> I need fragment elimination for the query
> c) select * from transaction where tr_date>='1/1/2005' and
> tr_date<='31/03/2005' and month(tr_date)>=1 and month(tr_date)<=3
> This can easily be done by a developer.
>
> In theory there is no need to additional syntax for this. It is just
> because fragmentation scheme could be re-written as:
> FRAGMENT BY EXPRESSION month(tr_date=1) in frag1, month(tr_date) in
> frag2, ...
>
> In practice this is too complex for SQL parser. SWITCH syntax is only
> for purposes to make it easier to discover that fragment can be eliminated.
Actually month() would be pretty tough because it's not monotonic.
Given fragments of year_to_months snipping of the days preserves
monotonicity and can hence be discovered by the system.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

david@smooth1.co.uk wrote:
> I have started a document for IDS Feature Requests at
>
> http://www.smooth1.co.uk/pages/smoot...com/IDS_FR.txt
>
> Can people add to this? Please post (email address, date, request) to
> this topic.
>
> (Using Google Groups I cannot see peoples full email address!)
>
> David.
>

I'd like to see the "WITH ... AS" clause for inline views.

Built-in functions such as UPPER could be made invariant, for functional
indices.

--
rh

Added.

Folks,

I still can't see peoples full email addresses in this crappy google
groups.

I want to present this list to IBM but I can't without proper email
addresss (and preferably
company names).

If we want this to be taken seriously I'm going to need help here!

I can't say "Richard Harnden <richard.harn...@lineone.net>" asked for
this as IBM will
say who? How can I contact them?

Sorry to pick on the person I am replying to here but this also applied
to

E.g.
Fernando Ortiz <for...@lacorona.com.mx>
robsosno <robso...@gmail.com>
DL Redden <redde...@yahoo.com>
"Andrew Ford" <andrewf...@austin.rr.com>
"Redden96" <Redde...@yahoo.com> (same as above??).

david@smooth1.co.uk wrote:
> Added.
>
> Folks,
>
> I still can't see peoples full email addresses in this crappy google
> groups.

Then use a proper news-reader to read usenet.

Unless, that is, your PHB is scared that you'll immediatly subscribe to
alt.binaries.obnoxio

--
rh