Adding data source connections to Data Virtualization to connect with personal credentials

Before you begin

Copy link to section

• Pass-through authentication must be enabled by an administrator. For more information, see Enabling and disabling pass-through authentication in Data Virtualization.
• You must have access to the data source in platform connections so that you can add your credentials to the data source. For more information, see Managing collaborators on platform connections.
• To enable personal credentials, you must first create the connection in the Platform connections page. Ensure you select Personal as the credential option, which requires each user to enter their own credentials for accessing data. You cannot change this option once you create the connection.
  Note: With personal credentials, each user must input their own credentials in Catalogs > Platform assets catalog, or else they will encounter errors when attempting to virtualize or preview Data Virtualization related tasks.
• To use personal credentials, you need to set your API key in IAM by following these steps.

  1. In IAM, navigate to Access.
  2. Navigate to API keys and then select Create.
  3. Run the DVAASUSERAPIKEY stored procedure. For more information, see DVAASUSERAPIKEY stored procedure.

     call dvsys.setDVAASUSERAPIKEY(<USER_API_KEY>)`
     Copy to clipboardCopied to clipboard

Hostname

The data source hostname.

Port

The network port of the data source service.

Database Name

The name of the database for the connection.

Connection Options

Specifies a comma-delimited list of extra options in the following format: '<key1>=<value1>, <key2>=<value2>, ...'.

User

The data source username. The username and password should have higher authority in the data source that you are connecting to. The username and password are used in operations such as getting database metadata or gathering statistics from a catalog or by a query.

Password

The password for the specified user.

SSL

Specifies whether the database connection uses SSL. Valid values are 0 (the connection does not use SSL) and 1 (the connection uses SSL).

Node Name

The node that you are adding the data source to. To find valid node names, run the following query:

SELECT * FROM DVSYS.LISTNODES;
Copy to clipboardCopied to clipboard

Note: Adding too many data sources to the same node impacts performance. To check which nodes already have associated data sources, run the following query:

SELECT * FROM DVSYS.LISTRDBC;
Copy to clipboardCopied to clipboard

CCID

The unique identifier of the platform connection, which can be found included in the URL of the connection.

To find the CCID, navigate to the Connectivity page, select the Platform connections tab, and then select the connection. Then refer to the URL to find the CCID.

Example URL

In the following URL, the CCID is 5728a13e-7e22-4182-82c5-51c246983745

https://dataplatform.dev.cloud.ibm.com/connections/5728a13e-7e22-4182-82c5-51c246983745?catalog_id=d3068196-b097-4244-88a2-4f83dd2ce087&context=cpdaas&redirect_url=https%3A%2F%2Fdataplatform.dev.cloud.ibm.com%2Fdata%2Fconnections%2F%3Fcontext%3Dcpdaas&cancel_url=https%3A%2F%2Fdataplatform.dev.cloud.ibm.com%2Fdata%2Fconnections%2F%3Fcontext%3Dcpdaas&global_connection=true&tearsheet_mode=false

CID

The unique identifier of the data source connection. Run the following command to get the cid from the dvsys.rdb_connections table:

select cid, url from dvsys.rdb_connections;
Copy to clipboardCopied to clipboard

You can also find this identifier in the UI. Go to Data > Data virtualization and expand the connection on the Data sources page to see the CID.

CCDEFINER

The platform’s unique identifier for a user. This identifier is the same as the one that is used to identify the user that is adding the connection to Data Virtualization. This user is also the user who created the connection in platform connections and whose credentials to the data source are being passed in the User and Password parameters. To find a user’s unique identifier, go to Administration > Access Control. The identifier is the value in the User ID column.

The CCDEFINER is the Db2 session user, which you can obtain by running the following command:

SELECT SESSION_USER FROM SYSIBM.SYSDUMMY1
Copy to clipboardCopied to clipboard

Adding connections to Data Virtualization by using personal credentials

Copy link to section

To add the connection, set the configuration options on the Data Virtualization connection by running the setrdbcx SQL command in the Run SQL interface, as shown in the following example:

call dvsys.setrdbcx('DB2','9.30.167.128',50000,'SAMPLE','','<user>','<password>',0,0,'','','qpendpoint_1:6415','CCID=611335a2-164b-4873-be5e-074989d12dee,CCDEFINER=1000330999,USE_PERSONAL_CREDENTIALS=TRUE',?,?,?)
Copy to clipboardCopied to clipboard

For more information about the setrdbcx stored procedure, see setRdbcX stored procedure (Variation 1) or setRdbcX stored procedure (Variation 2).

Editing connections that are added to Data Virtualization by using personal credentials

Copy link to section

After you add a data source connection to Data Virtualization by using personal credentials, do not edit the data source connection in Data Virtualization user interface because the OPTIONS_CSV will be overwritten. To edit a connection that you added by using the setrdbcx, use the additional option EDITCID=CID (Connection ID) of the data source to be edited, as shown in the following example:

dvsys.setrdbcx('DB2','9.30.167.128',50000,'SAMPLE','','db2inst1','db2inst1',0,0,'','','qpendpoint_1:6415','CCID=611335a2-164b-4873-be5e-074989d12dee,CCDEFINER=1000330999,USE_PERSONAL_CREDENTIALS=TRUE,EDITCID=DB210000',?,?,?)
Copy to clipboardCopied to clipboard

For more information about the setrdbcx stored procedure, see setRdbcX stored procedure (Variation 1) or setRdbcX stored procedure (Variation 2).

Limitations and known issues

Copy link to section