IDS New Feature Requests - 2007

Database/SQL feature requests 1. The ability to create foreign key constraints to tables in another database on the same instance. 2. Same as number 1, but with databases on a different instance.
create primary keys and foreighn keys in paralel if not done so already; allow dbspaces to be readonly so really backup once;
Allow inplace alter table for varchar columns!!!
This concept that I'll explain is something that would be helpful for testing of applications. For each test we want to start with the database in a known state. Currently we either have to do a dbimport, or a lot of LOAD sqls. What I want is something that allows us to run tests without that work necessary at the beginning of each test. What I have in mind is either 1) a mode so that when you disconnect from a database all your changes are rolledback, or 2)a mode so that when you connect to a database your changes are not written to the database except for as viewed in your session, or 3) the ability to create a 'known point' in the database and then allow rollbacks to that point.
Copy data from one table to another very quickly.
Increase the number of cleaner limit from 128 to 512 ( same as LRU) support for GK and bitmap index remove limitation of 2GB on unload/load statement enhance fragmentation include -
1. resource governor which limits the amount of memory AND cpu a session (or user) can consume 2. a way to prioritize sessions; example: end-of-month sessions get top priority, in regards to engine resources
- parallelized oncheck, dbexport, dbimport - ontape Point in Time restore - onspaces: resize chunk size online - onspaces: rename chunk/dbspace online - larger pagesizes (32, 64, 128, 256kB) - more than 255 rows/slots per data/index page (to use larger pagesizes with small rowsizes)
I would like have the ability to have more specific indexes, ones with multiple attriburtes (e.g idx(site,acct)) be able to be leveraged as the underlying index to a FK (to site in this case). Now, I need two indexes, one on site/acct and one on site.
I would like to stress the REORG capability - critical for our operations. We currently survive on DBReorg.
It would make my life MUCH easier if an exclusive lock would actually grant me total control (as a DBA) over a table
Automatic table reorg which keeps tables in shape (ex. extent count)
On online reorg of a table also add option in same space or differant space.
onstat command that shows locks (and type) on table by name and the sessions that hold them
Add an "index advisor" like DB2's "db2advis" to evaluate's and recommend's index creation/dropping for SQL statements analized.
Improvements to view performance (all, not just ANSI)
RE: CREATE OR REPLACE TABLE x I have seen this done in other database SQL as - IF EXISTS DROP <object> x; This form would be more versatile in our environment. I may only want to drop the object without creating it again. -- On the same line how about: Current: alter table x add(f char(1)); New: alter tabke x add or modify(f char(1));
improving the ISA by adding control to the table locks ,current sql running some statistics
Notification in online log when an application fails due to lock on table also notification online when rollback occurs
be able to log/identify slow queries. be able to log/identify sequential scan queries or queries w/o index(s). improve DBA tool.
Be careful with giving the DBA too much control of memory/locks in sessions/instances: with the positive impact of IBM moving IDS to more self-healing, self-correcting, and self-tuning, anything CONTRARY to that must be validated by input from a large set of customers!
I would like something like "priority" to a session. (like batch jobs that should get less resources (CPU) than online sessions). Basically more enhanced "user management".
Control size of first extent of a new fragment. Ability to meaningfully cluster database across nodes.
1. I would like to see timestamps on system tables so that I can see when a system object ie table or view or stored procedures was created and last modified and by whom. 2. I would like to see conditional statements in scripts so that I can write statements like: if ! exists table xyz then create table xyz ... end if or if ! exists xyz.column then alter table xyz add column varchar(20) not null; end if This means that for example running a script twice by mistake does not cause problems and using one script to update multiple instances of a database which are out of sync. By script I mean an sql file which is fed into dbaccess. 3. I would like to see improvements to dbaccess to have a more friendly user interface while still being a command line utility.
Allow specification of a fillfactor when btree cleaner compresses indexes. (or have the btree cleaner use the setting you gave the original index).
Identification of the application that created a long transaction.
Ability to specify the amount of available memory to the informix process instead of specifying how much should be allocated to locks/buffers/virtual portion.
Ability to change onconfig parameters, online.
Add ALTER VIEW or CREATE OR REPLACE VIEW capability. This is essential to maintain access permissions to views that have their definition modified.
More visibility into what's going on inside the server using tools like onstat
Add ability to create triggers on syscatalog tables
Create queue of locks between session. When cursor or SQL statement comes to lock on row, the lock is inserted to an internal queue, session don't wait that lock. It continue processing another row. During transaction is commiting by another session it hand down lock to the first session from queue (insted of release). Session which skipped row which was locked will start processing that row ASAP after lock will be passed for it.
not crashing hard coded with plog overflow ; (automatically) re-online a chunk that went down only because of a temporarily missing sym link or disk as long as data consistency is not at stake; improved rollback performance
- An onstat lock chain report where you can see the lock dependencies: Given a session waiting on a lock, I want to see the session owner of that lock, and the object locked. - Be able to force defaults behaviours (lock modes, optimization level, isolation levels, etc.) that cannot be overriden at session level.
1. Add ability to forcefully drop XA transaction independently of state of its state (LONGTX problem). 2. Add support for logging of the sql statements that access one or more of the given table/tables in specified time range; e.g. log all statements accessing table "foo" between 11am and 2pm. Logged statements would be most useful in source form, with bound values as well as executing user name included. Such feature would help a lot in looking for causes of slowdowns in production environments. 3. Logging date, time, user (and maybe operation) to separate log file, NOT online.log when a database structure is changed (object is dropped or modified). It seems to be a bit annoying to have info, related to quite normal activity of the one particular database, placed in a server-wide online.log. Separate log files for every database seems to be a better idea. 4. Add ability to extract all useful data (session, user, connection, running/last executed statements, locks etc.) about specified user or session. 5. Add info about statement's execution time to onstat's output. 6. Add possibility to automatically dump some info to the file in case of IDS server overload. Such info could contain running statements with timings as well as users executing them. It would be nice to have statement list sorted to have those causing most load at the beginning.
dbaccess: should have command history and tabcompletion for objects. e.g: select * from <table> where <tab> => evaluates the columns of table and suggests them with the second tab-stroke (also analog readline-library). Result presentation of dbaccess <db> - by default in a "more"-like style (or less on linux)
Provide greater smi table access (eg 1. showing table access by user and 2. logical log usage).
In SQL, the ability to select a column that is related to aggregate. For example: SELECT a, b, max(c) from x group by a, b I would like to get the value of column d that corresponds to the max(c). If I add d in the SELECT, it would alter the result.
When in a non-ANSI database, the ability to make a remote query to an ANSI database. When in an ANSI database, the ability ro make a remote query to a non-ANSI database. Ability to support query of index parts from remote sysindexes. "select part1, part2, ... from db@server:sysindexes where ...". Maybe allow the remote server to evaluate the UDT and return the results to the engine that the session is connected to rather than having the engine the session is connected to try and directly evaluate the UDT which may or may not be the same "version" as the UDT on the remote server. Relational operators "INTERSECT" and "MINUS" similar to "UNION". This was in the Informix pre-SQL product (3.3). Ability to specify the tape drive on the command line to ontape rather than having to change TAPEDEV and LTAPEDEV.
All of the functionality, such as conversion to UTF-8 needs to be as seemless as possible, perhaps on a table by table basis. In a 24/7 shop, a long outage is not feasible to make these changes. Also, the ability to grant database permissions to Unix groups and then users' ids that are part of that group get those permissions, much like DB2 does, would also be helpful.
a new field in both systables and syscolumns to hold a label type field describing the table name and column name... (spaces allowed)
1. Have reaching the SHMTOTAL value for an instance result in not trying to increase it, instead of having it result in the instance "alarming itself" into an assert failure as it continues to attempt to dynamically allocate more memory. Otherwise, rename the parameter to "CRASH_INSTANCE_WHEN_SHMTOTAL_EXCEEDED". 2. Improve the usefulness of the online log messages by noticing when the engine is sending hundreds or thousands of the same message per minute and, instead, after enough messages to get the detail, post summaries like "2053 messages like "x" since <datetime>. 3. Improve ability to manage memory by consolidating tiny numbers of used blocks per dynamically allocated virtual shared memory segment so the bulk can be dropped. For example, I'm currently looking at 9 dynamically added segments most with less 200 blocks used (out of 16000) and none of them will be freed up by onmode -F. So it's great to have the dynamic increase but it would be even more great to be able to set an onconfig parameter or an onmode command, beyond onmode -F, to dynamically shrink memory after the temporary, desirable, spike increase. 4. Have HPL invoke PDQ (when the environment variable is set) when it enables constraints and indexes after an "express mode" lode (if it doesn't do that already--I haven't confirmed that it does and it seems that enabling indexes and constraints is quite slow). 5. Allow designation of the dbspace for the implied indexes created by constraints. 6. Add an onstat report for percentage of a tablespace unusable due to deleted rows, for easier access to knowing that a table re-org is desirable.
1. oncheck to be able to check on functional indexes as well as plain ones. (I realise this may be tough in some cases....) 64 bit build approved spatial with ESRI would be useful for us. Ability to check and align table/schema structure (as in selection list), across DB engines, would be *VERY* useful for us, as we have several shadow DB copies in use. Spatial/smart object between engines would also be very useful too. I know this will be very hard to do though.
Reorganize/move of TBLspace extents for chunks that only contain TBLspace extents with the result that one can drop a chunk that contains no data, but only the TBLspace extent(s).
(1) a combined insert/update command (aka "upsert") (2) more string functions (such as index and regex)
Essential: - Enhance the sysmaster tables of the SQL trace facility to provide a relation between the contents of syssqltrace_iter and the actual table that is used in the respective step. As it is now it is useless. - Provide the parameter values that are collected in SQL trace mode "high" in a sysmaster table. - improve "onstat -g his" to filter for table names, run times, buffer reads ...
Identify critical dbspaces with flag such as "is critical" like "is root". Critical applies to physical log and logical log and is known to "onbar".
1. Currently with reoptimization option is only available for open cursor. It should also be available for execute statement if it is a singleton select we are executing. We should not have to covert it to cursor just to use with reoptimization. 2. We would like to see if we can have an option to have with reoptmization available for the whole site even if a statement had that issued or not. The option to apply this to the whole engine for every query without modifying our programs. 3. Whenever a deadlock is detected, dump the list of all processes that were invovled in deadlock and what queries they were running, and what locks they had in place, so it can be used to debug how to avoid deadlocks for the future. As of now only thing we know is that deadlock has happened. We need more details. 4. We should have capability where we can enable tracking on an index, so if this tracking is enabled, informix should start tracking which process and which query is hitting that index. I understand that there will be a performance impact of this. But I believe it is wirth it some time to research the issue. 5. Current onperf utility to present graphical picture of performance dynamics is not good enough. It is very poor. It is too slow. We need graphical view of the performance of the system similar to glance for the database side and it should be fast too. Onstat command line interface is too rudimentary and one has to run multiple commands to get a useful picture. I should be able to run a single command to look at all the locks on the system in a meanful presentable way which makes sense from Application perspective.
Capability to move a dbspace with serveral chunks into a new single larger chunk. That is moving pages internally between old chunks to new one. It's very complicated to configure and manage systems with tons of chunks. It could be nice for customers which come from versions prior of supporting at max of 2 GB per chunk.
Ability to control all environment variables for user sessions - memory, buffer usage and temp space use
Add ability to alter/drop/disable trigger without setting exclusive lock on a target table
what about document the content of the field, tables or an index.
Could have an ability to preserve the commands about defintions of dbspaces and chunks, in a config file or table ? For example: in a file: link volume raw/chk size dbspacename parameters ol_root01 lh_root01 vg00 512M rootdbs ol_sblb01 lh_sblb01 vg00 2G sblobdbs -g 2 -Df "LOGGING=ON" Best Regards.
Ability to store large objects (pics, text, sound) on the fly...without having to create special storage areas.
functions like split, allow to accede to a lvarchar type field from one database to other database
The ability to add performance to the same database/tables by adding server nodes.
On configuration advisory application for suggesting ONCONFIG parameters, please also include OS KERNEL PARAMETERS suggestion. Thanks!
We'd like to see usage of more than one CPU on multi CPU machines when doing LRU scans during checkpoint and during other seaches/scans, which can run in parallel. We need faster creation of constraints.
1) To be able to drop/disable/create a trigger without generating "Table was altered" error messages. 2) Fix the bug that forces us to use the "drop distributions" clause to prevent IDS behaviour of going through a secuential pass. It a bug that has been dragging on from release to release.
Ability to add user-defined ALARMPROGRAM calls (e.g. class codes 100 onwards could be application-specific)
Replace into' type SQL statement. Where if a record does not exist, it is inserted. If it does exist than update it.
Database level restore (PIT included) onstat -g opn with user info onstat -g tpf with sid info
- allow subselect with "SELECT FIRST" Clause
Alter 1st extent size without dropping table
Allow coexistence of foreign key constraints and triggers on the same table
Allow for recasting of system catalogues to overcome badly fragmented tables, eg: sysprocplan.
Improve blob-related performance, load/delete rows
1. dbschema can get the real extent number, and assign a value , something like "extent * 1.2 " 2. prohibit user such as delphi BDE tool to view all the DB's tables through the client-sdk. ( not grant priviledge from ids server)
Ability to initialize IDS with a specific page size. eg. 4K page size on a Sun Server.
make it pssible to create a pk, uk or fk constraint (even in create table), but with the ability to advise where the implicit created index has to go and which name is given to it. Currently I have to create the Index first and then create the constraint.
resize a chunk