Solved

proc contents in SAS EG with connection to UNIX Db2 Tables

Posted on 2016-08-10
1
55 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
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…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

760 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