Weird DataStage 7 Issue

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

Post Reply
shawn_ramsey
Participant
Posts: 145
Joined: Fri May 02, 2003 9:59 am
Location: Seattle, Washington. USA

Weird DataStage 7 Issue

Post by shawn_ramsey »

I have logged a Trouble Ticket with Ascential on this one but was wondering if there is anything else that I might try:

We are having strange issues with the SQL Server bulk load stage after 7 upgrade. We have several jobs that we used in a previous POC that worked correctly. These are pretty simple jobs that pull data from an EBCDIC formatted files, run them through a set of parallel conversions (using the splitter/combiner) then bulk loading them into SQL Server.

While testing the jobs I found out that we were getting duplicates in the primary key. :( I could not understand what was going on until I removed the primary key constraints from the table and saw that every row that was being inserted was all zeros. :? I wanted to make sure it was nothing in the transformation so I ran the code in the debugger then finally I redirected it to a flat file and the numbers were OK. :shock:

Once I did that I decided to modify the original job to load both into a transformer so that I could write to the file and the table at the same time, but once I did that everything was OK with the data going into SQL Server. This made me think that when I refreshed the columns that the metadata was updated and it worked because of that.

Finally, after a little more experimenting it looks like I need to have a transformer stage after a combiner and before going into the bulk load or it loads zeros.
Shawn Ramsey

"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
Teej
Participant
Posts: 677
Joined: Fri Aug 08, 2003 9:26 am
Location: USA

Re: Weird DataStage 7 Issue

Post by Teej »

shawn_ramsey wrote:I have logged a Trouble Ticket with Ascential
*hums* Whenever's there trouble... We're there on the double! We're the bloodhound gang! */hum*

In paraphrase, you did this:

Flat file (EBCDIC) -> Transform -> SQL Server (FAILED)
Flat file (EBCDIC) -> Transform -> Flat File & SQL Server (WORKED)
Flat file (EBCDIC) -> Transform -> Transform -> SQL Server (WORKED)

Hmm.

Sounds like one of the Transforms got screwed up. Try this: Rename your job and recompile it.

-T.J.
Developer of DataStage Parallel Engine (Orchestrate).
shawn_ramsey
Participant
Posts: 145
Joined: Fri May 02, 2003 9:59 am
Location: Seattle, Washington. USA

Re: Weird DataStage 7 Issue

Post by shawn_ramsey »

More like:

Flat file (EBCDIC) -> Split -> 4 Shared Containers -> Combiner -> SQL Server (FAILED)
Flat file (EBCDIC) -> Split -> 4 Shared Containers -> Combiner -> Transform -> Flat File & SQL Server (WORKED)
Flat file (EBCDIC) -> Split -> 4 Shared Containers -> Combiner -> Transform -> SQL Server (WORKED)

I tried the copy and it did not work either. I even tried deleting the link to the SQL Bulk Load and repopulated the column metadata without any luck. As a matter of fact any job that we have that goes directly out of the combiner into the SQL Bulk load is doing the same thing. At leas I have a work around and we only have a few jobs that this impacts
Shawn Ramsey

"It is a mistake to think you can solve any major problems just with potatoes."
-- Douglas Adams
Post Reply