03-03-2014 09:41 AM
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?
03-04-2014 12:37 PM
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.
03-04-2014 02:19 PM
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.
03-05-2014 01:55 AM
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 ?