Unable to populate a char field

Nic · Post by **Nic** » Wed Jul 26, 2006 7:46 am

My source data is a Longvarchar and I have to take a substring of that to populate a table in a DB2 database using an ODBC connection.
I can populate the table successfully by using the substrings of the Longvarchar but I have a problem with one single field which unless I set to null
(using SetNull()) my job aborts. If I try to populate this one field which is a nullable CHAR(1) I get the following message.

Fatal error:

APT_CombinedOperatorController,0: [DataDirect][ODBC DB2 Wire Protocol driver]Socket closed.
....,0: Failure during execution of operator logic.

The job runs fine is that field is set to null. I have tried many different things but nothing seems to work. What am I missing please?

ashwin141 · Post by **ashwin141** » Wed Jul 26, 2006 8:08 am

[quote="Nic"]My source data is a Longvarchar and I have to take a substring of that to populate a table in a DB2 database using an ODBC connection.
I can populate the table successfully by using the substrings of the Longvarchar but I have a problem with one single field which unless I set to null
(using SetNull()) my job aborts. If I try to populate this one field which is a nullable CHAR(1) I get the following message.
quote]

Hi Nic

What is the derivation for this CHAR(1) field? Try doing the same after making it non-nullable. Have you done any kind of null-handling there?

Regards
Ashwin

Nic · Post by **Nic** » Wed Jul 26, 2006 8:30 am

Ashvin

the only derivation is a Trim. I am not doing any null handling.
I tried changing the nullable setting, it didn't work

ashwin141 · Post by **ashwin141** » Wed Jul 26, 2006 9:39 am

I think I won't be able to say much without having a look at your job. Because if you are doing everything right then it seems a strange problem

Regards
Ashwin

ray.wurlod · Post by **ray.wurlod** » Wed Jul 26, 2006 4:21 pm

Is the Char(1) field really nullable in the target table?

Are you trying to specify an out of band null (in which case you should be using MakeNull() rather than SetNull())? Can you try NullToValue(field," ") to replace null with a space character, to see whether it's the null handling or something else that is giving the problem?

kumar_s · Post by **kumar_s** » Wed Jul 26, 2006 6:38 pm

Have you tried the direct load of data to that table with a value in that char field.
Check if any trigger set to that table.
What the defenition of the field in database?

Nic · Post by **Nic** » Thu Jul 27, 2006 2:01 am

I have tried updating the field directly in the database and that works fine. There is no problem if I use SetNull(), it works fine. The problem is not with not being able to null it, it is not being able to populate it. I thought maybe there is a problem with the data itself but hardcoding a character doesn't work either. It only seems to accept nulls and nothing else. The field in the database it set to Character(1) and there is nothing associated with this field.

ArndW · Post by **ArndW** » Thu Jul 27, 2006 2:37 am

Nic,
what is the column name you are using, and have you gone into the "edit row" attributes to ensure that you don't have some sort of a setting there that you aren't aware of?

Nic · Post by **Nic** » Thu Jul 27, 2006 2:53 am

I have gone into edit Row and there is nothing oyt of the ordinary there. The details are just like every other field in the table I can populate. The table definitions were imported from the database and I haven't changed anything. The column name is Tank_Online_Ind and I have already reimported the table definitons just in case. I am populating the table via a view and there don't seem to be any problems there either. There are similar jobs doing the same thing and they work fine.

ArndW · Post by **ArndW** » Thu Jul 27, 2006 3:11 am

Would it be possible to do a quick server job to just load a couple of rows with some value in this column? This might produce a better error message or might work but will also serve to narrow down the error.

Nic · Post by **Nic** » Thu Jul 27, 2006 3:30 am

Well the error message is not exactly descriptive. The latest development is that it worked with an upsert it just doesn't work with an append to table. I wonder if this is a known problem.
I will try and reproduce it with a server job just to see what would happen.

kumar_s · Post by **kumar_s** » Thu Jul 27, 2006 6:49 pm

Reset the job, "From Previous run" log might give you more information.

Nic · Post by **Nic** » Wed Nov 22, 2006 11:17 am

I could not use insert only (Write only) as there was 1 duplicate row in the data which shouldn't have been there and it was trying to update that row. Once I changed to Insert then update it worked fine.

DSXchange