sqlcode -407 sur update

[liodb2]

Bonjour,
L'ordre update suivant me renvoit un sqlcode -407. Est-ce que quelqu'un peut m'aider, tout en sachant que je ne peux créer de table intermédiaire.

Code :

1
2
3
4
5
6
7
8
9
10
UPDATE DPRO.I928 B                                       
  SET B.DTENSO =   (SELECT MIN(A.DTENSO) FROM DPRO.I928 A
                    WHERE                                
                        A.NUCE = B.NUCE                  
                    AND A.COCXBQ = B.COCXBQ              
                    AND A.CDSOLE = B.CDSOLE              
                    AND A.NUPER  = B.NUPER               
                    AND A.DTENSO < B.DTENSO              
                    AND A.CTETSO = 'A'                   
                    AND B.CTETSO = 'A');

[K2R400]

Il faut faire :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
UPDATE DPRO.I928 B 
SET B.DTENSO = (SELECT MIN(A.DTENSO) FROM DPRO.I928 A
WHERE 
A.NUCE = B.NUCE 
AND A.COCXBQ = B.COCXBQ 
AND A.CDSOLE = B.CDSOLE 
AND A.NUPER = B.NUPER 
AND A.DTENSO < B.DTENSO 
AND A.CTETSO = 'A' 
AND B.CTETSO = 'A')
WHERE EXISTS (SELECT * FROM DPRO.I928 A
WHERE 
A.NUCE = B.NUCE 
AND A.COCXBQ = B.COCXBQ 
AND A.CDSOLE = B.CDSOLE 
AND A.NUPER = B.NUPER 
AND A.DTENSO < B.DTENSO 
AND A.CTETSO = 'A' 
AND B.CTETSO = 'A');

[Mercure]

Ou bien, plus sioux mais aussi plus difficile à appréhender.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
UPDATE DPRO.I928 B 
SET B.DTENSO = CASE WHEN 
((SELECT MIN(A.DTENSO) FROM DPRO.I928 A 
WHERE 
A.NUCE = B.NUCE 
AND A.COCXBQ = B.COCXBQ 
AND A.CDSOLE = B.CDSOLE 
AND A.NUPER = B.NUPER
AND A.DTENSO < B.DTENSO
) IS NOT NULL
AND A.CTETSO = 'A' 
AND B.CTETSO = 'A')
THEN 
(SELECT MIN(A.DTENSO) FROM DPRO.I928 A 
WHERE 
A.NUCE = B.NUCE 
AND A.COCXBQ = B.COCXBQ 
AND A.CDSOLE = B.CDSOLE 
AND A.NUPER = B.NUPER
AND A.DTENSO < B.DTENSO 
AND A.CTETSO = 'A' 
AND B.CTETSO = 'A') 
ELSE B.DTENSO
END

[punkoff]

Pour des raisons de perf la 2eme méthode je ne l'utiliserai pas (forcer un update sur toute la table alors qu'il n'y en a pas besoin), sans compter que la requête risque d'engendrer 2 sous requête sur la même table non ?

[Luc Orient]

En DB2 z/OS (espérons que ça soit cette plate forme ... ) on a pour le -407 :

AN UPDATE, INSERT, OR SET VALUE IS NULL, BUT THE OBJECT COLUMN column-name CANNOT CONTAIN NULL VALUES

Explanation

One of the following conditions occurred:

•A null insert or update value was specified for a column defined as NOT NULL.

•A SET transition variable statement specified a NULL value for column defined as NOT NULL without the WITH DEFAULT clause in the table definition.

•The update or insert value was DEFAULT, but the object column was declared as NOT NULL without WITH DEFAULT in the table definition. Consequently:
◦A default value of NULL cannot be inserted into that column.
◦An update cannot set default values of NULL in that column.
◦A SET transition variable statement in a trigger cannot set default values of NULL in that column.

•The column name list for the INSERT statement omits a column declared NOT NULL and without WITH DEFAULT in the table definition.

•The view for the INSERT statement omits a column declared NOT NULL and without WITH DEFAULT in the base table definition.
•A null insert value was specified for a ROWID column.

System action

The statement cannot be executed. The 'column-name' might be returned in the SQLCA, depending on the syntax of the SQL statement in which the error was detected
Programmer response

Examine the definition of the object table to determine which columns of the table have the NOT NULL attribute or have a type of ROWID, and correct the SQL statement accordingly.

Moi je pense tout simplement que la table résultante sur laquelle s'applique la fonction MIN est vide et que par conséquent la fonction retourne un NULL qui ne peut être stocké dans la colonne à gauche du SET puisque cette dernière n'accepte pas le NULL ...

[punkoff]

y a une valeur null car son update n'a pas de clause where.

Du coup quand il fait son case il ne couvre pas toutes les possibilités.

Chose fixée par k2r et mercure

Qui plus est avec une clause where si il n'y aucune ligne à updater, le sgbd ne fera rien, et ne retournera pas d'erreur.

[Luc Orient]

Envoyé par punkoff

y a une valeur null car son update n'a pas de clause where.

Je parle du SELECT pas de l'UPDATE.

Du coup quand il fait son case il ne couvre pas toutes les possibilités.

Dans l'ordre d'origine je ne vois pas de CASE.

Qui plus est avec une clause where si il n'y aucune ligne à updater, le sgbd ne fera rien, et ne retournera pas d'erreur.

Pas compris là ...
On parle bien de l'ordre donné dans le premier message non ?

[punkoff]

pardon oui j'ai mélangé les deux mais la démarche reste la même.

son sous-select dans son update ne va trouver que des valeurs selon la clause where de son sous-select.

Ceci va engendrer des valeurs null pour les enregistrements que son sous-select ne prend pas en compte (vu qu'il fait un update de toute la table)


Rajouter une clause where à son update fixera ce problème car le sous-select trouvera systématiquement les valeurs, et de ce fait, si la table est vide, il n'updatera rien donc aucune erreur.

edit: donc sur le principe de l'erreur je suis entièrement d'accord avec vous (null value), par contre sur le fait que la table initial soit vide, je ne suis pas forcément d'accord

[Luc Orient]

Envoyé par punkoff

edit: donc sur le principe de l'erreur je suis entièrement d'accord avec vous (null value), par contre sur le fait que la table initial soit vide, je ne suis pas forcément d'accord

Pour moi, c'est la définition même du -407. Le MIN ou le MAX de rien c'est pas défini, donc c'est NULL en SQL et vice versa ...

[Mercure]

DB2 z/OS ou DB2 System i, même combat. SQL renvoie le code SQL407 pour les mêmes raisons : à savoir qu'il ne peut pas mettre la valeur NULL dans une colonne qui n'accepte pas la valeur nul. Et, effectivement, la valeur renvoyée est nulle s'il n'y a pas correspondance dans le prédicat WHERE qui joint les deux tables, même si en fait il s'agit ici de la même table qui est jointe mais considérée par SQL comme s'il y avait deux tables distinctes.

Envoyé par punkoff

Pour des raisons de perf la 2eme méthode je ne l'utiliserai pas (forcer un update sur toute la table alors qu'il n'y en a pas besoin), sans compter que la requête risque d'engendrer 2 sous requête sur la même table non ?

Moi aussi, je préfère la méthode indiquée par K2R400 mais j'ai posté l'autre pour montrer qu'on peut aboutir à un bon résultat également en testant si les colonnes ont la valeur nul. Quant aux perfs, je ne pense pas que la différence entre les 2 méthodes soit vraiment significative. Il faudrait tester sur un gros volume pour être fixé. Peut-être que liodb2, l'OP de ce fil, peut nous faire ça ?

[bernard59139]

bonjour

la requete de base est fausse, ou du moins très incomplète.
On maj une colonne sans sélection, avec une sous-requete qui reprend la meme colonne de la même table.

Si la demanderesse pouvait s'exprimer...

[pdz74]

Bonsoir,

Par rapport à tous les échanges, la 1ère réponse de K2R400 parait tout à fait répondre au besoin. Ceci dit, la spécificité de cette requête c'est qu'on fait un UPDATE avec une ss-requête sur la même table. Le -407 est du aux lignes de la table pour lesquelles DB2 ne trouve pas de lignes dans la ss-requête. Et le seul prédicat qui empêche de trouver, c'est "AND A.DTENSO < B.DTENSO". En supprimant ce prédicat, il y a donc possibilité d'éviter de faire une 2ème ss-requête avec l'ajout de la clause WHERE, mais la contrepartie est qu'on fera plus de mise à jour que prévu, vu que même les lignes qui ont déjà la date minimum seront mises à jour.

Ca pourrait donner :

Code :

1
2
3
4
5
6
7
8
9
UPDATE DPRO.I928 B
  SET B.DTENSO =   (SELECT MIN(A.DTENSO) FROM DPRO.I928 A
                    WHERE
                        A.NUCE = B.NUCE
                    AND A.COCXBQ = B.COCXBQ
                    AND A.CDSOLE = B.CDSOLE
                    AND A.NUPER  = B.NUPER
                    AND A.CTETSO = 'A')
WHERE B.CTETSO = 'A';

Qu'est-ce qui sera le plus performant entre les différentes solutions, c'est totalement dépendant de la logique de la table I928. Si tu as très peu de doublons sur les colonnes NUCE, COCXBQ, CDSOLE et NUPER, cela signifie que très peu de lignes doivent être mises à jour, ma solution risque alors d'être pénalisante, la solution de K2R400 est meilleure. Si, inversement, tu as énormément de doublons et que de nombreuses lignes sont donc à mettre à jour, quelques unes de plus ne couteront pas beaucoup plus chères, ma proposition peut alors s'avérer meilleure.

Il y a de nombreux paramètres qui vont rentrer en ligne de compte tel le LOCKSIZE de ton tablespace. A ce propos, avant de passer ce type de requête qui peut générer de nombreuses maj sans possibilité de COMMIT intermédiaire, ne pas hésiter à se servir de l'ordre LOCK TABLE I928 IN EXCLUSIVE MODE. C'est bien sur à utiliser avec parcimonie puisque cela bloque tout accès concurrent, c'est donc générateur de contentions. Mais cela peut diviser les temps de réponse d'une telle requête par un facteur non négligeable puisque DB2 n'a plus qu'à gérer un seul verrou. Si tu n'es pas en LOCKSIZE ANY, cad sans possibilité de faire du LOCK ESCALATION, cela peut même être obligatoire, sinon tu risques d'atteindre le nombre maxi de LOCK acceptés pour une tache et ta requête va planter par -904 chaque fois que tu la relanceras. Et si tu es en LOCKSIZE ANY, cela évite de gérer des milliers de verrous jusqu'au LOCK ESCALATION, qui, au final, va également bloquer toute la table (cqfd, autant anticiper et ne pas perdre de temps...).

Bonne utilisation et n'hésite pas à nous tenir au courant.