Dynamically export files based on Config table

A forum for discussing DataStage<sup>®</sup> basics. If you're not sure where your question goes, start here.

Moderators: chulett, rschirm, roy

Post Reply
WILLIS
Premium Member
Premium Member
Posts: 8
Joined: Thu Nov 16, 2017 6:37 am
Location: PA

Dynamically export files based on Config table

Post by WILLIS »

Preface:
I am new to Datastage. I have been doing ETL for 10ish years with SQL tools but I'm struggling a bit conforming to the ways of Datastage. I'm at the bottom of the learning curve. I've done the Datastage Essentials course and just submitted payment for premium status. I've authored roughly 20 jobs thus far - most are pretty simple ETL.

What I'm trying to do:
The Job requirement is simple in concept, Export one file for every customer in a list.

What I have done:
(PJob1)
I have the parallel job setup to accept the following parameters:
FileName
CustAccountCode
This jobs works as I expect it to.

(C_Tbl)
I've created a config table with 3 columns
RecId | FileName | CustAccountCode

I want to loop through C_Tbl and pass each record as two parameters into PJob1 which creates one file for every record. I can't figure the sequence job out to do so.

I've read quite a few posts with similar questions which are posted from more senior level persons who have a better concept of the intricacies of Datastage. I'm struggling to fill in the blanks, so to speak.

What's the best way to accomplish the above?
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Welcome to DataStage, where you often have 2 or 3 ways to accomplish anything!

In this case you might want to try using a Server job to read records from the table, then a Transformer stage to call the DSUtilityRunJob() function for each record. Search on DSUtilityRunJob for more info.
Choose a job you love, and you will never have to work a day in your life. - Confucius
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

True dat! :wink:

Another approach would be a looping Sequence job. Use a User Variables Activity stage to read the config file and pass each entry in it to the Start Loop stage. There's an old post of mine that might help with that, I'll see if I can dig it up later.

The server job approach would be easier, I do believe.
-craig

"You can never have too many knives" -- Logan Nine Fingers
WILLIS
Premium Member
Premium Member
Posts: 8
Joined: Thu Nov 16, 2017 6:37 am
Location: PA

Post by WILLIS »

qt_ky wrote:you might want to try using a Server job to read records from the table, then a Transformer stage to call the DSUtilityRunJob() function for each record.
I've read about this technique - but I'm struggling to fill in the blanks.

So, right now I have a Server job. It reads my SQL Server Config table through ODBC stage which is handed to the Transformer.

Questions and me thinking out loud:
  • The transformer is calling my other job through UtilityRunJob, It doesn't look like I can pass multiple parameters. I will have to pass one parameter but delimited. I can handle that in my SQL call. Ok. So how do I read the parameter within my job to spread my one parameter back into two? FileName and CustAccountCode?

    If the Transformer is calling my other job, what stage is needed after the Transformer stage to complete my Server Job?
Thanks for the pointers so far.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Where will your two parameters be leveraged in your Parallel job?

As for the Server job, I'd just end it with a Sequential File stage where you write out pretty much anything, say like the parameter values used. You could even write it to "/dev/null" (or whatever the Windows equivalent is) so it doesn't clutter anything up.
-craig

"You can never have too many knives" -- Logan Nine Fingers
WILLIS
Premium Member
Premium Member
Posts: 8
Joined: Thu Nov 16, 2017 6:37 am
Location: PA

Post by WILLIS »

The first parameter is FileName. It is used to place the file in the appropriate file hierarchy for the specified customer in the config table.

The second parameter, CustAccountCode, is used in the Netezza Stage within the SQL where clause.

As to the Server job - Check. Dummy Sequence is in place.
FranklinE
Premium Member
Premium Member
Posts: 739
Joined: Tue Nov 25, 2008 2:19 pm
Location: Malvern, PA

Post by FranklinE »

I have a loop that uses a list file with parameters, an index (increasing integer, controls the number of loops) and a file name. Each instance of the loop calls a parallel job to FTP get the named file.

First read of list gets maximum index (number of loops). During the loop, the instance of the loop reads the indexed file name, passes it to the FTP job as a parameter.

No server jobs or routines are involved. If your "table" was a list file, it would easily plug into my job design. You can read the list in other ways, ending the loop when you identify the last item in the list.
Franklin Evans
"Shared pain is lessened, shared joy increased. Thus do we refute entropy." -- Spider Robinson

Using mainframe data FAQ: viewtopic.php?t=143596 Using CFF FAQ: viewtopic.php?t=157872
WILLIS
Premium Member
Premium Member
Posts: 8
Joined: Thu Nov 16, 2017 6:37 am
Location: PA

Post by WILLIS »

deleted
Last edited by WILLIS on Thu Nov 16, 2017 12:27 pm, edited 1 time in total.
WILLIS
Premium Member
Premium Member
Posts: 8
Joined: Thu Nov 16, 2017 6:37 am
Location: PA

Post by WILLIS »

If it helps - Below is the SQL i have in the Server Job. One column is being passed to the Transformer, Parm.

Code: Select all

SELECT 'D:\External\Outbound\EXTRACT_002\' + CUSTOMER_NAME + '\' + CYCLE + '\DH_'+CUSTOMER_NAME+'_'+CYCLE +'_ORD_TRACKING_'+ CONVERT(VARCHAR(10), GETDATE()-1, 112) + '.XLST|'+CUST_CODES PRAM
FROM EXP_002_CONFIG
Result:
D:\External\Outbound\EXTRACT_002\AAFES_STORES\DAILY\DH_AAFES_STORES_DAILY_ORD_TRACKING_20171115.XLST|290623
D:\External\Outbound\EXTRACT_002\AAFES_CONUS\DAILY\DH_AAFES_CONUS_DAILY_ORD_TRACKING_20171115.XLST|290624


My deliminator is a bar. I don't know how or where to parse that into two variables. I don't think that it can be done after it is send to the job.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Well, I thought it used to be called DSUtilityRunJob() but I did some poking around and only found one called UtilityRunJob() which is actually what my old test job has in it. I wonder if it got renamed at some point over the years.

Anyhow, if you are in Designer and expand Routines in your Repository view, under sdk, Utility, UtilityRunJob is there. Double click it and look at the Arguments tab. In the description it gives the format for passing multiple parameters.

It might be of interest as well that you can go to the Code tab and just walk through each line of code in the routine to see how it works.
Choose a job you love, and you will never have to work a day in your life. - Confucius
WILLIS
Premium Member
Premium Member
Posts: 8
Joined: Thu Nov 16, 2017 6:37 am
Location: PA

Post by WILLIS »

Ah ha! Very helpful - Thanks.

I think the only thing I'm missing is the technique to split the parameters back apart in the Job I am running.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

So you need to do the split in the PX source SQL? If so, what database are you using?
-craig

"You can never have too many knives" -- Logan Nine Fingers
WILLIS
Premium Member
Premium Member
Posts: 8
Joined: Thu Nov 16, 2017 6:37 am
Location: PA

Post by WILLIS »

I don't think so.

Using the server method - I have:

Image

The Transformer calls my Job using:
UtilityRunJob('Export_Job', CONFIG.PRAM, 0, 0)

In the job that the Transformer calls, how do I configure my parameters to receive the parameter sent from the Transformer? I suspect there is some expression that will read the parameter and split based on array value- but I don't see where or how that should work.
WILLIS
Premium Member
Premium Member
Posts: 8
Joined: Thu Nov 16, 2017 6:37 am
Location: PA

Post by WILLIS »

Sweet, Got it.

I didn't read the requirements for the UtilityRunJob() close enough.

I was passing it the value of my variable. The Arguments clearly state that I needed to pass it as

ParamName1=Value1|ParamName2=Value2

My SQL has been modified to :

Code: Select all

SELECT 'FILENAME=D:\External\Outbound\EXTRACT_002\' + CUSTOMER_NAME + '\' + CYCLE + '\DH_'+CUSTOMER_NAME+'_'+CYCLE +'_ORD_TRACKING_'+ CONVERT(VARCHAR(10), GETDATE()-1, 112) + '.XLST|VAR_ACCOUNTS=' + CUST_CODES PARM
FROM EXP_002_CONFIG
Which gives the Results of:
FILENAME=D:\External\Outbound\EXTRACT_002\AAFES_STORES\DAILY\DH_AAFES_STORES_DAILY_ORD_TRACKING_20171115.XLST|VAR_ACCOUNTS=290623
FILENAME=D:\External\Outbound\EXTRACT_002\AAFES_CONUS\DAILY\DH_AAFES_CONUS_DAILY_ORD_TRACKING_20171115.XLST|VAR_ACCOUNTS=290624


So two jobs - A server job and a Parallel job. SWEET.
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

You got it! :)
Choose a job you love, and you will never have to work a day in your life. - Confucius
Post Reply