Results 1 to 11 of 11
  1. #1
    Join Date
    Sep 2011
    Posts
    57

    Question Unanswered: how to call procedures without schema?

    Hi experts.

    I've got an issue about calling sp without writting the shchema. I mean is there anyway to create a kind of public sinonym or sort like that? I'm using w7 v 9.7. Thanks for your help.

    Best regards.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If a schema is missing, whether it is for a Stored Procedure call or a table reference, DB2 will implicitly supply it. See here:

    IBM DB2 9.7 Information Center for Linux, UNIX, and Windows

    Andy

  3. #3
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18

  4. #4
    Join Date
    Sep 2011
    Posts
    57
    Thanks for your quickly replay, here's the matter. Yesterday I tried to do so, but when I deploy the proceure send me an issue. Here's the code

    Code:
    LANGUAGE SQL AUTONOMOUS
     
    BEGIN
    DECLARE VLISalida INTEGER;
    DECLARE VEI_SQLCODE INTEGER;
     
    set current SCHEMA  ='AETADEO';
     
    IF  VEV_SQLCODER LIKE 'SQL%' THEN 
    SET VEI_SQLCODE= SUBSTR(VEV_SQLCODER,4,3);
    ELSE 
    SET VEI_SQLCODE=VEV_SQLCODER;
    END IF;
     
          IF VEI_SQLCODE < 0 AND VEI_PROCESO > 0 THEN 
                CALL SP_GE_BitacoraProcesos (VEI_PROCESO, VEV_ESQUEMA, VEV_PROCEDIMIENTO, 1, VLISalida);
                GOTO INSERCION;
          ELSE
                GOTO INSERCION; 
          END IF;
          
    INSERCION:
    INSERT INTO CON_BIT_EJECUCION_DETALLE (ID_EJECP_PROCESO,EJECD_ESQUEMA,EJECD_PROCEDIMIENTO,EJECD_SQLCODE, EJECD_ECODE,EJECD_QUERY,EJECD_DESCRIPCION,EJECD_RESULTADO,TS_EJECD_FECHA)
    VALUES (VEI_PROCESO,  VEV_ESQUEMA,VEV_PROCEDIMIENTO, VEI_SQLCODE,      VEV_SQLCODE, VEV_QUERY, VEV_DESCRIPCION, VEV_RESULTADO, CURRENT TIMESTAMP);
     
    END
    Thanks

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Why not just supply the schema on the CALL?

    Andy

  6. #6
    Join Date
    Sep 2011
    Posts
    57
    I know that is the easiest way, but for polititcs (in the work) i can't do it anymore, besides in a SP i can call a lot of SP, so we're looking a way to call an SP without supply de schema everytime need it.

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What is the difference of hard coding a SET SCHEMA just before the CALL or just supplying the schema on the CALL? Politics is a dumb reason for doing something. DB2 needs to know the schema somehow so it knows which SP to call. It is also better for readability, maintenance, useability if the schema is supplied during the CALL. There is no ambiguity as to which SP is being called.

    Andy

  8. #8
    Join Date
    Sep 2011
    Posts
    57
    I know it, but i don't make politics, unfortunally i have to follow them even i don't like them...

  9. #9
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You have my sympathy.

    Andy

  10. #10
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Some sites at which I work also prefer to avoid hard coding the schema-qualifier in the CALL statement. It gives operational advantages in certain scenarios.

    Attached script-file shows a trivial example of calling sql-pl stored procedures without using explicit schemas in the CALL statement, by using the SET PATH method.

    Works on DB2 v9.7.

    A more realistic technique would fetch the FUNCTION PATH value dynamically from a table (instead of hard-coding the string like my example) and then apply the function-path value before invoking other procedures.
    Attached Files Attached Files

  11. #11
    Join Date
    Sep 2011
    Posts
    57
    I'm going to check it and use it. Thanks a lot.

Posting Permissions

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