Needed Help with SQl select

, ,
1

Hi everybody,

i am trying to query a database table which has very large volume of data (1 Million) and create a flat file out of the data retreived.I would like to know whether webMethods would work fine if i do a simple select of these many records using the JDBC adapeter. I am getting an out of memory exception. will it work if i change the heap size. can anybody suggest me the optimal heap size that i have to set to do such a query.
or is there any batch select option that i can use or is it possible at all .

thanks in advance

your help is greatly appericaiated

2

Mike,

The ideal way for this situation is using Adapter service (storedprocedure template)and wrap this in a service and create a schedule job.

There is no such batch select in WM as far i know.

HTH,
RMG

3

Mike,

You do NOT want to use an EAI/B2B tool like webMethods for a batch process to extract and transform large volumes of data. It will perform very poorly. Could be many, many hours.

This is a problem for an ETL tool like Informatica, Data Junction, Microsoft DTS, etc.

Even a stand-alone SQL/Stored procedure script would be better than doing this in an EAI tool.

Regards

4

Thanks guys,

Is there any way that i can do a select batch by batch- a way that i corporate in the flow service.it does not matter if it takes a long time . a way that i can use the cursor and get the write to a file batch by batch

5

Mike,

Using SP script select should not be problme but writing to a file (1mill records)could eat lot of IS memory…May be there is an easier way exist…
Mark can guide you on this./.

HTH,
RMG

6

Ram,

Just as you say, the problem is dragging this kind of volume through the IS.

If you don’t have an ETL tool, the next choice would be your database vendors bulk load utility. If that is not available, I’d go with a stand-alone stored procedure, or a 3gl program.

If you are using Oracle, don’t forget BULK COLLECT (as of 9i).

Regards.

7

Mike…

1.Create a Flat File schema… with record name as recordWithNoID and generate the documentList from that.
2.Call the Adapter service which gives u recordList as output.
3. call convertToString service and give the ffschema that is created as input for ffSchema.
4. Store the output string to a flat file.

This takes very less time.
Please revert back if u have any questions.

Thanks

8

Hi Rakesh,
if you have written a sample service for this, please upload the zip file here.I am just trying to understand how you have done.

Thanks

9

Hi Rakesh,

To make it work this way, you would still need to reduce the memory consumed by the SQL result set.

Just curious…

How much time has it taken you to move a million records this way? I’ve seen these things run for days at a time on a million records.

Regards

10

Rakesh,
As Mark is telling even then i will have to select the data from Database.(1 million record of the resultset would be in the memory).
also suppose i have this much volume already in the memory and i do a ConvertToString ( of the whole volume of record) wont it blow up.
will webMethods internally take care of the streaming from IDATA to the flatfile consider i am setting the option “outputFileName” in the input of the “convertToString” service
Mike Miller

11

Mike,

If you really do not care how long it takes (it could run for days), you can get this done in webMethods by breaking up the process into multiple selects that pull small result sets.

Create a where clause that can partition the data and substitute the appropriate values in a loop.

In one last attempt to help, if your source is Oracle and the dept table in the scott schema contained 1 million rows, you could do a SQL*Plus script like this and get finished much more quickly:

set echo off
set pagesize 0
set head off
set arraysize 5000
column deptno format 9999999
column dname format a14
column loc format a13
spool c:\foo.dat
set termout off
SELECT deptno,dname,loc FROM dept;
spool off

12

Mike,

You are right convertToString and writing to a file(1million records) will take lot of time may be days as mark says.so using sql script to a file is the simple option for data extraction.

13

Thanks Mark,

I Know it would be easier to do this using a sql export. But i am stuck with just webMethods i dont have any other options. probably i will try breaking the query and do multiple queries to the database. by the way i am using DB2. or i will try if there is any command that DB2 provides that i can use in as a dynamic sql and get the data

thanks again
Mike

14

Mike,

You can use webMethods java service to read oracle data using java jdbc -
get data using resultset object

  • loop on result and write the data into a file.
  • I tried with 250K records - it took me around 35 mins.

HTH
Krishna

15

Good point, doing the whole thing inside a Java service, bypassing the JDBC adapter and pipeline processing would be much faster.

16

Hi all,

My point was that if the Sql retrieves more records, then there is no need to loop over the results, instead the flat file schema method can be used to store the result set in a flat file.

Thanks
Rakesh

17

Krishna
1.How many records can the JAVA JDBC ResultSet hold at a time. I did the same thing but i have not tested it yet.
2. currently I am breaking my query into many small query and doing a jdbc call using the webMethods adapter. Can the JAVA JDBC Resultset hold more that a million record at a time so that i can loop through the resultSet.
3.By the way have anybody worked in DB2. and how to apply the DB2 fetch in the Java Code.Can anybody attach the code they have written to loop through ( I an reading about fetch commond in DB2 but i could not see a live example that explains about how to declare a cursor and make it point to the first record in the Database and use it in the Java Code)

Can anybody help on this ?

thanks
Mike Miller

18

hi all,
this is murali,i have a problem with java services to connect with databases ,i used jdbc driver for connecting oracle ,connection is established but i got nothing while executing ---------------------values s=con.getTableInfo(“current catalog”,“–”,“–”,“emp”); where con is JDBCConnection object,i did not get any value to values(s), i only got in IS console(s.o.pln(s)) >>><<< in values(s)…
thanks
murali

19

Murali,

Can you post the whole java service i.e the way you are getting the Connection Object?
try using DBConnection instead of JDBCConnection Object.

ramesh.

20

hi ramesh,
this is murali,i wrote a java program using webMethods java API…to connect oracle server…using this driver :oracle.jdbc.driver.Oracledriver
connection is established using this:con =new JDBCConnection
(“jdbc:oracle:thin:@ORASRV:1521:MIRACLE”,“it7”,“it7”,“oracle.jdbc.driver.OracleDriver”); after that i want to use menthods in JDBCConnection
1:getTables() 2:getTableInfo() those are not working they didn,t give proper results.Values d=con.getTableInfo(“webmethods72”,“it7”,“emp”,“eno”);it gives this error
values:. >>>$errorDump=java.sql.SQLException:[wm-cjdbc32-0014][SQLServer JDBC Driver]This driver is locked for use with embedd
ed applications., $errorInfo= >>>$errorDump=java.sql.SQLException:[wm-cjdbc32-0014][SQLServer JDBC Driver]This driver is lock
ed for use with embedded applications., $error=[wm-cjdbc32-0014][SQLServer JDBC Driver]This driver is locked for use with emb
edded applications., $localizedError=[wm-cjdbc32-0014][SQLServer JDBC Driver]This driver is locked for use with embedded appl
ications., $errorType=java.sql.SQLException, $service=hh:Untitled, $user=Administrator, $time=Sun Aug 08 16:06:20 EDT 2004<<<
, $error=[wm-cjdbc32-0014][SQLServer JDBC Driver]This driver is locked for use with embedded applications., $errorType=java.
sql.SQLException, location=getTables<<<
thanks in advance…
murali