- If you see "connection refused" do the following:
- Log in using nz creds
- Type
nzstateto see if the connection is stopped - If it is stopped, enter
nzstart - Log back into student account, and try accessing the nzsql again
- Temporarily cache creds
nzpassword add –u username -pw password - Check cache -
nzpassword - Connect to system DB
nzsql -d SYSTEM\dt- list tables\?- view help\q- exit nzsql`logout- Log out of host machine
CREATE USER test WITH PASSWORD password123;CREATE GROUP test_group WITH USER test;GRANT [permission] ON [table] TO [group/user]all admin to [group]
##### Administration Tools
* IBM Netezza Administrator
* NZ Performance Portal (like sql monitor, but you can administor users/etc)
##### Full Schema Support
* Show your current schema `select current_schema`
* Create a db `create database some_db`
* Switch catalog/schema `\c some_db`
* Display current schema `select current_schema`
* Set schema - `set schema [schema]`
##### Distributing Data
* :trollface:
##### Loading the Data
* Use [`nzload`](http://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.load.doc/c_load_nzload_command.html) to load data into the DB
* `time nzload -db db_name -t table_name -delimiter 'delim' -df /path/to/fil_name.unl`
* `time` shows the time taken to perform task
* `-df` denotes the file to load
##### External Tables
* `create external table table_external sameas table using (DATAOBJECT('/path/to/table.unl') delimiter 'delim');`
* `insert into table_external select * from table;`
#### ODBC Connectivity
* :trollface:
#### Analyzing Query Plans
* CTAS - `CREATE TABLE AS` steps:
* `CREATE TABLE [table]_ctas AS SELECT * FROM [table] DISTRIBUTE ON (table_key)`
* `drop table [table]`
* `alter [table]_ctas rename to [table]`
* [Optimize Query Plans](http://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.adm.doc/c_sysadm_nz_optimize_query_plans.html)
* Use [`explain`](https://www.ibm.com/support/knowledgecenter/SSULQD_7.1.0/com.ibm.nz.adm.doc/r_sysadm_display_plan_types.html) before a query to see query will be executed
#### Zone Maps and Cluster-based Tables
* CBT - Cluster-based Table
* `CREATE TABLE table_cbt AS SELECT * FROM table ORGANIZE ON (key1, key2);`
#### Materialized Views
* I had to create mine at the command line, because the app would crash when I tried to use the GUI
#### General Statistics
* :trollface:
#### Transactions w/ Schema commands and Truncate Table
* Change DB `\c db_to_change_to;`
* Check current schema - `select current_schema;`
* Change schema - `set schema schema_to_change_to;`
* Check row count of table in another schema - `select count(*) from other_schema.table;`
* Truncate table - `truncate table schema.table;`
#### Reclaim Disk Space Using `Groom`
* `groom table [table_to_groom]`
#### Stored Procedures
* From Linux:
* Create a SP - `CREATE OR REPLACE PROCEDURE sp_name(arg_data_type1, arg_data_type2, ..)`
* Save SP - `nzsql -d database -f sp_name.sql`
* From `nzsql`:
* Run SP - `EXEC sp_name(args);`
## **Admin Course**
#### DBOS and System Logs
* PS - Displays information about active processes
* `ps man` to see description
* Check Log - `cat /user/kit/log/*/pg.log | more`
* Merge logs - `nzlogmerge -v path/to/log1 path/to/log2 > file_name.out`
#### NZSQL And Scripting
* :trollface:
#### NZEVENT
* [NZEVENT](http://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.adm.doc/r_sysadm_nzevent_cmd.html) - Course uses IBM Admin Tool
#### Analyzing Performance
*
#### Reviewing Query History
#### Backup and Restore
* [Backup](http://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.adm.doc/r_sysadm_nzbackup_syntax.html) a DB - `nzbackup -dir /backup_dir -u user -pw password -db db_to_backup -v`
* `-v` denotes verbose console logging
* output gives you a backupset - `YYYYMMDDHHMMSS`
* [Restore](http://www.ibm.com/support/knowledgecenter/SSULQD_7.2.1/com.ibm.nz.adm.doc/r_sysadm_nzrestore_syntax.html) a DB/Table - `nzrestore`