Results 1 to 8 of 8
  1. #1
    Join Date
    May 2008
    Posts
    39

    Unanswered: Parameterizing schema name

    Hi
    I am trying to paramertize schema name.Below procedure is working fine if I have ABCD--another parametre as varchar.But when I add a parameter as timestamp it throws an error.
    CREATE PROCEDURE x.SAMPLE_DYN1
    (
    IN SCHEMA_NAME VARCHAR(50),
    IN ABCD VARCHAR(30)
    )
    SPECIFIC x.SQL080723153803300
    LANGUAGE SQL
    NOT DETERMINISTIC
    CALLED ON NULL INPUT
    MODIFIES SQL DATA
    INHERIT SPECIAL REGISTERS
    BEGIN
    DECLARE ENO INTEGER DEFAULT 0;
    DECLARE ENAME CHAR(50);
    DECLARE STMT VARCHAR(500);
    DECLARE at_end SMALLINT DEFAULT 0;
    DECLARE DYN_STMT VARCHAR(500);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT FOUND-------------Exit handler from cursor loop
    BEGIN
    set at_end =1;
    RESIGNAL;
    END;

    --DECLARE STMT1 STATEMENT;

    SET STMT =' SELECT NAME FROM '||SCHEMA_NAME||'.ZZ_EMPT2 WHERE NO='||ABCD||'' ;


    -- EXEC SQL PREPARE S1 FROM TMT;

    PREPARE STMT1 FROM STMT;
    BEGIN
    --EXECUTE STMT1;
    DECLARE C1 CURSOR FOR STMT1;
    ------ Cursor declaration

    OPEN C1;
    WHILE (at_end = 0) DO
    FETCH C1 INTO ENAME;
    SET ENO = ENO +1;
    SET DYN_STMT = 'INSERT INTO idm_dev.ZZ_EMPT3(NO,NAME) VALUES(?,?)';
    PREPARE S1 FROM DYN_STMT;
    EXECUTE S1 USING ENO,ENAME;

    END WHILE;
    END;
    COMMIT;
    END;

    Error:
    [IBM][CLI Driver][DB2/LINUXX8664] SQL0440N No authorized routine named "||" of type "FUNCTION" having compatible arguments was found. LINE NUMBER=26. SQLSTATE=42884
    Please help

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You might have to do a RTRIM on the schema. But why not just issue the set schema command before your SQL Select.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    CONCAT only accepts CHAR or VARCHAR als parameter.

    other types must be CAST to CHAR before you can CONCAT them.



    try:

    SET STMT =' SELECT NAME FROM '||SCHEMA_NAME||'.ZZ_EMPT2 WHERE NO='''||CAST(ABCD AS CHAR(26) ) ||'''';

    > You need to enquote the concatenated string, so some extra quotes are required.

    > there is no need to RTRIM the SCHEMA-NAME. Blancs are allowed between schema and table name
    Last edited by amittambe; 07-28-08 at 12:45.

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Maybe I am missing something, but variable ABCD appears to be a VARCHAR:

    CREATE PROCEDURE x.SAMPLE_DYN1
    (
    IN SCHEMA_NAME VARCHAR(50),
    IN ABCD VARCHAR(30)
    )
    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
    May 2008
    Posts
    39
    Thanks for your reply.I converted to char as you had mentioned.But when I execute I get below error

    call X.SP_DYN ('X','2008-08-01 18:17:32.000000')
    SQL0104N An unexpected token ".32" was found following "S =
    2008-08-01-18.17". Expected tokens may include: "SELECTIVITY".
    SQLSTATE=42601

  6. #6
    Join Date
    Dec 2005
    Posts
    273
    please post your complete new CREATE PROCEDURE statement.

    Maybe there's just a quote missing

  7. #7
    Join Date
    May 2008
    Posts
    39
    CREATE PROCEDURE X."SP_DYN"
    (IN "PROCESS_NAME" VARCHAR(100),
    IN "RUN_DT" TIMESTAMP,
    IN "SCHEMA_NAME" VARCHAR(50),
    OUT "SOURCE_COUNT" INTEGER,
    OUT "TARGET_COUNT" INTEGER
    )


    BEGIN

    --======================
    --Variable Declarations
    --======================


    --================================================== ==============
    --Cursor Declaration
    --================================================== ==============
    DECLARE COUNTRY_LOAD CURSOR FOR CURSOR_STMT1;
    SET CURSOR_STMT ='SELECT COUNTRY_RTBL.COUNTRY_CD,COUNTRY_RTBL.COUNTRY_NM,A. DM_LINEAGE_ID
    FROM '||SCHEMA_NAME||'.COUNTRY_RT COUNTRY_RT,'||SCHEMA_NAME||'.ETL_LINE A
    WHERE A.DM_LINEAGE_CREATE_TS = '||CAST('2008-08-01 18:17:32.000000' AS CHAR(26) ) ||' AND A.PROCESS_NM = '||PROCESS_NAME||'';
    PREPARE CURSOR_STMT1 FROM CURSOR_STMT;

    END;

    call X.SP_DYN ('X','2008-08-01 18:17:32.000000')
    SQL0104N An unexpected token ".32" was found following "S =
    2008-08-01-18.17". Expected tokens may include: "SELECTIVITY".
    SQLSTATE=42601

    If I include quote before :i.e
    SET CURSOR_STMT ='SELECT COUNTRY_RTBL.COUNTRY_CD,COUNTRY_RTBL.COUNTRY_NM,A. DM_LINEAGE_ID
    FROM '||SCHEMA_NAME||'.COUNTRY_RT COUNTRY_RT,'||SCHEMA_NAME||'.ETL_LINE A
    WHERE A.DM_LINEAGE_CREATE_TS = '''||CAST('2008-08-01 18:17:32.000000' AS CHAR(26) ) ||'''AND A.PROCESS_NM = '||PROCESS_NAME||'';
    I get below error
    call IDM_DEV.SP__DYN ('SP_DYN','2008-08-01 18:17:32.000000','IDM_DEV',?,?);
    SQL0206N "SP_DYN" is not valid in the context where it is used.
    SQLSTATE=42703


    Statement processed with ERROR.

    Thanks in advance

  8. #8
    Join Date
    Dec 2005
    Posts
    273
    Yes, I see:


    ... ||'''AND A.PROCESS_NM = '||PROCESS_NAME||'' ...


    1) add a blanc before the AND
    2) you need some quotes before and after PROCESS_NAME.

    try:
    || ''' AND A.PROCESS_NM = ''' || PROCESS_NAME || ''''

Posting Permissions

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