Re: DataStage ETL question

TurkBear,

First of all, thanks for reply. Sorry for delay.

My original plan was to take table_name, column_name from the table user_ind_columns in Oracle 8i. For this step, use OCIstage. And, this stage again sent table_name and column_name to another second OCI. Once 2nd OCI have those info, it needs to form a SQL which should be

       "SELECT max(COLUMN_NAME) FROM table_name" and executes it. Finally, the output of this 2nd OCI will go to a hash file that basically keeps two columns, column_name and maxKeyValue.

The job flow might probably be like this one:

           table_name,                column_name
           column_name                maxKeyValue    
OCIstage -----------------> OCIstage --------------> HASHstage

The problem I had was the 2nd OCI does not seem to recognize the table_name and column_name coming in from 1st OCI.

Finally, I write a bacthJob that reads out those two info from a sequential file and sends them as parameters into another Job with OCIstage in which the SQL used looks like SELECT max(#column_name#) FROM #table_name#. Some extra steps, but it worked. Nevertheless,I am still looking for a way closer to the first method with TWO OCIstages I mentioned above.

I appreciate if you could enlighten me for this. Thank you, TurkBear.

TurkBear <john.greco_at_dot.state.mn.us> wrote in message news:<k983qugtloodt62n46o87llda3q0m26sco_at_4ax.com>...
> I won't bother to ask why, since DS Designer can include sql statements as before or after stage actions, you are using a
> sequential file to 'feed ' the OCI stage, or why you do not just write a shell script to do the Sql ..
> Please indicate how you pass the variables to the sequential file ...
> I am not sure that the method you describe is a supported technique, but, without more info, I can't be sure..
>
> BTW. have you contacted Ascential's excellent support folks?
>
>
>
> jshinwar_at_yahoo.com (shinwar) wrote:
>
> >Hoping anyone might be helpful to answer my DataStage(DS) ETL question.
> >Sorry for posting this in Oracle site. Because I do not know if there
> >is any site focus on DataStage discussion.
> >My question is:
> >I have a sequential file with simple SQL statements. Inside DS designer,
> >I put a Sequential stage to read this sequential file. And, this stage is
> >link to another OCI stage which is for Oracle.
> >
> > SEQstage --------------> OCIstage
> >
> >So, OCIstage supposed to execute any SQL coming on from SEQstage. Somehow,
> >I could not succeed in doing so. I do not know what is the problem. I tried
> >OCI with fixed SQL before. It was all fine. Now, I do not know how OCI should
> >handle variable SQL statements. Please advise me. Thank you.
> >
> >Shinway
>
>
>
> -----------== Posted via Newsfeed.Com - Uncensored Usenet News ==----------
> http://www.newsfeed.com The #1 Newsgroup Service in the World!
> -----= Over 100,000 Newsgroups - Unlimited Fast Downloads - 19 Servers =-----
Received on Tue Oct 08 2002 - 16:07:44 CDT