Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2005
    Posts
    9

    Unanswered: create multiple functions / stored procs

    Hi

    This is my first post here. I have a very simple doubt.

    Is it possible to create multiple functions / stored procs in batch command like we can for creating multiple tables.

    My version details

    DB2 administration tools level:
    Product identifier SQL08010
    Level identifier 01010106
    Level DB2 v8.1.0.36
    Build level s021023
    PTF
    ================================================== ==========
    Java development kit (JDK):
    Level IBM Corporation 1.3.1
    ================================================== ==========



    Thanks in advance

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

    But, it is advisable not to use ; as the statement delimiter becuase SQL PL (you use in UDFs and SPs) use ; as the delimiter ..

    You may use @ ..

    Eg: create function f1(i int)
    returns int
    begin atomic
    declare j int ;
    set j=2 ;
    return i+j ;
    end @
    create function f2(i int)
    returns int
    begin atomic
    declare j int ;
    set j=2 ;
    return i+j ;
    end @

    save it as udf1.sql
    on the command line
    db2 -td@ -f udf1.sql

    HTH

    Sathayram
    db2
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jan 2005
    Posts
    9
    hi Sathyaram...thanx for the quick reply but theres a problem

    which prompt should I execute this command from...I mean should I use the Command Line processor or the normal command window(MS-DOS prompt)


    I use the command prompt for this command for creating multiple tables

    db2batch -d staging -a stride01/stday01 -f amit.sql -r amit.lst

    where amit.sql is the file containing all the create table commands and the .lst file is the file for error messages (I am sure u knew that)


    If I execute the command from the normal command prompt I get this error


    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL1024N A database connection does not exist. SQLSTATE=08003

    I know what the error means but I do not explicitly connect to the database while executing the batch command that I have mentioned above.

    Please bear with me as I am a beginner.

  4. #4
    Join Date
    Aug 2004
    Location
    London, UK
    Posts
    31

    Command Window/Command Line Processer

    I'm not sure why you're using db2batch. Just run what Sathya suggests from the DB2 Command Window/Command Line Processor. It'll save you lot's of time

  5. #5
    Join Date
    Jan 2005
    Posts
    9
    I am not keen on using the batch command ... its the only one I know

    I tried connecting to the DB through command centre/line processor / window
    and got the same error message viz.


    SQL0104N An unexpected token "db2" was found following "BEGIN-OF-STATEMENT".
    Expected tokens may include: "SELECT". SQLSTATE=42601

    except in command window where I could not execute the connect command.

    Any help will be appreciated

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    db2batch is not used for executing queries .. It is actually for performance testing ..

    Start -- > Run --> db2cmd

    This will open a new db2 command window ..

    In the db2 command window type in the command as specified above

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jan 2005
    Posts
    9
    Same error again

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL1024N A database connection does not exist. SQLSTATE=08003

    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL1024N A database connection does not exist. SQLSTATE=08003

    here I can't even use the connect command to connect to the database

Posting Permissions

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