Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    74

    Arrow Unanswered: Why I can't see the SQL stored procedure in SPB after I build it in the CLP.

    I am writing programs on windows 2000 server and DB2 7.2. The following codes is I have wrote and it is saved in d:\append_by_date.db2. After I run the db2cmd command I enter "db2 connect to develop user db2admin using db2admin" and then I connect to my database. After that I enter "db2 -td@ -vf d:\append_by_date.db2". The message of CLP is "DB20000I The SQL command completed successfully.". But when I open the SPB I find that I can't find it. Why??


    create procedure append_by_date(in source varchar(128),
    in target varchar(128),
    in s_datevar int,
    in t_etldate varchar(128),
    in t_datevar char(7),
    out error_code integer,
    out error_label varchar(254))

    language sql
    begin
    declare SQLCODE integer default 0;
    declare stmt varchar(32672);
    declare at_end int default 0;
    declare col_name varchar(128);
    declare all_col varchar(32672);

    declare c1 cursor for
    select colname
    from syscat.columns
    where tabschema = ucase(substr(source, 1, posstr(source, '.') - 1))
    and tabname = substr(source, posstr(source, '.') + 1, length(source))
    order by colno;

    declare EXIT HANDLER FOR SQLEXCEPTION
    set error_code = SQLCODE;
    declare CONTINUE HANDLER FOR NOT FOUND
    set at_end = 1;

    set error_code = 0;
    set all_col = '';

    set stmt = 'delete from '||target||
    ' where '||t_etldate||'='''||t_datevar||'''';
    set error_label = 'The position of the error raised is 0001.';
    prepare ps from stmt;
    set error_label = 'The position of the error raised is 0002.';
    execute ps;



    set at_end=0;
    open c1;
    fetch c1 into col_name;
    while at_end = 0 do
    set all_col = all_col||col_name||',';
    fetch c1 into col_name;
    end while;
    close c1;



    set stmt = 'insert into '||target||' select '||all_col||''''||substr(char(current date, iso), 1, 7)||
    ''' from '||source;
    set error_label = 'The position of the error raised is 0003.';
    prepare ps from stmt;
    set error_label = 'The position of the error raised is 0004.';
    execute ps;

    set error_label = '';
    end @

  2. #2
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186
    Hi,

    I doubt the way you are connecting to the database, i mean which user are you using. There may be difference between the owner of the procedure and the user with which you are connecting to the database in SPB.

    Please check and let us know.

    Cheers,
    Prashant

  3. #3
    Join Date
    Jan 2003
    Posts
    74

    Arrow

    Thank you for your replay. I have resolved this problem. Because I turned off the "auto-commit" option of CLP using the command of "db2set db2options=+c" before I build the SQL stored procedure and I forget it, so though the CLP raise "sucessful" message and I can use the command of "db2 select substr(prcschema, 1, 15), substr(procname, 1, 15) from syscat.procedures" to see the SQL stored procedure, actually DB2 haven't committed it. After I entered "db2 commit" I can see it in SPB.

Posting Permissions

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