Informix

nested-group-icon.png

DB2

Expand all | Collapse all

IDS Bug of the Day: DBINFO

  • 1.  IDS Bug of the Day: DBINFO

    Posted Wed June 17, 2020 07:23 PM
    Consider the following query:

    SELECT dbsname, tabname, DBINFO('dbspace', partnum)
      FROM systabnames
     ORDER BY 1, 2;

    In 12.10.FC13 and earlier, this query works fine. But starting with 12.10.FC14, it fails:

    727: Invalid or NULL TBLspace number given to dbinfo(dbspace).
    Error in line 3
    Near character position 12

    I believe this is because records were added to systabnames in FC14 that have very low partnum values that DBINFO doesn't know what to do with.

    I have also replicated this in 14.10.FC3.

    I believe the DBINFO() function needs to be updated to account for these.

    ------------------------------
    TOM GIRSCH
    ------------------------------


  • 2.  RE: IDS Bug of the Day: DBINFO

    Posted Wed June 17, 2020 07:29 PM
    Known feature - not a bug according to HCL -with a fix coming that allows environment to override the behaviour

    But you are correct all scripts/code that use DBinfo are now broke thanks to HCL developers

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 3.  RE: IDS Bug of the Day: DBINFO

    Posted Thu June 18, 2020 12:01 AM
    Just add a filter to ignore all partnuns < 10240

    Art





  • 4.  RE: IDS Bug of the Day: DBINFO

    Posted Thu June 18, 2020 10:45 AM
    But a PITA because I had several scripts to update and had to re-deploy to about half a dozen systems.

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 5.  RE: IDS Bug of the Day: DBINFO

    Posted Thu June 18, 2020 12:55 PM

    Yep – HCL broke thousands of scripts

     






  • 6.  RE: IDS Bug of the Day: DBINFO

    Posted Thu June 18, 2020 10:43 AM
    T32768: DBINFO(DBSPACE) RAISES 727: INVALID OR NULL TBLSPACE NUMBER

    https://www.ibm.com/support/pages/node/6207160​​

    ------------------------------
    TOM GIRSCH
    ------------------------------



  • 7.  RE: IDS Bug of the Day: DBINFO

    Posted Thu June 18, 2020 12:55 PM

    From XC5 you can set  DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM to reset back to the original functionaliy

     

    An environment variable named DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM was created and can be set to a non-zero value to enable the new feature. It is not active by default to continue the existing behavior.

    If it is set this way dbinfo('dbspace', partnum) will return NULL instead of an error when an invalid partnum is provided. This is true for any partnum that results in an invalid (db)space number. It includes the pseudo-tables for which the (db)space number is 0. For these tables the partnum is invalid in that sense. If a partnum of NULL is provided, the error -727 will be returned in any case.

    The environment variable can be set:

    1) as an environment variable for the instance

    2) as an environment variable for the client like dbaccess

    3) as a session environment variable

    In this order of increasing precedence - meaning session environment takes precedence over client environment which takes precedence over instance environment.

     

     

    You can request a special build  - I did

     

    Cheers

    Paul

     






  • 8.  RE: IDS Bug of the Day: DBINFO

    Posted 29 days ago

    Hello Paul,

    Should I request a special build of version 12.10.xc14 to use the DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM environment variable? Setting environment variables doesn't seem to make any difference in behavior.

    # export DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM=1
    # echo "select first 1 dbinfo('dbspace',partnum) from systabnames;" | dbaccess sysmaster

    Database selected.


    727: Invalid or NULL TBLspace number given to dbinfo(dbspace).
    Error in line 1
    Near character position 56


    Database closed.

    # dbaccess sysmaster -

    Database selected.

    > set environment DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM "1";

    19840: Invalid session environment variable.
    Error in line 1
    Near character position 65
    >



    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 9.  RE: IDS Bug of the Day: DBINFO

    Posted 29 days ago
    All you can do is ask :-)

    Classic IBM - take a perfectly good function and tweak it so it breaks everything and then blame us for not using it correctly :-)

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 10.  RE: IDS Bug of the Day: DBINFO

    Posted Thu June 18, 2020 10:44 AM
    I did partnum >= 1048576

    My support case later suggested the same thing.

    ------------------------------
    TOM GIRSCH
    ------------------------------