DSXchange

Sorry - No advice or explanation for the issues.

If you compare the resultant files at 8.1 and 8.7 are they identically sized? Those would be the DATA.30 and OVER.30 files.

The documentation is usually a good place to start.

See DataStage dsjob -run

Now that I really think of the problem you are having with the DELETE I realize it is the same issue I encountered with my generic job that wants to do updates. I ended up using a generic stage as shown in this thread.

If you activate $OSH_DUMP that should write out the actual runtime-parsed SQL for the DELETE to the log, could you search for that and perhaps post that?

Ahhh... right - that won't work since the "TempKeyCol" isn't the real key column name, you'll have to revert back to the custom SQL. What does your custom SQL look like now?

Since a change in any value in any of the 3 tables would trigger an update, you really cannot get around having to check all 3 tables. With this amount of data it would make sense to put a column in your tables which would indicate that it has changed since the last run - with an index on that colum...

Ray's comment made me think - if you have the key column defined, then you don't need custom SQL for this, just let DataStage do that for you.

The "TRANS" function comes from the old UniVerse database / Server engine and retrieves a column from another table, akin to a join.

This TRANS goes to file "DS_JOBS" and retrieves the value in column 6 using the key value from column "jobName".

To the last question - you do need to specify a datatype for the temporary column. I usually end up using a Varchar type with no specified length for this, as the conversions to/from string from most datatypes is implicit. Does the error message contain the real table name or "<tablename>"?

Does function correctly when run from your SQL-Server Management Studio?

Does the DELETE statement work in your SQL Tool? The comment about indexed views and computed columns makes me wonder.

No worries.

I am in an urgent project right now and with 14-16 hour days I don't have much spare time and energy to approach such problems. Considering that my solution, if any, would be posted as premium content you should at least consider getting a premium membership. Note that "urgent" or "AS...

Shouldn't you be doing as:

SELECT
DAY_KEY,
DATEPART(yyyy,getdate())-2 AS DateTypeFieldFROM FMPA_MSS.dbo.DAY_DIM
WHERE DATEPART(yyyy,getdate())-2 >= DATEPART(YYYY,FISCAL_YEAR_END_DATE);


And in your column definitions you have DAY_KEY and DateTypeField declared.

The ODBC passes SQL to the database, and your error message is indicative of an error in the stage.

did you cut-and-paste the SQL? If so, you used a "," instead of a "." in the first DATEPART function.