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!
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;
Single quotes prevent the resolution of macro variables. Use double quotes for strings with macro variables.
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...
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.