Configure History Database in IBM Netezza

Configure History Database in IBM Netezza

Step:-1 Create History User                                          

-----------------------------------------                                                           

nzsql -c "Create user HISTUSR with password 'HISTUSR';"  

               

Step: - 2 Grant privileges   to user HISTUSR                                                      

-----------------                                                      

nzsql -c "grant create database to HISTUSR;"                           

nzsql -c "grant list on user to HISTUSR ;"                              

Step: 3 create the history database                                            

---------------------------                                            

nzhistcreatedb -d HISTDB -t query -o HISTUSR -pw HISTUSR -u HISTUSR  -v 3    

                                              

Step: 3 create the history configuration                                      

---------------------------------                                      

nzsql -d SYSTEM                                                         

CREATE HISTORY CONFIGURATION HISTORY_ENABLE_V3                         

HISTTYPE QUERY                                                         

NPS LOCALHOST                                                          

DATABASE HISTDB                                                         

USER  HISTUSR                                                          

PASSWORD 'HISTUSR'                                                     

COLLECT QUERY , PLAN , TABLE , COLUMN , SERVICE , STATE                

LOADINTERVAL 5                                                         

LOADMINTHRESHOLD 4                                                     

LOADMAXTHRESHOLD 20                                                     

STORAGELIMIT 51200                                                     

LOADRETRY 1                                                            

ENABLEHIST TRUE                                                        

ENABLESYSTEM TRUE                                                      

VERSION 3 ;                                                            

                                           

Step: 4 to start the collection of history data                                 

---------------------------------------                                

nzsql -c "set history configuration HISTORY_ENABLE_V3;"                

Step 5. Restart NPS to place to changes.

---------------------------------------------

nzstop/nzstart at linux prompt

                           

Keep Checking status on using nzstate.

Troubleshooting

----------------------------------------------

     

IF the password of the "HISTUSR" changes, please note that we need to   Update the new password to the history configuration also.             

     

nzsql -c "CREATE HISTORY CONFIGURATION hist_disabled HISTTYPE NONE;"   

nzsql -c "SET HISTORY CONFIGURATION hist_disabled;"                    

     

nzstop/nzstart at linux prompt                                         

     

Alter the change password in query history                             

     

nzsql -c "ALTER HISTORY CONFIGURATION HISTORY_ENABLE_V3 USER HISTUSR PASSWORD 'HISTUSR'"                                                     

     

Set query history     

                                                

nzsql -c "set history configuration HISTORY_ENABLE_V3;"                

     

nzstop/nzstart at linux prompt                                          

     

====================================================================   

     

Below are command to check the snippet of outputs   for history database.                                    

     

nzsql -c "set history configuration HIST_ALL;"

nzsql -c "show history configuration all"