Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Can I create a UDF in schema "SYSFUN" ??

    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

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What is the lanuguage? Are those SQL UDF's or C UDF's.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Quote Originally Posted by Marcus_A
    What is the lanuguage? Are those SQL UDF's or C UDF'sX

    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;
    This is DDL for one function.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by DBFinder
    Plz help

    DBFinder
    Read my lips.

    Please post the exact error message you are getting.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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)
    Attached Thumbnails Attached Thumbnails IMAGE.bmp  

  12. #12
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  13. #13
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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.
    Attached Files Attached Files

  14. #14
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by DBFinder
    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?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  15. #15
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •