SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
DB2 connection problems

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
DB2 connection problems
Author Message
Greg Johnson



Joined: 20 Oct 2001
Posts: 26

Post DB2 connection problems Reply with quote
I have the latest build 13 that was posted.

I am writing SQL against a DB2 LUW version 8.2 database. When SQL Assistant brings up the connection dialog box to connect to the DB2 database and I try connecting I receive the following error:

Execute: ODBC Error: SQLExecDirect
[IBM][CLI Driver][DB2/NT] SQL0440N No authorized routine named "TRIM" of type "FUNCTION" having compatable arguments was found.
SQLSTATE=42884

values (current server, trim(user), 'noDB', trim(current schema))

I checked the target options and they were set for DB2.

So at this point I am not able to get SQL Assistant working for DB2. The problem seems to be the TRIM function used in your SQL statement. In DB2 there is an LTRIM and RTRIM but no TRIM function available. I cannot see anywhere that this SQL can be modified. I did modify the other DB2 queries that had the TRIM function to be RTRIM.
Tue Apr 24, 2007 11:08 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
Sounds like the trim() function is not available in your DB2 version. We will look into this issue.

For now, as a workaround use the following simple solution:

In SQL Assistant options click DB Options tab, then select DB Queries section. Scroll down and find Schemas (DB2) item and select it. You will see the actual query on the right hand side of the screen. In the query replace "TRIM(schemaname)" with "RTRIM(schemaname)" and also replace "TRIM(t.grantee)" with "RTRIM(t.grantee)" Click OK to save changes. You may need to restart your editor after this change.
Tue Apr 24, 2007 11:48 am View user's profile Send private message
Greg Johnson



Joined: 20 Oct 2001
Posts: 26

Post Reply with quote
I had made changes to the SQL for the DB Queries from TRIM to RTRIM. The code that is failing does not appear to be able to be modified using any of the DB Query Options screens.

As a workaround I added a UDF called TRIM to the database I am testing this with. That allowed me to get connected and everything seems to be working correctly.

I believe the TRIM function may have been available in DB2 version 7. I do not have any version 7 databases to check this out. DB2 version 8 and 9 has LTRIM and RTRIM functions but no TRIM function.
Wed Apr 25, 2007 1:47 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
Thanks for the update. TRIM function is documented in DB2 9 in the SQL Reference Part I. You can see it here
http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0023198.htm

Anyway, I am glad it is working for you now.
Wed Apr 25, 2007 2:30 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
In Build 2.0.14 and later we replaced TRIM with RTRIM to make SQL Assistant compatible with older DB2 versions
Thu May 03, 2007 10:01 pm View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7841

Post Reply with quote
If you still get that error, please update your SQL Assistant settings. In Options dialog, click Import/Export button, on the Import tab, select Default options and click OK to load factory-default settings.

In the default settings the Schema query for DB2 UDB looks like below. As you can see, it uses RTRIM function only.

Code:
select
   RTRIM(schemaname), 'SC'
from
   syscat.schemata

union all
select
   RTRIM(t.grantee), 'SL'
from
   (
   select grantee
     from syscat.dbauth
    group by grantee
   ) t

order by 1

Thu Mar 31, 2016 9:26 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.