Configuring Access to IBM Db2 in SAS Viya

1 Like

SAS Viya supports access to numerous databases. Each of them requires more or less effort regarding their configuration. Db2 has come up recently in several customer requests. Let’s walk through the steps of configuring SAS/ACCESS to Db2 in SAS Viya for both SAS Compute Server and CAS. While in this article I’ll give an overview of the procedure, I’ll also share detailed instructions in a separate document (see end of this article).

Native or JDBC/ODBC?

SAS/ACCESS to Db2 is not the easiest one I had to configure. Instead, I could have chosen JDBC to access Db2 because the configuration consists of copying a single file (the Db2 JDBC driver) into a specific directory and that would have been it. ODBC requires more steps.

But, by using alternative routes like ODBC or JDBC, you miss some important features that only the native access (SAS/ACCESS to Db2) can offer. And it’s all about performance: in-database SQL pushdown, data movement reduction, bulkloading and so on. That’s why it’s important to use the native way whenever you can.

Documentation

Before you start, checking the SAS documentation is always a good idea. You can check the requirements for Db2 here. Then, the configuration instructions are placed in a README file that you can access in 2 ways:

On https://my.sas.com, in your order’s “Downloads” section:

In the downloaded README, there’s a section named “Configuring SAS/ACCESS and Data Connectors for SAS Viya 4”.

Or in your deployments assets once you have extracted them ($deploy/sas-bases/examples/data-access/README.md)

Download the Db2 client

It’s not always simple to find the right client to use for a database. Regarding Db2, you should be looking for something named “IBM Data Server Client” on the https://www.ibm.com/support/fixcentral/ portal. The following instructions give more information: https://www.ibm.com/docs/en/db2/11.5?topic=clients-installing-data-server-unix-linux.

Install the Db2 client

There are probably multiple options to get it done and no wrong approaches. But here is what I did to successfully install and configure the Db2 client in a Kubernetes context.

Create a dedicated user

The Db2 client and configuration ultimately require to be owned by the sas user defined inside the SAS Viya deployment (through a NFS mount). This sas user has a UID and GID of 1001.

So, on the jump host or Kubernetes client that you are using to deploy SAS Viya, one approach is to create a new user account with a UID/GID of 1001 (or use the current 1001 UID user if one exists) and install the Db2 client with it.

Here is an example:

sudo groupadd -g 1001 sas
sudo useradd -u 1001 -g 1001 sas -m
sudo passwd sas
# Choose a password for this account

NB: the user name sas is not essential, it could be another name.

Run the installer

As the sas user you can then run the Db2 installer once you have extracted the Db2 client archive and install it in $HOME/sqllib:

./db2_install -b $HOME/sqllib

Validate access to your Db2 database

You can start defining your data sources on the client:

. $HOME/sqllib/db2profile
db2cli writecfg add -database <db2_db> -host <db2_host> -port <db2_port>
db2cli writecfg add -dsn <dsn_name> -database <db2_db> -host <db2_host> -port <db2_port>

And validate that the client is working fine with these definitions:

db2cli validate -dsn <dsn_name> -connect -user <db2-user> -passwd <db2-user-passwd>

Make the Db2 Client accessible from SAS Viya

The client is hopefully working fine on our jump host/client. We need to make it available to SAS Viya engines: SAS Compute and CAS.

Prepare the NFS structure

To do that, we usually setup NFS mounts as described in the following README files (https://my.sas.com or in your deployment assets):

$deploy/sas-bases/examples/sas-compute-server/configure/compute-server-add-nfs-mount.yaml
$deploy/sas-bases/examples/cas/configure/cas-add-nfs-mount.yaml

To make things easy, mount this NFS directory on your machine (jump host/deployment machine) exactly as it is mounted in SAS Viya (same path). Then prepare 2 directories for Db2:

/nfs-path-to-sas-access-clients/db2client
/nfs-path-to-sas-access-clients/db2

Make them owned by the sas user (1001 UID).

Copy Db2 client files to the NFS shared location

As sas, copy the Db2 client from the local installation to the NFS directory (-L to materialize all links as real files and directories):

cp -R -L $HOME/sqllib /nfs-path-to-sas-access-clients/db2client/sqllib

Copy client configuration files

Adjust 2 variables (DB2DIR and INSTHOME) in the db2profile file on NFS:

export DB2_NET_CLIENT_PATH=/nfs-path-to-sas-access-clients/db2client/sqllib

sed -i 's|^DB2DIR=.*|DB2DIR='$DB2_NET_CLIENT_PATH'|g' $DB2_NET_CLIENT_PATH/db2profile
sed -i 's|^INSTHOME=.*|INSTHOME=/nfs-path-to-sas-access-clients/db2|g' $DB2_NET_CLIENT_PATH/db2profile

Run the copy client profile command:

source $DB2_NET_CLIENT_PATH/db2profile
export DB2_APPL_DATA_PATH=/nfs-path-to-sas-access-clients/db2
export DB2_APPL_CFG_PATH=/nfs-path-to-sas-access-clients/db2
$DB2_NET_CLIENT_PATH/bin/db2ccprf -f -t /nfs-path-to-sas-access-clients/db2

This utility copies configuration files in the /nfs-path-to-sas-access-clients/db2 directory.

At this stage, you should have your 2 Db2 directories (db2client and db2) populated, owned by sas, and accessible from SAS Viya.

Update SAS Viya

The last step is to update SAS Viya with some required Db2 environment variables so that SAS Compute Server and CAS can find the Db2 client.

Follow the steps mentioned in the section named “Configuring SAS/ACCESS and Data Connectors for SAS Viya 4 => Installation => Set Environment Variables” of the README file.

Basically, you have to update the sample sas-access.properties with variables needed by Db2:

##########################
# 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

Where

DB2CLIENTPATH=/nfs-path-to-sas-access-clients/db2client/sqllib
and DB2PATH=/nfs-path-to-sas-access-clients/db2

NB: the DB2INSTANCE variable is purposely set to sas which is the predefined user that SAS Viya uses behind the scenes. It is mapped automatically to the uid=1001, regardless of the username chosen earlier.

Then you need to apply this modification to your SAS Viya deployment according to your deployment method (see https://communities.sas.com/t5/SAS-Communities-Library/New-SAS-Viya-Deployment-Methods/ta-p/856206).

Test SAS/ACCESS to Db2

After having restarted CAS and started a new SAS Compute session, you are ready to test. You can adapt the following SAS code to start your validation:

/* Check some environment variables */
%put %sysget(DB2) ;
%put %sysget(INSTHOME) ;
%put %sysget(INST_DIR) ;

/*** SAS Compute Server ***/

/* Native Db2 */

/* sampledsn is a dsn example defined earlier using db2cli writecfg */
libname ndb2 db2 database="sampledsn" user="myuser" password="mypw" ;

/* Other syntaxes */
/*
libname ndb2 db2 noprompt="DSN=sampledsn;UID=myuser;PWD=mypw;" ;
libname ndb2 db2 noprompt="Hostname=mydb2server;Database=sample;ServiceName=50000;UID=myuser;PWD=mypw;" ;
*/

/* List Db2 tables */
proc datasets lib=ndb2 ;
quit ;


/*** CAS ***/

cas mysession ;

/* Drop caslib if exists */
proc cas ;
   dropcaslib / caslib="casndb2" quiet=true ;
quit ;

/* Native Db2 */

/* sampledsn is a dsn example defined earlier using db2cli writecfg */
caslib casndb2 datasource=(srctype="db2" database="sampledsn"
   username="myuser" password="mypw" schema="MYUSER")
   libref=casndb2 ;

/* Other syntaxes */
/*
caslib casndb2 datasource=(srctype="db2" conOpts="DSN=sampledsn;UID=myuser;PWD=mypw;"
   schema="MYUSER")
   libref=casndb2 ;
caslib casndb2 datasource=(srctype="db2" conOpts="Hostname=mydb2server;Database=sample;ServiceName=50000;UID=myuser;PWD=mypw;"
   schema="MYUSER")
   libref=casndb2 ;
*/

/* List Db2 tables */
proc casutil incaslib="casndb2" outcaslib="casndb2" ;
   list files ;
quit ;

cas mysession terminate ;

If you are looking for a more detailed procedure and experiment it, feel free to check the attached document out.

Thanks for reading.