DataStage

Historicisation is ancient history, really. I started with data-warehousing in 2001 and back then it was ancient history (no pun meant, would be lame anyway). No rocket science at all. Still it is a major topic in my daily life to date. I am very astonished no database has take on this topic so far, well no that I know of. I did not research just my own experience with Oracle, DB2 LUW and PostgreSQL. As a hobby, I implement such a framework for PostgreSQL (pre-alpha so far). Back to datastage. DataStage does provide at least two stage sets to tackle this but far from being a DS hotshot or even seasoned DS developer, I could not make to get run those Change Capture/Apply and Slowly Changing Dimension stage setup properly.

While debugging the other problems , I noticed the peek to output spaces for a char column even though the very same value written to the database was NULL. Surprising twist...

I had this experience with NULL and Change Capture stage . So I needed explicit NULL handling but stumble over another problem, well it rather is a problem of mine but I feel prone to forget about it, so maybe this post reminds me of it. I tried to set up NULL handling for a char(3) column by first converting the content of the column in a transformer stage using NullToEmpty() doing the change capture and reconverting the empty string to NULL before writing to the database. I was surprised to find the string '   ' instead of a null value in the database. You probably spotted the problem right away, but it took me some hours to get to wisdom over it however. The point is, that char columns can be NULL but there is no emptiness. By definition a char is a string right padded to its length with space characters... So I reformed the replacement by some very unlikely i. e. three non-printable characters like bell,  device control three, negative acknowledgement. However, my ...

Well, I just adapted a job where the Change Capture stage is being used. My test data contain NULL as values to be compared against. I was very much surprised that the comparison with NULL results in finding no difference even though - to the best of my knowledge - SQL standard requires that such comparison always return non-equality. I know, DataStage is no SQL database but then again it is a tool heavily (I suspect mainly) being used in connection with SQL databases (at least as of writing this) and thus it would have been nice and straight forward if it followed the SQL standard where applicable. Please let me know if your experience is with respect to Change Capture stage and NULL is different. My observation is that counter-intuitive to me, that it make me unsure whether I misinterpreted the results.

Well, you checked and double-checked the existence of respective field and it is there! Yeah, stumbled over it again. And again! Possible solutions You need to compile jobs explicitly. You're done then. Try it. Go! What are you waiting for? If you are really extra sure compiled the job, then double-check the presence of the respective field again, but not in the stage DS tells you but in the very source at the start of the dataflow where this field comes from. It happens that if the column name of your SQL is not identical to the field name propagated. I do absolutely not know why: compilation does not reveal the problem DataStage misses the correct problem location by far

Well, what ever the actual reason for this mysterious error message, I could reproduce it when I tried to use a Modify stage therewith changing a not-nullable column to nullable.