` 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:+ [Creating an Amazon EventBridge rule that runs on a schedule](https://docs.aws.amazon.com/eventbridge/latest/userguide/eb-create-rule-schedule.html)
+ [Accelerate orchestration of an ELT process using AWS Step Functions and Amazon Redshift Data API](https://aws.amazon.com/blogs/big-data/accelerate-orchestration-of-an-elt-process-using-aws-step-functions-and-amazon-redshift-data-api/).
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 following1. To authorize connections from Quick Sight to Amazon Redshift, open the Amazon Redshift console, and add an inbound rule in the Amazon Redshift security group. The rule should allow traffic to port 5439 (the default Redshift port) from the CIDR range where you set up Quick Sight. For a list of AWS Regions and their IP addresses, see [Supported AWS Regions for Quick Sight](https://docs.aws.amazon.com/quicksight/latest/user/regions-qs.html).
On the Amazon Redshift console, choose **Workgroup**, **Data access**, **Network and security**, and enable **Publicly accessible**. | Migration engineer |
| Create a dataset for Quick Sight. | To create a dataset for Quick Sight from Amazon Redshift, do following:1. On the Quick Sight console, in the navigation pane, choose **Datasets**.
2. On the **Datasets** page, choose **New data set**.
3. Choose **Redshift Manual connect**.
4. In the **New Redshift data source** window, enter the connection information:For **Data source name**, enter a name for the Amazon Redshift data source.For **Database server**, enter the endpoint of the Amazon Redshift cluster. You can get the endpoint value from the **Endpoint** field in the **General information** section for the cluster workgroup on the Amazon Redshift Serverless dashboard. The server address is the first part of the endpoint before the colon, as shown in the following example:mfdata-insights.NNNNNNNNN.us-east-1.redshift-serverless.amazonaws.com:5439/dev
For the port, enter `5439` (the default port for Amazon Redshift).Enter the name of the database (after the slash in the endpoint). In this case, the database name is `dev`.For **Username** and **Password**, enter the username and password for the Amazon Redshift database.
5. Choose **Validate Connection**. If successful, you should see a green check mark, which indicates validation. If validation fails, see the [Troubleshooting](#generate-db2-zos-data-insights-aws-mainframe-modernization-amazon-q-in-quicksight-troubleshooting) section.
6. Choose **Create data source**. | 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:1. To create the mainframe data analysis, follow the instructions in the [AWS documentation](https://docs.aws.amazon.com/quicksight/latest/user/generative-bi-build-visuals.html). For the dataset, choose the dataset that you created.
2. On the analysis page choose **Build visual**.
3. In the **Create topic for analysis** window, choose **Update existing topic**.
4. In the **Select a topic** dropdown list, choose the topic that you created previously.
5. Choose **Topic linking**.
6. After you link the topic, choose **Build visual** to open the Amazon Q **Build a Visual** window.
7. In the prompt bar. write your analysis questions. The example questions used for this pattern are the following:Show member distribution by regionShow member distribution by ageShow member distribution by genderShow member distribution by plan typeShow members not completed preventive immunization
After you enter your questions, choose **Build**. Amazon Q in Quick Sight creates the visuals.
8. To add the visuals to your visual dashboard, choose **ADD TO ANALYSIS**.
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:1. To create the data story, follow the instructions in the [AWS documentation](https://docs.aws.amazon.com/quicksight/latest/user/working-with-stories-create.html).
2. For the data story prompt, use the following:
`Build a data story about Region with most numbers of members. Also show the member distribution by medical plan, vision plan, dental plan. Recommend how to motivate members to complete immunization. Include 4 points of supporting data for this pattern.`
You can also build your own prompt to generate data stories for other business insights.
3. Choose **Add visuals**, and add the visuals that are relevant to the data story. For this pattern, use the visuals that you created previously.
4. Choose **Build**.
5. For example data story output, see *Data story output* 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 |
| 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. | 1. Confirm that the security group attached to the Amazon Redshift Serverless instance allows inbound traffic from the IP address range associated with the Region where you set up Quick Sight.
2. Confirm that the VPC where Amazon Redshift Serverless is deployed is publicly available.
3. Confirm that you are using the correct username and password for Amazon Redshift. You can reset the username and password on the Amazon Redshift console. |
| 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:+ `SQDD018E Cannot connect to transfer socket(rc==0x18468). Agent: Socket:/u/./sqdata/.DB2ZTOMSK.cab.data`
+ `SQDUR06E Error opening url cdc://:2626/DB2ZTOMSK/DB2ZTOMSK : errno:1128 (Unknown error 1128)` | 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\_details.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\_plans.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`*.*
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`.
**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.
The data story provides an analysis of member numbers for the four regions. The Northeast, Southwest, and Southeast regions have the most members.
The data story presents an analysis of members by age.
The data story focuses on immunization efforts in the Midwest.
## Attachments
To access additional content that is associated with this document, download and unzip the following file: [attachment.zip](samples/p-attach/18e72bcb-1b9a-406a-8220-83aca7743ad2/attachments/attachment.zip)