English original
Modeling with IBM Rational Xde Developer for Java for DB2 UDB Database
Suita Gupta
(Suitag@my.ibm.com), Developer Technical Support, IBM
Nanda Pilaka
(Nandap@us.ibm.com), Developer Technical Support, IBM 2004 June
Content introduction Before starting, the database and table space modeling table and its relationship view stored procedure trigger Based on data model generation DDL script connection database reverse engineering comparison and synchronization mode end language
Introduction
Everyone participating in the database application development will follow a certain method. The way you choose will help you analyze your needs and develop and deliver the final product. The most important part of this method is to design and modeling business-based databases. In this article, I will talk about the use of "Rational® Xde Developer For Java", specifically designed and modeling "DB2® Universal Database"!
Unified Modeling Language (UML) is a graphical representation for standard modeling of business and software application requirements. In the past few years, UML has been widely used in software development of object-oriented. In addition to applications to object modeling, UML is also suitable for designing and establishing a data storage model for your application. Basically, data modeling includes analyzing customer needs to develop a customer's data demand model, and then build a database as a blueprint or architecture for a blueprint or architecture.
This article will show you how easy it is based on a familiar DB2 Sample database to generate a database model. At the end of the article, you will see how to model some of the data objects for the DB2 Sample database. We will also show you how to generate DDL scripts from the completed model, and how to use these scripts to create the final DB2 database.
We will model using Rational Xde Developer for Java (version: 2003.06.00) and IBM DB2 Universal Database V8.1. Rational XDE supports all DB2 UDB versions starting from V5.2 from the ANSI SQL 92 standard and DB2 for OS / 390® 5.x, 6.x, 7.x data modeling. Rational XDE can also be used to model Oracle, Sybase, and SQL Server database.
The advantages of data modeling data modeling have many advantages, which can benefit data designers and administrators in the development team and the programmers involved.
Database designers can use Rational XDE and other tools to establish and visualize database models to ensure database rules, add different database objects, and set up relationships. The database administrator can create a model for an existing database, or can generate DDL from the model and create a database for a particular implementation. The data model helps application developers better understand the overall architecture of the application, although they do not need to know the details of the database architecture. They can build a model model with a data model, such as generated by the table structure. The data model is also very useful for end users, verifying if all data needs have been accurately captured.
If all models in the project are done with UML, the data model will completely match the rest of the system design and help the entire team seamlessly work.
before the start
Before you start creating a database model, let's take a look at a Screen snapshot of the Rational XDE and we will use the menu and options for the establishment of the database model, as shown in Figure 1:
Figure 1. Starting from Rational XDE We need to create a project for the data model. Click File -> New -> Project. Select Create Data Modeling Project and name "Data Model". Once the project is created, you can see it on the Model Explorer pane, as shown in Figure 2 below.
Among them, two models have been created for you - a physical data model and a logical data model. Data architects and database designers usually use logical data models to capture data requirements in the form of a specific database implementation. The logical model is a good tool for communicating with the business risk bearer (Stakeholder) to ensure clear capture of data requirements, as it does not involve details of a particular database. Database designers typically refine logic model into one or more "paradigms" to eliminate data repeat and improve the quality of the final design. Once the accuracy of logical data needs is full, you can convert the logical model to a physical data model to match the model to a specific target database. The physical data model can then be improved and optimized for a particular database system or application.
The use of logical data models is not discussed in this article. Next, we will pay attention to the database design at the physical data model hierarchy.
Figure 2. Model Explorer
Modeling for databases and tablespace
First, we will create a database model. As you will see in Figure 3 below, the database is the maximum element that can be modulated and used in the data model to use UML symbol << Database >>. Rational XDE allows you to select any DBMS it supports the implementation of the data model. In this article, we chose DB2 UDB V8 to demonstrate the process of modeling data modeling.
Create a database component in Model Explorer, right-click Main -> Add Data Modeler -> Database.
Figure 3. Creating a database model
Then, click the newly created database component in Model Explorer, enter a name. We use Sampledb as the unique name of our database.
Right-click Sampledb in Model Explorer and click Data Modeler -> Open Specification.
Figure 4. Open Specification
In the Database Spectation dialog, select IBM DB2 8.x as the target database in the Database list.
Figure 5. Select the target database
Click OK and drag and drop the database component to the Diagram view.
Rational XDE allows you to create a tablespace model and assign the table to this table space. We will see how to build a table space model named Sample_TBSP and have allocated a container. Note that the container path you specified in the following steps should already exist.
Before further continue, you can select the logical data model by right-clicking Logical Data Model in Model Explorer. You can save your model with your hotkey Ctrl S at any time.
Create a table space in Model Explorer, right-click the database, click Add Data Modeler -> TableSpace.
Figure 6. Add table space
In Model Explorer, right-click on the new tablespace and click Data Modeler -> Open Specification. In the General tab, enter the name of the table space, such as SAMPLE_TBSP. You can also specify additional details, such as the type of tablespaces, expansion block size, prefetch size, page size, and buffer pool name.
Figure 7. Specific designation of table space
After completing, click OK and drag and drop this tablespace component to the Diagram view. Once you have established a database model and a table space model, Rational XDE automatically creates dependencies between databases and tablespaces, as shown in Figure 8: Figure 8. Dependency
Create a container In Model Explorer, right-click on a table space and click Data Modeler -> Open Specification. In the Container tab, click the New icon and enter the path name of the new container.
Figure 9. Defining the container
Checkpoint: Your model should now be shown in Figure 10:
Figure 10. Model at this time
Table and its relationship
We will model them for both tables in the original Sample database (Employee table and DEPARTMENT table). We will also model the reference integrity relationship between the two tables.
Steps to create a table component:
In Model Explorer, right-click the data model, and then click Add Data Modeler -> TABLE. In the General tab of the Table Specification dialog, specify the table name and add the column of the table to the Columns tab. You can also specify the primary key for the table. Figure 11. Add a table to the model Click OK, you will see this table component in Model Explorer. Drag and drop it on the Diagram view.
We will be
Employee table and
The DEPARTMENT table is implemented above. for
EMPLOYEE table, we will omit ""
Creation of the WorkDept "column.
Employee table and
When the reference integrity between the DEPARTMENT table is automatically created.
Now, we will establish the reference integrity between the Employee table (foreign key: workdept) and the Departments table (primary key: deptno). You can do it by creating the "identifying" relationship between these two tables:
Select the Identifying Relationship option from the Data Modeler list of the toolbox, then first click the parent table DEPARTMENT component on the Diagram view, and then click the sub-table Employee component. Figure 12. Setting Reference Integrity In the Relationship Spectification dialog, you can specify the child role and the parent role. Figure 13. Specifies the specific designation of the relationship In the Migrated Keys tab of the RELATIONSHIP SPECification dialog, you can also modify the foreign key column name in the Employee table: Figure 14. Migrated Keys
You will see a new column in the Employee table. In the Column tab of the Table Specification dialog, you can modify the location of "WorkDept" column in the Employee table, as shown in Figure 15 below:
Figure 15. Table Specification dialog
Your model should be similar to Figure 16 below:
Figure 16. Model
view
Rational XDE allows you to create a view based on a table or SQL query. These queries may include WHERE, Group BY or Order By clause. You can also specify a SQL query manually, or you can select each column for the view in the View Specification dialog.
The following is the step of creating view components:
In MODEL Explorer, right-click the model and click Add Data Modeler -> View. A new view will be displayed in Model Explorer. Right click on this new view and click Data Modeler> Open Specification. This will open the View Specification dialog. In the General tab, enter "EMP_VIEW" as the view name in the Name box. Click OK and drag and drop the view component to the Diagram view. Specify columns for this view:
Select View Dependency from the Data Modeler list of the toolbox, then first click on the View component (EMP_VIEW) on the Diagram (DIAGRAM), then click the Source Table Component (Employee Table). Figure 17. Creating all columns in the source table will populate them into your view. At this point, you can specify custom SQL queries in the "User Defined" check box in the General tab of the View Specification dialog box: Figure 18. Specifies the specific specification of the view and then in the SQL tab of the same dialog Enter the custom SQL query. Click OK after completion. Figure 19. Specific designation of the view
Stored procedure
Rational XDE also allows you to build a model of stored procedures for the database. The supported DBMs is:
IBM DB2 UDB 5.2, 6.1, 7.0 and 8.0 IBM DB2 MVS 5.x, 6.x, and 7.x Oracle 7.3, 8.x, and 9i for Windows NT Microsoft SQL Server 6.5, 7.0 and 2000 Sybase Adaptive Server 12.x
In Rational XDE, the term "stored procedure" includes a conventional stored procedure (returns multiple results) and storage functions (return scalar values). Both stored procedures can be defined and modeling with a corresponding parameter and an action body.
However, please keep in mind that the database and stored procedures must have a realization relationship to execute DDLs for the target database. In Rational XDE, the stored procedure is packet in the stored procedure container. Before generating DDLs for the model, you need to assign the table, stored procedures, and views to the database by implementing relationships.
Steps to create a stored procedure component:
In the Model Explorer window, right click on Main. Select Add Data Modeler and click Stored ProCedure Container. This will create a stored procedure container that is default "ProcedureContainer1" in the data model. You can use the container default name or modify it via the Properties window located below the Model Explorer window. Right-click Store Process Container "ProcedureContainer1", then select Add Data Modeler and click Stored Procedure. This will add a stored procedure container that is "procedure1". Similarly, you can use this default name, if you want, you can also provide another name through the Properties window below the Model Explorer window. You must also add a "database implementation" relationship from the database to the stored procedure to generate DDL for the stored procedure. To add the Database Implementation relationship, click Data Modeler in the Toolbox, and roll down the list to "Database Realization", click Select it. Then click on the database component, then click the stored procedure container component to create a realization relationship. The data model will be shown in Figure 20: Figure 20. Data model showing the relationship is displayed at this time or later, you can select Data Modeler and click Open Specification by right-clicking on the stored procedure in Model Explorer. The name and signature of the process (parameters, etc.). This will open the Stored Procedure Spectation dialog. In this dialog, you can modify the process name, write language, etc. through the General tab, add parameters through the Parameters tab, and finally specify an action through the ACTION Body tab, as shown in Figure 21: Figure 21. Storage Specifiable designation
trigger
XDE allows you to create user-defined triggers to implement business rules in the database. Modeling for trigger:
Right-click the desired table in Model Explorer. Select Data Modeler and click Open Specification to open the specified specified by this table. In the Spectation dialog, click the Triggers tab, and then click New to create a new trigger model for the table. You can create an "instator" or "before" trigger, and create an action for the trigger. The following figure 22 illustrates how it is performed:
Figure 22. Creating a trigger
Data model generation DDL script
Rational XDE provides a Forward Engineering Wizard to generate a data definition language (DDL) script for specified components in the entire database or data model. The DDL generated by Rational XDE follows the ANSI SQL 92 standard. XDE also allows you to perform the generated DDL to the target database, and the target database is specified before starting the data model project. Therefore, make sure you have been properly connected to the target database. At the same time, all database components in this data model must be connected to the relationship clarified in the previous section to connect the database component. If it does not work for any components, then DDL will not be generated for a particular component.
To generate DDL scripts: In Model Explorer, right click on an existing database, pack, or table, and then click Data Modeler> Forward Engineer. This will open the Forward Engineering Wizard dialog for data modeling. Then follow the instructions in the wizard. The following figure 23 is a screen snapshot that generates DDL through the Forward Engineering wizard to the entire database: Figure 23. Forward Engineering Wizard
Connect to the database
Rational XDE allows you to connect to a DBMS and perform the generated DDL to create a database object. The supported DBMs is:
IBM DB2 UDB 5.2, 6.1, 7.0 and 8.0 IBM DB2 MVS 5.x, 6.x and 7.x Oracle 7.3, 8.x and 9i for Windows NT Microsoft SQL Server 6.5, 7.0 and 2000 Sybase Adaptive Server 12.x Rational XDE supports the following drivers to connect to the database system:
IBM DB2 APP Driver (ODBC) IBM DB2 UDB JDBC Driver Oracle OLE DB Provider Oracle Thin JDBC Driver OLE DB Provider MS OLE DB for ODBC MS OLE DB for Oracle (MDAC) MS SQL OLE DB Provider Sybase ASE ODBC Driver for connection to the target database, You must configure it into an ODBC data source and provide correct access information to connect it. You can configure the database as a system ODBC data source, or configured as a user ODBC data source. You can also establish a connection through the Forward Engineering or Reverse Engineering wizard, or in comparison and synchronization (Compare and Sync) mode [rlk1]. Please also note that if you choose to connect the database, you may need to install the runtime client (for DB2 UDB) or equivalent client components (for other DBMS) for the selected database system. For more information on this, please see the Rational XDE Help. The connection to the target database can be tested, for example, in the Forward Engineering wizard, in the Execute check box, as shown in Figure 24 below:
Figure 24. Connecting the database
Reverse Engineering
We have already introduced forward engineering. With Reverse Engineering, we can establish models for existing databases. This will allow us to modify the model, such as adding tables, stored procedures, triggers, etc., and then perform forward engineer by generating and executing DDL modified models.
Reverse engineering for databases:
In Model Explorer, right-click on the data model and click Data Modeler -> Reverse Engineer. This will open the Reverse Engineering Wizard. Select the database and all elements contained in the reverse engineering process according to the instructions in the wizard.
Comparison and synchronization mode
Comparison and synchronization in Rational XDE (Compare and Sync) feature is used to ensure that the data model is consistent with the generated DDL or target database. Compare and synchronization does not include the relationship between the map and the comparison model. Before comparing data models and databases, verify that you have correctly connected databases and log in correctly, passwords correctly. Compare the data model with the DDL file or database to synchronize
In Model Explorer or diagram, right click on an existing database, then click Data Modeler> Compare and Sync. This will open the Database Synchronization Wizard of the data model. Perform in accordance with the instructions in the wizard. With regard to comparison and synchronization, remember the following points:
If the data model generated by the reverse engineering is compared to DDL, you must reset the Owner / Schema property to the model name of the reverse engineering. You must complete this setting for each data model element contained in the comparison. You should assign all elements that need to be included in comparison and synchronization to the data model database. In the comparison process, XDE ignores all elements that are not assigned to the data model database. Your data model database must use the same name and destination database as the compared DDL or DBMS database. If you compare the views in your data model and the view in DDL, DDL must use a fully qualified name, especially in the SQL statement of the view. If the DDL does not use a fully qualified name, then these views look different in the comparison process, and you must manually view the SQL statement of each view to find true syntax.
Conclude
Finally, Figure 25 below shows our test project "Data Model1" current database model.
Figure 25. Database model
Finally, we all overview the process of using Rational XDE for database modeling, but there is no discussion too many details so as not to allow primary users unbearable. This simple example has already opened a head, you can start using this tool to meet your own database design and modeling requirements. To get more information for this product, see the online help from the Help menu.
note
The Forward Engineering Wizard is not inserted or generated in the DDL file, or generates a database DDL (Create Database Command). This means that the target database needs to be manually created before performing DDL scripts for the target database. The Rational XDE version used herein is 2003.06.00. When establishing a stored procedure model, you may find that if a moving body is specified in the Action Body tab specified by the stored procedure, there is no such action in the DDL of the stored procedure. This defect has been repaired in Service Release 2 (FixPack 2) released on April 9, 2004. For more information on the current revision, please see www.rational.com. When the operating body is specified for the DB2 stored procedure, a non-conventional SQL statement termination character, such as "@", etc., because the semicolon is used to terminate the stored procedure operation statement in the storage process body. However, when creating a stored procedure DDL through the Forward Engineering Wizard, a semicolon will be generated after "@", so "@" is not required, otherwise the SQL compiler of DB2 will make an error. You may need to edit the generated DDL script to ensure that the statement termination characters used are correct.
Page
About author
Suita Gupta is a Technical Support Representative of the WebSphere Compete Center team. She has DB2 certification in application development and management. Before the WebSphere supports team, she studies the development and management issues of various DB2 applications in the Developer Relations DB2 Technical Support team. You can contact Suita via suitag@my.ibm.com. Nanda Pilaka is a software engineer for Texas Dallas Content Manager Business Partner Support Group. He has DB2 certification in application development and management. Before the Content Manager supported team, he studied the development and management issues of various DB2 applications in the IBM Dallas's Developer Relations DB2 Technical Support group. You can contact NANDA via nandap@us.ibm.com.
