Full working example of connecting Netezza from Java and python

Before start connecting you must make sure you can access the Netezza database and table from the machine where you are trying to run Java and or Python samples.

Connecting Netezza server from Python Sample

Check out my Ipython Jupyter Notebook with Python Sample

Step 1: Importing python jaydebeapi library

import jaydebeapi

Step 2: Setting Database connection settings

dsn_database = "avkash"            
dsn_hostname = "172.16.181.131" 
dsn_port = "5480"                
dsn_uid = "admin"        
dsn_pwd = "password"      
jdbc_driver_name = "org.netezza.Driver"
jdbc_driver_loc = "/Users/avkashchauhan/learn/customers/netezza/nzjdbc3.jar"
###jdbc:netezza://" + server + "/" + dbName ;
connection_string='jdbc:netezza://'+dsn_hostname+':'+dsn_port+'/'+dsn_database
url = '{0}:user={1};password={2}'.format(connection_string, dsn_uid, dsn_pwd)
print("URL: " + url)
print("Connection String: " + connection_string)

Step 3:Creating Database Connection

conn = jaydebeapi.connect("org.netezza.Driver", connection_string, {'user': dsn_uid, 'password': dsn_pwd},
                         jars = "/Users/avkashchauhan/learn/customers/netezza/nzjdbc3.jar")
curs = conn.cursor()

Step 4:Processing SQL Query

curs.execute("select * from allusers")
result = curs.fetchall()
print("Total records: " + str(len(result)))
print(result[0])

Step 5: Printing all records

for i in range(len(result)):
    print(result[i])

Step 6: Closing all connections

curs.close()
conn.close()

Connecting Netezza server from Java Code Sample

Step 1: Have the Netezza driver as nzjdbc3.jar in a folder.

Step 2: Create netezzaJdbcMain.java as below in the same folder where nzjdbc3.jar is placed.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class netezzaJdbcMain {
    public static void main(String[] args) {
        String server = "x.x.x.x";
        String port = "5480";
        String dbName = "_db_name_";
        String url = "jdbc:netezza://" + server + "/" + dbName ;
        String user = "admin";
        String pwd = "password";
        String schema = "db_schema";
        Connection conn = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            Class.forName("org.netezza.Driver");
            System.out.println(" Connecting ... ");
            conn = DriverManager.getConnection(url, user, pwd);
            System.out.println(" Connected "+conn);
            
            String sql = "select * from allusers";
            st = conn.createStatement();
            rs = st.executeQuery(sql);

            System.out.println("Printing result...");
            int i = 0;
            while (rs.next()) {
                String userName = rs.getString("name");
                int year = rs.getInt("age");
                System.out.println("User: " + userName +
                        ", age is: " + year);
                i++;
            }
            if (i==0){
                System.out.println(" No data found");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if( rs != null) 
                    rs.close();
                if( st!= null)
                    st.close();
                if( conn != null)
                    conn.close();
            } catch (SQLException e1) {
                    e1.printStackTrace();
                }
        }
    }
}

Step 3: Compile code as below:

$ javac -cp nzjdbc3.jar -J-Xmx2g -J-XX:MaxPermSize=128m netezzaJdbcMin.java                                                                                                                                
Java HotSpot(TM) 64-Bit Server VM warning: ignoring option MaxPermSize=128m; support was removed in 8.0

Note: You should see your main class is compiled without any problem.

Step 4: Run compiled class as below:

$ java -cp .:nzjdbc3.jar netezzaJdbcMain

 Connecting ...
 Connected org.netezza.sql.NzConnection@3feba861
Printing result...
User: John                , age is: 30
User: Jason               , age is: 26
User: Jim                 , age is: 20
User: Kyle                , age is: 21
User: Kim                 , age is: 27

Note: You will see results something as above.

Thats it, enjoy!!

Advertisements

Restoring DVD rental database into postgresql

Get postgresql ready:

Now make sure you have a postgresql installed and running. If you need help please visit my blog: https://aichamp.wordpress.com/2017/03/20/setup-postgresql-database-on-osx/

Get the DVD Rental database:

Next, please download DVD Rental Sample Database from the link below:

http://www.postgresqltutorial.com/postgresql-sample-database/#

Note: The database file is in zipformat ( dvdrental.zip) so you need to extract it to  dvdrental.tar. You dont need to untar it, just keep the .tar file.

 

Environment:

$ pwd

/Users/avkashchauhan/tools/postgres

$ mkdir dvdrentaldb

$ initdb dvdrentaldb

Make sure the database dvdrentaldb is initialize and fully ready with username avkash.

Restore the Database:

$ pg_restore -U avkash -d dvdrentaldb dvdrental.tar

Now verify the database:

$ psql -U avkash dvdrentaldb

You will have access to postgresql shell and then you can run command as below:

psql (9.6.2)
Type "help" for help.

h2odb=# \dt
 List of relations
 Schema | Name | Type | Owner
--------+---------------+-------+--------
 public | actor | table | avkash
 public | address | table | avkash
 public | category | table | avkash
 public | city | table | avkash
 public | country | table | avkash
 public | customer | table | avkash
 public | film | table | avkash
 public | film_actor | table | avkash
 public | film_category | table | avkash
 public | inventory | table | avkash
 public | language | table | avkash
 public | payment | table | avkash
 public | rental | table | avkash
 public | staff | table | avkash
 public | store | table | avkash
(15 rows)

Thats all, enjoy!!

 

Setup postgresql database on OSX

Install postgres on OSX

$ brew install postgres

$ pg_ctl –version

pg_ctl (PostgreSQL) 9.6.2

Now create a folder name h2odb as below

$ pwd

/Users/avkashchauhan/tools/postgres

$ mkdir h2odb

Now initialize the database into h2odb as below:

$ initdb h2odb 

The files belonging to this database system will be owned by user "avkashchauhan".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

creating directory h2odb ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

pg_ctl -D h2odb -l logfile start

Now visit inside h2odb folder and edit pg_hba.conf by added the following at the end of the file:

$ vi pg_hba.conf

Add/append the following in pg_hba.conf where h2odb is database name and avkash is the database user name:

local h2odb avkash md5
host h2odb avkash 127.0.0.1/32 md5
local all all md5
host all all ::1/32 md5

Start postgres with h2odb as database (You must be outside h2odb folder or add full path to it):

$ pg_ctl -D h2odb -l logfile start

Now run the following command to setup user avkash with password avkash for the database h2odb as below:

$ psql postgres -c “create user avkash PASSWORD ‘avkash'”

$ psql postgres -c “create database h2odb with owner avkash”

$ psql postgres -c “alter role avkash superuser”

$ psql h2odb -U avkash -c “select version();”

Now you can access the h2odb as below where parameters are “-U  user_name data_base_name” :

$ psql -U avkash h2odb

Above command will give you access to postgres shell where you can run command as:

> \l  (list databases) 
> \dt (list tables for the selected database)
> \?  (Get Help)
> select * from table_name;

To check if postgres is up and running:

$ ps -ef | grep postgres

Stop postgres with h2odb as database

$ pg_ctl -D h2odb -l logfile stop

Thats all!! Enjoy it..