Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
4 replies Latest Post - ‏2013-01-23T07:20:24Z by B.Hauser
SystemAdmin
SystemAdmin
3129 Posts
ACCEPTED ANSWER

Pinned topic ?

‏2013-01-17T09:28:43Z |
i have a legacy program liba/pgma,
for accessing it i create a stored procedure in QGPL/sp_pgma.
liba is included in *libl.

THE FOLLOWING IS SP CODE:

CREATE PROCEDURE SP_PGMA (
IN I_ID CHAR(2),
OUT O_ERR_CODE CHAR(01),
OUT O_NAME CHAR(12))
LANGUAGE SQL
P1: BEGIN
DECLARE WK_PGMA_AREA CHAR(15);--;
SET O_ERR_CODE = ' ';
SET O_NAME = ' ';
SET WK_PGMA_AREA = I_ID
|| O_ERR_CODE
|| O_NAME;
CALL PGMA(WK_PGMA_AREA);
SET O_ERR_CODE = SUBSTR(WK_PGMA_AREA,3,1);
SET O_NAME = SUBSTR(WK_PGMA_AREA,4,12);
END P1

i write a embedded sql code in cobol to call sp_pgma.
but os/400 tells me she can not find out pgma in QGPL.
it seems she recognizes pgma will be in the library where SP resides.
has anyone encountered this problem?
Updated on 2013-01-23T07:20:24Z at 2013-01-23T07:20:24Z by B.Hauser
  • SystemAdmin
    SystemAdmin
    3129 Posts
    ACCEPTED ANSWER

    ? Stored Procedure can not locate legacy program which resides in *libl

    ‏2013-01-17T09:33:26Z  in response to SystemAdmin
    Sorry
    i miss the question title.
    • krmilligan
      krmilligan
      447 Posts
      ACCEPTED ANSWER

      Re: ? Stored Procedure can not locate legacy program which resides in *libl

      ‏2013-01-17T21:53:00Z  in response to SystemAdmin
      Did you register your external program with a CREATE PROCEDURE statement? The SQL PATH controls the resolution of unqualified CALL statements.
      • SystemAdmin
        SystemAdmin
        3129 Posts
        ACCEPTED ANSWER

        Re: ? Stored Procedure can not locate legacy program which resides in *libl

        ‏2013-01-23T05:48:53Z  in response to krmilligan
        i paste the code into as/400 source physical file.

        execute runsqlstm to create the stored procedure.

        the stored procedure can work (i.e. it can search unqualified object by library list) from called by java.

        i checked the runsqlstm paramaters, which has one "naming" , whose value is "*SYS".
        i think that's why Data Studio can not access unqualified object by library list.
        i configure property of driver properties(AS/400 Toolbox for JAVA Default),
        i added naming=system,
        then created stored procedure.
        but this stored procedure can not locate my table in another library, which is in my library list.

        has any one encountered this problem?
        • B.Hauser
          B.Hauser
          250 Posts
          ACCEPTED ANSWER

          Re: ? Stored Procedure can not locate legacy program which resides in *libl

          ‏2013-01-23T07:20:24Z  in response to SystemAdmin
          Check whether a DEFAULT or CURRENT SCHEMA is set in your environment.
          If a default/current schema is set tables, views and indexes are searched within this schema even though System Naming is used!

          For more information about SQL and System Naming please check the following link:
          System versus SQL name: part 2

          Birgitta