dynamically passing tablename

[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?

[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.

[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.

[loveojha2]

Why write a code when the same can be done through a simple Sequence Job The friendly GUI.

[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.

[baglasumit21]

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

[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.

[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).

[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.