Adding New Job Parameter from PX routine
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 64
- Joined: Fri Jan 25, 2008 4:41 am
Adding New Job Parameter from PX routine
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.
-
- Participant
- Posts: 64
- Joined: Fri Jan 25, 2008 4:41 am
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
-
- Participant
- Posts: 64
- Joined: Fri Jan 25, 2008 4:41 am
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.
-
- Participant
- Posts: 64
- Joined: Fri Jan 25, 2008 4:41 am
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;
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;
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
This approach definitely requires re-design of the job itself, so don't know how practical it is to your case .
Saad