Global temporary table modeling with Infosphere Data Architect 8.5 for DB2 (i) Getting Started

Brief introduction

This series of articles is made up of two parts. This article (part 1th) describes how to create a global temporary table CGTT model for database DB2 for z/Os 10 (new feature mode) and DB2 for Linux, UNIX, and Windows 9.7, and how to use Infosphere Data A Rchitect V8.5 performs the following tasks.

Create a physical data model using CGTT for the DB2 for z/Os 10 (new feature mode) and DB2 for Linux, UNIX, and Windows 9.7.

Generates a DDL using the syntax specific to CGTT that can be run on the database server.

Part 2nd of this series describes how to compare and synchronize incremental changes to the global temporary tables that have been created and deploy them to the database server. This section will explore the following characteristics of Infosphere Data Architect 8.5.

Use CGTT to reverse-design the database to create a physical data model.

Modify the CGTT under the physical data model to propagate the changes to the database by running the generated Delta DDL on the server.

Problem description

If you are a company IT department employee. Companies spend money to execute projects for their customers. Your team needs to develop a project cost control application that can generate project execution reports to help management make decisions. The following requirements must be considered by the application.

An enterprise can perform multiple projects for the same customer.

In accordance with the agreed terms, after the completion of the project, the customer will have to pay a certain fee.

The project should have a start date and an end date.

The enterprise employee must be assigned to a specific project. Employees can only work on one project at any point in time.

Corporate employees need monthly pay.

The project cost is divided into several categories:

Salary

Raw materials

Manufacturing

Packaging

Transport

Managers need to use the following reports for analysis and decision making

A separate project cost report for the completed project.

Project ID and project name.

The cost of completing the project is paid by the customer.

Cost of various projects.

Total cost of project execution.

Profit Margin – ((Price-Total cost)/gross cost) x100.

Fiscal year consolidated report for completed projects

Financial year and number of projects executed.

Project income.

Total cost of project implementation

Profit Margin – (Project revenue-project Total cost)/Total project cost (x100).

Items that are currently running that cost has exceeded the quote.

Project ID and project name.

Price.

Total cost that has been incurred at this time.

Cost overruns.

Customer ID and customer name.

Completed project profitability report for the customer

Customer ID and customer name.

Project number.

Total revenue from the execution of the project.

The total cost of executing the project that has occurred.

Profit margins.

Model design

You can create a simplified version of the logical data Model PROJECTCOSTMODEL.LDM, which contains the character entities, attributes, and relationships that are listed in the modeling problem description, as shown in Figure 1.

Figure 1. Simplified logical data Model of project cost control system