cancel
Showing results for 
Search instead for 
Did you mean: 

JDBC sender scenario and data integrity

Former Member
0 Kudos

Hi,

I need to extract some data from several tables of a sql server database.

So sender jdbc channel must pick up records from a header table, a detail table and more than one sub-detail tables.

I hope you experts can help me to find the best way to do it, considering:

  • data integrity between all tables

  • Avoid to repeat data (with a multiple select) , so there is a great volume of information.

Thanks,

Carme.

Accepted Solutions (1)

Accepted Solutions (1)

former_member184681
Active Contributor
0 Kudos

Hi,

As per my knowledge, it is not possible to select data from several related SQL tables without having data repetetions. Even using joins will cause data redundancy (even bigger, if you ask me). Higher level data will be repeated for each detailed record. Data being redundant to some extent is actually indispensable for you to correctly match header, detail, subdetail and detal records in PI.

The only alternative I can see (when using a pure database as a sender system) is consuming a web service whose server will be your PI system. As far as I know this is possible with some DBMS. Thus you can avoid data redundancy by structurizing your XML data correctly. But I am not sure if this is the best idea. First of all, instead of redundant data you will get the XML overhead. Moreover - it is PI's duty to change the data format from whatever the source is to whatever the output is required to be.

Hope this helps,

Greg

Answers (3)

Answers (3)

Former Member
0 Kudos

Thanks, Gregorz for your suggestion.

Indeed, I'm analiizing to do it with a stored procedure and extract data in a XML format (XMLSQL) .

Anyone experience with that? Is it possible with JDBC adapter?

Thanks, in advance,

Carme

former_member184681
Active Contributor
0 Kudos

Hi,

As far as I understood, you want to ensure that the data already sent is not sent again, is that correct? If so, then you have to ensure it on the database level. For instance, you might create a stored procedure (like Michal has suggested) that not only will collect data and return it to PI, but also mark the data as already sent. For instance you can set some indicator or even better: set a "distribution timestamp" (might appear useful for debugging reasons some day).

Hope this helps,

Greg

Former Member
0 Kudos

Hi Gregor,

I know that it's necessary mark the data already uploaded to ensure that it is not sent again.

Perhaps i have not explained correctly my doubt.

I must upload our centrer's budgets but the data is distributed in multiple sql tables:

Header table --> with key center and key budget and another header data

Detail table --> with key center, key budget , key number line and another detail data (1 header --> N detail)

Sub-detail table --> with key center, key budget, key number, key number line , key promotion number and another sub-detail data ( 1 detal record --> M sub-detail records)

My doubt...

Doing a select with joins of all tables, many fields of header and detail tables will be repeated .

How can i extract all budget data (entirely and with integrity ) without repeat key fields (center, budget, key number) and without repeat header fields?

Regards,

Carme.

aashish_sinha
Active Contributor
0 Kudos

Hi,

As per my understanding of your problem, Data fetched from database will be unique one for Header, and detail key combination from sub detail which will contain Primary key of header table, foreign key of Detail table, with combination of these keys, sub detail will return unique value for all records found in Header table.

And you have to use all join SQL query in one Stored Procedure to fetch it using JDBC adapter and mark it if it is necessary.

Regards

Aashish Sinha

MichalKrawczyk
Active Contributor
0 Kudos

Hi,

create a stored procedure in your DB and call it from PI - JDBC sender adapter

Regards,

Michal Krawczyk

Former Member
0 Kudos

Hi, Michal

And thanks four your quick reply

But, doing a multiple select directly in sender channel or doing it inside a stored procedure doesn't avoid duplicate header data...

Carme.