Modeling created global temporary tables for DB2 with InfoSphere Data Architect 8.5, Part 2: Modifying an existing data model

Business applications commonly need to reuse aggregated or processed data from a set of data sources over a single series of operations. An example for this could be generation of reports for analysis and decision making. In order to support this need, some database vendors have introduced support for temporary tables that can hold such aggregated or processed data. Beginning with IBM® InfoSphere® Data Architect version 8.5, you can model created global temporary tables (CGTTs) for DB2® for z/OS® version 10 and DB2 for Linux®, UNIX®, and Windows® version 9.7. This two-part series will demonstrate how you can accelerate data modeling with CGTTs with DB2 for z/OS version 10 and DB2 for Linux, UNIX, and Windows version 9.7 by adopting InfoSphere Data Architect 8.5.

Ajith Kumar Punnakula (ajithkumar@in.ibm.com), System Software Engineer, IBM

Author Photo of Ajith PunnakulaAjith Kumar Punnakula is a System Software Engineer at the IBM lab in Bengaluru, India. He has a total of five years experience in Java, Eclipse and Database related technologies. Ajith has a masters degree in Microelectronic Systems from IISc, Bengaluru.



Rahul Jain (rahjain8@in.ibm.com), Staff Software Engineer, IBM

Photo of author Rahul JainRahul Jain is a Staff Software Engineer at the IBM lab in Bengaluru, India. He is currently part of the InfoSphere Data Architect development team. His areas of expertise includes Data modeling and Eclipse platform technologies. Rahul has a master's degree in Information Technology from IIIT, Bengaluru, India.



Balaji Kadambi (bkadambi@in.ibm.com), Advisory Software Engineer, IBM

Photo of Balaji KalambiBalaji Kadambi is an Advisory Software Engineer at the IBM lab in Bengaluru, India. He is currently part of the InfoSphere Data Architect development team. His areas of interest includes Data modeling and Databases. Balaji has over thirteen years of experience working in the commercial software industry.



02 May 2013

Introduction

In part 1 of this article series, you learned how to use InfoSphere Data Architect V8.5 to model created global temporary tables for DB2 for z/OS version 10 (New-Function mode) and DB2 for Linux, UNIX, and Windows version 9.7. In the continuation of the series, you will learn how to modify an existing data model in order to incorporate any change in requirements for the created global temporary table (CGTT) using InfoSphere Data Architect V8.5.


Problem description

Now that you have modeled a CGTT to store intermediate processed data that is reused in a single series of operation, additional requirements are defined. The management team wants to analyze the performance of the projects further to take appropriate action to improve profitability.

The management team requests the following additional data for the projects to work on employee skill-building initiatives and improve the pricing methodology for the projects:

  1. Individual project cost report for completed projects.
    • Project ID and project name.
    • Price to be paid by the customer after completion of the project.
    • The expenses under various cost categories for the project.
    • Total cost incurred for execution of the project.
    • Profit percentage – ((Price – Total cost)/Total cost)*100.
    • Additional reporting requirements:
      • The average contribution of an employee to the project revenue: (Project price/Number of employee days of effort on the project).
      • The contribution of raw material cost to the total cost as a percentage for the project.
      • The contribution of manufacturing cost to the total cost as a percentage for the project.
      • The contribution of transportation cost to the total cost as a percentage for the project.
  2. Aggregated report for the financial year for completed projects.
    • Financial year and number of projects executed.
    • Revenue from projects.
    • Total cost incurred for execution of the projects.
    • Profit percentage: ((Revenue from projects-Total cost of projects)/Total cost of projects)*100.
    • Additional reporting requirements:
      • The average contribution of an employee to the company revenue: (Company revenue/Number of employee days of effort).
      • The contribution of raw material cost to the total cost as a percentage aggregated across all the executed projects.
      • The contribution of manufacturing cost to the total cost as a percentage aggregated across all the executed projects.
      • The contribution of transportation cost to the total cost as a percentage aggregated across all the executed projects.
  3. Projects that are currently active whose cost has already exceeded the quoted price.
    • Project ID and project name.
    • Price.
    • Total cost incurred till date.
    • Cost overrun: (Expected revenue from project execution – Total cost incurred till date).
    • Customer id and customer name.
    • Additional reporting requirements:
      • The average contribution of an employee to the project revenue: (Project price/Number of employee days of effort on the project).
      • The contribution of raw material cost to the total cost as a percentage.
      • The contribution of manufacturing cost to the total cost as a percentage.
      • The contribution of transportation cost to the total cost as a percentage.
  4. Profitability report of completed projects executed for a customer.
    • Customer ID and customer name.
    • Number of projects.
    • Total revenue from executed projects.
    • Total cost incurred for executed projects.
    • Profit percent.
    • Additional reporting requirements:
      • The average contribution of an employee to the revenue from a customer.
      • The contribution of raw material cost to the total cost as a percentage for the projects executed for the customer.
      • The contribution of manufacturing cost to the total cost as a percentage for the projects executed for the customer.
      • The contribution of transportation cost to the total cost as a percentage for the projects executed for the customer.

To address this new requirement, you will need additional processed data to be stored in the created global temporary tables.

You can modify the existing CGTTs in either of the following ways:

  1. Modifying the ProjectCostModel.dbm physical data model that you developed in Part 1 of this article series.
  2. Reverse-engineering the database to create, then modify a new model.

Note: This article briefly describes how to reverse-engineer a database so that you can make changes to existing data sources.


Reverse engineer the Project Cost model from the database

Follow these steps in order to reverse-engineer the project cost database to create a new physical data model in the Data Project Explorer.

  1. From the main menu, click File > New > Physical Data Model.
  2. On the Model File page, specify a file name, select the Create from reverse engineering option, as shown in Figure 1, then click Next.
    Figure 1. Model File option page
    This figure shows the Model File option page with Create from reverse engineering option selected.
  3. From the Source page, select the Database option, as shown in Figure 2, then click Next.
    Figure 2. Source option page
    This figure shows the Source option page with Database option selected.
  4. From the Select Connection page, create a new connection by clicking the New button or select an existing connection, as shown in Figure 3, then click Next.
    Figure 3. Select Connection page
    This figure shows the Select Connection page where you can choose a new or existing connection.
  5. From the Select Objects page, select COST_MODEL_SCHEMA, as shown in Figure 4, then click Next.
    Figure 4. Select Objects page
    This figure shows the Select Objects page where COST_MODEL_SCHEMA option is selected.
  6. From the Database Elements page, ensure that all of the database element check boxes are selected, as shown in Figure 5, and then click Finish.
    Figure 5. Database Elements page
    This figure shows the Database Elements page where the check boxes for database element are selected.

Inspect the physical data model

To view the physical data model with fresh eyes, you can inspect it in the Data Project Explorer. Notice the CUSTOMER, EMPLOYEE, EMPLOYEE_X_PROJECT, PROJECT, PROJECT_EXPENSES, SALARY, TEMP_PROJECT_COST_SUMMARY, TEMP_PROJECT_EXPENSES tables under the COST_MODEL_SCHEMA schema, as shown in Figure 6.

Figure 6. Data Project Explorer view of the physical data model
This figure shows the Data Project Explorer view of the physical data model.

Modify the CGTT for new requirements

The new requirements in the report need the following additional data:

  • Average contribution of an employee to the revenue of the project.
  • Contribution of the cost from individual cost categories to the total cost of the project.

For computing the average contribution of an employee to a project, the following processed data would be needed:

  • Number of employee days effort on a project.

Since this information is at a project level, the number of employee days effort can be stored in the TEMP_PROJECT_COST_SUMMARY table under a column EMPLOYEE_DAYS_EFFORT.

For computing the contribution to the cost from individual cost categories, the cost under individual cost categories is already available in the TEMP_PROJECT_EXPENSES table.

By storing the data shown previously, you can avoid computing the number of employee days effort for a project that are needed for multiple reports, as shown in Figure 7.

Figure 7. Modified TEMP_PROJECT_COST_SUMMARY table
This figure shows the Modified TEMP_PROJECT_COST_SUMMARY table which shows the data stored to avoid computing employee days.

Propagate changes to the database

Use the Compare editor to compare and merge your changes, then generate the delta DDL.

  1. Right-click the COST_MODEL_SCHEMA schema, then click Compare With and select Original Source, as shown in Figure 8.
    Figure 8. Comparing a model with a source
    This figure shows comparing a model with a source
  2. In the Filtering Criteria window, keep the default options and click OK to compare the model to the source database.
  3. Inspect the result of the comparison in the Compare editor. You see that the TEMP_PROJECT_COST_SUMMARY table has an additional column that is not present in the database, as shown in Figure 9.
    Figure 9. Comparing a model to its source database
    This figure shows comparing a model to its source database in the Compare editor, including TEMP_PROJECT_COST_SUMMARY table.
  4. Specify that you want to merge the column differences into the source database. Select the Column rows in the Compare editor, then click the Copy from Left to Right button, as shown in Figure 10.
    Figure 10. Merging changes in the Compare editor
    This figure shows merging changes in the Compare editor, including clicking Copy from Left to Right button.

    The columns are copied to the right side of the compare editor.

  5. Click the Generate Right Delta DDL button, as shown in Figure 11.
    Figure 11. Generating deployable delta DDL in the Compare editor
    This figure shows generating deployable delta DDL in the Compare editor
  6. Save the delta DDL, as shown in Figure 12, so that you can share it with your database administrator. Your database administrator will deploy the changes for you on the database server.
    Figure 12. Save the delta DDL
    This figure shows saving the delta DDL to compare with your database administrator.

Inspect the deployed model in the database server

After the database administrator runs the DDL script, you can inspect the deployed changes in the Data Source Explorer by expanding the nodes down to the COST_MODEL_SCHEMA schema. Upon viewing the model, verify that the column EMPLOYEE_DAYS_EFFORT has been added to the TEMP_PROJECT_COST_SUMMARY table, as shown in Figure 13.

Figure 13. Viewing the deployed Project Cost model
This figure shows Viewing the deployed Project Cost model and verifying the columns have been added to the table.

Conclusion

In this article, you learned how to model changes to a CGTT. When you compared and merged changes, you generated a delta DDL script to deploy your changes to the target database. InfoSphere Data Architect V8.5 makes it easy to deploy changes to a CGTT with a simple delta DDL generator that places your changes into a simple script that runs on your database server.

Resources

Learn

Get products and technologies

  • Download a trial version of InfoSphere Data Architect 8.5.0 and learn how to create a bitemporal model efficiently.
  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=900948
ArticleTitle=Modeling created global temporary tables for DB2 with InfoSphere Data Architect 8.5, Part 2: Modifying an existing data model
publish-date=05022013