Solved

proc contents in SAS EG with connection to UNIX Db2 Tables

Posted on 2016-08-10
1
79 Views
Last Modified: 2016-08-11
Experts,

I am able to query a DB2 table from SAS EG using a connection to DB2.
 I can assign a library using:
LIBNAME TST DB2 USER=&USR. PASSWORD=&PWD. DATASRC=&SRC.;

The problem is the lib is assigned but I do not see any tables.
Does anyone know what I need to do and is there a way to do a PROC CONTENTS?

I can query using this syntax:
Proc SQL;
   Connect to db2(db=&SRC. user=&USR.   pass=&PWD.);
         Create table mytst as select * from connection to db2
               (Select * from BM.TABLEA);
   Disconnect from db2;

BM=Schema    Table Name = TABLEA
0
Comment
Question by:morinia
1 Comment
 
LVL 8

Accepted Solution

by:
ShannonEE earned 500 total points
ID: 41751702
Hi there  morinia,

Within proc SQL the pseudo library "DICTIONARY" exists and tables within that library can be queried just like any other table.

BUT ....

  • All tables within that library are strictly "READ ONLY"
  • When you query a DICTIONARY table (and there are many tables in the library) proc SQL goes out and collect the information you want.  Hence it is always up to date, but the query may consume more time than you expect.  However I rarely have any problems in that regard.
  • If there is a sas library association option which does not facilitate the collection of information by proc SQL, then the DICTIONARY table will not have any entries for that library.  I know that some external databases have DEFER type processing which may cause problems.
  • the library name "DICTIONALY" is not accessiable outside of proc sql, however for each table in DICTIONARY a view has been set up in library "SASHELP" with a name being "V": followed by the table name within the DICTIONARY library.  Hence you can access the same information in (say) proc print if you wanted.
  • Usually external databases have a system table (or group of tables) that contains definitional information about database objects very similar to PROC SQLs DICTIONARY library.  Look in IBM DB2 documentation for information on these tables.  You should be able to query they using PROC SQL.

If you define a library name pointing to an external data-base before using the PROC SQL, then tables in that library are accessible as if they are SAS datasets.  You may use the "connection to" syntax if you want to or need to use the SQL features of the external database. However that is often not necessary as PROC SQL will do all the necessary conversions.

Using the "connection to" syntax interrupts the flow of your script and makes it harder to do small scale tests etc.

Using the "connection to" syntax can off-load processing to the external database and can lead to better efficiencies especially with grouping, summary functions and joins.  There is some optimisation that PROC SQL does do to help even if you don't use the "connection to" syntax.

Example
libname test DB2 user=&USR password=&PWD datasrc=&SRC;

proc SQL;

     describe table dictionary.tables;
     describe table dictionary.columns;
     
title "list of tables in DB2 data-base";
     select libname,
            memname,
	    memtype,
	    dbms_memtype,
	    memlabel,
	    typemem,
	    nobs,
	    nvar
     from   dictionary.tables
     where  libname="TEST";


title "list of columns in table TABLEA within DB2 data-base";
     select libname,
            memname,
            memtype,
            name,
            type,
            length,
            label
     from   dictionary.columns
     where  libname='TEST' and
            memname='TABLEA'
     order by
            npos;

     ** Another way of getting the information **;
     describe table TEST.TABLEA;
    
     create table TABLEAcopy as
     select *
     from   test.TABLEA;

     describe table work.TABLEAcopy;
     
quit;


proc print data=test.TABLEA noobs;
run;
quit;

proc print data=sashelp.VTable noobs label;
var   libname  memname memtype  dbms_memtype memlabel  typemem  nobs nvar;
quit;


libname test clear;

Open in new window

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now