Libname vs. Connect to
in [SAS]
|
From: Gerry on 17 Oct 2007 11:04 David: We use LIBNAME access to Oracle (on a RAC cluster) pretty heavily, and the "session" issue that your DBAs are referring to pertains to the fact that each time you make a request to Oracle for a particular schema, SAS will open another Oracle session (as far as I know this is Oracle specific, we have not had the issue on SQL Server or DB2). This is a known issue, not a problem, because there are ways to control it, and only really a consideration when running from the SAS IDE or Enterprise Guide. Here is an example of some of our LIBNAME statements for Oracle: Libname TAISMGR Oracle Path=&SYSTEM User=XXXXXXXX Password=XXXXX Schema=TAISMGR Defer=YES DBIndex=YES Preserve_Tab_Names=YES DBMAX_TEXT=32767 ReadBuff=7500 InsertBuff=7500 Connection=GLOBAL ; Libname SATURN ORACLE Path=&SYSTEM User=XXXXXXX Password=XXXXXXX Schema=SATURN Defer=YES DBIndex=YES Preserve_Tab_Names=YES DBMAX_TEXT=32767 ReadBuff=7500 InsertBuff=7500 Connection=GLOBAL ; In the above examples, the DEFER= and CONNECTION= parameters control when and how many sessions SAS opens to Oracle. Without them, the minimum number of sessions would be two if these libname statements were executed in your SAS AUTOEXEC at sign on. Each time it referenced a database object, it potentially could open another session. Without the controlling options, it is possible that users could have many sessions opened under one user id, depending on the number of schemas they have access to (we've had as many as 48 for one user). The "Connection=GLOBAL" (there is also "Connection=UNIQUE") limits SAS to one session per schema (according to SI Tech Support). The "Defer=YES" means do not open a session to the schema until an object is referenced in it. This is covered in the "SAS/ACCESS 9.1 Supplement for Oracle", or you can call SAS tech support (the manual isn't quite as clear as it could be, and a call to tech support helped). -Gerry Gerard T. Pauline Mgr, Internet Applications & University Web Master Computer Systems, DoIT Pace University -----Original Message----- From: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] On Behalf Of David McNulty Sent: Wednesday, October 17, 2007 6:17 AM To: SAS-L(a)LISTSERV.UGA.EDU Subject: SQL: Libname vs. Connect to Hi Folks, Could someone enlighten me on the difference between using a libname to connect to an Oracle database and the pass through sql facility? Example Libname libname mylib odbc schema=name1 user=#### password="####" dsn="name2"; proc sql; create table mytable as select sas_select_clause from mylib.table_name; quit; Example connect to proc sql; connect to oracle (path='pathname' user=#### password=#### preserve_comments); create table mytable as select * from connection to oracle (select oracle_select_clause); quit; Whenever possible I prefer to use libnames as I do not need to worry about the different flavours of SAS and Oracle SQL. For example in SAS I can use the data set where option to qualify base tables and use the sql where statement to manage the join. In Oracle SQL all the where processing (I believe) is within the SQL where statement. Recently the IT bods have raised issues over the efficiency of queries against the company data base. My working assumption are: 1) The SAS libname odbc engine will convert my SAS into an efficient query against the data base. 2) SAS manages all sessions (IT supplied word see question 1) against the Oracle database and does not leave sessions hanging. My questions are: 1) What is a session i.e. what SAS commands start and end a session? 2) Under what circumstances will SAS leave a session open but inactive. 3) Are the queries generated by the SAS/SQL libname interface efficient (and how can I improve efficiency e.g. SAS features to avoid). Regards Dave .. ---------------------------------------------------------------------------- ------------------------------------------ Bank of Scotland plc, Registered in Scotland Number SC327000 Registered office: The Mound, Edinburgh EH1 1YZ. Authorised and regulated by Financial Services Authority. ============================================================================ ==
|
Pages: 1 Prev: SQL: Libname vs. Connect to Next: Proc TABULATE, mixed up on missing |