BookmarkSubscribeRSS Feed

Configuring DB2 Data Source at Remote Data Agent (Cloud Data Exchange)

Started ‎02-09-2024 by
Modified ‎02-16-2024 by
Views 250

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.

 

uk_1_CDE_Confgiure_DB2_Data_Source_RDA_1.png

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.

Version history
Last update:
‎02-16-2024 10:34 AM
Updated by:
Contributors

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Free course: Data Literacy Essentials

Data Literacy is for all, even absolute beginners. Jump on board with this free e-learning  and boost your career prospects.

Get Started

Article Tags