Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002
    Posts
    2

    Question Unanswered: Sql1131n - Sqlstate=38503

    I am using db2udb 7.2 for Linux. Client side - Delphi. When I call sp from the client - I send '[IBM][CLI Driver][DB2/LINUX] SQL1131N ... SQLSTATE=38503'. Throw one-two times stored proc begin to work. After db2 restart - again 1-2 fails and then normal. I use many recursive SQL, and when I call sp without recursion - all very well. What is it mean? Help please.

  2. #2
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,
    Definitely the reason may be some programminig mistake in the Procedure which abnormally terminates the DARI process which is required for the procedure call.
    Also we need to check for other processes which may be terminating the DARI process which is initiated earlier.
    If you can please get the procedure on the forum.
    -Prashant G Dahalkar
    Prashant

  3. #3
    Join Date
    Mar 2002
    Posts
    2
    This mistake occures when strored procedure has recursive SQL and returns result set. For example:

    CREATE PROCEDURE NSI.VIEW$DOC_TYPES()
    SPECIFIC NSI.VIEW_DOC_TYPES
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    BEGIN
    DECLARE rCur CURSOR WITH RETURN
    FOR
    WITH work(level, id, name, code, own_id, path) AS
    (
    SELECT 1, sys_type_id, sys_type_name, sys_type_code, own_sys_type_id, varchar(rtrim(char(sys_type_id)),250)
    FROM ktlg_sys_type
    WHERE upper(sys_type_code) = 'DOC_STOCK'
    UNION ALL
    SELECT b.level+1, a.sys_type_id, a.sys_type_name, a.sys_type_code, a.own_sys_type_id, b.path||'>'||varchar(rtrim(char(a.sys_type_id)),25 0)
    FROM ktlg_sys_type a, work b
    WHERE a.own_sys_type_id = b.id
    )


    SELECT id, name, code, own_id FROM WORK
    order by path;

    OPEN rCur;

    END
    ;

    Table KTLG_SYS_TYPE is reference to itself SYS_TYPE_ID is PK and OWN_SYS_TYPE_ID is FK.

  4. #4
    Join Date
    Oct 2002
    Location
    orlando, fl
    Posts
    3

    Unhappy same SQL1131N here as well

    I run the following store procedure once and it fails with SQL1131N. Subsequent calls after this, it runs fine. Then if i run it after a couple of hours, it'll fail again, then start running again on subsequent calls.

    here's the store proc:
    CREATE PROCEDURE DB2INST1.GETXXX
    (IN IN_GRP_ID INTEGER
    )
    DYNAMIC RESULT SETS 1
    LANGUAGE SQL
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    MODIFIES SQL DATA
    begin
    declare c1 cursor with return to caller for
    --recursive union
    with temp1 (group_sys_id, parent_group_id) as
    ( (select root.group_sys_id, root.parent_group_id
    from XXX root
    where root.group_sys_id=IN_GRP_ID)
    union all
    (select child.group_sys_id, child.parent_group_id
    from temp1 parent, XXX child
    where parent.group_sys_id =
    child.parent_group_id)
    )

    select group_sys_id
    from temp1
    order by group_sys_id;

    open c1;
    end;

Posting Permissions

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