Page 1 of 1
dynamically passing tablename
Posted: Thu Jan 04, 2007 1:38 am
by Ritesh Vikram
I have a file in which a tablename is there. My job has a DB2 stage which uses this tablename. Now I want to dynamically pass this tablename directly to my DB2 stage. Can anyone plz suggest how to do this?
Posted: Thu Jan 04, 2007 1:43 am
by kumar_s
Hi Ritesh,
You need two jobs. Job 1 read the table for the table name and pass it to the other job as JobParameter.
If you search the forum, you can find lot many similar recent post of the same kind. You can either use UserStatus or write into a file and read it to pass the value as JobParameter.
If you version permits, you can read the tablenames, and pass it to stored procedure satge, which has the functionality to create tables for each input records.
Posted: Thu Jan 04, 2007 3:00 am
by wnogalski
In the first job read the file with tablename and use SetJobUserStatus(link.PARAMETER) to set job's user status. In the next job in the sequence You can pass it as parameter f.e. job1.$UserStatus.
Posted: Thu Jan 04, 2007 4:45 am
by loveojha2
Why write a code when the same can be done through a simple Sequence Job
The friendly GUI.
Posted: Thu Jan 04, 2007 4:46 am
by ray.wurlod
You don't understand DSSetUserStatus() function. It must be executed from within a job. Also, there's nothing in your code to run Job1.
Posted: Thu Jan 04, 2007 5:19 am
by baglasumit21
wnogalski wrote:In the first job read the file with tablename and use SetJobUserStatus(link.PARAMETER) to set job's user status. In the next job in the sequence You can pass it as parameter f.e. job1.$UserStatus.
Hi
I am from same team as Ritesh. The jobs are now running fine with hard coded value for the table name for eg. "call DSSetUserStatus("table_name")" but it is not taking the table name when I state "call DSSetUserStatus(link.PARAMETER)". is there any other way by which I can get that value from the link
Posted: Thu Jan 04, 2007 5:46 am
by wnogalski
Create a sequence - put 2 jobs inside and connect them.
Job 1 - Sequential File --> Transformer --> Dummy HF.
Inside the Transformer in the Derivation from the output link use SetJobUserStatus(link.PARAMETER) where link.PARAMETER is the column from the input. This will set the job's status to the table name you extracted from the file.
Job 2 - create a parameter called for example TABLE_NAME. Inside the sequence a new parameter for job 2 will apprear and You will be able to assign job1.$UserStatus to it.
Posted: Thu Jan 04, 2007 4:36 pm
by kumar_s
Hi wnogalski,
SetJobUserStatus might be a built in routine by some of your other team in you site.
You just need to create a routine which has the following code
Code: Select all
Call DSSetUserStatus(Arg1)
Ans = Arg1
RETURN(Ans)
So that the value will be set for the derivation as well (If requried).
Posted: Thu Jan 04, 2007 6:46 pm
by ray.wurlod
Job1 selects the table name from wherever it is stored, and loads the table name into its (the job's) user status area via a routine that invokes DSSetUserStatus(), picking up the input table name from the input link.
The job sequence reads Job1's user status via $UserStatus activity variable and uses that to set a parameter in Job2.
Job2 uses a job parameter reference to set the table name.