CODEWITHSUNDEEP
databaseloggingschemadefaultnetezza
jyoti
jyoti

Reputation: 31

default schema name in netezza query log

I am looking for default schema name using which a particular query has been executed(if table name is not fully qualified).In Netezza all the logged queries get stored in histDb, where I can find out the default database for the logged queries, however I am not able to get default database against queries. I can get default schema for any database in '_V_database' meta data view.But user can change the default schema before executing any query using 'Set schema command'. I want to know information about default schema under which a query has been run.

Upvotes: 0

Views: 803

Answers (1)

ScottMcG
ScottMcG

Reputation: 3887

If your system is configured to use schemas and your history database is of the proper version (v2 or v3) to support recording the schema, you can find the schema to which a given query was connected when it ran.

Look for the SCHEMANAME column in the $hist_query_prolog_n (e.g. for version 3, this will be $hist_query_prolog_3 )table in your history database. This is documented thoroughly here.

Remember that you need to quote the history table names in your queries.

select schemaname from "$hist_query_prolog_3"  where querytext like 'select count(1)%';
 SCHEMANAME 
------------
 SCHEMA_A
 SCHEMA_B
 SCHEMA_A
 SCHEMA_A
(4 rows)

Upvotes: 0

Related Questions