How to pass Query from file to ODBC stage

[BMSK]

Hi all,

I want to pass the query to odbc stage in paralell job, that quey is generated in datastage job its in stored in file from that file i want to pass the genrated query in OODBC stage.

thanks
BMSK.

[ray.wurlod]

Not possible.

Why do you think you want to do it that way?

There is a way, but you have to generate the query externally to the job (perhaps in a prior job) then pass the query into this job as a parameter value.

[chulett]

Clarify for us if this 'generated query in a file' is generated in the same job as the ODBC stage that will use it or in a previous job? Former bad, latter good.

[BMSK]

In my first job i will be generated query and store in file. In the second job and in ODBC stage i want to read the query from that file and th pull the data.


flow
first job seq->transform->seq

this is the query i generated
SELECT A.CONT_ID, B.CONT_ID AS SUSPECT_CONT_ID FROM DB2ADMIN.MDX_DATA_SRC A,DB2ADMIN.MDX_DATA_SRC B WHERE (A.DEPT= B.DEPT AND A.sat= B.sat ) AND A.CONT_ID<>B.CONT_ID

second job odbcstage-> seq

In ODBC stage how this query will be call it.

thanks
bmsk.

[datisaq]

This you can do at a job sequence level.

Main sequence:-

Job1----->ExecCommand---->job2

In ExecCommand Stage read that file and assign it to a Job parameter say "pQuery".

Then use that parameter "pQuery" in ODBC stage of your job2

[franco]

This can't be done from the same sequence. Because the value of the parameter will be initialized when the sequence start .If it is changed in the sequence, it will not reflect.Gurus, correct me if i am wrong

This you can do at a job sequence level.

Main sequence:-

Job1----->ExecCommand---->job2

In ExecCommand Stage read that file and assign it to a Job parameter say "pQuery".

Then use that parameter "pQuery" in ODBC stage of your job2

[ray.wurlod]

Welcome aboard.

You're wrong. It can be done in the one sequence, precisely as datisaq outlined.

[franco]

Welcome aboard.

You're wrong. It can be done in the one sequence, precisely as datisaq outlined. ...

because in one of our job , we have the following sequence

shell script to update the <param1> --> job that use <param1>

but the job was using the old value (i.e. the value before the sequence has started)

[BMSK]

Hi

i did the job in the flow J1--> Execute command activity --->J2

I facing the error

Call_sequence..JobControl (@EXE_CAT_SQL): Executed: head C:\Satish\sf_query.txt
Reply=0
Output from command ====>
SELECT A.CONT_ID, B.CONT_ID AS SUSPECT_CONT_ID FROM DB2ADMIN.MDX_DATA_SRC as A,DB2ADMIN.MDX_DATA_SRC as B WHERE (A.'LAST_NAME' is not recognized as an internal or external command,operable program or batch file.= B.'LAST_NAME' is not recognized as an inte

regards
bmsk

[chulett]

So, it tried to execute your output as DOS commands and got confused. Type using 'type' or 'cat' instead of 'head' and see if that helps.

[BMSK]

Hi chulett,

I tried with type, cat as well..but they are also giving the same error..

regards
bmsk

[BMSK]

Hi all,

I decleare the environment variable $envtest, when passing the value of $envtest=lastname its working fine.

the same environment varaible when I pass the parameter $envtest= lastname|firstname (with Pipe as delimiter) its not working and throwing error.

regards
bmsk