topic Re: SAS Connection to the ODBC Server - Locks the Database in SAS Programming

SAS Connection to the ODBC Server - Locks the Database

SNG1 — Mon, 17 Jun 2019 13:25:55 GMT

Hello All,

I am using SAS EG 7.1 (32 bit), I am connecting to a SQL Server via ODBC Data Source Administrator (using the SQL Server Driver).

Using the above I run into issues where when I run a Program, it locks the entire DB I am connecting to...I do use a NOLOCK option in my library statement

i.e. READ_LOCK_TYPE = nolock;

Even with this statement I sometimes end up locking the entire DB, is there something that I am missing? Is there a statement that I need to add in the Program itself to prevent this from happening?

Thanks for all your Help

Re: SAS Connection to the ODBC Server - Locks the Database

SNG1 — Wed, 19 Jun 2019 12:47:07 GMT

Anybody has any solution for these? Much Appreciated!

Thanks

Re: SAS Connection to the ODBC Server - Locks the Database

JBailey — Wed, 19 Jun 2019 13:13:51 GMT

Hi @SNG1

When you say "entire DB" do you mean the table(s) you are dealing with or the entire MS SQL Server database?

Best wishes,

Jeff

Re: SAS Connection to the ODBC Server - Locks the Database

SNG1 — Wed, 19 Jun 2019 13:16:02 GMT

My Apologies, its the table that I am connecting to that gets locked.

Re: SAS Connection to the ODBC Server - Locks the Database

JBailey — Wed, 19 Jun 2019 13:20:10 GMT

Hi @SNG1

That's good. Can you share the code that you are running?

Best wishes,

Jeff

Re: SAS Connection to the ODBC Server - Locks the Database

SNG1 — Wed, 19 Jun 2019 13:24:43 GMT

Thanks for the Quick Reply:

I have the following in THE LIBRARY Statement -

libname wc13b odbc dsn='wc13b' readbuff=3000 insertbuff=3000 dbcommit=3000 READ_LOCK_TYPE=NOLOCK;

I have also tried it with -

libname wc13b odbc dsn='wc13b' READ_LOCK_TYPE=NOLOCK;

My program to the pull the data is very simple -

Proc Sql;
create table SASUSER.clients_B
as
select distinct
Power_ID,
input(compress(phonecell),10.) as Cell_Phone,
LSTNAME,
FIRSTNAME,
input(HAREA, 10.) as HAREA,
input(HPHONE, 10.) as HPHONE,
input(catx('',HAREA, HPHONE),10.) AS Home_Phone

from wc13.clients
;quit;

The clients table gets locked

Re: SAS Connection to the ODBC Server - Locks the Database

JBailey — Wed, 19 Jun 2019 13:34:20 GMT

Hi @SNG1

Which version of SAS are you using?

Are you viewing the table in an EG data grid (not sure exactly what it is called) while you are running this code?

Best wishes,

Jeff

Re: SAS Connection to the ODBC Server - Locks the Database

SNG1 — Wed, 19 Jun 2019 13:37:18 GMT

Its SAS Enterprise GUIDE 7.1 and I am viewing the data in the EG Grid.

Re: SAS Connection to the ODBC Server - Locks the Database

JBailey — Wed, 19 Jun 2019 13:41:54 GMT

Hi @SNG1

I am looking for something like SAS 9.4M6 for the version of SAS that you are connecting to.

Best wishes,

Jeff

Re: SAS Connection to the ODBC Server - Locks the Database

JBailey — Wed, 19 Jun 2019 13:44:00 GMT

Hi @SNG1

Try your program while not viewing data in the grid.

Best wishes,

Jeff

Re: SAS Connection to the ODBC Server - Locks the Database

SNG1 — Wed, 19 Jun 2019 13:50:51 GMT

Thanks

the version Is SAS i see if SAS 9.4

Re: SAS Connection to the ODBC Server - Locks the Database

JBailey — Thu, 25 Jul 2019 19:51:33 GMT

Hi @SNG1

Try your program while not viewing data in the grid. This should stop the locking issue.

I have researched this problem. Opening a table in the Data Grid creates a READ lock on the table. According to the developers, it "is a SAS thing, and not an EG thing." I am currently trying to find out what this means. I am looking into this.

Here are some things that may help, but probably won't:

Using SAS/SHARE can reduce the locking to record-level (I know, this is a non-solution).
Enterprise Guide 8.1 holds the lock for a much shorter time (3 minutes of inactivity, can be set to a minimum of 1 minute). Previous releases of EG close the data grid after 30 minutes.

Best wishes,

Jeff