Hi,
there are several columns in the table defined to allow nulls.

But in the program that access the table some Selects or Insert statements did not use the null indicators. So I changed the code in the program to use null indicator each time the value is selected or inserted into such column.

For example:
The table looked like this:

Define Table
Col_1 Char (10) not null,
Col_2 Char (5) not null,
Col_3 Timestamp not null,
Col_4 Timestamp,
Col_5 Char (1).

The code was:

Select Col_1, Col_2, Col_3, Col_4, Col_5
Into :var1, :var2, :var3, :var4, :var 5.

So I changed the INTO line to be:
Into (:var1, :var2, :var3, :var4 :null-ind-A, :var5 :null-ind-B).

Also I changed the code to populate null-ind-A and null-ind-B with either 0 or -1 if the value of the var4 and var5 was <= spaces.

The insert statement already had code that used null indicators and I didn't touch it.

Now, I am told that another group got -180 sqlcode on Insert. Which mean that data being inserted is not in Timestamp format.

I don't see how my changes could affect the execution. I haven't had a chance to look into the data but I am assuming the data is good. Which mean my changes somehow caused the problem. Which is strange since TS field must have some values in there and the program would set Null-ind-A to 0.

If Timestamp column allow nules should be treated differently than other columns when anticipating nulls?

Also, if the value in the host variable is in good format is it possible that the value of the null indicator causes the -180 code on insert?


Thanks.

It's difficult to comment without seeing the actual program implementations.
If the only thing that changed is the addition of indicator variables in the SELECT statement, and it worked before, that means that no NULLs were present in the database in Col_4 or Col_5 before that time. (At least, not in rows being accessed by that program.)
My first guess would be the following:
- the program logic inspects Col_4; seeing a blank, some sensible thing is done (e.g., using an INSERT without an explicit Column_4).
- now, because of your second change, NULLs are being put in the database instead of the default (which is "current timestamp").
- that program logic is now receiving a NULL but does not expect it, i.e., "forgets" to inspect the indicator variable.
- since the content of the :var4 host variable might be anything when :null-ind-A is negative, this could indeed be an invalid timestamp
- the program logic then goes to the wrong branch and tries to insert that invalid timestamp ==> SQLCODE -180.

Solution: need to change all "var4 is spaces" tests to "null-ind-A is negative".
Simpler solution, at least if my analysis is correct: changeinto and the program can continue checking for blanks instead of for NULLs...

But if the null indicator is set to -1, the system should not care what is inside the field, right?

I think all the rows contain valid timestamps. And I don't see how my changes would change anything in this case. How can checking for nulls on Select put blanks into var4? It would interpret the value in Col_4 as a valid timestamp and set null-ind to zero and put the value of the column into var4.
And later on the var4 would have the same timestamp (which is > spaces) and null indicator would be set to 0 and the value would be inserted without any problems.

Of course, but I have the impression that this could exactly be the problem:
viz. that the program does *not* verify the indicator, hence continues as if the field has been filled by DB2, but that's not not case. So the field still contains what was there just before, maybe blanks or some other initialisation?

So I would suggest to verify the program logic to be sure it's always checking the (new) indicator.

sounds like you are populating the column(s) correctly with the null, now and they were not previously. The SQL getting the -180 should be altered to account for the null or you go back to populating the fields incorrectly again.

We are glad to inform you, that our first game server soon will start to work. It will be server World of Warcraft.
The reasons why you are necessary for playing at WoW PROMMORPG.COM:
Own gameworld base;
Unique quests;
Unique events;
Frequent competitions and tournaments;
Modern equipment of our servers
More information: Welcome to PROMMORPG.COM. Your World of FREE Online Multiplayer Games.