How to find all table dependencies in Vertica

Vertica_User

Hi,
I would like to find all the scripts & views which uses the Vertica table. I need this as there are lots of tables which are not in use in our database. If the table is not used in any scripts / views, then we are planning to delete the table in future.

Thanks in Advance

skeswani

each time a projection is used we write a log to dc_projections_used
select * from dc_projections_used;

increase the retention of the above table.

select set_data_collector_policy('ProjectionsUsed', .., ..);
select get_data_collector_policy('ProjectionsUsed');

This will give you a list of projections used, and how often.
The inverse of this set are projections that have not been used.

Vertica_User

Thanks. But, I am trying to find the scripts (programs) used to update / fetch the data in the table.

skeswani

this table (dc_projection_used) has the tx_id and statement_id, which you can join with dc_request_issued to find the query that touched this projection.

Vertica_User

Thanks. Is it possible to find the shell script (.sh) or .SQL file which calls the query.

Jim_Knicely

There is a CLIENT_LABEL column in the DC_REQUESTS_ISSUED table...

dbadmin=> select get_client_label();
 get_client_label
------------------

(1 row)

dbadmin=> select set_client_label('Connection from shell script /home/dbadmin/my_test.sh');
                             set_client_label
---------------------------------------------------------------------------
 client_label set to Connection from shell script /home/dbadmin/my_test.sh
(1 row)

dbadmin=> select count(*) from test;
 count
-------
     1
(1 row)

dbadmin=> select request from dc_requests_issued where client_label = 'Connection from shell script /home/dbadmin/my_test.sh';
                                                       request
----------------------------------------------------------------------------------------------------------------------
 select count(*) from test;
 select request from dc_requests_issued where client_label = 'Connection from shell script /home/dbadmin/my_test.sh';
(2 rows)

Vertica_User

Thanks Jim