SQLCODE -401 kommt nur auf bestimmten DB2-Systemen

Ansicht von 6 Beiträgen - 1 bis 6 (von insgesamt 6)
  • Author
    Posts
  • 2. Mai 2013 um 12:45 Uhr #4165

    Anonym
    Inaktiv

    Hallo zusammen,

    ich bekomme bei ein und demselben SQL auf der gleichen Tabelle in einem System einen SQLCODE -401 und auf dem anderen System läuft er fehlerfrei durch.

    DSNT408I SQLCODE = -401, ERROR:  THE OPERANDS OF AN ARITHMETIC OR COMPARISON
             OPERATION ARE NOT COMPARABLE
    DSNT418I SQLSTATE   = 42818 SQLSTATE RETURN CODE
    DSNT415I SQLERRP    = DSNXOBFC SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD    = 930 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD    = X’000003A2’  X’00000000’  X’00000000’  X’FFFFFFFF‘
             X’00000000’  X’00000000′ SQL DIAGNOSTIC INFORMATION

    So sieht der SQL aus.

    SELECT
    * FROM
    XXXX.Table1
    WHERE CHARCOL1  <> 1
    FETCH FIRST 5 ROWS ONLY ;

    Der -DIS GROUP command liefert auf beiden Systemen
    DB2  LVL 101.

    Woran kann das liegen?
    Das ganze ist deshalb unerfreulich, da der SQL auf der Testanlage durchläuft, aber dann in der Produktion abbricht.

    Gruß Klaus

    3. Mai 2013 um 19:52 Uhr #4303

    Anonym
    Inaktiv

    Hi Klaus,

    quite strange, because usually DB2 responds as follows:

    SELECT NAME, DEGREE 
    
	FROM SYSIBM.SYSPLAN 
    
	WHERE DEGREE <> 1 
    
	
    
	DSNT408I SQLCODE = -420, ERROR:  THE VALUE OF A STRING ARGUMENT WAS NOT   
    
	         ACCEPTABLE TO THE DECFLOAT FUNCTION                             
    
	DSNT418I SQLSTATE   = 22018 SQLSTATE RETURN CODE                         
    
	DSNT415I SQLERRP    = DSNXRNUM SQL PROCEDURE DETECTING ERROR             
    
	DSNT416I SQLERRD    = -245  0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION     
    
	DSNT416I SQLERRD    = X'FFFFFF0B'  X'00000000'  X'00000000'  X'FFFFFFFF' 
    
	         X'00000000'  X'00000000' SQL DIAGNOSTIC INFORMATION

    Reason:
    When DB2 implicitly casts a character string or graphic string value to a numeric value, the target type is DECFLOAT(34) which is compatible with other numeric data types.

    Would you please provide your DB2’s system variables as described in blog article https://ruban.de/1575/misc/sql-to-retrieve-db2-subsystem-information-update-for-db2-10-zos/.

    regards
    Gernot

    15. Mai 2013 um 9:16 Uhr #4399

    Anonym
    Inaktiv

    Hello Ruban,

    all system variables match.

    Only the two below differ (top Value ==> production; bottom value ==> test machine)

    VERSION
    —-+—–
    DSN10012
    DSN10015

    MODIFCATION_LEVEL
    ——-+———+—–
    CM9/CM9*/ENFM9/ENFM9/
    NFM10/

    I don’t know the details about Version and modification level.
    May this explain the behavior?

    Since mainframe SPUFI-SQL results in very wide output file, I don’t know how paste all variables from SQL.

    Hope these variables help.

    regards Klaus

    15. Mai 2013 um 9:44 Uhr #4464

    Anonym
    Inaktiv

    Hello Ruban,

    your SQL also gives an SQLCODE -401 (not -420 like on your machine) on our machine!

    ———+———+———+———+———+———+———+———+
         SELECT NAME, DEGREE
         FROM SYSIBM.SYSPLAN
         WHERE DEGREE <> 1
    ———+———+———+———+———+———+———+———+
    DSNT408I SQLCODE = -401, ERROR:  THE OPERANDS OF AN ARITHMETIC OR COMPARISON
             OPERATION ARE NOT COMPARABLE
    DSNT418I SQLSTATE   = 42818 SQLSTATE RETURN CODE
    DSNT415I SQLERRP    = DSNXOBFC SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD    = 930 0  0  -1  0  0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD    = X’000003A2’  X’00000000’  X’00000000’  X’FFFFFFFF‘
             X’00000000’  X’00000000′ SQL DIAGNOSTIC INFORMATION
    ———+———+———+———+———+———+———+———+
    DSNE618I ROLLBACK PERFORMED, SQLCODE IS 0
    DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

    21. Mai 2013 um 9:49 Uhr #4496

    Anonym
    Inaktiv

    Hi Klaus,

    your production system is probably still in DB2 10 Compatibility Mode (to version 9) or Enable New Function Mode.

    Your test/development system has been fully migrated to DB2 10 New Function Mode

    This difference is quite important and explains the different behavior of test and production system.

    Ask your DB2 system administration for planned migration path.

    Regards
    Gernot

    20. Juni 2013 um 7:49 Uhr #4519

    Anonym
    Inaktiv

    One more example of problems which can raise from uneven DB2 software levels:
    [list bull-blueball][*]tested in development/test system => works[/list][list bull-redball][*]execution in production => fails with SQLCODE -206[/list]
    Reason: DB2 software maintenance applied on development/test system, but currently not on production system.

    DB2 returns SQLCODE206 (or precompiler message DSNH4702I) for an UPDATE statement which contains multiple assignments in the SET assignment clause, and an assignment containing a scalar subquery with a JOIN is followed by an assignment in which the target column name is qualified with the correlation-name. Which is a fault.           

    APAR PM62190
    RA10 PSY UK78352   UP12/05/15 P F205
    R910 PSY UK78353   UP12/05/15 P F205

    regards
    Gernot

  • Author
    Posts
Ansicht von 6 Beiträgen - 1 bis 6 (von insgesamt 6)

You must be logged in to reply to this topic.