RCP - CDC Stage - Partitoning and Sorting

koti9 · Post by **koti9** » Thu Jul 02, 2015 12:13 pm

Hello all,

We are trying to do one single job with CDC Stage(comparing file and db) in it and also RCP Enabled. Did anyone try that, if yes, how did you handle the Hash Partitioning and sorting in it? Any help would be greatly appreciated.

Thanks & Regards
Koti

Mike · Post by **Mike** » Thu Jul 02, 2015 2:03 pm

That most likely depends on your plan for handling a variable number of key fields.

I opted for using a single concatenated key called KEY, so the partitioning and sorting was simply by KEY.

I found a generic RCP-enabled shared container to be more useful than a generic RCP-enabled job. Gives you more flexibility on prepping the new incoming data, especially if you need to do complex transformations.

Mike

priyadarshikunal · Post by **priyadarshikunal** » Sun Jul 05, 2015 4:28 am

I second the thought of having single concatanated field for key. You can even have the key field location or start end position parameterized.

priyadarshikunal · Post by **priyadarshikunal** » Sun Jul 05, 2015 4:35 am

viewtopic.php?t=153932

This post should give you little more detail on how to do it for files.

koti9 · Post by **koti9** » Tue Jul 07, 2015 1:37 pm

Hi Mike,

Thanks for your input, I too have dynamic number of keys based on table, and i want to follow your approach, exactly how did you generate the concatenated key (Through Parameter Set Values???), if yes , can you give me more details. Just to make things spicy, we want it be NLS Compatiable as we get different languages..So all three in one job(RCP,NLS and using CDC Stage)

Thanks & Regards
Koti

ShaneMuir · Post by **ShaneMuir** » Wed Jul 08, 2015 3:01 am

If you have a dynamic list of keys, then you need to be able to identify those key fields. Using your DBs system tables should allow you to do that.

Using the system tables to identify the key values you can then generate an SQL statement which concatenates those values into a single output column as well as naming any other fields that you require. You then pass that SQL as either a parameter, or write it to a file and pass the file name to your DB stage.

As long as your job is set up to receive those foreign language chars (ie NLS is enabled, you're using NVarChar or Varchar (Unicode), using the correct character set mapping etc) then NLS should not be an issue.

koti9 · Post by **koti9** » Thu Jul 09, 2015 12:52 pm

Hi Shane/Mike,

Once again Thanks for your responses,
I got a break through with CDC and RCP.But it is working only with one key field(that i pass through parameter set). Here is my design.

Code: Select all

SeqFile----Sort-----CDC----Transformer----Modify-----Oralce
                     |
                     |
                     |
                   Oracle

I was able to pass the sort keys, CDC Keys via (parameter set value files),
But if i am trying for multiple use multiple keys(with nulls in some of them), Its failing.As long as the parameter set value file has some column in varaible name, its working. Did you try this method of RCP??

Let me know your thoughts...I can provide the error message also...i already searched error the forum for the same, with zero hits...

Thanks & Regards
Koti

chulett · Post by **chulett** » Thu Jul 09, 2015 1:13 pm

<edited your post to add code tags to preserve whitespace>

Mike · Post by **Mike** » Thu Jul 09, 2015 2:06 pm

Nice job so far Koti.

I tend to like using parameter set values files to support my generic multi-instance RCP jobs since it provides a nice container for organizing everything that needs parameterizing.

Now you can appreciate the challenge of handling a variable number of keys. And you also have the additional requirement of handling potential NULL key values.

Let me ask a question to start the thought process for handling your keys:
What is the maximum number of keys that you need to be able to support?

Mike

koti9 · Post by **koti9** » Thu Jul 09, 2015 3:09 pm

Thanks Mike,

We are planning max of 5 keys as of now...

Thanks
Koti

ShaneMuir · Post by **ShaneMuir** » Fri Jul 10, 2015 2:28 am

Can you just quickly explain how you are setting your key value field for both the DB and the Seq file?

Are you passing a set of column names from a parameter set?

chulett · Post by **chulett** » Fri Jul 10, 2015 7:13 am

And Koti, when you do - please stop quoting everything you are replying to. Save that for when it makes sense, otherwise learn to love the Reply to topic link.

koti9 · Post by **koti9** » Fri Jul 10, 2015 10:58 am

Sorry Craig, Will follow that...

Shane,

Thats right i am sending the Key columns via parameter set value file.

I was able get to a temporary work around, i am passing separate schema file just with keys and using ColumnExport Stage to concatenate dynamically.....As i can't mention the key in my original schema file...
So my Sequential File have original schema file and Column Export stage have another schema file with keys.

chulett · Post by **chulett** » Fri Jul 10, 2015 11:09 am

koti9 wrote:As i can't mention the key in my original schema file

Sorry, but... why can you not?

koti9 · Post by **koti9** » Fri Jul 10, 2015 12:16 pm

Hi Craig,

There is no provision to mention the key in the schema file.
I think it is like that for a while....

Thanks & Regards
Koti