Database Schema

Overview

The AWS CardDemo application uses a hybrid database architecture combining three data storage technologies:

VSAM Files - Primary operational data storage
DB2 Relational Database - Transaction types and fraud tracking (optional)
IMS Hierarchical Database - Authorization processing (optional)

Entity Relationship Diagram

Loading diagram...

VSAM File Definitions

1. ACCTDAT - Account Master File

Purpose: Stores credit card account information including balances and limits

File Specifications:

Organization: KSDS (Key Sequenced Data Set)
Key: ACCT-ID (Position 0, Length 11)
Record Size: 300 bytes (fixed)
Access: Random and sequential
Share Options: (2,3) - Multi-region access

Field Layout:

Field Name	Position	Length	Type	Description
ACCT-ID	1-11	11	9(11)	Account ID (Primary Key)
ACCT-ACTIVE-STATUS	12	1	X(01)	Account status (A=Active, C=Closed)
ACCT-CURR-BAL	13-24	12	S9(10)V99	Current balance (signed)
ACCT-CREDIT-LIMIT	25-36	12	S9(10)V99	Credit limit
ACCT-CASH-CREDIT-LIMIT	37-48	12	S9(10)V99	Cash advance limit
ACCT-OPEN-DATE	49-58	10	X(10)	Account open date (YYYY-MM-DD)
ACCT-EXPIRATION-DATE	59-68	10	X(10)	Account expiration date
ACCT-REISSUE-DATE	69-78	10	X(10)	Card reissue date
ACCT-CURR-CYC-CREDIT	79-90	12	S9(10)V99	Current cycle credits
ACCT-CURR-CYC-DEBIT	91-102	12	S9(10)V99	Current cycle debits
ACCT-ADDR-ZIP	103-112	10	X(10)	Account ZIP code
ACCT-GROUP-ID	113-122	10	X(10)	Discount group ID (FK)
FILLER	123-300	178	X(178)	Reserved

Business Rules:

ACCT-CURR-BAL can be negative (customer owes)
ACCT-CURR-BAL ≤ ACCT-CREDIT-LIMIT (enforced by application)
ACCT-ACTIVE-STATUS = 'A' for active accounts

Indexes:

Primary: ACCT-ID (unique)

2. CUSTDAT - Customer Master File

Purpose: Stores customer demographic and identification information

File Specifications:

Organization: KSDS
Key: CUST-ID (Position 0, Length 9)
Record Size: 500 bytes (fixed)
Share Options: (2,3)

Field Layout:

Field Name	Position	Length	Type	Description
CUST-ID	1-9	9	9(09)	Customer ID (Primary Key)
CUST-FIRST-NAME	10-34	25	X(25)	First name
CUST-MIDDLE-NAME	35-59	25	X(25)	Middle name
CUST-LAST-NAME	60-84	25	X(25)	Last name
CUST-ADDR-LINE-1	85-134	50	X(50)	Address line 1
CUST-ADDR-LINE-2	135-184	50	X(50)	Address line 2
CUST-ADDR-LINE-3	185-234	50	X(50)	Address line 3
CUST-ADDR-STATE-CD	235-236	2	X(02)	State code
CUST-ADDR-COUNTRY-CD	237-239	3	X(03)	Country code
CUST-ADDR-ZIP	240-249	10	X(10)	ZIP/Postal code
CUST-PHONE-NUM-1	250-264	15	X(15)	Primary phone
CUST-PHONE-NUM-2	265-279	15	X(15)	Secondary phone
CUST-SSN	280-288	9	9(09)	Social Security Number
CUST-GOVT-ISSUED-ID	289-308	20	X(20)	Government ID
CUST-DOB-YYYY-MM-DD	309-318	10	X(10)	Date of birth
CUST-EFT-ACCOUNT-ID	319-328	10	X(10)	EFT account ID
CUST-PRI-CARD-HOLDER-IND	329	1	X(01)	Primary cardholder flag
CUST-FICO-CREDIT-SCORE	330-332	3	9(03)	FICO score
FILLER	333-500	168	X(168)	Reserved

Relationships:

One customer can have multiple accounts (1:N)

3. CARDDAT - Card Master File

Purpose: Stores credit card details and status

File Specifications:

Organization: KSDS
Key: CARD-NUM (Position 0, Length 16)
Record Size: 150 bytes (fixed)
Share Options: (2,3)

Field Layout:

Field Name	Position	Length	Type	Description
CARD-NUM	1-16	16	X(16)	Card number (Primary Key)
CARD-ACCT-ID	17-27	11	9(11)	Account ID (FK, AIX key)
CARD-CVV-CD	28-30	3	9(03)	Card verification value
CARD-EMBOSSED-NAME	31-80	50	X(50)	Name on card
CARD-EXPIRATION-DATE	81-90	10	X(10)	Expiration date
CARD-ACTIVE-STATUS	91	1	X(01)	Card status
FILLER	92-150	59	X(59)	Reserved

Alternate Index:

CARDAIX: CARD-ACCT-ID (non-unique, with UPGRADE)
- Allows retrieval of all cards for an account
- Path: AWS.M2.CARDDEMO.CARDDATA.VSAM.AIX.PATH

Relationships:

Each card belongs to one account (N:1)
Account to cards is one-to-many (1:N)

4. CCXREF - Card Cross-Reference File

Purpose: Cross-references cards to customers and accounts for quick lookup

File Specifications:

Organization: KSDS
Key: XREF-CARD-NUM (Position 0, Length 16)
Record Size: 50 bytes (fixed)
Share Options: (2,3)

Field Layout:

Field Name	Position	Length	Type	Description
XREF-CARD-NUM	1-16	16	X(16)	Card number (Primary Key)
XREF-CUST-ID	17-25	9	9(09)	Customer ID (FK)
XREF-ACCT-ID	26-36	11	9(11)	Account ID (FK, AIX key)
FILLER	37-50	14	X(14)	Reserved

Alternate Index:

CXACAIX: XREF-ACCT-ID (non-unique, with UPGRADE)
- Allows retrieval of all cards for an account
- Path: AWS.M2.CARDDEMO.CARDXREF.VSAM.AIX.PATH

Usage Pattern:

Fast card-to-account-to-customer lookups
Used extensively in transaction validation

5. TRANSACT - Transaction Master File

Purpose: Stores posted transaction records

File Specifications:

Organization: KSDS
Key: TRAN-ID (Position 0, Length 16)
Record Size: 350 bytes (fixed)
Share Options: (2,3)

Field Layout:

Field Name	Position	Length	Type	Description
TRAN-ID	1-16	16	X(16)	Transaction ID (Primary Key)
TRAN-TYPE-CD	17-18	2	X(02)	Transaction type code (FK)
TRAN-CAT-CD	19-22	4	9(04)	Category code
TRAN-SOURCE	23-32	10	X(10)	Transaction source
TRAN-DESC	33-132	100	X(100)	Description
TRAN-AMT	133-144	12	S9(09)V99	Amount (signed)
TRAN-MERCHANT-ID	145-153	9	9(09)	Merchant ID
TRAN-MERCHANT-NAME	154-203	50	X(50)	Merchant name
TRAN-MERCHANT-CITY	204-253	50	X(50)	Merchant city
TRAN-MERCHANT-ZIP	254-263	10	X(10)	Merchant ZIP
TRAN-CARD-NUM	264-279	16	X(16)	Card number (FK)
TRAN-ORIG-TS	280-305	26	X(26)	Original timestamp
TRAN-PROC-TS	306-331	26	X(26)	Processing timestamp (AIX)
FILLER	332-350	19	X(20)	Reserved

Alternate Index:

Transaction AIX: TRAN-PROC-TS (non-unique, with UPGRADE)
- Allows chronological access to transactions

Transaction Types:

Positive amounts: Charges, purchases
Negative amounts: Credits, refunds, payments

6. TCATBALF - Transaction Category Balance File

Purpose: Maintains running balances by transaction category for interest calculation

File Specifications:

Organization: KSDS
Key: TRAN-CAT-KEY (Position 0, Length 17, composite)
Record Size: 50 bytes (fixed)
Share Options: (2,3)

Field Layout:

Field Name	Position	Length	Type	Description
TRAN-CAT-KEY	1-17	17	Composite	Composite key
TRANCAT-ACCT-ID	1-11	11	9(11)	Account ID
TRANCAT-TYPE-CD	12-13	2	X(02)	Transaction type
TRANCAT-CD	14-17	4	9(04)	Category code
TRAN-CAT-BAL	18-29	12	S9(09)V99	Category balance
FILLER	30-50	21	X(22)	Reserved

Usage:

Updated during transaction posting
Read during interest calculation
Enables category-specific interest rates

7. DISCGRP - Disclosure Group File

Purpose: Stores interest rates and fee structures by account group and category

File Specifications:

Organization: KSDS
Key: DIS-GROUP-KEY (Position 0, Length 16, composite)
Record Size: 50 bytes (fixed)
Share Options: (2,3)

Field Layout:

Field Name	Position	Length	Type	Description
DIS-GROUP-KEY	1-16	16	Composite	Composite key
DIS-ACCT-GROUP-ID	1-10	10	X(10)	Account group ID
DIS-TRAN-TYPE-CD	11-12	2	X(02)	Transaction type
DIS-TRAN-CAT-CD	13-16	4	9(04)	Category code
DIS-INT-RATE	17-22	6	S9(04)V99	Interest rate
FILLER	23-50	28	X(28)	Reserved

Usage:

Different interest rates by account group
Category-specific rates (purchases, cash advances)
Monthly interest calculation

8. USRSEC - User Security File

Purpose: Stores user authentication and authorization data

File Specifications:

Organization: KSDS
Key: USER-ID (Position 0, Length 8)
Record Size: 100 bytes (fixed)
Share Options: (2,3)

Field Layout:

Field Name	Position	Length	Type	Description
SEC-USR-ID	1-8	8	X(08)	User ID (Primary Key)
SEC-USR-PWD	9-16	8	X(08)	Password (encrypted)
SEC-USR-TYPE	17	1	X(01)	User type (A=Admin, U=User)
SEC-USR-FNAME	18-42	25	X(25)	First name
SEC-USR-LNAME	43-67	25	X(25)	Last name
FILLER	68-100	33	X(33)	Reserved

User Types:

'A': Administrator (full access)
'U': Regular user (restricted access)

DB2 Database Schema (Optional)

1. TRANSACTION_TYPE Table

Purpose: Reference data for transaction type codes

CREATE TABLE CARDDEMO.TRANSACTION_TYPE (
    TR_TYPE         CHAR(2) NOT NULL,
    TR_DESCRIPTION  VARCHAR(50) NOT NULL,
    PRIMARY KEY (TR_TYPE)
)

Indexes:

CREATE UNIQUE INDEX CARDDEMO.XTRAN_TYPE
ON CARDDEMO.TRANSACTION_TYPE (TR_TYPE ASC)

Sample Data:

TR_TYPE	TR_DESCRIPTION
01	Purchase
02	Cash Advance
03	Payment
04	Fee
05	Interest

2. TRANSACTION_TYPE_CATEGORY Table

Purpose: Maps transaction types to specific categories

CREATE TABLE CARDDEMO.TRANSACTION_TYPE_CATEGORY (
    TRC_TYPE_CODE     CHAR(2) NOT NULL,
    TRC_TYPE_CATEGORY CHAR(4) NOT NULL,
    TRC_CAT_DATA      VARCHAR(50) NOT NULL,
    PRIMARY KEY (TRC_TYPE_CODE, TRC_TYPE_CATEGORY),
    FOREIGN KEY (TRC_TYPE_CODE)
        REFERENCES CARDDEMO.TRANSACTION_TYPE(TR_TYPE)
        ON DELETE RESTRICT
)

Indexes:

CREATE UNIQUE INDEX CARDDEMO.X_TRAN_TYPE_CATG
ON CARDDEMO.TRANSACTION_TYPE_CATEGORY
(TRC_TYPE_CODE ASC, TRC_TYPE_CATEGORY ASC)

Relationships:

Each type can have multiple categories (1:N)
Foreign key ensures referential integrity

3. AUTHFRDS Table (Fraud Tracking)

Purpose: Stores authorization transactions marked as fraudulent

CREATE TABLE CARDDEMO.AUTHFRDS (
    CARD_NUM                CHAR(16) NOT NULL,
    AUTH_TS                 TIMESTAMP NOT NULL,
    AUTH_TYPE               CHAR(4),
    CARD_EXPIRY_DATE        CHAR(4),
    MESSAGE_TYPE            CHAR(6),
    MESSAGE_SOURCE          CHAR(6),
    AUTH_ID_CODE            CHAR(6),
    AUTH_RESP_CODE          CHAR(2),
    AUTH_RESP_REASON        CHAR(4),
    PROCESSING_CODE         CHAR(6),
    TRANSACTION_AMT         DECIMAL(12,2),
    APPROVED_AMT            DECIMAL(12,2),
    MERCHANT_CATAGORY_CODE  CHAR(4),
    ACQR_COUNTRY_CODE       CHAR(3),
    POS_ENTRY_MODE          SMALLINT,
    MERCHANT_ID             CHAR(15),
    MERCHANT_NAME           VARCHAR(22),
    MERCHANT_CITY           CHAR(13),
    MERCHANT_STATE          CHAR(2),
    MERCHANT_ZIP            CHAR(9),
    TRANSACTION_ID          CHAR(15),
    MATCH_STATUS            CHAR(1),
    AUTH_FRAUD              CHAR(1),
    FRAUD_RPT_DATE          DATE,
    ACCT_ID                 DECIMAL(11),
    CUST_ID                 DECIMAL(9),
    PRIMARY KEY (CARD_NUM, AUTH_TS)
)

Indexes:

CREATE UNIQUE INDEX CARDDEMO.XAUTHFRD
ON CARDDEMO.AUTHFRDS (CARD_NUM ASC, AUTH_TS DESC)
WITH COPY YES

IMS Database Schema (Optional)

Authorization Database (HIDAM Structure)

Loading diagram...

1. PAUTSUM0 - Authorization Summary (Root)

Purpose: Summary of pending authorizations by account

Field Structure:

Field Name	Length	Type	Description
PA-ACCT-ID	6	COMP-3 S9(11)	Account ID (sequence field)
PA-CUST-ID	9	9(09)	Customer ID
PA-AUTH-STATUS	1	X(01)	Authorization status
PA-ACCOUNT-STATUS	10	X(02) OCCURS 5	Account status array
PA-CREDIT-LIMIT	6	COMP-3 S9(09)V99	Credit limit
PA-CASH-LIMIT	6	COMP-3 S9(09)V99	Cash limit
PA-CREDIT-BALANCE	6	COMP-3 S9(09)V99	Credit balance
PA-CASH-BALANCE	6	COMP-3 S9(09)V99	Cash balance
PA-APPROVED-AUTH-CNT	2	COMP S9(04)	Approved count
PA-DECLINED-AUTH-CNT	2	COMP S9(04)	Declined count
PA-APPROVED-AUTH-AMT	6	COMP-3 S9(09)V99	Approved total
PA-DECLINED-AUTH-AMT	6	COMP-3 S9(09)V99	Declined total

Size: 100 bytes

2. PAUTDTL1 - Authorization Details (Child)

Purpose: Individual authorization transaction details

Field Structure (Key Fields):

Field Name	Length	Type	Description
PA-AUTH-DATE-9C	3	COMP-3 S9(05)	Date (compressed)
PA-AUTH-TIME-9C	5	COMP-3 S9(09)	Time (compressed)
PA-CARD-NUM	16	X(16)	Card number
PA-AUTH-TYPE	4	X(04)	Authorization type
PA-TRANSACTION-AMT	6	COMP-3 S9(10)V99	Transaction amount
PA-APPROVED-AMT	6	COMP-3 S9(10)V99	Approved amount
PA-AUTH-RESP-CODE	2	X(02)	Response code
PA-MERCHANT-NAME	22	X(22)	Merchant name
PA-TRANSACTION-ID	15	X(15)	Transaction ID

Size: 200 bytes

Hierarchy:

One PAUTSUM0 can have many PAUTDTL1 children
Sequence field: Composite date+time

Key Relationships Summary

Loading diagram...

Relationship Table:

Parent	Child	Type	Key
CUSTOMER	ACCOUNT	1:N	CUST-ID
ACCOUNT	CARD	1:N	ACCT-ID
CARD	TRANSACTION	1:N	CARD-NUM
ACCOUNT	CARD-XREF	1:N	ACCT-ID
CARD	CARD-XREF	1:1	CARD-NUM
ACCOUNT	TCAT-BALANCE	1:N	ACCT-ID
ACCOUNT	DISCOUNT-GROUP	N:1	GROUP-ID
TRANSACTION	TRANSACTION-TYPE	N:1	TRAN-TYPE-CD

Storage Estimates

VSAM File Sizes

File	Record Size	Est. Records	Est. Size	Growth Rate
ACCTDAT	300 bytes	50,000	15 MB	5% annually
CUSTDAT	500 bytes	50,000	25 MB	5% annually
CARDDAT	150 bytes	100,000	15 MB	10% annually
CCXREF	50 bytes	100,000	5 MB	10% annually
TRANSACT	350 bytes	1,000,000	350 MB	20% monthly
TCATBALF	50 bytes	500,000	25 MB	15% monthly
DISCGRP	50 bytes	1,000	50 KB	Minimal
USRSEC	100 bytes	500	50 KB	Minimal

Total VSAM: ~435 MB (initial), grows to ~1 GB annually

DB2 Table Sizes

Table	Est. Rows	Row Size	Est. Size
TRANSACTION_TYPE	100	~60 bytes	6 KB
TRANSACTION_TYPE_CATEGORY	500	~60 bytes	30 KB
AUTHFRDS	100,000	~300 bytes	30 MB

IMS Database Sizes

Database	Segments	Est. Size
DBPAUTP0 (Primary)	50K summary + 500K details	110 MB
DBPAUTX0 (Index)	50K entries	2 MB

Backup and Recovery

VSAM Files

Backup Strategy:

Daily: IDCAMS EXPORT to sequential files
Weekly: Full backup via REPRO
Monthly: Archive to tape/S3

Recovery Procedures:

// IDCAMS IMPORT
//   INFILE(BACKUP.DATASET)
//   OUTFILE(VSAM.CLUSTER)

DB2 Tables

Backup Strategy:

Automatic: DB2 image copies (COPY YES indexes)
Log-based recovery via RECOVER utility
Point-in-time recovery capability

IMS Databases

Backup Strategy:

IMS utilities: DFSURDB0 (Database Recovery)
GSAM unload/reload for data migration
Exit routines enabled for logging

Performance Optimization

VSAM Tuning

Recommendations:

CI Size: 4096 bytes (standard)
CA Size: 1 cylinder
FREESPACE: 10% CI, 20% CA for high-update files
Buffers: Increase for frequently accessed files
AIX: UPGRADE option for automatic maintenance

DB2 Tuning

Recommendations:

Indexes: Unique indexes on primary keys
Statistics: Run RUNSTATS regularly
Buffer Pools: Dedicated pools for high-activity tables
Compression: Enable for large tables

IMS Tuning

Recommendations:

HIDAM: Provides both indexed and sequential access
SCAN: Set to appropriate level (3 for this database)
Buffer Pools: Size based on active working set