topic PROC SQL delete from DB2 table that has a qualifier ? in SAS Procedures

PROC SQL delete from DB2 table that has a qualifier ?

saroj — Wed, 21 Jan 2009 12:02:12 GMT

Hi I want to delete rows from a db2 table as shown in below code. the table has a qualifier name followed by table name. ADRPMMT - is the table qualifier and INV_COMM_CONV_RATE is the table name. But it is taking ADRPMMT as SAS libname. please let me know what change is required to the code.
22 PROC SQL;
23 CONNECT TO DB2(DB2SSID=DB39);
24 DELETE FROM ADRPMMT.INV_COMM_CONV_RATE
25 WHERE ROW_LOAD_DATE = &CHKDATE ;
ERROR: Libname ADRPMMT is not assigned.
ERROR: Libname ADRPMMT is not assigned.
ERROR: Libname ADRPMMT is not assigned.
ERROR: Libname ADRPMMT is not assigned.
ERROR: Libname ADRPMMT is not assigned.
ERROR: Libname ADRPMMT is not assigned.

Re: PROC SQL delete from DB2 table that has a qualifier ?

LinusH — Wed, 21 Jan 2009 12:19:46 GMT

If you are using DELETE FROM in "open" proc sql code, it will assume that twp level table names will contain a SAS libname. In fact, it requires that you first assign a libname to your DB2 database.

But by using CONNECT TO, you are using SQL pass-thru. To use pass-thru to delete records enclose your DB2 specific SQL using execute:

EXCUTE( ... ) BY DB2;

/Linus

Re: PROC SQL delete from DB2 table that has a qualifier ?

saroj — Thu, 22 Jan 2009 05:07:24 GMT

Thanks Linus , it worked !