topic Re: Macro Quoting DB2-Command (SQL-Pass Through) in SAS Programming

Macro Quoting DB2-Command (SQL-Pass Through)

FK1 — Fri, 02 Aug 2019 05:35:20 GMT

Hi everyone,

I keep getting an error message, when trying to execute a DB2-command via pass-through. The problem seems to be, that my macro variable is not being resolved during compilation/execution?

I am pretty sure, the issue at hand is macro quoting. But I do not know what function (%STR(), %QUOTE(), %SUPERQ(), etc.) to use. Here is the error return code of the database:

ERROR: CLI execute error: [IBM][CLI Driver][DB2/AIX64] SQL2306N The table or index "SCHEMA1.&DB2_TBL_NAME" does not exist.

Can anybody give me a hint, on how to write the code properly?

This is, what I got as of now:

%let DB2_TBL_NAME  = Table1;

proc sql;
connect to db2 (database=sas AUTHDOMAIN="DB2Auth");
 EXECUTE( CALL SYSPROC.ADMIN_CMD( 'RUNSTATS ON TABLE SCHEMA1.&DB2_TBL_NAME ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND SAMPLED DETAILED INDEXES ALL SET PROFILE' ))by db2; 
disconnect from db2; 
quit;

Thank's in advance!

Re: Macro Quoting DB2-Command (SQL-Pass Through)

Kurt_Bremser — Fri, 02 Aug 2019 05:47:53 GMT

Single quotes prevent the resolution of macro variables. Use double quotes for strings with macro variables.

Re: Macro Quoting DB2-Command (SQL-Pass Through)

FK1 — Fri, 02 Aug 2019 05:53:41 GMT

Hi Kurt,

I wish, I could, but the DB2-command, does not allow for double quotation marks. Therefore "I am forced" to use singe quotation marks....

With double qoutes, I get this error from DB2:

ERROR: CLI execute error: [IBM][CLI Driver][DB2/AIX64] SQL0107N The name "RUNSTATS ON TABLE SCHEMA1.Table1
ON ALL COL" is too long. The maximum length is "128". SQLSTATE=42622

The issue is NOT, that my command is too long. It is the double quotation marks, that DB2 does not like...

Re: Macro Quoting DB2-Command (SQL-Pass Through)

Tom — Fri, 02 Aug 2019 06:18:09 GMT

You can try using %BQUOTE().

%let DB2_TBL_NAME  = Table1;

proc sql;
connect to db2 (database=sas AUTHDOMAIN="DB2Auth");
 EXECUTE( CALL SYSPROC.ADMIN_CMD(
%bquote('RUNSTATS ON TABLE SCHEMA1.&DB2_TBL_NAME ON ALL COLUMNS WITH DISTRIBUTION ON ALL COLUMNS AND SAMPLED DETAILED INDEXES ALL SET PROFILE' )
))by db2; 
disconnect from db2; 
quit;

Or make the whole quoted string as a macro variable.

%let DB2_TBL_NAME  = Table1;
%let cmd=%sysfunc(quote(
RUNSTATS ON TABLE SCHEMA1.&DB2_TBL_NAME 
ON ALL COLUMNS 
WITH DISTRIBUTION ON ALL COLUMNS 
AND SAMPLED DETAILED INDEXES ALL 
SET PROFILE,%str(%')));

proc sql;
  connect to db2 (database=sas AUTHDOMAIN="DB2Auth");
  EXECUTE( CALL SYSPROC.ADMIN_CMD(&cmd))by db2; 
  disconnect from db2; 
quit;