This article describes a problem which occurs when CURRENT does not recalculate the actual date and time when called more than once in a stored procedure.
Resolving The Problem
PROBLEM
Repeated calls to the CURRENT function within a stored procedure always returns the same value.
This problem is typically seen when the stored procedure uses a cursor that loops through large quantities of data, such that by the time the final row is reached, the actual time has moved on and CURRENT no longer hold the true system datetime.
CAUSE
If using CURRENT in a stored procedure, the datetime value is set to the current system datetime at the time the procedure is called. CURRENT is not updated while the stored procedure is running.
SOLUTION
The system tablesysmaster:sysshmvals.sh_curtimestores the exact system datetime. Run the following query within your stored procedure to return the actual time and store it into the local variable current_time.
select DBINFO('utc_to_datetime', sh_curtime) into current_time from sysmaster:sysshmvals;
The following example demonstrates the different results returned when comparing the use of the CURRENT function with that of the sysshmvals query shown above.
Example:
create database currdemo;
create table dates (text char(20), time datetime year to second);
create procedure test() define i int; define current_time datetime year to second;
insert into dates values ("current 1st run", CURRENT);
select DBINFO('utc_to_datetime', sh_curtime) into current_time from sysmaster:sysshmvals; insert into dates values ("realtime 1st run", current_time);
system "sleep 5" ;
insert into dates values ("current 2nd run", CURRENT);
select DBINFO('utc_to_datetime', sh_curtime) into current_time from sysmaster:sysshmvals; insert into dates values ("realtime 2nd run", current_time);
end procedure;
execute procedure test();
select * from dates order by text;
The output looks as follows:
text time
current 1st run 2006-10-31 17:02:11 current 2nd run 2006-10-31 17:02:11 realtime 1st run 2006-10-31 17:02:11 realtime 2nd run 2006-10-31 17:02:16
프로시저 내부 작업들이 병렬로 수행되는 것은 참 좋아보입니다만 시스템 자원을 과도하게 사용할 가능성도 생깁니다.
따라서 PDQ 값을 0또는 작은 값으로 설정하고 프로시저를 컴파일하는 것이 좋겠네요.
Informix의 각 데이터베이스 별로 sysprocplan과 sysprocedures 테이블이 존재합니다. 아래는 카탈로그 테이블들의 각 컬럼 값을 참조하여 PDQ값을 확인하는 사용자 정의 함수 코드입니다.
CREATE FUNCTION get_proc_pdq_value(v_proc_name VARCHAR(128))
RETURNING SMALLINT;
DEFINE v_ret_pdq_value SMALLINT;
SELECT
CASE data[1,3]
WHEN "AAA" THEN 0
WHEN "AQA" THEN 1
WHEN "AAE" THEN 1
WHEN "AgA" THEN 2
WHEN "AAI" THEN 2
WHEN "AwA" THEN 3
WHEN "AAM" THEN 3
WHEN "BAA" THEN 4
WHEN "AAQ" THEN 4
WHEN "BQA" THEN 5
WHEN "AAU" THEN 5
WHEN "BgA" THEN 6
WHEN "AAY" THEN 6
WHEN "BwA" THEN 7
WHEN "AAc" THEN 7
WHEN "CAA" THEN 8
WHEN "AAg" THEN 8
WHEN "CQA" THEN 9
WHEN "AAk" THEN 9
WHEN "CgA" THEN 10
WHEN "AAo" THEN 10
WHEN "CwA" THEN 11
WHEN "AAs" THEN 11
WHEN "DAA" THEN 12
WHEN "AAw" THEN 12
WHEN "DQA" THEN 13
WHEN "AA0" THEN 13
WHEN "DgA" THEN 14
WHEN "AA4" THEN 14
WHEN "DwA" THEN 15
WHEN "AA8" THEN 15
WHEN "EAA" THEN 16
WHEN "ABA" THEN 16
WHEN "EQA" THEN 17
WHEN "ABE" THEN 17
WHEN "EgA" THEN 18
WHEN "ABI" THEN 18
WHEN "EwA" THEN 19
WHEN "ABM" THEN 19
WHEN "FAA" THEN 20
WHEN "ABQ" THEN 20
WHEN "FQA" THEN 21
WHEN "ABU" THEN 21
WHEN "FgA" THEN 22
WHEN "ABY" THEN 22
WHEN "FwA" THEN 23
WHEN "ABc" THEN 23
WHEN "GAA" THEN 24
WHEN "ABg" THEN 24
WHEN "GQA" THEN 25
WHEN "ABk" THEN 25
WHEN "GgA" THEN 26
WHEN "ABo" THEN 26
WHEN "GwA" THEN 27
WHEN "ABs" THEN 27
WHEN "HAA" THEN 28
WHEN "ABw" THEN 28
WHEN "HQA" THEN 29
WHEN "AB0" THEN 29
WHEN "HgA" THEN 30
WHEN "AB4" THEN 30
WHEN "HwA" THEN 31
WHEN "AB8" THEN 31
WHEN "IAA" THEN 32
WHEN "ACA" THEN 32
WHEN "IQA" THEN 33
WHEN "ACE" THEN 33
WHEN "IgA" THEN 34
WHEN "ACI" THEN 34
WHEN "IwA" THEN 35
WHEN "ACM" THEN 35
WHEN "JAA" THEN 36
WHEN "ACQ" THEN 36
WHEN "JQA" THEN 37
WHEN "ACU" THEN 37
WHEN "JgA" THEN 38
WHEN "ACY" THEN 38
WHEN "JwA" THEN 39
WHEN "ACc" THEN 39
WHEN "KAA" THEN 40
WHEN "ACg" THEN 40
WHEN "KQA" THEN 41
WHEN "ACk" THEN 41
WHEN "KgA" THEN 42
WHEN "ACo" THEN 42
WHEN "KwA" THEN 43
WHEN "ACs" THEN 43
WHEN "LAA" THEN 44
WHEN "ACw" THEN 44
WHEN "LQA" THEN 45
WHEN "AC0" THEN 45
WHEN "LgA" THEN 46
WHEN "AC4" THEN 46
WHEN "LwA" THEN 47
WHEN "AC8" THEN 47
WHEN "MAA" THEN 48
WHEN "ADA" THEN 48
WHEN "MQA" THEN 49
WHEN "ADE" THEN 49
WHEN "MgA" THEN 50
WHEN "ADI" THEN 50
WHEN "MwA" THEN 51
WHEN "ADM" THEN 51
WHEN "NAA" THEN 52
WHEN "ADQ" THEN 52
WHEN "NQA" THEN 53
WHEN "ADU" THEN 53
WHEN "NgA" THEN 54
WHEN "ADY" THEN 54
WHEN "NwA" THEN 55
WHEN "ADc" THEN 55
WHEN "OAA" THEN 56
WHEN "ADg" THEN 56
WHEN "OQA" THEN 57
WHEN "ADk" THEN 57
WHEN "OgA" THEN 58
WHEN "ADo" THEN 58
WHEN "OwA" THEN 59
WHEN "ADs" THEN 59
WHEN "PAA" THEN 60
WHEN "ADw" THEN 60
WHEN "PQA" THEN 61
WHEN "AD0" THEN 61
WHEN "PgA" THEN 62
WHEN "AD4" THEN 62
WHEN "PwA" THEN 63
WHEN "AD8" THEN 63
WHEN "QAA" THEN 64
WHEN "AEA" THEN 64
WHEN "QQA" THEN 65
WHEN "AEE" THEN 65
WHEN "QgA" THEN 66
WHEN "AEI" THEN 66
WHEN "QwA" THEN 67
WHEN "AEM" THEN 67
WHEN "RAA" THEN 68
WHEN "AEQ" THEN 68
WHEN "RQA" THEN 69
WHEN "AEU" THEN 69
WHEN "RgA" THEN 70
WHEN "AEY" THEN 70
WHEN "RwA" THEN 71
WHEN "AEc" THEN 71
WHEN "SAA" THEN 72
WHEN "AEg" THEN 72
WHEN "SQA" THEN 73
WHEN "AEk" THEN 73
WHEN "SgA" THEN 74
WHEN "AEo" THEN 74
WHEN "SwA" THEN 75
WHEN "AEs" THEN 75
WHEN "TAA" THEN 76
WHEN "AEw" THEN 76
WHEN "TQA" THEN 77
WHEN "AE0" THEN 77
WHEN "TgA" THEN 78
WHEN "AE4" THEN 78
WHEN "TwA" THEN 79
WHEN "AE8" THEN 79
WHEN "UAA" THEN 80
WHEN "AFA" THEN 80
WHEN "UQA" THEN 81
WHEN "AFE" THEN 81
WHEN "UgA" THEN 82
WHEN "AFI" THEN 82
WHEN "UwA" THEN 83
WHEN "AFM" THEN 83
WHEN "VAA" THEN 84
WHEN "AFQ" THEN 84
WHEN "VQA" THEN 85
WHEN "AFU" THEN 85
WHEN "VgA" THEN 86
WHEN "AFY" THEN 86
WHEN "VwA" THEN 87
WHEN "AFc" THEN 87
WHEN "WAA" THEN 88
WHEN "AFg" THEN 88
WHEN "WQA" THEN 89
WHEN "AFk" THEN 89
WHEN "WgA" THEN 90
WHEN "AFo" THEN 90
WHEN "WwA" THEN 91
WHEN "AFs" THEN 91
WHEN "XAA" THEN 92
WHEN "AFw" THEN 92
WHEN "XQA" THEN 93
WHEN "AF0" THEN 93
WHEN "XgA" THEN 94
WHEN "AF4" THEN 94
WHEN "XwA" THEN 95
WHEN "AF8" THEN 95
WHEN "YAA" THEN 96
WHEN "AGA" THEN 96
WHEN "YQA" THEN 97
WHEN "AGE" THEN 97
WHEN "YgA" THEN 98
WHEN "AGI" THEN 98
WHEN "YwA" THEN 99
WHEN "AGM" THEN 99
WHEN "ZAA" THEN 100
WHEN "AGQ" THEN 100
ELSE
-1
END pdq_value
INTO
v_ret_pdq_value
FROM
sysprocplan p, sysprocedures f
WHERE
p.planid = -2 AND
f.procid = p.procid AND
f.procname = v_proc_name;
IF v_ret_pdq_value = -1
THEN
RAISE EXCEPTION -746,0,'Could not decode PDQ value. Please check query';
ELSE
RETURN v_ret_pdq_value;
END IF
END FUNCTION;
과연 일반 사용자들은 짐작조차 할 수 없겠군요. 어쨌든 이 함수를 사용해 각 함수나 프로시저의 PDQ값을 참조하여 재컴파일 등을 고려해볼 필요도 있겠습니다.
제가 프로시저를 재컴파일한 테스트 결과를 공유드립니다.
$ dbaccess testdb -
Database selected.
> select get_proc_pdq_value('temp_test') from systables where tabid = 1;
(expression)
80
1 row(s) retrieved.
> set pdqpriority 100;
PDQ Priority set.
> update statistics for routine;
Routine Statistics updated.
> select get_proc_pdq_value('temp_test') from systables where tabid = 1;
(expression)
100
1 row(s) retrieved.
> set pdqpriority 0;
PDQ Priority set.
> update statistics for routine;
Routine Statistics updated.
> select get_proc_pdq_value('temp_test') from systables where tabid = 1;
(expression)
0
1 row(s) retrieved.
데이터베이스 마이그레이션, 리스토어(RESTORE) 작업으로 인해 루틴이 INVALID 상태로 빠지는 경우가 있습니다. 아래에서 설명하는 sysproc.admin_revalidate_db_objects 프로시저를 사용해 오브젝트 별 또는 일괄로 루틴들을 재컴파일 할 수 있습니다.
Question
DB2 Package could be revalidated by either the REBIND or the BIND command, however it may not work for SQL routines, how to mark a routine to be VALID?
Cause
For some reasons, some packages or routines may be marked as INVALID. For package which is invalid, it will allow revalidation to occur implicitly when the package is first used, or to occur explicitly by either the REBIND or the BIND command. The rebind_rouine_package function will rebind the package associate with the SQL procedure, it will mark the related package itself VALID as well. -> 해당 루틴이 VALID인지는 syscat.routines 카탈로그 테이블을 참조하면 됩니다. 'N'이면 INVALID 상태입니다.
However it is not the case for routine, it could not mark the routine VALID by REBIND, BIND command, and the rebind_rouine_package function.
Answer
As REBIND validate the package only, for routine to be VALID, it needs to revalidate it implicitly by accessing it, or call function such as admin_revalidate_db_objects explicitly.
For example: call sysproc.admin_revalidate_db_objects('PROCEDURE','MY_SCHEMA','MY_PROCEDURE') -> 특정 프로시저나 함수이름을 지정할 수 있고, 특정 스키마에 대한 일괄 수행을 하려면 NULL을 씁니다.
It is different to revalidate objects that are inside of a module, If the routine is within one module, it needs to use the MODULE type with the name of a specific module in ADMIN_REVALIDATE_DB_OBJECTS call, and all of the invalid objects include routines inside of that module will be revalidated.
Such as the routine SYSIBMADM.WRITE_BLOB could NOT be revalidated by: call sysproc.admin_revalidate_db_objects('PROCEDURE','SYSIBMADM','WRITE_BLOB').
Instead, as it belongs to UTL_MAIL module, it needs to be revalidated by: call sysproc.admin_revalidate_db_objects('MODULE','SYSIBMADM','UTL_MAIL').