Hi everybody,

At my workplace:
I need to create couple of functions is SYSFUN schema.

There are about six user defined functions in existing database. They all are in SYSFUN schema. Their name start as "SYSFUN "."ole......."

these are part of my DDL generated by db2look ver 8.1.9 fp 7

I need to duplicate database on CentOS 5.1 db2 v 9.5.

It doesn't allow me to create these functions in "SYSFUN" schema. If I remove the schema qualifier then these are created in DB2INST1 schema.


Anybody have an Idea ?? Everything else work with little touchup.

DBFinder

What is the lanuguage? Are those SQL UDF's or C UDF's.

This is DDL for one function.

So you created the database in V9.5 and then ran the db2look to create the addtional objects? Then that UDF should alread be there. If you created the database in an earlier version of DB2, you may need to upgrade the database so that these system objects will be created.

It helps if you post the exact error message.

DB2 LUW: You cannot create any objects by yourself in any schema starting with SYS or IBM. Those schemas are reserved for IBM-provided functionality only.

So as Marcus said, the real question is where the function comes from in the original system. Have you checked that this function wasn't deprecated and is now removed? And did you run the various db2updv8/db2updv9 tools after you applied some FixPaks or migrated to a newer version?

Well,

At job I was told to make duplicate database in DB2 V9.5 Express-C @ CenOS 5.1.

I created Database, Bufferpool and then Sequences and functions.
For all this I used [ db2look -d database -a -e -o NEWDDL.SQL ]
on source db2 V8.1.9 FP 7.

The DDL had couple other discrepancies including this one.
So I hve to find out where and how these UDFs were created if not allowed to be created in V9.5 ?

I have searched a lot an everywhere I git same answer as stolze. On day one I knew that I cannot create any function in "SYSFUN". Now before opening PMR with IBM I want to make sure that I try my friends' knowledge and experience.

So there are 6 functions that are OLE functions and I want to know how can I get these functions in Target Database. Otherwise if i skip these functions then import fails.

Plz help

DBFinder

Read my lips.

Please post the exact error message you are getting.

CREATE FUNCTION "SYSFUN "."OLEDBDATASOURCES"
(
VARCHAR(128)
)
RETURNS TABLE
(
NAME VARCHAR(128),
PROG_ID VARCHAR(128),
DESCRIPTION VARCHAR(128)
)
SPECIFIC SQL021018111113472
EXTERNAL NAME 'oledbfn!oledbdatasources'
LANGUAGE OLE
PARAMETER STYLE DB2SQL
NOT VARIANT
FENCED NOT THREADSAFE
NOT NULL CALL
NO SQL
NO EXTERNAL ACTION
SCRATCHPAD
NO FINAL CALL
DISALLOW PARALLEL
NO DBINFO;

DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0553N An object cannot be created with the schema name "SYSFUN ". LINE
NUMBER=1. SQLSTATE=42939

What is really strange on this is that none of the DB2 manuals mention a function like "OLEDBDATASOURCES".

Therefore, it would be good to know how those functions came into existence in DB2 V8. Do you see those functions when you create a new database in V8? If so, we are talking about undocumented functions, which are for internal purposes only. You can drop your quest to get them working in V9 right there. If you relied on them, it's your own fault and you need to rework your applications/scripts. If those function do not exist in a newly created database, try to figure out where they come from...

Did you check to see if those functions already exist in your database? Maybe you can just ignore the error. In any case, unless you accessing the database with an OLE connection via MS Dot.net, you probably don't even need it.

These functions are surprise to me. I must find out because the target database is a test database for our development team. As a DBA will ask them and I will update on this issue. For now I am attachin an image of the control Centre listing (last 8)

I don't understand the problem. According to your attachment, the functions already exist and don't need to be created (they come with DB2).

Yes, but those are in SOURCE database and I want to create these in TARGET database.

db2look generated DDL ( see attachment ) which I cannot run because db2 is not allowing me to create functions in SYSFUNC schema.

What version is the target database? Did you look in the target database to see if the UDF is already there?

Target is DB2 V9.5 Express-C on CentOS 5.1


There are 8 such functions in source database.
There are no such functions in target database t.