Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2008
    Posts
    62

    Unanswered: Sql0553n & Sql0104n ????

    Hi,

    If u know then please tell what are the following errors which occur during function and procedure creation respectively?

    SQL0553N An object cannot be created with the schema name "SYSPROC ". LINE
    NUMBER=1. SQLSTATE=42939


    SQL0104N An unexpected token "GNRLNULL" was found following " PARAMETER
    STYLE". Expected tokens may include: "<proc_param_style>". LINE NUMBER=9.
    SQLSTATE=42601

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You care to tell us which version of DB2 you are using on which platform?

    The first error says that you cannot create an object with the schema name SYSPROC. What's not clear with the message? I guess you didn't bother to look at the explanation of the message:
    $ db2 "? sql553"


    SQL0553N An object cannot be created with the schema name
    "<schema-name>".

    Explanation:

    The reason the schema name "<schema-name>" is invalid depends on the
    type of object that is being created.
    * Table, view, index and package objects cannot be created with the
    schema name SYSCAT, SYSFUN, SYSSTAT or SYSIBM. It is strongly advised
    that schema names should not start with SYS since additional schemas
    starting with this letters may be reserved for exclusive use of DB2
    in the future.
    * All other types of objects (for example: user defined functions,
    distinct types, triggers, schemadata, aliases) cannot be created with
    any schema name that starts with the letters SYS.

    The statement cannot be processed.

    User response:

    Use a valid schema name or remove the explicit schema name and rerun the
    statement.

    sqlcode: -553

    sqlstate: 42939
    The second messages says you have a syntax error following the keywords PARAMETER STYLE. Since we don't know the exact statement that triggered this message, we can only repeat the message and its explanation:
    $ db2 "? sql104"


    SQL0104N An unexpected token "<token>" was found following "<text>".
    Expected tokens may include: "<token-list>".

    Explanation:

    A syntax error in the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure was detected at the specified token
    following the text "<text>". The "<text>" field indicates the 20
    characters of the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

    As an aid, a partial list of valid tokens is provided in the SQLERRM
    field of the SQLCA as "<token-list>". This list assumes the statement is
    correct to that point.

    The statement cannot be processed.

    User response:

    Examine and correct the statement in the area of the specified token.

    sqlcode: -104

    sqlstate: 42601
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2008
    Posts
    62
    sql553:

    Its right Knut.

    But I took the export (db2look) for "procedures". And creating them in other environment with same schema "SYSPROC" [which is already in procedure creation stmt by db2look].

    Now these stmts givs above error, but if i check in table "sysibm.sysprocedures" these procedures are there in this table with schema "SYSPROC"!!

    Now, The question is that is this error is ignorable?

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You need to rerun the db2look using the following additional parameter:

    -td @

    for example: db2look -d sample -e -td @

    Then when you run the generated ddl, use the following syntax:

    db2 -td@ -vf db2look_output.ddl
    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
    Quote Originally Posted by prashant44
    Now these stmts givs above error, but if i check in table "sysibm.sysprocedures" these procedures are there in this table with schema "SYSPROC"!!

    Now, The question is that is this error is ignorable?
    Hmm... since you neglect to show us at least one such CREATE PROCEDURE statement, we can't really comment.

    Maybe you run db2look against a DB2 for z/OS subsystem and now try to create the procedure on a DB2 LUW. (On DB2 z/OS, you can use the SYSPROC schema while DB2 LUW prevents you from doing that.) But whether this guess is right or wrong... who can tell? I can't.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Feb 2008
    Posts
    62
    Hello Markus it doesn't work. i just tried it. Again it gave the same error...

    Sorry Stolze, I should specify it.
    I am using DB2 LUW version "DB2 v8.1.1.120" on AIX platform.
    And the export was of the same kind of DB2 which is also in AIX. Here is ex from the fun. & proc. each.

    CREATE PROCEDURE "SYSPROC "."ADMIN_CMD"
    (
    IN CMD CLOB(2097152)
    )
    DYNAMIC RESULT SETS 1
    SPECIFIC ADMIN_CMD
    EXTERNAL NAME 'db2admcmd!admin_cmd'
    LANGUAGE C
    PARAMETER STYLE GNRLNULL
    NOT DETERMINISTIC
    FENCED NOT THREADSAFE
    MODIFIES SQL DATA
    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:
    SQL0104N An unexpected token "GNRLNULL" was found following " PARAMETER
    STYLE". Expected tokens may include: "<proc_param_style>". LINE NUMBER=9.
    SQLSTATE=42601


    And the function defn:


    CREATE FUNCTION SYSPROC.BASE_TABLE (aliasschema VARCHAR(128), aliasname
    VARCHAR(128)) RETURNS TABLE ( BASESCHEMA VARCHAR(128), BASENAME
    VARCHAR(128)) SPECIFIC BASE_TABLE DETERMINISTIC NO EXTERNAL ACTION
    RETURN WITH rec(baseschema, basename, level) AS (VALUES(aliasschema,
    aliasname, 10000) UNION ALL SELECT BASE_TABSCHEMA, BASE_TABNAME,
    level -1 FROM SYSCAT.TABLES, REC WHERE TABSCHEMA = BASESCHEMA AND
    TABNAME = BASENAME AND TYPE = 'A' AND level > 0) SELECT * FROM (SELECT
    BASESCHEMA, BASENAME FROM rec ORDER BY level FETCH FIRST ROW ONLY) AS
    X
    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 "SYSPROC ". LINE
    NUMBER=1. SQLSTATE=42939

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    That is an IBM supplied stored proc. You don't need to move it to a new database since it should already be 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

  8. #8
    Join Date
    Feb 2008
    Posts
    62
    Hi Markus & Stolze,
    I also think the same.

    Thank you Very much for your precious time & Your prompt reply. I think now it is clear to me. Thanks for clearing my Doubt.

    Regards,
    Prashant

  9. #9
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you find a stored procedure or function in the SYSPROC schema on DB2 LUW and if it is additionally documented in the manuals, it must be an IBM-supplied procedure and there must be a mechanism to create the procedure in your database (if it is not already there), e.g. db2updv9.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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