Date:         Fri, 2 May 2003 17:59:46 +0200
Reply-To:     Tom Schliemann <trs@ITERATION.DK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Tom Schliemann <trs@ITERATION.DK>
Organization: TDC Internet
Subject:      Updating DB2-2 tables via ODBC With DATA step

Hi



I have a problem updating a db2 table called person. I am trying with the
modify statment of the datastep.
I get the following msg
ERROR: MEMBER level locking for UPDATE or RANDOM access is not supported by
this engine.
Have you ever tryed this, then please help me !!!

SAS log


NOTE: Copyright (c) 1999-2001 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) Proprietary Software Release 8.2 (TS2M0)
Licensed to ITERATION A/S, Site 0089850001.
NOTE: This session is executing on the WIN_PRO platform.


NOTE: SAS initialization used:
real time 3.80 seconds
cpu time 2.08 seconds
1 libname TBC ODBC noprompt=XXXXXXXXXXXXXXXXXXXXXXXXXXXXX
2 DBINDEX=YES USE_ODBC_CL=yes
3 UPDATE_LOCK_TYPE=ROW;
NOTE: Libref TBC was successfully assigned as follows:
Engine: ODBC
Physical Name:
4 Data Pers;
5 length name $40;
6 pnr = 1;
7 name='Tom';
8 output;
9 pnr = 5;
10 name='bla';
11 output;
12 run;
NOTE: The data set WORK.PERS has 2 observations and 2 variables.
NOTE: DATA statement used:
real time 0.69 seconds
cpu time 0.18 seconds

13 data TBC.person;
14 set pers;
15 flag=0;
16 do until(flag);
17 modify TBC.person (DBKEY=pnr) key=pnr;
ERROR: MEMBER level locking for UPDATE or RANDOM access is not supported by
this engine.
18 select (_iorc_);
19 when (%sysrc(_sok)) replace;
20 when (%sysrc(_dsenom))
21 do;
22 _error_=0;
23 flag=1;
24 end;
25 otherwise
26 do;
27 put 'Unexpected ERROR: _iorc_= ' _iorc_;
28 stop;
29 end;
30 end; /* ends SELECT group */
31 end; /* ends DO UNTIL loop */
32 run;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used:
real time 0.81 seconds
cpu time 0.17 seconds

Program more readeable

libname TBC ODBC noprompt="dsn=TBC;uid=iterat2;pwd=xxx" DBINDEX=YES
USE_ODBC_CL=yes UPDATE_LOCK_TYPE=ROW;
Data Pers;
    length name $40;
    pnr = 1;
    name='Tom';
    output;
    pnr = 5;
       name='bla';
    output;
run;

data TBC.person;
    set pers;
    flag=0;
    do until(flag);
    modify TBC.person (DBKEY=pnr) key=pnr;
    select (_iorc_);
        when (%sysrc(_sok)) replace;
        when (%sysrc(_dsenom))
            do;
            _error_=0;
            flag=1;
        end;
        otherwise
            do;
                put 'Unexpected ERROR: _iorc_= ' _iorc_;
                stop;
            end;
        end; /* ends SELECT group */
    end; /* ends DO UNTIL loop */
run;


Regards Tom
Thank you in advance