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 ODBC Connection Problems

Hello,

I've recently run across a couple of problems trying to connect to DB2 using the .NET ODBC Provider.

1) I can create a DataSource using the DataSource designer, and when I edit the connection, the User Name and Password text boxes are empty. If I fill in the correct information and hit "Test Connection", I am able to connect. However, this information is not saved in the DataSource the next time I open it or try to run a Data Flow task that uses it! Is there some property I can set to fix this?

2) When I open up a solution, the DataSource appears to be trying to connect to the DB2 server, and perhaps there is no valid User Name and Password, the connection is refused and I eventually get locked out of my account. This could be related to the issue above.

Does anyone have any experience connecting to DB2 using either an ODBC driver or the perhaps the IBM DB2 UDB iSeries OLE DB providers?

Thanks in advance,

Mark

Mark,

The same problem occurs with Progress Database using .NET ODBC Provider and a didn′t find a solution. Problably is a .NET ODBC Provider problem but I don′t know how to fix. If you find a solution, please post here.

Question: Do you receive this error message ?

“Error at Data Flow Task [DataReader Soucer [135]]: Cannot acquire a managed connection from the run-time connection manager”

Thanks.

Rodrigo Chagas

|||

I found this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=93045&SiteID=1

Maybe will be useful for you. It seems ODBC connections are having a lot of problems in SQL 2005.

My hope is getting smaller...

|||This is not a problem with .Net, but rather the way in which the ODBC driver builds the connection string or handles security. Some drivers including the CA ODBC driver do not append the password property to the connection string. So when you attempt to connect it will prompt for a password. Depending on how you invoke the program, you may not even see the prompt.
One way to resolve the issue is to append the password to your connection string in the form of "Password=MyPass;"
I've used the OLEDB provider for DB2 before, but there are some limitations to it. Per IBM, OLEDB does not provide the same level of functionality as their ODBC drivers. One thing that OLEDB does not provide is scrollable cursors.
I haven't used DB2 in almost a year, so IBM may have some updated drivers. Also MS just released their version of an OLEDB provider for DB2, so you could try it. You can get it at the following url.
http://www.microsoft.com/downloads/details.aspx?familyid=D09C1D60-A13C-4479-9B91-9E8B9D835CDC&displaylang=en
Larry

DB2 Connection Custom Assembly

Have a small problem. I have written a custom assembly to query a db2
database through a dsn datasource. It works fine in Report Designer but
shows the #error message in the fields on the report once it is published.
Following is my rssrvpolicy.config entry:
<CodeGroup
class="UnionCodeGroup"
version="1.0.0.0"
PermissionSetName="FullTrust"
Name="DB2Link"
Description="This assembly connects to the DB2 database server and
retrieves information">
<IMembershipCondition
class="UrlMembershipCondition"
version="1.0.0.0"
Url="C:\Program Files\Microsoft SQL
Server\MSSQL\Reporting Services\ReportServer\bin\DB2Link.dll"
/>
</CodeGroup>
I have also added the following code to my Class Library prior to compiling
and deploying:
Dim Permission As New
Odbc.OdbcPermission(Security.Permissions.PermissionState.Unrestricted)
Permission.Assert()
but to no avail. Any help would be greatly appreciated. BTW, the ODBC
connection information is all housed within the library. It takes a string
value passed from SRS and returns a single string value. Nothing fancy...
Do I need to create an access group to the DSN File?
Thanks,Asserting the ODBC Permission might not be sufficient. You may want to try
asserting full trust in the custom assembly:
[PermissionSet(SecurityAction.Assert, Unrestricted=true)]
public foo()
{
// Your code:
// open database connection
// ...
}
See also this thread related to connecting to Oracle from a custom assembly:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs&mid=3cd1a1de-15d4-41fe-bc9b-3c9df93ac2ee&sloc=en-us
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"JamesH" <JamesH@.discussions.microsoft.com> wrote in message
news:84982C3D-32DD-4D3B-B46C-2766810046F0@.microsoft.com...
> Have a small problem. I have written a custom assembly to query a db2
> database through a dsn datasource. It works fine in Report Designer but
> shows the #error message in the fields on the report once it is published.
> Following is my rssrvpolicy.config entry:
> <CodeGroup
> class="UnionCodeGroup"
> version="1.0.0.0"
> PermissionSetName="FullTrust"
> Name="DB2Link"
> Description="This assembly connects to the DB2 database server and
> retrieves information">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1.0.0.0"
> Url="C:\Program Files\Microsoft SQL
> Server\MSSQL\Reporting Services\ReportServer\bin\DB2Link.dll"
> />
> </CodeGroup>
> I have also added the following code to my Class Library prior to
compiling
> and deploying:
> Dim Permission As New
> Odbc.OdbcPermission(Security.Permissions.PermissionState.Unrestricted)
> Permission.Assert()
> but to no avail. Any help would be greatly appreciated. BTW, the ODBC
> connection information is all housed within the library. It takes a
string
> value passed from SRS and returns a single string value. Nothing fancy...
> Do I need to create an access group to the DSN File?
> Thanks,|||Thanks, I will look at it, I left the code on another computer and won't be
able to try it until tomorrow, will let you know.
"JamesH" wrote:
> Have a small problem. I have written a custom assembly to query a db2
> database through a dsn datasource. It works fine in Report Designer but
> shows the #error message in the fields on the report once it is published.
> Following is my rssrvpolicy.config entry:
> <CodeGroup
> class="UnionCodeGroup"
> version="1.0.0.0"
> PermissionSetName="FullTrust"
> Name="DB2Link"
> Description="This assembly connects to the DB2 database server and
> retrieves information">
> <IMembershipCondition
> class="UrlMembershipCondition"
> version="1.0.0.0"
> Url="C:\Program Files\Microsoft SQL
> Server\MSSQL\Reporting Services\ReportServer\bin\DB2Link.dll"
> />
> </CodeGroup>
> I have also added the following code to my Class Library prior to compiling
> and deploying:
> Dim Permission As New
> Odbc.OdbcPermission(Security.Permissions.PermissionState.Unrestricted)
> Permission.Assert()
> but to no avail. Any help would be greatly appreciated. BTW, the ODBC
> connection information is all housed within the library. It takes a string
> value passed from SRS and returns a single string value. Nothing fancy...
> Do I need to create an access group to the DSN File?
> Thanks,|||Robert, I've tried about everything I can think of: I even tried a sample
that has worked for others using an assembly to read a text file but to no
avail. Sorry for all of the code below but I'm at a loss. I've also set the
version to be 1.0.0.0.0 in my assembly and added the : <Assembly:
AllowPartiallyTrustedCallers()> although I haven't setup strong naming. If
you see anything that I've messed up, please let me know...I'm stumped.
This is my Class Code:
Imports System.IO
Imports System.Security.Permissions
Public Class ReadResultsFile
<FileIOPermissionAttribute(SecurityAction.Assert,
Read:="C:\JHHTST2.txt")> _
Public Shared Function GetResults() As String
Dim reader As StreamReader = New StreamReader("c:\JHHTST2.txt")
Dim hello As String = reader.ReadToEnd()
reader.Close()
Return hello
End Function
End Class
This as the PermissionSet Code:
<PermissionSet
class="NamedPermissionSet"
version="1"
Name="ReadObjectFilePermissionSet"
Description="A special permission set that grants read access to my hello
file.">
<IPermission
class="FileIOPermission"
version="1"
Read="C:\JHHTST2.txt"
/>
<IPermission
class="SecurityPermission"
version="1"
Flags="Execution, Assertion"
/>
</PermissionSet>
This is my Codegroup:
<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="ReadObjectFilePermissionSet"
Name="ReadScriptFile"
Description="A special code group for my custom assembly.">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="C:\Program Files\Microsoft SQL Server\MSSQL\Reporting
Services\ReportServer\bin\ReadScriptFile.dll"
/>
</CodeGroup>
Thanks,
JamesH.|||Make sure the the CodeGroup section you added is right below the
Report_Expressions_Default_Permissions CodeGroup. Position in the file
does have an effect.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.|||I added it in there, no change.
JamesH.
"Donovan R. Smith [MS]" wrote:
> Make sure the the CodeGroup section you added is right below the
> Report_Expressions_Default_Permissions CodeGroup. Position in the file
> does have an effect.
> --
> Thanks.
> Donovan R. Smith
> Software Test Lead
> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Never Mind, got it working today by adding a linked server to SQL and then
using the SQLClient connection in the assembly, worked like a champ... Is
there anybody that has used ODBC with Custom assemblies?
JamesH.
"JamesH" wrote:
> This weekend I rebuilt everything at least 10 times and then built a virtual
> pc image and reloaded everything to no avail. I've added Strong_name to the
> .dlls and still no luck. I'm not seeing any errors in the logs at all, just
> the #error message on my report screen. I've tried deleting the reports,
> re-adding the custom assembly each time and then re-deploying along with
> re-copying the .dll. I even tried to get it to work with another .dll that
> still doesn't work. Any help now would be greatly appreciated on just
> getting a single custom assembly working. Are there any walk-throughs
> (cradle to grave) at all by MS?
> THanks,
> "JamesH" wrote:
> > I added it in there, no change.
> >
> > JamesH.
> >
> > "Donovan R. Smith [MS]" wrote:
> >
> > > Make sure the the CodeGroup section you added is right below the
> > > Report_Expressions_Default_Permissions CodeGroup. Position in the file
> > > does have an effect.
> > >
> > > --
> > > Thanks.
> > >
> > > Donovan R. Smith
> > > Software Test Lead
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no rights.
> > >sql