Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2011
    Posts
    87

    Unanswered: DB2 stored proc with sysproc.admin_cmd

    db2 9.7 fp 4 / windows

    Hi,

    With your assistance, I have created a procedure to export data.

    when I do a small select statement with sysproc.admin_cmd it works fine.

    eg:
    create procedure test()
    language sql
    begin
    call sysproc.admin_cmd('export to my.del of del select * from table');
    end

    But when I add a condition for the strings

    WHERE NAME="JOHN" it compiles successfully. but when I call , it gives error.

    "SQL3022N an SQL error -206 occurred while processing SELECT in the action sting parameter"

    When I tried with numeric value, again works fine.
    (where amount=100);

    This is the full procedure which compiles successfully, but gives error when you call.

    create procedure test()
    language sql
    BEGIN
    call sysproc.admin_cmd('export to my.del of del select * from table
    where name="JOHN" ');
    END

    can you please assist, I cant find any information in infocenter.
    Even I raised a PMR , IBM said they are testing in their test system and it may take a week to come back as this type of support is not included in the support contract.

    Thanks,

  2. #2
    Join Date
    Nov 2011
    Posts
    334
    It looks like that the error comes from the double quotes.
    You can try to use two single quote instead of double quotes

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by fengsun2 View Post
    It looks like that the error comes from the double quotes.
    You can try to use two single quote instead of double quotes
    I agree. If a double qoute is used it will be passed as is to the engine. The only possible interpretation is that it is a variable whos name starts with a double qoute (not allowed, but even if it where it would not be initialized).

  4. #4
    Join Date
    Nov 2011
    Posts
    87

    No success

    I have tried with single quote ' . But still not working. with single quote i cant even compile (I am using data studio).

    I tried to initialize a variable and assigned the name to it. Like

    Declare vname varchar(15);
    set vname='JOHN' ;

    even I tried with set vname="JOHN"

    But no success.

    any other suggestion ?

    Thanks,

  5. #5
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by db2mtrk View Post
    I have tried with single quote ' . But still not working. with single quote i cant even compile (I am using data studio).

    I tried to initialize a variable and assigned the name to it. Like

    Declare vname varchar(15);
    set vname='JOHN' ;

    even I tried with set vname="JOHN"

    But no success.

    any other suggestion ?

    Thanks,
    Code:
    create table t ( x char(1) not null primary key ) @
    create procedure p()
    language sql
    begin
    call sysproc.admin_cmd('export to /tmp/my.del of del select * from t where x = ''a''');
    end @
    Note, there are no double quotes in there, only single ones


    /Lennart
    --
    Lennart

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by db2mtrk View Post
    Even I raised a PMR , IBM said ... this type of support is not included in the support contract.
    Support contracts usually include product defect and product usage.

  7. #7
    Join Date
    Nov 2011
    Posts
    87

    Still not working

    Hi,
    I have used single quote (' ') and double quotes, but no success.

    can anybody test this ? or any advise please?


    Thanks.

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Did you try double single quotes: ''JOHN''?

    Andy

  9. #9
    Join Date
    Nov 2011
    Posts
    87
    Quote Originally Posted by ARWinner View Post
    Did you try double single quotes: ''JOHN''?

    Andy
    Yes, I tried with double single quotes. I will post the exact error when I am back to work.

    Thanks,

  10. #10
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by db2mtrk View Post
    Hi,
    I have used single quote (' ') and double quotes, but no success.

    can anybody test this ? or any advise please?


    Thanks.
    My example worked for me

  11. #11
    Join Date
    Nov 2011
    Posts
    87
    Hi, finally it's working with double single quotes.

    I am not sure that any issues with my data studio. I have changed the terminater from ; to @ then the data studio was hanging. I rebooted the PC then after the procedure was compiled successfully. It took me more than a week.

    Now, i need to enhance my query.

    with the sysproc.admin_cmd('export to myexp.del of del messages on server select * from table')

    i need to include retrieve the messages
    also, need to do the error handling.
    can you please assist me ?

    Thanks for all your support.

Posting Permissions

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