Configuring DB2 Data Source at Remote Data Agent (Cloud Data Exchange)
In the SAS Viya Cloud Data Exchange (CDE) environment, the Remote Data Agent supports the DB2 database access as well along with other databases. The DB2 client configuration at the On-Premises server is a vital task in the process of DB2 data source configuration. The CDE administrator is required to deploy a compatible DB2 client to the location mounted with the Data Agent container.
In this post, I discuss the configuration of the DB2 data source at the Remote Data agent.
The Remote Data Agent is a containerized software and runs with docker runtime. The CDE administrator can mount an NFS location with the DB2 client deployed while starting the Remote Data Agent container.
The following pics describe the local/NFS folder with the DB2 client mounted to the Remote Data Agent container and access to the DB2 database.
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
The following steps describe the local/NFS folder with the DB2 client configuration and the Remote Data Agent mount to the same location with the DB2 data source configuration.
Deploy Compatible DB2 Client
The CDE administrator is required to deploy a compatible DB2 client to a location reachable from the remote data agent server. The DB2 client compatibility should be tested against the DB2 database server.
The DB2 client can be downloaded by using the instructions listed at the site https://www.ibm.com/docs/en/db2/11.5?topic=clients-installing-data-server-unix-linux.
The download file is a compressed JAR file. It needs to be uncompressed on Unix server as user “sas” (uid 1001:1001). Place the compressed file “v11.5.8_linuxx64_client.tar.gz” under /tmp folder to execute the following statement.
Code:
sudo su – sas
cd
tar -xvf /tmp/v*linuxx64_client.tar.gz
./client/db2_install -b $HOME/sqllib -f sysreq -y
Note: -f sysreq is used to not check the minimum requirements on the server
Configure and Validate DB2 Client against DB2 Database
Source the DB2 client profile on Unix server as user “sas” (uid 1001:1001).
Code:
. $HOME/sqllib/db2profile
Create a DB2 database DSN profile under client configuration on the Unix server as user “sas” (uid 1001:1001).
Code:
db2cli writecfg add -database sample -host $(hostname -f) -port 50000
db2cli writecfg add -dsn sampledsn -database sample -host $(hostname -f) -port 50000
cat $HOME/sqllib/cfg/db2dsdriver.cfg
Validate the access to DB2 DSN using db2cli on the Unix server as user “sas” (uid 1001:1001).
Code:
db2cli validate -dsn sampledsn -connect -user db2inst1 -passwd
Log:
sas@p04206-jump-vm:~$ db2cli validate -dsn sampledsn -connect -user db2inst1 -passwd XXXXX
======================================================================
Client information for the current copy:
======================================================================
Client Package Type : IBM Data Server Client
Client Version (level/bit): DB2 v11.5.8.0 (s2209201700/64-bit)
Client Platform : Linux/X8664
Install/Instance Path : /home/sas/sqllib
….
….
…
======================================================================
Connection attempt for data source name "sampledsn":
======================================================================
[SUCCESS]
======================================================================
The validation is completed.
======================================================================
sas@p04206-jump-vm:~$
Make the DB2 Client Accessible from the CDE RDA container
Prepare a folder structure as user “sas” at the shared location available to the RDA container to copy the customized DB2 client files.
Code:
mkdir -p /viya-share/gelenv/config/access-clients/db2client
mkdir -p /viya-share/gelenv/config/access-clients/db2
ls -l /viya-share/gelenv/config/access-clients/
Copy the DB2 Client files as user “sas” from the home folder to the shared location available to the RDA container.
Code:
cp -R -L $HOME/sqllib /viya-share/gelenv/config/access-clients/db2client/sqllib
chmod -R 755 /viya-share/gelenv/config/access-clients/db2client/
Update the db2profile as user “sas” with new paths at the shared location.
Code:
export DB2_NET_CLIENT_PATH=/viya-share/gelenv/config/access-clients/db2client/sqllib
sed -i 's|^DB2DIR=.*|DB2DIR='$DB2_NET_CLIENT_PATH'|g' $DB2_NET_CLIENT_PATH/db2profile
sed -i 's|^INSTHOME=.*|INSTHOME=/viya-share/gelenv/config/access-clients/db2|g' $DB2_NET_CLIENT_PATH/db2profile
# DB2INSTANCE should already be set to the user who installed the client (sas for us)
# DB2INSTANCE=
Copy the db2profile to the shared location db2 folder.
Code:
export DB2_NET_CLIENT_PATH=/viya-share/gelenv/config/access-clients/db2client/sqllib
source $DB2_NET_CLIENT_PATH/db2profile
export DB2_APPL_DATA_PATH=/viya-share/gelenv/config/access-clients/db2
export DB2_APPL_CFG_PATH=/viya-share/gelenv/config/access-clients/db2
$DB2_NET_CLIENT_PATH/bin/db2ccprf -f -t /viya-share/gelenv/config/access-clients/db2
chmod -R 755 /viya-share/gelenv/config/access-clients/db2
Update the sas-access.properties with the DB2 environment Variable
The sas-access.properties file is meant for SAS/ACCESS engine-related environment variables. Users can include and update the respective access engine environment variables. The following variable is related to the DB2 client location and configuration.
Code:
cd ~/CDERemoteDA_Deploy
DB2CLIENTPATH=/viya-share/gelenv/config/access-clients/db2client/sqllib
DB2PATH=/viya-share/gelenv/config/access-clients/db2
tee sas-access-db2.properties > /dev/null << EOF
##########################
# SAS/ACCESS to DB2
##########################
CUR_INSTHOME=
CUR_INSTNAME=
DASWORKDIR=
DB2DIR=${DB2CLIENTPATH}
DB2INSTANCE=sas
DB2LIB=${DB2CLIENTPATH}
DB2_HOME=${DB2CLIENTPATH}
DB2_NET_CLIENT_PATH=${DB2CLIENTPATH}
IBM_DB_DIR=${DB2CLIENTPATH}
IBM_DB_HOME=${DB2CLIENTPATH}
IBM_DB_INCLUDE=${DB2CLIENTPATH}
IBM_DB_LIB=${DB2CLIENTPATH}
INSTHOME=${DB2PATH}
INST_DIR=${DB2CLIENTPATH}
PREV_DB2_PATH=
DB2=${DB2CLIENTPATH}/lib64:${DB2CLIENTPATH}/lib64/gskit:${DB2CLIENTPATH}/lib32
DB2_BIN=${DB2CLIENTPATH}/bin:${DB2CLIENTPATH}/adm:${DB2CLIENTPATH}/misc
EOF
more sas-access-db2.properties
cat sas-access-db2.properties >> sas-access.properties
Start the RDA service with the DB2 client location folder
Start the RDA service with the local shared folder (e.g. /viya-share/gelenv) mounted to the RDA container. The mounted folder contains the DB2 client and configuration files.
Code:
./container-manager start \
--license-path SASViyaV4_9CV11D_license.jwt \
--sasdata sasdata \
--vars da-vars.env \
--data sasdata/data \
--mount /viya-share/gelenv:/viya-share/gelenv \
--access-vars sas-access.properties \
sas-data-agent-server-remote
Create a DB2 Security Domain at RDA server
Create a DB2 security domain on the RDA server to keep the SAS Viya user identity and DB2 database credentials in pairs.
Code:
sas-viya dagentsrv servers set-default --data-agent sas-data-agent-server-remote
sas-viya dagentsrv security domains create --domain DB2DOM1
sas-viya dagentsrv security domains list
Log:
jumpuser@p04206-jump-vm:~/CDERemoteDA_Deploy$ sas-viya dagentsrv security domains create --domain DB2DOM1
The domain was successfully created.
jumpuser@utkuma-p04206-jump-vm:~/CDERemoteDA_Deploy$
jumpuser@p04206-jump-vm:~/CDERemoteDA_Deploy$ sas-viya dagentsrv security domains list
ID Type Label Description
kerberos GSSCredential Kerberos credentials captured by logon and cached for delegation
DefaultAuth password The default authentication domain for SAS Viya
EsriAuth password EsriAuth Esri authentication credential domain
sas.naturalLanguageConversations.connectors connectorDomain
EsriPortalAuth password EsriPortalAuth Local Esri portal authentication credential domain
EsriPortalRefreshToken token EsriPortalRefreshToken Esri portal OAuth2 refresh credential domain
DB2DOM1 shadow
jumpuser@p04206-jump-vm:~/CDERemoteDA_Deploy$
• Add Viya user identity to DB2 Security Domain at RDA server
Add the SAS Viya user identity with the Oracle database user ID and password to access the database objects from the SAS Viya applications. The Oracle database user password used in the following statement is a one-time user credentials setup. The Password of the database user never leaves the on-premises Remote Data Agent Server. It resides in the SAS Secrets Manager (Vault) at the Remote Data Agent Server.
Code:
### gatedemo001@gelenable.sas.com - SAS Viya user id. ###
### db2inst1 – DB2 database user ###
### xxxxxxxxxx – DB2 database password for user db2inst1 ###
sas-viya dagentsrv security credentials create --domain DB2DOM1 --identity gatedemo001@gelenable.sas.com --username db2inst1 --password ‘xxxxxxx’
sas-viya dagentsrv security credentials create --domain DB2DOM1 --identity sasadm@gelenable.sas.com --username db2inst1 --password 'xxxxxxxxxx'
sas-viya dagentsrv security credentials create --domain DB2DOM1 --identity geldmui@gelenable.sas.com --username db2inst1 --password 'xxxxxxxxxx'
sas-viya dagentsrv security credentials list --domain DB2DOM1
Log:
jumpuser@p04206-jump-vm:~/CDERemoteDA_Deploy$ sas-viya dagentsrv security credentials list --domain DB2DOM1
Identity Type UserId
gatedemo001@gelenable.sas.com user db2inst1
geldmui@gelenable.sas.com user db2inst1
sasadm@gelenable.sas.com user db2inst1
jumpuser@p04206-jump-vm:~/CDERemoteDA_Deploy$
Create a DB2 data source at RDA
The following statement creates a DB2 data service at the Remote Data Agent using DB2 DSN name as a database name. The statement uses the DB2 domain name which has the database user-id password saved with the SAS Viya user identity.
Code:
sas-viya dagentsrv data-services create db2 --name db2srv12 --domain DB2DOM1 --database-name sampledsn
sas-viya dagentsrv services list
Log:
jumpuser-p04206-jump-vm:~$ sas-viya dagentsrv data-services create db2 --name db2srv12 --domain DB2DOM1 --database-name sampledsn
The data service was successfully created.
jumpuser@p04206-jump-vm:~$
jumpuser@p04206-jump-vm:~$ sas-viya dagentsrv services list
Name Type Domain Version Options
BASE base --- 2.5 ---
__SERVER__ server --- 2.5 PURGE_CACHE=30;CASE_SENSITIVITY=(OBJECT=F;COLUMN=F);CACHE=(NAME=AS;TIMEOUT=300)
db2srv12 db2 DB2DOM1 2.5 CONOPTS=(DRIVER=DB2;DATABASE=sampledsn);CASE_SENSITIVITY=(OBJECT=T;COLUMN=T)
jumpuser@p04206-jump-vm:~$
Test the DB2 Remote Data Agent data source
In this case, the login to the DB2 database is using the Viya identity user to fetch the database password from the DB2 security domain and SAS security vault. The Viya identity user is authenticated against the Viya platform using the sas-viya CLI.
Code:
sas-viya dagentsrv data-sources test --name db2srv12
Log:
jumpuser@04206-jump-vm:~$ sas-viya dagentsrv data-sources test --name db2srv12
Successfully connected to data source db2srv12.
jumpuser@p04206-jump-vm:~$
List the DB2 database table and data from a DB2 data source table
With a successful test connection to the DB2 data source, the user can list the data table and data from the DB2 database using the sas-viya CLI.
Code:
sas-viya dagentsrv data-sources tables list --data-source db2srv12 --catalog db2srv12 --schema DB2INST1
sas-viya dagentsrv sql --sql "select * from DB2INST1.department " --dsn db2srv12
Log:
jumpuser@p04206-jump-vm:~$ sas-viya dagentsrv data-sources tables list --data-source db2srv12 --catalog db2srv12 --schema DB2INST1
Name Type Native Catalog
ACT TABLE NULL
ADEFUSR MATERIALIZED QUERY TABLE NULL
CL_SCHED TABLE NULL
DEPARTMENT TABLE NULL
DEPT ALIAS NULL
EMP ALIAS NULL
EMP_ACT ALIAS NULL
…
…..
…
jumpuser@p04206-jump-vm:~$ sas-viya dagentsrv sql --sql "select * from DB2INST1.department " --dsn db2srv12
ADMRDEPT DEPTNAME DEPTNO LOCATION MGRNO
======== ======== ====== ======== =====
A00 SPIFFY COMPUTER SERVICE DIV. A00 000010
A00 PLANNING B01 000020
A00 INFORMATION CENTER C01 000030
A00 DEVELOPMENT CENTER D01
D01 MANUFACTURING SYSTEMS D11 000060
D01 ADMINISTRATION SYSTEMS D21 000070
….
…..
jumpuser@p04206-jump-vm:~$
SAS Access to DB2 Data Source at Remote Data Agent (RDA)
With the DB2 data source configured at the Remote Data Agent, users can access the DB2 data tables from the SAS Compute Server using the CDE LIBNAME statement. The following code describes the access of a DB2 database table from a DB2 RDA data source.
Code:
LIBNAME cdedb2 cde dataagentname="sas-data-agent-server-remote"
dsn=db2srv12 preserve_tab_names=yes;
data cdedb2.FISH_SAS ;
set sashelp.fish ;
run;
Proc SQL outobs=20;
select * from cdedb2.FISH_SAS ;
run;quit;
Log:
79
80 LIBNAME cdedb2 cde dataagentname="sas-data-agent-server-remote"
81 dsn=db2srv12 preserve_tab_names=yes;
NOTE: Libref CDEDB2 was successfully assigned as follows:
Engine: CDE
Physical Name: db2srv12
82
83
80
81 data cdedb2.FISH_SAS ;
82 set sashelp.fish ;
83 run;
NOTE: There were 159 observations read from the data set SASHELP.FISH.
NOTE: The data set CDEDB2.FISH_SAS has 159 observations and 7 variables.
NOTE: DATA statement used (Total process time):
real time 1.41 seconds
cpu time 0.10 seconds
84
85
80 Proc SQL outobs=20;
81 select * from cdedb2.FISH_SAS ;
WARNING: Statement terminated early due to OUTOBS=20 option.
82 run;quit;
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
NOTE: The PROCEDURE SQL printed page 1.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.16 seconds
cpu time 0.05 seconds
83
CAS Access to DB2 Data Source at Remote Data Agent
With the DB2 data source configured at the Remote Data Agent, users can access the DB2 data tables from the CAS using the CDE data connector. The following code describes the CAS access to the DB2 table from a DB2 data source using the CDE CASLIB statement.
Code:
CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true);
caslib cdedb12 datasource=(
srctype="clouddex",
dataAgentName="sas-data-agent-server-remote",
catalog="DB2SRV12",
schema="DB2INST1",
conopts="dsn=db2srv12"
) libref=cdedb12;
proc casutil incaslib="cdedb12";
list files ;
run;quit;
/* CAS load from CDE RDA DB2 table */
proc casutil incaslib="cdedb12" outcaslib="cdedb12";
load casdata="DEPT" casout="DEPT" replace ;
list tables ;
run;quit;
/* Save CAS table to RDA DB2 database */
proc casutil incaslib="cdedb12" outcaslib="cdedb12" ;
droptable casdata="SAS_CARS" quiet ;
load data=sashelp.cars casout="SAS_CARS" ;
save casdata="SAS_CARS" casout="SAS_CARS" replace ;
list files ;
quit ;
cas mysession terminate;
Log:
81
82 caslib cdedb12 datasource=(
83 srctype="clouddex",
84 dataAgentName="sas-data-agent-server-remote",
85 catalog="DB2SRV12",
86 schema="DB2INST1",
87 conopts="dsn=db2srv12"
88 ) libref=cdedb12;
NOTE: Executing action 'table.addCaslib'.
NOTE: 'CDEDB12' is now the active caslib.
NOTE: Cloud Analytic Services added the caslib 'CDEDB12'.
NOTE: Action 'table.addCaslib' used (Total process time):
NOTE: real time 0.011457 seconds
80 /* CAS load from CDE RDA DB2 table */
81 proc casutil incaslib="cdedb12" outcaslib="cdedb12";
NOTE: The UUID 'a2d24d50-926b-184c-90a0-756067585be5' is connected using session MYSESSION.
82 load casdata="DEPT" casout="DEPT" replace ;
NOTE: Executing action 'table.loadTable'.
NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'.
WARNING: Using server default session timeout of 3600
NOTE: Cloud Analytic Services made the external data from DEPT available as table DEPT in caslib cdedb12.
NOTE: Action 'table.loadTable' used (Total process time):
NOTE: real time 0.902593 seconds
NOTE: SASHELP.CARS was successfully added to the "CDEDB12" caslib as "SAS_CARS".
84 save casdata="SAS_CARS" casout="SAS_CARS" replace ;
NOTE: Executing action 'table.save'.
NOTE: Performing serial SaveTable action using SAS Data Connector to Cloud Data Exchange.
NOTE: Connecting using the OAuth token for CAS user 'geldmui@gelenable.sas.com'.
WARNING: Using server default session timeout of 3600
NOTE: Cloud Analytic Services saved the file SAS_CARS in caslib CDEDB12.
NOTE: Action 'table.save' used (Total process time):
NOTE: real time 3.448311 seconds
Important Links:
Cloud Data Exchange for the SAS Viya Platform
Cloud Data Exchange for the SAS Viya Platform
SAS Viya Cloud Data Exchange Deployment and Configuration (Part -1)
SAS Viya Cloud Data Exchange Deployment and Configuration (Part -2)
Configuring BASE SAS Data Source at Remote Data Agent (Cloud Data Exchange)
Configuring Oracle Data Source at Remote Data Agent (Cloud Data Exchange)
Configuring SAS Federated Data Source at Remote Data Agent (Cloud Data Exchange)
Find more articles from SAS Global Enablement and Learning here.
