Patterns: Information Aggregation and Data Integration with DB2 Information Integrator

242 Patterns: Information Aggregation and Data Integration with DB2 Information Integrator

alter table credit_account

add constraint cc_acc_fk1 foreign key (cc_nbr, p_cardholder_id)

references credit_card (cc_nbr, cardholder_id);

alter table credit_account

add constraint cc_acc_fk2 foreign key (cc_nbr, s_cardholder_id)

references credit_card (cc_nbr, cardholder_id);

-------------------------------------------------------------------

create table product (

prod_id integer not null,

prod_desc char(30),

type char(20) not null,

purchase_rate decimal(3,1),

cash_adv_rate decimal(3,1),

rewards char(1),

annual_feestatus decimal(3),

created_dt date not null,

created_by char(10) not null,

primary key (prod_id)

) in userspace1;

----------------------------------------------------------------------

create table transaction (

cc_nbr bigint not null,

cardholder_id integer not null,

trans_category char(10),

trans_type char(10),

vendor char(10),

amount decimal(15,2) not null,

referencechar(20),

last_trans_ts timestamp not null,

created_by char(10),

primary key (cc_nbr, cardholder_id, last_trans_ts)

) in userspace1;

alter table transaction

ADD CONSTRAINT CC_TRAN_FK FOREIGN KEY (cc_nbr, cardholder_id)

REFERENCES credit_card (cc_nbr, cardholder_id);

We loaded some representative data in these tables, which is not included here.

Note that the data in operational systems is maintained by its business

transactions.

Rewards

This system is associated with the credit card system and supports the

accumulation of hotel points and airline miles with partner companies for

Appendix C. Data models, table/file definitions, et al used in the DFC Customer Insight business solution

purchases made with the credit card. This is a rewards application hosted on an

IBM z/OS platform using VSAM files. There are four VSAM KSDS files

associated with the rewards system, namely, Tracking, Offering Partners,

Transactions, and a Journal that maintained an audit trail of all changes occurring

in the application.

Figure C-3 through Figure C-6 on page 245 describe the fields defined in the

various VSAM files, while Example C-3 on page 245 describes the rewards

lookup XML file.

Figure C-3 Rewards Tracking VSAM file field attributes

Note: There is also a rewards lookup XML file stored on an IBM AIX platform

that is part of this rewards application. This scenario was contrived to

demonstrate DB2 Information Integrator’s XML access capabilities, and does

not reflect a real-world environment.

FIELD OFFSET LENGTH DATA

TYPE

Description

CCNUM 0 16 PIC CREDIT CARD NUMBER

LAST_NAME 16 30 CHAR LAST NAME

FIRST_NAME 46 30 CHAR FIRST NAME

PARTNER_ID_NUM 76 12 CHAR AIRLINE FREQUENT FLYER NUMBER

REWARDID 88 10 PIC REWARD PRODUCT ID CODE

POINTBALANCE 98 8 PIC POINTS BALANCE AS OF LAST MONTHLY PROCESSING

PROCESS_DATE 106 10 CHAR DATE OF LAST MONTHLY PROCESSING

CREATED_DATE 116 10 CHAR DATE MM-DD-YYYY

CREATED_BY 126 10 CHAR USERID OF PERSON CREATING ENTRY

244 Patterns: Information Aggregation and Data Integration with DB2 Information Integrator

Figure C-4 Rewards Offering Partners VSAM file field attributes

Figure C-5 Rewards Transactions VSAM file field attributes

FIELD OFFSET LENGTH DATA

TYPE

Description

REWARDID 0 10 PIC REWARD PRODUCT ID CODE

PARTNER_NAME 10 30 CHAR NAME OF REWARDS PARTNER

CLASSIFICATION 40 10 CHAR AIRLINE, HOTEL

ACCUMULATION_TYPE 50 6 CHAR POINTS, MILES

EFFECTIVE_FROM 56 10 CHAR DATE THIS REWARD IS AVAILABLE FOR USE (MM-DD-YYYY)

EFFECTIVE_TO 66 10 CHAR DATE THIS REWARD IS WITHDRAWN - 99-99-9999 IS

DEFAULT

CREATED_DATE 76 10 CHAR DATE MM-DD-YYYY

CREATED_BY 86 10 N USERID OF PERSON CREATING ENTRY

FIELD OFFSET LENGTH DATA

TYPE

Description

CCNUM 0 16 PIC CREDIT CARD NUMBER

TIMESTAMP 16 26 CHAR DB2 FORMAT TIMESTAMP

YYYY-MM-DD.HH:MM:SS.TTTTTT

AMOUNT 42 10 PIC POINTS ASSOCIATED WITH TRANSACTION

TRAN_TYPE 52 1 CHAR TRANSACTION TYPE - A (ADD), D (DELETE)

REASON_CODE 53 4 CHAR PURC (PURCHASE), RETN (RETURN), REST (RESTORE - ADD

BACK)

REWARD_ID 57 10 PIC REWARD IDENTIFIER

SEQ 67 8 PIC PROCESSING SEQUENCE NUMBER

Appendix C. Data models, table/file definitions, et al used in the DFC Customer Insight business solution

Figure C-6 Rewards Journal VSAM file field attributes

Example: C-3 Rewards Lookup XML file

<?xml version="1.0" encoding="UTF-8"?>

<!-- edited with XMLSPY v2004 rel. 3 U (http://www.xmlspy.com) by Bill Mathews

(IBM) -->

<!--Druid Bank Credit Card Rewards-->

<Reward xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"

xsi:noNamespaceSch

emaLocation="C:\MyShares\RewardsXML\Rewards.xsd">

emaLocation="C:\MyShares\RewardsXML\Rewards.xsd">

<id>100001</id>

<Short_Name>Travel2004</Short_Name>

<Description>Druid Bank Credit Card Travel Purchase

Awards</Description>

<Sponser>DruidBankTravel.com</Sponser>

<Card_Type>VISA</Card_Type>

<Affiliation>

<Name>We Get You There Airlines</Name>

<Points>3</Points>

<per_unit>1</per_unit>

<unit_type>Dollar</unit_type>

<Marketing_Msg>Use your Druid Bank Credit Card to purchase your

"We Get You There Airlines" tickets at DruidBankTravel.com and receive 3 points

for every dollar spent!</Marketing_Msg>

</Affiliation>

FIELD OFFSET LENGTH DATA

TYPE

Description

RECORD_TYPE 0 1 CHAR C (CREATE/NEW), U (UPDATE), D (DELETE), H

(HISTORICAL/PRIOR VIEW)

CHANGE_SEQ 1 2 PIC SEQUENCE NUMBER FOR GROUPED CHANGES, E.G.

CHANGE_TS 3 26 CHAR TIMESTAMP FOR CHANGE

CHANGE_SOURCE 29 1 CHAR S (SYSTEM GENERATED), R (CSR), C (CUSTOMER)

INITIATOR 30 10 PIC IDENTIFICATION NUMBER (CSR SERIAL NUMBER OR

CUSTOMER ID) FOR PERSON MAKING CHANGE

CCNUM 40 16 PIC CREDIT CARD NUMBER

LAST_NAME 56 30 CHAR LAST NAME

FIRST_NAME 86 30 CHAR FIRST NAME

PARTNER_ID_NUM 116 12 CHAR AIRLINE FREQUENT FLYER NUMBER

REWARDID 128 10 PIC REWARD PRODUCT ID CODE

POINTBALANCE 138 8 PIC POINTS BALANCE AS OF LAST MONTHLY PROCESSING

PROCESS_DATE 146 10 CHAR DATE OF LAST MONTHLY PROCESSING

CREATED_DATE 156 10 CHAR DATE MM-DD-YYYY

CREATED_BY 166 10 CHAR USERID OF PERSON CREATING ENTRY