DSXchange

Thanks everyone for the feedback!

Adding the key column (with value=1) to both files was the trick.

Nice. I'll mark this as resolved.

Is it possible to perform a cartesian product on two input files using DataStage? Input File#1 (3 rows, 1 column) Colx A B C Input File #2 (2 rows, 1 column) Coly 1 2 Output File (6 rows, 2 columns) Colx Coly A 1 A 2 B 1 B 2 C 1 C 2 I tried the Join Stage, but the Outer Join is not the same as a car...

Craig, thanks for the insight.

Your answer is cut off because it's 'premium content'. Building the column headings in the first row sounds promising; for now, I think the max number of columns is 100.

Mike

Yes, target is a flat file. Here's what I started: Transformer Loop Output ID COLNAME COLVALUE AA C1 05 AA C2 10 BB C1 20 BB C3 25 BB C4 50 CC C2 15 CC C6 12 Vertical Pivot Output ID COLVALUE_1 COLVALUE_2 COLVALUE_3, ... COLVALUE_N AA 05 10 BB 20 25 50 CC 15 12 Issues with my output: 1. Column names...

Here's the challenge... Input File (1 column) AA,C1=05,C2=10 BB,C1=20,C3=25,C4=50 CC,C2=15,C6=12 Output (7 columns in this example, could have 100+ columns) ID C1 C2 C3 C4 C5 C6 AA 05 10 NL NL NL NL BB 20 NL 25 50 NL NL CC NL 15 NL NL NL 12 Notes: 1. The 'NL' represents a null value for the column 2...

I appreciate the creative work-around solution, but it does not answer my question, "why would DataStage treat some negative SQL return codes as fatal and others as a warning?"

Any other suggestions?

I can't believe this is an issue in the first place...why would DataStage treat some negative return codes as fatal and others as a warning? This should have been my original question posted.

The ODBC stages are typically the first stage in our jobs; these stages are selecting data from DB2 for the remaining stages to manipulate, etc. Adding a Transformer before each of these stages to handle a negative SQL return code seems like a lot of work.

That solution is an option, but one that would require a Transformer Stage after every ODBC (DB2 select) Stage. This would be a lot of rework on our existing jobs, but I do appreciate your insight. I need to confirm this, but I believe a SQL -911 on our ODBC connection to our DB2 mainframe issues a ...

Hey Ray...I couldn't read your entire post because its labeled "premium content". I agree that all jobs within a project should have access to these hashed and/or sequential files. I believe this is still possible, we only need to prefix the file path with the jobname that created the file...

I just wanted thank everyone for their comments on this topic. It's now resolved...turns out that this was all related to a scheduling problem on our system which affected the data and underlying queries. The DataStage processes were getting started just a few minutes too early. Recompiling the jobs...

The hash file was only there for us to review what was actually inserted into the DB2 table during the batch cycle. It was a nice to have for troubleshooting, etc. but not really required and really didn't help. If it makes any difference the file had approx 500,000 records. One option that we didn'...

We removed the hash file from the job, and it ran without issues in production last night...originally we had a Transformer-->Hash File-->ODBC Insert setup, now we have just the Transformer-->ODBC Insert. We are still don't know why the original flow doesn't work; we had to do something as the on-ca...

Correct, the database error goes away after recompiling & rerunning the datastage job every time. And yes, the problem recurs every night. And we have confirmed that the database schema (ddl) is the same in dev and prod; both are defined as "not null with default". We have also confirm...

We're running version 8.1, build 0.0; what's strange is this problem only happens in our production environment. We're not able to recreate the problem on the DataStage development server. Both development and production servers are running the same version & build.