IBM Netezza
- Setting UNIX environment for Netezza to run
- Running nzload with a controlfile
- Updating table with aggregated values from a set of tables
- List all databases
- change to another database
- List all user tables in the database
- 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)
- export NETEZZA=/usr/local/nz
- export PATH=${NETEZZA}/bin:${PATH}
- export LIBPATH=${NETEZZA}/bin:${NETEZZA}/lib:${LIBPATH}
- export NZ_USER=your_user_name
- export NZ_PASSWORD=your_password_here
- export NZ_DATABASE=BCLQADB1
- xport NZ_HOST=10.192.46.163
- export NZ_PORT=5480
- export NZ_SCHEMA=CBA
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
- 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
- create schema qa: nzsql> create schema qa;
- grant permission to user boris: nzsql>grant all on qa to boris;
- 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