get the db2 table names in sas

[sfffdg]

I have 2 questions .

1)How to access the table names from Db2 schema using sas  ,if the libnamestatement is as below?

rsubmit;
libname ABC db2 complete="userid=XXXXX;pwd=XXX;datasource=XXXX;"
schema=ABC;
endrsubmit;

2)Also if I want the table names having a particular column,

Some how the below sql dictionary query does not give the required results .

rsubmit;
proc sql;
create table a as
select name
from dictionary.columns
where upcase(libname) = 'ABC' and upcase(memname) like '%TRANSFORM%';
quit;
endrsubmit;

Is the query wrong?

[Ksharp]

Your code look right. Make sure Libname is successfully assigned. (Especially the SCHEMA is right)
Or could try this :

libname ABC db2 dsn=xxx schema=ABC;

proc sql;
connect to db2(dsn=xxx user=xxx pw=xxx);
select * from
connection to db2(DB2::SQLTables);
quit;

[Patrick]

Your SQL looks right BUT it needs to be in the place where you issue the libname statement. 

You have two rsubmit blocks - one where you execute the libname and one where you execute the SQL. 

rsubmit creates a new SAS session and the libname statement will only be valid within this SAS session. If you don't "config" your 2nd rsubmit in a way that it re-connects to the session you created with the first rsubmit then the libref won't exist and though the SQL querying the dictionary tables with a selection on this libref will return zero rows.

The SQL as such looks o.k.

Do you need rsubmit blocks at all? The only reason would be that you need to connect to a different SAS server which has the access to the database ...but then the rsubmit would highly likely have some more options. 

[Kurt_Bremser]

Maxim 2: Read the Log.

See if the LIBNAME works at all.

If in doubt, run both code parts in a single RSUBMIT and post the log here, using this button:

[Patrick]

Do you get any result if just selecting for the libname on its own?
 

where libname = 'LIB1'

I've run below code using a libname that points to Postgres and things work as expected. I'm not aware that there is anything "special" with DB2 that would prevent such code to work.

proc sql;
  create table test as
  select memname, name
  from dictionary.columns
  where 
    upcase(libname) = 'ABC'  
    and upcase(name) like 'ENTRY%'
  order by memname,name
  ;
quit;

[LinusH]

Two reasons why this might not give you desired result:

- You don't have the appropriate authorization for the tables you wish to query.

- Some table (and column) names may exceed SAS 32 char limit for object names. They will be visible using @Ksharps method though.