Using Variables in a PARALLEL JOB ODBC Connector SQL statement

[donafran]

Hi
I am BRAND NEW to Datastage and am tasked with converting our porcesses from Data Manager to Datastage.

>>> In Data Stage, our task is :
We are trying to use a table accessed through our ODBC connection to an AS400 system as our input Stream.

Our requirement is that the SQL statement use one or more variables in the “WHERE” clause to control the data selection
(i.e., FROM_DATE, or RECORD_TYPE) to allow us to run the same job without modification for multiple RECORD_TYPES (i.e., Forecast vs. Budget).

We have jobs that run just prior to our main job to set the variables in the SQL table to the correct values – based on which system is triggering the ETL job.

Question 1 :
The variables and values exist in a SQL Server table in the TARGET database.
** QUESTION: Is there a method that would allow us to use the values from this control table from the Target SQL Server Database in the ODBC Connector stage SQL code?
>>> WE are able to do this In DataManager, on the JOB stream properties, we define a variable that is resolved by retreiving a value from a table on our TARGET SQL database. I am then able to reference this variable in the FACT BUILD DataStream QUERY SQL statement that uses the ODBC connection as SOURCE.

I know they are very different tools, but my thinking is that there should be some way to accomplish most of the flexibility we had in Data Manager


Question 2:
As an alternative, we have created the parameter and value as a JOB PARAMETER using a Parameter Set/Value Set/Value File(“xx.txt” file). We can successfully access the value of the Job parameter in our ODBC Stage SQL code.
** QUESTION: Using this Job Parameter method, is there a way/process to update the VALUE of the job parameter dynamically/systematically? Using a stage/process/ etc within Data Stage ??
(without manually editing a .txt file? )
Everything I have tried, even though the value appears to be different, the Data Stage SQL keeps reconciling to the original value.

Thanks

 

[johnherman]

First. You can have more than one input table to any Data Stage job. Those tables can process independently or be joined to other tables in the job.

Question 1 - you can join the table with the parameters to the query for the main process.
Question 2 - you can read the table with the parameters and store into job parameters called "Stage Variables". Do a little research on them; this seems to be the better approach based on the info you provided.

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[donafran]

Thank you, but I am still confused.

Question 1 - you can join the table with the parameters to the query for the main process.

The question is How do I join the SQL table to the As/400 Table >>IN THE ODBC << Connection stage? Our Source As/400 table contains over 20,000,000 transactions, but on any given run of my DataStage job, I want to only select a few thousand...So I really need to control the selection IN the ODBC SQL statement -- STAGE VARIABLES would not work since they are only accessed in the TRANSFORMER STAGE -- I would already have selected too many records to pass into a Transformer Stage.

IN the SQL statement in ODBC connection stage need to look something like this :

 select * from AS400_table_1 
                  where TRANSACTION_TYPE = [COLOR=#4E9A06][b]'Forecast'[/b][/color] and 
                        TRANS_CREATED_DATE >= [COLOR=#4E9A06] [b]20140501[/b] [/color]

for one run.
Then later the same day, reusing the SAME DataStage job, we want to process Budget, so it may need to read:

 select * from AS400_table_1 
                 where TRANSACTION_TYPE = [COLOR=#4E9A06][b]'BUDGET'[/b][/color] and 
                       TRANS_CREATED_DATE >= [COLOR=#4E9A06] [b]20140515[/b][/color]

In Data Manager, even though SQL was against the SOURCE (AS/400), the JOB Node could define the variables as being resolved from a Control table on the TARGET SQL database .. then the Fact Build SQL would use that variable.
On the Job Node, we defined the variables as:

  [COLOR=#EF2929] [i]REC_TYPE[/i][/color] = LOOKUP([b]'TARGET',[/b] CONCAT( 'SELECT PARM_VAL FROM CONTL_TABLE
             where PARM_NAME= ''Transaction Type'' '))

and

 [COLOR=#EF2929][i]FROM_DATE[/i][/color]  = LOOKUP([b]'TARGET'[/b], CONCAT( 'SELECT PARM_VAL FROM CONTL_TABLE
             where PARM_NAME= ''Process_Date'' '))

Then, our ODBC Connection SQL was able to use those variables as:

 select *  from AS400_table_1 
                       where TRANSACTION_TYPE = [COLOR=#EF2929]'{$REC_TYPE}'[/color]  and
                             TRANS_CREATED_DATE >= [COLOR=#EF2929]'{$FROM_DATE}'[/color]

I am able to declare a parameter set/Value file (.txt) and use A Job Parameter as a variable:

 select * from AS400_table_1 
             where TRANSACTION_TYPE =[COLOR=#EF2929]#test_parm.REC_TYPE#[/color]  and 
                   TRANS_CREATED_DATE >= [COLOR=#EF2929]#test_parm.FROM_DATE#[/color]

BUT HOW CAN I CHANGE THE VALUES OF THE JOB PARAMETERS?
Manually updating the TXT file, and rerunning the job appeared to resolve the PARM variables to the Original values, not the new values in the TXT file.