Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Answered: Set Passthru on linux

    Hi,

    I have created few procedures on db2luw windows. Now I m trying to impact all the procedures on LINUX. In one procedure I m having

    EXECUTE IMMEDIATE 'SET PASSTHRU '|| p_database ;

    This procedure is working fine on windows db2 & trying to compile the same on linux I m getting the below error.I m having the SYSADM privilege.
    OS: Linux, Database: Db2 10.5

    Error:
    Create stored procedure returns SQLCODE: -789, SQLSTATE: 429BB.
    ARE_TABLE: 82: The data type for parameter or SQL variable "'SET PASSTHRU '" is not supported in the routine, compound SQL statement, or parameter list of a cursor value constructor.. SQLCODE=-789, SQLSTATE=429BB, DRIVER=4.17.30
    The data type for parameter or SQL variable "'SET PASSTHRU '" is not supported in the routine, compound SQL statement, or parameter list of a cursor value constructor.. SQLCODE=-789, SQLSTATE=429BB, DRIVER=4.17.30
    ARE_TABLE - Deploy for debug failed.
    ARE_TABLE - Roll back completed successfully.

    can someone kindly let me know what is error on linux & do i need to include anything else to work PASSTHRU on linux.

    Thanks

  2. Best Answer
    Posted by przytula_guy

    "is the connection db2 to db2
    dbname is still limited to 8 char in length"


  3. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    so : what is the datatype of p_database
    is the variable correctly initialized ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  4. #3
    Join Date
    Jul 2014
    Posts
    294
    Thanks for the reply.

    CREATE OR REPLACE PROCEDURE PREE_TABLE (IN p_job_id INTEGER,IN p_database VARCHAR(4000),IN p_table VARCHAR(4000))

    This is the database for p_database

  5. #4
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    this works on my 10.5 linux:

    Code:
    --#SET TERMINATOR @
    create procedure test_sp (p_database varchar(128))
    begin
      EXECUTE IMMEDIATE 'SET PASSTHRU '|| p_database ;
    end@
    db2 -f test_sp.sql

    Does it work in your environment?
    Regards,
    Mark.

  6. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    is the connection db2 to db2
    dbname is still limited to 8 char in length
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  7. #6
    Join Date
    Jul 2014
    Posts
    294
    I tried decreasing the length of p_database to 40 & now it is working.
    Is there any specific reason for this ?

    CREATE OR REPLACE PROCEDURE PREE_TABLE (IN p_job_id INTEGER,IN p_database VARCHAR(40),IN p_table VARCHAR(4000))

Tags for this Thread

Posting Permissions

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