How do I grant select permissions in Teradata via a SAS Script?

Question

I'm trying to load a table, create an index, collect statistics, grant select permissions on a table in teradata using SAS to complete the work. SAS is the only piece of software I have to choose from.

The following script successfully deletes the table, load the table, creates the indexes but doesn't collect statistics or grant select. Is there another way of doing this, or am I doing this incorrectly?

libname ias teradata server=<server> USER=&tduser. schema=<dbname> password=&tdpass.;

proc datasets library=ias;
   delete <tablename>;
run;

data ias.<tablename> (bulkload=yes sleep=10 tenacity=4 dbcommit=80000);
  set WORK.<data set name>;
run;

proc sql;
    connect to teradata as td (user=&tduser.  password=&tdpass. tdpid="<server>" schema="<dbname>");
    execute(create index <indexname> (<columnname>) on <dbname>.<tablename>) by td;
    execute(commit) by td;
    execute(collect statistics on <dbname>.<tablename> index (<columnname>);
    execute(commit) by td;
    execute(grant select on <dbname>.<tablename> to <list of users>) by td;
    execute(commit) by td;
disconnect from td;
run;

score 0 · Accepted Answer · answered Jun 19 '12 at 15:07

Everything looks fine to me. How do you know the COLLECT and GRANT commands did not work? Did you get an error message?

A couple notes:

In your PROC SQL CONNECT statement, I suggest that you add the option MODE=TERADATA; that will put the connection in "Teradata" rather than "ANSI" mode, one benefit of which is that commands are "committed" automatically; you do not need those "commit" steps.

Also in your PROC SQL CONNECT statement, you are specifying the SCHEMA option; I don't think that has any effect. Everything in the EXECUTE block must be pure Teradata syntax, so fully qualify all table references.

Be sure that your COLLECT STATISTICS statement is on "index ()" not "index().

Finally, I strongly suggest that you explicitly define the tables primary index and data type using the DBCREATE_TABLE_OPTS and DBTYPE data set options when you create the table, something like:

data ias.<tablename>
     (bulkload-yes sleep=10 tenacity=4
      dbcreate_table_opts='primary index(<columnname>)'
      dbtype=(  <columnname>='<teradata column definiton>'
              , <anothercolumn>='<teradata column definition>'
             ));
       set WORK.<data set name>;
run;

If you do not name the index explicitly, the first variable in your SAS data set will be selected for the index (which may or may not be correct), and the data type might not be appropriate.

If you still have problems, post back with any error messages; and please use an exact example (don't hide column and table names with bracketed references).

I've made the following changes: updated to mode=teradata, removed commit statements, added dbcreate_table_opts, removed schema reference. Closed parenthesis on "execute( ...index ()" to "execute(... index())". I'm now getting an error after the collect statistics statement. proc sql; connect to teradata as td <...>; execute(create index bdw_job_inventory2i(controlm_calendar) on ud812.bdw_job_inventory) by td; execute(collect statistics on ud812.bdw_job_inventory index (job_name)); Error 22-322: Expecting a name. I can run collect stats syntax in TD but not in SAS. — Lee_Str, Jun 19 '12 at 15:29
It should be "collect statistics on ud812.bdw_job_inventory index (bdw_job_inventory2i)", not "index(job_name)", assuming you are referring to the secondary index you created in the previous step. — BellevueBob, Jun 19 '12 at 16:00

How do I grant select permissions in Teradata via a SAS Script?

1 Answers1