DB2 question

deiussum · 09-13-2004, 04:30 PM

I'm hoping there is at least 1 DB2 expert here that can help me with a problem I am running into. I am in the process of changing some of our code so that we can run on a DB2 database as well as MS SQL Server. Most of my expertise is in the MS SQL Server domain, so it's been a bit of a learning experience switching over to DB2...

My problem is this: I converted our data model from our SQL Server to a DB2 server we have setup. After some fiddling around, I finally was able to get IBM's DB2 MTK to work... Our original database used a number of User defined data types, which got converted to User Defined Distinct DataTypes in DB2... All that is fine, so far...

Now, I am running into a problem where I get errors trying to execute any SQL statements that contain a LIKE clause on a column that uses one of those UserDefined Distinct DataTypes... For instance, one of those Types is an EchelonName, defined as a vargraphic (128). (In SQL Server it was an nvarchar(128))

If I execute the following SQL statement on our "Types" table where "Name" is a type of "EchelonName":

Code:

SELECT * 
FROM Types 
WHERE Name LIKE '%CISCO%'

I get the following error:

Quote:

ERROR [42884] [IBM][CLI Driver][DB2/NT] SQL0440N No authorized routine named \"LIKE\" of type \"FUNCTION\" having compatible arguments was found. SQLSTATE=42884\r\n

It appears to be a problem caused by the column being a user defined type, because I created a table with the following:

Code:

create table NESUSER.TEST 
(
    C1 VARCHAR(32),
    C2 VARGRAPHIC(32),
    C3 NESUSER.EchelonName   
)

Then tried the following queries:

Code:

-- Query 1, SUCCEEDS
SELECT * FROM NESUSER.TEST WHERE C1 LIKE '%ABC%';

-- Query 2, SUCCEEDS
SELECT * FROM NESUSER.TEST WHERE C2 LIKE '%ABC%';

-- Query 3, FAILS with same error as above
SELECT * FROM NESUSER.TEST WHERE C3 LIKE '%ABC%';

So... I'm hoping someone knows of some setting that allows me to get LIKE to work with user defined data types. The alternative is going back through my script to create the database and getting rid of the user defined data types altogether. A task I do not look forward to doing...

deiussum · 09-14-2004, 09:42 AM

Doing some more testing I found that I can't even do something like so:

Code:

SELECT * 
FROM NESUSER.TEST
WHERE C3='a'

Where C3 is again an EchelonName of type vargraphic....

The more I think about this, the more I think I should just change my conversion script so that it doesn't use any User-defined datatypes in the DB2 database...