• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

proc contents in SAS EG with connection to UNIX Db2 Tables

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
morinia
Asked:
morinia
1 Solution
 
ShannonEECommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now