Re: IDS New Feature Requests

david@smooth1.co.uk napisaďż˝(a): > I have started a document for IDS Feature Requests at > > http://www.smooth1.co.uk/pages/smooth1_my-bulldog_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/smooth1_my-bulldog_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/smooth1_my-bulldog_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