DB2 connection problems

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.

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.

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.

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.

In Build 2.0.14 and later we replaced TRIM with RTRIM to make SQL Assistant compatible with older DB2 versions

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