` with the name of your Amazon MSK topic.CREATE MATERIALIZED VIEW member_view
AUTO REFRESH YES
AS SELECT
kafka_partition,
kafka_offset,
refresh_time,
json_parse(kafka_value) AS Data
FROM member_schema.
WHERE CAN_JSON_PARSE(kafka_value);
| Migration engineer |
| Create target tables in Amazon Redshift. | Amazon Redshift tables provide the input for Quick Sight. This pattern uses the tables `member_dtls` and `member_plans`, which match the source Db2 tables on the mainframe.To create the two tables in Amazon Redshift, run the following SQL commands in Amazon Redshift query editor v2:-- Table 1: members_dtls
CREATE TABLE members_dtls (
memberid INT ENCODE AZ64,
member_name VARCHAR(100) ENCODE ZSTD,
member_type VARCHAR(50) ENCODE ZSTD,
age INT ENCODE AZ64,
gender CHAR(1) ENCODE BYTEDICT,
email VARCHAR(100) ENCODE ZSTD,
region VARCHAR(50) ENCODE ZSTD
) DISTSTYLE AUTO;
-- Table 2: member_plans
CREATE TABLE member_plans (
memberid INT ENCODE AZ64,
medical_plan CHAR(1) ENCODE BYTEDICT,
dental_plan CHAR(1) ENCODE BYTEDICT,
vision_plan CHAR(1) ENCODE BYTEDICT,
preventive_immunization VARCHAR(50) ENCODE ZSTD
) DISTSTYLE AUTO;
| Migration engineer |
| Create a stored procedure in Amazon Redshift. | This pattern uses a stored procedure to sync-up change data (`INSERT`, `UPDATE`, `DELETE`) from the source mainframe to the target Amazon Redshift data warehouse table for analytics in Quick Sight.To create the stored procedure in Amazon Redshift, use query editor v2 to run the stored procedure code that's in the GitHub repository. | Migration engineer |
| Read from the streaming materialized view and load to the target tables. | The stored procedure reads data change from the streaming materialized view and loads the data changes to the target tables. To run the stored procedure, use the following command:call SP_Members_Load();
You can use [Amazon EventBridge](https://aws.amazon.com/eventbridge/) to schedule the jobs in your Amazon Redshift data warehouse to call this stored procedure based on your data latency requirements. EventBridge runs jobs at fixed intervals. To monitor whether the previous call to the procedure completed, you might need to use a mechanism such as an [AWS Step Functions](https://aws.amazon.com/step-functions/) state machine. For more information, see the following resources:[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/generate-db2-zos-data-insights-aws-mainframe-modernization-amazon-q-in-quicksight.html)Another option is to use Amazon Redshift query editor v2 to schedule the refresh. For more information, see [Scheduling a query with query editor v2](https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-v2-schedule-query.html). | Migration engineer |
### Connect Quick Sight to data in Amazon Redshift
| Task | Description | Skills required |
| --- | --- | --- |
| Set up Quick Sight. | To set up Quick Sight, follow the instructions in the [AWS documentation](https://docs.aws.amazon.com/quicksight/latest/user/setting-up.html). | Migration engineer |
| Set up a secure connection between Quick Sight and Amazon Redshift. | To set up secure a connection between Quick Sight and Amazon Redshift, do the following[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/generate-db2-zos-data-insights-aws-mainframe-modernization-amazon-q-in-quicksight.html) | Migration engineer |
| Create a dataset for Quick Sight. | To create a dataset for Quick Sight from Amazon Redshift, do following:[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/generate-db2-zos-data-insights-aws-mainframe-modernization-amazon-q-in-quicksight.html) | Migration engineer |
| Join the dataset. | To create analytics in Quick Sight, join the two tables by following the instructions in the [AWS documentation](https://docs.aws.amazon.com/quicksight/latest/user/joining-data.html#create-a-join).In the **Join Configuration** pane, choose **Left** for **Join type**. Under **Join clauses**, use `memberid from member_plans = memberid from members_details`. | Migration engineer |
### Get business insights from the mainframe data by using Amazon Q in Quick Sight
| Task | Description | Skills required |
| --- | --- | --- |
| Set up Amazon Q in Quick Sight. | To set up the Amazon Q in Quick Sight generative BI capability, follow the instructions in the [AWS documentation](https://docs.aws.amazon.com/quicksight/latest/user/generative-bi-get-started.html). | Migration engineer |
| Analyze mainframe data and build a visual dashboard. | To analyze and visualize your data in Quick Sight, do the following:[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/generate-db2-zos-data-insights-aws-mainframe-modernization-amazon-q-in-quicksight.html)When you're finished, you can publish your dashboard to share with others in your organization. For examples, see *Mainframe visual dashboard* in the [Additional information](https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/generate-data-insights-by-using-aws-mainframe-modernization-and-amazon-q-in-quicksight.html#generate-data-insights-by-using-aws-mainframe-modernization-and-amazon-q-in-quicksight-additional) section. | Migration engineer |
### Create a data story with Amazon Q in Quick Sight from mainframe data
| Task | Description | Skills required |
| --- | --- | --- |
| Create a data story. | Create a data story to explain insights from the previous analysis, and generate a recommendation to increase preventive immunization for members:[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/generate-db2-zos-data-insights-aws-mainframe-modernization-amazon-q-in-quicksight.html) | Migration engineer |
| View the generated data story. | To view the generated data story, choose that story on the **Data stories** page. | Migration engineer |
| Edit a generated data story. | To change the formatting, layout, or visuals in a data story, follow the instructions in the [AWS documentation](https://docs.aws.amazon.com/quicksight/latest/user/working-with-stories-edit.html). | Migration engineer |
| Share a data story. | To share a data story, follow the instructions in the [AWS documentation](https://docs.aws.amazon.com/quicksight/latest/user/working-with-stories-share.html). | Migration engineer |
## Troubleshooting
| Issue | Solution |
| --- | --- |
| For Quick Sight to Amazon Redshift dataset creation, `Validate Connection` has faled. | [\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/generate-db2-zos-data-insights-aws-mainframe-modernization-amazon-q-in-quicksight.html) |
| Trying to start the Apply engine on the EC2 instance returns the following error:`-bash: sqdeng: command not found` | Export the `sqdata` installation path by running following command:export PATH=$PATH:/usr/sbin:/opt/precisely/di/sqdata/bin
|
| Trying to start the Apply Engine returns one of the following connection errors:[\[See the AWS documentation website for more details\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/generate-db2-zos-data-insights-aws-mainframe-modernization-amazon-q-in-quicksight.html) | Check the mainframe spool to make sure that the controller daemon jobs are running. |
## Related resources
+ [Generate insights by using AWS Mainframe Modernization and Amazon Q in Quick Sight](https://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/generate-data-insights-by-using-aws-mainframe-modernization-and-amazon-q-in-quicksight.html?did=pg_card&trk=pg_card) (pattern)
+ [Generate data insights by using AWS Mainframe Modernization and Amazon Q in Quick Sight](https://youtu.be/F8b7l79p6TM?si=gASuQtFbMVuEm7IJ) (demo)
+ [AWS Mainframe Modernization - Data Replication for IBM z/OS](https://aws.amazon.com/marketplace/pp/prodview-doe2lroefogia?sr=0-4&ref_=beagle&applicationId=AWSMPContessa)
+ [Amazon Redshift streaming ingestion to a materialized view](https://docs.aws.amazon.com/redshift/latest/dg/materialized-view-streaming-ingestion.html)
## Additional information
**Example .ddl files**
*members\$1details.ddl*
```
CREATE TABLE MEMBER_DTLS (
memberid INTEGER NOT NULL,
member_name VARCHAR(50),
member_type VARCHAR(20),
age INTEGER,
gender CHAR(1),
email VARCHAR(100),
region VARCHAR(20)
);
```
*member\$1plans.ddl*
```
CREATE TABLE MEMBER_PLANS (
memberid INTEGER NOT NULL,
medical_plan CHAR(1),
dental_plan CHAR(1),
vision_plan CHAR(1),
preventive_immunization VARCHAR(20)
);
```
**Example .sqd file**
Replace** **`` with your Amazon MSK topic name.
*script.sqd*
```
-- Name: DB2ZTOMSK: DB2z To MSK JOBNAME DB2ZTOMSK;REPORT EVERY 1;OPTIONS CDCOP('I','U','D');-- Source Descriptions
JOBNAME DB2ZTOMSK;
REPORT EVERY 1;
OPTIONS CDCOP('I','U','D');
-- Source Descriptions
BEGIN GROUP DB2_SOURCE;
DESCRIPTION DB2SQL /var/precisely/di/sqdata/apply/DB2ZTOMSK/ddl/mem_details.ddl AS MEMBER_DTLS;
DESCRIPTION DB2SQL /var/precisely/di/sqdata/apply/DB2ZTOMSK/ddl/mem_plans.ddl AS MEMBER_PLANS;
END GROUP;
-- Source Datastore
DATASTORE cdc:///DB2ZTOMSK/DB2ZTOMSK
OF UTSCDC
AS CDCIN
DESCRIBED BY GROUP DB2_SOURCE ;
-- Target Datastore(s)
DATASTORE 'kafka:////key'
OF JSON
AS TARGET
DESCRIBED BY GROUP DB2_SOURCE;
PROCESS INTO TARGET
SELECT
{
REPLICATE(TARGET)
}
FROM CDCIN;
```
**Mainframe visual dashboard**
The following data visual was created by Amazon Q in Quick Sight for the analysis question `show member distribution by region`*.*
![\[Northeast and Southwest have 8 members, Southwest has 5 members, Midwest has 4 members.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/18e72bcb-1b9a-406a-8220-83aca7743ad2/images/b40a784c-c1fc-444b-b6df-8bd1f7a6abaa.png)
The following data visual was created by Amazon Q in Quick Sight for the question `show member distribution by Region who have not completed preventive immunization, in pie chart`.
![\[Southeast shows 6, Southwest shows 5, and Midwest shows 4.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/18e72bcb-1b9a-406a-8220-83aca7743ad2/images/8a95da3c-df4a-458b-9cfe-44e34f80a235.png)
**Data story output**
The following screenshots show sections of the data story created by Amazon Q in Quick Sight for the prompt `Build a data story about Region with most numbers of members. Also show the member distribution by age, member distribution by gender. Recommend how to motivate members to complete immunization. Include 4 points of supporting data for this pattern`.
In the introduction, the data story recommends choosing the region with the most members to gain the greatest impact from immunization efforts.
![\[Introduction screen for analysis based on geographic, demographic, and age of the member base.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/18e72bcb-1b9a-406a-8220-83aca7743ad2/images/40f13957-2db4-42b7-b7a4-a0dd3dad6899.png)
The data story provides an analysis of member numbers for the four regions. The Northeast, Southwest, and Southeast regions have the most members.
![\[Northeast and Southwest regions have 8 members, Southeast has 6 members, and Midwest has 4 members.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/18e72bcb-1b9a-406a-8220-83aca7743ad2/images/fc6ed0a0-b79c-4397-95ac-a2fc4c87482a.png)
The data story presents an analysis of members by age.
![\[Chart showing that the member base skews toward younger and middle-aged adults.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/18e72bcb-1b9a-406a-8220-83aca7743ad2/images/8c56f1ec-3a2e-47a6-bbc4-3631782aa333.png)
The data story focuses on immunization efforts in the Midwest.
![\[Recommendation for personal outreach campaign and regional challenges.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/18e72bcb-1b9a-406a-8220-83aca7743ad2/images/84a647e8-c7d5-4637-94f0-03a611f899b3.png)
![\[Continuation of data story analysis, with anticipated outcomes and conclusion.\]](http://docs.aws.amazon.com/prescriptive-guidance/latest/patterns/images/pattern-img/18e72bcb-1b9a-406a-8220-83aca7743ad2/images/fc9094fc-2a20-485d-b238-e5e4ec70f1d3.png)
## Attachments
To access additional content that is associated with this document, unzip the following file: [attachment.zip](samples/p-attach/18e72bcb-1b9a-406a-8220-83aca7743ad2/attachments/attachment.zip)