Adding New Job Parameter from PX routine

[shershahkhan]

Is it possible that i call a PX routine and inside that routine i add a new parameter to the job in which this routine is called and set its value, Also how to change the Job parameter value when the job is running? I am facing this problem in migrating PX shared container and i want to create unique table name.

[chulett]

No.

[chulett]

How about explaining in detail the problem you are trying to solve so that people can help you find a proper solution?

[shershahkhan]

I have a shared container and which is called from many jobs at the same time, i am trying to create a temp table and wants its name to be unique(Using Teradata Connector stage in PX and Teradata MLOAd in server job), as far as i know i can't parametrize table except using Job parameter. I am looking for a way to generate unique table name in shared container for each call, i can't use sequence or change the calling jobs, except the shared container. Let me know if more information is needed

[chulett]

OK... you want a unique table name, but unique how? Not sure what "per call" means, so - unique per job? Per run? In other words, just how unique does this need to be.

[shershahkhan]

Unique per shared container call, i think Creating table name from Timestamp or from job PID will work, but the only place where i can write a routine to generate unique names is transfer stage but even after creating a unique name in the transformer how will i use it in place of tablename? You have any easy way to do it?and one other constraint is table name should not be more then 30 char, as teradata doesn't allow that.

[shershahkhan]

If suppose the shared continer is called from a multi-instance job, more then once then i want the temp table name to be different in both the calls, sorry if still not clear then i will try to explain more, thanks for your help

[chulett]

I don't believe that you can do what you want without "using sequence or changing the calling jobs", something that you've ruled out. So it seems like it may be time to change the rules.

[toshea]

The only way you can dynamically set the table name in the Teradata Connector or Teradata MultiLoad stage is via a job parameter. And the only way to set a job parameter's value is when starting a job. You cannot change a job parameter's value midstream. If you need to dynamically set a table name midstream, you need to generate your SQL statement dynamically and call a stored procedure that executes it:

In the Teradata Connector, you would put your procedure call in the User-defined SQL property:

CALL SysExecSQL (ORCHESTRATE.stmt)

Or you may want to use the Stored Procedure stage instead, as it is better suited for calling stored procedures.

You would need to pre-create your stored procedure in BTEQ or Teradata SQL Assistant:

REPLACE PROCEDURE SysExecSQL (
stmt VARCHAR(32000) CHARACTER SET UNICODE
)
BEGIN
CALL dbc.SysExecSQL (:stmt);
END;

[Saad]

We implemented a similar scenario at one of our projects where we had to dynamically assign a new table for different set of months. The approach we adopted was to call that job through another sequence in which we used to loop around generating the next month and then calling the same job again. We used to pass the generated value through a parameter because of the limitation of Datastage. It worked considerably well for our problem.

This approach definitely requires re-design of the job itself, so don't know how practical it is to your case .

Saad