DB2OLEDB Query Parameters in OLE DB Sources

Hi

I'm using Microsoft DB2 OLE DB Driver to access a DB2 database, and I have a problem when I create an OLE DB Source using a parameterized query. Everytime I push the "Parameters" button, I get this error:

TITLE: Microsoft Visual Studio

Parameters cannot be extracted from the SQL command. The provider might not help to parse parameter information from the command. In that case, use the "SQL command from variable" access mode, in which the entire SQL command is stored in a variable.

ADDITIONAL INFORMATION:

El proveedor no puede derivar la información del parámetro, no se llamó a SetParameterInfo. (Microsoft DB2 OLE DB Provider)

I would really appreciatte any help.

Thanks.

Well, the message looks pretty clear to me. The provider you are using does not support an interface used to derive parameters from queries so parametrized queries cannot be used in that source adapter.

The workaround is to use "Sql command from variable" access mode and build your query in a single variable.

Thanks,

Bob

|||

Hi

First of all, thanks for your answer. I know I can use the "Sql command from variable" workaround, for that matter I could also use a DataReader Source instead of an OLE DB Source and use an expression to build the query. The thing is, I was wondering if there was a more recent version of the driver that would allow me to build parametrized queries the same way I build them when accessing a MS SQL Server database. I know I should have been more specific when I posted my question, for that I apologize.

Right now I'm evaluating Microsoft's and IBM's DB2 providers, and both have certain limitations. For instance when I use MS provider and the code page of the DB2 database is improperly configured, the development studio simply vanishes when I try to create a Data Flow. I know is not that big of a problem, I just need to be more careful when setting up the connections properties, but still it took me a while to realize what was going on, probably because I have little experience with MS SQL Server.

My point is, I was hoping to access DB2 the same way I access any other database, instead of having to change the way I do things depending on the provider used to set up the connection, but I supposse this is the way things are.

Thanks again for your answer.

Cheers.

|||

Hi

It looks like it is possible to use parametrized queries. I just had to change the value of "Derive parameters" to true in the "All" tab.

Thanks again.

sql

DB2 query in an expression (Slow ?)

I am using DB2 as my datasource.
I have a query that takes 10 sec.
I needed to add report parameters to this query, so I converted it
into an expression. However, now the report is taking longer than the
original query - just because I converted the query into an
expression...
Has anyone seen this? What would be the work around for this?
ThanksJust to be sure. When you added parameters did you add it to the query or
did you do a filter. If you are using a filter then it brings over all the
data before applying the filter. Also, it is very unlikely that you needed
to change it into an expression.
Bruce L-C
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0408181551.5abb1d88@.posting.google.com...
> I am using DB2 as my datasource.
> I have a query that takes 10 sec.
> I needed to add report parameters to this query, so I converted it
> into an expression. However, now the report is taking longer than the
> original query - just because I converted the query into an
> expression...
> Has anyone seen this? What would be the work around for this?
> Thanks|||I am using it in a filter... so I guess you are right. It brings the
whole data over.. and it's slow.
Now I am doing as below:
select col1, col2 from tab1 where col3=?
But I want to be able to change the filter from col3=? to col4=?
How do I do that?
Thanks in advance.|||I'm confused. Is the issue that you want to dynamically craete the sql
string. Sometimes having it be col3 and sometimes col4.
You can put an expression in the generic designer. Or, you can have a
selection of all for the parameters where when they select all the parameter
value is % (is the wildcard for db2 a *, if so then use a * instead of a %).
Then change it to use like.
select col1, col2 from tab1 where col3 like ? and col4 like ?
Bruce L-C
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0408201037.59d458ce@.posting.google.com...
> I am using it in a filter... so I guess you are right. It brings the
> whole data over.. and it's slow.
> Now I am doing as below:
> select col1, col2 from tab1 where col3=?
> But I want to be able to change the filter from col3=? to col4=?
> How do I do that?
> Thanks in advance.|||Thanks for the reply.
isn't "Like" only for string data types?
My col3 and col4 are numeric columns...
what would I do there?|||OK, then what you need to do is have a dynamic query. This means you make
the query an expression. Use the generic query designer and put in the
expression. I suggest first assigning the expression to a textbox to debug
what you are doing, make sure you get the SQL string you want. Here is an
example posted yesterday by Donovan Smith of MS:
="select Col1, Col2 from Table" & iif (Parameters!FilterByDate.Value ==true, " where Date > '" & Parameters!FilterDate.Value & "'", "")
Bruce L-C
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0408201905.447fb7d6@.posting.google.com...
> Thanks for the reply.
> isn't "Like" only for string data types?
> My col3 and col4 are numeric columns...
> what would I do there?|||Bruce,
Expressions are bad performance!! Which is why I started this posting.
Here is what I am doing now. Hopefully this will work good:
select col 1, col2 from table1
where
((1 = ? AND col4 = ?) OR (1=? AND col5 = ?) OR (1=? AND col6 = ?))
This way I can say "1" / "0" to any parameter that I want to pass...
It doubles the number of my internal parameters... but it seems to work...
What do you think?|||No, expression do not give bad performance, filters give bad performance
because filters bring over all the data prior to filtering. It is two
different things. However, I only use expressions as a last alternative and
what you have below is definitely a good way to solve the problem.
Bruce L-C
"Harsh" <creative@.mailcity.com> wrote in message
news:fa671a26.0408211025.46ab1122@.posting.google.com...
> Bruce,
> Expressions are bad performance!! Which is why I started this posting.
> Here is what I am doing now. Hopefully this will work good:
>
> select col 1, col2 from table1
> where
> ((1 = ? AND col4 = ?) OR (1=? AND col5 = ?) OR (1=? AND col6 = ?))
>
> This way I can say "1" / "0" to any parameter that I want to pass...
> It doubles the number of my internal parameters... but it seems to work...
> What do you think?

DB2 lookup with parameters

Hi,

We have a package using a lookup query on DB2 to validate data from a file. Everything works fine, except for the lookup query that has to cache about 1,5 million rows.

Now I would like to specify parameters to that query to minimize the data being cached. I tried using parameters in the query, but I get an error:

"Provider cannot derive parameter information and SetParameterInfo has not been called."

Anyone had that problem?

I am using Microsoft's OLE DB Provider for DB2.

Thanks

Can you elaborate a litle more about how you are planning to use parameter to reduce the number of rows?

To reduce the number of rows, just add a where clause in the query (do not select a table from the dropdown list); also limit the number of columns in the select part of the query. 1.5 million rows should not be a problem; unless there is a limited amount of RAM and/or the row size of the lookup query is too big.

|||

One of the lookup column is the transaction date/time (transaction_ts).

I want to cache only the rows where transaction_ts is between the min and max values of the transaction_ts in the text file.

There are only about 15000 rows in the text file and 1,5 million in the lookup. Currently the lookup operation represents approx. 97% of the total execution time so I'm trying to see if it could be optimized...

Thanks

|||

Fleo,

Using paramters in the Lookup is not what you want. By using paramters in the lookup, the component would be using partial cache; which translate on issuing a query against the lookup table for each row passing trhough; hence degradating performance even more. A work around could be to build a view (with a filter in transaction_ts) using Execute SQl task and then configure the lookup component to use the view instead.

BTW, can you provide more details on the current settings of the lookup (query, data type of each column in the query, cache mode, etc)? I still think 1.5 million rows is not that much...

|||

Hi Rafael,

Thanks for the suggestion, it's a good idea. I could also build a view which is using fields in a "configuration" table and update those fields.

I know 1.5 million rows is not that much. Width of the row is int + datetime + char(19) = 31 bytes.

Can't SSIS use full cache even with parameters? I mean the values of the parameters do not depend upon the value of the current row. SSIS would be retrieving the same data every time...

|||

Fleo,

No, you cannot use parameter and full cache at the same time. If you think about it, a parameter gives the avility of running a new query for each row in the data pipeline; so even with full cache, the next row will require a new query to be issued; which would not make too much sense. A solution though wuld be to make the query property of the LK transformation 'expressionable' so the querye gets set dinamically at run time (1 query for all rows in a single package execution though). I Think the SSIS team has already thought about it, but I don't know when they could actualy make it available.

|||

Rafael Salas wrote:

If you think about it, a parameter gives the avility of running a new query for each row in the data pipeline; so even with full cache, the next row will require a new query to be issued; which would not make too much sense.

I'm not sure I get it. Or maybe I didn't explain it well...

Right now, without parameters and full caching, the data in cache corresponds to something like "SELECT col1, col2, col3 FROM Table1 WHERE account like '123%'.

What I would like to have in cache is something similar to "SELECT col1,... FROM Table1 WHERE ... AND TRANSACTION_TS BETWEEN ? AND ?. The 2 parameters would correspond to the MIN and MAX values of TRANSACTION_TS for the rows in the data pipeline.

The parameters are not set with values extracted from the current row so this query could be runned once.

I guess I don't understand the way SSIS handles lookups.

Thanks

|||fleo,
You can't cache records if the lookup cache depends on SQL to be dynamically built via the data flow stream. Lookups using full-cache are built BEFORE the data flow executes.|||

Ok...

The full cache is built before the execution while partial cache is issuing a query for each row in the data pipe.

The SQL for the lookup query cannot be configured.

So I conclude that what I'm trying to do is technologically impossible with the current implementation of lookups (?).

Thanks for your help guys

|||No, you can use lookups, just with no caching. Partial caching works by caching results, and if no match is found, then the SQL is executed against the table again to look for a match.|||

fleo wrote:

Ok...

The full cache is built before the execution while partial cache is issuing a query for each row in the data pipe.

The SQL for the lookup query cannot be configured.

So I conclude that what I'm trying to do is technologically impossible with the current implementation of lookups (?).

Thanks for your help guys

Don't forget you still have the create/drop view option....it is a intrusive aproach but it should do the trick.