Modeling created global temporary tables for DB2 with InfoSphere Data Architect 8.5, Part 1: Getting started

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.



25 April 2013

Also available in Chinese

Introduction

This article series is presented in two parts. This article (Part 1) describes created global temporary table CGTT modeling for the databases DB2 for z/OS version 10 (New-Function mode) and DB2 for Linux, UNIX, and Windows version 9.7, and shows how you can leverage InfoSphere Data Architect V8.5 to perform the following tasks.

  • Create a physical data model with CGTTs for DB2 for z/OS version 10 (New-Function mode) and DB2 for Linux, UNIX, and Windows version 9.7.
  • Generate a DDL with CGTT-specific syntax that you can run on the database server.

Part 2 of this series will describe how incremental changes to a created global temporary table can be compared, synced, and deployed to the database server. The following features of InfoSphere Data Architect 8.5 will be explored.

  • Reverse-engineer from the database with CGTTs to create a physical data model.
  • Modify the CGTT in the physical data model and propagate the changes to the database by running the generated delta DDL on the server.

Problem description

You are a member of the IT division in a company. The company executes projects for its customers for a price. Your team wants to develop a project-costing application that can generate project performance reports for management to aid in decision making. The following requirements have been specified for which the application must account.

  • The company can execute multiple projects for the same customer.
  • A project has a price that will be paid by the customer once the project is complete, as per the agreed-upon terms.
  • A project has a start date and end date.
  • The employees of the company are allocated to work on the projects. An employee can work on only one project at any point in time.
  • The employees of the company are paid a salary every month.
  • The expenses involved in a project fall under the following categories:
    • Salary
    • Raw material
    • Manufacturing
    • Packaging
    • Transportation

Management needs the following reports for analysis and decision making.

  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 expense for execution of the project.
    • Profit percentage – ((Price – Total cost)/Total cost)*100.
  2. Aggregated report for the financial year for completed projects.
    • Financial year and number of projects executed.
    • Revenue from projects.
    • Total expenses for execution of the projects.
    • Profit percentage – ((Revenue from projects-Total cost of projects)/Total cost of projects)*100.
  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.
    • Customer ID and customer name.
  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.

Model design

You create a simplified version of the logical data model ProjectCostModel.ldm that contains entities, attributes, and relationships that model the requirements listed in the problem description, as shown in Figure 1.

Figure 1. Simplified logical data model for the project-costing system
This figure shows the simplified logical data model for the project costing system

The PROJECT entity models information about a particular project as follows.

  • PROJECT_ID: unique identifier code for a project.
  • PROJECT_NAME: name of the project.
  • PROJECT_DESCRIPTION: description of the project.
  • PROJECT_PRICE: price of the project.
  • PROJECT_START_DATE: start date of the project.
  • PROJECT_END_DATE: end date of the project.
  • PROJECT_STATUS: indicates whether a project is active or completed.
  • CUSTOMER_ID: unique identifier code for a customer.

The CUSTOMER entity models information about a particular customer as follows.

  • CUSTOMER_ID: unique identifier code for a customer.
  • CUSTOMER_NAME: name of the customer.
  • CUSTOMER_DESCRIPTION: description of the customer.

The EMPLOYEE entity models information about a particular employee as follows.

  • EMPLOYEE_ID: unique identifier code for an employee.
  • EMPLOYEE_NAME: name of the employee.
  • EMPLOYEE_DESIGNATION: designation of the employee.

The SALARY entity models information about salary expenses as follows.

  • EMPLOYEE_ID: unique identifier code for an employee.
  • SALARY_MONTH: month for which salary is paid.
  • SALARY_YEAR: year for which salary is paid.
  • SALARY_AMOUNT: salary amount.

The PROJECT_EXPENSES entity models information about the non-salary expenses for a particular project as follows.

  • PROJECT_EXPENSES_ID: unique identifier code for a project expense.
  • PROJECT_EXPENSES_CATEGORY: category of the expense other than salary.
  • PROJECT_EXPENSES_AMOUNT: expense amount.
  • PROJECT_ID: unique identifier code for a project.

Transform the logical data model to a physical data model

You have approved the logical data model design with the business users. To model the physical storage, you must transform the logical data model into a physical data model for the DBMS that you use.

Perform the following steps to transform the model.

  1. Right-click the logical data model, then select Transform to Physical Model from the context menu, as shown in Figure 2.
    Figure 2. Transform the logical data model to a physical data model
    This figures shows the screen to transform the logical data model to a physical data model
  2. Select the Create new model option from the Target Physical Data Model page.
  3. From the Physical Data Model File page, specify a file name, then select the details of the database that you use. In Figure 3, DB2 for z/OS V10 (New-Function Mode) has been selected.
    Figure 3. Specify database, version, and location for transformation
    This figure shows how to specify database, version,and location for transformation
  4. From the Options page, specify COST_MODEL_SCHEMA in the Schema name field, as shown in Figure 4. Keep all of the other default options.
    Figure 4. Options page
    This figure shows choosing COST_MODEL_SCHEMA from the Option page
  5. From the Output page, view the transformation status, then click Finish to generate the physical data model. The physical data model ProjectCostModel.dbm is created from the logical data model.

Inspect the generated physical data model

Inspect the physical data model in the Data Project Explorer, as shown in Figure 5.

Figure 5. Viewing the physical data model in the Data Project Explorer
This figures shows you the physical data model in the Data Project Explorer

Upon inspecting the physical data model, you find the following items are true.

  1. The COST_MODEL_SCHEMA schema is created.
  2. Six tables, CUSTOMER, EMPLOYEE, EMPLOYEE_X_PROJECT, PROJECT, PROJECT_EXPENSES, and SALARY are created with the columns that were transformed from the attributes of the logical data model.

The employees have a start date and end date for a project that they are assigned to. Hence, add columns START_DATE and END_DATE of type DATE to EMPLOYEE_X_PROJECT table.

A diagram view of the physical data model is shown in Figure 6.

Figure 6. Diagram view of the physical data model
This figure shows the diagram view of the physical data model

Created global temporary table support

The reporting requirement needs data to be fetched and processed from multiple tables. Some of such aggregated and processed data is common between the reports that need to be generated. In such cases, the data is fetched, aggregated, and processed multiple times, resulting in increased response times for the generation of the reports.

This can be avoided by using created global temporary tables which will store such common aggregated and processed data that is temporarily required by multiple reports.

For the current requirement, all of the reports need the expenses under various cost categories for the project. This data can be computed once, stored in a temporary table, and reused by all the reports.

The expenses in a project can be categorized into salary and non-salary (raw material, manufacturing, packaging, and transportation) expenses.

The salary expenses for a project are computed from the data present in EMPLOYEE_X_PROJECT and SALARY tables. If an employee has worked on multiple projects in a month, then the salary is apportioned to the individual projects in proportion to the number of days that the employee has worked on a project for the month.

The non-salary expenses are stored in the PROJECT_EXPENSES table. The multiple expense items under the same cost category needs to be aggregated and stored in a temporary table.

You can see that there can be a significant improvement in response times for generation of reports by storing the following data in a temporary table.

  • Total salary expense for a project
  • Total expense incurred for a project
  • The project expenses under different cost categories for a project

You can create two created global temporary tables to hold the above data:

  1. TEMP_PROJECT_COST_SUMMARY
    • PROJECT_ID: unique identifier code for a project.
    • SALARY_COST: salary expense incurred for the project.
    • TOTAL_COST: total expense incurred for the project.
    • PROJECT_PRICE: price for the project.
  2. TEMP_PROJECT_EXPENSES
    • PROJECT_ID: unique identifier code for a project.
    • PROJECT_EXPENSES_CATEGORY: category of the expense other than salary. Example: Raw material, Manufacturing, and so on.
    • AMOUNT: Total expense amount for a project under an expense category. This is the sum total of all expenses incurred under a cost category for the project.

You can create a CGTT to hold this common data by performing the following tasks.

  1. Right-click COST_MODEL_SCHEMA in the Data Project Explorer.
  2. Click Add Data Object and select Created Global Temporary Table, as shown in Figure 7.
    Figure 7. Adding CGTT support
    This figure shows you how to add CGTT support using Created Global Temporary Table.
  3. Rename the table as TEMP_PROJECT_COST_SUMMARY.
  4. Repeat steps 1 and 2.
  5. Rename table as TEMP_PROJECT_EXPENSES.
  6. For DB2 for Linux, UNIX, and Windows, you can explicitly set the On Commit and Logging properties for CGTT, as shown in Figure 8.
    Figure 8. CGTT properties page for DB2 Linux, UNIX, and Windows
    This figure shows the CGTT properties page for DB2 Linux, UNIX, and Windows and hyow to explicitely set the properties.
  7. Add following columns to table TEMP_PROJECT_COST_SUMMARY: PROJECT_ID, SALARY_COST, TOTAL_COST, PROJECT_PRICE, as shown in Figure 9.
  8. Add following columns to table TEMP_PROJECT_EXPENSES: PROJECT_ID, PROJECT_EXPENSES_CATEGORY and AMOUNT, as shown in Figure 9.
    Figure 9. Adding columns
    This figure shows the adding of colomns.

Generate DDL

Perform the following steps to generate the DDL script to deploy the model, then share the script with the database administrator to run the script on the database server.

  1. Right-click the COST_MODEL_SCHEMA schema in the Data Project Explorer.
  2. Select Generate DDL, as shown in Figure 10.
    Figure 10. Generate DDL for the new schema
    This figure shows the selection of Generate DDL for the new schema
  3. Complete the pages of the Generate DDL wizard. Do not change the default settings of the Options and Objects pages.
  4. From the Save and Run DDL page, as shown in Figure 11 (DB2 for z/OS) and Figure 12 (DB2 for Linux, UNIX, and Windows), specify a file name. Keep the file name in mind so that you can easily share the file with a database administrator so that they can deploy your changes.
    Figure 11. Save and Run DDL page for DB2 for z/OS
    This figure shows the Save and Run DDL page for DB2 for z/OS
    Figure 12. Save and Run DDL page for DB2 for Linux, UNIX, and Windows
    This figure shows the Save and Run DDL page for DB2 for Linux, UNIX, and Windows
  5. If your organization allows you to deploy data models directly to the server, you can select the Run DDL on Server option before you click Next.
  6. From the Select Connection page, select an existing connection, as shown in Figure 13, or specify a new connection by clicking New.
    Figure 13. Selecting a connection
    This figure shows the Select Connection page for existing or new connections.
  7. Verify the options that you selected on the Summary page, then click Finish to generate the DDL.

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 folders down to the COST_MODEL_SCHEMA schema. Upon viewing the model, verify that the tables CUSTOMER, EMPLOYEE, EMPLOYEE_X_PROJECT, PROJECT, PROJECT_EXPENSES, and SALARY are under the Tables folder, and TEMP_PROJECT_COST_SUMMARY and TEMP_PROJECT_EXPENSES are under the Temporary Tables folder, as shown in Figure 14.

Figure 14. Viewing the deployed project cost model
This figure shows viewing the deployed Project Cost model after the database admin runs the DDL script

Conclusion

In this article, you learned how to model a created global temporary tables requirement for a project costing system. You learned how InfoSphere Data Architect version 8.5 can provide a convenient way to model temporary data requirements for DB2 for z/OS and DB2 for Linux, UNIX, and Windows.

With little effort, you created a physical data model having CGTT support. Then, you generated the DDL script with temporary table-specific syntax that is required to deploy those changes to the target database.

In the Part 2 of this article series, you will learn how InfoSphere Data Architect version 8.5 can be used to make incremental changes in a created global temporary table that can be compared, synced, and deployed to the 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=870627
ArticleTitle=Modeling created global temporary tables for DB2 with InfoSphere Data Architect 8.5, Part 1: Getting started
publish-date=04252013