Ryslander.com – Page 11 – implementation, consolidation and innovation

How to install and configure DB2 ODBC driver

The following shows you how I got an ODBC connection to DB2 on a Windows Server 2008R2. After doing a search on the Internet I found this page that really helped me a lot. They do it a bit different.

NEW
If you also want to install the IBM Control Center you might want to look at this page. This also installs the ODBC out-of-the-box.

If your need a 32 or 64-bit version for Windows and DB2 version 9.7. You should look for something like this.

On this page you can find and download the ODBC-drivers that fits your system. I you don’t know what version of DB2 that you’re running, you can contact your DBA. The DB2-version can be found using the command DB2level.

Choose either ODBC 32-bit or 64-bit for your DB2-version and download it for Windows.

If your need a 64-bit version of the ODBC-driver for Windows and DB2 version 9.7. You should look for something like this.

NB! You need to create an IBM-account to download the file.

Copy the content of ZIP-file containing the ODBC-driver that you just downloaded to c:\program files\IBM. The path is optional, you can choose a different path if you want to or are using a 32-bit ODBC-driver. The files and folders in c:\program files\IBM, should look something like this.

Start a Windows command-prompt as administrator. If you don’t know how to do this, you can take a look at this page.

Go to the folder c:\program files\IBM\bin and type db2oreg1 –i (if you later want to uninstall it type db2org1 –u).

Go to the folder c:\program files\IBM\bin and type db2oreg1 –setup (Thanks to Noel for this comment).

This will register the DB2 ODBC-driver in Data Sources (ODBC) on Windows. You can take a look at the drivers tab.

NB! If you have installed af 32-bit version of the ODBC-driver on a 64-bit system, then you have to look in the 32-bit version of the ODBC Administrator to find the driver. The 32-bit version of the ODBC Administrator can be found here: C:\Windows\SysWOW64\odbcad32.exe (Thanks to Grae for this comment)

Now you have to setup a ODBC-driver for DB2. You can either make a System or User DSN.
In this example i’m creating a User DSN. Go to the User DSN-tab and press the Add-button.

Choose the DB2 ODBC-driver and press the Finish-button.

Give the DSN a name that you can use in your programs. Here I have just given it the name DB2DB.

Then press the Add-button and go to the Advanced Settings-tab.

Here you need to press the Add-button to add the settings below.
Database: Should point to the database that you want to connect to.
Hostname: Should point to the server where your DB2-server is installed.
Port: The portnumber where the DB2-server on the Hostname is responding.
Protocol: The protocol used to connect to the DB2-server. There’s different options as you can se in the picture below.

The final settings should be the ones listed below.

Now you press the OK-button and you have a new DB2 ODBC connection and your done. You can now use this DB2 ODBC connection in your different programs.

NOTE
If you get the error described below in SAS. Then you most likely didn’t install the correct version 32/64-bit for your operating system. Install the correct bit-version of the Control Center from the IBM DB2 Client Server pack (further information at the start of this post).

The SAS/ACCESS Interface to DB2 cannot be loaded. The SASDBU code appendage could not be loaded

DB2 Client Tools

There’s different client tools available for DB2. You can find a good overview here

Loop a delimited macrovariabel in SAS

This shows how to loop a delimited macrovariabel.

%let Delimitor = ¤;
%let Logins = Login1¤Login2;

%let NumberOfLogins = %sysfunc(countw(&Logins., &Delimitor.));
%put Number of logins: &NumberOfLogins.;

%macro _CreateLogin;

%do J=1 %to &NumberOfLogins;

%let Login = %scan(&Logins., &J, &Delimitor.);
%CreateLogin(_Login=&Login.);

%end;

%mend;

%_CreateLogin;

Adding path to SASAUTOS

From SAS9.2 it’s possible to add a path to eg SASAUTOS. It’s done using ‘insert’ or ‘append’.
Read more about append and insert.

options insert=(sasautos=&SASProgramPath.\Macros);

If you get a strange error, that insert isn’t recognized or something like that. The error might be caused because there’s spaces in the path, that your trying to insert.

Getting path for the SAS-program being executed

/* Gets path for the SAS-program being executed */

%macro GetSASProgramPath;
%qsubstr(%sysget(SAS_EXECFILEPATH),1,%length(%sysget(SAS_EXECFILEPATH))-%length(%sysget(SAS_EXECFILEname)));
%mend;

%let SASProgramPath = %GetSASProgramPath;

This will only work on Windows.

In batchmode you can get the programname with the code below. This will work in eg an AIX environment.

%let ProgramName = %sysfunc(getoption(sysin));
%put &ProgramName;

SAS SQL DB2 pass-through eg select

The SAS-code below shows you how to make SQL pass-through to DB2 and collect the result in a new table.

proc sql noprint;

connect to db2 as db(database=<Your database> user=<Your username> password=<Your password>);
create table <Dummy> as
select * from connection to db
(
select * from <schema>.<table>
);
disconnect from db;
quit;

It is also possible to use authdomain to collect credentials instead of user and password and the DB2 database has to be available to SAS.

SAS SQL DB2 pass-through eg index

The SAS code below shows you how to make SQL DB2 pass-through.

proc sql noprint;
connect to db2 (database=<Your database> user=<Your username> password=”<Your password>”);
execute
(

) by db2;
disconnect from db2;
quit;

If you have your credentials stored in a SAS metadata-server it is possible to use a authdomain and skip entering username and password.

It is done using the following option in SAS:

options
metaserver=”<Your metadataserver>”
metaport=<usually 8561>
metaprotocol=bridge
metarepository=”Foundation”
;

proc sql noprint;
connect to db2 (database=<Your database> authdomain=<Your authdomain>);
execute
(

) by db2;
disconnect from db2;
quit;

The database has to be available to SAS. This can eg be done through your DB2 ControlCenter.

Alternating rows in Excel (danish)

This is the danish translation of the english version for alternation of rows in Excel.

English: =MOD(ROW(),2)=0

Dansih: =REST(RÆKKE();2)=0

DB2 libname in SAS

The syntax is:

libname <name> db2 database=<database> schema=<schema> user=<user> password=<password> in=<tablespace>;

Eg.

libname testname db2 database=mydb schema=myschema user=domain.com\myuser password=”1234Hello” in=mytablespace;

The connection to the database has to be created eg through the DB2 ControlCenter before it is possible to connect to it in SAS.
‘in=’ determins the tablespace where the data is placed.