×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Using Variables in a PARALLEL JOB ODBC Connector SQL statement

Using Variables in a PARALLEL JOB ODBC Connector SQL statement

Using Variables in a PARALLEL JOB ODBC Connector SQL statement

(OP)
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

RE: Using Variables in a PARALLEL JOB ODBC Connector SQL statement

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


RE: Using Variables in a PARALLEL JOB ODBC Connector SQL statement

(OP)
Thank you, but I am still confused.

Quote (johnherman)

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 :

CODE -->

select * from AS400_table_1 
                  where TRANSACTION_TYPE = 'Forecast' and 
                        TRANS_CREATED_DATE >=  20140501  

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

CODE -->

select * from AS400_table_1 
                 where TRANSACTION_TYPE = 'BUDGET' and 
                       TRANS_CREATED_DATE >=  20140515 


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:

CODE -->

 REC_TYPE = LOOKUP('TARGET', CONCAT( 'SELECT PARM_VAL FROM CONTL_TABLE
             where PARM_NAME= ''Transaction Type'' ')) 

and

CODE -->

FROM_DATE  = LOOKUP('TARGET', CONCAT( 'SELECT PARM_VAL FROM CONTL_TABLE
             where PARM_NAME= ''Process_Date'' ')) 
Then, our ODBC Connection SQL was able to use those variables as:

CODE -->

select *  from AS400_table_1 
                       where TRANSACTION_TYPE = '{$REC_TYPE}'  and
                             TRANS_CREATED_DATE >= '{$FROM_DATE}' 


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

CODE -->

select * from AS400_table_1 
             where TRANSACTION_TYPE =#test_parm.REC_TYPE#  and 
                   TRANS_CREATED_DATE >= #test_parm.FROM_DATE# 

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close