Organization of team development of database structures
Recently in our organization, the issue of team development (about 10 people) of a data scheme for an Oracle database has become very urgent. We worked in the old-fashioned way with the help of the notorious product of Erwin 3.5.x family and for the time being were completely satisfied with its capabilities, placing the file in a centralized version control system and blocking it as needed, thereby avoiding parallel development conflicts. But everything flows, everything changes, the team is growing, and the XXI century is in the yard, so we decided to use more modern means. Actually, below is a story about the process of translating a scheme into a new format (albeit the same manufacturer) and organizing tools for collective development and versioning support, diluted with arguments about the product in general and patterns of its use in our work in particular. Without the pitfalls, the described process did not go through, so perhaps the experience of such a transition would be useful to someone.
Story plan:
Maintaining order in the database schema is important, especially if there is a live development, the number of tables and their interrelationships grows, and no one can simply keep the whole hierarchy of structures in their head. It is clear to all, well, it was also clear to us when we started developing for Oracle. It was a long-standing business, and in favor was a design tool from the then-Platinum company called Erwin 3.5.2. This tool has been studied (by the way, is still being studied - I learned from familiar students) on computer specialties at universities and on courses for database developers. This version has received some popularity, and I must say quite justified. Being the same age as Windows'98, it is quite efficient, copes with decent-sized schemes, flexibly adjusts thanks to macros and generates the correct scripts for modern versions of Oracle, although it is designed for only version 8, copes, by the way, with reverse engeneering from the database. We used it until recently, and there were no special failures. However, there are downsides. First, for some reason, it is not at all friendly with network printers. If a network printer is configured as the default printer, the initial load of the scheme will be very slow, if the printer is connected directly to the machine or a virtual printer is used, the same scheme loads much faster. Secondly, as I mentioned, it is designed to work with Oracle 8 (and indeed the choice of database versions is not particularly large, MySQL or Postgree is not in principle), and therefore fine tuning of table structures for new versions, such as using partitions, is impossible . Third, we were unable to configure support for team development. Potentially, ModelMart support is already in this version, but no one bothered at the time (the team itself was 2 people), well, later it could not be found as a separate product. Again, a feature of the ModelMart (now called Model Manager) is the storage of the schema in the database, i.e. how old versions of this product are friends with new versions of the database - this is another question. The remaining shortcomings are not decisive, although of course they sometimes manifest themselves and are very annoying (for example, searching for a submodel by name is possible only by the first letter, and by first entry; searching for a table to add to the submodel is possible only by the name of the physical layer, whereas in reports, which are generated, sometimes only the logical level is displayed; restriction of rights is possible only at the level of access to the scheme file; there is no alignment of objects on the grid and other nuances)
There was a certain turning point, and there was an exact determination of the need to switch to the modern version of Erwin. The management even agreed to buy the product, but of course no old versions are sold. But an important prerequisite for the transition is the need to support all the old developments. Old developments are one file with the entire data scheme (approximately 20Mb and 7th year of development). We have always used Erwin purely for modeling structures, and the logic of work does not get there. Thus, it is not worth the task to deploy the base from scratch with the script generated from the program — a standard base with structures, logic and all the technical settings serves for the deployment of new instances. If we kept the logic, the problem of updating versions would have appeared much earlier, because pl / sql in the eighth version and pl / sql in 10 are two big differences. As a result, managed to delay the moment. Since we are only interested in structures and connections - we clean the schema from different “garbage” (when using reverse engeneering, the triggers associated with the table fall into the scheme, they are easy to find through the Entity Reports-> Entity / Trigger options report), download the trial from the developers website version 7.3 “to try” and we expect that the scheme will open in a new format (in fact, I exaggerate, of course, no one expected it to be easy). But no, the ER1 format of the third version of the seventh version does not see.
It is time to quarrel about the formats. Erwin 3.5.x stores the schema in a single .ER1 file format (plus backup .BK1). This is such a closed binary format, if you open it for viewing - well, the familiar letters will be, but no more. The seventh version of Erwin Data Modeler understands a certain .ER1 format, but with a note that this is the format of the fourth version, and not the third one. This is probably due to the fact that it was from the fourth version that the Platinum product was bought by Computer Associates (or CA as they are officially designated now). We have to go to the trick, because Erwin 4.1.x does not officially apply and does not find any trial versions (and certainly not to buy it). So, with a clear conscience, download the pirated version from where we can find it. In general, it is strange that for such a well-known and expensive product, CA did not provide a utility for converting between version formats. Conversion occurs in memory directly at the time of opening. Initially the file took about 20 Mb, the conversion took a couple of minutes. After this operation, the file began to weigh already 60 Mb. However, we must pay tribute, it seems to load this business faster than in the third version.
')
Then we install Erwin Data Modeler 7.3 (complete with Erwin Model Navigation 7.3), and then separately Erwin Model Manager 7.3. The installation itself does not cause difficulties, however, before the first launch of the ModelMart, it is necessary to configure the database where all the objects of the erwin scheme will be stored. The CA firm took care of this and there are two guides in the distribution kit - we are interested in the Implementation Guide (in the second, the Administrator Guide, nothing particularly interesting is by the way). It is necessary to create (we have oracle - so everything said concerns working with this DBMS) tablespace for storing the model, index tablespace, the role of the user who will install the models, the user of the model administrator (this user can also be given the role of installation) and the role for Model Manager users (by the way, you need to remember to give these users and dba). Actually, when you first start the administrative part of the Model Manager, this is what will be asked - you need to log in under the created administrator, specify the tablespace for the data and for the indexes, and wait until the program creates the necessary tables and procedures. There are no problems with initialization, but if necessary, to delete the settings you have created (it’s not necessary to experiment on the working version right away) I had an error and a total glitch, after which I couldn’t connect to the Model Manager with error messages (the same nonsense happens when attempt to upgrade the license - from trial to work, if the model has already been created in the trial). It was treated as follows - deleted all objects of the user of the model administrator and launched the MMartInit.ora script in SQL Navigator from the program folder with the error-ignoring mode. Something happened in this script, something didn’t, but after launching the Model Manager admin console, the program was able to somehow connect to the database and said that I had something, but it was damaged and could restore it. After confirming on my part, the table structure was re-re-created and everything worked. CA would have to be scolded for this moment: naturally, before buying such an expensive product, the organization wants to test the entire business process in advance, generous versions with full functionality are available directly from the manufacturer. And of course, after everything is set up and tested, the process of updating licenses for full-featured ones should not cause any problems.
A few words should be said about choosing a base for work. It is better to create a new instance, because the indexes grow for large models, and the model management operations themselves are quite resource intensive. So the most critical is the removal of the model from the library. In general, we use a database instance with some additional load besides the Model Manager, but of course this is not a production server.
So, all the preliminary settings are made. The next step is to convert the schema to the 7.x version. This process is surprisingly very long and very demanding of RAM. However, it’s just not possible to leave work at night either, because Erwin periodically reports on the results of work and asks all sorts of trivia (for example, the old scheme worked with Oracle 8, and the new one supports Oracle 10/11, hence the inevitable question from the program “Will we transfer to a new oracle, or will we choose something else?”, And so on in that spirit). So, it is necessary to be patient. Following the results of the work, a log-file of the detected errors in the scheme was formed (I didn’t have so many of them — a few duplicate objects and a foreign key invalid, which is easily repaired after reconverting). It should be noted that the validation of fresh Erwin is very serious - I found various shoals in the spirit of “varchar (50” (no closing bracket in the type definition)) that were not noticed by the earlier versions. The final scheme had the .erwin extension and expanded in size 112 Mb. The total increase is almost six times as compared with the initial size. This business is loaded at an average speed, not quickly of course, but not critical slowly.
Then begins the thing for which everything was started by and large - the transfer of the scheme for storage in the database and the organization of teamwork. To do this, open the schema file in Erwin, connect to the Model Manager as an admin user, and select the Save model option. After selecting the location of the object in the hierarchical scheme of the model libraries, the scheme is loaded into the database. Again, everything is predictable - the process is not fast. But sooner or later it will be executed, and now our scheme is already stored in the database and is ready for team development. However, the very first attempt to open any submodel from the created model (remembering to close the file from which the model was created first, otherwise it will not work) results in a hang of 30 minutes. This is certainly not an option. We connect to the database and look at the model administrator's schema (say, EADMIN, as in the manual). Tables are created, indexes are created, but statistics on tables is not collected. As a result: DBMS_STATS.GATHER_SCHEMA_STATS ('EADMIN'). Further we deal with tables. Actually the biggest are the two of them m7object and m7objectproperty. As is obvious from the name: the first one stores objects (all entities, tables, submodels in hierarchical order), the second their properties. Looking at these property tables, I noticed quite a few entries with the value “Imported from a previous version of ERwin.” In the string value field. It is possible and clean, only gently, the size will be reduced of course not at times, but still.
The initial scheme was divided into many subcircuits (subject areas in the notation of the third version), each such subcircuit can in theory be opened independently. But, blocking is possible only at the circuit level, this time. If you save and merge changes to the repository, you still have to load the whole scheme, these are two. And finally, the opening of the entire circuit takes the same time (and a little more memory) than the opening of a separate subcircuit, it is three. So you can immediately forget about the possibility of working with a separate subcircuit. Total we have about 700 mb of RAM occupied by the loaded scheme. Something until everything is bleak. However, the possibilities are certainly good - here you can find detailed merging with the repository, rollback between saved versions, and faard engineering and reverse engineering with the base, with a different scheme or file, save all settings for comparing and generating scripts right in the scheme and so on.
Minus one - the speed of work. All the manipulations with the base and settings allowed only slightly speeding up the process. Pomonitori work with the base - all requests are primitive (albeit slightly illogical), but the tables themselves are large. We tried to contact the official distributor in Russia for a consultation, identifying the apparent hypotheses of problems. As a result, we received the answer that there is no universal solution for performance problems when transferring old models to Erwin. As a result, while we are at a crossroads between good functionality and poor performance, we still continue to fight, but we are already looking at the products of competitors.
Story plan:
- Product considerations, comments on the topic of usage patterns, swearing on formats.
- Preparatory work, shamanism in the database.
- Organization of team development, performance optimization.
Product considerations, comments on the topic of usage patterns, swearing on formats.
Maintaining order in the database schema is important, especially if there is a live development, the number of tables and their interrelationships grows, and no one can simply keep the whole hierarchy of structures in their head. It is clear to all, well, it was also clear to us when we started developing for Oracle. It was a long-standing business, and in favor was a design tool from the then-Platinum company called Erwin 3.5.2. This tool has been studied (by the way, is still being studied - I learned from familiar students) on computer specialties at universities and on courses for database developers. This version has received some popularity, and I must say quite justified. Being the same age as Windows'98, it is quite efficient, copes with decent-sized schemes, flexibly adjusts thanks to macros and generates the correct scripts for modern versions of Oracle, although it is designed for only version 8, copes, by the way, with reverse engeneering from the database. We used it until recently, and there were no special failures. However, there are downsides. First, for some reason, it is not at all friendly with network printers. If a network printer is configured as the default printer, the initial load of the scheme will be very slow, if the printer is connected directly to the machine or a virtual printer is used, the same scheme loads much faster. Secondly, as I mentioned, it is designed to work with Oracle 8 (and indeed the choice of database versions is not particularly large, MySQL or Postgree is not in principle), and therefore fine tuning of table structures for new versions, such as using partitions, is impossible . Third, we were unable to configure support for team development. Potentially, ModelMart support is already in this version, but no one bothered at the time (the team itself was 2 people), well, later it could not be found as a separate product. Again, a feature of the ModelMart (now called Model Manager) is the storage of the schema in the database, i.e. how old versions of this product are friends with new versions of the database - this is another question. The remaining shortcomings are not decisive, although of course they sometimes manifest themselves and are very annoying (for example, searching for a submodel by name is possible only by the first letter, and by first entry; searching for a table to add to the submodel is possible only by the name of the physical layer, whereas in reports, which are generated, sometimes only the logical level is displayed; restriction of rights is possible only at the level of access to the scheme file; there is no alignment of objects on the grid and other nuances)
There was a certain turning point, and there was an exact determination of the need to switch to the modern version of Erwin. The management even agreed to buy the product, but of course no old versions are sold. But an important prerequisite for the transition is the need to support all the old developments. Old developments are one file with the entire data scheme (approximately 20Mb and 7th year of development). We have always used Erwin purely for modeling structures, and the logic of work does not get there. Thus, it is not worth the task to deploy the base from scratch with the script generated from the program — a standard base with structures, logic and all the technical settings serves for the deployment of new instances. If we kept the logic, the problem of updating versions would have appeared much earlier, because pl / sql in the eighth version and pl / sql in 10 are two big differences. As a result, managed to delay the moment. Since we are only interested in structures and connections - we clean the schema from different “garbage” (when using reverse engeneering, the triggers associated with the table fall into the scheme, they are easy to find through the Entity Reports-> Entity / Trigger options report), download the trial from the developers website version 7.3 “to try” and we expect that the scheme will open in a new format (in fact, I exaggerate, of course, no one expected it to be easy). But no, the ER1 format of the third version of the seventh version does not see.
It is time to quarrel about the formats. Erwin 3.5.x stores the schema in a single .ER1 file format (plus backup .BK1). This is such a closed binary format, if you open it for viewing - well, the familiar letters will be, but no more. The seventh version of Erwin Data Modeler understands a certain .ER1 format, but with a note that this is the format of the fourth version, and not the third one. This is probably due to the fact that it was from the fourth version that the Platinum product was bought by Computer Associates (or CA as they are officially designated now). We have to go to the trick, because Erwin 4.1.x does not officially apply and does not find any trial versions (and certainly not to buy it). So, with a clear conscience, download the pirated version from where we can find it. In general, it is strange that for such a well-known and expensive product, CA did not provide a utility for converting between version formats. Conversion occurs in memory directly at the time of opening. Initially the file took about 20 Mb, the conversion took a couple of minutes. After this operation, the file began to weigh already 60 Mb. However, we must pay tribute, it seems to load this business faster than in the third version.
')
Preparatory work, shamanism in the database
Then we install Erwin Data Modeler 7.3 (complete with Erwin Model Navigation 7.3), and then separately Erwin Model Manager 7.3. The installation itself does not cause difficulties, however, before the first launch of the ModelMart, it is necessary to configure the database where all the objects of the erwin scheme will be stored. The CA firm took care of this and there are two guides in the distribution kit - we are interested in the Implementation Guide (in the second, the Administrator Guide, nothing particularly interesting is by the way). It is necessary to create (we have oracle - so everything said concerns working with this DBMS) tablespace for storing the model, index tablespace, the role of the user who will install the models, the user of the model administrator (this user can also be given the role of installation) and the role for Model Manager users (by the way, you need to remember to give these users and dba). Actually, when you first start the administrative part of the Model Manager, this is what will be asked - you need to log in under the created administrator, specify the tablespace for the data and for the indexes, and wait until the program creates the necessary tables and procedures. There are no problems with initialization, but if necessary, to delete the settings you have created (it’s not necessary to experiment on the working version right away) I had an error and a total glitch, after which I couldn’t connect to the Model Manager with error messages (the same nonsense happens when attempt to upgrade the license - from trial to work, if the model has already been created in the trial). It was treated as follows - deleted all objects of the user of the model administrator and launched the MMartInit.ora script in SQL Navigator from the program folder with the error-ignoring mode. Something happened in this script, something didn’t, but after launching the Model Manager admin console, the program was able to somehow connect to the database and said that I had something, but it was damaged and could restore it. After confirming on my part, the table structure was re-re-created and everything worked. CA would have to be scolded for this moment: naturally, before buying such an expensive product, the organization wants to test the entire business process in advance, generous versions with full functionality are available directly from the manufacturer. And of course, after everything is set up and tested, the process of updating licenses for full-featured ones should not cause any problems.
A few words should be said about choosing a base for work. It is better to create a new instance, because the indexes grow for large models, and the model management operations themselves are quite resource intensive. So the most critical is the removal of the model from the library. In general, we use a database instance with some additional load besides the Model Manager, but of course this is not a production server.
So, all the preliminary settings are made. The next step is to convert the schema to the 7.x version. This process is surprisingly very long and very demanding of RAM. However, it’s just not possible to leave work at night either, because Erwin periodically reports on the results of work and asks all sorts of trivia (for example, the old scheme worked with Oracle 8, and the new one supports Oracle 10/11, hence the inevitable question from the program “Will we transfer to a new oracle, or will we choose something else?”, And so on in that spirit). So, it is necessary to be patient. Following the results of the work, a log-file of the detected errors in the scheme was formed (I didn’t have so many of them — a few duplicate objects and a foreign key invalid, which is easily repaired after reconverting). It should be noted that the validation of fresh Erwin is very serious - I found various shoals in the spirit of “varchar (50” (no closing bracket in the type definition)) that were not noticed by the earlier versions. The final scheme had the .erwin extension and expanded in size 112 Mb. The total increase is almost six times as compared with the initial size. This business is loaded at an average speed, not quickly of course, but not critical slowly.
Organization of team development, performance optimization
Then begins the thing for which everything was started by and large - the transfer of the scheme for storage in the database and the organization of teamwork. To do this, open the schema file in Erwin, connect to the Model Manager as an admin user, and select the Save model option. After selecting the location of the object in the hierarchical scheme of the model libraries, the scheme is loaded into the database. Again, everything is predictable - the process is not fast. But sooner or later it will be executed, and now our scheme is already stored in the database and is ready for team development. However, the very first attempt to open any submodel from the created model (remembering to close the file from which the model was created first, otherwise it will not work) results in a hang of 30 minutes. This is certainly not an option. We connect to the database and look at the model administrator's schema (say, EADMIN, as in the manual). Tables are created, indexes are created, but statistics on tables is not collected. As a result: DBMS_STATS.GATHER_SCHEMA_STATS ('EADMIN'). Further we deal with tables. Actually the biggest are the two of them m7object and m7objectproperty. As is obvious from the name: the first one stores objects (all entities, tables, submodels in hierarchical order), the second their properties. Looking at these property tables, I noticed quite a few entries with the value “Imported from a previous version of ERwin.” In the string value field. It is possible and clean, only gently, the size will be reduced of course not at times, but still.
The initial scheme was divided into many subcircuits (subject areas in the notation of the third version), each such subcircuit can in theory be opened independently. But, blocking is possible only at the circuit level, this time. If you save and merge changes to the repository, you still have to load the whole scheme, these are two. And finally, the opening of the entire circuit takes the same time (and a little more memory) than the opening of a separate subcircuit, it is three. So you can immediately forget about the possibility of working with a separate subcircuit. Total we have about 700 mb of RAM occupied by the loaded scheme. Something until everything is bleak. However, the possibilities are certainly good - here you can find detailed merging with the repository, rollback between saved versions, and faard engineering and reverse engineering with the base, with a different scheme or file, save all settings for comparing and generating scripts right in the scheme and so on.
Minus one - the speed of work. All the manipulations with the base and settings allowed only slightly speeding up the process. Pomonitori work with the base - all requests are primitive (albeit slightly illogical), but the tables themselves are large. We tried to contact the official distributor in Russia for a consultation, identifying the apparent hypotheses of problems. As a result, we received the answer that there is no universal solution for performance problems when transferring old models to Erwin. As a result, while we are at a crossroads between good functionality and poor performance, we still continue to fight, but we are already looking at the products of competitors.
Source: https://habr.com/ru/post/111265/
All Articles