This article is written in English and Portuguese
Este artigo est� escrito em Ingl�s e Portugu�s
English version:
A few years ago I needed to check if I had some stored procedures created with PDQ. Maybe some readers don't know, but if you have SET PDQPRIORITY n in your session before creating a procedure it will run with that PDQ. And that can cause abnormal resource consumption. In most cases you'll want your procedures created without PDQ.
The PDQ associated with a procedure will also change if you run UPDATE STATISTICS FOR PROCEDURE [...], in a session with PDQ active. This is also a frequent issue when people forgot to turn it off or when the statistics are gathered for tables (where PDQ can be helpful) and procedures on the same command or script.
So, how to check the PDQ of your procedures? The catalog tables don't provide this information, so at the time I asked for help internally within IBM. Cosmo, from UK came to my rescue with a strange query that I used successfully a couple of times. The query checks the first 3 characters of the column "data" from sysprocplan where planid = -2.... Don't ask... supposedly this is an encode of the PDQ value.
Recently I had another situation on a customer, where we suspected they had their procedures with PDQ. We were expecting to see the value of 80 (which we caught in some sessions) but all we saw was NULL. Once again I asked Cosmo for help, and during our conversations we noticed that we only had run the query on
little endian platforms (Linux on Intel and Tru64). So, Cosmo sent me a script that created 100 procedures with different PDQ levels and then returns the encoded values. It was no surprise that the values were different. Following Cosmo's suggestion I joined both set of values into a big CASE statement that you can check at the end of this article.
The query has a few important points:
- I used data[1,3] in the CASE statement. We could use data::CHAR(3), but with that it would not run on version 7. It's not supported anymore, but there are still people using it, so I decided to use a syntax that's compatible with those old versions.
- If I remember correctly Cosmo's first query was really a function that received the procedure name. I'm including that as another option in the end of the article. In that version the query raises and error if it enters the ELSE condition of the CASE statement. In the version I was using at the later customer, the case returned NULL and that fulled me... In this new version it will raise an error since it means there is something wrong with the query.
For the non-procedural version, I did a small and dirty trick: If it enters the ELSE condition it will try to return a value divided by zero. This will cause an error.
Note that this conditions can't happen because the PDQ must be a value between 0 and 100. So if in the future the encoding changes the query will start raising errors, and this should alert the user... - The encoded values for PDQ 1 to 100 are distinct in the two types of platforms. The encoded value for 0 however is equal. That's why we have two WHEN conditions for each value between 1 and 100 and just one for 0.
Vers�o Portuguesa:
H� alguns anos atr�s precisei de verificar se tinha criado algum procedimento com PDQ. Talvez alguns leitores n�o saibam, mas se tivermos executado SET PDQPRIORITY n na sess�o antes de criar um procedimento, este ir� ser executado com esse valor de PDQ. E isso pode causar um consumo anormal de recursos. Na maioria das situa��es n�o queremos ter procedimentos criados com PDQ.
O PDQ associado a um procedimento tamb�m muda se corrermos um UPDATE STATISTICS FOR PROCEDURE [...], numa sess�o com PDQ activo. Isto � um problema habitual quando nos esquecemos de desligar o PDQ, ou quando as estat�sticas s�o criadas para tabelas (processo onde o PDQ � ben�fico) e procedimentos no mesmo comando ou
script.Como podemos verificar o PDQ dos procedimentos? As tabelas do cat�logo n�o disponibilizam esta informa��o, por isso na altura pedi ajuda internamente na IBM. O Cosmo, de UK, auxiliou-me com o envio de uma query um pouco estranha que usei algumas vezes com sucesso. A query verifica os tr�s primeiros caracteres de uma coluna "data" da tabela sysprocplan, onde "planid = -2"... N�o pergunte... � suposto isto ser o valor de PDQ codificado de alguma forma.
Recentemente tive outra situa��o num cliente onde suspeit�vamos que tinham os procedimentos com PDQ. Esper�vamos ver o valor 80 (que tinhamos visto activo em algumas sess�es), mas tudo o que a query nos dava era NULL. Note-se que se tivesse olhado bem para a query deveria ter percebido que o NULL implicava um erro. Mas como est�vamos � espera de ver um valor (80) cometi um "erro de simpatia". Recorri novamente ao Cosmo para ajuda. E durante as nossas trocas de impress�es not�mos que s� tinhamos corrido a query em plataforms que usam
little endian (Linux em Intel e Tru64). Assim o Cosmo enviou-me um
script que cria 100 procedimentos com diferentes valores de PDQ e que retorna os respectivos valores codificados. N�o foi surpresa ver valores diferentes. Seguindo uma sugest�o do Cosmo, juntei ambos os conjuntos de valores numa instru��o CASE que pode ser consultada no final deste artigo.
A query tem alguns pontos importantes:
- Utilizei data[1,3] na instru��o CASE. Pod�amos utilizar data::CHAR(3), mas com isso a query n�o correria na vers�o 7. Esta vers�o j� est� sem suporte, mas ainda � usada, por isso preferi uma sintaxe compat�vel com vers�es mais antigas
- Se bem me recordo a primeira query que o Cosmo me enviou, era na realidade uma fun��o que recebia o nome do procedimento que quer�amos verificar. Esta forma est� tamb�m inclu�da no final do artigo. Nesta forma o c�digo causa um erro caso entre na condi��o ELSE do CASE. Na vers�o que estava a usar neste �ltimo cliente, o CASE retornava NULL e isso enganou-me... Nesta vers�o actualizada e corrigida, ir� gerar um erro, dado que isso implica que algo n�o est� correcto na query. N�o vers�o n�o procedimental inclu� um pequeno truque: Se entrar no ELSE do CASE vai tentar retornar um valor a dividir por zero. Isto ir� causar um erro.
Note-se que esta condi��o n�o pode acontecer pois o PDQ ser� sempre um valor entre 0 e 100. Se no futuro os valores codificados forem alterados a query dever� come�ar a dar erro, e isso dever� alertar o utilizador... - Os valores codificados de PDQ de 1 a 100 s�o distintos nos dois tipos de plataforma (little endian e big endian). No entanto o valor codificado para 0 � igual. � por isso que temos duas condi��es WHEN para cada valor entre 1 e 100 e s� um para o valor 0.
Non-procedural query:
Query n�o procedimental:
SELECT
f.procname,
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
TRUNC(p.rowid / 0)
END pdq_value
FROM
sysprocplan p, sysprocedures f
WHERE
p.planid = -2 AND
f.procid = p.procid
ORDER BY 2 DESC
Procedural version:
Vers�o procedimental:
CREATE FUNCTION get_proc_pdq_value(v_proc_name VARCHAR(128))
RETURNING SMALLINT;
DEFINE v_ret_pdq_value SMALLINT;
SELECT
f.procname,
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;
Updated on Jan 12, 2011: Cosmo noted that the procedure should be declared as a function and asked me to reference him as Cosmo instead of Simon David
Alterado em 12 de Jan de 2011: O Cosmo referiu que o procedimento deveria ser declarado como fun��o e pediu para ser referido como Cosmo em vez de Simon David
