JDBC to IDOC Scenario.

[former_member238007]

Dear Experts,

My scenario is jdbc to file scenario,challenges facing here is:

I have 3 tables at the source side, where i need to fetch few fields from each table, there is a primary key among 3 tables but there is no additional field to update in any table, so how could i differentiate processed and unprocessed records.

If a view is created can it be handled? as view is transactional data how update happends so i beleive it cannot be handled (Anyworkaround will be really apreciated

Another doubt is:

If i need to fetch data from a table with flag field indicating processed and unprocessed,  as i am new to database scenarios,i want to know how PI fetches data from the table, is the records sequentially fetched from the database or will records be fetched randomly.

Daily in a table around 10gb records are dropped, while fetching data the search criteria takes time when the size of the DB is increased and it impacts performance, so what is the maximum turn around time that JDBC adapter holds to fetch records from database.

Request your valuable inputs..

Thanks,

--Kishore

[ambrish_mishra]

Hi Kishore,

Please mark the question as answered if your query was resolved.

thanks

Ambrish

[Former Member]

Dear Ambrish,

I didnt tried earlier but I tried now getting curios results.

its updating records more than the select statement. during testing bulk database its working fine but when i try with real time data, the xml output structure record count is not equal to my source record base.

RowID:

Foreg: 1200, 1201, 1203, 1204

I could see 1200, 1201 later in the next set i could see the sequence starting from 1217, 1218

there is no tract from 1203record to 1216 record in PI and no output for this exist but the status is chaned at source level.

Can you throw some light on this.

Will ROWID Alias work for this if i fetch the records one by one?

Please suggest few possible solution approach to resolve this issue.

Thanks,

--Kishore

[ambrish_mishra]

Hi Kishore,

Best way to do this would be to ask the DB team to update a staging table through a stored procedure from the 3 tables.

PI can fetch the data from this staging table through a stored procedure. Which database are you fetching the data from ? DB2, MS SQL or oracle ? and which PI version are you on?

DB2 and MS SQL support stored procedure while oracle has some limitations. Records can be updated in staging table and fetched sequentially through the stored procedure. that is not a issue but the issue is huge data load so the staging table update and PI polling the staging table has to be managed well.

Hope it helps!

Ambrish

[former_member238007]

HI Ambrish,

DB team is willing to create a newtable by fetching data from other tables.

so, now we have table in place where i can fetch the information,

Now, challenges comes on fetching data from a huge backup.

the records are placed in db with serialnumber in a column, so i need to send that information sequentially and serially,

So, can anyone of you confirm me whether data fetched from Oracle database is sequential.

Next need to concentrance on performance issues.

Thanks,

--Kishore

[ambrish_mishra]

Hi Kishore,

You can try 2 options:

• Connect to the database, make a select and see how it goes or ask database guys to run a select query which you are going to do from PI and share the results with you.
• Stored procedure can be written in such a way to fetch the data sequentially.

IMO, it should work.

Ambrish

[ambrish_mishra]

Hi Kishore,

Here is your solution. I was looking through other posts and found this one for you. This is bang on!

Suppose your field name is Seq_Num and you want to choose top 100 in sequence.

*************************** *************************** *************************** ***************************

SELECT      * (you can put specific field names if you need limited fields for better performance)

      FROM  tablename

      WHERE Seq_Num in (

            SELECT DISTINCT TOP 100 Seq_Num

                  FROM  tablename

                  WHERE Status = 0

                  ORDER BY Seq_Num)

      ORDER BY Seq_Num

UPDATE tablename SET Status = 1 where Seq_Num in (select Distinct top 100 Seq_Num from tablename where Status = 0 order by Seq_Num)

*************************** *************************** *************************** ***************************

Status field is like a flag or indicator field for PI to update the record when it is read.

If you need any clarity on the above, please revert back.

Hope it works and we can close this thread so that it can help others!

cheers,

Ambrish

NB:The solution will take care of the possible performance issues as well.

Message was edited by: Ambrish Mishra

[ambrish_mishra]

Curious! Did it work?

[Former Member]

Hi Ambrish

me getting error while i try above query.

Database-level error reported by JDBC driver while executing statement 'SELECT * FROM DEV_CREATE WHERE SALES_NUM IN ( SELECT DISTINCT TOP 100 SALES_NUM FROM DEV_CREATE WHERE FLAG IS NULL ORDER BY SALES_NUM ) ORDER BY SALES_NUM'. The JDBC driver returned the following error message: 'java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected
'. For details, contact your database server vendor.

QUERY

SELECT * FROM DEV_CREATE WHERE SALES_NUM IN ( SELECT DISTINCT TOP 100 SALES_NUM FROM DEV_CREATE WHERE FLAG IS NULL ORDER BY SALES_NUM ) ORDER BY SALES_NUM

Regards

Raju

[ambrish_mishra]

Hi Raju,

Respond on your thread pls.

I don't remember your query at the back of my hand.

thanks

Ambrish

[rajasekhar_reddy14]

create intermediate table and ask db team to write a procedure to pull data from 3 tables and update in intermediate table,then using JDBC sender channel pull data from intermediate table.

JDBC does not support STored procedure and writing complex statement in JDBC channel not right idea.

[former_member238007]

Hi Raja,

Are the records fetched sequentially from the intermediate table?

Thanks,

--Kishore

[rajasekhar_reddy14]

No 100% gaurantee, but you can sort the records at mapping level or using select statement or while copying data from multiple tables as DB team to sort. This is very much possible,