Skip to content

Case sensitive object name – Issue & Resolution

ISSUE:
Getting error message like below, even when you can see the objects exists:
ERROR: Relation ‘TESTING’ does not exist
ERROR: Attribute ‘name’ not found

DESCRIPTION:
In Netezza we can create case-sensitive object names by enclosing the in double quotes (“”) while creating the objects. If object is not enclosed in double quotes then Netezza by default will consider it uppercase. Same is the scenario when we query these objects.

RESOLUTION:
Avoid creating case sensitive objects except if it is needed. To query these objects use double quotes around the object name . Make sure it is spelled in correct case for case sensitive objects.

EXAMPLE:
Here is a example of how case sensitive and non case sensitive object names work in Netezza.

TESTDB(ADMIN)=> create table “Testing” (name char(10), “Name” char(10));
CREATE TABLE

TESTDB(ADMIN)=> select * from Testing;
ERROR: Relation ‘TESTING’ does not exist

TESTDB(ADMIN)=> select * from “Testing”;
NAME | Name
——+——
(0 rows)

TESTDB(ADMIN)=> select name from “Testing”;
NAME
——
(0 rows)

TESTDB(ADMIN)=> select name,”Name” from “Testing”;
NAME | Name
——+——
(0 rows)

TESTDB(ADMIN)=> select “name”,”Name” from “Testing”;
ERROR: Attribute ‘name’ not found

TESTDB(ADMIN)=> select “NAME”,”Name” from “Testing”;
NAME | Name
——+——
(0 rows)

TESTDB(ADMIN)=> drop table Testing;
ERROR: Relation ‘TESTING’ does not exist

TESTDB(ADMIN)=> drop table “Testing”;
DROP TABLE

Netezza – InvalidateSharedInvalid: handled cache state reset

You may see messages like below in postgres logs (/nz/kit/log/postgres/pg.log):

DEBUG:InvalidateSharedInvalid: handled cache state reset

This is just a informational message and no action is required.
Reason why you see this message is because during system activity Netezza cache the catalog information. At a point in time when system finds that the catalog information cached is now obsolete it resets the cache information. When this reset happens it writes this informational message in postgres logs.

More frequent your system catalog changes more frequently you see these messages in pg.log

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.