Data Source Issues using DB2

Has anyone used SSIS with DB2 as a data source. I am able to make it work with the OLE/db driver for DB2, but it is verrrrry slow and fails if it is not an extreamly small table (probably < 1000 rows). It runs much faster and seems to handle large tables if I do it with a script component using an ODBC DataReader. We would like to not have to write code for every table and I can't get a pre-defined data source to work with ODBC.

The OLE driver also seems to have trouble running queries. What I mean by that is that it only works if I use the Table or View option on the OLE data source, it hiccups if I try to use the SQL Command option.

Sorry if I'm overloading the forum, but we have a number of questions.One thing you can try is to create an ADO.NET:ODBC connection in SSIS and use DataReaderSource adapter to pull out data, see whether that works.

We got reports of similar issues when customers use IBM OLEDB provider and the root causes seemed to fall into the provider's plate, not actually SSIS problems.
If instead you are using MS OLEDB Provider for DB2 and facing issues, please provide more detailed information about, for example the failure info, how "slow" when processing large amount of rows etc, and we will be able to better assist you.

Thanks
Wenyang|||Yes, I figured out the ado.net/datareader solution this morning and it works pretty well. We are using IBM's OLEDB provider. I will try and get the MS OLEDB for DB2 to see how that does.

Thanks for the help!
Harrysql

Data Retrieval Terribly Slow

Hi,

I'm using ASP.NET 1.1, SQL Server 2000 Server:

I followed the ASP.NET 1.1 Starter Kit's Commerce application and applied the same principles it had written the code to retrieve data to my web application I created. For example I've written this Function in a class to return a sqldatareader:

Public

Function GetAdvanceSearch(ByVal sAsString,ByVal ExtAsInteger,ByVal fdateAs DateTime,ByVal tdateAs DateTime)As SqlDataReaderDim oDrAdSearchAs SqlDataReader
Dim oCmdGetSearchAsNew SqlCommand("spAdvanceSearch", oComConn)With oCmdGetSearch
.CommandType = CommandType.StoredProcedure
.Parameters.Add(New SqlParameter("@.DialNo", SqlDbType.VarChar)).Value = s
.Parameters.Add(New SqlParameter("@.FDate", SqlDbType.DateTime)).Value = fdate
.Parameters.Add(New SqlParameter("@.TDate", SqlDbType.DateTime)).Value = tdate
.Parameters.Add(New SqlParameter("@.Ext", SqlDbType.Int)).Value = Ext
EndWith

oComConn.Open()
oDrAdSearch = oCmdGetSearch.ExecuteReader(CommandBehavior.CloseConnection)

If oDrAdSearch.HasRowsThen
Return oDrAdSearch
Else
ReturnNothing
EndIfEndFunction

And When I'm calling this function I do write in this way (assuming that this function is in a class called "Calls"):

Dim objCalls as New Calls

DataGrid1.DataSource = objCalls.GetAdvanceSearch(<PARAMS......>)
DataGrid1.Databind

My application is a Telephone Call Recording System and could expect vast amount of data. Averagely, a month may produce approximately 50,000 records or more. So while querying through my web application for a month, the application itself either gets stuck or the retrieval speed gets drastically slow. However I'm using Datareaders for every querying scenario. My Web application is hosted in a Windows 2000 Server and accessed via Local Network or IntraNet.

What are the ways I could make this retrieval more speedier and efficient? I would like to hear from anyone who have come across this problem and anyone who could help me on this.

Thanks in Advance. Looking forward for a reply from some one.

Have you narrowed down the bottleneck? Is it the actual stored procedure that is slowing things down, or your data access code?|||

Hi Morton,

Firstly, thanks for the reply. Infact, along with this stored procedure and several others written the same way as I had written earlier in the post is causing the slowness or getting stuck. I couldn't figure out why when querying about 50,000 or more records it gets slow or gets stuck, or this Error page appears:

Server Application Unavailable

The web application you are attempting to access on this web server is currently unavailable. Please hit the "Refresh" button in your web browser to retry your request.

Administrator Note:An error message detailing the cause of this specific request failure can be found in the application event log of the web server. Please review this log entry to discover what caused this error to occur.

Atleast I couldn't query 10,000 records, however lesser than that...it takes little time to upload. Pls. help me on this.

Thanks & Regards,

|||

Hi Hifni,

Did you check the sql statements with in the stored procedurespAdvanceSearch?

Because generally the select statements for searches may use different execution plans for different where conditions so they use different indexes. So it is better to be sure that the necessary indexes exist over the related columns. You can also update the statistics of the database if you have recently a bulk load on related tables.

One important point is if you use a complex sp, when it is compiled by the sql engine it will work with the compiled execution plan afterwards.

So if you should change the sp to use sp_executeSQL to create and use execution plans each time the sp is called. It may help to reduce period of the execution.

Eralper

http://www.kodyaz.com

Data Retrieval is Slow

I have a database with a table that holds 30 million rows. Clients have
access to only those records that concern them. The problem is they want to
have access to all their data at once with the largest resultset
returning1.5 million rows.
Having toyed with the indexes, performance monitors and the like, I still
can't get the data to the client fast enough.
Could you offer any advice on what I should do?
Thanks
'Wale
wale wrote:

> I have a database with a table that holds 30 million rows. Clients have
> access to only those records that concern them. The problem is they want to
> have access to all their data at once with the largest resultset
> returning1.5 million rows.
> Having toyed with the indexes, performance monitors and the like, I still
> can't get the data to the client fast enough.
> Could you offer any advice on what I should do?
> Thanks
> 'Wale
>
LOL. Manage their expectations? ;)
But, realistically, how can anyone do anything practical with 1.5
million rows at once? There must be some better way of presenting the
data without giving it all to them at once (obviously I'm making this
statement with absolutely no knowledge of your situation so I could be
full of it ;) )
Zach
sql

Data Retrieval is Slow

I have a database with a table that holds 30 million rows. Clients have
access to only those records that concern them. The problem is they want to
have access to all their data at once with the largest resultset
returning1.5 million rows.
Having toyed with the indexes, performance monitors and the like, I still
can't get the data to the client fast enough.
Could you offer any advice on what I should do?
Thanks
'Walewale wrote:

> I have a database with a table that holds 30 million rows. Clients have
> access to only those records that concern them. The problem is they want
to
> have access to all their data at once with the largest resultset
> returning1.5 million rows.
> Having toyed with the indexes, performance monitors and the like, I still
> can't get the data to the client fast enough.
> Could you offer any advice on what I should do?
> Thanks
> 'Wale
>
LOL. Manage their expectations? ;)
But, realistically, how can anyone do anything practical with 1.5
million rows at once? There must be some better way of presenting the
data without giving it all to them at once (obviously I'm making this
statement with absolutely no knowledge of your situation so I could be
full of it ;) )
Zach

Data read is so slow

I have a table with 3.5 million records and 50 columns. Users have been
complaining that they get ODBC errors when trying to run any query from MS
Access against this table. I created a view few fewer columns (about 25).
When I test it by doing select * from viewx in the query analyzer on my local
machine it takes 14 minutes (slams my machine) to get back the whole dataset.
What could be happening.
Comments Please.
Thank you
Bringing back 3.5 million rows will take time. Who could make use of such many rows.
Start by retrieving only the data you need, then tune the query by adding indexes etc to get better
execution times.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:D1F44BC4-6478-4155-B9FF-5F6AF1A030E8@.microsoft.com...
>I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you
|||Do you have the WHERE clause in you SELECT ?
Please, post the execution plan for your query.
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"helpplease" wrote:

> I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you

Data read is so slow

I have a table with 3.5 million records and 50 columns. Users have been
complaining that they get ODBC errors when trying to run any query from MS
Access against this table. I created a view few fewer columns (about 25).
When I test it by doing select * from viewx in the query analyzer on my loca
l
machine it takes 14 minutes (slams my machine) to get back the whole dataset
.
What could be happening.
Comments Please.
Thank youBringing back 3.5 million rows will take time. Who could make use of such ma
ny rows.
Start by retrieving only the data you need, then tune the query by adding in
dexes etc to get better
execution times.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:D1F44BC4-6478-4155-B9FF-5F6AF1A030E8@.microsoft.com...
>I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my lo
cal
> machine it takes 14 minutes (slams my machine) to get back the whole datas
et.
> What could be happening.
> Comments Please.
> Thank you|||Do you have the WHERE clause in you SELECT ?
Please, post the execution plan for your query.
** * Esta msg foi útil pra você ? Ent?o marque-a como tal. ***
Regards,
Rodrigo Fernandes
"helpplease" wrote:

> I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my lo
cal
> machine it takes 14 minutes (slams my machine) to get back the whole datas
et.
> What could be happening.
> Comments Please.
> Thank you

Data read is so slow

I have a table with 3.5 million records and 50 columns. Users have been
complaining that they get ODBC errors when trying to run any query from MS
Access against this table. I created a view few fewer columns (about 25).
When I test it by doing select * from viewx in the query analyzer on my local
machine it takes 14 minutes (slams my machine) to get back the whole dataset.
What could be happening.
Comments Please.
Thank youBringing back 3.5 million rows will take time. Who could make use of such many rows.
Start by retrieving only the data you need, then tune the query by adding indexes etc to get better
execution times.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"helpplease" <helpplease@.discussions.microsoft.com> wrote in message
news:D1F44BC4-6478-4155-B9FF-5F6AF1A030E8@.microsoft.com...
>I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you|||Do you have the WHERE clause in you SELECT ?
Please, post the execution plan for your query.
--
** * Esta msg foi útil pra você ? Então marque-a como tal. ***
Regards,
Rodrigo Fernandes
"helpplease" wrote:
> I have a table with 3.5 million records and 50 columns. Users have been
> complaining that they get ODBC errors when trying to run any query from MS
> Access against this table. I created a view few fewer columns (about 25).
> When I test it by doing select * from viewx in the query analyzer on my local
> machine it takes 14 minutes (slams my machine) to get back the whole dataset.
> What could be happening.
> Comments Please.
> Thank you

Data flow task running very slow

Hello,

I developed an SSIS package doing a nightly load into a data warehouse. We have an 8 hour loading window - currently the package takes 16 hours to complete.

I isolated the problem to a Data Flow task where +-35% of the time is spent. This task is pretty straight forward:

- OLE DB source, reading +- 800,000 rows from a SQL server database

- 13 Lookups in sequence, to get surrogate keys from dimension tables. Lookups are all on GUIDS.

- An aggregation

- OLEDB target, fact table in a SQL server database.

It seems unreasonable for the this task to take over 5 hours. It spends the majority of time on the lookups - not so much at target, source and aggregation.

Any comments and advice will be greatly appreciated.

Thanks.

(PS some machine details:

OS Name Microsoft(R) Windows(R) Server 2003, Standard Edition
Version 5.2.3790 Service Pack 1 Build 3790
Other OS Description Not Available
OS Manufacturer Microsoft Corporation
System Name ARK-SQL
System Manufacturer HP
System Model ProLiant DL380 G5
System Type X86-based PC
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
Processor x86 Family 6 Model 15 Stepping 6 GenuineIntel ~1866 Mhz
BIOS Version/Date HP P56, 9/18/2006
SMBIOS Version 2.3
Windows Directory C:\WINDOWS
System Directory C:\WINDOWS\system32
Boot Device \Device\HarddiskVolume1
Locale United States
Hardware Abstraction Layer Version = "5.2.3790.1830 (srv03_sp1_rtm.050324-1447)"
User Name Not Available
Time Zone South Africa Standard Time
Total Physical Memory 3,327.30 MB
Available Physical Memory 938.20 MB
Total Virtual Memory 1.10 GB
Available Virtual Memory 2.78 GB
Page File Space 2.00 GB
Page File C:\pagefile.sys)

How many rows are the lookups caching? Are you selecting the whole table (All columns) or are you using a SQL query to specify only the columns you need.

Are you sure it is not your dest that is slow receiving the rows?
If it is, SSIS will slow down the rows retrieved from the source making it appear as if it is something else slowing it down.

Check the lookups. Are as few columns as possible being selected.
Push your rows into nothing such as a Konesans Trash Destination. Does it appear faster?

Also, if your lookup is caching alot of rows and the key you are caching is a GUID, that's a chunk of work to do.|||

You need to find where the bottleneck is. You could start measuring how fast the dataflow 'reads from source'; then how fast it does the transformations (since you have a fair amount of transformations, I would measure a several points); and finally measure the how fast it writes into the destination.

few tips:

The lookups could slow down performance if you are using partial cache. So, limit the number of columns and rows (by providing a select statement with a where clause if possible) and use full cache mode. This approach could generate another problem if memory in server is limited.

See if you can move the aggregation upstream and or limit the number of columns to be used as 'group by'. In general aggregations will perform better if the number of columns with fewer columns in the 'group by'.

If you use an OLE DB destination, try to stick with 'fast load' and set the interval commit to an acceptable range

Use DB profiler tools to measure the performance of each SQL statement use in the data flow (OLE DB source, lookups, etc)

This white paper has some other information

http://www.microsoft.com/technet/prodtechnol/sql/2005/ssisperf.mspx|||

I would also highly recommend taking a read of this:

Donald Farmer's Technet webcast

(http://blogs.conchango.com/jamiethomson/archive/2006/06/14/SSIS_3A00_-Donald-Farmer_2700_s-Technet-webcast.aspx)

-Jamie

|||

Hi Crispin,

Thanks for your response.

I solved the problem last night. The key was in the caching.

I modify the SQL statement in most of my lookups to handle inferred fact entries. I.e. a sale against an unknown customer gets linked to the 'UNKNOWN CUTOMER' dimension table entry. It seems that SSIS sets the caching to partial when you modify the lookup SQL. I assume the lookup had to go back to disk quite often and therefore high cost for big dimension tables (...of which I have quite a few).

I am achieving a +-500% performance increase by keeping the Lookup transform as it is and by setting the cahcing to full. I treat the inferred fact entries at a later stage in stead of at the point of lookup.

|||Good to hear it has approved, however...

The lookup does not change to partial cache when you specify a statement. Not sure why you say it has done this. Was the checkbox checked?

On the partial cache thing, you are correct. The lookup will check it's cache, if the key is found, use it, else run the query against SQL. This can become dog slow. Only time it has been a good thing, for me at least, is when you know your facts are going to be a very small portion of a really large dimension. That way, the first few queries are slow but you don't have to cache 13 million rows only to use 1000.