IBM Support

IT31975: SELECT WITH SUBSTRING() RETURNS SQL0138N FOR TABLE ORGANIZED BYCOLUMN

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as fixed if next.

Error description

  • When executing a SELECT statement with SUBSTRING() function on
    table organized by column, error " SQL0138N  The statement was
    not executed because a numeric argument of a scalar function is
    out of range" is returned
    
    Here's the scenario:
    
    CREATE TABLE ROW_TABLE ( ST CHAR(02),LOW VARCHAR(25) )
    DISTRIBUTE BY RANDOM ORGANIZE BY ROW
    DB20000I  The SQL command completed successfully.
    
    INSERT INTO "ROW_TABLE" (ST, LOW) VALUES ('OK', '36673')
    DB20000I  The SQL command completed successfully.
    
    CREATE TABLE COLUMN_TABLE ( ST CHAR(02),LOW VARCHAR(25) )
    DISTRIBUTE BY RANDOM ORGANIZE BY COLUMN
    DB20000I  The SQL command completed successfully.
    
    INSERT INTO "COLUMN_TABLE" (ST, LOW) VALUES ('OK', '36673')
    DB20000I  The SQL command completed successfully.
    
    SELECT T0.C0 AS C0,T0.C1 AS C1,T0.C2 AS C2,T0.C3 AS C3,(CASE
    WHEN (T0.C2 > 0) THEN SUBSTRING(T0.C1, 1, T0.C3,CODEUNITS32)
    ELSE NULL END) AS C4 FROM (SELECT T0.C0 AS C0,T0.C1 AS C1,(CASE
    WHEN (T0.C2 IS NULL) THEN 0 ELSE T0.C2 END) AS C2,((CASE WHEN
    (T0.C2 IS NULL) THEN 0 ELSE T0.C2 END) - 1) AS C3 FROM (SELECT
    T0.ST AS C0,T0.LOW AS C1,LOCATE('-', T0.LOW, CODEUNITS32) AS C2
    FROM COLUMN_TABLE T0) T0) T0
    
    C0 C1                        C2          C3          C4
    -- ------------------------- ----------- -----------
    -------------------------
    OK 36673                               0          -1 -
    SQL0138N  The statement was not executed because a numeric
    argument of a
    scalar function is out of range.  SQLSTATE=22011
    
    SELECT T0.C0 AS C0,T0.C1 AS C1,T0.C2 AS C2,T0.C3 AS C3,(CASE
    WHEN (T0.C2 > 0) THEN SUBSTRING(T0.C1, 1, T0.C3,CODEUNITS32)
    ELSE NULL END) AS C4 FROM (SELECT T0.C0 AS C0,T0.C1 AS C1,(CASE
    WHEN (T0.C2 IS NULL) THEN 0 ELSE T0.C2 END) AS C2,((CASE WHEN
    (T0.C2 IS NULL) THEN 0 ELSE T0.C2 END) - 1) AS C3 FROM (SELECT
    T0.ST AS C0,T0.LOW AS C1,LOCATE('-', T0.LOW, CODEUNITS32) AS C2
    FROM ROW_TABLE T0) T0) T0
    
    C0 C1                        C2          C3          C4
    -- ------------------------- ----------- -----------
    -------------------------
    OK 36673                               0          -1 -
    
    With following environment:
    DB2_WORKLOAD=ANALYTICS
    DB2_USE_ALTERNATE_PAGE_CLEANING=ON [DB2_WORKLOAD]
    DB2_DIRECT_IO=NO
    DB2_ANTIJOIN=EXTEND [DB2_WORKLOAD]
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * all                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to db2 version 11.5.4.0                              *
    ****************************************************************
    

Problem conclusion

Temporary fix

Comments

APAR Information

  • APAR number

    IT31975

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B50

  • Status

    CLOSED FIN

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2020-02-25

  • Closed date

    2020-06-30

  • Last modified date

    2020-06-30

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

Fix information

Applicable component levels

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.5"}]

Document Information

Modified date:
01 July 2020