### Stored procedure PDQ / PDQ dos procedimentos

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

## No comments:

Post a Comment