RE: thank you IBM
> -----Original Message----- > From: Madison Pruet [mailto:mpruet@comcast.net] > > > "rkusenet" <rkusenet@sympatico.ca> wrote in message > news:2iu0r7Fr1n5fU1@uni-berlin.de... > > > > "Hamilton, Jerry" <hamiltoj@fleishman.com> wrote \\ > > > > > > DB2 is process based architecture. Informix is thread based. Db2 has no > > concept of checkpoint. A page can not be modified more than once, before > > it is written to disk. How smart :-). > > Are you sure about that? It was my understanding that DB2 didn't use > checkpoints because it used a trickle flush technique which worked similar > to the way that the IDS fuzzy checkpoints work. I know that the way that > data pages and index pages are logged and structured on DB2 are such that > it would be fairly easy to not need to do periodic checkpoints, use a > physical log, or go through physical recovery. This is a fragment from DB2 "Administration Guide: Performance" ....Specifically, a "dirty" page is forced to disk at the following times: - When another agent chooses it as a victim. ....... Can You imagine, what additional data/index write activity by this approach? In most OLTP systems, many parallel sessions write records (e.g. transaction records) into the same pages... In out system, under normal OLTP load the write caching is at 95%. That is, under DB2, the disk write activity would be up to 20 times bigger. Though, this behavior can be partially compensated by using 'smart' disk array with write-back cache instead of dumb disks. > > Also, I don't really see why we would want to introduce the checkpoint > concept in DB2. Especially since one of the more common questions I'm > asked about IDS is how to avoid long checkpoint times. ;-0 > If an introduction of real (rather then fuzzy- or soft-) checkpoints is the only way to get rid of extra dirty page flushes - I prefer checkpoints. Frankly speaking, I always use 'NOFUZZYCKPT 1' in my 'onconfig' - just because I want to have control over the database recovery time > > There are too many differences between them to merge. There are several aspects of DB2 that make porting of Informix applications to DB2 extremely difficult: 1. IDS and DB2 have different style of transactions (implicit ANSI-style transactions in DB2 vs. explicit BIGIN-COMMIT) - though, in most cases, application transaction logic (with respect to transaction rollback point) shouldn't change if all BIGIN WORK's are changed to COMMIT's with ANSI-style transactions 2. DB2 doesn't support implicit typecasting. That means, that most SQL and SPL code originally written for Informix should be re-written 3. DB2 has absolutely different SPL exception handling; I can't imagine that Informix-style exception handling can ever be added to DB2. May be, some automation of the exception handling conversion can be added to the 'Migration Toolkit' in the future 4. There is no support in DB2 for the declaration of SPL variable by the database column type (like: DEFINE my_var LIKE my_table.my_col) Though, this feature (automatic conversion of variable definitions by column type to hard-coded types) can be easily added to the 'Migration Toolkit' 5. There is no support for collection data types in DB2 (both for column definitions and SPL variable definitions) Unfortunately, we widely use MULTISET in our applications... 6. It really makes sense to introduce NVL in DB2 (along with existing 'coalesce') to ease portability Other features of DB2 (no HDR, no table fragmentation, cumbersome upgrade, impossibility to specify the index physical location at index creation - it should be specified at table creation instead) are annoying, but they do not influence application portability from IDS to DB2 Alexey Sonkin sending to informix-list
"Alexey Sonkin" <alexeis@grandvirtual.com> wrote in message news:cai8sl$11k$1@news.xmission.com... > > > > > -----Original Message----- > > From: Madison Pruet [mailto:mpruet@comcast.net] > > > > > > "rkusenet" <rkusenet@sympatico.ca> wrote in message > > news:2iu0r7Fr1n5fU1@uni-berlin.de... > > > > > > "Hamilton, Jerry" <hamiltoj@fleishman.com> wrote \\ > > > > > > > > > DB2 is process based architecture. Informix is thread based. Db2 has no > > > concept of checkpoint. A page can not be modified more than once, before > > > it is written to disk. How smart :-). > > > > Are you sure about that? It was my understanding that DB2 didn't use > > checkpoints because it used a trickle flush technique which worked similar > > to the way that the IDS fuzzy checkpoints work. I know that the way that > > data pages and index pages are logged and structured on DB2 are such that > > it would be fairly easy to not need to do periodic checkpoints, use a > > physical log, or go through physical recovery. > > This is a fragment from DB2 "Administration Guide: Performance" > ....Specifically, a "dirty" page is forced to disk at the > following times: > - When another agent chooses it as a victim. > ....... > > Can You imagine, what additional data/index write activity > by this approach? In most OLTP systems, many parallel > sessions write records (e.g. transaction records) into the same pages... > In out system, under normal OLTP load the write caching is at 95%. > That is, under DB2, the disk write activity would be up to 20 times bigger. > Though, this behavior can be partially compensated by using > 'smart' disk array with write-back cache instead of dumb disks. What is described by the 'victim' in the Admin Guide is the same as a forground write in IDS. This does not mean that every dirty page must be flushed to disk before it can be used again. It means that if the buffer pool is so tight that there are no clean buffers available, then a page will be forced to disk, just as with the IDS forground write. Normally the dirty buffers will be flushed to disk by the page cleaners when the parameter of chngpgs_thresh is reached, or if the distance between the oldest dirty page and the current log position reaches a set percentage as defined by the 'softmax' parameter. Therefor, recovery time is based on a set percentage of the total log file size. If you want to minimize the recovery time, you can set 'softmax' to 1 and thus ensure that the recovery process will only need to process 1% of the logs. t
Alexey Sonkin wrote: > There are several aspects of DB2 that make porting > of Informix applications to DB2 extremely difficult: > > 1. IDS and DB2 have different style of transactions > (implicit ANSI-style transactions in DB2 vs. explicit BIGIN-COMMIT) > - though, in most cases, application > transaction logic (with respect to transaction rollback > point) shouldn't change if all BIGIN WORK's are changed > to COMMIT's with ANSI-style transactions Not quite. Conceptually this is actually not that hard. The devil, as always lies in the detail, such as dealing with cursors in "autocommit mode". From an app side DB2 supports a toggle between auto-commit and explicit commit. The trouble maker are stored procedures since the behaviour can get inherited from the up to the procedure. We can thank TPC-C for what's there sofar (Benchmarks do have their uses). > > 2. DB2 doesn't support implicit typecasting. That means, that > most SQL and SPL code originally written for Informix should > be re-written That is partially correct. DB2 supports function overloading and promotion chains. What's needed are more fucntiosn which allow crossing the type bounaries. No rocket science. I see this happening if for no other reason than Informix customers. > 3. DB2 has absolutely different SPL exception handling; > I can't imagine that Informix-style exception handling > can ever be added to DB2. > May be, some automation of the exception handling conversion > can be added to the 'Migration Toolkit' in the future Now that's teh part I don't get (yet). Looking at the docs SQL/PSM exception handling looks like a superset of SPL errro handling. What am I missing. Syntax differences are indeed best handled through the MTK. > 4. There is no support in DB2 for the declaration of SPL > variable by the database column type (like: > DEFINE my_var LIKE my_table.my_col) > Though, this feature (automatic conversion of variable > definitions by column type to hard-coded types) > can be easily added to the 'Migration Toolkit' Both, it can be easily added to the MTK as well as to SQL PL. I don't see this as principle hurdle. It's coding that's all. It's kind of sad to have to add it because IMHO user defined distinct types are a cleaner model, but the market has decided differently. (same as with the type-casting). > 5. There is no support for collection data types in DB2 > (both for column definitions and SPL variable definitions) > Unfortunately, we widely use MULTISET in our applications... I get mixed messages on the OR extension usages. In general the Illustra side doesn't seem to have caught on all that widely. While the need for some form of sets is obvious in the procedural world (arguments, local variables in routines). The column types are less clear to me. > 6. It really makes sense to introduce NVL in DB2 > (along with existing 'coalesce') to ease portability It took me 35min to get DECODE to work on DB2. NVL (being a 2 argument version of COALESCE) should be equally easy. In development speak we call that "syntactic sugar". What about other features such as using brackets for substring(), using double quotes for constants, update from vs MERGE? Doe anyone use subqueries in the ON-condition of a join? As you see I'm getting down and dirty in these SQL dialect hings. Giving me feedback will help us straighten out the priorities. Cheers Serge -- Serge Rielau DB2 SQL Compiler Development IBM Toronto Lab