Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2003
    Posts
    51

    Unanswered: Writing stored procedure in DB2 V8

    I am very new to DB2. I have V8 in my system. I am trying to write a Stored Procedure thru the wizard. After completing the stored procedure, I get the following error. Can anybody help me in this.

    DSNTPSMP CONSOLE 1 *** DB2 V7R1 SQL Procedure Processor DSNTPSMP 1.15 (PQ86898 2004-04-01)
    DSNTPSMP CONSOLE 2 Precompile warnings and/or errors, rc=-2323
    DSNTPSMP CONSOLE 3 Abend 913 occured while running Precompile program DSNHPC
    DSNTPSMP CONSOLE 4 Precompile of C source is not successful, cannot continue
    DSNTPSMP CONSOLE 5 ** WARNING ** Delete processing did not complete.
    DSNTPSMP CONSOLE 6 Abend 913 occured while running dataset utility DSNX9STW.
    DSNTPSMP CONSOLE 7 ** WARNING ** Delete processing did not complete.
    DSNTPSMP CONSOLE 8 Abend 913 occured while running dataset utility DSNX9STW.
    DSNTPSMP CONSOLE 9 ** WARNING ** Delete processing did not complete.
    DSNTPSMP CONSOLE 10 Abend 913 occured while running dataset utility DSNX9STW.
    DSNTPSMP Summary 1 Request: BUILD routine PROCEDURE1 did not complete.
    PRECOMPILE SQLTERM 1 SOURCE STATISTICS
    PRECOMPILE SQLTERM 2 SOURCE LINES READ: 30
    PRECOMPILE SQLTERM 3 NUMBER OF SYMBOLS: 57
    PRECOMPILE SQLTERM 4 SYMBOL TABLE BYTES EXCLUDING ATTRIBUTES: 4096
    PRECOMPILE SQLTERM 5 THERE WERE 0 MESSAGES FOR THIS PROGRAM.
    PRECOMPILE SQLTERM 6 THERE WERE 0 MESSAGES SUPPRESSED BY THE FLAG OPTION.
    PRECOMPILE SQLTERM 7 272152 BYTES OF STORAGE WERE USED BY THE PRECOMPILER.
    PRECOMPILE SQLTERM 8 RETURN CODE IS 0


    Build utility function requested: BUILD
    SYSPROC.DSNTPSMP - Returned +8
    sampath gowri

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What version of DB2 (with FP level)? What OS? What type of SP are you trying to create (please supply the CREATE statement)?

    Andy

  3. #3
    Join Date
    Oct 2003
    Posts
    51
    OS is DB2 UDB for z/OS. Databse Version 8.
    Create Stmt is ,
    select * from tablename where fieldname=:hostvariable.
    sampath gowri

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I am sorry, but I do not know Z/OS.

    That does not look like a create procedure statement to me.

    Andy

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Wait, what wizard on z/OS are you talking about

    It must be some GUI installed on a client

    There are no wizards on the mainframe
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Oct 2003
    Posts
    51
    Kindly ignore all the above requests. I am trying to do a simple sql stmt. This shld be my stmt.
    Select * from tablename where fieldname=:value.
    while executing , the user shld be prompted to enter a value and as the user types the value, the results shld be displayed.

    It is not necessary that I need to write a stored procedure. I can write a simple sql stmt. In which window do I need to write this. I see many options like command Editor, development center etc. Which one do I need to use.

    Thanks.
    sampath gowri

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by sampathgowri
    Kindly ignore all the above requests. I am trying to do a simple sql stmt. This shld be my stmt.
    Select * from tablename where fieldname=:value.
    while executing , the user shld be prompted to enter a value and as the user types the value, the results shld be displayed.

    It is not necessary that I need to write a stored procedure. I can write a simple sql stmt. In which window do I need to write this. I see many options like command Editor, development center etc. Which one do I need to use.

    Thanks.

    Again, what product are you using?

    QMF, SPUFI, DSNTIAUL, DB2 Connect?

    Or some other product?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Oct 2003
    Posts
    51
    Db2connect.
    sampath gowri

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The first message you posted says "DB2 V7R1", which would be version 7 - not V8. So are you sure you are using V8? Are you sure you are working with the correct DB2 subsystem?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  10. #10
    Join Date
    Oct 2003
    Posts
    51
    I am not a DB2 programmer. And I really donno to answer all your questions. This is how i do.
    Start->Programs->IBM DB2->Command Line Tools->Command Editor.
    My task is to write a simple sql stmt. Execute it. While executing , the values has to be passed.
    Again, the stmt is,
    select * from tablename where fieldname=(value to be passed)
    Thanks.
    sampath gowri

  11. #11
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Are you executing a simple SQL statement or do you try to build a stored procedure? Your initial post says the latter, while your description here says the former. What shall it be?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  12. #12
    Join Date
    Oct 2003
    Posts
    51
    Either a stored procedure or thru a single stmt is ok with me. It has to be,
    select * from tablename where fieldname=(value to be passed).
    sampath gowri

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Since you want to user to be "prompted" to enter a value, you need some sort of application that does the prompting. Aside from that, your query is nothing special and you can run it from the application and show the results.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  14. #14
    Join Date
    Oct 2003
    Posts
    51
    Can this not be done in DB2, without using another application.

    I saw some example like this in DB2. A procedure by name ABC was created and called -
    Call ABC (param, param1). Param and Param1 were declared as host Variables. This will help the query/stored procedure to accept a value for the field.

    I also noticed something called as Host Variable in SqlAssist in the Where Filter. After declaring the host Variable, my stored procedure, gives the error, which I have mentioned in the thread begining.

    If I try to run just like a SQL query, i get the error message that
    'The host variable "<host-name>" appears in the SQL statement, but
    host variables are not allowed in dynamic SQL statements, in the
    SELECT statement of a view definition, or in the triggered action
    of a trigger definition.'

    Any help is greatly appreciated.

    Thanks.
    sampath gowri

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yes if you create an external stored procedure, you can call it using DB2 Connect and then you can supply input parameters

    I'm assuming your background is not DB2
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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