Netezza to Azure Synapse – Migrating Stored Procedures

azure-sql-databasemigrationnetezzastored-procedures

I have looked everywhere for useful documentation that comprehensively gives me the differences between Netezza stored procedures and functions so I can find Azure Synapse equivalents.

Is there any site or link that documents this sort of thing without having to look up each individual incompatibility by running the procs in Azure and having them fail?

I guess I would like to proactively fix these rather than reactively fixing them to save time

Any ideas?

Best Answer

Azure Synapse Pathway is a new tool currently in preview which can look at existing Netezza code for you, attempt to convert it and flag up errors.

Download the tool and run it against your Netezza installation. Start here: https://docs.microsoft.com/en-us/sql/tools/synapse-pathway/azure-synapse-pathway-overview?view=azure-sqldw-latest

As a wizard, it's probably at best going to give you 80/20, but will at least offer a start.

Related Solutions

Sql-server – Using stored procedures

Yes you will get a performance boost if you do this entirely on the server as the IO will be contained entirely inside the database server. However, at the same time you will introduce a performance hit on the server potentially.

It's a trade-off but I'd say it's worth it if you have spare capacity.

Netezza Error – Record Size Exceeds Internal Limit of 65535 Bytes

I suppose the data type of the result of the function SUBSTR(PARAM, ...) has the same length as the column PARAM itself. Since you have two of those in your select list, if the length of PARAM is large enough it's possible that the combined length of two values in the select list exceeds some sort of internal limit.

Try explicitly casting the values to shorter data types, e.g.

SELECT 
  cast(
    substr(PARAM, (instr (PARAM , 'Name":"' )+7), (instr(PARAM, 'StepName":"'))-21)
  as varchar (100)) as Name, 
  cast(
    substring(PARAM, (instr (PARAM , 'StepName":"'))+14, (instr(PARAM, 'StepData":"'))-(instr(PARAM, 'StepName":"'))-17) 
  as varchar (100)) as StepName 
FROM Table

Best Answer

Related Solutions

Sql-server – Using stored procedures

Netezza Error – Record Size Exceeds Internal Limit of 65535 Bytes

Related Question