Sysadmin during upgrades / Sysadmin durante upgrades

Curious situation related to sysadmin during an upgrade (original version here: http://informix-technology.blogspot.com/2016/09/sysadmin-during-upgrades-sysadmin.html)


English version
Recently at a customer we were testing an upgrade from 11.50.xC7 to 12.10.xC7W1 and it apparently hanged. That's not what you like to see during an upgrade, even in a test environment.
We waited a few minutes and tried to understand what was happening. There was a thread called "convert" that apparently was idle. The thread stack trace apparently didn't change.
The functions showing up in the stack suggested it was trying to connect to a remote server following up what looks to be a call to UPDATE STATISTICS. That was a bit of a surprise.... looking at the online.log it was "hanged" during the "SCHAPI" phase. This is related to sysadmin.
Considering the upgrade didn't look good and it was a test system we decided to kill the engine and use onrestorept to revert back to version 11.50.
Then we decided to run an UPDATE STATISTICS FOR PROCEDURE on sysadmin database and voilá.... hanged again.
So, an UPDATE STATISTICS FOR PROCEDURE on sysadmin got hanged, and apparently that same thing happened during the upgrade. Further investigation led to a simple conclusion. We had created procedures in sysadmin to implement some monitoring tasks. And some of them tried to do remote operations on an engine where we centralize the monitoring data. Just before the upgrade a change was made to the instance $INFORMIXSQLHOSTS so that we "isolated" it from the outside world. This was done by adding some value to the service ports. The consequence of this was that during the upgrade, when it tried to run the UPDATE STATISTICS FOR PROCEDURE it tried to access a non-reachable remote engine. As it was not possible, it hanged some time for each remote instruction. Later tests on 12.10 showed the time it waits for each connection attempt is by default 60s and can be defined by changing the relatively new ONCONFIG parameter INFORMIXCONTIME

This can be easily reproduced. On a version 11.50 engine we create a simple (dumb) procedure:


bica@primary:informix-> onstat -

IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 00:30:33 -- 269068 Kbytes


bica@primary:informix-> cat test_remote_proc_stats.sql


CREATE PROCEDURE test_updstats_remote()

DEFINE i INTEGER;

SELECT 1 INTO i FROM stores@test:systables WHERE tabid = 1;

SELECT 2 INTO i FROM somedb@test:systables WHERE tabid = 1;

END PROCEDURE;
bica@primary:informix-> 


bica@primary:informix-> dbaccess sysadmin test_remote_proc_stats.sql

Database selected.

Routine created.

Database closed.

bica@primary:informix->

Database server "test" exists in the $INFORMIXSQLHOSTS, but points to a wrong port. Let's try an upgrade to 12.10.FC7 and see what happens.

From the online.log we get:


01:30:52 IBM Informix Dynamic Server Started.
01:30:52 Requested shared memory segment size rounded from 4308KB to 4796KB

Wed Aug 31 01:30:54 2016

01:30:54 Requested shared memory segment size rounded from 221001KB to 221004KB
01:30:54 Successfully added a bufferpool of page size 2K.

01:30:54 Event alarms enabled. ALARMPROG = '/usr/informix/srvr1210fc7w1/etc/alarmprogram.sh'
01:30:54 Booting Language from module <>
01:30:54 Loading Module
01:30:54 Booting Language from module <>
01:30:54 Loading Module
01:30:59 DR: DRAUTO is 0 (Off)
01:30:59 DR: ENCRYPT_HDR is 0 (HDR encryption Disabled)
01:30:59 Event notification facility epoll enabled.
01:31:00 Trusted host cache successfully built:/etc/hosts.equiv.
01:31:00 CCFLAGS2 value set to 0x200
01:31:00 SQL_FEAT_CTRL value set to 0x8008
01:31:00 SQL_DEF_CTRL value set to 0x4b0
01:31:00 IBM Informix Dynamic Server Version 12.10.FC7W1 Software Serial Number AAA#B000000
01:31:00 Conversion from version 11.50.xC6 Started

[...]

01:31:05 processing sysmaster
01:31:05 *** entity_ver=31 targetVersion=36 indexVersion= 0 ***
01:31:05 *** FromVersion=10 ToVersion=26 entity_ver=31 targetVersion=36 indexVersion=0 ***
01:31:05 *** FromVersion=26 ToVersion=28 entity_ver=31 targetVersion=36 indexVersion=1 ***
01:31:05 *** FromVersion=28 ToVersion=29 entity_ver=31 targetVersion=36 indexVersion=2 ***
01:31:05 *** FromVersion=29 ToVersion=30 entity_ver=31 targetVersion=36 indexVersion=3 ***
01:31:05 *** FromVersion=30 ToVersion=31 entity_ver=31 targetVersion=36 indexVersion=4 ***
01:31:05 *** FromVersion=31 ToVersion=32 entity_ver=31 targetVersion=36 indexVersion=5 ***
01:31:05 *** SQL 1 11.70.xC1 ***
01:31:05 Converting database sysmaster ...
01:31:06 The database sysmaster has been converted successfully.
01:31:06 *** FromVersion=32 ToVersion=33 entity_ver=32 targetVersion=36 indexVersion=6 ***
01:31:06 *** SQL 1 11.70.xC3 ***
01:31:06 Converting database sysmaster ...
01:31:06 The database sysmaster has been converted successfully.
01:31:06 *** FromVersion=33 ToVersion=34 entity_ver=33 targetVersion=36 indexVersion=7 ***
01:31:06 *** SQL 1 12.10.xC1 ***
01:31:06 Converting database sysmaster ...
01:31:06 The database sysmaster has been converted successfully.
01:31:06 *** FromVersion=34 ToVersion=35 entity_ver=34 targetVersion=36 indexVersion=8 ***
01:31:06 *** SQL 1 12.10.xC4 ***
01:31:06 Converting database sysmaster ...
01:31:06 The database sysmaster has been converted successfully.
01:31:06 *** FromVersion=35 ToVersion=36 entity_ver=35 targetVersion=36 indexVersion=9 ***
01:31:06 *** SQL 1 12.10.xC4 ***
01:31:06 processing sysutils

[...]

01:31:17 Conv/rev: calling conversion to 12.10.xC1 ...
01:31:19 Conv/rev: blade conversion to 12.10.xC1 completed
01:31:19 *** FromVersion=6 ToVersion=7 entity_ver=6 targetVersion=7 indexVersion=6 ***
01:31:19 *** BLADE 1 12.10.xC4 ***
01:31:19 Conv/rev: calling conversion to 12.10.xC4 ...
01:31:21 Conv/rev: blade conversion to 12.10.xC4 completed
01:31:21 Conv/rev: Finished conversion for component BLADE
01:31:21 Conv/rev: Started conversion for component SCHAPI
01:31:21 *** numVersions=7 targetVersion=7 ***
01:31:21 *** entity_ver=3 targetVersion=7 indexVersion= 0 ***
01:31:21 *** FromVersion=0 ToVersion=1 entity_ver=3 targetVersion=7 indexVersion=0 ***
01:31:21 *** FromVersion=1 ToVersion=2 entity_ver=3 targetVersion=7 indexVersion=1 ***
01:31:21 *** FromVersion=2 ToVersion=3 entity_ver=3 targetVersion=7 indexVersion=2 ***
01:31:21 *** FromVersion=3 ToVersion=4 entity_ver=3 targetVersion=7 indexVersion=3 ***
01:31:21 *** SCHAPI 1 11.70.xC1 ***
01:31:21 calling schapi_cvr1170_convert
01:31:21 *** FromVersion=4 ToVersion=5 entity_ver=4 targetVersion=7 indexVersion=4 ***
01:31:21 *** SCHAPI 1 11.70.xC3 ***



and it hangs...
Looking into the threads and thread stack:


IBM Informix Dynamic Server Version 12.10.FC7W1 -- Single-User -- Up 00:00:54 -- 266648 Kbytes

Threads:
tid tcb rstcb prty status vp-class name
2 4579d9e8 0 1 IO Idle 3lio* lio vp 0
3 458fd368 0 1 IO Idle 4pio* pio vp 0
4 4591e368 0 1 IO Idle 5aio* aio vp 0
5 4593f368 1d43600 1 IO Idle 6msc* msc vp 0
6 45970368 0 1 IO Idle 7fifo* fifo vp 0
7 45991608 44c1e028 3 sleeping secs: 1 1cpu main_loop()
8 459ed4c0 0 1 running 8soc* soctcppoll
9 459edc98 0 2 sleeping forever 1cpu* soctcplst
10 45a3cc88 44c1e8f0 1 sleeping secs: 1 1cpu flush_sub(0)
11 45a59028 44c1f1b8 1 sleeping secs: 1 1cpu flush_sub(1)
12 45a59368 44c1fa80 1 sleeping secs: 1 1cpu flush_sub(2)
13 45a596a8 44c20348 1 sleeping secs: 1 1cpu flush_sub(3)
14 45a599e8 44c20c10 1 sleeping secs: 1 1cpu flush_sub(4)
15 45a59d28 44c214d8 1 sleeping secs: 1 1cpu flush_sub(5)
16 45ab70e0 44c21da0 1 sleeping secs: 1 1cpu flush_sub(6)
17 45ab7420 44c22668 1 sleeping secs: 1 1cpu flush_sub(7)
18 45ab7760 0 3 IO Idle 1cpu* kaio
19 45c0c2c8 44c22f30 3 sleeping secs: 1 1cpu convert
20 45c0c9f8 44c237f8 2 sleeping secs: 1 1cpu aslogflush
21 45ccc958 44c240c0 1 sleeping secs: 8 1cpu btscanner_0
22 45ceba00 44c24988 3 cond wait ReadAhead 1cpu readahead_0
23 45d0eb50 44c25250 3 sleeping secs: 1 1cpu auto_tune
27 45d8aa80 44c25b18 1 sleeping secs: 0 1cpu sbspclean
28 45d30aa8 44c26ca8 1 sleeping secs: 1 1cpu defragclean
30 45d738f0 44c27570 3 sleeping secs: 1 1cpu* onmode_mon
31 45d89028 44c27e38 3 sleeping secs: 1 1cpu periodic
32 45d89608 44c28700 3 sleeping forever 1cpu memory
33 45d89be8 44c28fc8 3 sleeping secs: 15 1cpu session_mgr
35 45e08768 44c263e0 1 cond wait bp_cond 1cpu bf_priosweep()

IBM Informix Dynamic Server Version 12.10.FC7W1 -- Single-User -- Up 00:01:03 -- 266648 Kbytes

Stack for thread: 19 convert
base: 0x0000000045c19000
len: 69632
pc: 0x00000000013eaa6f
tos: 0x0000000045c24d50
state: sleeping
vp: 1

0x00000000013eaa6f (oninit) yield_processor_svp
0x00000000013ed5d3 (oninit) mt_yield
0x0000000001501340 (oninit) net_wait_for_connect
0x0000000001506ade (oninit) connsocket
0x000000000155f3c0 (oninit) tlConnect
0x000000000155c56a (oninit) slSQIreq
0x0000000001555343 (oninit) pfConReq
0x000000000154c628 (oninit) cmReqSync
0x000000000154d7ef (oninit) cmConReq
0x00000000014ee62e (oninit) ASF_Call
0x000000000076fd94 (oninit) dd_newconnect
0x00000000007712d0 (oninit) dd_remconnect
0x0000000000765404 (oninit) remcatalog
0x0000000000734831 (oninit) readtab
0x000000000073a372 (oninit) ddgettable_hierarchy_trace
0x00000000006bd1d5 (oninit) aud_ddgettable_trace
0x0000000000601206 (oninit) gettab_trace
0x0000000000655f15 (oninit) tlgetddtab_trace
0x0000000000656530 (oninit) tlp2tnode
0x000000000065af3b (oninit) tbl2tab
0x000000000063785d (oninit) sel2cb
0x0000000000639498 (oninit) subq2cb
0x00000000006632c1 (oninit) sq2expr
0x0000000000663373 (oninit) subq2expr
0x000000000066cca4 (oninit) expr2expr
0x0000000000682764 (oninit) ip_exprparse
0x00000000006a28fe (oninit) cmp_stmt
0x00000000006a2c1d (oninit) pr_compile
0x00000000006bf021 (oninit) aud_pr_compile
0x00000000006a3987 (oninit) updprocstat
0x00000000005cfba6 (oninit) excommand
0x0000000000f7eed7 (oninit) admin_exec_cmd
0x0000000000f7f2d5 (oninit) run_sql_script
0x0000000000f7f418 (oninit) run_script_etc
0x0000000000f9b826 (oninit) run_script_conv
0x0000000000f9b936 (oninit) ph_convrev
0x0000000000f42233 (oninit) schapi_cvr1170XC3_convert
0x0000000000f3f906 (oninit) do_each_version_work
0x0000000000f402d3 (oninit) generic_fw_main
0x0000000000f40cf6 (oninit) convertSM
0x00000000013c3720 (oninit) th_init_initgls
0x000000000142a518 (oninit) startup




The stack trace is clear. And precisely 2 minutes later it continues:


01:31:21 *** SCHAPI 1 11.70.xC3 ***
01:33:22 *** FromVersion=5 ToVersion=6 entity_ver=5 targetVersion=7 indexVersion=5 ***
01:33:22 *** SCHAPI 1 12.10.xC1 ***
01:33:22 calling schapi_cvr1210_convert
01:33:22 *** FromVersion=6 ToVersion=7 entity_ver=6 targetVersion=7 indexVersion=6 ***
01:33:22 *** SCHAPI 1 12.10.xC4 ***
01:33:22 calling schapi_cvr1210XC4_convert
01:33:23 Dynamically allocated new virtual shared memory segment (size 8192KB)
01:33:23 Memory sizes:resident:4796 KB, virtual:270044 KB, no SHMTOTAL limit
01:33:23 Conv/rev: Finished conversion for component SCHAPI




And it completed the conversion. In the customer situation, a later test showed a "hang" period of around 45m. So, a big warning.... The conversion process will run an UPDATE STATISTICS FOR PROCEDURE on sysadmin database. If you have created some procedures that do remote/distributed operations than you must consider this "problem" during the upgrade. Here are your options:

1. Allow connections to that remote server(s)
2. Remove the procedures and recreate them
3. Use INFORMIXCONTIME parameter (12.10 only) to reduce the timeouts
4. Remove the remote INFORMIXSERVER from the INFORMIXSQLHOSTS during migration. This also makes the connections attempts very quickly (as it simply can't find where to connect)

Versão Portuguesa
Há algum tempo num cliente, estando a testar uma migração de uma versão 11.50.FC7 para 12.10.FC7W1 deparámo-nos com um aparente bloqueio. Não é o que gostamos de ver, ainda que seja num ambiente de testes.
Esperámos uns minutos e tentámos perceber o que se estava a passar. Havia uma thread chamada "convert" que aparentemente estava inativa. O seu stack trace não parecia mudar
As funções que apareciam no stack sugeriam que estava a tentar contactar um servidor remoto, seguindo o que parecia ser uma chamada a um UPDATE STATISTICS. Isto foi uma grande surpresa.... Olhando para o online.log estava parado na fase "SCHAPI". Isto está relacionado com a sysadmin.
Considerando que o upgrade não estava com bom aspeto e que se tratava de um ambiente de testes decidimos abortar e usámos o onrestorept para reverter para a versão 11.50 (umas das minhas funcionalidades favoritas).
Para efeitos de teste e investigação decidimos executar um UPDATE STATISTICS FOR PROCEDURE na base de dados sysadmin. E voilá, preso de novo.
Portanto um UPDATE STATISTICS FOR PROCEDURE na sysadmin bloqueava o sistema e isso teria acontecido durante o upgrade. Após mais investigação concluímos que tínhamos criado procedimentos nesta base de dados para implementar algumas tarefas de monitorização e que esses procedimentos executavam algumas operações remotas para enviar a informação de monitorização para um sistema central. E antes do upgrade tinha sido feito uma alteração "de rotina" que consistiu na alteração do $INFORMIXSQLHOSTS de forma a "isolar" este motor. Basicamente foi adicionado um valor a todas as portas dos listeners, o que faz com que os clientes (não alterados) não consigam chegar ao ambiente. A consequência disto é que quando efetuamos o UPDATE STATISTICS este motor tenta aceder ao(s) motores referenciados nas operações remotas o que também não será possível. E como tal temos um timeout por cada operação remota encontrada nos procedimentos. Testes posteriores na 12.10 mostraram que a espera por cada tentativa de conexão é o valor por omissão (60s) ou o que estiver definido no parâmetro de $ONCONFIG INFORMIXCONTIME introduzido recentemente.

Tudo isto pode ser reproduzido facilmente. Num motor com a versão 11.50 criamos um procedimento simples (inútil):


bica@primary:informix-> onstat -

IBM Informix Dynamic Server Version 11.50.FC7 -- On-Line -- Up 00:30:33 -- 269068 Kbytes


bica@primary:informix-> cat test_remote_proc_stats.sql


CREATE PROCEDURE test_updstats_remote()

DEFINE i INTEGER;

SELECT 1 INTO i FROM stores@test:systables WHERE tabid = 1;

SELECT 2 INTO i FROM somedb@test:systables WHERE tabid = 1;

END PROCEDURE;
bica@primary:informix-> 


bica@primary:informix-> dbaccess sysadmin test_remote_proc_stats.sql

Database selected.

Routine created.

Database closed.

bica@primary:informix->

O servidor de base de dados "test" existe no $INFORMIXSQLHOSTS, mas aponta para um serviço não existente. Tentemos um upgrade para a 12.10.FC7 e vejamos o que acontece:

No online.log temos:


01:30:52 IBM Informix Dynamic Server Started.
01:30:52 Requested shared memory segment size rounded from 4308KB to 4796KB

Wed Aug 31 01:30:54 2016

01:30:54 Requested shared memory segment size rounded from 221001KB to 221004KB
01:30:54 Successfully added a bufferpool of page size 2K.

01:30:54 Event alarms enabled. ALARMPROG = '/usr/informix/srvr1210fc7w1/etc/alarmprogram.sh'
01:30:54 Booting Language from module <>
01:30:54 Loading Module
01:30:54 Booting Language from module <>
01:30:54 Loading Module
01:30:59 DR: DRAUTO is 0 (Off)
01:30:59 DR: ENCRYPT_HDR is 0 (HDR encryption Disabled)
01:30:59 Event notification facility epoll enabled.
01:31:00 Trusted host cache successfully built:/etc/hosts.equiv.
01:31:00 CCFLAGS2 value set to 0x200
01:31:00 SQL_FEAT_CTRL value set to 0x8008
01:31:00 SQL_DEF_CTRL value set to 0x4b0
01:31:00 IBM Informix Dynamic Server Version 12.10.FC7W1 Software Serial Number AAA#B000000
01:31:00 Conversion from version 11.50.xC6 Started

[...]

01:31:05 processing sysmaster
01:31:05 *** entity_ver=31 targetVersion=36 indexVersion= 0 ***
01:31:05 *** FromVersion=10 ToVersion=26 entity_ver=31 targetVersion=36 indexVersion=0 ***
01:31:05 *** FromVersion=26 ToVersion=28 entity_ver=31 targetVersion=36 indexVersion=1 ***
01:31:05 *** FromVersion=28 ToVersion=29 entity_ver=31 targetVersion=36 indexVersion=2 ***
01:31:05 *** FromVersion=29 ToVersion=30 entity_ver=31 targetVersion=36 indexVersion=3 ***
01:31:05 *** FromVersion=30 ToVersion=31 entity_ver=31 targetVersion=36 indexVersion=4 ***
01:31:05 *** FromVersion=31 ToVersion=32 entity_ver=31 targetVersion=36 indexVersion=5 ***
01:31:05 *** SQL 1 11.70.xC1 ***
01:31:05 Converting database sysmaster ...
01:31:06 The database sysmaster has been converted successfully.
01:31:06 *** FromVersion=32 ToVersion=33 entity_ver=32 targetVersion=36 indexVersion=6 ***
01:31:06 *** SQL 1 11.70.xC3 ***
01:31:06 Converting database sysmaster ...
01:31:06 The database sysmaster has been converted successfully.
01:31:06 *** FromVersion=33 ToVersion=34 entity_ver=33 targetVersion=36 indexVersion=7 ***
01:31:06 *** SQL 1 12.10.xC1 ***
01:31:06 Converting database sysmaster ...
01:31:06 The database sysmaster has been converted successfully.
01:31:06 *** FromVersion=34 ToVersion=35 entity_ver=34 targetVersion=36 indexVersion=8 ***
01:31:06 *** SQL 1 12.10.xC4 ***
01:31:06 Converting database sysmaster ...
01:31:06 The database sysmaster has been converted successfully.
01:31:06 *** FromVersion=35 ToVersion=36 entity_ver=35 targetVersion=36 indexVersion=9 ***
01:31:06 *** SQL 1 12.10.xC4 ***
01:31:06 processing sysutils

[...]

01:31:17 Conv/rev: calling conversion to 12.10.xC1 ...
01:31:19 Conv/rev: blade conversion to 12.10.xC1 completed
01:31:19 *** FromVersion=6 ToVersion=7 entity_ver=6 targetVersion=7 indexVersion=6 ***
01:31:19 *** BLADE 1 12.10.xC4 ***
01:31:19 Conv/rev: calling conversion to 12.10.xC4 ...
01:31:21 Conv/rev: blade conversion to 12.10.xC4 completed
01:31:21 Conv/rev: Finished conversion for component BLADE
01:31:21 Conv/rev: Started conversion for component SCHAPI
01:31:21 *** numVersions=7 targetVersion=7 ***
01:31:21 *** entity_ver=3 targetVersion=7 indexVersion= 0 ***
01:31:21 *** FromVersion=0 ToVersion=1 entity_ver=3 targetVersion=7 indexVersion=0 ***
01:31:21 *** FromVersion=1 ToVersion=2 entity_ver=3 targetVersion=7 indexVersion=1 ***
01:31:21 *** FromVersion=2 ToVersion=3 entity_ver=3 targetVersion=7 indexVersion=2 ***
01:31:21 *** FromVersion=3 ToVersion=4 entity_ver=3 targetVersion=7 indexVersion=3 ***
01:31:21 *** SCHAPI 1 11.70.xC1 ***
01:31:21 calling schapi_cvr1170_convert
01:31:21 *** FromVersion=4 ToVersion=5 entity_ver=4 targetVersion=7 indexVersion=4 ***
01:31:21 *** SCHAPI 1 11.70.xC3 ***



e bloqueia...
Vejamos as threads e o respetivo stack:


IBM Informix Dynamic Server Version 12.10.FC7W1 -- Single-User -- Up 00:00:54 -- 266648 Kbytes

Threads:
tid tcb rstcb prty status vp-class name
2 4579d9e8 0 1 IO Idle 3lio* lio vp 0
3 458fd368 0 1 IO Idle 4pio* pio vp 0
4 4591e368 0 1 IO Idle 5aio* aio vp 0
5 4593f368 1d43600 1 IO Idle 6msc* msc vp 0
6 45970368 0 1 IO Idle 7fifo* fifo vp 0
7 45991608 44c1e028 3 sleeping secs: 1 1cpu main_loop()
8 459ed4c0 0 1 running 8soc* soctcppoll
9 459edc98 0 2 sleeping forever 1cpu* soctcplst
10 45a3cc88 44c1e8f0 1 sleeping secs: 1 1cpu flush_sub(0)
11 45a59028 44c1f1b8 1 sleeping secs: 1 1cpu flush_sub(1)
12 45a59368 44c1fa80 1 sleeping secs: 1 1cpu flush_sub(2)
13 45a596a8 44c20348 1 sleeping secs: 1 1cpu flush_sub(3)
14 45a599e8 44c20c10 1 sleeping secs: 1 1cpu flush_sub(4)
15 45a59d28 44c214d8 1 sleeping secs: 1 1cpu flush_sub(5)
16 45ab70e0 44c21da0 1 sleeping secs: 1 1cpu flush_sub(6)
17 45ab7420 44c22668 1 sleeping secs: 1 1cpu flush_sub(7)
18 45ab7760 0 3 IO Idle 1cpu* kaio
19 45c0c2c8 44c22f30 3 sleeping secs: 1 1cpu convert
20 45c0c9f8 44c237f8 2 sleeping secs: 1 1cpu aslogflush
21 45ccc958 44c240c0 1 sleeping secs: 8 1cpu btscanner_0
22 45ceba00 44c24988 3 cond wait ReadAhead 1cpu readahead_0
23 45d0eb50 44c25250 3 sleeping secs: 1 1cpu auto_tune
27 45d8aa80 44c25b18 1 sleeping secs: 0 1cpu sbspclean
28 45d30aa8 44c26ca8 1 sleeping secs: 1 1cpu defragclean
30 45d738f0 44c27570 3 sleeping secs: 1 1cpu* onmode_mon
31 45d89028 44c27e38 3 sleeping secs: 1 1cpu periodic
32 45d89608 44c28700 3 sleeping forever 1cpu memory
33 45d89be8 44c28fc8 3 sleeping secs: 15 1cpu session_mgr
35 45e08768 44c263e0 1 cond wait bp_cond 1cpu bf_priosweep()

IBM Informix Dynamic Server Version 12.10.FC7W1 -- Single-User -- Up 00:01:03 -- 266648 Kbytes

Stack for thread: 19 convert
base: 0x0000000045c19000
len: 69632
pc: 0x00000000013eaa6f
tos: 0x0000000045c24d50
state: sleeping
vp: 1

0x00000000013eaa6f (oninit) yield_processor_svp
0x00000000013ed5d3 (oninit) mt_yield
0x0000000001501340 (oninit) net_wait_for_connect
0x0000000001506ade (oninit) connsocket
0x000000000155f3c0 (oninit) tlConnect
0x000000000155c56a (oninit) slSQIreq
0x0000000001555343 (oninit) pfConReq
0x000000000154c628 (oninit) cmReqSync
0x000000000154d7ef (oninit) cmConReq
0x00000000014ee62e (oninit) ASF_Call
0x000000000076fd94 (oninit) dd_newconnect
0x00000000007712d0 (oninit) dd_remconnect
0x0000000000765404 (oninit) remcatalog
0x0000000000734831 (oninit) readtab
0x000000000073a372 (oninit) ddgettable_hierarchy_trace
0x00000000006bd1d5 (oninit) aud_ddgettable_trace
0x0000000000601206 (oninit) gettab_trace
0x0000000000655f15 (oninit) tlgetddtab_trace
0x0000000000656530 (oninit) tlp2tnode
0x000000000065af3b (oninit) tbl2tab
0x000000000063785d (oninit) sel2cb
0x0000000000639498 (oninit) subq2cb
0x00000000006632c1 (oninit) sq2expr
0x0000000000663373 (oninit) subq2expr
0x000000000066cca4 (oninit) expr2expr
0x0000000000682764 (oninit) ip_exprparse
0x00000000006a28fe (oninit) cmp_stmt
0x00000000006a2c1d (oninit) pr_compile
0x00000000006bf021 (oninit) aud_pr_compile
0x00000000006a3987 (oninit) updprocstat
0x00000000005cfba6 (oninit) excommand
0x0000000000f7eed7 (oninit) admin_exec_cmd
0x0000000000f7f2d5 (oninit) run_sql_script
0x0000000000f7f418 (oninit) run_script_etc
0x0000000000f9b826 (oninit) run_script_conv
0x0000000000f9b936 (oninit) ph_convrev
0x0000000000f42233 (oninit) schapi_cvr1170XC3_convert
0x0000000000f3f906 (oninit) do_each_version_work
0x0000000000f402d3 (oninit) generic_fw_main
0x0000000000f40cf6 (oninit) convertSM
0x00000000013c3720 (oninit) th_init_initgls
0x000000000142a518 (oninit) startup




O stack é claro. E precisamente 2 minutos depois a conversão continua (o procedimento tinha duas instruções remotas):


01:31:21 *** SCHAPI 1 11.70.xC3 ***
01:33:22 *** FromVersion=5 ToVersion=6 entity_ver=5 targetVersion=7 indexVersion=5 ***
01:33:22 *** SCHAPI 1 12.10.xC1 ***
01:33:22 calling schapi_cvr1210_convert
01:33:22 *** FromVersion=6 ToVersion=7 entity_ver=6 targetVersion=7 indexVersion=6 ***
01:33:22 *** SCHAPI 1 12.10.xC4 ***
01:33:22 calling schapi_cvr1210XC4_convert
01:33:23 Dynamically allocated new virtual shared memory segment (size 8192KB)
01:33:23 Memory sizes:resident:4796 KB, virtual:270044 KB, no SHMTOTAL limit
01:33:23 Conv/rev: Finished conversion for component SCHAPI




E termina a conversão. Na situação concreta do cliente, um novo teste com a mesma instância apresentou um "bloqueio" durante cerca de 45m (correspondente a sensivelmente o mesmo número de instruções remotas nos procedimentos da sysadmin). Portanto um grande alerta...: O processo de conversão irá executar um UPDATE STATISTICS FOR PROCEDURE na base de dados sysadmin (isto não está documentado, mas é natural tendo em conta o processo de conversão da mesma). Se porventura criou procedimentos que acedem a instâncias remotas, então deverá ter isto em conta As opções para lidar com isto serão::

1. Permitir as ligações ao(s) servidor remoto
2. Remover os procedimentos e recriá-los depois da migração
3. Usar o parâmetro INFORMIXCONTIME (a partir da 12.10) para reduzir os tempos de espera
4. Remover o INFORMIXSERVER remoto do $INFORMIXSQLHOSTS durante a conversão. Isto também permite acelerar as tentativas de conexão pois ao não encontrar a referência no ficheiro o motor não sabe onde se ligar, e portanto não se aplicam os timeouts

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:

1. 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.
2. 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...
3. 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:

1. 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
2. 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...
3. 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