Re: [U2] Migration
I think the performance kick you see in SQL is more related to how multiple rows are read in a database page from disk... I could be wrong. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of fft2...@aol.com Sent: Thursday, December 23, 2010 11:46 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Migration But back to reality, I don't think SQL works this way anyway. The perception that it sorts much faster is probably related more closely to the horsepower behind the scenes. Pick systems tend to be installed on slower systems because they are so efficient and most users are cheap with their database, and expensive with their graphics. So they install the SQL type databases on speedy machines. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
LOL In my mind - there would be a operator map tool here - I don't think the tool could be self aware enough to figure out every possible combination of everything. It could certainly guess 80% correctly and be corrected on the remaining 20% on suggesting maps. I see this with data going from MV to SQL - sometimes the dictionaries are just wrong... it's easier to adjust using the map... (in particular if they're not your dictionaries to adjust) I would imagine an adjustment could be made to the maps to increase MV performance - just like you would in SQL when porting MV data there. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Robert Houben Sent: Friday, December 24, 2010 1:41 AM To: U2 Users List Subject: Re: [U2] Migration Oh, one more point. What if your SQL environment had NOT defined a primary key for APPOINTMENTS, but had multiple indexes, one of which happened to have CUSTOMERNO, APPTDATE, APPTTIME and APPTTYPE. How would you figure out what to use as the item-id of the PICK file? What if you had a SQL table that actually did not have a set of fields that guaranteed a unique value? Then you have NOTHING to create an item-id from! I have to stop this, it will consume me! :o But the list goes on. Oh the humanity! ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
[AD] If I don't put this here, someone might complain... Our tools for going MV to SQL do exactly that. We have a mapping wizard that analyzes the dictionaries and the data. Depending on how good the dictionaries are, we get close to 80% right first try. Then you get to test and tweak, all in a graphical environment, until you have it 100% right. The tool lets you even decide what %age of bad data you'll ignore. It's not uncommon to have a handful of bad records in a big file. Set the tolerance to 1% and if you have a dictionary that says attribute 2 is a date, provided less than 1 in 100 items have something that's not a valid date (you can set the range of values that work for your system, too) we'll call it a date and use that dictionary for the field name. We also have tools that let you identify bad records so you can actually find and fix them. The map is ultimately stored in the file's dictionary. I think we've gotten off-topic, again, though. The original question was about going the other way. Going SQL to MV, Item-id selection is likely to be the biggest hurdle. You do not need to set a primary key in SQL Server. Even if you do, you may have a unique index in SQL which would make a better candidate for the item-id. A tool to migrate can make a best first guess, but it will sometimes get it wrong. At some point, someone familiar with the application, with an architectural mindset, is going to have to look at the output and probably tweak the results. I just don't see a substitute for this. On the other hand, if you just get the data over, someone can convert it locally to whatever format they ultimately want. Our tools typically exist to help get the data to where the application programmers are, and they take it from there. Then the application guys figure out what to do with it from there. If you have to repeat this from time to time, you use a staging approach; you pass the raw converted data to a temporary file, then the application people process this file and convert it to their ultimate format. So, you get to choose between doing it all on the first pass, or getting the data over and letting someone at the target end (in this case, MV) complete the transformation. You are choosing between E - TL and ET - L. Where T happens is often driven by where the programming muscle in the project resides. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Marc Harbeson Sent: Tuesday, December 28, 2010 8:13 AM To: 'U2 Users List' Subject: Re: [U2] Migration LOL In my mind - there would be a operator map tool here - I don't think the tool could be self aware enough to figure out every possible combination of everything. It could certainly guess 80% correctly and be corrected on the remaining 20% on suggesting maps. I see this with data going from MV to SQL - sometimes the dictionaries are just wrong... it's easier to adjust using the map... (in particular if they're not your dictionaries to adjust) I would imagine an adjustment could be made to the maps to increase MV performance - just like you would in SQL when porting MV data there. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Robert Houben Sent: Friday, December 24, 2010 1:41 AM To: U2 Users List Subject: Re: [U2] Migration Oh, one more point. What if your SQL environment had NOT defined a primary key for APPOINTMENTS, but had multiple indexes, one of which happened to have CUSTOMERNO, APPTDATE, APPTTIME and APPTTYPE. How would you figure out what to use as the item-id of the PICK file? What if you had a SQL table that actually did not have a set of fields that guaranteed a unique value? Then you have NOTHING to create an item-id from! I have to stop this, it will consume me! :o But the list goes on. Oh the humanity! ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration (OT)
On 25/12/10 04:01, Dawn Wolthuis wrote: Oh wow, it sure is hard not to jump in on this one, but it's Christmas Eve so I will render quick opinion and hope that the conversation is still going on when I really have a chance to respond. A couple of opinions -- 1. Were it not for the standardization of SQL, a majority of BI would be more easily accomplished using data modeled for and stored in an MV database over a SQL DBMS. 2. The MV model is more the future than is the legacy SQL DBMS data model. Thinking that the RDBMS model has trumped the MV model makes sense on the one hand, but the RDBMS model is now on the wane while the MV model is part of the group of data models that will wax again, some under the category NoSQL. So, were I to write new software (hey, I am working with a team that is doing just that), I would choose one of the non-RDBMS models (Ok, I chose Pick/MV) rather than the old-fashioned, restrictive, and so-last-century SQL DBMS tools. I did choose a database with the fastest SQL implementation against the MV model, however. Given that for some applications SaaS software will replace software deployed by other means, user organizations will care a lot less about whether the DBMS is Oracle or some other big dog. I've very recently heard of Date's 3rd Manifesto or how to force an object database into a relational mould (my paraphrase :-) When are the relational guys going to learn that the reason object doesn't fit relational is that relational is BROKE! And just to keep you in the loop on something else, look for Wol in the new contributors to LibreOffice :-) I haven't actually done an awful lot (it's easy to get a lot of commits while not doing much ...) but they need a new database :-) so guess what - I'm planning to write nf2engine for them. The problem is family commitments as my wife now has Parkinsons. And I've learnt some more about why relational is broken by discussing things with other people on the sqlite list. We'll see. At some point soon relational will implode, the question is when. Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration (OT)
The point of relational as I understand it, was to try to create a database where *calculations* were not required at all. The query language could just start spitting out results immediately without the need for any interim work space. Obviously all you have to do is add a SORT to this and you need interim workspace. Of course if you then add just a simple index to the field by which you are sorting, you again need no interim workspace, although you do introduce a truckload of disk thrashing. The issue, to my mind, isn't that Nested Relational has a string that we interpret as multiple answers, in a single field, but rather that we by nature expect to be able to *break* these out on separate lines, repeating the fields that we see as single valued on each of those multiple lines. That's the reason why first-normal form devotees see MV as different. W In a message dated 12/27/2010 6:18:40 A.M. Pacific Standard Time, antli...@youngman.org.uk writes: On 25/12/10 04:01, Dawn Wolthuis wrote: Oh wow, it sure is hard not to jump in on this one, but it's Christmas Eve so I will render quick opinion and hope that the conversation is still going on when I really have a chance to respond. A couple of opinions -- 1. Were it not for the standardization of SQL, a majority of BI would be more easily accomplished using data modeled for and stored in an MV database over a SQL DBMS. 2. The MV model is more the future than is the legacy SQL DBMS data model. Thinking that the RDBMS model has trumped the MV model makes sense on the one hand, but the RDBMS model is now on the wane while the MV model is part of the group of data models that will wax again, some under the category NoSQL. So, were I to write new software (hey, I am working with a team that is doing just that), I would choose one of the non-RDBMS models (Ok, I chose Pick/MV) rather than the old-fashioned, restrictive, and so-last-century SQL DBMS tools. I did choose a database with the fastest SQL implementation against the MV model, however. Given that for some applications SaaS software will replace software deployed by other means, user organizations will care a lot less about whether the DBMS is Oracle or some other big dog. I've very recently heard of Date's 3rd Manifesto or how to force an object database into a relational mould (my paraphrase :-) When are the relational guys going to learn that the reason object doesn't fit relational is that relational is BROKE! And just to keep you in the loop on something else, look for Wol in the new contributors to LibreOffice :-) I haven't actually done an awful lot (it's easy to get a lot of commits while not doing much ...) but they need a new database :-) so guess what - I'm planning to write nf2engine for them. The problem is family commitments as my wife now has Parkinsons. And I've learnt some more about why relational is broken by discussing things with other people on the sqlite list. We'll see. At some point soon relational will implode, the question is when. Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
On 24/12/10 15:50, Robert Houben wrote: SQL will beat MV every time when you sort fields that are indexed. Huh? Ime (UniVerse), that's wrong. Indexes are b-trees, which you can walk, and the contents of the index are sorted. afaik you would have been right about PI, but that's long dead. Dunno about UniData, but UV is a lot of sites where MV will equal SQL ... :-) For direct reads, MV seems to have a slight advantage. Inserts and updates that affect indexed fields are slower in SQL (inserts are painfully slow if you fail to size your SQL table well, but try inserting millions of records into a file with a modulo of 1...) Been there, done that. But that's why most places use dynamic files nowadays. :-) Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
Should have clarified when you sort *multiple* fields that are indexed. I still haven't heard anyone tell me that either UV or UD now support more than one indexed field. Let me know if this has changed... -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Sunday, December 26, 2010 4:33 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Migration On 24/12/10 15:50, Robert Houben wrote: SQL will beat MV every time when you sort fields that are indexed. Huh? Ime (UniVerse), that's wrong. Indexes are b-trees, which you can walk, and the contents of the index are sorted. afaik you would have been right about PI, but that's long dead. Dunno about UniData, but UV is a lot of sites where MV will equal SQL ... :-) For direct reads, MV seems to have a slight advantage. Inserts and updates that affect indexed fields are slower in SQL (inserts are painfully slow if you fail to size your SQL table well, but try inserting millions of records into a file with a modulo of 1...) Been there, done that. But that's why most places use dynamic files nowadays. :-) Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
I should also clarify that we have a lot of customers we support on mvBase, mvEnterprise, D3 and lots of other platforms, and yes, we do have PI Open customers... I recognize that the topic here is U2, but even there, we have customers who use our products running on Universe 5 (don't ask, we just do...) So, I find that I often have to assume a lowest common denominator when building a reusable solution. I may not always be aware of the current state of a particular platform. I'd love to be corrected if my understanding of limitations is out-of-date! Last I knew, if you wanted to sort an MV file by more than one field, regardless of how many indexes you had, you got to pick one of them, and you would settle for brute force for the others. This was true, last I knew, of EVERY MV platform I knew of that had indexes. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Robert Houben Sent: Sunday, December 26, 2010 4:42 PM To: U2 Users List Subject: Re: [U2] Migration Should have clarified when you sort *multiple* fields that are indexed. I still haven't heard anyone tell me that either UV or UD now support more than one indexed field. Let me know if this has changed... -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Sunday, December 26, 2010 4:33 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Migration On 24/12/10 15:50, Robert Houben wrote: SQL will beat MV every time when you sort fields that are indexed. Huh? Ime (UniVerse), that's wrong. Indexes are b-trees, which you can walk, and the contents of the index are sorted. afaik you would have been right about PI, but that's long dead. Dunno about UniData, but UV is a lot of sites where MV will equal SQL ... :-) For direct reads, MV seems to have a slight advantage. Inserts and updates that affect indexed fields are slower in SQL (inserts are painfully slow if you fail to size your SQL table well, but try inserting millions of records into a file with a modulo of 1...) Been there, done that. But that's why most places use dynamic files nowadays. :-) Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
I've been lurking, following this thread, and I guess I have to stick my 2 cents in. I've worked with MV for 33 years, from Reality to jBASE, and one of the best features ever added was secondary indexes. Choices depend on the platform, the iron involved, size and structure of the database and how often you need a particular select, but this is an approach I have used with success. I like to use an index to cull the file down as much as possible, then use that select to drive the next index select, etc. (if the platform allows it and it's appropriate). Then, when the final list is culled down to the records I need, I use another index select to put the list in the sequence I want. The key can be constructed of multiple fields to get just the desired sequence. No brute force required. One advantage of multi-field keys is small nodes, which means more efficient index updating and selects. Now, you can't go crazy and index everything in sight, but you can design indexes to give you the maximum bang for your buck. At least, this approach has worked well for me. Like it, hate it, throw rocks at it, it's all OK by me. BTW, it's Sunday after Christmas - what are we doing thinking about work today? Merry Christmas and Happy New Year to all, Charlie Noah Charles W. Noah Associates cwn...@comcast.net The views and opinions expressed herein are my own (Charlie Noah) and do not necessarily reflect the views, positions or policies of any of my former, current or future employers, employees, clients, friends, enemies or anyone else who might take exception to them. On 12-26-2010 6:50 PM, Robert Houben wrote: I should also clarify that we have a lot of customers we support on mvBase, mvEnterprise, D3 and lots of other platforms, and yes, we do have PI Open customers... I recognize that the topic here is U2, but even there, we have customers who use our products running on Universe 5 (don't ask, we just do...) So, I find that I often have to assume a lowest common denominator when building a reusable solution. I may not always be aware of the current state of a particular platform. I'd love to be corrected if my understanding of limitations is out-of-date! Last I knew, if you wanted to sort an MV file by more than one field, regardless of how many indexes you had, you got to pick one of them, and you would settle for brute force for the others. This was true, last I knew, of EVERY MV platform I knew of that had indexes. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Robert Houben Sent: Sunday, December 26, 2010 4:42 PM To: U2 Users List Subject: Re: [U2] Migration Should have clarified when you sort *multiple* fields that are indexed. I still haven't heard anyone tell me that either UV or UD now support more than one indexed field. Let me know if this has changed... -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Sunday, December 26, 2010 4:33 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Migration On 24/12/10 15:50, Robert Houben wrote: SQL will beat MV every time when you sort fields that are indexed. Huh? Ime (UniVerse), that's wrong. Indexes are b-trees, which you can walk, and the contents of the index are sorted. afaik you would have been right about PI, but that's long dead. Dunno about UniData, but UV is a lot of sites where MV will equal SQL ... :-) For direct reads, MV seems to have a slight advantage. Inserts and updates that affect indexed fields are slower in SQL (inserts are painfully slow if you fail to size your SQL table well, but try inserting millions of records into a file with a modulo of 1...) Been there, done that. But that's why most places use dynamic files nowadays. :-) Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
I was answering while uploading family videos to YouTube! :) -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Charlie Noah Sent: Sunday, December 26, 2010 7:02 PM To: U2 Users List Subject: Re: [U2] Migration I've been lurking, following this thread, and I guess I have to stick my 2 cents in. I've worked with MV for 33 years, from Reality to jBASE, and one of the best features ever added was secondary indexes. Choices depend on the platform, the iron involved, size and structure of the database and how often you need a particular select, but this is an approach I have used with success. I like to use an index to cull the file down as much as possible, then use that select to drive the next index select, etc. (if the platform allows it and it's appropriate). Then, when the final list is culled down to the records I need, I use another index select to put the list in the sequence I want. The key can be constructed of multiple fields to get just the desired sequence. No brute force required. One advantage of multi-field keys is small nodes, which means more efficient index updating and selects. Now, you can't go crazy and index everything in sight, but you can design indexes to give you the maximum bang for your buck. At least, this approach has worked well for me. Like it, hate it, throw rocks at it, it's all OK by me. BTW, it's Sunday after Christmas - what are we doing thinking about work today? Merry Christmas and Happy New Year to all, Charlie Noah Charles W. Noah Associates cwn...@comcast.net The views and opinions expressed herein are my own (Charlie Noah) and do not necessarily reflect the views, positions or policies of any of my former, current or future employers, employees, clients, friends, enemies or anyone else who might take exception to them. On 12-26-2010 6:50 PM, Robert Houben wrote: I should also clarify that we have a lot of customers we support on mvBase, mvEnterprise, D3 and lots of other platforms, and yes, we do have PI Open customers... I recognize that the topic here is U2, but even there, we have customers who use our products running on Universe 5 (don't ask, we just do...) So, I find that I often have to assume a lowest common denominator when building a reusable solution. I may not always be aware of the current state of a particular platform. I'd love to be corrected if my understanding of limitations is out-of-date! Last I knew, if you wanted to sort an MV file by more than one field, regardless of how many indexes you had, you got to pick one of them, and you would settle for brute force for the others. This was true, last I knew, of EVERY MV platform I knew of that had indexes. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Robert Houben Sent: Sunday, December 26, 2010 4:42 PM To: U2 Users List Subject: Re: [U2] Migration Should have clarified when you sort *multiple* fields that are indexed. I still haven't heard anyone tell me that either UV or UD now support more than one indexed field. Let me know if this has changed... -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Sunday, December 26, 2010 4:33 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Migration On 24/12/10 15:50, Robert Houben wrote: SQL will beat MV every time when you sort fields that are indexed. Huh? Ime (UniVerse), that's wrong. Indexes are b-trees, which you can walk, and the contents of the index are sorted. afaik you would have been right about PI, but that's long dead. Dunno about UniData, but UV is a lot of sites where MV will equal SQL ... :-) For direct reads, MV seems to have a slight advantage. Inserts and updates that affect indexed fields are slower in SQL (inserts are painfully slow if you fail to size your SQL table well, but try inserting millions of records into a file with a modulo of 1...) Been there, done that. But that's why most places use dynamic files nowadays. :-) Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo
Re: [U2] Migration
I have one file that has 30 indexed fields, UV 10.3.something. I have noticed that, since switching from Linux to Windows, I can't rely upon the index being sorted though. Jerry On 12/26/2010 6:42 PM, Robert Houben wrote: Should have clarified when you sort *multiple* fields that are indexed. I still haven't heard anyone tell me that either UV or UD now support more than one indexed field. Let me know if this has changed... -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Sunday, December 26, 2010 4:33 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Migration On 24/12/10 15:50, Robert Houben wrote: SQL will beat MV every time when you sort fields that are indexed. Huh? Ime (UniVerse), that's wrong. Indexes are b-trees, which you can walk, and the contents of the index are sorted. afaik you would have been right about PI, but that's long dead. Dunno about UniData, but UV is a lot of sites where MV will equal SQL ... :-) For direct reads, MV seems to have a slight advantage. Inserts and updates that affect indexed fields are slower in SQL (inserts are painfully slow if you fail to size your SQL table well, but try inserting millions of records into a file with a modulo of 1...) Been there, done that. But that's why most places use dynamic files nowadays. :-) Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
Agreed, that this is how it is often done. But there is nothing that says that this is how it has to be done OR even how it should be done. These 2 files could be setup such that : The CUSTOMER file contains no such embedded 'cross reference' field for appointments The APPOINTMENTS file could be keyed as : @ID = CUSTOMERNO*APPTDATE*APPTTIME*APPTTYPE With ITYPE entries setup for CUSTOMERNO = @ID['*',1,1] APPTDATE = @ID['*',2,1] APPTTIME = @ID['*',3,1] APPTYPE = @ID['*',4,1] -- OR -- @ID = sequentially generated With data fields defined for CUSTOMERNO, APPTDATE, APPTTIME APPTYPE In both cases, indexed on CUSTOMERNO. I believe you will find the following 2 snippets pretty comparable performance wise : * I know there are other better (?) ways to do this * insert your own list traversal code appcnt=dcount(custrecappxref,@VM ) for appidx = 1 to appcnt appid = custrecappxref,appidx next execute 'SELECT APPOINTMENTS WITH CUSTOMERNO = ':squote(custno) loop while readnext appid repeat Dropping the xref field in the parent table would help immensely with file sizing as records would tend to be a lot closer in size to the average instead of having CUSTOMER records that have accumulate massive xref fields over time - this is a problem I see in systems all the time. This also gets around your problem of knowing what is related to what - you know that all keys are automatic indexes in sql server and anything that could be used for lookup/joining purposes should also have an explicit index in sql server else it would be a bottleneck in sql server as well. There is also nothing that says that a file's key value has to be based on anything related to the data in the record - some would argue that key values should NEVER be related to the data. The only reason to prefer the 1st method over the 2nd is for human readability which imo shouldn't be a concern anyway. The 2nd scheme also gets around the sql-mv field mapping, no defined relation, no key, non-unique key and key length max exceeded problems, and would definitely produce a much more even hash distribution. Just some thoughts, Gerry -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Robert Houben Sent: December 24, 2010 01:41 AM To: U2 Users List Subject: Re: [U2] Migration Oh, one more point. What if your SQL environment had NOT defined a primary key for APPOINTMENTS, but had multiple indexes, one of which happened to have CUSTOMERNO, APPTDATE, APPTTIME and APPTTYPE. How would you figure out what to use as the item-id of the PICK file? What if you had a SQL table that actually did not have a set of fields that guaranteed a unique value? Then you have NOTHING to create an item-id from! I have to stop this, it will consume me! :o But the list goes on. Oh the humanity! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Robert Houben Sent: Thursday, December 23, 2010 10:36 PM To: U2 Users List Subject: Re: [U2] Migration I may have been unclear in my earlier post, so I'll clarify. Consider a CUSTOMER file and an APPOINTMENTS file. The item-id of the CUSTOMER file is the customer number. The item-id of the APPOINTMENTS file is CUSTOMERNO*APPTDATE*APPTTIME*APPTTYPE. When you have a parent/child relationship in multiple files in MV, except in those cases where the key to a child file is the item-id of the parent file with a sequential ordinal, the common way to indicate a parent-child relationship involving more than one file in MV is to embed, in the parent item a multivalued set of either all the entire item-ids of the child table, or the portion that needs to be concatenated to the parent's item-id. That is what I call a cross reference field. If you don't have this, you are faced with trying to scan the whole APPOINTMENTS file to find all item-ids that start with your CUSTOMERNO value. What you might actually have in the CUSTOMER file is a set of 3 correlated multivalued attributes that have APPTDATE, APPTTIME, and APPTTYPE values for all the APPOINTMENTS items that pertain to the CUSTOMER item. In a SQL environment, the primary key to the child table would consist of at least two fields, one or more of which would be the full primary key of the parent table. In SQL Server a true primary key forces the file to actually be sorted by those key fields (it forces a clustered index). You can also have secondary indexes that are also pre-sorted by their indexed columns. They are effectively complete copies of the indexed fields and a copy of the primary key so it can directly read the data once you've found the index entries that match your query. In our example above, you'd have CUSTOMERNO as a primary key to the CUSTOMER
Re: [U2] Migration
On 24/12/10 01:06, Robert Houben wrote: In the end, where databases are concerned, there is no substitute for good architecture, design and planning. And while you're at it, design for flexibility: You'll almost certainly get some things *wrong* the first time around! AOL !!! :-) Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
And the second, and third, and then when you get it right you will realise that that first really wrong version actually had a couple of realy cool features that you have now designed out of it -but hey this keeps us in jobs ... Merry Christmas everyone. From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: 24 December 2010 11:29 To: u2-users@listserver.u2ug.org Subject: Re: [U2] Migration On 24/12/10 01:06, Robert Houben wrote: In the end, where databases are concerned, there is no substitute for good architecture, design and planning. And while you're at it, design for flexibility: You'll almost certainly get some things *wrong* the first time around! AOL !!! :-) Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users _ No virus found in this message. Checked by AVG - www.avg.com Version: 10.0.1188 / Virus Database: 1435/3334 - Release Date: 12/23/10 ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
I was under the impression that when a relational table is being indexed the DBMS creates and maintains a sorted copy of the original table for every indexed field. That means for clustered indices tables sorted by every conceivable combination need to be maintained, having a huge impact on space requirements and performance. So when you are looking for a row or rows that match the indexed key, in a RDBMS an algorithm can be used to locate the row(s) instead of reading in the whole table and comparing every single row. So you could for instance go to the middle of the (already pre-sorted) table (if you have 100 Million rows in your table you read row 50) and check if the key is greater equal or smaller than the value you are looking for and carry on that way until you have a match. That way you eliminate half the number of rows you need to compare with every read. Of course they probably use much more sophisticated algorithms these days. But regardless what algorithm they may use it has to be slower than the hashing algorithm used by mv as long as you have well sized files using sensible item ids. To save space some RDBMS may also have implemented reduced datasets so they may just hold the indexed keys in the row instead of duplicating the whole data; in which case the primary index somehow needs to be used to retrieve the data in the row afterwards. So as you can see even in a RDBMS there is calculation going on when indices are used. I would actually go so far to say that relational databases don't use real indices at all. They just duplicate the dataset sorted in different ways. But that of course is a matter of how you define what a real index is supposed to be. Well, and when you want to sort then you just read the already sorted table into memory instead of the original - so it can be a lot faster than reading a list of item ids from an index file and then reading the items one by one from the data file using the hashing algorithm as it is done in the mv-world. That is also the reason why mv can only use one index at a time and why we don't need joins. On 24/12/2010 04:45, fft2...@aol.com wrote: In a message dated 12/23/2010 4:28:38 PM Pacific Standard Time, antli...@youngman.org.uk writes: SQL uses indexes. MV uses cross references to item-ids (MV sometimes supports indexes, but they don't always work as well as in the relational world.) I don't know as that is true ... or are you using the word index to mean something completely different to me? I'll agree the implementation of indices can be buggy, but surely that's true of relational engines too? I'm not quite sure I'm confortable with the idea (expressed in the prior-prior posting of which I here quote and enquote the reponse) that MV uses cross-references to item-ids. To me a hash table, isn't the same thing as a cross-reference which sounds a lot like a secondary key. Hashing calculates an exact jump point at which a group of related records are kept. They are related by having the same hash value. But the hash value itself isn't looked up, it's a calculation. I wonder if you can setup a first normal form table in such a way, that it maintains a constant sorted order ? Sorting on the primary key, would then be merely display time bound, there is no effort to it. I suppose you could even pick up and lay down the database periodically so the sort order matches the actual disk layout. Pick could never do something like that. There is always going to be effort involved in any sorting, even if you're simply traversing the index tree and grabbing the underlying data records. But back to reality, I don't think SQL works this way anyway. The perception that it sorts much faster is probably related more closely to the horsepower behind the scenes. Pick systems tend to be installed on slower systems because they are so efficient and most users are cheap with their database, and expensive with their graphics. So they install the SQL type databases on speedy machines. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
A read on a primary key is about as efficient as an MV hashed read. Each has their trade-offs. Get the modulo wrong and your MV read can be nasty. You can get a SQL table in trouble, too. You really can't beat getting all your data in one disk head movement, but we were talking about just creating a single PICK file for each table, to keep the migration simple. There are some excellent articles on how indexes work in SQL Server that you may wish to read. Your understanding below is partially correct in places, but indexes do NOT copy all the data. Just the indexed fields. Note that a PICK index has to copy all the item-ids and all the indexed fields, but you don't get anything pre-sorted, just hashed. Note that the primary key sorts the actual table. All subsequent indexes are actually sorted copies of the indexed columns with keys or some other references to the real records. If your query only uses fields in the index, you won't ever read the real table's data. SQL will beat MV every time when you sort fields that are indexed. For direct reads, MV seems to have a slight advantage. Inserts and updates that affect indexed fields are slower in SQL (inserts are painfully slow if you fail to size your SQL table well, but try inserting millions of records into a file with a modulo of 1...) Here's one short article with some diagrams that show what goes on in clustered and non-clustered indexes: http://technet.microsoft.com/en-us/library/aa964133(SQL.90).aspx -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Mecki Foerthmann Sent: Friday, December 24, 2010 5:56 AM To: U2 Users List Subject: Re: [U2] Migration I was under the impression that when a relational table is being indexed the DBMS creates and maintains a sorted copy of the original table for every indexed field. That means for clustered indices tables sorted by every conceivable combination need to be maintained, having a huge impact on space requirements and performance. So when you are looking for a row or rows that match the indexed key, in a RDBMS an algorithm can be used to locate the row(s) instead of reading in the whole table and comparing every single row. So you could for instance go to the middle of the (already pre-sorted) table (if you have 100 Million rows in your table you read row 50) and check if the key is greater equal or smaller than the value you are looking for and carry on that way until you have a match. That way you eliminate half the number of rows you need to compare with every read. Of course they probably use much more sophisticated algorithms these days. But regardless what algorithm they may use it has to be slower than the hashing algorithm used by mv as long as you have well sized files using sensible item ids. To save space some RDBMS may also have implemented reduced datasets so they may just hold the indexed keys in the row instead of duplicating the whole data; in which case the primary index somehow needs to be used to retrieve the data in the row afterwards. So as you can see even in a RDBMS there is calculation going on when indices are used. I would actually go so far to say that relational databases don't use real indices at all. They just duplicate the dataset sorted in different ways. But that of course is a matter of how you define what a real index is supposed to be. Well, and when you want to sort then you just read the already sorted table into memory instead of the original - so it can be a lot faster than reading a list of item ids from an index file and then reading the items one by one from the data file using the hashing algorithm as it is done in the mv-world. That is also the reason why mv can only use one index at a time and why we don't need joins. On 24/12/2010 04:45, fft2...@aol.com wrote: In a message dated 12/23/2010 4:28:38 PM Pacific Standard Time, antli...@youngman.org.uk writes: SQL uses indexes. MV uses cross references to item-ids (MV sometimes supports indexes, but they don't always work as well as in the relational world.) I don't know as that is true ... or are you using the word index to mean something completely different to me? I'll agree the implementation of indices can be buggy, but surely that's true of relational engines too? I'm not quite sure I'm confortable with the idea (expressed in the prior-prior posting of which I here quote and enquote the reponse) that MV uses cross-references to item-ids. To me a hash table, isn't the same thing as a cross-reference which sounds a lot like a secondary key. Hashing calculates an exact jump point at which a group of related records are kept. They are related by having the same hash value. But the hash value itself isn't looked up, it's a calculation. I wonder if you can setup a first normal form table in such a way, that it maintains a constant sorted order
Re: [U2] Migration (OT)
So I was more or less right then. ;-) Afaik mv-indexing uses a b-tree structure for better performance. Therefore your statement that you don't get anything pre-sorted might not be quite right. And which one of your statements shall we believe when you claim that a read on a primary key is about as efficient as a MV hashed read and for direct reads, MV seems to have a slight advantage? It's either one or the other. And talking about inserting millions of records into a file with a modulo of 1 isn't really helpful now, or is it? I use relational for data warehousing, analytics and reporting myself. Relational can be fast but once you start using joins... Nevertheless, in a well designed mv database you will need a lot less indexing than in a similar relational one. And as you admit yourself, when it comes to writing (inserting) mv can't be beat. So for operational data, mv would be my choice of db anytime. And no, we are not talking about just creating a relational model using a mv-database. That defies the purpose and is what this tread imho was about. After all first normal form is just a sub-set of mv, while in sql-land it's the only one you have! MV only excels when you convert dependant sub-tables into mv fields. And that's where any attempts to fully automate this process has to fail. On 24/12/2010 15:50, Robert Houben wrote: A read on a primary key is about as efficient as an MV hashed read. Each has their trade-offs. Get the modulo wrong and your MV read can be nasty. You can get a SQL table in trouble, too. You really can't beat getting all your data in one disk head movement, but we were talking about just creating a single PICK file for each table, to keep the migration simple. There are some excellent articles on how indexes work in SQL Server that you may wish to read. Your understanding below is partially correct in places, but indexes do NOT copy all the data. Just the indexed fields. Note that a PICK index has to copy all the item-ids and all the indexed fields, but you don't get anything pre-sorted, just hashed. Note that the primary key sorts the actual table. All subsequent indexes are actually sorted copies of the indexed columns with keys or some other references to the real records. If your query only uses fields in the index, you won't ever read the real table's data. SQL will beat MV every time when you sort fields that are indexed. For direct reads, MV seems to have a slight advantage. Inserts and updates that affect indexed fields are slower in SQL (inserts are painfully slow if you fail to size your SQL table well, but try inserting millions of records into a file with a modulo of 1...) Here's one short article with some diagrams that show what goes on in clustered and non-clustered indexes: http://technet.microsoft.com/en-us/library/aa964133(SQL.90).aspx -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Mecki Foerthmann Sent: Friday, December 24, 2010 5:56 AM To: U2 Users List Subject: Re: [U2] Migration I was under the impression that when a relational table is being indexed the DBMS creates and maintains a sorted copy of the original table for every indexed field. That means for clustered indices tables sorted by every conceivable combination need to be maintained, having a huge impact on space requirements and performance. So when you are looking for a row or rows that match the indexed key, in a RDBMS an algorithm can be used to locate the row(s) instead of reading in the whole table and comparing every single row. So you could for instance go to the middle of the (already pre-sorted) table (if you have 100 Million rows in your table you read row 50) and check if the key is greater equal or smaller than the value you are looking for and carry on that way until you have a match. That way you eliminate half the number of rows you need to compare with every read. Of course they probably use much more sophisticated algorithms these days. But regardless what algorithm they may use it has to be slower than the hashing algorithm used by mv as long as you have well sized files using sensible item ids. To save space some RDBMS may also have implemented reduced datasets so they may just hold the indexed keys in the row instead of duplicating the whole data; in which case the primary index somehow needs to be used to retrieve the data in the row afterwards. So as you can see even in a RDBMS there is calculation going on when indices are used. I would actually go so far to say that relational databases don't use real indices at all. They just duplicate the dataset sorted in different ways. But that of course is a matter of how you define what a real index is supposed to be. Well, and when you want to sort then you just read the already sorted table into memory instead of the original
Re: [U2] Migration (OT)
AM To: U2 Users List Subject: Re: [U2] Migration (OT) So I was more or less right then. ;-) Afaik mv-indexing uses a b-tree structure for better performance. Therefore your statement that you don't get anything pre-sorted might not be quite right. And which one of your statements shall we believe when you claim that a read on a primary key is about as efficient as a MV hashed read and for direct reads, MV seems to have a slight advantage? It's either one or the other. And talking about inserting millions of records into a file with a modulo of 1 isn't really helpful now, or is it? I use relational for data warehousing, analytics and reporting myself. Relational can be fast but once you start using joins... Nevertheless, in a well designed mv database you will need a lot less indexing than in a similar relational one. And as you admit yourself, when it comes to writing (inserting) mv can't be beat. So for operational data, mv would be my choice of db anytime. And no, we are not talking about just creating a relational model using a mv-database. That defies the purpose and is what this tread imho was about. After all first normal form is just a sub-set of mv, while in sql-land it's the only one you have! MV only excels when you convert dependant sub-tables into mv fields. And that's where any attempts to fully automate this process has to fail. On 24/12/2010 15:50, Robert Houben wrote: A read on a primary key is about as efficient as an MV hashed read. Each has their trade-offs. Get the modulo wrong and your MV read can be nasty. You can get a SQL table in trouble, too. You really can't beat getting all your data in one disk head movement, but we were talking about just creating a single PICK file for each table, to keep the migration simple. There are some excellent articles on how indexes work in SQL Server that you may wish to read. Your understanding below is partially correct in places, but indexes do NOT copy all the data. Just the indexed fields. Note that a PICK index has to copy all the item-ids and all the indexed fields, but you don't get anything pre-sorted, just hashed. Note that the primary key sorts the actual table. All subsequent indexes are actually sorted copies of the indexed columns with keys or some other references to the real records. If your query only uses fields in the index, you won't ever read the real table's data. SQL will beat MV every time when you sort fields that are indexed. For direct reads, MV seems to have a slight advantage. Inserts and updates that affect indexed fields are slower in SQL (inserts are painfully slow if you fail to size your SQL table well, but try inserting millions of records into a file with a modulo of 1...) Here's one short article with some diagrams that show what goes on in clustered and non-clustered indexes: http://technet.microsoft.com/en-us/library/aa964133(SQL.90).aspx -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Mecki Foerthmann Sent: Friday, December 24, 2010 5:56 AM To: U2 Users List Subject: Re: [U2] Migration I was under the impression that when a relational table is being indexed the DBMS creates and maintains a sorted copy of the original table for every indexed field. That means for clustered indices tables sorted by every conceivable combination need to be maintained, having a huge impact on space requirements and performance. So when you are looking for a row or rows that match the indexed key, in a RDBMS an algorithm can be used to locate the row(s) instead of reading in the whole table and comparing every single row. So you could for instance go to the middle of the (already pre-sorted) table (if you have 100 Million rows in your table you read row 50) and check if the key is greater equal or smaller than the value you are looking for and carry on that way until you have a match. That way you eliminate half the number of rows you need to compare with every read. Of course they probably use much more sophisticated algorithms these days. But regardless what algorithm they may use it has to be slower than the hashing algorithm used by mv as long as you have well sized files using sensible item ids. To save space some RDBMS may also have implemented reduced datasets so they may just hold the indexed keys in the row instead of duplicating the whole data; in which case the primary index somehow needs to be used to retrieve the data in the row afterwards. So as you can see even in a RDBMS there is calculation going on when indices are used. I would actually go so far to say that relational databases don't use real indices at all. They just duplicate the dataset sorted in different ways. But that of course is a matter of how you define what a real index is supposed to be. Well, and when you want to sort then you just read the already
Re: [U2] Migration (OT)
? I'm really curious if one has been written and achieved significant market penetration. I'm not considering new releases of old apps like RR or Dynix or Epicor, I'm talking about a new product written from scratch. Anyways, we've digressed seriously from our original discussion. The point is, there are fairly common SQL constructs that defy simple migration to MV, just as there are unusual uses of multivalues, subvalues, and transient data types in MV that defy simple migration to SQL. You could probably move 75-80% of your tables to MV if you decided how to handle multi-part keys. Then you'd have to look for the implicit, hidden, foreign key relationships that your automatic search missed, and figure out how to deal with them. And you'd have to probably auto-create dictionaries and define indexes to enable your app to efficiently find all the children in a parent/child relationship. You may also choose to merge some child relationships into the parent MV file instead of making it a separate table, but you could do that in a separate step. In the end, there will undoubtedly be some application-specific stuff that you do, but then, that's almost always the case, when dealing with a dbms, isn't it! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Mecki Foerthmann Sent: Friday, December 24, 2010 9:25 AM To: U2 Users List Subject: Re: [U2] Migration (OT) So I was more or less right then. ;-) Afaik mv-indexing uses a b-tree structure for better performance. Therefore your statement that you don't get anything pre-sorted might not be quite right. And which one of your statements shall we believe when you claim that a read on a primary key is about as efficient as a MV hashed read and for direct reads, MV seems to have a slight advantage? It's either one or the other. And talking about inserting millions of records into a file with a modulo of 1 isn't really helpful now, or is it? I use relational for data warehousing, analytics and reporting myself. Relational can be fast but once you start using joins... Nevertheless, in a well designed mv database you will need a lot less indexing than in a similar relational one. And as you admit yourself, when it comes to writing (inserting) mv can't be beat. So for operational data, mv would be my choice of db anytime. And no, we are not talking about just creating a relational model using a mv-database. That defies the purpose and is what this tread imho was about. After all first normal form is just a sub-set of mv, while in sql-land it's the only one you have! MV only excels when you convert dependant sub-tables into mv fields. And that's where any attempts to fully automate this process has to fail. On 24/12/2010 15:50, Robert Houben wrote: A read on a primary key is about as efficient as an MV hashed read. Each has their trade-offs. Get the modulo wrong and your MV read can be nasty. You can get a SQL table in trouble, too. You really can't beat getting all your data in one disk head movement, but we were talking about just creating a single PICK file for each table, to keep the migration simple. There are some excellent articles on how indexes work in SQL Server that you may wish to read. Your understanding below is partially correct in places, but indexes do NOT copy all the data. Just the indexed fields. Note that a PICK index has to copy all the item-ids and all the indexed fields, but you don't get anything pre-sorted, just hashed. Note that the primary key sorts the actual table. All subsequent indexes are actually sorted copies of the indexed columns with keys or some other references to the real records. If your query only uses fields in the index, you won't ever read the real table's data. SQL will beat MV every time when you sort fields that are indexed. For direct reads, MV seems to have a slight advantage. Inserts and updates that affect indexed fields are slower in SQL (inserts are painfully slow if you fail to size your SQL table well, but try inserting millions of records into a file with a modulo of 1...) Here's one short article with some diagrams that show what goes on in clustered and non-clustered indexes: http://technet.microsoft.com/en-us/library/aa964133(SQL.90).aspx -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Mecki Foerthmann Sent: Friday, December 24, 2010 5:56 AM To: U2 Users List Subject: Re: [U2] Migration I was under the impression that when a relational table is being indexed the DBMS creates and maintains a sorted copy of the original table for every indexed field. That means for clustered indices tables sorted by every conceivable combination need to be maintained, having a huge impact on space requirements and performance. So
Re: [U2] Migration
I don't know a lot about fully normalized relational database but I do know you can read the the schema of a database. (Again - just thinking out loud) They have Master Data Management (MDM) tools that collect, aggregate, match, consolidates, persists and distributes data to ensure consistency and control of this information. By using this kind of tool, theoretically, you could re-build your schema in an MV format that would take advantage of MV technology. To Ross's point, the technology might not be there for real-time processing across databases, however, you could get near-time. And outside of transactional processing, near-time meets the needs of most projects. I just see a lot of people looking to migrate data off of MV, I think by creating an easy migration path to (and from) an MV environment, you would draw more attention. Not to get to far into this discussion (at this time) I respectfully disagree with those who have said that your data needs to be application specific. This thought puts the emphasis on the application and not on the data. And it's all about the data! Applications are easy to build and they SHOULD be much more dynamic then they currently are and that is because business is dynamic. 'We act as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about.' - Original Message From: Wols Lists antli...@youngman.org.uk To: u2-users@listserver.u2ug.org Sent: Wed, December 22, 2010 6:01:09 PM Subject: Re: [U2] Migration On 22/12/10 19:49, Shawn Hayes wrote: Why would it need to be application specific? I was just thinking that architecturally (sometimes) there are advantages to using a non first normal form databases. If you can read the schema of a fully relational database, couldn't you easily enough re-create the files embedding child elements into MV tables? NO. (Sadly) I've read the other replies saying it's application specific. And it is. Ask yourself how you're going to *program* your migration tool to know which tables should be merged into an MV file. It can't be done. And the reason is inherent in relational theory. In theory, an attribute can exist on its own. In reality, an attribute is like an adjective, with nothing to describe it doesn't exist. How is your migration tool going to work out which adjectives describe which noun, and hence which attributes belong in the same file, and which ones don't? You can guess, but chances are you're going to make *several* mistakes, which could seriously damage all the advantages MV brings. Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
For the single-valued stuff, then the migration path to SQL is a slam dunk. A little CRUD subprogram could be written to handle a multivalue blob inside a SQL cell. Is it an optimal solution?... of course not. Could it be done?...yes. Would anybody want to buy it? ... --Bill ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
A simple ETL program can do the single value stuff. I have actually done this with Kettle from Pentaho. Would anyone buy it - That is a great question! The second part of this is creating a need:) This idea in and of itself is not enough. However, MV technology is a proven technology that can (and will) be a force in the market. Whether it be in existing platforms like U2 or integrated into existing fully normalized relational databases 'We act as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about.' - Original Message From: Bill Brutzman bi...@hkmetalcraft.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Thu, December 23, 2010 11:08:54 AM Subject: Re: [U2] Migration For the single-valued stuff, then the migration path to SQL is a slam dunk. A little CRUD subprogram could be written to handle a multivalue blob inside a SQL cell. Is it an optimal solution?... of course not. Could it be done?...yes. Would anybody want to buy it? ... --Bill ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
The multi-valued format as you're calling it, is not an abnormal form. It is a non-first normal form. That is, it is normal, but it is not first normal. The query language is extended with an implied unnest operation. We don't actually use this type of language in MV, since we just assume that it can do it, without our explicit order. MV is not the only structure that allows nested relationships. I don't really know if this type of designation non-first normal is going to catch hold in the MV world. Probably we're going to end up calling it Nested Relational. The MV world is certainly a giant step beyond the true non-relational databases such as *shudder* Excel which have multiple abnormalities in the way they can and are updated. I.E. dependency relationships are often left dangling or in ambiguous states, a thing which can not happen (at least not in the same fashion) in MV. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
In a message dated 12/23/2010 11:14:45 AM Pacific Standard Time, antli...@youngman.org.uk writes: Actually, I'd disagree with you. Applications are all about the METAdata, which a relational database throws away. ALL relational APPS contain an awful lot of logic to manage stuff that SHOULD be managed in the database - except an RDBMS has no way of managing that information so it can't be managed in an RDBMS. I talked about adjectives out there in the real world. Adjectives describe nouns. What's the database equivalent of a noun? That's right, in an RDBMS there is NO SUCH EQUIVALENT. Can you give those of use who are more dense, a concrete, specific example of what you're talking about? I've seen several messages like this, and still don't comprehend it. Do not first-normal form databases have column headings? Aren't those headings the names of the attributes (nouns if you will)? Or the table names the names of the nouns. I'm still not seeing why you can't simply create an MV file for each Table, a record for each row, and an attibute for each column. Where's the problem? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
There is the problem of atomicity... one of the important hallmarks of good database design. MV files of records with attribute marks can be directly ported to SQL tables. The problem is what to do about data with value marks and subvalue marks. These blobs can be crammed into SQL cells but then the data is no longer atomic. --Bill Actually, I'd disagree with you. Applications are all about the METAdata, which a relational database throws away. ALL relational APPS contain an awful lot of logic to manage stuff that SHOULD be managed in the database - except an RDBMS has no way of managing that information so it can't be managed in an RDBMS. I talked about adjectives out there in the real world. Adjectives describe nouns. What's the database equivalent of a noun? That's right, in an RDBMS there is NO SUCH EQUIVALENT. Can you give those of use who are more dense, a concrete, specific example of what you're talking about? I've seen several messages like this, and still don't comprehend it. Do not first-normal form databases have column headings? Aren't those headings the names of the attributes (nouns if you will)? Or the table names the names of the nouns. I'm still not seeing why you can't simply create an MV file for each Table, a record for each row, and an attibute for each column. Where's the problem? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration [AD]
I've been watching this thread with some interest. Because I'm going to reference our product, I'm putting th [AD] marker on this. One of our best-selling products assists our customers in rapid migration/data warehousing of Multivalued and Subvalued data to either SQL Server, Oracle, DB2, Progress, MySQL and other relational databases. We actually have a whole suite of tools to assist our customers in solving these issues. With our most popular product, you start by identifying (and mapping) the data that you want to retrieve. We provide graphical mapping tools that make this relatively painless. We also provide tools for data cleansing and analysis, so you can ensure that you have mapped the data right, can fix the worst problems in your data, and can skip-and-log when you hit errors, rather than crash-and-burn when you hit these situations. In some cases the mapping step can be skipped, and we even have a way to let you use dictionaries to get your output data. Once you have sourced the output data, we will then optionally create the required tables, columns and primary keys in the Relational database, to create the tables that are required to support the equivalent structure to your MultiValued data. Note that going from MultiValue to Relational is the easy direction, but even so, it is fraught with some nasty issues, the worst ones being that PICK is very forgiving of garbage in situations. SQL is not. If you decide that the field is supposed to have a date, then TOMORROW, BEFORE 1PM is not going to work! (this was a real scenario for one of our customers.) In order to go the other direction, there are some inescapable questions that have to be answered: MV can only support 2 levels of nesting. If you have 3 or more 1-to-many relationships, you have to decide at some point to keep a set of keys (item-ids) in a multivalued or subvalued cross-reference and use another file. Deciding where and when to do this becomes the tricky thing. SQL uses indexes. MV uses cross references to item-ids (MV sometimes supports indexes, but they don't always work as well as in the relational world.) There are other issues, but that's a good starting point. If you really want to explore the concept there is no substitute for playing with it, and you'll discover there are more issues lurking there... Enjoy! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Bill Brutzman Sent: Thursday, December 23, 2010 3:09 PM To: U2 Users List Subject: Re: [U2] Migration There is the problem of atomicity... one of the important hallmarks of good database design. MV files of records with attribute marks can be directly ported to SQL tables. The problem is what to do about data with value marks and subvalue marks. These blobs can be crammed into SQL cells but then the data is no longer atomic. --Bill Actually, I'd disagree with you. Applications are all about the METAdata, which a relational database throws away. ALL relational APPS contain an awful lot of logic to manage stuff that SHOULD be managed in the database - except an RDBMS has no way of managing that information so it can't be managed in an RDBMS. I talked about adjectives out there in the real world. Adjectives describe nouns. What's the database equivalent of a noun? That's right, in an RDBMS there is NO SUCH EQUIVALENT. Can you give those of use who are more dense, a concrete, specific example of what you're talking about? I've seen several messages like this, and still don't comprehend it. Do not first-normal form databases have column headings? Aren't those headings the names of the attributes (nouns if you will)? Or the table names the names of the nouns. I'm still not seeing why you can't simply create an MV file for each Table, a record for each row, and an attibute for each column. Where's the problem? ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
On 23/12/10 22:03, fft2...@aol.com wrote: In a message dated 12/23/2010 11:14:45 AM Pacific Standard Time, antli...@youngman.org.uk writes: Actually, I'd disagree with you. Applications are all about the METAdata, which a relational database throws away. ALL relational APPS contain an awful lot of logic to manage stuff that SHOULD be managed in the database - except an RDBMS has no way of managing that information so it can't be managed in an RDBMS. I talked about adjectives out there in the real world. Adjectives describe nouns. What's the database equivalent of a noun? That's right, in an RDBMS there is NO SUCH EQUIVALENT. Can you give those of use who are more dense, a concrete, specific example of what you're talking about? I've seen several messages like this, and still don't comprehend it. Do not first-normal form databases have column headings? Aren't those headings the names of the attributes (nouns if you will)? Or the table names the names of the nouns. First Normal Form databases are two-dimensional. Table and column names are irrelevant here, the question is how do you *store* a noun-thingy IN the database, not how you define the database. Let's take a car ... Let's define a relational table called CAR. Let's key it off the VIN, which should be a unique id. Add a column called REG ... oops - cars can have multiple registrations over the years, so we can't put the registration in the CAR table. Now let's create a column called COLOUR ... oops, a car can be multi-coloured, so we can't put the colour in the CAR table. etc etc etc. The point is, a well designed MV database has ONE file that contains ALL the attributes for any given entity - each row contains a *complete* instance of a noun, each column contains *all* the values of the adjective that describes that noun. In an MV-dbms, ALL the data about any one car is stored in one record in one file. In an RDBMS, ALL the data about any one car is plastered across many rows in many tables. I'm still not seeing why you can't simply create an MV file for each Table, a record for each row, and an attibute for each column. Where's the problem? Because if you do this you do not have a Multi-Valued database. You have a relational database in a multi-value engine. In other words, the *worst* of both worlds. There's nothing stopping you doing it. You just don't end up with a Multi-Valued database at the end of it! I recognise your addy. Surely you know all this? It's all pretty basic MultiValue! Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
On 24/12/10 00:07, Robert Houben wrote: I've been watching this thread with some interest. Because I'm going to reference our product, I'm putting th [AD] marker on this. One of our best-selling products assists our customers in rapid migration/data warehousing of Multivalued and Subvalued data to either SQL Server, Oracle, DB2, Progress, MySQL and other relational databases. We actually have a whole suite of tools to assist our customers in solving these issues. Except that's not the issue that the OP posed :-) He wanted to migrate AWAY from SQL server etc :-) In order to go the other direction, there are some inescapable questions that have to be answered: MV can only support 2 levels of nesting. If you have 3 or more 1-to-many relationships, you have to decide at some point to keep a set of keys (item-ids) in a multivalued or subvalued cross-reference and use another file. Deciding where and when to do this becomes the tricky thing. This, imho, is where the relational guys have a problem with reality. Yes I know that decision is HARD. So the relational guys abstract it away. The cost is that relational databases are not deterministic. It becomes a case of yes there is definitely a solution, and I will definitely find it, but it may take longer than the life of the universe to get there :-) (And I'm only *half* joking). THAT is the reason that MV engines are *always* faster than relational engines. Einstein said don't make things *too* simple. The relational guys have done exactly that! SQL uses indexes. MV uses cross references to item-ids (MV sometimes supports indexes, but they don't always work as well as in the relational world.) I don't know as that is true ... or are you using the word index to mean something completely different to me? I'll agree the implementation of indices can be buggy, but surely that's true of relational engines too? There are other issues, but that's a good starting point. If you really want to explore the concept there is no substitute for playing with it, and you'll discover there are more issues lurking there... Yup ... Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
I left the discussion alone until some people started talking about both directions, although we do help people go both ways. I actually had a customer at one time who had migrated off a mainframe. They had so much data that when you looked at the disk-head movement, average access time, and volume of data that they had to process, they were able to prove mathematically that they could not, with the technology that was then available (and I think still not) get away with a FNF relational setup. They had to use a MV approach, and decided on Unidata. I recall having to do indexed SELECT statements against 30 GB files and doing intersect merges to combine indexes because LIST/SORT/SELECT/SSELECT would use the first indexed field you referenced, and then ignore all other indexes, brute forcing the rest of the selection criteria. With a 30 GB file, that was never an option. And yes, if you throw enough joins at SQL Server it gives up optimizing, but lately, you have to really make it complex before it gives up. I haven't tried it recently, so maybe Unidata handles indexes better these days. Maybe Universe does. But the last time I tried doing anything significant using indexes on large U2 files, it got tricky. My point is that an application using SQL Server, Oracle or some other relational engine, often uses indexes in a way that may not port easily to MV. They also work differently. One designs Relational data differently than one designs MV data. So maybe instead of saying of indexes on MV that they don't work as well, I should have said they solve different problems in different ways. I agree with your assessment of the Relational approach having issues with reality. They intentionally abstract out reality, and the programmer gets to reconstitute it. One of the things that I've noticed is that when the data that describes an object is in multiple tables, as with a FNF relational setup, as soon as you want to work with it in an application, you effectively have to create internal structures to work with the whole item anyways. But now you're making a programmer do it, using variables, instead of allowing the database to do it for you. On the flip side, doing data mining is much easier when the data is fully normalized and duplicates are eliminated... Having worked on both sides of the house, there are decidedly times when each one excels, and tasks for which each model is best. I will, in the same day, program PICK/BASIC, Java, C#, C++ and JavaScript and probably at least one other language. The power of MV for rapidly working with data is amazing. The fact that I can create a file and start to use it (it's my problem to make sure I use it consistently) without having to jump through hurdles to configure everything I need in it is both inspiring and a bit scary. Two programmers can decide to grab the same next field and start using it and if they don't happen to compare notes, they'll get away with it, with devastating results when they deploy their changes. This doesn't happen in the relational world, but then, the hurdles you go through to work with data are considerably more daunting. In the end, where databases are concerned, there is no substitute for good architecture, design and planning. And while you're at it, design for flexibility: You'll almost certainly get some things *wrong* the first time around! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Wols Lists Sent: Thursday, December 23, 2010 4:28 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Migration On 24/12/10 00:07, Robert Houben wrote: I've been watching this thread with some interest. Because I'm going to reference our product, I'm putting th [AD] marker on this. One of our best-selling products assists our customers in rapid migration/data warehousing of Multivalued and Subvalued data to either SQL Server, Oracle, DB2, Progress, MySQL and other relational databases. We actually have a whole suite of tools to assist our customers in solving these issues. Except that's not the issue that the OP posed :-) He wanted to migrate AWAY from SQL server etc :-) In order to go the other direction, there are some inescapable questions that have to be answered: MV can only support 2 levels of nesting. If you have 3 or more 1-to-many relationships, you have to decide at some point to keep a set of keys (item-ids) in a multivalued or subvalued cross-reference and use another file. Deciding where and when to do this becomes the tricky thing. This, imho, is where the relational guys have a problem with reality. Yes I know that decision is HARD. So the relational guys abstract it away. The cost is that relational databases are not deterministic. It becomes a case of yes there is definitely a solution, and I will definitely find it, but it may take longer than
Re: [U2] Migration
In a message dated 12/23/2010 4:20:22 PM Pacific Standard Time, antli...@youngman.org.uk writes: I'm still not seeing why you can't simply create an MV file for each Table, a record for each row, and an attibute for each column. Where's the problem? Because if you do this you do not have a Multi-Valued database. You have a relational database in a multi-value engine. In other words, the *worst* of both worlds. There's nothing stopping you doing it. You just don't end up with a Multi-Valued database at the end of it! I recognise your addy. Surely you know all this? It's all pretty basic MultiValue! Yes but. The original question however wasn't how to make an effective and efficient database in MV. Just how to make one from the first normal form tables. I certainly understand your point now, but you went a step beyond the requirement. The client isn't paying for that! And I disagree with your assessment that you don't have a Multi-Valued database at the end. You do, it's just flat. After all an empty MV file is still an MV file. A file in MV does not *have* to have multi-values in order to be a file in the MV environment. Certainly it's not the best possible organization of the data in MV. But it is a mapped version of the original first normal form tables. W ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
In a message dated 12/23/2010 4:28:38 PM Pacific Standard Time, antli...@youngman.org.uk writes: SQL uses indexes. MV uses cross references to item-ids (MV sometimes supports indexes, but they don't always work as well as in the relational world.) I don't know as that is true ... or are you using the word index to mean something completely different to me? I'll agree the implementation of indices can be buggy, but surely that's true of relational engines too? I'm not quite sure I'm confortable with the idea (expressed in the prior-prior posting of which I here quote and enquote the reponse) that MV uses cross-references to item-ids. To me a hash table, isn't the same thing as a cross-reference which sounds a lot like a secondary key. Hashing calculates an exact jump point at which a group of related records are kept. They are related by having the same hash value. But the hash value itself isn't looked up, it's a calculation. I wonder if you can setup a first normal form table in such a way, that it maintains a constant sorted order ? Sorting on the primary key, would then be merely display time bound, there is no effort to it. I suppose you could even pick up and lay down the database periodically so the sort order matches the actual disk layout. Pick could never do something like that. There is always going to be effort involved in any sorting, even if you're simply traversing the index tree and grabbing the underlying data records. But back to reality, I don't think SQL works this way anyway. The perception that it sorts much faster is probably related more closely to the horsepower behind the scenes. Pick systems tend to be installed on slower systems because they are so efficient and most users are cheap with their database, and expensive with their graphics. So they install the SQL type databases on speedy machines. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
I may have been unclear in my earlier post, so I'll clarify. Consider a CUSTOMER file and an APPOINTMENTS file. The item-id of the CUSTOMER file is the customer number. The item-id of the APPOINTMENTS file is CUSTOMERNO*APPTDATE*APPTTIME*APPTTYPE. When you have a parent/child relationship in multiple files in MV, except in those cases where the key to a child file is the item-id of the parent file with a sequential ordinal, the common way to indicate a parent-child relationship involving more than one file in MV is to embed, in the parent item a multivalued set of either all the entire item-ids of the child table, or the portion that needs to be concatenated to the parent's item-id. That is what I call a cross reference field. If you don't have this, you are faced with trying to scan the whole APPOINTMENTS file to find all item-ids that start with your CUSTOMERNO value. What you might actually have in the CUSTOMER file is a set of 3 correlated multivalued attributes that have APPTDATE, APPTTIME, and APPTTYPE values for all the APPOINTMENTS items that pertain to the CUSTOMER item. In a SQL environment, the primary key to the child table would consist of at least two fields, one or more of which would be the full primary key of the parent table. In SQL Server a true primary key forces the file to actually be sorted by those key fields (it forces a clustered index). You can also have secondary indexes that are also pre-sorted by their indexed columns. They are effectively complete copies of the indexed fields and a copy of the primary key so it can directly read the data once you've found the index entries that match your query. In our example above, you'd have CUSTOMERNO as a primary key to the CUSTOMER table, and 4 separate fields (no * delimiter) that make up the primary key of the APPOINTMENTS table. I'm not exactly sure how you'd accomplish the same thing in a MultiValued environment if you just copied all the tables as flat MV files. You'd lose the ability to access the child records without doing a complete table scan. Unless of course you analyzed the data, and created some special linking files, but the objective seems to be to avoid human intervention. You might actually be able to do something with an MV index on a dictionary record that references just the portion of the child file's item-id that makes up the parent file's item-id (in our example, the CUSTOMERNO). But you'd have to add that, at the very least, and that would mean creating a dictionary record as part of what you create. And that's NOT how you'd do it in SQL Server, for instance. You might have defined foreign key references in the child table, but that's not a given, so how you'd even know there was a parent-child relationship in place is not clear. In some cases the naming of the keys in the files can give you a hint, (that's how MS Access always tried to figure it out and it worked a surprising amount of the time), but you are not guaranteed that this will work in all cases. I've seen plenty of cases where this did not work. So, you could, in theory get all the data over, but you'd still be faced with making it usable in a truly performant way. Here's another gotcha to consider: In many SQL tables, including our above example, the primary key will consist of several fields. What do you do in PICK where you have one, and only one, item-id attribute? Do you concatenate and assume fixed length, or do you concatenate, choose a separator character and pray it's not contained in the data? What if the concatenation of these fields exceeds the length limit of an MV item-id? I know of real world applications where this is the case... There are workarounds, but they are NOT automatic! You will have to choose some rules to work around issues. You will hit exceptions that you'll actually have to think about, and you may have to redesign some structures, to make them practical. In short, there are things that you would do in a relational environment that don't really have an exact analog that works in the MV world, and vice versa. There, that's a bit more encompassing. I'm out of time, but I hope this has been helpful. -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of fft2...@aol.com Sent: Thursday, December 23, 2010 8:46 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Migration In a message dated 12/23/2010 4:28:38 PM Pacific Standard Time, antli...@youngman.org.uk writes: SQL uses indexes. MV uses cross references to item-ids (MV sometimes supports indexes, but they don't always work as well as in the relational world.) I don't know as that is true ... or are you using the word index to mean something completely different to me? I'll agree the implementation of indices can be buggy, but surely that's true of relational engines too? I'm not quite sure I'm
Re: [U2] Migration
Oh, one more point. What if your SQL environment had NOT defined a primary key for APPOINTMENTS, but had multiple indexes, one of which happened to have CUSTOMERNO, APPTDATE, APPTTIME and APPTTYPE. How would you figure out what to use as the item-id of the PICK file? What if you had a SQL table that actually did not have a set of fields that guaranteed a unique value? Then you have NOTHING to create an item-id from! I have to stop this, it will consume me! :o But the list goes on. Oh the humanity! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users-boun...@listserver.u2ug.org] On Behalf Of Robert Houben Sent: Thursday, December 23, 2010 10:36 PM To: U2 Users List Subject: Re: [U2] Migration I may have been unclear in my earlier post, so I'll clarify. Consider a CUSTOMER file and an APPOINTMENTS file. The item-id of the CUSTOMER file is the customer number. The item-id of the APPOINTMENTS file is CUSTOMERNO*APPTDATE*APPTTIME*APPTTYPE. When you have a parent/child relationship in multiple files in MV, except in those cases where the key to a child file is the item-id of the parent file with a sequential ordinal, the common way to indicate a parent-child relationship involving more than one file in MV is to embed, in the parent item a multivalued set of either all the entire item-ids of the child table, or the portion that needs to be concatenated to the parent's item-id. That is what I call a cross reference field. If you don't have this, you are faced with trying to scan the whole APPOINTMENTS file to find all item-ids that start with your CUSTOMERNO value. What you might actually have in the CUSTOMER file is a set of 3 correlated multivalued attributes that have APPTDATE, APPTTIME, and APPTTYPE values for all the APPOINTMENTS items that pertain to the CUSTOMER item. In a SQL environment, the primary key to the child table would consist of at least two fields, one or more of which would be the full primary key of the parent table. In SQL Server a true primary key forces the file to actually be sorted by those key fields (it forces a clustered index). You can also have secondary indexes that are also pre-sorted by their indexed columns. They are effectively complete copies of the indexed fields and a copy of the primary key so it can directly read the data once you've found the index entries that match your query. In our example above, you'd have CUSTOMERNO as a primary key to the CUSTOMER table, and 4 separate fields (no * delimiter) that make up the primary key of the APPOINTMENTS table. I'm not exactly sure how you'd accomplish the same thing in a MultiValued environment if you just copied all the tables as flat MV files. You'd lose the ability to access the child records without doing a complete table scan. Unless of course you analyzed the data, and created some special linking files, but the objective seems to be to avoid human intervention. You might actually be able to do something with an MV index on a dictionary record that references just the portion of the child file's item-id that makes up the parent file's item-id (in our example, the CUSTOMERNO). But you'd have to add that, at the very least, and that would mean creating a dictionary record as part of what you create. And that's NOT how you'd do it in SQL Server, for instance. You might have defined foreign key references in the child table, but that's not a given, so how you'd even know there was a parent-child relationship in place is not clear. In some cases the naming of the keys in the files can give you a hint, (that's how MS Access always tried to figure it out and it worked a surprising amount of the time), but you are not guaranteed that this will work in all cases. I've seen plenty of cases where this did not work. So, you could, in theory get all the data over, but you'd still be faced with making it usable in a truly performant way. Here's another gotcha to consider: In many SQL tables, including our above example, the primary key will consist of several fields. What do you do in PICK where you have one, and only one, item-id attribute? Do you concatenate and assume fixed length, or do you concatenate, choose a separator character and pray it's not contained in the data? What if the concatenation of these fields exceeds the length limit of an MV item-id? I know of real world applications where this is the case... There are workarounds, but they are NOT automatic! You will have to choose some rules to work around issues. You will hit exceptions that you'll actually have to think about, and you may have to redesign some structures, to make them practical. In short, there are things that you would do in a relational environment that don't really have an exact analog that works in the MV world, and vice versa. There, that's a bit more encompassing. I'm out of time, but I hope this has been helpful
[U2] Migration
Are there products out there to take a fully relational database and migrate it into a non-first-normal form database? We have products out there that migrate from MV databases to fully relational databases... How about the other way around 'We act as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about.' ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
I would think the migration would be application specific. That said, it certainly wouldn't be a difficult thing to write. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
Why would it need to be application specific? I was just thinking that architecturally (sometimes) there are advantages to using a non first normal form databases. If you can read the schema of a fully relational database, couldn't you easily enough re-create the files embedding child elements into MV tables? This would be a great migration path to utilizing some advantages on MV applications? 'We act as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about.' - Original Message From: Kevin King precisonl...@gmail.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Wed, December 22, 2010 1:34:40 PM Subject: Re: [U2] Migration I would think the migration would be application specific. That said, it certainly wouldn't be a difficult thing to write. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
Even though you are right that there can be distinct advantages MV vs. Relational. But you surely wouldn't want a Product Category file that holds all the product information in multi-valued fields. Or Order and invoice data as multivalued fields in the customer file. There is obviously a little bit more to mv database design than just parent-child relationships. On 22/12/2010 19:49, Shawn Hayes wrote: Why would it need to be application specific? I was just thinking that architecturally (sometimes) there are advantages to using a non first normal form databases. If you can read the schema of a fully relational database, couldn't you easily enough re-create the files embedding child elements into MV tables? This would be a great migration path to utilizing some advantages on MV applications? 'We act as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about.' - Original Message From: Kevin King precisonl...@gmail.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Wed, December 22, 2010 1:34:40 PM Subject: Re: [U2] Migration I would think the migration would be application specific. That said, it certainly wouldn't be a difficult thing to write. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
I realize there is a bit more to MV database design then just parent-child relationships. The lack of constraints is both a curse and a blessing in the MV world. However, I don't accept that you wouldn't want a product category file that holds all the product information in MV fields or order and invoice data as MV fields in a customer file. A single read sounds intriguing in a web app?!?!?!? 'We act as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about.' - Original Message From: Mecki Foerthmann mec...@gmx.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Wed, December 22, 2010 2:47:22 PM Subject: Re: [U2] Migration Even though you are right that there can be distinct advantages MV vs. Relational. But you surely wouldn't want a Product Category file that holds all the product information in multi-valued fields. Or Order and invoice data as multivalued fields in the customer file. There is obviously a little bit more to mv database design than just parent-child relationships. On 22/12/2010 19:49, Shawn Hayes wrote: Why would it need to be application specific? I was just thinking that architecturally (sometimes) there are advantages to using a non first normal form databases. If you can read the schema of a fully relational database, couldn't you easily enough re-create the files embedding child elements into MV tables? This would be a great migration path to utilizing some advantages on MV applications? 'We act as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about.' - Original Message From: Kevin King precisonl...@gmail.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Wed, December 22, 2010 1:34:40 PM Subject: Re: [U2] Migration I would think the migration would be application specific. That said, it certainly wouldn't be a difficult thing to write. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
Yeah, if you can design data objects that you can get in one read, Mazeltov! Date: Wed, 22 Dec 2010 13:04:32 -0800 From: go_mnviki...@yahoo.com To: u2-users@listserver.u2ug.org Subject: Re: [U2] Migration I realize there is a bit more to MV database design then just parent-child relationships. The lack of constraints is both a curse and a blessing in the MV world. However, I don't accept that you wouldn't want a product category file that holds all the product information in MV fields or order and invoice data as MV fields in a customer file. A single read sounds intriguing in a web app?!?!?!? 'We act as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about.' - Original Message From: Mecki Foerthmann mec...@gmx.net To: U2 Users List u2-users@listserver.u2ug.org Sent: Wed, December 22, 2010 2:47:22 PM Subject: Re: [U2] Migration Even though you are right that there can be distinct advantages MV vs. Relational. But you surely wouldn't want a Product Category file that holds all the product information in multi-valued fields. Or Order and invoice data as multivalued fields in the customer file. There is obviously a little bit more to mv database design than just parent-child relationships. On 22/12/2010 19:49, Shawn Hayes wrote: Why would it need to be application specific? I was just thinking that architecturally (sometimes) there are advantages to using a non first normal form databases. If you can read the schema of a fully relational database, couldn't you easily enough re-create the files embedding child elements into MV tables? This would be a great migration path to utilizing some advantages on MV applications? 'We act as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about.' - Original Message From: Kevin King precisonl...@gmail.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Wed, December 22, 2010 1:34:40 PM Subject: Re: [U2] Migration I would think the migration would be application specific. That said, it certainly wouldn't be a difficult thing to write. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
Getting everything you want in one read is practical in limited circumstances. Getting what you want in one REQUEST, however... that's much more valuable. We use JSON formatted strings to pass structured data into and out of Unidata using subroutines to collect everything we need. This allows a web request to make a single request and get a response that could include any number of different data elements spanning one read, multiple reads, even multiple files. It's pretty slick. But that's beside the original question. The original question of taking information out of a SQL database and mapping it to a MV database is meaningless without a context, and that context - in my opinion of course - is an application that is creating and/or consuming that information, irrespective of the configuration of data refrigerator in use. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
I was just thinking out loud... First of all, I am not talking about an Easy Button. I am talking about a migration path from a fully normalized relational database (hope that is a more accurate term) to take advantage of MV database functionality. Using U2 as a Data warehouse, serving data to web apps, use my U2 experience to get high paying jobs, increase efficiencies in the cloud and in BI reporting, insert need here. PS Will - There are actually 5 ways to normalize data, and yes, there is a second normal form. However, I have never heard of a First abnormal form. Also, If I thought you could write it better and faster then me, I might take you up on it;) 'We act as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about.' - Original Message From: fft2...@aol.com fft2...@aol.com To: u2-users@listserver.u2ug.org Sent: Wed, December 22, 2010 3:44:35 PM Subject: Re: [U2] Migration In a message dated 12/22/2010 9:02:52 AM Pacific Standard Time, go_mnviki...@yahoo.com writes: Are there products out there to take a fully relational database and migrate it into a non-first-normal form database? Fully relational is a slur. First normal form does that imply there is a first abnormal form ? Or a second normal form ? At any rate, first normal databases are just tables of columns and rows. The rows all share the same column-defined attributes. The rows are records, the columns are the attributes in those records. It's very simple. I'll write it for you for 20 grand. Will Johnson ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
Shawn, while I applaud the concept of finding a way to plug a MV database in where a SQL database might otherwise be ensconced, one problem with the attempt is that while the storage itself is a different animal, more so is the access. Most of these types of apps that rely on a SQL database do so because the data access code is a bunch of SQL queries. And while most MV platforms provide some type of SQL compatibility, that compatibility negates much of the benefits of the MV environment (i.e. nested data sets, etc.), single reads, etc. A while ago I did a proof of concept for a design that used PHP objects to provide pluggable access to Unidata, Universe, MySQL, and PostgreSQL. While I'm sure more talented others have taken the concept much farther than I, the more important problem in all this is that it's inventing a new data access method that isn't MV and it isn't SQL. Moreover, when coding something like this to be compatible with the least-common-denominator, often the end result is the very definition of least. Personally, I think the read/write/delete model of MV is head and shoulders better than the select/insert/update/remove model in SQL. So my efforts were invested on providing a simple read/write/delete model on top of a generic SQL db. But what I found was that there is no generic SQL. MySQL and PostgreSQL in particular are two completely different animals when it comes to insert/update. And all this ripples down to the issue of advisory locking and the other niceties that we tend to take for granted with MV. On the flip side, I am of the opinion that indexing and query optimization are generally much better on SQL. I do wish more SQL implementations supported virtual/derived/correlative fields. This is another of those wonderful things in MV that we tend to take for granted. I am all about finding ways to integrate MV into the larger technology landscape. But right now I believe the two worlds are so far apart that a general purpose application of one technology into the other camp is still problematic. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
I will have to think more about this... I appreciate you sharing your experience and time!!! 'We act as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about.' - Original Message From: Kevin King precisonl...@gmail.com To: U2 Users List u2-users@listserver.u2ug.org Sent: Wed, December 22, 2010 4:45:24 PM Subject: Re: [U2] Migration Shawn, while I applaud the concept of finding a way to plug a MV database in where a SQL database might otherwise be ensconced, one problem with the attempt is that while the storage itself is a different animal, more so is the access. Most of these types of apps that rely on a SQL database do so because the data access code is a bunch of SQL queries. And while most MV platforms provide some type of SQL compatibility, that compatibility negates much of the benefits of the MV environment (i.e. nested data sets, etc.), single reads, etc. A while ago I did a proof of concept for a design that used PHP objects to provide pluggable access to Unidata, Universe, MySQL, and PostgreSQL. While I'm sure more talented others have taken the concept much farther than I, the more important problem in all this is that it's inventing a new data access method that isn't MV and it isn't SQL. Moreover, when coding something like this to be compatible with the least-common-denominator, often the end result is the very definition of least. Personally, I think the read/write/delete model of MV is head and shoulders better than the select/insert/update/remove model in SQL. So my efforts were invested on providing a simple read/write/delete model on top of a generic SQL db. But what I found was that there is no generic SQL. MySQL and PostgreSQL in particular are two completely different animals when it comes to insert/update. And all this ripples down to the issue of advisory locking and the other niceties that we tend to take for granted with MV. On the flip side, I am of the opinion that indexing and query optimization are generally much better on SQL. I do wish more SQL implementations supported virtual/derived/correlative fields. This is another of those wonderful things in MV that we tend to take for granted. I am all about finding ways to integrate MV into the larger technology landscape. But right now I believe the two worlds are so far apart that a general purpose application of one technology into the other camp is still problematic. ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
On 22/12/10 19:49, Shawn Hayes wrote: Why would it need to be application specific? I was just thinking that architecturally (sometimes) there are advantages to using a non first normal form databases. If you can read the schema of a fully relational database, couldn't you easily enough re-create the files embedding child elements into MV tables? NO. (Sadly) I've read the other replies saying it's application specific. And it is. Ask yourself how you're going to *program* your migration tool to know which tables should be merged into an MV file. It can't be done. And the reason is inherent in relational theory. In theory, an attribute can exist on its own. In reality, an attribute is like an adjective, with nothing to describe it doesn't exist. How is your migration tool going to work out which adjectives describe which noun, and hence which attributes belong in the same file, and which ones don't? You can guess, but chances are you're going to make *several* mistakes, which could seriously damage all the advantages MV brings. Cheers, Wol ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
Re: [U2] Migration
I assume you aren't talking about just ANY old BOM, but one with 10 or 15 levels of nesting, right ;-) Ross Ferris Stamina Software Visage Better by Design! -Original Message- From: u2-users-boun...@listserver.u2ug.org [mailto:u2-users- boun...@listserver.u2ug.org] On Behalf Of Mecki Foerthmann Sent: Thursday, 23 December 2010 8:36 AM To: U2 Users List Subject: Re: [U2] Migration Might be great for a specific web app, but just try to build a Bill Of Material with that kind of data structure. ;-( And wouldn't that just be a prime example for being application specific? On 22/12/2010 21:04, Shawn Hayes wrote: I realize there is a bit more to MV database design then just parent- child relationships. The lack of constraints is both a curse and a blessing in the MV world. However, I don't accept that you wouldn't want a product category file that holds all the product information in MV fields or order and invoice data as MV fields in a customer file. A single read sounds intriguing in a web app?!?!?!? 'We act as though comfort and luxury were the chief requirements of life, when all that we need to make us happy is something to be enthusiastic about.' ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users ___ U2-Users mailing list U2-Users@listserver.u2ug.org http://listserver.u2ug.org/mailman/listinfo/u2-users
[U2] Migration to SAP from U2 Causes Bankruptcy of Company
Reposted with a new title so more people may read this important entry from Doug Averch. Thank You Doug! = Doug's original post = We came across this article in the Denver Post a few days ago: (http://www.denverpost.com/search/ci_11446814) Jewelry retailer Shane Co. attributed its bankruptcy partly to a new inventory-management system that cost four times as much as expected and led it to overstock, according to documents filed by the Centennial-based company...The final blow to the company was a point-of-sale and inventory management system purchased from business-software giant SAP for $8 million to $10 million, which ended up costing $36 million and took three times as long to implement. In the meantime, because it did not work entirely, the system did not provide accurate inventory numbers and led to the chain being substantially overstocked in the fall of 2007. The abbreviated backstory is that the Shane Co ran software in the early 1980's on Prime Information. When Prime went out of business in 1988, they ported their system to Universe. The Shane Co spent many years developing extending those and other applications in BASIC. This software ran their business successfully for over many many years. Management made the decision to go the SAP from Universe and the result, as they say, was catastrophic. Regards, Doug Averch www.u2logic.com --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
[U2] Migration to Windows Servber 2003 on a VMWARE box
I suspect that like many of you in the past, I am being asked to get Universe up on Windows 2003 server which will actually be a virtual machine (or two) I am in contact with IBM who I am sure will be more than helpful as usual, but any feedback (off list so as not to bore everyone else) would be appreciated Its a 100 user system on 10.1.14 and aix 5.3 We are using the GCI and aix sendmail and various links across sockets and via odbc to MS SQL databases The system is pretty much 24/7 (except for the savevg's of course) Thanks in advance - Bob Witney IT Senior EXPLORE! Nelson House 55 Victoria Road Farnborough Hampshire GU14 7PA, UK [EMAIL PROTECTED] Tel: 01252 379489 Mob: 07973 451156 www.explore.co.uk __ This email has been scanned by the MessageLabs Email Security System. For more information please visit http://www.messagelabs.com/email __ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
