Both – dbos.log and pg.log – these logs are very useful in understanding SQL statements that are hitting Netezza database system.
dbos.log: Records information about SQL plans (/nz/kit/log/dbos/dbos.log)
pg.log: Main database log file (/nz/kit/log/postgres/pg.log)
Below screenshots will help you understand both these log files with an example:
Step1: Connect to a database, as shown below:
Please note from above screenshot: 16258 is the session id and 7892 is the process ID, for connection session. 16259 is the session id for “nzsession” command.
Step2: Create some dummy table (Example: pg1) and insert data
Step3: Issue a select statement to query from “pg1” table, as below
Step4: Open /nz/kit/log/dbos/dbos.log and search for PID 7892 noted in step:1. It’s advisable to search from bottom of dbos.log file.
You can notice: Planid, Session Id, Process Id of above running SELECT statement from dbos.log file. However, you can’t see SQL statement here. If you want to see SQL statement, you need to go to /nz/kit/log/postgres/pg.log file
Step5: Open /nz/kit/log/postgres/pg.log file
Against same process id 7892, you can notice the SELECT statement recorded.
In summary, pg.log file tells what has been submitted. dbos.log file tells when the query got completed.
Please note: metadata queries won’t produce plans and will not write anything into dbos.log file. For example: running commands such as nzsession, \l, etc doesn’t produce any entries into dbos.log, where as you can see entries written into pg.log file.