Hi Everyone,
Please find below the script, I needed to understand step by step:confused:. Please If someone can help me out i will be very greatful:D.
Please Guide me in a way which can help me out in figuring what this script doing on my server.
========================================================
first you are taking the start time then you have tables name inside the file ab_done_yyyymm_tablenames.unl
you are reading it line by line using while loop so that table name is stored in a variable called abcd
then you are connecting to database further is the normal DB procedure..
Thakz vidyadhar85,
Well actually i want to know the complete script, it would be really helpful if you or someone guide me step by step.
Like certain clauses
1) alter table $abcd add ( encrypt char(11) ) ;
create index idx_neuker on $abcd(insys_serial) ;
2) create procedure p$abcd()
returning char(40); ----------------------> what does this 40 means?
DEFINE acc_num char(20);
DEFINE i integer;
DEFINE sum integer;
set debug file to 'pev_rech_yyyymmnn.log';
trace 'begin update';
let i = 0;
let sum=0;
begin work;
foreach up_cur with hold for
3) let i=i+1;
let sum=sum+1;
if i=10000 then
trace 'record processed '||sum;
commit work;
let i=0;
begin work;
end if
end foreach
commit work;
trace 'procedure ok! total processed records are '||sum;
return "procedure complete!";
end procedure;
execute procedure p$abcd();
drop procedure p$abcd ;
drop index idx_neuker;
Please advice me step by step.
Thankz in advance.....
HOPE THIS WILL SLOVE SOME OF YOUR DOUBT:D
dbaccess xyzdb << EOF >> db$abcd.log 2>&1 ------------------- connecting to database
alter table $abcd add ( encrypt char(11) ) ; ------------------- altering the table to add a new column called encrypt with a datatype char of length 11
create index idx_neuker on $abcd(insys_serial) ; ------------------ creating a index named idx_neuker on table on column insys_serial
update statistics high for table $abcd(insys_serial);
create procedure p$abcd() ------- procedure started
returning char(40);
DEFINE acc_num char(20); ---- defining the different variable with datatypes like char an dinteger with different length as mentioned within ()
DEFINE i integer;
DEFINE sum integer;
set debug file to 'pev_rech_yyyymmnn.log'; ------ creating the debugg file with a name pev_rech_yyyymmnn.log
trace 'begin update';
let i = 0; ------ initializing the variable to 0
let sum=0;
begin work; ------ begin here
foreach up_cur with hold for ----- in this loop they are selecting the insys_serial into a variable called acc_num and updating the field encrypt=msisdn
select insys_serial into acc_num from $abcd
update $abcd set encrypt=msisdn where insys_serial=acc_num;
let i=i+1; --------------- this is done till it process for 10000 records the commit is done and it will loop back
let sum=sum+1;
if i=10000 then
trace 'record processed '||sum;
commit work;
let i=0;
begin work;
end if
end foreach ------- ending the if and for loop
commit work;
trace 'procedure ok! total processed records are '||sum;
return "procedure complete!";
end procedure;
execute procedure p$abcd();
drop procedure p$abcd ; -- deleteing the index and procedure here
drop index idx_neuker;
EOF