Stored procedures for timed execution of DB2 in Linux

I. Description of requirements

The live data in DB2 is aggregated into the summary table by day and hour.

Ii. process and steps

　　The first step : Write the DB2 stored procedure.

1. Create a new SQL editor in Dbvisualizer.

2. Code:

1--/2CREATE PROCEDURE"Ncpolldata"."aggregate_entity_daily"3 ()4 LANGUAGE SQL5 specific SQL1604271232063006 BEGIN7--Declare varibles8DECLARE V_prefix_time VARCHAR (7);9DECLARE V_start_time VARCHAR ( -);TenDECLARE V_end_time VARCHAR ( -); OneDECLARE V_aggregate_timestamp BIGINT DEFAULT0; ADECLARE V_year_value INTEGER DEFAULT0; -DECLARE V_month_value INTEGER DEFAULT0; - DECLARE v_aggregate_time TIMESTAMP; the--Setvaribles Value -SET V_prefix_time = ('1'|| REPLACE (SUBSTR (CHAR (Current_timestamp-1Days),3,9),'-',"')); -SET v_start_time = V_prefix_time | |'000000000'; -SET v_end_time = V_prefix_time | |'235959999'; +SET V_year_value = year (Current_timestamp-1Days ); -SET V_month_value = MONTH (Current_timestamp-1Days ); +SET v_aggregate_time = Current_timestamp-1Days ; ASET V_aggregate_timestamp = Timestampdiff (2, CHAR (Current_timestamp-timestamp ('1970-01-01-00.00.00'))); at--Action - For Temp_cur as -SELECT t1. Monitoredobjectid as"policy_id", T2. Mainnodeentityid as"entity_id",  -MIN (VALUE) as"Min_value", MAX (VALUE) as"Max_value", AVG (BIGINT (VALUE)) as"Avg_value",  -COUNT (VALUE) as"Data_count", SUM (BIGINT (VALUE)) as"Data_sum"  -From Polldata T1 the left JOIN monitoredinstance T2 on T1. Monitoredinstid =T2. Monitoredinstid in WHERE t1. Tdwtime between V_start_time and V_end_time - GROUP by T1. Monitoredobjectid, T2. Mainnodeentityid to ORDER by T1. Monitoredobjectid, T2. Mainnodeentityid +  Do ---INSERT Statement the INSERT into polldata_entity_daily * VALUES (temp_cur. policy_id, Temp_cur. entity_id, V_aggregate_timestamp, $ V_year_value, V_month_value, V_aggregate_time,Panax Notoginseng Temp_cur. Min_value, Temp_cur. Max_value, Temp_cur. Avg_value, Temp_cur. Data_count, Temp_cur. Data_sum);  - END for; the END +/

　　Note: Writing the DB2 stored procedure in Dbvisualizer, the first line of the--/and the last line/is essential otherwise error.

　　3. Execute the stored procedure: Click the Execute button of the Dbvisualizer stored procedure edit box, or you can execute call procedure_name on the command line.

　　Step Two : Write script commands that can be executed on Linux systems.

#!/bin/Shdb2_home=/opt/ibm/tivoli/netcool/platform/linux2x86/Db2path= $DB 2_home/bin: $PATHexport $DB 2_homeexport $PATHstartTime= ' Date +%y%m%d%h%m%S ' echo $startTimeecho"Start Script Execution"DB2 connect to ITNM user NCiMusingTiv0liecho"connect to the database and start executing the script"DB2"Call Ncpolldata. Aggregate_entity_daily ()"EndTime= ' Date +%y%m%d%h%m%S ' echo $endTimeecho"Script Execution Complete! To close a database connection"DB2 Terminate

Above command reference: http://blog.csdn.net/sweetbowie/article/details/19614289

　　Step three : Set up the Crond service with Linux system self-booting

[Email protected] ~]# /etc/init.d/crond Restart

If you let Crond run at boot time, you should change its operating level

[Email protected] ~]# chkconfig--levels crond on

　　"Links to Related Materials"

1. http://blog.csdn.net/sweetbowie/article/details/19614289

2. http://www.linuxidc.com/Linux/2012-02/54391.htm

3. http://blog.csdn.net/liu_xing_hui/article/details/6565804/

4. http://blog.csdn.net/lulei9876/article/details/9170151

5. http://blog.csdn.net/jiujie395/article/details/8692655

　　

Stored procedures for timed execution of DB2 in Linux