Skip to content

Analyse user activity in Netezza server

This script will find all the sessions made by any specific user in current pg.log directory.
It pulls the session id as well as all the activities done in that session. Expected output will be pretty long if user have made too many connections and/or ran too many queries.

Script:

#save as analyze_user_activity.sh
USR=$1
PGLOG=/nz/kit/log/postgres/pg.log

for SESSION in `grep -i $USR $PGLOG | awk '/\[/{print $4}'`
do
echo $SESSION
S=`echo $SESSION | sed 's/\[//;s/\]//'`
grep \\[$S\\] $PGLOG
done

How to Run:
Run this script with one parameter, which will be the user id for which you want to monitor the activity.

analyze_user_activity.sh user123

How to find Netezza software support tools?

To find the current version of IBM Netezza Software support tools or support scripts (in general) run the below command:

head -n2 /nz/support/Version.txt

OUTPUT:

IBM_Netezza-7.0.2.3-130424-1831
NPS 7.0.2.3

This means we are on support tools version 7.0.2.3

NOTE:- This version is not same as NPS version. Both can be different.

Error – Record size limit exceeded

ISSUE:
You may get below error message when running a SQL query with REPLACE function.
ERROR: 229008 : Record size limit exceeded

DESCRIPTION:
You will face this error when you are using SQL extension toolkit version 1.6.1. It is a know defect 78087 reported with IBM and still there is no fix.

To check SQL extension toolkit, run below command in system database.
SYSTEM(ADMIN)=> SELECT regexp_version();
REGEXP_VERSION
————————————————————
IISI XML / Regular Expression Library Version 1.6.1 Build ()
(1 row)

RESOLUTION:
THere is no permanent fix available at this time. As a work around you cna downgrade to lower version of SQL extension toolkit like SQLEXT 1.4 and try to run your sql. It should run without issues.

Netezza – ERROR [08S02] Unexpected protocol character/message

Issue:
You may face below error message when you are trying to create external table in Netezza appliance.

ERROR [08S02] Unexpected protocol character/message

CAUSE:
This error may occure when you try to create a external table.

CREATE EXTERNAL TABLE ‘c:\testtest.csv’ USING (DELIMITER ‘,’ REMOTESOURCE ‘odbc’ FILLRECORD ‘true’ DATEDELIM
‘/’ DATESTYLE ‘YMD’ QUOTEDVALUE ‘yes’ TIMEDELIM ‘:’ TIMESTYLE ’12HOUR’)
as ……

You will also see something like below in postgres logs
26499] ERROR: found delim ‘,’ in a data field, specify escapeChar ‘\’ option in the external table definition

RESOLUTION:
Make sure to specify escapeChar ‘\’ option in the create external table syntax and rerun your query.

CREATE EXTERNAL TABLE ‘c:\testtest.csv’ USING (DELIMITER ‘,’ REMOTESOURCE ‘odbc’ FILLRECORD ‘true’ DATEDELIM
‘/’ DATESTYLE ‘YMD’ QUOTEDVALUE ‘yes’ TIMEDELIM ‘:’ TIMESTYLE ’12HOUR’ escapeChar ‘\’)
as ……

SQL Server database restarting every couple of minutes

ISSUE:

You may face an issue when your database is just restarting every couple of minutes and you can see check DB is running at same frequency under same spid, when you look into the SQL Server error log. But users are able to connect to database without any issues.

Below is the error message that you will find in SQL Server error log stating that database is starting:

Date                         6/6/2013 10:10:43 AM

Log                            SQL Server (Current – 6/6/2013 10:10:00 AM)

Source                     spid69

Message

Starting up database ‘WW_Testing’.

Below is the error message that you will find in SQL Server error log stating that checkdb is running on this database:

Date                         6/6/2013 10:10:43 AM

Log                            SQL Server (Current – 6/6/2013 10:10:00 AM)

Source                     spid69

Message

CHECKDB for database ‘WW_Testing’ finished without errors on 2013-06-01 11:00:24.160 (local time). This is an informational message only; no user action is required.

CAUSE:

Regarding database restart, this will happen because you have set auto close database setting to enabled. So, your database is closed as soon as the last connection to this database is closed, but DB again restarts as soon as a new request to connect to database comes to SQL Server instance.

You can run below query to find if it is enabled or not. If output is 1 then its enabled If 0 then its disabled.

select is_auto_close_on from sys.databases

where name='DBNAME'

Now regarding checkDB running at same frequency, This is not a issue. SQL Server always performs checkDB whenever any database is started. This is to make sure a consistent copy of database is available before coming online. You will notice that this message comes every time after the Starting database message.

RESOLUTION:

Only thing you need to do is to disable database auto close parameter by running below query in master database or by changing that by going to SSMS –> Databases –> DBNAME –> Right Click –> Properties –> Options –> Auto Close and set it to FALSE.

USE [master]

GO

ALTER DATABASE [Test] SET AUTO_CLOSE ON WITH NO_WAIT

GO