macro to collect stats of tera data tables in SAS job

[Sai1]

Hi 

can anyone please answer me how to know the statistics collected or not on a tera data table (column level)which is used as an input in a SAS Di job.

i need to know it bcoz I am asked to prepare a user written transformation (which includes a macro calling with a %include  statement)which will collect the stats from input tera data tables i am really struck with the logic

[Kurt_Bremser]

What do you mean by "stats"?

[Sai1]

Hi sir,

it is statistics sir..

[ballardw]

If the teradata is accessible in a library you should be able to get column information about a table with:

Proc sql;

    select *

    from dictionary.columns

    where Libname= 'MYLIB' and memname='MYTABLE'

    ;

quit;

Where MYLIB is the name of your library in uppercase and MYTABLE is the name of the table also in upper case.

There are a number of dictionary tables to query depending on the information desired but the most of the meta data related to SAS is there, libraries, tables, macro varibles, titles, footnotes, file references and such.

[Sai1]

Spoiler

Thank you sir,for your reply.

I am at intermediate level in SAS ...I tried with dictionary.columns and other dictionary tables too,but I am unable to find the information about the statistics collected on a table(column level)..please assist me further 

[Tom]

The answer depends on what you mean by "statistics collected". COLLECT STATISTICS has a special meaning in Teradata. Are you asking how to query Teradata and find out what statistics it has collected on a table? Or are you just asking how to get information on a table such as the column names or number of records?

[Sai1]

Yes sir,it is the first one...I tried to find out with SAS metadata...but it is not there...so I need to query tera data and find out what statistics it has collected on a table it seems...I tried with sselect * from dbc. statstbl;statement but I am getting user does not access to the table...is my approach correct or is ther any other way?

[Tom]

If you cannot access the metadata views then perhaps you can just ask SAS to run the HELP STATISTICS command and capture the result into a table?

proc sql ;
  connect to teradata ..... ;
  create table stats as 
    select * from connection to teradata
    (help statistics mydb.mytable)
  ;
quit;

[Sai1]

Thanks sir,i hope it will work..but before that i struck with another thing..

i want to read each table schema at run time and call the table

using _input1 and _input2...macro variables i am able to extract the table names...

but i am unable to extract the schema of the tables..

as   _input1_connect and _input2_connect ....macro variables are assigned as( %let _INPUT2_connect =  SERVER="voda8.vodafone.com.au" AUTHDOMAIN="Teradata Auth")in the job.

is there any possibility to convert the libref to teradata schema ?

below is the _input macro variable..

   %let _INPUT = TDMDLV.Subs_Anlyt_Item_Rslt;

[Tom]

Look at the SAS metadata table SASHELP.VLIBNAM.

The value of SYSVALUE where LIBNAME='your libref' and SYSNAME='Schema/User' should have the name of the database in Teradata.

[Sai1]

Thank you so much for your help sir!!

[Sai1]

Hello sir,hope you are doing well..thank you so much for your great help  ..

now i am asked to do the same macro in oracle..

when i execute the query in sql developer it is working fine

below is the code:

BEGIN
 

DBMS_STATS.GATHER_TABLE_STATS (
   ownname   =>      'RTDMASL',
   tabname    =>      'CMPGN_ACCT_1301',
   cascade => false);
END;

but when i tried with sas enterprise guide i am getting an error..i tried with all the possibilities i can..

below is the code:

proc sql;

connect to oracle (user=RTDMASL password=rtdmasl path='SAS1D' );
execute
                    (
                     begin
                             dbms_stats.gather_table_stats(
                             ownname=> 'RTDMASL',
                             indname=> 'CMPGN_ACCT_1301' ,
                             cascade => false);
                     end;
                    )  by oracle;


disconnect from oracle;
            quit;

below is the error message i am getting:

ERROR: ORACLE execute error: ORA-06550: line 1, column 106:PLS-00103: Encountered the symbol ";" when expecting one of the
       following:   . ( ) , * @ % & = - + < / > at in is mod remainder not rem   <an exponent (**)> <> or != or ~= >= <= <> and
       or like like2   like4 likec between || multiset member submultiset.

what is wrong with my code...

[Sai1]

Hi, i am asked to create a job with userwritten ,job includes only transformation  and no tables ,it should be able to collect stats on oracle tables in a particular schema in sas di.

here is the requirement:

Tables with CMPGN_* like will be included to collect the table stats in RTDMASL schema
> User written code module will be used to find out the cmpgn_*  like tables
> User written code module will used to loop throgh the tables  and collect the table stats
>

here is my code:

%macro CMPGN_TBL_Stats_Coll;

proc sql;
connect to oracle (user=RTDMASL password=rtdmasl path='SAS1D' );
create table cmpntbls as
select * from connection to oracle
(
 SELECT
  table_name, owner
FROM
  all_tables
  where owner='RTDMASL' and table_name like 'CMPGN_%'
ORDER BY
  owner, table_name

  );

  disconnect from oracle
;
quit;

options symbolgen;

DATA _NULL_;
SET cmpntbls END=NOMORE;
CALL SYMPUT
('_TABLE' || COMPRESS(PUT(_N_,3.)),
TRIM(table_name));
call symput ('_schema',owner);
IF NOMORE then CALL SYMPUT
('TOT_TAB', PUT(_N_,3.));
RUN;

%DO I=1 %TO &TOT_TAB;

proc sql;

connect to oracle (user=RTDMASL password=rtdmasl path='SAS1D' );
execute(
      begin
      DBMS_STATS.GATHER_TABLE_STATS(
      ownname=> &_schema,
      tabname=>  &&_table&i.,
      cascade => false);
      end;)  by oracle;


disconnect from oracle;
            quit;

            %end;

            %mend CMPGN_TBL_Stats_Coll;


%CMPGN_TBL_Stats_Coll;

and the error message i am getting is :

   Line 215: ERROR: ORACLE execute error: ORA-06550: line 1, column 48: PLS-00201: identifier 'RTDMASL' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored.