IBM Netezza

  1. Setting UNIX environment for Netezza to run
  2. Running nzload with a controlfile
  3. Updating table with aggregated values from a set of tables
  4. List all databases
  5. change to another database
  6. List all user tables in the database
  7. List nzload progress
switch to another database(cbahistory) on the server

set catalog cbahisory;
Get a list of all the databases on the server

nzsql> SELECT DATABASE FROM _V_DATABASE;
nzsql> \l
Setting UNIX environment for Netezza to run(AIX example)
Running nzload with a controlfile
Using editor of your choice create a datafile,delimited by 0XB6(¶):
vi /usr/local/landing_area/test.dat
1¶ IDES OF MARCH¶03152015¶CAESAR'S JUBILEE
2¶ BATTLE OF QADESH¶04151274¶
3¶ FILE LOADING DATE¶¶NOT SURE
Note the date does not have delimiter, 2 last records do not have values, we need them as nulls
Using editor of your choice create a file and save it as "controlfile".
vi /usr/local/landing_area/controlfile The file contents:
DATAFILE /usr/local/landing_area/test.dat
{
TableName TESTLOAD
Delimiter '0XB6'
DateDelim ''
NullValue ''
crinstring 'true'
ctrlchars 'true'
maxerrors 10
}
Create a table in the schema pointed to by NZ_SCHEMA parameter, matching the file specification and name it TESTLOAD
nzsql -c "create table cba.testload(ID int,EVENT varchar(34),EVENT_DT DATE,NOTES varchar(12))"
Load the data as follows:
nzload -cf /usr/local/landing_area/controlfile
Updating table with aggregated values from a set of tables

UPDATE CBA.EQTY_PARM_VAL_WK_FL_1 TRG
SET SIG_IVSM_AMT=A1.SUM_AMT
FROM
( SELECT b.LGL_ENT_NODE_CD
,COALESCE(Sum(a.AS_AT_BOOK_VAL_AMT),0) SUM_AMT
From BCL.EQTY_ESR_ME_F a
Inner join BCL.STAT_NODE_LGL_ENT_XREF b
On a.STAT_NODE = b.LVL15_STAT_NODE
And a.SNPST_DT = b.SNPST_DT
Where a.SNPST_DT = DATE('2015-06-30')
And a.END_DT = '9999-12-31'
And a.OSFI_ESR_TP_CD = 'SININS'
GROUP BY b.LGL_ENT_NODE_CD
) as A1
WHERE A1.LGL_ENT_NODE_CD = TRG.LGL_ENT_NODE_CD
Get a list of all the user tables in current database on the server

select tablename from _V_TABLE where tablename NOT like '\_%';
creating a schema in a database
  1. To create a schema in the database the parameter in /nz/data/postgresql.conf should be changed and server restarted.
    enable_schema_dbo_check = 1
  2. create schema qa: nzsql> create schema qa;
  3. grant permission to user boris: nzsql>grant all on qa to boris;
  4. alternatively the user can be granted create schema: nzsql> grant create schema to boris;
      List progress of nzload
      issue the following SQL statement:
      select
      databasename
      ,schemaname
      ,tablename
      ,to_char(rowsinserted,'999,999,999') as num_rows
      from _v_load_status

      top of the page