Exporting and manually running the scripts to enable data movement service

If data movement service has not been enabled during installation of a monitor model, you can enable data movement service for a monitor model by exporting the scripts and then run them manually. To do this, you must first use the WebSphere® Application Server administrative console to export the data movement service scripts to a data definition language (DDL) file and then run those scripts later.

1. Stop a monitor model before enabling data movement service. Failure to do so could result in loss of data.
2. In the WebSphere Application Server administrative console, click Applications > Monitor Models.
3. Click the version of the model for which you want to enable data movement service.
4. Click Enable Data Movement Service.
5. Click Export Enable DMS Script.
6. Save the file. This procedure produces a script that you can run manually using a command prompt. You can run this script later or from a remote location.

   After data movement service has been enabled it will be in a suspended state for the model version. Data is not copied to the reporting tables or pruned from the operational tables until the data movement service is activated for the model version. Activate the data movement service by using the Resume Data movement service option.

7. If you are running the scripts from a remote server, either move the exported files to that machine or make sure you can connect to the Monitor database from the local machine.
8. Connect to the Monitor database as a user with database privileges to create tables and users and follow one of these sets of instructions depending upon the database you are using:
   • For DB2® Universal, in a DB2 Command Window (CLP), complete the following steps:
     1. Navigate to the path containing the exported DDL file
     2. Enter DB2 CONNECT TO MONITOR USER dbuser USING dbpassword
     3. Enter db2 -td@ -vf EnableDMS_<Model> where model is the name of the model.
     4. Enter DB2 TERMINATE
   • For DB2 for z/OS running on a DB2 for z/OS server, complete the following steps:
     1. Open the EnableDMS_model_name.ddl file where model_name is the name of the model, and replace the variables listed at the top of the DDL file. These instructions are also included at the top of the exported DDL file.
     2. Save the modified EnableDMS_model.ddl file.
     3. Use the SPUFI or DSNTEP2 utility to run the script.
   • For DB2 for z/OS running on Windows, complete the following steps:
     1. Navigate to the path containing the exported DDL file.
     2. Open the EnableDMS_model_name.ddl file where model_name is the name of the model, and replace the variables listed at the top of the DDL file. These instructions are also included at the top of the exported DDL file.
     3. Save the modified EnableDMS_model.ddl file.
     4. Make a copy of the modified DDL.
     5. The stored procedures included in the exported file require special attention. Using a text editor, open the Enable Data movement service DDL, and delete all the stored procedures in the file. Delete all lines between the comments 'BEGIN DMS Stored Procedures' and 'END DMS Stored Procedures ' within the file.
     6. Connect to the Monitor database on the host, and run the Enable data movement service DDL edited in the previous step.
     7. Open the copy of Enable Data movement service DDL and locate the stored procedures starting at comment 'BEGIN DMS Stored Procedures'. Each stored procedure starts with the 'CREATE PROCEDURE' and terminates with 'END' as shown here:

        CREATE PROCEDURE ORDERITEM.SP_ORDERITEMMC_20060803000000(OUT P_NUM_PRUNED INTEGER,
        OUT P_NUM_MERGED INTEGER,
        OUT P_SQL_MSG VARCHAR(4000),
        OUT P_ROWS_FAILED INTEGER,
        OUT P_SQL_CODE INTEGER,
        OUT P_SQL_STATE CHAR(5))
            ...
            ...
        END

     8. Copy each stored procedure individually and paste each one into a new file. Save each file with the extension .sql (for example, OrderItemMMCv1.sql).
     9. Verify that your DB2 Developer Workbench 9.1 installation has been updated to fix pack 3 or later.
     10. From the DB2 Developer Workbench Database Explorer, create a new connection.
     11. In the Connection window, enter your values for the JDBC driver, database, host name, port number, user name, and password. Accept the default values for the remaining fields. For example,
         • JDBC driver: IBM DB2 Universal Database:MVS000
         • Host: MVS000.host.example.com
         • Port: 8070
         • JDBC driver class: com.ibm.db2.jcc.DB2Drive
         • Database Manager: DB2 UDB zSeries -> V8 (new-function Mode) for DB2 z/OS 8.1
     12. Test the connection by selecting Test Connection.
     13. Select OK and then Finish in the Connection window.
     14. Create a new Data Development project. Accept the default values and select the connection created earlier as the connection for this project.
     15. From the Data Project Explorer, expand the newly created project, right-click Stored Procedures, and select Import.
     16. Locate each .sql file created earlier for each stored procedure and import each one into the project.
     17. From the Data Project Explorer, right-click the stored procedure and select Deploy. A message is displayed indicating a successful deployment.
     18. The data movement service tables must be initialized before you enable the data movement service in the administrative console. There are two stored procedures for data movement service for each monitoring context. The stored procedure that begins with SI_ is the initialization procedure. In a DB2 Command Window (CLP), complete the following steps for each stored procedure beginning with SI_:
         1. Enter DB2 CONNECT TO MONITOR USER dbuser USING dbpassword
         2. Enter DB2 CALL procedure_name. For example,

            DB2 CALL ORDERITEM.SI_ORDERITEMMC_20060803000000();

         3. Enter DB2 TERMINATE
   • For Oracle, complete the following steps:
     1. Navigate to the path containing the exported DDL file
     2. Enter sqlplus dbuser/dbpassword@your_Oracle_SID @EnableDMS_modelname.ddl where model_name is the name of the model.
     3. Commit the changes to the database.
   • For Microsoft SQL Server, complete the following steps:
     1. Navigate to the path containing the exported DDL file
     2. Enter sqlcmd -U dbuser -P dbpassword -e -i EnableDMS_modelname.ddl where model_name is the name of the model.
     3. Commit the changes to the database.