Generate Db2 z/OS data insights by using AWS Mainframe Modernization and Amazon Q in QuickSight

To create an IAM policy and an IAM role, follow the instructions in the AWS documentation.

The IAM policy grants access to create topics on the Amazon MSK cluster and to send data to those topics.

After you create the IAM role, associate the policy with it.

Note the IAM role name. This role will be used as the IAM instance profile when you launch the EC2 instance.

Sign in to AWS Marketplace, and subscribe to AWS Mainframe Modernization ‒ Data Replication for IBM z/OS.

Select the AMI from the managed subscriptions, and choose on Launch new instance.

Provide other configuration details, such as the instance name, instance type, key pair, VPC, and subnets. For more information, see the Amazon EC2 documentation.

In the dropdown list, choose the security group that you created earlier.

Under Advanced details, IAM instance profile, you must select the role that you created earlier.

Choose Launch instance.

Sign in to the AWS Management Console, and open the Amazon MSK console at https://console.aws.amazon.com/msk/.

Choose Create cluster.

For the cluster creation method, choose Custom create, and for the cluster type, choose Provisioned.

Provide a name for the cluster.

Update Cluster settings as needed, and keep the defaults for the other settings.

Note the <Kafka version>. You will need it during Kafka client setup.

Choose Next.

Choose the same VPC and subnets that you used for the Precisely EC2 instance, and choose the security group that you created earlier.

In the Security settings section, enable both SASL/SCRAM and IAM role-based authentication. Precisely Connect CDC uses SASL/SCRAM (Simple Authentication and Security Layer/ Salted Challenge Response Mechanism), and IAM is needed for connecting to Amazon Redshift.

Choose Next.

For reviewing, choose Monitoring and Broker log delivery method.

Choose Next, and then choose Create the cluster.

To set up a secret in Secrets Manager, follow the instructions in the AWS documentation.

Open the Amazon MSK console, and select the Amazon MSK cluster that you created previously.

Choose the Properties tab.

Choose Associate secrets, Choose the secrets, select the secrets key that you created, and then choose Associate secrets.

You will see a success message similar to the following:

Successfully associated 1 secret for cluster <chosen cluster name>

Choose the cluster name.

In the cluster summary, choose View client information.

Note the private endpoint connection string for the authentication type SASL/SCRAM.

Connect to the EC2 instance that you created earlier, and install the latest updates by running the following command:

sudo yum update -y

Install the Java and Kafka library by running the following command:

sudo yum install -y java-11 librdkafka librdkafka-devel

To create a folder named kafka in /home/ec2-user, navigate to that folder, and run the following command:

mkdir kafka;cd kafka

Download the kafka client library to the kafka folder, replacing <YOUR MSK VERSION> with the Kafka version that you noted during the Amazon MSK cluster creation:

wget https://archive.apache.org/dist/kafka//kafka_2.13-<YOUR MSK VERSION>.tgz

To extract the downloaded file, run the following command, replacing YOUR MSK VERSION>:

tar -xzf kafka_2.13-<YOUR MSK VERSION>.tgz

To navigate to the kafka libs directory and download the Java IAM authentication Java Archive (JAR) file, run the following commands, replacing <YOUR MSK VERSION>:

cd kafka_2.13-<YOUR MSK VERSION>/libs
wget https://github.com/aws/aws-msk-iam-auth/releases/download/v1.1.1/aws-msk-iam-auth-1.1.1-all.jarkafka

To navigate to the Kafka bin directory and create the client.properties file, run the following commands:

cd /home/ec2-user/kafka/kafka_2.13-<YOUR MSK VERSION>/bin
cat >client.properties

Update the client.properties file with the following contents:

security.protocol=SASL_SSL
sasl.mechanism=AWS_MSK_IAM
sasl.jaas.config=software.amazon.msk.auth.iam.IAMLoginModule required;
sasl.client.callback.handler.class=software.amazon.msk.auth.iam.IAMClientCallbackHandler

To create a Kafka topic, navigate to the Kafka bin and run the following command, replacing <kafka broker> with the IAM bootstrap server private endpoint that you noted during Amazon MSK cluster creation:

./kafka-topics.sh --bootstrap-server <kafka broker> --command-config client.properties --create --replication-factor 3 —partitions 6 --topic <topic name>

When the message Created topic <topic name> appears, note the topic name.

To create a folder name precisely and change to that folder, run the following command:

mkdir /home/ec2-user/precisely;cd /home/ec2-user/precisely

To create two folders inside precisely called scripts and ddls, and then change to the scripts folder, run the following command:

mkdir scripts;mkdir ddls;cd scripts

To create a file named sqdata_kafka_producer.conf in the scripts folder, run the following command:

cat >sqdata_kafka_producer.conf

Update sqdata_kafka_producer.conf file with the following content:

builtin.features=SASL_SCRAM
security.protocol=SASL_SSL
sasl.mechanism=SCRAM-SHA-512
sasl.username=<User Name>
sasl.password=<Password>
metadata.broker.list=<SASL/SCRAM Bootstrap servers>

Create a script.sqd file in the scripts folder.

cat >script.sqd

The Apply Engine uses script.sqd to process source data and replicate the source data to the target. For an example Apply Engine script, see the Additional information section.

To change to the ddls folder and create a .ddl file for each Db2 table, run the following commands:

cd /home/ec2-user/precisely/ddls
cat >mem_details.ddl
cat >mem_plans.ddl

To export the sqdata installation path, run the following command:

export PATH=$PATH:/usr/sbin:/opt/precisely/di/sqdata/bin

To change to the /home/ec2-user directory and generate the network ACL key, run the following commands:

cd /home/ec2-user
sqdutil keygen --force

After the public and private keys are generated, the following message is displayed:

SQDUT04I Generating a private key in file /home/ec2-user/.nacl/id_nacl
SQDC017I sqdutil(pid=27344) terminated successfully

Note the generated public key that is stored in the .nacl folder.

On the SQData z/OS Main Menu screen, choose option 2.

On the Add Daemon to List screen, in the Daemon Name field, enter a name for the daemon, and then press Enter.

On the SQData z/OS Main Menu screen, choose option 3. This takes you to the Capture/Publisher Summary screen.

Choose the option to add a CAB file. This will take you to the Add CAB File to List screen.

In the Name field, enter a name for the CAB file. For Db2, enter the Type as D.

Press Enter. This takes you to the Create New Db2 Capture CAB File screen.

In the zFS Dir field, specify the storage mount point.

Press Enter to save and continue.

On the SQData z/OS Main Menu screen, choose option 2.

Enter S near the daemon that you created to see the daemon details.

Enter 1, and then press Enter to edit the agents file.

Add your CAB file details. The following example shows the details for a CAB file named DB2ZTOMSK. Use your mainframe user ID instead of <userid>.

ÝDB2ZTOMSK¨
type=capture 
cab=/u/<userid>/sqdata/DB2ZTOMSK.cab

Press F3.

Enter 2 to edit the ACL file. Add your userid to the acl configuration file as shown in the following example:

Ýacls¨
prod=admin,<userid>

Press F3 to save and exit.

In Options, enter G.

Enter the JOB Card, job and proc libraries, and Db2 load library details.

Enter the network ACL file details, and enter option 2 to generate the job control language (JCL) file in the specified job library.

On the SQData z/OS Main Menu screen, choose option 3. This takes you to the Capture/Publisher Summary screen.

Enter S next to the CAB file to select it. This takes you to the Db2 Capture/Publisher Detail screen.

In Options, enter G in options to generate the capture/publisher job.

Enter the JOB card, job, and procedure libraries, and Db2 load library details.

To create the job, choose option 4. The job is created in the job library specified in job library.

Check the DATACAPTURE flag for the Db2 table by running the following query, changing <table name> to your Db2 table name:

SELECT DATACAPTURE FROM SYSIBM.SYSTABLES WHERE NAME='<table name>';

Confirm that the result shows DATACAPTURE as Y.

If DATACAPTURE is not Y, run the following query to enable CDC on the Db2 table, changing <table name> to your Db2 table name:

ALTER TABLE <table name> DATA CAPTURE CHANGES;

To connect to the EC2 instance, follow the instructions in the AWS documentation.

Change to the directory that contains the script.sqd file:

cd /home/ec2-user/precisely/scripts

To start the Apply Engine, run the following sqdeng start command:

sqdeng -s script.sqd --identity=/home/ec2-user/.nacl/id_nacl

The Apply Engine will start waiting for updates from the mainframe source.

To test CDC, make some record inserts or updates in the Db2 table.

Validate that the Apply Engine log shows the number of records captured and written to the target.

To change to the bin directory of the Kafka client installation path on the EC2 instance, run the following command, replacing <Kafka version> with your version:

cd /home/ec2-user/kafka/kafka_2.13-<Kafka version>/bin

To validate the Db2 CDC written as messages in the Kafka topic, run the following command, replacing <kafka broker> and <Topic Name> with the topic that you created earlier:

./kafka-console-consumer.sh --bootstrap-server <kafka broker>:9098 --topic <Topic Name> --from-beginning --consumer.config client.properties

Validate that the messages match the number of records updated in the Db2 table.

Create an IAM policy for Amazon Redshift to access Amazon MSK.

Replacing [region] with the AWS Region for Amazon MSK, [account-id] with your AWS account ID, and [msk-cluster-name] with Amazon MSK cluster name, run the following code:

{"Version": "2012-10-17","Statement": [{"Sid": "MSKIAMpolicy","Effect": "Allow","Action": ["kafka-cluster:ReadData","kafka-cluster:DescribeTopic","kafka-cluster:Connect"],"Resource": ["arn:aws:kafka:[region]:[account-id]:cluster/[msk-cluster-name]/*","arn:aws:kafka:[region]:[account-id]:topic/[msk-cluster-name]/*"]},{"Effect": "Allow","Action": ["kafka-cluster:AlterGroup","kafka-cluster:DescribeGroup"],"Resource": ["arn:aws:kafka:[region]:[account-id]:group/[msk-cluster-name]/*"]}]}

You can find the cluster name and the Amazon Resource Name (ARN) on the Amazon MSK console. On the console, choose Cluster summary, and then choose ARN.

To create an IAM role and attach the policy, follow the instructions in the AWS documentation.

To attach the IAM role to the Amazon Redshift Serverless namespace, do following:

In your Amazon Redshift Serverless security group, create an inbound rule with the following details:

In your Amazon MSK security group, create an inbound rule with following details:

This pattern uses the port for IAM authentication for both Amazon Redshift and Amazon MSK configuration. For more information, see the AWS documentation (step 2).

Turn on enhanced VPC routing for the Amazon Redshift Serverless workgroup. For more information, see the AWS documentation.

To authorize connections from QuickSight 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 QuickSight. For a list of AWS Regions and their IP addresses, see Supported AWS Regions for QuickSight.

On the Amazon Redshift console, choose Workgroup, Data access, Network and security, and enable Publicly accessible.

On the QuickSight console, in the navigation pane, choose Datasets.

On the Datasets page, choose New data set.

Choose Redshift Manual connect.

In the New Redshift data source window, enter the connection information:

Choose Validate Connection. If successful, you should see a green check mark, which indicates validation. If validation fails, see the Troubleshooting section.

Choose Create data source.

To create the mainframe data analysis, follow the instructions in the AWS documentation. For the dataset, choose the dataset that you created.

On the analysis page choose Build visual.

In the Create topic for analysis window, choose Update existing topic.

In the Select a topic dropdown list, choose the topic that you created previously.

Choose Topic linking.

After you link the topic, choose Build visual to open the Amazon Q Build a Visual window.

In the prompt bar. write your analysis questions. The example questions used for this pattern are the following:

After you enter your questions, choose Build. Amazon Q in QuickSight creates the visuals.

To add the visuals to your visual dashboard, choose ADD TO ANALYSIS.

To create the data story, follow the instructions in the AWS documentation.

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.

Choose Add visuals, and add the visuals that are relevant to the data story. For this pattern, use the visuals that you created previously.

Choose Build.

For example data story output, see Data story output in the Additional information section.