IBM Netezza DBA SQL Queries

IBM Netezza DBA SQL Queries

SQL to find list of views, Owner and their definitions in a NZ DB:

SELECT VIEWNAME,OWNER,DEFINITION FROM _V_VIEW WHERE OBJTYPE='VIEW';

SQL to get a list of tables in a database:

SELECT TABLENAME, OWNER, CREATEDATE FROM _V_TABLE WHERE OBJTYPE='TABLE';

or connect to database like below

nzsql –d PROD

nzsql>\d

SQL to find a list of columns from a table or a view:

SELECT ATTNUM, ATTNAME FROM _V_RELATION_COLUMN WHERE NAME=UPPER('<TABLE NAME>')

ORDER BY ATTNUM ASC;

SQL to find list of user groups on the box:

SELECT GROUPNAME,OWNER,CREATEDATE,ROWLIMIT,SESSIONTIMEOUT,

QUERYTIMEOUT,DEF_PRIORITY,MAX_PRIORITY FROM _V_GROUP;

SQL to find list of users and the groups they are in, on the box:

SELECT GROUPNAME, OWNER, USERNAME FROM _V_GROUPUSERS where GROUPNAME='PROD_RO' ;

Netezza NZSQL command:--

\dt                          --- In nzsql session to find the list tables

\dv                         ---To get list of views

\dmv                      ---List of materialized views

\l                            ---List of databases

\dg                         ---List of groups

\du                         ---List of users

\dpu                       ---Permissions set to a user

\dT                         ---List of datatypes

\d <tab_name>      ---DESC the table

\act                        ---Show current active sessions

\df                          ---List functions

\l                            ---List databases

\dT                         ---List data types

\du                         ---List users

\dg                         ---List groups

\dpu                       ---List permissions granted to a user

\dpg                       ---List permissions granted to a group

Utility from Netezza Software Support tools

Utility from Netezza Software Support tools. We can use these utility for different purpose.

nz_abort	Abort the specified user sessions.
nz_altered_tables	Display information about all versioned (ALTER’ed) tables.
nz_backup	To backup (or restore) one or more tables.
nz_backup_size_estimate	To estimate the storage requirements of a ‘-differential’ database backup.
nz_best_practices	Report on how well a database conforms to a subset of “best practices”.
nz_build_html_help_output	To generate HTML output that documents all of the individual nz_*** scripts.
nz_catalog_diff	Display any “diff”erences between two different versions of the NPS catalogs.
nz_catalog_dump	To dump out (describe) the catalog definition of all system tables and views.
nz_catalog_size	To report information about the size of the catalog that resides on the host.
nz_change_owner	To globally change the owner of a set of objects.
nz_check_disks	To show S.M.A.R.T. information concerning the status of each SPU’s disk drive.
nz_check_disk_scan_speeds	Check the read/write speed of each disk or ‘dataslice’.
nz_check_statistics	Checks that a table’s statistics are appropriate (based on the table’s DDL)
nz_check_views	Check each VIEW to make sure it is not obsolete (and in need of rebuilding).
nz_cksum	Checksum and count the rows in a table.
nz_clone	To clone the DDL for an existing object, and optionally assign it a new name.
nz_columns	Create a table that will provide all column definitions across all databases.
nz_compiler_check	Verify that the C++ compiler (and its license) are operating correctly.
nz_compiler_stats	Report various statistics about the utilization of the object code cache.
nz_compressedTableRatio	Estimate the compression ratio of a table or materialized view.
nz_compress_old_files	Compress (via gzip) old + unused files under the /nz directory.
nz_core	Dump the program execution/backtrace from a core file.
nz_db_group_access_listing	To show what groups have been granted access to what databases.
nz_db_size	Displays the amount of storage space that is being used by each table.
nz_db_tables_rowcount	To display the tablename + rowcount for every table in a given database.
nz_db_tables_rowcount_statistic	Display the tablename, rowcount, and storage size for all tables in a database.
nz_db_user_access_listing	To show what users have been granted access to what databases.
nz_db_views_rowcount	To display the rowcount + viewname for every view in a given database.
nz_ddl	To dump out all of the SQL/DDL that defines this NPS system.
nz_ddl_aggregate	To dump out the SQL/DDL that was used to create a user defined aggregate.
nz_ddl_comment	To dump out the SQL/DDL used to add a comment to an object.
nz_ddl_database	To dump out the SQL/DDL that was used to create a database.
nz_ddl_diff	Report any DDL “diff”erences between two databases.
nz_ddl_ext_table	To dump out the SQL/DDL that was used to create an external table.
nz_ddl_function	To dump out the SQL/DDL that was used to create a user defined function.
nz_ddl_grant_group	To dump out the SQL/DDL that represents any access GRANT’ed to a group.
nz_ddl_grant_user	To dump out the SQL/DDL that represents any access GRANT’ed to a user.
nz_ddl_group	To dump out the SQL/DDL that was used to create a group.
nz_ddl_history_config	To dump out the SQL/DDL that was used to create a history configuration.
nz_ddl_library	To dump out the SQL/DDL that was used to create a user defined shared library.
nz_ddl_mview	To dump out the SQL/DDL that was used to create a materialized view.
nz_ddl_object	To dump out the SQL/DDL that was used to create any object (of any type).
nz_ddl_owner	To dump out the SQL/DDL used to change the owner of an object.
nz_ddl_procedure	To dump out the SQL/DDL that was used to create a user defined procedure.
nz_ddl_schema	To dump out the SQL/DDL that was used to create a schema.
nz_ddl_security	To dump out the SQL/DDL for creating various security objects.
nz_ddl_sequence	To dump out the SQL/DDL that was used to create a sequence.
nz_ddl_synonym	To dump out the SQL/DDL that was used to create a synonym.
nz_ddl_sysdef	To dump out the SQL/DDL for setting the system’s default values.
nz_ddl_table	To dump out the SQL/DDL that was used to create a table.
nz_ddl_table_redesign	Provide alternative DDL for a table that optimizes each column’s datatype.
nz_ddl_user	To dump out the SQL/DDL that was used to create a user.
nz_ddl_view	To dump out the SQL/DDL that was used to create a view.
nz_dimension_or_fact	Identify whether a given table is a dimension table or a fact table.
nz_dump_ext_table	Dump out the header info found at the start of a compressed external table/file.
nz_find_acl_issues	Diagnostic Script:  Used to identify any oddities in the ACL tables.
nz_find_control_chars_in_data	Find any binary/non-printable control characters in a table’s text columns.
nz_find_non_integer_strings	Find any non-integer characters in a table’s text column.
nz_find_object	To help find+identify an ‘object’ — based on its name or its object id value.
nz_find_object_orphans	Diagnostic Script:  Used to identify certain discrepancies within the catalogs.
nz_find_object_owners	Find objects that are owned by users — other than the ‘admin’ user.
nz_find_table_orphans	Check that both the host and the SPU/S-Blades have entries for every table.
nz_fix_acl	To fix discrepancies in the system ACL tables.
nz_format	To format a block of SQL to make it more readable.
nz_frag	Dump out extent/page allocations in order to visualize table fragmentation.
nz_genc	Recompile code snippets (under /nz/kit/log/gencErrors) to identify any problems.
nz_genstats	Generate statistics on those tables that don’t have up-to-date statistics.
nz_get	Get (and display) the statistics for a user table or a system table.
nz_get_acl	To list the access privileges associated with a particular object.
nz_get_admin	List the administrative privileges that a user has been granted to a database.
nz_get_aggregate_name	Verifies that the user defined aggregate exists.
nz_get_aggregate_names	List the user defined aggregates found in this database.
nz_get_aggregate_signatures	List the signatures of the user defined aggregates in this database.
nz_get_column_attnum	Get a column’s logical attribute number (its order/position) within the object.
nz_get_column_name	Verifies that the specified column exists in the object.
nz_get_column_names	List the column names comprising an object.
nz_get_column_oid	List the colid (column id) for the specified column.
nz_get_column_type	Get a column’s defined data type.
nz_get_database_name	Verifies that the specified database exists on this server.
nz_get_database_names	Return the list of database names on this server.
nz_get_database_objid	List the object id for the specified database.
nz_get_database_owner	List the owner (creator of) the specified database.
nz_get_database_table_column_names	A sample script — that will list all database/schema/table/column names.
nz_get_ext_table_name	Verifies that the specified external table exists.
nz_get_ext_table_names	List the external table names found in this database.
nz_get_ext_table_objid	List the object id for the specified external table.
nz_get_ext_table_owner	List the owner (creator of) the specified external table.
nz_get_function_name	Verifies that the user defined function exists.
nz_get_function_names	List the user defined functions found in this database.
nz_get_function_signatures	List the signatures of the user defined functions in this database.
nz_get_group_name	Verifies that the specified group exists.
nz_get_group_names	Return the list of group names defined on this server.
nz_get_group_objid	List the object id for the specified group.
nz_get_group_owner	List the owner (creator of) the specified group.
nz_get_group_users	List the members of the specified group.
nz_get_lastTXid	To get the value of the last transaction ID that was assigned.
nz_get_library_name	Verifies that the user defined library exists.
nz_get_library_names	List the user defined libraries found in this database.
nz_get_mgmt_table_name	Verifies that the specified management table exists.
nz_get_mgmt_table_names	List the management table names found in this database.
nz_get_mgmt_view_name	Verifies that the specified management view exists.
nz_get_mgmt_view_names	List the management view names found in this database.
nz_get_model	To identify the model number of this NPS server.
nz_get_mview_basename	Returns the base tablename that this materialized view is based upon.
nz_get_mview_definition	Display the definition (the SQL) that defined the materialized view.
nz_get_mview_matrelid	Get the OBJID of the storage that is associated with a materialized view.
nz_get_mview_name	Verifies that the specified materialized view exists.
nz_get_mview_names	List the materialized view names found in this database.
nz_get_mview_objid	List the object id for the specified materialized view.
nz_get_mview_owner	List the owner (creator of) the specified materialized view.
nz_get_object_name	Verifies that the specified object exists.
nz_get_object_objid	Get the unique objid (object identifier number) associated with an object.
nz_get_object_owner	Get the owner of an object.
nz_get_object_type	For a given object, identify what type of object it is (TABLE, VIEW, etc …).
nz_get_procedure_name	Verifies that the user defined procedure exists.
nz_get_procedure_names	List the user defined procedures found in this database.
nz_get_procedure_signatures	List the signatures of the user defined procedures in this database.
nz_get_schema_name	Verifies that the specified schema exists.
nz_get_schema_names	List the schema names found in this database.
nz_get_schema_objid	List the object id for the specified schema.
nz_get_sequence_name	Verifies that the specified sequence exists.
nz_get_sequence_names	List the sequence names found in this database.
nz_get_sequence_objid	List the object id for the specified sequence.
nz_get_sequence_owner	List the owner (creator of) the specified sequence.
nz_get_stableTXid	To get the value of the “Stable Transaction ID”.
nz_get_synonym_definition	For the specified synonym, return the REFERENCED object.
nz_get_synonym_name	Verifies that the specified synonym exists.
nz_get_synonym_names	List the synonym names found in this database.
nz_get_synonym_objid	List the object id for the specified synonym.
nz_get_synonym_owner	List the owner (creator of) the specified synonym.
nz_get_sysmgmt_table_name	Verifies that the specified system table *OR* management table exists.
nz_get_sysmgmt_table_names	List the system table *AND* management table names found in this database.
nz_get_sysmgmt_table_objid	List the object id for the specified system table *OR* management table.
nz_get_sysmgmt_view_name	Verifies that the specified system view *OR* management view exists.
nz_get_sysmgmt_view_names	List the system view *AND* management view names found in this database.
nz_get_sysmgmt_view_objid	List the object id for the specified system view *OR* management view.
nz_get_sys_table_name	Verifies that the specified system table exists.
nz_get_sys_table_names	List the system table names found in this database.
nz_get_sys_view_name	Verifies that the specified system view exists.
nz_get_sys_view_names	List the system view names found in this database.
nz_get_table_distribution_key	Identify the column name(s) on which this table is distributed.
nz_get_table_fks	Identify the ‘foreign tables’ (if any) that are referenced by this table.
nz_get_table_name	Verifies that the specified table exists.
nz_get_table_names	List the table names found in this database.
nz_get_table_objid	List the object id for the specified table.
nz_get_table_organization_key	Identify the column name(s) on which this table is organized.
nz_get_table_owner	List the owner (creator of) the specified table.
nz_get_table_pk	If a PRIMARY KEY was defined for a table, list the column name(s) comprising it.
nz_get_table_rowcount	Perform a “SELECT COUNT(*) FROM <table>;” to get its true rowcount.
nz_get_table_rowcount_statistic	Returns the STATISTICAL VALUE representing this table’s rowcount.
nz_get_user_groups	List the groups that this user is a member of.
nz_get_user_name	Verifies that the specified user exists.
nz_get_user_names	Return the list of user names defined on this server.
nz_get_user_objid	List the object id for the specified user.
nz_get_user_owner	List the owner (creator of) the specified user.
nz_get_view_definition	Display the definition (the SQL) that the view will execute.
nz_get_view_name	Verifies that the specified view exists.
nz_get_view_names	List the view names found in this database.
nz_get_view_objid	List the object id for the specified view.
nz_get_view_owner	List the owner (creator of) the specified view.
nz_get_view_rowcount	Performs a “SELECT COUNT(*) FROM <view>;”  and returns the rowcount.
nz_gra_history	Copy the Scheduler’s GRA information out to a permanent table on disk.
nz_grep_views	Search all views, looking for any matches against the specified <search_string>.
nz_groom	A wrapper around the ‘groom’ command to provide additional functionality.
nz_health	Reports on the overall health of the system.
nz_help	Provide help (a listing) of the scripts that are included with this toolkit.
nz_host_memory	Display the host’s memory allocation table.
nz_inconsistent_data_types	List column names whose datatype is NOT consistently used from table to table.
nz_invisible	Provide information about the number of visible and INVISIBLE rows in a table.
nz_load4	To speed up the loading of a single file by using multiple nzload jobs.
nz_load_files	Utility script to assist with loading many data files into a table (or tables).
nz_lock	Check to see if an exclusive lock can be obtained on a table.
nz_maintenance_mode	Disable user access to the server, or to just the specific database(s).
nz_manual_vacuum	Vacuum and reindex the host catalogs.
nz_migrate	Migrate (i.e., copy) database table(s) from one NPS server to another.
nz_mm	Display information about each MM (Management Module) and/or its blades.
nz_my_access	To list out ALL of the objects that a given user has access to.
nz_my_grants	Dump out all of the GRANTs associated with a particular user (or group).
nz_pause	An alternative to “nzsystem pause”.
nz_physical_table_layout	To list out a table’s columns — sorted by their PHYSICAL field ID.
nz_ping	Diagnostic Script:  ‘ping’ all of the Mustang SPUs to verify their location.
nz_plan	Analyze a query’s *.pln file and highlight things of note.
nz_query_history	Copy the query history information out to a permanent table on disk.
nz_query_stats	Report various statistics about the queries that have been run on this box.
nz_reclaim	A wrapper around the ‘nzreclaim’ utility to provide additional functionality.
nz_record_skew	To check the record skew for a table.
nz_replay	Extract queries (from the query history) so they can be replayed/retested.
nz_replicate	A script to assist in replicating a database across two different NPS hosts.
nz_responders	Show interactions/responses for running queries (across each dataslice).
nz_restore	To backup (or restore) one or more tables.
nz_rev	Report the major.minor revision level of the software running on the host.
nz_select_fixed_data	Extract data from a table — formatting each column to a fixed width.
nz_select_quoted_data	Extract data from a table — wrapping each column value in “double quotes”.
nz_sense	Provide environmental “sense” data for Mustang series SPA’s.
nz_set	Dump out the optimizer ‘SET’tings that are currently in effect.
nz_show_locks	Show information about locks being held on the system.
nz_show_topology	Provide a report that describes the overall disk topology.
nz_skew	Identify any issues with data skew on the system.
nz_spu_memory	Provide a summary of how much memory the individual SPUs are using.
nz_spu_swap_space	Provide a summary of how much swap space the individual SPUs are using.
nz_spu_top	Show the current CPU Utilization and Disk Utilization on the S-Blades.
nz_state	Mimics the “nzstate” and “nzstate -terse” commands … but via SQL.
nz_stats	Show various statistics about the system (like ‘nzstats’ does, but more).
nz_storage_stats	Report various storage statistics about the system.
nz_sysmgmt_view_references	Identify the relationships between various system/mgmt tables and views.
nz_sysutil_history	Copy the system utilization information out to a permanent table on disk.
nz_table_analyze	Analyze a table column to determine the dispersion/uniqueness of its data.
nz_table_constraints	To dump out the SQL/DDL pertaining to any table constraints.
nz_table_references	Identify any tables with PRIMARY KEY / FOREIGN KEY relationships.
nz_tables	List ALL tables in a database, along with other useful pieces of information.
nz_temperatures	Report temperature and voltage information for each of the Mustang SPAs.
nz_test	Run a test to verify that these scripts can connect to the database.
nz_transactions	Display information about the current database transactions.
nz_unload	To unload data from a table swiftly … via the use of remote external tables.
nz_update_statistic_date_high_value	Update a table’s statistics — the MAX date value (of a DATE column).
nz_update_statistic_min_or_max	Update a table’s statistics — the MIN/MAX column value.
nz_update_statistic_null_values	Update a table’s statistics — # of NULL values in a column.
nz_update_statistic_table_rowcount	Update a table’s statistics — # of rows in the table.
nz_update_statistic_unique_values	Update a table’s statistics — # of unique values (dispersion) in a column.
nz_view_plan_file	View a *.pln plan file — on a remote/client worksation.
nz_view_references	Identify the relationships between various user tables and views.
nz_watch	Watch (i.e., monitor) the system … to verify that it is operational.
nz_wrapper	To “wrap” an object name with quotes (or the appropriate delimiter).
nz_zonemap	To dump out the zonemap information for a table or materialized view.