CODEWITHSUNDEEP
stored-proceduresnetezzabyteasystem-tablesnzsql
Cor Basson
Cor Basson

Reputation: 43

How to copy data from Netezza DEFINITION_SCHEMA [ignoring the bytea error]

I am trying to analyse the code used in the stored procs on our Netezza server.

First step is to get the definitions/code contained in the stored procs - this can easily be done by either of the following:

Using the system views

select
    PROCEDURE,
    PROCEDURESOURCE
from _v_procedure
where
    PROCEDURE = 'MY_PROC'
;

Or using the base table [view points to this table]

select
    PRONAME,
    PROSRC as PROCEDURESOURCE
from
    DEFINITION_SCHEMA."_T_PROC" P
where
    PRONAME= 'MY_PROC'

Now, once I run some analysis on the PROCEDURESOURCE column and try to write this information out to a table, I always get the following error:

ERROR: Type 'bytea' not supported by IBM Netezza SQL

Easy way to replicate this error is simply doing the following

create table MY_SCHEMA.TEST_TMP as
with rs as
(
    select
        PRONAME,
        PROSRC
    from
        DEFINITION_SCHEMA."_T_PROC" P
    where
        PRONAME = 'MY_PROC'
)
select * from rs

I have determined that there is a column in DEFINITION_SCHEMA."_T_PROC" of type bytea (column name = PROBIN)

I am however not selecting this column, so I am not sure why I am getting this error

Can anyone help with a workaround on how to copy the PROCEDURESOURCE into a new table and bypass the 'bytea' error

Thanks

Upvotes: 0

Views: 366

Answers (1)

Lars G Olsen
Lars G Olsen

Reputation: 1118

3 suggestions: 1) Sometimes the ‘limit all’ trick helps: What are the benefits of using LIMIT ALL in a subquery? 2) Alternatively, do a ‘create external table’ and put your data into a file, then another statement to read it back from the file 3) last guess is that you may be able to explicitly cast the column to a more benign data type (Varchar() or similar)

Upvotes: 0

Related Questions