Skip to content

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

Filesystem monitoring by NPS software

Starting from NPS version 7, we can now monitor Netezza mount points /nz and /nzscratch using the newly introduced system manager threshold parameters. These parameters can be defined in /nz/data/config/system.cfg

1) sysmgr.hostFileSystemUsageThresholdOneToRiseEvent (default value is 85%)
This parameter will send a notification when ever /nz or /nzscratch filesystem (mount point) usage goes above 85%.

2) sysmgr.hostFileSystemUsageThresholdTwoToRiseEvent (default value is 90%)
This parameter will send a warning message when ever /nz or /nzscratch filesystem (mount point) usage goes above 90%.

3) sysmgr.hostFileSystemUsageThresholdToStopSystem (default value is 95%)
This parameter will stop Netezza if /nz or /nzscratch usgae goes above 95% (for NPS version 7.0.0, 7.0.1 and 7.0.2). Starting from v7.0.3 system stops Netezza only if /nz file system is 95% full and not for /nzscratch.

All these parameters can take any value between 1 to 100, in percentage.

Script to list out all the SQL Server logins

Script to list out all the SQL Server logins along with its properties. The output of this script will be a create login script which can be run on the target machine where all these logins are to be migrated. You will see their are two selct queries in this script. First select query is to pull out all the SQL authenticated logins and second script will pull out all the Windows authenticated logins.

SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' WITH PASSWORD=' + sys.fn_varbintohexstr(password_hash) + ' HASHED, SID=' + sys.fn_varbintohexstr(sid) + ', ' + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master')) + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name, 'us_english')) + ', CHECK_EXPIRATION=' + CASE is_expiration_checked WHEN 1 THEN 'ON' ELSE 'OFF' END + ', CHECK_POLICY=' + CASE is_policy_checked WHEN 1 THEN 'ON' ELSE 'OFF' END as 'SQL Server Logins' FROM sys.sql_logins WHERE name not like 'sa' UNION ALL SELECT 'CREATE LOGIN ' + QUOTENAME(name) + ' FROM WINDOWS WITH ' + 'DEFAULT_DATABASE='+ QUOTENAME(COALESCE(default_database_name, 'master')) + ', DEFAULT_LANGUAGE=' + QUOTENAME(COALESCE(default_language_name, 'us_english')) as 'SQL Server Logins' FROM sys.server_principals WHERE type IN ('U','G') 

ERROR: Recursion detected in view expansion

ISSUE:
You may see this error when trying to query a view or create a view on another view.

ERROR: Recursion detected in view expansion

CAUSE:
Possible issue is that your view is broken and is pointing to itself. Just generate a ddl of this view and you will find something like below i.e. a view pointing to itself.

create or replace view MyView1
as select * from MyView1

RESOLUTION:
Netezza do not allow creating a view pointing to itself or any object with same name in a database. But there is a bug in Netezza which allows you to create a view pointing to itself if view already exist.

To resolve the issue you have to recreate a view with correct defination for the query to run successfully.
MORE EXPLANATION:
1) If you create a brand new view pointing to itself you will get error:
TESTDB.ADMIN(ADMIN)=> create or replace view MyView1 as select * from MyView1;
ERROR: relation does not exist TESTDB.ADMIN.MYVIEW1

2) Now, if you create this view as valid view it will run fine:
TESTDB.ADMIN(ADMIN)=> create or replace view MyView1 as select * from T1;
CREATE VIEW

3) Run select on view and it will run fine:
TESTDB.ADMIN(ADMIN)=> select * from MyView1;
<Runs Successfully>

4) Now, again try to recreate this view as we tried to create in step 1. It will work fine:
TESTDB.ADMIN(ADMIN)=> create or replace view MyView1 as select * from MyView1;
CREATE VIEW

5) Run select on view and it will throw error:
TESTDB.ADMIN(ADMIN)=> select * from MyView1;
ERROR: Recursion detected in view expansion