Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  IDS Bug of the Day: DBINFO

    Posted 06/17/20 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
    ------------------------------

    #Informix


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

    Posted 06/17/20 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 06/18/20 12:01 AM
    Just add a filter to ignore all partnuns < 10240

    Art





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

    Posted 06/18/20 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
    ------------------------------

    Original Message


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

    Posted 06/18/20 12:55 PM

    Yep – HCL broke thousands of scripts

     




    Original Message


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

    Posted 06/18/20 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 06/18/20 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

     




    Original Message


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

    Posted 12/21/20 05:09 PM

    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
    ------------------------------

    Original Message


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

    Posted 12/21/20 05:15 PM
    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



    Original Message


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

    Posted 06/18/20 10:44 AM
    I did partnum >= 1048576

    My support case later suggested the same thing.

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



Related Content