Cloud Database
Pre-Requisites
- DB2 on Cloud Enterprise Edition
Connecting to DB2 on Cloud from inside firewall
For on-prem installations of ADDI and RA servers, if there is a firewall blocking access to DB2 on Cloud then a network security ticket will need to be opened. Ideally, the network security team will open up the firewall directly to the DB2 on Cloud host. In which case, you can proceed as normal.
In some cases, the network security team will create a "plug proxy" instead of opening the firewall directly. They will then give you a proxy host and IP address to use that will then create a tunnel to connect to the DB2 host on the otherside of the firewall. This complicates the configuration.
Another important detail to keep in mind is that the DB2 host given by IBM Cloud is a load balancer. There are other actual IP addresses behind that load balancer IP. You will need all hosts and IP addresses from DB2 on Cloud to configure connection through a plug proxy.
For example, the nslookup command below shows all hosts and IP addresses for a DB2 on Cloud load balancer host/ip combination.
➜ ~ nslookup bb54fad1-34c8-4d62-b5ea-3a11d91f3f3f.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud
Server: 172.26.32.1
Address: 172.26.32.1#53
Non-authoritative answer:
bb54fad1-34c8-4d62-b5ea-3a11d91f3f3f.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud canonical name = db2-icd-prod-us-s-590130-4f0f9a5666e20005abfaee753b7e644d-0000.us-south.containers.appdomain.cloud.
db2-icd-prod-us-s-590130-4f0f9a5666e20005abfaee753b7e644d-0000.us-south.containers.appdomain.cloud canonical name = db2-icd-prod-us-s-590130-4f0f9a5666e20005abfaee753b7e644d-0000.c3n41cmd0nqnrk39u98g.akadns.net.
Name: db2-icd-prod-us-s-590130-4f0f9a5666e20005abfaee753b7e644d-0000.c3n41cmd0nqnrk39u98g.akadns.net
Address: 52.117.249.178
Name: db2-icd-prod-us-s-590130-4f0f9a5666e20005abfaee753b7e644d-0000.c3n41cmd0nqnrk39u98g.akadns.net
Address: 169.48.136.34
Name: db2-icd-prod-us-s-590130-4f0f9a5666e20005abfaee753b7e644d-0000.c3n41cmd0nqnrk39u98g.akadns.net
Address: 52.116.221.102
You will need to note not only the load balancer hostname but the actual hostname.
Load balancer: bb54fad1-34c8-4d62-b5ea-3a11d91f3f3f.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud
Actual: db2-icd-prod-us-s-590130-4f0f9a5666e20005abfaee753b7e644d-0000.c3n41cmd0nqnrk39u98g.akadns.net
Let's now assume the plug proxy given is plugproxy-xyz.customer.lab and the
IP is 10.1.70.150 and the DB2 hostnames above are to be used. The Windows hosts file
needs to be configured as follows.
10.1.70.150 plugproxy-xyz.customer.lab
10.1.70.150 bb54fad1-34c8-4d62-b5ea-3a11d91f3f3f.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud
10.1.70.150 db2-icd-prod-us-s-590130-4f0f9a5666e20005abfaee753b7e644d-0000.c3n41cmd0nqnrk39u98g.akadns.net
After configuring the hosts file (Found under C:\Windows\System32\drivers\etc), you will then
proceed to configure the connection to DB2 using plugproxy-xyz.customer.lab as the hostname.
Creating the service credentials
In the IBM Cloud dashboard, select databases from the resource list and open it up
Select the Service Credentials
Create the name as db2_sa with the Manager role and click Add
The service UserID will be generated as well as a password. Take note of these values from the JSON.
{
"apikey": "<API KEY>",
"connection": {
"cli": {
"arguments": [
[
"-u",
"<Username>",
"-p",
"<Password>",
"--ssl",
"--sslCAFile",
"<ssl CA file>",
"--authenticationDatabase",
"admin",
"--host",
"<HOST>:<PORT:31106>"
]
],
"bin": "db2",
"certificate": {
"certificate_base64": "LS0tLS1CRUdJTiBDRVJUSUZJQ0FURS0tLS0tCk1JSURFakNDQWZxZ0F3SUJBZ0lKQVA1S0R3ZTNCTkxiTUEwR0NTcUdTSWIzRFFFQkN3VUFNQjR4SERBYUJnTlYKQkFNTUUwbENUU0JEYkc5MVpDQkVZWFJoWW1GelpYTXdIaGNOTWpBd01qSTVNRFF5TVRBeVdoY05NekF3TWpJMgpNRFF5TVRBeVdqQWVNUnd3R2dZRFZRUUREQk5KUWswZ1EyeHZkV1FnUkdGMFlXSmhjMlZ6TUlJQklqQU5CZ2txCmhraUc5dzBCQVFFRkFBT0NBUThBTUlJQkNnS0NBUUVBdXUvbitpWW9xdkdGNU8xSGpEalpsK25iYjE4UkR4ZGwKTzRUL3FoUGMxMTREY1FUK0plRXdhdG13aGljTGxaQnF2QWFMb1hrbmhqSVFOMG01L0x5YzdBY291VXNmSGR0QwpDVGcrSUsxbjBrdDMrTHM3d1dTakxqVE96N3M3MlZUSU5yYmx3cnRIRUlvM1JWTkV6SkNHYW5LSXdZMWZVSUtrCldNMlR0SDl5cnFsSGN0Z2pIUlFmRkVTRmlYaHJiODhSQmd0amIva0xtVGpCaTFBeEVadWNobWZ2QVRmNENOY3EKY21QcHNqdDBPTnI0YnhJMVRyUWxEemNiN1hMSFBrWW91SUprdnVzMUZvaTEySmRNM1MrK3labFZPMUZmZkU3bwpKMjhUdGJoZ3JGOGtIU0NMSkJvTTFSZ3FPZG9OVm5QOC9EOWZhamNNN0lWd2V4a0lSOTNKR1FJREFRQUJvMU13ClVUQWRCZ05WSFE0RUZnUVVlQ3JZanFJQzc1VUpxVmZEMDh1ZWdqeDZiUmN3SHdZRFZSMGpCQmd3Rm9BVWVDclkKanFJQzc1VUpxVmZEMDh1ZWdqeDZiUmN3RHdZRFZSMFRBUUgvQkFVd0F3RUIvekFOQmdrcWhraUc5dzBCQVFzRgpBQU9DQVFFQUkyRTBUOUt3MlN3RjJ2MXBqaHV4M0lkWWV2SGFVSkRMb0tPd0hSRnFSOHgxZ2dRcGVEcFBnMk5SCkx3R08yek85SWZUMmhLaWd1d2orWnJ5SGxxcHlxQ0pLOHJEU28xZUVPekIyWmE2S1YrQTVscEttMWdjV3VHYzMKK1UrVTFzTDdlUjd3ZFFuVjU0TVU4aERvNi9sVHRMRVB2Mnc3VlNPSlFDK013ejgrTFJMdjVHSW5BNlJySWNhKwozM0wxNnB4ZEttd1pLYThWcnBnMXJ3QzRnY3dlYUhYMUNEWE42K0JIbzhvWG5YWkh6UG91cldYS1BoaGdXZ2J5CkNDcUdIK0NWNnQ1eFg3b05NS3VNSUNqRVZndnNLWnRqeTQ5VW5iNVZZbHQ0b1J3dTFlbGdzRDNjekltbjlLREQKNHB1REFvYTZyMktZZE4xVkxuN3F3VG1TbDlTU05RPT0KLS0tLS1FTkQgQ0VSVElGSUNBVEUtLS0tLQo=",
"name": "f64c7886-a9cf-45d4-8c62-2a5d468cb8d2"
},
"composed": [
"db2 -u 856d915d -p 7bpRpEYGALwWt4fr --ssl --sslCAFile f64c7886-a9cf-45d4-8c62-2a5d468cb8d2 --authenticationDatabase admin --host dbd5d8b8-61c3-49dc-9213-79a4104c27f6.c8l9ggsd0kmvoig3l8kg.databases.appdomain.cloud:31106"
],
"environment": {},
"type": "cli"
},
"db2": {
"authentication": {
"method": "direct",
"password": "7bpRpEYGALwWt4fr",
"username": "856d915d"
},
"certificate": {
"certificate_base64": "LS0tLS1CRUdJTiBDRVJUSUZJQ0FURS0tLS0tCk1JSURFakNDQWZxZ0F3SUJBZ0lKQVA1S0R3ZTNCTkxiTUEwR0NTcUdTSWIzRFFFQkN3VUFNQjR4SERBYUJnTlYKQkFNTUUwbENUU0JEYkc5MVpDQkVZWFJoWW1GelpYTXdIaGNOTWpBd01qSTVNRFF5TVRBeVdoY05NekF3TWpJMgpNRFF5TVRBeVdqQWVNUnd3R2dZRFZRUUREQk5KUWswZ1EyeHZkV1FnUkdGMFlXSmhjMlZ6TUlJQklqQU5CZ2txCmhraUc5dzBCQVFFRkFBT0NBUThBTUlJQkNnS0NBUUVBdXUvbitpWW9xdkdGNU8xSGpEalpsK25iYjE4UkR4ZGwKTzRUL3FoUGMxMTREY1FUK0plRXdhdG13aGljTGxaQnF2QWFMb1hrbmhqSVFOMG01L0x5YzdBY291VXNmSGR0QwpDVGcrSUsxbjBrdDMrTHM3d1dTakxqVE96N3M3MlZUSU5yYmx3cnRIRUlvM1JWTkV6SkNHYW5LSXdZMWZVSUtrCldNMlR0SDl5cnFsSGN0Z2pIUlFmRkVTRmlYaHJiODhSQmd0amIva0xtVGpCaTFBeEVadWNobWZ2QVRmNENOY3EKY21QcHNqdDBPTnI0YnhJMVRyUWxEemNiN1hMSFBrWW91SUprdnVzMUZvaTEySmRNM1MrK3labFZPMUZmZkU3bwpKMjhUdGJoZ3JGOGtIU0NMSkJvTTFSZ3FPZG9OVm5QOC9EOWZhamNNN0lWd2V4a0lSOTNKR1FJREFRQUJvMU13ClVUQWRCZ05WSFE0RUZnUVVlQ3JZanFJQzc1VUpxVmZEMDh1ZWdqeDZiUmN3SHdZRFZSMGpCQmd3Rm9BVWVDclkKanFJQzc1VUpxVmZEMDh1ZWdqeDZiUmN3RHdZRFZSMFRBUUgvQkFVd0F3RUIvekFOQmdrcWhraUc5dzBCQVFzRgpBQU9DQVFFQUkyRTBUOUt3MlN3RjJ2MXBqaHV4M0lkWWV2SGFVSkRMb0tPd0hSRnFSOHgxZ2dRcGVEcFBnMk5SCkx3R08yek85SWZUMmhLaWd1d2orWnJ5SGxxcHlxQ0pLOHJEU28xZUVPekIyWmE2S1YrQTVscEttMWdjV3VHYzMKK1UrVTFzTDdlUjd3ZFFuVjU0TVU4aERvNi9sVHRMRVB2Mnc3VlNPSlFDK013ejgrTFJMdjVHSW5BNlJySWNhKwozM0wxNnB4ZEttd1pLYThWcnBnMXJ3QzRnY3dlYUhYMUNEWE42K0JIbzhvWG5YWkh6UG91cldYS1BoaGdXZ2J5CkNDcUdIK0NWNnQ1eFg3b05NS3VNSUNqRVZndnNLWnRqeTQ5VW5iNVZZbHQ0b1J3dTFlbGdzRDNjekltbjlLREQKNHB1REFvYTZyMktZZE4xVkxuN3F3VG1TbDlTU05RPT0KLS0tLS1FTkQgQ0VSVElGSUNBVEUtLS0tLQo=",
"name": "f64c7886-a9cf-45d4-8c62-2a5d468cb8d2"
},
"composed": [
"db2://856d915d:7bpRpEYGALwWt4fr@dbd5d8b8-61c3-49dc-9213-79a4104c27f6.c8l9ggsd0kmvoig3l8kg.databases.appdomain.cloud:31106/bludb?authSource=admin&replicaSet=replset"
],
"database": "bludb",
"host_ros": [
"dbd5d8b8-61c3-49dc-9213-79a4104c27f6.c8l9ggsd0kmvoig3l8kg.databases.appdomain.cloud:31858"
],
"hosts": [
{
"hostname": "dbd5d8b8-61c3-49dc-9213-79a4104c27f6.c8l9ggsd0kmvoig3l8kg.databases.appdomain.cloud",
"port": 31106
}
],
"jdbc_url": [
"jdbc:db2://dbd5d8b8-61c3-49dc-9213-79a4104c27f6.c8l9ggsd0kmvoig3l8kg.databases.appdomain.cloud:31106/bludb:user=<userid>;password=<your_password>;sslConnection=true;"
],
"path": "/bludb",
"query_options": {
"authSource": "admin",
"replicaSet": "replset"
},
"replica_set": "replset",
"scheme": "db2",
"type": "uri"
}
},
"iam_apikey_description": "Auto-generated for key crn:v1:bluemix:public:dashdb-for-transactions:us-south:a/fb7864c969b640e29a59ee1365731e3b:dbd5d8b8-61c3-49dc-9213-79a4104c27f6:resource-key:856d915d-d65a-4381-afe1-c92d03288fce",
"iam_apikey_id": "ApiKey-f9751182-792f-451b-b963-34de564f6059",
"iam_apikey_name": "db2_sa",
"iam_role_crn": "crn:v1:bluemix:public:iam::::serviceRole:Manager",
"iam_serviceid_crn": "crn:v1:bluemix:public:iam-identity::a/fb7864c969b640e29a59ee1365731e3b::serviceid:ServiceId-719a6cfa-ec45-4ff1-8970-e2208ad69d0d",
"instance_administration_api": {
"deployment_id": "crn:v1:bluemix:public:dashdb-for-transactions:us-south:a/fb7864c969b640e29a59ee1365731e3b:dbd5d8b8-61c3-49dc-9213-79a4104c27f6::",
"instance_id": "crn:v1:bluemix:public:dashdb-for-transactions:us-south:a/fb7864c969b640e29a59ee1365731e3b:dbd5d8b8-61c3-49dc-9213-79a4104c27f6::",
"root": "https://api.db2.cloud.ibm.com/v5/ibm"
}
}
From the above output we can extract the following values for this service account:
- Service Account Username:
856d915d - Service Account Password:
7bpRpEYGALwWt4fr - FQDN + Port:
dbd5d8b8-61c3-49dc-9213-79a4104c27f6.c8l9ggsd0kmvoig3l8kg.databases.appdomain.cloud:31106
Installing/Configuring DB2 Client
You will need the following:
- DB2 FQDN
- DB2 port
- DB2 user
- DB2 Password
As of this writing you will need to open the webui for the DB2 cloud instance.
Open the webui and follow the below images
- Download Windows driver package Download Windows driver package from driver list File name: ibm_data_server_driver_package_win64_v11.5.exe (104 MB)
- Install the drivers by running the
ibm_data_server_driver_package_win64_v11.5.exefile as an administrator. - In The Connection configuration resources section, select whether or not you want to secure your connections by using SSL. If your application uses its own driver and you want to connect with SSL, download the SSL certificate (DigiCertGlobalRootCA.crt). For Java apps, use the JDBC string as the database URL in your call to the JDBC getConnection method. For ODBC apps, add new entries to the db2dsdriver.cfg driver configuration file by running the following commands:
Determine the FQDN of your DB2 on Cloud instance:
From the above image our example FQDN for DB2 is dbd5d8b8-61c3-49dc-9213-79a4104c27f6.c8l9ggsd0kmvoig3l8kg.databases.appdomain.cloud and our port is 31106.
Once you have the instance up and you have the FQDN, run the following db2 local config commands in Powershell on the ADDI Host as illustrated from the db2 webui:
db2cli writecfg add -database bludb -host dbd5d8b8-61c3-49dc-9213-79a4104c27f6.c8l9ggsd0kmvoig3l8kg.databases.appdomain.cloud -port 31106
db2cli writecfg add -dsn dashdb -database bludb -host dbd5d8b8-61c3-49dc-9213-79a4104c27f6.c8l9ggsd0kmvoig3l8kg.databases.appdomain.cloud -port 31106
db2cli writecfg add -database bludb -host dbd5d8b8-61c3-49dc-9213-79a4104c27f6.c8l9ggsd0kmvoig3l8kg.databases.appdomain.cloud -port 31106 -parameter "SecurityTransportMode=SSL"
Configure WCA4Z instance for DB2
In the IBM Cloud Dashboard under resources select the WCA4Z instance
Launce the watsonx Code Assistant ui
We are going to perform the tasks highlighted by the red arrows.
Create service Id
We'll call it wca4z_service_id
Create API Key
Once the service ID is created, select API Keys from left hand side. Click Create and call the API key wca-api-key
Copy or download the API key somewhere safe. This is needed for next step.
Create the Deployment Space
We are going with wca-deployment for our deployment space name.
Add Service ID to Deployment Space
Select under the Add collaborators button add service ids
Check off the service ID we created earlier and set the role to Admin
Add DB2 Connection to workspace
Under Import asserts, select Data access and then Connection
Select IBM under the provider and DB2 on Cloud
Populate the values with the service account user/pass/fqdn/port we extracted from our returned JSON. For the connection name we're going with db2-wca4z-connection.
Finally test the connection and then click Create
Configure ADDI for DB2
Download the above script and run it with your DB2 FQDN. You will need the following:
- FQDN (retrieved above)
- Service Account Username (retrieved above):
856d915d - Service Account Password (retrieved above):
7bpRpEYGALwWt4fr - Port:
31106
PS D:\> .\db2configuration.bat dbName=BLUDB db2Host=3b84375f-36fb-42b0-8241-ac0e7840e5dc.bpe60pbd01oinge4psd0.databases.appdomain.cloud db2Port=31838 db2User=856d915d db2Password=7bpRpEYGALwWt4fr useTLS=true
Adding new DB2 database instance into ADDI
Success.
This will add the appropriate entries into ADDI.
IP Restrictions for DB2 on Cloud
For security, it might be required to restrict IP addresses that can access the sensitive meta-data contained there. This can be done directly in DB2 on Cloud through the adminstration console by updating the "Allowlist IPs".
Prerequisite: The list of IP addresses or subnets for the environment where ADDI deployed are required. If ADDI is deployed to an on-prem environment you will need to work with the network team to get those values.
-
Logon to https://cloud.ibm.com/
-
Navigate to Resource List → Databases
-
Select the DB2 on Cloud instance you want to modify
-
Click on Go To UI under Manage
-
Click on Adminstration
-
Click on Access restriction
-
Scroll down to Allowlist IPs
-
Click on "Add IP +" button
-
The list below includes the production IP subnets for the WCA4Z service. Add each of the following as an Allowlisted IP address. This allows the WCA4Z service to access the DB2 instance.
note(April 2024) These IP subnets are not currently published and are accurate as of the time of this documentation.
169.60.39.176/28
169.60.36.32/27
169.61.221.64/26
169.61.134.0/27
169.61.138.0/26
169.61.14.48/28
169.62.147.0/27
52.117.255.0/26
10.38.218.0/25
10.93.43.64/26
10.74.40.0/26
10.241.100.128/25
10.73.71.0/26 -
You will also need to add IP addresses or subnets that include the ADDI server environment so that ADDI can access the DB2 instance to write the meta-data. Add those IP addresses or subnet in the same manner as you did above.
Clearing out the Database
As of May 2024, the cloud database can only contain one project for transformation. If you want to transform a new project, then the cloud database will need to be cleared out and reset.
-
Log in to the DB2 on Cloud management UI and select Run SQL.
-
Run the following SQL commands in sequence. It is recommended to run one at a time and let each one run to completion before running the next. In theory, they can be run as one unit but in practice some commands might fail and will need to be run again.
CALL SYSPROC.ADMIN_DROP_SCHEMA('EZSCH', NULL, 'ERRORSCHEMA', 'ERRORTABLE');
drop schema ERRORSCHEMA RESTRICT;
drop tablespace TS_DATA;
drop tablespace TS_IDX;
ALTER TABLESPACE TS_TMP SWITCH ONLINE;
drop tablespace TS_TMP;
drop bufferpool BPTEMP32K;
drop bufferpool BPDATA32K;
drop bufferpool BPIDX32K; -
Ensure that the EZSCH schema no longer exists.
The next time that a new project is defined using the ADDI build client that targets the DB2 on Cloud server , the database schema will be rebuilt. This takes about 15 minutes.