DSXchange

No, as a Timestamp with generated SQL there should be no problem if the DB2 timestamp is indeed in the format you say it is.

Post the TO_DATE() portion of your insert sql for this field.

Change the datatype to Timestamp in your job and ensure the stage generates the SQL. Do that, remove the milliseconds and then you'll be fine.

Hack off the milliseconds if your Oracle target is a DATE datatype. You can leave them on if you have an appropriate TIMESTAMP datatype, however.

Could you not also do the pivot in the source query that builds the lookup? Especially if you know it will 'always be three', return a single record there with the three fields rather than all the shenanigans to do it afterwards.

Ah... thanks for that Ken, not something I was aware of.

There's a poster by the name of Larry.Griffith who has written pivot BuildOps. One of the posts where he mentions that is in here. Might be worth contacting him and see if he can help, if he doesn't stop by and post something here.

Errr, ok. If this was a CLOB you would be golden. However, being a BLOB or Binary Large Object I'm not sure there's a way to 'convert to char' in any kind of meaningful way. Whatever you need to do will need to happen in the source select. If you are on 10g then look into the use of the DBMS_LOB.SUB...

I tried it using two different hash files like you suggested... The suggestion was to use two different hashed file stages both referring to the same hashed file name. Is that what you did? From there it's just a matter of getting the constraints right. What happened to DB2 in your latest 'job desi...

Actually, no you don't have to set it there though it is recommended. Remember, that is the default value used only when new jobs are created - it does not affect existing jobs. You'll need a custom routine or job to make mass changes to existing log auto-purge settings. Ken Bland has such a thing a...

You might want to test the routine code you post first. It's close, but it would help if 'x' existed and it actually reversed the string rather than duplicating it.

How about:


Bare bones.

What are you planning on doing with this BLOB field? There's no 'transformations' you can do on it and (as you are finding) they aren't really supported. What is your goal and does this really need to be accomplished with an ETL job?

Either ensure the $DSHOME/include directory is in the appropriate path environment variable or include the full path to the file in the include preprocessor directive.

'Certain jobs' = Sequence jobs? As Ray noted, search the forums for your error phrase 'The system cannot locate the resource specified' and you'll see it's a fairly common problem. And you'll find the solution as well.

PATH issue, as in the directory where dsjob lives is not in your production PATH environment variable.

A nullable date should take a null without issue. Doesn't sound like you are inserting what you think you are inserting. Why not try replacing the OCI stage with a Sequential File stage and see what ends up in the file?