zOs/SQL/STPRDIAG

$#@
$>.fEdit('~wk.texv(stprdiag)')
$<=[
set current sqlid = 's100447' ;
drop   procedure gdb9998.diag1;
drop   procedure gdb9998.diag2;
terminator    } ;
create procedure gdb9998.DIAG1(in fun int, out diag varchar(32000))
        reads sql data                                       
    begin                                                    
    declare c1  varchar(20) default '-123';                  
    declare i1, i2 int default -99;                          
    declare sqlCode int default 0;                           
    declare prCu cursor for                                  
        select version, parm_count                           
        from sysibm.sysRoutines r                            
        where r.name = 'DIAG1';                              
    declare continue handler for sqlException begin          
        declare cSt, cCd, cCc varchar(1000) default '';      
        declare sSt, sCd, sCc varchar(1000) default '';      
        declare no, cLN, sLN int default -999;               
        declare tst timestamp;
        set tst = current timestamp;
        set diag = diag || ' handler@' || char(tst);
        get current diagnostics cSt = all statement;         
        get current diagnostics cCd = all condition;         
        get current diagnostics condition 1 cLn = db2_line_number;
        get current diagnostics cCc = all connection;        
        get stacked diagnostics sSt = all statement;         
        get stacked diagnostics condition 1 sLn = db2_line_number;
        get stacked diagnostics sCd = all condition;         
        get stacked diagnostics sCc = all connection;        
        set diag = diag || ' currentStmt>>> '   || cSt       
            || ' currentConndi>>> lNo='||strip(char(cLN)) ||',' || cCd
                        || ' currentConnec>>> ' || cCc;      
        if cSt <> sSt then
            set diag = diag  || ' stackedStmt>>> '   || sSt; 
            end if;
        if cCd <> sCd or cLn <> sLn then
            set diag = diag                                  
            || ' stackedConndi>>> lNo='||strip(char(sLN)) ||',' || sCd;
            end if;
        if cCc <> sCc then
            set diag = diag || ' stackedConnec>>> ' || sCc;  
            end if;
        end;                                                 
    set diag = 'beginFun=' || strip(char(fun));              
    set i1 = 22 / (fun-99);                                  
    set i1 = 22 / fun;                                       
    set diag = diag || ' l0=' || strip(char(i1));            
    if fun = 1 or fun = 99 then                              
        set c1 = 'fun1';                                     
        end if;                                              
    set i2 = int(c1);                                        
    set diag = diag || ' l1=' || strip(char(i2));            
    open prCu;                                               
    set diag = diag || ' l2=Open1';                          
    if fun = 2 or fun = 99 then                              
        open prCu;                                           
        set diag = diag || ' l2=Open2';                      
        end if;                                              
    set diag = diag || ' l99=end';
    return;
    end
}
terminator ; }
commit;
call gdb9998.DIAG1(-1, DIA1);
call gdb9998.DIAG1(0, DIA0);
call gdb9998.DIAG1(1, DIA1);
call gdb9998.DIAG1(2, DIA1);
call gdb9998.DIAG1(3, DIA1);
call gdb9998.DIAG1(99, DIA1);
$] call sqlStmtsOpt
$#out                                              20110628 09:23:28
$#out                                              20110628 09:19:21