Simple guide to DIY NEM blockchain analysis

Step 1: download and unpack NIS

$ wget -qO- | tar xzv -C /opt/nem/

Step 2: obtain blockchain database snapshot

a) either from your locally running and fully synchronized NIS

  • configure nis.bootName and nis.bootKey in /opt/nem/package/nis/
  • run NIS
$ cd /opt/nem/package
$ ./

(I run this usually inside “screen” (apt-get install screen) session, so I can detach from it and logout from my VPS)

  • let it synchronize - this may take a long time; you may speed things up by downloading recent database snapshot from (i.e. and unzipping it into ~/nem/data directory … it should be imported once you start the NIS; the correct order is
    – start NIS
    – stop NIS
    – copy/unzip database from into ~/nem/data (overwrite the one already in there)
    – start NIS again
    – wait until it is fully synchronized

  • copy the database (so we don’t have to stop the NIS to open the database with another program)

$ cp ~/nem/nis/data/nis5_mainnet.h2.db ~/nem/nis/data/nis5_mainnet-copy.h2.db

b) or by downloading from (i.e.; of course, this one will not be up-to-date, but you may use it to play around - inspect the database schema and experiment with SQL queries

Step 3: inspect the database

$ java -Dh2.bindAddress= -cp /opt/nem/package/libs/h2-1.3.176.jar -baseDir ~/nem/nis/data 
  • open your browser and go to http://localhost:8082/
  • fill-in JDBC URL: jdbc:h2:nis5_mainnet-copy
  • both name and password are empty
  • connect and enjoy

Step 4: write more SQL queries

  • select max(height) from blocks
  • select count(*) from accounts
  • select sum(totalfee)/1000000.0 from blocks
  • select b.height as block, b.totalfee/1000000.0 as fee,a.printablekey as harvester from blocks b left outer join accounts a on order by b.totalfee desc
  • select sum(b.totalfee)/1000000.0 as fee,a.printablekey as harvester from blocks b left outer join accounts a on group by harvester order by fee desc
  • below all transactions from some exchange address:

asender.printablekey as sender,
arecipient.printablekey as recipient,
when 0 then t.amount/1000000.0
else -t.amount/1000000.0
end as amount

  accounts a
  left outer join transfers t on or
  left outer join accounts asender on
  left outer join accounts arecipient on

where a.printablekey like 'ND2JRPQIWXHKAA26INVGA7SREEUMX5QAI6VU7HNR'

Step 5: automate

  • create file
import java.sql.*;
public class BlockExplorer {
    public static void main(String[] a) throws Exception {
        Connection conn = DriverManager.getConnection("jdbc:h2:~/nem/nis/data/nis5_mainnet-copy", "", "");

        Statement stmt = conn.createStatement();
        ResultSet rs;

        rs = stmt.executeQuery("SELECT * FROM ACCOUNTS"); //use whatever SQL query you created in Step 4  
        while ( ) {
            String address = rs.getString("PRINTABLEKEY");

  • compile
$ javac -g 
  • run
$ java -cp .:/opt/nem/package/libs/h2-1.3.176.jar BlockExplorer  

Step 6: enjoy you new superpower

(and if you do, feel free to send me an equivalent of 1 beer in XEM)



Thank you for this. I have moved it to the guide section, hopefully it will get more attention there.

It seems that if you append “;FILE_LOCK=NO” to the connection string (example in Step 5 of the guide), you can work directly with the live NIS database (database opened and locked by someone else). Again, use at own risk!

Thank you @CryptoBeliever for finding this out!

Connection conn = DriverManager.getConnection(“jdbc:h2:~/nem/nis/data/nis5_mainnet;FILE_LOCK=NO”, “”, “”);

edit: my NIS stopped working with “ERROR Timeout trying to lock table “BLOCKS”; SQL statement:” after trying the above; I do not recommend to use it until (if) solution is found.

1 Like