BookmarkSubscribeRSS Feed
ENM
Calcite | Level 5 ENM
Calcite | Level 5

Hello,

We have a series of DI Studio jobs (SAS 9.4) which write to Netezza tables. All of these jobs use the REPLACE option in the table loader, and have been working very well. Now however, we require that some of these jobs use UPDATE/INSERT instead, something which appears to be problematic.

Here's the process so far:

1. The table metadata is established separately for Netezza.

2. The DI Studio jobs load the empty tables using UPDATE/INSERT quickly and efficiently, and the tables can be viewed without any problems.

3. However, once a job is triggered for the second time (no longer with an empty target table), the table loader transformation never finishes loading.

4. When I force the job to stop running, the target table can still be opened as though the job had never been rerun at all. This is peculiar because when I force table loaders to stop when writing to other tables, the tables typically become 'corrupted' and must be restored.

5. According to our Netezza administrator, the Netezza log says that the UPDATE/INSERT completed successfully. He suggested that DI Studio perhaps just doesn't get a confirmation that the table is successfully updated.

6. However, if I rerun the job with different input data, then let it run for an hour before forcing it to stop, the output table doesn't contain any of the new data at all. It's as though the target table is oblivious to the fact that I've tried writing to it.

Basically it seems as though the table loader keeps loading and loading without really being able to write to the Netezza table at all, even though it can do so without any problems when using the REPLACE table loader option.

I can fix this in a 'dirty way' by first loading the data to SAS tables using UPDATE/INSERT, and then loading the SAS tables to Netezza tables using REPLACE, but I'd like to resolve this in a neater way.

Do any of you have any advice on how we might resolve this?

3 REPLIES 3
Diane_SAS
SAS Employee

Please open a track with Technical Support. There could be issues with the type of data you are trying to load to an existing table, or something else. Tech Support should be able to walk through what you are doing to get more clarity on the situation.

Technical Support Form

LinusH
Tourmaline | Level 20

Open a track to SAS support sounds like a reasonable approach.

I get the feeling by your story that you are testing this interactively within DI Studio, or?

If so, what happens if you try this using the BatchServer instead?

Also, adding SASTRACE and SASTRACELOC options might give you some hints of what's going on.

Data never sleeps
Patrick
Opal | Level 21

Are you using Netezza 7.0.3 or higher? If so could the issue be related to this one: 50926 - SAS/ACCESS® Interface to Netezza LIBNAME does not support Netezza Server 7.0.3 ?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 3 replies
  • 1470 views
  • 0 likes
  • 4 in conversation