Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    8

    Unanswered: DB2 Documentation

    Hi All,
    as a newcomer to DB2 (but with pretty good experience on various other DBs) I'm very confused by the available documentation on DB2. From experience, when writing a stored procedure or UDF, you look at the command/function syntax, then use it in your code. This doesnt seem to apply to DB2. I'll give 2 or 3 examples.

    1. in the online documentation examples, and in examples all over the web, you can write a function using BEGIN...END. In reality, you must use BEGIN ATOMIC...END when writing a function.

    2. in the online documentation examples, and in examples all over the web, you can DECLARE A CURSOR inside a function (CREATE FUNCTION ... DECLARE CURSOR...), but in reality, you can't, inside a function.

    3. in the online documentation examples, and in examples all over the web, you can declare a temporary table by DECLARE GLOBAL TEMPORARY TABLE, but in reality, again you can't.

    4. SPs and UDFs can fail to compile, but when I move a DECLARE statement up above another one, it compiles. This feature is not documented.

    Many things work in a SP, but not in a UDF, and I dont know why, and can find no documentation telling me why.

    Please can someone tell me where I'm going wrong? I'm wasting a hell of a lot of time on trial-and-error.

    Thanks,
    Deji

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: DB2 Documentation

    Are you checking the manuals for your platform and your version ?

    Can you post examples of your function or SP and the error you get when using them ?

    Most of the documents say what you can and don't say what you cant ...

    Cheers
    Sathyaram


    Originally posted by deji_akadiri
    Hi All,
    as a newcomer to DB2 (but with pretty good experience on various other DBs) I'm very confused by the available documentation on DB2. From experience, when writing a stored procedure or UDF, you look at the command/function syntax, then use it in your code. This doesnt seem to apply to DB2. I'll give 2 or 3 examples.

    1. in the online documentation examples, and in examples all over the web, you can write a function using BEGIN...END. In reality, you must use BEGIN ATOMIC...END when writing a function.

    2. in the online documentation examples, and in examples all over the web, you can DECLARE A CURSOR inside a function (CREATE FUNCTION ... DECLARE CURSOR...), but in reality, you can't, inside a function.

    3. in the online documentation examples, and in examples all over the web, you can declare a temporary table by DECLARE GLOBAL TEMPORARY TABLE, but in reality, again you can't.

    4. SPs and UDFs can fail to compile, but when I move a DECLARE statement up above another one, it compiles. This feature is not documented.

    Many things work in a SP, but not in a UDF, and I dont know why, and can find no documentation telling me why.

    Please can someone tell me where I'm going wrong? I'm wasting a hell of a lot of time on trial-and-error.

    Thanks,
    Deji
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Mar 2004
    Posts
    8
    Hi Sathyaram,
    I'm using v8.1 on windows XP.

    Here'a a couple of samples that won't compile.

    CREATE FUNCTION ARENA_SCHEMA.FuncX( param_0 VARCHAR(4) )
    RETURNS TABLE (table_column1 INTEGER)
    ------------------------------------------------------------------------
    -- SQL UDF (Table)
    ------------------------------------------------------------------------
    F1: BEGIN ATOMIC
    declare x cursor for select * from syscat.functions;
    RETURN SELECT count(*) FROM SYSCAT.FUNCTIONS;
    END

    --------------------------------------------------------------
    CREATE FUNCTION hzj2sq.FUNCTION1( )
    RETURNS INTEGER
    ------------------------------------------------------------------------
    -- SQL UDF (Scalar)
    ------------------------------------------------------------------------
    F1: BEGIN atomic

    DECLARE GLOBAL TEMPORARY TABLE SESSION.XDATES
    (x integer);

    RETURN NULL;
    END

    regards,
    Deji

  4. #4
    Join Date
    Jan 2004
    Location
    Tallahassee, FL, USA
    Posts
    96
    do install C compiler for SP 's.

    need to instal C compiler at DB server side.

    based on DB2 version need to setup SPpath to run SP's in DB2


    Let me know , if any more info


    --Lekharaju Ennam

  5. #5
    Join Date
    Mar 2004
    Posts
    8
    famudba,

    I have installed the C compiler. In fact, most of my SPs and UDFs compile and run succesfully. It's only those specific features that work in SPs, but not in UDFs. (DECLARE CURSOR, DECLARE GLOBAL TEMPORARY TABLE, etc)

    Regards,
    Deji

Posting Permissions

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