Installing the PowerDesigner MetaWorksâ„¢ Repository in IBM DB2 for OS/390
- DBMS and Repository Connections
- Building the Repository in DB2 for OS/390
- Dropping the Repository in DB2 for OS/390
The PowerDesigner MetaWorks Repository is a relational database that can be created in a variety of database management systems (DBMSs). Creating it in IBM's DB2 for OS/390 requires adjustments to be made to the installation script automatically generated by PowerDesigner. This document describes the process of obtaining and modifying the installation script, and one way to run it to create the Repository database. The procedure described here applies to IBM DB2 for OS/390 Version 5 and IBM DB2 UDB for OS/390 Version 6 and Version 7.
PowerDesigner 8.0 with the MetaWorks option must be installed.
You will also need a working ODBC connection to your mainframe DB2 system. There are a number of ways to accomplish this, and they do not depend on PowerDesigner. Using Sybase OpenClient on your PC and Sybase DirectConnect as a data access server is one way. Another way is to use DB2 client software on your PC and go through a DB2 server with Distributed Database Connection Services (DDCS) under Distributed Relational Database Architecture (DRDA). Sybase also provides a gateway-less solution for mainframe connectivity.
Before attempting to perform the procedure described in this document, you should be familiar with at least Chapters 1 through 3 of the PowerDesigner Repository User's Guide, which provide an introduction to the Repository and how to connect to, disconnect from, and initialize (build the Repository database structure) the Repository.
Connecting to the DBMS Server
Chapter 3 of the Repository User's Guide, under the topic "Repository Initialization," says: "When you connect for the first time to a database, you initialize the repository and create the tables." In a DBMS like Sybase Adaptive Server Enterprise (ASE), this means you have, outside of PowerDesigner, issued commands to create an artifact called a database associated with certain storage resources. It also means that you have made an ODBC data source that specifies this database and that any commands issued through the ODBC interface will be executed primarily in the context of this database. If you issue a simple "create table" command, a table will be created in the specified database.
A DBMS like DB2 UDB Version 6 for OS/390 does not allow you to create a database before creating objects in it; rather, when objects like tables are created, a database name is provided as an attribute that serves to group those that logically belong together. Although the ODBC data source you define may include specifying a database name, commands used to create objects are executed in the context of the server, and are restricted to the context of a single database only if database name qualifiers are included in the data definition language (DDL) being executed. If application database name qualifiers are not included in create table statements run against DB2 for OS/390, tables are created by default in a system database, DSNDB04. This is undesirable since DSNDB04 is reserved for DB2 metadata -- for system use. Many installations protect this database and do not allow users to create tables in it.
Initializing the Repository in DB2 for OS/390
Since PowerDesigner supports many different DBMSs, it generates a DDL script for initializing the Repository that is not specific to any DBMS. As noted above, it is necessary for DB2 for OS/390 to specify, at minimum, a database name for each create table statement in the script. It may also be necessary or desirable to include other options in the script specifying storage resources and other operating parameters. Consequently, PowerDesigner's default DDL script must be modified before it is run against the DB2 server. The default PowerDesigner process for generating and running the Repository initialization script is not sufficient for DB2 for OS/390; additional steps must be taken. The remainder of this document describes those steps.
The last topic in this document notes similar considerations for dropping the Repository, should that become necessary.
Talk to your DB2 database administrator (DBA) to determine exactly which options you need to specify for your DB2 installation when you create tables.
It is not necessary to have a PowerDesigner model open for this procedure. Click on the Repository tab sheet of the PowerDesigner 8 Object Browser to bring it forward. The node at the top will indicate when a connection has been established to your Repository database, after it has been initialized.
1. Create a Repository Definition for your DB2 server
A Repository Definition is a named resource in PowerDesigner 8 that you create to make it easy to connect PowerDesigner with your Repository database. It includes your ODBC Data Source Name (DSN) that navigates from your PC to mainframe DB2, as referred to above under "Requirements". Follow the instructions in Chapter 3 of the Repository User's Guide for creating a Repository Definition with the correct parameters and testing it.
2. Connect using your Repository Definition
Establish a connection between PowerDesigner and your DB2 server using the Connect option on the Repository menu, as described in the Repository User's Guide under the topic "Connecting to a repository".
When the connection is first attempted where a MetaWorks Repository does not already exist, an error message window will be displayed noting that table PMUSER under your authorization ID is an undefined name. For example:
This is normal. OK the window to continue.
3. Confirm Repository installation
Next, a message window will be posted confirming that you want to install the Repository in the database to which ODBC has connected.
4. Copy the generated DDL script
The Repository initialization DDL script will be generated and posted in another window.
In the Repository Installation Script window, right-click in the text space and select "Select All" from the context menu. The entire contents of the window will be highlighted. Press Control-C to copy to the Windows clipboard.
5. Edit the Repository Installation Script
Paste the DDL script copied from the Repository Installation Script window into the text editor of your choice. Add the options that are needed to properly create tables at your DB2 site. In the following illustration, the clause "IN DATABASE METAWKS" was added to each create table statement. This associates database name METAWKS with each of the tables being created, and implies that tablespaces named the same as the tables will be created, also associated with database name METAWKS. (This may not be appropriate for your site; explicit tablespace name specification may be required. See the IBM DB2 UDB for OS/390 V6 SQL Reference manual for more information on creation DDL options.)
Save the edited DDL script file.
6. Run the modified script to create the Repository
The edited script file must be run against the DB2 server to initialize the Repository. This can be done independently of PowerDesigner. There may be a preferred way of executing SQL scripts at your installation -- a particular command line interpreter or batch facility you are used to using. Use whatever method is appropriate for you.
If you like, you can use PowerDesigner's SQL interpreter to run the script. With a PDM open in the workspace, the Execute SQL option on the Database menu can be selected to start an ODBC connection to the server and run your script, after pasting it into the interpreter window. The remainder of this topic describes this procedure.
A. Open a new PDM and choose the type of DBMS to which you will be connecting. Then select Execute SQL from the Database menu.
B. The Connect to an ODBC Data Source window will be posted. Select your DB2 server ODBC DSN, enter your mainframe login and password, and click the Connect button.
C. Once the ODBC connection has been established, the Execute Query window will be displayed. This is where you will paste and then execute your modified Repository initialization script.
D. Return to the text editor where you modified the default Repository initialization script PowerDesigner generated (see step 5 above). Open the modified script if it is not already open. Do a "Select All" and then copy the entire script to the Windows clipboard. Paste the script in PowerDesigner's Execute Query window.
E. Using the scroll bar, inspect the script to make sure it is all there and contains the modifications you made. When you are satisfied that it is ready to run, click the Execute button. Tracking messages are displayed in the bottom portion of the Execute Query window.
F. Following successful execution of the Repository initialization script, you can close the Execute Query window, and close the ODBC connection from PowerDesigner to DB2 by selecting Disconnect from the Database menu.
7. Connect to the Repository and verify your connection
Once Repository initialization has been completed, the Repository is ready to use. No message windows will be displayed when you successfully connect to the Repository from PowerDesigner. The hourglass cursor will be displayed during the process of connection. The cursor will return to normal when the connection has been made. To verify that you are connected, you can run the Connection Information option under the Repository menu.
8. Consolidate and extract a test model
Test your Repository installation by consolidating and extracting a test model -- for example, project.pdm in the Examples folder in your PowerDesigner 8 install path. Follow the instructions in Chapters 6 and 7 of the Repository User's Guide. After successfully consolidating and extracting the test model, you can remove it from the Repository. See the topic "Deleting a document version" in Chapter 8 of the Repository User's Guide.
At a certain point it may become necessary to remove the MetaWorks Repository from your mainframe DB2 server. The Drop Repository Database option on the Repository menu is provided for this purpose.
If you execute the Drop Repository Database menu option, the following confirmation window is displayed before actually performing the removal of the Repository database objects:
Despite the way this option is named, it does not actually perform a "drop database" command. Instead, it runs an internal script that performs a "drop table" on each Repository table. Depending on how you created your Repository database in DB2, this may be sufficient to remove all the Repository database objects from your server. For example, if you added only the IN DATABASE clause (for example, IN DATABASE METAWKS was shown in the illustrations above) to PowerDesigner's default initialization script, DB2 implicitly created tablespaces named the same as the tables when the script was executed. In this case, a drop table command will remove the table and the tablespace, in addition to indexes and other objects dependent on the table.
However, if you explicitly added tablespaces to your Repository initialization script, a simple series of drop table statements may not be sufficient to clean up the server. In that case, you can create a customized script to drop all the necessary objects. Using your Repository ODBC data source, reverse engineer the Repository to obtain an up-to-date list of Repository tables and tablespaces. Build a DDL script to drop these, making sure to specify the correct authorization ID, database name, and any other necessary qualifiers. Check the script with your DB2 DBA before executing it.