Download entire DB2 database into a local SAS library.

[Yves_Boonen]

I recently started a new project at a bank. I need to use SAS to analyze data from two different databases.

1. An IBM QMF database (DB2)

2. A Java webbased database

Due to access restrictions I will not be able to connect SAS to the Java database and due to security purposes I will most likely never get the access required. Instead I will receive CSV-file loadouts to work with. I managed to import said data into a local SAS library.

Next up I want to connect to the IBM QMF database and download all the existing data into a local SAS library. I found a table under SIGNDATA.REMOTES, which contains a lot of information regarding remote connections within SAS. Variables within this table are remote, description, enviroment, operating system, hostname and port. This is the database I need to connect with.

[SERVER INFO REMOVED]

When trying to connect, using the information above, I managed to create the following script.

LIBNAME LOCLIB "C:\temp\LOCAL_LIBRARY";

RSUBMIT;

LIBNAME REMLIB "HOSTNAME" SERVER=REMOTE_NAME;

PROC DOWNLOAD

INLIB=LOCLIB

OUTLIB=REMLIB;

RUN;

QUIT;

 ENDRSUBMIT;

This script results in two errors, which I can't seem to solve.

1. ERROR: Attempt to connect to server SSWC failed.

    ERROR: A communication subsystem partner link setup request failure has occurred.

    ERROR: XMS Communication Failure: requested-link XVT not found.

    ERROR: Error in the LIBNAME statement.

2. ERROR: Libref LOCLIB is not assigned.

I am new to SAS and already tasked with a quite a challenge. I tried looking for tutorials to get me started, but the tutorials found on support.sas.com mainly use SAS Studio, which appears to be missing from my installation. Instead I have access to the following programs, see attachment. Can someone also point me to the right place to find tutorials that suit my needs, so I can start studying? I will have to connect to databases, download all the content to a local SAS library and create reports from the data using SQL.

EDIT: The screenshot in the attachments displays all the available SAS software. Perhaps I missed my SAS Studio or perhaps it has a different name now. Anyway, if you could point me into the right direction, software- and tutorialwise, that would be great.

[Reeza]

I would highly recommend deleting your server information from your post, espeically if you work at a bank....

[Reeza · Accepted solution]

You have a DB2 database.

Set up an ODBC connection to said database as a user DSN. This is old, but probably still valid:

https://support.sas.com/techsup/technote/ts765.pdf

Once you have that set up you can use the libname reference and access your data. I would recommend against downloading the entire database, but I have a feeling you will anyways. 

[Reeza]

Quick add on, the courses, specifically programming 1, has instructions on how to work with a variety of SAS set ups. Use the appropriate instructions in the set up guide.

See the resources tab on the right hand side of the page here:

https://communities.sas.com/t5/SAS-Analytics-U/bd-p/sas_analytics_u

[RW9]

Your best bet is to sit down with your IT group, ensure that you (or your machine/image/network) has the required privilidges, can access the database etc.  Its going to be very difficult for us to say anything without lots of information on setups/networks etc.  That would be my advice on your first question.

One the second part, if your new to SAS, then my first question would be, how do you aim to deliver on this.  It sounds like a fairly large process, many data imports, cleaning, summarising, reporting etc.  Am not sure a few tutorials will set you up wel enough.  Anyways the free stuff on SAS that you have found, would still be a starting point.  Base SAS - this is the actual language - is common through all SAS products.  SAS Studio (University Edition) is a stripped down version of SAS to be used as a learning tool, you already appear to have the full product available, so you should be fine to run anything - although note you might not have the test data, and you will have to change library references etc.

You have also fallen into one hole straight away - "create reports from the data using SQL.".  Why?  Don't start limiting yourself to particular strains of thinking, SAS does have an SQL compiler, however in a lot of cases it has its own summary procedures and ways of working which can be less resource intensive and faster.  Use what is apropriate to the task.

Other than that, there is plenty of information to be had on these forums, and if you need graph information, check out this blog which has many examples of all types of graphs:

http://blogs.sas.com/content/graphicallyspeaking/

You will also find anything by Lex Jansen worth reading:

http://www.lexjansen.com/

And always read the manual.  Best of luck.

[Kurt_Bremser]

Aside from the fact that you seem to have posted highly confidential data that could get you fired and even legally prosecuted, I notice this:

libname remlib "......" server=.....;

This syntax of the libname statement does not connect to DB/2, it connects to a remote library of a SAS/CONNECT server.

To connect to DB/2, you need

- SAS ACCESS for DB/2 (installed and licensed)

- a DB/2 client installed on the machine that runs SAS

- a DB/2 "instance" defined on that machine that sets up the connection to the database you need to access

- the correct syntax of LIBNAME for Relational Databases

[Yves_Boonen]

Thanks for all the feedback and suggestions on where to start this challenge. I will try to set up an ODBC connection and try to download only the required data from the database.

Much appreciated!