@novinosrin
I like your passion.
Here a few more things worth knowing.
You can use the library meta engine to assign a library that is defined in metadata as not pre-assigned (the white nodes in EG) using code as below:
/*
assign library defined in metadata that is not pre-assigned
- explicitly add option readbuff
*/
%let lref=potbnd;
libname &lref meta liburi="SASLibrary?@Libref='&lref'" metaout=data readbuff=100000;
libname &lref list;
The SAS/Access Engine can't push all SAS functions to DB2. The list of functions that can get pushed is here .
If you use any other function in a where clause or join condition then SAS will first load the data into SAS and only execute the function within SAS. The code will still work but performance will degrade because more data gets transferred over the network.
Explicit pass-through SQL lets you write SQL in the database flavor. So here SAS will just send the code as-is to the database. This allows you full control what gets sent to the DB and you can also use DB functionality not available via implicit SQL or SAS datastep code.
/* explicit pass-through SQL using a library definition to connect to a database */
proc sql;
connect using &lref as mycon;
select * from mycon as
(
select src_stm_id
from OTBND.ar
where src_stm_id in (1,30,42,48,49)
with UR
)
;
disconnect from mycon;
quit;
You've posted the connection info for you library....
General DB2-POTBND
Type:Library
Server: SASApp
Engine: DB2
Location: /Shared Data/DB2-POTBND
Options: schema='OTBND'
READ_ISOLATION_LEVL=UR
Datasrc=MODPOTBD
Libref: POTBND
Read Only :No
Temporary: No
...and it looks like the DB2 schema you need is already defined in your libary. I believe you therefore could omit the DB2 schema name in the DB2 SQL part. The advantage of a one level name: If the DB2 schema ever changes then the only place which requires amendment is the metadata libname definition.
.....
(
select src_stm_id
from ar
where src_stm_id in (1,30,42,48,49)
with UR
)
.....
What above also demonstrates: A SAS Libname connects to a single database schema and that's where your code executes. If you want to join tables from different schemas then you would need two different libnames - but in such a case SAS can only push schema specific filter conditions to the DB. It can't push conditions which require access to both schemas (as SAS uses two different connections/libnames).
If you use explicit pass-through SQL then you can use a single libname and have the join in-database. You just need to use a two level name for your tables with the DB2 schema name as first part. This should work as long as the user connecting to the DB can access both schemas.
And last but not least:
I'm not sure that what I've said about READBUFF applies to DB2. Still worth trying though. From the docu:
DB2 under UNIX and PC Hosts
If you do not specify a value for this option, the default buffer size is automatically calculated based on the row length of your data. The SQLExtendedFetch API call is used
Have fun!
